Suppliers(sid: integer, sname: string, address: string)

Parts(pid: interger, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in SQL:

(a) (2 points) Write SQL command to create this tables.

(b) (2 points) Write insert command to insert a single row for each table. [Hint: choose arbitrary values with proper domain]

(c) (2 points) Increase price of all green parts by 5% sold by supplier named Gordon. [Hint: update]

(d) (3 points) Find pnames of parts for which there is some suppliers.

(e) (3 points) Find the sid of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part).

(f) (4 points) Find the names of the suppliers who supply a red part that costs less than 100 dollars. (Hint: nested subquery)

(g) (4 points) For every suppliers, print sid, name and price of the most expensive part that she supplies. [HItns: Group by]

Suppliers(sid: integer, sname: string, address: string) Parts(pid: interger, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in SQL (a) (2 points) Write SQL command to create this tables (b) (2 points) Write insert command to insert a single row for each table. [Hint: choose arbitrary values with proper domain (c) (2 points) Increase price of all green parts by 5% sold by supplier named 'Gordon Hint: update (d) (3 points) Find pnames of parts for which there is some suppliers (e) (3 points) Find the sid of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part) (Hint: nested subquery) [HItns: Group by] (f) (4 points) Find the names of the suppliers who supply a red part that costs less than 100 dollars (g) (4 points) For every suppliers, print sid, name and price of the most expensive part that she supplies