SQL Queries for Invoice and Customer Data

This assignment provides a set of SQL queries designed to extract specific information from a database containing tables for invoices, customers, products, vendors, and lines. The queries cover various operations like grouping by invoice number, finding missing state information in vendors, filtering customer balances, using natural joins and having clauses, identifying customers with high invoice totals, and analyzing purchase patterns. Each query is accompanied by its output, showcasing the results obtained from executing the SQL statements.

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 `cus_code`, `cus_lname`, `cus_lname`, `cus_initial` 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`, CONCAT(`cus_initial` ,' ', `cus_fname` , ' ',`cus_lname`) FROM
`customer` NATURAL JOIN `invoice` GROUP BY `cus_code` HAVING
SUM(`inv_total`)>200;
Output:

5. Show the names of the customers for whom we have invoices.
Query:
SELECT `cus_code`, CONCAT(`cus_initial` ,' ', `cus_fname` , ' ',`cus_lname`) AS
cus_name 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`, CONCAT(`cus_initial` ,' ', `cus_fname` , ' ',`cus_lname`) AS
cus_name FROM `customer` NATURAL JOIN `invoice` GROUP BY `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`, CONCAT(`cus_initial` ,' ', `cus_fname` , ' ',`cus_lname`) AS
cus_name 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`, CONCAT(`cus_initial` ,' ', `cus_fname` , ' ',`cus_lname`) AS
cus_name FROM `customer` NATURAL JOIN `invoice` GROUP BY `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_generated_times FROM
`customer` NATURAL JOIN `invoice` GROUP BY `cus_code`;
Output:
10. List all purchases by customer 10018.
Query:
SELECT `p_code`,`p_descript` FROM `customer` NATURAL JOIN `invoice` NATURAL
JOIN `line` NATURAL JOIN `product` WHERE `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`) AS unit_sold
FROM `line` INNER JOIN `product` ON `line`.`p_code`= `product`.`p_code` GROUP
BY `product`.`p_code` HAVING SUM(`line_units`)>10;
Output: