CREATE TABLE STUDENT
(	
	SID INT PRIMARY KEY IDENTITY,
	SNAME NVARCHAR(50),
	SAGE DATETIME,
	SSEX CHAR(4)
)
CREATE TABLE TEACHER
(
	TID INT PRIMARY KEY IDENTITY,
	TNAME NVARCHAR(50)
)
CREATE TABLE COURSE
(
	CID INT PRIMARY KEY IDENTITY,
	CNAME NVARCHAR(50),
	TID INT FOREIGN KEY REFERENCES TEACHER(TID)
)
CREATE TABLE SCORE
(
	ID INT PRIMARY KEY IDENTITY,
	SID INT FOREIGN KEY REFERENCES STUDENT(SID),
	CID INT FOREIGN KEY REFERENCES COURSE(CID),
	SCORES INT
)

– create stored procedures to insert data for student tables

GO
CREATE PROC P_INSERTSTU
@SNAME NVARCHAR(50),
@SAGE DATETIME,
@SSEX CHAR(4)
AS
BEGIN
	INSERT INTO STUDENT VALUES(@SNAME,@SAGE,@Ssex)
END

– create trigger, delete student information, and print out "warning: XX students have been deleted, please know!"

GO
CREATE TRIGGER T_DELETESTU
ON STUDENT
AFTER DELETE
AS
BEGIN
	DECLARE @STUNAME NVARCHAR(50)
	DELETE STUDENT WHERE SID IN (SELECT SID FROM DELETED)
	SELECT @STUNAME =SNAME FROM DELETED
	PRINT 'Warning:'+@STUNAME+'Students have been deleted, please know!'
END

– create trigger, modify student information, and print out "warning: XX student has been modified to XX, please know!"

GO
CREATE TRIGGER T_UPDATESTU
ON STUDENT
AFTER UPDATE
AS
BEGIN
	DECLARE @STUNAMEUB NVARCHAR(50)
	DECLARE @STUNAMEUA NVARCHAR(50)
	SELECT @STUNAMEUA =SNAME FROM INSERTED
	SELECT @STUNAMEUB = SNAME FROM DELETED
	PRINT 'Warning:'+@STUNAMEUB+'Students have been changed to'+@STUNAMEUA+'，Please kindly be informed!'
END

– create a benchmark function with SCORES as the parameter to return NVARCHAR(50). Use CASE WHEN THEN END in the function to grade SCORES > 60 'fail' > 70 'pass' > 80' medium '> 100' excellent '= 100' satisfied '

GO
CREATE FUNCTION FUN_RANK(@SCORES INT)
RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @RESTUCH NVARCHAR(50)
	SELECT @RESTUCH =
	(CASE WHEN @SCORES < 60 THEN 'Fail,',
		    WHEN @SCORES < 70 THEN 'pass',
		    WHEN @SCORES < 80 THEN 'secondary,
		    WHEN @SCORES < 100 THEN 'excellent',
		    WHEN @SCORES = 100 THEN 'Satisfied,
	)
	RETURN @RESTUCH
END

– create a view that shows student number, student name, course name, teacher, and grade grades by calling the grade function

GO
CREATE VIEW V_STUDENT
AS
SELECT S.SID,S.SNAME,C.CNAME,T.TNAME,R.SCORES,DBO.FUN_RANK(R.SCORES) AS RANKS FROM STUDENT S JOIN SCORE R ON S.SID= R.SID JOIN COURSE C ON R.CID= C.CID JOIN TEACHER T ON T.TID = C.TID
GO

– use row to column to subdivide the grade table

SELECT 
SID,
MAX(CASE WHEN CID = 1 THEN SCORES ELSE NULL END) 'Chinese',
MAX(CASE WHEN CID = 2 THEN SCORES ELSE NULL END) 'Mathematics',
MAX(CASE WHEN CID = 3 THEN SCORES ELSE NULL END) 'English?',
 FROM SCORE GROUP BY SID

– create a stored procedure to paginate scores and output the total number of records

GO
CREATE PROC P_PAGESTU
@STUNAME NVARCHAR(20),
@PAGEINDEX INT,
@PAGESIZE INT,
@PAGECOUNT INT OUTPUT
AS
BEGIN 
	SELECT * FROM (
	SELECT R.SCORES,ROW_NUMBER() OVER(ORDER BY S.SID) AS NUM FROM STUDENT S JOIN SCORE R ON S.SID = R.SID WHERE S.SNAME LIKE '%'+@STUNAME+'%') TEMP WHERE TEMP.NUM BETWEEN ((@PAGEINDEX-1)*@PAGESIZE+1) AND (@PAGEINDEX*@PAGESIZE)
	SELECT @PAGECOUNT=COUNT(S.SID) FROM STUDENT S JOIN SCORE R ON S.SID = R.SID WHERE S.SNAME LIKE '%'+@STUNAME+'%' 
	)
END

- create a cursor to correctly read the student's name, i.e. grade

DECLARE CURSOR_SCORE CURSOR SCROLL
FOR SELECT S.SNAME,R.SCORE FROM STUDENT S JOIN SCORE R ON S.SID= R.SID
OPEN CURSOR_SCORE
DECLARE @STUNAME NVARCHAR(20)
DECLARE @STUSCOR INT
FETCH FIRST FROM CURSOR_SCORE INTO @STUNAME,@STUCORE
WHILE @@FETCH_STATUS =0
BEGIN
	SELECT @STUNAME=S.SNAME,@STUSCORE = R.SCORES FROM STUDENT S JOIN SCORE R ON S.SID = R.SID 
	FETCH NEXT FROM CURSOR_SCORE INTO @STUNAME,@STUSCORE
END
PRINT @STUNAME +achievement+CONVERT(NVARCHAR(20),@STUSCORE)
CLOSE CURSOR_SCORE
DEALLOCATE CURSOR_SCORE