1. Consider the following schema: Suppliers(sid: integer, sname: string, address

Question

1. Consider the following schema:

Suppliers(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in SQL:

a) what does The following query returns:

SELECT DISTINCT C.sid

FROM Catalog C

WHERE C.cost > ( SELECT AVG (C1.cost)

FROM Catalog C1

WHERE C1.pid = C.pid )

b) Find the sids of suppliers who supply only red parts

c) what does The following query returns:

SELECT S.sname, COUNT(*) as PartCount

FROM Suppliers S, Parts P, Catalog C

WHERE P.pid = C.pid AND C.sid = S.sid

GROUP BY S.sname, S.sid

HAVING EVERY (P.color=’Green’)

d) For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.

Explanation / Answer

a) what does The following query returns:

SELECT DISTINCT C.sid
FROM Catalog C
WHERE C.cost > ( SELECT AVG (C1.cost)
FROM Catalog C1
WHERE C1.pid = C.pid )

It gives the sid of suppliers who sell products at a cost above the average price of the product.

=======================
b) Find the sids of suppliers who supply only red parts

SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid AND P.color = 'red'

----------------------
c) what does The following query returns:

SELECT S.sname, COUNT(*) as PartCount
FROM Suppliers S, Parts P, Catalog C
WHERE P.pid = C.pid AND C.sid = S.sid
GROUP BY S.sname, S.sid
HAVING EVERY (P.color=’Green’)

This query returns the names of suppliers who supply only Green part
---------------------------

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.