sql语句练习50题(自己的答案)

-- 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)