Định dạng tháng năm - Tài liệu tham khảo | Đại học Hoa Sen

--Dinh dang ngay thang nam set dateformat dmy --Tao database create database db_btQLSV317 --Chon database hien hanh use db_btQLSV317 --Tao table cho cac bang create table khoa (
makh varchar(2) not null primary key,
tenkh nvarchar(50) not null unique ) create table monhoc (
mamh varchar(2) not null primary key, tenmh nvarchar(50) not null, sotiet int not null ) create table sinhvien (
masv varchar(3) not null primary key, hosv nvarchar(50) not null, tensv nvarchar(50) not null,
phai int not null check(phai in (1, 0)), ngaysinh datetime not null,
noisinh nvarchar(50) not null, makh varchar(2) not null, hocbong int )
alter table sinhvien add constraint fk_sv_kh foreign key (makh) references khoa (makh) create table ketqua ( masv varchar(3) not null, mamh varchar(2) not null,
diem real not null check(diem between 0 and 10) )
alter table ketqua add constraint pk_kq primary key (masv, mamh)
alter table ketqua add constraint fk_kq_sv foreign key (masv) references sinhvien (masv)
alter table ketqua add constraint fk_kq_mh foreign key (mamh) references monhoc (mamh) --Chen dl
insert into khoa values ('AV',N'Anh Văn')
insert into khoa values ('LS',N'Lịch sử')
insert into khoa values ('TH',N'Tin học')
insert into khoa values ('TR',N'Triết')
insert into khoa values ('VL',N'Vật lý')
insert into khoa values ('SH',N'Sinh học') select * from khoa insert into sinhvien values
('A01',N'Nguyễn Thu',N'Hải',0,'23/02/1980',N'TP.HCM','AV',100000)
insert into sinhvien values('A02',N'Trần
Văn',N'Chính',1,'24/12/1982',N'TP.HCM','TH',100000)
insert into sinhvien values('A03',N'Lê Thu
Bạch',N'Yến',0,'21/02/1982',N'Hà Nội','AV',140000)
insert into sinhvien values('A04',N'Trần
Anh',N'Tuấn',1,'08/12/1984',N'Long An','LS',80000)
insert into sinhvien values('A05',N'Trần
Thanh',N'Triều',1,'01/02/1980',N'Hà Nội','VL',80000)
insert into sinhvien values('B01',N'Trần
Thanh',N'Mai',0,'20/12/1981',N'Bến Tre','TH',200000)
insert into sinhvien values('B02',N'Trần Thị
Thu',N'Thủy',0,'13/02/1982',N'TP.HCM','TH',null)
insert into sinhvien values('B03',N'Trần
Thị',N'Thanh',0,'31/12/1982',N'TP.HCM','TH',50000) select * from sinhvien
insert into monhoc values ('01',N'Nhập môn máy tính',30)
insert into monhoc values ('02',N'Trí tuệ nhân tạo',45)
insert into monhoc values ('03',N'Truyền tin',45)
insert into monhoc values ('04',N'Đồ họa',60)
insert into monhoc values ('05',N'Văn phạm',45)
insert into monhoc values ('06',N'Đàm thoại',30)
insert into monhoc values ('07',N'Vật lý nguyên tử',30) select * from monhoc
insert into ketqua values('A01','01',10)
insert into ketqua values('A01','02',4)
insert into ketqua values('A01','05',9)
insert into ketqua values('A01','06',3)
insert into ketqua values('A02','01',5)
insert into ketqua values('A03','02',5)
insert into ketqua values('A03','04',10)
insert into ketqua values('A03','06',1)
insert into ketqua values('A04','02',4)
insert into ketqua values('A04','04',6)
insert into ketqua values('B01','01',0)
insert into ketqua values('B01','04',8)
insert into ketqua values('B02','03',6)
insert into ketqua values('B02','04',8)
insert into ketqua values('B03','02',10)
insert into ketqua values('B03','03',9) select * from ketqua --------------- select * from khoa select * from sinhvien select * from monhoc select * from ketqua select * from sinhvien
--=====================Stored Procedure=====================--
/*2. Xây dựng Stored Procedure tên sp_TongHocBongSVTheoKhoa với tham số
vào là Tên khoa để tính
tổng học bổng của các sinh viên thuộc khoa đó. Nếu Tên khoa không hợp lệ thì thông báo lỗi.*/ go
create proc sp_TongHocBongSVTheoKhoa (@tenkh nvarchar(50)) as
if not exists(select * from khoa where tenkh=@tenkh) begin print 'Khong co khoa '+@tenkh return end
if not exists(select * from khoa kh inner join sinhvien sv on
sv.makh=kh.makh where tenkh=@tenkh) begin
print 'Khoa '+@tenkh+' khong co Sinh vien' return end declare @tong int =0
select @tong=sum(isnull(hocbong,0)) from khoa kh inner join
sinhvien sv on sv.makh=kh.makh where tenkh=@tenkh if @tong=0
print 'Khoa '+@tenkh+' khong co hoc bong' else
print 'Khoa '+@tenkh+' co tong hoc bong cua SV la: '+cast(@tong as varchar(10)) go
exec dbo.sp_TongHocBongSVTheoKhoa N'Triết' go
/*4. Xây dựng Stored Procedure tên sp_HienThi_DSSV_TheoKhoa với tham số
vào là mã khoa để hiển thị
thông tin sinh viên thuộc Khoa đó và có thêm cột GHI CHÚ hiển thị “Đã
thi xxx môn” nếu SV có kết quả
thi, ngược lại thì hiển thị “Chưa có kết quả thi” nếu SV chưa thi môn nào.*/
create proc sp_HienThi_DSSV_TheoKhoa (@makh varchar(2)) as
if not exists(select * from sinhvien where makh=@makh) begin print 'Khoa nay ko co sv' return end
select sv.masv, hosv, tensv, GC=case when count(*)>0 then 'co
'+cast(count(*) as varchar(2)) else 'ko' end
from ketqua kq inner join sinhvien sv on sv.masv=kq.masv where makh=@makh group by sv.masv, hosv, tensv go
exec sp_HienThi_DSSV_TheoKhoa 'TR'
--=====================Function=====================--
--5. Xây dựng hàm fn_DTB_MH(@mamh) trả về điểm TB của môn học có mã số truyền vào. go
create function fn_DTB_MH(@mamh varchar(2)) returns float as begin declare @dtb float
select @dtb=AVG(diem) from ketqua where mamh=@mamh return @dtb end go print dbo.fn_DTB_MH('02')
/*6. Xây dựng thủ tục sp_CapNhatMH có sử dụng hàm fn_DTB_MH để cập
nhật lại số tiết trong bảng MONHOC
theo các qui tắc sau: Tăng 10 tiết nếu ĐTB của SV học dưới 5.
Tăng 5 tiết nếu ĐTB của SV học từ 5 ≤ ĐTB < 7
Không tăng số tiết nếu ĐTB của SV học ≥ 7 hoặc không có SV học.*/ go
create proc sp_Update_Sotiet(@mamh varchar(2)) as if @mamh is null begin print 'Chua nhap mon' return end
if not exists(select * from monhoc where mamh=@mamh) begin
print 'Khong co mon '+@mamh return end
if not exists(select * from ketqua where mamh=@mamh) begin
print 'Mon '+@mamh+' chua co diem' return end declare @dtb float, @stt int set @dtb=dbo.fn_DTB_MH(@mamh) if @dtb<5 set @stt=10
if @dtb>=5 and @dtb<7 set @stt=5 if @dtb>=7 set @stt=0
update monhoc set sotiet=sotiet+@stt where mamh=@mamh
print 'Mon '+@mamh+' da duoc cap nhat so tiet' go exec dbo.sp_Update_Sotiet '01'
/*7.Xây dựng thủ tục sp_CapNhatMH_KyTuDau(@kytudau) có sử
dụng hàm fn_DTB_MH để cập nhật lại số tiết trong bảng MONHOC
cho các môn học mà tên có ký tự đầu là “T”.*/ go
create proc sp_CapNhatMH_KyTuDau(@kytudau varchar(3), @mamh varchar(5)) as if @mamh is null begin print 'Chua nhap mon' return end
if not exists(select * from monhoc where mamh=@mamh) begin
print 'Khong co mon '+@mamh return end
if not exists(select * from ketqua where mamh=@mamh) begin
print 'Mon '+@mamh+' chua co diem' return end declare @dtb float, @stt int set @dtb=dbo.fn_DTB_MH(@mamh) if @dtb<5 set @stt=10
if @dtb>=5 and @dtb<7 set @stt=5 if @dtb>=7 set @stt=0
update monhoc set sotiet=sotiet+@stt where mamh=@mamh and
@kytudau=tenmh and tenmh like 'T%'
print 'Mon '+@mamh+' da duoc cap nhat so tiet' go
exec dbo.sp_CapNhatMH_KyTuDau'05','T'
/*8.Xây dựng hàm fn_DanhSachSinhVien_DTB(@makh) trả về danh
sách các SV của mã khoa truyền vào, gồm các thông tin: mã SV, họ tên SV, ĐTB.*/ go
create function fn_DanhSachSinhVien_DTB(@makh varchar(2)) returns table as return
select sv.masv,N'Họ tên SV'=hosv+' '+tensv,N'ĐTB'=AVG(diem)
from sinhvien sv inner join ketqua kq on sv.masv=kq.masv
inner join khoa kh on sv.makh=kh.makh where sv.makh=@makh
group by sv.masv,hosv+' '+tensv go
select * from dbo.fn_DanhSachSinhVien_DTB(02)
/*9.Xây dựng hàm fn_DanhSachMonHoc(@masv) trả về danh sách
gồm các thông tin: mã môn học, tên môn học và điểm số tương
ứng của mã SV truyền vào.*/ go
create function fn_DanhSachMonHoc(@masv varchar(3)) returns table as return select mh.mamh,tenmh,diem
from sinhvien sv inner join ketqua kq on sv.masv=kq.masv
inner join monhoc mh on kq.mamh=mh.mamh
inner join khoa kh on sv.makh=kh.makh where kq.masv=@masv group by mh.mamh,tenmh,diem go
select * from dbo.fn_DanhSachMonHoc('D01')
/*10.Xây dựng hàm fn_DSSV_ThiMon(@mamh) để lọc danh sách SV
đã thi môn học với mã môn truyền vào, gồm các thông tin: mã SV, họ tên SV, tên khoa.*/ go
create function fn_DSSV_ThiMon(@mamh varchar(3)) returns table as return
select sv.masv,N'Họ tên SV'=hosv+' '+tensv,tenkh
from sinhvien sv inner join ketqua kq on sv.masv=kq.masv
inner join monhoc mh on kq.mamh=mh.mamh
inner join khoa kh on sv.makh=kh.makh where mh.mamh=@mamh
group by sv.masv,hosv+' '+tensv,tenkh go
select * from dbo.fn_DSSV_ThiMon('01')
/*11.Xây dựng hàm fn_DSKhoa_ThiMon(@mamh) để lọc danh sách
khoa có SV đã thi môn học với mã môn truyền vào.*/ go
create function fn_DSKhoa_ThiMon(@mamh varchar(3)) returns table as return
select sv.masv,N'Họ tên SV'=hosv+' '+tensv,tenmh
from sinhvien sv inner join ketqua kq on sv.masv=kq.masv
inner join monhoc mh on kq.mamh=mh.mamh
inner join khoa kh on sv.makh=kh.makh where mh.mamh=@mamh
group by sv.masv,hosv+' '+tensv,tenmh
/*14. Xây dựng hàm fn_LocDSMH_CapNhatSoTiet để lọc danh sách môn
học (gồm các thông tin: mã MH, tên MH, ĐTB thi của SV, số tiết cũ,
số tiết mới) với số tiết mới của SV được tính theo các qui tắc sau:
• Không tăng số tiết nếu không có SV học hoặc ĐTB của SV học dưới 5.
• Tăng 5 tiết nếu ĐTB của SV học từ 5 ≤ ĐTB < 7
• Tăng 10 tiết nếu ĐTB của SV học ≥ 7*/ go
create function fn_LocDSMH_CapNhatSoTiet() returns table as return
select mh.mamh, tenmh, STC=sotiet, DTb=AVG(diem),
STM=sotiet+case when AVG(diem)>=7 then 10
when AVG(diem)>=5 and AVG(diem)<7 then 5 else null end
from monhoc mh inner join ketqua kq on mh.mamh=kq.mamh
group by mh.mamh, tenmh, sotiet go
select * from dbo.fn_LocDSMH_CapNhatSoTiet() /paste thêm/
--A16. Liệt kê danh sách sinh viên trong khoa Tin học, gồm các thông tin: Họ tên sinh viên,
--Ngày sinh, Mã khoa, Tên khoa, Mã môn, Điểm. Danh sách được sắp giảm
dần theo Điểm, nếu cùng
--điểm thì sắp tăng dần theo Mã môn.
select HT=hosv+' '+tensv, ngaysinh, kh.makh, tenkh, mamh, diem
from sinhvien sv inner join khoa kh on sv.makh=kh.makh
inner join ketqua kq on sv.masv=kq.masv where tenkh=N'Tin học' order by diem desc, mamh -----------------C
--C6. liệt kê danh sách sinh viên sinh vào tháng 2 năm 1980, gồm các thông tin: Họ tên sinh
--viên, Phái, Ngày sinh, tenkhoa, tenmh. Trong đó, Ngày sinh chỉ lấy
giá trị ngày của field NGAYSINH. Sắp xếp
--dữ liệu giảm dần theo cột Ngày sinh
select HT=hosv+' '+tensv, phai, NS=day(ngaysinh), tenkh, tenmh
from sinhvien sv inner join khoa kh on sv.makh=kh.makh
inner join ketqua kq on sv.masv=kq.masv
inner join monhoc mh on kq.mamh=mh.mamh where month(ngaysinh)=2 and year(ngaysinh)=1980 order by NS desc
---A1 Cho biết danh sách các môn học, gồm các thông tin sau: Mã môn
học, Tên môn học, Số tiết select * from monhoc mh
---A2Liệt kê danh sách sinh viên, gồm các thông tin sau: Mã sinh viên,
Họ sinh viên, Tên sinh viên, Học bổng. sách sẽ được sắp xếp theo thứ tự Mã sinh viên tăng dần. select * from sinhvien sv order by masv asc
---A3 . Danh sách các sinh viên, gồm các thông tin sau: Mã sinh viên,
Tên sinh viên, Phái, Ngày sinh. Danh sách sẽ được sắp xếp theo thứ tự Nam/Nữ select * from sinhvien sv order by phai asc
---A4. Thông tin các sinh viên gồm: Họ tên sinh viên, Ngày sinh, Học
bổng. Thông tin sẽ được sắp xếp theo thứ tự Ngày sinh tăng dần và Học bổng giảm dần select * from sinhvien sv
order by ngaysinh asc,hocbong desc
---A5 Danh sách các môn học có tên bắt đầu bằng chữ T, gồm các thông
tin: Mã môn, Tên môn, Số tiết select * from monhoc mh where tenmh like 'T%'
--A 6. Liệt kê danh sách những sinh viên có chữ cái cuối cùng trong
tên là I, gồm các thông tin: Họ tên sinh viên, Ngày sinh, Phái select * from sinhvien sv where tensv like 'I%'
---A7 Danh sách những khoa có ký tự thứ hai của tên khoa có chứa chữ N,
gồm các thông tin: Mã khoa, Tên khoa select * from khoa kh where tenkh like 'N%'
---A8 Liệt kê những sinh viên mà họ có chứa chữ Thị select * from sinhvien sv where hosv like'Thị&'
--A9 Cho biết danh sách những sinh viên ký tự đầu tiên của tên nằm
trong khoảng từ a đến m, gồm các thông tin: Mã sinh viên, Họ tên sinh viên, Phái, Học bổng. select * from sinhvien sv where tensv like '[a-m]%'
---A10 Cho biết danh sách những sinh viên mà tên có chứa ký tự nằm
trong khoảng từ a đến m, gồm các thông tin: Họ tên sinh viên, Ngày
sinh, Nơi sinh, Học bổng. Danh sách được sắp xếp tăng dần theo Họ tên sinh viên select * from sinhvien sv where tensv like'[a-m]%' order by hosv asc
----A11Cho biết danh sách các sinh viên của khoa Anh văn, gồm các thông
tin sau: Mã sinh viên, Họ tên sinh viên, Ngày sinh, Mã khoa
select HT=hosv+''+tensv,ngaysinh,tensv,masv,kh.makh,sv.masv
from sinhvien sv inner join khoa kh on sv.makh=kh.makh where tenkh =N'Anh văn'
---A12 Liệt kê danh sách sinh viên của khoa Vật Lý, gồm các thông tin
sau: Mã sinh viên, Họ tên sinh viên, Ngày sinh. Danh sách sẽ được sắp
xếp theo thứ tự Ngày sinh giảm dần
select HT=hosv+''+tensv,ngaysinh,tensv,masv,kh.makh,sv.masv
from sinhvien sv inner join khoa kh on sv.makh=kh.makh where tenkh=N'Vật lý' order by ngaysinh desc
----A13 Cho biết danh sách các sinh viên có học bổng lớn hơn 100,000,
gồm các thông tin: Mã sinh viên, Họ tên sinh viên, Mã khoa, Học bổng.
Danh sách sẽ được sắp xếp theo thứ tự Mã khoa giảm dần select * from sinhvien sv where hocbong>100000 order by makh desc
---A14Liệt kê danh sách sinh viên sinh vào ngày 20/12/1981, gồm các
thông tin: Họ tên sinh viên, Mã khoa, Tên khoa, Học bổng select * from sinhvien sv where ngaysinh ='20/12/1981'
---A15 . Liệt kê kết quả học tập các môn của sinh viên khoa Anh Văn,
gồm các thông tin: Họ tên sinh viên, Ngày sinh, Tên môn, Điểm. Danh
sách được sắp xếp giảm dần theo Họ tên sinh viên
select HT=hosv+''+tensv,ngaysinh,tensv,masv,kh.makh,sv.masv
from sinhvien sv inner join khoa kh on sv.makh=kh.makh where tenkh=N'Anh Văn' order by hosv desc Bạn đã gửi
/*2. Xây dựng Stored Procedure tên sp_TongHocBongSVTheoKhoa với tham số
vào là Tên khoa để tính
tổng học bổng của các sinh viên thuộc khoa đó. Nếu Tên khoa không hợp lệ thì thông báo lỗi.*/ go
create proc sp_TongHocBongSVTheoKhoa (@tenkh nvarchar(50)) as
if not exists(select * from khoa where tenkh=@tenkh) begin print 'Khong co khoa '+@tenkh return end
if not exists(select * from khoa kh inner join sinhvien sv on
sv.makh=kh.makh where tenkh=@tenkh) begin
print 'Khoa '+@tenkh+' khong co Sinh vien' return end declare @tong int =0
select @tong=sum(isnull(hocbong,0)) from khoa kh inner join
sinhvien sv on sv.makh=kh.makh where tenkh=@tenkh if @tong=0
print 'Khoa '+@tenkh+' khong co hoc bong' else
print 'Khoa '+@tenkh+' co tong hoc bong cua SV la: '+cast(@tong as varchar(10)) go
exec dbo.sp_TongHocBongSVTheoKhoa N'Triết' go
/*4. Xây dựng Stored Procedure tên sp_HienThi_DSSV_TheoKhoa với tham số
vào là mã khoa để hiển thị
thông tin sinh viên thuộc Khoa đó và có thêm cột GHI CHÚ hiển thị “Đã
thi xxx môn” nếu SV có kết quả
thi, ngược lại thì hiển thị “Chưa có kết quả thi” nếu SV chưa thi môn nào.*/
create proc sp_HienThi_DSSV_TheoKhoa (@makh varchar(2)) as
if not exists(select * from sinhvien where makh=@makh) begin print 'Khoa nay ko co sv' return end