sql面试语句编写

#--------------------------------------------------------------------------------------------------

MYSQL


#班级信息表
create table tb_class(
        id          bigint(20)  not null  auto_increment,
        class_no    varchar(40) not null    comment '班级', 
        st_no       varchar(40) not null    comment '学生号',
        name        varchar(40) not null    comment '学生姓名',
        crdate      timestamp   not null default current_timestamp on update current_timestamp,
        primary key (id)
)engine=innodb auto_increment=1 default charset=utf8  comment='班级信息表'

select * from tb_class;

insert into tb_class(class_no,st_no,name) values('c1','st1','sam');
insert into tb_class(class_no,st_no,name) values('c1','st2','lucy');
insert into tb_class(class_no,st_no,name) values('c2','st3','jean');
insert into tb_class(class_no,st_no,name) values('c2','st4','lock');
insert into tb_class(class_no,st_no,name) values('c2','st5','lily');

#--------------------------------------------------------------------------------------------------

#成绩表
create table tb_score(
        id          bigint(20)  not null  auto_increment,
        st_no       varchar(40) not null    comment '学生号',
        subject_no  varchar(40) not null    comment '课程编号',
        score       decimal(10)  not null   comment  '成绩',
        crdate      timestamp   not null default current_timestamp on update current_timestamp,
        primary key (id)
)engine=innodb auto_increment=1 default charset=utf8  comment='成绩表'

select * from tb_score;

insert into tb_score(st_no,subject_no,score) values('st1','1',72);
insert into tb_score(st_no,subject_no,score) values('st1','2',89);
insert into tb_score(st_no,subject_no,score) values('st1','3',78);
insert into tb_score(st_no,subject_no,score) values('st2','1',55);
insert into tb_score(st_no,subject_no,score) values('st2','2',82);
insert into tb_score(st_no,subject_no,score) values('st2','3',93);
insert into tb_score(st_no,subject_no,score) values('st3','1',87);
insert into tb_score(st_no,subject_no,score) values('st3','3',89);
insert into tb_score(st_no,subject_no,score) values('st4','1',98);
insert into tb_score(st_no,subject_no,score) values('st4','2',94);
insert into tb_score(st_no,subject_no,score) values('st5','2',63);
insert into tb_score(st_no,subject_no,score) values('st5','3',90);

#--------------------------------------------------------------------------------------------------

#课程表
create table tb_subject(
        id            bigint(20)   not null  auto_increment,
        subject_no    varchar(40)  not null    comment '课程编号',
        subject_name  varchar(40)  not null    comment  '课程名称',
        crdate      timestamp   not null default current_timestamp on update current_timestamp,
        primary key (id)
)engine=innodb auto_increment=1 default charset=utf8  comment='课程表'

select * from tb_subject;

insert into tb_subject(subject_no,subject_name) values(1,'语文');
insert into tb_subject(subject_no,subject_name) values(2,'数学');
insert into tb_subject(subject_no,subject_name) values(3,'英语');

#--------------------------------------------------------------------------------------------------

#成绩信息展示
select c.name,c.class_no,su.subject_name,s.score from tb_class c right join tb_score s on c.st_no=s.st_no 
right join tb_subject su on s.subject_no=su.subject_no order by c.class_no;

#1.(查询每个班级各科成绩总和)
select c.class_no,su.subject_name ,sum(s.score)from tb_class c inner join tb_score  s  on  c.st_no=s.st_no 
inner join tb_subject su on s.subject_no=su.subject_no  group by s.subject_no,c.class_no order by c.class_no

# 另外一种写法
select su.subject_name as '课程',
sum(case when c.class_no = 'c1' then score else 0 end) as 'c1',
sum(case when c.class_no = 'c2' then score else 0 end) as 'c2'
from tb_class c inner join tb_score  s  on  c.st_no=s.st_no 
inner join tb_subject su on s.subject_no=su.subject_no group by s.subject_no;

#2.(查询每个班级语文成绩大于60的人数)
select c.class_no,count(1) from tb_class c left join tb_score  n  on  c.st_no=n.st_no 
right join tb_subject su on n.subject_no=su.subject_no
where su.subject_name='语文' and n.score> 60  group by c.class_no

#3.(查询 语文成绩大于数学成绩 的姓名和归宿班级)
select distinct a.name,a.class_no,a.score from 
(
    select c.name,c.class_no,su.subject_name,s.score from tb_class c right join tb_score s on c.st_no=s.st_no 
    right join tb_subject su on s.subject_no=su.subject_no order by c.class_no
) a,
(
    select c.name,c.class_no,su.subject_name,s.score from tb_class c right join tb_score s on c.st_no=s.st_no 
    right join tb_subject su on s.subject_no=su.subject_no order by c.class_no
) b
where a.name=b.name
and a.subject_name='语文'
and b.subject_name='数学'
and a.score>b.score

#4.统计总分数大于180分的学生人数
select count(1) from (
     select c.name, sum(s.score) as score from tb_class c inner join tb_score s on c.st_no=s.st_no group by c.name
) sc where sc.score>180