SQL Queries and Database Operations

This assignment provides a collection of SQL queries demonstrating different database operations. The queries cover tasks such as calculating line totals per invoice, identifying missing vendor state information, filtering customers based on balance ranges, utilizing natural joins with `HAVING` clauses for invoice total comparisons, finding customers without invoices, and listing customers with maximum or specific invoice counts. Additionally, the assignment includes queries for retrieving product purchase details by customer, listing vendors supplying products, and finding products sold in quantities exceeding a threshold.

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_STATE
Query:
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 200
Query:
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_CODE
IN (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_CODE
NOT 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);

Output:
9) List the first name and last name of the customers and how many times a customer has generated an
invoice.
Query:
SELECT CUS_FNAME, CUS_LNAME, COUNT(CUS_CODE) AS INVOICE_GENERATED_TIMES FROM
CUSTOMER NATURAL JOIN INVOICE GROUP BY CUS_CODE;
Output:
10) List all purchases by customer 10018.
Query:
SELECT PRODUCT.P_CODE, P_DESCRIPT FROM INVOICE INNER JOIN LINE ON
INVOICE.INV_NUMBER=LINE.INV_NUMBER INNER JOIN PRODUCT ON
PRODUCT.P_CODE=LINE.P_CODE WHERE CUS_CODE=10018;
Output:
11) List the names of the vendors who provided products.
Query:
SELECT DISTINCT V_NAME FROM VENDOR NATURAL JOIN PRODUCT;
Output:

12) Using inner join, list the details of the products which sold more than 10 units.
Query:
SELECT PRODUCT.P_CODE, P_DESCRIPT FROM LINE INNER JOIN PRODUCT ON PRODUCT.P_CODE
=LINE.P_CODE GROUP BY PRODUCT.P_CODE HAVING SUM(LINE_UNITS)>10;
Output: