1)
A…
CREATE VIEW TopLevelCust AS
SELECT CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit
FROM Customer
WHERE CreditLimit >=10000
;

B…
SELECT CustomerNum, CustomerName
FROM TopLevelCust
WHERE Balance>10000 AND CreditLimit>=10000
;

C…
SELECT Customer.CustomerNum, Customer.CustomerName, Customer.Street, Customer.City, Customer.State, Customer.Zip, Customer.Balance, Customer.CreditLimit
FROM Customer
WHERE (((Customer.Balance)>10000) AND ((Customer.CreditLimit)>=10000));

2)
A…
CREATE VIEW PartOrder AS
SELECT PartNum, Description, Price, OrderNum, OrderDate, NumOrdered, QuotedPrice
FROM Part
;

B…
CREATE VIEW TopLevelCust AS
SELECT PartNum, Description, OrderNum, QuotedPrice
FROM Part
WHERE QuotedPrice>100
;

C…
SELECT Part.PartNum, Part.Description, Part.Price, Orders.OrderNum, Orders.OrderDate, OrderLine.NumOrdered, OrderLine.QuotedPrice
FROM Part INNER JOIN (Orders INNER JOIN OrderLine ON Orders.OrderNum = OrderLine.OrderNum) ON Part.PartNum = OrderLine.PartNum
WHERE (((OrderLine.QuotedPrice)>100));

3)
CREATE VIEW OrdTot AS
SELECT OrderNum, NumOrdered*QuotedPrice
AS OrderTotal INTO OrdTot
FROM OrderLine;

4)
A… CREATE INDEX PartIndex1 ON OrderLine(PartNum);
B… CREATE INDEX PartIndex2 ON Part(Warehouse);
C… CREATE INDEX PartIndex3 ON Part(Warehouse, Class);
D… CREATE INDEX PartIndex4 ON Part(Warehouse, OnHand DESC);

5)
DROP INDEX PartIndex3;

6)
CHECK (Class IN (SG, HW, AP)) FOREIGN KEY (PartNum) PRIMARY KEY (PartNum) REFERENCES Part

7)
ALTER TABLE Part ADD Allocation(0);
SELECT SUM(NumOrdered) AS Allocation
FROM Part, OrderLine
PartNum = KV29
WHERE PartNum= PartNum
;

8)
ALTER TABLE Part
MODIFY Warehouse CHAR(2)
UPDATE Part SET Warehouse=’1a’
WHERE Warehouse=’1’
;

9)
ALTER TABLE Part DELETE Allocation
;

10)
DROP Part Table

11)
DELIMITER $$