Python/ MySQL练习题（一）
查询“生物”课程比“物理”课程成绩高的所有学生的学号
SELECT
*
FROM
(
SELECT
*
FROM
course
LEFT JOIN score ON score.course_id = course.cid
WHERE
course.cname = '生物'
) AS A
INNER JOIN (
SELECT
*
FROM
course
LEFT JOIN score ON score.course_id = course.cid
WHERE
course.cname = '物理'
) AS B ON A.student_id = B.student_id
WHERE
A.num > B.num
查询平均成绩大于60分的同学的学号和平均成绩
SELECT
B.student_id,
student.sname,
B.cc
FROM
(
SELECT
student_id,
num,
avg(num) AS cc
FROM
score
GROUP BY
student_id
HAVING
avg(num) > 60
) AS B
LEFT JOIN student ON B.student_id = student.sid
查询所有同学的学号、姓名、选课数、总成绩
SELECT
student_id,
student.sname,
count(score.course_id)as cc,
sum(num)as cj
FROM
student
LEFT JOIN score ON score.student_id = student.sid
GROUP BY
score.student_id
查询姓“李”的老师的个数
SELECT * from teacher where tname like '李%'
查询没学过“李平”老师课的同学的学号、姓名
SELECT student.sid,student.sname from student where sid not in
(SELECT
student_id
FROM
score
WHERE course_id IN
(
SELECT
course.cid
FROM
course
LEFT JOIN teacher ON teacher.tid = course.teacher_id
WHERE
tname = '李平老师'
) GROUP BY student_id)
查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
select A.student_id,B.sname FROM (SELECT score.student_id,student.sname,course_id
from score LEFT JOIN student on student.sid=score.student_id where score.course_id='')as A
LEFT JOIN(SELECT score.student_id,student.sname,course_id
from score LEFT JOIN student on student.sid=score.student_id where score.course_id='')as B
on A.student_id=B.student_id
where A.course_id=1 and B.course_id=2;
查询学过“李平”老师所教的所有课的同学的学号、姓名
SELECT
student.sid,
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT
student.sname
FROM
student
WHERE
student.sid IN (
SELECT
course.cid
FROM
course
LEFT JOIN teacher ON teacher.tid = course.teacher_id
WHERE
teacher.tname = '李平老师'
)
)
查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
SELECT
student.sid,
student.sname
FROM
(
SELECT
*
FROM
score
WHERE
score.course_id = ''
) AS A
LEFT JOIN (
SELECT
*
FROM
score
WHERE
score.course_id = ''
) AS B ON A.student_id = B.student_id
LEFT JOIN student ON student.sid = B.student_id
WHERE
A.num < B.num
查询有课程成绩小于60分的同学的学号、姓名
SELECT
student.sid,
student.sname
FROM
score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON student.sid = score.student_id
WHERE
score.num < 60
GROUP BY
student_id
查询没有学全所有课的同学的学号、姓名
SELECT
student.sid,
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT
student.sid
FROM
score
LEFT JOIN course ON course.cid = score.course_id
LEFT JOIN student ON student.sid = score.student_id
GROUP BY
score.student_id
HAVING
count(course_id) = (SELECT COUNT(cid) FROM course)
)
查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
SELECT
*
FROM
score
LEFT JOIN student on score.student_id = student.sid
LEFT JOIN course ON course.cid = score.course_id
WHERE student_id != 1 AND
score.course_id in (
SELECT
course_id
FROM
score
WHERE
student_id = 1
)
GROUP BY student_id
查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名
SELECT student_id from score where student_id!=1 and course_id IN
(select course_id from score where student_id =1 GROUP BY course_id)
GROUP BY student_id
查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
SELECT
student_id,
sname
FROM
score
LEFT JOIN student ON score.student_id = student.sid
WHERE
student_id IN (
SELECT
student_id
FROM
score
WHERE
student_id != 1
GROUP BY
student_id
HAVING
count(course_id) = (
SELECT
count(1)
FROM
score
WHERE
student_id = 1
)
)
AND course_id IN (
SELECT
course_id
FROM
score
WHERE
student_id = 1
)
GROUP BY
student_id
HAVING
count(course_id) = (
SELECT
count(1)
FROM
score
WHERE
student_id = 1
)
删除学习“李平”老师课的SC表记录
DELETE FROM score where score.course_id IN
(SELECT course_id from course LEFT JOIN teacher on teacher.tid=course.teacher_id
LEFT JOIN score on score.course_id=course.cid
LEFT JOIN student on score.student_id=student.sid
WHERE teacher.tname='李平老师'
GROUP BY course_id;)
向SC表中插入一些记录，这些记录要求符合以下条件：
①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩
insert into score (student_id,course_id,num)SELECT student_id,1,FLOOR(avg(num))
(SELECT student_id from score where course_id !=2
SELECT FLOOR(avg(num))from score where course_id = 2)
按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：
学生ID,语文,数学,英语,有效课程数,有效平均分
select sc.student_id,
(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,
(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,
(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,
count(sc.course_id),
avg(sc.num)
from score as sc
group by student_id desc
查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select student_id,MAX(num),MIN(num) from score GROUP BY course_id
按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT
course_id,
avg(num) AS nn,
sum(
CASE
WHEN num < 60 THEN
0
ELSE
1
END
),
SUM(1),
sum(
CASE
WHEN num < 60 THEN
0
ELSE
1
END
) / SUM(1) AS pj
FROM
score
GROUP BY
course_id
ORDER BY
avg(num) DESC
课程平均分从高到低显示（现实任课老师）
SELECT score.course_id,course.cname,avg(num),teacher.tname from score LEFT JOIN course on course.cid=score.course_id
LEFT JOIN teacher on teacher.tid=course.teacher_id
GROUP BY course_id HAVING avg(num)
ORDER BY avg(num) DESC