Share 31 bài tập SQL quản lý sinh viên

Code dưới đây mình tự làm, căn bản đả hoàn chỉnh, có gì thiếu sót các bạn bổ sung nhé 

/* Cau 1: Tao CSDL, cac bang va cac khoa ngoai */

create database QLDIEM

 on primary 

  (

    name =QLDIEM,

    filename ='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\QLDIEM.mdf',

    size=50mb,

    maxsize=200mb,

    filegrowth=10mb 

  )

  log on 

   (

      name=QLDIEM_log,

      filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\QLDIEM_log.ldf',

      size=20mb,

      maxsize=100mb,

)      filegrowth=10mb

   )

   go

   create table KHOA

   (

MAKHOA char(5)not null,

TENKHOA nvarchar(30) not NULL,

constraint pk_KHOA_makhoa primary  key (MAKHOA)

)

create table SINHVIEN

      (

HOSV nvarchar(50) not null,

TEN nvarchar(50) not null,

MASV char(5) not null,

NGAYSINH datetime not null,

PHAI char(3) not null,

MAKHOA char(5)not null,

constraint pk_SINHVIEN_MASV primary key(MASV),

constraint fk_SINHVIEN_MAKHOA foreign key(MAKHOA)references KHOA(MAKHOA)

)

create table MONHOC

(

TENMH  nvarchar(50) not null,

MAMH char(5) not null,

SOTIET int not null,

constraint pk_MONHOC_MAMH primary key(MAMH)

)

create table KETQUA

(

MASV char(5) not null,

MAMH char(5) not null,

LANTHI numeric,

DIEM numeric,

constraint pk_KETQUA_MASV_MAMH_LANTHI primary key(MASV,MAMH,LANTHI),

constraint fk_KETQUA_MAMH foreign key(MAMH) references MONHOC(MAMH),

constraint fk_ketqua_masv foreign key(MASV) references SINHVIEN(MASV)

)

/* Cau 2: Xoa cac rang buoc khoa ngoai tham chieu den bang SINHVIEN va bang MONHOC */

alter table KETQUA drop constraint FK_KETQUA_MASV

alter table KETQUA drop constraint FK_KETQUA_MAMH

/* Cau 3: Xoa bang KHOA va MONHOC */

alter table SINHVIEN drop constraint FK_SINHVIEN_MAKHOA

Drop table KHOA

Drop table MONHOC

/* Cau 4: Tao lai cac bang va cac khoa ngoai da xoa o cau 2 va cau 3 */

create table KHOA /* TAO LAI BANG KHOA*/

   (

MAKHOA char(5)not null,

TENKHOA nvarchar(30) not NULL,

constraint pk_KHOA_makhoa primary  key (MAKHOA)

create table MONHOC /* tao lai bang MONHOC */

(

TENMH  nvarchar(50) not null,

MAMH char(5) not null,

SOTIET int not null,

constraint pk_MONHOC_MAMH primary key(MAMH)

/* Tao lai cac khoa ngoai */

alter table SINHVIEN add constraint FK_SINHVIEN_MAKHOA foreign key (MAKHOA) references KHOA(MAKHOA)

alter table KETQUA add constraint FK_KETQUA_MASV foreign key (MASV) references SINHVIEN(MASV)

alter table KETQUA add constraint FK_KETQUA_MAMH foreign key (MAMH) references MONHOC(MAMH)

/*Cau 5: Nhap du lieu cho cac bang */

/* Nhap bang KHOA */

insert into KHOA

values('AVAN','Khoa anh van')

insert into KHOA

values('CNTT','Khoa cong nghe thong tin')

insert into KHOA

values('DTVT','Khoa dien tu vien thonh')

insert into KHOA

values('QTKD','Khoa quan tri kinh doanh')

/*Nhap bang SINHVIEN */

insert into SINHVIEN

values('Tran Minh','Son','S001','05/01/1985','NAM','CNTT')

insert into SINHVIEN

values('Nguyen quoc','bao','S002','05/16/1986','NAM','CNTT')

insert into SINHVIEN

values('Phan Anh','Tung','S003','12/20/1983','Nam','QTKD')

insert into SINHVIEN

values('Bui thi Anh','Thu','S004','02/01/1985','Nu','QTKD')

insert into SINHVIEN

values('Le Thi','Lan','S005','07/03/1987','Nu','DTVT')

insert into SINHVIEN

values('Nguyen Thi','Lam','S006','11/11/1984','Nu','DTVT')

insert into SINHVIEN

values('Phan Thi','Ha','S007','07/03/1988','Nu','CNTT')

insert into SINHVIEN

values('Tran The','Dung','S008','10/21/1985','Nam','CNTT')

/* Nhap bang MONHOC */

insert into MONHOC

values('Anh van','AV','45')

insert into MONHOC

values('Co so du lieu','CSDL','45')

insert into MONHOC

values('Ke toan tai chinh','KTTC','45')

insert into MONHOC

values('Toan cao cap','TCC','60')

insert into MONHOC

values('Tin hoc van phong','THVP','30')

insert into MONHOC

values ('Tri tue nhan tao','TTNT','60')

insert into MONHOC

values ('Ki thuat ap trinh','KTLT','60')

/* Nhap bang KETQUA */

insert into KETQUA

values('S001','CSDL','1','4')

insert into KETQUA

values('S001','TCC','1','6')

insert into KETQUA

values('S002','CSDL','1','3')

insert into KETQUA

values('S002','CSDL','2','6')

insert into KETQUA

values('S003','KTTC','1','5')

insert into KETQUA

values('S004','AV','1','8')

insert into KETQUA

values('S004','THVP','1','4')

insert into KETQUA

values('S004','THVP','2','8')

insert into KETQUA

values('S006','TCC','1','5')

insert into KETQUA

values('S007','AV','1','2')

insert into KETQUA

values('S007','AV','2','9')

insert into KETQUA

values('S007','KTLT','1','6')

insert into KETQUA

values('S008','AV','1','7')

/* Cau 6. Sua doi so tiet cua mon "Tri tue nhan tao" la 50  --> Li thuyet trang 71*/

update MONHOC

set SOTIET=50 where TENMH='Tri tue nhan tao'

/* Hien thi bang */

select *from SINHVIEN

/* Cau 7: Xoa ket qua cua sinh vien co ma sinh vien S001 */

delete KETQUA

where MASV='S001'

/* Cau 8: Chen lai cac bo sinhvien co ma sinh vien S001 */

insert into KETQUA

values('S001','CSDL','1','4')

insert into KETQUA

values('S001','TCC','1','6')

/* Cau 9: Sua sinh vien "Le Thi Lan" thnh "Nguyen Thanh Son" va phai thanh 'Nam'*/

update SINHVIEN

set HOSV='Nguyen Thanh', TEN='Son',PHAI='Nam'

where MASV='S005' 

/* Cau 10: Chuyen sinh vien ' Le Thi Lam' sang khoa CNTT */

update SINHVIEN

set MAKHOA='CNTT'

where MASV='S006'

/* Cau 11: Cho biet ket qua hoc tap cua sinh vien co ma S007 */

Select * from SINHVIEN

where MASV='S007'

/* Cau 12: Liet ke nhưng sinh vien vao ngay 03 thang 07 */

select *from SINHVIEN

where DAY(NGAYSINH)='03' and MONTH(NGAYSINH)='07' /* Ly thuyet trang 83 */

/* Cau 13: Cho biet sinh vien ma HO co chua chu 'Anh' */

select *from SINHVIEN

where HOSV like('%Anh%')           /* Ly thuyet trang 79 */

/* Cau14: Cho biet ket qua hoc tap gom: HOSV,TEN,MAMH,LANTHI,DIEM cua sinh vien co ma S007 */

select HS.HOSV,HS.TEN,KQ.MAMH,KQ.LANTHI,KQ.DIEM

from SINHVIEN HS INNER JOIN KETQUA KQ ON HS.MASV = KQ.MASV /*INNER JOIN de ket noi du lieu gia 2 bang */

where (KQ.MASV='S007')

/* Cach # Cau 14 */

select SINHVIEN.HOSV,SINHVIEN.TEN,KETQUA.MAMH,KETQUA.LANTHI,KETQUA.DIEM

from SINHVIEN inner join KETQUA on SINHVIEN.MASV=KETQUA.MASV

where KETQUA.MASV ='S007'

/* Cau 15: Cho biet ket qua hoc tap gom: HOSV, TENSV,TENMH,LANTHI,DIEM */

/* Giong cau 14 */

/* Cau 16: Cho biet danh sach sinh vien Nam khoa 'Cong nghe thong tin' sap tang dan theo ten */

select  SINHVIEN.TEN,TENKHOA

from SINHVIEN inner join KHOA on SINHVIEN.MAKHOA=KHOA.MAKHOA

where SINHVIEN.PHAI='Nam' and SINHVIEN.MAKHOA='CNTT'

order by SINHVIEN.TEN

/* Cau 17: Cho biet danh sach cac mon hoc tren 40 tiet va co ten bat dau bang bang chu T */

select * from MONHOC 

where SOTIET>40 and TENMH like 't%'

/* or */

select *from MONHOC

where MAMH like't%' and SOTIET>=40

/* Cau 18: Liet ke nhung sinh vien tu 22 tuoi tro len khoa 'Cong nghe thong tin' va khoa 'Dien tu vien thong */

select *from SINHVIEN

where ((year (getdate())-year(NGAYSINH))>22 and MAKHOA='CNTT' or MAKHOA='DTVT')

/*  Cau 19: Cho biet ten khoa nao khong co sinh vien */

select KHOA.TENKHOA

from KHOA left outer join SINHVIEN on SINHVIEN.MAKHOA=KHOA.MAKHOA

where SINHVIEN.MAKHOA is null

/* Cau 20: Cho biet danh sach sinh vien chua hoc mon nao, thong tin gom: Ho, Ten,Ngay sinh,ten khoa */

select SINHVIEN.HOSV, SINHVIEN.TEN,SINHVIEN.NGAYSINH, KHOA.TENKHOA

from SINHVIEN left join KETQUA on KETQUA.MASV=SINHVIEN.masv

inner join KHOA on SINHVIEN.MAKHOA=khoa.MAKHOA

where ketqua.MASV is null

/* Cau 21: Cho biet nhung sinh vien da hoc mon co ma AV, sap giam dan theo theo ngay sinh */

/* Thong tin gom: MASV,HOSV,TENSV,NGAYSINH,TENKHOA */

select  SINHVIEN.MASV,HOSV,TEN,NGAYSINH,KHOA.TENKHOA

from SINHVIEN left join KETQUA on SINHVIEN.MASV=KETQUA.MASV

inner join KHOA on SINHVIEN.MAKHOA=KHOA.MAKHOA

where SINHVIEN.MASV=KETQUA.MASV and MAMH='AV'

/* Câu 22: Cho biet ten sinh vien co diem cao nhat */

select SINHVIEN.HOSV, SINHVIEN.TEN, KETQUA.DIEM

from SINHVIEN inner join KETQUA on SINHVIEN.MASV=KETQUA.MASV

where DIEM=(select MAX(DIEM) from KETQUA)

/* Cau 23: Cho biet sinh vien nào rot môn CSDL ở lan thi thu 1 */

select SINHVIEN.HOSV,SINHVIEN.TEN

from SINHVIEN inner join KETQUA on SINHVIEN.MASV=KETQUA.MASV

where (KETQUA.MAMH='CSDL' and KETQUA.DIEM<5 and KETQUA.LANTHI=1)

/* Cau 24: Cho biet sinh vien nao rot mon CSDL o lan thi thu nhat ma chua thi lai lan 2*/

select SINHVIEN.HOSV,SINHVIEN.TEN

from SINHVIEN inner join KETQUA on SINHVIEN.MASV=KETQUA.MASV

where KETQUA.MAMH='CSDL' and KETQUA.DIEM<5 and KETQUA.LANTHI=1 

  and SINHVIEN.MASV not in (select MASV from KETQUA where MAMH='CSDL'and LANTHI=2 )

/* Cau 25: Dem so luong sinh vien khoa 'Quan tri kinh doanh' */

select KHOA.TENKHOA, count(*)as SOLUONGSV 

from SINHVIEN inner join KHOA on SINHVIEN.MAKHOA=KHOA.MAKHOA

where KHOA.MAKHOA='QTKD'

group by KHOA.TENKHOA

/* Cau 26: Dem so luong sinh vien cua tung khoa */

select KHOA.TENKHOA, count(*) as SOLUONGSV

from SINHVIEN inner join KHOA on SINHVIEN.MAKHOA=KHOA.MAKHOA

group by KHOA.TENKHOA

/* Cau 27: Cho biet ten khoa dong sinh vien nhat */

select TENKHOA, count(*) as [So luong]

from SINHVIEN inner join KHOA on SINHVIEN.MAKHOA=KHOA.MAKHOA

group by TENKHOA 

having count(*)>=all

(select COUNT(*) as[So luong] from SINHVIEN,KHOA

where SINHVIEN.MAKHOA=KHOA.MAKHOA group by KHOA.MAKHOA)

/* Cau 28: Cho biet diem thap nhat cua moi mon hoc */

select TENMH, min(diem) as [DIEM THAP NHAT]

from MONHOC inner join KETQUA on MONHOC.MAMH=KETQUA.MAMH

group by TENMH

/* Cau 29: Cho biet ten mon hoc nao chua co sinh vien hoc */

select MONHOC.TENMH

from MONHOC 

where MAMH not in(select distinct MAMH from KETQUA)

/* distinct - hien thi danh sach gia tri khong trung lap - ly thuyet trang 93*/

/* Cau 30: Cho biet diem trung binh lan thi thu nhat cua sinh vien S004 */

select MASV,avg(DIEM) as [DIEM TRUNG BINH]

from KETQUA

where LANTHI='1' and MASV='S004'

group by MASV

/* Cau 31: Cho biet diem trung binh, diem lon nhat, diem nho nhat lan thi thu nhat cua tung sinh vien */

/* Thong tin gom: TENSV,TENKHOA,DIEMTB */

select SINHVIEN.HOSV,SINHVIEN.TEN, KHOA.TENKHOA, avg(DIEM) as [DIEM TB], max(DIEM) as [DIEM LON NHAT], min(DIEM)as [DIEM NHO NHAT]

from SINHVIEN,KETQUA,KHOA 

where SINHVIEN.MAKHOA=KHOA.MAKHOA and SINHVIEN.MASV = KETQUA.MASV

group by SINHVIEN.HOSV,SINHVIEN.TEN,KHOA.TENKHOA

/* Cau 31: Cach khac */

select SINHVIEN.HOSV,SINHVIEN.TEN, KHOA.TENKHOA, avg(DIEM) as [DIEM TB], max(DIEM) as [DIEM LON NHAT], min(DIEM)as [DIEM NHO NHAT]

from SINHVIEN inner join KETQUA on SINHVIEN.MASV=KETQUA.MASV

inner join KHOA on SINHVIEN.MAKHOA=KHOA.MAKHOA

group by SINHVIEN.HOSV,SINHVIEN.TEN,KHOA.TENKHOA

/* BAI TAP MINH TU RA DE VA TU GIAI CACS BAN THAM KHAO */

/* Dem so luong Nam va Nu trong bang SINHVIEN */

select PHAI,count(*) 

from SINHVIEN

group by PHAI

/* Tong So diem cua Hoc sinh theo MAHS */

select MAMH, SUM(DIEM)as TONGDIEM

from KETQUA

group by MAMH

/* Dem so luong thi sinh khoa CNTT va khoa QTKD */

select MAKHOA, count(*)

from SINHVIEN

group by MAKHOA

having (MAKHOA='CNTT' or MAKHOA='QTKD')

/* Chi hien ra nhung MAMH nao co tong diem 10 */

select MAMH , SUM(DIEM) as TONGDIEM

from KETQUA

group by  MAMH

having SUM(DIEM)>10

/* Chuc cac ban hoc tot! */