Database Queries and Results Analysis for Customer and Product Data

This assignment presents a series of SQL queries designed to extract and analyze data from a relational database. The queries cover various aspects of data retrieval, including selecting specific records, calculating sums and averages, and joining tables to retrieve related information. The assignment includes queries to find customer information based on different criteria, such as balance, invoice totals, and whether they have invoices. It also explores queries to find product details and vendor information. Furthermore, the assignment demonstrates how to use aggregate functions and joins to generate insightful reports. This assignment is a valuable resource for students learning database concepts and SQL query writing, providing practical examples of how to retrieve and manipulate data for various business scenarios. The solutions are available on Desklib, a platform offering AI-based study tools for students.

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: