国家、城市、河流关系表

【创建表】

CREATE TABLE COUNTRY(
NAME CHAR(20) PRIMARY KEY,
CONT CHAR(10),
POP FLOAT(1),
GDP FLOAT(1),
LIFEEXP FLOAT(2));

CREATE TABLE CITY(
NAME CHAR(20) PRIMARY KEY,
COUNTRY CHAR(20),
POP FLOAT(1),
CAPITAL CHAR(3),
FOREIGN KEY(COUNTRY) REFERENCES COUNTRY(NAME)
);

CREATE TABLE RIVER(
NAME CHAR(20) PRIMARY KEY,
ORIGIN CHAR(20),
LENGTH INT,
FOREIGN KEY(ORIGIN) REFERENCES COUNTRY(NAME)
);

【插入国家数据】

INSERT
INTO COUNTRY
VALUES('CANADA','NAM',30.1,685.0,77.08);

INSERT
INTO COUNTRY
VALUES('MEXICO','NAM',107.5,694.3,69.36);

INSERT
INTO COUNTRY
VALUES('BRAZIL','SAM',183.3,1004.0,65.60);

INSERT
INTO COUNTRY
VALUES('CUBA','NAM',11.7,16.9,75.95);

INSERT
INTO COUNTRY
VALUES('USA','NAM',270.0,8003.0,75.75);

INSERT
INTO COUNTRY
VALUES('ARGENTINA','SAM',36.3,348.2,70.75);

【插入城市数据】

INSERT
INTO CITY
VALUES('HAVANA','CUBA',2.1,'Y');

INSERT
INTO CITY
VALUES('WASHINGTON, D.C','USA',3.2,'Y');

INSERT
INTO CITY
VALUES('MONTERREY','MEXICO',2.0,'N');

INSERT
INTO CITY
VALUES('TORONTO','CANADA',3.4,'N');

INSERT
INTO CITY
VALUES('BRASILIA','BRAZIL',1.5,'Y');

INSERT
INTO CITY
VALUES('ROSARIO','ARGENTINA',1.1,'N');

INSERT
INTO CITY
VALUES('OTTAWA','CANADA',0.8,'Y');

INSERT
INTO CITY
VALUES('MEXICO CITY','MEXICO',14.1,'Y');

INSERT
INTO CITY
VALUES('BUENOS AIRES','ARGENTINA',10.75,'Y');

【插入河流数据】 

 

INSERT
INTO RIVER
VALUES('RIO PARANA','BRAZIL',2600);

INSERT
INTO RIVER
VALUES('ST. LAWRENCE','USA',1200);

INSERT
INTO RIVER
VALUES('RIO GRANDE','USA',3000);

INSERT
INTO RIVER
VALUES('MISISSIPPI','USA',6000);

【查询】

1.查找拥有2条及以上河流的国家

SELECT COUNTRY.NAME
FROM COUNTRY
WHERE COUNTRY.NAME IN (
SELECT RIVER.ORIGIN
FROM RIVER
GROUP BY RIVER.ORIGIN
HAVING COUNT(RIVER.NAME)>=2);

或者

SELECT COUNTRY.NAME
FROM COUNTRY,RIVER
WHERE COUNTRY.NAME = RIVER.ORIGIN
GROUP BY COUNTRY.NAME
HAVING COUNT(RIVER.NAME)>=2

关注我获取更多编程方面的知识，和我共同进步吧~