SQLite for Beginners: INSERT INTO, Multi-Row Inserts & NULL Values | Episode 2

SQLite for Beginners: INSERT INTO, Multi-Row Inserts & NULL Values

In this tutorial, you will learn how to add data to an SQLite database using the INSERT INTO statement. We cover inserting single rows with specified columns, adding multiple rows in one command, and using NULL to represent missing values. By building a simple bookshelf database, you will gain practical experience with these fundamental SQL operations.

Code

-- Create a table named books with four columns: id, title, author, and pages
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  pages INTEGER
);

-- Insert one book row at a time, specifying columns and values
INSERT INTO books (title, author, pages) VALUES ('The Hobbit', 'J.R.R. Tolkien', 310);
INSERT INTO books (title, author, pages) VALUES ('1984', 'George Orwell', 328);
INSERT INTO books (title, author, pages) VALUES ('To Kill a Mockingbird', 'Harper Lee', 281);

-- Insert multiple rows in a single statement by separating each row with commas
INSERT INTO books (title, author, pages) VALUES
  ('Pride and Prejudice', 'Jane Austen', 279),
  ('The Catcher in the Rye', 'J.D. Salinger', 214),
  ('Moby-Dick', 'Herman Melville', NULL);  -- NULL used for unknown page count

-- Query to verify all inserted books
SELECT * FROM books;

Key Points

• The INSERT INTO statement adds one row by listing column names followed by corresponding VALUES in parentheses.
• You can insert multiple rows in one statement by separating each set of values with commas.
• Text values must be enclosed in single quotes, while numbers are written without quotes.
• NULL is a keyword representing missing or unknown data and should not be quoted.
• Running SQL scripts with SQLite can be done using command-line tools like sqlite3 for efficient batch execution.