Database Question: Consider the following relations: Supplier (sid, sname, addre
ID: 3834953 • Letter: D
Question
Database Question:
Consider the following relations: Supplier (sid, sname, address) Parts(pid, pname, color) Catalog(sid, pid, cost) The Catalog relation lists the prices charged for parts by Suppliers. Translate the following queries into SQL. (a) Find the sids of the suppliers who supply some red part and some green part. (b) Find the sids of the suppliers who supply every red part. (c) For each supplier that supplies more than one part, find the number of parts it supplies and the total cost of those partsExplanation / Answer
a) Find the sids of the suppliers who supply some red part and some green part.
Ans)
We can write the query in different ways. I have written two solutions one is using SOME and other using IN clause.
query-1:
select distinct sid from catalog inner join parts on catalog.pid = parts.pid and parts.color = SOME('red', 'green');
query-2:
SELECT sid from Catalog INNER JOIN Parts on Catalog.pid = Parts.pid and Parts.color in ('red', 'green');
b) Find the sids of the suppliers who supply every red part
Ans)
We can write a subquery that returns all pid's with color red and in the main join query we are checking if there is any sid who has all pids with red parts. Thats why we have used ALL clause so pids joined should match all red part pids.
query:
select distinct sid from Catalog inner join parts on catalog.pid = parts.pid and parts.pid = ALL (select distinct pid from parts where color = 'red');
c) For each supplier that supplies more than one part, find the number of parts it supplies and the total cost of those parts
Ans)
Here we have written a sub query that matches sids only when they supply more than one part assuming catalog will have multiple entries for sid only if they supply more than one part. The sids returned from subquery will then have the count of parts and total cost of those parts.
query:
select count(pid) as "Number of Parts", sum(cost) as "Total Cost of Parts" from catalog where sid in (select sid from catalog where count(sid)>1);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.