Question

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

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.

Answer 1

12.

i. INSERT INTO city (Name,Country,population,capital) VALUES ('washington d.c.','united states',681170,'Y');

ii. INSERT INTO city (Name,Country,population,capital) VALUES ('london.','united kingdom',8788000,'Y');

iii. INSERT INTO city (Name,Country,population,capital) VALUES ('iowa city','united states',74398,'N');

iv. INSERT INTO city (Name,Country,population,capital) VALUES ('chennai','india',4900000,'N');

13.

-- Update keyword is used to update the table

UPDATE city

--- population increases by 100 if country is united states

SET population = population+100

WHERE Country = 'united states';