Oracle语法 及 SQL题目（一）

时间：2023-03-09 17:02:28
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

相关文章