Please answer the following using SQL code 5. Get part names for parts that are
ID: 3835539 • Letter: P
Question
Please answer the following using SQL code
5. Get part names for parts that are not supplied for job J3. (Use only sub query)
6. Get supplier numbers for suppliers with status lower than that of supplier S1. (use only sub-query)
7. Get supplier numbers and names for suppliers whose status is greater than status values of all suppliers in located Paris. (use only exists sub-query)
8. Calculate each supplier’s total sales quantity and get the sales person’s name if the sales person supplies parts more than 1000 units in total.
PROJECT enum iname city Sorter Paris Punch Rome j3 Reader Athens i4 Console Athens Collator London Terminal Oslo Tape London PART p num pname color weight city Nut Red 12 London P2 olt Green 17 Paris p3 Screw Blue 17 Rome p4 14 London Screw Red 12 Paris p5 Cam Blue p6 Cog Red 19 London SUPPLIER s, num s name status city S1 Smith 20 London S2 Jones 10 Paris S3 Blake 30 Paris 54 Clark 20 London S5 Adams 30 Athens SPJ Saenump num num qty 51 p 200 51 P1 i4 S2 p3 400 52 p3 12 200 S2 p3 200 S2 p3 D4 500 52 15 600 S2 p3 400 2 p 800 52 ps 12 100 S3 p3 200 p4 54 6 13 300 S4 p6 300 5 pl i4 100 55 2 12 200 55 4 A 800 5 p5 500 S5 p5 100 5 p6 12 200 5 500Explanation / Answer
[5]
SELECT pname
FROM PART
WHERE p_num IN (SELECT p_num
FROM SPJ
WHERE j_num NOT LIKE '%j3%;
);
[6]
SELECT s_name
FROM SUPPLIER
WHERE status < (SELECT status
FROM SUPPLIER
WHERE s_num = 's1'
);
[7]
SELECT s_num,s_name
FROM SUPPLIER
WHERE EXISTS (SELECT status
FROM SUPPLIER a, SUPPLIER b
WHERE a.city = 'Paris'
AND b.status > a.status
);
[8]
SELECT a.s_num,
DRV.QUANTITY,
CASE DRV.QUANTITY > 1000
THEN a.s_name
END SUPPLIER_NAME
FROM (SLECT s_num,sum(qty) QUANTITY
FROM SPJ
group by s_num
) DRV
JOIN SUPPLIER a ON (DRV.s_num = a.s_num)