BAI 8: DIA LY VIET NAM.

-------------- ĐỊA LÝ VIỆT NAM ---------------------

IF DB_ID('DLVN') IS NOT NULL

DROP DATABASE DLVN

CREATE DATABASE DLVN

USE DLVN

--- TAO BANG MIEN

IF OBJECT_ID('MIEN') IS NOT NULL

DROP TABLE MIEN

CREATE TABLE MIEN

MAMN VARCHAR(10) NOT NULL,

TENMN NVARCHAR(30) NOT NULL,

PRIMARY KEY (MAMN, TENMN)

--- TAO BANG TINH THANH

IF OBJECT_ID('TINHTHANH') IS NOT NULL

DROP TABLE TINHTHANH

CREATE TABLE TINHTHANH

MATT VARCHAR(10) NOT NULL,

TENTT NVARCHAR(30) NOT NULL,

DS INT NOT NULL,

DT FLOAT NOT NULL,

MAMN VARCHAR(10),

PRIMARY KEY(MATT, TENTT)

--- TAO BANG BIEN GIOI

IF OBJECT_ID('BIENGIOI') IS NOT NULL

DROP TABLE BIENGIOI

CREATE TABLE BIENGIOI

MATT VARCHAR(10) NOT NULL ,

MAQG VARCHAR(10) NOT NULL,

PRIMARY KEY(MATT, MAQG)

--- TAO BANG LANG GIENG

IF OBJECT_ID('LANGGIENG') IS NOT NULL

DROP TABLE LANGGIENG

CREATE TABLE LANGGIENG

MATT VARCHAR(10) NOT NULL,

MATTLG VARCHAR(10) NOT NULL,

PRIMARY KEY(MATT, MATTLG)

------------- NHAP LIEU ---------------

--- NHAP BANG MIEN

INSERT INTO MIEN VALUES('MB', N'MIỀN BẮC')

INSERT INTO MIEN VALUES('MT', N'MIỀN TRUNG')

INSERT INTO MIEN VALUES('MN', N'MIỀN NAM')

SELECT * FROM MIEN

--- NHAP BANG TINH THANH

INSERT INTO TINHTHANH VALUES('HN', N'HÀ NỘI', 4, 600, 'MB')

INSERT INTO TINHTHANH VALUES('TB', N'THÁI BÌNH', 2, 450, 'MB')

INSERT INTO TINHTHANH VALUES('DB', N'ĐIỆN BIÊN', 1, 660, 'MB')

INSERT INTO TINHTHANH VALUES('DL', N'DĂKLĂK', 1.5, 800, 'MT')

INSERT INTO TINHTHANH VALUES('KT', N'KONTUM', 1.3, 700, 'MT')

INSERT INTO TINHTHANH VALUES('HCM', N'HỒ CHÍ MINH', 6, 600, 'MN')

INSERT INTO TINHTHANH VALUES('TN', N'TÂY NINH', 1.1, 150, 'MN')

SELECT * FROM TINHTHANH

--- NHAP BANG BIEN GIOI

INSERT INTO BIENGIOI VALUES('DB','TQ')

INSERT INTO BIENGIOI VALUES('DB','L')

INSERT INTO BIENGIOI VALUES('DL','CP')

INSERT INTO BIENGIOI VALUES('KT','L')

INSERT INTO BIENGIOI VALUES('KT','CP')

INSERT INTO BIENGIOI VALUES('TN','CP')

SELECT * FROM BIENGIOI

--- NHAP BANG LANGGIENG

INSERT INTO LANGGIENG VALUES('HN','TB')

INSERT INTO LANGGIENG VALUES('TB','HN')

INSERT INTO LANGGIENG VALUES('TB','DB')

INSERT INTO LANGGIENG VALUES('DB','TB')

INSERT INTO LANGGIENG VALUES('DL','KT')

INSERT INTO LANGGIENG VALUES('KT','DL')

INSERT INTO LANGGIENG VALUES('HCM','TN')

INSERT INTO LANGGIENG VALUES('TN','HCM')

INSERT INTO LANGGIENG VALUES('TN','KT')

INSERT INTO LANGGIENG VALUES('KT','TN')

SELECT * FROM LANGGIENG

---- TRA LOI T-SQL

--- CÂU 1

SELECT TENTT AS TỈNH_THÀNH_CÓ_DT_LỚN200KM2

FROM TINHTHANH

WHERE DT>=200

--- CÂU 2

SELECT TENTT AS TỈNH_MIỀN_BẮC

FROM TINHTHANH

WHERE MAMN = 'MB'

--- CÂU 3

SELECT MAQG AS QUỐC_GIA_BIÊN_GIỚI_VỚI_CÁC_TỈNH_MB

FROM TINHTHANH A, BIENGIOI B

WHERE A.MATT = B.MATT AND MAMN = 'MB'

--- CÂU 5

SELECT AVG(DT) AS DIỆN_TÍCH_TB_CÁC_TỈNH_MN

FROM TINHTHANH

WHERE MAMN = 'MN'

GROUP BY MAMN

--- CÂU 6

SELECT SUM(DS)/SUM(DT) AS MẬT_ĐỘ_DÂN_CƯ_CÁC_TỈNH_MT

FROM TINHTHANH

WHERE MAMN = 'MT'

GROUP BY MAMN

--- CÂU 7

SELECT A.MATT AS MÃ_TỈNH, DT AS DIỆN_TÍCH

FROM TINHTHANH A, LANGGIENG B

WHERE A.MATT = B.MATT AND DT >= ALL (

SELECT DT

FROM TINHTHANH A, LANGGIENG B

WHERE A.MATT = B.MATT)

--- CÂU 8

SELECT TENTT AS TỈNH_CÓ_DT_LỚN_NHẤT

FROM TINHTHANH

WHERE DT >= ALL (SELECT DT FROM TINHTHANH)

--- CÂU 9

SELECT A.TENTT AS TỈNH_CÓ_BG_TRÊN_2QG, COUNT(*) AS SỐ_QUỐC_GIA

FROM BIENGIOI B, TINHTHANH A

WHERE B.MATT = A.MATT

GROUP BY A.MATT, B.MATT, A.TENTT

HAVING COUNT(*) >= 2

--- CÂU 10

SELECT TENMN AS TÊN_MIỀN, TENTT AS TÊN_TỈNH

FROM TINHTHANH A, MIEN B

WHERE A.MAMN = B.MAMN

---- CÂU 11

SELECT B.TENTT AS TÊN_TỈNH_NHIỀU_LÁNG_GIỀNG, COUNT(*) AS SỐ_LÁNG_GIỀNG

FROM LANGGIENG A, TINHTHANH B

WHERE A.MATT = B.MATT

GROUP BY A.MATT, B.MATT, B.TENTT

HAVING COUNT(*) >= ALL (SELECT COUNT(*)

FROM LANGGIENG

GROUP BY MATT)

--- CÂU 12

SELECT TENTT AS TÊN_TỈNH_LÁNG_GIỀNG_VỚI_MT

FROM TINHTHANH A, LANGGIENG B

WHERE A.MATT = B.MATT AND MAMN <> 'MT' AND MATTLG IN (SELECT MATT FROM TINHTHANH WHERE MAMN = 'MT')

--- CÂU 13

SELECT TENTT AS TÊN_TỈNH_DT_LỚN_NHẤT_TRONG_TỈNH, MAMN AS MÃ_MIỀN, DT AS DIỆN_TÍCH_LỚN_NHẤT

FROM TINHTHANH

WHERE DT IN (SELECT MAX(DT) FROM TINHTHANH GROUP BY MAMN)

--- CÂU 14

SELECT TENTT AS TÊN_TỈNH_DS_LỚN_HƠNTB, DS AS DÂN_SỐ

FROM TINHTHANH

WHERE MAMN = 'MB' AND DS >= ANY (SELECT AVG(DS) FROM TINHTHANH WHERE MAMN = 'MB' GROUP BY MAMN)

UNION ALL

SELECT TENTT AS TÊN_TỈNH_DS_LỚN_HƠNTB, DS AS DÂN_SỐ

FROM TINHTHANH

WHERE MAMN = 'MT' AND DS >= ANY (SELECT AVG(DS) FROM TINHTHANH WHERE MAMN = 'MT' GROUP BY MAMN)

UNION ALL

SELECT TENTT AS TÊN_TỈNH_DS_LỚN_HƠNTB, DS AS DÂN_SỐ

FROM TINHTHANH

WHERE MAMN = 'MN' AND DS >= ANY (SELECT AVG(DS) FROM TINHTHANH WHERE MAMN = 'MN' GROUP BY MAMN)