Question

consider the following relational database that records details of parts and suppliers. Primary keys are underlined and the Foreign Keys are identified with (FK).

Suppliers (sid, sname, address, credit)
Parts (pid, pname, color)
Catalog (sid (FK), pid (FK), cost)

The credit attribute denotes the size of the supplier’s credit account. The Catalog relation lists the prices charged for parts by Suppliers.

Which of the following queries returns the snames of suppliers who supply every part? Select one or more: a. SELECT sname FROM suppliers NATURAL JOIN catalog GROUP BY sname HAVING COUNT(pid) = (SELECT COUNT (pid) FROM parts) b. SELECT DISTINCT sname FROM suppliers NATURAL JOIN catalog NATURAL JOIN parts c. SELECT sname FROM suppliers NATURAL JOIN catalog C WHERE sid NOT IN (SELECT C1.sid FROM catalog C1 WHERE C1.sid = C.sid AND C1.pid = parts.pid) d. SELECT sname FROM suppliers NATURAL JOIN catalog C WHERE NOT EXISTS (SELECT pid FROM parts WHERE NOT EXISTS (SELECT C1.sid FROM catalog C1 WHERE C1.sid = C.sid AND C1.pid = parts.pid)) e. None of the other options.

Answer 1

Suppliess ( sid, sname, address; eredet) Parts (pid, pnome, color) Catalay ( sid (FK), PID (FK), Cost ) i consider ca SELECT Snome from suppliers Natural Jcin Catalog Group by shame Having Ceemit (rad) = ( SELECT count (pid) from parts) SELECT count (pid) from parts This query counts the total no. of pid in parts and catalog Then suppliers table Natural JOIN where It will recturn shame who supply every item. (1) Conside (6) SELECT Distinct shame From supplies Natural join Colalog natuwal join ports. let's take example :- Catalog side pid Cost pid Ports prame color Redl red - - 2 2 3 30 Reda Green! Reda red Green Red 2 4 2 2 2 20 30 suppliers. Isid Shame address credit klaus New orleance 50000 2 Telijah New orleance 40270 3 Marcel 500 New crleaned App! Natural join by natural joining of it return table is =) Snome Klaus So but both are not elijah Supply all parts this query is not answer. SELECT sname from supplies natural join Catalog Where sid NOT IN (SELECT Clusid from Cataloge WOHERE 'Ci sida Cosid AND.Ct.pid = posts, pidly query will generafe esses because these is emknown column 'posts.id in this query. we cannot use column of other table without jerning them of appling ang other operation SELECT Shame FROM supplies Natural join Catalogo where NOT Exist (SELECT pid from parts cohere AIOT Exist (SELECT closid from Catalog Ct whese Closid=c.sid AND r. pid = parts opid ))) It will return all suppliers who supply all parts but it will return multiple time it will return for each parts. eashome Ang