CS2203 Programming Assignment Unit 5.sql

-- BOOKS TABLES
CREATE TABLE Books (
    ISBN VARCHAR(17) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Author_ID VARCHAR(25),
    Genre VARCHAR(50),
    Quantity INT,
    FOREIGN KEY (Author_ID) REFERENCES Authors(Author_ID)
);

-- MEMBERS TABLE
CREATE TABLE Members (
    Member_ID VARCHAR(25) PRIMARY KEY,
    Member_Name VARCHAR(100) NOT NULL,
    Member_Email VARCHAR(50),
    Member_Phone VARCHAR(12),
);

-- BOOK LOANS TABLE
CREATE TABLE Loans (
    Loan_ID VARCHAR(25) PRIMARY KEY,
    Member_ID VARCHAR(25),
    ISBN VARCHAR(17),
    Loan_Date DATE NOT NULL,
    Return_Date DATE NOT NULL,
    FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN),
);

-- Authors Table
CREATE TABLE Authors (
    Author_ID VARCHAR(25) PRIMARY KEY,
    Author_Name VARCHAR(100) NOT NULL,
    Author_Nationality VARCHAR(50),
    Author_Birth_Year DATE
);

-- Fetch methods for inserting new values into tables
INSERT INTO Books (ISBN, Title, Author, Genre, Quantity) VALUES
    ('0-380-97365-0', 'American Gods', 'Neil Gaiman', 'Fantasy', 4),
    ('978-0-385-51787-4', 'Rant', 'Chuck Palahniuk', 'Science Fiction, Satire, Horror', 2);

INSERT INTO Members (Member_ID, Member_Name, Member_Email, Member_Phone) VALUES
    ('MEM-123', 'John Doe', 'john@proton.com', '787-345-6578'),
    ('MEM-124', 'Jane Doe', 'jane@gmail.com', '787-879-3278');

INSERT INTO Loans (Loan_ID, Member_ID, ISBN, Loan_Date, Return_Date) VALUES
    ('LX-36', 'MX-123', '0-380-97365-0', '2024-05-07', '2024-05-21'),
    ('LX-37', 'MX-124', '978-0-385-51787-4', '2024-05-08', '2024-05-22');

-- Retrieving information about books borrowed by a library member
SELECT Books.*
FROM Books
INNER JOIN Loans
    ON Books.ISBN = Loans.ISBN
WHERE Loans.Member_ID = 'MX-123';

-- Update book quantity in 'Books' table
UPDATE Books
SET Quantity = 3
Where ISBN = '0-380-97365-0';

-- Deletion of a member from the 'Members' table
DELETE FROM Members
WHERE Member_ID = 'MX-124';


-- Database alterations for Assignment Unit 5 start here

-- Inserting 5 new authors into Authors table
INSERT INTO Authors (Author_ID, Author_Name, Author_Nationality, Author_Birth_Year) VALUES
    ('AUTH-001', 'Neil Richard MacKinnon Gaiman', 'British', '1960-11-10'),
    ('AUTH-002', 'Charles Michael Palahniuk', 'American', '1962-02-21'),
    ('AUTH-003', 'J.R.R. Tolkien', 'British', '1892-01-03'),
    ('AUTH-004', 'Hunter Stockton Thompson', 'American', '1937-06-18'),
    ('AUTH-005', 'George Orwell', 'British', '1903-06-25');

-- Inserting 10 new books into Books table
INSERT INTO Books (ISBN, Title, Author_ID, Genre, Quantity) VALUES
    ('0-380-97365-0', 'American Gods', 'AUTH-001', 'Fantasy', 4),
    ('978-0-06-053092-1', 'The Graveyard Book', 'AUTH-001', 2),
    ('978-0-385-51787-4', 'Rant', 'AUTH-002', 'Science Fiction, Satire, Horror', 2),
    ('0-393-03976-5', 'Fight Club', 'AUTH-002', 'Satire', 3),
    ('978-0345339706', 'The Fellowship of the Ring', 'AUTH-003', 'Fantasy', 2),
    ('978-0345339713', 'The Two Towers', 'AUTH-003', 'Fantasy', 2),
    ('0-679-78589-2', 'Fear and Loathing in Las Vegas', 'AUTH-004', 'Satire, Gonzo Journalism', 3),
    ('0-684-85521-6', 'The Rum Diary', 'AUTH-004', 'Fiction', 1),
    ('978-1443434973', 'Nineteen Eighty-Four', 'AUTH-005', 'Political Fiction', 4),
    ('978-0151072552', 'Animal Farm', 'AUTH-005', 'Political Satire', 3);

-- Inserting 20 new members into Members table
INSERT INTO Members (Member_ID, Member_Name, Member_Email, Member_Phone) VALUES
    ('MEM-001', 'Alice Johnson', 'alice.johnson@example.com', '1234567890'),
    ('MEM-002', 'Bob Smith', 'bob.smith@example.com', '0987654321'),
    ('MEM-003', 'Carol White', 'carol.white@example.com', '1112223333'),
    ('MEM-004', 'David Brown', 'david.brown@example.com', '2223334444'),
    ('MEM-005', 'Eve Black', 'eve.black@example.com', '3334445555'),
    ('MEM-006', 'Frank Green', 'frank.green@example.com', '4445556666'),
    ('MEM-007', 'Grace Blue', 'grace.blue@example.com', '5556667777'),
    ('MEM-008', 'Hank Orange', 'hank.orange@example.com', '6667778888'),
    ('MEM-009', 'Ivy Red', 'ivy.red@example.com', '7778889999'),
    ('MEM-010', 'Jack Purple', 'jack.purple@example.com', '8889990000'),
    ('MEM-011', 'Kara Yellow', 'kara.yellow@example.com', '9990001111'),
    ('MEM-012', 'Leo Gray', 'leo.gray@example.com', '0001112222'),
    ('MEM-013', 'Mia Pink', 'mia.pink@example.com', '1112223333'),
    ('MEM-014', 'Nina Silver', 'nina.silver@example.com', '2223334444'),
    ('MEM-015', 'Oscar Gold', 'oscar.gold@example.com', '3334445555'),
    ('MEM-016', 'Pia Brown', 'pia.brown@example.com', '4445556666'),
    ('MEM-017', 'Quinn White', 'quinn.white@example.com', '5556667777'),
    ('MEM-018', 'Rita Blue', 'rita.blue@example.com', '6667778888'),
    ('MEM-019', 'Sam Orange', 'sam.orange@example.com', '7778889999'),
    ('MEM-020', 'Tina Red', 'tina.red@example.com', '8889990000');


-- Select all books available from a particular author
SELECT Books.ISBN, Books.Title, Books.Genre, Books.Quantity
FROM Books
JOIN Authors
    ON Books.Author_ID = Authors.Author_ID
WHERE Authors.Author_ID = 'AUTH-001';
/* Here, we could also use 'Author_Name' as a query for WHERE */


-- Drop (removing) a table from the database
DROP TABLE Authors;


-- Identifying which members have borrowed a specific book
SELECT Members.Member_Name
FROM Members
JOIN Loans
    ON Members.Member_ID = Loans.Member_ID
JOIN Books
    ON Loans.ISBN = Books.ISBN
WHERE Books.Title = 'The Graveyard Book';

-- Adding new column for membership types to 'Members' table
ALTER TABLE Members
ADD Membership_Type VARCHAR(50);


SELECT s.first_name, s.last_name
FROM Students AS s
JOIN Enrollments AS e
    ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name, s.last_name
HAVING COUNT(e.course_id) >= 2;