SQLize | PHPize | SQLtest

-- -- 1. Create the database -- CREATE DATABASE assignment3_db; -- -- 2. Use the database -- USE assignment3_db; -- 3. Create Occupation Table CREATE TABLE occupation ( id INT PRIMARY KEY, name VARCHAR(50) ); -- 4. Create City Table CREATE TABLE city ( id INT PRIMARY KEY, name VARCHAR(50) ); -- 5. Create Users Table CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT, gender CHAR(1) CHECK (gender IN ('M', 'F')), occ_id INT NOT NULL, city_id INT NOT NULL, FOREIGN KEY (occ_id) REFERENCES occupation(id), FOREIGN KEY (city_id) REFERENCES city(id) ); -- 6. Insert Data into Occupation Table INSERT INTO occupation (id, name) VALUES (1, 'Software Engineer'), (2, 'Accountant'), (3, 'Pharmacist'), (4, 'Library Assistant'); -- 7. Insert Data into City Table INSERT INTO city (id, name) VALUES (1, 'Halifax'), (2, 'Calgary'), (3, 'Boston'), (4, 'New York'), (5, 'Toronto'); -- 8. Insert Data into Users Table INSERT INTO users (id, name, age, gender, occ_id, city_id) VALUES (1, 'John', 25, 'M', 1, 3), (2, 'Sara', 20, 'F', 3, 4), (3, 'Victor', 31, 'M', 2, 5), (4, 'Jane', 27, 'F', 1, 3); CREATE TABLE users_new LIKE users; INSERT INTO users_new SELECT * FROM users; SELECT users.name, occupation.name AS job, CASE WHEN occupation.name = 'Software Engineer' THEN 80000 WHEN occupation.name = 'Accountant' THEN 70000 WHEN occupation.name = 'Pharmacist' THEN 90000 ELSE 0 END AS salary FROM users JOIN occupation ON users.occ_id = occupation.id; -- Add a foreign key from occ_id to occupation table ALTER TABLE users ADD CONSTRAINT fk_occ_id FOREIGN KEY (occ_id) REFERENCES occupation(id); -- Add a foreign key from city_id to city table ALTER TABLE users ADD CONSTRAINT fk_city_id FOREIGN KEY (city_id) REFERENCES city(id); -- Add a foreign key from occ_id to occupation table ALTER TABLE users ADD CONSTRAINT fk_occ_id FOREIGN KEY (occ_id) REFERENCES occupation(id); -- Add a foreign key from city_id to city table ALTER TABLE users ADD CONSTRAINT fk_city_id FOREIGN KEY (city_id) REFERENCES city(id); ALTER TABLE city ADD country VARCHAR(50) DEFAULT 'Canada'; UPDATE city SET country = 'USA' WHERE name = 'Boston' OR name = 'New York';