Oracle SQL经典练习50题 | 附答案

• 建表

建表语句可以先根据自己对表关系进行设计，自定义发挥，写法不局限。

-- 学生表student()
create table student(
       stu_id number generated always as identity,
       stu_name varchar2(80) not null,
       stu_birthday date,
       stu_sex varchar(2),
       primary key(stu_id),
       check(stu_sex in ('m','f'))
);

--课程表course（id,课程名，老师id）
create table course(
       cou_id number generated always as identity primary key ,
       cou_name varchar2(80),
       tea_id number,
       constraint fk_tea_id foreign key (tea_id) references teacher(tea_id)
    
);

--教师表teacher（id,名字）
create table teacher(
       tea_id number generated always as identity primary key,
       tea_name varchar2(80)
);

--成绩表score
create table score(
       sco_id number generated always as identity primary key,
       stu_id number,
       cou_id number,
       score number
       
);

• 插入数据

以下只是初步的一个数据插入，并非一层不变，可以根据后续条件的需求再来调整。

-- 学生信息
INSERT INTO STUDENT VALUES(Default, '赵雷' , to_date('1990-01-01','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(Default, '钱电' , to_date('1990-12-21','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(Default, '孙风' , to_date('1990-12-20','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(Default, '李云' , to_date('1990-12-06','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(Default, '周梅' , to_date('1991-12-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(Default, '吴兰' , to_date('1992-01-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(Default, '郑竹' , to_date('1989-01-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(Default, '张三' , to_date('2017-12-20','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(Default, '李四' , to_date('2017-12-25','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(Default, '李四' , to_date('2012-06-06','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(Default, '赵六' , to_date('2013-06-13','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(Default, '孙七' , to_date('2014-06-01','YYYY-MM-DD') , 'f');

select * from STUDENT;
 
-- 课程信息
INSERT INTO COURSE VALUES(Default, '语文' , 2);
INSERT INTO COURSE VALUES(Default, '数学' , 1);
INSERT INTO COURSE VALUES(Default, '英语' , 3);

select * from COURSE;
 
-- 教师信息
INSERT INTO TEACHER VALUES(Default, '张三');
INSERT INTO TEACHER VALUES(Default, '李四');
INSERT INTO TEACHER VALUES(Default, '王五');

select * from teacher;
 
-- 成绩
INSERT INTO SCORE VALUES(default,1 , 1 , 80);
INSERT INTO SCORE VALUES(default,1 , 2 , 90);
INSERT INTO SCORE VALUES(default,1 , 3 , 99);
INSERT INTO SCORE VALUES(default,2 , 1 , 70);
INSERT INTO SCORE VALUES(default,2 , 2 , 60);
INSERT INTO SCORE VALUES(default,2 , 3 , 80);
INSERT INTO SCORE VALUES(default,3 , 1 , 80);
INSERT INTO SCORE VALUES(default,3 , 2 , 80);
INSERT INTO SCORE VALUES(default,3 , 3 , 80);
INSERT INTO SCORE VALUES(default,4 , 1 , 50);
INSERT INTO SCORE VALUES(default,4 , 2, 30);
INSERT INTO SCORE VALUES(default,4, 3 , 20);
INSERT INTO SCORE VALUES(default,5 , 1, 76);
INSERT INTO SCORE VALUES(default,5, 2, 87);
INSERT INTO SCORE VALUES(default,6 , 1, 31);
INSERT INTO SCORE VALUES(default,6 , 3, 34);
INSERT INTO SCORE VALUES(default,7 , 2 , 89);
INSERT INTO SCORE VALUES(default,7, 3 , 98);

• SQL习题

以下写法只是个人见解，如有问题欢迎指出，相互学习，一起进步。

-- 1.查询"数学 "课程比" 语文 "课程成绩高的学生的信息及课程分数
select stu.*, c.couid1, c.sco1,  c.couid2, c.sco2 from student stu ,
(
select a.stu_id, a.cou_id as couid1, a.score as sco1, b.cou_id as couid2, b.score as sco2 from 
(select * from score where cou_id = 2) a,
(select * from score where cou_id = 1) b
where a.stu_id = b.stu_id and a.score > b.score
)c
where stu.stu_id = c.stu_id

-- 优化:
with
--数学成绩临时表
math_score as (
select *from score where cou_id = 2
),
--语文成绩临时表
chinese_score as (
select *from score where cou_id = 1
)

select * from 
student stu,math_score ms,chinese_score cns
where 
stu.stu_id = ms.stu_id 
and stu.stu_id = cns.stu_id 
and ms.score > cns.score

-- 1.1 查询同时存在" 数学 "课程和" 语文 "课程的情况
with
math_score as (
select * from score sco where sco.cou_id = 2
),
chinese_score as (
select * from score sco where sco.cou_id = 1
)

select * from 
math_score ms, chinese_score cns
where 
ms.stu_id = cns.stu_id

-- 1.2 查询存在" 数学 "课程但可能不存在" 语文 "课程的情况(不存在时显示为 null )
select * from 
(select * from score sco where sco.cou_id = 2) m
left join 
(select * from score sco where sco.cou_id = 1) c
on m.stu_id = c.stu_id

-- 1.3 查询不存在" 数学 "课程但存在" 语文 "课程的情况
--方式1
select * from score sco where sco.cou_id = 1
and sco.stu_id not in 
(select sco.stu_id from score sco where sco.cou_id = 2) 

--方式2
select * from score sc where sc.cou_id = 1
and not exists
(
select 1 from score scs where sc.stu_id = scs.stu_id and scs.cou_id = 2
)

-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select stu.stu_id, stu.stu_name, round(avg(sco.score),2) as avg_sco from student stu, score sco 
where stu.stu_id = sco.stu_id
group by stu.stu_id, stu.stu_name
having avg(sco.score) > 60


-- 3.查询在 成绩 表存在成绩的学生信息
select distinct stu.* from student stu, score sco 
where stu.stu_id = sco.stu_id

-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select stu.stu_id, stu.stu_name, count(sco.cou_id) as count_cou , sum(sco.score) as sun_score from student stu
left join score sco 
on stu.stu_id = sco.stu_id
group by stu.stu_id, stu.stu_name
order by sum(sco.score)

-- 4.1 查有成绩的学生信息
select * from student stu
right join score sco 
on stu.stu_id = sco.stu_id 

-- 5.查询「李」姓老师的数量
select * from teacher tea where tea.tea_name like '李%'

-- 6.查询学过「张三」老师授课的同学的信息
select * from student stu,score sco 
where stu.stu_id = sco.stu_id
and sco.cou_id in
(select cou.cou_id from course cou where cou.tea_id = (select tea.tea_id from teacher tea where tea.tea_name = '张三'))

--优化
select * from student stu 
join score sco on stu.stu_id = sco.stu_id
join course cou on sco.cou_id = cou.cou_id
join teacher tea on cou.tea_id = tea.tea_id
where tea.tea_name = '张三'

-- 7.查询没有学全所有课程的同学的信息
select * from student stu 
left join score sco 
on stu.stu_id = sco.stu_id 
where stu.stu_id not in 
(select sco.stu_id from score sco group by sco.stu_id having count(cou_id) >= 3) 

--优化
select stu.stu_id, stu.stu_name from student stu
left join score sco 
on stu.stu_id = sco.stu_id 
group by stu.stu_id, stu.stu_name
having count(sco.cou_id) < (select count(*) from course)
order by stu.stu_id

-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select stu.stu_id, stu.stu_name, sco.cou_id from student stu 
join score sco 
on stu.stu_id = sco.stu_id
where sco.cou_id in (select sco.cou_id from score sco where sco.stu_id = 1)
order by stu.stu_id

-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select stu.stu_id, stu.stu_name from score sco 
join student stu on sco.stu_id = stu.stu_id
where stu.stu_id != 1
group by stu.stu_id, stu.stu_name
having count(*) = (select count(*) from score where stu_id = 1)
AND NOT EXISTS (
        SELECT 1
        FROM score s1
        WHERE s1.stu_id = 1
        AND s1.cou_id NOT IN (
            SELECT cou_id
            FROM score s2
            WHERE s2.stu_id = stu.stu_id
        )
    );

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student stu
where stu.stu_id not in
(select sco.stu_id from score sco 
join course cou on sco.cou_id = cou.cou_id
join teacher tea on cou.tea_id = tea.tea_id
where tea.tea_name = '张三')

--优化
select * from student stu
where not exists
(
select 1 from score sco 
join course cou on sco.cou_id = cou.cou_id
join teacher tea on cou.tea_id = tea.tea_id
where tea.tea_name = '张三'
and sco.stu_id = stu.stu_id
)

-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select stu.stu_id, stu.stu_name, round(avg(sco.score),2) from student stu
join score sco
on stu.stu_id = sco.stu_id
where stu.stu_id in 
(
select sco.stu_id from score sco 
where sco.score < 60
group by sco.stu_id
having count(*) >= 2
)
group by stu.stu_id, stu.stu_name;

--优化1
select 
stu.stu_id, stu.stu_name, round(avg(sco.score),2) as avg_score 
from 
student stu
join 
score sco on stu.stu_id = sco.stu_id
where Exists 
(
select 1 
from score sco 
where sco.stu_id = stu.stu_id
and sco.score < 60
group by sco.stu_id
having count(*) >= 2
)
group by 
stu.stu_id, stu.stu_name;

--优化2
with bad_stu as (
select sco.stu_id from score sco 
where sco.score < 60
group by sco.stu_id
having count(*) >= 2
)
select stu.stu_id, stu.stu_name, round(avg(sco.score),2) from student stu
join score sco
on stu.stu_id = sco.stu_id
where stu.stu_id in 
(
select stu_id from bad_stu
)
group by stu.stu_id, stu.stu_name;

-- 12.检索" 数学 "课程分数小于 60，按分数降序排列的学生信息
select * from score sco 
join course cou on sco.cou_id = cou.cou_id
where cou.cou_name = '数学'
and sco.score < 60 
order by sco.score desc -- order by 默认是asc 升序，降序为 desc

-- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
with avg_score as (
select sco.stu_id, round(avg(sco.score),2) as avg_sco from score sco 
group by sco.stu_id
)

select stu.stu_id, stu.stu_name, sco.cou_id, sco.score, avgsco.avg_sco from score sco 
join student stu on sco.stu_id = stu.stu_id
join avg_score avgsco on stu.stu_id = avgsco.stu_id
order by avgsco.avg_sco desc, sco.score DESC;

--优化
select 
      stu.stu_id, stu.stu_name, sco.cou_id, sco.score, round(avg(sco.score)over(partition by stu.stu_id),2) as avg_sco
from 
      score sco 
join student stu on sco.stu_id = stu.stu_id
order by avg_sco desc, sco.score desc

-- 14.查询各科成绩最高分、最低分和平均分：
select 
   sco.cou_id, cou.cou_name, max(sco.score)as max_sco, min(sco.score) as min_sco, round(avg(sco.score),2) as avg_sco 
from score sco
join course cou on sco.cou_id = cou.cou_id 
group by sco.cou_id, cou.cou_name

-- 15.以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

/*
  及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
 
  要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
 
  按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

*/ 
with score_stats as 
(
select 
   sco.cou_id, cou.cou_name, count(*) as stu_count, max(sco.score)as max_sco, min(sco.score) as min_sco, round(avg(sco.score),2) as avg_sco,
   --及格人数
   sum(case when sco.score >= 60 then 1 else 0 end) as pass_count,
   --中等人数
   sum(case when sco.score >= 70 and sco.score < 80 then 1 else 0 end) as mid_count,
   --优良人数
   sum(case when sco.score >= 80 and sco.score < 90 then 1 else 0 end) as good_count,
   --优秀人数 
   sum(case when sco.score >= 90 then 1 else 0 end) as excellent_count
from score sco
join course cou on sco.cou_id = cou.cou_id 
group by sco.cou_id, cou.cou_name
)

select
 scos.cou_id,
 scos.cou_name, 
 scos.stu_count,
 scos.max_sco,
 scos.min_sco, 
 scos.avg_sco,
  -- 计算比率
 round((scos.pass_count / scos.stu_count) * 100, 2) as pass_rate,
 round((scos.mid_count / scos.stu_count) * 100, 2) as mid_rate,
 round((scos.good_count / scos.stu_count) * 100, 2) as good_rate,
 round((scos.excellent_count / scos.stu_count) * 100, 2) as excellent_rate,
 -- 按各科平均成绩进行排名，
 rank()over(order by avg_sco desc) as rank_no
from
 score_stats scos
order by 
 scos.stu_count desc, scos.cou_id asc

-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select sco.*, rank()over(partition by sco.cou_id order by sco.score desc) as rank_by_score from score sco 

-- 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select
 stu.stu_id, stu.stu_name, coalesce(sum(sco.score),0) as sum_sco, rank()over(order by coalesce(sum(sco.score),0) desc ) as rank_no 
from
 score sco 
join
 student stu
on
 sco.stu_id = stu.stu_id
group by
 stu.stu_id, stu.stu_name

-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
--rank(): 有并列，跳过，例如 1,1,3
--dense_rank(): 有并列，不跳过，例如：1,1,2
select
 stu.stu_id, stu.stu_name, coalesce(sum(sco.score),0) as sum_sco, dense_rank()over(order by coalesce(sum(sco.score),0) desc ) as rank_no 
from
 score sco 
join
 student stu
on
 sco.stu_id = stu.stu_id
group by
 stu.stu_id, stu.stu_name

-- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-90]，(90-70]，(70-60]，(60-0] 及所占百分比
with score_rates as 
(
select
 sco.cou_id,
 cou.cou_name,
 count(*) as stu_count,
 sum(case when sco.score >=0 and sco.score < 60 then 1 else 0 end) as notpass_count,
 sum(case when sco.score >=60 and sco.score < 70 then 1 else 0 end) as mid_count,
 sum(case when sco.score >=70 and sco.score < 90 then 1 else 0 end) as good_count,
 sum(case when sco.score >=90 and sco.score <= 100 then 1 else 0 end) as excellent_count
from score sco
join course cou on sco.cou_id = cou.cou_id
group by sco.cou_id, cou.cou_name
)


select
 scos.cou_id,
 scos.cou_name,
 scos.stu_count,
 scos.notpass_count,
 scos.mid_count,
 scos.good_count,
 scos.excellent_count,
 --占比
 round((scos.notpass_count / scos.stu_count) * 100,2) ||'%' as  notpass_rate,
 round((scos.mid_count / scos.stu_count) * 100,2) ||'%' as  mid_rate,
 round((scos.good_count / scos.stu_count) * 100,2) ||'%' as  good_rate,
 round((scos.excellent_count / scos.stu_count) * 100,2) ||'%' as  excellent_rate
from score_rates scos

-- 18.查询各科成绩前三名的记录
select * from 
(
    select sco.*, dense_rank()over(partition by cou_id order by sco.score desc) as dense_rank_no from score sco
) 
where dense_rank_no <= 3


-- 19.查询每门课程被选修的学生数
select
 sco.cou_id,
 cou.cou_name,
 count(*) as stu_count
from score sco
join course cou on sco.cou_id = cou.cou_id
group by sco.cou_id, cou.cou_name

-- 20.查询出只选修两门课程的学生学号和姓名

select sco.stu_id, stu.stu_name from score sco 
join student stu on sco.stu_id = stu.stu_id
group by sco.stu_id, stu.stu_name
having count(sco.stu_id) = 2

-- 21.查询男生、女生人数
select stu.stu_sex, count(*) from student stu 
group by stu.stu_sex

-- 22.查询名字中含有「风」字的学生信息
select * from student stu 
where stu.stu_name like '%风%'

-- 23.查询同名同性学生名单，并统计同名人数(错0
select stu.stu_name, count(*) from student stu 
group by stu.stu_name
having count(*) >= 2

-- 24.查询 1990 年出生的学生名单
--方式一：性能差，无法使用索引
select * from student stu 
where to_char(stu.stu_birthday, 'yyyy') = '1990'

-- 方式二：性能好，可以使用索引
select * from student stu 
where stu.stu_birthday >= to_date('1990-01-01','yyyy-mm-dd')
and stu.stu_birthday < to_date('1991-01-01','yyyy,mm-dd')

--方式三：性能差，无法使用索引
select * from student stu 
where extract(year from stu.stu_birthday) = '1990'

-- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select sco.cou_id, round(avg(sco.score),2) as avg_sco from score sco
group by sco.cou_id
order by round(avg(sco.score),2) desc, sco.cou_id asc

-- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select sco.stu_id,stu.stu_name, round(avg(sco.score),2) from score sco
join student stu on sco.stu_id = stu.stu_id 
group by sco.stu_id, stu.stu_name
having round(avg(sco.score),2) >= 85

-- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select stu.stu_id,stu.stu_name,sco.cou_id,sco.score from score sco 
join student stu on sco.stu_id = stu.stu_id
join course cou on sco.cou_id = cou.cou_id
where cou.cou_name = '数学'
and score <60

-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select * from student stu 
left join score sco on stu.stu_id = sco.stu_id

-- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select stu.stu_id, stu.stu_name, cou.cou_id, cou.cou_name, sco. score from score sco 
join student stu on sco.stu_id = stu.stu_id
join course cou on sco.cou_id = cou.cou_id
where sco.score > 70

-- 30.查询不及格的课程
select sco.stu_id, sco.cou_id, cou.cou_name, sco.score from score sco 
join course cou on sco.cou_id = cou.cou_id
where sco.score < 60

-- 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select stu.stu_id, stu.stu_name, sco.cou_id, score from score sco
join student stu on sco.stu_id = stu.stu_id 
where sco.cou_id = 1
and sco.score > 80

-- 32.求每门课程的学生人数

-- 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.stu_id, stu.stu_name, cou.cou_id, cou.cou_name, sco.score from score sco 
join course cou on sco.cou_id = cou.cou_id
join teacher tea on cou.tea_id = tea.tea_id
join student stu on sco.stu_id = stu.stu_id
where tea.tea_name = '张三'
order by sco.score desc
FETCH FIRST 1 ROWS ONLY; 

--select * from score ;
--update score sco set sco.score = 90 where sco.stu_id in (5,7) ;

--34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.stu_id, stu.stu_name, cou.cou_id, cou.cou_name, sco.score from score sco 
join course cou on sco.cou_id = cou.cou_id
join teacher tea on cou.tea_id = tea.tea_id
join student stu on sco.stu_id = stu.stu_id
where tea.tea_name = '张三'
order by sco.score desc
fetch first 1 rows with ties; -- 如果有多个最高分会全部显示出来

-- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
with repeat_count as (
select sco.cou_id, sco.score, count(*), sco.cou_id || '' || sco.score as cou_and_sco from score sco
group by sco.cou_id, sco.score
having count(*) >= 2
)
select stu.stu_id, stu.stu_name, sco.cou_id, sco.score from score sco 
join student stu on sco.stu_id = stu.stu_id
where sco.cou_id || '' || sco.score in (select cou_and_sco from repeat_count )
order by sco.cou_id, sco.score
-- 拼接字符串存在隐患：sco.cou_id || '' || sco.score，如果cou_id 或 score 是null，整个拼接结果为null，导致匹配失败
-- 性能问题：in在大数据中查询效率较低，且以上代码in子查询返回的是字符串拼接的结果，数据库无法有效利用索引

-- 优化方案1：避免字符串拼接，Exist性能比in好       
with repeat_count as (
select sco.cou_id, sco.score, count(*), sco.cou_id || '' || sco.score as cou_and_sco from score sco
group by sco.cou_id, sco.score
having count(*) >= 2
)
select stu.stu_id, stu.stu_name, sco.cou_id, sco.score from score sco 
join student stu on sco.stu_id = stu.stu_id
where exists 
(
      select 1 from repeat_count rc  where sco.cou_id = rc.cou_id and sco.score = rc.score
)
order by sco.cou_id, sco.score;

-- 优化方案2:使用count()over()函数，只需扫描score表一次，性能高，时候大数据场景
select stu.stu_id,stu.stu_name,scos.cou_id, scos.score from 
(select sco.*, count(*)over(partition by sco.cou_id, sco.score) as repeat_count from score sco) scos
join student stu on scos.stu_id = stu.stu_id
where scos.repeat_count >= 2

--优化方案3：in + 行构造器，语法简洁
select
 stu.stu_id, stu.stu_name, sco.cou_id, sco.score
from
 score sco 
join student stu on sco.stu_id = stu.stu_id
where (sco.cou_id, sco.score) in
(
   select cou_id, score from score group by cou_id, score having count(*) >=2
)
ORDER BY sco.cou_id, sco.score;


-- 36.查询每门功成绩最好的前两名
with rank_score as 
(
select sco.*, dense_rank()over(partition by sco.cou_id order by sco.score desc) as rank_no from score sco
)

select stu.stu_id,stu.stu_name,rsco.cou_id,rsco.score,rsco.rank_no from rank_score rsco 
join student stu on rsco.stu_id = stu.stu_id
where rsco.rank_no <= 2

-- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select sco.cou_id, count(*) as stu_count
from score sco
group by sco.cou_id
having count(*) > 5


-- 38.检索至少选修两门课程的学生学号
select sco.stu_id, count(*) as stu_cou_count from score sco group by sco.stu_id having count(*) >=2

-- 39.查询选修了全部课程的学生信息
select sco.stu_id, stu.stu_name, count(*) as stu_cou_count from score sco
join student stu on stu.stu_id = sco.stu_id
group by sco.stu_id,stu.stu_name
having count(*) = (select count(*) from course cou)

-- 40.查询各学生的年龄，只按年份来算
select stu.*,extract(year from sysdate)-extract(year from stu.stu_birthday) as age  from student stu 
--方式二：
select stu.*,to_char(sysdate,'yyyy') - to_char(stu.stu_birthday,'yyyy') as age  from student stu 

-- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select stu.*, floor(months_between(sysdate,stu.stu_birthday)/12) as age  from student stu 


-- 42.查询本周过生日的学生

--方案一：
--逻辑：将学生的生日换成今年的日期，然后再判断该日期是否在本周一到周日内

--select *from student;
--update student set stu_birthday = to_date('1990-4-22','yyyy-mm-dd') where stu_id = 2

select * from student stu
where
-- 将学生的生日转成今年的日期
trunc(
 to_date
 (
 to_char(sysdate,'yyyy')||'-'||to_char(stu.stu_birthday,'mm-dd'),'yyyy-mm-dd'
 )
)
between
trunc(sysdate,'IW') -- 本周的起始日期，周一
and
trunc(sysdate,'IW')+6; -- 本周的起始日期，周日

--方案二：
--逻辑：只关心月和日，学生的生日月日是否在本周内
select * from student stu
where 
to_char(stu.stu_birthday,'mm-dd')
between
to_char(trunc(sysdate,'IW'),'mm-dd')
AND 
to_char(trunc(sysdate,'IW')+6,'mm-dd')


-- 43.查询下周过生日的学生
select * from student stu
where
-- 将学生的生日转成今年的日期
trunc(
 to_date
 (
 to_char(sysdate,'yyyy')||'-'||to_char(stu.stu_birthday,'mm-dd'),'yyyy-mm-dd'
 )
)
between
trunc(sysdate,'IW')+7 -- 本周的起始日期，周一
and
trunc(sysdate,'IW')+13; -- 本周的起始日期，周日

-- 44.查询本月过生日的学生
--方式1
select * from student stu
where 
extract(month from stu.stu_birthday) = extract(month from sysdate)
--方式2
select * from student stu
where 
to_char(stu.stu_birthday,'mm') = to_char(sysdate,'mm')

-- 45.查询下月过生日的学生
select * from student stu
where 
extract(month from stu.stu_birthday) = extract(month from sysdate)+1