country(#countrycode:char(2), name:varchar, population:numeric)
city(#code:char(3), countrycode=>country, name:varchar, population:numeric):
CREATE TABLE country (
countrycode CHAR(2) NOT NULL,
name VARCHAR NOT NULL,
population NUMERIC(3),
PRIMARY KEY (countrycode)
);
CREATE TABLE city (
citycode CHAR(3) NOT NULL,
countrycode CHAR(2) NOT NULL,
name VARCHAR NOT NULL,
population NUMERIC(2,1),
PRIMARY KEY (citycode),
FOREIGN KEY (countrycode) REFERENCES country(countrycode)
);
INSERT INTO country VALUES ('ES', 'Spain', 46);
INSERT INTO country VALUES ('FR', 'France', 67);
INSERT INTO country VALUES ('DE', 'Germany', 82);
INSERT INTO city VALUES ('BAR', 'ES', 'Barcelona', 1.9);
INSERT INTO city VALUES ('MAD', 'ES', 'Madrid', 3.3);
INSERT INTO city VALUES ('ZAR', 'ES', 'Zaragoza', 0.7);
INSERT INTO city VALUES ('PAR', 'FR', 'Paris', 2.2);
INSERT INTO city VALUES ('LYO', 'FR', 'Paris', 0.5);
INSERT INTO city VALUES ('LLL', 'FR', 'Lille', 0.2);
INSERT INTO city VALUES ('AMN', 'FR', 'Amiens', 0.1);
SELECT countrycode
FROM city;
countrycode
-------------
ES
ES
ES
FR
FR
FR
FR