ITSE 2309 LAB 2 More SQL Queries and Modification

Description

ITSE 2309 LAB 2 More SQL Queries and Modification

Oracle 11g SQL–Chapters- 3,6,8,9,11,12,
You will continuing using items created in Lab 1

Lab 2a — Problems 1–4,
1. For each customer, list each stock item ordered,
1) the manufacturer,
2) the quantity ordered, and
3) 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.
Submit/hand in Output from SQL query

2. List all orders with a shipping date between December 25, 1999 and January 5, 2000
Include
1) the Order Number,
2) Order Date,
3) Customer company name, and
4) Shipping Date.
Order by
Customer Company Name and Order Number.
Submit/hand in Output from SQL query

3. Count the number of customers who do not have any orders placed.
Submit/hand in Output from SQL query

4. List all customers –
I) Who are ordering equipment whose description begins with ‘tennis’ or ‘volleyball’.
II )Include
1) Customer number,
2) Stock number, and
3) Description.
Submit/hand in Output from SQL query
Do not repeat any rows.

Lab 2b Problems 5, 6, 7 and 8
5. Use the following SQL CREATE commands to CREATE the following tables in your
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
);
Submit/Hand in: Print out of the Create commands, the system response and a DESCRIBE of the tables created.

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.
Submit a
Listing of each INSERT command,
The systems response and the resulting tables after the INSERTS are completed
(Example: SELECT * FROM Student;).

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.
Submit/hand in : A listing of the DELETE statements.
The answers to questions a. b. and c.
A listing of the two tables after the deletes have run.

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
Submit/hand in : A listing of the UPDATE statements
A listing of the two tables after the UPDATEs have run.