In this first lesson, we will build a tiny record-store database from scratch.

Create Your First SQLite Database

SQLite is a small, fast database engine that stores an entire database in a single file. It does not require a separate database server, a complicated installation, or an administrator standing by. You give SQLite a command, and SQLite gets to work.

In this first lesson, we will build a tiny record-store database from scratch. We will:

1. Create a table.
2. Add three albums.
3. Retrieve the data with a query.

By the end, you will have created, seeded, and queried your first SQLite database.

Create the Table

A database table organizes related information into rows and columns. Our table will be named albums, and every row will represent one album in the store.

CREATE TABLE albums (
    album_id INTEGER PRIMARY KEY,
    artist   TEXT NOT NULL,
    title    TEXT NOT NULL,
    price    REAL NOT NULL
);

The CREATE TABLE statement defines four columns:

• album_id gives every album a unique numeric identifier.
• artist stores the name of the performer.
• title stores the album title.
• price stores the selling price.

PRIMARY KEY means that every album_id must be unique. NOT NULL means that a value is required in that column.

SQLite normally prints nothing when CREATE TABLE succeeds. No news is good news.

Seed the Database

A new table is empty, so our next step is to seed it with a little sample data. We will add all three albums with one INSERT statement.

INSERT INTO albums (album_id, artist, title, price)
VALUES
    (1, 'Elvis Presley',      'Elvis Presley',          10.99),
    (2, 'Georgia Satellites', 'Georgia Satellites',     11.99),
    (3, 'Chuck Berry',        'The Great Twenty-Eight', 12.99);

Each pair of parentheses supplies one complete row. The values appear in the same order as the column names listed after albums.

Text values are enclosed in single quotation marks. Numeric values are not.

Like CREATE TABLE, a successful INSERT normally produces no output. We will confirm the new rows by querying the table.

Query the Table

The SELECT statement retrieves data. An asterisk means “all columns.”

SELECT *
FROM albums;

Expected Output

1|Elvis Presley|Elvis Presley|10.99
2|Georgia Satellites|Georgia Satellites|11.99
3|Chuck Berry|The Great Twenty-Eight|12.99

SQLite displays one album per line. The vertical bars separate the four column values.

The database now contains a table, and the table contains data. That is the basic pattern behind almost every database application:

Create the structure -> Add the data -> Query the data

Complete Source Code

Here is the entire lesson as one SQLite script:

CREATE TABLE albums (
    album_id INTEGER PRIMARY KEY,
    artist   TEXT NOT NULL,
    title    TEXT NOT NULL,
    price    REAL NOT NULL
);

INSERT INTO albums (album_id, artist, title, price)
VALUES
    (1, 'Elvis Presley',      'Elvis Presley',          10.99),
    (2, 'Georgia Satellites', 'Georgia Satellites',     11.99),
    (3, 'Chuck Berry',        'The Great Twenty-Eight', 12.99);

SELECT *
FROM albums;

Every time this script starts with an empty database, it produces the same three rows. That makes it safe to change the artist names, album titles, or prices and run the experiment again.

What You Learned

You have now used three essential SQL statements:

• CREATE TABLE created the database structure.
• INSERT added rows to the table.
• SELECT retrieved those rows.

We have a working SQLite database. Next, we will make its query results look good.

---

Part 2—Runnable Snippet

Now run this code snippet for yourself.