Q11 (10 pts) Write SQL to create a table called City in your account. The schema

Question

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.

681170

8788000

74398

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)
);

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

Explanation / Answer

1. Query to create the city table

CREATE TABLE city (

   Name varchar2(50) primary key,

   Country varchar2(60),

   population number(11,0),

   capital char(1),

   check (capital ='Y' or capital ='N' or capital= null),

   FOREIGN KEY (Country) REFERENCES country(name)

);

2.

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');

3.

-- 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';

4.

Delete from city

---- deletes the rows if it is not capital city

---- != is the not equals

Where capital != 'Y';

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.