*** SQL Databases. Please be concise and show full explanation. Please help. Show your own work. Has to run on Oracle SQL Developer. ***

(a) 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).

(b) Find the sids of suppliers who supply a red part and a green part.

(c) For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that it supplies.

Here are the create tables to work with each part:

create table suppliers( sid number(9,0) primary key, sname varchar2(30), address varchar2(40) ); create table parts( pid number(9,0) primary key, pname varchar2(40), color varchar2(15) ); create table catalog( sid number(9,0), pid number(9,0), cost number(10,2), primary key(sid,pid), foreign key(sid) references suppliers, foreign key(pid) references parts ); 

The tables with data are below:

Suppliers

Parts

Catalog:

Here are the queries for each part and the error:

(5)

SELECT DISTINCT c.sid FROM Catalog AS c, (SELECT c1.pid AS avg pid, AVG(c1.cost) AS avg cost FROM Catalog AS c1 GROUP BY c1.pid) AS TEMP WHERE c.pid = TEMP.avg pid AND c.cost > TEMP.avg cost;

--Error--

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 141 Column: 14

(8)

(SELECT DISTINCT c1.sid AS SID FROM Parts AS p1, Catalog AS c1 WHERE p1.pid = c1.pid AND p1.color = Green) INTERSECT (SELECT DISTINCT c2.sid AS SID FROM Parts AS p2, Catalog AS c2 WHERE p2.pid = c2.pid AND p2.color = Red);

--Error--

ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action: Error at Line: 151 Column: 13

(10)

SELECT s.sid, s.sname, COUNT(c.pid) FROM Suppliers AS s, Catalog AS c WHERE s.sid = c.sid AND s.sid IN ((SELECT DISTINCT c1.sid AS SID FROM Catalog AS c1, Parts AS p1 WHERE c1.pid = p1.pid AND p1.color = Green) EXCEPT (SELECT DISTINCT c2.sid AS SID FROM Catalog AS c2, Parts AS p2 WHERE c2.pid = p2.pid AND p2.color != Green)) GROUP BY s.sid, s.sname;

--Error--

ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 163 Column: 16

How would you go about fixing these small errors? This has to work in Oracle SQL Developer. Please show sample output that it works too.

Exercise 5.2 Consider the following schema: Suppliers (sid: integer, sname: string, address: string) Parts(pid: integer, 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