Q11 (10 pts) Write SQL to create a table called City in your account. The schema of the table is listed below. You must define the data types and not null constraint (if needed) of every column and all the primary key and foreign key constraints for the table. No screenshot needed for this question.

City (Name, Country, Population, Capital). Name: the name of the city. Text string with maximum 50 characters. Must not be null.

Country: the name of the country where this city belongs to. It is a foreign key referencing the name column of the country table. Could be null.

Population: integer with up to 11 digits. Population of the city. Could be null. Capital: one character (fixed length). Y for capital cities and N otherwise. Could be null.

Q12. (12 pts) Write SQL statements to insert the following 3 cities as well as your home city into the city table. If your home city is already listed, then add a different city you have visited. Leave the Population column of the last city empty. If your home country is not listed in the country table, you may also leave the Country column empty and add a comment in your SQL script file to explain this.

Then write a SQL query to list all the cities in the City table (show all the columns). For this question, you only need to submit the output of the last step.

	Name	Country	Population	Capital
1	Washington D.C.	United States	681170	Y
2	London	United Kingdom	8788000	Y
3	Iowa City	United States	74398	N

2

Q13 (6 pts). Add 100 to the population of all the cities in the United States. Write a SQL query to list all the affected cities and their new population values. For this question, you only need to submit the output of the last step.

Q14. (6 pts) Delete all non-capital cities from the City table. Write a SQL query to show the remaining cities (show all the columns). For this question, you only need to submit the output of the last step.