BAI 7: QUAN LY HOI NGHI.

------------- QUAN LY HOI NGHI -------------------------

IF DB_ID('QLHN') IS NOT NULL

DROP DATABASE QLHN

CREATE DATABASE QLHN

USE QLHN

-- TAO BANG BAO CAO VIEN

IF OBJECT_ID('BAOCAOVIEN') IS NOT NULL

DROP TABLE BAOCAOVIEN

CREATE TABLE BAOCAOVIEN

(

MABCV VARCHAR(10) NOT NULL PRIMARY KEY,

TENBCV NVARCHAR(30) NOT NULL,

HOCVI NVARCHAR(30) NOT NULL,

MATB VARCHAR(10) NOT NULL

)

--- TAO BANG CHUYENDE

IF OBJECT_ID('CHUYENDE') IS NOT NULL

DROP TABLE CHUYENDE

CREATE TABLE CHUYENDE

(

MACD VARCHAR(10) NOT NULL PRIMARY KEY,

TENCD NVARCHAR(30) NOT NULL

)

--- TAO BANG CD-BCV

IF OBJECT_ID('CDBCV') IS NOT NULL

DROP TABLE CDBCV

CREATE TABLE CDBCV

(

MACD VARCHAR(10) NOT NULL,

MABCV VARCHAR(10) NOT NULL,

PRIMARY KEY(MACD,MABCV)

)

--- TAO BANG TIEU BAN

IF OBJECT_ID('TIEUBAN') IS NOT NULL

DROP TABLE TIEUBAN

CREATE TABLE TIEUBAN

(

MATB VARCHAR(10) NOT NULL PRIMARY KEY,

TENTB NVARCHAR(30) NOT NULL,

CHUTOA VARCHAR(10)

)

--- TAO BANG TIEU BAN CD

IF OBJECT_ID('TIEUBANCD') IS NOT NULL

DROP TABLE TIEUBANCD

CREATE TABLE TIEUBANCD

(

MATB VARCHAR(10) NOT NULL,

MACD VARCHAR(10) NOT NULL,

TGBC DATETIME,

PRIMARY KEY(MATB,MACD)

)

--- TAO BANG PHONG

IF OBJECT_ID('PHONG') IS NOT NULL

DROP TABLE PHONG

CREATE TABLE PHONG

(

MAPH VARCHAR(10) NOT NULL,

TENPH NVARCHAR(10) NOT NULL,

SLCHO INT,

MATB VARCHAR(10) NOT NULL,

PRIMARY KEY(MAPH, MATB)

)

--- TAO BANG DAI BIEU

IF OBJECT_ID('DAIBIEU') IS NOT NULL

DROP TABLE DAIBIEU

CREATE TABLE DAIBIEU

(

MADB VARCHAR(10) NOT NULL PRIMARY KEY,

TENDB NVARCHAR(30) NOT NULL,

DCDB NVARCHAR(30)

)

--- TAO BANG DANG KY

IF OBJECT_ID('DANGKY') IS NOT NULL

DROP TABLE DANGKY

CREATE TABLE DANGKY

(

MACD VARCHAR(10) NOT NULL,

MADB VARCHAR(10) NOT NULL,

PRIMARY KEY(MACD,MADB)

)

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

--- NHAP BANG BAO CAO VIEN

INSERT INTO BAOCAOVIEN VALUES('BCV1', N'TRẦN QUỐC THẢO','TH.S', 'TB1')

INSERT INTO BAOCAOVIEN VALUES('BCV2', N'TRƯƠNG HỮU THẮNG','TH.S', 'TB2')

INSERT INTO BAOCAOVIEN VALUES('BCV3', N'HÀ VĨNH PHÚC','T.S', 'TB2')

INSERT INTO BAOCAOVIEN VALUES('BCV4', N'GIÓ THỊ LÀO','TH.S', 'TB1')

SELECT * FROM BAOCAOVIEN

--- NHAP BANG CHUYEN DE

INSERT INTO CHUYENDE VALUES('CD1', N'BUÔN LẬU XUYÊN BIÊN GIỚI')

INSERT INTO CHUYENDE VALUES('CD2', N'CÔNG NGHẸ THẾ KỶ 21')

INSERT INTO CHUYENDE VALUES('CD3', N'NHÁI XANH')

SELECT * FROM CHUYENDE

--- NHAP BANG CD-BCV

INSERT INTO CDBCV VALUES('CD3', 'BCV4')

INSERT INTO CDBCV VALUES('CD1', 'BCV1')

INSERT INTO CDBCV VALUES('CD2', 'BCV3')

SELECT * FROM CDBCV

--- NHAP BANG TIEUBAN

INSERT INTO TIEUBAN VALUES('TB1', N'CÂY THÔNG', 'BCV1')

INSERT INTO TIEUBAN VALUES('TB2', N'ẾCH ỘP', 'BCV3')

SELECT * FROM TIEUBAN

--- NHAP BANG TIEUBAN-CD

INSERT INTO TIEUBANCD VALUES('TB1', 'CD1', '05/16/2010')

INSERT INTO TIEUBANCD VALUES('TB2', 'CD3', '05/17/2010')

INSERT INTO TIEUBANCD VALUES('TB2', 'CD2', '05/14/2010')

SELECT * FROM TIEUBANCD

---- NHAP BANG PHONG

INSERT INTO PHONG VALUES('P01', N'PHÒNG 1', 2, 'TB1')

INSERT INTO PHONG VALUES('P02', N'PHÒNG 2', 1, 'TB2')

INSERT INTO PHONG VALUES('P03', N'PHÒNG 3', 2, 'TB2')

SELECT * FROM PHONG

--- NHAP BANG DAI BIEU

INSERT INTO DAIBIEU VALUES('DB01', N'HỒ SĨ NGHỊ', N'QUẬN 3')

INSERT INTO DAIBIEU VALUES('DB02', N'ĐỖ QUANG KHẢI', N'QUẬN 6')

SELECT * FROM DAIBIEU

--- NHAP BANG DANG KY

INSERT INTO DANGKY VALUES('CD1','DB01')

INSERT INTO DANGKY VALUES('CD1','DB02')

INSERT INTO DANGKY VALUES('CD2','DB02')

SELECT * FROM DANGKY

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

---CAU 1

SELECT TENPH AS PHÒNG_BC_CHUYÊN_ĐỀ_C01

FROM TIEUBANCD A, PHONG B

WHERE A.MATB = B.MATB AND MACD = 'CD1'

--- CAU 2

SELECT A.MABCV AS MÃ_BÁO_CÁO_VIÊN, TENBCV AS TÊN_BCV

FROM BAOCAOVIEN A, CDBCV B

WHERE A.MABCV = B.MABCV AND MACD = 'CD1'

--- CÂU 3

SELECT MABCV AS MÃ_BÁO_CÁO_VIEN, CHUTOA

FROM BAOCAOVIEN A, TIEUBAN B

WHERE A.MATB = B.MATB AND A.MATB = 'TB1'

--- CÂU 4

SELECT D.MADB AS ĐẠI_BIỂU_CỦA_P01, TENDB AS TÊN_ĐẠI_BIỂU

FROM TIEUBANCD A, PHONG B, DANGKY C, DAIBIEU D

WHERE B.MATB = A.MATB AND A.MACD = C.MACD AND D.MADB = C.MADB AND MAPH = 'P01'

---- CÂU 6

SELECT MATB AS MÃ_TIỂU_BAN, A.MACD AS MÃ_CHUYÊN_ĐỀ, COUNT(*) AS SỐ_ĐB

FROM TIEUBANCD A, DANGKY B, DAIBIEU C

WHERE A.MACD = B.MACD AND B.MADB = C.MADB

GROUP BY A.MACD, B.MACD, MATB

--- CÂU 7

SELECT MACD AS MÃ_CHUYÊN_ĐỀ, COUNT(*) AS SỐ_ĐB_NHIỀU_NHẤT

FROM DANGKY A, DAIBIEU B

WHERE A.MADB = B.MADB

GROUP BY MACD

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

FROM DANGKY A, DAIBIEU B

WHERE A.MADB = B.MADB

GROUP BY MACD)

--- CÂU 8

SELECT MATB AS MÃ_TIỂU_BAN, A.MACD AS CHUYÊN_ĐỀ_NHIỀU_ĐB_NHẤT , COUNT(*) AS SỐ_ĐB

FROM TIEUBANCD A, DANGKY B

WHERE A.MACD = B.MACD

GROUP BY MATB, A.MACD, B.MACD

HAVING COUNT(*) >= ANY (SELECT COUNT(*) FROM TIEUBANCD A, DANGKY B WHERE A.MACD = B.MACD GROUP BY MATB)