Homework SourseFor Problems 14 using the STORES Database formulate SQL quer
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
| 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 |
Solution
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
