Python/ MySQL练习题（一）
查询“生物”课程比“物理”课程成绩高的所有学生的学号
1 SELECT
2 *
3 FROM
4 (
5 SELECT
6 *
7 FROM
8 course
9 LEFT JOIN score ON score.course_id = course.cid
10 WHERE
11 course.cname = '生物'
12 ) AS A
13 INNER JOIN (
14 SELECT
15 *
16 FROM
17 course
18 LEFT JOIN score ON score.course_id = course.cid
19 WHERE
20 course.cname = '物理'
21 ) AS B ON A.student_id = B.student_id
22 WHERE
23 A.num > B.num
查询平均成绩大于60分的同学的学号和平均成绩
1 SELECT
2 B.student_id,
3 student.sname,
4 B.cc
5 FROM
6 (
7 SELECT
8 student_id,
9 num,
10 avg(num) AS cc
11 FROM
12 score
13 GROUP BY
14 student_id
15 HAVING
16 avg(num) > 60
17 ) AS B
18 LEFT JOIN student ON B.student_id = student.sid
查询所有同学的学号、姓名、选课数、总成绩
1 SELECT
2 student_id,
3 student.sname,
4 count(score.course_id)as cc,
5 sum(num)as cj
6 FROM
7 student
8 LEFT JOIN score ON score.student_id = student.sid
9 GROUP BY
10 score.student_id
查询姓“李”的老师的个数
1 SELECT * from teacher where tname like '李%'
查询没学过“李平”老师课的同学的学号、姓名
1 SELECT student.sid,student.sname from student where sid not in
2 (SELECT
3 student_id
4 FROM
5 score
6 WHERE course_id IN
7 (
8 SELECT
9 course.cid
10 FROM
11 course
12 LEFT JOIN teacher ON teacher.tid = course.teacher_id
13 WHERE
14 tname = '李平老师'
15 ) GROUP BY student_id)
查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
1 select A.student_id,B.sname FROM (SELECT score.student_id,student.sname,course_id
2 from score LEFT JOIN student on student.sid=score.student_id where score.course_id='1')as A
3 LEFT JOIN(SELECT score.student_id,student.sname,course_id
4 from score LEFT JOIN student on student.sid=score.student_id where score.course_id='2')as B
5 on A.student_id=B.student_id
6 where A.course_id=1 and B.course_id=2;
查询学过“李平”老师所教的所有课的同学的学号、姓名
1 SELECT
2 student.sid,
3 student.sname
4 FROM
5 student
6 WHERE
7 student.sid NOT IN (
8 SELECT
9 student.sname
10 FROM
11 student
12 WHERE
13 student.sid IN (
14 SELECT
15 course.cid
16 FROM
17 course
18 LEFT JOIN teacher ON teacher.tid = course.teacher_id
19 WHERE
20 teacher.tname = '李平老师'
21 )
22 )
查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
1 SELECT
2 student.sid,
3 student.sname
4 FROM
5 (
6 SELECT
7 *
8 FROM
9 score
10 WHERE
11 score.course_id = '2'
12 ) AS A
13 LEFT JOIN (
14 SELECT
15 *
16 FROM
17 score
18 WHERE
19 score.course_id = '1'
20 ) AS B ON A.student_id = B.student_id
21 LEFT JOIN student ON student.sid = B.student_id
22 WHERE
23 A.num < B.num
查询有课程成绩小于60分的同学的学号、姓名
1 SELECT
2 student.sid,
3 student.sname
4 FROM
5 score
6 LEFT JOIN course ON course.cid = score.course_id
7 LEFT JOIN student ON student.sid = score.student_id
8 WHERE
9 score.num < 60
10 GROUP BY
11 student_id
查询没有学全所有课的同学的学号、姓名
1 SELECT
2 student.sid,
3 student.sname
4 FROM
5 student
6 WHERE
7 student.sid NOT IN (
8 SELECT
9 student.sid
10 FROM
11 score
12 LEFT JOIN course ON course.cid = score.course_id
13 LEFT JOIN student ON student.sid = score.student_id
14 GROUP BY
15 score.student_id
16 HAVING
17 count(course_id) = (SELECT COUNT(cid) FROM course)
18 )
查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
1 SELECT
2 *
3 FROM
4 score
5 LEFT JOIN student on score.student_id = student.sid
6 LEFT JOIN course ON course.cid = score.course_id
7 WHERE student_id != 1 AND
8 score.course_id in (
9 SELECT
10 course_id
11 FROM
12 score
13 WHERE
14 student_id = 1
15 )
16 GROUP BY student_id
查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名
1 SELECT student_id from score where student_id!=1 and course_id IN
2 (select course_id from score where student_id =1 GROUP BY course_id)
3 GROUP BY student_id
查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
1 SELECT
2 student_id,
3 sname
4 FROM
5 score
6 LEFT JOIN student ON score.student_id = student.sid
7 WHERE
8 student_id IN (
9 SELECT
10 student_id
11 FROM
12 score
13 WHERE
14 student_id != 1
15 GROUP BY
16 student_id
17 HAVING
18 count(course_id) = (
19 SELECT
20 count(1)
21 FROM
22 score
23 WHERE
24 student_id = 1
25 )
26 )
27 AND course_id IN (
28 SELECT
29 course_id
30 FROM
31 score
32 WHERE
33 student_id = 1
34 )
35 GROUP BY
36 student_id
37 HAVING
38 count(course_id) = (
39 SELECT
40 count(1)
41 FROM
42 score
43 WHERE
44 student_id = 1
45 )
删除学习“李平”老师课的SC表记录
1 DELETE FROM score where score.course_id IN
2 (SELECT course_id from course LEFT JOIN teacher on teacher.tid=course.teacher_id
3 LEFT JOIN score on score.course_id=course.cid
4 LEFT JOIN student on score.student_id=student.sid
5 WHERE teacher.tname='李平老师'
6 GROUP BY course_id;)
向SC表中插入一些记录，这些记录要求符合以下条件：
①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩
1 insert into score (student_id,course_id,num)SELECT student_id,1,FLOOR(avg(num))
2 (SELECT student_id from score where course_id !=2
3 SELECT FLOOR(avg(num))from score where course_id = 2)
按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：
学生ID,语文,数学,英语,有效课程数,有效平均分
1 select sc.student_id,
2 (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
3 (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
4 (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
5 count(sc.course_id),
6 avg(sc.num)
7 from score as sc
8 group by student_id desc
查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
1 select student_id,MAX(num),MIN(num) from score GROUP BY course_id
按各科平均成绩从低到高和及格率的百分数从高到低顺序
1 SELECT
2 course_id,
3 avg(num) AS nn,
4 sum(
5 CASE
6 WHEN num < 60 THEN
7 0
8 ELSE
9 1
10 END
11 ),
12 SUM(1),
13 sum(
14 CASE
15 WHEN num < 60 THEN
16 0
17 ELSE
18 1
19 END
20 ) / SUM(1) AS pj
21 FROM
22 score
23 GROUP BY
24 course_id
25 ORDER BY
26 avg(num) DESC
课程平均分从高到低显示（现实任课老师）
1 SELECT score.course_id,course.cname,avg(num),teacher.tname from score LEFT JOIN course on course.cid=score.course_id
2 LEFT JOIN teacher on teacher.tid=course.teacher_id
3 GROUP BY course_id
4
5 HAVING avg(num)
6 ORDER BY avg(num) DESC
浙公网安备 33010602011771号