S1 Smith 20 London S2 Jones 10 Paris S3 Blake 30 Paris S4 Clark 20 London S5 Ada
ID: 3619406 • Letter: S
Question
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
P PNO PNAME COLOR WEIGHT STOREDCITY
P1 Nut Red 12 London
P2 Bolt Green 17 Paris
P3 Screw Blue 17 Rome
P4 Screw Red 14 London
P5 Cam Blue 12 Paris
P6 Cog Red 19 London
SP SNO PNO QTY
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S2 P1 300
S2 P2 400
S3 P2 200
S4 P2 200
S4 P4 300
S4 P5 400
1. List all the PNOs in table SP without duplication.
2. List SNO for suppliers in Paris (City’s value equals to ‘Paris’) with a status > 20.
3. List SNO and STATUS for suppliers in Paris, in descending order of STATUS.
4. List all columns about suppliers and parts if supplier’s city is the same as part’s stored city.
5. Count the number of suppliers for P2..
1. List SNO for all suppliers who ships more than one part..
2. List supplier names for suppliers who supply at least one red part.
3. List supplier names for suppliers who do not supply part P2.
4. For each part shipped, get its PNO and the total shipment quantity of the PNO.
Explanation / Answer
II 1) Select distinct PNO from SP; 2) Select SNO from S where CITY = ‘paris’ and STATUS > 20; 3) Select SNO, STATUS from S where CITY = ‘paris’ order by STATUS DESC; 4) Select * from S,P where S.CITY = P.STOREDCITY; 5) Select count * SNO from SP where SP.PNO = P2; III 2) Select SNAME from S where S.CITY = (select STOREDCITY from P where P.COLOR = ‘Red’); 3) select SNAME from S where (select S.SNO from S MINUS select SNO from SP where PNO = P2);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.