• 日期: 2027-11-23
  • 标签: 常规

使用T-SQL语句完成以下学生信息管理系统操作

数据库: STUDENTSCORE

数据表:

  • STUDENT表

    • sno (学号)
    • sname (姓名)
    • ssex (性别)
    • sage (年龄)
    • sdept (所在系)

  • COURSE表

    • cno (课程号)
    • cname (课程名)
    • ccredit (学分)

  • SC表

    • sno (学号)
    • cno (课程号)
    • grade (成绩)

1. 将计算机系的学生信息放入一张名为JSJSTUDENT的新表中，新表中的属性包含（学号，姓名，性别，年龄）。

SELECT sno, sname, ssex, sage
INTO JSJSTUDENT
FROM STUDENT
WHERE sdept = '计算机系'

2. 查询JSJSTUDENT表，将年龄最大的3位学生的信息放入一张本地临时表中，然后查询临时表查看年龄最大的3位学生信息。

SELECT TOP 3 *
INTO #JSJSTUDENT
FROM JSJSTUDENT
ORDER BY sage DESC

SELECT *
FROM #JSJSTUDENT

3. 查询计算机系学生选修课程的信息。结果显示学号，姓名，性别，选修课程名和成绩。（请使用显式内连接和派生表查询两种方式完成）

显式内连接

SELECT S.sno, S.sname, S.ssex, C.cname, SC.grade
FROM STUDENT S
INNER JOIN SC ON S.sno = SC.sno
INNER JOIN COURSE C ON SC.cno = C.cno
WHERE S.sdept = '计算机系'

派生表

SELECT S.sno, S.sname, S.ssex, C.cname, SC.grade
FROM (
    SELECT sno, sname, ssex
    FROM STUDENT
    WHERE sdept = '计算机系'
) S
INNER JOIN SC ON S.sno = SC.sno
INNER JOIN COURSE C ON SC.cno = C.cno

4. 查询没有选修课程的学生学号，姓名和所在系。（请使用外连接方式完成）

SELECT S.sno, S.sname, S.sdept
FROM STUDENT S
LEFT JOIN SC ON S.sno = SC.sno
WHERE SC.sno IS NULL

5. 查询有哪些课程没有人选修，结果显示课程号，课程名和学分。（请使用外连接和嵌套查询两种方式完成）

外连接

SELECT C.cno, C.cname, C.ccredit
FROM COURSE C
LEFT JOIN SC ON C.cno = SC.cno
WHERE SC.sno IS NULL

嵌套查询

SELECT C.cno, C.cname, C.ccredit
FROM COURSE C
WHERE NOT EXISTS (
    SELECT *
    FROM SC
    WHERE SC.cno = C.cno
)

6. 统计每名学生选修课程的门数和平均分，显示学生学号，姓名，所在系和选课门数及课程平均分。结果显示选课门数排名前3的学生信息。

SELECT S.sno, S.sname, S.sdept, COUNT(SC.cno) AS course_count, AVG(SC.grade) AS avg_grade
FROM STUDENT S
INNER JOIN SC ON S.sno = SC.sno
GROUP BY S.sno, S.sname, S.sdept
ORDER BY course_count DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY

7. 统计除2号课程外的其它课程的选课人数和平均分，显示课程号，课程名，学分，选课人数及平均分。最后结果中要包含2号课程的信息。

SELECT C.cno, C.cname, C.ccredit, COUNT(SC.sno) AS student_count, AVG(SC.grade) AS avg_grade
FROM COURSE C
LEFT JOIN SC ON C.cno = SC.cno
WHERE C.cno <> '2号课程'
GROUP BY C.cno, C.cname, C.ccredit

UNION ALL

SELECT C.cno, C.cname, C.ccredit, COUNT(SC.sno) AS student_count, AVG(SC.grade) AS avg_grade
FROM COURSE C
LEFT JOIN SC ON C.cno = SC.cno
WHERE C.cno = '2号课程'
GROUP BY C.cno, C.cname, C.ccredit

8. 查询选修每门课程的男女生人数和平均分，结果显示课程号、性别、选课人数和平均分。（在GROUP BY子句中使用WITH ROLLUP和WITH CUBE短语，比较结果）

WITH ROLLUP

SELECT C.cno, S.ssex, COUNT(SC.sno) AS student_count, AVG(SC.grade) AS avg_grade
FROM COURSE C
INNER JOIN SC ON C.cno = SC.cno
INNER JOIN STUDENT S ON SC.sno = S.sno
GROUP BY C.cno, S.ssex WITH ROLLUP

WITH CUBE

SELECT C.cno, S.ssex, COUNT(SC.sno) AS student_count, AVG(SC.grade) AS avg_grade
FROM COURSE C
INNER JOIN SC ON C.cno = SC.cno
INNER JOIN STUDENT S ON SC.sno = S.sno
GROUP BY C.cno, S.ssex WITH CUBE

9. 查询所有学生的信息，结果统计总人数和平均年龄。（可使用COMPUTE子句或使用批处理）

COMPUTE子句

SELECT sno, sname, ssex, sage, sdept
FROM STUDENT
COMPUTE COUNT(sno), AVG(sage)

批处理

SELECT sno, sname, ssex, sage, sdept
FROM STUDENT

SELECT COUNT(sno) AS total_count, AVG(sage) AS avg_age
FROM STUDENT

10. 查询每个学生选修课程的信息（学号、姓名、课程号、成绩），并统计每个学生选修的课程数和平均分。（可使用COMPUTE BY子句或使用批处理）

COMPUTE BY子句

SELECT S.sno, S.sname, SC.cno, SC.grade
FROM STUDENT S
INNER JOIN SC ON S.sno = SC.sno
ORDER BY S.sno
COMPUTE COUNT(SC.cno), AVG(SC.grade) BY S.sno, S.sname

批处理

SELECT S.sno, S.sname, SC.cno, SC.grade
FROM STUDENT S
INNER JOIN SC ON S.sno = SC.sno
ORDER BY S.sno

SELECT S.sno, S.sname, COUNT(SC.cno) AS course_count, AVG(SC.grade) AS avg_grade
FROM STUDENT S
INNER JOIN SC ON S.sno = SC.sno
GROUP BY S.sno, S.sname

11. 查询选修了 '数据库' 课程的计算机系学生信息，显示学生学号，姓名和成绩。要求最后结果中成绩以等级制显示。（90分以上为'优秀'，80-90分为'良好'，70-80分为'中等'，60-70分为'及格'，60分以下为'不及格'）

SELECT S.sno, S.sname, 
    CASE 
        WHEN SC.grade >= 90 THEN '优秀'
        WHEN SC.grade >= 80 THEN '良好'
        WHEN SC.grade >= 70 THEN '中等'
        WHEN SC.grade >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM STUDENT S
INNER JOIN SC ON S.sno = SC.sno
INNER JOIN COURSE C ON SC.cno = C.cno
WHERE S.sdept = '计算机系' AND C.cname = '数据库'

12. 将计算机系学生的所有成绩都降低5分。（请在更新语句中使用子查询和FROM子句两种方式实现）

子查询

UPDATE SC
SET grade = grade - 5
WHERE sno IN (
    SELECT sno
    FROM STUDENT
    WHERE sdept = '计算机系'
)

FROM子句

UPDATE SC
SET grade = grade - 5
FROM SC
INNER JOIN STUDENT S ON SC.sno = S.sno
WHERE S.sdept = '计算机系'

13. 删除计算机系成绩不及格学生的选课记录。（请在删除语句中使用子查询和FROM子句两种方式实现）

子查询

DELETE FROM SC
WHERE sno IN (
    SELECT sno
    FROM STUDENT
    WHERE sdept = '计算机系'
) AND grade < 60

FROM子句

DELETE SC
FROM SC
INNER JOIN STUDENT S ON SC.sno = S.sno
WHERE S.sdept = '计算机系' AND SC.grade < 60

注意:

  • 以上代码中部分课程名称和课程号仅供示例，请根据实际情况进行修改。
  • 部分代码中使用了'计算机系'等字符串，请根据实际情况进行修改。
  • 可以根据需要进行拓展，实现更多功能。

更多练习:

  • 使用子查询查询选修所有课程的学生信息。
  • 使用子查询查询没有选修任何课程的学生信息。
  • 查询选修课程数量最多的学生的信息。
  • 查询平均成绩最高的课程的信息。
  • 查询每门课程的最高分和最低分。
T-SQL练习：学生信息管理系统操作

原文地址: https://www.cveoy.top/t/topic/ph76 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录