-- 7、查询学过"张三"老师授课的同学的信息
select a.*from Student a
inner join Score b on a.s_id = b.s_id
inner join Teacher c on b.c_id =c.t_id
where c.t_name = '张三';
查询没学过"张三"老师授课的同学的信息
select d.*
from Student d
where d.s_name not in (select a.s_name from Student a
inner join Score b on a.s_id = b.s_id
inner join Teacher c on b.c_id =c.t_id
where c.t_name = '张三');
-- 11、查询没有学全所有课程的同学的信息
select a.*from Student a
where a.s_id not in (select s_id from Score where c_id='01')
or a.s_id not in (select s_id from Score where c_id='02')
or a.s_id not in (select s_id from Score where c_id='03');
#查询至少有一门课与学号为"01"的同学所学相同的同学的信息select distinct a.*from Student a , Score b
where a.s_id= b.s_id and b.c_id in (select c_id from Score where s_id= '01');
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名select s_namefrom student
where s_name not in (select a.s_namefrom Student a,Score b , Course c, Teacher d
where a.s_id= b.s_id and b.c_id = c.c_id and c.t_id=d.t_id and d.t_name ='张三')
#- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.* ,b.c_id, b.s_score
from Student a , Score b
where a.s_id = b.s_id and a.s_id in (select s_id from Score where c_id = '01' and s_score<60) and b.c_id = '01'
order by b.s_score DESC
select t.s_score,(select count(s.s_score)+1 from Score as s where s.s_score>t.s_score) as rank1
from Score as t
order by t.s_score desc;
-- 19、按各科成绩进行排序，并显示排名(实现不完全)
select c.s_id , c.c_id, c.s_score , count(d.s_score)+1 s_rank
from score c
left join Score d
on c.s_score < d.s_score AND c.c_id=d.c_id
group by c.c_id ,c.s_id
order by c.c_id ;
--20 不会做
select a.s_id, a.sum_score ,count(b.sum_score)+1 rk
from (select s_id, sum(s_score) sum_score from Score group by s_id) a, (select s_id, sum(s_score) sum_score from Score group by s_id) b
where a.sum_score <= b.sum_score and a.s_id=b.s_id
group by a.s_id, a.sum_score
order by a.s_id;
-21
#查询不同老师所教不同课程平均分从高到低显示
select a.* ,avg(c.s_score)
from Teacher a , Course b,Score c
where a.t_id=b.t_id and b.c_id = c.c_id
group by a.t_idorder by avg(c.s_score) desc ;
select name , continent
from world a
where a.population>= all(select population*3 from world b where a. continent =b.continent)
