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 '李%'