SQL练习题参考答案

原题见：https://blog.csdn.net/jiahe1224/article/details/80261262

# MySQL
# 1
SELECT sname, ssex, sclass
FROM student;

# 2
SELECT DISTINCT depart
FROM teacher;

# 3
SELECT *
FROM student;

# 4
SELECT *
FROM score
WHERE degree BETWEEN 60 AND 80;

# 5
SELECT *
FROM score
WHERE degree in (85,86,88);

# 6
SELECT *
FROM student
WHERE sclass='95031' or ssex='女';

# 7
SELECT *
FROM student
ORDER BY sclass DESC;

# 8
SELECT *
FROM score
ORDER BY cno, degree DESC;

# 9
SELECT COUNT(*)
FROM student
WHERE sclass = '95031';

# 10
SELECT sno, cno, MAX(degree)
FROM score;

# 11
SELECT AVG(degree)
FROM score
WHERE cno = '3-105';

# 12
SELECT AVG(degree)
FROM score
WHERE cno in 
(SELECT cno
FROM score
WHERE cno LIKE '3%'
GROUP BY cno
HAVING COUNT(*) >= 5);

# 13
SELECT DISTINCT sno
FROM score
WHERE degree BETWEEN 70 AND 90;

# 14
SELECT student.sname, score.*
FROM score, student
WHERE score.sno = student.sno;

# 15
SELECT score.*, course.cname
FROM course, score
WHERE course.cno = score.cno;

# 16
SELECT student.sname, course.cname, score.degree
FROM student, course, score
WHERE student.sno = score.sno AND score.cno = course.cno;

# 17
SELECT AVG(score.degree)
FROM score, student
WHERE score.sno=student.sno AND sclass = '95033';

# 18
create table grade(low int(3),upp int(3),ranking varchar(1));

START TRANSACTION;
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');
COMMIT;

SELECT score.*, grade.ranking
FROM score, grade
WHERE score.degree >= grade.low AND score.degree <= grade.upp;

# 19.此题题意不好理解，给几个方案吧
# 19.1
SELECT student.*
FROM student
WHERE sno in 
(
SELECT sno
FROM score
WHERE degree > 
(select degree from score
where cno='3-105' and sno='109')
);

# 19.2
SELECT *
FROM score
WHERE degree > 
(select degree from score
where cno='3-105' and sno='109');

# 19.3
SELECT *
FROM score
WHERE cno='3-105' and degree > 
(select degree from score
where cno='3-105' and sno='109');

# 20
SELECT *
FROM score
WHERE degree NOT IN 
(SELECT MAX(degree) FROM score)
AND sno IN 
(SELECT sno FROM score GROUP BY sno HAVING COUNT(*) > 1);

# 21
SELECT *
FROM score
WHERE degree > 
(SELECT degree 
FROM score 
WHERE sno='109' AND cno='3-105');

# 22
SELECT *
FROM student
WHERE extract(year from sbirthday) =
(SELECT extract(year from sbirthday)
FROM student
WHERE sno='108');

# 23
SELECT *
FROM score
WHERE cno = 
(SELECT cno
FROM course
WHERE tno = (
SELECT tno
FROM teacher
WHERE tname='张旭'));

# 24
SELECT tname
FROM teacher
WHERE tno in
(SELECT tno
FROM course
WHERE cno in
(SELECT cno
FROM score
GROUP BY cno
HAVING COUNT(*) > 5));

# 25
SELECT *
FROM student, score
WHERE student.sno=score.sno
AND student.sclass in ('95033','95031');

# 26
SELECT cno
FROM score
GROUP BY cno
HAVING MAX(degree) > 85;

SELECT DISTINCT Cno
FROM score
WHERE Degree > 85;

# 27
SELECT score.*, student.sname, student.ssex, student.sclass, course.cname
FROM student, score, course, teacher
WHERE student.sno = score.sno
AND score.cno = course.cno
AND course.tno = teacher.tno
AND teacher.depart = '计算机系';

# 28,结果中不能有两系都有的助教
SELECT Tname,prof
FROM teacher
WHERE depart='计算机系' OR depart='电子工程系' 
GROUP BY prof
HAVING COUNT(*)=1;

# 29
SELECT *
FROM score
WHERE cno = '3-105' 
AND degree > 
ALL(SELECT degree FROM score WHERE cno='3-245');

# 30
SELECT sname as name,ssex as sex, sbirthday as birthday
FROM student
UNION ALL
SELECT tname as name,tsex as sex, tbirthday as birthday
FROM teacher;

# 31
SELECT sname as name,ssex as sex, sbirthday as birthday
FROM student
WHERE ssex='女'
UNION ALL
SELECT tname as name,tsex as sex, tbirthday as birthday
FROM teacher
WHERE tsex='女';

# 32
SELECT a.*
FROM score a
JOIN 
(SELECT cno, AVG(degree) as avg_deg
FROM score
GROUP BY cno) b
WHERE a.cno = b.cno
AND a.degree < b.avg_deg;

# 33
SELECT teacher.tname, teacher.depart
FROM teacher
WHERE tno in 
(SELECT tno
FROM course
WHERE cno in (SELECT DISTINCT cno FROM score));

# 34
SELECT teacher.tname, teacher.depart
FROM teacher
WHERE tno in 
(SELECT tno
FROM course
WHERE cno NOT IN (SELECT DISTINCT cno FROM score));

# 35
SELECT sclass
FROM student
WHERE ssex='男'
GROUP BY sclass
HAVING COUNT(*) >= 2;

# 36
SELECT *
FROM student
WHERE sname NOT LIKE '王%';

# 37
SELECT sname, datediff(current_date, sbirthday)/365 AS age
FROM student;

# 38
SELECT MAX(sbirthday),MIN(sbirthday)
FROM student;

# 39
SELECT *
FROM student
ORDER BY sclass DESC,sbirthday;

# 40
SELECT tname, cname
FROM course,teacher
WHERE course.tno = teacher.tno
AND tsex = '男';

# 41
SELECT *
FROM score
WHERE degree = 
(SELECT MAX(degree) FROM score);

# 42
SELECT *
FROM student
WHERE ssex=
(SELECT ssex FROM student WHERE sname='李军');

# 43
SELECT *
FROM student
WHERE ssex =
(SELECT ssex FROM student WHERE sname='李军')
AND sclass =
(SELECT sclass FROM student WHERE sname='李军');

# 44
SELECT *
FROM score
WHERE cno in
(SELECT cno FROM course WHERE cname = '计算机导论')
AND sno in
(SELECT sno FROM student WHERE ssex = '男');