shiyan5.sql

来自「一般的数据库系统」· SQL 代码 · 共 60 行

USE XSCJ
EXEC SP_ADDTYPE 'student_num','char(9)','not null'

SELECT XS_KC.XH,XM,GRADE=
  CASE WHEN CJ>=85 THEN '优秀'
  WHEN CJ>=75 THEN '良好'
  WHEN CJ>=60 THEN '合格'
  ELSE '不合格'
END
FROM XS,XS_KC,KC

DECLARE @N INT,@T INT
SET @N=2
WHILE @N<=1000
 BEGIN 
  SET @T=2
  WHILE @T<@N
   BEGIN 
     IF @N%@T=0
      BREAK
     SET @T=@T+1
   END
  IF @T>=@N
  SELECT @N
  SET @N=@N+1
 END

CREATE FUNCTION AVERAGE(@CNUM VARCHAR(20))RETURNS INT
 AS
 BEGIN
   DECLARE @AVER INT
   SELECT @AVER=
     ( SELECT AVG(CJ)
       FROM XS_KC
       WHERE KCH=@CNUM
       GROUP BY KCH
      )
 RETURN @AVER
END

DECLARE @AVER INT
EXEC @AVER=DBO.AVERAGE @CNUM='101'
SELECT @AVER AS '101课程的平均成绩'


CREATE FUNCTION ST_SCORE
 (@ID CHAR(6))
 RETURNS TABLE
 AS RETURN 
 (
   SELECT XS.XH,XM,KCM,CJ
   FROM XS INNER JOIN XS_KC
        INNER JOIN KC
        ON XS_KC.KCH=KC.KCH
        ON XS.XH=XS_KC.XH
   WHERE XS.XH=@ID)

SELECT *
 FROM ST_SCORE('001101')