国家、城市、河流关系表

【创建表】

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

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！