Consider the following schema:
Employee (ID,Name,Address)
Supplier (ID,Name)
PurchaseOrder (OrderID,EmpIssuerID,SupplierID,Date)
PurchaseItem (ItemID,OrderID,ItemName,ItemCost)
The first two schemas are self-explanatory. Each tuple in PurchaseOrder describes a purchase
order issued by a particular employee to a particular supplier. The last relation, PurchaseItem,
describes each ordered item and its relationship to the corresponding order.
Write the following queries using the SQL language:
(a) Names of employees who have made a purchase order that contains an item costing more
than $150.
(b) For each supplier, list the name and the total cost of all items ever ordered from this
supplier.
(c) Number of orders such that the total cost of items in each of those orders is over $200.
(d) Names of employees who have issued a purchase order to every supplier.
(a) Names of employees who have made a purchase order that contains an item costing more
than $150.
Solution:
SELECT E.Name
FROM Employee E, PurchaseOrder P, PurchaseItem I
WHERE E.ID=P.EmpIssuerID AND P.OrderID = I.OrderID
AND ItemCost > 150
(b) For each supplier, list the name and the total cost of all items ever ordered from this
supplier.
Solution:
SELECT S.Name, SUM(I.ItemCost)
FROM Supplier S, PurchaseOrder P, PurchaseItem I
WHERE S.ID=P.SupplierID AND P.OrderID = I.OrderID
GROUP BY P.SupplierID
(c) Number of orders such that the total cost of items in each of those orders is over $200.
Solution:
SELECT COUNT(P.OrderID)
FROM PurchaseOrder P
WHERE 200 < ( SELECT SUM(I.ItemCost)
FROM PurchaseItem I
WHERE I.OrderID = P.OrderID
GROUP BY I.OrderID )
(d) Names of employees who have issued a purchase order to every supplier.
Solution:
SELECT E.Name
FROM Employee E
WHERE NOT EXISTS (
( SELECT S.Id
FROM Supplier S )
EXCEPT
( SELECT P.SupplierID
FROM PurchaseOrder P
WHERE E.Id = P.EmpIssuerID)
)
You might also like to view...
The __________ loop allows the user to decide on the number of iterations.
a. counter controlled loop b. dynamically executed loop c. user controlled loop d. infinite loop
Answer the following questions true (T) or false (F)
1. In a switch statement, the default case is always executed. 2. Not including the break statements within a switch statement results in a syntax error.
To customize the elements of your chart you can use the ___________________ contextual tab(s).
A. CHART LAYOUT B. CHART TOOLS DESIGN and FORMAT C. CUSTOMIZE CHART D. CHART DESIGN and CREATION
What are the advantages and drawbacks of multiversion timestamp ordering in comparison with ordinary timestamp ordering?
What will be an ideal response?