Sample SQL Queries For SUM, SELECT, JOIN, And GROUP BY In An Essay.

a.Create a queryFile.txt  as follows and call this file  as  .read queryFile.txt
b.Output can be obtained in commandline interface using .output off  in the queryFile.txt

Questions:

Show sums of line units for each invoice.

select sum (line_units) as line_unit, inv_number from line group by inv_number;

Output 

line_unit   INV_NUMBER 

2.0         1001

2.0         1002

7.0         1003

5.0         1004

12.0        1005

6.0         1006

3.0         1007

9.0         1008

1. Show the details of the products that do not have a value for the attribute v_code.

select *from product where v_code not in (select v_code from vendor);

Output 

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

6185303  0.05

PVC23DRT    PVC pipe, 3.5-in., 8-  2/20/2008   188         75          5.8699998

8555908  0.0   

2. Show the details of the invoices whose subtotal is greater than 25 but less than 75.

select *from invoice where inv_subtotal between 26 and 74;

Output 

INV_NUMBER  CUS_CODE    INV_DATE    INV_SUBTOTAL      INV_TAX           INV_TOTA

L 

1004        10011       1/17/2008   34.9700012207031  2.79999995231628  37.77000

04577637

1005        10018       1/17/2008   70.4400024414062  5.6399998664856   76.08000

18310547

1007        10015       1/17/2008   34.9700012207031  2.79999995231628  37.77000

04577637

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

select *from invoice where inv_subtotal in (select min(inv_subtotal) from invoic

e);

Output 

INV_NUMBER  CUS_CODE    INV_DATE    INV_SUBTOTAL      INV_TAX            INV_TOT

AL 

1002        10011       1/16/2008   9.97999954223633  0.800000011920929  10.7799

997329712

1. Show the codes and names of the vendors who supplied products.

select v_name, v_code from vendor where v_code in (select v_code from product);

Output 

Bryson, Inc.  21225

D&E Supply    21231

Gomez Bros.   21344

Randsets Ltd  23119

ORDVA, Inc.   24288

Rubicon Syst  25595

1. Using EXCEPT show the codes of the vendors who did not supply any products. 

select vendor.v_code from vendor EXCEPT select product.v_code from product where

 p_code is not null;

Output 

V_CODE 

21226

22567

24004

25443

25501

1. COMP9311 Database Systems

select vendor.v_code, count(product.p_code) from vendor inner join product on ve

ndor.v_code=product.v_code group by vendor.v_code;

Output 

V_CODE      count(product.p_code) 

21231       1

21344       3

23119       2

24288       3

25595       3

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

select vendor.v_code, vendor.v_name, count(product.p_code) from vendor inner joi

n product on vendor.v_code=product.v_code group by vendor.v_code;

Output 

V_CODE      V_NAME        count(product.p_code) 

21225       Bryson, Inc.  2

21231       D&E Supply    1

21344       Gomez Bros.   3

23119       Randsets Ltd  2

24288       ORDVA, Inc.   3

25595       Rubicon Syst  3

1. List all purchases by customer 10011.

select product.p_code, product.p_descript, product.p_indate, product.p_qoh, prod

uct.p_min, product.p_price, product.p_discount from product, line, customer, inv

oice where customer.cus_code=10011 and product.p_code=line.p_code and line.inv_n

umber -invoice.inv_number and invoice.cus_code=customer.cus_code Group by Produc

t.p_code;

Output 

13-Q2/P2    7.25-in. pwr. saw blade  12/13/2007  32          15          14.9899

997711182  0.05

1546-QQ2    Hrd. cloth, 1/4-in., 2x  1/15/2008   15          8           39.9500

007629395  0.0

2232/QTY    B&D jigsaw, 12-in. blad  12/30/2007  8           5           109.919

998168945  0.05

2238/QPD    B&D cordless drill, 1/2  1/20/2008   12          5           38.9500

007629395  0.05

23109-HB    Claw hammer              1/20/2008   23          10          9.94999

980926514  0.1

54778-2T    Rat-tail file, 1/8-in.   12/15/2007  43          20          4.98999

977111816  0.0

89-WRE-Q    Hicut chain saw, 16 in.  2/7/2008    11          5           256.989

990234375  0.05

PVC23DRT    PVC pipe, 3.5-in., 8-ft  2/20/2008   188         75          5.86999

988555908  0.0

SM-18277    1.25-in. metal screw, 2  3/1/2008    172         75          6.98999

977111816  0.0

WR3/TT3     Steel matting, 4'x8'x1/  1/17/2008   18          5           119.949

996948242  0.1

1. Show the details of the employees who are located in area code 615. 

select *from emp where emp_areacode=615;

Output 

EMP_NUM     EMP_TITLE   EMP_LNAME   EMP_FNAME   EMP_INITIAL  EMP_DOB     EMP_HIR

E_DATE  EMP_AREACODE  EMP_PHONE   EMP_MGR 

100         Mr.         Kolmycz     George      D            6/15/1942   3/15/19

85      615           324-5456

101         Ms.         Lewis       Rhonda      G            3/19/1965   4/25/19

86      615           324-4472    100

103         Ms.         Jones       Anne        M            10/16/1974  8/28/19

94      615           898-3456    100

105         Mr.         Williams    Robert      D            3/14/1975   11/8/19

98      615           890-3220

106         Mrs.        Smith       Jeanine     K            2/12/1968   1/5/198

9       615           324-7883    105

107         Mr.         Diante      Jorge       D            8/21/1974   7/2/199

4       615           890-4567    105

108         Mr.         Wiesenbach  Paul        R            2/14/1966   11/18/1

992     615           897-4358

111         Mr.         Washington  Rupert      E            1/3/1966    6/21/19

93      615           890-4925    105

112         Mr.         Johnson     Edward      E            5/14/1961   12/1/19

83      615           898-4387    100

113         Ms.         Smythe      Melanie     P            9/15/1970   5/11/19

99      615           324-9006    105

115         Mrs.        Saranda     Hermine     R            7/25/1972   4/23/19

93      615           324-5505    105

116         Mr.         Smith       George      A            11/8/1965   12/10/1

988     615           890-2984    108

References: -

Atkinson, M. (1981). Database. Maidenhead, Berkshire, England: Pergamon Infotech.

Awad, E., & Gotterer, M. (1992). Database management. Danvers, Mass.: Boyd & Fraser Pub. Co.

Chia, K., Seow, E., & Teo, K. (2004). Database. Singapore: Pearson Prentice Hall.

Coronel, C., & Morris, S. (2018). Database Systems. Mason, OH: Cengage Learning US.

Li, D. (1987). A PROLOG database system. Letchworth Herts.: Research Studies Press.

MacWhinney, B. (2000). The database. Mahwah, NJ [u.a.]: Lawrence Erlbaum.

O'Neil, P. (2014). Database. Elsevier Science.

Ramarkrishnan, R. (1997). Database management system. London: McGraw-Hill Pub. Co. (ISE Editions).