经典sql50题
创建测试数据: 学生表 Student(S,Sname,Sage,Ssex) - S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 课程表 Course(C,Cname,T) - C 课程编号,Cname 课程名称,T 教师编号 教师表 Teacher(T,Tname) - T 教师编号,Tname 教师姓名 成绩表 Score(S,C,Scoreore) - S 学生编号,C 课程编号,score 分数
create table Student (S varchar(10),Sname varchar(10),Sage date,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(C varchar(10),Cname varchar(10),T varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(T varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table Score(S varchar(10),C varchar(10),score decimal(18,1));
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数
select stu.*,sc.sc1,sc.sc2 from
(select sc01.s,sc01.score sc1,sc02.score sc2 from
(select * from score where c='01') sc01
left join
(select * from score where c='02') sc02
on sc01.s = sc02.s
where sc01.score > sc02.score) sc,student stu
where sc.s = stu.s;
2、查询”01”课程比”02”课程成绩低的学生的信息及课程分数
select stu.*,sc.sc1,sc.sc2 from
(select sc01.s,sc01.score sc1,sc02.score sc2 from
(select * from score where c = '01') sc01
left join
(select * from score where c = '02') sc02
on sc01.s = sc02.s
where sc01.score < sc02.score) sc,student stu
where sc.s = stu.s;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select stu.s,stu.sname,sc.avg from
(select s,avg(score) from score
group by s
having(avg(score)>= 60)) sc,student stu
where stu.s = sc.s;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select stu.s,stu.sname,sc.avg from
(select s,avg(score) from score
group by s
having(avg(score)<60)) sc,student stu
where sc.s = stu.s;
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select stu.s,stu.sname,sc.count,sc.sum from
(select s,count(c),sum(score) from score group by s) sc,
student stu
where sc.s = stu.s
order by s;
6、查询”李”姓老师的数量
select count(tname) from teacher where tname like '李%'
7、查询学过”张三”老师授课的同学的信息
select * from student where s in
(select s from score where c =
(select c from course where t =
(select t from teacher where tname = '张三')));
8、查询没学过”张三”老师授课的同学的信息
select * from student where s not in
(select s from score where c =
(select c from course where t =
(select t from teacher where tname = '张三')));
9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息
select * from student where s in
(select s from score where c = '01'
intersect
select s from score where c = '02');
10、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息
select s from score where c = '01'
intersect
select s from student where s not in
(select s from score where c = '02')
11、查询没有学全所有课程的同学的信息
select stu.*,s.count from
(select s,count(c) from score
group by s
having(count(c) <> (select count(c) from course))) s,
student stu
where stu.s = s.s
order by s;
12、查询至少有一门课与学号为”01”的同学所学相同的同学的信息
select * from student where s in
(select distinct s from score where c in
(select c from score where s = '01'));
13、查询和”01”号的同学学习的课程完全相同的其他同学的信息
select * from student where s in
(select s from score
group by s
having(count(c)=(select count(c) from score where s = '01')));
14、查询没学过”张三”老师讲授的任一门课程的学生姓名
select sname from student where s not in
(select s from score where c =
(select c from course where t =
(select t from teacher where tname = '张三')));
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select stu.s,stu.sname,b.avg from
(select s,avg(a.score) from
(select s,score from score where score < 60) a
group by s
having(count(score) >= 2)) b,
student stu
where stu.s = b.s;
16、检索”01”课程分数小于60，按分数降序排列的学生信息
select s,score from score where c = '01' and score < 60 order by score desc;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s,b.c,b.score,a.avg from
(select s,c,score from score) b
left join
(select s,avg(score) from score group by s) a
on a.s = b.s
order by avg desc;
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：
课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select a.c,a.cname,b.max,b.min,b.avg,c.round 及格率,d.round 中等率,e.round 优良率,f.round 优秀率
from
(select * from course) a
left join
(select c,max(score),min(score),avg(score) from score group by c) b
on a.c = b.c
left join
(select c,round(sum(case when score>=60 then 1 else 0 end)*1.0/count(c),2) from score group by c) c
on a.c = c.c
left join
(select c,round(sum(case when score>=70 and score<80 then 1 else 0 end)*1.0/count(c),2) from score group by c) d
on c.c = d.c
left join
(select c,round(sum(case when score>=80 and score<90 then 1 else 0 end)*1.0/count(c),2) from score group by c) e
on c.c = e.c
left join
(select c,round(sum(case when score>=90 then 1 else 0 end)*1.0/count(c),2) from score group by c) f
on c.c = f.c
19、按各科成绩进行排序，并显示排名
select stu.s,stu.sname,c.cname,sc.score,sc.dense_rank from student stu
left join
(select s,c,score, dense_rank() over(partition by c order by score desc) from score) sc
on stu.s = sc.s
left join
(select c,cname from course) c
on c.c = sc.c
20、查询学生的总成绩并进行排名
select stu.sname,sc.sum,sc.row_number from
(select s,sum(score),row_number() over(order by sum(score) desc) from score group by s) sc
left join
(select s,sname from student) stu
on sc.s = stu.s
order by row_number;
21、查询不同老师所教不同课程平均分从高到低显示
select t.tname,c.cname,avg.avg from teacher t
left join
(select t,c,cname from course) c
on t.t=c.t
left join
(select c,avg(score) from score group by c order by avg desc) avg
on c.c = avg.c
order by avg desc;
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select s,c,score,sort from
(select s,c,score,rank() over(partition by c order by score desc) as sort from score) sc
where sc.sort between 2 and 3
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select c.cname 课程名称,num.* from course c
left join
(select c,
sum(case when score between 85 and 100 then 1 else 0 end) as A级人数,
sum(case when score between 70 and 85 then 1 else 0 end) as B级人数,
sum(case when score between 60 and 70 then 1 else 0 end) as C级人数,
sum(case when score between 0 and 60 then 1 else 0 end) as D级人数,
round(sum(case when score between 85 and 100 then 1 else 0 end)*1.0/count(s),2) as A级比例,
round(sum(case when score between 70 and 85 then 1 else 0 end)*1.0/count(s),2) as B级比例,
round(sum(case when score between 60 and 70 then 1 else 0 end)*1.0/count(s),2) as C级比例,
round(sum(case when score between 0 and 60 then 1 else 0 end)*1.0/count(s),2) as D级比例
from score group by c) as num
on num.c = c.c;
24、查询学生平均成绩及其名次
select s,avg(score),row_number() over(order by avg(score) desc) from score group by s;
25、查询各科成绩前三名的记录
select c,score,sort from
(select c,score,row_number() over(partition by c order by score desc) sort from score) score
where sort between 1 and 3;
26、查询每门课程被选修的学生数
select c.cname,sc.count from course c
left join
(select c,count(score) from score group by c) sc
on sc.c = c.c
27、查询出只有两门课程的全部学生的学号和姓名
select stu.s,stu.sname,num.count
from student stu
left join
(select distinct s,count(c) over(partition by s) from score) num
on stu.s = num.s
where count = 2;
28、查询男生、女生人数
select ssex,count(ssex) from student group by ssex;
29、查询名字中含有”风”字的学生信息
select * from student where sname like '%风%';
30、查询同名同性学生名单，并统计同名人数
select sname,ssex,count(*) from student
group by sname,ssex
having count(*)>=2
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from student where cast(sage as varchar) like '1990%'
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
select c,avg(score) from score group by c order by avg desc,c
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select stu.s 学生学号,stu.sname 学生姓名,sc.avg 平均成绩 from student stu
right join
(select s,avg(score) from score group by s having(avg(score)>=85)) sc
on stu.s = sc.s
34、查询课程名称为”数学”，且分数低于60的学生姓名和分数
select c.cname,stu.sname,sc.score from score sc
left join course c
on c.c = sc.c
left join student stu
on sc.s = stu.s
where cname='数学' and score<60
35、查询所有学生的课程及分数情况；
select * from student stu
inner join score sc
using(s);
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select distinct stu.sname,c.cname,sc.score
from student stu,course c,score sc
where stu.s = sc.s and sc.c = c.c and score > 70
37、查询不及格的课程
select distinct stu.sname,c.cname,sc.score
from student stu,course c,score sc
where stu.s = sc.s and sc.c = c.c and score < 60;
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select stu.sname,c.cname,sc.score from student stu
right join
(select * from score where c = '01' and score >= 80) sc
on stu.s = sc.s
left join course c
on sc.c = c.c;
39、求每门课程的学生人数
select c.cname,count from course c
right join
(select c,count(c) from score group by c) num
on num.c = c.c
40、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.*,sort.score from student stu,
(select sc.s,sc.score,row_number() over(order by score desc) max from score sc
right join
(select c.c from teacher t
left join course c
on t.t = c.t
where tname = '张三') c
on c.c = sc.c) sort
where sort.max = 1 and sort.s = stu.s;
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct stu1.sname,sc1.c,stu2.sname,sc2.c,sc1.score
from score sc1,score sc2,student stu1,student stu2
where sc1.c != sc2.c and sc1.s != sc2.s and sc1.score = sc2.score and sc1.s = stu1.s and sc2.s = stu2.s
42、查询每门课程成绩最好的前两名
select sc.c,sc.score from
(select c,score,row_number() over(partition by c order by score desc) sort from score) sc
where sc.sort < 3;
43、统计每门课程的学生选修人数（超过5人的课程才统计）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c,count(c) from score group by c having(count(c)>=5) order by count desc,c
44、检索至少选修两门课程的学生学号
select s,count(c) from score group by s having(count(c)>=2);
45、查询选修了全部课程的学生信息
select * from student where s in
(select s from score group by s having(count(c)=(select count(c) from course)));
46、查询各学生的年龄
select sname,(current_date - sage)/365||'岁' age from student;
select sname,extract(year from age(now(),sage)) age from student;
47、查询本周过生日的学生
select * from student where extract(week from now()) = extract(week from sage);
48、查询下周过生日的学生
select * from student where extract(week from now())+1 = extract(week from sage);
49、查询本月过生日的学生
select * from student where extract(month from now()) = extract(month from sage);
50、查询下月过生日的学生
select * from student where extract(month from now())+1 = extract(month from sage);
