Question

1. Answer the questions with respect to the table below. users gender id name age occ_id city_id John 25 M 3 1 2. Sara 20 F 3 4 3 Victor 31 M 2 LO 4 Jane 27 F 1 3 occupation id name 1 2 Software Engineer Accountant Pharmacist Library Assistant 3 City id name 1 2 Halifax Calgary Boston 3 4 New York 5 Toronto

Answer 1

1.

CREATE TABLE users_new LIKE users;

2.

INSERT INTO users_new SELECT * FROM users;

3.

ALTER TABLE users ADD salary INT;

UPDATE users, occupation
SET salary =
(
case
when occupation.name = 'Software Engineer' then 80000
when occupation.name = 'Accountant' then 75000
when occupation.name = 'Pharmacist' then 90000
end
)
WHERE users.occ_id= occupation.id;