如感兴趣请搜索下载:在主页的资源中:Mysql作业压缩文件
1、查询所有的课程的名称以及对应的任课老师姓名
select cname,tname from course inner join teacher on teacher_id=tid;
2、查询学生表中男女生各有多少人
select gender,count(sid) from student group by gender='女' having count(sid);
3、查询物理成绩等于100的学生的姓名
select sname from student where student.sid in(select student_id from score inner join course on course_id=cid and cname='物理'and num=100);
第一步:取出student_id号
select student_id from score inner join course on course_id=cid and cname='物理'and num=100;
第二步:对比student_id号
select sname from student where student.sid in(select student_id from score inner join course on course_id=cid and cname='物理'and num=100);
4、查询平均成绩大于八十分的同学的姓名和平均成绩
第一步:以名字分组并且得到平均分数 >80的名字和平均成绩
select sname,avg(num) from student inner join score on student_id =student.sid group by sname having avg(num)>80;
5、查询所有学生的学号，姓名，选课数，总成绩
select student.sid,sname,count(course_id),sum(num) from student inner join score on student_id=student.sid group by student.sid ;
6、 查询姓李老师的个数
select tname from teacher WHERE tname like '李%' GROUP BY tname;
7、 查询没有报李平老师课的学生姓名
第一步:得到李平老师教的课程id
select cid from course inner join teacher on teacher_id = tid where tname = '李平老师'
第二步:得到没有报李平老师课的学生姓名
select student.sname from student where sname not in(select sname from student inner join score on student.sid =score.student_id WHERE course_id NOT in (select cid from course inner join teacher on teacher_id = tid where tname = '李平老师'))
8、 查询物理课程比生物课程高的学生的学号
第一步:得到两个课程的学号和成绩
select student_id,num from score WHERE course_id =(select cid from course where cname='物理');
select student_id,num from score WHERE course_id =(select cid from course where cname='生物');
第二步:对比两个的分数高低:(加上as t1/t2)
select t1.student_id FROM (select student_id,num from score WHERE course_id =(select cid from course where cname='物理'))as t1 inner join
(select student_id,num from score WHERE course_id =(select cid from course where cname='生物'))as t2 on t1.student_id=t2.student_id WHERE t1.num>t2.num;
9、 查询没有同时选修物理课程和体育课程的学生姓名
第一步:得到同时选了两门课的同学id号
select t1.student_id FROM (select student_id from score WHERE course_id =(select cid from course where cname='物理'))as t1 inner join
(select student_id from score WHERE course_id =(select cid from course where cname='体育'))as t2 on t1.student_id=t2.student_id ;
第二步:对比id号不存在的就是没有同时选择的.
select sname from student WHERE sid not in ( select t1.student_id FROM (select student_id from score WHERE course_id =(select cid from course where cname='物理'))as t1 inner join
(select student_id from score WHERE course_id =(select cid from course where cname='体育'))as t2 on t1.student_id=t2.student_id );
10、查询挂科超过两门(包括两门)的学生姓名和班级
第一步:先得到连续挂科两门的学生id
select student_id from score where num<60 HAVING (count(num<60)>=2);
第二步:得到名字和班级id
select sname,class_id from student where student.sid in (select student_id from score where num<60 HAVING (count(num<60)>=2));
第三步:得到班级名称:
select sname,caption from (select sname,class_id from student where student.sid in (select student_id from score where num<60 HAVING (count(num<60)>=2))) as b left join class on class.cid = b.class_id;
11 、查询选修了所有课程的学生姓名
第一步:得到选修课程的课程数量:
select count(cid) from course;
第二步:分组下筛选同id下的学生有多少个class_id
select sname from student inner join score on student_id=student.sid GROUP BY student_id having count(class_id)=(select count(cid) from course);
12、查询李平老师教的课程的所有成绩记录
第一步:得到李平老师教的那个课程id号
select tid from teacher where tname='李平老师';
第二步:得到李平老师所教的课程id号:
select cid from course where teacher_id=(select tid from teacher where tname='李平老师');
第三步:得到选择此课程的学生id: 需要得到成绩
select num from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
13、查询全部学生都选修了的课程号和课程名
第一步:得到全部学生的数量:
select count(sid) from student;
第二步:分组查看选课数量是不是等于学生数量,得到课程id:
select course_id from student inner join score on student.sid = student_id group by course_id having count(course_id)=(select count(sid) from student);
第三步:得到课程名称:
select cname from course where cid=(select course_id from student inner join score on student.sid = student_id group by course_id having count(course_id)=(select count(sid) from student));
14、查询每门课程被选修的次数
select course_id,count(course_id) from student inner join score on student.sid = student_id group by course_id ;
15、查询之选修了一门课程的学生姓名和学号
第一步:得到成绩表中corese_id数量为一的学生学号:
select student_id from score group by student_id having count(course_id)=1;
第二步:通过id得到学生名字和id号:
select sname,sid from student where sid in (select student_id from score group by student_id having count(course_id)=1);
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
select distinct num from score order by num desc;
17、查询平均成绩大于85的学生姓名和平均成绩
select sname,avg(num) from score inner join student on student.sid=student_id group by sname having avg(num)>85;
18、查询生物成绩不及格的学生姓名和对应生物分数
第一步:得到生物课程的id号:
select cid from course where cname='生物';
第二步:得到生物课程分数低于60分的学生id和分数:
select * from student where student.sid in b.student_id (select student_id,num from score where course_id=(select cid from course where cname='生物') having num<60)as b;
第三步:得到学生名字:
select sname,b.num from student inner join ((select student_id,num from score where course_id=(select cid from course where cname='生物') having num<60))as b on student.sid=b.student_id ;
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
第一步:得到李平老师教的那个课程id号
select tid from teacher where tname='李平老师';
第二步:得到李平老师所教的课程id号:
select cid from course where teacher_id=(select tid from teacher where tname='李平老师');
第三步:得到学生id: 得到平均成绩(平均成绩最高的学生姓名)
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、查询每门课程成绩最好的前两名学生姓名
第一步:得到课程id:
select cid from course ;
第二步: 按照课程id分组:
21、查询不同课程但成绩相同的学号，课程号，成绩
第一步: 不同课程:但是成绩相同
的学号课程号与成绩
22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；
第一步得到李平老师教的课程id
select cid from course where teacher_id=(select tid from teacher where tname='李平老师');
第二步得到有学李平老师课程的学生id
select * from score inner join student on student.sid=student_id where course_id in(select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
第三步:得到学生姓名和课程名称:
select sname,cname from course right join (select course_id,sname from score right join (select sid,sname from student where sid not in (select distinct student_id from score where course_id in(select cid from course where teacher_id=(select tid from teacher where tname='李平老师'))))as f on student_id=f.sid)as t on course.cid=t.course_id;
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
第一步:得到学号唯一的同学所选修的课程id
select course_id from score where student_id=1; 1 2 4
第二步:得到学生学号和名字:
select student_id,sname from student right join (select distinct student_id from score where course_id in (select course_id from score where student_id=1)) as f on student.sid=f.student_id;
24、任课最多的老师中学生单科成绩最高的学生姓名
题目