CREATE DATABASE QUANLYDIEMRENLUYEN

USE QUANLYDIEMRENLUYEN

CREATE TABLE LOP(

MAL CHAR(10) PRIMARY KEY,

TENL CHAR(20) NOT NULL

CREATE TABLE SV(

MASV CHAR(10) PRIMARY KEY,

TENSV CHAR(30) NOT NULL,

NGAYSINH SMALLDATETIME,

MAL CHAR(10),

CONSTRAINT KN_SV FOREIGN KEY (MAL) REFERENCES LOP(MAL)

CREATE TABLE DIEMRL(

MASV CHAR(10),

HOCKY INT,

NAM CHAR(4),

DIEM INT--DIEM REN LUYEN--

CONSTRAINT KC_DIEMRL PRIMARY KEY(MASV,HOCKY,NAM),

CONSTRAINT KN_DIEMRL FOREIGN KEY (MASV) REFERENCES SV(MASV)

--2. Hãy tạo view để tổng hợp thông tin về điểm rèn luyện của những sinh viên học lớp “Cao đẳng K4A”--

CREATE VIEW VD2

AS

SELECT * FROM DIEMRL

WHERE MASV IN (SELECT MASV FROM SV

WHERE MAL IN (SELECT MAL FROM LOP

WHERE TENL='CAO DANG K4A'))

--3. Hãy tạo view để in ra thông tin về sinh viên có điểm rèn luyện cao nhất.--

ALTER VIEW VD3

AS

SELECT  *

FROM SV

WHERE MASV IN (SELECT MASV FROM DIEMRL

WHERE DIEM IN (SELECT MAX(DIEM) FROM DIEMRL))

--4. Tạo view để tổng hợp thông tin về những sinh viên có điểm rèn luyện<50 trong năm học 2006-2007.--

CREATE VIEW VD4

AS

SELECT * FROM SV

WHERE MASV IN(SELECT MASV FROM DIEMRL

WHERE DIEM<50 AND NAM ='2006-2007')

--5. Tạo thủ tục có tham số vào @Masv để đưa ra thông tin về điểm rèn luyện của sinh viên trên.--

CREATE PROC VD5

@MASV CHAR(10)

AS

SELECT * FROM DIEMRL

WHERE MASV =@MASV

EXEC VD5 'SV2'

/*6. Tạo thủ tục có tham số vào @Malop, @Nam để đưa ra thông tin về điểm rèn luyện của lớp trên vào 

nam học trên.*/

CREATE PROC VD6

@MAL CHAR(10),@NAM CHAR(4)

AS

SELECT * FROM DIEMRL

WHERE NAM=@NAM AND MASV IN (SELECT MASV FROM SV

WHERE MAL=@MAL)

EXEC VD6 'L01','2010'

--7. Tạo thủ tục có tham số vào @tenlop để đưa ra thông tin về điểm rèn luyện của lớp trên.--

CREATE PROC VD7

@TENL CHAR(20)

AS

SELECT * FROM DIEMRL

WHERE MASV IN (SELECT MASV FROM SV

WHERE MAL IN (SELECT MAL FROM LOP

WHERE TENL=@TENL))

EXEC VD7 'TH'

/*8. Tạo Trigger kiểm tra việc nhập dữ liệu cho bảng DIEMRL nếu điểm nhập vào <0 hoặc >100 thì đưa 

ra yêu cầu nhập lại và bản ghi này không được phép nhập vào bảng, ngược lại thì in ra thông báo nhập

 thành công.*/

CREATE TRIGGER TG8

ON DIEMRL

FOR INSERT

AS

IF EXISTS (SELECT DIEM FROM DIEMRL

WHERE DIEM<0 OR DIEM >100)

BEGIN 

PRINT 'DU LIEU NHAP KHONG HOP LE'

ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO DIEMRL

VALUES('SV4','2','2007',90)

/*9. Sử dụng kiểu dữ liệu Cursor để in ra màn hình danh sách điểm rèn luyện của học kỳ 1 năm học 

2007-2008.*/

DECLARE CS9 CURSOR FOR

SELECT DIEMRL.*

FROM DIEMRL

WHERE HOCKY='1' AND NAM='2007-2008'

--MO--

OPEN CS9

--XU LY--

PRINT 'DANH SACH DIEM REN LUYEN CUA HOC KY 1 NAM HOC 2007-2008 LA:'

PRINT 'MASV  HOCKY    NAM    DIEM'

DECLARE @MASV CHAR(10),@HOCKY INT,@NAM CHAR(20),@DIEM INT

FETCH NEXT FROM CS9

INTO @MASV,@HOCKY,@NAM,@DIEM

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @MASV+CONVERT(CHAR(10),@HOCKY)+@NAM+CONVERT(CHAR(10),@DIEM)

FETCH NEXT FROM CS9

INTO @MASV,@HOCKY,@NAM,@DIEM

END

--DONG--

CLOSE CS9

--HUY--

DEALLOCATE CS9

--10. Sử dụng kiểu dữ liệu Cursor để in ra màn hình danh sách điểm rèn luyện của năm học 2006-2007.--

--KHAI BAO--

DECLARE CS10 CURSOR FOR

SELECT DIEMRL.*

FROM DIEMRL

WHERE NAM='2006-2007' 

--MO--

OPEN CS10

--XU LY--

PRINT 'DANH SACH DIEM REN LUYEN CUA HOC KY 1 NAM HOC 2007-2008 LA:'

PRINT 'MASV  HOCKY    NAM    DIEM'

DECLARE @MASV CHAR(10),@HOCKY INT,@NAM CHAR(20),@DIEM INT

FETCH NEXT FROM CS10

INTO @MASV,@HOCKY,@NAM,@DIEM

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @MASV+CONVERT(CHAR(10),@HOCKY)+@NAM+CONVERT(CHAR(10),@DIEM)

FETCH NEXT FROM CS10

INTO @MASV,@HOCKY,@NAM,@DIEM

END

--DONG--

CLOSE CS10

--HUY--

DEALLOCATE CS10