把文档中的问题解答出来
时间: 2024-12-20 07:15:18 浏览: 9
.NET6.0官方中文文档.pdf
5星 · 资源好评率100%
以下是《实验6:函数存储过程触发器——学生选课案例》中的问题及其解答:
### 1. 编写函数,返回选修“指定课程号(如c01)”的学生人数。
**代码示例:**
```sql
CREATE FUNCTION GetStudentCountByCourse (@courseNo CHAR(4))
RETURNS INT
AS
BEGIN
DECLARE @count INT;
SELECT @count = COUNT(*)
FROM cjb
WHERE cno = @courseNo;
RETURN @count;
END;
-- 调用函数
SELECT dbo.GetStudentCountByCourse('c01');
```
### 2. 使用函数根据学生的姓名和课程名返回该学生该门课程的成绩等级。
**代码示例:**
```sql
CREATE FUNCTION GetGradeLevel (@studentName VARCHAR(8), @courseName VARCHAR(20))
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @grade DECIMAL(3);
DECLARE @level VARCHAR(10);
SELECT @grade = grade
FROM cjb
JOIN student ON cjb.sno = student.sno
JOIN course ON cjb.cno = course.cno
WHERE student.sname = @studentName AND course.cname = @courseName;
IF @grade >= 90 SET @level = '优秀';
ELSE IF @grade >= 75 SET @level = '良好';
ELSE IF @grade >= 60 SET @level = '中等';
ELSE SET @level = '不及格';
RETURN @level;
END;
-- 调用函数
SELECT dbo.GetGradeLevel('黄蓉', '高数');
```
### 3. 创建函数,根据学号和课程号返回该学生获得的学分。
**代码示例:**
```sql
CREATE FUNCTION GetCreditByStudentAndCourse (@studentNo CHAR(6), @courseNo CHAR(4))
RETURNS DECIMAL(1)
AS
BEGIN
DECLARE @credit DECIMAL(1);
DECLARE @grade DECIMAL(3);
SELECT @grade = grade
FROM cjb
WHERE sno = @studentNo AND cno = @courseNo;
IF @grade >= 60
SELECT @credit = credit
FROM course
WHERE cno = @courseNo;
ELSE
SET @credit = 0;
RETURN @credit;
END;
-- 调用函数
SELECT dbo.GetCreditByStudentAndCourse('s01', 'c01');
SELECT dbo.GetCreditByStudentAndCourse('s01', 'c02');
```
### 4. 编写存储过程,查询所有课程的课程名、课程号、选课人数和平均分。
**代码示例:**
```sql
CREATE PROCEDURE GetAllCoursesInfo
AS
BEGIN
SELECT
course.cno,
course.cname,
COUNT(cjb.sno) AS student_count,
AVG(cjb.grade) AS average_grade
FROM course
LEFT JOIN cjb ON course.cno = cjb.cno
GROUP BY course.cno, course.cname;
END;
-- 执行存储过程
EXEC GetAllCoursesInfo;
```
### 5. 编写存储过程,利用游标逐条输出所有不及格学生的学号、姓名、选修的课程名及成绩。
**代码示例:**
```sql
CREATE PROCEDURE GetFailedStudents
AS
BEGIN
DECLARE @studentNo CHAR(6);
DECLARE @studentName VARCHAR(8);
DECLARE @courseName VARCHAR(20);
DECLARE @grade DECIMAL(3);
DECLARE FailedCursor CURSOR FOR
SELECT
cjb.sno,
student.sname,
course.cname,
cjb.grade
FROM cjb
JOIN student ON cjb.sno = student.sno
JOIN course ON cjb.cno = course.cno
WHERE cjb.grade < 60;
OPEN FailedCursor;
FETCH NEXT FROM FailedCursor INTO @studentNo, @studentName, @courseName, @grade;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '学号: ' + @studentNo + ', 姓名: ' + @studentName + ', 课程名: ' + @courseName + ', 成绩: ' + CAST(@grade AS VARCHAR(3));
FETCH NEXT FROM FailedCursor INTO @studentNo, @studentName, @courseName, @grade;
END;
CLOSE FailedCursor;
DEALLOCATE FailedCursor;
END;
-- 执行存储过程
EXEC GetFailedStudents;
```
### 6. 创建并执行存储过程,查询所有学生的姓名、课程名和成绩等级。
**代码示例:**
```sql
CREATE PROCEDURE GetAllStudentsGrades
AS
BEGIN
SELECT
student.sname,
course.cname,
CASE
WHEN cjb.grade >= 90 THEN '优秀'
WHEN cjb.grade >= 75 THEN '良好'
WHEN cjb.grade >= 60 THEN '中等'
ELSE '不及格'
END AS grade_level
FROM cjb
JOIN student ON cjb.sno = student.sno
JOIN course ON cjb.cno = course.cno;
END;
-- 执行存储过程
EXEC GetAllStudentsGrades;
```
### 7. 创建并执行存储过程,利用游标逐条输出所有课程的课程名、选课人数和平均分。
**代码示例:**
```sql
CREATE PROCEDURE GetCourseStats
AS
BEGIN
DECLARE @courseNo CHAR(4);
DECLARE @courseName VARCHAR(20);
DECLARE @studentCount INT;
DECLARE @averageGrade DECIMAL(5, 2);
DECLARE CourseCursor CURSOR FOR
SELECT cno, cname
FROM course;
OPEN CourseCursor;
FETCH NEXT FROM CourseCursor INTO @courseNo, @courseName;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @studentCount = COUNT(*), @averageGrade = AVG(grade)
FROM cjb
WHERE cno = @courseNo;
PRINT '课程号: ' + @courseNo + ', 课程名: ' + @courseName + ', 选课人数: ' + CAST(@studentCount AS VARCHAR(10)) + ', 平均分: ' + CAST(ISNULL(@averageGrade, 0) AS VARCHAR(10));
FETCH NEXT FROM CourseCursor INTO @courseNo, @courseName;
END;
CLOSE CourseCursor;
DEALLOCATE CourseCursor;
END;
-- 执行存储过程
EXEC GetCourseStats;
```
### 8. 创建并执行存储过程,根据学号输出该生选修的课程门数和平均分。
**代码示例:**
```sql
CREATE PROCEDURE GetStudentStats (@studentNo CHAR(6))
AS
BEGIN
DECLARE @courseCount INT;
DECLARE @averageGrade DECIMAL(5, 2);
SELECT @courseCount = COUNT(*), @averageGrade = AVG(grade)
FROM cjb
WHERE sno = @studentNo;
SELECT
@studentNo AS student_no,
@courseCount AS course_count,
@averageGrade AS average_grade;
END;
-- 执行存储过程
EXEC GetStudentStats 's01';
```
### 9. 利用触发器,控制成绩表中的成绩属性列取值在0到100之间。
**代码示例:**
```sql
CREATE TRIGGER trg_CheckGradeRange
ON cjb
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE grade < 0 OR grade > 100)
BEGIN
RAISERROR ('成绩必须在0到100之间', 16, 1);
ROLLBACK TRANSACTION;
END;
END;
-- 验证触发器
INSERT INTO cjb (sno, cno, grade) VALUES ('s01', 'c01', 105);
```
### 10. 定义一个限制学生选课门数不超过3门的触发器。
**代码示例:**
```sql
CREATE TRIGGER trg_LimitCourseSelection
ON cjb
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (
SELECT sno
FROM inserted
GROUP BY sno
HAVING COUNT(*) > 3
)
BEGIN
RAISERROR ('每个学生最多只能选修3门课程', 16, 1);
ROLLBACK TRANSACTION;
END;
END;
-- 验证触发器
INSERT INTO cjb (sno, cno, grade) VALUES ('s01', 'c04', 80);
```
### 11. 若STUDENT表和SC表不存在关系(即无外键关联),定义两个触发器。
#### (1) 当更改STUDENT表中的学号时同步更改CJB表中对应的学号。
**代码示例:**
```sql
CREATE TRIGGER trg_UpdateStudentID
ON student
AFTER UPDATE
AS
BEGIN
IF UPDATE(sno)
BEGIN
UPDATE cjb
SET sno = i.sno
FROM inserted i
WHERE cjb.sno = deleted.sno;
END;
END;
-- 验证触发器
UPDATE student
SET sno = 's09'
WHERE sno = 's01';
```
#### (2) 当删除学生记录时同步删除CJB表中对应的记录。
**代码示例:**
```sql
CREATE TRIGGER trg_DeleteStudentRecord
ON student
AFTER DELETE
AS
BEGIN
DELETE FROM cjb
WHERE sno IN (SELECT sno FROM deleted);
END;
-- 验证触发器
DELETE FROM student
WHERE sno = 's09';
```
这些代码示例涵盖了文档中提到的所有问题,并提供了相应的解决方案。希望对你有所帮助!
阅读全文