ORACLE 习题之一

习题一：

表结构：
1、 表名：g_cardapply
字段(字段名/类型/长度)：
applyno varchar2（8）；//申请单号（关键字）
applydate date； //申请日期
state varchar2（2）；//申请状态

 

2、 表名：g_cardapplydetail
字段(字段名/类型/长度)：
applyno varchar8；//申请单号（关键字）
name varchar30；//申请人姓名
idcard varchar18；//申请人身份证号
state varchar2；//申请状态

其中，两个表的关联字段为申请单号。

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

 

建表语句:

SQL>create table g_cardapply(
applyno varchar2(8) primary key,
applydate date not null,
state varchar2(2)
);
SQL> create table g_cardapplydetail(
2 applyno varchar2(8) primary key,
3 name varchar2(30) not null,
4 idcard varchar2(18) not null,
5 state varchar2(2) not null,
6 constraint FK_CARDAPPLY_DETAIL FOREIGN KEY (applyno) references g_cardapply(applyno)
7 );

解题思路：

1.给的是表g_cardapplydetail中的idcard，要的是表g_cardapply中的apllydate,这是跨表操作，必须关联。

有两种方法：

a:

select a.applydate
from g_cardapply a
inner join g_cardapplydetail b
on a.applyno=b.applyno
where b.idcard='440401430103082';

b:

用子查询（效率比较低）

select applydate
from g_cardapply
where applyno in
(
select applyno
from g_cardapplydetail
where idcard='440401430103082'
);
2.给的是idcard要的是idcard及记录，只在一张表中操作即可。这有统计的操作，必然涉及到分组。分组时，只有被分组的字段和被聚合函数修饰的字段才能出现在select后面。因为是虚表，所以不能用where,必须用having。

select idcard,count(*)
from g_cardapplydetail
group by idcard
having count(*)>=2;

3.这是跨表修改，因为关联只能用来查询，所以只能用子查询来修改（删除也是修改，子查询是个万金油，但查询效率低）。

没有一条语句能够修改两张表，所以要分两条语句完成。

update g_cardapplydetail set state='07' where idcard='440401430103082';

update g_cardapply
set state='07'
where applyno in (
select applyno
from g_cardapplydetail
where idcard='440401430103082'
);

4.这道题考的是模糊查询，%可以表示多个字符，_（下划线）代表一个字符。

delete from g_cardapplydetail
where name like '李%'