Question

Consider the following schema:

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

In a plain text file, write the following queries in SQL:

1. Find the names of all suppliers who supply a green part.
2. Find the names of all suppliers who are from Illinois.
3. Find the names of all suppliers who sell a red part costing less than $100.
4. Find the names and colors of all parts that are green or red.

In writing these queries you may make the following assumptions:

a. Each part has only one color.
b. The cost field is a real number with two decimal places (e.g., 100.25, 93.00).
c. The sid field in Suppliers and the sid field in Catalog refer to the same field.
d. The pid field in Parts and the pid field in Catalog refer to the same field.

Answer 1

Answer:

Find the names of all suppliers who supply a green part.

Select s.sname,p.color from Suppliers s
join Catalog c on c.sid=s.sid
join Parts p on p.pid = c.pid
where p.Color = "Green" order by s.sname;
/*The above query will return the names of suppliers who supply green part*/

Find the names of all suppliers who are from Illinois.

Select * from Suppliers where address like '%illinois%'
/*The above query will return names of all suppliers who are from Illinois*/

Find the names of all suppliers who sell a red part costing less than $100.

Select s.sname,p.color from Suppliers s
join Catalog c on c.sid=s.sid
join Parts p on p.pid = c.pid
where p.Color = "red" c.cost < 100.00 order by s.sname;
/*The above query will return the names of all suppliers who sell a red part costing less than $100*/

Find the names and colors of all parts that are green or red.

Select pname,color from Parts where color in ('green','red');
/*The above query will return the names and colors of all parts that are green or red*/

Thanks and all the best. Let me know in case if you have any doubts.