三张数据表分别是：student,course,SC（即学生表，课程表，选课表）

表结构如下：

CREATE DATABASE SQL查询语句27道
GO
USE SQL查询语句27道
--创建学生表
CREATE TABLE student
(
Sno VARCHAR(20) PRIMARY KEY NOT NULL,
Sname VARCHAR(50) NOT NULL,
Ssex VARCHAR(10) NOT NULL,
Sage INT NOT NULL,
Sdept VARCHAR(50) NOT NULL
)
-- 创建课程表
CREATE TABLE course
(
Cno VARCHAR(20) NOT NULL PRIMARY KEY,
Cname VARCHAR(50) NOT NULL,
Hours INT NOT NULL
)
GO
-- 创建选课表
CREATE TABLE SC
(
Sno VARCHAR(20) FOREIGN KEY (Sno) REFERENCES student (Sno),
Cno VARCHAR(20) FOREIGN KEY (Cno) REFERENCES course (Cno),
Grade INT NULL
)
GO
USE [SQL查询语句27道]
GO
INSERT INTO student
(Sno, Sname, Ssex, Sage, Sdept)
VALUES (9512101, '李勇', '男', 19, '计算机系'),
(9512102, '刘晨', '男', 20, '计算机系'),
(9512103, '王敏', '女', 20, '计算机系'),
(9521101, '张立', '男', 22, '信息系'),
(9521102, '吴宾', '女', 21, '信息系'),
(9521103, '张海', '男', 20, '信息系'),
(9531101, '钱小力', '女', 18, '数学系'),
(9531102, '王大力', '男', 19, '数学系')
GO
INSERT INTO course
(Cno, Cname, Hours)
VALUES ('C01', '计算机文化学', 70),
('C02', 'VB', 90),
('C03', '计算机网络', 80),
('C04', '数据库基础', 108),
('C05', '高等数学', 180),
('C06', '数据结构', 72)
GO
INSERT INTO SC
(Sno, Cno, Grade)
VALUES (9512101, 'C01', 90),
(9512101, 'C02', 86),
(9512101, 'C06', NULL),
(9512102, 'C02', 78),
(9512102, 'C04', 66),
(9521102, 'C01', 82),
(9521102, 'C02', 75),
(9521102, 'C04', 92),
(9521102, 'C05', 50),
(9521103, 'C02', 68),
(9521103, 'C06', NULL),
(9531101, 'C01', 80),
(9531101, 'C05', 95),
(9531102, 'C05', 85)

题：

统计每门课程的修课人数和考试最高分

算法1：

with t1(cno, 最大值, count_person) as (
select cno, max(grade) '最大值', count(*) as count_person
from sc
group by cno
)
select cname '课程', t1.count_person '选修人数', t1.最大值
from course
join t1
on course.cno = t1.cno

算法二

SELECT Cname 课程名, COUNT(*) 修课人数, MAX(Grade) 考试最高分
FROM SC,
course
WHERE course.Cno = SC.Cno
GROUP BY course.Cname
-- 请注意该表用于分组的字段！，当使用cno进行分组时，无法通过

统计每个学生的选课门数,并按选课门数的递增顺序显示结果

算法一

SELECT sname, count(*) AS '选课数'
FROM sc
JOIN student
ON sc.sno = student.sno
GROUP BY sname
ORDER BY count(*) asc
-- 这一个算法和上一题的第二个算法相同

算法二

SELECT student.Sname 学生姓名, student.Sno 学生学号, COUNT(SC.Sno) 选课门数
FROM student
inner join SC ON SC.Sno = student.Sno
GROUP BY student.Sname, student.Sno
ORDER BY COUNT(SC.Sno) ASC

查询选课门数超过2门的学生的平均成绩和选课门数

算法一

SELECT sname, Count(*) '选课数目', AVG(Grade) '平均成绩'
FROM SC
JOIN student on sc.sno = student.sno
GROUP BY sname
HAVING COUNT(*) > 2

算法二：

SELECT Student.Sname,AVG(Sc.Grade) 平均成绩,COUNT(SC.Sno) 选课门数
FROM SC
--内联接join或inner join,内联系是比较运算符，只返回符合条件的行
JOIN Student ON (SC.Sno = Student.Sno)
JOIN Course ON (SC.Cno = Course.Cno)
GROUP BY Student.Sname
HAVING COUNT(distinct Course.Cno) >2--分组条件

查询选修了c02号课程的学生的姓名和课程名。

这个要使用SC表作为中介，用来连接student和coures

SELECT Sname,Cname,SC.Cno
FROM student s
JOIN SC ON s.Sno=SC.Sno
JOIN course c ON SC.Cno=c.Cno
WHERE SC.Cno='C02'

查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。

SELECT Sname, Cno, Grade
FROM student s
INNER JOIN SC ON s.Sno = SC.Sno
WHERE Grade >= 80
ORDER BY Grade

查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

这个算法用到了表的自联结

SELECT a.Sname, a.Sage
FROM student a
join student b
ON a.Sage = b.Sage
ORDER BY a.Sage

查询哪些课程没有人选,要求列出课程号和课程名。

SELECT Cno,
Cname
FROM course
WHERE Cno NOT IN (
SELECT DISTINCT Cno
FROM SC)

8. 查询有考试成绩的所有学生的姓名、修课名称及考试成绩

--要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。

SELECT s.Sname,
c.Cname,
SC.Grade
INTO new_sc
FROM student s
JOIN SC ON s.Sno = SC.Sno
JOIN course c ON SC.Cno = c.Cno
WHERE SC.Grade IS NOT NULL

分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
--并要求将这两个查询结果合并成一个结果集,
--并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

SELECT Sname, Ssex, Cname, Grade
FROM student s
JOIN SC ON s.Sno = SC.Sno
JOIN course c ON SC.Cno = c.Cno
WHERE Sdept IN ('计算机系', '信息系')
ORDER BY Sdept, Sname, Ssex, Grade

9.用子查询查询选修了C01号课程的学生的姓名和所在系。

SELECT Sname, Sdept
FROM student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno = 'C01')

10. 查询高等数学成绩80分以上的学生的学号、姓名。

SELECT s.Sno, Sname
FROM student s
JOIN SC ON s.Sno = SC.Sno
WHERE Cno IN
(SELECT Cno
FROM course
WHERE Cname = '高等数学'
)
AND Grade >= 80

WHERE IN 语句可以使用表连接进行替代。

这个查询可以不建立表连接，而替换为一个嵌套的子查询

具体为：先从couser中查询到高等数学的课程号，在利用其课程号在SC表中查询到选修高等数学且成绩80以上学生的学号，之后使用查询到的学号对student表进行过滤

11.将所有选修VB课程的学生的成绩加10分

UPDATE SC
SET Grade+=10
WHERE Cno IN (
SELECT Cno
FROM course
WHERE Cname = 'VB')

版权声明：

本文档大量参考了SQL查询练习27道中的内容。但查询算法系本人所作，如有雷同，纯属巧合。