BAI 6: QUAN LY CHUYEN DU LICH.

-------- QUAN LY CHUYEN DU LICH -------------------------

IF DB_ID('QLCDL') IS NOT NULL

DROP DATABASE QLCDL

CREATE DATABASE QLCDL

USE QLCDL

--- TAO BANG XE

IF OBJECT_ID('XE') IS NOT NULL

DROP TABLE XE

CREATE TABLE XE

MAXE VARCHAR(10) NOT NULL,

BSXE VARCHAR(10) NOT NULL,

MOTAXE NVARCHAR(30),

PRIMARY KEY(MAXE,BSXE)

--- TAO BANG DIA DIEM

IF OBJECT_ID('DIADIEM') IS NOT NULL

DROP TABLE DIADIEM

CREATE TABLE DIADIEM

MADD VARCHAR(10) NOT NULL PRIMARY KEY,

TENDD NVARCHAR(30) NOT NULL,

MOTADD NVARCHAR(30)

--- TAO BANG NHAN VIEN

IF OBJECT_ID('NHANVIEN') IS NOT NULL

DROP TABLE NHANVIEN

CREATE TABLE NHANVIEN

MANV VARCHAR(10) NOT NULL,

CMND VARCHAR(10) NOT NULL,

TENNV NVARCHAR(30) NOT NULL,

DCNV NVARCHAR(30) ,

PRIMARY KEY (MANV, CMND)

--- TAO BANG KHACH

IF OBJECT_ID('KHACH') IS NOT NULL

DROP TABLE KHACH

CREATE TABLE KHACH

MAKH VARCHAR(10) NOT NULL,

CMND VARCHAR(10) NOT NULL,

TENKH NVARCHAR(30) NOT NULL,

DCKH NVARCHAR(30) ,

PRIMARY KEY (MAKH, CMND)

--- TAO BANG CHUYEN DI

IF OBJECT_ID('CHUYENDI') IS NOT NULL

DROP TABLE CHUYENDI

CREATE TABLE CHUYENDI

MACH VARCHAR(10) NOT NULL PRIMARY KEY,

NGAYKH DATETIME NOT NULL,

NGAYKT DATETIME NOT NULL,

NOIDUNG NVARCHAR(30)

--- TAO BANG CD-NV

IF OBJECT_ID('CDNV') IS NOT NULL

DROP TABLE CDNV

CREATE TABLE CDNV

MACH VARCHAR(10) NOT NULL,

MANV VARCHAR(10) NOT NULL,

PRIMARY KEY (MACH, MANV)

--- TAO BANG CD-XE

IF OBJECT_ID('CDXE') IS NOT NULL

DROP TABLE CDXE

CREATE TABLE CDXE

MACH VARCHAR(10) NOT NULL,

MAXE VARCHAR(10) NOT NULL,

PRIMARY KEY (MACH, MAXE)

--- TAO BANG CD-DD

IF OBJECT_ID('CDDD') IS NOT NULL

DROP TABLE CDDD

CREATE TABLE CDDD

MACH VARCHAR(10) NOT NULL,

MADD VARCHAR(10) NOT NULL,

NOIDUNGDD NVARCHAR(30),

PRIMARY KEY (MACH, MADD)

--- TAO BANG CD-KH

IF OBJECT_ID('CDKH') IS NOT NULL

DROP TABLE CDKH

CREATE TABLE CDKH

MACH VARCHAR(10) NOT NULL,

MAKH VARCHAR(10) NOT NULL,

PRIMARY KEY (MACH, MAKH)

------- NHAP LIEU

--- NHAP BANG XE

INSERT INTO XE VALUES('XE1','53Y1111',N'XANH LÁ CÂY')

INSERT INTO XE VALUES('XE2','53Y2222',N'XANH LÁ CÂY')

INSERT INTO XE VALUES('XE3','53Y3333',N'XANH LÁ CÂY')

INSERT INTO XE VALUES('XE4','53Y4444',N'XANH LÁ CÂY')

SELECT * FROM XE

--- NHAP BANG DIA DIEM

INSERT INTO DIADIEM VALUES('D01', N'ĐỒNG XOÀI',N'BẢO TÀNG')

INSERT INTO DIADIEM VALUES('D02', N'TÂY NINH',N'NHÀ THỜ')

INSERT INTO DIADIEM VALUES('D03', N'HỒ CHÍ MINH',N'CỦ CHI')

INSERT INTO DIADIEM VALUES('D04', N'PHAN THIẾT',N'CĂN CỨ')

SELECT * FROM DIADIEM

--- NHAP BANG NHANVIEN

INSERT INTO NHANVIEN VALUES('NV01', '452526457', N'HỒ THỊ ÁNH NGA',N'TP.HCM')

INSERT INTO NHANVIEN VALUES('NV02', '236724554', N'PHAN THI THÚY',N'TP.HCM')

INSERT INTO NHANVIEN VALUES('NV03', '869844638', N'LÊ THU HƯƠNG',N'TP.HCM')

INSERT INTO NHANVIEN VALUES('NV04', '469484732', N'PHAN THỊ NỞ',N'TP.HCM')

SELECT * FROM NHANVIEN

--- NHAP BANG KHACH

INSERT INTO KHACH VALUES('K01', '452526457', N'CHU VĂN AN',N'ĐỒNG THÁP')

INSERT INTO KHACH VALUES('K02', '448499520', N'HỒ CẨM ĐÀO',N'BẮC KINH')

INSERT INTO KHACH VALUES('K03', '938334782', N'PHẠM VĂN DỒNG',N'BẮC KẠN')

INSERT INTO KHACH VALUES('K04', '777723642', N'NGUYỄN TẤN DŨNG',N'CÀ MAU')

INSERT INTO KHACH VALUES('K05', '342476234', N'AN DƯƠNG VƯƠNG',N'THĂNG LONG')

INSERT INTO KHACH VALUES('K06', '143573957', N'G. BUSH',N'NEW YORK')

SELECT * FROM KHACH

--- NHAP BANG CHUYEN DI

INSERT INTO CHUYENDI VALUES('C01', '05/15/2010', '05/18/2010',N'TÂY NINH')

INSERT INTO CHUYENDI VALUES('C02', '05/21/2010', '05/22/2010',N'CỦ CHI')

SELECT * FROM CHUYENDI

--- NHAP BANG CD-NV

INSERT INTO CDNV VALUES('C01','NV01')

INSERT INTO CDNV VALUES('C02','NV02')

INSERT INTO CDNV VALUES('C01','NV03')

SELECT * FROM CDNV

--- NHAP BANG CD-XE

INSERT INTO CDXE VALUES('C01','XE1')

INSERT INTO CDXE VALUES('C01','XE3')

INSERT INTO CDXE VALUES('C02','XE2')

SELECT * FROM CDXE

--- NHAP BANG CD-DD

--- NHAP BANG CD-KH

INSERT INTO CDKH VALUES('C01','K01')

INSERT INTO CDKH VALUES('C01','K04')

INSERT INTO CDKH VALUES('C01','K05')

INSERT INTO CDKH VALUES('C02','K02')

INSERT INTO CDKH VALUES('C02','K04')

INSERT INTO CDKH VALUES('C03','K06')

SELECT * FROM CDKH

------- TRA LOI T-SQL

--- CÂU 1

SELECT B.MANV AS MÃ_NHÂN_VIÊN, TENNV AS tÊN_NHÂN_VIÊN

FROM NHANVIEN A, CDNV B

WHERE A.MANV = B.MANV AND MACH = 'C01'

--- CÂU 2

SELECT A.MAKH AS MÃ_KHÁCH_HÀNG, TENKH AS TÊN_KHÁCH_HÀNG

FROM KHACH A, CDKH B

WHERE A.MAKH = B.MAKH AND MACH = 'C01'

--- CÂU 4

SELECT COUNT(*) AS SỐ_XE_THỰC_HIỆN_CHUYẾN_ĐI

FROM CDXE

WHERE MACH = 'C01'

GROUP BY MACH

--- CÂU 5

SELECT COUNT(*) AS SỐ_XE_THỰC_HIỆN_CHUYẾN_ĐI

FROM CDXE A, CHUYENDI B

WHERE A.MACH = 'C01' AND A.MACH = B.MACH AND B.NGAYKH BETWEEN '05/14/2010' AND '05/16/2010'

GROUP BY A.MACH, B.MACH

--- CÂU 6

SELECT A.MACH AS MÃ_CHUYẾN_NHIỀU_KHÁCH_NHẤT, COUNT(*) AS SỐ_KHÁCH

FROM CDKH A, CHUYENDI B

WHERE A.MACH = 'C01' AND A.MACH = B.MACH AND NGAYKH BETWEEN '05/14/2010' AND '05/21/2010'

GROUP BY A.MACH, B.MACH

HAVING COUNT(*) >= ALL(

SELECT COUNT(*)

FROM CDKH A, CHUYENDI B

WHERE A.MACH = 'C01' AND A.MACH = B.MACH AND NGAYKH BETWEEN '05/14/2010' AND '05/21/2010'

GROUP BY A.MACH, B.MACH )