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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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).
To export a reference to this article please select a referencing stye below:
My Assignment Help. (2021). Sample SQL Queries For SUM, SELECT, JOIN, And GROUP BY In An Essay.. Retrieved from https://myassignmenthelp.com/free-samples/comp9311-database-systems/line-group.html.
"Sample SQL Queries For SUM, SELECT, JOIN, And GROUP BY In An Essay.." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/comp9311-database-systems/line-group.html.
My Assignment Help (2021) Sample SQL Queries For SUM, SELECT, JOIN, And GROUP BY In An Essay. [Online]. Available from: https://myassignmenthelp.com/free-samples/comp9311-database-systems/line-group.html
[Accessed 13 November 2024].
My Assignment Help. 'Sample SQL Queries For SUM, SELECT, JOIN, And GROUP BY In An Essay.' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/comp9311-database-systems/line-group.html> accessed 13 November 2024.
My Assignment Help. Sample SQL Queries For SUM, SELECT, JOIN, And GROUP BY In An Essay. [Internet]. My Assignment Help. 2021 [cited 13 November 2024]. Available from: https://myassignmenthelp.com/free-samples/comp9311-database-systems/line-group.html.