SQL Queries for Database Management

This assignment presents a collection of SQL queries designed to demonstrate fundamental database management tasks. Each query focuses on a specific operation, such as calculating invoice totals, identifying customers with specific balances, or listing vendors who supplied products. The assignment includes the SQL code for each query along with its expected output, providing a practical understanding of how SQL can be used to manipulate and retrieve data from a relational database.

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: