DAY 5 kejian

Sub Query
Independent Sub Query
Correlated Query
Exists and Not Exists
A sub-query is a query within a query.

 A sub-query answers the queries that have multiple parts

 The sub-query answers one part of the question

The sub query is enclosed in parentheses

List the name of item whose unit price is greater than the unit price of item id ‘BAK101’
SELECT itemname  FROM item
 WHERE unitprice
  >
 (SELECT unitprice FROM item WHERE itemid=‘BAK101’ );

 

List the details of the items whose unit price is maximum.
item
itemid  itemname unitprice  class
SELECT itemid, itemname, unitprice
  FROM item
        WHERE unitprice 
   =
 (SELECT MAX(unitprice) FROM item);    //Get the MAXimum Unit Price available in the item table

List the second highest Unit price from the item table
SELECT MAX(unitprice)
  FROM item
        WHERE unitprice 
   !=  //Remove the highest Unit Price
 (SELECT MAX(unitprice) FROM item); //Get the highest Unit Price

List the details of the item whose unit price is more than the average Unit price of items.
SELECT itemid,itemname,unitprice
  FROM item
        WHERE unitprice 
   >
 (SELECT AVG(unitprice) FROM item);   //Get the average Unit Price

List the customerid and SUM of total purchase amount  for those customers whose SUM of  total purchase amount is

more than the average  of the SUM of total purchase amount  of all the customers.
customerpurchase
customerid   itemid   qtypurchased   billid   netprice
SELECT customerid,SUM(netprice)
  FROM customerpurchase
              GROUP BY customerid
        HAVING SUM(netprice) 
   >
 (SELECT AVG(SUM(netprice)) FROM customerpurchase GROUP BY customerid);  //Get the average of

SUM of Net price  of all the purchases

 

customerid  itemid  qtypurchased  billid  netprice
C1 STN001 5 1001 150
C2 GRO001 1 1002 10
C1 ELC001 1 1001 5000
C2 STN002 2 1002 400
C3 STN002 2 1003 400
After grouping  by customer id and finding SUM of net Price for each group
customerid  SUM(netprice)
C1 5150
C2 410
C3 400
After finding average of SUM of net price for each group
AVG(SUM(netprice))
1986.666667

List the details of the customer(s) who have paid a bill of maximum amount
customer
customerid  customername  dateofreg  userid  password

SELECT customerid, customername FROM customer
              WHERE customerid
   IN
   (SELECT customerid FROM customerpurchase
       GROUP BY billid, customerid HAVING SUM(netprice)   //Get the customer id(s) who have paid the maximum bill. Asuming one bill will be paid by only one customer
   =
(SELECT MAX(SUM(netprice)) FROM customerpurchase GROUP BY billid));  //Get the maximum  of SUM of Net price  of all the bill ids

customerpurchase
customerid  itemid  qtypurchased  billid  netprice
C1 STN001 5 1001 150
C2 GRO001 1 1002 10
C1 ELC001 1 1001 5000
C2 STN002 2 1002 400
C3 STN002 2 1003 400

After grouping  by billidand finding SUM of net Price for each group
billid  SUM(netprice)
1001  5150
1002  410
1003  400
After finding maximum of SUM of net price for each group
MAX(SUM(netprice))
5150
You can refer to the table in the FROM clause of the outer query in the inner query using Correlated sub-queries.

The inner query is executed separately for each row of the outer query.
 (i.e. In Co-Related Sub-queries, SQL performs a sub-query over and over again – once for each row of the main query. )