For Problems 14, using the STORES Database, formulate SQL queries, Hand in a listing of each query and its output.

1. (10 Points) For each customer, list each stock item ordered, the manufacturer, the quantity ordered, and the total price paid. Include the following columns in the order given below:

From Customer Table: Company

From Stock Table: Description

From the Manufact Table: Manu_Name

From the Items Table: Quantity, Total Price

Order the output by Company and Description.

2. (10 Points) List all orders with a shipping date between December 25, 1999 and January 5, 2000. List the Order Number, Order Date, Customer company name, and Shipping Date. Order by Customer Company Name and Order Number.

3. (10 Points) Count the number of customers who do not have any orders placed.

4. (10 Points) List all customers who are ordering equipment whose description begins with tennis or volleyball. List the Customer number, Stock number, and Description. Do not repeat any rows.

5. (15 Points) Use the following SQL CREATE commands to CREATE the following tables in your

User ID:

CREATE TABLE Professor

(Prof_ID NUMBER(3) Constraint pk_Professor Primary Key,

Prof_Lname VARCHAR2(15) NOT NULL,

Prof_Hiredate DATE,

Prof_Sal NUMBER(8,2),

Prof_Dept CHAR(6)

);

CREATE TABLE Student

(Stu_ID NUMBER(4) Constraint pk_Student Primary Key,

Stu_Lname VARCHAR2(15) NOT NULL,

Stu_Major CHAR(6),

Stu_CredHrs NUMBER(4),

Stu_GradePts NUMBER(5),

Prof_ID NUMBER(3),

CONSTRAINT fk_Student_Prof_ID FOREIGN KEY(Prof_ID)

REFERENCES Professor

);

Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables.

6. (15 Points) Insert the following data into the tables created above using SQL INSERT commands.

Professor Table:

Prof_ID	Prof_Lname	Prof_Hiredate	Prof_Sal	Prof_Dept
123	Hilbert	20-MAY-1992	58000.00	MATH
243	Newell	15-JUL-1997	65500.00	CMPSCI
389	Lessing	04-APR-1988	40250.00	ENG

Student Table:

Stu_ID	Stu_Lname	Stu_Major	Stu_CredHrs	Stu_GradePts	Prof_ID
2001	Parker	CMPSCI	52	160	243
2166	Smith	ENG	30	75	389
3200	Garcia	MATH	62	248	123
4520	Smith	CMPSCI	45	157	NULL