T-SQL练习题：数据库操作与查询语句实战

T-SQL 练习题：数据库操作与查询语句实战

本练习题使用 STUDENTSCORE 数据库，包含以下三个表：

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

请使用 T-SQL 语句完成以下操作：

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

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

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

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

SELECT *
FROM #JSJSTUDENT

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

-- 显式内连接
SELECT s.sno, s.sname, s.ssex, c.cname, sc.grade
FROM STUDENT s
JOIN SC sc ON s.sno = sc.sno
JOIN COURSE c ON sc.cno = c.cno
WHERE s.sdept = '计算机系'

-- 派生表查询
SELECT s.sno, s.sname, s.ssex, c.cname, sc.grade
FROM (SELECT * FROM STUDENT WHERE sdept = '计算机系') s
JOIN SC sc ON s.sno = sc.sno
JOIN COURSE c ON sc.cno = c.cno

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

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

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

-- 外连接方式
SELECT c.cno, c.cname, c.ccredit
FROM COURSE c
LEFT JOIN SC sc ON c.cno = sc.cno
WHERE sc.cno IS NULL

-- 嵌套查询方式
SELECT c.cno, c.cname, c.ccredit
FROM COURSE c
WHERE c.cno NOT IN (
    SELECT sc.cno
    FROM SC sc
)

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

SELECT s.sno, s.sname, s.sdept, COUNT(sc.cno) AS 选课门数, AVG(sc.grade) AS 课程平均分
FROM STUDENT s
JOIN SC sc ON s.sno = sc.sno
GROUP BY s.sno, s.sname, s.sdept
ORDER BY 选课门数 DESC
OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY

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

SELECT c.cno, c.cname, c.ccredit, COUNT(sc.sno) AS 选课人数, AVG(sc.grade) AS 平均分
FROM COURSE c
LEFT JOIN SC sc ON c.cno = sc.cno
GROUP BY c.cno, c.cname, c.ccredit

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

-- 使用 WITH ROLLUP
SELECT sc.cno, s.ssex, COUNT(sc.sno) AS 选课人数, AVG(sc.grade) AS 平均分
FROM SC sc
JOIN STUDENT s ON sc.sno = s.sno
GROUP BY sc.cno, s.ssex WITH ROLLUP

-- 使用 WITH CUBE
SELECT sc.cno, s.ssex, COUNT(sc.sno) AS 选课人数, AVG(sc.grade) AS 平均分
FROM SC sc
JOIN STUDENT s ON sc.sno = s.sno
GROUP BY sc.cno, s.ssex WITH CUBE

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

SELECT COUNT(*) AS 总人数, AVG(sage) AS 平均年龄
FROM STUDENT

-- 使用 COMPUTE 子句
SELECT sage
FROM STUDENT
COMPUTE COUNT(sage), AVG(sage)

-- 使用批处理
SELECT COUNT(*) AS 总人数, AVG(sage) AS 平均年龄
FROM STUDENT

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

SELECT s.sno, s.sname, sc.cno, sc.grade
FROM STUDENT s
JOIN SC sc ON s.sno = sc.sno
ORDER BY s.sno

-- 使用 COMPUTE BY 子句
SELECT s.sno, s.sname, sc.cno, sc.grade
FROM STUDENT s
JOIN SC sc ON s.sno = sc.sno
ORDER BY s.sno
COMPUTE COUNT(sc.cno), AVG(sc.grade) BY s.sno

-- 使用批处理
SELECT s.sno, s.sname, sc.cno, sc.grade
FROM STUDENT s
JOIN SC sc ON s.sno = sc.sno
ORDER BY s.sno

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

将计算机系学生的所有成绩都降低 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
JOIN STUDENT s ON SC.sno = s.sno
WHERE s.sdept = '计算机系'

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

-- 使用子查询
DELETE FROM SC
WHERE sno IN (
    SELECT sno
    FROM STUDENT
    WHERE sdept = '计算机系'
) AND grade < 60

-- 使用 FROM 子句
DELETE SC
FROM SC
JOIN STUDENT s ON SC.sno = s.sno
WHERE s.sdept = '计算机系'
    AND SC.grade < 60

注意： 以上代码示例仅供参考，实际执行前请根据您的数据库环境和需求进行调整。