Ask a question from expert

Ask now

Structured Query Language (SQL) Assignment

4 Pages451 Words190 Views
   

Added on  2020-03-23

Structured Query Language (SQL) Assignment

   Added on 2020-03-23

BookmarkShareRelated Documents
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:
Structured Query Language (SQL) Assignment_1
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);
Structured Query Language (SQL) Assignment_2

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 Invoice
|6
|479
|147

Show sums of line totals for each invoice.. Query:.
|6
|460
|212

Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertation etc
|6
|703
|93

Database Assignment Module
|13
|375
|111

Database Module Assignment
|13
|366
|40

Database Module | Assignment
|13
|332
|92