SQL基础40题

1、查询“01”课程比“02”课程成绩高的所有学生的学号
select
distinct a.sid
from sc a 
inner join sc b
on a.sid=b.sid
and a.cid = '01'
and b.cid = '02'
where a.score_number>b.score_number

2、查询平均成绩大于60分的同学的

select
sid,
avg(score_number) as avg_score
from sc
group by
sid
HAVING avg(score_number) >60

3、查询所有同学的学号、姓名、选课数、总成绩
select
    student.sid as sid
    ,sname
    ,count(distinct cid) course_cnt
    ,sum(score_number) as total_score
from student
left join sc 
on student.sid=sc.sid
group by student.sid,sname;

4、查询姓“李”的老师的个数；

select
count(tid) as count_tid
from teacher
where tname like '李%'

5、查询没学过“张三”老师课的同学的学号、姓名；

select
distinct
a.sid,
a.sname
from
student a
inner join 
sc b
on a.sid=b.sid
inner join 
course c
on b.cid = c.cid
where c.tid != '01'

select sid,sname from student where sid not in (
select s.sid from sc s where cid in (
select c.cid from course c inner join teacher t on c.tid=t.tid where t.tname='张三'
));