Activity 4: Library Book Joins

-- Authors table
CREATE TABLE authors (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(30)
);

-- Books table (references authors)
CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    available BOOLEAN,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Sample data
INSERT INTO authors VALUES
(1, 'J.K. Rowling', 'UK'),
(2, 'Stephen King', 'USA'),
(3, 'Agatha Christie', 'UK');

INSERT INTO books VALUES
(1, 'Harry Potter', 1, true),
(2, 'The Shining', 2, false),
(3, 'Murder Mystery', 3, true),
(4, 'Pet Sematary', 2, true),
(5, 'Hercule Poirot', 3, false);

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- Show books with author names
SELECT books.title, authors.name as author_name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

-- List all books with author names
SELECT books.title, authors.name as author_name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

-- Show only available books
SELECT books.title, authors.name as author_name, books.available
FROM books
INNER JOIN authors ON books.author_id = authors.id
WHERE books.available = true;

-- Count books per author
SELECT authors.name, COUNT(books.id) as book_count
FROM authors
INNER JOIN books ON authors.id = books.author_id
GROUP BY authors.id, authors.name
ORDER BY book_count DESC;

Harry Potter     | J.K. Rowling
Murder Mystery   | Agatha Christie  
Pet Sematary     | Stephen King