-- Create the Books table
CREATE TABLE Books (
    ISBN VARCHAR(20) PRIMARY KEY,
    Title VARCHAR(150) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    Genre VARCHAR(60),
    Quantity INT NOT NULL CHECK (Quantity >= 0)
);

-- Create the Members table
CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(120) NOT NULL UNIQUE,
    Phone VARCHAR(30)
);

-- Create the Loans table
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    MemberID INT NOT NULL,
    ISBN VARCHAR(20) NOT NULL,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);

-- Insert books
INSERT INTO Books VALUES
('9780131103627', 'The C Programming Language',
 'Brian Kernighan', 'Programming', 5),
('9780132350884', 'Clean Code',
 'Robert C. Martin', 'Programming', 4),
('9780262033848', 'Introduction to Algorithms',
 'Thomas H. Cormen', 'Computer Science', 3),
('9781491950296', 'Designing Data-Intensive Applications',
 'Martin Kleppmann', 'Databases', 2);

-- Insert members
INSERT INTO Members VALUES
(101, 'Amina Otieno', 'amina@example.com', '0712345678'),
(102, 'Brian Mwangi', 'brian@example.com', '0723456789'),
(103, 'Grace Wanjiku', 'grace@example.com', '0734567890');

-- Insert loan records
INSERT INTO Loans VALUES
(1001, 101, '9780132350884', '2026-10-01', NULL),
(1002, 101, '9780262033848', '2026-10-03', '2026-10-10'),
(1003, 102, '9780131103627', '2026-10-04', NULL);
SELECT * FROM Books;
SELECT * FROM Members;
SELECT * FROM Loans;
SELECT Books.Title, Loans.LoanDate, Loans.ReturnDate
FROM Loans
JOIN Books ON Loans.ISBN = Books.ISBN
WHERE Loans.MemberID = 101;
UPDATE Books
SET Quantity = 6
WHERE ISBN = '9780131103627';
SELECT * FROM Books
WHERE ISBN = '9780131103627';
-- Delete Grace Wanjiku
DELETE FROM Members
WHERE MemberID = 103;

-- Display remaining members
SELECT * FROM Members;

-- UNIT 5 ASSIGNMENT: LIBRARY DATABASE
-- Create tables, insert data, retrieve records, and alter tables.

-- STEP 1: Remove old tables if they exist
DROP TABLE IF EXISTS Loans;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Members;
DROP TABLE IF EXISTS Authors;

-- STEP 2: Create the Authors table
CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Nationality VARCHAR(60),
    BirthYear INT
);

-- STEP 3: Create the Books table
CREATE TABLE Books (
    ISBN VARCHAR(20) PRIMARY KEY,
    Title VARCHAR(150) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    Genre VARCHAR(60),
    Quantity INT NOT NULL CHECK (Quantity >= 0)
);

-- STEP 4: Create the Members table
CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100),
    Phone VARCHAR(20)
);

-- STEP 5: Create the Loans table
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    MemberID INT NOT NULL,
    ISBN VARCHAR(20) NOT NULL,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);

-- STEP 6: Insert five authors
INSERT INTO Authors VALUES
(1, 'Brian Kernighan', 'Canadian', 1942),
(2, 'Robert C. Martin', 'American', 1952),
(3, 'Thomas H. Cormen', 'American', 1956),
(4, 'Martin Kleppmann', 'British', 1983),
(5, 'Yuval Noah Harari', 'Israeli', 1976);

-- STEP 7: Insert ten books
INSERT INTO Books VALUES
('9780131103627', 'The C Programming Language', 'Brian Kernighan', 'Programming', 5),
('9780132350884', 'Clean Code', 'Robert C. Martin', 'Programming', 4),
('9780262033848', 'Introduction to Algorithms', 'Thomas H. Cormen', 'Computer Science', 3),
('9781491950296', 'Designing Data-Intensive Applications', 'Martin Kleppmann', 'Databases', 2),
('9780062316097', 'Sapiens', 'Yuval Noah Harari', 'History', 7),
('9780134494166', 'Clean Architecture', 'Robert C. Martin', 'Programming', 4),
('9780262046305', 'Algorithms Unlocked', 'Thomas H. Cormen', 'Computer Science', 5),
('9780306406157', 'Science and Technology', 'Brian Kernighan', 'Science', 6),
('9780062464316', 'Homo Deus', 'Yuval Noah Harari', 'History', 4),
('9781449373320', 'Database Systems Guide', 'Martin Kleppmann', 'Databases', 3);

-- STEP 8: Insert twenty members
INSERT INTO Members VALUES
(101, 'Amina Otieno', 'amina@example.com', '0712345678'),
(102, 'Brian Mwangi', 'brian@example.com', '0723456789'),
(103, 'Grace Wanjiku', 'grace@example.com', '0734567890'),
(104, 'Daniel Kiptoo', 'daniel@example.com', '0745678901'),
(105, 'Mary Njeri', 'mary@example.com', '0756789012'),
(106, 'Peter Kamau', 'peter@example.com', '0767890123'),
(107, 'Sarah Achieng', 'sarah@example.com', '0778901234'),
(108, 'John Mutua', 'john@example.com', '0789012345'),
(109, 'Faith Chebet', 'faith@example.com', '0701234567'),
(110, 'David Ouma', 'david@example.com', '0711122233'),
(111, 'Lydia Wairimu', 'lydia@example.com', '0722233344'),
(112, 'Samuel Kibet', 'samuel@example.com', '0733344455'),
(113, 'Esther Adhiambo', 'esther@example.com', '0744455566'),
(114, 'Joseph Maina', 'joseph@example.com', '0755566677'),
(115, 'Mercy Atieno', 'mercy@example.com', '0766677788'),
(116, 'Kevin Omondi', 'kevin@example.com', '0777788899'),
(117, 'Ann Wambui', 'ann@example.com', '0788899001'),
(118, 'Michael Kariuki', 'michael@example.com', '0700011223'),
(119, 'Janet Jepkoech', 'janet@example.com', '0711223344'),
(120, 'Eric Onyango', 'eric@example.com', '0722334455');

-- STEP 9: Insert sample loan records
INSERT INTO Loans VALUES
(1001, 101, '9780132350884', '2026-10-01', NULL),
(1002, 102, '9780132350884', '2026-10-02', NULL),
(1003, 104, '9780132350884', '2026-10-03', NULL),
(1004, 103, '9780262033848', '2026-10-04', '2026-10-10'),
(1005, 105, '9780131103627', '2026-10-05', NULL);

-- STEP 10: Display all books
SELECT * FROM Books;

-- STEP 11: Display all authors
SELECT * FROM Authors;

-- STEP 12: Display all members
SELECT * FROM Members;

-- STEP 13: Retrieve books by a specific author
-- Required query: books written by Robert C. Martin
SELECT ISBN, Title, Author, Genre, Quantity
FROM Books
WHERE Author = 'Robert C. Martin';

-- STEP 14: Identify members who borrowed a specific book
-- Required query: members who borrowed Clean Code
SELECT DISTINCT Members.Name
FROM Members
JOIN Loans ON Members.MemberID = Loans.MemberID
WHERE Loans.ISBN = '9780132350884';

-- STEP 15: Alter Members to include MembershipType
ALTER TABLE Members
ADD COLUMN MembershipType VARCHAR(30) DEFAULT 'Standard';

-- Verify the new attribute
SELECT MemberID, Name, MembershipType
FROM Members;

-- STEP 16: Drop the Authors table
-- Run this after capturing the Authors results and book-by-author results.
DROP TABLE Authors;
SELECT name
FROM sqlite_master
WHERE type = 'table'
  AND name = 'Authors';