ORDERS CUSTOMERS Order Orderdate stname Shipdate FirstnameShipstreet Shipzip Zip
ID: 3701196 • Letter: O
Question
ORDERS CUSTOMERS Order Orderdate stname Shipdate FirstnameShipstreet Shipzip Zip Referred Region 800 ODKAUTHOR AUTHORS AuthoriD ORDERITEMS Pubdate PubID Cost Retal Discount Order ISBN Quandty DetountPUBUSHERS Paideach Name PROMOTION Contact Phone Gift Minretall Maxretal Problem: Produce a list of all customers who live in the state of Florida or Texas and have ordered computer books that has an ISBN starting with '83'. Display only those orders that have been shipped. Sort the results in descending order of custome r# LASTNAME ST ORDERD SHIPDATE ISBN CATEGORY 1007 GIANA TX 1001 MORALES FL 1007 84-APR-15 8843172113 COMPUTER 1003 88-MAR-15 8843172113 COMPUTER FOUR SQL STATEMENTS ARE REQUIRED by using the (1) implicit join. 12) explicitExplanation / Answer
Answer.)
1. IMPLICIT JOIN
SELECT CUST.LASTNAME,CUT.FIRSTNAME,CUST.EMAIL,CUST.ADDRESS,CUST.CITY,CUST.STATE,CUST.ZIP,CUST.REFERRED,CUST.REGION
FROM CUSTOMER CUST,BOOKS BOOK, ORDERS ORDER, ORDERITEMS ORDERIT
WHERE CUST.CUSTOMER#=ORDER.CUSTOMER#
AND ORDER.ORDER#=ORDERIT.ORDER#
AND ORDERIT.ISBN=BOOK.ISBN
AND CUST.STATE IN ('FLORIDA','TEXAS')
AND BOOK.CATEGORY='COMPUTER BOOKS'
AND BOOK.IBN LIKE '88%'
AND ORDER.SHIPDATE>CURRENT DATE
ORDER BY CUSTOMER# DESC;
2.NATURAL JOIN
SELECT CUST.LASTNAME,CUT.FIRSTNAME,CUST.EMAIL,CUST.ADDRESS,CUST.CITY,CUST.STATE,CUST.ZIP,CUST.REFERRED,CUST.REGION
FROM CUSTOMER CUST NATURAL JOIN ORDERS ORDER NATURAL JOIN ORDERITEMS ORDERIT NATURAL JOIN BOOKS BOOK
WHERE CUST.STATE IN ('FLORIDA','TEXAS')
AND BOOK.CATEGORY='COMPUTER BOOKS'
AND BOOK.IBN LIKE '88%'
AND ORDER.SHIPDATE>CURRENT DATE
ORDER BY CUSTOMER# DESC;
3.JOIN-USING
SELECT CUST.LASTNAME,CUT.FIRSTNAME,CUST.EMAIL,CUST.ADDRESS,CUST.CITY,CUST.STATE,CUST.ZIP,CUST.REFERRED,CUST.REGION
FROM CUSTOMER CUST INNER JOIN ORDERS ORDER USING (CUSTOMER#)
INNER JOIN ORDERITEMS ORDERIT USING (ORDER#)
INNER JOIN BOOKS BOOK USING (ISVN)
WHERE CUST.STATE IN ('FLORIDA','TEXAS')
AND BOOK.CATEGORY='COMPUTER BOOKS'
AND BOOK.IBN LIKE '88%'
AND ORDER.SHIPDATE>CURRENT DATE
ORDER BY CUSTOMER# DESC;
4.JOIN-ON
SELECT CUST.LASTNAME,CUT.FIRSTNAME,CUST.EMAIL,CUST.ADDRESS,CUST.CITY,CUST.STATE,CUST.ZIP,CUST.REFERRED,CUST.REGION
FROM CUSTOMER CUST INNER JOIN ORDERS ORDER
ON CUST.CUSTOMER#=ORDER.CUSTOMER#
INNER JOIN ORDERITEMS ORDERIT
ON ORDER.ORDER#=ORDERIT.ORDER#
INNER JOIN BOOKS BOOK
ON ORDERIT.ISBN=BOOK.ISBN
WHERE CUST.STATE IN ('FLORIDA','TEXAS')
AND BOOK.CATEGORY='COMPUTER BOOKS'
AND BOOK.IBN LIKE '88%'
AND ORDER.SHIPDATE>CURRENT DATE
ORDER BY CUSTOMER# DESC;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.