my sql 成绩查询面试题集锦（一）

系统表格设置如下：

1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名

select a.s_id as s_id,a.s_name as s_name,b.c_id as c_id,b.scores as score  from test.student as a,test.score as b
where a.s_id=b.s_id
and a.s_id not in (
select distinct test.score.s_id as s_id from test.score
where test.score.scores<80
) 

显示结果：

注：最后的括号后面加 “c”会报错

其他思路：

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
select name from table group by name having min(fenshu)>80

2.删除重复的行，完全重复，包括主键

第一步 先创建一个复制表：

create table test.score_copy like  test.score

第二步 将要表格信息去重后插入新表：

insert into test.score_copy(s_id,
                            c_id,
                            scores)
select * from test.score
group by s_id,c_id,scores

第三步 删除旧表：

drop table test.score

第四步 将新表改名：

alter table test.score_copy rename test.score

通过以上操作就完成了

还有另一种情况是：删除除了自动编号不同, 其他都相同的学生冗余信息

 delete tablename where 自动编号 not in(select min( 自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

3.查询"01"课程比"02"课程成绩高的学生的姓名、学号及课程分数

select s.s_name ,t.s_id,t.score_1,t.score_2 from test.student as s
join 
(select a.s_id as s_id,a.scores as score_1,b.scores as score_2 from test.score as a,test.score as b
where a.s_id=b.s_id and a.c_id=1 and b.c_id=2 and a.scores>b.scores) t
on s.s_id=t.s_id

运行结果如下：

语文成绩比数学成绩高的情况：

select a.s_id,a.yscore  as "语文成绩",b.sscore as "数学成绩" from
(select test.score.s_id as s_id ,test.score.scores as yscore from test.score,test.course where test.score.c_id= test.course.c_id and test.course.c_name="语文" ) a,
(select test.score.s_id as s_id ,test.score.scores as sscore from test.score,test.course where test.score.c_id= test.course.c_id and test.course.c_name="数学" ) b
where a.s_id=b.s_id and a.yscore>b.sscore

显示结果：

4.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select s.s_id, st.s_name, avg(s.scores) as score_avg from test.score as s 
join test.student as st  on  s.s_id=st.s_id
group by s.s_id
having score_avg>60

显示结果：

5.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

select s.s_id, st.s_name, avg(s.scores) as score_avg from test.score as s 
join test.student as st  on  s.s_id=st.s_id
group by s.s_id
having score_avg<60

显示结果：

6.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select s.s_id, st.s_name, count(s.scores) as score_cn ,sum(s.scores) as score_sum from test.score as s 
join test.student as st  on  s.s_id=st.s_id
group by s.s_id

显示结果：

``
7.查询"李"姓老师的数量

SELECT count(t.t_name) as num FROM test.teacher as t
where t.t_name like "李%"

显示结果：

8.查询学过"雷军"老师授课的同学的信息

子查询：

select * from test.student as st 
where st.s_id in(
   select s.s_id from  test.score as s 
   where s.c_id in (
        select c.c_id from test.course as c,test.teacher as t
  where c.t_id=t.t_id and t.t_name="雷军" ))

多表关联：

select st.s_id,st.s_name,st.s_birth,st.s_sex from test.student as st
join test.score as s  on st.s_id=s.s_id
join test.course as c on s.c_id=c.c_id 
join test.teacher as t on c.t_id=t.t_id and t.t_name="雷军"

如果去重，后面加：group by st.s_id,st.s_name,st.s_birth,st.s_sex
显示结果：

9.查询没学过"雷军"老师授课的同学的信息

select * from test.student
where test.student.s_id not in (
     select distinct st.s_id from test.student as st
     join test.score as s  on st.s_id=s.s_id
     join test.course as c on s.c_id=c.c_id 
     join test.teacher as t on c.t_id=t.t_id and t.t_name="雷军")

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

select * from test.student as st
where st.s_id in