Question { TCS, 7426 }

Hi all,

I need query help for below senorio, could you please help
me.

TableName = City
CITYID ContinuationID CITYNAME
1 1 SAN
1 2 DIEGO
2 1 SAN
2 2 FRANCISCO
3 1 CHICAGO
4 1 NEW
4 2 YORK
4 3 CITY

Could you please help me to write a generalized SQL that
returns results as given below in the

Query result
CITYID NAME1 NAME2 NAME3 NAME4 NAME5
1 SAN DIEGO
2 SAN FRANCISCO
3 CHICAGO
4 NEW YORK CITY

Answer

How about this, a single dynamic query:

create table cities (CITYID int, ContinuationID int,
CITYNAME varchar(50))

insert into cities
select 1, 1, 'SAN' UNION
select 1, 2, 'DIEGO' UNION
select 2, 1, 'SAN' UNION
select 2, 2, 'FRANCISCO' UNION
select 3, 1, 'CHICAGO' UNION
select 4, 1, 'NEW' UNION
select 4, 2, 'YORK' UNION
select 4, 3, 'CITY' UNION
select 5, 1, 'CITY1' UNION
select 5, 2, 'CITY2' UNION
select 5, 3, 'CITY3' UNION
select 5, 4, 'CITY4' UNION
select 5, 5, 'CITY5'

select * from cities

select distinct CITYID,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=1)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=1)
else ''
end as name1,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=2)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=2)
else ''
end as name2,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=3)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=3)
else ''
end as name3,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=4)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=4)
else ''
end as name4,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=5)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=5)
else ''
end as name5
from cities c
where ContinuationID=1

drop table cities

Question { ABC, 9342 }

What is the difference between in and exists.
Ex: select * from emp where empno in(....) and
select * from emp where empno exists(....)

What is the difference between a Join and Union and Union
and UnionAll.

Answer

Check below links to know the reason with examples from my blog posts:

IN vs EXISTS vs JOIN: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/

UNION vs UNION ALL: http://sqlwithmanoj.wordpress.com/2010/12/30/why-union-all-is-faster-than-union/

Question { HCL, 7108 }

Advantages and disadvantages of stored procedures.

Answer

The only disadvantage I see with Stored-procs is that we
cannot use them with select statements.

Question { IBM, 9842 }

What is the diffrence between update_one and auto_fix?

Answer

Both are used as 1st param values with sp_change_users_login
s-proc

This is deprecated, should not be used.
more info: http://msdn.microsoft.com/en-us/library/ms174378.aspx

Question { TCS, 10176 }

Delete duplicate records from the table?(Table must have
unique id)

Answer

Check following blog post on how to first identify & then delete duplicate records: http://sqlwithmanoj.wordpress.com/2011/10/14/identify-delete-duplicate-records-from-a-table/


This lists multiple ways to delete duplicates.

Question { 3742 }

Simple example for difference between select and cursor in sql

Answer

- SELECT is a Query operator, which is also called a set-based approach.

- CURSOR is a looping operator, which is also called a iterative approach or no-set based approach.

Ideally SELECT is more performant than CUSROSR, but there are certain scenarios when CURSOR are the last approach and sometimes beneficial.

More about Cursors:
- Cursor Life Cycle: http://sqlwithmanoj.wordpress.com/2010/10/24/sql-server-cursor-life-cycle/
- Cursor Performance: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/

Check my blog for more Interview Questions: http://sqlwithmanoj.wordpress.com/interview-questions/

Question { Accenture, 6194 }

What Is The Difference Between Primary Key & Super Key

Answer

Primary Key is a column which uniquely identifies all records in a table. For example EmployeeID column in an Employee table.

If you add another column to a PK, like (EmployeeID+FirstName) it still identifies identifies all rows uniquely, is known as Super Key.

For more interview Questions on SQL Server: http://sqlwithmanoj.wordpress.com/interview-questions/

Question { 4798 }

I have Two table First have UserName and second is address,
in address table more than value relegated to UserName
table, i want to fetch 2nd address if exist other wise 1st
address access

Answer

select user_name, coalesce(address1, address2) as address
from username u
left join address a
on u.userid = a.userid


For more Interview Questions check my blog: http://sqlwithmanoj.wordpress.com/interview-questions/

Question { ADP, 6208 }

Differencr Between DELETE and TRUNCATE?

Answer

DELETE vs TRUNCATE
Check: http://sqlwithmanoj.wordpress.com/2009/02/22/difference-between-truncate-delete-and-drop-commands/

~Manoj

Question { ADP, 6114 }

Explain SELF JOIN ?

Answer

SELF JOIN is like joining a table with itself, to get a recursive series of data within the table. Like in Employee table to get Manager details you need to join Manager ID with Employee ID in the same table, like:

SELECT
E.EmployeeID,
E.EmployeeName,
E.ManagerID,
M.EmployeeName as ManagerName
FROM Employee E
LEFT JOIN Employee M
ON M.EmployeeID = E.ManagerID

~Manoj(SQLwithManoj.wordpress.com)

Question { ADP, 7444 }

Define Foreign Key?

Answer

Foreign Key is used to establish Referential Integrity/Constraint between 2 tables. Mainly it is a column in a table that uniquely identifies every record in another table. A table can have 1 or more than 1 FKs.

~Manoj (sqlwithmanoj.wordpress.com)

Question { 5383 }

Can anyone explain difference between Database, Data warehouse
and Data mart with some example?````

Answer

1. Database: primarily is a collection of Tables that are normalized upto 3-NF and can be interrelated by FKs. It is an OLTP system which is meant for storing data of Information systems, like a CRM, ERP, Company/College/School record management system, and similar Data Entry other tools.

2. Data Warehouse: DW or EDW (Enterprise DW) is a system created by combining more than one Database systems, and/or by getting data from different Sources like heterogeneous Databases, flat-files, data feeds, etc. The tables in this system are de-normalized like in 1-NF, so that all related information is in a single table. Is is an OLAP system which is meant mainly for Reporting.

3. Data Mart: are built on top of a Data Warehouse, either by creating Views from underlying tables or a CUBE from fact & dimension tables.

~Manoj (http://sqlwithmanoj.wordpress.com)

Question { HCL, 4206 }

difference between function and procedure

Answer

- Stored Procedures can contain a single SQL statement or a group of SQL statements with data flow control logic containing IF-ELSE, WHILE loop constructs, TRY-CATCH, transactions, etc.
SPs are used to return one or many result-sets to its calling application.

- On the other hand Functions or UDFs can contain single or multiple SQL statements depending on its type. A Scalar UDF & Inline UDF can only have a single SELECT statement. And a Multi-Statement UDF can contain a body with multiple SQL statements including SELECTS, IF-ELSE, WHILE loops and DMLs but limited to manipulating table variables only.
UDFs return a single Scalar value or a Table variable to the calling SELECT statement.

Check all the difference here:
http://sqlwithmanoj.com/2011/09/21/stored-procedures-vs-functions-difference-between-sp-udf/