把自做的经典SQL练习题答案记录下来,方便日后复习。
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname, ssex, class FROM student;
2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart FROM teacher;
3、 查询Student表的所有记录。
SELECT * FROM student;
4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
5、 查询Score表中成绩为85,86或88的记录。
SELECT * FROM score WHERE degree IN (85, 86, 88);
6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student WHERE class = '95031' OR ssex = '女';
7、 以Class降序查询Student表的所有记录。
SELECT * FROM student ORDER BY class DESC;
8、 以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM score ORDER BY cno, degree DESC;
9、 查询“95031”班的学生人数。
SELECT COUNT(sno) FROM student WHERE class = '95031';
10、查询Score表中的最高分的学生学号和课程号。
SELECT sno, cno FROM score HAVING degree = MAX(degree);
错了
SELECT sno, cno FROM score WHERE degree = MAX(degree);
错了
SELECT FIRST(sno), FIRST(cno) FROM score ORDER BY degree DESC;
错了
SELECT FIRST(sno, cno) FROM score ORDER BY degree DESC;
错了...看答案
法1:
SELECT sno, cno FROM score WHERE degree = (SELECT MAX(degree) FROM score);
法2:
SELECT sno, cno FROM score ORDER BY degree DESC LIMIT 1;
11、查询‘3-105’号课程的平均分。
SELECT AVG(degree) FROM score WHERE cno = '3-105';
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(degree) FROM score
WHERE cno LIKE '3%' AND
COUNT(sno) >= 5
GROUP BY cno;
错了 看答案
SELECT AVG(degree),cno
FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(sno) >= 5;
*计数函数不能和WHERE一起用,要和HAVING一起
13、查询最低分大于70,最高分小于90的Sno列。
SELECT sno FROM score
WHERE MIN(degree) >70 AND MAX(degree) <90
GROUP BY sno;
错了
SELECT sno FROM score
GROUP BY sno
HAVING MIN(degree) >70 AND MAX(degree) <90;
对了
*找最大/小值的函数也要和HAVING一起
14、查询所有学生的Sname、Cno和Degree列。
SELECT a.sname, b.cno, b.degree
FROM student a JOIN score b
ON a.sno = b.sno;
15、查询所有学生的Sno、Cname和Degree列。
SELECT b.sno, a.cname, b.degree
FROM course a JOIN score b
ON a.cno = b.cno;
16、查询所有学生的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;
17、查询“95033”班所选课程的平均分。
SELECT AVG(b.degree)
FROM student a JOIN score b
ON a.sno = b.sno
WHERE a.class = '95033';
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM score
WHERE cno = '3-105' AND
degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT * FROM score
WHERE degree < (SELECT MAX(degree) FROM score)
GROUP BY sno
HAVING COUNT(sno) > 1
ORDER BY degree;
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM score
WHERE cno = '3-105' AND
degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105');
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT sno, sname, sbirthday FROM student
WHERE YEAR(sbirthday) = (SELECT YEAR(sbirthday) FROM student WHERE sno = '108');
23、查询“张旭“教师任课的学生成绩。
SELECT b.degree
FROM course a JOIN (score b, teacher c)
ON a.tno = c.tno AND a.cno = b.cno
WHERE c.tname = '张旭';
24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT c.tname
FROM course a JOIN (score b, teacher c)
ON a.cno = b.cno AND a.tno = c.tno
GROUP BY b.cno
HAVING COUNT(b.cno) > 5;
25、查询95033班和95031班全体学生的记录。
SELECT * FROM student WHERE class IN ('95033', '95031');
26、查询存在有85分以上成绩的课程Cno。
SELECT cno FROM score
GROUP BY cno
HAVING MAX(degree) > 85;
27、查询出“计算机系“教师所教课程的成绩表。
SELECT b.cno, b.degree
FROM course a JOIN (score b, teacher c)
ON a.cno = b.cno AND a.tno = c.tno
WHERE c.depart = '计算机系';
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT tname, prof FROM teacher
WHERE depart = '计算机系'
AND prof NOT IN (SELECT prof FROM teacher WHERE depart = '电子工程系');
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT * FROM score
WHERE cno = '3-105'
AND degree > (SELECT degree FROM score WHERE cno = '3-245')
ORDER BY degree DESC;
错了
SELECT * FROM score
WHERE cno = '3-105'
AND degree > (SELECT degree FROM score WHERE cno = '3-245')
ORDER BY degree DESC
GROUP BY sno;
错了 看答案
SELECT * FROM score
WHERE cno = '3-105' AND
degree > ANY(SELECT degree FROM score WHERE cno ='3-245')
ORDER BY degree DESC;
*ANY()函数
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree。
SELECT * FROM score
WHERE cno = '3-105' AND
degree > ALL(SELECT degree FROM score WHERE cno ='3-245')
ORDER BY degree DESC;
*ALL()函数
31、查询所有教师和同学的name、sex和birthday。
SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacher
UNION
SELECT sname AS name, ssex AS sex, sbirthday AS birthday FROM student;
32、查询所有“女”教师和“女”同学的name、sex和birthday。
SELECT tname AS name, tsex AS sex, tbirthday AS birthday FROM teacher
WHERE tsex = '女'
UNION
SELECT sname AS name, ssex AS sex, sbirthday AS birthday FROM student
WHERE ssex = '女';
33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM score
GROUP BY cno
HAVING degree < AVG(degree);
错了 看答案
SELECT a.* FROM score a
WHERE degree < (SELECT AVG(degree) FROM score b WHERE a.cno = b.cno);
34、查询所有任课教师的Tname和Depart。
SELECT b.tname, b.depart
FROM course a JOIN teacher b
ON a.tno = b.tno;
35 、查询所有未讲课的教师的Tname和Depart。
SELECT tname, depart FROM teacher
WHERE tname NOT IN (SELECT b.tname FROM course a JOIN teacher b ON a.tno = b.tno);
36、查询至少有2名男生的班号。
SELECT class FROM student
GROUP BY class
HAVING COUNT(ssex) >= 2;
37、查询Student表中不姓“王”的同学记录。
SELECT * FROM student
WHERE sname NOT LIKE '王%';
38、查询Student表中每个学生的姓名和年龄。
SELECT sname, (YEAR(NOW())-YEAR(sbirthday)) AS age FROM student;
39、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(sbirthday), MIN(sbirthday) FROM student;
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM student ORDER BY class DESC, sbirthday;
41、查询“男”教师及其所上的课程。
SELECT b.tname, a.cname
FROM course a JOIN teacher b
ON a.tno = b.tno
WHERE b.tsex = '男';
42、查询最高分同学的Sno、Cno和Degree列。
SELECT * FROM score ORDER BY degree DESC LIMIT 1;
43、查询和“李军”同性别的所有同学的Sname。
SELECT sname FROM student
WHERE ssex = (SELECT ssex FROM student WHERE sname = '李军');
44、查询和“李军”同性别并同班的同学Sname。
SELECT sname FROM student
WHERE ssex = (SELECT ssex FROM student WHERE sname = '李军')
AND class = (SELECT class FROM student WHERE sname = '李军');
45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT c.*
FROM student a JOIN (course b, score c)
ON a.sno = c.sno AND b.cno = c.cno
WHERE b.cname = '计算机导论' AND a.ssex = '男';