BASIC SQL. Consider the following schema: Suppliers ( sid: integer, sname: strin

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 charged 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 snames of suppliers who supply every part.

3) Find the snames of suppliers who supply every red part.

4) Find the sids of suppliers who supply only red parts.

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

Explanation / Answer

1) Find the pnames of parts for which there is some supplier.
ans:
SELECT p.pname FROM Parts AS p
WHERE EXISTS (
SELECT * FROM Catalog AS c
WHERE c.pid = p.pid)
2) Find the snames of suppliers who supply every part.
ans:
SELECT S.sname FROM Suppliers S
WHERE NOT EXISTS (( SELECT P.pid FROM Parts P )
EXCEPT
( SELECT C.pid FROM Catalog C
WHERE C.sid = S.sid ))
3) Find the snames of suppliers who supply every red part.
ans:
SELECT s.sid, s.sname
FROM Suppliers AS s, Catalog AS c, Parts AS p
WHERE s.sid = c.sid
AND p.pid = c.pid AND p.color = ‘red’
GROUP BY s.sid, s.sname
HAVING COUNT(*) = (SELECT COUNT(*) FROM Parts AS p1
WHERE p1.color=‘red’)
4)Find the sids of suppliers who supply only red parts.
ans:
SELECT DISTINCT C.sid
FROM Catalog C
WHERE NOT EXISTS ( SELECT *
FROM Parts P
WHERE P.pid = C.pid AND P.color <> ‘Red’ )
5) Find the sids of suppliers who supply a red part and a green part.
ans:
(SELECT DISTINCT c1.sid AS SID
FROM Parts AS p1, Catalog AS c1
WHERE p1.pid = c1.pid AND p1.color = ‘green’)
INTERSECT (
SELECT DISTINCT c2.sid AS SID
FROM Parts AS p2, Catalog AS c2
WHERE p2.pid = c2.pid AND p2.color = ‘red’)
or
SELECT DISTINCT c.sid
FROM Catalog c, Parts p
WHERE c.pid = p.pid and p.color = 'red'
INTERSECT
SELECT DISTINCT c.sid
FROM Catalog c, Parts p
WHERE c.pid = p.pid and p.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.