Basic SQL Consider the following schema: Suppliers( sid: integer, sname: string,

Question

Basic SQL

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 charted for parts by Suppliers. Write the following queries in SQL:

1. Find the pnames of parts for which there is some supplier.

2. Find the sids of suppliers who charge more for some part thatn the average cost of that part( averaged over all the suppliers who supply that part).

3. Find the sids of suppliers who supply a red part and a green part.

Explanation / Answer

1)   SELECT DISTINCT P.pname
FROM Parts P, Catalog C
WHERE P.pid = C.pid

2) SELECT DISTINCT C.sid
FROM Catalog C

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

3)   SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid AND P.color = ‘Red’
INTERSECT
SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color = ‘Green’

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

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