sql练习题

1.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：

大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。

显示格式：

语文 数学 英语

及格 优秀 不及格

SELECT * from score a where a.语文=70;
SELECT * from score a where a.数学=80;
SELECT * from score a where a.英语=58;

select a.语文,a.数学,a.英语 from score a where a.语文=70 or a.数学=80 or a.英语=58
select * from score a where a.语文=70 or a.数学=80 or a.英语=58;
SELECT * FROM score WHERE 语文=70 OR 数学=80 OR 英语=58;

SELECT 
(CASE WHEN 语文>=80 THEN '优秀' WHEN 语文>=60 AND 语文<80 THEN '及格' ELSE '不及格' END)AS 语文,
(CASE WHEN 数学>=80 THEN '优秀' WHEN 数学>=60 AND 数学<80 THEN '及格' ELSE '不及格' END)AS 数学,
(CASE WHEN 英语>=80 THEN '优秀' WHEN 英语>=60 AND 英语<80 THEN '及格' ELSE '不及格' END)AS 英语
FROM score WHERE 语文=70 OR 数学=80 OR 英语=58;

2.

一道SQL语句面试题，关于group by 
表内容： 
2005-05-09 胜 
2005-05-09 胜 
2005-05-09 负 
2005-05-09 负 
2005-05-10 胜 
2005-05-10 负 
2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

胜 负 
2005-05-09 2 2 
2005-05-10 1 2

create table tmp(rq varchar(10),shengfu nchar(1));
insert into tmp values('2015-05-09','胜'),('2015-05-09','胜'),('2015-05-09','负'),('2015-05-09','负'),('2015-05-10','胜'),('2015-05-10','负'),('2015-05-10','负');
select rq,sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from tmp group by rq;

 

　3.

把这样一个表儿

year month amount

1991 1 1.1

1991 2 1.2

1991 3 1.3

1991 4 1.4

1992 1 2.1

1992 2 2.2

1992 3 2.3

1992 4 2.4

查成这样一个结果

year m1 m2 m3 m4

1991 1.1 1.2 1.3 1.4

1992 2.1 2.2 2.3 2.4

 

CREATE TABLE y (`year` VARCHAR(4),`month`VARCHAR(1),amount decimal(2,1));


INSERT into y VALUES('1991','1','1.1'),('1991','2','1.2'),('1991','3','1.3'),('1991','4','1.4'),('1992','1','2.1'),
('1992','2','2.2'),('1992','3','2.3'),('1992','4','2.4');

SELECT * from Y;

select year,
(select amount from y m where month=1 and m.year=y.year) as m1,
(select amount from y m where month=2 and m.year=y.year) as m2,
(select amount from y m where month=3 and m.year=y.year) as m3,
(select amount from y m where month=4 and m.year=y.year) as m4
from y group by year;

DELETE from y where 1=1;

 

题目：  

姓名：name 课程：subject 分数：score 学号：stuid

张三 数学 89 1

张三 语文 80 1

张三 英语 70 1

李四 数学 90 2

李四 语文 70 2

李四 英语 80 2

CREATE TABLE stuscore(name VARCHAR(20),subject VARCHAR(20),score int, stuid int);
SELECT * from stuscore;
INSERT into stuscore VALUES('张三', '数学', 89, 1),('张三', '语文', 80, 1),('张三', '英语', 70, 1),
('李四', '数学', 90, 2),('李四', '语文', 70, 2),('李四', '英语', 80, 2);

1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)
SELECT s.name,sum(s.score)as 总成绩 from stuscore s group by s.name ORDER BY 总成绩 desc;

2.计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)
SELECT DISTINCT s.stuid,s.name,sum(s.score)as 总成绩 FROM stuscore s group by s.name ORDER BY 总成绩 desc;

select distinct t1.name,t1.stuid,t2.allscore from stuscore t1,
( select stuid,sum(score) as allscore from stuscore group by stuid)t2 where t1.stuid=t2.stuid order by t2.allscore desc;

3.计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)
SELECT s.stuid,s.name,s.subject,max(s.score)as 最高成绩 FROM stuscore s GROUP BY s.name;

select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,
(select stuid,max(score) as maxscore from stuscore group by stuid) t2 
where t1.stuid=t2.stuid and t1.score=t2.maxscore;

4.计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
SELECT s.stuid,s.name,avg(s.score)as avgscore FROM stuscore s GROUP BY s.name;

select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,
(select stuid,avg(score) as avgscore from stuscore group by stuid) t2 where t1.stuid=t2.stuid;

5.列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)
SELECT DISTINCT s.stuid,s.name,s.subject,s.score FROM stuscore s where s.score in
(SELECT max(score)as maxscore  from stuscore GROUP BY subject);

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 a.stuid,a.name, a.subject, a.score FROM stuscore AS a WHERE 
(SELECT COUNT(DISTINCT score) FROM stuscore AS b 
WHERE b.subject = a.subject AND b.score >= a.score) <= 2 
ORDER BY a.subject ASC, a.score DESC;
    如果有同分数的两个第二名则删除第二名：
SELECT a.stuid,a.name, a.subject, a.score FROM stuscore AS a WHERE 
(SELECT COUNT(*) FROM stuscore AS b 
WHERE b.subject = a.subject AND b.score >= a.score) <= 2 
ORDER BY a.subject ASC, a.score DESC;

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 stuscore GROUP BY stuid,name ORDER BY 总分 desc;

8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）
select subject,avg(score) as avgscore from stuscore group by subject;

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）
select stuid,name,score,
(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2  
where subject='数学' order by score desc;
 --注释：排序，比较大小，比较的次数+1 = 排名。

10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）
 select t3.*  from (
 select stuid,name,subject,score,
(select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from
 stuscore t2  where subject='数学') t3 
 where t3.名次 between 2 and 3 order by t3.score desc;

11．求出李四的数学成绩的排名
SELECT stuid,name,subject,score,
(SELECT count(*) from stuscore t1 where subject ='数学' and t1.score>t2.score)+1 as 名次 
from stuscore t2 where subject='数学' and name='李四' order by score desc;

　　12．统计如下：

课程	不及格（0-59）个	良（60-80）个	优（81-100）个

select subject 科目,sum(case when score between 0 and 59 then 1 else 0 end) as 不及格,
 sum(case when score between 60 and 80 then 1 else 0 end) as 良,
 sum(case when score between 81 and 100 then 1 else 0 end) as 优秀 from stuscore
 group by subject;    

13．统计如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)      

 declare @s nvarchar(1000)
 set @s=''
 select @s =@s+','+name+'('+convert(nvarchar(10),score)+'分)'from 
 stuscore where subject='数学'
 set @s=stuff(@s,1,1,' ')print '数学:'+@s

14.计算各科及格的人的平均成绩
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