More SQL Queries and Modification Commands
For Problems 1-4, using the STORES Database, formulate SQL queries, Hand in a listing of each query and its output.
1. 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:
Order the output by Company and Description.
2. 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. Count the number of customers who do not have any orders placed.
4. 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. 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
);
6. 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 |
BE SURE TO ISSUE A COMMIT AFTER TABLE MODIFICATION COMMANDS HAVE BEEN RUN SUCCESSFULLY.
7. Perform the following SQL DELETE statements. Be sure to do them in order. Issue a COMMIT command after all DELETEs have run.
a.Try to delete Professor 389. What message do you get? ___________________________
b.Delete Student 2166.
c.Now Delete Professor 389. Explain why the first attempt in a. was unsuccessful, and this time the DELETE was successful.
8. Perform the following UPDATE commands. Issue a COMMIT command after all UPDATEs have run.
a.Replace the value of the Prof_ID for Student 4520 with 243.
b.Add 10% to the salary for each professor