This lecture is on complex query SQL server, topic covered are:
• OUTPUT Clause
• RANK(), ROW_NUMBER() and DENSE_RANK() Function
• Query Optimization and Use of execution plan.



Click Here to see full article and download sql script

Read complete article



----Basic but important functions
--ROW_NUMBER()
--RANK()
--DENSE_RANK()
--NTILE()

--ROW_NUMBER() --Returns a sequential row number with in partition of result set
use Northwind;
SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products;

----Getting alternate rows
------Odd Row
select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]%2=1;
------Even Row
select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]%2=0;
------Row Between
select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]
Between 5 and ... Read more »

Union is to select all records from two table but common record only once.

Select * from tbemp1
UNION
Select * from tbemp2


Inersect is to select common records from two tables.

Select * from tbemp1
INTERSECT
Select * from tbemp2

Except(Minus) is equivalent to oracle minus clause. Select records of table 1 which are not in table 2.

Select * from tbemp1
EXCEPT
Select * from tbemp2

Get duplicate records from table
Select ename from tbemp a where a.eno!=(select Max(eno) from tbemp b where a.ename=b.ename)

Here eno denotes to employee number and ename denotes to employee name.

Without Data
Select * INTO tbem1 from tbemp where 1=2

Why 1=2?
Because none of the record could satisfy this condition i.e. 1=2. So no data would be copied into newly created table.

With Data
Select * INTO tbemp2 from tbemp

With Condition
Select * INTO tbemp3 from tbemp where depcod=102

Here depcod denotes to department code.