[SQL SEVER] Bài tập thực hành số 5

Mình không dám chắc code dưới đây là đúng là tối ưu, vậy nên bạn nào có cách làm hay hơn xin đóng góp thêm nhé!

Nội dung 1:

Câu 1: Nhập vào 2 số @s1, @s2. In ra câu “Số lớn nhất của @s1 và @s2 là max” với @s1, @s2, max là các giá trị tương ứng.

Mã:

 CREATE PROC CAU1 (@S1 INT,@S2 INT)
AS
DECLARE @M INT
SET @M=@S2
IF @S1>@S2
SET @M=@S1
PRINT  N'SỐ LỚN NHẤT CỦA'+STR(@S1) +N','+STR(@S2)+N' '+ N'LÀ' + STR(@M)
EXECUTE CAU1 2,4
 

Câu 2: Nhập vào 2 số @s1, @s2. Xuất max của chúng ta tham số @max. Cho thực thi và in các giá trị của các tham số này để kiểm tra.

Mã:

 CREATE PROC CAU2 (@S1 INT,@S2 INT,@MAX INT OUTPUT)
AS
SET @MAX=@S1
IF @S2>@MAX
SET @MAX=@S2

DECLARE @T INT
EXEC CAU2 100,18,@T OUTPUT
SELECT @T
 

Câu 3: Viết thủ tục nhập dữ liệu cho bảng KHOA

Mã:

 CREATE PROC CAU3(@MAKHOA NVARCHAR(10),@TENKHOA NVARCHAR(50),@DIENTHOAI NVARCHAR(20))
AS
INSERT INTO KHOA
VALUES(@MAKHOA,@TENKHOA,@DIENTHOAI)

EXEC CAU3 'dhmt',N'Đồ họa','99999999999' 

Câu 4: Viết thủ tục đưa vào mã sinh viên và in ra câu thông báo: “Họ và tên sinh viên này lớn tuổi hơn (cùng hoặc nhỏ tuổi hơn) sinh viên kia” dựa vào năm sinh của 2 sinh viên.

Mã:

 CREATE proc CAU4(@masv1 nvarchar(10), @masv2 nvarchar(10))
as
begin
	declare @n1 nvarchar(4)
	declare @n2 nvarchar(4)
	declare @hoten1 nvarchar(30)
	declare @hoten2 nvarchar(30)

	select @hoten1=hodem+' '+ten, @n1=year(ngaysinh)
	from sinhvien
	where @masv1=masv
	
	select @hoten2=hodem+' '+ten, @n2=year(ngaysinh)
	from sinhvien
	where masv=@masv2

	if(@n1>@n2)
		print @hoten1 + N' lớn tuổi hơn ' +@hoten2
	if(@n1<@n2)
		print @hoten1 + N' nhỏ tuổi hơn ' +@hoten2
	else
		print @hoten1 + N' bàng tuổi ' +@hoten2
end

exec CAU4 'dh01-005', 'dl01-001' 

Câu 5: Viết thủ tục với tham số đưa vào là mã lớp, tham số xuất ra là số sinh viên của lớp đó.

Mã:

 CREATE proc CAU5 (@malop nvarchar(10))
as
begin
	declare @sv nvarchar(10)
	select @sv=count(masv)
	from sinhvien
	where malop=@malop
	print N'Lớp có mã lớp: '+@malop + N' có ' + @sv + N' sinh viên'
end

exec CAU5 'dl01' 

Câu 6: Viết thủ tục cập nhật cho bảng DIEMTHI với tham số đưa vào là mã sinh viên, mã học phần, lần thi, điểm thi củ, điểm thi mới.

Mã:

 CREATE proc CAU6 (@mahocphan nvarchar(10), @masv nvarchar(10), @lanthi tinyint, @diemthimoi numeric(5, 2))
as
begin
	update diemthi
	set mahocphan=@mahocphan, masv=@masv, lanthi=@lanthi, diem=@diemthimoi
	where masv=@masv and mahocphan=@mahocphan
end

EXECUTE CAU6 'sql','dl01-001',1,9 

Câu 7: Viết thục tục hiển thị các thông tin về sinh nhật ngày hôm nay gồm các thông tin: họ và tên, năm sinh, giới tính (Nam hoặc nữ), tên lớp.

Mã:

CREATE PROC CAU7
AS
SELECT MASV, HODEM, TEN, NGAYSINH,CASE GIOITINH
WHEN 0 THEN N'NỮ'
ELSE N'Nam'
END 'GIOI TINH', NOISINH
FROM SINHVIEN 
WHERE DAY(NGAYSINH)=DAY(GETDATE()) AND MONTH(NGAYSINH)=MONTH(GETDATE())

EXEC CAU7 

Câu 8: Viết thủ tục xóa toàn bộ thông tin của sinh viên trong CSDL có mã sinh viên là tham số đưa vào.

Mã:

 CREATE PROC CAU8(@MASV NVARCHAR(10))
AS
DELETE FROM SINHVIEN
WHERE @MASV=MASV

EXEC CAU8 'dh01-005' 

Câu 9: Viết thủ tục với tham số đưa vào là mã sinh viên, tham số xuất ra là điểm trung bình lần 1 và số lần thi của sinh viên đó.

Mã:

 CREATE PROC CAU9(@MASV NVARCHAR(10))
AS
BEGIN
DECLARE @TB FLOAT
DECLARE @SOLT INT
SELECT @TB=(SELECT AVG(DIEM) FROM DIEMTHI WHERE @MASV=MASV AND LANTHI=1)
SELECT @SOLT=(SELECT COUNT(LANTHI) FROM DIEMTHI WHERE @MASV=MASV)
PRINT N'Sinh viên có mã sinh viên:'+N' '+@MASV+N' '+N'có điểm thi trung bình lần 1 là:'+STR(@TB)+N' '+N'số lần thi là: '+STR(@SOLT)
END

EXEC CAU9 'dl01-003' 

Câu 10: Xây dựng thủ tục giải phương trình bậc nhất (ax+b=0) gồm 2 tham số đưa vào là a và b.

Mã:

 CREATE PROC CAU10(@A INT,@B INT)
AS
DECLARE @X INT
SET @X=-(@B)/@A
IF @A=0
IF @B=0
PRINT N'Phương trình có vô số nghiệm!'
ELSE 
PRINT N'Phương trình vô nghiệm!'
ELSE
PRINT N'Phương trình có một nghiệm là:'+N' '+STR(@X)

EXEC CAU10 3,4 

Câu 11: Viết thủ tục đưa vào mã khoa, thủ tục trả về số sinh viên của khoa đó.

Mã:

 CREATE PROC CAU11(@MAKHOA NVARCHAR(5))
AS
BEGIN
DECLARE @DEM INT
SELECT @DEM=(SELECT COUNT(S.MASV) FROM SINHVIEN S JOIN LOP L ON S.MALOP=L.MALOP
JOIN KHOA K ON L.MAKHOA=K.MAKHOA WHERE @MAKHOA=L.MAKHOA)
PRINT N'Số sinh viên của khoa có mã khoa: '+@MAKHOA+N' la '+ STR(@DEM)+N' sinh viên'
END
EXEC CAU11 'khmt' 

Câu 12: Viết thủ tục với tham số đưa vào là mã sinh viên, mã học phần, lần thi và hiển thị ra câu thông báo “Họ tên sinh viên học lớp thi học phần đạt loại’ (A,B,C,D,F)

Mã:

 create proc cau12 (@masv nvarchar(10), @mahocphan nvarchar(10), @lanthi int)
as
begin
	declare @diemthi float
	declare @hodem nvarchar(25)
	declare @ten nvarchar(10)
	declare @tenlop nvarchar(60)
	declare @tenhocphan nvarchar(50)
	select @hodem=hodem, @ten=ten, @tenlop=tenlop, @diemthi=diem, @mahocphan=hocphan.mahocphan, @tenhocphan=tenhocphan
	from sinhvien inner join lop on sinhvien.malop=lop.malop 
			inner join diemthi on sinhvien.masv=diemthi.masv
			inner join hocphan on diemthi.mahocphan=hocphan.mahocphan
	where sinhvien.masv=@masv and hocphan.mahocphan=@mahocphan and lanthi=@lanthi
	print 'Sinh viên '+@hodem+' '+@ten+' học lớp '+@tenlop+' thi học phần '+'"'+@tenhocphan+'"'+' được '+str(@diemthi)+' điểm, đạt loại: '+
			case
				when @diemthi>=8.5 then 'A'
				when @diemthi>=7.5 then 'B'
				when @diemthi>=5.5 then 'C'
				when @diemthi>=4.5 then 'D'
				else 'F'
			end
end
exec cau12 'dl01-002', 'tindc', '1' 

Câu 13: Xây dựng thủ tục hiển thị tất cả thông tin của sinh viên từ bảng SINHVIEN có ngày sinh Từ ngày và Đến ngày (2 tham số vào)
a. Nếu không truyền tham số Đến ngày thì hiển thị thông tin có Ngaysinh>=Từ ngày.
b. Nếu không truyền vào tham số Từ ngày thì hiển thị thông tin có Ngaysinh<=Đến ngày.
c. Nếu cả 2 tham số không truyền vào thì hiển thị tất cả.

Mã:

 CREATE PROC CAU13(@TUNGAY DATETIME=NULL,@DENNGAY DATETIME=NULL)
AS
begin
IF @TUNGAY IS NULL AND @DENNGAY IS NULL
	SELECT * FROM SINHVIEN
ELSE 
	IF @TUNGAY IS NULL
		SELECT * FROM SINHVIEN WHERE NGAYSINH<=@DENNGAY
	ELSE IF @DENNGAY IS NULL
		SELECT * FROM SINHVIEN WHERE NGAYSINH>=@TUNGAY
ELSE SELECT * FROM SINHVIEN
	WHERE NGAYSINH BETWEEN @TUNGAY AND @DENNGAY
End

exec CAU13 '1986-03-15','1986-08-09'