Oracle sql 复习题目总结
副标题[/!--empirenews.page--]
sql 题目一表结构1、表名:g_cardapply 题目1、查询身份证号码为440401430103082的申请日期 建表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_IDww023 要求:至少实现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系统中,有一员工角色表,情况如下: EMP_NAME |
EMP_ROLE |
|