一、创建学生表、成绩表
1、创建一张学生表
create table student(
id number(10) primary key,
name varchar2(20) not null,
age varchar2(2)
);
create table grade(
no number,
id number(10),
kemu varchar2(10),
score varchar2(10)
);
declare
n number;
begin
for n in 1001 .. 1010 loop
insert into student values (n,‘张三‘||to_char(n-1000),TRUNC(dbms_random.value(10,15)) );
end loop;
end;
declare
n number;
begin
for n in 1001 .. 1010 loop
insert into grade values (n-1000,n,‘英语‘，TRUNC(dbms_random.value(10,100)) );
end loop;
end;
二、
1.计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成绩
select t.id,t.name ,dd.avg_score from student t join (select d.id,avg(d.score) avg_score from grade d group by d.id) dd on t.id=dd.id;
2.计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学，
英语，总分，平均分
with t1 as (select d.id,d.score,d.kemu from grade d where d.kemu=‘数学‘), t2 as (select d.id,d.score,d.kemu from grade d where d.kemu=‘语文‘ ), t3 as (select d.id,d.score ,d.kemu from grade d where d.kemu=‘英语‘), t4 as (select d.id,sum(d.score) zong from grade d group by id ), t5 as (select d.id,avg(d.score) ping from grade d group by id ) select t1.id,t1.score 数学 ,t2.score 语文, t3.score 英语,t4.zong 总分,t5.ping 平均 from t1 join t2 on t1.id =t2.id join t3 on t2.id = t3.id join t4 on t3.id=t4.id join t5 on t4.id=t5.id;
3.列出各门课程的平均成绩，要求显示字段：课程，平均成绩
select d.kemu,avg(d.score) from grade d group by d.kemu;
4.列出数学成绩的排名， 要求显示字段：学号，姓名，成绩，排名
--row_number 对select 结果进行排序，每条数据加一个序号（即使相同数据也依次排序） select t.id ,t.name,d.score, row_number()over (order by d.score desc) pming from grade d join student t on d.id=t.id where d.kemu=‘数学‘; --相同分数名称相同，会出现名称间隔 select t.id ,t.name,d.score, rank () over (order by d.score desc) pming from grade d join student t on d.id=t.id where d.kemu=‘数学‘; --相同分数名称相同，且名称连续性 select t.id ,t.name,d.score, dense_rank () over (order by d.score desc) pming from grade d join student t on d.id=t.id where d.kemu=‘数学‘;
--以分为n组排名进行排名
select t.id ,t.name,d.score, ntile (3) over (order by d.score desc) pming from grade d join student t on d.id=t.id where d.kemu=‘数学‘;
三、
1、列出数学成绩前 3 名的学生（要求显示字段：学号，姓名, 科目，成绩）
select *from (select * from grade d where d.kemu=‘数学‘ order by d.score desc) where rownum < 4;
2.查询数学成绩第 2 和第 3 名的学生
select *from ( select rank()over (order by d.score desc) pm,d.* from grade d where d.kemu=‘数学‘ ) dd where dd.pm in (2,3)
3.查询第 3 名到后面所有的学生数学成绩
select *from ( select dense_rank ()over (order by d.score desc) pm,d.* from grade d where d.kemu=‘数学‘ ) dd where dd.pm >=3;
4.统计英语课程少于 80 分的，显示 学号 id, 姓名，科目，分数
SELECT a.id, a.name, b.kemu, b.score FROM student a, grade b WHERE a.id = b.id AND b.kemu = ‘英语‘ AND b.score < 80;
5.查找每科成绩前 2 名，显示 id, 姓名，科目，分数
select * from (select row_number() over (partition by d.kemu order by d.score desc) pm,d.* from grade d ) dd where dd.pm<3;
6、统计每门课程不及格、一般、优秀
SELECT b.kemu, (SELECT COUNT(*) FROM grade WHERE score < 60 and kemu = b.kemu) as 不及格, (SELECT COUNT(*) FROM grade WHERE score between 60 and 80 and kemu = b.kemu) as 一般, (SELECT COUNT(*) FROM grade WHERE score > 80 and kemu = b.kemu) as 优秀 FROM grade b GROUP BY kemu; --或者 select a.kemu,a.low 不及格,b.good 及格,c.high 优秀 from (select d.kemu,count(*) low from grade d where d.score<60 group by d.kemu )a , (select d.kemu,count(*) good from grade d where d.score<80 and d.score >=60 group by d.kemu ) b, (select d.kemu,count(*) high from grade d where d.score >=80 group by d.kemu )c where a.kemu= b.kemu and b.kemu=c.kemu;
7、查询 学生表每门课都大于 40分的学生姓名
--having select * from student e where e.id in ( select distinct(d.id) from grade d group by d.id having min(d.score)>40); --not in select * from student t where t.id not in ( select d.id from grade d where d.score<=40);
原文：https://www.cnblogs.com/liangyf/p/13697873.html