SQLite Tutorial

This SQLite tutorial teaches you everything you need to know to start using SQLite effectively. You will learn SQLite through extensive hands-on practices.

If you have been working with other relational database management systems such as MySQL, PostgreSQL, Oracle, Microsoft SQL Server and you hear about SQLite. And you are curious to know more about it.

If your friends recommended you use an SQLite database instead of using a file to manage structured data in your applications. You want to get started with the SQLite immediately to see if you can utilize it for your apps.

If you are just starting out learning SQL and want to use SQLite as the database for your application.

If you are one of the people described above, this SQLite tutorial is for you.

SQLite is an open-source, zero-configuration, self-contained, stand-alone, transaction relational database engine designed to be embedded into an application.

Getting started with SQLite

You should go through this section if this is the first time you have worked with SQLite. Follow these 3-easy steps to get started with SQLite fast.

First, help you answer the first important question: what is SQLite? You will have a brief overview of SQLite before working on it.
Second, show you step by step how to download and install SQLite GUI tool on your computer.
Third, introduce you to an SQLite sample database and walk you through the steps of using the sample database for practicing.

Basic SQLite tutorial

This section presents basic SQL statements that you can use with SQLite. You will first start querying data from the sample database. If you are already familiar with SQL, you will notice the differences between SQL standard and SQL dialect in SQLite.

Section 1. Simple query

Select – query data from a single table using SELECT statement.

Section 2. Sorting rows

Order By – sort the result set in ascending or descending order.

Section 3. Filtering data

Select Distinct – query unique rows from a table using the DISTINCT clause.
Where – filter rows of a result set using various conditions.
Limit – constrain the number of rows that you want to return. The LIMIT clause helps you get the necessary data returned by a query.
Between – test whether a value is in a range of values.
In – check if a value matches any value in a list of value or subquery.
Like – query data based on pattern matching using wildcard characters: percent sign (%) and underscore (_).
Glob – determine whether a string matches a specific UNIX-pattern.
IS NULL – check if a value is null or not.

Section 4. Joining tables

SQLite join – learn the overview of joins including inner join, left join, and cross join.
Inner Join – query data from multiple tables using inner join clause.
Left Join – combine data from multiple tables using left join clause.
Cross Join – show you how to use the cross join clause to produce a cartesian product of result sets of the tables involved in the join.
Self Join – join a table to itself to create a result set that joins rows with other rows within the same table.
Full Outer Join – show you how to emulate the full outer join in the SQLite using left join and union clauses.

Section 5. Grouping data

Group By – combine a set of rows into groups based on specified criteria. The GROUP BY clause helps you summarize data for reporting purposes.
Having – specify the conditions to filter the groups summarized by the GROUP BY clause.

Section 6. Set operators

Union – combine result sets of multiple queries into a single result set. We also discuss the differences between UNION and UNION ALL clauses.
Except – compare the result sets of two queries and returns distinct rows from the left query that are not output by the right query.
Intersect – compare the result sets of two queries and returns distinct rows that are output by both queries.

Section 7. Subquery

Subquery – introduce you to the SQLite subquery and correlated subquery.
Exists operator – test for the existence of rows returned by a subquery.

Section 8. More querying techniques

Case – add conditional logic to the query.

Section 9. Changing data

This section guides you on how to update data in the table using insert, update, and delete statements.

Insert – insert rows into a table
Update – update existing rows in a table.
Delete – delete rows from a table.
Replace – insert a new row or replace the existing row in a table.

Section 10. Transactions

Transaction – show you how to handle transactions.

Section 11. Data definition

In this section, we show you how to create database objects such as tables, views, indexes using SQL data definition language.

SQLite Data Types – introduce you to the SQLite dynamic types system and its important concepts: storage classes, manifest typing, and type affinity.
Create Table – show you how to create a new table in the database.
Primary Key – show you how to define the primary key for a table.
NOT NULL constraint – ensure values in a column are not NULL.
UNIQUE constraint – ensure values in a column or a group of columns are unique.
CHECK constraint – ensure the values in a column meet a specified condition defined by an expression.
AUTOINCREMENT – explain how the attribute AUTOINCREMENT works and why you should avoid using it.
Alter Table – show you how to use modify the structure of an existing table.
Rename column – learn step by step how to rename a column of a table.
Drop Table – guide you on how to remove a table from the database.
VACUUM – show you how to optimize database files.

Section 12. Views

Create View – introduce you to the view concept and show you how to create a new view in the database.
Drop View – show you how to drop a view from its database schema.

Section 13. Indexes

Index – teach you about the index and how to utilize indexes to speed up your queries.
Index for Expressions – show you how to use the expression-based index.

Section 14. Triggers

Trigger – manage triggers in the SQLite database.
INSTEAD OF triggers – learn about INSTEAD OF triggers and how to create an INSTEAD OF trigger to update data via a view.

Section 15. Full-text search

Full-text search – get started with the full-text search in SQLite.

Section 16. SQLite tools

SQLite Commands – show you the most commonly used command in sqlite3 program.
SQLite Show Tables – list all tables in a database.
SQLite Describe Table – show the structure of a table.
SQLite Dump – how to use dump command to backup and restore a database.
SQLite Import CSV – how to import CSV file into a table.
SQLite Export CSV – how to export an SQLite database to CSV files.

SQLite Functions

SQLite Aggregate Functions

This tutorial shows you how to use the SQLite aggregate functions to find the maximum, minimum, average, sum, and count of a set of values.

SQLite Date Functions

This section provides you with SQLite date and time functions that help you manipulate datetime data effectively.

SQLite String Functions

This section shows the most commonly used SQLite string functions that help you manipulate character string data effectively.

SQLite Window Functions

SQLite window functions perform a calculation on a set of rows that are related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single result row.

SQLite Programming Interfaces

SQLite Resources

If you want to know more information about SQLite, you can go through a well-organized SQLite resources page that contains links to useful SQLite sites.