Question

Write the following four queries in MySQL using the supplies database. Please make sure to submit a screenshot of the output of each of your query. (100 points)

1. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part).

2. For each part, find the sname of the supplier who charges the most for that part.

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

4. For every supplier that only supplies red parts, print the name of the supplier and the total number of parts that they supply.

Answer 1

1.

Select Supplier.sid from Supplier inner join SP on Supplier.sid = SP.sid inner join Part on SP.pid = Part.pid group by Supplier.sid having price> avg(price);

2.

Select sname from Supplier inner join SP on Supplier.sid = SP.sid inner join Part on SP.pid = Part.pid group by Part.pid having price = max(price);

3.

Select Supplier.sid from Supplier inner join SP on Supplier.sid = SP.sid inner join Part on SP.pid = Part.pid group by Supplier.sid having Part.color = 'red' and part.color = 'green';

4.

Select sname,count(Parts.pid) from Supplier inner join SP on Supplier.sid = SP.sid inner join Part on SP.pid = Part.pid group by sname having Part.color = 'red' ;

Do ask if any doubt. Please upvote.