Oracle语法 及 SQL题目（一）
课例复制
OCM
全称：Oracle Certified Master 认证大师
含义：Oracle 原厂推出的数据库方向最高级别认证
作用：是否能成为资深数据库专家的技能考试
1.你所做的任何操作，都会在数据字典中被记录。
2.你所不理解的任何ORACLE的咨询都可以在文档找到资料。
清屏命令
clear screen
clear scr
cle scr（只要书写三个字母即可）
只写clean也是可以的
若是删除表格，需要先删除从表，解除依赖，再删除主表
SQL题目一
表结构：
1、表名：g_cardapply
字段(字段名/类型/长度)：
apply_no varchar8; //申请单号（关键字）
apply_date date; //申请日期
state varchar2; //申请状态
2、表名：g_cardapplydetail
字段(字段名/类型/长度)：
apply_no varchar8; //申请单号（关键字）
name varchar30; //申请人姓名
idcard varchar18; //申请人身份证号
state varchar2; //申请状态
其中，两个表的关联字段为申请单号。
-- 创建工卡表
create table g_cardapply(
apply_no varchar2(8),
apply_date date not null,
state varchar2(2) not null,
constraint PK_APPLY_NO PRIMARY KEY (apply_no)
);
-- 创建工卡申请明细表，apply_no即是主键又是外键，我们称为主外键重合，是一种经典的一对一设计方案
create table g_cardapplydetail(
apply_no varchar2(8) ,
apply_name varchar2(30) not null,
idcard char(18) not null,
state varchar2(2) not null,
constraint PK_APPLY_DETAIL_NO PRIMARY KEY (apply_no),
constraint FK_CARDAPPLY_DETAIL foreign key (apply_no) references g_cardapply(apply_no)
);
-- oracle字符串转日期，必须使用to_date函数，然后做好日期格式的适配
insert into g_cardapply values('00000010',to_date('2011-11-12','yyyy-mm-dd'),'01');
insert into g_cardapply values('00000011',to_date('2011-11-13','yyyy-mm-dd'),'01');
insert into g_cardapply values('00000012',to_date('2011-11-14','yyyy-mm-dd'),'02');
insert into g_cardapply values('00000013',to_date('2011-11-15','yyyy-mm-dd'),'03');
insert into g_cardapply values('00000014',to_date('2011-11-16','yyyy-mm-dd'),'03');
insert into g_cardapplydetail values('00000010','mary','440401430103082','01');
insert into g_cardapplydetail values('00000011','david','440401430103083','01');
insert into g_cardapplydetail values('00000012','mary','440401430103082','02');
insert into g_cardapplydetail values('00000013','mike','440401430103084','03');
insert into g_cardapplydetail values('00000014','mary','440401430103082','03');
commit;
要求：
1、查询身份证号码为440401430103082的申请日期
-- 内关联
select a.apply_date
from g_cardapply a inner join g_cardapplydetail b
on a.apply_no=b.apply_no and b.idcard='440401430103082';
2、查询同一个身份证号码有两条以上记录的身份证号码及记录个数
select idcard,count(*)
from g_cardapplydetail
group by idcard
having count(*)>=2;
3、将身份证号码为440401430103082的记录在两个表中的申请状态均改为07
update g_cardapplydetail
set state='07'
where idcard='440401430103082';
-- 子查询
update g_cardapply
set state='07'
where apply_no in (
select apply_no
from g_cardapplydetail
where idcard='440401430103082'
);
commit;
4、删除cardapplydetail表中所有姓李的记录
-- 模糊查询
delete from g_cardapplydetail
where apply_name like '李%';
SQL题目二
有一个工厂，非常繁忙，同时在进行多个订单的生产任务。
每个订单都有自己的订单编号(WORKORDER_ID)，
每个订单要生产的物品要拆分成多个工序，
这些工序并行进行，每个工序都有自己的编号STEP_NBR,
测试数据如下:
C-完成 W-等待
-- 建表语句
create table projects(
workorder_id varchar2(10) not null,
step_nbr int not null,
step_status char(1) not null,
constraint PK_PROJECTS PRIMARY KEY (workorder_id,step_nbr)
);
insert into projects values('ww023',0,'C');
insert into projects values('ww023',1,'W');
insert into projects values('ww023',2,'W');
insert into projects values('ww024',0,'W');
insert into projects values('ww024',1,'W');
insert into projects values('ww025',0,'C');
insert into projects values('ww025',1,'C');
请编写SQL语句，找出STEP_NBR=0,其 STEP STATUS='C',同时本订单其它工序STEP_STATUS均为W的订单
比如对以上数据的分析结果就是:
WORKORDER ID
ww023
要求:至少实现2种写法(多写更好)，语句越短越好
-- 条件1:
where step_nbr=0 and step_status='C'
-- 条件2:
'W'= ALL
(
select step_status from projects where step_nbr> = 1
)
-- 解一：
select workorder_id from projects p
where p.step_nbr=0 and p.step_status='C'
and 'W'= ALL
(
select step_status
from projects
where step_nbr>=1 and workorder_id=p.workorder_id
);
-- 解二：
select workorder_id,sum(step_nbr)
from projects
where step_status='C'
group by workorder_id
having sum(step_nbr)=0;
SQL题目三
Northwind商贸公司，业务日益发展，公司OA系统正不断推出新版本以紧跟公司的发展.
在OA系统中，有一员工角色表，情况如下：
数据：
EMP_NAME EMP_ROLE
陈城 W
刘海 D
刘海 O
田亮 O
王晓刚 D
张玲 S
张天明 D
张天明 O
其中：
W – 搬运工人
D – 主任
O – 高级职员
S – 秘书
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('陈城','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');
OA开发组的SQL程序员张明得到了上级的一个任务：
领导要求得到的高级职员信息表如下：
EMP_NAME COMBINE_ROLE
刘海 B
田亮 O
王晓刚 D
张天明 B
要求：
（1）只列出主任和高级职员的信息
（2）如果即是高级职员又是主任，用B表示其角色, 其它信息不用再显示 （只一条记录）。
你能不能用单条SQL语句帮助张明实现这个查询？
-- 一解：
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, substr('DOB',sum(instr('DO',emp_role)),1) combine_role
from roles
where emp_role in ('D','O')
group by emp_name;
-- 三解：
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 ;
笔记
instr('DO',emp_ role)
instr函数表示emp_ role变量在'DO'中出现的位置
比如emp_ role是'D'，则返回值为1；
若emp_ role是'D'和'O'，则返回为3，是1+2；
substr('DOB' , sum(instr('DO',emp_ role)),l)
substr函数表示通过返回值的位置取一个字符
substr('DOB' , sum(instr('DO','DO')),l)
取得是B
substr('DOB' , sum(instr('DO','D')),l)
取得是D
substr('DOB' , sum(instr('DO','O')),l)
取得是O
