Oracle 面试题

数据

create table users(
"uid" number(5) primary key,
uname varchar2(3)
);
insert into users values(1001,'AAA');
insert into users values(1002,'BBB');
insert into users values(1003,'CCC');
insert into users values(1004,'DDD');

select * from users;

create table student(
s_id number(10) primary key,
s_name varchar2(10),
s_birth date,
s_sex varchar2(10)
);

insert into student values(1001,'赵雷',to_date('1990/1/1','yyyy/mm/dd'),'男');
insert into student values(1002,'钱电',to_date('1990/12/21','yyyy/mm/dd'),'男');
insert into student values(1003,'李云',to_date('1990/8/6','yyyy/mm/dd'),'女');
insert into student values(1004,'吴兰',to_date('1990/3/1','yyyy/mm/dd'),'女');
select * from student;

create table score(
s_id number(10),
c_id varchar2(10),
s_score number(10)
);

insert into score values(1001,'c1',68);
insert into score values(1001,'c2',79);
insert into score values(1001,'c3',85);
insert into score values(1002,'c2',90);
insert into score values(1002,'c3',56);
insert into score values(1003,'c1',82);
insert into score values(1003,'c2',96);
insert into score values(1003,'c3',48);
insert into score values(1004,'c1',46);
insert into score values(1004,'c2',76);
select * from score;
create table course(
c_id varchar2(10) primary key,
course varchar2(10)
);


insert into course values('c1','语文');
insert into course values('c2','数学');
insert into course values('c3','英语');
select * from course;
create table orders(
"uid" number(4),
foreign key("uid") references users("uid"),
order_sn varchar2(50),
create_time date,
order_amount number
);

insert into orders values(
1001,'E0001',date '2021-1-1',200);
insert into orders values(1001,
'E0002',date '2021-1-1',150);
insert into orders values(
1002,'D3092',date '2021-1-1',100);
insert into orders values(
1002,'D3093',date '2021-1-2',80);
insert into orders values(
1003,'D3094',date '2021-1-3',66);
insert into orders values(
1003,'E0003',date '2021-1-3',120);

select * from orders;
commit;

学生成绩表习题

1. 请查出各科得分情况，输出字段：课程名称，课程总分，课程最低分，课程最高分
2. 请查出至少有两门课程超过 75 分的学生姓名
3. 请查出各科的最高成绩，输出：课程名称、学生 ID、分数
4. 请查出总分超过 200 分的学生分数并按平均分倒序，输出字段：学生 id、总分数、平均分数
5. 请查出学生成绩排行榜，如没考得 0 分，输出字段：学生 id，语文、数学、英语，总分

1.请查出各科得分情况，输出字段：课程名称，课程总分，课程最低分，课程最高分
with
a as(select c_id,sum(s_score)课程总分,min(s_score) 课程最低分,max(s_score)课程最高分
      from score group by c_id),
b as(select * from course)
select course,课程总分,课程最低分,课程最高分 from a join b on a.c_id = b.c_id;

2.请查出至少有两门课程超过75分的学生姓名
select s_name from student where s_id in
  (select s_id from score where s_score > 75 group by s_id having count(1) > 1);

3.请查出各科的最高成绩，输出：课程名称、学生ID、分数
with
a as( select s_id,c_id,s_score,
    row_number() over(partition by c_id order by s_score desc)r from score),
b as(select * from course)
select s_id,course,s_score from a  join b on a.c_id = b.c_id where r=1;


select s_id,c_id,s_score,
    max(s_score) over(partition by c_id ) from score;
--group by
with
a as(select c_id,max(s_score) 最高分 from score group by c_id),
b as(select * from score),
c as(select * from course)
select course,s_id,s_score from a join b on a.c_id = b.c_id and s_score = 最高分
join  c 
on a.c_id  = c.c_id ;

4.请查出总分超过200分的学生分数并按平均分倒序，输出字段：学生id、总分数、平均分数

select s_id,sum(s_score),avg(s_score) 
from score 
group by s_id 
having sum(s_score)>200
order by avg(s_score) desc;

5.请查出学生成绩排行榜，如没考得0分，输出字段：学生id，语文、数学、英语，总分
with
a as (select * from score),
b as (select * from course)
select s_id,course,s_score from b  join a  on a.c_id  = b.c_id;


select s_id,
sum(case when course='语文' then s_score else 0 end) 语文,
sum(case when course='数学' then s_score else 0 end) 数学,
sum(case when course='英语' then s_score else 0 end) 英语,
sum(case when course='语文' then s_score else 0 end)+
sum(case when course='数学' then s_score else 0 end)+
sum(case when course='英语' then s_score else 0 end) 总分
from
(with
a as (select * from score),
b as (select * from course)
select s_id,course,s_score from b  join a  on a.c_id  = b.c_id)
group by s_id;

select s_id,语文,数学,英语,语文+数学+英语 总分
from
  (select s_id,
  sum(case when course='语文' then s_score else 0 end) 语文,
  sum(case when course='数学' then s_score else 0 end) 数学,
  sum(case when course='英语' then s_score else 0 end) 英语
  from
  (with
  a as (select * from score),
  b as (select * from course)
  select s_id,course,s_score from b  join a  on a.c_id  = b.c_id)
  group by s_id) order by 总分 desc;

订单表习题

1.请查出上图中连续 2 天都有下单的用户名单

select * from
(select uid,create_time-lag(create_time,2,create_time) 
over(partition by uid order by create_time) 连续下单间隔
from orders)where 连续下单间隔>1;

2.请查出上图中连续 2 天都有下单的用户名单请查出每天销售额情况，按日期升序、输出：日期、当月累计销售额（当月 1 日累计截止到当前日，如上图中 1 日=450,2 日=1 日 +80,3 日=2 日 +186）

select create_time, 当日销售额,sum(当日销售额) 
over(partition by to_char(create_time,'yyyymm') order by create_time) 当月累计销售额
from(
select create_time,sum(order_amount) 当日销售额 from orders group by create_time)

3.请查出 1 月份销售额情况，按日期升序，输出：日期，当日销售额，当日下单人数，当月累计销售额，当月累计下单人数。

with 
a as (select create_time, 当日销售额,sum(当日销售额) 
over(partition by to_char(create_time,'yyyymm') order by create_time) 当月累计销售额
from(
select create_time,sum(order_amount) 当日销售额 from orders group by create_time)),

b as(select create_time, 当日下单人数,sum(当日下单人数) 
over(partition by to_char(create_time,'yyyymm') order by create_time) 当月累计下单人数
from(
select create_time,count(distinct "uid") 当日下单人数 from orders group by create_time))
select a.create_time,当日销售额, 当月累计销售额,当日下单人数,当月累计下单人数
from a join b on a.create_time=b.create_time;