T-SQL语句练习：学生信息管理

本页面提供T-SQL语句练习，涵盖学生信息管理的各种操作，包括创建新表、插入数据、查询数据、统计分析、更新数据、删除数据等。每个练习都包含详细的步骤和代码示例，并提供多种解决方案，帮助您深入理解T-SQL语言的使用方法。

数据库结构

STUDENT表
- 学号 (sno)
- 姓名 (sname)
- 性别 (ssex)
- 年龄 (sage)
- 所在系 (sdept)
COURSE表
- 课程号 (cno)
- 课程名 (cname)
- 学分 (ccredit)
SC表
- 学号 (sno)
- 课程号 (cno)
- 成绩 (grade)

练习题目

在STUDENTSCORE数据库下，将STUDENT表中计算机系的学生信息放入一张名为JSJSTUDENT的新表中，新表中的属性包含（学号，姓名，性别，年龄）。提示：请使用INTO子句实现该操作。

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

查询JSJSTUDENT表，将年龄最大的3位学生的信息放入一张本地临时表中（临时表名可与基本表名相同）。查询临时表，查看年龄最大的3位学生信息。

SELECT TOP 3 sno, sname, ssex, sage INTO #JSJSTUDENT
FROM STUDENTSCORE.dbo.JSJSTUDENT
ORDER BY sage DESC

SELECT * FROM #JSJSTUDENT

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

显式内连接

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

派生表查询

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

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

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

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

外连接

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

嵌套查询

SELECT C.cno, C.cname, C.ccredit
FROM STUDENTSCORE.dbo.COURSE C
WHERE C.cno NOT IN (
    SELECT DISTINCT cno
    FROM STUDENTSCORE.dbo.SC
)

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

SELECT S.sno, S.sname, S.sdept, COUNT(SC.cno) AS 选课门数, AVG(SC.grade) AS 课程平均分
FROM STUDENTSCORE.dbo.STUDENT S
JOIN STUDENTSCORE.dbo.SC SC ON S.sno = SC.sno
GROUP BY S.sno, S.sname, S.sdept
HAVING COUNT(SC.cno) >= 3
ORDER BY COUNT(SC.cno) DESC

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

SELECT C.cno, C.cname, C.ccredit, COUNT(SC.sno) AS 选课人数, AVG(SC.grade) AS 平均分
FROM STUDENTSCORE.dbo.COURSE C
JOIN STUDENTSCORE.dbo.SC SC ON C.cno = SC.cno
WHERE C.cno <> 2
GROUP BY C.cno, C.cname, C.ccredit
WITH ROLLUP

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

SELECT C.cno, S.ssex, COUNT(SC.sno) AS 选课人数, AVG(SC.grade) AS 平均分
FROM STUDENTSCORE.dbo.COURSE C
JOIN STUDENTSCORE.dbo.SC SC ON C.cno = SC.cno
JOIN STUDENTSCORE.dbo.STUDENT S ON SC.sno = S.sno
GROUP BY CUBE (C.cno, S.ssex)

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

COMPUTE子句

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

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

COMPUTE BY子句

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

查询选修了 '数据库' 课程的计算机系学生信息，显示学生学号，姓名和成绩。要求最后结果中成绩以等级制显示。（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 成绩
FROM STUDENTSCORE.dbo.STUDENT S
JOIN STUDENTSCORE.dbo.SC SC ON S.sno = SC.sno
JOIN STUDENTSCORE.dbo.COURSE C ON SC.cno = C.cno
WHERE C.cname = '数据库' AND S.sdept = '计算机系'

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

子查询

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

FROM子句

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

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

子查询

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

FROM子句

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

总结

以上练习涵盖了T-SQL语言中常见的操作，希望能够帮助您更好地理解T-SQL的使用方法。您可以根据自己的需求进行修改和扩展，并尝试编写更复杂的SQL语句。