Database Queries: Analyzing Customer and Invoice Data

This assignment presents a series of SQL queries designed to analyze relationships between customer, invoice, and product data. The solutions cover a range of database operations, including calculating invoice totals, identifying customers based on balance, and finding products sold. Queries utilize JOIN operations to combine data from multiple tables, such as customer, invoice, line, and product, to extract specific information. Examples include finding customers with balances within a specified range, identifying customers with invoices, and determining the number of invoices per customer. The assignment demonstrates how to use aggregate functions like SUM and COUNT, along with GROUP BY and HAVING clauses, to perform complex data analysis and reporting. It includes queries to retrieve customer names, vendor details, and product information based on various criteria, such as invoice totals, product codes, and units sold, offering a comprehensive guide to database querying techniques.

1) Show sums of line totals for each invoice.
Query:
SELECT inv_number, SUM(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 * FROM customer WHERE cus_balance BETWEEN (200+1) AND (300-1);
Output:
4) Using natural join and having, list the customers whose invoice total is greater than 200
Query:
SELECT customer.cus_code, cus_lname, cus_fname, cus_initial FROM customer NATURAL
JOIN invoice GROUP BY customer.cus_code HAVING SUM(inv_total)>200;
Output:

5) Show the names of the customers for whom we have invoices.
Query:
SELECT cus_code, cus_lname, cus_fname, cus_initial 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 customer.cus_code, cus_lname, cus_fname, cus_initial FROM customer NATURAL
JOIN invoice GROUP BY customer.cus_code HAVING SUM(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_lname, cus_fname, cus_initial 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 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:

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_count FROM customer NATURAL JOIN
invoice GROUP BY customer.cus_code;
Output:
10) List all purchases by customer 10018.
Query:
SELECT product.p_code, product.p_descript FROM customer NATURAL JOIN invoice
NATURAL JOIN line NATURAL JOIN product WHERE customer.cus_code=10018;
Output:

11) List the names of the vendors who provided products.
Query:
SELECT v_code, v_name FROM vendor WHERE V_code IN (SELECT v_code FROM
product);
Output:
12) Using inner join, list the details of the products which sold more than 10 units.
Query:
Select product.p_code , product.p_descript, sum(line_units) from line inner join product on
line.p_code= product.p_code group by product.p_code having sum(line_units)>10;
Output: