CREATE TABLE Author ( 
    AuthorID INT PRIMARY KEY, 
    Name VARCHAR(100), 
    BirthYear INT
    );

CREATE TABLE Book( 
    Title VARCHAR(100), 
    AuthorID INT, 
    Genre VARCHAR(50), 
    PublishedYear INT, 
    FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);

INSERT INTO Author (AuthorID, Name, BirthYear)VALUES
    (1, 'SE Hinton', 1948), 
    (2, 'J.K. Rowling', 1965), 
    (3, 'George Orwell', 1903); 

INSERT INTO Book (Title, AuthorID, Genre, PublishedYear)VALUES 
  ('Bridge To Terabithia', 1, 'Drama', 2008),
  ('The Outsiders', 1, 'Young Adult', 1985), 
  ('Harry Potter and Sorcerer\'s Stone', 2, 'Fantasy', 1997),
  ('1984', 3, 'Dystopian', 1949); 

SELECT * 
FROM Book; 

SELECT * 
FROM Author; 


-- Find all books by SE Hinton 
    SELECT Title,Genre, PublishedYear
    FROM Book 
    WHERE AuthorID = 1; 

-- Join Books with author names 
    SELECT Book.Title, Author.Name, Book.Genre, Book.PublishedYear
    FROM Book 
    JOIN Author ON Book.AuthorID = Author.AuthorID; 

-- Counts books per author 
    SELECT Author.Name, COUNT(Book.Title) AS BookCount 
    FROM Author 
    JOIN Book ON Author.AuthorID = Book.AuthorID 
    GROUP BY Author.Name; 

SELECT Title, Genre 
FROM Book 
WHERE PublishedYear > 2000; 
CREATE VIEW BooksAfter2000 AS 
SELECT Title, Genre, PublishedYear
FROM Book 
WHERE PublishedYear > 2000;