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

--查询"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_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)
)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_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) 
)

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_2
  from score
  group 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_score
  from score
  group 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 score
  where 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_id
  from score where c_id in (
  --select c.c_id from teacher t,course c where t_name = '张三' and t.t_id = c.c_id
  select 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_id
  from score
  where c_id = 01
  and 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_id
  from score
  where 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_id
  from score
  group 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_id
  from score
  where 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 data
  where 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_score
  from score s
  inner join (
        select c_id from score where s_id = 1
  )a on a.c_id = s.c_id
  where s_id != 1
  group by s_id
  having 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 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

-- 没学过 -> 首先想到 排除学过的

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，优秀为：>=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

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 rank
 from 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_dj
 from 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 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

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 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分以上的学生姓名、课程名称和分数

--查询任何一门课程成绩在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

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_id
      from score
      where 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_s
     from score
     group 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_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

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

42 统计每门课程的前几名

select a.c_id ,c_name ,a.s_id ,s_name ,s_score 
from (
select
  s_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 才统计

--要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select
  c_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

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

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

select * 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
  *
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'