sql50题

create table student(
    s_id varchar(10),
    s_name varchar(20),
    s_age date,
    s_sex varchar(10)
);

create table course(
    c_id varchar(10),
    c_name varchar(20),
    t_id varchar(10)
);


create table teacher (
t_id varchar(10),
t_name varchar(20)
);

create table score (
    s_id varchar(10),
    c_id varchar(10),
    score varchar(10)
);

insert into student (s_id, s_name, s_age, s_sex)
values  (‘01‘ , ‘赵雷‘ , ‘1990-01-01‘ , ‘男‘),
        (‘02‘ , ‘钱电‘ , ‘1990-12-21‘ , ‘男‘),
        (‘03‘ , ‘孙风‘ , ‘1990-05-20‘ , ‘男‘),
        (‘04‘ , ‘李云‘ , ‘1990-08-06‘ , ‘男‘),
        (‘05‘ , ‘周梅‘ , ‘1991-12-01‘ , ‘女‘),
        (‘06‘ , ‘吴兰‘ , ‘1992-03-01‘ , ‘女‘),
        (‘07‘ , ‘郑竹‘ , ‘1989-07-01‘ , ‘女‘),
        (‘08‘ , ‘王菊‘ , ‘1990-01-20‘ , ‘女‘);

insert into course (c_id, c_name, t_id)
values  (‘01‘ , ‘语文‘ , ‘02‘),
        (‘02‘ , ‘数学‘ , ‘01‘),
        (‘03‘ , ‘英语‘ , ‘03‘);

insert into teacher (t_id, t_name)
values  (‘01‘ , ‘张三‘),
        (‘02‘ , ‘李四‘),
        (‘03‘ , ‘王五‘);

insert into score (s_id, c_id, score)
values  (‘01‘ , ‘01‘ , 80),
        (‘01‘ , ‘02‘ , 90),
        (‘01‘ , ‘03‘ , 99),
        (‘02‘ , ‘01‘ , 70),
        (‘02‘ , ‘02‘ , 60),
        (‘02‘ , ‘03‘ , 80),
        (‘03‘ , ‘01‘ , 80),
        (‘03‘ , ‘02‘ , 80),
        (‘03‘ , ‘03‘ , 80),
        (‘04‘ , ‘01‘ , 50),
        (‘04‘ , ‘02‘ , 30),
        (‘04‘ , ‘03‘ , 20),
        (‘05‘ , ‘01‘ , 76),
        (‘05‘ , ‘02‘ , 87),
        (‘06‘ , ‘01‘ , 31),
        (‘06‘ , ‘03‘ , 34),
        (‘07‘ , ‘02‘ , 89),
        (‘07‘ , ‘03‘ , 98);

select*from(
    select d.*,c.score from
        (select a.s_id,b.score chinese from 
            (select distinct s_id from score) a
        left join score b
            on a.s_id=b.s_id
        where b.c_id=‘01‘) d
    left join score c
        on c.s_id=d.s_id
        where c.c_id=‘02‘    
    )e 
where e.chinese>e.score    

select a.s_id,a.score,b.score from
    (select  s_id,c_id,score from score where c_id=‘01‘)a
inner join
    (select s_id,c_id,score from score where c_id=‘02‘)b
on a.s_id=b.s_id
where a.score>b.score

select * from(
    select s_id,avg(score) avgScore from score group by s_id)a 
where a.avgScore>60

select s_id,avg(score) from score group by s_id having avg(score)>60