SQL Queries for Database Analysis: Invoice, Customer, and Product Data

This assignment showcases a collection of SQL queries designed to analyze data from an example database. The queries cover various aspects, including calculating line totals for invoices, locating records in the vendor table, finding customers based on balance criteria, and using joins and aggregate functions to retrieve specific information. The solutions demonstrate how to find customers with invoice totals greater than a certain value, list customers with and without invoices, identify the maximum invoice total, count invoices per customer, and retrieve purchase details by customer. Furthermore, the queries illustrate how to list product details based on sales volume and retrieve data involving vendors and products. The provided queries offer practical examples of how to perform database analysis and reporting using SQL.

SQL Queries
1. Show sums of line totals for each invoice.
select inv_number, line_number, p_code, line_units, sum(line_price) from line group by
inv_number;
2. Locate the record in the vendor table that does not have a value for the attribute V_STATE
select * from vendor where v_state = ' ';

3. Find the customers whose balance is greater than 200 but less than 300.
select * from customer where cus_balance> 200 and cus_balance<300;
4. Using natural join and having, list the customers whose invoice total is greater than 200

select invoice.inv_number, invoice.cus_code, customer.cus_fname, invoice.inv_date,
sum(line.line_price) as invoice_total from invoice natural join line, customer group by
invoice.inv_number having invoice_total>200;
5.
6. Show the names of the customers for whom we have invoices.
select cus_code, cus_fname, cus_lname from customer where cus_code in (select cus_code
from invoice);

7. Using natural join and having, list the customers whose invoice total is greater than
the average
select invoice.inv_number, invoice.cus_code, customer.cus_fname, invoice.inv_date,
sum(line.line_price) as invoice_total
from invoice natural join line, customer
group by invoice.inv_number having invoice_total>avg(line.line_price);

8. Show the names of the customers for whom we DO NOT have invoices.
select cus_code, cus_fname, cus_lname from customer where cus_code not in (select cus_code
from invoice);

9. Using natural join and having, list the customers whose invoice total is the
maximum.
select invoice.inv_number, invoice.cus_code, customer.cus_fname, invoice.inv_date,
max(invoice_total) from invoice, customer, (select invoice.inv_number, invoice.cus_code,
customer.cus_fname, invoice.inv_date, sum(line.line_price) as invoice_total from invoice
natural join line, customer group by invoice.inv_number) ;
10. List the first name and last name of the customers and how many times a customer
has generated an invoice.
select invoice.cus_code, customer.cus_fname, customer.cus_lname,
count(invoice.cus_code) from invoice natural join customer group by invoice.cus_code;

11. List all purchases by customer 10018.
select invoice.inv_number, invoice.cus_code, inv_date, line.p_code, product.p_descript from
invoice, line, product where line.inv_number=invoice.inv_number and
line.p_code=product.p_code and invoice.cus_code=10018;

12. List the names of the vendors who provided products.
select invoice.inv_number, invoice.cus_code, inv_date, line.p_code, product.p_descript,
vendor.v_name from line, invoice, product, vendor where
line.inv_number=invoice.inv_number and line.p_code=product.p_code and
product.v_code=vendor.v_code and invoice.cus_code=10018;

13. Using inner join, list the details of the products which sold more than 10 units.
select line.p_code, product.p_descript, sum(line.line_units) from line inner join product on
line.p_code=product.p_code group by line.p_code;