SQL常见⾯试题⽬（多表联查）
⼀.学⽣表(学⽣id，姓名，性别，分数)student（s_id，name,sex,score）
班级表（班级id，班级名称）class（c_id，c_name）
学⽣班级表（班级id，学⽣id）student_class(s_id,c_id)(考察三表联查)
1.查询⼀班得分在80分以上的学⽣。
答：select * from student s where score > 80 and s.s_id in (select sid from student_class where c_id=(select c_id from class where c_name='⼀班' )
2.查询所有班级的名称，和所有班中⼥⽣⼈数和⼥⽣的平均分
答：select c.c_name,⼥⽣⼈数=sum(s.s_id),⼥⽣平均分=avg(s.score) from student  inner join student_class sc on s.s_id =
sc.s_id  and inner join class c on sc.c_id = s.c_id where s.sex='⼥'
⼆.⼀道SQL语句⾯试题，关于group by表内容：(考察group by 和 case when )
info 表
date result
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose
如果要⽣成下列结果, 该如何写sql语句?
win lose
2005-05-09 2 2
2005-05-10 1 2
答：select date,sum(case when result='win' then 1 else 0 end) as 'win' ,sum(case when result='lose' then 1 else o end)as
'lose' from info group by date
select distinct from三.表中有A B C三列,⽤SQL语句实现：当A列⼤于B列时选择A列否则选择B列，当B列⼤于C列时选择B列否则选择C列(考察case when) (case when xxx then xxx else xxxx  end)
答：select (case when A >B then A else B),(case when B >C then B else  C) from table;
四.有⼀张表，⾥⾯有3个字段：语⽂，数学，英语。其中有3条记录分别表⽰语⽂70分，数学80分，英语58分，请⽤⼀条sql语句查询出这三条记录并按以下条件显⽰出来（并写出您的思路）(考察case when)：
⼤于或等于80表⽰优秀，⼤于或等于60表⽰及格，⼩于60分表⽰不及格。
显⽰格式：语⽂ 数学 英语
及格 优秀 不及格
答：select (case when '语⽂'>=80 then '优秀' when '语⽂' >=60 then '及格' else  '不及格' end) as '语⽂' ,(case when '数学'>=80
then '优秀' when '数学' >=60 then '及格' else '不及格' end) as '数学'，(case when '英语'>=80 then '优秀 when '英语' >=60 then '及格' else '不及格' end)as '英语' from table
五.姓名：name 课程：subject 分数：score 学号：stuid  ，表名：stuscore
张三                数学            89                1
张三                语⽂            80                1
张三                英语            70                1
李四                数学            90                2
李四                语⽂            70                2
李四                英语            80                2
题⽬：1.计算每个⼈的总成绩并排名(要求显⽰字段：姓名，总成绩)(根据姓名进⾏分组)
答：select sum(score) as allscore,name from stuscore group by name order by allscore ;
2.计算每个⼈的总成绩并排名(要求显⽰字段: 学号，姓名，总成绩)(根据学号进⾏分组)
答：SELECT DISTINCT stuid,NAME,SUM(score) AS allscore FROM stuscore GROUP BY stuid ORDER BY allscore ；
3.计算每个⼈单科的最⾼成绩(要求显⽰字段: 学号，姓名，课程，最⾼成绩)
答：select stuid,name,subject ,max(score) from stuscore group by name
4.计算每个⼈的平均成绩（要求显⽰字段: 学号，姓名，平均成绩）
答：select stuid,name,avg(score) from  stuscore group by name
5.列出各门课程成绩最好的学⽣(要求显⽰字段: 学号，姓名，科⽬，成绩)
答：SELECT t1.stuid,t1.name,t1.subject,t2.maxscore FROM stuscore t1,(SELECT SUBJECT,MAX(score) AS maxscore FROM stuscore GROUP BY SUBJECT) t2 WHERE t1.subject =
t2.subject AND t1.score=t2.maxscore
6.列出各门课程成绩最好的两位学⽣(要求显⽰字段: 学号，姓名，科⽬，成绩)
答：select
所以剩下的我觉得等我休息⼀会⼉再整理....................
7.统计如下：学号 姓名 语⽂ 数学 英语 总分 平均分
答案：select stuid as 学号,name as 姓名,sum(case when subject=’语⽂’ then score else 0 end) as 语⽂,sum(case when subject=’数学’ then score else 0 end) as 数学,sum(case when subject=’英语’ then score else 0 end) as 英语,sum(score) as 总分,(sum(score)/count(*)) as 平均分from stuscoregroup
by stuid,name order by 总分desc
8．列出各门课程的平均成绩（要求显⽰字段：课程，平均成绩）
答案：select subject,avg(score) as avgscore from stuscoregroup by subject
9．列出数学成绩的排名（要求显⽰字段：学号，姓名，成绩，排名）
答案：
declare @tmp table(pm int,name varchar(50),score int,stuid int)
insert into @tmp select null,name,score,stuid from stuscore where subject=’数学’ order by score des
c
declare @id int
set @id=0;
update @tmp set @id=@id+1,pm=@id
select * from @tmp
oracle:
select DENSE_RANK () OVER(order by score desc) as row,name,subject,score,stuid from stuscore where subject=’数
学’order by score desc
my sql(最佳选择)
select (select count(*) from stuscore t1 where subject =’数学’ and t1.score>t2.score)+1 as row ,stuid,name,score from stuscore t2 where subject =’数学’ order by score desc
10．列出数学成绩在2-3名的学⽣（要求显⽰字段：学号，姓名，科⽬，成绩）
答案：select t3.* from(select top 2 t2.* from (select top 3 name,subject,score,stuid from stuscore where subject=’数
学’order by score desc) t2 order by t2.score) t3 order by t3.score desc
11．求出李四的数学成绩的排名
答案：
declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp select null,name,score,stuid from stuscore where subject=’数学’ order by score descdeclare @id intset @id=0;update @tmp set @id=@id+1,pm=@idselect * from
@tmp where name=’李四’
12．统计如下：课程 不及格（0-59）个 良（60-80）个 优（81-100）个
答案：select subject, (select count(*) from stuscore where score<60 and subject=t1.subject) as 不及
格,(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良,(select count(*) from stuscore where score >80 and subject=t1.subject) as 优from stuscore t1 group by subject
13．统计如下：数学：张三(50分)，李四(90分)，王五(90分)，赵六(76分)
答案：
declare @s varchar(1000)set @s=”select @s =@s+’,’+name+'(‘+convert(varchar(10),score)+’分)’ from stuscore where subject=’数学’ set @s=stuff(@s,1,1,”)print ‘数学:’+@s
14.计算科科及格的⼈的平均成绩
答案： select distinct t1.stuid,t2.avgscore from stuscore t1,(select stuid,avg(score) as avgscore from stuscore group by stuid ) t2,(select stuid from stuscore where score<60 group by stuid) t3 where t1.stuid=t2.stuid and t1.stuid!=t3.stuid;
select name,avg(score) as avgscore from stuscore s where (select sum(case when i.score>=60 then 1 else 0 end) from stuscore i where i.name= s.name)=3 group by name
1. ⽤⼀条SQL 语句 查询出每门课都⼤于80 分的学⽣姓名
name  kecheng  fenshu
张三      语⽂      81
张三      数学      75
李四      语⽂      76
李四      数学      90
王五      语⽂      81
王五      数学      100
王五      英语      90
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。