SQL基础题

网上有很多SQL面试50题的讨论，然后我就跟着B站整理了一遍

创建表格和测试数据放在最后

  

1、-- 查询课程编号01比02成绩高的学生学号

select a.s_id,a.s_score,b.s_score 
from(
select s_id,c_id,s_score from Score where c_id = '01'
) as a
join (
select s_id,c_id,s_score from Score where c_id = '02'
) as b
on a.s_id = b.s_id
where a.s_score > b.s_score

 

2、-- 查询平均成绩大于60分的学生学号和平均成绩

select s_id,avg(s_score) 平均成绩
from score GROUP BY s_id
having avg(s_score)>60

 3、-- 查询所有学生的学号、姓名、选课数和总成绩

select s.s_id,s.s_name,count(c_id) 选课数,sum(s_score) 总成绩
from Score g,Student s
where g.s_id = s.s_id
GROUP BY s_id

 这种做法（同inner join，都是取交集）有一点不严谨在于，如果有学生没有选课因此在成绩表里没有他的名字，则不会被统计在内，使用连接且以学生表为主表，按学生学号分组-- 查询所有学生的学号、姓名、选课数和总成绩

select s.s_id,s.s_name,count(g.c_id) 选课数,sum(s_score) 总成绩
from Student s
left join Score g
ON g.s_id = s.s_id
GROUP BY s.s_id

总成绩为空，修改为零

sum(case when s_score is null 
then 0 else s_score 
end) 总成绩

 4、-- 查询姓猴的老师的个数

select count(*)     //select count(distinct  t_name)去重，但是效率降低
from Teacher
where t_name like '猴%'

5、-- 查询没有学过张三老师课程的学生信息

SELECT s_id,s_name from student
where s_id not in
(SELECT s_id 
from course c,score s,teacher t
where c.c_id = s.c_id
and c.t_id = t.t_id
and t.t_name like '张三'   -- 此处用having查询不了
)

 来看看连接的表，有重复栏，这样占存储空间，如果一层层嵌套代码很长

 

(SELECT * from score s
INNER JOIN course c 
ON c.c_id = s.c_id
INNER JOIN teacher t
ON c.t_id = t.t_id
where t.t_name like '张三'
)

7、-- 查询学过编号为01和02课程的学生学号和姓名

SELECT s_id,s_name from student
where s_id in
(
select a.s_id from
(SELECT s_id from score where c_id = '01') a
INNER JOIN 
(SELECT s_id from score where c_id = '02') b
on a.s_id = b.s_id
)

题外话：select LENGTH(null),LENGTH('23kjj')，空的长度也为空

 8、-- 查询所有成绩都小于60分的学生姓名、学号

思路：①找出学生小于60分的课程数 比较 所学的课程数②找出学生的最高分，判断是否小于60

SELECT s.s_id,st.s_name,max(s.s_score)
from score s,student st
where s.s_id = st.s_id
GROUP BY s.s_id
HAVING MAX(s.s_score)<60

 9、-- 查询没有学完所有课程的学生学号、姓名

SELECT st.s_id,st.s_name,count(s.c_id)
from student st
LEFT JOIN score s
on s.s_id = st.s_id
GROUP BY st.s_id
HAVING count(s.c_id) < (SELECT COUNT(*) from course)   -- 这里不能直接写3

  学生表为主表左连接成绩表才不会漏掉学生

10、-- 查询至少有一门课与学号为01的学生课程相同的学生学号、姓名

SELECT st.s_id,st.s_name
FROM student st,score sc
where st.s_id = sc.s_id
AND sc.c_id in
(SELECT c_id from score
where s_id = '01'
)AND sc.s_id !='01'
GROUP BY st.s_id

11、-- 与学号01的学生课程完全相同的学生学号、姓名

-- 1选出课程数一样的学生

-- 2判断完全一致就去找其中一个不一致

SELECT * FROM student 
where s_id in (
SELECT s_id FROM score where s_id != '01'
GROUP BY s_id having count(*) = (
SELECT COUNT(c_id) FROM score where s_id = '01')
)AND s_id not in(
SELECT s_id from score WHERE c_id not in (
SELECT c_id from score where s_id = '01'))

  

12、-- 查询两门课及其以上都不及格的学生信息及平均成绩

-- 1找出不及格的成绩按学生分组，计算数量>=2，的学号

-- 2连接学生表和成绩表，筛选符合条件1的学生

SELECT st.s_id,st.s_name,avg(sc.s_score)
FROM student st INNER JOIN score sc
on st.s_id = sc.s_id
WHERE st.s_id in(
SELECT s_id
from score
where s_score < 60
GROUP BY s_id
having count(c_id) >= 2)
GROUP BY st.s_id

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

-- 1连接student表和score表

-- 2筛选01课程且成绩小于60，分数降序

SELECT a.*,c_id,s_score
FROM student a INNER JOIN score b
on a.s_id = b.s_id
WHERE c_id = '01'
and s_score < 60
ORDER BY s_score DESC    

  

14、-- case when按平均成绩从高到低显示所有学生的所有课程成绩和平均成绩

SELECT s_id,
MAX(CASE when c_id='01' THEN s_score ELSE NULL END) '语文',
MIN(CASE when c_id='02' THEN s_score ELSE NULL END) '数学',
SUM(CASE when c_id='03' THEN s_score ELSE NULL END) '英语',
MAX(CASE when c_id='04' THEN s_score ELSE NULL END) '化学',
avg(s_score)平均成绩
FROM score 
GROUP BY s_id
ORDER BY avg(s_score) DESC

  

15、-- 查询各科成绩最高分最低分平均分

-- 显示课程id、课程名、最高最低平均，及格>=60、中等70-80、优良80-90、优秀率>=90

SELECT s.c_id '课程编号',c_name '课程名',
MAX(s.s_score)最高分,MIN(s.s_score)最低分,AVG(s.s_score)平均分,
SUM(CASE WHEN s.s_score<60 THEN 1 else 0 end)/COUNT(s_id) '不及格率',
SUM(CASE WHEN s.s_score>=60 and s.s_score<70 THEN 1 else 0 end)/COUNT(s_id) '及格率',
SUM(CASE WHEN s.s_score>=70 and s.s_score<80 THEN 1 else 0 end)/COUNT(s_id) '中等率',
SUM(CASE WHEN s.s_score>=80 and s.s_score<90 THEN 1 else 0 end)/COUNT(s_id) '优良率',
SUM(CASE WHEN s.s_score>=90 THEN 1 else 0 end)/COUNT(s_id) '优秀率'
from score s,course c
WHERE s.c_id = c.c_id
GROUP BY c.c_id

  

16、-- 按各科成绩进行排序，显示排名（row_number）

SELECT st.s_name,c.c_name,sc.s_score,
rank() over(PARTITION BY c_name order by s_score desc) '排名'
FROM course c 
INNER JOIN score sc on sc.c_id = c.c_id
INNER JOIN student st on st.s_id = sc.s_id

-- row_number() 1234不重复连续排序

 

-- dense_rank() 1223连续排序

 

-- rank() 1224跳跃排序

 

  

17、查询学生总成绩并进行排名

SELECT s_id,sum(s_score) total,
dense_rank() over(ORDER BY SUM(s_score) DESC) ranking
from score
GROUP BY s_id

-- 查询学生平均成绩及其名词

  

18、-- 查询不同老师所教课程平均分从高到低显示

SELECT t.t_name 老师,c.c_name 课程,AVG(s.s_score) 平均分
FROM teacher t INNER JOIN course c on t.t_id = c.t_id
inner join score s on s.c_id = c.c_id 
GROUP BY t.t_name,c.c_name
ORDER BY 平均分 DESC

20、-- 查询所有课程的成绩第二名到第三名学生信息和成绩

SELECT *  
FROM(SELECT st.*,c.c_name,sc.s_score,
rank() over(PARTITION BY c_name order by s_score desc) m
FROM course c 
INNER JOIN score sc on sc.c_id = c.c_id
INNER JOIN student st on st.s_id = sc.s_id
WHERE m in (2,3)

  题外话：之前用了一个很麻烦的方法（嵌套多次）这里就提一下遇到的问题需要注意

[Err] 1248 - Every derived table must have its own alias每个派生出来的表都必须有一个自己的别名

  

21、-- 统计各分数段人数

SELECT c_id 课程,
SUM(case when s_score<=60 then 1 else 0 end) as '(<=60]',
SUM(case when s_score>60 and s_score<=70 then 1 else 0 end) as '[70,60)',
COUNT(case when s_score>70 and s_score<=85 then 1 else NULL end) as '[85,70)',
COUNT(case when s_score>85 then 1 else NULL end) as '[>85)'
FROM score GROUP BY c_id

**这里要注意sum和count的区别

22、-- 查询每门课程的学生数

SELECT c_id,COUNT(*)
from score
GROUP BY c_id

23、-- 查询男生、女生人数

SELECT s_sex,COUNT(*)
from student
GROUP BY s_sex

SELECT 
count(case when s_sex = '男' then 1 else null end) 'boy'
,SUM(case when s_sex = '女' then 1 else 0 end) as 'girl'  
from student    

 **注意count和sum的使用方法

24、-- 查询1990年出生的学生信息

SELECT *
from student
where YEAR(s_birth)=1990

时间格式YYYY-MM-DD、YYYY/MM/DD、YYYYMMMDD、YYMMDD

时间函数year、month、day、curdate当前年月日、curtime当前时分秒、now

25、-- 查询学生年龄

SELECT s_name,
ceil(DATEDIFF(CURDATE(),s_birth)/365) 虚岁,
floor(DATEDIFF(CURDATE(),s_birth)/365) 周岁
FROM student

 26、-- 查询下个月过生日的学生信息

SELECT * from student
WHERE month(s_birth) = MOD(month(CURDATE()),12)+1
-- 假设本月为12月

以下为建表、插入测试数据代码

create table Student(
s_id varchar(20) PRIMARY KEY,
s_name varchar(20) not null DEFAULT '',
s_birth VARCHAR(20),
s_sex VARCHAR(10)
);

create table Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score int(3),
primary key(s_id,c_id) 
);

create table Course(
c_id varchar(20) PRIMARY KEY,
c_name VARCHAR(20),
t_id VARCHAR(20)
);

create table Teacher(
t_id VARCHAR(20) PRIMARY KEY,
t_name 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' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

这里有个问题我一直还耿耿于怀，是做题时想到的：查询只学了两门课程的学生学号和所选课程编号

SELECT s_id ,c_id FROM score
where s_id in(
SELECT s_id FROM score
GROUP BY s_id HAVING COUNT(*)=2
)

 结果是这样的，但是我想要的显示是这样的，应该是挺容易的但是我脑子最近就是浆糊！！

s_id	1	2
05	01	02
06	01	03
07	02	03

SELECT s.s_id,st.s_name 
,max(case when c.c_id='01' then c.c_name else null END) '1'
,max(case when c.c_id='02' then c.c_name else null END) '2'
,max(case when c.c_id='03' then c.c_name else null END) '3'
FROM score s
INNER JOIN course c on s.c_id=c.c_id
INNER JOIN student st on st.s_id=s.s_id
where s.s_id in(
SELECT s_id FROM score
GROUP BY s_id HAVING COUNT(*)=2
)GROUP BY s.s_id

好了我又挣扎了一番

求解！忘留言