drop table student;

create table student(

s_id varchar(10),

sname varchar(20),

sage date,

sex varchar(20)

);

insert into Student values('01' , '赵雷' ,'1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

select * from student;

create table course(

c_id varchar(10),

cname varchar(20),

t_id varchar(10)

);

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

select * from course;

create table teacher(

t_id varchar(10),

tname varchar(20)

);

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

select * from teacher;

create table sc(

s_id varchar(10),

c_id varchar(10),

score decimal(18,1)

);

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

select * from sc order by s_id , c_id;

/*1*/

select  t1.s_id,t1.c_id,t1.score,t2.c_id,t2.score

from sc t1 inner join sc t2

on t1.s_id=t2.s_id

and t1.c_id='01'

and t2.c_id='02'

and t1.score>t2.score;

select a.s_id,a.c_id,b.s_id,b.c_id

from (select *from sc where c_id='01') a

left join (select * from sc where c_id='02') b

on a.s_id=b.s_id

where a.score >b.score;

/*1.1*/

select t1.s_id,t1.c_id,t2.s_id,t2.c_id

from sc t1 inner join sc t2

on t1.s_id=t2.s_id

and t1.c_id='01'

and t2.c_id='02';

select *from

(select *from sc where c_id='01') a

left join (select * from sc where c_id='02') b

on a.s_id=b.s_id

where b.s_id is not null;

/*1.2*/

select *from

(select *from sc where c_id='01') a

left join (select * from sc where c_id='02') b

on a.s_id=b.s_id

;

/*1.3*/

select * from

sc where c_id='02'

and s_id not in

(select s_id from sc where c_id='01');

/*2*/

select a.s_id,b.sname,avg(a.score)

from sc a left join student b

on a.s_id=b.s_id

group by a.s_id,b.sname

having avg(a.score)>=60;

/*3*/

select * from student where s_id in (select s_id from sc group by s_id)

/*4*/

select a.s_id,a.sname,count(b.s_id),sum(b.score)

from student a left join sc b

on a.s_id=b.s_id

group by a.s_id , a.sname;

/*4.1*/

select a.s_id,b.s_id, a.countclass,a.totlescore

from

(select s_id,count(s_id) countclass,sum(score) totlescore from sc group by s_id) a

left join

student b

on  a.s_id=b.s_id

 /*5*/

 select count(*) from teacher where tname like '李%';

 /*6*/

select *

from student a left join sc b

on a.s_id=b.s_id

left join course  c

on b.c_id= c.c_id

left join teacher  d

on c.t_id =d.t_id

where d.tname='张三';

/*7*/

select a.s_id,a.sname,a.sage,a.sex  from student a left join sc b

on a.s_id =b.s_id

having count(b.s_id)<3

group by a.s_id,a.sname,a.sage,a.sex

;

/*8*/

select * from student where s_id

in (select distinct s_id from sc where c_id in (select c_id from sc where s_id='01')  )

/*9*/

select * from student where

s_id in(select s_id from sc where c_id in

(select c_id from sc where s_id='01' ) and s_id<>'01'

group by s_id having count(s_id)>=3) ;

/*10*/

select * from student where s_id not in

(select s_id from sc where c_id in

(select c_id from course where t_id in

(select t_id from teacher where tname='张三')));

/*11*/

select a.s_id,a.sname, b.avg_score

from student a right join (select s_id ,avg(score) avg_score from sc where score<60 group by s_id having count(score)>=2) b

on a.s_id =b.s_id; 

/*12*/

select a.s_id,a.sname,a.sage,a.sex, b.score

from student  a right join sc b

on a.s_id=b.s_id

where b.c_id='01'

and b.score<60

order by b.score desc;

/*13*难点：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩*/

select s_id, max(case c_id when '01' then score else 0 end ) a,

max(case c_id when '02' then score else 0 end ) b,

max(case c_id when '03' then score else 0 end) c,

avg(score)

from sc

group by s_id

order by 5 desc;

/*14*/

select a.c_id,a.cname,b.highest,b.lowest,b.avgscore,c.jigelv,d.middle,e.excellent,f.great,g.people_number

from course a left join

(select c_id,max(score) highest, min(score) lowest ,avg(score) avgscore

from sc group by c_id) b

on a.c_id=b.c_id

left join

(select c_id,(sum(case  when score>=60 then 1 else 0 end)*1.00/count(*)*100) jigelv  from sc group by c_id) c

on a.c_id=c.c_id

left join

(select c_id ,(sum(case when score>=70 and score<80 then 1 else 0 end)*1.00/count(*)*100) middle from sc group by c_id) d

on a.c_id=d.c_id

left join

(select c_id,(sum(case when score>=80 and score<90 then 1 else 0 end )*1.00/count(*)*100) excellent from sc group by c_id)e

on a.c_id=e.c_id

left join

(select c_id,(sum(case when score>=90 then 1 else 0 end )*1.00/count(*)*100) great from sc group by c_id ) f

on a.c_id=f.c_id

left join

(select c_id,count(*) people_number from sc group by c_id) g

on a.c_id=g.c_id

order by g.people_number,a.c_id;

/*15,15.1    row_number()over() rank()over() dense_rank()*/

select s_id, c_id,score,row_number()over(partition by c_id order by score desc ) rank1

from sc;

select s_id,c_id,score,rank()over(partition by c_id order by score desc) rank1

from sc;

select s_id,c_id,score,dense_rank() over(partition by c_id order by score desc) rank1

from sc;

/*16*/

select s_id,sum(score),rank()over(order by sum(score) desc) from sc group by s_id;

/*16.1*/

select s_id,sum(score),dense_rank()over(order by sum(score) desc) from sc group by s_id;

/*17*/

select c_id, sum(case when score<=60 then 1 else 0 end ) a1,

(sum(case when score<=60 then 1 else 0 end)*1.00/count(*) ) a2

from sc group by c_id

order by c_id;

/*18*/

select * from

(select c_id,s_id,score,rank()over( partition by c_id order by score desc ) rank1

from sc ) b where b.rank1<=3

/*方法二：难点*/

select a.c_id,a.s_id ,a.score,b.score from sc a

left join sc b

on a.c_id=b.c_id and a.score<b.score

group by a.s_id,a.c_id,a.score

having count(b.s_id)<3

order by a.c_id,a.score desc;

select * from sc a where

(select count(*) from sc where c_id=a.c_id and score>a.score)<3

order by a.c_id, a.score desc

/*19*/

select c_id ,count(*) from sc group by c_id;

/*20*/

select s_id from sc group by s_id having count(s_id)=2

/*21*/

select sex,count(sex) from student group by sex

/*22*/

select * from student where sname like'%风%';

/*23*/

select a.s_id,b.countnumber from student a

left join (select sname,sex,count(*) countnumber from student group by sname,sex)b

on a.sname=b.sname

and a.sex=b.sex

where b.countnumber>1;

/*24 to_char()的使用*/

select s_id ,sname from student where to_char(sage,'yyyy')=1990

/*25*/

select c_id,avg(score) avgscore from sc group by c_id order by avgscore desc,c_id;

/*26*/

select a.s_id,a.sname,avg(b.score) avgscore

from student a left join sc b

on a.s_id=b.s_id

group by a.s_id ,a.sname

having avg(b.score)>85;

/*27*/

select a.sname,b.score

from

student a left join sc b

on a.s_id=b.s_id

left join course c

on b.c_id=c.c_id

where c.cname='数学'

and b.score<60;

/*28*/

select a.s_id,a.sname,b.c_id,b.score,c.cname

from student a left join sc b

on a.s_id=b.s_id

left join course c

on b.c_id=c.c_id

order by a.s_id,b.c_id;

/*29%%*/

select a.sname,c.cname,b.score

from (select s_id,c_id,score from sc where score>70) b

left join

course c on b.c_id=c.c_id

left join student a

on b.s_id=a.s_id;

/*30*/

select c_id,count(*)

from sc where  score<60

group by c_id

;

/*31*/

select a.c_id,count(*) from

course a left join sc b

on a.c_id=b.c_id

group by a.c_id

/*32*/

select c_id,count(*)

from

sc group by c_id;

/*33 %%成绩不重复*/

select top 1* from sc

where c_id in (select c_id from course where t_id in (select t_id from teacher where tname='张三'))

order by score desc;

/*34 %%成绩重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩*/

select a.s_id,a.sname,b.score,b.c_id from

(select c_id,max(score) maxscore

from sc

group by c_id

 ) e

 left join

 sc b

 on e.maxscore=b.score and e.c_id=b.c_id

 left join course c

 on b.c_id=c.c_id

 left join teacher d

 on c.t_id=d.t_id

 left join student a

 on a.s_id=b.s_id

 where d.tname='张三';

 /*dense_rank()*/

 select e.s_id,e.c_id,e.score

 from (select s_id ,c_id, score,dense_rank()over(partition by c_id order by s_id)rank1

 from sc) e

 left join course c

 on e.c_id=c.c_id

 left join teacher d

 on c.t_id=d.t_id

 where d.tname='张三' and e.rank1=1;

 select   top 1*

 from (select s_id ,c_id, score,dense_rank()over(partition by c_id order by s_id)rank1

 from sc) e

 left join course c

 on e.c_id=c.c_id

 left join teacher d

 on c.t_id=d.t_id

 where d.tname='张三' ;

 /*不同学生课程相同，分数相同*/

 select a.s_id,a.c_id,a.score,b.s_id,b.c_id,b.score from  sc a left join sc b

 on a.score=b.score and a.s_id>b.s_id and a.c_id=b.c_id

 where b.score is not null;

 /*35,查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩*/

select c.s_id,max(c.c_id) c_id,max(c.score) score from sc c

left join (select s_id ,avg(score)a from sc group by s_id)b

on c.s_id=b.s_id

where c.score=b.a

group by c.s_id

having count(0)=(select count(0) from sc where s_id=c.s_id)

 /*存在三行，如何归并成一行*/

 select a.s_id,a.c_id,b.s_id,b.c_id,a.score,b.score from

 (select s_id,c_id, score,rank()over(partition by s_id order by score) rank1 from sc) a

 inner join

 (select s_id,c_id, score,rank()over(partition by s_id order by score) rank1 from sc)b

 on a.rank1=b.rank1

 and a.s_id=b.s_id

 and a.c_id<b.c_id;

 /*36*/

 select * from

 (select s_id,c_id,score,rank()over(partition by c_id order by score) rank1

 from sc )a  where  a.rank1<3;

 /*37*/

select c_id,count(*)from sc

group by c_id

having count(*)>5;

 /*38*/

 select s_id from sc

 group by s_id

 having count(c_id)>=2

/*39*/

select s_id from sc

group by s_id

having count(c_id)=3;

/*40*/

select s_id,sname,extract(year from date)-extract(year from sage) age from student;