Library

Example of a library database

You must execute the code again to save those values in the database.

-- Create the Authors table
CREATE TABLE Authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    nationality VARCHAR(50)
);

-- Create the Books table
CREATE TABLE Books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    published_year INT,
    genre VARCHAR(50),
    author_id INT REFERENCES Authors(id)
);

-- Insert sample data into Authors
INSERT INTO Authors (name, nationality) VALUES
('George Orwell', 'British'),
('Jane Austen', 'British'),
('Mark Twain', 'American'),
('Gabriel Garcia Marquez', 'Colombian'),
('Haruki Murakami', 'Japanese'),
('Aldous Huxley', 'British'),
('Ray Bradbury', 'American');

-- Insert sample data into Books
INSERT INTO Books (title, published_year, genre, author_id) VALUES
('1984', 1949, 'Dystopian', 1),
('Animal Farm', 1945, 'Political Satire', 1),
('Pride and Prejudice', 1813, 'Romance', 2),
('Sense and Sensibility', 1811, 'Romance', 2),
('The Adventures of Tom Sawyer', 1876, 'Adventure', 3),
('The Adventures of Huckleberry Finn', 1884, 'Adventure', 3),
('One Hundred Years of Solitude', 1967, 'Magical Realism', 4),
('Love in the Time of Cholera', 1985, 'Romantic', 4),
('Norwegian Wood', 1987, 'Fiction', 5),
('Kafka on the Shore', 2002, 'Fiction', 5),
('Brave new world', 1932, 'Dystopian', 6),
('Fahrenheit 451', 1953, 'Dystopian', 7);

-- Query 
SELECT 
    title, 
    published_year, 
    name AS author_name
FROM Books
JOIN Authors ON Books.author_id = Authors.id
WHERE genre = 'Dystopian'
ORDER BY published_year DESC;

title	published_year	author_name
Fahrenheit 451	1953	Ray Bradbury
1984	1949	George Orwell
Brave new world	1932	Aldous Huxley

CREATE TABLE
CREATE TABLE
INSERT 0 7
INSERT 0 12