|
Homework # 6 - Answer Key SQL 2 Due: 3/13/2001 |
70-455 Information Resources Management 3/6/2001 50 points |
Using the same database that was used for HW #5, write SQL for the following. The number of points is indicated.
1. Create the Employee table. (2)
Create Table Employee
(EmpNo INTEGER,
EmpFirstName VARCHAR(25),
EmpLastName VARCHAR(25),
EmpPhone CHAR(12),
SupEmpNo INTEGER,
EmpCommRate DECIMAL (5,2),
PRIMARY KEY (EmpNo),
FOREIGN KEY (SupEmpNo) REFERENCES Employee)
2. Create the OrdLine table. (2)
Create Table OrdLine
(OrdNo INTEGER,
ProdNo INTEGER,
Qty INTEGER NOT NULL
PRIMARY KEY (OrdNo, ProdNo),
FOREIGN KEY (OrdNo) REFERENCES Order,
FOREIGN KEY (ProdNo) REFERENCES Product)
3. Insert two people into the employee table. One should be the other's supervisor. Explain whether or not the order in which these two inserts are completed is significant. (3)
Insert Into Employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, SupEmpNo, EmpCommRate)
VALUES (100, 'George W', 'Bush', '412-555-1234',,15)
Insert Into Employee (EmpNo, EmpFirstName, EmpLastName, EmpPhone, SupEmpNo, EmpCommRate)
VALUES (101, 'Dick', 'Cheney', '412-555-1234',100,15)
The supervisor must be inserted before the employee because of the foreign key.
4. Add yourself to the customer table. (2)
Insert Into Customer
VALUES (1500, 'Kevin', 'Stolarick', '5300 Forbes Ave', 'Pittsburgh',. 'PA', 15213, 250)
5. Create an order for yourself using the employee (not the supervisor) added in #3. (2)
Insert Into Order
VALUES (5555,'03/13/2001',1500,101,'Bill Clinton', '1300 Harlem Ave', 'New York', 'NY', 10034)
6. Add two products to the OrdLine table using the order created in #5. (2)
Insert Into OrdLine
VALUES (5555,11111,3)
Insert Into OrdLine
VALUES (5555,22222,15)
7. Increase the price by 15% for all products provided by "MicroSquishy". (2)
Update Product
Set ProdPrice = ProdPrice * 1.15
Where ProdMfg = 'MicroSquishy'
8. Change the address (street, city, state, zip) for the customer added in #4 without using the customer number. (2)
Update Customer
Set CustStreet = '1300 Pennsylvania Ave', CustCity = 'Washington', CustState = 'DC', CustZip = '12001'
Where CustFirstName = 'Kevin' AND CustLastName = 'Stolarick'
9. Delete the rows added in #4, #5, and #6. Explain whether or not the order in which these three deletes are completed is significant. (3)
Delete From OrdLine
Where OrdNo = 5555
Delete From Order
Where OrdNo = 555
Delete From Customer
Where CustNo = 1500
The deletes should be done is this order. If I delete customer first, the result could be an order for a customer that doesn't exist. If order is deleted before order line, the order line table would have entries for a non-existent order.
10. Reduce by 10% the price of all products that have not been ordered since the beginning of the year, whose quantity on hand (ProdQOH) is greater than 10, and that have a delivery scheduled to ship by the end of March. (2)
Update Product
Set ProdPrice = ProdPrice * .90
Where ProdQOH > 10 AND ProdNextShipDate < '03/31/2001' AND ProdNo NOT IN
(Select OL.ProdNo From OrdLine as OL, Order as O
Where OL.OrdNo = O.OrdNo AND O.OrdDate > '01/01/2001')
11. Create a new table, InternetOrder, that has the same structure of the order table. (2)
Create Table InternetOrder
(OrdNo INTEGER,
OrdDate DATE,
CustNo INTEGER NOT NULL,
EmpNo INTEGER
OrdName VARCHAR(100),
OrdStreet VARCHAR(100),
OrdCity VARCHAR(50),
OrdState CHAR(2),
OrdZip CHAR(10),
PRIMARY KEY (OrdNo),
FOREIGN KEY (CustNo) REFERENCES Customer)
12. Add to the InternetOrder table all rows from the order table that were placed on the internet (employee number will be null). (3)
Insert Into Internet Order
Select * from Order Where EmpNo IS NULL
13 Change the InternetOrder table to include an additional column, IP address, and remove employee number from it. (3)
ALTER TABLE InternetOrder
(ADD IPAddr CHAR(19),
DROP EmpNo)
14 Using the new InternetOrder table, calculate commission savings realized because of internet ordering. Assume that average employee commission that would have been paid if an employee had taken the order is 3%. (3)
Select SUM(OL.Qty * P.ProdPrice * 0.03)
From InternetOrder as IO, OrdLine as OL, Product as P
Where IO.OrdNo = OL.OrdNo AND OL.ProdNo = P.ProdNo
15. Completely remove the Internet Order table from the database. (2)
DROP TABLE InternetOrder
Write the queries for the five questions that you did not complete for homework #5.
(3 points each) Keep the numbers from homework #5.