一、資料庫來源

https://www.w3resource.com/sql-exercises/hospital-database-exercise/sql-exercise-hospital-database-38.php

二、使用資料表

• appointment(掛號資料)
• patient(病人資料)
• nurse(護士資料)
• physician(醫生資料)

三、題目與參考答案 (使用MySQL)

1. 請根據四張資料表當中的資料，自行設計並撰寫建立資料表的SQL，並且加入適當的欄位限制與索引

CREATE TABLE physician(
	employeeid INTEGER PRIMARY KEY AUTO_INCREMENT,
	name TEXT,
	position TEXT,
	ssn INTEGER
)

CREATE TABLE nurse (
	employeeid INTEGER PRIMARY KEY AUTO_INCREMENT,
	name TEXT,
	position TEXT,
	registered BOOLEAN,
	ssn INTEGER
)

CREATE INDEX registered_index ON nurse(registered)

CREATE TABLE patient  (
	ssn INTEGER PRIMARY KEY AUTO_INCREMENT,
	name TEXT,
	address TEXT,
	phone TEXT,
	insuranceid INTEGER,
	pcp INTEGER
)

CREATE TABLE appointment (
	appointmentid INTEGER PRIMARY KEY AUTO_INCREMENT,
	patient INTEGER,
	prepnurse INTEGER,
	physician INTEGER,
	start_dt_time TIMESTAMP,
	end_dt_time TIMESTAMP,
	examinationroom TEXT,
	FOREIGN KEY (patient) REFERENCES patient(ssn),
	FOREIGN KEY (prepnurse) REFERENCES nurse(employeeid),
	FOREIGN KEY (physician) REFERENCES physician(employeeid)
)

2. 請分別為這四張資料表撰寫新增資料的SQL，將網頁當中的看到的資料加入資料表當中

INSERT INTO physician (employeeid, name, position, ssn)
VALUES(1, 'John Dorian', 'Staff Internist', 111111111),
      (2, 'Elliot Reid', 'Attending Physician', 222222222),
			(3, 'Christopher Turk', 'Surgical Attending Physician', 333333333),
			(4, 'Percival Cox', 'Senior Attending Physician', 444444444),
			(5, 'Bob Kelso', 'Head Chief of Medicine', 555555555),
			(6, 'Todd Quinlan', 'Surgical Attending Physician', 666666666),
			(7, 'John Wen', 'Surgical Attending Physician', 777777777),
			(8, 'Keith Dudemeister', 'MD Resident', 888888888),
			(9, 'Molly Clock', 'Attending Psychiatrist', 999999999)

INSERT INTO nurse (employeeid, name, position, registered, ssn)
VALUES(101, 'Carla Espinosa', 'Head Nurse', true, 111111110),
      (102, 'Laverne Roberts', 'Nurse', true, 222222220),
			(103, 'Paul Flowers', 'Nurse', false, 333333330)

INSERT INTO patient (ssn, name, address, phone, insuranceid, pcp)
VALUES(100000001, 'John Smith', '42 Foobar Lane', '555-0256', 68476213, 1),
      (100000002, 'Grace Ritchie', '37 Snafu Drive', '555-0512', 36546321, 2),
			(100000003, 'Random J. Patient', '101 Omgbbq Street', '555-1204', 65465421, 2),
			(100000004, 'Dennis Doe', '1100 Foobaz Avenue', '555-2048', 68421879, 3)

INSERT INTO appointment (appointmentid, patient, prepnurse, physician, start_dt_time, end_dt_time, examinationroom)
VALUES(13216584, 100000001, 101, 1, '2008-04-24 10:00:00', '2008-04-24 11:00:00', 'A'),
      (26548913, 100000002 , 101, 2, '2008-04-24 10:00:00', '2008-04-24 11:00:00', 'B'),
			(36549879, 100000001, 102, 1, '2008-04-25 10:00:00', '2008-04-25 11:00:00', 'A'),
			(46846589, 100000004, 103, 4, '2008-04-25 10:00:00', '2008-04-25 11:00:00', 'B'),
			(59871321, 100000004, NULL , 4, '2008-04-26 10:00:00', '2008-04-26 11:00:00', 'C'),
			(69879231, 100000003, 103 , 2, '2008-04-26 11:00:00', '2008-04-26 12:00:00', 'C')

3. 更新所有檢查是在A的掛號資料的start_dt_time　和　end_dt_time　(原本時間往後加 2019分鐘)

UPDATE appointment
SET start_dt_time = DATE_ADD(start_dt_time, INTERVAL 2019 MINUTE)
WHERE examinationroom = 'A'

4. 將沒有護士的掛號資料從掛號資料中刪除

DELETE
FROM appointment
WHERE prepnurse IS NULL

四參考語法

1. 建立表格：CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name(
 -- 設定欄位
 主鍵欄位名稱 欄位資料型態 [NOT NULL] [IDENTITY (從多少開始, 每次累加多少)] [UNIQUE] ,
 其他欄位名稱 欄位資料型態 [NOT NULL] [IDENTITY (從多少開始, 每次累加多少)] [UNIQUE] ,
 ...,
 
 -- 設定欄位限制：外來鍵與索引鍵
 [PRIMARY KEY (套用得主鍵欄位) ],
 [FOREIGN KEY (關聯的來源表名稱) REFERENCES Persons(關聯的來源欄位)],
 [UNIQUE INDEX `唯一鍵的索引名稱` (`索引的欄位`)]
);

第八堂：家事管理 - 從每張表的CREATE TABLE 活用常見的欄位限制(主鍵、外來鍵、索引、唯一)
https://ithelp.ithome.com.tw/articles/10220223

2. 新增資料：INSERT INTO

INSERT INTO 指定的資料表名稱 (欄位1, 欄位2, 欄位3, ...)
VALUES (欄位1的值, 欄位2的值, 欄位3的值, ...); 

第九堂：家事管理 - 使用INSERT INTO產生隨機排班資料 (Window Function-Ranking Function、UUID()、RAND() )
https://ithelp.ithome.com.tw/articles/10220691

3. 修改資料：UPDATE SET

UPDATE 要修改的表格
SET 要修改的欄位1 = value1, 要修改的欄位2 = value2, ...
WHERE 過濾要修改的資料; 

第十堂：家事管理 - 使用Update修改休息的成員排班 (UPDATE + WHERE IN 相同的表格)
https://ithelp.ithome.com.tw/articles/10221153

4. 刪除特定資料： DELETE FROM

DELETE 
FROM 要刪除的資料表資料的資料表名稱 
WHERE 過濾資料表當中要刪除的資料;

第十一堂：家事管理 - 使用 DELETE 刪除多餘的實際做家事資料 (DELETE vs TRUNCATE vs DROP)
https://ithelp.ithome.com.tw/articles/10221665