Oracle sql 复习题目总结

sql 题目一

表结构

1、表名：g_cardapply
字段（字段名/类型/长度）：
apply_no varchar8; //申请单号（关键字）
apply_date date; //申请日期
state varchar2; //申请状态
2、表名：g_cardapplydetail
字段(字段名/类型/长度)：
apply_no varchar8; //申请单号（关键字）
name varchar30; //申请人姓名
idcard varchar18; //申请人身份证号
state varchar2; //申请状态
其中，两个表的关联字段为申请单号。

题目

1、查询身份证号码为440401430103082的申请日期
2、查询同一个身份证号码有两条以上记录的身份证号码及记录个数
3、将身份证号码为440401430103082的记录在两个表中的申请状态均改为07
4、删除cardapplydetail表中所有姓李的记录

建表

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)
);

create table g_cardapplydetail(
    apply_no varchar2(8),apply_name varchar2(30) not null,idcard char(18) 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','01');
insert into g_cardapply values('00000012',to_date('2011-11-14','02');
insert into g_cardapply values('00000013',to_date('2011-11-15','03');
insert into g_cardapply values('00000014',to_date('2011-11-16','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','02');
insert into g_cardapplydetail values('00000013','mike','440401430103084','03');
insert into g_cardapplydetail values('00000014','03');
commit;

求解

1、查询身份证号码为440401430103082的申请日期

select apply_date from g_cardapply a,g_cardapplydetail b
where a.apply_no=b.apply_no and b.idcard='440401430103082';

或

select 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(idcard) as records from g_cardapplydetail
group by idcard having count(idcard)>=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,测试数据如下:

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)
);

--  C-完成  W-等待
insert into projects values('ww023','C');
insert into projects values('ww023',1,'W');
insert into projects values('ww023',2,'W');
insert into projects values('ww024','W');
insert into projects values('ww025','C');
insert into projects values('ww025','C');

C-完成 W-等待

请编写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 XXX 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 from projects p where step_status='C' 
group by workorder_id having sum(step_nbr)=0;

sql 题目三

Northwind商贸公司，业务日益发展，公司OA系统正不断推出新版本以紧跟公司的发展。在OA系统中，有一员工角色表，情况如下：