Giải bài tập CSDL Bài toán quản lý đề tài(Đề CH ĐHQGHCM)

Giải bài tập CSDL Bài toán quản lý đề tài

USE MASTER

GO

DROP DATABASE CH_HT_QUAN_LY_DE_TAI_SINH_VIEN

GO

CREATE DATABASE CH_HT_QUAN_LY_DE_TAI_SINH_VIEN

GO

USE CH_HT_QUAN_LY_DE_TAI_SINH_VIEN

GO

CREATE TABLE GIANGVIEN

(

       MAGV NVARCHAR(10) PRIMARY KEY,

       HOTENGIANGV NTEXT

)

GO

CREATE TABLE SINHVIEN

(

       MASV NVARCHAR(10) PRIMARY KEY,

       HOTENSV NVARCHAR(200),

       NAMNHAPHOC INT

)

GO

CREATE TABLE LOAIDETAI

(

       MALOAI NVARCHAR(10) PRIMARY KEY,

       TENLOAI NTEXT

)

GO

CREATE TABLE DETAI

(

       MADETAI NVARCHAR(10) PRIMARY KEY,

       TENDETAI NTEXT,

       MAGV NVARCHAR(10),

       MALOAI NVARCHAR(10)

)

GO

ALTER TABLE DETAI

ADD CONSTRAINT FK_GIANGVIEN_DETAI FOREIGN KEY (MAGV) REFERENCES GIANGVIEN(MAGV)

GO

ALTER TABLE DETAI

ADD CONSTRAINT FK_LOAIDETAI_DETAI FOREIGN KEY (MALOAI) REFERENCES LOAIDETAI(MALOAI)

GO

CREATE TABLE DETAI_SINHVIEN

(

       MASV NVARCHAR(10),

       MADETAI NVARCHAR(10),

       CONSTRAINT  FP_DETAI_SINHVIEN PRIMARY KEY (MADETAI,MASV)

)

GO

ALTER TABLE DETAI_SINHVIEN

ADD CONSTRAINT FK_DETAI_DETAI_SINHVIEN FOREIGN KEY (MADETAI) REFERENCES DETAI(MADETAI)

GO

ALTER TABLE DETAI_SINHVIEN

ADD CONSTRAINT FK_SINHVIEN_DETAI_SINHVIEN FOREIGN KEY (MASV) REFERENCES SINHVIEN(MASV)

GO

-----

INSERT INTO GIANGVIEN VALUES('GV01',N'Nguyễn Vân Anh')

GO

INSERT INTO GIANGVIEN VALUES('GV02',N'Nguyễn Vân Ah')

GO

INSERT INTO GIANGVIEN VALUES('GV03',N'Nguyễn Vân AK')

GO

INSERT INTO GIANGVIEN VALUES('GV04',N'Nguyễn Vân ACh')

GO

INSERT INTO GIANGVIEN VALUES('GV05',N'Nguyễn Vân h')

GO

-----

INSERT INTO SINHVIEN VALUES('SV01',N'Phạm Khánh An',2009)

GO

INSERT INTO SINHVIEN VALUES('SV02',N'Phạm Khả Hân',2009)

GO

INSERT INTO SINHVIEN VALUES('SV03',N'Phạm Thái Luân',2010)

GO

INSERT INTO SINHVIEN VALUES('SV04',N'Phạm Ánh Loan',2011)

GO

INSERT INTO SINHVIEN VALUES('SV05',N'Phạm Linh Ngân',2012)

GO

-----

INSERT INTO LOAIDETAI VALUES('LOAI01',N'Lý thuyết')

GO

INSERT INTO LOAIDETAI VALUES('LOAI02',N'Thực hành')

GO

-----

INSERT INTO DETAI VALUES('DT01',N'Phân tích ứng dụng nhận dạng biển số xe','GV01','LOAI01')

GO

INSERT INTO DETAI VALUES('DT02',N'Xếp thời khóa biểu','GV02','LOAI02')

GO

INSERT INTO DETAI VALUES('DT03',N'Đề tài A','GV03','LOAI02')

GO

INSERT INTO DETAI VALUES('DT04',N'Đề tài B','GV04','LOAI02')

GO

INSERT INTO DETAI VALUES('DT05',N'Đề tài C','GV01','LOAI01')

GO

INSERT INTO DETAI VALUES('DT06',N'Đề tài D','GV02','LOAI01')

GO

INSERT INTO DETAI VALUES('HT1',N'Đề tài D','GV02','LOAI01')

GO

INSERT INTO DETAI VALUES('HT2',N'Đề tài D','GV02','LOAI01')

GO

-----

INSERT INTO DETAI_SINHVIEN VALUES('SV01','DT05')

GO

INSERT INTO DETAI_SINHVIEN VALUES('SV02','DT04')

GO

INSERT INTO DETAI_SINHVIEN VALUES('SV03','DT03')

GO

INSERT INTO DETAI_SINHVIEN VALUES('SV04','DT02')

GO

INSERT INTO DETAI_SINHVIEN VALUES('SV03','DT02')

GO

INSERT INTO DETAI_SINHVIEN VALUES('SV05','DT01')

GO

INSERT INTO DETAI_SINHVIEN VALUES('SV05','HT1')

GO

INSERT INTO DETAI_SINHVIEN VALUES('SV04','HT1')

GO

INSERT INTO DETAI_SINHVIEN VALUES('SV05','HT2')

GO

--1 Đại số quan hệ

--a/ Mã sinh viên đã tham gia 2 đề tài HT1 và HT2

SELECT  A.MASV FROM DETAI_SINHVIEN A INNER JOIN SINHVIEN B ON A.MASV=B.MASV

WHERE MADETAI IN ('HT1','HT2')

GROUP BY A.MASV

HAVING COUNT(MADETAI)>1

--Cách 2

SELECT  A.MASV FROM DETAI_SINHVIEN A INNER JOIN SINHVIEN B ON A.MASV=B.MASV

WHERE  MADETAI = 'HT1'

AND A.MASV IN(SELECT  A.MASV FROM DETAI_SINHVIEN A INNER JOIN SINHVIEN B ON A.MASV=B.MASV

WHERE  MADETAI = 'HT2')

--b Mã đề tài được thực hiện bởi sinh viên "Phạm Khánh An"

SELECT  MADETAI FROM DETAI_SINHVIEN A INNER JOIN SINHVIEN B ON A.MASV=B.MASV

WHERE HOTENSV =N'Phạm Khánh An'

--2/ viết sql

--a/ Mã sinh viên, Tên sinh viên làm ít nhất 2 loại đề tài khác nhau

SELECT SV.MASV,SV.HOTENSV FROM DETAI_SINHVIEN DTSV INNER JOIN SINHVIEN SV

ON SV.MASV=DTSV.MASV INNER JOIN DETAI DT ON DTSV.MADETAI=DT.MADETAI

GROUP BY SV.MASV,SV.HOTENSV

HAVING COUNT(MALOAI)>=2

--b/ Mã đề tài, Tên đề tài, tên giảng viên, duy nhất có 1 sinh viên tham gia

SELECT DT.MADETAI,TENDETAI,HOTENGIANGV FROM DETAI DT INNER JOIN GIANGVIEN GV

ON DT.MAGV=GV.MAGV

WHERE DT.MADETAI IN

(SELECT DT.MADETAI FROM DETAI DT INNER JOIN GIANGVIEN GV

ON DT.MAGV=GV.MAGV INNER JOIN DETAI_SINHVIEN DTSV ON  DTSV.MADETAI=DT.MADETAI

GROUP BY DT.MADETAI

HAVING COUNT(MASV)=1)

--c/ Mã giảng viên, họ tên giảng viên, hướng dẫn nhiều đề tài nhất

  SELECT GV.MAGV,GV.HOTENGIANGV FROM GIANGVIEN GV

 WHERE GV.MAGV IN(SELECT GV.MAGV FROM GIANGVIEN GV INNER JOIN DETAI DT ON DT.MAGV=GV.MAGV

 GROUP BY GV.MAGV

 HAVING COUNT(MADETAI)>=ALL(SELECT  COUNT(MADETAI) FROM DETAI GROUP BY MAGV))