SQL — Library schema (DDL, view, join)

-- A small library schema: tables with constraints, seed data,
-- a view, and an aggregate query with a join.
CREATE TABLE authors (
    id       INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    country  TEXT
);

CREATE TABLE books (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors (id),
    title      TEXT NOT NULL,
    published  INTEGER,
    rating     NUMERIC(3, 1) CHECK (rating BETWEEN 0 AND 5)
);

INSERT INTO authors (id, name, country) VALUES
    (1, 'Ada Lovelace', 'UK'),
    (2, 'Grace Hopper', 'US');

INSERT INTO books (id, author_id, title, published, rating) VALUES
    (1, 1, 'Notes on the Analytical Engine', 1843, 4.8),
    (2, 2, 'Compiling for Humans', 1952, 4.5);

CREATE VIEW book_catalog AS
SELECT b.title, a.name AS author, b.published, b.rating
FROM books AS b
JOIN authors AS a ON a.id = b.author_id;

SELECT author, COUNT(*) AS titles, AVG(rating) AS avg_rating
FROM book_catalog
GROUP BY author
ORDER BY avg_rating DESC;