Question

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.

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)

);