1. Answer the questions with respect to the table below.

users

id	name	age	gender	occ_id	city_id
1	John	25	M	1	3
2	Sara	20	F	3	4
3	Victor	31	M	2	5
4	Jane	27	F	1	3

occupation

id	name
1	Software Engineer
2	Accountant
3	Pharmacist
4	Library Assistant

City

id	name
1	Halifax
2	Calgary
3	Boston
4	New York
5	Toronto

1. Create the three tables with their SQL queries and paste the code in text format below. Make sure while creating the tables: [5+2.5+2.5]

• Must have primary keys in all the tables.
• The attributes which can potentially be foreign keys must have NOT NULL Constraint.
• Gender attribute in users table should CHECK for 'M' or 'F'.

Users Table Query :

Occupation Table Query :

City Table Query :

1. [10+5+5]

Write 2 different types of sql queries to find the users in city 'Boston'. Write 2 types of queries, one using joins to find the answer and another using subqueries to find the same answer.

Write sql query to find how many users are there per occupation.

Perform full outer join between users and city.

1. [2.5+2.5+5]

Write query to make a copy of 'users' table known as 'users_new'.

Write query to insert all columns of 'users' to the 'users_new'.

Write CASE query to add one more column with salary values to the 'users' table. Salary for Software engineer is 80,000, Accountant is 70,000 and Pharmacist is 90,000.

1. [5+5]

Write query to add foreign keys constraints to 'users' table.

Add country column to 'city' table. DEFAULT constraint must be used to add Canada as a default country for cities. [Use DEFAULT Constraint to default your country to Canada, that way you only have to write the countries for cities not in Canada, Hint : remember 'boston' and 'new York' are cities in US, rest all are in Canada, Use ALTER TABLE to add column and default constraint].