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

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.

HERE IS THE DATA FOR THE QUESTIONS ABOVE IF YOU NEED THEM:

CREATE TABLE country ( name varchar2(50), region varchar2(60), area number(10,0), population number(11,0), gdp number, CONSTRAINT country_PK primary key (name) );

INSERT INTO country (name,region,area,population,gdp) VALUES ('Andorra','Europe',450,84825,.038); INSERT INTO country (name,region,area,population,gdp) VALUES ('Angola','Africa',1246700,13338541,.016); INSERT INTO country (name,region,area,population,gdp) VALUES ('Anguilla','Central America and the Caribbean',91,15094,-.085); INSERT INTO country (name,region,area,population,gdp) VALUES ('Argentina','South America',2766890,41769726,.075); INSERT INTO country (name,region,area,population,gdp) VALUES ('Armenia','Commonwealth of Independent States - European States',29800,2967975,.026); INSERT INTO country (name,region,area,population,gdp) VALUES ('Aruba','Central America and the Caribbean',193,106113,.024); INSERT INTO country (name,region,area,population,gdp) VALUES ('Australia','Oceania',7686850,21766711,.027); INSERT INTO country (name,region,area,population,gdp) VALUES ('Afghanistan','Asia',647500,29835392,.082); INSERT INTO country (name,region,area,population,gdp) VALUES ('Albania','Ethnic Groups in Eastern Europe, Europe',28750,2994667,.035); INSERT INTO country (name,region,area,population,gdp) VALUES ('Algeria','Africa',2381740,34994937,.033); INSERT INTO country (name,region,area,population,gdp) VALUES ('American Samoa','Oceania',199,67242,.03); INSERT INTO country (name,region,area,population,gdp) VALUES ('Bermuda','North America',50,68679,.046); INSERT INTO country (name,region,area,population,gdp) VALUES ('Bhutan','Asia',47000,708427,.067); INSERT INTO country (name,region,area,population,gdp) VALUES ('Bolivia','South America',1098580,10118683,.042); INSERT INTO country (name,region,area,population,gdp) VALUES ('Botswana','Africa',600370,2065398,.086); INSERT INTO country (name,region,area,population,gdp) VALUES ('Brazil','South America',8511965,203429773,.075); INSERT INTO country (name,region,area,population,gdp) VALUES ('Bulgaria','Ethnic Groups in Eastern Europe, Europe',110910,7093635,.002); INSERT INTO country (name,region,area,population,gdp) VALUES ('Burundi','Africa',27830,10216190,.039); INSERT INTO country (name,region,area,population,gdp) VALUES ('Cambodia','Southeast Asia',181040,14701717,.06); INSERT INTO country (name,region,area,population,gdp) VALUES ('Cameroon','Africa',475440,19711291,.03); INSERT INTO country (name,region,area,population,gdp) VALUES ('Austria','Europe',83850,8217280,.02); INSERT INTO country (name,region,area,population,gdp) VALUES ('Azerbaijan','Commonwealth of Independent States - European States',86600,8372373,.05); INSERT INTO country (name,region,area,population,gdp) VALUES ('Bahrain','Middle East',620,1214705,.041); INSERT INTO country (name,region,area,population,gdp) VALUES ('Bangladesh','Asia',144000,158570535,.06); INSERT INTO country (name,region,area,population,gdp) VALUES ('Barbados','Central America and the Caribbean',430,286705,-.005); INSERT INTO country (name,region,area,population,gdp) VALUES ('Belarus','Commonwealth of Independent States - European States',207600,9577552,.076); INSERT INTO country (name,region,area,population,gdp) VALUES ('Belgium','Europe',30510,10431477,.02); INSERT INTO country (name,region,area,population,gdp) VALUES ('Belize','Central America and the Caribbean',22960,321115,.02); INSERT INTO country (name,region,area,population,gdp) VALUES ('Benin','Africa',112620,9325032,.025); INSERT INTO country (name,region,area,population,gdp) VALUES ('Canada','North America',9976140,34030589,.031); INSERT INTO country (name,region,area,population,gdp) VALUES ('Cape Verde','World',4030,516100,.054); INSERT INTO country (name,region,area,population,gdp) VALUES ('Cayman Islands','Central America and the Caribbean',260,51384,.011); INSERT INTO country (name,region,area,population,gdp) VALUES ('Cocos (Keeling) Islands','Southeast Asia',14,596,.01); INSERT INTO country (name,region,area,population,gdp) VALUES ('Comoros','Africa',2170,794683,.021); INSERT INTO country (name,region,area,population,gdp) VALUES ('Cook Islands','Oceania',240,11124,.001); INSERT INTO country (name,region,area,population,gdp) VALUES ('Costa Rica','Central America and the Caribbean',51100,4576562,.042); INSERT INTO country (name,region,area,population,gdp) VALUES ('Croatia','Ethnic Groups in Eastern Europe, Europe',56538,4483804,-.014); INSERT INTO country (name,region,area,population,gdp) VALUES ('Cuba','Central America and the Caribbean',110860,11087330,.015); INSERT INTO country (name,region,area,population,gdp) VALUES ('Cyprus','Middle East',9250,1120489,.01); INSERT INTO country (name,region,area,population,gdp) VALUES ('Czech Republic','Ethnic Groups in Eastern Europe, Europe',78703,10190213,.023); INSERT INTO country (name,region,area,population,gdp) VALUES ('Denmark','Europe',43070,5529888,.021); INSERT INTO country (name,region,area,population,gdp) VALUES ('Djibouti','Africa',22000,757074,.045); INSERT INTO country (name,region,area,population,gdp) VALUES ('Central African Republic','Africa',622980,4950027,.033); INSERT INTO country (name,region,area,population,gdp) VALUES ('Chad','Africa',1284000,10758945,.051); INSERT INTO country (name,region,area,population,gdp) VALUES ('Chile','South America',756950,16888760,.053); INSERT INTO country (name,region,area,population,gdp) VALUES ('China','Asia',9596960,1336718015,.103); INSERT INTO country (name,region,area,population,gdp) VALUES ('Faroe Islands','Europe',1400,49267,.005); INSERT INTO country (name,region,area,population,gdp) VALUES ('Fiji','Oceania',18270,883125,.001); INSERT INTO country (name,region,area,population,gdp) VALUES ('Finland','Europe',337030,5259250,.031); INSERT INTO country (name,region,area,population,gdp) VALUES ('France','Europe',547030,65312249,.015); INSERT INTO country (name,region,area,population,gdp) VALUES ('French Polynesia','Oceania',3941,294935,.027); INSERT INTO country (name,region,area,population,gdp) VALUES ('Gabon','Africa',267670,1576665,.057); INSERT INTO country (name,region,area,population,gdp) VALUES ('Georgia','Middle East',69700,4585874,.064); INSERT INTO country (name,region,area,population,gdp) VALUES ('Germany','Europe',356910,81471834,.035); INSERT INTO country (name,region,area,population,gdp) VALUES ('Ghana','Africa',238540,24791073,.057); INSERT INTO country (name,region,area,population,gdp) VALUES ('Dominica','Central America and the Caribbean',750,72969,.01); INSERT INTO country (name,region,area,population,gdp) VALUES ('Dominican Republic','Central America and the Caribbean',48730,9956648,.078); INSERT INTO country (name,region,area,population,gdp) VALUES ('Ecuador','South America',283560,15007343,.032); INSERT INTO country (name,region,area,population,gdp) VALUES ('Egypt','Africa',1001450,82079636,.051); INSERT INTO country (name,region,area,population,gdp) VALUES ('El Salvador','Central America and the Caribbean',21040,6071774,.007); INSERT INTO country (name,region,area,population,gdp) VALUES ('Equatorial Guinea','Africa',28050,668225,-.008); INSERT INTO country (name,region,area,population,gdp) VALUES ('Eritrea','Africa',121320,5939484,.022); INSERT INTO country (name,region,area,population,gdp) VALUES ('Estonia','Europe',45100,1282963,.031); INSERT INTO country (name,region,area,population,gdp) VALUES ('Ethiopia','Africa',1127127,90873739,.08); INSERT INTO country (name,region,area,population,gdp) VALUES ('Greece','Europe',131940,10760136,-.045); INSERT INTO country (name,region,area,population,gdp) VALUES ('Greenland','Arctic Region',2175600,57670,-.02); INSERT INTO country (name,region,area,population,gdp) VALUES ('Haiti','Central America and the Caribbean',27750,9719932,-.051); INSERT INTO country (name,region,area,population,gdp) VALUES ('Honduras','Central America and the Caribbean',112090,8143564,.028); INSERT INTO country (name,region,area,population,gdp) VALUES ('Hong Kong','Southeast Asia',1040,7122508,.068); INSERT INTO country (name,region,area,population,gdp) VALUES ('Hungary','Ethnic Groups in Eastern Europe, Europe',93030,9976062,.012); INSERT INTO country (name,region,area,population,gdp) VALUES ('Iceland','Arctic Region',103000,311058,-.035); INSERT INTO country (name,region,area,population,gdp) VALUES ('India','Asia',3287590,1189172906,.104); INSERT INTO country (name,region,area,population,gdp) VALUES ('Indonesia','Southeast Asia',1919440,245613043,.061); INSERT INTO country (name,region,area,population,gdp) VALUES ('Iran','Middle East',1648000,77891220,.01); INSERT INTO country (name,region,area,population,gdp) VALUES ('Iraq','Middle East',437072,30399572,.008); INSERT INTO country (name,region,area,population,gdp) VALUES ('Grenada','Central America and the Caribbean',340,108419,-.014); INSERT INTO country (name,region,area,population,gdp) VALUES ('Guadeloupe','Central America and the Caribbean',1780, NULL,NULL); INSERT INTO country (name,region,area,population,gdp) VALUES ('Guatemala','Central America and the Caribbean',108890,13824463,.026); INSERT INTO country (name,region,area,population,gdp) VALUES ('Guinea','Africa',245860,10601009,.019); INSERT INTO country (name,region,area,population,gdp) VALUES ('Guyana','South America',214970,744768,.036); INSERT INTO country (name,region,area,population,gdp) VALUES ('Kyrgyzstan','Commonwealth of Independent States - Central Asian States',198500,5587443,-.014); INSERT INTO country (name,region,area,population,gdp) VALUES ('Latvia','Europe',64100,2204708,-.003); INSERT INTO country (name,region,area,population,gdp) VALUES ('Lebanon','Middle East',10400,4143101,.075); INSERT INTO country (name,region,area,population,gdp) VALUES ('Lesotho','Africa',30350,1924886,.024); INSERT INTO country (name,region,area,population,gdp) VALUES ('Liberia','Africa',111370,3786764,.051); INSERT INTO country (name,region,area,population,gdp) VALUES ('Liechtenstein','Europe',160,35236,.018); INSERT INTO country (name,region,area,population,gdp) VALUES ('Lithuania','Europe',65200,3535547,.013); INSERT INTO country (name,region,area,population,gdp) VALUES ('Luxembourg','Europe',2586,503302,.034); INSERT INTO country (name,region,area,population,gdp) VALUES ('Madagascar','Africa',587040,21926221,-.02); INSERT INTO country (name,region,area,population,gdp) VALUES ('Malawi','Africa',118480,15879252,.066); INSERT INTO country (name,region,area,population,gdp) VALUES ('Ireland','Europe',70280,4670976,-.01); INSERT INTO country (name,region,area,population,gdp) VALUES ('Israel','Middle East',20770,7473052,.046); INSERT INTO country (name,region,area,population,gdp) VALUES ('Italy','Europe',301230,61016804,.013); INSERT INTO country (name,region,area,population,gdp) VALUES ('Jamaica','Central America and the Caribbean',10990,2868380,-.011); INSERT INTO country (name,region,area,population,gdp) VALUES ('Japan','Asia',377835,126475664,.039); INSERT INTO country (name,region,area,population,gdp) VALUES ('Jordan','Middle East',89213,6508271,.031); INSERT INTO country (name,region,area,population,gdp) VALUES ('Kazakhstan','Commonwealth of Independent States - Central Asian States',2717300,15522373,.07); INSERT INTO country (name,region,area,population,gdp) VALUES ('Kenya','Africa',582650,41070934,.05); INSERT INTO country (name,region,area,population,gdp) VALUES ('Kuwait','Middle East',17820,2595628,.02); INSERT INTO country (name,region,area,population,gdp) VALUES ('Malaysia','Southeast Asia',329750,28728607,.072); INSERT INTO country (name,region,area,population,gdp) VALUES ('Maldives','Asia',300,394999,.08); INSERT INTO country (name,region,area,population,gdp) VALUES ('Mexico','North America',1972550,113724226,.055); INSERT INTO country (name,region,area,population,gdp) VALUES ('Monaco','Europe',2,30539, NULL); INSERT INTO country (name,region,area,population,gdp) VALUES ('Mongolia','Asia',1566000,3133318,.061); INSERT INTO country (name,region,area,population,gdp) VALUES ('Montserrat','Central America and the Caribbean',100,5140,-.01); INSERT INTO country (name,region,area,population,gdp) VALUES ('Morocco','Africa',446550,31968361,.032); INSERT INTO country (name,region,area,population,gdp) VALUES ('Mozambique','Africa',801590,22948858,.07); INSERT INTO country (name,region,area,population,gdp) VALUES ('Namibia','Africa',825418,2147585,.044); INSERT INTO country (name,region,area,population,gdp) VALUES ('Nepal','Asia',140800,29391883,.046); INSERT INTO country (name,region,area,population,gdp) VALUES ('Netherlands','Europe',37330,16847007,.017); INSERT INTO country (name,region,area,population,gdp) VALUES ('Mali','Africa',1240000,14159904,.045); INSERT INTO country (name,region,area,population,gdp) VALUES ('Malta','Europe',320,408333,.037); INSERT INTO country (name,region,area,population,gdp) VALUES ('Marshall Islands','Oceania',181,67182,-.003); INSERT INTO country (name,region,area,population,gdp) VALUES ('Mauritania','Africa',1030700,3281634,.047); INSERT INTO country (name,region,area,population,gdp) VALUES ('Mauritius','World',1860,1303717,.04); INSERT INTO country (name,region,area,population,gdp) VALUES ('Pakistan','Asia',803940,187342721,.048); INSERT INTO country (name,region,area,population,gdp) VALUES ('Palau','Oceania',458,20956,NULL); INSERT INTO country (name,region,area,population,gdp) VALUES ('Panama','Central America and the Caribbean',78200,3460462,.075); INSERT INTO country (name,region,area,population,gdp) VALUES ('Papua New Guinea','Oceania',461690,6187591,.07); INSERT INTO country (name,region,area,population,gdp) VALUES ('Paraguay','South America',406750,6459058,.153); INSERT INTO country (name,region,area,population,gdp) VALUES ('Peru','South America',1285220,29248943,.088); INSERT INTO country (name,region,area,population,gdp) VALUES ('Philippines','Southeast Asia',300000,101833938,.073); INSERT INTO country (name,region,area,population,gdp) VALUES ('Poland','Ethnic Groups in Eastern Europe, Europe',312680,38441588,.038); INSERT INTO country (name,region,area,population,gdp) VALUES ('Portugal','Europe',92080,10760305,.014); INSERT INTO country (name,region,area,population,gdp) VALUES ('Netherlands Antilles','Central America and the Caribbean',960, NULL,NULL); INSERT INTO country (name,region,area,population,gdp) VALUES ('New Caledonia','Oceania',19060,256275,NULL); INSERT INTO country (name,region,area,population,gdp) VALUES ('New Zealand','Oceania',268680,4290347,.015); INSERT INTO country (name,region,area,population,gdp) VALUES ('Nicaragua','Central America and the Caribbean',129494,5666301,.045); INSERT INTO country (name,region,area,population,gdp) VALUES ('Niger','Africa',1267000,16468886,.075); INSERT INTO country (name,region,area,population,gdp) VALUES ('Nigeria','Africa',923770,155215573,.084); INSERT INTO country (name,region,area,population,gdp) VALUES ('Niue','Oceania',260,1311,.062); INSERT INTO country (name,region,area,population,gdp) VALUES ('Norway','Europe',324220,4691849,.004); INSERT INTO country (name,region,area,population,gdp) VALUES ('Oman','Middle East',212460,3027959,.042); INSERT INTO country (name,region,area,population,gdp) VALUES ('Puerto Rico','Central America and the Caribbean',9104,3989133,-.058); INSERT INTO country (name,region,area,population,gdp) VALUES ('Qatar','Middle East',11000,848016,.163); INSERT INTO country (name,region,area,population,gdp) VALUES ('Reunion','World',2510, NULL,NULL); INSERT INTO country (name,region,area,population,gdp) VALUES ('Sao Tome and Principe','Africa',960,179506,.045); INSERT INTO country (name,region,area,population,gdp) VALUES ('Saudi Arabia','Middle East',1960582,26131703,.037); INSERT INTO country (name,region,area,population,gdp) VALUES ('Senegal','Africa',196190,12643799,.042); INSERT INTO country (name,region,area,population,gdp) VALUES ('Seychelles','Africa',455,89188,.062); INSERT INTO country (name,region,area,population,gdp) VALUES ('Sierra Leone','Africa',71740,5363669,.05); INSERT INTO country (name,region,area,population,gdp) VALUES ('Singapore','Southeast Asia',632,4740737,.145); INSERT INTO country (name,region,area,population,gdp) VALUES ('Slovakia','Ethnic Groups in Eastern Europe, Europe',48845,5477038,.04); INSERT INTO country (name,region,area,population,gdp) VALUES ('Slovenia','Ethnic Groups in Eastern Europe, Europe',20296,2000092,.012); INSERT INTO country (name,region,area,population,gdp) VALUES ('Solomon Islands','Oceania',28450,571890,.056); INSERT INTO country (name,region,area,population,gdp) VALUES ('Somalia','Africa',637660,9925640,.026); INSERT INTO country (name,region,area,population,gdp) VALUES ('Romania','Ethnic Groups in Eastern Europe, Europe',237500,21904551,-.013); INSERT INTO country (name,region,area,population,gdp) VALUES ('Rwanda','Africa',26340,11370425,.065); INSERT INTO country (name,region,area,population,gdp) VALUES ('Saint Lucia','Central America and the Caribbean',620,161557,.008); INSERT INTO country (name,region,area,population,gdp) VALUES ('San Marino','Europe',60,31817,-.13); INSERT INTO country (name,region,area,population,gdp) VALUES ('Thailand','Southeast Asia',514000,66720153,.078); INSERT INTO country (name,region,area,population,gdp) VALUES ('Togo','Africa',56790,6771993,.034); INSERT INTO country (name,region,area,population,gdp) VALUES ('Tonga','Oceania',748,105916,.003); INSERT INTO country (name,region,area,population,gdp) VALUES ('Tunisia','Africa',163610,10629186,.037); INSERT INTO country (name,region,area,population,gdp) VALUES ('Turkey','Middle East',780580,78785548,.082); INSERT INTO country (name,region,area,population,gdp) VALUES ('Turkmenistan','Commonwealth of Independent States - Central Asian States',488100,4997503,.092); INSERT INTO country (name,region,area,population,gdp) VALUES ('Turks and Caicos Islands','Central America and the Caribbean',430,44819,.049); INSERT INTO country (name,region,area,population,gdp) VALUES ('Uganda','Africa',236040,34612250,.052); INSERT INTO country (name,region,area,population,gdp) VALUES ('Ukraine','Commonwealth of Independent States - European States',603700,45134707,.042); INSERT INTO country (name,region,area,population,gdp) VALUES ('South Africa','Africa',1219912,49004031,.028); INSERT INTO country (name,region,area,population,gdp) VALUES ('Spain','Europe',504750,46754784,-.001); INSERT INTO country (name,region,area,population,gdp) VALUES ('Sri Lanka','Asia',65610,21283913,.091); INSERT INTO country (name,region,area,population,gdp) VALUES ('Sudan','Africa',2505810,45047502,.051); INSERT INTO country (name,region,area,population,gdp) VALUES ('Suriname','South America',163270,491989,.044); INSERT INTO country (name,region,area,population,gdp) VALUES ('Swaziland','Africa',17360,1370424,.02); INSERT INTO country (name,region,area,population,gdp) VALUES ('Sweden','Europe',449964,9088728,.055); INSERT INTO country (name,region,area,population,gdp) VALUES ('Switzerland','Europe',41290,7639961,.026); INSERT INTO country (name,region,area,population,gdp) VALUES ('Tajikistan','Commonwealth of Independent States - Central Asian States',143100,7627200,.065); INSERT INTO country (name,region,area,population,gdp) VALUES ('United Arab Emirates','Middle East',75581,5148664,.032); INSERT INTO country (name,region,area,population,gdp) VALUES ('United Kingdom','Europe',244820,62698362,.013); INSERT INTO country (name,region,area,population,gdp) VALUES ('Yemen','Middle East',527970,24133492,.08); INSERT INTO country (name,region,area,population,gdp) VALUES ('Zaire','Africa',2345410, NULL,NULL); INSERT INTO country (name,region,area,population,gdp) VALUES ('Zambia','Africa',752610,13881336,.076); INSERT INTO country (name,region,area,population,gdp) VALUES ('Zimbabwe','Africa',390580,12084304,.09); INSERT INTO country (name,region,area,population,gdp) VALUES ('United States','North America',9372610,313232044,.028); INSERT INTO country (name,region,area,population,gdp) VALUES ('Uruguay','South America',176220,3308535,.085); INSERT INTO country (name,region,area,population,gdp) VALUES ('Uzbekistan','Commonwealth of Independent States - Central Asian States',447400,28128600,.085); INSERT INTO country (name,region,area,population,gdp) VALUES ('Vanuatu','Oceania',14760,224564,.022); INSERT INTO country (name,region,area,population,gdp) VALUES ('Venezuela','South America',912050,27635743,-.019);

commit;