SQL Commands to run queries on a database

Using the same scenario and the database you created in Practice Exercise 4, write at least 15 SQL queries on your populated database.

Submit a Microsoft Word document showing each of your 15 SQL commands and a screen capture of the results of each command in the database application (i.e Microsoft Access, Oracle, or MySQL, etc.).

Your 15 SQL commands must have Select From Statements including at least one of the following:

WHERE

AND/OR IN BETWEEN

Relational Operators (>=, <=, <, >, <>)

LIKE with Wildcards (*, %, etc.)

At least one of: COUNT SUM AVG MIN MAX

DISTINCT

HAVING

GROUP BY

ORDER BY

Subqueries

Joins

FROM MY PREVIOUS ACTIVITY

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE --------- -------------------- PRODUCTS READ WRITE

SQL> SQL> CREATE TABLE Rep(RepNum CHAR(2) PRIMARY KEY,LastName CHAR(15),FirstName CHAR(15),Street CHAR(15),City CHAR(15),State CHAR(2),Zip CHAR(5),Commission DECIMAL(7,2),Rate DECIMAL(3,2) );

CREATE TABLE Customer(CustomerNum CHAR(3) PRIMARY KEY,CustomerName CHAR(35) NOT NULL,Street CHAR(15),City CHAR(15),State CHAR(2),Zip CHAR(5),Balance DECIMAL(8,2),CreditLimit DECIMAL(8,2),RepNum CHAR(2) );

CREATE TABLE Orders(OrderNum CHAR(5) PRIMARY KEY,OrderDate DATE,CustomerNum CHAR(3) );

CREATE TABLE Part(PartNum CHAR(4) PRIMARY KEY,Description CHAR(15),OnHand DECIMAL(4,0),Class CHAR(2),Warehouse CHAR(1),Price DECIMAL(6,2) );

CREATE TABLE OrderLine(OrderNum CHAR(5),PartNum CHAR(4),NumOrdered DECIMAL(3,0),QuotedPrice DECIMAL(6,2),PRIMARY KEY (OrderNum, PartNum) );

INSERT INTO Rep VALUES('20','Kaiser','Valerie','624Randall','Grove','FL','33321',20542.50,0.05); INSERT INTO Rep VALUES('35','Hull','Richard','532Jackson','Sheldon','FL','33553',39216.00,0.07); INSERT INTO Rep VALUES('65','Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05);

INSERT INTO Customer VALUES('148','Al''s Appliance and Sport','2837Greenway','Fillmore','FL','33336',6550.00,7500.00,'20'); INSERT INTO Customer VALUES('282','Brookings Direct','3827Devon','Grove','FL','33321',431.50,10000.00,'35'); INSERT INTO Customer VALUES('356','Ferguson''s','382Wildwood','Northfield','FL','33146',5785.00,7500.00,'65'); INSERT INTO Customer VALUES('408','The Everything Shop','1828Raven','Crystal','FL','33503',5285.25,5000.00,'35'); INSERT INTO Customer VALUES('462','Bargains Galore','3829Central','Grove','FL','33321',3412.00,10000.00,'65'); INSERT INTO Customer VALUES('524','Kline''s','838Ridgeland','Fillmore','FL','33336',12762.00,15000.00,'20'); INSERT INTO Customer VALUES('608','Johnson''s Department Store','372Oxford','Sheldon','FL','33553',2106.00,10000.00,'65'); INSERT INTO Customer VALUES('687','Lee''s Sport and Appliance','282Evergreen','Altonville','FL','32543',2851.00,5000.00,'35'); INSERT INTO Customer VALUES('725','Deerfield''s Four Seasons','282Columbia','Sheldon','FL','33553',248.00,7500.00,'35'); INSERT INTO Customer VALUES('842','All Season','28Lakeview','Grove','FL','33321',8221.00,7500.00,'20');

INSERT INTO Orders VALUES('21608',TO_DATE('2007/10/20', 'yyyy/mm/dd'),'148'); INSERT INTO Orders VALUES('21610',TO_DATE('2007-10-20', 'yyyy/mm/dd'),'356'); INSERT INTO Orders VALUES('21613',TO_DATE('2007-10-21', 'yyyy/mm/dd'),'408'); INSERT INTO Orders VALUES('21614',TO_DATE('2007-10-21', 'yyyy/mm/dd'),'282'); INSERT INTO Orders VALUES('21617',TO_DATE('2007-10-23', 'yyyy/mm/dd'),'608'); INSERT INTO Orders VALUES('21619',TO_DATE('2007-10-23', 'yyyy/mm/dd'),'148'); INSERT INTO Orders VALUES('21623',TO_DATE('2007-10-23', 'yyyy/mm/dd'),'608');

INSERT INTO Part VALUES('AT94','Iron',50,'HW','3',24.95); INSERT INTO Part VALUES('BV06','Home Gym',45,'SG','2',794.95); INSERT INTO Part VALUES('CD52','Microwave Oven',32,'AP','1',165.00); INSERT INTO Part VALUES('DL71','Cordless Drill',21,'HW','3',129.95); INSERT INTO Part VALUES('DR93','Gas Range',8,'AP','2',495.00); INSERT INTO Part VALUES('DW11','Washer',12,'AP','3',399.99); INSERT INTO Part VALUES('FD21','Stand Mixer',22,'HW','3',159.95); INSERT INTO Part VALUES('KL62','Dryer',12,'AP','1',349.95); INSERT INTO Part VALUES('KT03','Dishwasher',8,'AP','3',595.00); INSERT INTO Part VALUES('KV29','Treadmill',9,'SG','2',1390.00);

INSERT INTO OrderLine VALUES('21608','AT94',11,21.95); INSERT INTO OrderLine VALUES('21610','DR93',1,495.00); INSERT INTO OrderLine VALUES('21610','DW11',1,399.99); INSERT INTO OrderLine VALUES('21613','KL62',4,329.95); INSERT INTO OrderLine VALUES('21614','KT03',2,595.00); INSERT INTO OrderLine VALUES('21617','BV06',2,794.95); INSERT INTO OrderLine VALUES('21617','CD52',4,150.00); INSERT INTO OrderLine VALUES('21619','DR93',1,495.00); INSERT INTO OrderLine VALUES('21623','KV29',2,1290.00); Table created.

SQL> SQL> Table created.

SQL> SQL> Table created.

SQL> SQL> Table created.

SQL> SQL> Table created.

SQL> SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> 1 row created.

SQL> select Orders.OrderNum,orders.OrderDate,Part.PartNum,Part.Description,Order Line.NumOrdered,OrderLine.QuotedPrice from Orders,Part,OrderLine;

ORDER ORDERDATE PART DESCRIPTION NUMORDERED QUOTEDPRICE ----- --------- ---- --------------- ---------- ----------- 21608 20-OCT-07 AT94 Iron 11 21.95 21608 20-OCT-07 BV06 Home Gym 11 21.95 21608 20-OCT-07 CD52 Microwave Oven 11 21.95 21608 20-OCT-07 DL71 Cordless Drill 11 21.95 21608 20-OCT-07 DR93 Gas Range 11 21.95 21608 20-OCT-07 DW11 Washer 11 21.95 21608 20-OCT-07 FD21 Stand Mixer 11 21.95 21608 20-OCT-07 KL62 Dryer 11 21.95 21608 20-OCT-07 KT03 Dishwasher 11 21.95 21608 20-OCT-07 KV29 Treadmill 11 21.95 21608 20-OCT-07 AT94 Iron 1 495

ORDER ORDERDATE PART DESCRIPTION NUMORDERED QUOTEDPRICE ----- --------- ---- --------------- ---------- ----------- 21608 20-OCT-07 BV06 Home Gym 1 495 21608 20-OCT-07 CD52 Microwave Oven 1 495 21608 20-OCT-07 DL71 Cordless Drill 1 495 21608 20-OCT-07 DR93 Gas Range 1 495 21608 20-OCT-07 DW11 Washer 1 495 21608 20-OCT-07 FD21 Stand Mixer 1 495 21608 20-OCT-07 KL62 Dryer 1 495 21608 20-OCT-07 KT03 Dishwasher 1 495 21608 20-OCT-07 KV29 Treadmill 1 495 21608 20-OCT-07 AT94 Iron 1 399.99 21608 20-OCT-07 BV06 Home Gym 1 399.99

ORDER ORDERDATE PART DESCRIPTION NUMORDERED QUOTEDPRICE ----- --------- ---- --------------- ---------- ----------- 21608 20-OCT-07 CD52 Microwave Oven 1 399.99 21608 20-OCT-07 DL71 Cordless Drill 1 399.99 21608 20-OCT-07 DR93 Gas Range 1 399.99 21608 20-OCT-07 DW11 Washer 1 399.99 21608 20-OCT-07 FD21 Stand Mixer 1 399.99 21608 20-OCT-07 KL62 Dryer 1 399.99 21608 20-OCT-07 KT03 Dishwasher 1 399.99 21608 20-OCT-07 KV29 Treadmill 1 399.99 21608 20-OCT-07 AT94 Iron 4 329.95 21608 20-OCT-07 BV06 Home Gym 4 329.95 21608 20-OCT-07 CD52 Microwave Oven 4 329.95

ORDER ORDERDATE PART DESCRIPTION NUMORDERED QUOTEDPRICE ----- --------- ---- --------------- ---------- ----------- 21608 20-OCT-07 DL71 Cordless Drill 4 329.95 21608 20-OCT-07 DR93 Gas Range 4 329.95 21608 20-OCT-07 DW11 Washer 4 329.95 21608 20-OCT-07 FD21 Stand Mixer 4 329.95 21608 20-OCT-07 KL62 Dryer 4 329.95 21608 20-OCT-07 KT03 Dishwasher 4 329.95 21608 20-OCT-07 KV29 Treadmill 4 329.95 21608 20-OCT-07 AT94 Iron 2 595 21608 20-OCT-07 BV06 Home Gym 2 595 21608 20-OCT-07 CD52 Microwave Oven 2 595 21608 20-OCT-07 DL71 Cordless Drill 2 595

ORDER ORDERDATE PART DESCRIPTION NUMORDERED QUOTEDPRICE ----- --------- ---- --------------- ---------- ----------- 21608 20-OCT-07 DR93 Gas Range 2 595 21608 20-OCT-07 DW11 Washer 2 595 21608 20-OCT-07 FD21 Stand Mixer 2 595 21608 20-OCT-07 KL62 Dryer 2 595

OrderNum	OrderDate	PartNum	Description	NumOrdered	QuotedPrice
21608	10/20/2010	AT94	Iron	11	$21.95
21610	10/20/2010	DR93	Gas Range	1	$495.00
21610	10/20/2010	DW11	Washer	1	$399.99
21613	10/21/2010	KL62	Dryer	4	$329.95
21614	10/21/2010	KT03	Dishwasher	2	$595.00
21617	10/23/2010	BV06	Home Gym	2	$794.95
21617	10/23/2010	CD52	Microwave Oven	4	$150.00
21619	10/23/2010	DR93	Gas Range	1	$495.00
21523	10/23/2010	KV29	Treadmill	2	$1290.00