CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Nationality VARCHAR(50),
    BirthYear INT
);
PRAGMA table_info(Authors);

CREATE TABLE Books (
    ISBN VARCHAR(20) PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    Author VARCHAR(100),
    Genre VARCHAR(50),
    Quantity INT
);
INSERT INTO Books (ISBN, Title, Author, Genre, Quantity)
VALUES
('9780132350884', 'Clean Code', 'Robert C. Martin', 'Programming', 7),
('9780262033848', 'Introduction to Algorithms', 'Thomas H. Cormen', 'Computer Science', 3),
('9780596007126', 'Head First SQL', 'Lynn Beighley', 'Database', 4),
('9781491950357', 'Designing Data-Intensive Applications', 'Martin Kleppmann', 'Database', 2),
('ISBN005', 'Harry Potter and the Sorcerer''s Stone', 'J.K. Rowling', 'Fantasy', 5),
('ISBN006', 'The Clean Coder', 'Robert C. Martin', 'Programming', 4),
('ISBN007', 'Algorithms Unlocked', 'Thomas H. Cormen', 'Computer Science', 3),
('ISBN008', 'Harry Potter and the Chamber of Secrets', 'J.K. Rowling', 'Fantasy', 4),
('ISBN009', 'Harry Potter and the Prisoner of Azkaban', 'J.K. Rowling', 'Fantasy', 4),
('ISBN010', 'Harry Potter and the Goblet of Fire', 'J.K. Rowling', 'Fantasy', 4);
SELECT * FROM Books;

INSERT INTO Authors (AuthorID, Name, Nationality, BirthYear)
VALUES
(1, 'Robert C. Martin', 'American', 1952),
(2, 'Thomas H. Cormen', 'American', 1956),
(3, 'Lynn Beighley', 'American', 1968),
(4, 'Martin Kleppmann', 'German', 1982),
(5, 'J.K. Rowling', 'British', 1965);
SELECT * FROM Authors;

CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100),
    Phone VARCHAR(20)
);
INSERT INTO Members (MemberID, Name, Email, Phone)
VALUES
(101, 'John Smith', 'john@example.com', '0712345678'),
(102, 'Sarah Johnson', 'sarah@example.com', '0723456789'),
(103, 'David Brown', 'david@example.com', '0734567890'),
(104, 'Emily Wilson', 'emily@example.com', '0745678901'),
(105, 'Michael Davis', 'michael@example.com', '0756789012'),
(106, 'Emma Taylor', 'emma@example.com', '0767890123'),
(107, 'Daniel Thomas', 'daniel@example.com', '0778901234'),
(108, 'Olivia Moore', 'olivia@example.com', '0789012345'),
(109, 'James Martin', 'james@example.com', '0790123456'),
(110, 'Sophia Anderson', 'sophia@example.com', '0701234567'),
(111, 'William Jackson', 'william@example.com', '0713456789'),
(112, 'Ava White', 'ava@example.com', '0724567890'),
(113, 'Benjamin Harris', 'benjamin@example.com', '0735678901'),
(114, 'Mia Thompson', 'mia@example.com', '0746789012'),
(115, 'Lucas Garcia', 'lucas@example.com', '0757890123'),
(116, 'Charlotte Martinez', 'charlotte@example.com', '0768901234'),
(117, 'Henry Robinson', 'henry@example.com', '0779012345'),
(118, 'Amelia Clark', 'amelia@example.com', '0780123456'),
(119, 'Alexander Lewis', 'alexander@example.com', '0791234567'),
(120, 'Ella Walker', 'ella@example.com', '0702345678');
SELECT * FROM Members;

SELECT ISBN, Title, Author, Genre, Quantity
FROM Books
WHERE Author = 'Robert C. Martin';

DROP TABLE Authors;
SELECT name
FROM sqlite_master
WHERE type = 'table';

CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    MemberID INT,
    ISBN VARCHAR(20),
    LoanDate DATE,
    ReturnDate DATE
);
INSERT INTO Loans (LoanID, MemberID, ISBN, LoanDate, ReturnDate)
VALUES
(1001, 101, '9780132350884', '2026-09-01', NULL),
(1002, 101, '9780262033848', '2026-09-05', '2026-09-15'),
(1003, 102, '9780596007126', '2026-09-10', NULL),
(1004, 103, '9780132350884', '2026-09-11', '2026-09-25'),
(1005, 105, '9780132350884', '2026-09-12', NULL),
(1006, 106, 'ISBN005', '2026-09-13', '2026-09-20');
SELECT DISTINCT M.Name
FROM Members M
JOIN Loans L ON M.MemberID = L.MemberID
JOIN Books B ON L.ISBN = B.ISBN
WHERE B.Title = 'Clean Code';

ALTER TABLE Members
ADD MembershipType VARCHAR(30);
SELECT * FROM Members;