47 lines
1.4 KiB
SQL

CREATE SCHEMA librarydb;
CREATE TABLE librarydb.book (
id INTEGER NOT NULL,
name VARCHAR ( 50 ) NOT NULL,
author VARCHAR ( 50 ),
publisher VARCHAR (50),
tags JSON,
genre_ids INTEGER[],
PRIMARY KEY (id)
);
CREATE TABLE librarydb.member (
id INTEGER NOT NULL,
name VARCHAR ( 50 ) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE librarydb.issue_history (
book_id INTEGER NOT NULL,
member_id INTEGER NOT NULL,
issue_date DATE,
return_date DATE,
CONSTRAINT fk_book FOREIGN KEY(book_id) REFERENCES librarydb.book(id),
CONSTRAINT fk_member FOREIGN KEY(member_id) REFERENCES librarydb.member(id)
);
INSERT INTO librarydb.book (id, name, author) VALUES (1, 'Book 1', 'ABC');
INSERT INTO librarydb.book (id, name, author) VALUES (2, 'Book 2', 'PQR');
INSERT INTO librarydb.book (id, name, author) VALUES (3, 'Book 3', 'XYZ');
INSERT INTO librarydb.member(id, name) VALUES (1, 'Member 1');
INSERT INTO librarydb.member(id, name) VALUES (2, 'Member 2');
INSERT INTO librarydb.issue_history VALUES (1, 1, TO_DATE('2021-09-27','YYYY-MM-DD'), TO_DATE('2021-09-27','YYYY-MM-DD'));
INSERT INTO librarydb.issue_history VALUES (2, 2, TO_DATE('2021-09-27','YYYY-MM-DD'), NULL);
CREATE VIEW librarydb.book_in_circulation as
SELECT b.id, b.name, b.author, b.publisher, i.member_id, i.issue_date FROM
librarydb.book b
JOIN
librarydb.issue_history i
on b.id=i.book_id
where i.return_date is null;