SQL Queries for Database Management Systems - Semester 1

This assignment provides solutions to a series of SQL queries designed to test understanding of database operations. The queries cover a range of topics including data retrieval, filtering, aggregation, and joining of tables. Specific tasks include finding sums of line totals, locating records with missing data, filtering records based on conditions, using joins and aggregations to retrieve specific data, and identifying customers based on various criteria. The solutions demonstrate the use of SELECT, FROM, WHERE, GROUP BY, HAVING, and JOIN clauses, and subqueries to achieve the desired results. The assignment covers a variety of SQL commands and database concepts, providing a comprehensive overview of database querying techniques. The provided solutions can be used as a reference for students learning SQL and database management.

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: