SQL Chapter678

USE SQLTest4
GO
IF
	EXISTS (
	SELECT
		SC.Cno,
		SC.Grade,
		SC.Sno,
		Course.CNAME,
		Course.Ccredit,
		Course.Cpno 
	FROM
		Course,
		SC 
	WHERE
		SC.Cno = Course.CNO 
		AND Course.CNAME = '数据库' 
	AND SC.Grade > 75 
	) PRINT '平均成绩高于75分'


USE SQLTest4
GO 
IF ((SELECT AVG (Grade) FROM SC WHERE cno=3)< 60) PRINT '不及格' 
ELSE IF ((SELECT AVG (Grade) FROM SC WHERE cno=3)>=90) PRINT '优秀' 
ELSE PRINT '合格'

DECLARE	@AVGGRADE INT
WHILE( 1 = 1 ) 
BEGIN
	SELECT @AVGGRADE = AVG(GRADE) FROM SC
	IF (@AVGGRADE < 85)
		 UPDATE SC SET GRADE = CASE
		 WHEN GRADE < 60 THEN GRADE + 5
		 WHEN GRADE BETWEEN 60 AND 69 THEN GRADE + 3
		 WHEN GRADE BETWEEN 70 AND 79 THEN GRADE + 2
		 WHEN GRADE BETWEEN 80 AND 89 THEN GRADE + 1
		 END
	ELSE
		BREAK
END
 

-- 创建
CREATE PROC Proc_1 AS
SELECT Student.SNO ,Student.SNAME, Student.SSex,Course.CNO,Course.Ccredit,Student.Sdept FROM Student,SC,Course
WHERE  SC.Cno=Course.Cno AND Course.Ccredit=4;

-- 执行
USE SQLTest4 
GO
EXEC Proc_1

-- 创建
CREATE PROC Proc_2 
(@ID1 CHAR(6), @ID2 CHAR(6),@result INT OUTPUT ) AS BEGIN
DECLARE @A1 INT, @A2 INT
SET @A1=(SELECT AVG(GRADE) FROM SC WHERE SNO = @ID1) 
SET @A2=(SELECT AVG(GRADE) FROM SC WHERE SNO = @ID2) 
IF @A1 > @A2 SET @result = 0 ELSE
SET @result = 1 END


-- 执行
DECLARE @result INT
EXEC Proc_2 '201215121', '201215122', @result 
OUTPUT SELECT @result

CREATE TRIGGER sex_control1 ON STUDENT
FOR INSERT,UPDATE
AS 
DECLARE @SEX CHAR(5)
SET @SEX = (SELECT SSex FROM INSERTED)
BEGIN 
IF (@SEX NOT IN ('男','女'))
 PRINT '性别只能是男或女'
 ROLLBACK
END



INSERT INTO student VALUES('112','John','烫','CS')