创建学生表

create table stu(
s_id varchar(10) primary key,
s_name varchar(10) not null,
s_age date not null,
s_sex varchar(10) default ‘未知’
);

创建课程表

create table co(
c_id varchar(10) primary key,
c_name varchar(10) not null,
t_id varchar(10) not null
);

创建教师表

create table te(
t_id varchar(10) primary key,
t_name varchar(10) not null
);

创建成绩表

create table sc(
s_id varchar(10) ,
c_id varchar(10) not null,
score int not null
);

明确表结构间的关系后,请完成以下题目,解题方法多样,答案仅供参考

1 查询学生编号为01的学生成绩总分、平均分、最高分、最低分
select s_id,sum(score),avg(score),max(score),min(score) from sc where s_id=“01”;

2 查询名字中含有“风”字的学生信息
select * from stu where s_name like “%风%”;

3 查询每位同学的平均成绩，并按分数降序、学生编号升序排列
select s_id,avg(score) as 分数 from sc
group by s_id
order by 分数 desc,s_id;

4 查询至少两门课程及格的学生学号
select s_id from sc
where score>=60
group by s_id
having count(*)>1;

5 查询仅学过编号为“01”并且也学过编号为“02”的课程的学生编号：
select s_id from sc
group by s_id
having sum(c_id=“01”)=1 and sum(c_id=“02”)=1 and count(c_id)=2 ;

6 每门课程的第二名和第三名
方法1
select * from sc sc1 where
(select sum(score>sc1.score) from sc where c_id=sc1.c_id) in(1,2);

方法2 使用开窗函数
select * from
(select *,dense_rank() over(partition by c_id order by score desc) 排名 from sc) t
where 排名<=2;

7 查询没学过“张三”老师授课的同学的信息
select stu.* from stu
left join sc on stu.s_id=sc.s_id
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
group by stu.s_id
having ifnull(group_concat(t_name),0) not like “%张三%”;

8 查询“01”课程比“02”课程成绩高的学生的信息及课程分数
select stu.*,
sum((c_id=“01”)*score) 01分数,
sum((c_id=“02”)score) 02分数
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having (01分数>02分数)(sum(c_id in (“01”,“02”))=2);

9 查询学生的总成绩并进行排名
select s_id,sum(score) 总成绩,dense_rank() over(order by sum(score) desc) 排名
from sc
group by s_id;

以上题目你都做对了吗?简单总结下,作为一名数据分析师,玩转数据库查询语言至关重要。
精选Mysql笔试题助你面试成功
另外一个重要知识点,多表查询逻辑
1.确定查询的信息在哪几张表
2.确定表和表之间的对应关系和主附关系
3.确定表和表之间的连接条件
4.一表作为主表可以保证维度的完整性，多表作为主表可以保证度量的准确性。没有明确表示需要保证维度完整性的情况下，优先保证度量的准确性，所以将度量值所在的表作为主表
5.度量字段通常存在于多表中，因此通常情况下可以将多表作为主表进行外连接

精选Mysql笔试题助你面试成功

创建学生表

创建课程表

创建教师表

创建成绩表

相关推荐