librarayv2, PostgreSQL

librarayv2

--PostgreSQL 9.6
--'\\' is a delimiter
-- CREATE TABLES

CREATE TABLE _user(
    user_id SERIAL PRIMARY KEY,
    firstName VARCHAR(255) NOT NULL,
    lastName VARCHAR(255) NOT NULL,
    userPhoto VARCHAR(500) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password  VARCHAR(255) NOT NULL,
    create_on TIMESTAMP (10) NOT NULL,
    isAdmin BOOLEAN NOT NULL DEFAULT FALSE
    );

CREATE TABLE _author(
    author_id SERIAL  PRIMARY KEY,
    authorFullName VARCHAR(255) NOT NULL
    );

CREATE TABLE _book(
    book_id SERIAL PRIMARY KEY,
    isbn_number VARCHAR(16) NOT NULL,
    title VARCHAR(255) UNIQUE NOT NULL,
    gener TEXT[] NOT NULL,
    quantity INT NOT NULL CHECK(quantity>0),
    publisher VARCHAR(255) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES _author (author_id) ON DELETE CASCADE
    );
    
CREATE TABLE _borrowed(
    borrowed_id SERIAL PRIMARY KEY,
    user_id INT,
    book_id INT,
    FOREIGN KEY (user_id) REFERENCES _user (user_id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES _book (book_id) ON DELETE CASCADE,
    borrowedDate TIMESTAMP(10) NOT NULL,
    returnDate TIMESTAMP(10) 
    );
    
-- INSERT AUTHORS
INSERT INTO _author (author_id, authorFullName) VALUES (1, 'Cahra Bestwerthick'),
(2, 'Miles Lattimore'),
(3, 'Corly Crosio');

-- INSERT USERS
INSERT INTO _user (user_id, firstName, lastName, email, create_on, password , userPhoto, isAdmin) VALUES 
(1, 'Arliene', 'Ertel', 'aertel0@cdbaby.com', '11/23/2019', '12345', 'http://dummyimage.com/119x118.bmp/5fa2dd/ffffff', true),
(2, 'Link', 'Pressland', 'lpressland1@oracle.com', '5/27/2020', crypt('12345', gen_salt('bf',8)), 'http://dummyimage.com/135x237.bmp/5fa2dd/ffffff', false),
(3, 'Granthem', 'Kleynen', 'gkleynen2@scribd.com', '8/21/2020', crypt('1234', gen_salt('bf',8)), 'http://dummyimage.com/229x117.bmp/cc0000/ffffff', false),
(4, 'Berkly', 'Swainsbury', 'bswainsbury3@w3.org', '11/14/2019', crypt('123123', gen_salt('bf',8)), 'http://dummyimage.com/236x122.jpg/dddddd/000000', false),
(5, 'Berkly', 'Swainsbury', 'test@w3.org', '11/14/2019', crypt('123123', gen_salt('bf',8)), 'http://dummyimage.com/236x122.jpg/dddddd/000000', false);

-- INSERT BOOKS
INSERT INTO _book (book_id, isbn_number, title, gener, quantity,publisher, author_id) 
VALUES (1, '371492331-4', 'Trouble Man', ARRAY['History'], 3,'7/19/2020', 1),
(2, '805644033-X', 'The Sea Vultures',ARRAY ['Science','Fiction'], 2,'11/23/2019', 1),
(3, '550346339-0', 'The Castle of Fu Manchu', ARRAY['Science','Romance'], 1,'12/8/2019', 2),
(4, '475003322-7', 'Snake of June, A (Rokugatsu no hebi)', ARRAY['Biography'], 2,'12/13/2019', 1),
(5, '292492547-9', 'Land, The (Al-ard)',ARRAY ['Biography','Fantasy'], 4,'9/18/2020', 2),
(6, '792267807-X', 'Back to School', ARRAY['Biography','History'], 2,'2/9/2020', 3);

-- BORROWED BOOK

INSERT INTO _borrowed (borrowed_id,user_id,book_id,borrowedDate,returnDate) VALUES 
( 1, 2,2,'9/18/2020',null),
( 2, 4,1,'9/3/2020','10/18/2020'),
( 3, 2,4,'6/4/2020',null),
( 4, 1,3,'5/15/2020',null);

SELECT * FROM _user;
SELECT * FROM _book;
SELECT * FROM _author;
SELECT * FROM _borrowed;

--Userlogin
SELECT * FROM _user WHERE email = lower('lpressland1@oracle.com') AND password  = crypt('12345',password );
SELECT * FROM _user WHERE email = lower('aertel0@cdbaby.com') AND password  = '12345';

--UPDATE USER
UPDATE _user SET email = 'dummy@gmail.com', password = crypt('12345', gen_salt('bf',8)) WHERE user_id = 1 RETURNING *;

--DELETE USER
DELETE FROM _user WHERE user_id = 5 RETURNING *;
-- INNER JOIN

SELECT b.title, b.gener, b.quantity FROM _book b INNER JOIN _author ath ON ath.author_id = b.author_id;

-- THREE TABLE INNER JOIN

SELECT 
       u.firstName AS user_firstName,
       b.title AS b_title,
       bor.borroweddate
FROM
       _user u
       INNER JOIN _borrowed bor ON bor.user_id = u.user_id
       INNER JOIN _book b ON bor.book_id = b.book_id

--SELECT * FROM _user
-- SELECT users.firstName, book.title
-- FROM _user users
-- INNER JOIN  _borrowed borrow ON borrow.user_id = users.user_id


run \| edit \| history \| help	0