Question

Considering the tables

Supplier (sid, name, address)
Supplies (sid, pid)
Parts (pid, partname, color)

Assume that the part identification is done by a single letter: Q, X, Z, or T. For exam someone could say they just bought part Q and part X.

Write the SQL query for:

a) Find the total number of suppliers for part ids (T) and X

b) Find out if "IBM" supplies part (T)

c) Find the name of the suppliers that do not supply "computers"

d) Find the color of part number (T)

Answer 1

a) SELECT COUNT(*)

FROM Supplies

WHERE sid=T OR sid=X;

b) SELECT sid

CASE

WHEN sid=IMB AND pid=T

THEN 'YES'

ELSE 'No'

END AS 'check'

FROM Supplies

c) SELECT pid

WHERE partname != computers

FROM Parts

LEFT JOIN Supplies

ON Parts.pid=Supplies.pid;

d) SELECT color

FROM Parts

WHERE pid=T;

Step-by-step explanation:

a) Here we will use aggregate function along with WHERE and operators to count for specifc part ids

b) This is a CASE query

c) This is again a case query but invloves two separate tables. So, here 1st required values from 1st table are selected and then table is joined with the other table using OUTER JOIN

d) This is again a CASE query. It can be written by calling upon the all the values in one column corresponding to values in another column