11-25

感觉最后一题不太合理，过段时间再补，好折磨啊

我去凹方舟27了

11-26

凹好了，最后一题也补完了，好耶

--使用数据库student，完成以下操作，并写出相应的代码
--1.   用函数实现：求某个专业选修了某门课程的学生人数，并调用函数求出计算机系“数据库”课程的选课人数。
USE Student
GO
CREATE FUNCTION dbo.Sdept_select_amount(@sdept VARCHAR(20),@cname CHAR(40))
RETURNS INT
BEGIN
    DECLARE @tot INT
    SELECT @tot=COUNT(*)
    FROM Student JOIN dbo.SC S on Student.Sno = S.Sno JOIN Course C on C.Cno = S.Cno
    WHERE Cname=@cname AND Sdept=@sdept
    RETURN @tot
end
GO

SELECT dbo.Sdept_select_amount('CS','数据库') AS '计算机系选数据库课程的人数'
--2.   用内嵌表值函数实现：查询某个专业所有学生所选的每门课的平均成绩；调用该函数求出计算机系的所有课程的平均成绩。
CREATE FUNCTION dbo.Sdept_average_grade(@sdept VARCHAR(20))
RETURNS TABLE
AS
    RETURN
        (
            SELECT C.Cno, C.Cname, AVG(Grade) AS '平均成绩'
            FROM Student
                     JOIN dbo.SC S on Student.Sno = S.Sno
                     JOIN Course C on C.Cno = S.Cno
            WHERE Sdept = @sdept
            GROUP BY C.Cno, C.Cname
        )
GO

SELECT * FROM Sdept_average_grade('CS')

--3.   创建多语句表值函数，通过学号作为实参调用该函数，可显示该学生的姓名以及各门课的成绩和学分，调用该函数求出“200515002”的各门课成绩和学分。
CREATE FUNCTION dbo.Student_info(@sno CHAR(9))
RETURNS @Student_info TABLE(Sname CHAR(20),Cname CHAR(40),Grade SMALLINT,Ccredit SMALLINT)
AS
BEGIN
    INSERT @Student_info
    SELECT Sname,Cname,Grade,Ccredit
    FROM Student JOIN dbo.SC S on Student.Sno = S.Sno JOIN Course C on C.Cno = S.Cno
    WHERE Student.Sno=@sno
RETURN
end
GO

SELECT* FROM dbo.Student_info ('200515002')
--4.   编写一个存储过程，统计某门课程的优秀（90-100）人数、良好（80-89）人数、中等（70-79）人数、及格（60-69）人数和及格率，
-- 其输入参数是课程号，输出的是各级别人数及及格率，及格率的形式是90.25%，执行存储过程，在消息区显示1号课程的统计信息。
CREATE PROCEDURE Course_percent @cno CHAR(4) AS
    DECLARE @S INT,@A INT,@B INT,@C INT,@per VARCHAR(10)
    SELECT  @S=COUNT(CASE WHEN Grade BETWEEN 90 AND 100 THEN 1 END),
            @A=COUNT(CASE WHEN Grade BETWEEN 80 AND 89 THEN 1 END),
            @B=COUNT(CASE WHEN Grade BETWEEN 70 AND 79 THEN 1 END),
            @C=COUNT(CASE WHEN Grade BETWEEN 60 AND 69 THEN 1 END),
            @per=CONCAT(CAST(CONVERT(FLOAT,COUNT(CASE WHEN Grade BETWEEN 60 AND 100 THEN 1 END))/CONVERT(FLOAT,COUNT(DISTINCT Sno))*100.0 AS NUMERIC(10,2)),'%')
    FROM SC JOIN Course C2 on C2.Cno = SC.Cno WHERE @cno=SC.Cno
    PRINT '优秀人数：'+CAST(@S AS VARCHAR(10))+' 良好人数：'+CAST(@A AS VARCHAR(10))+' 中等人数：'+CAST(@B AS VARCHAR(10))+' 及格人数：'+CAST(@C AS VARCHAR(10))+' 及格率：'+@per;
GO

EXEC Course_percent '1'
--5.   创建一个带有输入参数的存储过程，该存储过程根据传入的学生名字，查询其选修的课程名和成绩，执行存储过程，在消息区显示赵菁菁的相关信息。
CREATE PROCEDURE Student_name_info @sname CHAR(20) AS
    DECLARE @cname VARCHAR(40),@grade SMALLINT
    DECLARE csr1 CURSOR FOR
        SELECT Cname,Grade
        FROM SC JOIN Course C on C.Cno = SC.Cno JOIN Student S on S.Sno = SC.Sno
        WHERE @sname=S.Sname
    OPEN csr1
    FETCH NEXT FROM csr1 INTO @cname,@grade
    PRINT @sname
    PRINT '-----------------------'
    PRINT '  课程名          成绩'
    WHILE @@FETCH_STATUS=0
    BEGIN
        PRINT @cname+CAST(@grade AS CHAR(4))
        FETCH NEXT FROM csr1 INTO @cname,@grade
    end
    PRINT '======================='
    CLOSE csr1
    DEALLOCATE csr1
GO

EXEC Student_name_info '赵菁菁'
--消息区那必得游标了啊
--6.   以基本表 course为基础，完成如下操作
--生成显示如下报表形式的游标：报表首先列出学生的学号和姓名，然后在此学生下，列出其所选的全部课程的课程号、课程名和学分；依此类推，直到列出全部学生。
-- CREATE PROCEDURE Student_table AS
DECLARE @sno CHAR(9),@sname VARCHAR(20),@cno CHAR(4),@cname VARCHAR(40),@ccredit SMALLINT
DECLARE csr2_1 CURSOR FOR
    SELECT Sno,Sname FROM Student
OPEN csr2_1
FETCH NEXT FROM csr2_1 INTO @sno,@sname
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT @sno+' '+@sname
    PRINT '----------------------------------------------------------'
    PRINT '课程号           课程名                                  学分'
    DECLARE csr2_2 CURSOR FOR
        SELECT C.Cno,Cname,Ccredit FROM SC JOIN Course C on C.Cno = SC.Cno WHERE @sno=Sno
    OPEN csr2_2
    FETCH NEXT FROM csr2_2 INTO @cno,@cname,@ccredit
    WHILE @@FETCH_STATUS=0
    BEGIN
        PRINT @cno+'            '+@cname+CONVERT(CHAR(4),@ccredit)
        FETCH NEXT FROM csr2_2 INTO @cno,@cname,@ccredit
    end
    PRINT '----------------------------------------------------------'
    CLOSE csr2_2
    DEALLOCATE csr2_2
    FETCH NEXT FROM csr2_1 INTO @sno,@sname
end
CLOSE csr2_1
DEALLOCATE csr2_1
GO

-- EXEC Student_table
--7.   请设计一个存储过程实现下列功能：判断某个专业某门课程成绩排名为n的学生的成绩是否低于该门课程的平均分，如果低于平均分，则将其成绩改为平均分，否则输出学号、姓名、班号、课程号、课程名、成绩。（提示：可以在存储过程内部使用游标）。
CREATE PROCEDURE  Student_grade_update @cno CHAR(4),@rank INT AS
    DECLARE @avg INT,@sno CHAR(9),@grade SMALLINT
    SELECT @avg=AVG(Grade) FROM Student JOIN SC S on Student.Sno = S.Sno WHERE @cno=Cno
    DECLARE csr3 SCROLL CURSOR FOR
        SELECT Sno,Cno,Grade
        FROM SC
        WHERE @cno=Cno
        ORDER BY Grade DESC
    OPEN csr3
    FETCH ABSOLUTE @rank FROM csr3 INTO @sno,@cno,@grade
    IF @grade<@avg
        BEGIN
            PRINT '成绩低于这门课程平均分'
            UPDATE SC SET Grade=@avg WHERE @sno=Sno AND @cno=Cno
        end
    ELSE BEGIN
            SELECT S2.Sno,Sname,C.Cno,Cname,Grade
            FROM Student JOIN SC S2 on Student.Sno = S2.Sno JOIN Course C on C.Cno = S2.Cno
            WHERE @cno=C.Cno AND @sno=S2.Sno
    end
    CLOSE csr3
    DEALLOCATE csr3
GO

EXEC Student_grade_update 1 ,1
EXEC Student_grade_update 1,6

--8.   对student数据库设计存储过程，实现将某门课程成绩低于课程平均成绩的学生成绩都加上3分。（提示可以使用存储过程内部使用游标）
CREATE PROCEDURE Student_grade_update_plus3 @cno CHAR(4) AS
    DECLARE @avg INT,@sno CHAR(9),@grade SMALLINT
    SELECT @avg=AVG(Grade) FROM SC WHERE Cno=@cno
    DECLARE csr4 CURSOR FOR
    SELECT Sno,Grade FROM SC WHERE Cno=@cno
    OPEN csr4
    FETCH NEXT FROM csr4 INTO @sno,@grade
    WHILE @@FETCH_STATUS=0
    BEGIN
        IF @grade<@avg
        BEGIN
            UPDATE SC SET Grade=Grade+3 WHERE @sno=Sno AND @cno=Cno
        end
        FETCH NEXT FROM csr4 INTO @sno,@grade
    end
    CLOSE csr4
    DEALLOCATE csr4
GO
EXEC Student_grade_update_plus3 10
--9.   设计存储过程实现学生转专业功能：某个学生（学号）在转专业时，如果想转入的专业是计算机专业那么要求该学生的平均成绩必须超过95分，
-- 否则不允许转专业，并将转专业的信息插入到一个转专业的表里，changesd(学号，原专业，新专业，平均成绩)
CREATE TABLE Changed
(
    Sno CHAR(9) PRIMARY KEY ,
    Odept VARCHAR(20),
    Ndept VARCHAR(20),
    Avggrade SMALLINT,
    FOREIGN KEY (SNO) REFERENCES Student(Sno)
)
CREATE PROCEDURE Student_sdept_trans @sno CHAR(9),@sdept VARCHAR(20) AS
    DECLARE @avg INT,@odept VARCHAR(20)
    SELECT @avg=AVG(Grade) FROM SC WHERE Sno=@sno
    SELECT @odept=Sdept FROM Student WHERE @sno=Sno
    IF @avg<=95 AND @sdept='CS'
        BEGIN
            PRINT '要求该学生的平均成绩必须超过95分'
        end
    ELSE BEGIN
            UPDATE Student SET Sdept=@sdept WHERE Sno=@sno
            INSERT INTO Changed(SNO, ODEPT, NDEPT, AVGGRADE)
            VALUES (@sno,@odept,@sdept,@avg)
    end
GO
EXEC Student_sdept_trans 200515009 ,'CS'
--10.  现有图书管理数据库， 其中包含如下几个表：
--读者表：reader(学号，姓名，性别，余额)
--借书表：lend（学号，书号，借书日期，应还日期，是否续借）
--欠款表：debt(学号，日期，欠款金额)
--还书表：return(学号，书号，还书日期)
--请设计一个存储过程实现续借或还书操作，具体要求如下：
--只有没有超期的书才可以续借（借书和续借时间都为30天），修改应还日期，
-- 否则只能还书；还书时删除借书表内的借阅记录，并向还书表中插入一条还书记录，注意还书日期为当前日期，
-- 并且对超期图书，按照超期的天数计算出罚款金额（每天每本书罚款0.1元），将罚款信息插入到欠款表中，同时将罚款从读者表的余额里扣除。
-- CREATE PROCEDURE Library_renew_return @sno CHAR(9),@bno CHAR(9) AS
--     DECLARE @day DATETIME,@datediff INT
--     SELECT @day=GETDATE()
--     SELECT @datediff=DATEDIFF(dd,lend.应还日期,@day) FROM lend WHERE @sno=Sno AND @bno=Bno
--     IF @datediff>=0 --可以续借
--         BEGIN
--             UPDATE lend SET 应还日期=DATEADD(yyyy-MM-dd,30,应还日期) WHERE @sno=Sno AND @bno=Bno
--         end
--     ELSE IF @datediff<0
--         BEGIN
--             PRINT '已经超期，只能还书不能续借'
--             DELETE FROM lend WHERE @sno=Sno AND @bno=Bno
--             INSERT INTO return(学号,书号,还书日期) VALUES (@sno,@bno,GETDATE())
--             INSERT INTO debt(学号,日期,欠款金额) VALUES (@sno,GETDATE(),CONVERT(FLOAT,ABS(@datediff))*0.1)
--         end
-- GO
--有待改进
--至少我觉得应该是两个存储过程，一个负责续借，一个负责还书？
--虽然写的是设计一个存储过程这样子，但是上面这一个过程就，如果没超期就会自动给你续借，只有超了才可以还而且还必须要交钱，
--想想就不大合理
CREATE PROCEDURE Library_renew @sno CHAR(9),@bno CHAR(9) AS
    DECLARE @day DATETIME,@datediff INT
    SELECT @day=GETDATE()
    SELECT @datediff=DATEDIFF(dd,lend.应还日期,@day) FROM lend WHERE @sno=Sno AND @bno=Bno
    IF @datediff>=0 --可以续借
        BEGIN
            UPDATE lend SET 应还日期=DATEADD(yyyy-MM-dd,30,应还日期) WHERE @sno=Sno AND @bno=Bno
        end
    ELSE IF @datediff<0
        BEGIN
            PRINT '已经超期，只能还书不能续借'
        END
GO

CREATE PROCEDURE Library_return @sno CHAR(9),@bno CHAR(9) AS
    DECLARE @day DATETIME,@datediff INT
    SELECT @day=GETDATE()
    SELECT @datediff=DATEDIFF(dd,lend.应还日期,@day) FROM lend WHERE @sno=Sno AND @bno=Bno
    IF @datediff<0
        BEGIN
            PRINT '已经超期，请缴纳罚金'
            INSERT INTO debt(学号,日期,欠款金额) VALUES (@sno,GETDATE(),CONVERT(FLOAT,ABS(@datediff))*0.1)
        end
    INSERT INTO return(学号,书号,还书日期) VALUES (@sno,@bno,GETDATE())
    DELETE FROM lend WHERE @sno=Sno AND @bno=Bno
GO
--以上是拆分成续借和还书两个部分之后的代码