Question

Questions in this part ask you to write SQL queriesthat would retrieve certain information from the tables.

For each question, write the SQL query that would display the information asked by the question.

1. Display information about parts that have their weights greater than the average weight of all parts.  

PNO	PNAME
P2	BOLT
P3	SCREW

2. Find supplier numbers who supply any screws (i.e., such that the part name is ‘screw’).  The constraint is that you may not use any join or cross (product).  (Hint: Use subquery(ies).)

SNO

S1

S3

S4

S5

3. Find supplier numbers of suppliers located in London who supply screws (i.e., such that the part name is ‘screw’) or bolts.  The constraint is that you may not use any join or product, but use a subquery(ies). (Hint: Use subquery(ies).)

SNO

S1

S2

S4

4. Display the total number of orders (over all parts) and the minimum, average, and maximum quantity of individual orders (each order is a tuple in sp).

COUNT(PNO)	SUM(qty)	MIN(qty)	AVG(qty)	MAX(qty)
13	3200	100	246.1538	400

5. For each part find the total, minimum, average, and maximum quantity of individual orders (each order is a tuple in p) and the total number of orders. Display the part number and for that part: the number of orders, total, minimum, average, maximum quantity of individual orders for that part and also the average quantity on order over all parts. Furthermore, display information in reverse order by part number.

PNO	COUNT(PNO)	SUM(qty)	MIN(qty)	AVG(qty)	MAX(qty)	AVG(qty)
P5	1	100	100	100.0000	100	246.1538
P4	2	400	200	200.0000	200	246.1538
P3	3	1000	200	333.3333	400	246.1538
P2	4	1000	200	250.0000	400	246.1538
P1	3	700	100	233.3333	300	246.1538

S                                                                              P

sNo (PK)	sName	sStatue	sCity
S1	Smith	20	London
S2	Jones	10	London
S3	Blake	30	Paris
S4	Clark	20	London
S5	Adams	30	Athens

pNo (PK)	pName	pColor	pWeight
P1	Nut	Red	12
P2	Bolt	Blue	17
P3	Screw	Green	17
P4	Screw	Red	14
P5	Cam	Blue	12

SP

sNo (PK) (FK)	pNo (PK) (FK)	qty
S1	P1	300
S1	P2	200
S1	P3	400
S1	P4	200
S1	P5	100
S2	P1	300
S2	P2	400
S3	P1	100
S3	P2	200
S3	P3	200
S4	P2	200
S4	P3	400
S5	P4	200

Figure 2.  Content of the Supplier-Parts DB Tables

                                   

Answer 1

If you have any doubts, please give me comment...

1)

SELECT PNO, PNAME

FROM P

WHERE pWeight>(

SELECT AVG(pWeight)

FROM P

);

2)

SELECT SNO

FROM S

WHERE sNo IN(

SELECT sNo

FROM SP

WHERE pNo IN(

SELECT pNo

FROM P

WHERE pName = 'Screw'

)

);

3)

SELECT SNO

FROM S

WHERE sCity = 'London' AND sNo IN(

SELECT sNo

FROM SP

WHERE pNo IN(

SELECT pNo

FROM P

WHERE pName = 'Screw' OR pName ='Bolt'

)

);

4)

SELECT COUNT(PNo), SUM(qty), MIN(qty), AVG(qty), MAX(qty)

FROM SP;

5)

SELECT COUNT(PNo), SUM(qty), MIN(qty), AVG(qty), MAX(qty)

FROM SP

GROUP BY PNo

ORDER BY PNo DESC;