oracle 数据库 50道经典例题

/*
表结构：
--1.学生表
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
--3.教师表
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数


select * from Student
select * from Course
select * from Teacher
select * from SC
*/
--准备工作：建表、新增测试数据
create table Student(S# varchar2(10),Sname varchar2(10),Sage date,Ssex varchar2(10));
insert into Student values('01' , '赵雷' , to_date('1990-01-01','yyyy-mm-dd') , '男');
insert into Student values('02' , '钱电' , to_date('1990-12-21','yyyy-mm-dd') , '男');
insert into Student values('03' , '孙风' , to_date('1990-05-20','yyyy-mm-dd') , '男');
insert into Student values('04' , '李云' , to_date('1990-08-06','yyyy-mm-dd') , '男');
insert into Student values('05' , '周梅' , to_date('1991-12-01','yyyy-mm-dd') , '女');
insert into Student values('06' , '吴兰' , to_date('1992-03-01','yyyy-mm-dd') , '女');
insert into Student values('07' , '郑竹' , to_date('1989-07-01','yyyy-mm-dd') , '女');
insert into Student values('08' , '王菊' , to_date('1990-01-20','yyyy-mm-dd') , '女');


create table Course(C# varchar2(10),Cname varchar2(10),T# varchar2(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');


create table Teacher(T# varchar2(10),Tname varchar2(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');


create table SC(S# varchar2(10),C# varchar2(10),score number(4,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);


--习题go


--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
--方法1
select s.*,s1.sc1 "01课程成绩",s2.sc2 "02课程成绩"
from Student s,
(select  sc.s# s#1,sc.score sc1 from Student s,SC sc where s.s#=sc.s# and sc.c#='01') s1,
(select sc.s# s#2,sc.score sc2 from Student s,SC sc where s.s#=sc.s# and sc.c#='02') s2
where s.s#=s1.s#1 and s.s#=s2.s#2 and s1.sc1>s2.sc2;


--方法2
select *
from Student s,SC sc1,SC sc2
where s.s#=sc1.s# and s.s#=sc2.s# and sc1.c#='01' and sc2.c#='02' and sc1.score>sc2.score;


--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--方法1
select s.*,s1.sc1 "01课程成绩",s2.sc2 "02课程成绩"
from Student s,
(select  sc.s# s#1,sc.score sc1 from Student s,SC sc where s.s#=sc.s# and sc.c#='01') s1,
(select sc.s# s#2,sc.score sc2 from Student s,SC sc where s.s#=sc.s# and sc.c#='02') s2
where s.s#=s1.s#1 and s.s#=s2.s#2 and s1.sc1<s2.sc2;
--方法2
select *
from Student s,SC sc1,SC sc2
where s.s#=sc1.s# and s.s#=sc2.s# and sc1.c#='01' and sc2.c#='02' and sc1.score<sc2.score;


--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.s#,s.sname,s1.a 平均成绩
from student s,
(select s1.s# s#,round(avg(s1.score),2) a from sc s1 group by s1.s# order by s1.s#)s1
where s.s#=s1.s# and s1.a>=60;


--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select s.s#,s.sname,s1.a 平均成绩
from student s,
(select s1.s# s#,round(avg(s1.score),2) a from sc s1 group by s1.s# order by s1.s#)s1
where s.s#=s1.s# and s1.a<60;


--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s#,s.sname,s1.c1 选课总数,s1.sum 总成绩
from student s,
(select s.s# s#,count(s.c#) c1,sum(s.score) sum from sc s group by s.s#) s1
where s.s#=s1.s#;


--6、查询"李"姓老师的数量
select count(*) 总数
from teacher t
where t.tname like '李%';


--7、查询学过"张三"老师授课的同学的信息
select *
from student s,course c,teacher t,sc s1
where s.s#=s1.s# and s1.c#=c.c# and c.t#=t.t# and t.tname='张三';




--8、查询没学过"张三"老师授课的同学的信息
--先求出张三所教的学生的编号
select s.* 
from Student s
where s.s# not in (select distinct sc.s# s from SC sc, Course , Teacher where sc.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = '张三');




--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select distinct s.*
from student s, sc s1,sc s2
where s.s#=s1.s# and s.s#=s2.s# and s1.c#=01 and s2.c#=02
order by s.s#;




--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select s.*
from student s,sc s1
where s.s#=s1.s# and s1.c#='01' and s.s# not in (select s.s# from student s,sc s1 where s.s#=s1.s# and s1.c#='02');


--11、查询没有学全所有课程的同学的信息
--先找出总课程数，再求出学生所学的总数，如果学门所学总数小与课程总数就是所求
--找到3个
select distinct s.*
from student s,(select s#,count(c#) c2 from sc group by s#) c1
where s.s#=c1.s# and c1.c2<(select count(c#) c1 from course)
order by s.s#;


--找到4个
select Student.* 
from Student 
left join SC on Student.S# = SC.S# 
group by Student.S# , Student.Sname , Student.Sage , Student.Ssex 
having count(C#) < (select count(C#) from Course);


--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct s.*
from student s,sc s1
where s.s#=s1.s# and s1.c# in(select c# from sc  where s#=01) and s.s#<>01
order by s.s#;




--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
--不懂，百度的
select Student.* 
from Student 
where S# in
(select distinct SC.S# 
from SC 
where S# <> '01' and SC.C# in (select distinct C# from SC where S# = '01') 
group by SC.S# 
having count(1) = (select count(1) from SC where S#='01'));


--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select distinct s.sname
from student s
where s.s# not in (select s# from sc where c# in(select c.c# from course c,teacher t where c.t#=t.t# and t.tname='张三'));


--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.s#,s.sname,round(avg(s1.score),2) 平均成绩
from student s,sc s1
where s.s#=s1.s# and s1.score<60
group by s.s#,s.sname
having count(s.s#)>=2;


--16、检索"01"课程分数小于60，按分数降序排列的学生信息
select *
from student s,sc s1
where s.s#=s1.s# and s1.c#=01 and s1.score<60
order by  s1.score desc;


--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select *
from student s,
(select s1.s#,round(avg(s1.score),2) 平均成绩 from sc s1 group by s1.s#) s1,
sc s2
where s.s#=s1.s# and s.s#=s2.s#(+)
order by s1.平均成绩 desc,s2.score desc;




--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：-80，优良为：-90，优秀为：>=90
--不懂，网上的


select 
  m.C# 课程编号, 
  m.Cname 课程名称,  
  max(n.score) 最高分, 
  min(n.score) 最低分, 
  cast(avg(n.score) as decimal(18,2)) 平均分, 
  cast((select count(1) from SC where C# = m.C# and score >= 60)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2))  及格率,
  cast((select count(1) from SC where C# = m.C# and score >= 70 and score  < 80 )*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) 中等率, 
  cast((select count(1) from SC where C# = m.C# and score >= 80 and score  < 90 )*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) 优良率, 
  cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SC where C# = m.C#) as decimal(18,2)) 优秀率
from Course m , SC n 
where m.C# = n.C# 
group by m.C# , m.Cname 
order by m.C#;


--19、按各科成绩进行排序，并显示排名
select s#,c#,score,rank() over(partition by c# order by score desc) 排名 from sc;




--20、查询学生的总成绩并进行排名
select s.s#,s.sname,sum(nvl(s1.score,0)) 总成绩,rank() over(order by sum(s1.score) desc) 排名
from student s,sc s1
where s.s#=s1.s#
group by s.s#,s.sname;


--21、查询不同老师所教不同课程平均分从高到低显示
select t.T# 教师编号, t.Tname 教师名字, 
cast(avg(s.score) as decimal(18,2)) 平均分
from Teacher t , Course c, SC s
where t.T# = c.T# and c.C# = s.C#
group by t.T#,t.Tname
order by 平均分 desc;


--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select *
from
(select s#,c#,score,row_number() over(partition by c# order by score desc) 排名 from sc)
where 排名 in(2,3);




--23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select Course.C# 课程编号 , Cname as 课程名称 ,
  sum(case when score >= 85 then 1 else 0 end ) "[85-100]",
  sum(case when score >= 70 and score < 85 then 1 else 0 end) "[70-85]",
  sum(case when score >= 60 and score < 70 then 1 else 0 end) "[60-70]",
  sum(case when score < 60 then 1 else 0 end) "[0-60]"
from sc , Course
where SC.C# = Course.C#
group by Course.C# , Course.Cname
order by Course.C#;


--24、查询学生平均成绩及其名次
--方法一，显示列号，但是有缺陷，成绩相同时排名会有问题
select s.s#, 平均成绩,rownum 排名 
from (select s#, round(avg(score),2) 平均成绩 from sc group by s# order by 平均成绩 desc) s;
--方法二，用rank 方法排序
select s#,round(avg(score),2) 平均成绩,rank() over(order by avg(score) desc) 排名 from sc group by s# 




  
--25、查询各科成绩前三名的记录
select *
from (select s#,c#,score,row_number() over(partition by c# order by score desc) 排名 from sc)
where 排名<4;




--26、查询每门课程被选修的学生数
select s1.c# 课程编号,count(s1.c#) 课程人数
from sc s1
group by s1.c#;


--27、查询出只有两门课程的全部学生的学号和姓名
select s1.s#,s.sname, count(s1.c#) 课程数
from student s,sc s1
where s.s#=s1.s#
group by s1.s#,s.sname
having count(s1.c#)=2;


--28、查询男生、女生人数
select s.ssex 性别,count(1) 人数
from student s
group by s.ssex;




--29、查询名字中含有"风"字的学生信息
select s.*
from student s
where s.sname like '%风%';




--30、查询同名同姓学生名单，并统计同名人数
select Sname 学生姓名, count(*)  人数 
from Student 
group by Sname 
having count(*) >1;
 
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select *
from student s
where to_char(s.sage,'yyyy')=1990;


--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select s1.c# 课程编号,round(avg(s1.score),2) 平均成绩
from sc s1
group by s1.c#
order by 平均成绩 desc,课程编号;




--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.s# 学号,s.sname 姓名,round(avg(s1.score),2) 平均成绩
from student s,sc s1
where s.s#=s1.s#
group by s.sname,s.s#
having round(avg(s1.score),2)>=85;




--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.sname 姓名,c.cname 课程名称,s1.score 分数
from student s,course c,sc s1
where s.s#=s1.s# and s1.c#=c.c# and c.cname='数学' and s1.score<60;


--35、查询所有学生的课程及分数情况；
select s.s# 学号,s.sname 学生姓名,c.cname 课程名称,s1.score 课程分数
from student s,course c,sc s1
where s.s#=s1.s# and c.c#=s1.c#;




--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select s.sname 姓名,c.cname 课程名称,s1.score 课程分数
from student s,sc s1,course c
where s.s#=s1.s# and c.c#=s1.c# and s1.score>70
order by s.sname;




--37、查询不及格的课程
select s.sname 姓名,c.cname 课程名称,s1.score 课程分数
from student s,sc s1,course c
where s.s#=s1.s# and c.c#=s1.c# and s1.score<60
order by s.sname;


--38、查询课程编号01为且课程成绩在80分以上的学生的学号和姓名；
select s.s# 学号,s.sname 姓名,s1.c# 课程编号,s1.score 分数
from student s,sc s1
where s.s#=s1.s# and s1.c#=01 and s1.score>=80;


--39、求每门课程的学生人数
select s1.c# 课程编号,count(s1.c#) 课程人数
from sc s1
group by s1.c#;


--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*,c.cname,s1.score
from student s,teacher t,course c,sc s1
where s.s#=s1.s# and s1.c#=c.c# and c.t#=t.t# and t.tname='张三' 
and s1.score=(select max(SC.score) from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三');




--41、查询不同课程,成绩相同的学生的学生编号、课程编号、学生成绩
select s.*
from sc s,(select C# , score from SC group by C#,score having count(1)>1) s1
where s.c#=s1.c# and s.score=s1.score
order by s.s#;


--42、查询每门功成绩最好的前两名
select *
from (select s#,c#,score,row_number() over(partition by c# order by score desc) 排名 from sc)
where 排名<3;


--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 
select s1.c# 课程编号,count(s1.c#) 课程人数
from sc s1
group by s1.c#
having count(s1.c#)>5;


--44、检索至少选修两门课程的学生学号
select s1.s# 学号,count(s1.c#) 课程数
from sc s1
group by s1.s#
having count(s1.c#)>=2
order by s1.s#;


--45、查询选修了全部课程的学生信息
select s.*
from student s,
(select s1.s# s,count(s1.c#) c from sc s1 group by s1.s# having count(s1.c#)=3) s1
where s.s#=s1.s;




--46、查询各学生的年龄
select s.s#,s.sname,to_char(sysdate,'yyyy')-to_char(s.sage,'yyyy') 年龄
from student s;


--47、查询本周过生日的学生
select s.*
from student s
where to_char(s.sage,'mmdd') between to_char(trunc(sysdate,'d'),'mmdd') and to_char(trunc(next_day(sysdate,'星期六')),'mmdd');


--48、查询下周过生日的学生
select s.*
from student s
where to_char(s.sage,'mmdd') between to_char(trunc(next_day(sysdate,'星期日')),'mmdd') and to_char(trunc(next_day(sysdate,'星期六')),'mmdd');


--49、查询本月过生日的学生
select s.*
from student s
where to_char(s.sage,'mmdd') between to_char(trunc(sysdate,'mm'),'mmdd') and to_char(last_day(sysdate),'mmdd');


--50、查询下月过生日的学生
select s.*
from student s
where to_char(s.sage,'mmdd') between to_char(add_months(trunc(sysdate,'mm'),2),'mmdd') and to_char(add_months(last_day(sysdate),2),'mmdd');