Bài tập quản lí sinh viên SQL

QUẢN LÍ SINH VIÊN

/*=====================Create DataBase======================*/

Bạn đang xem: Bài tập quản lí sinh viên SQL

use master
go
if exists(select name from sysdatabases where name='QuanLyDiemSV')
drop Database QuanLyDiemSV
go
Create Database QuanLyDiemSV
go

use QuanLyDiemSV
go

/*=============DANH MUC KHOA==============*/

Create table DMKhoa
(
MaKhoa char(2) primary key,
TenKhoa nvarchar(30)not null,
)

/*==============DANH MUC SINH VIEN============*/

Create table DMSV
(
MaSV char(3) not null primary key,
HoSV nvarchar(15) not null,
TenSV nvarchar(7)not null,
Phai nchar(7),
NgaySinh datetime not null,
NoiSinh nvarchar (20),
MaKhoa char(2),
HocBong float,

)

/*===================MON HOC========================*/

create table DMMH
(
MaMH char (2) not null,
TenMH nvarchar (25)not null,
SoTiet tinyint
Constraint DMMH_MaMH_pk primary key(MaMH)
)

/*=====================KET QUA===================*/

Create table KetQua
(
MaSV char(3) not null,
MaMH char (2)not null ,
LanThi tinyint,
Diem decimal(4,2),
Constraint KetQua_MaSV_MaMH_LanThi_pk primary key (MaSV,MaMH,LanThi)
)

/*==========================TAO KHOA NGOAI==============================*/
Alter table dmsv
add Constraint DMKhoa_MaKhoa_fk foreign key (MaKhoa)
References DMKhoa (MaKhoa)
Alter table KetQua
add constraint KetQua_MaSV_fk foreign key (MaSV) references DMSV (MaSV),
constraint DMMH_MaMH_fk foreign key (MaMH) references DMMH (MaMH)

/*==================NHAP DU LIEU====================*/

/*==============NHAP DU LIEU DMMH=============*/
Insert into DMMH(MaMH,TenMH,SoTiet)
values('01',N'Cơ Sở Dữ Liệu',45)
Insert into DMMH(MaMH,TenMH,SoTiet)
values('02',N'Trí Tuệ Nhân Tạo',45)
Insert into DMMH(MaMH,TenMH,SoTiet)
values('03',N'Truyền Tin',45)
Insert into DMMH(MaMH,TenMH,SoTiet)
values('04',N'Đồ Họa',60)
Insert into DMMH(MaMH,TenMH,SoTiet)
values('05',N'Văn Phạm',60)

/*==============NHAP DU LIEU DMKHOA=============*/
Insert into DMKhoa(MaKhoa,TenKhoa)
values('AV',N'Anh Văn')
Insert into DMKhoa(MaKhoa,TenKhoa)
values('TH',N'Tin Học')
Insert into DMKhoa(MaKhoa,TenKhoa)
values('TR',N'Triết')
Insert into DMKhoa(MaKhoa,TenKhoa)
values('VL',N'Vật Lý')

/*==============NHAP DU LIEU DMSV=============*/

SET DATEFORMAT DMY
GOInsert into DMSV
values('A01',N'Nguyễn Thị',N'Hải',N'Nữ','23/02/1990',N'Hà Nội','TH',130000)
Insert into DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
values('A02',N'Trần Văn',N'Chính',N'Nam','24/12/1992',N'Bình Định','VL',150000)
Insert into DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
values('A03',N'Lê Thu Bạch',N'Yến',N'Nữ','21/02/1990',N'TP Hồ Chí Minh','TH',170000)
Insert into DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
values('A04',N'Trần Anh',N'Tuấn',N'Nam','20/12/1990',N'Hà Nội','AV',80000)
Insert into DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
values('B01',N'Trần Thanh',N'Mai',N'Nữ','12/08/1991',N'Hải Phòng','TR',0)
Insert into DMSV(MaSV,HoSV,TenSV,Phai,NgaySinh,NoiSinh,MaKhoa,HocBong)
values('B02',N'Trần Thị Thu',N'Thủy',N'Nữ','02/01/1991',N'TP Hồ Chí Minh','AV',0)

/*==============NHAP DU LIEU BANG KET QUA=============*/

Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A01','01',1,3)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A01','01',2,6)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A01','02',2,6)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A01','03',1,5)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A02','01',1,4.5)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A02','01',2,7)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A02','03',1,10)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A02','05',1,9)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A03','01',1,2)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A03','01',2,5)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A03','03',1,2.5)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A03','03',2,4)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('A04','05',2,10)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('B01','01',1,7)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('B01','03',1,2.5)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('B01','03',2,5)
Insert into KetQua(MaSV,MaMH,LanThi,Diem) values('B02','02',1,6)
Insert into KetQua(MaSV,MaMH,LanThi,Diem)
values('B02','04',1,10)

/*===============CAP NHAT THONG TIN=================*/

--câu 2--
update dmmh
set sotiet=45
where mamh='05'
--câu 3,4---
update dmsv
set tensv=N'Kỳ',phai ='Nam'
where masv='b01'
-------câu 5-----
update dmsv
set ngaysinh='05/07/1990'
where masv='b02'
----------câu 6----------
update dmsv
set hocbong=hocbong+100000
where makhoa='Av'
-------câu 7----------
delete from ketqua
where lanthi=2 and diem<5
-------câu 8---------
--delete from dmsv
--where hocbong=0
--không được vì thế đem buộc ràng khóa nước ngoài --
--B.Truy vấn những câu đơn giản
--9. Liệt kê list SV, bao gồm những vấn đề sau: Mã SV, Họ SV, Tên
--sinh viên, Học bổng. Danh sách sẽ tiến hành bố trí theo đuổi trật tự Mã SV tăng dần dần.
select masv as 'Mã sinh viên',hosv as 'Họ sinh viên',tensv as 'Tên sinh viên',hocbong as 'Học Bổng'
from dmsv
order by masv asc

--10. Danh sách những SV bao gồm vấn đề sau: Mã SV, chúng ta thương hiệu SV, Phái, Ngày
--sinh. Danh sách sẽ tiến hành bố trí theo đuổi trật tự Nam/Nữ.
select masv as 'Mã sinh viên',hosv+' '+tensv as 'họ thương hiệu sinh viên',phai as 'Phái',ngaysinh as 'Ngày sinh'
from dmsv
order by nhạt asc

--11. tin tức những SV gồm: Họ thương hiệu SV, Ngày sinh, Học bổng. tin tức tiếp tục
--được bố trí theo đuổi trật tự Ngày sinh tăng dần dần và Học bổng hạn chế dần dần.
select hosv+' '+tensv as 'họ thương hiệu sinh viên',ngaysinh as 'Ngày sinh',hocbong as 'Học bổng'
from dmsv
order by ngaysinh asc,hocbong desc
--12. Danh sách những môn học tập mang tên chính thức bằng văn bản T, bao gồm những thông tin: Mã môn, Tên
--môn, Số tiết.
select mamh as 'Mã môn học',tenmh as 'Tên môn học',sotiet as 'số tiết'
from dmmh
where tenmh like 't%'

--13. Liệt kê list những SV đem vần âm ở đầu cuối vô thương hiệu là I, bao gồm những thông
--tin: Họ thương hiệu SV, Ngày sinh, Phái.
select hosv+' '+tensv as 'Họ thương hiệu sinh viên',ngaysinh as 'Ngày sinh',phai as 'Phái'
from dmsv
where tensv like '%i'
--14. Danh sách những khoa đem ký tự động loại nhị của thương hiệu khoa đem chứa chấp chữ N, bao gồm những thông tin:
--Mã khoa, Tên khoa.
select makhoa as 'Mã khoa',tenkhoa as 'tên khoa'
from dmkhoa
where tenkhoa like '_n%'
--15. Liệt kê những SV mà người ta đem chứa chấp chữ Thị.
select *
from dmsv
where hosv like N'%thị%'

--16. Cho biết list những SV đem ký tự động trước tiên của thương hiệu ở trong vòng kể từ a
--đến m, bao gồm những thông tin: Mã SV, Họ thương hiệu SV, Phái, Học bổng.
Select MaSV As N'Mã Sinh Viên',HoSV+' '+TenSV As N'Họ Tên Sinh Viên',Phai As N'Phái',HocBong As N'Học Bổng'
from dmsv
where tensv between 'a' and 'm'

--17. Cho biết list những SV tuy nhiên thương hiệu đem chứa chấp ký tự động ở trong vòng kể từ a cho tới
--m, bao gồm những thông tin: Họ thương hiệu SV, Ngày sinh, Nơi sinh, Học bổng. Danh sách
--được bố trí tăng dần dần theo đuổi chúng ta thương hiệu SV.
Select MaSV As N'Mã Sinh Viên',HoSV+' '+TenSV As N'Họ Tên Sinh Viên',Phai As N'Phái',HocBong As N'Học Bổng'
From DMSV
Where TenSV like '%[a-m]%'
Order by HoSV+' '+TenSV ASC

--18. Cho biết list những SV đem học tập bổng to hơn 100,000, bao gồm những thông tin: Mã
--sinh viên, Họ thương hiệu SV, Mã khoa, Học bổng. Danh sách sẽ tiến hành bố trí theo đuổi trật tự
--Mã khoa hạn chế dần dần.
Select MaSV As N'Mã Sinh Viên',HoSV+' '+TenSV As N'Họ Tên Sinh Viên',MaKhoa As N'Mã Khoa',HocBong As N'Học Bổng'from dmsv
where hocbong>100000
order by makhoa desc

--19. Liệt kê những SV đem học tập bổng kể từ 150,000 trở lên trên và sinh ở Hà Nội Thủ Đô, bao gồm những thông
--tin: Họ thương hiệu SV, Mã khoa, Nơi sinh, Học bổng.
Select HoSV+' '+TenSV As N'Họ Tên Sinh Viên',MaKhoa As N'Mã Khoa',NoiSinh As N'Nơi Sinh',HocBong As N'Học Bổng'
From DMSV
Where HocBong>=150000 and NoiSinh=N'Hà Nội'

--20. Danh sách những SV của khoa Anh văn và khoa Vật lý, bao gồm những thông tin: Mã sinh
--viên, Mã khoa, Phái.
Select MaSV As N'Mã Sinh Viên',MaKhoa As N'Mã Khoa',Phai As N'Phái'
From DMSV
Where MaKhoa='AV' or MaKhoa='VL'

--21. Cho biết những SV đem ngày sinh từ thời điểm ngày 01/01/1991 cho tới ngày 05/06/1992 bao gồm
--các thông tin: Mã SV, Ngày sinh, Nơi sinh, Học bổng.
Select MaSV As N'Mã Sinh Viên' ,NgaySinh As N'Ngày Sinh',NoiSinh As N'Nơi Sinh',HocBong As N'Học Bổng'
From DMSV
Where NgaySinh>='01/01/1991' and NgaySinh<='05/06/1992'

--22. Danh sách những SV đem học tập bổng kể từ 80.000 cho tới 150.000, bao gồm những thông tin: Mã
--sinh viên, Ngày sinh, Phái, Mã khoa.
Select MaSV As N'Mã Sinh Viên',NgaySinh As N'Ngày Sinh',Phai As N'Phái',MaKhoa As N'Mã Khoa'
From DMSV
Where HocBong>=80000 and HocBong<=150000

--23. Cho biết những môn học tập đem số tiết to hơn 30 và nhỏ rộng lớn 45, bao gồm những thông tin: Mã
--môn học tập, Tên môn học tập, Số tiết.
Select MaMH As N'Mã Môn Học',TenMH As N'Tên Môn Học',SoTiet As N'Số Tiết'
From DMMH
Where SoTiet>30 and SoTiet<45
--24. Liệt kê những SV nam giới của khoa Anh văn và khoa tin yêu học tập, bao gồm những thông tin: Mã
--sinh viên, Họ thương hiệu SV, thương hiệu khoa, Phái.
select masv 'Mã sinh viên','Họ thương hiệu sinh viên'=hosv+' '+tensv,tenkhoa 'Tên khoa',phai 'Phái'
from dmsv sv,dmkhoa khoa
where sv.makhoa=khoa.makhoa
--25. Liệt kê những SV nữ giới, thương hiệu đem chứa chấp chữ N--
select *
from dmsv
where phai=N'nữ' and tensv like N'%n%'
--C. Truy vấn dùng hàm: year, month, day, getdate, case, ….
--26. Danh sách SV đem điểm sinh ở Hà Nội Thủ Đô và sinh vô mon 02, bao gồm những thông tin: Họ
--sinh viên, Tên SV, Nơi sinh, Ngày sinh.
select hosv 'Họ sinh viên',tensv 'Tên sinh viên',noisinh 'Nơi sinh',ngaysinh 'ngày sinh'
from dmsv
where noisinh=N'Hà nội' and month(ngaysinh)=2
--27. Cho biết những SV đem tuổi tác to hơn đôi mươi, vấn đề gồm: Họ thương hiệu SV, Tuổi,Học bổng.
select hosv+' '+tensv 'Tên sinh viên','Tuổi'=year(getdate())-year(ngaysinh),hocbong 'Học bổng'
from dmsv
where year(getdate())-year(ngaysinh)>20
--28. Danh sách những SV đem tuổi tác kể từ đôi mươi cho tới 25, vấn đề gồm: Họ thương hiệu SV, Tuổi,
--Tên khoa.
select hosv+' '+tensv 'Tên sinh viên','Tuổi'=year(getdate())-year(ngaysinh),tenkhoa 'Tên khoa'
from dmsv sv,dmkhoa khoa
where sv.makhoa=khoa.makhoa and (year(getdate())-year(ngaysinh)between đôi mươi and 25)
--29. Danh sách SV sinh vô ngày xuân năm 1990, bao gồm những thông tin: Họ thương hiệu SV,
--Phái, Ngày sinh.
select hosv+' '+tensv 'Tên sinh viên',phai 'Phái',ngaysinh 'Ngày sinh'
from dmsv
where year(ngaysinh)=1990 and (month (ngaysinh)in(1,2,3)) --between 1 and 3)
--30. Cho biết vấn đề về nút học tập bổng của những SV, gồm: Mã SV, Phái, Mã
--khoa, Mức học tập bổng. Trong số đó, nút học tập bổng tiếp tục hiển thị là “Học bổng cao” nếu như độ quý hiếm
--của field học tập bổng to hơn 500,000 và ngược lại hiển thị là “Mức trung bình”
select masv'Mã sinh viên',phai'Phái',makhoa 'Mã khoa','Mức trung bình'=case when hocbong>500000 then N'Học bổng cao' else N' Mức trung bình' end
from dmsv
--D. Truy vấn dùng hàm kết hợp: max, min, count, sum, avg và gom
--nhóm
--32. Cho biết tổng số SV của toàn trường
select 'Tổng SV toàn trường'=count(masv)
from dmsv
--33. Cho biết tổng SV và tổng SV nữ giới.
--đây là cơ hội ghi chép gộp vô bảng
select 'Tổng sinh viên'=count(masv),'Tổng SV nữ'=sum(case nhạt when N'nữ'then 1 else 0 end)
from dmsv
------------
select 'Tổng sinh viên'=count(masv),t.nu 'Tổng SV nữ'
from dmsv,
(
select count(masv) as 'nu'
from dmsv
where phai=N'Nữ'
) as t
group by t.nu--34. Cho biết tổng số SV của từng khoa.
select makhoa 'Mã khoa','Mã sinh viên'=count(masv)
from dmsv
group by makhoa

--35. Cho biết con số SV học tập từng môn.
select tenmh'Tên môn học',count(distinct masv)'Mã sinh viên'
from ketqua kq,dmmh mh
where kq.mamh=mh.mamh
group by tenmh
--36. Cho biết con số môn học tập tuy nhiên SV tiếp tục học(tức tổng số môn học tập đem torng bảng kq)
select count(distinct mamh)'Tổng số môn học'
from ketqua

--37. Cho biết tổng số học tập bổng của từng khoa.
select makhoa 'Mã khoa',sum(hocbong)'Tổng học tập bổng'
from dmsv
group by makhoa
--38. Cho biết học tập bổng tối đa của từng khoa.
select makhoa 'Mã khoa',max(hocbong)'Học bổng cao nhất'
from dmsv
group by makhoa
--39. Cho biết tổng số SV nam giới và tổng số SV nữ giới của từng khoa.
select makhoa,'Tổng SV nam'=sum(case nhạt when N'nam'then 1 else 0 end),'Tổng SV nữ'=sum(case nhạt when N'nữ'then 1 else 0 end)
from dmsv
group by makhoa
--40. Cho biết con số SV theo đuổi từng lứa tuổi.
select year(getdate())-year(ngaysinh) 'Tuổi',count(masv) 'Số sinh viên'
from dmsv
group by year(getdate())-year(ngaysinh)

--41. Cho biết trong những năm sinh này đem 2 SV đang được theo đuổi học tập bên trên ngôi trường.
select year(ngaysinh)'Năm',count(Masv)'Số sinh viên'
from dmsv
group by year(ngaysinh)
having count(Masv)=2

--42. Cho biết những điểm này đem rộng lớn 2 SV đang được theo đuổi học tập bên trên ngôi trường.
select NoiSinh, count(Masv)'Số sinh viên'
from dmsv
group by NoiSinh
having count(Masv)>=2
--43. Cho biết những môn này đem bên trên 3 SV tham dự cuộc thi.
select mamh 'Mã môn học',count(masv)'Số Sinh viên'
from ketqua
group by mamh
having count(masv)>3

--44. Cho biết những SV ganh đua lại bên trên gấp đôi.
select masv,mamh,count(lanthi)'so lan ganh đua lai' from ketqua
group by masv,mamh

Xem thêm: Nhạc tập 5 bài Pháp Luân Công: MP3, Audio, Video tải về miễn phí

having count(lanthi)>2

--45. Cho biết những SV nam giới đem điểm khoảng phen 1 bên trên 7.0
select Hosv+' '+tensv 'Họ thương hiệu sinh viên',phai,lanthi,avg(Diem)'diem trung binh'
from ketqua kq,dmsv sv
where kq.masv=sv.masv and lanthi=1 and phai=N'nam'
group by lanthi,phai, Hosv+' '+tensv
having avg(Diem)>7.0

--46. Cho biết list những SV rớt bên trên 2 môn ở phen ganh đua 1.
select masv 'Mã sinh viên',count(mamh)'Số môn rớt'
from ketqua
where lanthi=1 and diem<5
group by masv
having count(mamh)>=2
--47. Cho biết list những khoa đem nhiều hơn thế 2 SV nam giới
select makhoa 'Mã khoa','Số SV nam'=count(masv)
from dmsv
where phai=N'Nam'
group by makhoa
having count(masv)>=2

--48. Cho biết những khoa đem 2 sinh đạt học tập bổng kể từ 200.000 cho tới 300.000.
select makhoa 'Mã khoa','Số sinh viên'=count(masv)
from dmsv
where hocbong between 200000 and 300000
group by makhoa
having count(masv)>2

--49. Cho biết con số SV đậu và con số SV rớt của từng môn vô phen ganh đua 1.
--làm từng bảng
select tenmh,'Số SV Đậu'=count(masv)
from ketqua kq,dmmh mh
where kq.mamh=mh.mamh and lanthi=1 and diem>=5
group by tenmh

select tenmh,'Số SV Rớt'=count(masv)
from ketqua kq,dmmh mh
where kq.mamh=mh.mamh and lanthi=1 and diem<5
group by tenmh
--Làm gộp
select tenmh 'Tên môn học','Số SV Đậu'=sum(case when diem>=5 then 1 else 0 end ),'Số SV Rớt'=sum(case when diem<5 then 1 else 0 end )
from ketqua kq,dmmh mh
where kq.mamh=mh.mamh and lanthi=1
group by tenmh
--50. Cho biết con số SV nam giới và con số SV nữ giới của từng khoa.
--trùng lại câu 39
select makhoa,'Tổng SV nam'=sum(case nhạt when N'nam'then 1 else 0 end),'Tổng SV nữ'=sum(case nhạt when N'nữ'then 1 else 0 end)
from dmsv
group by makhoa
------

--F. Truy vấn con cái trả về một độ quý hiếm
--51. Cho biết SV này đem học tập bổng tối đa.
select hosv+' '+tensv 'Họ thương hiệu sinh viên',hocbong
from dmsv
where hocbong=(select max(hocbong) from dmsv)
--52. Cho biết SV này đem điểm ganh đua phen 1 môn hạ tầng tài liệu tối đa.

select hosv+' '+tensv 'Họ thương hiệu sinh viên',tenmh 'Tên môn học',lanthi,diem
from ketqua kq,dmmh mh,dmsv sv
where sv.masv=kq.masv and kq.mamh=mh.mamh and lanthi=1 and tenmh=N'cơ sở dữ liệu'
and diem=
(
select max(diem)
from ketqua kq,dmmh mh
where kq.mamh=mh.mamh and tenmh=N'cơ sở dữ liệu' and lanthi=1
)
--53. Cho biết SV khoa anh văn đem tuổi tác lớn số 1.
select hosv+' '+tensv 'Họ thương hiệu sinh viên',ngaysinh 'Ngày sinh',makhoa 'Mã khoa'
from dmsv
where /*makhoa='av' and*/ ngaysinh=(
select min(ngaysinh)
from dmsv
where makhoa='av'

)

--or
select hosv+' '+tensv 'Họ thương hiệu sinh viên',ngaysinh 'Ngày sinh',makhoa 'Mã khoa'
from dmsv
where (getdate()-ngaysinh)=
(
select max(getdate()-ngaysinh)
from dmsv
where makhoa='av'

)
--54. Cho biết khoa này đem nhộn nhịp SV nhất.
--cach 1:
select tenkhoa
from dmsv sv,dmkhoa kh
where sv.makhoa=kh.makhoa
group by tenkhoa
having count(tenkhoa)>=all(select count(masv)
from dmsv
group by makhoa)
--cach 2:
select tenkhoafrom dmsv sv,dmkhoa kh
where sv.makhoa=kh.makhoa
group by tenkhoa
having count(tenkhoa)= (select max(t.tong)
from
(
select count(masv) as tong
from dmsv
group by makhoa
) as t
)

--55. Cho biết khoa này đem nhộn nhịp nữ giới nhất.
select tenkhoa 'Tên khoa'
from dmsv sv,dmkhoa kh
where sv.makhoa=kh.makhoa and phai=N'nữ'
group by tenkhoa
having count(tenkhoa)>=all(select count(masv)
from dmsv
where phai=N'nữ'
group by makhoa)
--56. Cho biết môn này có rất nhiều SV rớt phen 1 tối đa.

select mamh
from ketqua
where lanthi=1 and diem<5
group by mamh
having count(diem)>=all (select count(diem)
from ketqua
where lanthi=1 and diem<5
group by mamh)
--57. Cho biết SV ko học tập khoa anh văn đem điểm ganh đua môn phạm to hơn điểm ganh đua văn
--phạm của SV học tập khoa anh văn.
select distinct kq.masv
from ketqua kq, dmsv sv
where sv.masv=kq.masv and mamh='05' and makhoa not like 'av' and diem>(
select diem
from ketqua kq, dmsv sv
where sv.masv=kq.masv and mamh='05' and makhoa='av'
)
--G. Truy vấn con cái trả về nhiều độ quý hiếm, dùng lượng kể từ all, any, union, top.
--58. Cho biết SV đem điểm sinh cùng theo với Hải.
select masv,hosv+' '+tensv
from dmsv
where noisinh=( select noisinh
from dmsv
where tensv=N'hải')
--59. Cho biết những SV này đem học tập bổng to hơn toàn bộ học tập bổng của
sinh viên nằm trong khoa anh văn
select masv
from dmsv
where hocbong>=all (select hocbong from dmsv where makhoa='av')
--60. Cho biết những SV đem học tập bổng to hơn ngẫu nhiên học tập bổng của SV học tập khóa anh văn
select masv,hocbong
from dmsv
where hocbong>=any (select hocbong from dmsv where makhoa='av')
--61. Cho biết SV này đem điểm ganh đua môn hạ tầng tài liệu phen 2 to hơn toàn bộ điểm ganh đua phen 1
--môn hạ tầng tài liệu của những SV không giống.
select masv
from ketqua
where mamh='01' and lanthi=2 and diem>=all(select diem from ketqua where mamh='01' and lanthi=1)
--62. Cho biết những SV đạt điểm tối đa vào cụ thể từng môn.
select masv,ketqua.mamh,diem
from ketqua, (select mamh, max(diem) as maxdiem
from ketqua
group by mamh)a
where ketqua.mamh=a.mamh and diem=a.maxdiem
--63. Cho biết những khoa không tồn tại SV học tập.
select *
from dmkhoa
where not exists (select distinct makhoa
from ketqua,dmsv where ketqua.masv=dmsv.masv and makhoa=dmkhoa.makhoa)
--64. Cho biết SV ko ganh đua môn hạ tầng tài liệu.
select *
from dmsv
where not exists
(select distinct*
from ketqua
where mamh = '01' and masv=dmsv.masv)
--65. Cho biết SV này ko ganh đua phen 1 tuy nhiên đem tham dự cuộc thi phen 2.
select masv
from ketqua kq
where lanthi=2 and not exists
(select *
from ketqua
where lanthi=1 and masv=kq.masv)
--66. Cho biết môn này không tồn tại SV khoa anh văn học tập.
select tenmh
from dmmh
where
not exists
(select mamh
from ketqua kq,dmsv sv
where sv.masv=kq.masv and sv.makhoa='av' and dmmh.mamh=mamh)

--67. Cho biết những SV khoa anh văn ko học tập môn văn phạm.
Select MaSV
From DMSv dmsv Where MaKhoa='AV' And Not Exists (Select *
From KetQua
Where MaMH='05' And MaSV=dmsv.MaSV
)

--68. Cho biết những SV ko rớt môn này.
Select MaSV
From DMSV dmsv
Where Not Exists (Select *
From KetQua
Where Diem<=5 And MaSV=dmsv.MaSV
)

--69. Cho biết những SV học tập khoa anh văn đem học tập bổng và những SV ko bao
--giờ rớt.
Select MaSV,MaKhoa,HocBong
From DMSv dmsv
Where MaKhoa='AV' And HocBong>0 And Not Exists (Select *
From KetQua
Where Diem<5 And MaSV=dmsv.MaSV
)

--70. Cho biết khoa này đem nhộn nhịp SV nhận học tập bổng nhất và khoa này khoa này đem không nhiều
--sinh viên nhận học tập bổng nhất.
Select MaKhoa,count(MaSV)'So Luong SV'
From DMSV
Where HocBong>0
Group By MaKhoa
Having count(MaSV)>=All (Select count(MaSV)
From DMSv
where hocbong>0
Group By MaKhoa
)
UNION
Select MaKhoa,count(MaSV)'So Luong SV'
From DMSV
Where HocBong>0
Group By MaKhoa
Having count(MaSV)<=All (Select count(MaSV)
From DMSV
where hocbong>0
Group By MaKhoa
)

--71. Cho biết 3 SV đem học tập nhiều môn nhất.

Select top 3 MaSV,Count(Distinct MaMH)'Số môn học'
From KetQua
Group By MaSV
Having Count(Distinct MaMH)>=All(Select count( distinct MaMH)
From KetQua
Group By MaSV
)

/*==========================H. Truy vấn sử dụng luật lệ phân chia =========================*/
--72. Cho biết những môn được toàn bộ những SV theo đuổi học tập.
Select MaMH
From KetQua
Group By MaMH
Having count(distinct MaSV)=(Select count(MaSV)
From DMSv
)

--73. Cho biết những SV học tập những môn tương đương SV đem mã số A02 học tập.
Select distinct MaSV
From KetQua kq
Where Exists(Select distinct MaMH
From KetQua
Where MaSV='A02' and MaMH=kq.MaMH
)

--74.Cho biết những SV học tập những môn vày trúng những môn tuy nhiên SV A02 học tập.
Select TenSV
From KetQua kq,DMSv dmsv,(Select MaSV,MaMH,count(distinct MaMH)SoMon
From KetQua
Where MaSV='A02'
Group By MaSV,MaMH)a
Where kq.MaSV=dmsv.MaSV and kq.MaMH=a.MaMH and kq.MaSV <>a .MaSV
Group By TenSV
Having count(distinct kq.MaMH)=(Select count(distinct MaMH)
From KetQua
Where MaSV='A02')

Select dmsv.MaSV
From KetQua kq, DMSv dmsv
Where kq.MaSV=dmsv.MaSV and MaMH=(Select distinct MaMH
From KetQua
Where MaSV='A02' and MaMH=kq.MaMH) and dmsv.MaSV Not Like 'A02'
Group By dmsv.MaSV
Having count(distinct MaMH)=(Select count(distinct MaMH)
From KetQua
Where MaSV='A02')

--75. Tạo một bảng mới nhất thương hiệu sinhvien-ketqua: gồm: MASV, HoSV, TenSV, SoMonHoc. Sau
--đó Thêm tài liệu vô bảng này phụ thuộc tài liệu tiếp tục đem.
Create Table SinhVien_KetQua
(
MaSV char(3) not null,
HoSV nvarchar(15) not null,
TenSV nvarchar(7)not null,
SoMonHoc tinyint
)

Insert Into SinhVien_KetQua
Select dmsv.MaSV,HoSV,TenSV,count(distinct MaMH)
From DMSV dmsv,KetQua kq
Where dmsv.MaSV=kq.MaSV
Group By dmsv.MaSV,HoSV,TenSV

--76. Thêm vô bảng khoa cột Siso, update sỉ số vô khoa kể từ tài liệu SV.
go
alter table dmkhoa
add siso tinyint
go

update dmkhoa
set siso=(select count(masv)
from dmsv
where makhoa='av'
group by(makhoa))
where makhoa='av'

update dmkhoa
set siso=(select count(masv)
from dmsv
where makhoa='TH'
group by(makhoa))
where makhoa='Th'update dmkhoa
set siso=(select count(masv)
from dmsv
where makhoa='Tr'
group by(makhoa))
where makhoa='Tr'

update dmkhoa
set siso=(select count(masv)
from dmsv
where makhoa='vl'
group by(makhoa))
where makhoa='vl'

--77. Tăng thêm một điểm cho những SV vớt phen 2. Nhưng chỉ tăng tối nhiều là 5 điểm
update ketqua
set diem=diem+1
where lanthi=2 and diem+1<=5

select * from ketqua
--78. Tăng học tập bổng lên 100000 cho tới những SV đem điểm khoảng là 6.5 trở lên trên
update dmsv
set hocbong=hocbong+100000
where masv in (select masv
from ketqua
group by masv
having avg(diem)>=6.5)
--79. Thiết lập học tập bổng vày 0 cho tới những SV ganh đua nhị môn rốt ở phen 1
update dmsv
set hocbong=0
where masv in (select masv
from ketqua
where lanthi=1 and diem<5
group by masv
having count(mamh)=2
)

--80. Xoá toàn bộ những SV ko tham dự cuộc thi môn này.
delete from dmsv
where not exists (select masv
from ketqua
where masv=dmsv.masv
)
--81. Xóa những môn tuy nhiên không tồn tại SV học tập.
delete from dmmh
where not exists(select mamh
from ketqua
where mamh=dmmh.mamh
)
-- Tạo view
--82. Danh sách SV ko bi rớt môn này
create view cau82
as
select dmsv.masv,hosv,tensv,phai,ngaysinh,noisinh,hocbong
from dmsv,ketqua
where dmsv.masv=ketqua.masv
group by dmsv.masv,hosv,tensv,phai,ngaysinh,noisinh,hocbong
having min(diem)>=5