SQLite Database Examples: Simple Queries Every Beginner Should Know
SQLite Database Examples: Simple Queries Every Beginner Should Know
Tanvi Ladva
Author & ContributorSQLite Database Examples: Simple Queries Every Beginner Should Know
Learning SQLite becomes much easier when you stop reading only about database concepts and start writing actual queries.
If you're a beginner, you don't need to learn hundreds of SQL commands at once. A small set of common queries can help you understand how databases work and give you a strong foundation for building applications.
In this guide, we'll use a simple blog database to learn practical SQLite queries step by step.
We'll cover how to:
Create a table
Insert data
Read data
Filter records
Update records
Delete records
Sort results
Limit results
Search using
LIKECount records
Use
GROUP BYWork with multiple tables using
JOIN
Let's get started.
What Is SQLite?
SQLite is a lightweight relational database engine that stores a database in a file rather than requiring a separate database server.
For example:
blog.db
You can use SQL queries to create tables and manage the data inside that database.
SQLite is commonly used for small applications, local development, testing, desktop software, mobile applications, and embedded systems.
1. Create a Table
Before storing information, we need a table.
Let's create a posts table for our blog:
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
content TEXT,
author TEXT,
views INTEGER DEFAULT 0
);
Here we're creating five columns:
id— unique ID for each posttitle— title of the blog postcontent— actual article contentauthor— name of the authorviews— number of views
The PRIMARY KEY makes id the unique identifier for each record.
2. Insert Data Into a Table
Once the table exists, we can add data using INSERT.
INSERT INTO posts (title, content, author, views)
VALUES (
'Learn SQLite',
'SQLite is a lightweight database.',
'Tanvi',
100
);
You can also insert multiple records in one query:
INSERT INTO posts (title, content, author, views)
VALUES
('Learn SQL', 'SQL is used to work with databases.', 'Rahul', 250),
('SQLite Basics', 'Learn the basics of SQLite.', 'Priya', 180),
('Database Guide', 'A beginner guide to databases.', 'Amit', 320);
Now our table contains multiple blog posts.
3. Select All Records
The SELECT statement is one of the most important SQL commands.
To retrieve every post:
SELECT * FROM posts;
The * means that we want all columns.
The result could look like:
idtitleauthorviews1Learn SQLiteTanvi1002Learn SQLRahul2503SQLite BasicsPriya1804Database GuideAmit320
4. Select Specific Columns
You don't always need every column.
For example, if you only want the title and author:
SELECT title, author
FROM posts;
This returns only the requested columns.
Selecting only the data you need can make queries easier to understand and can reduce unnecessary data retrieval.
5. Find a Specific Record With WHERE
The WHERE clause lets you filter records.
For example, to find posts written by Tanvi:
SELECT *
FROM posts
WHERE author = 'Tanvi';
You can also search by ID:
SELECT *
FROM posts
WHERE id = 2;
WHERE is one of the SQL features you'll use frequently in real applications.
6. Filter Using Numbers
You can also filter numeric values.
For example, find posts with more than 200 views:
SELECT *
FROM posts
WHERE views > 200;
You can use other comparison operators too:
= Equal
!= Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
For example:
SELECT *
FROM posts
WHERE views >= 200;
7. Use AND
You can combine multiple conditions using AND.
For example, find posts written by Tanvi with more than 100 views:
SELECT *
FROM posts
WHERE author = 'Tanvi'
AND views > 100;
Both conditions must be true for a record to be returned.
8. Use OR
OR allows a record to match either condition.
For example:
SELECT *
FROM posts
WHERE author = 'Tanvi'
OR author = 'Priya';
This returns posts written by either Tanvi or Priya.
9. Sort Results With ORDER BY
You can sort query results using ORDER BY.
For example, to display posts from highest views to lowest:
SELECT *
FROM posts
ORDER BY views DESC;
DESC means descending order.
To sort from lowest to highest:
SELECT *
FROM posts
ORDER BY views ASC;
ASC means ascending order.
10. Get Only a Few Results With LIMIT
Sometimes you don't want every record.
For example, you might want the five most-viewed posts:
SELECT *
FROM posts
ORDER BY views DESC
LIMIT 5;
This is useful for pages such as:
Popular posts
Latest posts
Top articles
Recommended content
11. Search Text Using LIKE
The LIKE operator is useful when you want to search for text patterns.
For example, find posts where the title contains the word "SQLite":
SELECT *
FROM posts
WHERE title LIKE '%SQLite%';
The % symbol represents any number of characters.
For example:
SQLite Database
Learn SQLite
SQLite for Beginners
could all match the search.
You can also search for titles beginning with "Learn":
SELECT *
FROM posts
WHERE title LIKE 'Learn%';
12. Update Existing Data
The UPDATE statement changes existing records.
Suppose Tanvi's post has 150 views and we want to change it to 200:
UPDATE posts
SET views = 200
WHERE id = 1;
The WHERE clause is extremely important.
Without it:
UPDATE posts
SET views = 200;
every post would be changed to 200 views.
So, always be careful when using UPDATE.
13. Increase a Number
You can also update a value based on its current value.
For example, increase the views of post number 1 by 10:
UPDATE posts
SET views = views + 10
WHERE id = 1;
This is useful for things such as:
View counters
Download counts
Likes
Scores
Inventory values
14. Delete a Record
The DELETE statement removes records.
For example:
DELETE FROM posts
WHERE id = 4;
This removes the post with ID 4.
Just like UPDATE, be careful with the WHERE clause.
This query:
DELETE FROM posts;
removes all records from the table.
The table itself still exists, but its data is deleted.
15. Count Records
The COUNT() function tells you how many records exist.
For example:
SELECT COUNT(*)
FROM posts;
You can also give the result a name:
SELECT COUNT(*) AS total_posts
FROM posts;
The result might be:
total_posts
-----------
4
This can be useful for displaying statistics in an application.
16. Find the Highest Number
You can use MAX() to find the highest value.
For example:
SELECT MAX(views) AS highest_views
FROM posts;
This returns the highest number of views among all posts.
17. Find the Lowest Number
Similarly, MIN() finds the smallest value:
SELECT MIN(views) AS lowest_views
FROM posts;
18. Calculate an Average
The AVG() function calculates the average value.
For example:
SELECT AVG(views) AS average_views
FROM posts;
This could help you calculate the average number of views per blog post.
19. Calculate the Total
The SUM() function adds numeric values.
For example:
SELECT SUM(views) AS total_views
FROM posts;
If your posts have 100, 250, 180, and 320 views, the total would be 850.
20. Group Data With GROUP BY
GROUP BY is useful when you want to group records based on a column.
For example, suppose multiple posts can have the same author.
You could find how many posts each author has written:
SELECT author, COUNT(*) AS post_count
FROM posts
GROUP BY author;
The result could look like:
authorpost_countAmit1Priya1Rahul1Tanvi1
As your database becomes larger, GROUP BY becomes especially useful for reports and statistics.
21. Use DISTINCT
Sometimes the same value appears multiple times, but you only want each value once.
For example:
SELECT DISTINCT author
FROM posts;
This returns a list of unique authors.
If your table contains:
Tanvi
Rahul
Tanvi
Priya
Rahul
the query returns:
Tanvi
Rahul
Priya
22. Use BETWEEN
BETWEEN lets you search for values within a range.
For example, find posts with between 100 and 300 views:
SELECT *
FROM posts
WHERE views BETWEEN 100 AND 300;
This can be useful for filtering numeric data.
23. Use IN
Instead of writing multiple OR conditions, you can use IN.
For example:
SELECT *
FROM posts
WHERE author IN ('Tanvi', 'Priya', 'Rahul');
This finds posts written by any of those three authors.
24. Work With Multiple Tables Using JOIN
Real applications usually have more than one table.
For example, instead of storing the author's name directly inside posts, you might create an authors table.
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
Then your posts table could store the author's ID:
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
content TEXT,
author_id INTEGER
);
Now you can connect the two tables using JOIN.
SELECT posts.title, authors.name
FROM posts
JOIN authors
ON posts.author_id = authors.id;
The result might look like:
titlenameLearn SQLiteTanviLearn SQLRahulDatabase GuideAmit
JOIN is one of the most important concepts to understand when working with relational databases.
25. Check for NULL Values
Sometimes a database field doesn't have a value.
This is represented by NULL.
To find records where content is missing:
SELECT *
FROM posts
WHERE content IS NULL;
Don't use:
WHERE content = NULL;
For checking NULL values, use IS NULL or IS NOT NULL.
For example:
SELECT *
FROM posts
WHERE content IS NOT NULL;
26. Add a New Column
You can add a new column to an existing table using ALTER TABLE.
For example:
ALTER TABLE posts
ADD COLUMN category TEXT;
Now the posts table has a new category column.
You could then update a post:
UPDATE posts
SET category = 'Database'
WHERE id = 1;
27. Create an Index
Indexes can help databases find records more efficiently.
For example, if you frequently search posts by author:
CREATE INDEX idx_posts_author
ON posts(author);
The database can use the index to improve certain queries.
However, indexes aren't free. They take storage and can add overhead to data modifications, so you should create them based on actual query patterns.
A Small SQLite Project Example
Let's put some of these queries together.
Imagine you're building a simple blog.
First, create the table:
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT NOT NULL,
views INTEGER DEFAULT 0
);
Insert some data:
INSERT INTO posts (title, author, views)
VALUES
('SQLite for Beginners', 'Tanvi', 500),
('Learning SQL', 'Rahul', 300),
('Database Basics', 'Priya', 700),
('Understanding SQLite', 'Tanvi', 450);
Find all posts:
SELECT * FROM posts;
Find Tanvi's posts:
SELECT *
FROM posts
WHERE author = 'Tanvi';
Find posts with more than 400 views:
SELECT *
FROM posts
WHERE views > 400;
Show the most popular posts first:
SELECT *
FROM posts
ORDER BY views DESC;
Show only the top two:
SELECT *
FROM posts
ORDER BY views DESC
LIMIT 2;
Count all posts:
SELECT COUNT(*) AS total_posts
FROM posts;
Find the post with the highest views:
SELECT MAX(views) AS highest_views
FROM posts;
This small example already covers many of the SQL operations you'll use when building real applications.
SQLite Query Cheat Sheet
Here's a quick reference for the most important beginner queries:
TaskSQLite QueryCreate tableCREATE TABLEAdd dataINSERT INTORead dataSELECTFilter dataWHERESort dataORDER BYLimit resultsLIMITUpdate dataUPDATEDelete dataDELETESearch textLIKECount recordsCOUNT()Highest valueMAX()Lowest valueMIN()AverageAVG()TotalSUM()Group recordsGROUP BYRemove duplicatesDISTINCTCombine tablesJOINAdd a columnALTER TABLECreate an indexCREATE INDEX
Final Thoughts
You don't need to memorize every SQLite command when you're starting out.
Focus on understanding the basic operations:
Create → Insert → Select → Filter → Update → Delete
Once you're comfortable with those, start learning ORDER BY, LIMIT, LIKE, aggregate functions, GROUP BY, and JOIN.
The best way to learn SQLite is to actually create a small database and experiment with it.
Try creating a database for a blog, expense tracker, task manager, or library system. Add some records, modify them, search through them, and see what each query does.
The more you practice writing SQL queries, the more natural database development becomes.
