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

昆拗干 · 昨天 20:05

建表

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

-- 学生表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<br><br>
-- 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

复制代码

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

账号		自动登录	找回密码
密码			立即注册

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

相关帖子

浏览过的版块

签约作者

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

相关帖子

相关推荐

浏览过的版块

签约作者