/* Su dung Query analyzer thuc hien cac cau hoi sau */

/* Cau 1: Tao mot Database QLDiem_TN gom cac bang sao */

create database QLDiem_TN

on primary /* Tap tin chinh */(name=QLDiem_TN,filename='D:\Sql Server\QLDiem_TN.mdf',size=10MB,maxsize=200MB,filegrowth=10%)

log on /* Tap tin luu vet */(name=QLDiem_TN_log,filename='D:\Sql Server\QLDiem_TN_log.ldf',size=20MB,maxsize=200MB,filegrowth=10%)

/* Tao bang */create table SINHVIEN(MSSV char(6) not null,TENSV varchar(30)not null,SODT varchar(10) not null,LOP char(6) not null,DIACHI char(50) not nullconstraint PK_SINHVIEN_MSSV primary key(MSSV)) 

create table DETAI(MSDT char(6) not null,TENDT varchar(30) not null,constraint PK_DETAI_MSDT primary key(MSDT))

create table SV_DETAI(MSSV char(6) not null,MSDT char(6) not null,constraint FK_SV_DETAI_MSSV foreign key(MSSV) references SINHVIEN(MSSV),constraint FK_SV_DETAI_MSDT foreign key(MSDT) references DETAI(MSDT))

create table HOCHAM(MSHH smallint not null,TENHH varchar(20) not null,constraint PK_HOCHAM_MSHH primary key(MSHH))

create table GIAOVIEN(MSGV smallint not null,TENGV varchar(30)not null,DIACHI varchar(50) not null,SODT varchar(10) not null,MSHHAM smallint not null,NAMHH smalldatetime not nullconstraint PK_GIAOVIEN_MSGV primary key(MSGV),constraint FK_GIAOVIEN_MSHHAM foreign key(MSHHAM) references HOCHAM(MSHH) )

create table HOCVI(MSHV smallint not null,TENHV varchar(10) not null,constraint PK_HOCVI_MSHV primary key(MSHV))

create table CHUYENNGANH(MSCN smallint not null,TENCN varchar(30) not null,constraint PK_CHUYENNGANH_MSCH primary key(MSCN))

create table GV_HV_CN(MSGV smallint not null,MSHV smallint not null,MSCN smallint not null,NAM smalldatetime not null,constraint PK_GV_HV_CN_MSGV_MSHV_MACN primary key(MSGV,MSHV,MSCN),constraint FK_GV_HV_CN_MSGV foreign key(MSGV) references GIAOVIEN(MSGV),constraint FK_GV_HV_CN_MSHV foreign key(MSHV) references HOCVI(MSHV),constraint FK_GV_HV_CN_MSCN foreign key(MSCN) references CHUYENNGANH(MSCN) )

create table GV_HDDT(MSGV smallint not null,MSDT char(6) not null,DIEM float not null,constraint PK_GV_HDDT_MSGV_MSDT primary key(MSGV,MSDT),constraint FK_GV_HDDT_MSGV foreign key(MSGV) references GIAOVIEN(MSGV),constraint FK_GV_HDDT_MSDT foreign key(MSDT) references DETAI(MSDT))

create table GV_PBDT(MSGV smallint not null,MSDT char(6) not null,DIEM float not null,constraint PK_GV_PBDT_MSGV_MADT primary key(MSGV,MSDT),constraint FK_GV_PBDT_MSGV foreign key(MSGV) references GIAOVIEN(MSGV),constraint FK_GV_PBDT_MSDT foreign key(MSDT) references DETAI(MSDT) )

create table GV_UVDT(MSGV smallint not null,MSDT char(6) not null,DIEM float not null,constraint PK_GV_UVDT_MSGV_MSDT primary key (MSGV,MSDT),constraint FK_GV_UVDT_MSGV foreign key(MSGV) references GIAOVIEN(MSGV),constraint FK_GV_UVDT_MSDT foreign key(MSDT) references DETAI(MSDT))

create table HOIDONG(MSHD smallint not null,PHONG smallint,TGBD datetime,NGAYHD smalldatetime not null,TINHTRANG varchar(30) not null,MSGVCTHD smallint not null,constraint PK_HOIDONG_MSHD primary key(MSHD),constraint FK_HOIDONG_MSGVCTHD foreign key(MSGVCTHD)references GIAOVIEN(MSGV) )

create table HOIDONG_GV(MSHD smallint not null,MSGV smallint not null,constraint PK_HOIDONG_GV_MSHD_MSGV primary key(MSHD,MSGV),constraint FK_HOIDONG_GV_MSHD foreign key(MSHD) references HOIDONG(MSHD),constraint FK_HOIDONG_GV_MSGV foreign key(MSGV) references GIAOVIEN(MSGV) )

create table HOIDONG_DT(MSHD smallint not null,MSDT char(6) not null,QUYETDINH char(10),constraint PK_HOIDONG_DT_MSHD_MSDT primary key(MSHD,MSDT),constraint FK_HOIDONG_DT_MSHD foreign key(MSHD) references HOIDONG(MSHD),constraint FK_HOIDONG_DT_MSDT foreign key(MSDT) references DETAI(MSDT))

/* Cau 2: Nhap du lieu cho cac table */

/* Nhap du lieu cho bang SINHVIEN */insert into SINHVIEN(MSSV,TENSV,DIACHI,LOP,SODT)select '97TH01','Nguyen Van An','12 NTMK','97TH01','9688543' unionselect '97TH02','Tran Hung','13/4 LCT','97TH01','6453443' unionselect '97TH03','Le Thuy Hang','24 Pasteur','97TH01','8544457' unionselect '97TH04','Ngo Khoa','54/12 LHP','97TH02','8545439' unionselect '97TH05','Pham Tai','12 HPT','97TH02','8149023' unionselect '97TH06','Dinh Tien','31 THD','97TH01','8956123'

/* Nhap du lieu cho bang DETAI */insert into DETAI(MSDT,TENDT)select '97001','Quan ly thu vien' unionselect '97002','Nhan dang van tay' unionselect '97003','Ban dau gia tren mang' union select '97004','Quan ly sieu thi' union select '97005','Xu ly anh' 



/* Nhap du lieu cho bang SV_DETAI */insert into SV_DETAIselect '97TH01','97004' unionselect '97TH02','97005' unionselect '97TH03','97001' unionselect '97TH04','97002' unionselect '97TH05','97003' unionselect '97TH06','97005'

/* Nhap du lieu cho bang HOCHAM */insert into HOCHAM(MSHH,TENHH)values('1','Pho giao su')insert into HOCHAM(MSHH,TENHH)values('2','Giao su')

/* Nhap du lieu cho bang GIAOVIEN */insert into GIAOVIEN(MSGV,TENGV,DIACHI,SODT,MSHHAM,NAMHH)select '1','Nguyen Van A','11 NVD','8754321','1','1/1/1996' unionselect '2','Tran Thu Trang','54 XVNT','8964334','1','1/1/1996' unionselect '3','Le Trung','12/5/CMTT','8903561','1','1/1/1996' unionselect '4','Nguyen Thi Loan','321 BTX','8012864','2','1/1/1997' unionselect '5','Chu Van Tien','1/60 TVD','8157906','2','1/1/1997'

/* Nhap du lieu cho bang HOCVI */insert into HOCVI(MSHV,TENHV)select '1','KS' unionselect '2','CN' union select '3','Th.S' union select '4','TS' union select '5','TSKH'

/* Nhap du lieu cho bang CHUYENNGANH*/insert into CHUYENNGANH(MSCN,TENCN)select '1','He thong thong tin' unionselect '2','Mang' unionselect '3','Do hoa' unionselect '4','Cong nghe phan mem'

/* Nhap du lieu cho bang GV_HV_CN*/insert into GV_HV_CN(MSGV,MSHV,MSCN,NAM)select '1','1','1','1/1/1999' unionselect '1','1','2','1/1/1999' unionselect '1','2','1','1/1/1998' unionselect '2','3','2','1/1/1997' unionselect '3','2','4','1/1/1997' unionselect '4','3','2','1/1/1996' 

/* Nhap du lieu cho bang GV_HDDT*/insert into GV_HDDT(MSGV,MSDT,DIEM)select '1','97001','7' unionselect '2','97002','8' unionselect '3','97003','9' unionselect '4','97004','8.5' unionselect '5','97005','7' 
/* Nhap du lieu cho bang GV_PBDT*/insert into GV_PBDT(MSGV,MSDT,DIEM)select '1','97005','5' unionselect '2','97001','7' unionselect '3','97004','6' unionselect '4','97003','8.5' unionselect '5','97002','8'

/* Nhap du lieu cho bang GV_UVDT*/insert into GV_UVDT(MSGV,MSDT,DIEM)select '5','97005','6' unionselect '2','97005','5' unionselect '4','97005','5' unionselect '3','97001','7' unionselect '4','97001','7' unionselect '5','97001','8' unionselect '3','97003','10' unionselect '1','97003','7' unionselect '2','97003','7' unionselect '1','97004','8' unionselect '2','97004','9' unionselect '3','97004','5' unionselect '1','97002','9' unionselect '4','97002','9' unionselect '5','97002','6'

/* Nhap du lieu cho bang HOIDONG*/insert into HOIDONG(MSHD,PHONG,TGBD,NGAYHD,TINHTRANG,MSGVCTHD)select '1','002','7:00','10/30/2001','That','1' unionselect '2','102','7:00','10/31/2001','Thu','2' unionselect '3','003','8:00','10/31/2001','That','3'

/* Nhap du lieu cho bang HOIDONG_GV*/insert into HOIDONG_GV(MSHD,MSGV)select '1','1' unionselect '1','2' unionselect '1','3' unionselect '1','4' unionselect '2','3' unionselect '2','2' unionselect '2','5' unionselect '2','4' union select '3','1' unionselect '3','2' unionselect '3','3' unionselect '3','4'

/* Nhap du lieu cho bang HOIDONG_DT*/insert into HOIDONG_DT(MSHD,MSDT,QUYETDINH)select '1','97001','Duoc' unionselect '1','97002','Duoc' unionselect '2','97003','Khong' unionselect '2','97004','Khong' unionselect '1','97005','Duoc' unionselect '3','97001','Khong' unionselect '3','97002','Duoc' unionselect '3','97004','Khong'

/* Cau III: Tao cac bang ao(view) nhu sau */

/* Cau1: Ung voi moi de tai cho biet: ten de tai, ten sinh vien tham gia, ten giao vien huong dan, ten giao vien phan bien */create view cau1asselect TENDT,TENSV, GV1.TENGV as [GIAO VIEN HUONG DAN],GV2.TENGV as [GIAO VIEN PHAN BIEN]from DETAI DT,SINHVIEN SV, SV_DETAI SV_DT,GIAOVIEN GV1, GIAOVIEN GV2,GV_HDDT,GV_PBDTwhere DT.MSDT=SV_DT.MSDT and SV.MSSV=SV_DT.MSSV and DT.MSDT= GV_HDDT.MSDT and DT.MSDT=GV_PBDT.MSDTand GV1.MSGV=GV_HDDT.MSGV and GV2.MSGV=GV_PBDT.MSGVand GV1.TENGV in(select GV1.TENGV from DETAI, GIAOVIEN,GV_HDDT where DT.MSDT=GV_HDDT.MSDT and GV1.MSGV=GV_HDDT.MSGV)and GV2.TENGV in(select GV2.TENGV from DETAI, GIAOVIEN,GV_PBDTwhere DT.MSDT=GV_PBDT.MSDT and GV2.MSGV=GV_PBDT.MSGV)goselect*from cau1

/* Cau 2: Ung voi moi giao vien cho biet ten de tai huong da */create view cau2asselect TENGV,TENDTfrom GIAOVIEN inner join GV_HDDT on GIAOVIEN.MSGV=GV_HDDT.MSGVinner join DETAI on DETAI.MSDT=GV_HDDT.MSDTgoselect *from cau2/* cach 2 */create view cau2_otherwiseas select TENGV,TENDTfrom GIAOVIEN,DETAI,GV_HDDTwhere(GIAOVIEN.MSGV=GV_HDDT.MSGV and GV_HDDT.MSDT=DETAI.MSDT) goselect *from cau2_otherwise

/*Cau 3: Ung voi moi giao vien cho biet: Ten de tai huong dan */create view cau3asselect TENGV,TENDTfrom DETAI,GIAOVIEN,GV_HDDTwhere (DETAI.MSDT=GV_HDDT.MSDT and GIAOVIEN.MSGV=GV_HDDT.MSGV) goselect *from cau3

create view cau3_otherwiseasselect TENGV,TENDTfrom DETAI inner join GV_HDDT on DETAI.MSDT=GV_HDDT.MSDTinner join GIAOVIEN on GIAOVIEN.MSGV=GV_HDDT.MSGVgoselect *from cau3_otherwise
/* Cau 4: Ung voi moi hoi dong bao ve "that" cho biet: ten de tai, quyet dinh duoc bao ve hay khong? */create view cau4asselect DETAI.TENDT,HOIDONG_DT.QUYETDINHfrom DETAI,HOIDONG_DT,HOIDONGwhere(DETAI.MSDT=HOIDONG_DT.MSDT AND HOIDONG.MSHD=HOIDONG_DT.MSHD AND HOIDONG.TINHTRANG='THU')goselect *from cau4
/*cau 5: ung voi moi hoi dong bao ve "that" cho biet: ten de tai, diem cuoi cung cua moi de tai(DIEMTB=diemGVHD+diem.GVPB+sum(diemGV+UV) */create view cau5 /* MÌNH CHUA LÀM RA :d BAN NAO LAM RA ROI GIUP MINH VOI NHÉ ]asselect DETAI.TENDT,avg(GV_HDDT.DIEM+GV_PBDT.DIEM) + sum(GV_UVDT.DIEM) as [DIEM TB]from DETAI,GV_UVDT,GV_HDDT,GV_PBDTwhere(DETAI.MSDT=GV_HDDT.MSDT and DETAI.MSDT=GV_PBDT.MSDT and GV_UVDT.MSDT=DETAI.MSDT)group by TENDTgoselect *from cau5

/* Cau 6: Ung voi moi hoi dong cho biet: Ten de tai, ten sinh vien,diem cua moi giao vien thuoc hoi dong */create view cau6asselect DETAI.TENDT,SINHVIEN.TENSV,GV_HDDT.DIEM AS[DIEM GV HDDT],GV_PBDT.DIEM AS[DIEM GV PBDT]from DETAI,SINHVIEN,GV_HDDT,GV_PBDT,SV_DETAIwhere(SINHVIEN.MSSV=SV_DETAI.MSSV AND GV_HDDT.MSDT=GV_PBDT.MSDT AND SV_DETAI.MSDT=DETAI.MSDT AND GV_HDDT.MSDT=SV_DETAI.MSDT AND GV_PBDT.MSDT=SV_DETAI.MSDT)goselect *from cau6

/*Cau7:ung voi moi hoi dong cho biet: thoi gian, ngay, so luong de tai bao ve. ten chu tich hoi dong */ create view cau7([THOI GIAN],[NGAY BAO VE],[SO LUONG DE TAI],[ TEN CTHD])asselect cast(datepart(hour,TGBD) as varchar)+'GIO',cast(day(NGAYHD) as varchar)+'-'+cast(month(NGAYHD)as varchar)+'-'+cast(year(NGAYHD)as varchar),count(MSDT),TENGVfrom HOIDONG T1,HOIDONG_DT T2,GIAOVIEN T3where T1.MSHD=T2.MSHD and MSGVCTHD=MSGV group by TGBD,NGAYHD,TENGVgoselect *from cau7

/* Cau 8: Ung voi moi hoi dong cho biet: Ten giao vien thuoc hoi dong va so dien thoai */create view cau8asselect TENGV,SODTfrom GIAOVIEN,HOIDONGwhere (GIAOVIEN.MSGV=HOIDONG.MSGVCTHD)goselect *from cau8