Oracle sql 复习题目总结

要求：
1）只列出主任和高级职员的信息
2）如果即是高级职员又是主任，用B表示其角色,其它信息不用再显示 （只一条记录）。
你能不能用单条SQL语句帮助张明实现这个查询？

建表

create table roles(
    emp_name varchar2(20) not null,emp_role char(1) not null,constraint pk_roles primary key(emp_name,emp_role)
 );
 
 -- 英文名
insert into roles values('Mary','W');
insert into roles values('David','D');
insert into roles values('David','O');
insert into roles values('Mike','O');
insert into roles values('Kate','D');
insert into roles values('Lucy','S');
insert into roles values('Nick','D');
insert into roles values('Nick','O');

-- 中文名
insert into roles values('陈城','W');
insert into roles values('刘海','D');
insert into roles values('刘海','O');
insert into roles values('田亮','O');
insert into roles values('王晓刚','D');
insert into roles values('张玲','S');
insert into roles values('张天明','D');
insert into roles values('张天明','O');

求解

一解：

select emp_name,'B' combine_role from roles where emp_role in ('D','O')
group by emp_name having count(*)=2
union
select emp_name,max(emp_role) combine_role from roles where emp_role in ('D','O')
group by emp_name having count(*)=1;

二解：

select emp_name,case when count(*)=1 then max(emp_role) else 'B' end as emp_role
from roles where emp_role in ('D','O') group by emp_name;

或

select emp_name,case when count(*)=2 then 'B' else max(emp_role) end as emp_role
from roles where emp_role in ('D','O') group by emp_name;

三解：

select emp_name,substr('DOB',sum(instr('DO',emp_role)),1) combine_role from roles
where emp_role in ('D','O')group by emp_name;

-- instr：返回第二个参数在第一个参数中第一次出现的位置

sql 题目四

最近，经过你的努力，你得到了一份工作，成为了百货公司的一位经理。 到位后，你发现你的销售数据库中有两张表，一个是商店促销时间的日历，另一个是在促销期间的销售额列表。你需要编写一个查询，告诉我们在每次促销中哪位职员的销售额最高，这样可以给那个职员发绩效奖金。

找出在各次促销活动中，销售量最高的销售员。
请编制一条SQL来完成这个查询。(尽量考虑多种写法)

建表

-- 商店促销时间的日历
create table promotions (
    promo_name varchar2(50) not null primary key,-- 促销活动名称
    start_date date not null,-- 开始时间
    end_date date not null,-- 终止时间
    check(start_date<=end_date)
);

-- 促销期间的销售额表 （注意：该表只是保存促销期间的销售额）
create table sales
(
    ticket_nbr int not null primary key,--销售票据编号 （自增）
    clerk_name varchar2(20) not null,--销售员姓名
    sale_date date not null,--销售日期
    sale_amount number(9,2) not null      --销售金额
);

insert into promotions values('spring sales',to_date('2009-2-1','yyyy/mm/dd'),to_date('2009-2-15','yyyy/mm/dd'));
insert into promotions values('worker sale',to_date('2009-5-1',to_date('2009-5-4','yyyy/mm/dd'));
insert into promotions values('children sale',to_date('2009-6-1','yyyy/mm/dd'));
insert into promotions values('national day sale',to_date('2009-10-1',to_date('2009-10-7','yyyy/mm/dd'));

create sequence seq_nbr;

insert into sales values(seq_nbr.nextval,30);
insert into sales values(seq_nbr.nextval,'tom',73);
insert into sales values(seq_nbr.nextval,110);
insert into sales values(seq_nbr.nextval,to_date('2009-2-2',190);
insert into sales values(seq_nbr.nextval,92);
insert into sales values(seq_nbr.nextval,to_date('2009-2-3',130);
insert into sales values(seq_nbr.nextval,90);
insert into sales values(seq_nbr.nextval,to_date('2009-2-4',70);
insert into sales values(seq_nbr.nextval,9);
insert into sales values(seq_nbr.nextval,to_date('2009-2-5',88);
insert into sales values(seq_nbr.nextval,to_date('2009-2-13',50);
insert into sales values(seq_nbr.nextval,170);
insert into sales values(seq_nbr.nextval,to_date('2009-2-14',270);
insert into sales values(seq_nbr.nextval,67.5);
insert into sales values(seq_nbr.nextval,280.5);
insert into sales values(seq_nbr.nextval,113);
insert into sales values(seq_nbr.nextval,to_date('2009-5-2',35.5); 
insert into sales values(seq_nbr.nextval,125);
insert into sales values(seq_nbr.nextval,to_date('2009-5-3',93);
insert into sales values(seq_nbr.nextval,167);
insert into sales values(seq_nbr.nextval,123.5);
insert into sales values(seq_nbr.nextval,200);
insert into sales values(seq_nbr.nextval,2);
insert into sales values(seq_nbr.nextval,110.5);
insert into sales values(seq_nbr.nextval,213);
insert into sales values(seq_nbr.nextval,1123);
insert into sales values(seq_nbr.nextval,780);
insert into sales values(seq_nbr.nextval,310);
insert into sales values(seq_nbr.nextval,to_date('2009-10-2',139);
insert into sales values(seq_nbr.nextval,1110.5);
insert into sales values(seq_nbr.nextval,998);
insert into sales values(seq_nbr.nextval,to_date('2009-10-3',120);
insert into sales values(seq_nbr.nextval,to_date('2009-10-4',10);
insert into sales values(seq_nbr.nextval,234);
insert into sales values(seq_nbr.nextval,to_date('2009-10-5',to_date('2009-10-6',23);
insert into sales values(seq_nbr.nextval,10.5); 
insert into sales values(seq_nbr.nextval,'王海','刘万理','高春梅',10.5);

求解

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!