SQL Examples – How to use SUB-QUERIES

SELECT V_CODE, V_NAME FROM VENDOR
WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT);
: LIST OF PRODUCTS WITH PRICE >= AVERAGE PRODUCT PRICE?
SELECT P_CODE, P_PRICE FROM PRODUCT
WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT);
: --- WHERE SUB-QUERIES
: LIST ALL CUSTOMERS WHO ORDERED THE PRODUCT "CLAW HAMMER"?
SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER JOIN INVOICE USING (CUS_CODE)
JOIN LINE USING (INV_NUMBER)
JOIN PRODUCT USING (P_CODE)
WHERE P_CODE = (SELECT P_CODE FROM PRODUCT WHERE P_DESCRIPT = 'Claw hammer');
: ALTERNATIVE SYNTAX
SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME 
FROM CUSTOMER 	JOIN INVOICE USING (CUS_CODE)
JOIN LINE USING (INV_NUMBER) 
JOIN PRODUCT USING (P_CODE)
WHERE P_DESCRIPT = 'Claw hammer';
: --- IN SUB-QUERIES
: LIST ALL CUSTOMERS THAT PURCHASED ANY TYPE OF HAMMER OR ANY KIND OF SAW OR SAW BLADE?
SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME 
FROM CUSTOMER 	JOIN INVOICE USING (CUS_CODE)
JOIN LINE USING (INV_NUMBER) 
JOIN PRODUCT USING (P_CODE)
WHERE P_CODE IN (SELECT P_CODE FROM PRODUCT
WHERE P_DESCRIPT LIKE '%hammer%' OR P_DESCRIPT LIKE '%saw%');
: --- HAVING SUB-QUERIES
: LIST ALL PRODUCTS WITH A TOTAL QTY SOLD GREATER THAN THE AVERAGE QTY SOLD?
SELECT P_CODE, SUM(LINE_UNITS)
FROM LINE
GROUP BY P_CODE
HAVING SUM(LINE_UNITS) > (SELECT AVG(LINE_UNITS) FROM LINE);
: --- ALL MULTI-ROW OPERAND SUB-QUERIES
: LIST ALL PRODUCTS WITH A PRODUCT COST GREATER THAN ALL INDIVIDUAL PRODUCT COSTS OF PRODUCTS PROVIDED BY VENDORS IN FLORIDA?
SELECT P_CODE, P_ONHAND*P_PRICE
FROM PRODUCT
WHERE P_ONHAND*P_PRICE > ALL 
(SELECT P_ONHAND*P_PRICE FROM PRODUCT
WHERE V_CODE IN (SELECT V_CODE FROM VENDOR WHERE V_STATE = 'FL'));
: --- FROM SUB-QUERIES
: LIST ALL CUSTOMER WHO PURCHASED PRODUCTS 13-Q2/P2 AND 23109-HB?
SELECT DISTINCT CUSTOMER.CUS_CODE, CUSTOMER.CUS_LNAME 
FROM CUSTOMER, 
(SELECT INVOICE.CUS_CODE 
FROM INVOICE NATURAL JOIN LINE WHERE P_CODE = '13-Q2/P2') CP1, (SELECT INVOICE.CUS_CODE 
FROM INVOICE NATURAL JOIN LINE WHERE P_CODE = '23109-HB') CP2
WHERE CUSTOMER.CUS_CODE = CP1.CUS_CODE AND
CP1.CUS_CODE = CP2.CUS_CODE;
: USING VIRTUAL TABLES IN FROM CLAUSE
CREATE VIEW CP1 AS 
SELECT INVOICE.CUS_CODE FROM INVOICE NATURAL JOIN LINE WHERE P_CODE = '13-Q2/P2';
CREATE VIEW CP2 AS
SELECT INVOICE.CUS_CODE FROM INVOICE NATURAL JOIN LINE WHERE P_CODE = '23109-HB';
SELECT DISTINCT CUS_CODE, CUS_LNAME 
FROM CUSTOMER NATURAL JOIN CP1 NATURAL JOIN CP2;
: --- ATTRIBUTE LIST SUB-QUERIES
: List the the difference between each product’s price and the average product price
SELECT P_CODE, P_PRICE, (SELECT AVG(P_PRICE) FROM PRODUCT) AS AVGPRICE,
P_PRICE-(SELECT AVG(P_PRICE) FROM PRODUCT) AS DIFF
FROM PRODUCT;
: List the product code, total sales by product, and contribution by employee of each product sales?
SELECT P_CODE, SUM(LINE_UNITS*LINE_PRICE) AS SALES, 
(SELECT COUNT(*) FROM EMPLOYEE) AS ECOUNT, 
SUM(LINE_UNITS*LINE_PRICE)/(SELECT COUNT(*) FROM EMPLOYEE) AS CONTRIB
FROM LINE 
GROUP BY P_CODE;
OR
SELECT P_CODE, SALES, ECOUNT, SALES/ECOUNT AS CONTRIB
FROM (SELECT P_CODE, SUM(LINE_UNITS*LINE_PRICE) AS SALES,
(SELECT COUNT(*) FROM EMPLOYEE) AS ECOUNT
FROM LINE GROUP BY P_CODE);

Leave a Reply

Your email address will not be published. Required fields are marked *