Database Systems Assignment: SQL Queries and Database Analysis

This document presents a comprehensive solution to a database systems assignment, focusing on SQL queries and database analysis using SQLite. The assignment covers a range of SQL commands to retrieve specific data, including employee details based on area code, invoice totals, and products lacking vendor codes. It also demonstrates queries to filter invoices based on subtotal ranges, find minimum subtotals, and perform inner joins to retrieve product details based on line price. Further, the solution includes queries to list purchases by specific customers, vendor information, and the number of products supplied by each vendor. Additionally, the assignment utilizes EXCEPT and NOT IN clauses to identify vendors who did not supply any products. The solution provides a step-by-step approach with the corresponding SQL code and the results for each query, showcasing the functionality and capabilities of SQL in database management and information retrieval.

Running Head: Database Systems 1
Database Systems
Student’s name
University affiliation

Database Systems 2
1. Show the details of the employees who are located in area code 615.
Solution.
sqlite> .mode column
sqlite> .header on
sqlite> select*from EMP where EMP_AREACODE='615'
...> ;
EMP_NUM EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL EMP_DOB EMP_HIRE_DATE
EMP_AREACODE EMP_PHONE EMP_MGR
---------- ---------- ---------- ---------- ----------- ---------- ------------- ------------ ---------- ----------
100 Mr. Kolmycz George D 6/15/1942 3/15/1985 615 324-5456
101 Ms. Lewis Rhonda G 3/19/1965 4/25/1986 615 324-4472 100
103 Ms. Jones Anne M 10/16/1974 8/28/1994 615 898-3456 100
105 Mr. Williams Robert D 3/14/1975 11/8/1998 615 890-3220
106 Mrs. Smith Jeanine K 2/12/1968 1/5/1989 615 324-7883 105
107 Mr. Diante Jorge D 8/21/1974 7/2/1994 615 890-4567 105
108 Mr. Wiesenbach Paul R 2/14/1966 11/18/1992 615 897-4358
111 Mr. Washington Rupert E 1/3/1966 6/21/1993 615 890-4925 105
112 Mr. Johnson Edward E 5/14/1961 12/1/1983 615 898-4387 100
113 Ms. Smythe Melanie P 9/15/1970 5/11/1999 615 324-9006 105
115 Mrs. Saranda Hermine R 7/25/1972 4/23/1993 615 324-5505 105
116 Mr. Smith George A 11/8/1965 12/10/1988 615 890-2984 108

Database Systems 3
2. Show sums of line units for each invoice.
sqlite> .mode column
sqlite> .headers on
sqlite> select INV_TOTAL from INVOICE;
INV_TOTAL
----------------
26.8899993896484
10.7799997329712
166.160003662109
37.7700004577637
76.0800018310547
429.660003662109
37.7700004577637
431.079986572266
sqlite>

Database Systems 4
3. Show the details of the products that do not have a value for the attribute v_code
sqlite> .mode column
sqlite> .headers on
sqlite> select*from PRODUCT where V_CODE='';
P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE
---------- --------------------- ---------- ---------- ---------- ---------------- ---------- ----------
23114-AA Sledge hammer, 12 lb. 1/2/2008 8 5 14.3999996185303 0.05
PVC23DRT PVC pipe, 3.5-in., 8- 2/20/2008 188 75 5.86999988555908 0.0
sqlite>
4. Show the details of the invoices whose subtotal is greater than 25 but less than 75.
sqlite> .mode column
sqlite> .headers on
sqlite> select*from INVOICE where INV_SUBTOTAL>25 AND INV_SUBTOTAL<75;
INV_NUMBER CUS_CODE INV_DATE INV_SUBTOTAL INV_TAX INV_TOTAL
---------- ---------- ---------- ---------------- ---------------- ----------------
1004 10011 1/17/2008 34.9700012207031 2.79999995231628 37.7700004577637
1005 10018 1/17/2008 70.4400024414062 5.6399998664856 76.0800018310547
1007 10015 1/17/2008 34.9700012207031 2.79999995231628 37.7700004577637
sqlite>

Database Systems 5
5. Show the details of the invoice who has the minimum subtotal.
sqlite> select MIN(INV_SUBTOTAL) from INVOICE;
MIN(INV_SUBTOTAL)
-----------------
9.97999954223633
sqlite>
6. Using inner join, list the details of the products whose line price is greater than 100
sqlite> select*from Product INNER JOIN line on product.p_code = line.p_code
...> where
...> line_price>100;
P_CODE P_DESCRIPT P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE
INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE LINE_TOTAL
---------- ------------------------ ---------- ---------- ---------- ---------------- ---------- ---------- ---------- -----------
---------- ---------- ---------------- ----------------
2232/QTY B&D jigsaw, 12-in. blade 12/30/2007 8 5 109.919998168945 0.05 24288
1006 2 2232/QTY 1.0 109.919998168945 109.919998168945
89-WRE-Q Hicut chain saw, 16 in. 2/7/2008 11 5 256.989990234375 0.05 24288
1006 4 89-WRE-Q 1.0 256.989990234375 256.989990234375
WR3/TT3 Steel matting, 4'x8'x1/6 1/17/2008 18 5 119.949996948242 0.1 25595
1008 2 WR3/TT3 3.0 119.949996948242 359.850006103516

Database Systems 6
7. List all purchases by customer 10011.
sqlite> select*from INVOICE
...> where
...> CUS_CODE='10011';
INV_NUMBER CUS_CODE INV_DATE INV_SUBTOTAL INV_TAX INV_TOTAL
---------- ---------- ---------- ---------------- ----------------- ----------------
1002 10011 1/16/2008 9.97999954223633 0.800000011920929 10.7799997329712
1004 10011 1/17/2008 34.9700012207031 2.79999995231628 37.7700004577637
1008 10011 1/17/2008 399.149993896484 31.9300003051758 431.079986572266
sqlite>
8. Show the codes and names of the vendors who supplied products.
sqlite> select v_code,v_name from vendor where v_order='Y';
V_CODE V_NAME
---------- ------------
21225 Bryson, Inc.
21231 D&E Supply
23119 Randsets Ltd
24288 ORDVA, Inc.
25595 Rubicon Syst
sqlite>

Database Systems 7
9. List the codes of vendors and the number of products each vendor has supplied, i.e. vendor
XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
sqlite> select v_code, p_qoh from product;
V_CODE P_QOH
---------- ----------
25595 8
21344 32
21344 18
23119 15
23119 23
24288 8
24288 6
25595 12
21225 23
8
21344 43
24288 11
188
21225 172
21231 237
25595 18
sqlite>

Database Systems 8
10. Using EXCEPT show the codes of the vendors who did not supply any products.
sqlite> select v_code from vendor
...> except
...> select v_code from product;
V_CODE
----------
21226
22567
24004
25443
25501

Database Systems 9
11. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any
products.
12. List the names and codes of vendors and the number of products each vendor has
supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy
products etc.

Database Systems 10
References
Owens, M., & Allen, G. (2010). SQLite. Apress LP.
Kreibich, J. (2010). Using SQLite. " O'Reilly Media, Inc.".