sql题

1.

学生表(学生id，姓名，性别，分数)student（s_id，name,sex,score）

班级表（班级id，班级名称）class（c_id，c_name）

学生班级表（班级id，学生id）student_class(s_id,c_id)

①查询一班得分在80分以上的学生

select  name from student where score>80 and s_id in (select s_id from student_class  where c_id=1001);

②查询所有班级的名称，和所有班中女生人数和女生的平均分

select c.c_name,count(s.s_id),avg(s.score) from class c

inner join student_class sc on sc.c_id=c.c_id

inner join student s on s.s_id=sc.s_id where s.sex= '女' group by c.c_name;

 

2.

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 data , sum( case when result = 'win' then 1 else 0 end ) win,

sum( case when result = 'lose'  then 1 else 0 end ) as lose from info group by data;

 

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 infotest;

 

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

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

显示格式：?

语文 数学 英语?

及格 优秀 不及格?

select (case when chinese<60 then '不及格' when chinese>=60 and chinese<80 then '及格' when chinese>=80 then '优秀' else '异常' end) as chinese,(case when math<60 then '不及格' when math>=60 and math<80 then '及格' when math>=80 then '优秀' else '异常' end)as math,(case when english<60 then '不及格' when english>=60 and english<80 then '及格' when english>=80 then '优秀' else '异常' end)as english from infotest;

 

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

张三 数学 89 1

张三 语文 80 1

张三 英语 70 1

李四 数学 90 2

李四 语文 70 2

李四 英语 80 2

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

select name,sum(score) as '总成绩' from infotest group by name order by sum(score) desc;

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

select stuid,name,sum(score) as '总成绩' from infotest group by name,stuid order by sum(score) desc;

或

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

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

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

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

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

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

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

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

select distinct t1.* from infotest t1 where t1.stuid in (select top 2 infotest.stuid from infotest where subject = t1.subject order by score desc) order by t1.subject;

⑦统计如下：学号 姓名 语文 数学 英语 总分 平均分

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 总分,avg(score) as 平均分 from infotest group by stuid,name order by 总分 desc;

⑧列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

select subject,avg(score) as 平均成绩 from infotest group by subject;

⑨列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

select row_number() over (order by score desc) as 排名,stuid,name,score from infotest where subject='数学';

⑩列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

select row_number() over (order by score desc) as 排名,stuid,name,score from infotest where subject='数学' limit 1,3;

11.求出李四的数学成绩的排名

select row_number() over (order by score desc) as 排名,stuid,name,score from infotest where subject='数学' and name='李四';

12.统计如下：课程 不及格（0-59）个 良（60-80）个 优（81-100）个

select subject,sum(case when score>=0 and score<=59 then 1 else 0 end) as '不及格',sum(case when score>=60 and score<=80 then 1 else 0 end) as  '良',sum(case when score>=81 and score<=100 then 1 else 0 end) as  '优' from infotest group by subject;

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

select subject,sum(case when name='张三' then score else 0 end)as '张三',sum(case when name='李四' then score else 0 end)as '李四',sum(case when name='王五' then score else 0 end)as '王五' from infotest group by subject having subject='数学';

14.计算学科及格的人的平均成绩

select name,sum(case when score>=60 then score else 0 end)/sum(case when score>=60 then 1 else 0 end) as '平均分' from infotest group by name;

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

select name from infotest group by name having min(score)>80;

 

6.学生关系student（SNO，SNAME，AGE，SEX，SDEPT）；学习关系student_class（SNO，CNO，GRADE）；课程关系class（CNO，CNAME，CDEPT，TNAME）

查询问题：

（1）检索计算机系的全体学生的学号，姓名和性别；  3分

（2）检索学习课程号为1001的学生学号与姓名；       3分

（3）检索选修课程名为“DS”的学生学号与姓名；    3分

（4）检索选修课程号为1002或1001的学生学号与姓名；        3分

（5）检索至少选修课程号为1001和1002的学生学号；    3分

（6）检索不学ES课的学生姓名和年龄； 5分

（7）检索学习全部课程的学生姓名；

（1）    select sno,sname,sex from student where sdept='CS';

（2）    select sno,sname from student where sno in (select sno from student_class where cno='1001');

（3）select s.sno,s.sname from student s where s.sno in(select sno from student_class where cno in (select cno from class where cname='DS'));

或者：

select s.sno,s.sname from student s inner join student_class sc on s.sno=sc.sno inner join class c on sc.cno=c.cno  and  c.cname='DS';

  (4)  select sno,sname from student where sno in (select sno from student_class where cno='1001' or cno='1002');

（5） select x.sno from student_class x,student_class y where x.sno=y.sno and x.cno='1001' and y.cno='1002';

（6）select sname,age from student where sno not in (select sno from student_class where cno in (select cno from class where cname='ES'));

（7）select sname from student where sno in (select sno from student_class group by sno having count(*)=(select count(*) from class));