Oracle 经典练习题 50 题

先用sys创建一个用户，防止其他表带来干扰

CREATE USER c##baseMyf IDENTIFIED BY 123456GRANT CONNECT, RESOURCE, DBA TO c##baseMyf;alter user c##ifeng identified by 123456;

一 CreateTable

--Studentcreate table student (s_id int,s_name varchar(8),s_birth date,s_sex varchar(4)
);
go
insert into student values
(1,'赵雷',to_date('1990-01-01','yyyy-MM-dd'),'男');insert into student values
(2,'钱电',to_date('1990-12-21','yyyy-MM-dd'),'男');insert into student values
(3,'孙风',to_date('1990-05-20','yyyy-MM-dd'),'男');insert into student values
(4,'李云',to_date('1990-08-06','yyyy-MM-dd'),'男');insert into student values
(5,'周梅',to_date('1991-12-01','yyyy-MM-dd'),'女');insert into student values
(6,'吴兰',to_date('1992-03-01','yyyy-MM-dd'),'女');insert into student values
(7,'郑竹',to_date('1989-07-01','yyyy-MM-dd'),'女');insert into student values
(8,'王菊',to_date('1990-01-20','yyyy-MM-dd'),'女');--course
create table course (c_id int,c_name varchar(8),t_id int
);insert into course values
(1,'语文',2);
insert into course values
(2,'数学',1);
insert into course values
(3,'英语',3);-- teachercreate table teacher (t_id int,t_name varchar(8)
);insert into teacher values
(1,'张三');
insert into teacher values
(2,'李四');
insert into teacher values
(3,'王五');--score
create table score (s_id int,c_id int,s_score int
);insert into score values
(1,1,80);
insert into score values
(1,2,90);
insert into score values
(1,3,99);
insert into score values
(2,1,70);
insert into score values
(2,2,60);
insert into score values
(2,3,65);
insert into score values
(3,1,80);
insert into score values
(3,2,80);
insert into score values
(3,3,80);
insert into score values
(4,1,50);
insert into score values
(4,2,30);
insert into score values
(4,3,40);
insert into score values
(5,1,76);
insert into score values
(5,2,87);
insert into score values
(6,1,31);
insert into score values
(6,3,34);
insert into score values
(7,2,89);
insert into score values
(7,3,98);

二 练习题

1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

--查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select distinct stu.s_id, s_name, s_birth, s_sex ,s_score_1,s_score_2
from student stu
join score s on s.s_id = stu.s_id
join (select s_id ,max(case when c_id = 1 then s_score end) as s_score_1,max(case when c_id = 2 then s_score end) as s_score_2from scoregroup by s_idhaving max(case when c_id = 1 then s_score end) > max(case when c_id = 2 then s_score end)
)a on stu.s_id = a.s_id

2 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

--查询"01"课程比"02"课程成绩低的学生的信息及课程分数(查询了全部的课程分数)select distinct stu.s_id, s_name, s_birth, s.c_id,s.s_score
from student stu
join score s on stu.s_id = s.s_id
and s.s_id in  (select s_id--,max(case when c_id = 1 then s_score end) as score_1--,max(case when c_id = 2 then s_score end) as score_2from scoregroup by s_idhaving max(case when c_id = 1 then s_score end) < max(case when c_id = 2 then s_score end) 
)

3 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

--查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select stu.s_id, s_name, s_birth, s_sex ,a.avg_score
from student stu
join (
select s_id,round(avg(s_score),2) as avg_score
from score
group by s_id
having avg(s_score) > 60) a on a.s_id = stu.s_id

4 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

--查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
select stu.s_id, s_name, s_birth, s_sex ,a.avg_score,a.avg_score_2
from student stu
left join (select s_id--, c_id, s_score ,round(sum(s_score) / count(coalesce(c_id,1)),2) as avg_score,avg(s_score) as avg_score_2from scoregroup by s_id
) a on a.s_id = stu.s_id
where (avg_score < 60 or avg_score is null)

5 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

--查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩select stu.s_id, s_name,coalesce(count_c,0),coalesce(sum_score,0)
from student stu
left join (select s_id--, c_id, s_score ,count(c_id) as count_c,sum(s_score) as sum_scorefrom scoregroup by s_id
)a on stu.s_id = a.s_id

6 查询"李"姓老师的数量

--查询"李"姓老师的数量
select count(t_id) as count_li from teacher
where t_name like '李%'

7 查询学过"张三"老师授课的同学的信息

--查询学过"张三"老师授课的同学的信息select s_id, s_name, s_birth, s_sex 
from student where s_id in(select s_id from scorewhere c_id in (select c.c_id from teacher t join course c on c.c_id = t.t_id and t_name = '张三')
)

8 查询没学过"张三"老师授课的同学的信息

--查询没学过"张三"老师授课的同学的信息select s_id, s_name, s_birth, s_sex 
from student
where s_id not in (select s_idfrom score where c_id in (--select c.c_id from teacher t,course c where t_name = '张三' and t.t_id = c.c_idselect c.c_id from teacher t join course c on t.t_id = c.c_id and t_name = '张三')
)

9 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

--查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息select s_id, s_name, s_birth, s_sex 
from student
where s_id in (select s_idfrom scorewhere c_id = 01and s_id in (select s_id from score where c_id = 02)
)

10 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select s_id, s_name, s_birth, s_sex 
from student where s_id in(select s_idfrom scorewhere c_id = 1--where s_id in (--      select s_id from score where c_id = 1--)and s_id not in (select s_id from score where c_id = 2)
)

11 查询没有学全所有课程的同学的信息

--查询没有学全所有课程的同学的信息select s_id, s_name, s_birth, s_sex 
from student
where s_id in (select s_idfrom scoregroup by s_id having count(c_id) != (select count(*) from course)
)

12 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

--查询至少有一门课与学号为"01"的同学所学相同的同学的信息select s_id, s_name, s_birth, s_sex 
from student
where s_id in (select distinct s_idfrom scorewhere c_id in(select c_id from score where s_id = 1)
) and s_id != 1

13 查询和"01"号的同学学习的课程完全相同的其他同学的信息

--查询和"01"号的同学学习的课程完全相同的其他同学的信息
with data as (select distinct s_id,listagg(c_id,',') within group(order by c_id) over(partition by s_id) as cid_list
from score)select s_id, s_name, s_birth, s_sex 
from student
where s_id in (select s_id from datawhere cid_list in (select cid_list from data where s_id = 1) and s_id != 1
)

--查询和"01"号的同学学习的课程完全相同的其他同学的信息
select s_id, s_name, s_birth, s_sex 
from student
where s_id in(select s_id--, c_id, s_scorefrom score sinner join (select c_id from score where s_id = 1)a on a.c_id = s.c_idwhere s_id != 1group by s_idhaving count(*) = (select count(*) from score where s_id = 1)
)

14 查询没学过"张三"老师讲授的任一门课程的学生姓名

--查询没学过"张三"老师讲授的任一门课程的学生姓名select stu.s_id, stu.s_name, a.c_id
from student stu
join (select s_id, c_id, s_score from scorewhere c_id not in (select c.c_idfrom teacher tjoin course con t.t_id = c.c_id and t.t_name = '张三')
)a on a.s_id = stu.s_id

-- 没学过 -> 首先想到 排除学过的select * from student where s_id not in(select distinct s_id from score where c_id in(select c_id from course where t_id in(select t_id from teacher where t_name = '张三'   ))
)

15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

--查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩with data as (
select s_id, c_id, s_score ,avg(s_score) over(partition by s_id) as avg_score
from score)select stu.s_id, stu.s_name, avg_score
from student stu
join (
select s_id,avg_score
from data
group by s_id,avg_score
having sum(case when s_score < 60 then 1 else 0 end) >= 2
)a on stu.s_id = a.s_id

16 检索"01"课程分数小于60，按分数降序排列的学生信息

select stu.s_id, s_name, s_birth, s_sex ,a.s_score
from student stu
join (
select s_id,s_score from score
where c_id = 1 and s_score < 60
)a on stu.s_id = a.s_id
order by a.s_score desc

17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

--按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
with data as (
select *
from (select s_id, c_id, s_score,avg(s_score) over(partition by s_id) as avg_score from score) 
pivot(max(s_score)for c_id in(1 as 数学,2 as 语文,3 as 英语)
)
)select d.*,stu.s_name
from data d
join student stu on stu.s_id = d.s_id

18 查询各科成绩最高分、最低分和平均分，以如下形式显示

--查询各科成绩最高分、最低分和平均分，以如下形式显示：
--课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90select c_id, max(s_score ) as max_score, min(s_score ) as min_score, round(avg(s_score ),2) as avg_score, concat(round((sum(case when s_score >= 60 then 1 else 0 end) / count(*)) * 100,2),'%') as jg, concat(round((sum(case when s_score >= 70 and s_score < 80 then 1 else 0 end) / count(*)) * 100,2),'%') as zd , concat(round((sum(case when s_score >= 80 and s_score < 90 then 1 else 0 end) / count(*)) * 100,2),'%') as yl , concat(round((sum(case when s_score >= 90 then 1 else 0 end) / count(*)) * 100,2),'%') as yx 
from score
group by c_id

19 按各科成绩进行排序，并显示排名

select s.s_id, s.c_id,c.c_name, s.s_score ,rank() over(partition by s.c_id order by s.s_score desc) as rank
from score s
join student stu on s.s_id = stu.s_id
join course c on s.c_id = c.c_id
order by s.s_id,c.c_name,rank

20 查询学生的总成绩并进行排名

--查询学生的总成绩并进行排名
with data as (
select s_id, c_id, s_score ,sum(s_score) over(partition by s_id) as sum_score
from score
order by sum_score desc
)
select data.*,stu.s_name,rank() over(order by sum_score desc)  as rank
from data 
join student stu on stu.s_id = data.s_id
order by rank

21 查询不同老师所教不同课程平均分从高到低显示

--查询不同老师所教不同课程平均分从高到低显示select c.t_id,s.c_id,round(avg(s_score ),2) as avg_score
from course c
join score s on c.c_id = s.c_id
group by c.t_id,s.c_id
order by avg_score desc

22 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

--查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
with data as (
select s_id, c_id, s_score,rank() over(partition by c_id order by s_score desc) as rankfrom score)select stu.*,data.c_id,data.s_score
from data 
join student stu on stu.s_id = data.s_id and rank between 2 and 3

23 统计各科成绩各分数段人数：课程编号,课程名称,[100-85),[85-70),[70-60),[0-60)及所占百分比

--统计各科成绩各分数段人数：课程编号,课程名称,[100-85),[85-70),[70-60),[0-60)及所占百分比
with socre_s as (
select s_id, c_id, s_score,count(s_id) over(partition by c_id) as c_s_count,case when s_score > 85 and s_score <= 100 then '[100-85)'when s_score > 70 and s_score <= 85 then '[85-70)'when s_score > 60 and s_score <= 70 then '[70-60)'when s_score >= 0 and s_score < 60 then '[0-60)'end as score_djfrom score)select s_id, c_id,score_dj,concat(round((count(s_id) / c_s_count),2) * 100,'%') as pre_score
from socre_s
group by s_id, c_id,score_dj,c_s_count

24 查询学生平均成绩及其名次

select s.s_id, c_id, s_score ,s_name,avg(s_score) over(partition by s.s_id) as avg_score,rank() over(partition by c_id order by s_score desc)
from score s
join student stu
on stu.s_id = s.s_id

25 查询各科成绩前三名的记录

with data1 as (
select s_id, c_id, s_score ,rank() over(partition by c_id order by s_score desc) as rank
from score
)select s.s_id, s.s_name,c.c_id, c.c_name, c.t_id ,d.s_score
from course c
join data1 d on c.c_id = d.c_id and d.rank <= 3
join student s on s.s_id = d.s_id

--感觉写的很奇怪，平常都不这么用select c.c_id,c.c_name,s.s_id,s.s_name,s_score
from (select *from score scwhere (select count(*)from score sc1where sc.c_id = sc1.c_idand sc.s_score < sc1.s_score) < 3
)
t1
inner join student s on t1.s_id = s.s_id 
inner join course c on t1.c_id = c.c_id 
order by c.c_id,s_score desc

26 查询每门课程被选修的学生数

select c.c_id, c_name, t_id ,count_s
from course c
join (
select count(s_id) as count_s, c_id
from score
group by c_id
) a
on c.c_id = a.c_id

27 查询出只有两门课程的全部学生的学号和姓名

select s_id, s_name, s_birth, s_sex 
from student
where s_id in (
select s_id
from score
group by s_id
having count(c_id) = 2
)

28 查询男生、女生人数

select  s_sex ,count(s_id ) as count
from student
group by s_sex

29 查询名字中含有"风"字的学生信息

select s_id, s_name, s_birth, s_sex 
from student
where s_name like '%风%'

30 统计同姓的人员名单，打印 姓 人数 姓名

--统计同姓的人员名单，打印 姓 人数 姓名
select substr(s_name,0,1) as first_name, s_name,count(s_name) over(partition by substr(s_name,0,1)) as first_name_count
from student

31 查询1990年出生的学生名单

select s_id, s_name, s_birth, s_sex 
from student
--where to_char(s_birth,'yyyy') = 1990
where extract(year from s_birth) = 1990

32 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

--select c_id, avg(s_score) as avg_score
from score 
group by c_id
order by avg(s_score) desc,c_id

33 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select stu.s_id, s_name, avg_score
from student stu
join 
(select s_id, avg(s_score) as avg_score 
from score
group by s_id
having avg(s_score) >= 85) s
on s.s_id = stu.s_id

34 查询课程名称为"数学"，且分数低于60的学生姓名和分数

select stu.s_name, s.s_score 
from score s
join student stu
on s.s_id = stu.s_id
where c_id in (select c_id from course where c_name = '数学'
)and s.s_score  < 60

35 查询所有学生的课程及分数情况

Result1 group

select stu.s_id, s_name, s_birth, s_sex ,sum(case when s.c_id = 1 then s.s_score end) as 数学,sum(case when s.c_id = 2 then s.s_score end) as 语文,sum(case when s.c_id = 3 then s.s_score end) as 英语
from student stu
join score s on stu.s_id = s.s_id
join course c on s.c_id = c.c_id
group by stu.s_id, s_name, s_birth, s_sex 

Result2 pivot

with data as (
SELECT *
FROM scorePIVOT (MAX(s_score)FOR c_id IN (1 as 数学, 2 as 语文, 3 as 英语))
)select s.s_name, s.s_birth, s.s_sex ,d.*
from student s
join data d
on s.s_id  = d.s_id

select stu.s_id, s_name, s_birth, s_sex , coalesce(a.s_score,0) as  数学, coalesce(b.s_score,1) as  语文, coalesce(c.s_score,2) as  英语
from student stu
left join (select s_id, c_id, s_score from score where c_id = 1) a on a.s_id = stu.s_id
left join (select s_id, c_id, s_score from score where c_id = 2) b on b.s_id = stu.s_id
left join (select s_id, c_id, s_score from score where c_id = 3) c on c.s_id = stu.s_id

36 查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数

--查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数(任何的理解不同)select s_name, c.c_name  ,s.s_score
from student stu
join (select s_id, c_id, s_score ,max(s_score) over(partition by s_id) as max_scorefrom score) s
on stu.s_id = s.s_id
and s.max_score > 70 
join course c
on s.c_id = c.c_id

37 查询课程不及格的学生

--
select stu.s_id, s_name, s_birth, s_sex ,s.s_score
from student stu
join score s
on stu.s_id = s.s_id
and s.s_score < 60 

38 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select s_id, s_name, s_birth, s_sex 
from student s
where s_id in(select s_idfrom scorewhere c_id = 1 and s_score >= 80
)

39 查询每门课程的人数

select c.c_id, c_name, t_id ,count_s 
from course c
join(select c_id,count(s_id) as count_sfrom scoregroup by c_id
)a
on c.c_id = a.c_id

40 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

with cid as (
select c.c_id 
from course c
join teacher t
on c.t_id = t.t_id 
and t.t_name = '张三')
select * from (
select s.*,stu.s_name,rank() over(order by s_score desc) as rank
from score s
join cid on cid.c_id = s.c_id
join student stu on stu.s_id = s.s_id
) where rank = 1

41 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select s.s_id,stu.s_name,s.c_id,s.s_score 
from score s
join  (select s_idfrom scoregroup by s_id,s_score having count(c_id  ) > 1
) a
on s.s_id = a.s_id
join student stu
on stu.s_id = s.s_id
order by s.s_id,s.c_id--严谨一点
select a.s_id,s.s_name,a.c_id,a.s_score
from (selects_id,c_id,s_score,count(c_id) over(partition by s_id,s_score) as count_scorefrom score
)a
join student s
on s.s_id = a.s_id
and count_score > 1

select * from score where s_score in(select s_scorefrom score group by s_score having count(1) > 1
)

42 统计每门课程的前几名

select a.c_id ,c_name ,a.s_id ,s_name ,s_score 
from (
selects_id ,c_id ,s_score ,rank() over(partition by c_id order by s_score desc) as rank,row_number() over(partition by c_id order by s_score desc) as rn
from score) a
join student s
on a.rank <=3
and s.s_id = a.s_id
join course c
on c.c_id = a.c_id
order by a.c_id ,c_name ,a.s_id ,s_name ,s_score 

43 统计课程的选课人数，> 5 才统计

--要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
selectc_id,count(s_id ) as count_s
from score
group by c_id 
having count(s_id ) > 5
order by count(s_id ) desc,c_id

44 查询选修了2门课的sid

selects_id
from score
group by s_id 
having count(c_id ) >= 2

45 查询选修了全部课程的学生信息

select * from student
where s_id in (
selects_id
from score
group by s_id 
having count(c_id ) = (select count(c_id ) from course)
)

46 求学生周岁

selects_name ,s_birth ,trunc(MONTHS_BETWEEN(SYSDATE, s_birth ) / 12)from student

47 本周过生日的同学

selectto_char(trunc(sysdate,'IW'),'yyyy-mm-dd')  this_monday,to_char(trunc(sysdate,'IW') + 6,'yyyy-mm-dd')  this_sunday,to_char(trunc(next_day((sysdate),1)) ,'yyyy-mm-dd') next_fir_day_sun,to_char(trunc(next_day((sysdate),'星期日')),'yyyy-mm-dd')  next_sunday
from dual

48 下周过生日的同学

select*
from student
where s_birth between (trunc(sysdate,'IW') + 7) and (trunc(sysdate,'IW') + 13)

49 查询本月过生日的同学

select * from student
where extract(month from s_birth) = extract(month from sysdate)

50 查询12月份过生日的同学

select * from student
where to_char(s_birth ,'mm') = '12'