SQL Queries for Database Operations

This assignment presents 12 SQL queries covering diverse database operations such as calculating invoice totals, finding customers with specific balances, listing products sold by vendors, and more. Each query includes a detailed explanation of its purpose and the expected output. Students can utilize these examples to understand SQL syntax and application in real-world scenarios.

Question1: Show sums of line totals for each invoice.
Query: SELECT INV_NUMBER, SUM(LINE_TOTAL) AS LINE_TOTAL_SUM FROM LINE GROUP BY
INV_NUMBER;
Output:
Question2: 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:

Question 3: Find the customers whose balance is greater than 200 but less than 300.
Query: SELECT * FROM CUSTOMER WHERE CUS_BALANCE>200 AND CUS_BALANCE<300;
Output:
Question 4: Using natural join and having, list the customers whose invoice total is greater than 200
Query: SELECT CUS_CODE, CUS_LNAME, CUS_FNAME, CUS_INITIAL FROM CUSTOMER NATURAL
JOIN INVOICE GROUP BY CUS_CODE HAVING SUM(INV_TOTAL)>200;
Output:

Question5: Show the names of the customers for whom we have invoices.
Query: SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME, CUS_INITIAL FROM CUSTOMER
NATURAL JOIN INVOICE;
Output:
Question 6: Using natural join and having, list the customers whose invoice total is greater than the average
Query: SELECT CUS_CODE, CUS_LNAME, CUS_FNAME, CUS_INITIAL FROM CUSTOMER NATURAL
JOIN INVOICE GROUP BY CUS_CODE HAVING SUM(INV_TOTAL)> (SELECT AVG(INV_TOTAL) FROM
INVOICE);
Output:

Question7: Show the names of the customers for whom we DO NOT have invoices.
Query:
SELECT CUS_CODE, CUS_LNAME, CUS_FNAME, CUS_INITIAL FROM CUSTOMER WHERE CUS_CODE
NOT IN (SELECT CUS_CODE FROM INVOICE);
Output:
Question8: Using natural join and having, list the customers whose invoice total is the maximum.
Query: SELECT CUSTOMER.CUS_CODE, CUS_LNAME, CUS_FNAME, CUS_INITIAL FROM CUSTOMER
NATURAL JOIN INVOICE GROUP BY CUSTOMER.CUS_CODE ORDER BY SUM(INV_TOTAL) DESC
LIMIT 1;
Output:

Question 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(*) AS INVOICE_GENERATED_COUNT FROM
CUSTOMER NATURAL JOIN INVOICE GROUP BY CUSTOMER.CUS_CODE;
Output:
Question 10: List all purchases by customer 10018.
Query: SELECT P_CODE, P_DESCRIPT, LINE_UNITS FROM CUSTOMER NATURAL JOIN INVOICE
NATURAL JOIN LINE NATURAL JOIN PRODUCT WHERE CUS_CODE=10018;
Output:

Question11: List the names of the vendors who provided products.
Query: SELECT V_NAME FROM VENDOR WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT);
Output:
Question12: Using inner join, list the details of the products which sold more than 10 units.
Query:
SELECT PRODUCT.P_CODE, PRODUCT.P_DESCRIPT FROM LINE INNER JOIN PRODUCT ON
LINE.P_CODE= PRODUCT.P_CODE GROUP BY PRODUCT.P_CODE HAVING SUM(LINE_UNITS)>10;
Output: