Database Homework: SQL Queries for Invoice, Customer, and Vendor Data

This assignment solution demonstrates various SQL queries for a database, addressing tasks related to invoice, customer, and vendor data. The queries cover a range of operations, including calculating sums of line totals for invoices, locating records with missing values, finding customers within specific balance ranges, and using natural joins to retrieve relevant data. The solutions showcase the use of aggregate functions like SUM and AVG, along with conditional statements (WHERE, BETWEEN, IS NULL) and joins to extract and manipulate data from multiple tables. The queries also involve subqueries to find customers with no invoices, determine maximum invoice totals, count the number of invoices generated by each customer, and list purchases by a specific customer. Furthermore, the solution includes queries to list vendors who provided products and to retrieve product details based on the number of units sold, demonstrating a comprehensive understanding of SQL syntax and database operations.

Assessment item 5
1. Show sums of line totals for each invoice
SELECT 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_STATE
SELECT 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 300
SELECT 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;

4. Using natural join and having, list the customers whose invoice total is greater than 200
SELECT 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 invoices
SELECT 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 average
SELECT CUS_LNAME AS "Last Name", CUS_FNAME AS "First Name", AVG(INV_TOTAL) AS
"Average Total", INV_TOTAL AS "Invoice Total" 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 invoices
SELECT CUS_LNAME || ', ' || CUS_FNAME || ' ' || CUS_INITIAL AS "No Invoice Customer"
FROM CUSTOMER WHERE CUS_CODE NOT IN (SELECT CUS_CODE FROM INVOICE);

8. Using natural join and having, list the customers whose invoice total is the maximum
SELECT CUS_LNAME || ', ' || CUS_FNAME || ' ' || CUS_INITIAL AS "Customer Name",
INV_TOTAL AS "Highest Invoice Total" FROM CUSTOMER NATURAL JOIN INVOICE WHERE
INV_TOTAL=(SELECT MAX(INV_TOTAL) FROM INVOICE);
9. List the first name and last name of the customers and how many times a customer has
generated an invoice
SELECT CUS_LNAME || ', ' || CUS_FNAME || ' ' || CUS_INITIAL AS "Customer Name",
COUNT(*) AS "Number Times Invoice Generated" FROM CUSTOMER NATURAL JOIN
INVOICE GROUP BY CUS_CODE;
10. List all purchases by customer 10018
SELECT P_CODE AS "Product ID",P_DESCRIPT AS "Project Description", Line_Units AS
"Quantity Sold", Line_Price AS "Unit Price", Line_Total "Line Total" FROM LINE NATURAL JOIN
PRODUCT WHERE INV_NUMBER IN (SELECT INV_NUMBER FROM INVOICE WHERE
CUS_CODE=10018);

11. List the names of the vendors who provided products
SELECT DISTINCT vo.V_NAME AS "Vendor Name" FROM PRODUCT po INNER JOIN
VENDOR vo ON vo.V_CODE=po.V_CODE;
12. Using inner join, list the details of the products which sold more than 10 units
SELECT pr.P_DESCRIPT AS "Product Name", li.LINE_UNITS AS "Number of Units", li.LINE_TOTAL
AS "Total Price" FROM LINE li INNER JOIN PRODUCT pr ON pr. P_CODE=LI.P_CODE WHERE
li.LINE_UNITS>10;