Home Home » bài tập SQL » SQL Server » Tài liệu CNTT » Share 31 bài tập SQL quản lý sinh viên

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

Thứ Ba, 3 tháng 4, 2012
Sử dụng Query Analyzer thực hiện các câu hỏi sau

*Tạo một database QLdiem SV gồm các table sau:

- Table SINHVIEN




-Table KHOA

 -Table MONHOC


 -Table KETQUA


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é 

31 câu mình ghi đề và làm trực tiếp lên bài viết luôn nhé

Xem thêm: http://kenhdaihoc.com/forum/showthread.php?t=3052

Download file SQL http://www.mediafire.com/?khk0n25p95z4x67
/* 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! */

Chia sẻ bài viết
Bài liên quan

Comments[ 0 ]


Đăng nhận xét