这个练习题感觉挺经典的,题目有很多道,我选择了我认为有练习价值的,特别简单的没有收录进来。
创建表的代码
需要自己先创建一个数据库。然后再创建表。
CREATE TABLE STUDENT
(
SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5)
);
CREATE TABLE COURSE
(
CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL
);
CREATE TABLE SCORE
(
SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL
);
CREATE TABLE TEACHER
(
TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL,
TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL,
PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL
);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (108, '曾华'
, '男', '1977-09-01', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (105, '匡明'
, '男', '1975-10-02', 95031);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (107, '王丽'
, '女', '1976-01-23', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (101, '李军'
, '男', '1976-02-20', 95033);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (109, '王芳'
, '女', '1975-02-10', 95031);
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS) VALUES (103, '陆君'
, '男', '1974-06-03', 95031);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('3-105', '计算机导论', 825);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('3-245', '操作系统', 804);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('6-166', '数据电路', 856);
INSERT INTO COURSE (CNO, CNAME, TNO) VALUES ('9-888', '高等数学', 100);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (103, '3-245', 86);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (105, '3-245', 75);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (109, '3-245', 68);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (103, '3-105', 92);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (105, '3-105', 88);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (109, '3-105', 76);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (101, '3-105', 64);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (107, '3-105', 91);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (101, '6-166', 85);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (107, '6-106', 79);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (108, '3-105', 78);
INSERT INTO SCORE (SNO, CNO, DEGREE) VALUES (108, '6-166', 81);
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (804, '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (856, '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (825, '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES (831, '刘冰', '女', '1977-08-14', '助教', '电子工程系');
练习题
查询Score表中成绩在60到80之间的所有记录
SELECT *
FROM score
WHERE dergee > 60 and degree < 80;
或
SELECT *
FROM score
WHERE dergee BETWEEN 60 AND 80;
查询 score 表中成绩为85,86或88的记录
SELECT *
FROM score
WHERE degree = 85 or degree = 86 or degree = 88;
或
SELECT *
FROM score
WHERE degree IN (85, 86, 88);
以 cno 升序、degree降序查询 score 表的所有记录
SELECT *
FROM score
ORDER BY cno ASC, degree DESC;
注意 ORDER BY
子句中的顺序,交换顺序之后是不一样的。首先根据第一个字段排序,如果第一个字段相同,再根据第二个字段排序。
查询“95031”班的学生人数。
SELECT COUNT(*)
FROM STUDENT
WHERE class = '95031';
MySQL 中 COUNT()
函数的使用。
查询Score表中的最高分的学生学号和课程号
SELECT sno, cno
FROM score
ORDER BY degree DESC
LIMIT 1 OFFSET 0; -- 可以简写为 LIMIT 1;
下面的写法也可以,但是效率不高。
SELECT sno, cno
FROM score
WHERE degree = (
SELECT max(degree)
FROM score
);
查询‘3-105’号课程的平均分。
SELECT AVG(degree)
FROM score
WHERE cno = '3-105';
MySQL 的 AVG
函数的使用。
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT cno, AVG(degree)
FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(*) > 5;
查询最低分大于70,最高分小于90的Sno列。
本题乍一看不太好理解,可以翻译为:查询所有课程成绩最低分大于70分,最高分小于90分的学生的学号。
题意暗含了要根据 sno
分组的意思。
SELECT sno
FROM score
GROUP BY sno
HAVING min(degree) > 70 AND max(degree) < 90;
查询所有学生的Sname、Cno和Degree列。
SELECT sname, cno, degree
FROM student, score
WHERE student.sno = score.sno;
查询所有学生的Sno、Cname和Degree列。
SELECT score.sno, cno, degree
FROM student, score
WHERE student.sno = score.sno;
查询所有学生的Sname、Cname和Degree列。
SELECT a.sname, b.cname, c.degree
FROM student a
JOIN (course b, score c)
ON a.sno = c.sno AND b.cno = c.cno;
查询“95033”班所选课程的平均分。
SELECT AVG(degree)
FROM score
WHERE sno IN
(SELECT sno
FROM student
WHERE class = '95033');
查询所有同学的Sno、Cno和rank列。
首先,假设使用如下命令建立了一个grade表:
CREATE TABLE grade (
low NUMERIC (3, 0),
upp NUMERIC (3),
rank CHAR (1)
);
INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');
答案:
SELECT a.sno, a.cno, b.rank
FROM score a, grade b
WHERE a.degree BETWEEN b.low AND b.upp
ORDER BY rank;
或
SELECT a.Sno, a.Cno, b.rank
FROM score a
JOIN grade b ON a.degree >= b.low AND a.degree <= b.upp
ORDER BY rank;
其中使用 BETWEEN ... AND
来进行表的连接。
查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT *
FROM score
WHERE cno = '3-105'
AND degree >
(SELECT degree
FROM score
WHERE sno = '109' AND cno = '3-105');
查询score中选学一门以上课程的同学中分数为非最高分成绩的记录
这道题目有歧义,请参考本文末尾,我写了一大段用来解释这道题。
查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno, sname, sbirthday
FROM student
WHERE YEAR(sbirthday) =
(SELECT YEAR(sbirthday)
FROM student
WHERE sno = '108');
涉及到函数 YEAR()
的使用。
查询“张旭“教师任课的学生成绩。
SELECT *
FROM score
WHERE cno =
(SELECT cno
FROM course
JOIN teacher
ON course.tno = teacher.tno
AND tname = '张旭');
查询选修某课程的同学人数多于5人的教师姓名。
SELECT tname
FROM teacher
WHERE tno IN
(SELECT tno
FROM course
WHERE cno IN
(SELECT cno
FROM score
GROUP BY cno
HAVING count(sno) > 5));
-- 或
SELECT tname
FROM teacher
WHERE tno IN
(SELECT c.tno
FROM course c,score s
WHERE c.cno = s.cno
GROUP BY c.tno
HAVING COUNT(c.tno) > 5);
查询95033班和95031班全体学生的记录。
SELECT *
FROM student
WHERE CLASS IN ('95033', '95031');
查询存在有85分以上成绩的课程Cno。
SELECT cno
FROM score
GROUP BY cno
HAVING MAX(degree) > 85;
查询出“计算机系“教师所教课程的成绩表。
SELECT *
FROM score
WHERE cno IN
(SELECT cno
FROM course, teacher
WHERE depart = '计算机系'
AND course.tno = teacher.tno);
查询所有教师和同学的name、sex和birthday。
SELECT tname name, tsex sex, tbirthday birthday
FROM teacher
UNION
SELECT sname name, ssex sex, sbirthday birthday
FROM student;
其中使用到了集合运算 UNION
。
查询所有“女”教师和“女”同学的name、sex和birthday。
SELECT tname name, tsex sex, tbirthday birthday
FROM teacher
WHERE tsex = '女'
UNION
SELECT sname name, ssex sex, sbirthday birthday
FROM student
WHERE ssex = '女';
查询成绩比该课程平均成绩低的同学的成绩表。
SELECT a.*
FROM score a
WHERE degree <
(SELECT AVG(degree)
FROM score b
WHERE b.cno = a.cno);
查询所有任课教师的Tname和Depart。
SELECT tname, depart
FROM teacher a
WHERE tno IN
(SELECT DISTINCT tno
FROM course);
查询至少有2名男生的班号。
SELECT CLASS
FROM student
WHERE ssex = '男'
GROUP BY CLASS
HAVING count(ssex) > 1;
查询Student表中不姓“王”的同学记录。
SELECT *
FROM student
WHERE sname NOT LIKE "王%";
查询Student表中每个学生的姓名和年龄。
SELECT sname, YEAR(NOW()) - YEAR(sbirthday)
FROM student;
使用到了 NOW()
函数。
对查询score中选学一门以上课程的同学中分数为非最高分成绩的记录的辨析
假设有下面这样一张表,表名为 score
。
sno | cno | degree |
---|---|---|
103 | 3-245 | 86 |
105 | 3-245 | 75 |
109 | 3-245 | 68 |
103 | 3-105 | 92 |
105 | 3-105 | 88 |
109 | 3-105 | 76 |
101 | 3-105 | 64 |
107 | 3-105 | 91 |
101 | 6-166 | 85 |
108 | 3-105 | 79 |
108 | 6-166 | 81 |
考虑下面这个 SQL 语句
SELECT *
FROM score
GROUP BY sno;
你觉得这句 SQL 语句会返回什么?或许我们是希望返回按照 sno 分组之后的每个组中的所有记录。
实际上,这个语句只会返回每个分组中排名最靠前的一条记录(有可能是按照原表中数据的顺序排列的)。
假设我们想要查询 score 中选学一门以上课程的同学中分数为非最高分成绩的记录。
我们可以能会写出这样的 SQL 语句:
SELECT *
FROM score
GROUP BY sno
HAVING COUNT(cno) > 1 AND degree != MAX(degree);
HAVING degree != MAX(degree)
会对每个分组中的每一条记录进行判断吗?
实际上并不会,只会对每个分组中排名最靠前的一条记录进行判断。所以这个 SQL 并不会正确地返回期望的答案。其实 SQL 标准是不允许有这种存在含混不清的语句存在的,可以参考关于 MySQL 的 ONLY_FULL_GROUP_BY 的相关内容。
因为本题的题目的表述有歧义,可能有两种解读:
其一种是查询 score 中选学一门以上课程的同学中,分数不是其自己所有课程成绩的最高分的记录。
另一种是查询 score 中选学一门以上课程的同学中,其课程分数不是该课程所有学生成绩最高分的记录。
针对第一种理解方式的答案:
SELECT a.*
FROM score a
JOIN (SELECT sno, MAX(degree) AS max_degree
FROM score
GROUP BY sno) b
ON a.sno = b.sno AND a.degree != b.max_degree;
这个写法没有显式地体现出“选学一门以上课程”这个条件,是因为如果一个人只选了一门课程,那么这门课程的成绩在子查询中一定是 max_degree
,所以在外层查询一定会被过滤掉。
针对第二种理解方式的答案:
SELECT a.*
FROM score a
JOIN (SELECT cno, MAX(degree) AS max_degree
FROM score
GROUP BY cno) b
ON a.cno = b.cno AND a.degree != b.max_degree;
针对于第一种理解方式,这还有一个错误的答案,供参考,用来防止踩坑:
-- 这个不行,因为一个学生的不是最大成绩的记录,
-- 有可能和别的学生的最大成绩相同,所以不能使用 NOT IN
SELECT *
FROM score
GROUP BY sno, cno
HAVING degree
NOT IN (SELECT MAX(degree)
FROM score
GROUP BY sno);
题目来源
经过一番搜索,发现这套题出自《SQL Server 2000应用系统开发教程》。
豆瓣链接:SQL Server 2000应用系统开发教程 - 2005版
上边那个是2005版的,没有图书封面图片。还有一个2008版的链接,有图书封面图片:SQL Server 2000应用系统开发教程 - 2008版
Google Books 可以查看这本书的电子版的部分章节:SQL Server 2000应用系统开发教程 - Google Books
如何找到的(用了什么搜索技巧)
使用谷歌搜索 曾华 匡明 王丽 教材
,就直接找到 Google Books 提供的信息了。