iT邦幫忙

2021 iThome 鐵人賽

DAY 28
0
自我挑戰組

Oracle資料庫系列 第 28

[Day28]約束規則、更改結構實作

前幾篇的實作都是從HR或OE帳戶中查詢,這篇的實作帳戶使用自己新建的Hotel帳戶命名、新增並查詢。

  • 這張資料表是飯店中房務員的資料表,項目分別有:
  1. 房務員編號
  2. 房務員姓名
  3. 房務員電話號碼
  4. 房間整理狀態,又分為已清潔完成、清潔中和未清潔三種狀態。
  5. 每位房務員的負責人
    各項需求分析如下:
create table housekeepers
(
housekeeper_id NUMBER(5)primary key,
housekeeper_name NCHAR(20) not null,
housekeeper_phonenumber NUMBER(12) not null,
housekeeper_roomsituation varchar(8) not null,
housekeeper_boss NCHAR(5) not null
);

接下來要自己新增資料至資料表中:

insert into housekeepers values(456,'Ivy','0933123456','Done','Basia');
insert into housekeepers values(457,'Adele','0933168756','Done','Basia');
insert into housekeepers values(458,'Cara','0933123772','Done','Basia');
insert into housekeepers values(459,'Eden','0938123909','Cleaning','Basia');
insert into housekeepers values(460,'Rose','0933123342','Done','Basia');
insert into housekeepers values(461,'Allen','0938120019','Done','Basia');
insert into housekeepers values(462,'Celine','0933180624','Cleaning','Basia');
insert into housekeepers values(463,'Eve','0938123906','Cleaning','Basia');
insert into housekeepers values(464,'Gail','0933703342','Done','Basia');
insert into housekeepers values(465,'Hedy','0938127249','Undone','Basia');
insert into housekeepers values(466,'Kate','0943180624','Cleaning','Jolie');
insert into housekeepers values(467,'Kally','0943180545','Cleaning','Jolie');
insert into housekeepers values(468,'Grace','0943183124','Cleaning','Jolie');
insert into housekeepers values(469,'May','0943167925','Done','Jolie');
insert into housekeepers values(570,'Gray','0943141314','Cleaning','Jolie');
insert into housekeepers values(571,'Apple','0943188795','Done','Jolie');
insert into housekeepers values(572,'Kay','0978983124','Cleaning','Jolie');
insert into housekeepers values(573,'Novia','0943167675','Done','Jolie');
insert into housekeepers values(574,'Sharon','0943177645','Cleaning','Jolie');
insert into housekeepers values(575,'Pearl','0943189324','Undone','Jolie');
insert into housekeepers values(576,'Una','0943180321','Cleaning','Tammy');
insert into housekeepers values(577,'Winnie','0943180098','Cleaning','Tammy');
insert into housekeepers values(578,'Orange','0943183112','Cleaning','Tammy');
insert into housekeepers values(579,'Stella','0948357925','Done','Tammy');
insert into housekeepers values(580,'Ruby','0945381314','Cleaning','Tammy');
insert into housekeepers values(581,'Jennie','0967588795','Done','Tammy');
insert into housekeepers values(582,'Polly','0978914869','Cleaning','Tammy');
insert into housekeepers values(583,'Pag','0943190525','Done','Tammy');
insert into housekeepers values(584,'Myra','0943177365','Cleaning','Tammy');
insert into housekeepers values(585,'Lisa','0943189878','Undone','Tammy');

而如果TABLE是新增在預設的帳戶中,隨後才新增自己的帳戶,要將表格轉移至HOTEL帳戶輸入以下程式碼即可:

GRANT CREATE ANY TABLE TO Hotel;

輸入一SQL語句檢查是否新增成功,結果如下:

SELECT room_id,housekeeper_id,housekeeper_phonenumber,housekeeper_boss
from housekeepers join rooms
USING(housekeeper_id);

https://ithelp.ithome.com.tw/upload/images/20211013/20140915SbDeEC8ocv.png


上一篇
[Day27]效能監控
下一篇
[Day29]檢視表、索引
系列文
Oracle資料庫30

尚未有邦友留言

立即登入留言