Assignment5 Query Summaries

The assignment is about querying a database called 'assignment5' using SQL statements. It contains 11 queries that retrieve various information from the database, including sums of line totals for each invoice, customers with balances greater than 200 but less than 300, and products that sold more than 10 units. The queries also include natural joins and having clauses to filter results.

Student ID
Student Name
Course ID
Instructor Name

Table of Contents
Query 1........................................................................................................................................................3
Query 2........................................................................................................................................................4
Query 3........................................................................................................................................................5
Query 4........................................................................................................................................................6
Query 5........................................................................................................................................................7
Query 6........................................................................................................................................................8
Query 7........................................................................................................................................................9
Query 8......................................................................................................................................................10
Query 9......................................................................................................................................................11
Query 10....................................................................................................................................................12
Query 11....................................................................................................................................................13

Before executing any of the Query please select the database ‘assignment5’ by executing the below
command: use assignment5;
Query 1
Show sums of line totals for each invoice.
SQL Statement
SELECT INV_NUMBER, ROUND(SUM(LINE_TOTAL), 2) AS SUM
FROM line
GROUP BY INV_NUMBER;
Result

Query 2
Locate the record in the vendor table that does not have a value for the attribute V_STATE
SQL Statement
SELECT * FROM vendor
WHERE V_STATE IS NULL;
Result

Query 3
Find the customers whose balance is greater than 200 but less than 300
SQL Statement
SELECT * FROM customer
WHERE CUS_BALANCE > 200 AND CUS_BALANCE < 300;
Result

Query 4
Using natural join and having, list the customers whose invoice total is greater than 200
SQL Statement
SELECT cus.CUS_CODE, cus.CUS_LNAME, cus.CUS_FNAME, cus.CUS_INITIAL,
cus.CUS_AREACODE, cus.CUS_PHONE, cus.CUS_BALANCE, inv.INV_TOTAL
FROM customer AS cus
INNER JOIN invoice AS inv ON cus.CUS_CODE = inv.CUS_CODE
HAVING inv.INV_TOTAL > 200;
Result

Query 5
Show the names of the customers for whom we have invoices.
SQL Statement
SELECT DISTINCT (cus.CUS_FNAME), cus.CUS_LNAME
FROM customer AS cus
INNER JOIN invoice AS inv ON cus.CUS_CODE = inv.CUS_CODE;
Result

Query 6
Using natural join and having, list the customers whose invoice total is greater than the average
SQL Statement
SELECT cus.CUS_CODE, cus.CUS_LNAME, cus.CUS_FNAME, cus.CUS_INITIAL,
cus.CUS_AREACODE, cus.CUS_PHONE, cus.CUS_BALANCE, inv.INV_TOTAL
FROM customer AS cus
INNER JOIN invoice AS inv ON cus.CUS_CODE = inv.CUS_CODE
HAVING inv.INV_TOTAL > (SELECT ROUND(AVG(INV_TOTAL),2) FROM INVOICE);
Result

Query 7
Show the names of the customers for whom we DO NOT have invoices.
SQL Statement
SELECT CUS_FNAME, CUS_LNAME
FROM customer
WHERE CUS_CODE NOT IN (SELECT DISTINCT CUS_CODE FROM invoice);
Result

Query 8
List the first name and last name of the customers and how many times a customer has
generated an invoice.
SQL Statement
SELECT cus.CUS_FNAME, cus.CUS_LNAME, COUNT(inv.INV_NUMBER)
FROM customer AS cus
INNER JOIN invoice AS inv
ON cus.CUS_CODE = inv.CUS_CODE
GROUP BY inv.CUS_CODE;
Result

Query 9
List all purchases by customer 10018
SQL Statement
SELECT inv.INV_NUMBER, inv.INV_DATE, lin.P_CODE, pro.P_DESCRIPT,
inv.INV_SUBTOTAL, inv.INV_TAX, inv.INV_TOTAL, lin.LINE_UNITS, lin.LINE_PRICE,
lin.LINE_TOTAL
FROM invoice AS inv
INNER JOIN line AS lin
ON inv.INV_NUMBER = lin.INV_NUMBER
INNER JOIN product AS pro
ON lin.P_CODE = pro.P_CODE
WHERE inv.CUS_CODE = 10018;
Result

Query 10
List the names of the vendors who provided products
SQL Statement
SELECT DISTINCT ven.V_Name
FROM vendor ven
INNER JOIN product pro
ON ven.V_Code = pro.V_Code
WHERE pro.V_CODE <> 0;
Result

Query 11
Using inner join, list the details of the products which sold more than 10 units
SQL Statement
SELECT pro.P_CODE, pro.P_DESCRIPT, pro.P_INDATE, pro.P_QOH, pro.P_MIN,
pro.P_Price, pro.P_DISCOUNT, pro.V_CODE
FROM product AS pro INNER JOIN line AS lin
ON pro.P_CODE = lin.P_CODE
WHERE lin.LINE_UNITS > 10;
Result