-[作業-miro](https://miro.com/app/board/uXjVLMcM_hQ=/)

### 作業一：拯救明華國小的資料庫，哪個欄位適合變成外來鍵？

CREATE TABLE classes (
	id SERIAL PRIMARY KEY,
  	name VARCHAR(50)
); 

INSERT INTO classes (name) 
VALUES  ('三年一班'),
	('三年二班'); 

CREATE TABLE students (
	id SERIAL PRIMARY KEY,
  	name VARCHAR(50),
  	class_id INTEGER,
  	sex BOOLEAN, --true 男生 false 女生
  	age INTEGER,
  	FOREIGN KEY (class_id) REFERENCES classes(id) 
);

INSERT INTO students (name, class_id, sex, age)
VALUES 
('小明', 1, true, 8),
('小華', 2, false, 9),
('小美', 1, true, 8),
('小強', 1, false, 8),
('小智', 2, true, 9); 

### 作業二：第一題的延伸，多了一欄班級老師

CREATE TABLE teachers (
	id SERIAL PRIMARY KEY,
  	name VARCHAR(50),
	class VARCHAR(50)
);

INSERT INTO teachers (name, class) 
VALUES  ('廖洧杰', '三年一班'),
	('卡斯柏', '三年二班'),
	('查理', '三年一班'),
	('麥可', '三年一班'),
	('李燕容', '三年二班');

CREATE TABLE students (
	id SERIAL PRIMARY KEY,
  	name VARCHAR(50),
  	teacher_id INTEGER,
  	sex BOOLEAN, --true 男生 false 女生
  	age INTEGER,
  	FOREIGN KEY (teacher_id) REFERENCES teachers(id) 
);

INSERT INTO students (name, teacher_id, sex, age)
VALUES 
('小明', 1, true, 8),
('小華', 2, false, 9),
('小美', 3, true, 8),
('小強', 4, false, 8),
('小智', 5, true, 9);

SELECT students.id AS 學生編號,
		students.name AS 姓名,
    teachers.class AS 班級,
    teachers.name AS 班級老師,
		students.sex AS 性別,
		students.age AS 年齡
FROM students
INNER JOIN teachers ON students.teacher_id = teachers.id;


### 作業三：小孩的家庭歸類資料庫

CREATE TABLE parents (
	id SERIAL PRIMARY KEY,
  	name VARCHAR(50),
	telephone VARCHAR(50),
	sex BOOLEAN --true 男生 false 女生
);

INSERT INTO parents (name, telephone, sex) 
VALUES  ('王大祥', '0973254254', true),
	('王曉如', '0955717855', false);


CREATE TABLE students (
	id SERIAL PRIMARY KEY,
  	name VARCHAR(50),
  	parent_id INTEGER,
  	FOREIGN KEY (parent_id) REFERENCES parents(id) 
);

INSERT INTO students (name, parent_id)
VALUES 
('小明', 1),
('小華', 2),
('小美', 1),
('小強', 2),
('小智', 1);

SELECT students.id AS 小孩編號,
		students.name AS 姓名,
    parents.name AS 父母名稱,
    parents.telephone AS 父母電話,
		parents.sex AS 父母性別
FROM students
INNER JOIN parents ON students.parent_id = parents.id;


### 上一位同學的題目

a. 請優化此表格。
b. 查出有誰沒有還書
c. 借閱時間是30天，超過一天罰100元，請問有誰超過時間了？要被罰多少錢？

-- 創建作者表
CREATE TABLE authors (
	id SERIAL PRIMARY KEY,
  name VARCHAR(50)
);

--輸入資料
INSERT INTO authors (name)
VALUES ('小邏邏'),
('神仙教母'),
('隔壁老王'),
('周鋁輪'),
('胡弟'),
('佩豬'),
('柯北');


-- 創建借閱者表
CREATE TABLE borrowers (
	id SERIAL PRIMARY KEY,
  name VARCHAR(50)
);

-- 輸入資料
INSERT INTO borrowers (name)
VALUES 
('小明'),
('小花'),
('小王'),
('小黑'),
('仙度瑞拉'),
('安弟'),
('小毛');


-- 創建書本表
CREATE TABLE books (
	id SERIAL PRIMARY KEY,
  name VARCHAR(50),
	author_id INTEGER,
  borrower_id INTEGER,
  checkout_date TIMESTAMP,
  checkin_date TIMESTAMP,
  FOREIGN KEY (author_id) REFERENCES authors (id),
  FOREIGN KEY (borrower_id) REFERENCES borrowers (id)
);

-- 輸入資料
INSERT INTO books (name, author_id, borrower_id, checkout_date, checkin_date)
VALUES ('數位邏輯', 1, 1, '2024-01-01', '2024-01-15'),
('將南瓜變馬車', 2, 2,'2024-01-02', '2024-01-05'),
('時間管理大師祕技', 3, 3,'2024-01-03', '2024-01-31'),
('絆倒鋁盒', 4, 4,'2024-01-04', '2024-02-29'),
('12點前要離開舞會', 2, 5,'2024-01-10', '2024-01-12'),
('人工智能大合集', 1, 3, '2024-01-15', null),
('我的靴子裡有蛇',5, 6,'2024-01-17', '2024-02-06'),
('豬排要怎麼煮比較好吃',6, 4,'2024-01-20', '2024-02-02'),
('麻醉針使用',7,7,'2024-01-27', '2024-02-29');

-- 篩選
SELECT books.id AS 書本編號,
books.name AS 書名,
authors.name AS 作者,
borrowers.name AS 借閱者,
books.checkout_date AS 借閱日期,
books.checkin_date AS 還書日期
FROM books
INNER JOIN authors ON books.author_id = authors.id
INNER JOIN borrowers ON books.borrower_id = borrowers.id;


-- 找出誰沒還書
SELECT books.name AS 書名, borrowers.name AS 借閱者
FROM books
INNER JOIN borrowers ON books.borrower_id = borrowers.id
WHERE books.checkin_date ISNULL;


-- 找出逾期不還者及總罰金
SELECT books.name AS 書名,
       borrowers.name AS 借閱者,
       (EXTRACT(EPOCH FROM (books.checkin_date - books.checkout_date)) / 86400 - 30) * 100 AS 罰鍰
FROM books
INNER JOIN borrowers ON books.borrower_id = borrowers.id
WHERE EXTRACT(EPOCH FROM (books.checkin_date - books.checkout_date)) / 86400 > 30;