1)Show sums of line totals for each invoice.Query: SELECT INV_NUMBER, SUM(LINE_TOTAL) AS SUM_OF_LINE_TOTAL FROM LINE GROUP BY INV_NUMBER;Output:2) Locate the record in the vendor table that does not have a value for the attribute V_STATEQuery: SELECT * FROM VENDOR WHERE V_STATE IS NULL;Output:3) Find the customers whose balance is greater than 200 but less than 300.Query: SELECT CUS_CODE, CUS_INITIAL, CUS_FNAME, CUS_LNAME FROM CUSTOMER WHERE CUS_BALANCE>200 AND CUS_BALANCE<300;Output:4)Using natural join and having, list the customers whose invoice total is greater than 200Query: SELECT CUS_CODE, CUS_INITIAL, CUS_FNAME, CUS_LNAME FROM CUSTOMER NATURAL JOIN INVOICE GROUP BY CUS_CODE HAVING INV_TOTAL>200;Output:
5) Show the names of the customers for whom we have invoices.Query: SELECT CUS_CODE, CUS_INITIAL, CUS_FNAME, CUS_LNAME FROM CUSTOMER WHERE CUS_CODEIN (SELECT CUS_CODE FROM INVOICE);Output:6)Using natural join and having, list the customers whose invoice total is greater than the average Query: SELECT CUS_CODE, CUS_INITIAL, CUS_FNAME, CUS_LNAME FROM CUSTOMER NATURAL JOIN INVOICE GROUP BY CUS_CODE HAVING INV_TOTAL> (SELECT AVG(INV_TOTAL) FROM INVOICE);Output:7)Show the names of the customers for whom we DO NOT have invoices.Query:SELECT CUS_CODE, CUS_INITIAL, CUS_FNAME, CUS_LNAME FROM CUSTOMER WHERE CUS_CODENOT IN (SELECT CUS_CODE FROM INVOICE);Output:8)Using natural join and having, list the customers whose invoice total is the maximum. Query: SELECT CUS_CODE, CUS_INITIAL, CUS_FNAME, CUS_LNAME FROM CUSTOMER NATURAL JOIN INVOICE GROUP BY CUS_CODE HAVING INV_TOTAL=(SELECT MAX(INV_TOTAL) FROM INVOICE);
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Show Sums of Line Totals for Each Invoicelg...
|6
|479
|147
Show sums of line totals for each invoice.. Query:.lg...
|6
|460
|212
Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertation etclg...