Question

All the required files are attached to this question here in the form of images. Please refer the attached images and answer the questions.

1. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor AA has supplied xxx products, and vendor BB has supplied yyy products etc.
2. Show the total value of invoices for each customer with the customer the Last Name, and a column heading showing ‘Total_invoice_value’ in the results.
3. List the names of the customers and how many times a customer generated an invoice: make sure the new column heading reads “Num_Of_Invoices”.
4. Through a calculation, show the Total unit price (aliased column) for the units and line price.
5. Show the details of the invoice that has the minimum subtotal.
6. List all the purchases made by customer Orlando. (use a natural join).
7. Add a new vendor row/record in the vendor table and use your teacher’s name and your choice of other details. (1m)
8. Add a new row/record to the `customer` table to include your actual student_ID in the CUS_CODE, your last_name as the CUS_LNAME` and your first name as CUS_FNAME. Add any other details of your choice in all the other fields.
9. Add a new attribute (field) ‘Customer_rating’ with data type and length ‘VARCHAR(14) to the vendor table.
10. Update ‘Customer_rating’ for the Customer that has ‘your student ID and name’ from point 2, to show ‘GREAT’

156 157 158 select from EMP: 159 160 Interactive modeD OFF Stdin Inputs... Focus View: OD Version SQLite 3.23.1 O Execute SaveMy Projects Recent Collaborate FAQ More Options Result... CPU Time: 8.88 sec(s), Memory: 4824 kilobyte(s) EMP NUM EMPTITLE EMP_LNAME EMP FNAME EMP_INITIAL EMP DOB EMP HIRE DATE EMP AREACODE EMP PHONE EMP MGR 100 George Rhonda 6/15/1942 3/15/1985 324-5456 Lewis 101 3/19/1965 4/25/1986 324-4472 100 102 VanDam Rhett 11/14/1958 12/20/1990 675-8993 103 ones Anne 10/16/1974 8/28/1994 898-3456 100 104 John 11/8/1971 10/20/1994 504-4430 Williams Smith 105 Robert Jeanine 3/14/1975 11/8/1998 890-3220 106 2/12/1968 1/5/1989 324-7883 105 107 Diante 8/21/1974 7/2/1994 890-4567 105 Jorge Wiesenbach Paul 108 2/14/1966 11/18/1992 897-4358 Smith 109 George Leighla Washington Rupert 6/18/1961 4/14/1989 504-3339 108 Genkazi 110 5/19/1970 12/1/1990 569-0093 1/3/1966 6/21/1993 890-4925 105 112 Johnson Edward 5/14/1961 12/1/1983 898-4387 100 Melanie 113 9/15/1970 5/11/1999 324-9006 105 114 Brandon 11/2/1956 11/15/1979 882-0845 Hermine 115 Saranda 7/25/1972 4/23/1993 324-5505 105 Smith George 11/8/1965 12/10/1988 890-2984 157 158 select from invoice 159 160 Stdin Inputs... Fo O Execute SaveMy Projects Recent Result... CPU Time: 9.01 sec(s), 3929 kilobyte(s) Memory: INV NUMBER CUS_CODE INV DATE INV _SUBTOTAL INV DATE INV SUBTOTAL INV TAX INV TOTAL 1001 10014 1/16/2008 24.8999996185303 1.99000000953674 26.8899993896484 1002 10011 1/16/2008 9.97999954223633 0.80000001192092 10.7799997329712 1003 10012 1/16/2008 153.850006103516 12.3100004196167 166.160003662109 1004 10011 1/17/2008 34.9700012207031 2.79999995231628 37.7700004577637 1005 10018 1/17/2008 70.4400024414062 5.6399998664856 76.0800018310547 10014 1006 1/17/2008 397.82998657 2266 31.8299999237061 429.660003662109 1007 10015 1/17/2008 34.9700012207031 2.79999995231628 37.7700004577637 1008 10011 1/17/2008 399.149993896484 31.9300003051758 431.079986572266 156 157 158 select*from line; 159 160 Stdin Inputs... O Execute SaveMy Projects R Result... CPU Time: .8e sec(s), Memory: 4832 kilobyte(s) INV NUMBER LINE NUMBER P CODE LINE UNITS LINE PRICE LINE_TOTAL 1001 13-Q2/P2 1.0 23109-HB 1.0 14.9899997711182 14.9899997711182 1001 9.94999980926514 9.94999980926514 1002 54778-2T 2238/QPD 2.0 4.98999977111816 9.97999954223633 1.0 1003 38.9500007629395 38.9500007629395 1003 1546-QQ2 1.0 13-Q2/P2 5.0 54778-2T 39.9500007629395 39.9500007629395 1003 14.9899997711182 74.9499969482422 1004 3.0 4.98999977111816 14.9700002670288 1004 23109-HB 2.0 9.94999980926514 19.8999996185303 1005 PVC23DRT 12.0 5.86999988555908 70.4400024414062 1006 SM-18277 3.0 6.98999977111816 20.9699993133545 1006 2232/QTY 1.0 23109-HB 1.0 109.919998168945 109.919998168945 1006 9.94999980926514 9.94999980926514 89-WRE-Q 1.0 13-Q2/P2 2.0 54778-2T 1006 4 256.989990234375 256.989990234375 1007 14.9899997711182 29.9799995422363 1007 1.0 4.98999977111816 4.98999977111816 1008 PVC23DRT 5.0 5.86999988555908 29.3500003814697 1008 WR3/TT3 3.0 119.949996948242 359.850006103516 1.0 1008 23109-HB 9.94999980926514 9.94999980926514 156 157 158 select from Product; 159 160 Interactive modeD OFF Stdin Inputs... Focus View: OFF Version SQLite 3.23.1 O Execute SaveMy Projects Recent Collaborate FAQ More Options Result... CPU Time: θ.ee sec(s), 3992 kilobyte(s) Memory: PCODE P INDATE P_00H PDESCRİPT P MIN PPRICE P DISCOUNT V CODE 11QER/31 Power painter, 15 psi., 3-nozzle 11/3/2007 8 13-Q2/P2 7.25-in. pwr. saw blade 14-Q1/L3 9.0e-in. pwr. saw blade 1546-Q02 Hrd. cloth, 1/4-in., 2x50 1558-QW1 Hrd. cloth, 1/2-in., 3x50 2232/QTY B&D jigsaw, 12-in. blade 2232/QWE B&D jigsaw, 8-in. blade 2238/QPD B&D cordless drill, 1/2-in 109.98999786377 0.0 25595 12/13/2007 32 15 14.989999771118 0.05 21344 11/13/2007 18 12 17.489999771118 0.0 21344 1/15/2008 15 39.950000762939 0.0 23119 1/15/2008 23 43.990001678466 0.0 23119 12/30/2007 8 109.91999816894 0.05 24288 12/24/2007 6 99.870002746582 0.05 24288 1/20/2008 12 38.950000762939 0.05 25595 23109-HB Claw hammer 1/20/2008 23 10 9.9499998092651 0.1 21225 1/2/2008 8 23114-AA Sledge hammer, 12 lb 14.399999618530 0.05 54778-2T Rat-tail file, 1/8-in. fine 89-WRE-QHicut chain saw, 16 in PVC23DRT PVC pipe, 3.5-in., 8-ft SM-18277 1.25-in. metal screw, 25 SW-23116 2.5-in. wd. screw, 50 WR3/TT 12/15/2007 43 20 4.9899997711181 0.0 21344 2/7/2008 11 256.98999023437 0.05 24288 2/20/2008 188 3/1/2008 172 75 5.8699998855590 0.0 75 6.9899997711181 0.0 21225 2/24/2908 237 100 8.4499998092651 0.0 21231 Steel matting, 4'x8'x1/6", .5" m 1/17/2008 18 119.94999694824 0.1 25595 157 158 select from VENDOR; Stdin Inputs... Execute Save My Projects Result... CPU Time: θ.ee sec(s), 4836 kilobyte(s) Memory: V CODE V NAME V CONTACT V AREACODE V PHONE V STATE V_ORDER Bryson, Inc. Smithson 615 SuperLoo, In Flushing 904 D&E Supply Singh Gomez Bros. TN FL 21225 223-3234 21226 215-8995 21231 5 615 228-3245 TN 6 21344 Ortega 615 889-2546 KY Dome Supply Smith 22567 2 901 678-1419 23119 Randsets Ltd Anderson 901 678-3998 Brackman Bro Browning 615 ORDVA, Inc Hakford B&K, Inc. Damal Suppli Smythe Rubicon Syst Orton 24004 228-1410 TN 24288 615 898-1234 TN Smith 25443 227-0093 5 25501 615 890-3529 TN 25595 456-0092 FL 157 158 select from customer; 159 160 Stdin Inputs... O Execute Save My Projects Recent Result... CPU Time: 8.81 sec(s), Memory: 3984 kilobyte(s) CODE CUS LNAME CUS FNAME CUS INITIAL CUS AREACODE CUS PHONE CUS BALANCE 2 10010 Ramas Alfred 615 844-2573 0.0 2 10011 Dunne Leona 713 894-1238 0.0 Smith Kathy 345.8599853 10012 615 894-2285 Olowski 10013 Paul 615 894-2180 536.75 10014 Orlando 615 222-1672 0.0 0'Brian 10015 713 442-3381 0.0 221.1900024 10016 615 297-1228 James Williams 768.9299926 10017 George 615 290-2556 10018 Farriss 216.5500030 Anne 382-7185 Smith 10019 Olette 615 297-3809 0.0

Answer 1

1. 1.
2. Select Vendor.V_Code, V_Name, count(P_Code) from Vendor inner join Product on Vendor.V_Code = Product.V_Code group by Vendor.V_Code, V_Name;
3. 2.
4. Select Cus_LName sum(Inv_Total) as 'Total_invoice_value' from Customer inner join Invoice on Customer.Cus_Code = Invoice.Cus_Code group by Cus_LName;
5. 3.
6. Select Cus_FName, Cus_LName count(Invoice.Cus_Code) as 'Num_Of_Invoices' from Customer inner join Invoice on Customer.Cus_Code = Invoice.Cus_Code group by Cus_FName, Cus_LName;
7. 4.
8. Select sum(P_Price + Line_Price) as 'Total_unit_price' from Product inner join Line on Product.P_Code = Line.P_Code ;
9. 5.
10. Select * from Invoice where where Inv_Total = (Select min(Inv_Total) from Invoice);
11. 6.
12. Select Invoice.* from Customer natural join Invoice where Cus_FName = 'Orlando';
13. 7.
14. Insert into Vendor values(26690, 'Teacher' , 'Smith',615,'654-8976','TN','Y' );
15. 8.
16. Insert into Customer values(10020,'Last_Name','First_name','S','713-8766',888.99);
17. 9.
18. Alter Table Vendor Add Customer_rating VARCHAR(14);
19. 10.
20. Alter Table Vendor Alter Customer_rating = 'GREAT' where Cus_Code = 'StudentID' and Cust_FName = 'StudentFirstName' and Cust_LName = 'StudentLastName';

Do ask if any doubt. Please upvote.