T-SQL数据库操作练习题：从学生信息到课程管理

使用INTO子句将计算机系学生信息插入新表JSJSTUDENT：

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

查询JSJSTUDENT表，将年龄最大的3位学生信息放入临时表：

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

SELECT *
FROM #JSJSTUDENT

使用显式内连接查询计算机系学生选修课程信息：

SELECT S.sno, S.sname, S.ssex, C.cname, SC.grade
FROM STUDENT AS S
JOIN SC ON S.sno = SC.sno
JOIN COURSE AS 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 = '计算机系'
) AS S
JOIN SC ON S.sno = SC.sno
JOIN COURSE AS C ON SC.cno = C.cno

使用外连接查询没有选修课程的学生学号、姓名和所在系：

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

使用外连接查询没有人选修的课程号、课程名和学分：

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

使用嵌套查询查询没有人选修的课程号、课程名和学分：

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

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

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

统计除2号课程外的其他课程的选课人数和平均分，显示课程号、课程名、学分、选课人数及平均分，包含2号课程的信息：

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

UNION

SELECT C.cno, C.cname, C.ccredit, COUNT(SC.sno) AS '选课人数', AVG(SC.grade) AS '平均分'
FROM COURSE AS C
JOIN SC ON C.cno = SC.cno
WHERE C.cno = '2号课程'
GROUP BY C.cno, C.cname, C.ccredit

查询选修每门课程的男女生人数和平均分，显示课程号、性别、选课人数和平均分，使用WITH ROLLUP和WITH CUBE进行比较：

使用WITH ROLLUP：

SELECT C.cno, S.ssex, COUNT(SC.sno) AS '选课人数', AVG(SC.grade) AS '平均分'
FROM COURSE AS C
JOIN SC ON C.cno = SC.cno
JOIN STUDENT AS 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 '选课人数', AVG(SC.grade) AS '平均分'
FROM COURSE AS C
JOIN SC ON C.cno = SC.cno
JOIN STUDENT AS S ON SC.sno = S.sno
GROUP BY C.cno, S.ssex WITH CUBE

查询所有学生的信息，结果统计总人数和平均年龄，使用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), AVG(sage)
FROM STUDENT

查询每个学生选修课程的信息，统计每个学生选修的课程数和平均分，使用COMPUTE BY子句或使用批处理：

使用COMPUTE BY子句：

SELECT S.sno, S.sname, SC.cno, SC.grade
FROM STUDENT AS S
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 AS S
JOIN SC ON S.sno = SC.sno
ORDER BY S.sno

SELECT S.sno, S.sname, COUNT(SC.cno), AVG(SC.grade)
FROM STUDENT AS S
JOIN SC ON S.sno = SC.sno
GROUP 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 STUDENT AS S
JOIN SC ON S.sno = SC.sno
JOIN COURSE AS C ON SC.cno = C.cno
WHERE C.cname = '数据库' AND S.sdept = '计算机系'

将计算机系学生的所有成绩都降低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 AS 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 AS S ON SC.sno = S.sno
WHERE S.sdept = '计算机系' AND SC.grade < 60

student表，学号sno，姓名sname，性别ssex，年龄sage，所在系sdept course表，课程号cno，课程名cname，学分ccredit sc表，学号sno，课程号cno，成绩grade