Question

For Problems 1–4, 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

Answer 1

create table Professor

(Prof_ID integer,

constraint pk_Professor primary key(Prof_ID),

Prof_Lname varchar(15) not null,

Prof_Hiredate date,

Prof_Sal decimal(8,2),

Prof_Dept varchar(6));

create table Student

(Stu_ID integer ,

constraint pk_Student primary key(STU_ID),

Stu_Lname varchar(15) not null,

Stu_Major varchar(6),

Stu_CredHrs integer,

Stu_GradePts integer,

Prof_ID interger,

constraint fk_Student_Prof_ID foreign key (Prof_ID)  references Professor(Prof_ID));

6. insert into professor values('123','Hilbert','20-MAY-1992','58000.00',’MATH’);

insert into professor values('243','Newell','15-JULY-1997','.65500.00',’CMPSCI’);

insert into professor values('389','Lessing','4-APR-1988','40250.00',’ENG’);

insert into student values('2001','Parker','CMPSCI','52',’160’,’243’);

insert into student values('2166','Smith','ENG','30',’75’,’389’);

insert into student values('3200','Garcier','MATHS','62',’248’,’123’);

QUERY FOR ORACLE SQL