|
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
|
|
|