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