Could you please help me on this MySQL questions. Please send me results if poss
ID: 3701050 • Letter: C
Question
Could you please help me on this MySQL questions. Please send me results if possible. Appreciate your help
Using sub-queries as demonstrated in the textbook, complete the following tasks.
1. Use a sub-query to list the order number and order date for each order that was placed by Johnson’s Department Store and that contains an order line for a Gas Range. How many rows did your query return?
2. Rewrite step 1 above to list the order number and order date for each order that was placed by Johnson’s Department Store but that does not contain an order line for a Gas Range.
3. Use a sub-query to list the part number, part description, unit price, and item class for each part that has a unit price greater than the unit price of every part in item class AP. Use either the ALL or ANY operator in your query. HINT: Make sure you select the correct operator.
4. If you used ALL in step 3, repeat the exercise using ANY. If you used ANY, repeat the exercise using ALL, and then run the new command. What question does this command answer?
MySQL premiere Database file:
Explanation / Answer
/*1. Use a sub-query to list the order number and order date for each order that was placed by Johnson’s Department Store
and that contains an order line for a Gas Range. How many rows did your query return?*/
SELECT ORDER_NUM,ORDER_DATE FROM ORDERS WHERE CUSTOMER_NUM = (SELECT CUSTOMER_NUM FROM CUSTOMER WHERE CUSTOMER_NAME ='Johnson''s Department Store')
/*2. Rewrite step 1 above to list the order number and order date for each order that was placed by Johnson’s Department Store
but that does not contain an order line for a Gas Range.*/
SELECT ORDER_NUM,ORDER_DATE FROM ORDERS WHERE CUSTOMER_NUM =(SELECT CUSTOMER_NUM FROM CUSTOMER WHERE CUSTOMER_NAME ='Johnson''s Department Store')
AND ORDER_NUM NOT IN (SELECT ORDER_NUM FROM ORDER_LINE WHERE PART_NUM = (SELECT PART_NUM FROM PART WHERE DESCRIPTION = 'Gas Range'))
/*. Use a sub-query to list the part number, part description, unit price, and item class for
each part that has a unit price greater than the unit price of every part in item class AP.
Use either the ALL or ANY operator in your query. HINT: Make sure you select the correct operator.*/
SELECT PART_NUM,DESCRIPTION,PRICE,CLASS FROM PART WHERE PRICE > ANY ( SELECT PRICE FROM PART WHERE CLASS = 'AP')
/*4. If you used ALL in step 3, repeat the exercise using ANY. If you used ANY, repeat the exercise using ALL,
and then run the new command. What question does this command answer?*/
SELECT PART_NUM,DESCRIPTION,PRICE,CLASS FROM PART WHERE PRICE > ALL ( SELECT PRICE FROM PART WHERE CLASS = 'AP');