一、表关系
二、操作表
1、自行创建测试数据
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
SELECT A.student_id,A.生物, B.物理 from
(SELECT student_id,num as 生物 from score WHERE course_id=(SELECT cid FROM course WHERE cname='生物')) as A
LEFT JOIN
(SELECT student_id,num as 物理 from score WHERE course_id=(SELECT cid FROM course WHERE cname='物理')) as B
on A.student_id=B.student_id
WHERE A.`生物`>B.`物理`
3、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT student_id,avg(num) from score GROUP BY student_id HAVING avg(num) >60
4、查询所有同学的学号、姓名、选课数、总成绩；
SELECT student.sid,student.sname,A.count,A.sum_num from student
LEFT JOIN
(SELECT student_id,COUNT(1)as count,SUM(num)as sum_num from score GROUP BY student_id) as A
on student.sid=A.student_id
5、查询姓“李”的老师的个数；
SELECT COUNT(1) from teacher WHERE tname LIKE '李%'
6、查询没学过“叶平”老师课的同学的学号、姓名；
SELECT student.sid,student.sname from student WHERE sid not in
(SELECT student_id from score WHERE course_id in
(SELECT cid from course
INNER JOIN
(SELECT tid from teacher WHERE tname='李平老师') as A
on course.teacher_id=A.tid) GROUP BY student_id)
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT student.sid,student.sname from student
INNER JOIN
(SELECT A.student_id from
(SELECT * from score WHERE course_id=1) as A
inner JOIN(SELECT * from score WHERE course_id=2) as B
on A.student_id=B.student_id)as C
on student.sid=C.student_id
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT student.sid,student.sname from student INNER JOIN
(SELECT student_id from score WHERE course_id in
(SELECT cid from course
INNER JOIN
(SELECT tid from teacher WHERE tname='李平老师') as A
on course.teacher_id=A.tid) GROUP BY score.student_id
HAVING COUNT(1)=(SELECT COUNT(1) from course INNER JOIN(SELECT tid from teacher WHERE tname='李平老师') as B on course.teacher_id=B.tid)
) as C on student.sid=C.student_id
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT student.sid,student.sname from student
INNER JOIN
(SELECT A.student_id from
(SELECT * from score WHERE course_id=1) as A
INNER JOIN
(SELECT * from score WHERE course_id=2) as B
on A.student_id=B.student_id
WHERE B.num < A.num)as C
10、查询有课程成绩小于60分的同学的学号、姓名；
SELECT student.sid,student.sname from student
INNER JOIN
(SELECT student_id from score WHERE num<60 GROUP BY student_id) as A
on student.sid=A.student_id
11、查询没有学全所有课的同学的学号、姓名；
SELECT * from student
INNER JOIN
(SELECT student_id from score GROUP BY student_id HAVING COUNT(1)<(SELECT COUNT(1) from course)) as A
on student.sid=A.student_id;
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
SELECT student.sid,student.sname from student
INNER JOIN
(SELECT student_id from score
WHERE course_id in (SELECT course_id from score WHERE student_id=1) GROUP BY student_id) as A on student.sid=A.student_id
13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
SELECT student.sid,student.sname from student INNER JOIN
(SELECT student_id from score WHERE course_id in (SELECT course_id from score WHERE student_id=1)
GROUP BY student_id HAVING COUNT(1)>=(SELECT COUNT(1) from score WHERE student_id=1)
) as A on student.sid=A.student_id
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
SELECT student.sid,student.sname from student INNER JOIN
(SELECT student_id from score WHERE course_id in (SELECT course_id from score WHERE student_id=1)
GROUP BY student_id HAVING COUNT(1)=(SELECT COUNT(1) from score WHERE student_id=1)
) as A on student.sid=A.student_id
15、删除学习“叶平”老师课的score表记录；
DELETE from score WHERE score.course_id in
(
SELECT cid FROM course INNER JOIN
(SELECT tid from teacher WHERE tname='李平老师') as A
on course.teacher_id=A.tid
)
16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
SELECT
DISTINCT A.student_id as 学生ID,
(SELECT B.num from score as B WHERE A.student_id=B.student_id AND B.course_id=1)as 语文,
(SELECT C.num from score as C WHERE A.student_id=C.student_id AND C.course_id=2)as 数学,
(SELECT D.num from score as D WHERE A.student_id=D.student_id AND D.course_id=3)as 英语
FROM score as A
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT course_id as 课程ID,MAX(num)as 最高分,MIN(num)as 最低分 from score GROUP BY course_id
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；*
SELECT course_id,AVG(num) as avg_num,SUM(CASE WHEN score.num<60 THEN 0 ELSE 1 END)/COUNT(1) as percent from score
GROUP BY course_id ORDER BY avg_num ASC,percent DESC
20、课程平均分从高到低显示（显示任课老师）；
SELECT teacher.tname,B.avg_num,B.cname from teacher
INNER JOIN
(
SELECT course.teacher_id,A.avg_num,course.cname from course
INNER JOIN(SELECT course_id,AVG(num)as avg_num FROM score
GROUP BY course_id ORDER BY AVG(num) DESC) as A
on course.cid=A.course_id
) as B
on teacher.tid=B.teacher_id
21、查询各科成绩前三名的记录:(不考虑成绩并列情况) ***
select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join
(
select
sid,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as second_num
from
score as s1
) as T
on score.sid =T.sid
where score.num <= T.first_num and score.num >= T.second_num
22、查询每门课程被选修的学生数；
SELECT course_id,COUNT(1) from score GROUP BY course_id
23、查询出只选修了一门课程的全部学生的学号和姓名；
SELECT student.sid,student.sname from student INNER JOIN
(SELECT student_id from score GROUP BY student_id HAVING COUNT(1))as A
on student.sid=A.student_id
24、查询男生、女生的人数；
SELECT gender,COUNT(1) from student GROUP BY gender
25、查询姓“张”的学生名单；
SELECT * from student WHERE sname LIKE '张%'
26、查询同名同姓学生名单，并统计同名人数；
SELECT sname,COUNT(1) from student GROUP BY sname HAVING COUNT(1)>1
27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
SELECT course_id,avg(num) as avg_num from score GROUP BY course_id ORDER BY avg_num ASC,course_id DESC
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
SELECT student.sid,student.sname,A.avg_num from student INNER JOIN
(SELECT student_id,avg(num)as avg_num from score GROUP BY student_id HAVING AVG(num)>85) as A
on student.sid=A.student_id
29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
SELECT student.sname,A.num from student INNER JOIN
(SELECT student_id,num from score WHERE course_id=(SELECT cid from course WHERE cname='数学') AND num<60)as A
on student.sid=A.student_id
30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
SELECT student.sid,student.sname from student INNER JOIN
(SELECT student_id from score WHERE course_id=3 AND num>60) as A
on student.sid=A.student_id
31、求选了课程的学生人数
SELECT COUNT(1) FROM (SELECT student_id from score GROUP BY student_id) as A
32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
SELECT student.sname,B.num from student INNER JOIN
(
SELECT score.student_id,score.num from score WHERE course_id in (SELECT cid from course INNER JOIN
(SELECT tid from teacher WHERE tname='李平老师')as A
on course.teacher_id=A.tid)
ORDER BY score.num DESC LIMIT 0,1
) as B
on student.sid=B.student_id
33、查询各个课程及相应的选修人数；
SELECT course_id,COUNT(1) from score GROUP BY course_id
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；***
SELECT DISTINCT A.student_id,A.course_id,A.num from score as A,score as B
WHERE A.num=B.num AND A.course_id!=B.course_id
35、查询每门课程成绩最好的前两名；***
select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join
(
select
sid,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num
from
score as s1
) as T
on score.sid =T.sid
where score.num <= T.first_num and score.num >= T.second_num
36、检索至少选修两门课程的学生学号；
SELECT student_id from score GROUP BY student_id HAVING COUNT(1)>=2
37、查询全部学生都选修的课程的课程号和课程名；
SELECT course.cid,course.cname FROM course WHERE cid in
(
SELECT course_id from score GROUP BY course_id HAVING COUNT(1)=(SELECT COUNT(1) FROM student)
)
38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
SELECT student.sid,student.sname from student WHERE student.sid not in
(
SELECT student_id from score WHERE course_id in
(
SELECT course.cid from course INNER JOIN
(SELECT tid from teacher WHERE tname='李平老师') as A
on course.teacher_id=A.tid
) GROUP BY student_id
)
39、查询两门以上不及格课程的同学的学号及其平均成绩；
SELECT student_id,AVG(num) from score WHERE student_id in
(SELECT student_id from score WHERE num<60 GROUP BY student_id HAVING COUNT(1)>2)
GROUP BY student_id
40、检索“004”课程分数小于60，按分数降序排列的同学学号；
SELECT student_id from score WHERE course_id=4 AND num<60 ORDER BY num DESC
41、删除“002”同学的“001”课程的成绩；
DELETE from score WHERE score.course_id=1 AND score.student_id=2
