SQL Query Examples for Database Management

This assignment provides a collection of 11 solved SQL query examples. Each query focuses on different aspects of database management, including retrieving invoice details, listing product information, querying vendor data, and analyzing sales trends based on product quantities sold. The queries showcase the use of JOINs, WHERE clauses, and aggregate functions to manipulate and extract specific information from a relational database.

Student ID
Student Name
Subject Code

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

Query 1
Show sums of line totals for each invoice.
SQL Statement & Result
SELECT INV_NUMBER, SUM(LINE_TOTAL) AS SUM
FROM line
GROUP BY INV_NUMBER;
INV_NUMBER SUM
---------- ----------------
1001 24.9399995803833
1002 9.97999954223633
1003 153.849998474121
1004 34.8699998855591
1005 70.4400024414062
1006 397.82998752594
1007 34.9699993133545
1008 399.150006294251
Screenshot

Query 2
Locate the record in the vendor table that does not have a value for the attribute V_STATE
SQL Statement & Result
SELECT * FROM vendor
WHERE V_STATE IS NULL;
V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER
---------- ---------- ---------- ---------- ---------- ---------- ----------
25443 B&K, Inc. Smith 904 227-0093 N
Screenshot
Query 3
Find the customers whose balance is greater than 200 but less than 300
SQL Statement & Result
SELECT * FROM customer
WHERE CUS_BALANCE > 200 AND CUS_BALANCE < 300;
CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE
---------- ---------- ---------- ----------- ------------ ---------- ------------
----
10016 Brown James G 615 297-1228 221.19000244
1406
10018 Farriss Anne G 713 382-7185 216.55000305
1758

Screenshot
Query 4
Using natural join and having, list the customers whose invoice total is greater than 200
SQL Statement & Result
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
GROUP BY cus.CUS_CODE
HAVING inv.INV_TOTAL > 200;
CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE
INV_TOTAL
---------- ---------- ---------- ----------- ------------ ---------- -----------
----------------
10011 Dunne Leona K 713 894-1238 0.0
431.079986572266
10014 Orlando Myron 615 222-1672 0.0
429.660003662109

Screenshot
Query 5
Show the names of the customers for whom we have invoices.
SQL Statement & Result
SELECT DISTINCT (cus.CUS_FNAME), cus.CUS_LNAME
FROM customer AS cus
INNER JOIN invoice AS inv ON cus.CUS_CODE = inv.CUS_CODE;
CUS_FNAME CUS_LNAME
---------- ----------
Leona Dunne
Kathy Smith
Myron Orlando
Amy O'Brian
Anne Farriss
Screenshot

Query 6
Using natural join and having, list the customers whose invoice total is greater than the average
SQL Statement & Result
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
GROUP BY cus.CUS_CODE
HAVING inv.INV_TOTAL > (SELECT ROUND(AVG(INV_TOTAL),2) FROM INVOICE);
CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS_PHONE CUS_BALANCE
INV_TOTAL
---------- ---------- ---------- ----------- ------------ ---------- ----------- ------------
----
10011 Dunne Leona K 713 894-1238 0.0 431.07998657
2266
10012 Smith Kathy W 615 894-2285 345.8599853 166.16000366
2109
10014 Orlando Myron 615 222-1672 0.0 429.66000366
2109
Screenshot

Query 7
Show the names of the customers for whom we DO NOT have invoices.
SQL Statement & Result
SELECT CUS_FNAME, CUS_LNAME
FROM customer
WHERE CUS_CODE NOT IN (SELECT DISTINCT CUS_CODE FROM invoice);
CUS_FNAME CUS_LNAME
---------- ----------
Alfred Ramas
Paul Olowski
James Brown
George Williams
Olette Smith
Screenshot
Query 8
List the first name and last name of the customers and how many times a customer has generated an invoice.
SQL Statement & Result
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;
CUS_FNAME CUS_LNAME COUNT(inv.INV_NUMBER)
---------- ---------- ---------------------
Leona Dunne 3
Kathy Smith 1
Myron Orlando 2
Amy O'Brian 1
Anne Farriss 1
Screenshot
Query 9
List all purchases by customer 10018
SQL Statement & Result
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;
INV_NUMBER INV_DATE P_CODE P_DESCRIPT INV_SUBTOTAL INV_TAX INV_
TOTAL LINE_UNITS LINE_PRICE LINE_TOTAL
---------- ---------- ---------- ----------------------- ---------------- --------------- ----
------------ ---------- ---------------- ----------------
1005 1/17/2008 PVC23DRT PVC pipe, 3.5-in., 8-ft 70.4400024414062 5.6399998664856 76.0
800018310547 12.0 5.86999988555908 70.4400024414062
Screenshot
Query 10
List the names of the vendors who provided products
SQL Statement & Result
SELECT DISTINCT ven.V_Name
FROM vendor ven
INNER JOIN product pro
ON ven.V_Code = pro.V_Code
WHERE pro.V_CODE <> 0;
V_NAME
---------------
Rubicon Systems
Gomez Bros.
Randsets Ltd.

ORDVA, Inc.
Bryson, Inc.
D&E Supply
Screenshot
Query 11
Using inner join, list the details of the products which sold more than 10 units
SQL Statement & Result
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;
P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUN
T V_CODE
---------- ----------------------- ---------- ---------- ---------- ---------------- ---------
- ----------
PVC23DRT PVC pipe, 3.5-in., 8-ft 2/20/2008 188 75 5.86999988555908 0.0
Screenshot