软件测试sql题

1.
学生表(学生id，姓名，性别，分数)student（s_id，name,sex,score）
班级表（班级id，班级名称）class（c_id，c_name）
学生班级表（班级id，学生id）student_class(s_id,c_id)
1.查询一班得分在80分以上的学生
2.查询所有班级的名称，和所有版中女生人数和女生的平均分
题解:
1.select * from student where score> 80 and 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 classes c
innerjoin student_class sc on sc.c_id=c.c_id
innerjoin students s on s.s_id=sc.s_idwhere s.sex= ‘女’ group by c.c_name

2.一道SQL语句面试题，关于group by表内容：
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

答案：

(1) select date , sum( case when result = “win” then 1 else 0 end ) as “win”,
sum(case when result = “lose” then 1 else 0 end) as “lose” from info group by date;
(2) select a.date, a.result as win, b.result as lose from
　　(select date, count (result) as result from info where result = “win” group by date) as a
　　join
　　(select date, count (result) as result from info where result = “lose” group by date) as b

　　on a.date = b.date;2.学生成绩表(stuscore)：
3.表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列
select ( case when a > b then a else b end ), (case when b > c then b else c end ) from table;
4.

有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：?
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。?
显示格式：?
语文 数学 英语?

及格 优秀 不及格?

select
(case when 语文>=80 then ‘优秀’
when 语文>=60 then ‘及格’
else ‘不及格’) as 语文,
(case when 数学>=80 then ‘优秀’
when 数学>=60 then ‘及格’
else ‘不及格’) as 数学,
(case when 英语>=80 then ‘优秀’
when 英语>=60 then ‘及格’
else ‘不及格’) as 英语,
from table
5.姓名：name 课程：subject 分数：score 学号：stuid
张三 数学 89 1

张三 语文 80 1

张三 英语 70 1

李四 数学 90 2

李四 语文 70 2

李四 英语 80 2

题解：
1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

答案：select name,sum(score) as allscore from stuscore group by name order by allscore

2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)

答案：select distinct t1.name,t1.stuid,t2.allscore from stuscore t1,( select stuid,sum(score) as allscore from stuscore group by stuid)t2where t1.stuid=t2.stuidorder by t2.allscore desc

3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

答案：select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,(select stuid,max(score) as maxscore from stuscore group by stuid) t2where t1.stuid=t2.stuid and t1.score=t2.maxscore

4.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

答案：select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,(select stuid,avg(score) as avgscore from stuscore group by stuid) t2where t1.stuid=t2.stuid

5.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

答案：select t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,(select subject,max(score) as maxscore from stuscore group by subject) t2where t1.subject=t2.subject and t1.score=t2.maxscore

6.列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

答案：select distinct t1.* from stuscore t1 where t1.id in (select top 2 stuscore.id from stuscore where subject = t1.subject order by score desc) order by t1.subject

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 desc

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

ms 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