SQLite Query Examples

Employee Data with Area Code 615

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

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>

1. 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> 

1. 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> 

1. Show the details of the invoice who has the minimum subtotal. 

sqlite> select MIN(INV_SUBTOTAL) from INVOICE;

MIN(INV_SUBTOTAL)

—————–

9.97999954223633

sqlite> 

1. 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

1. 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>

1. 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>

1. 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>

1. 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

1. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products. 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. 

References

Owens, M., & Allen, G. (2010). SQLite. Apress LP.

Kreibich, J. (2010). Using SQLite. ” O’Reilly Media, Inc.”.