TechSapphire Monday, 2018-02-19, 9:09 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • 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
    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018