SQL Queries for Database Operations and Data Retrieval Tasks

This assignment provides a series of SQL queries and their corresponding outputs, addressing various database operations. The queries cover a range of tasks, including calculating sums of line totals for invoices, locating records with missing values, finding customers based on balance ranges, and retrieving customer information based on invoice totals. The solutions demonstrate the use of SELECT statements, JOIN operations (including natural join and inner join), GROUP BY and HAVING clauses, and aggregate functions. The queries also show how to retrieve customer names based on invoice presence or absence, find customers with maximum invoice totals, count invoice occurrences per customer, list purchases by specific customers, and retrieve vendor and product information. The assignment serves as a practical guide to writing SQL queries for data retrieval and manipulation in a relational database environment.

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: