Install SQLite and Create Your First Database

Confirm the installation

In a new command line window, enter:

sqlite3 --version

You should see a version number beginning with 3., followed by a date and a long code.

Step 1: Create the database file

In your command line window, move to a folder where you want to keep your practice work (for example, Documents), then start sqlite3 with a file name:

cd Documents
sqlite3 library.db

The prompt changes to sqlite>. The file library.db is created as soon as you create a table.

Step 2: Turn on readable output

Commands that start with a period are dot commands for the sqlite3 tool itself, not SQL. They don't need a semicolon:

.headers on
.mode box

This shows column names and draws query results as neat tables.

Step 3: Create tables

SQL statements end with a semicolon. Type or paste:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    country   TEXT
);

CREATE TABLE books (
    book_id   INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    year      INTEGER,
    author_id INTEGER REFERENCES authors(author_id)
);

An INTEGER PRIMARY KEY column numbers new rows automatically. PRAGMA foreign_keys = ON makes SQLite enforce the link between books and authors (it is off by default).

Step 4: Insert data

INSERT INTO authors (name, country) VALUES
    ('Jane Austen', 'England'),
    ('Gabriel Garcia Marquez', 'Colombia'),
    ('Toni Morrison', 'United States');

INSERT INTO books (title, year, author_id) VALUES
    ('Pride and Prejudice', 1813, 1),
    ('Emma', 1815, 1),
    ('One Hundred Years of Solitude', 1967, 2),
    ('Beloved', 1987, 3),
    ('Song of Solomon', 1977, 3);

Step 5: Query the data

-- Books published after 1900, newest first
SELECT title, year FROM books WHERE year > 1900 ORDER BY year DESC;

-- Join books to their authors
SELECT b.title, a.name, b.year
FROM books AS b
JOIN authors AS a ON a.author_id = b.author_id
ORDER BY a.name, b.year;

-- Count books per author
SELECT a.name, COUNT(b.book_id) AS book_count
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.author_id
GROUP BY a.name
ORDER BY book_count DESC, a.name;

The first query returns three rows, newest first: Beloved (1987), Song of Solomon (1977), and One Hundred Years of Solitude (1967). The last query shows Jane Austen and Toni Morrison with 2 books each, and Gabriel Garcia Marquez with 1.

Step 6: Explore and exit

Try these dot commands. .tables lists the tables, .schema books shows how the books table was created, and .quit exits sqlite3:

.tables
.schema books
.quit

Your data is saved in library.db automatically. To open it again later, run sqlite3 library.db from the same folder.