# 1、查询男生、女生的人数；
select gender, count(sid) from student group by gender;
# 2、查询姓“张”的学生名单；
select sid, sname from student where sname like '张%';
# 3、课程平均分从高到低显示
select course.cname, avg(score.num) from course inner join score on course.cid = score.course_id group by course.cid order by avg(score.num) desc;
# 4、查询有课程成绩小于60分的同学的学号、姓名；
select student.sid, student.sname from student inner join score on score.student_id = student.sid group by student.sid having min(score.num) < 60;
# 5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
select sid, sname from
(select student.sid, student.sname, score.course_id from student inner join score on student.sid = score.student_id) as t1
inner join
(select course_id from score where student_id = 1) as t2 on t2.course_id = t1.course_id group by t1.sid;
# 6、查询出只选修了一门课程的全部学生的学号和姓名；
select sid, sname from
(select student_id from score group by student_id having count(sid)=1) as t1
inner join
(select student.sid, student.sname from student inner join score on student.sid = score.student_id) as t2
on t1.student_id = t2.sid group by sid;
# 7、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id, max(num), min(num) from score group by course_id;
# 8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select sid, sname from student inner join(
select student_id from (
select t1.student_id, t1.num as num1, t2.num as num2
from score as t1
inner join score as t2 on t1.student_id = t2.student_id and t1.course_id = 1 and t2.course_id = 2
) t where num2 < num1
) st on st.student_id = student.sid;
# 9、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
select student_id from (
select t1.student_id, t1.num as biology, t2.num as physics from (
select score.student_id, score.course_id, score.num, course.cname from score inner join course on course.cid = score.course_id
) t1 inner join (
select score.student_id, score.course_id, score.num, course.cname from score inner join course on course.cid = score.course_id
) t2 on t1.student_id = t2.student_id and t1.cname = '生物' and t2.cname = '物理'
) t where t.biology > t.physics;
# 10、查询平均成绩大于60分的同学的学号和平均成绩;
select student_id, avg(num) from score group by student_id having avg(num) > 60;
# 11、查询所有同学的学号、姓名、选课数、总成绩；
select student.sid, student.sname, count(score.course_id), sum(score.num) from student inner join score on score.student_id = student.sid group by score.student_id;
# 12、查询姓“李”的老师的个数；
select count(tid) from teacher where tname like '李%';
# 13、查询没学过“张磊老师”课的同学的学号、姓名；
select student.sid, student.sname from student where sid not in (
select score.student_id from score inner join (
select course.cid, teacher.tname from course inner join teacher on teacher.tid = course.teacher_id
) as tt on tt.cid = score.course_id and tt.tname = '张磊老师'
);
# 14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
select sid, sname from student where sid in (
select t1.student_id from score t1 inner join score t2 on t1.student_id = t2.student_id and t1.course_id = 1 and t2.course_id = 2
);
# 15、查询学过“李平老师”所教的所有课的同学的学号、姓名；
select student_id from (
select *
from score
inner join (
select course.cid, teacher.tname
from course
inner join teacher on teacher.tid = course.teacher_id
) as tt on tt.cid = score.course_id and tt.tname = '李平老师'
) t group by student_id having count(student_id) = (select count from (select count(course.cid) as count, teacher.tname from course inner join teacher on teacher.tid = course.teacher_id group by teacher.tname) t where tname = '李平老师');
