Mysql语句练习题

SELECT * FROM school01.class;
SELECT * FROM school01.course;
SELECT * FROM school01.student;
#1.查询（没有学生所选）课程的信息
select * from course
left join score on course.cid = score.course_id
where score.student_id is NULL;

#2.查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from score
left join student on student.sid = score.student_id
where score.course_id in
(
select score.course_id from student
left join score on student.sid = score.student_id
where student.sid =1 group by score.course_id
) and student.sid !=1 group by score.student_id;

#3.查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from (
select T.student_id, group_concat(T.course_id ) as Course_Info from
(select * from score order by course_id ) as T group by T.student_id
) as M
where Course_Info =
(select group_concat(course_id ) from score where student_id = 1 order by course_id )
and M.student_id !=1 ;

#4.查询没学过"张三"老师授课的同学的信息
select * from student
where student.sid not in
(select score.student_id from score
where score.course_id in (
select course.cid from course left join teacher on course.teacher_id = teacher.tid
where teacher.tname = ‘张三’)
);

#5.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from
(select T.student_id, group_concat(T.course_id ) as Course_Info from
(select * from score order by course_id ) as T group by T.student_id
) as M
where M.Course_Info = ‘1,2’;

#6.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.sid, count() from student left join score on student.sid = score.student_id
where score.number < 80 group by student.sid having count() >=1 ;

#7.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select student.sid, avg(score.number), sum(score.number) from student
left join score on student.sid = score.student_id
group by student.sid order by avg(score.number) desc;

#8.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select student.*, avg(score.number), sum(score.number) from student
left join score on student.sid = score.student_id
group by student.sid order by sum(score.number) desc limit 1,2;

#9.查询出只有两门课程的全部学生的学号和姓名
select student., count() from student
left join score on student.sid = score.student_id
group by student.sid order by count(*) = 2;

#10.查询每个班上男生、女生总人数
select class.,student.gender, count() from student
left join class on student.class_id = class.cid
group by class.cid , student.gender；