Chapter 7 Subqueries

Chapter 7 Subqueries - Chapter7Subqueries List all products...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Chapter 7 Subqueries List all products with > average price SELECT P_CODE, P_PRICE FROM PRODUCT WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT); Subqueries with joins List all customers who ordered a Claw hammer SELECT DISTINCT C.CUS_CODE, CUS_LNAME, CUS_FNAME FROM CUSTOMER C JOIN INVOICE I ON C.CUS_CODE = I.CUS_CODE JOIN LINE L ON I.INV_NUMBER = L.INV_NUMBER JOIN PRODUCT P ON L.P_CODE = P.P_CODE WHERE P.P_CODE = (SELECT P_CODE FROM PRODUCT WHERE P_DESCRIPT = 'Claw hammer'); List customers who ordered any hammer or saw SELECT DISTINCT C.CUS_CODE, CUS_LNAME, CUS_FNAME FROM CUSTOMER C JOIN INVOICE I on C.CUS_CODE = I.CUS_CODE JOIN LINE L ON I.INV_NUMBER = L.INV_NUMBER JOIN PRODUCT P ON L.P_CODE = P.P_CODE WHERE P.P_CODE IN (SELECT P_CODE FROM PRODUCT WHERE P_DESCRIPT LIKE '%hammer%' OR P_DESCRIPT LIKE '%saw%'); Having subqueries Products with total quantity sold greater than average quantity sold SELECT P_CODE, SUM(LINE_UNITS) FROM LINE GROUP BY P_CODE HAVING SUM(LINE_UNITS) > (SELECT AVG(LINE_UNITS) FROM LINE);
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 10/25/2010 for the course CMPN SEM4 taught by Professor Thakur during the Spring '07 term at University of Mumbai.

Page1 / 3

Chapter 7 Subqueries - Chapter7Subqueries List all products...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online