经典45题--(1)
一、 数据准备
1、课程表course
2、成绩表sc(部分数据)
3、学生表student(部分数据)
4、教师表teacher
二、题目讲解
1、查询01课程比02课程成绩高的学生的信息及课程分数
select *
from student as a
right join sc as b
on a.sid=b.sid
inner join sc as c
on a.sid=c.sid and b.cid=01 and c.cid=02
where b.score>c.score;
1.1、查询同时存在01课程和02课程的情况
select *
from sc as a
inner join sc as b
on a.sid=b.sid and a.cid='01' and b.cid='02';
1.2查询存在01课程但可能不存在02课程的情况(不存在时显示为null)
select *
from sc as a
left join sc as b
on a.sid=b.sid and b.cid='02'
where a.cid = '01';
1.3、查询不存在01课程,存在02课程的情况
select *
from sc
where sid not in (select sid from sc where cid='01')
and cid ='02';
2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.sid,a.sname,avg(b.score) as s_avg
from student as a
right join sc as b
on a.sid=b.sid
group by b.sid
having s_avg>60;
3、查询在SC表存在成绩的学生信息
select a.*
from student as a
right join sc as b
on a.sid=b.sid
group by b.sid;
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程总成绩(没成绩的显示为null)
select a.sid,a.sname,count(b.cid) as c_sum,sum(b.score) as s_sum
from student as a
left join sc as b
on a.sid=b.sid
group by a.sid;
4.1、查询有成绩的学生信息
#法1
select * from student
where sid in(select distinct sid from sc);
#法2
select * from student
where sid in(select sid from sc group by sid);
#法3
select a.*
from student as a
inner join sc as b
on a.sid=b.sid
group by a.sid;
5、查看李老师的数量
select count(*)
from teacher
where tname like '李%';
6、查询学过张三老师的课的学生信息
select student.*
from teacher,course,sc,student
where teacher.tname = '张三'
and teacher.tid=course.tid
and course.cid=sc.cid
and sc.sid=student.sid;
7、查询没有学全所有课程的同学的信息
select a.*,count(cid) as c_count
from student as a
inner join sc as b
on a.sid=b.sid
group by a.sid
having c_count<(select count(cid) from course);
8、查询至少有一门课与学号为01的同学所学相同的同学信息
#法1
select cid from sc where sid = '01';
select * from student where sid in(
select distinct sid from sc
where cid in(select cid from sc where sid = '01'));
#法2
select distinct a.*
from student as a
inner join sc as b
on a.sid=b.sid
where cid in(select cid from sc where sid = '01');
9、查询与学号为01的同学所学课程完全相同的其他同学信息
select * from student where sid in(
select a.sid from (select sid,group_concat(cid order by cid) as courses
from sc group by sid) as a
inner join (select sid,group_concat(cid order by cid) as courses
from sc group by sid having sid='01') as b
on a.sid != 1 and a.courses = b.courses);
10、查询没有学过张三老师讲授的任意一门课程的学生姓名
select distinct sname from student where sid not in(
select sid from sc where cid in(
select a.cid from course as a
inner join teacher as b
on a.tid=b.tid
where b.tname = '张三' ));