TechSapphire Tuesday, 2018-04-24, 7:57 AM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Sub Queries-


    Three basic rules only.

    Where you can place sub query?
    3 places where you can place your sub query:
    SELECT ----- FROM
    FROM   ----- WHERE
    WHERE  ----->

    SELECT ----- FROM
    _____________________
    (sub query can only return single value & no alias to value returned is required)

    Correct:
    _________

    (select max(val) from abc where ---) Ex output: 10
    (select top 1 val from abc where ----) Ex output: abctext

    Wrong:
    _________
    (select val from abc where ----) and result is returning multple rows:

    Ex. ouput of above query:
    val
    ___
    12
    32
    45
    6
    45

    Not Required:
    _____________

    (select max(val) as abccol from abc where -----)


    FROM   ----- WHERE
    _____________________

    (sub query can return multiple rows with multiple columns & alias to column and sub query it self in mandatory)

    Correct:
    ______________
    (select -------- From ( select abccol,abccol2,max(abc) as [Col Alias] from abc where ----- ) as table1 where ----------
    Ex. output:
    abccol1 abccol2 col Alias
    _______ _______ _________
    row 1    abc    223
    row 2    abc    12

     

    WHERE  ----->
    ________________
    (sub query can return single and multiple values depends upon operator used (Ex. when using IN operator multiple values are allowed) but sub query is nt allowed to 
    return multiple columns & no alias to value returned is required.)

    Correct:
    __________
    (select ------- From ------ Where col in (select abc from abc) and col2 = (select max(abc) from abc)

     

     

    Script:

    --Rule 1: SELECT ----- FROM
    --Task: Getting Last order placed by customer
    --Sample 1:
    select 
    (select top 1 orderid from orders where Orders.CustomerID=cust.CustomerID) [Last Order ID],* 
    from Customers cust

    --Explanation:
    select top 1 orderid from orders where Orders.CustomerID='ANTON'

    --Sample 2:
    select 
    (select SUM(Orders.Freight) [Sum of amount] from orders where Orders.CustomerID=cust.CustomerID) [Sum Amount],* 
    from Customers cust

    --Explanation:
    select SUM(Orders.Freight) from orders where Orders.CustomerID='ALFKI'

    --Wrong Queries:
    select 
    (select * from orders where Orders.CustomerID=cust.CustomerID) [Last Order ID],* 
    from Customers cust

    select 
    (select  Orders.Freight from orders where Orders.CustomerID=cust.CustomerID) [Last Order ID],* 
    from Customers cust


    --Rule 2: FROM   ----- WHERE

    select 'a' ,1 col2
    union all select 'b',2
    union all select 'c',3
    union all select 'd',4
    union all select 'e',5

    select * from 
    (select 'a' col1 ,1  col2
    union all select 'b',2
    union all select 'c',3
    union all select 'd',4
    union all select 'e',5) t1

    select * from Customers cust inner join 
    (select OrderID,OrderDate,CustomerID from [Orders]) t1 on t1.CustomerID=cust.CustomerID

     

    select t1.*,cust.* from Customers cust inner join 
    (select MAX(OrderID) orderid ,CustomerID from [Orders] group by customerid) t1 on t1.CustomerID=cust.CustomerID


    --Rule 3: WHERE  ----->
    Select * from orders ord where ord.CustomerID=
    (select top 1 CustomerID from Customers)

    Select * from orders ord where ord.CustomerID IN 
    (select top 5 CustomerID from Customers)


    --Wrong:
    Select * from orders ord where ord.CustomerID=
    (select top 1 customerid from Customers)

    Select * from orders ord where ord.CustomerID=
    (select  CustomerID from Customers)

     

     

    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