Bài thực hành SQL: Địa lí VN [SQL]

Bài mang tính tham khảo, bạn nào chưa làm được thì down về xem nhe

http://www.mediafire.com/download.php?f20t5wo7y66be3o

create database dialyVN
use dialyVN
create table tinh_tp( 
    ma_t_tp varchar (3) primary key,
    ten_t_tp nvarchar (20) NOT NULL,
    dt float NOT NULL,
    ds bigint NOT NULL,
    mien nvarchar (10) NOT NULL,
);
create table biengioi(
    nuoc nvarchar (20) not null,
    ma_t_tp varchar (3) not null
);
create table langgieng(
    ma_t_tp varchar (3) not null,
    lg varchar (3) not null
);
alter table biengioi add 
constraint biengioi_pk primary key(nuoc,ma_t_tp);

alter table langgieng add 
constraint langgieng_pk primary key(ma_t_tp,lg);

alter table biengioi add 
constraint biengioi_fr foreign key (ma_t_tp) 
references tinh_tp(ma_t_tp);

alter table langgieng add
constraint langgieng_fr foreign key (ma_t_tp) 
references tinh_tp(ma_t_tp);

insert into tinh_tp values ('AG',N'An Giang',3406,2142709,'Nam');
insert into tinh_tp values ('BR',N'Bà Rịa - Vũng Tàu',1982,996682,'Nam');
insert into tinh_tp values ('BC',N'Bắc Cạn',4857,293826,N'Bắc');
insert into tinh_tp values ('BG',N'Bắc Giang',3823,1554131,N'Bắc');
insert into tinh_tp values ('BL',N'Bạc Liêu',2526,856518,'Nam');
insert into tinh_tp values ('BN',N'Bắc Ninh',808,1024472,N'Bắc');
insert into tinh_tp values ('BT',N'Bến Tre',2322,1255946,'Nam');
insert into tinh_tp values ('BD',N'Bình Định',6025,1486465,'Trung');
insert into tinh_tp values ('BD',N'Bình Dương',2696,1481550,'Nam');
insert into tinh_tp values ('BP',N'Bình Phước',6857,873598,'Nam');
insert into tinh_tp values ('BTH',N'Bình Thuận',7828,1167023,'Trung');
insert into tinh_tp values ('CM',N'Cà Mau',5202,1206938,'Nam');
insert into tinh_tp values ('CT',N'Cần Thơ',1390,1188435,'Nam');
insert into tinh_tp values ('CB',N'Cao Bằng',6691,507183,N'Bắc');
insert into tinh_tp values ('DN',N'Đà Nẵng',1256,887435,'Trung');
insert into tinh_tp values ('DL',N'Đắc Lắc',13085,1733624,'Trung');
insert into tinh_tp values ('DNO',N'Đắc Nông',6515,489392,'Trung');
insert into tinh_tp values ('DB',N'Điện Biên',9560,490306,N'Bắc');
insert into tinh_tp values ('DNA',N'Đồng Nai',5895,2486154,'Nam');
insert into tinh_tp values ('DT',N'Đồng Tháp',3246,1666467,'Nam');
insert into tinh_tp values ('GL',N'Gia Lai',15495,1274412,'Trung');
insert into tinh_tp values ('HG',N'Hà Giang',7884,724537,N'Bắc');
insert into tinh_tp values ('HNA',N'Hà Nam',852,784045,N'Bắc');
insert into tinh_tp values ('HN',N'Hà Nội',333447,6451909,N'Bắc');
insert into tinh_tp values ('HT',N'Hà Tây',2193,2047000,N'Bắc');
insert into tinh_tp values ('HTI',N'Hà Tĩnh',605570,1227554,N'Bắc');
insert into tinh_tp values ('HD',N'Hải Dương',1648,1705059,N'Bắc');
insert into tinh_tp values ('HP',N'Hải Phòng',1526,1837173,N'Bắc');
insert into tinh_tp values ('HAG',N'Hậu Giang',1608,757300,'Nam');
insert into tinh_tp values ('HB',N'Hòa Bình',4663,785217,N'Bắc');
insert into tinh_tp values ('HY',N'Hưng Yên',923,1127903,N'Bắc');
insert into tinh_tp values ('KH',N'Khánh Hòa',5198,1157604,'Trung');
insert into tinh_tp values ('KG',N'Kiên Giang',6268,1688248,'Nam');
insert into tinh_tp values ('KT',N'Kon Tum',9615,430133,'Trung');
insert into tinh_tp values ('LC',N'Lai Châu',9059,370502,N'Bắc');
insert into tinh_tp values ('LD',N'Lâm Đồng',9765,1187574,'Trung');
insert into tinh_tp values ('LS',N'Lạng Sơn',8305,732515,N'Bắc');
insert into tinh_tp values ('LCA',N'Lào Cai',6357,614595,N'Bắc');
insert into tinh_tp values ('LA',N'Long An',4491,1436066,'Nam');
insert into tinh_tp values ('ND',N'Nam Định',1641,1828111,N'Bắc');
insert into tinh_tp values ('NA',N'Nghệ An',16487,3113055,'Trung');
insert into tinh_tp values ('NB',N'Ninh Bình',1384,898999,N'Bắc');
insert into tinh_tp values ('NT',N'Ninh Thuận',3360,564993,N'Trung');
insert into tinh_tp values ('PT',N'Phú Thọ',3520,1316389,N'Bắc');
insert into tinh_tp values ('PY',N'Phú Yên',5045,862231,N'Trung');
insert into tinh_tp values ('QB',N'Quảng Bình',8051,80846924,N'Trung');
insert into tinh_tp values ('QN',N'Quảng ,Nam',10407,1422319,N'Trung');
insert into tinh_tp values ('QNG',N'Quảng Ngãi',5138,1216773,N'Trung');
insert into tinh_tp values ('QNI',N'Quảng Ninh',5900,1144988,N'Bắc');
insert into tinh_tp values ('QT',N'Quảng Trị',4745,70597985,N'Bắc');
insert into tinh_tp values ('ST',N'Sóc Trăng',3223,1292853,'Nam');
insert into tinh_tp values ('SL',N'Sơn La',14055,1076055,N'Bắc');
insert into tinh_tp values ('TN',N'Tây Ninh',4030,1066513,'Nam');
insert into tinh_tp values ('TB',N'Thái Bình',1545,1781842,N'Bắc');
insert into tinh_tp values ('TNG',N'Thái Nguyên',3543,1123116,N'Bắc');
insert into tinh_tp values ('TH',N'Thanh Hóa',11116,3400595,N'Bắc');
insert into tinh_tp values ('TTH',N'Thừa Thiên Huế',5053,991087579,N'Trung');
insert into tinh_tp values ('TG',N'Tiền Giang',2367,1672271,'Nam');
insert into tinh_tp values ('HCM',N'TP Hồ Chí Minh',2095,7162864,'Nam');
insert into tinh_tp values ('TV',N'Trà Vinh',2215,1003012,'Nam');
insert into tinh_tp values ('TQ',N'Tuyên Quang',5868,724821,N'Bắc');
insert into tinh_tp values ('VL',N'Vĩnh Long',1475,1024707,'Nam');
insert into tinh_tp values ('VP',N'Vĩnh Phúc',1371,999786,N'Bắc');
insert into tinh_tp values ('YB',N'Yên Bái',6883,740387,N'Bắc');

insert into langgieng values ('AG','DT');
insert into langgieng values ('AG','CT');
insert into langgieng values ('AG','KG');
insert into langgieng values ('BR','HCM');
insert into langgieng values ('BR','BTH');
insert into langgieng values ('BR','DNA');
insert into langgieng values ('DNA','BTH');
insert into langgieng values ('DNA','BR');
insert into langgieng values ('DNA','HCM');
insert into langgieng values ('BD','TN');
insert into langgieng values ('BD','DNA');
insert into langgieng values ('BD','BP');
insert into langgieng values ('BD','HCM');
insert into langgieng values ('BP','DL');
insert into langgieng values ('BP','BD');
insert into langgieng values ('BP','LD');
insert into langgieng values ('BP','DNA');
insert into langgieng values ('BL','KG');
insert into langgieng values ('BL','CT');
insert into langgieng values ('BL','CM');
insert into langgieng values ('BL','ST');
insert into langgieng values ('BT','TG');
insert into langgieng values ('BT','VL');
insert into langgieng values ('BT','TV');
insert into langgieng values ('BT','HCM');
insert into langgieng values ('CM','KG');
insert into langgieng values ('CM','BL');
insert into langgieng values ('CT','AG');
insert into langgieng values ('CT','DT');
insert into langgieng values ('CT','VL');
insert into langgieng values ('CT','KG');
insert into langgieng values ('CT','BL');
insert into langgieng values ('CT','ST');
insert into langgieng values ('DT','AG');
insert into langgieng values ('DT','LA');
insert into langgieng values ('DT','TG');
insert into langgieng values ('HAG','CT');
insert into langgieng values ('HAG','ST');
insert into langgieng values ('HAG','VL');
insert into langgieng values ('HAG','KG');
insert into langgieng values ('HAG','BL');
insert into langgieng values ('LA','TN');
insert into langgieng values ('LA','TG');
insert into langgieng values ('LA','DT');
insert into langgieng values ('LA','HCM');
insert into langgieng values ('HCM','BD');
insert into langgieng values ('HCM','TN');
insert into langgieng values ('HCM','DNA');
insert into langgieng values ('HCM','BR');
insert into langgieng values ('HCM','LA');
insert into langgieng values ('HCM','TG');
insert into langgieng values ('TG','LA');
insert into langgieng values ('TG','DT');
insert into langgieng values ('TG','BT');
insert into langgieng values ('TG','VL');
insert into langgieng values ('TN','BD');
insert into langgieng values ('TN','BP');
insert into langgieng values ('TN','LA');
insert into langgieng values ('TN','HCM');
insert into langgieng values ('BTH','DNA');

insert into BIENGIOI values ('CPC','AG');
insert into BIENGIOI values ('CPC','DL');
insert into BIENGIOI values ('CPC','DT');
insert into BIENGIOI values ('CPC','GL');
insert into BIENGIOI values ('CPC','KG');
insert into BIENGIOI values ('CPC','LA');
insert into BIENGIOI values ('CPC','BP');
insert into BIENGIOI values ('CPC','TN');
insert into BIENGIOI values ('LAO','GL');
insert into BIENGIOI values ('LAO','LC');
insert into BIENGIOI values ('LAO','NT');
insert into BIENGIOI values ('LAO','BT');
insert into BIENGIOI values ('TQ','CB');
insert into BIENGIOI values ('TQ','LC');

1.Xuất ra tên tỉnh, TP cùng với dân số của tỉnh,TP:

a) Có diện tích >= 5000 Km2

select ten_t_tp, ds from tinh_tp where dt >=5000

b) Có diện tích >= [input] (SV nhập một số bất kỳ)

2.Xuất ra tên tỉnh, TP cùng với diện tích của tỉnh,TP:

a) Thuộc miền Bắc

select ten_t_tp, dt from tinh_tp where mien=N'Bắc'

b) Thuộc miền [input] (SV nhập một miền bất kỳ)

3.Xuất ra các Tên tỉnh, TP biên giới thuộc miền [input] (SV cho một miền bất kỳ)

4.Cho biết diện tích trung bình của các tỉnh, TP (Tổng DT/Tổng số tỉnh_TP).

select sum(dt)/count(ma_t_tp) as [Diện tích TB] from tinh_tp

5.Cho biết dân số cùng với tên tỉnh của các tỉnh, TP có diện tích > 7000 Km2.

select ten_t_tp as [Tên T,Tp],ds as [Dân số] from tinh_tp where dt>=7000

6.Cho biết dân số cùng với tên tỉnh của các tỉnh miền ‘Bắc’.

select ten_t_tp as [Tên T,Tp],ds as [Dân số] from tinh_tp where mien=N'Bắc'

7.Cho biết mã các nước biên giới của các tỉnh miền ‘Nam’.

select t.ma_t_tp as [Mã T,Tp], ten_t_tp as [Tỉnh,Tp],nuoc as [Nước biên giới] 
from tinh_tp t,biengioi b, langgieng l
where t.ma_t_tp=b.ma_t_tp and b.ma_t_tp=l.ma_t_tp and t.mien=N'Nam'

8.Cho biết diện tích trung bình của các tỉnh, TP. (Sử dụng hàm)

select avg(dt) as [Diện tích Tb]
from tinh_tp

9.Cho biết mật độ dân số (DS/DT) cùng với tên tỉnh, TP của tất cả các tỉnh, TP.

select ten_t_tp as [Tên T,Tp],ds/dt as [Mật độ DS] 
from tinh_tp

10.Cho biết tên các tỉnh,TP láng giềng của tỉnh ‘Long An’.

select lg as [Láng giềng] from tinh_tp t, langgieng l
where t.ma_t_tp=l.ma_t_tp and ten_t_tp=N'Long An'

11.Cho biết số lượng các tỉnh, TP giáp với ‘CPC’.

select count(nuoc) as [Số tỉnh giáp CPC] from biengioi b
where nuoc='CPC'

12.Cho biết tên những tỉnh, TP có diện tích lớn nhất.

select ten_t_tp as [Tên T,Tp], dt [Diện tích lớn nhất] 
from tinh_tp where dt>=(select max(dt) from tinh_tp)

13.Cho biết tỉnh, TP có mật độ DS đông nhất.

select ten_t_tp, ds from tinh_tp where ds=(select max(ds) from tinh_tp)

14.Cho biết tên những tỉnh, TP giáp với hai nước biên giới khác nhau.

select ten_t_tp as [Tỉnh T,Tp giáp hai nước biên giới khác nhau] from tinh_tp t, biengioi b
where t.ma_t_tp=b.ma_t_tp group by TEN_T_TP
having count (*)=2

15.Cho biết danh sách các miền cùng với các tỉnh, TP trong các miền đó.

select ten_t_tp, mien from tinh_tp group by mien, ten_t_tp

16.Cho biết tên những tỉnh, TP có nhiều láng giềng nhất.

select t.ma_t_tp, ten_t_tp,count(t.ma_t_tp) as [Số lượng] from tinh_tp t, langgieng l
where t.ma_t_tp=l.ma_t_tp 
group by ten_t_tp, t.ma_t_tp
having count(ten_t_tp)>=all(select count(ten_t_tp) from tinh_tp t1, langgieng l1 where 
t1.ma_t_tp=l1.ma_t_tp group by t1.ma_t_tp)

17.Cho biết những tỉnh, TP có diện tích nhỏ hơn diện tích trung bình của tất cả tỉnh, TP.

18.Cho biết tên những tỉnh, TP giáp với các tỉnh, TP ở miền ‘Nam’ và không phải là miền ‘Nam’.

select ten_t_tp
from tinh_tp t
where mien!='Nam' and ma_t_tp in
(select l.lg 
from tinh_tp t,langgieng l
where t.ma_t_tp=l.ma_t_tp and mien='Nam')

19.Cho biết tên những tỉnh, TP có diện tích lớn hơn tất cả các tỉnh, TP láng giềng của nó.

SELECT DISTINCT TEN_T_TP 
FROM TINH_TP T1, LANGGIENG L 
WHERE T1.MA_T_TP = L.MA_T_TP 
  AND T1.DT>=ALL 
  (SELECT T2.DT FROM TINH_TP T2, LANGGIENG L2
   WHERE T2.MA_T_TP = L2.MA_T_TP )

20.Cho biết tên những tỉnh, TP mà ta có thể đến bằng cách đi từ ‘TP.HCM’ xuyên qua ba tỉnh khác nhau và cũng khác với điểm xuất phát, nhưng láng giềng với nhau.

NREADY.NET