Question

CUSTOMER EMP INVOICE LINE P CODE V CODE CUS CODE EMP CODE NV NUMBER NV NUMBER, LINE NUMBER Create SQL queries for displaying the following results 5. List the customers who have ordered product bearing product code "23109-HB. Display first names, last names of customers, invoice numbers and product codes 6. Display Vendor details (V_CODE, V NAME) and product details (P CODE, P DESCRIPT) and product quantity on hand in excess of product min quantity (give alias of Quantity above Minimum) for products where Quantity above Minimum' is less than 10 items. Sort result in the ascending order of Quantity above Minimum . For each product invoiced on 01-16-2010 display the product details (P CODE, P DESCRIPT),invoice date and the total number of items sold (give suitable column title) along with the total amount of sales (give suitable column title and sort the results on this field) There should be one row displayed per Product 8. For each vendor based in Tennessee (TN) display the Vendor details (V CODE, V NAME, V STATE) and the total number of items sold (give suitable column title) along with the total amount of sales (give suitable column title and sort the results on this field). There should be one row displayed per Vendor 9 Who are the customers with no invoices placed. Display last name, first name, customer balance for these 10. Display first and last name(s) of customers who have bought the product with the highest price? in Submission Requirements You will upload word file to the Moodle by the due date and time that shows the screenshots of each query followed by the result. Above each query, please write your full name as a comment. Also, write your name, course number, and the semester name on the coversheet Make sure that you increase the size of the T-SQL results section at the bottom of Visual Studio's SQLQuery window to show all rows of your result set as some queries display several rows Solve the problems in the order in which they are listed in the assignment. Indicate the question number before each query TABLE P P DESCRIPT P.INDATE P.QOH P MIN P PRICE P DISCOUNT V CODE P MIN ORDER P REORDER QER/31 Power painter, 15ps3nozzle 2009-11-03 00:00 00 000 8 3-02/P2725n pwr saw blade 4013 9.00n pr saw biade 546-002 Hrd. coth, 1/44n 250 2009-12-13 00 0000 000 32 2005-11-13 000000 .000 18 2010-01-15 00 0000 .000 15 2010-01-15 000000 .000 23 009-12-30 00 0000 000 009-12-24 00 00 00 000 2010-01-20 00:00.00.000 125 2010-01-20 000000.000 2 2010-01-02 00 00 00.000 009-12-15 00 00 00 .000 43204 000 2010-02-07 00 00100 0001 2010-02-20 00 0000 000 188 75 58 2010-03-01 0000 00 000 17275699000 2010-02-24 00 0000 000 237 100 845 0999 0.00 1499 005 749 000 995 0.00 43.99 000 09 92 005 9987 005 895 005 10 995 010 1440 005 25595 25 134450 1344 50 3119 35 23119 25 24288 15 24288 15 25595 12 21225 25 NULL 12 21344 25 24288 10 NULL 50 21225 50 21231 100 25595 10 15 232/QTY BADw, 12in blade 3109-HB Clew hammer 3114AA Slede hanmer, 12 S-WREQ Hiout chain saw, 16 in M-18277125n metal sorew,25 25699 005 W 23116 25nd screw, 50 WR3/TT3 Steelmatting, 4xBx1/652010-01-17 00:00 00 00018 995 0.10

Answer 1

5. SELECT c.CUS_LNAME, c.CUS_FNAME, i.INV_NUMBER, l.P_CODE FROM CUSTOMER c, INVOICE i, LINE l WHERE c.CUS_CODE = i.CUS_CODE AND i.INV_NUMBER, l.INV_NUMBER AND i.P_CODE = '23109-HB';

6. SELECT v.V_CODE, v.V_NAME, p.P_CODE, p.P_DESCRIPT, (p.QOH-p.MIN) AS 'Quantity over Minimum' FROM PRODUCT p, VENDOR v WHERE p.V_CODE = v.V_CODE AND (p.QOH-p.MIN) < 10 ORDER BY (p.QOH-p.MIN);

7. SELECT v.V_CODE, v.V_NAME, v.V_STATE, i.INV_DATE, sum(l.LINE_UNITS) AS TotalItemsSold, sum(l.LINE_UNITS*l.LINE_PRICE) AS TotalSalesAmount FROM PRODUCT p, INVOICE i, LINE l WHERE p.P_CODE = l.P_CODE AND l.INV_NUMBER = i.INV_NUMBER AND i.INV_DATE = '01-16-2010 GROUP BY l.P_CODE ORDER BY TotalSalesAmount;

8. SELECT p.P_CODE, p.P_DESCRIPT, sum(l.LINE_UNITS) AS TotalItemsSold, sum(l.LINE_UNITS*l.LINE_PRICE) AS TotalSalesAmount FROM PRODUCT p, VENDOR v, INVOICE i, LINE l WHERE p.P_CODE = l.P_CODE AND l.INV_NUMBER = i.INV_NUMBER AND p.V_CODE = v.V_CODE AND v.V_STATE = 'TN' GROUP BY p.V_CODE ORDER BY TotalSalesAmount;

NOTE: As per Chegg policy, I am allowed to answer only 4 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.