sql994 B

SQL — Library schema (DDL, view, join)

A realistic SQL script with CREATE TABLE constraints and foreign keys, INSERT seed data, a CREATE VIEW, and a grouped aggregate query with a join — for testing SQL parsers, formatters, and highlighters.

Syntax highlighting Code parsing Editor testing

Preview — first 34 linessql

-- 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;

Specifications

Language: SQL
Kind: realistic snippet
Lines: 33
Encoding: UTF-8
Line Endings: LF

Testing contract

Expected to pass

Scenario: Execute the whole script against a fresh database, then run the final SELECT.
Expected result: Two tables, two rows each, one view and one grouped query: the final SELECT returns two rows ordered by descending average rating - `Ada Lovelace` at 4.8 then `Grace Hopper` at 4.5, one title each. The `rating` CHECK constraint rejects any value outside 0 to 5, and `books.author_id` is a foreign key onto `authors.id`.

What is a .sql file?

SQL files contain plain-text Structured Query Language statements, typically schema definitions, data inserts, or queries used to build or populate a database. Dialect details vary between engines such as PostgreSQL, MySQL, and SQLite. A dump file often recreates an entire database when executed.

How to use this file

Use an example SQL file to test statement parsing, database restore and migration tooling, and dialect-compatibility of import pipelines.

How to use this file for testing

“SQL — Library schema (DDL, view, join)” is a deterministic Novus Examples fixture for Syntax highlighting, Code parsing, Editor testing. Idiomatic hello-world programs and realistic snippets across sixteen languages, exercising comments, string escapes, interpolation, numeric literals, and language keywords — for testing syntax highlighters, editor themes, and tree-sitter grammars.

Documented properties for this file: SQL · UTF-8 · LF. Compare results against paired or grouped companions on this page when present (clean↔damaged, searchable↔scanned, or format twins) so scores stay reproducible across runs.

Download the file once, keep the path stable in CI or local scripts, and treat the spec table as the contract: dimensions, seeds, field lists, and roles are intentional. Corrupt or invalid samples are labelled as such — expect parsers to fail loudly rather than silently accept them.

This is a short, known-correct source file. Run it through your syntax highlighter, linter, formatter, tree-sitter grammar, or parser, and open it in the in-browser editor to tweak and re-download. Each snippet exercises comments, string escapes, literals, and language keywords.

Code examples

psql mydb < library-schema.sql          # PostgreSQL
mysql -u user -p mydb < library-schema.sql  # MySQL

Generated by generation/code_samples.py. Free for any use, no attribution required — license.