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.
Script Used is mentioned below:
-- Procedure to get updated records back after getting updated
-- Incrementing Unit price by 1 of all product having category ID = 2
Begin
SELECT productid,productname,unitprice INTO #deleted from products where categoryid=2
UPDATE products set unitprice=unitprice+1 where categoryid=2
select productid,unitprice INTO #inserted from products where productid in
(select productid from #deleted)
select del.productid,del.productname,del.unitprice [Old price], ins.unitprice [New price]
from #deleted del inner join #inserted ins on del.productid=ins.productid
drop table #deleted,#inserted
END
update products set unitprice=unitprice-1 where categoryid=2
-- Using OUTPUT Clause
update products set unitprice=unitprice+1
OUTPUT inserted.productid, inserted.productname,deleted.unitprice [Old Price],inserted.unitprice [New price]
where categoryid=2
-- Complex Query
-- Paging
-- Getting ROW NUMBER
SELECT row_number() over(order by productid) [Sr No.], * from products
-- Select record with odd number
SELECT * from
(
SELECT row_number() over(order by productid) [SrNo], * from products
) t1 where t1.[SrNo] % 2=1
-- Select record with even number
SELECT * from
(
SELECT row_number() over(order by productid) [SrNo], * from products
) t1 where t1.[SrNo] % 2=0
-- Paging Start
DECLARE @PageSize int
DECLARE @PageNumber int
DECLARE @StartRecord int
DECLARE @EndRecord int
Set @PageSize=5
set @PageNumber=2
set @StartRecord=@PageNumber * @PageSize - @PageSize + 1
Set @EndRecord=@PageNumber * @PageSize
SELECT * fROM
(
SELECT row_number() over(order by productid) [SrNo], * from products
) t1
where t1.[SrNo] BETWEEN @StartRecord and @EndRecord
-- Producting Serial Number with respect to there category
select categoryname,productname,unitprice from products prod inner join categories cat
on cat.categoryid=prod.categoryid
select categoryname,row_number() over (partition by categoryname order by productname) [SRNo],
productname,unitprice from products prod inner join categories cat
on cat.categoryid=prod.categoryid
-- Product Ranking
select prod.productname,sum(quantity) [Total Sold] from [order details] od
inner join products prod on
prod.productid=od.productid group by prod.productname
-- Ranking
select prod.productname, RANK() over(order by sum(quantity) desc) [Rank],
sum(quantity) [Total Sold] from [order details] od
inner join products prod on
prod.productid=od.productid group by prod.productname order by sum(quantity) desc
-- Using Dense_Rank
select prod.productname, DENSE_RANK() over(order by sum(quantity) desc) [Rank],
sum(quantity) [Total Sold] from [order details] od
inner join products prod on
prod.productid=od.productid group by prod.productname order by sum(quantity) desc
-- Customer Sales Example
select cust.companyname,cust.customerid,sum(freight) [Amount],
(case when sum(freight)>=1000 then 'Top List'
when sum(freight)>500 and sum(freight)<1000 then 'Mid List'
else 'Low List' end) 'Type'
from orders ord
inner join customers cust on
cust.customerid=ord.customerid group by cust.companyname,cust.customerid
order by cust.companyname
--using partition by
select cust.companyname,cust.customerid,
DENSE_RANK() OVER(
partition by (case when sum(freight)>=1000 then 'Top List'
when sum(freight)>500 and sum(freight)<1000 then 'Mid List'
else 'Low List' end)
order by sum(freight) desc) [Rank],
sum(freight) [Amount],
(case when sum(freight)>=1000 then 'Top List'
when sum(freight)>500 and sum(freight)<1000 then 'Mid List'
else 'Low List' end) 'Type'
from orders ord
inner join customers cust on
cust.customerid=ord.customerid group by cust.companyname,cust.customerid
order by sum(freight) desc
-- simplifying same logic
with [customer Rank](companyname,Amount,[Type])
as
(
select cust.companyname,sum(freight) [Amount],
(case when sum(freight)>=1000 then 'Top List'
when sum(freight)>500 and sum(freight)<1000 then 'Mid List'
else 'Low List' end) 'Type'
from orders ord
inner join customers cust on
cust.customerid=ord.customerid group by cust.companyname,cust.customerid
)
select companyname,DENSE_RANK()
OVER (partition by type order by amount desc) [Rank],Amount,[Type]
from [customer Rank] order by [type]
--Select customer with last order details
select cust.companyname,orderid from orders ord inner join customers cust on
cust.customerid=ord.customerid
select cust.companyname,max(orderid) from orders ord inner join customers cust on
cust.customerid=ord.customerid group by cust.companyname
-- If want to select max freight
-- you can't use max(Freight) here with this
-- also want to select ship name
select cust.companyname,
(select top 1 orderid from orders o where
o.customerid=cust.customerid order by orderid desc) [Last order],
(select top 1 freight from orders o where
o.customerid=cust.customerid order by orderid desc) [Last Freight],
(select top 1 shipname from orders o where
o.customerid=cust.customerid order by orderid desc) [Last shipname]
from customers cust order by cust.companyname
-- What is more optimized
select cust.companyname,t1.orderid,t1.shipname,t1.freight from
(select o1.customerid,orderid,shipname,freight from orders o1 where orderid=
(select max(orderid) from orders o where o.customerid=o1.customerid)) t1 inner join customers cust on
t1.customerid=cust.customerid
order by cust.companyname
select cust.companyname,t1.orderid,t1.shipname,t1.freight from
(select o1.customerid,orderid,shipname,freight from orders o1 where orderid=
(select top 1 orderid from orders o where o.customerid=o1.customerid order by orderid desc)) t1 inner join customers cust on
t1.customerid=cust.customerid
order by cust.companyname
-- Most common queries asked in Interviews
--Duplicate Records
select * from Region
select regiondescription from region r1 where r1.regionid<
(select max(regionid) from region r2 where r1.regiondescription=r2.regiondescription)
--Nth Highest Salary
-- syntax
--SELECT *
--FROM Employee Emp1
--WHERE (N-1) = (
--SELECT COUNT(DISTINCT(Emp2.Salary))
--FROM Employee Emp2
--WHERE Emp2.Salary > Emp1.Salary)
use emp
select * from tbemp t1 where 1=
(select count(*) from tbemp t2 where t2.empsal>t1.empsal)
use northwind
-- Using Case
select companyname,country from customers
select companyname,
(case
when country='USA' then 'Main Office'
when country='UK' then 'Main Office'
else country
end) [Office] from customers
--Creating table from existing table without copying data
select * into #customer from customers
select * from #customer
drop table #customer
select * into #customer from customers where 1=2
select * from #customer
drop table #customer
