Giải bài tập CSDL Bài toán quản lý khoá học
USE MASTER
GO
DROP DATABASE CH_HT_QUAN_LY_KHOA_HOC
GO
CREATE DATABASE CH_HT_QUAN_LY_KHOA_HOC
GO
USE CH_HT_QUAN_LY_KHOA_HOC
GO
CREATE TABLE PHONGHOC
(
MAPHONG NVARCHAR(10) PRIMARY KEY,
SOLUONGNGUOITOIDA INT,
)
GO
CREATE TABLE THIETBI
(
MATB NVARCHAR(10) PRIMARY KEY,
TENTB NVARCHAR(500),
SOLUONGHIENTAI INT
)
GO
CREATE TABLE GIANGVIEN
(
MAGV NVARCHAR(10) PRIMARY KEY,
TENGV NVARCHAR(500),
NAMSINH INT,
PHAI BIT
)
GO
CREATE TABLE KHOAHOC
(
MAKH NVARCHAR(10) PRIMARY KEY,
NGAUBATDAU DATETIME,
SONGAY INT,
SONGUOITHAMGIA INT,
MAPHONG NVARCHAR(10)
)
GO
ALTER TABLE KHOAHOC
ADD CONSTRAINT FK_PHONGHOC_KHOAHOC FOREIGN
KEY (MAPHONG) REFERENCES PHONGHOC(MAPHONG)
GO
CREATE TABLE SUDUNGTHIETBI
(
MAKH NVARCHAR(10) ,
MATB NVARCHAR(10),
SOLUONGSUDUNG INT,
CONSTRAINT FP_SUDUNGTHIETBI PRIMARY
KEY (MAKH,MATB)
)
GO
ALTER TABLE SUDUNGTHIETBI
ADD CONSTRAINT FK_KHOAHOC_SUDUNGTHIETBI FOREIGN
KEY (MAKH) REFERENCES KHOAHOC(MAKH)
GO
ALTER TABLE SUDUNGTHIETBI
ADD CONSTRAINT FK_THIETBI_SUDUNGTHIETBI FOREIGN
KEY (MATB) REFERENCES THIETBI(MATB)
GO
CREATE TABLE GIANGVIENDAY
(
MAKH NVARCHAR(10) ,
MAGV NVARCHAR(10),
NGAYBATDAU DATETIME,
NGAYKETTHUC DATETIME,
CONSTRAINT FP_GIANGVIENDAY PRIMARY
KEY (MAKH,MAGV)
)
ALTER TABLE GIANGVIENDAY
ADD CONSTRAINT FK_KHOAHOC_GIANGVIENDAY FOREIGN
KEY (MAKH) REFERENCES KHOAHOC(MAKH)
GO
ALTER TABLE GIANGVIENDAY
ADD CONSTRAINT FK_GIAOVIEN_GIANGVIENDAY FOREIGN
KEY (MAGV) REFERENCES GIANGVIEN(MAGV)
GO
-----
INSERT INTO PHONGHOC VALUES('PH01',50)
GO
INSERT INTO PHONGHOC VALUES('PH02',45)
GO
INSERT INTO PHONGHOC VALUES('PH03',50)
GO
INSERT INTO PHONGHOC VALUES('PH04',30)
GO
INSERT INTO PHONGHOC VALUES('PH05',30)
GO
INSERT INTO PHONGHOC VALUES('PH06',45)
GO
-----
INSERT INTO THIETBI VALUES('TB01',N'Máy chiếu',10)
GO
INSERT INTO THIETBI VALUES('TB02',N'Máy tính xách tay',10)
GO
INSERT INTO THIETBI VALUES('TB03',N'Màn hình',10)
GO
INSERT INTO THIETBI VALUES('TB04',N'TV',10)
GO
INSERT INTO THIETBI VALUES('TB05','Máy quạt',10)
GO
INSERT INTO THIETBI VALUES('TB06','Máy lạnh',10)
GO
INSERT INTO THIETBI VALUES('TB07','Bàn giảng viên',10)
GO
-----
INSERT INTO GIANGVIEN VALUES('GV01',N'Nguyễn Văn An','1991','1')
GO
INSERT INTO GIANGVIEN VALUES('GV02',N'Nguyễn Văn Bình','1991','0')
GO
INSERT INTO GIANGVIEN VALUES('GV03',N'Nguyễn Văn Châu','1991','1')
GO
INSERT INTO GIANGVIEN VALUES('GV04',N'Nguyễn Văn Dân','1991','0')
GO
INSERT INTO GIANGVIEN VALUES('GV05',N'Nguyễn Văn Em','1991','1')
GO
-----
INSERT INTO KHOAHOC VALUES('KH01','6/6/2014',30,30,'PH06')
GO
INSERT INTO KHOAHOC VALUES('KH02','6/7/2014',35,15,'PH05')
GO
INSERT INTO KHOAHOC VALUES('KH03','6/8/2014',40,10,'PH04')
GO
INSERT INTO KHOAHOC VALUES('KH04','6/9/2014',45,60,'PH03')
GO
INSERT INTO KHOAHOC VALUES('KH05','6/10/2014',50,10,'PH01')
GO
INSERT INTO KHOAHOC VALUES('KH06','6/11/2015',30,10,'PH02')
GO
INSERT INTO KHOAHOC VALUES('KH07','6/11/2015',30,10,'PH02')
GO
-----
INSERT INTO SUDUNGTHIETBI VALUES('KH01','TB02','2')
GO
INSERT INTO SUDUNGTHIETBI VALUES('KH02','TB04','4')
GO
INSERT INTO SUDUNGTHIETBI VALUES('KH03','TB06','2')
GO
INSERT INTO SUDUNGTHIETBI VALUES('KH04','TB03','6')
GO
INSERT INTO SUDUNGTHIETBI VALUES('KH05','TB05','8')
GO
INSERT INTO SUDUNGTHIETBI VALUES('KH02','TB01','8')
GO
INSERT INTO SUDUNGTHIETBI VALUES('KH04','TB01','8')
GO
INSERT INTO SUDUNGTHIETBI VALUES('KH03','TB01','8')
GO
INSERT INTO SUDUNGTHIETBI VALUES('KH07','TB01','8')
GO
-----
INSERT INTO GIANGVIENDAY VALUES('KH01','GV01','6/6/2014','6/18/2014')
GO
INSERT INTO GIANGVIENDAY VALUES('KH05','GV02','6/8/2014','9/27/2014')
GO
INSERT INTO GIANGVIENDAY VALUES('KH04','GV05','6/7/2014','10/22/2014')
GO
INSERT INTO GIANGVIENDAY VALUES('KH03','GV04','6/5/2014','12/30/2014')
GO
INSERT INTO GIANGVIENDAY VALUES('KH02','GV03','6/9/2014','11/10/2014')
GO
INSERT INTO GIANGVIENDAY VALUES('KH02','GV01','6/9/2014','6/19/2014')
GO
INSERT INTO GIANGVIENDAY VALUES('KH03','GV01','6/5/2014','6/19/2014')
GO
INSERT INTO GIANGVIENDAY VALUES('KH07','GV01','6/11/2015','6/19/2016')
GO
--1/ Đại số quan hệ.
--A/ Liệt kê mã giảng viên đã giảng dạy
cả 2 khoá học với các mã khoá học là KH01 và KH02
SELECT MAGV FROM GIANGVIENDAY
WHERE NGAYKETTHUC<GETDATE()
AND MAKH IN('KH01','KH02')
GROUP BY MAGV
HAVING COUNT(MAKH)>=2
--B/ Liệt kê khoá học sử dụng ít nhất 1
thiết bị tên là máy chiếu
--C1
SELECT MAKH FROM SUDUNGTHIETBI
WHERE EXISTS
(SELECT MATB
FROM THIETBI
WHERE SUDUNGTHIETBI.MATB=THIETBI.MATB
AND TENTB LIKE N'%Máy chiếu%')
--c2
SELECT MAKH FROM SUDUNGTHIETBI
WHERE
(SELECT COUNT(*) FROM THIETBI
WHERE SUDUNGTHIETBI.MATB=THIETBI.MATB
AND TENTB LIKE N'%Máy chiếu%')>0
--- C3
SELECT MAKH FROM SUDUNGTHIETBI SD INNER JOIN THIETBI TB ON SD.MATB=TB.MATB
WHERE TENTB = N'Máy chiếu'
GROUP BY MAKH
HAVING COUNT (SD.MATB)>=1
--2/
--a/ Danh sách gồm mã thiết bị, tên thiết
bị, chưa được dùng cho khoá học nào kể từ năm 2014
SELECT MATB FROM THIETBI WHERE MATB NOT IN(SELECT MATB FROM SUDUNGTHIETBI
WHERE MAKH IN(SELECT MAKH FROM GIANGVIENDAY WHERE YEAR(NGAYBATDAU)>=2014))
--b/ Danh sách gồm mã khoá học, ngày bắt
đầu, điều kiện khoá học này có số người tham gia lớn hơn 50
--kéo dài hơn 3 ngày có hơn 4 loại thiết
bị khác nhau được sử dụng.
SELECT A.MAKH,NGAYBATDAU FROM GIANGVIENDAY A, KHOAHOC B, SUDUNGTHIETBI C
WHERE A.MAKH=B.MAKH
AND B.MAKH=C.MAKH
AND DATEDIFF(DD,NGAYBATDAU,NGAYKETTHUC)>3
AND SONGUOITHAMGIA>50
---- MỖI THIẾT BỊ KHÁC NHAU(*)
AND (SELECT COUNT(MATB) FROM SUDUNGTHIETBI C WHERE C.MAKH=B.MAKH GROUP BY MAKH)>4
--C/ Cho ra danh sách gồm MAGV, TENGV có
tổng số ngày giảng dạy cho tất cả các khoá học là nhiều nhất
SELECT GV.MAGV,TENGV
FROM GIANGVIEN GV INNER JOIN GIANGVIENDAY GVD ON GV.MAGV=GVD.MAGV
GROUP BY GV.MAGV,TENGV
HAVING SUM(DATEDIFF(DD,NGAYBATDAU,NGAYKETTHUC))>=
ALL(SELECT SUM(DATEDIFF(DD,NGAYBATDAU,NGAYKETTHUC)) FROM GIANGVIENDAY GROUP BY MAGV)
--d/ Danh sách gồm mã thiết bị, tên thiết
bị mà được sử dụng cho các khoá học được diễn ra trong ngày hôm nay.
SELECT TB.MATB, TENTB FROM SUDUNGTHIETBI SD
INNER JOIN THIETBI TB ON SD.MATB=TB.MATB
INNER JOIN KHOAHOC KH ON SD.MAKH=KH.MAKH
INNER JOIN GIANGVIENDAY GVD ON KH.MAKH=GVD.MAKH
WHERE GVD.NGAYBATDAU<GETDATE()
AND NGAYKETTHUC>GETDATE()
--C2
SELECT A.MATB, TENTB
FROM THIETBI A, SUDUNGTHIETBI B, KHOAHOC C, GIANGVIENDAY D
WHERE A.MATB=B.MATB
AND B.MAKH=
C.MAKH
AND D.MAKH=C.MAKH
AND GETDATE() BETWEEN NGAYBATDAU AND NGAYKETTHUC
No comments:
Post a Comment