logo

SQL JOINS - Exercises, Practice, Solution Assignment

4 Pages638 Words248 Views
   

Added on  2020-04-01

SQL JOINS - Exercises, Practice, Solution Assignment

   Added on 2020-04-01

ShareRelated Documents
Assessment item 51. Show sums of line totals for each invoiceSELECT IVC.INV_NUMBER AS "Invoice Number", IVC.INV_DATE AS "Invoice Date", SUM (L.LINE_TOTAL) AS "Total of each invoice" FROM INVOICE IVC, LINE L WHERE IVC.INV_NUMBER=L.INV_NUMBER GROUP BY IVC.INV_NUMBER;2. Locate the record in the vendor table that does not have a value for the attribute V_STATESELECT V_CODE AS "Vendor ID", V_NAME AS "Name of Vendor", V_CONTACT AS "Contact Person Name", V_AREACODE AS "Area Code",V_PHONE AS "Phone Number" FROM VENDOR WHERE V_STATE IS NULL;3. Find the customers whose balance is greater than 200 but less than 300SELECT CUS_CODE AS "Customer Code", CUS_LNAME AS "Last Name", CUS_FNAME AS "First Name", CUS_BALANCE "Balance between 201 and 300" FROM CUSTOMER WHERE CUS_BALANCE BETWEEN 201 AND 300;
SQL JOINS - Exercises, Practice, Solution Assignment_1
4. Using natural join and having, list the customers whose invoice total is greater than 200SELECT CUS_CODE AS "Customer Code", CUS_LNAME AS "Last Name", CUS_FNAME AS "First Name", INV_TOTAL AS "Total greater than 200" FROM CUSTOMER NATURAL JOIN INVOICE WHERE INV_TOTAL>200;5. Show the names of the customers for whom we have invoicesSELECT DISTINCT CUS_LNAME AS "Last Name", CUS_FNAME AS "First Name" FROM CUSTOMER NATURAL JOIN INVOICE;6. Using natural join and having, list the customers whose invoice total is greater than the averageSELECT CUS_LNAME AS "Last Name", CUS_FNAME AS "First Name", AVG(INV_TOTAL) AS "Average Total", INV_TOTAL AS "InvoiceTotal" FROM INVOICE NATURAL JOIN CUSTOMER GROUP BY CUS_CODE HAVING AVG(INV_TOTAL)<INV_TOTAL;7. Show the names of the customers for whom we DO NOT have invoicesSELECT CUS_LNAME || ', ' || CUS_FNAME || ' ' || CUS_INITIAL AS "No Invoice Customer" FROM CUSTOMER WHERE CUS_CODE NOT IN (SELECT CUS_CODE FROM INVOICE);
SQL JOINS - Exercises, Practice, Solution Assignment_2

End of preview

Want to access all the pages? Upload your documents or become a member.

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

Show Sums of Line Totals for Each Invoice
|6
|479
|147

Structured Query Language (SQL) Assignment
|4
|451
|190

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

Database Assignment Module
|13
|375
|111

Database Module Assignment
|13
|366
|40