Oracle 经典练习题 50 题

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

CREATE USER c##baseMyf IDENTIFIED BY 123456


GRANT CONNECT, RESOURCE, DBA TO c##baseMyf;


alter user c##ifeng identified by 123456;

一 CreateTable

--Student

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


-- teacher

create 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"课程成绩高的学生的信息及课程分数

--解1：group + case when
select distinct s.s_id, a.s_score_1 ,a.s_score_2
       ,stu.s_name
from score s
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_2
  from score
  group by s_id
  having max(case when c_id = 1 then s_score end) > coalesce(max(case when c_id = 2 then s_score end),0)
) a on s.s_id = a.s_id
join student stu on stu.s_id = s.s_id

----解2：自连接

select s1.s_id, s1.c_id as s1_cid, s1.s_score as s1_score,s.s_name
from score s1
join score s2 on s1.s_id = s2.s_id
and s1.c_id = 1 and s2.c_id = 2
and s1.s_score > s2.s_score 
join student s on s1.s_id = s.s_id 

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

--解1：group by + case when 

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_2
  from score
  group by s_id
  having max(case when c_id = 1 then s_score end) < max(case when c_id = 2 then s_score end) 
)

--解2：自连接
select s1.s_id, s1.s_score  as s1_score ,s2.s_score as s2_score,stu.s_name
from score s1
join score s2 on s1.s_id = s2.s_id
and s1.c_id = 1 and s2.c_id = 2
and s1.s_score < s2.s_score
join student stu on stu.s_id = s1.s_id

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

--解1 子查询中having过滤

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

---------
--解2 外层查询过滤
select * from (
  select s.s_id, s.c_id, s.s_score 
         ,avg(s_score ) over(partition by s.s_id) as avg_score
         ,stu.s_name 
  from score s
  join student stu on s.s_id = stu.s_id
) where avg_score > 60

--解3 ：全部join起来 最后having 过滤
select stu.s_name,s.s_id ,avg(s.s_score ) as avg_score
from score s 
join student stu on stu.s_id = s.s_id 
group by stu.s_name,s.s_id 
having avg(s.s_score ) > 60 

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

--解1：子查询出平局成绩
select stu.s_id, stu.s_name, s_birth, s_sex ,a.avg_score
from student stu
left join (
  select s_id, avg(s_score ) avg_score
  from score
  group by s_id
) a on stu.s_id = a.s_id 
where avg_score < 60 or avg_score is null 

--解2：全部join起来再having

select stu.s_id, s_name,avg(s_score )
from student stu
left join score s
on stu.s_id  = s.s_id 
group by stu.s_id,s_name
having avg(s_score ) < 60 or avg(s_score ) is null


--解3：开窗求avg
select distinct stu.s_id, s_name, s_birth, s_sex ,avg_score
from student stu 
left join(
  select s_id, c_id, s_score 
         ,avg(s_score ) over(partition by s_id) as avg_score
  from score
) a  on stu.s_id = a.s_id 
where avg_score < 60 or avg_score is null 

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

--解1 ：先聚合，stu再join子查询结果

select stu.s_id, s_name, a.xkzs,a.zcj
from student stu join (
  select s_id, count(c_id) as xkzs, sum(s_score ) as zcj
  from score
  group by s_id 
) a on stu.s_id = a.s_id

--解2：全部join之后再聚合
select stu.s_id, s_name
       ,count(c_id ) as xkzs
       ,sum(s_score ) as zcj
from student stu
left join score s
on stu.s_id = s.s_id
group by stu.s_id,s_name

--解3：开窗直接出结果
select distinct stu.s_id, s_name
       ,count(c_id ) over(partition by stu.s_id) xkzs
       ,sum(s_score ) over(partition by stu.s_id) zcj
from student stu
left join score s
on stu.s_id  = s.s_id 

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

--解1：like
select count(t_id) as count_li from teacher
where t_name like '李%'

--解2：substr
select t_id, t_name 
from teacher
where substr(t_name,0,1) = '李'

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

--解1：层层嵌套，找出结果

select stu.s_id,stu.s_name, c_id, s_score 
from score s
join student stu on stu.s_id = s.s_id
where c_id in (
  select c_id
  from course where t_id in (
  select t_id from teacher where t_name = '张三'
  )
)

--解2：全部join之后 再过滤
select stu.s_id, s_name, s_birth, s_sex ,s.c_id,c.c_name
from student stu
join score s on stu.s_id = s.s_id
join course c on c.c_id = s.c_id
join teacher t on t.t_id = c.t_id and t.t_name = '张三'

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

--1 没学过 = not in 学过
select s_id, s_name, s_birth, s_sex 
from student where s_id not in (
  select 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 = '张三'
    )
  )
)

--2 中间结果全部join起来再过滤
select s_id, s_name, s_birth, s_sex 
from student where s_id not in (

     select stu.s_id
     from student stu
     join score s on stu.s_id = s.s_id
     join course c on s.c_id = c.c_id
     join teacher t on t.t_id = c.t_id and t.t_name = '张三'
     
)

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

--1 最直接的解法
select s_id, s_name, s_birth, s_sex from student
where s_id in (select s_id from score where c_id = 01)
and s_id in (select s_id from score where c_id = 02)


--2:减少一步
select s_id, s_name, s_birth, s_sex 
from student
where s_id in (
  select s_id
  from score
  where c_id = 01
  and s_id in (
      select s_id from score where c_id = 02
  )
)

--3:join to filter
select stu.s_id, s_name, s_birth, s_sex 
from student stu
join score s1 on stu.s_id = s1.s_id and s1.c_id = 01
join score s2 on stu.s_id = s2.s_id and s2.c_id = 02

--4：利用count做对比
select s_id, count(c_id)
from score
where c_id in (01,02)
group by s_id
having count(c_id) = 2

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

--1:最直接的解法
select s_id, s_name, s_birth, s_sex 
from student
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)

--2：join to filter
select distinct stu.s_id, s_name, s_birth, s_sex 
from student stu
join score s1 on stu.s_id = s1.s_id and s1.c_id = 1
where stu.s_id not in (select s_id from score where c_id = 2)
--错误写法，！=2  join 会造成 学多门课程的同学 筛选错误
--join score s2 on stu.s_id = s2.s_id and s2.c_id != 2
--where stu.s_id  in (select s_id from score where c_id != 2)

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

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

--1 正向直接选取
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) != (select count(c_id) from course)

)

--2 取反操作
select s_id, s_name, s_birth, s_sex 
from student where s_id not in(
  select s_id
  from score group by s_id 
  having count(c_id) = (select count(c_id ) from course)
)

--3：开窗 稍显麻烦
--查询没有学全所有课程的同学的信息
select stu.s_id, s_name, s_birth, s_sex 
from student stu
join (
  select s_id, c_id, s_score 
         ,count(c_id ) over(partition by s_id ) count_c
  from score
) a on stu.s_id = a.s_id
and a.count_c != (select count(c_id) from course )

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

--1：直观写法，子查询过滤
select s_id, s_name, s_birth, s_sex 
from student where s_id in (
  select s_id
  from score s
  where c_id in (
  select c_id from score where s_id = 1
  )
)
and s_id != 1

--2 : join to filter
select distinct stu.s_id, s_name, s_birth, s_sex 
from student stu
join score s on stu.s_id = s.s_id and stu.s_id != 1
join score s1 on s.s_id = s1.s_id and s1.c_id = 1

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

--开窗求listagg 最直观的写法
--查询和"01"号的同学学习的课程完全相同的其他同学的信息
with data as (
  select s_id, c_id, s_score 
         ,listagg(c_id,',') within group(order by c_id) over(partition by s_id ) as list_c
  from score s 
)

select s_id, s_name, s_birth, s_sex 
from student where s_id in (
  select s_id 
  from data where list_c in (
       select list_c from data where s_id = 1
  )
)and s_id != 1

--2: join 之后比较count的值
select stu.s_id, stu.s_name, stu.s_birth, stu.s_sex 
from student stu join score s
on stu.s_id = s.s_id
join (select s_id, c_id, s_score from score where s_id = 1) a
on  s.c_id = a.c_id and s.s_id != 1
group by stu.s_id,stu.s_name, stu.s_birth, stu.s_sex 
having count(s.c_id) = (select count(c_id) from score where s_id = 1)

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

--1:错误写法，应该先查询学过的 在排除
select stu.s_id, stu.s_name, a.c_id
from student stu
join (
  select s_id, c_id, s_score 
  from score
  where c_id not in (
    select c.c_id
    from teacher t
    join course c
    on t.t_id = c.c_id and t.t_name = '张三'
  )
)a on a.s_id = stu.s_id

--2.正确写法：没学过 ----排除学过的

select s_id, s_name, s_birth, s_sex 
from student where s_id not in (
  select 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 = '张三'
    )
  )
 )

--3.
select s_id, s_name, s_birth, s_sex from student
where s_id not in (
  select s.s_id from score s
  join course c on s.c_id = c.c_id
  join teacher t on c.t_id = t.t_id and t.t_name = '张三'
)

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

-- 解1：开窗求不及格的课程数和平均分
select s.s_id, s.s_name, s.s_birth, s.s_sex ,a.bjg_count,a.pjcj
from student s join (
select distinct s_id
       ,count(case when s_score < 60 then s_score end) over(partition by s_id) as bjg_count
       ,avg(s_score ) over(partition by s_id ) pjcj
from score) a on a.s_id = s.s_id
where bjg_count >= 2

--------

--解2：先用子查询求出s_id
select s.s_id,stu.s_name,avg(s_score ) as pjcj
from score s
join student stu on s.s_id = stu.s_id
where s.s_id in (
  select s_id
  from score
  where s_score < 60
  group by s_id 
  having count(*) >= 2
) 
group by s.s_id,stu.s_name

--解3：结合体

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

------

--解1：先用子查询求出目标stu
select stu.*,s.c_id,s.s_score
from score s
join student stu on stu.s_id = s.s_id
where s.s_id in (
  select s_id
  from score
  where c_id = 01 and s_score < 60
)order by s_score desc

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

--解1：侧视图 挺麻烦的，能用 不建议使用
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

--解2：group max 做行转列
select 
  stu.s_id,pjcj
  ,max(case when c_id = 1 then s_score  end) as sxcj
  ,max(case when c_id = 2 then s_score end) as ywcj
  ,max(case when c_id = 3 then s_score end) as yycj
from (
  select s_id, c_id, s_score 
         ,avg(s_score ) over(partition by s_id ) as pjcj
  from score
)a right join student stu on stu.s_id = a.s_id
group by pjcj,stu.s_id
order by pjcj desc

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

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

select 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

-- 解2 尝试用count做
select c_id
       ,max(s_score) as zgf
       ,min(s_score) as zdf
       ,avg(s_score) as pjf
       ,(count(case when s_score >= 60 then s_id end) / count(distinct s_id ) ) as jgl
       ,(count(case when s_score >= 70 and s_score < 80  then s_id end) / count(distinct s_id ) ) as zdl
       ,(count(case when s_score >= 80 and s_score < 90  then s_id end) / count(distinct s_id ) ) as yll
       ,(count(case when s_score >= 90  then s_id end) / count(distinct s_id ) ) as yxl
from score
group by c_id

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

--解1 开窗求排名，最简单直接
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

--解2 自带rownum，求排名，当心坑
select s_id, c_id, s_score,rownum as rn from (
select s_id, c_id, s_score
from score
order by c_id,s_score desc
) 

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

--解1 ：开窗rank求排名
with data as (
select distinct s_id
       ,sum(s_score) over(partition by s_id) as sum_score
from score
order by sum_score desc
)
select  stu.s_id,data.*,stu.s_name,rank() over(order by coalesce(sum_score,0) desc )  as rank
from data 
right join student stu on stu.s_id = data.s_id
order by rank


--解2：rownum求排名,有一个同学没有成绩，容易漏掉

select s_name,s_id,zcj,rownum as rn from (
  select stu.s_id, coalesce(sum(s_score ),0) as zcj,stu.s_name 
  from score s
  right join student stu on s.s_id = stu.s_id
  group by stu.s_id ,stu.s_name 
  order by zcj desc 
)

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

--解1：直接group by求平均分

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

--解2：开窗求平均分
select s_id,c_id
       ,avg(s_score ) over(partition by s_id, c_id) as pjf
from score
order by pjf desc

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

-- 解1 开窗rank直接求出排名
select stu.s_id, s_name, s_birth, s_sex ,a.c_id,a.s_score
from student stu join (
  select s_id, c_id, s_score 
         ,rank() over(partition by c_id order by s_score desc) as rank
  from score
) a on a.s_id = stu.s_id
and rank between 2 and 3

--解2：emmm 还有什么方法吗？每个课程分段排名？

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

--解1：sum （case when ）求各类
select s.c_id
       ,c_name
       ,coalesce(sum(case when s_score >= 0 and s_score < 60 then 1 end),0) as "[0-60)"
       ,concat(coalesce(round(sum(case when s_score >= 0 and s_score < 60 then 1 end) / count(s_score ) * 100,2),0),'%') as "[0-60) pre"
       ,coalesce(sum(case when s_score >= 60 and s_score < 70 then 1 end),0) as "[60-70)"
       ,concat(coalesce(round(sum(case when s_score >= 60 and s_score < 70 then 1 end) / count(s_score ) * 100,2),0),'%') as "[60-70) pre"
       ,coalesce(sum(case when s_score >= 70 and s_score < 85 then 1 end),0) as "[70-85)"
       ,concat(coalesce(round(sum(case when s_score >= 70 and s_score < 85 then 1 end) / count(s_score ) * 100,2),0),'%') as "[70-85) pre"
       ,coalesce(sum(case when s_score >= 85 and s_score <= 100 then 1 end),0) as "[85-100]"
       ,concat(coalesce(round(sum(case when s_score >= 85 and s_score <= 100 then 1 end) / count(s_score ) * 100,2),0),'%') as "[0-60) pre"
from score s join course c on s.c_id = c.c_id
group by s.c_id,c_name 

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 查询学生平均成绩及其名次

--解1：开窗求平均成绩，排序后 用rownum
select s_id, c_id, s_score ,pjcj,rownum as rn from (
select s_id, c_id, s_score 
       ,round(avg(s_score) over(partition by s_id),2) pjcj
       --禁用套用开窗函数
       --,rank() over(order by round(avg(s_score) over(partition by s_id),2)) as rank 
from score
order by pjcj desc
)

--解2：group 求 avg score
select s_id,avg_score ,s_name
       ,rownum as rn
from(
select stu.s_id,coalesce(round(avg(s_score ),2),0) as avg_score,stu.s_name
from score s 
right join student stu on stu.s_id = s.s_id
group by stu.s_id,stu.s_name
order by avg_score desc)

--3:
select s_id, s_name, s_birth, s_sex ,avg,rownum as rn
from (
select s_id, s_name, s_birth, s_sex 
       ,( select coalesce(round(sum(s_score) / 3,2),0) as sum from score s where stu.s_id = s.s_id) avg
from student stu
order by avg desc
)
  

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 sc
  where (
    select count(*)
    from score sc1
    where sc.c_id = sc1.c_id
    and 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

--解1：开窗求rank
select * from(
select s_id, c_id, s_score 
       ,rank() over(partition by c_id order by s_score desc) as rank
from score)
where rank <= 3

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

--0:
select c_id, c_name, t_id 
       ,( select count(*) from score where c_id = c.c_id ) as hc
from course c


--解1：直接group出
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

--解2：强行开窗
select c.c_id, c_name, t_id ,hc
from course c
join (
  select distinct c_id
         ,count(s_id) over(partition by c_id) as hc
  from score

) a
on c.c_id = a.c_id

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

--解1：group having过滤课程数
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
) 

--解2：开窗求课程数
select stu.s_id, s_name, s_birth, s_sex ,kcs
from student  stu join (
select s_id, c_id, s_score 
       ,count(c_id) over(partition by s_id) as kcs
from score
) a on a.s_id = stu.s_id
and kcs = 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 统计同姓的人员名单，打印 姓 人数 姓名

--1：substr() 欧阳怎么办？
with data as (
  select s_id, s_name, s_birth, s_sex 
         ,case when length(s_name) < 4 then substr(s_name,0,1) 
               when length(s_name) >= 4 then substr(s_name,0,2) 
               end as first_name
  from student
) 
select first_name,count(s_id) as hc
from data
group by first_name

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 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

--1：group 出结果
select c_id,round(avg(s_score ),2) as pjcj
from score  
group by c_id
order by pjcj desc,c_id

--2：强行开窗
select * from (
select s_id, c_id, s_score 
       ,round(avg(s_score) over(partition by c_id),2) as pjcj
from score
) order by pjcj desc,c_id

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

--1：group 求平均
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

--2：开窗求平均
select stu.s_id, s_name, s_birth, s_sex ,pjcj
from student stu join (
select s_id, c_id, s_score
       ,round(avg(s_score) over(partition by s_id),2) as pjcj
from score
)a on a.s_id  = stu.s_id
and pjcj >= 85

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

--1：层层过滤
select s.s_id, c_id, s_score ,stu.s_name
from score s
join student stu on stu.s_id = s.s_id
where c_id in (
      select c_id from course where c_name = '数学'
) and s_score < 60 or s_score is null

--2：全部join起来
select stu.s_id, s_name, s_birth, s_sex 
from student stu 
join score s on s.s_id = stu.s_id and (s.s_score <= 60 or s.s_score is null)
join course c on s.c_id = c.c_id and c.c_name = '数学'

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

Result1 group

select stu.s_id, s_name, s_birth, s_sex 
       ,max(case when s.c_id = 1 then s.s_score end) as 数学
       ,max(case when s.c_id = 2 then s.s_score end) as 语文
       ,max(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 score
  PIVOT (
    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分以上的学生姓名、课程名称和分数

--1：查询任何一门课程成绩在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_score
     from score
     ) s
on stu.s_id = s.s_id
and s.max_score > 70 
join course c
on s.c_id = c.c_id

--2：只要超过70分课程的版本
select s.s_id, s.c_id, s_score ,stu.s_name
from score s
join (
  select s_id,c_id
  from score
  where s_score > 70
) a on s.s_id = a.s_id and s.c_id = a.c_id
join student stu
on stu.s_id = s.s_id

37 查询课程不及格的学生

--1
select s.s_id, c_id, s_score ,stu.s_name
from score s
join student stu on stu.s_id = s.s_id
where s_score < 60 or s_score is null

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

--1：先筛选
select s_id, s_name, s_birth, s_sex 
from student s
where s_id in(
      select s_id
      from score
      where c_id = 1 and s_score >= 80
)

--2：先join起来再筛选
select s.s_id, c_id, s_score ,stu.s_name
from score s
join student stu on stu.s_id = s.s_id
where c_id = 01 and s_score > 80

39 查询每门课程的人数

--1 先聚合再join
select c.c_id, c_name, t_id ,count_s 
from course c
left join(
     select c_id,count(s_id) as count_s
     from score
     group by c_id
)a
on c.c_id = a.c_id


--2 先join起来再聚合
select c.c_id, c_name, count(s_id ) as hc
from course c
left join score s on s.c_id = c.c_id
group by c.c_id, c_name

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

select * from (
select s.s_id, c_id, s_score ,stu.s_name,stu.s_birth,stu.s_sex
from score s
join student stu on stu.s_id = s.s_id
where c_id in (
  select c_id
  from course where t_id in (
       select t_id from teacher where t_name = '张三'
  )
) order by s_score desc
) where rownum = 1

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

select s.s_id,stu.s_name,s.c_id,s.s_score 
from score s
join  (
      select s_id
      from score
      group 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 (
      select
            s_id,c_id,s_score
            ,count(c_id) over(partition by s_id,s_score) as count_score
      from score
)a
join student s
on s.s_id = a.s_id
and count_score > 1

--2：
select stu.s_id, c_id, s.s_score ,stu.s_name
from score s join (
	select distinct s_id,  s_score 
	from score 
	group by s_id,s_score
	having count(s_score) > 1
) a on s.s_id = a.s_id and s.s_score = a.s_score
join student stu on stu.s_id = s.s_id

42 统计每门课程的前几名

--1：window
select stu.s_id, s_name, s_birth, s_sex 
from student stu
join (
select s_id, c_id, s_score 
       ,rank() over(partition by  c_id order by s_score desc) rank
from score
)a on stu.s_id = a.s_id and a.rank <= 3

--2：怎么用rownum直接求出？

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

--1：直接统计
select  c.c_id,count(s_id) as xkrs
from score s
join course c on s.c_id = c.c_id
group by c.c_id
having count(s_id) > 5
order by count(s_id ) desc,c.c_id

--2：开窗
select distinct c.c_id, c_name, t_id 
from course c 
join (
select s_id, c_id, s_score 
       ,count(s_id) over(partition by c_id) as xkrs
from score
)a on c.c_id = a.c_id and a.xkrs > 5

44 查询选修了2门课的sid

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


select * from (
select s_id, s_name, s_birth, s_sex 
       ,( select count(distinct c_id) from score where s_id = stu.s_id) as cnt
from student stu
)
where cnt > 1

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

--1：having count 
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) = (select count(c_id) from course)
)

46 求学生周岁

select
  s_name ,s_birth 
  ,trunc(MONTHS_BETWEEN(SYSDATE, s_birth ) / 12)

from student

47 本周过生日的同学

select
  to_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 s_id, s_name, s_birth, s_sex 
  from student
  where to_char(s_birth,'mm-dd') 
  between to_char((trunc(sysdate,'IW')+7),'mm-dd') 
  and to_char((trunc(sysdate,'IW')+13),'mm-dd')

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'

文章来源地址https://www.toymoban.com/news/detail-814151.html

到了这里，关于Oracle 经典练习题 50 题的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！