mysql练习题

mysql练习题

今日练习：


1、将所有的课程的名称以及对应的任课老师姓名打印出来，如下：  
select course.cname,teacher.tname from teacher inner join course on course.teacher_id=teacher.tid;

2、查询学生表中男女生各有多少人？ 如下：
select gender,count(1) as 人数 from student group by gender;

3、查询物理成绩等于100的学生的姓名？如下：
select sname from student where sid in (select student_id from course inner JOIN score on score.course_id=(SELECT course.cid FROM course WHERE cname='物理') and score.num=100 GROUP BY student_id);

4、查询平均成绩大于八十分的同学的姓名和平均成绩，如下：
select sname,avg(num) from student inner join score on score.student_id=student.sid GROUP BY student_id HAVING avg(num)>80;

5、查询所有学生的学号，姓名，选课数，总成绩
select student.sid,student.sname,count(1),sum(num) from student INNER JOIN score on student.sid=score.student_id GROUP BY student_id;

6、查询姓李老师的个数
select count(1) from teacher WHERE tname like '李%';

7、查询没有报李平老师课的学生姓名
SELECT sname from student WHERE sid 
                        not in (
                                    select student_id FROM score WHERE course_id IN
                                            (select cid from course where teacher_id=(
                                                        select tid from teacher where tname='李平老师')));

8、查询物理课程比生物课程高的学生的学号
select A.student_id from (SELECT * from score WHERE course_id = (SELECT cid from course WHERE cname='物理')) as A
INNER JOIN
(SELECT * from score WHERE course_id = (SELECT cid from course WHERE cname='生物')) as B
on A.student_id=B.student_id
WHERE A.num>B.num;

9、查询没有同时选修物理课程和体育课程的学生姓名
SELECT sname from student WHERE sname not in(
SELECT sname from student WHERE sid in(
    SELECT student_id from score WHERE course_id=(SELECT cid from course WHERE cname='物理'))
        and sid in (SELECT student_id from score WHERE course_id=(SELECT cid from course WHERE cname='体育')));

10、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT sname,class_id from student WHERE sid in (SELECT student_id from score WHERE num<60 GROUP BY student_id HAVING count(1)>=2);

11 、查询选修了所有课程的学生姓名
SELECT sname from student WHERE sid in(select sid from score WHERE course_id in (select cid from course));

12、查询李平老师教的课程的所有成绩记录
SELECT * from score where course_id in (SELECT course_id from course where teacher_id=(SELECT tid from teacher where tname='李平老师'));

 13、查询全部学生都选修了的课程号和课程名
 select course_id from score GROUP BY course_id HAVING count(1)=(SELECT count(1) from student);


14、查询每门课程被选修的次数
select count(1),course_id from score GROUP BY course_id;

15、查询之选修了一门课程的学生姓名和学号
select student_id from score GROUP BY student_id HAVING count(1)=1;

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT num from score GROUP BY num ORDER BY num desc;

17、查询平均成绩大于80的学生姓名和平均成绩
SELECT student.sname,avg(num) from score INNER JOIN student on student.sid = score.student_id GROUP BY student_id HAVING avg(num)>80;

18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT A.num,B.sname from (SELECT * from score WHERE course_id=(SELECT cid from course WHERE cname='生物') 
GROUP BY student_id HAVING num<60) as A INNER JOIN (SELECT * from student) as B on A.student_id=B.sid;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT sname from student where sid=(SELECT student_id from score WHERE course_id in (SELECT cid from course where teacher_id=
            (SELECT tid from teacher WHERE tname='李平老师')) GROUP BY student_id ORDER BY avg(num) desc limit 1);

20、查询每门课程成绩最好的前两名学生姓名
select * from score left JOIN ( SELECT sid,
(select DISTINCT num from score as s2 where s2.course_id=s1.course_id ORDER BY num desc limit 1) as first_num,
(select DISTINCT 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>=second_num;

21、查询不同课程但成绩相同的学号，课程号，成绩
SELECT s1.student_id,s1.course_id,s1.num from score as s1,score as s2 WHERE s1.num = s2.num and s1.course_id != s2.course_id;

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
select C.sname,D.cname from 
(select  DISTINCT B.sname,A.course_id from (
SELECT * from score WHERE course_id not in (SELECT cid from course INNER JOIN teacher on teacher.tid = course.teacher_id and teacher.tname='李平老师')) as A 
INNER JOIN student as B WHERE A.student_id=B.sid) as C INNER JOIN course as D on C.course_id=D.cid;

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
select A.sname,A.sid from (
SELECT student_id,course_id from score where course_id in (
SELECT course_id from score where student_id=1)) as B INNER JOIN student as A on B.student_id=A.sid ;

24、任课最多的老师中学生单科成绩最高的学生姓名
select B.sname from(
select * from score WHERE course_id in(
SELECT cid from course where teacher_id=(
select teacher_id from course GROUP BY teacher_id ORDER BY count(1) DESC limit 1)) 
and 
num=(select num from score WHERE course_id in(
SELECT cid from course where teacher_id=(
select teacher_id from course GROUP BY teacher_id ORDER BY count(1) DESC limit 1)) ORDER BY num desc LIMIT 1)) as A
INNER JOIN student as B on A.student_id = B.sid;