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