SQLite tutorial

Before starting the SQLite tutorial, let’s start reading about some fundamental notions about SQLite.
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.
SQLite is fast, reliable, and easy to use. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

SQLite databases are very versatile. They can be used as a data store for web applications, embedded systems, or even as a replacement for traditional SQL databases. SQLite databases are easy to create and require no special configuration. SQLite databases are self-contained, which means they do not need a separate server process or system to operate.

SQLite databases are lightweight and can be easily copied or moved from one system to another.
SQLite databases are transactional, which means that changes made to the database are atomic, consistent, isolated, and durable (ACID). SQLite databases are secure by default. SQLite is a great choice for applications that need a light-weight, easily portable database.

SQLite is free and open source. SQLite is not a SQL database engine, is a C library that implements an SQL database engine. SQLite does not have a separate server process like most other SQL databases. SQLite reads and writes directly to ordinary disk files.

A complete SQL database with multiple tables, indices, triggers, and views, can be contained in a single disk file. SQLite is not client/server. SQLite is embedded into the application that uses it and supports all the features you would expect from a modern SQL database engine including:

SQLite tutorial

Let’s start the tutorial with the basic concepts of SQLite, concepts that will be useful in developing a complex SQLite database.

1. Data Definition

Create Table – learn how to create a new table in the database.
Alter Table – learn how to modify the structure of a table.
Add Column – show you how to add column to a table.
Rename Column – show you how to rename column.
Drop Column – show you how to remove a column from a table.

2. Data Manipulation

Insert – insert records into a table.
Update – modify values of existing rows in a table.
Delete – delete rows from a table.
Replace – insert new rows or replace existing rows in a table.

3. Constraints

Primary Key – learn how to create the primary key.
Foreign Key – define foreign key to enforce the relationships between tables.
NOT NULL constraint – show you how to define a column with NOT NULL constraint.
CHECK constraint – enforces table integrity by limiting the values of a column.
UNIQUE constraint – requires that the values inserted in the columns of a table be unique.
AUTOINCREMENT – should be avoided unless strictly necessary.

4. Query table

Select – learn how to query data from a table using SELECT statement.

5. Sorting data rows

Order By – sort the data rows in either ascending or descending order.

6. Filtering data rows

Select Distinct – return unique rows from a table.
Where – filter rows in a query using specific conditions.
Between – is used to extract values within a given range.
Like – is used to match a certain pattern.
In – is used to check if a given value is contained within a set of values.
Limit – is used to limit the number of rows returned.

7. Grouping data rows

Group By – the GROUP BY clause is used to group together similar values.
Having – the HAVING clause is used to filter groups based on a specified condition.

8. Joining tables

Inner Join – returns rows from multiple tables that match the specified join condition.
Left Join – use the left join clause to return rows from the left table.
Cross Join – learn how to use the cross join clause to return the cartesian product.

9. Subquery

Subquery – syntax and example of SQLite subquery.

10. Views

Views – introduction to SQLite views.
Create View – learn how to create a view.
Drop View – learn how to drop a view.

11. Transactions

Transaction – introduction to SQLite transactions, examples with COMMIT and ROLLBACK.

12. Triggers

Triggers – shows you the basics of triggers.
Create Trigger – syntax and examples of how to create a trigger.
Drop Trigger – show you how to remove a trigger.