J jnum jname city j1 Sorter Paris j2 Punch Rome j3 Reader At

j_num

jname

city

Sorter

Paris

Punch

Rome

Reader

Athens

Console

Athens

Collator

London

Terminal

Oslo

Tape

London

p_num

pname

color

weight

city

Nut

Red

London

Bolt

Green

Paris

Screw

Blue

Rome

Screw

Red

London

Cam

Blue

Paris

Cog

Red

London

s_num

s_name

status

city

Smith

London

Jones

Paris

Blake

Paris

Clark

London

Adams

Athens

SPJ

s_num

p_num

j_num

qty

200

700

400

200

500

600

400

800

100

200

500

300

100

200

100

200

800

400

500

100

200

500

Note: Qty, status, and weight are numbers.

Exercise

1. Get supplier names and numbers for all suppliers who supplied part P3 and whose name begins with letter A.

2. Get supplier names and numbers for all suppliers whose name (supplier name) begins with letter A and who supplied parts whose name (part’s name) begins with setter S.

3. Get supplier names for suppliers who supplied for job J2. (Use a sub query)

4. Get supplier names for suppliers who supplied parts for jobs only in Athens. (Use sub query)

5. Get part names for parts that are not supplied for job J3. (Use a sub query)

6. Get supplier numbers for suppliers with status lower than that of supplier S1.

7. Get supplier numbers and names for suppliers whose status is greater than status values of all suppliers in located Paris.

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.

9. Get job numbers for jobs whose city is first in the alphabetical list of the job cities.

10. Increase the status values of suppliers by 5 who are located in Paris.

Please hand in the modified table and the query.

11. Change the name to ‘Hammer’ of parts that are Red and located in London and whose name was Screw.

Please hand in the modified table and the query.

12. Delete all jobs in Rome and all corresponding part shipments. (Please use 2 queries)

Please hand in the modified table and the query.

13. Smith moved to Adam’s location. Please update Smith’s city but do not use the city name directly.

14. Please create a “view table” supplier_shipment that shows each supplier and its total shipment quantity. (if the system do not allow you to create a view table then skip this question)

15. Please try to increase Smith’s total shipment by 100 in the view table you created in question 14. Then discuss what happens when you try to update the view table. (if question 14 is successful)

16. Please create a base table supplier_shipment that shows each supplier and its total shipments from S and SPJ tables.

17. Please repeat question 15 on the base table supplier_shipment and discuss the result.

Note: You might need following setting before run your update SQL

SET SQL_SAFE_UPDATES = 0;

j_num	jname	city
j1	Sorter	Paris
j2	Punch	Rome
j3	Reader	Athens
j4	Console	Athens
j5	Collator	London
j6	Terminal	Oslo
j7	Tape	London

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

-- 1. Get supplier names and numbers for all suppliers who supplied part P3 and whose name begins with letter A.

SELECT s_name, s_num

FROM S INNER JOIN SPJ ON S.s_num = SPJ.s_num

WHERE s_name LIKE \'A%\' AND p_num = P3;

-- 2. Get supplier names and numbers for all suppliers whose name (supplier name) begins with letter A and who supplied parts whose name (part’s name) begins with setter S.

SELECT s_name, s_num

FROM (S INNER JOIN SPJ ON S.s_num = SPJ.s_num) INNER JOIN P ON SPJ.p_num = P.p_num

WHERE s_name LIKE \'A%\' AND pname = \'S%\';

-- 3. Get supplier names for suppliers who supplied for job J2. (Use a sub query)

SELECT s_num

FROM S

WHERE s_num IN(

SELECT s_num

FROM SPJ

WHERE j_num = \'j2\'

);

-- 4. Get supplier names for suppliers who supplied parts for jobs only in Athens. (Use sub query)

SELECT s_num

FROM (S INNER JOIN SPJ ON S.s_num = SPJ.s_num) INNER JOIN J ON SPJ.j_num = J.j_num

WHERE J.city = \'Athens\' AND s_num NOT IN(

SELECT s_num

FROM (S INNER JOIN SPJ ON S.s_num = SPJ.s_num) INNER JOIN J ON SPJ.j_num = J.j_num

WHERE J.city <> \'Athens\'

);

-- 5. Get part names for parts that are not supplied for job J3. (Use a sub query)

SELECT pname

FROM P

WHERE p_num NOT IN(

SELECT p_num

FROM SPJ

WHERE p_num = \'j3\'

);

-- 6. Get supplier numbers for suppliers with status lower than that of supplier S1.

SELECT s_num

FROM S

WHERE status < (

SELECT status

FROM S

WHERE s_num = \'s1\'

);

-- 7. Get supplier numbers and names for suppliers whose status is greater than status values of all suppliers in located Paris.

SELECT s_num, sname

FROM S

WHERE status > ALL(

SELECT status

FROM S

WHERE city = Paris

);

-- 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.

SELECT S.s_num, s_name, SUM(qty)

FROM S INNER JOIN SPJ ON S.s_num = SPJ.s_num

GROUP BY S.s_num,s_name

HAVING SUM(qty)>1000;

-- 9. Get job numbers for jobs whose city is first in the alphabetical list of the job cities.

SELECT j_num

FROM J

WHERE j_num IN(

SELECT j_num

FROM J

ORDER BY city

LIMIT 1

);

-- 10. Increase the status values of suppliers by 5 who are located in Paris.

-- Please hand in the modified table and the query.

UPDATE S SET status = status+5 WHERE city =\'Paris\';

SELECT *

FROM S;

-- 11. Change the name to ‘Hammer’ of parts that are Red and located in London and whose name was Screw.

-- Please hand in the modified table and the query.

UPDATE P SET pname = \'Hammer\' WHERE color = \'Red\' AND city =\'London\' AND pname=\'Screw\';

SELECT *

FROM P;

-- 12. Delete all jobs in Rome and all corresponding part shipments. (Please use 2 queries)

-- Please hand in the modified table and the query.

DELETE FROM SPJ WHERE j_num IN (

SELECT j_num

FROM J

WHERE city = \'Rome\'

);

DELETE FROM J WHERE city =\'Rome\';

-- 13. Smith moved to Adam’s location. Please update Smith’s city but do not use the city name directly.

UPDATE S SET city = (SELECT city FROM S WHERE s_name=\'Adams\') WHERE s_name=\'Smith\';

-- 14. Please create a “view table” supplier_shipment that shows each supplier and its total shipment quantity. (if the system do not allow you to create a view table then skip this question)

CREATE VIEW supplier_shipment AS

SELECT s_num, SUM(qty) AS qty

FROM SPJ

GROUP s_num;

-- 15. Please try to increase Smith’s total shipment by 100 in the view table you created in question 14. Then discuss what happens when you try to update the view table. (if question 14 is successful)

UPDATE supplier_shipment SET qty = qty +100;

-- 16. Please create a base table supplier_shipment that shows each supplier and its total shipments from S and SPJ tables.

DESCRIBE supplier_shipment;