SQL面试题之一

​查询数学比语文分数相同的学生

select * from student s 
left join score c on s.s_id = c.s_id 
and c.c_id =(select c_id from course where c_name = '语文') 
left join score f on s.s_id = f.s_id 
and f.c_id =(select c_id from course where c_name = '数学') 
where c.s_score = f.s_score;

查看 score 表结构：

desc score;

用一条语句分别查询：语文老师的姓名，

 select * from teacher t left join course c on t.t_id=c.t_id where c_name='语文'

1990年出生的学生名单，

select * from student where year(s_birth)=1990;
或select * from student where s_birth>='1990-01-01' and s_birth<='1990-12-31';
或 select * from student where s_birth between '1990-01-01' and '1990-12-31';

年龄最小和年龄最大的学生编号、姓名、性别

select * from student where s_birth=(select max(s_birth) from student);

select * from student where s_birth=(select min(s_birth) from student);

分数等于80的学生信息

select * from student where s_id in (select s_id from score where s_score=80);（多于一行用in）

查询课程名称为"数学"，且分数低于60的学生姓名和分数

select s_name,s_score from student s left join score c on s.s_id=c.s_id left join course f on f.c_id=c.c_id where c_name='数学'and s_score<60 ;

查看学生成绩表 （一个学生一行数据）
    结果集格式： 学生编码、学生姓名、性别、选课门数、总成绩

select s.s_id,s_name,s_sex,sum(s_score), count(c.c_id) from student s 
left join score c on s.s_id=c.s_id 
left join course k on k.c_id=c.c_id 
group by s.s_id,s_name,s_sex;

 select s.s_id,s_name,s_sex,sum(s_score), count(c.c_id) from student s 
 left join score c on s.s_id=c.s_id 
 left join course k on k.c_id=c.c_id 
 group by s.s_id,s_name,s_sex having count(c.c_id)=2;

分别查询男生、女生成绩表
    结果集格式： 学生编码、学生姓名、性别、选课门数、总成绩

select s.s_id,s_name,s_sex,count(c.c_id),sum(s_score) 
from student s
left join score c on s.s_id=c.s_id 
group by s_id,s_name s_sex;

只查男性

select s.s_id,s_name,s_sex,count(c.c_id),sum(s_score) 
from student s 
left join score c on s.s_id=c.s_id 
group by s_id,s_name having s_sex='男';

查询各科的最高分、最低分、平均分 
    结果集格式： 科目代码，科目名称，老师姓名，最高分、最低分、平均分

select max(s_score),min(s_score),avg(s_score),t_name,c_name 
from  score c 
left join course k on c.c_id=k.c_id
left join teacher t on t.t_id=k.t_id 
group by c_name,t_name;

查询数学比英语分低的学生

select * 
from student s 
left join score c on s.s_id=c.s_id and c.c_id=(select c_id from course where c_name='英语') 
left join score f on s.s_id=f.s_id and f.c_id=(select c_id from course where c_name='数学')
where c.s_score<f.s_score;

查询数学高于平均分的学生

 select s.s_id,s_name,c_name,s_score
 from student s left join score c on s.s_id=c.s_id 
 left join course k on k.c_id=c.c_id 
 where s_score>(select avg(s_score)from score) and c_name='数学';

查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 

select * from student s3 
 where s_id in (select s_id from score s2  where c_id=1 and s_id in (SELECT s_id from score s where c_id =2)); 

或

select * from student 
where s_id in  (select s_id from   ( select s.s_id,count(*) cnt from student s left join score c on s.s_id=c.s_id left join course k on k.c_id=c.c_id where c.c_id=1 or c.c_id=3 group by s.s_id having cnt>1) G  ); 

或  

select * from student s left join score c on s.s_id=c.s_id and c.c_id=(select c_id from course where c_name='英语') left join score f on s.s_id=f.s_id and f.c_id=(select c_id from course where c_name='数学') where c.s_id  is not null and f.s_id is not null;

查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select * from score where c_id=1 and  s_id not in ( select s_id from score where c_id=2);

查询没有学全所有课程的同学的信息

select * from student 
where s_id not in (select s_id from (select s_id,count(c_id) cnt fromscore group by s_id having count(c_id)=3)A);

检索"01"课程分数小于60，按分数降序排列的学生信息

 select s.s_id,s_name,s_sex,s_score from student s left join score c
on s.s_id=c.s_id left join course k on c.c_id=k.c_id where c.c_id=1 and s_score<60 order by c.s_score desc

查询各科成绩最高分、最低分和平均分：

select avg(s_score),max(s_score),min(s_score),c_name from teacher t left join course c on t.t_id=c.t_id left join score s on s.c_id=c.c_id group by c_name;

​查询各学生的年龄

select s_id,s_name,2021-year(s_birth) age from student;

查询生日离今天最近的学生

select * from student where s_birth=(select max(s_birth) from student);

 距离1990-7-1最近的人

select * from student where abs(datediff('1990-07-01',s_birth))=(select min(abs(datediff('1990-07-01',s_birth))) from student);

查询这个月过生日的学生

select * from student where month(s_birth) =8;