SQLite Tutorial

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.
• AND – filter rows by combining multiple conditions.
• OR – combine multiple conditions and filter rows based on at least a specified condition being true.
• Limit – constrain the number of rows a query returns and how to get only the necessary data from a table.
• Between – test whether a value is in a range of values.
• In – check if a value matches any value in a list of values 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

• Inner Join – query data from multiple tables using the inner join clause.
• Left Join – combine data from multiple tables using the left join clause.
• Right Join – combine rows from two tables based on a related column.
• 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 with itself to create a result set that joins rows with other rows within the same table.
• Full Outer Join – show you how to use the full outer join in the SQLite.
• SQLite joins – recap the joins including inner join, left join, right join, full outer join, and cross join.

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 return 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 & CTE

• Subquery – introduce you to the SQLite subquery and correlated subquery.
• Exists operator – test for the existence of rows returned by a subquery.
• Common Table Expressions (CTE) – use CTEs to simplify your queries and make them more readable.

Section 8. More querying techniques

• Case – add conditional logic to the query.

Section 9. Changing data

This section guides you on updating data in the table using insert, update, delete, and replace 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.
• Upsert – perform an insert if the row does not exist or update otherwise.
• RETURNING clause – return the inserted, updated, and deleted rows.

Section 10. Transactions

• Transaction – show you how to handle transactions in SQLite.

Section 11. Data definition

In this section, you’ll learn how to create database objects such as tables, views, and indexes using SQL data definition language.

• SQLite Data Types – introduce you to the SQLite dynamic type 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.
• Alter Table – show you how to 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. Constraints

• Primary Key – show you how to define the primary key for a table.
• NOT NULL constraint – learn how to enforce values of columns that 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 AUTOINCREMENT column attribute works and why you should avoid using it.

Section 13. 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 14. 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 15. Triggers

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

Section 16. Full-text search

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

Section 17. SQLite tools

• SQLite Commands – show you the most commonly used command in the 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 the .dump command to back up and restore a database.
• SQLite Import CSV – import CSV files into a table.
• SQLite Export CSV – export an SQLite database to CSV files.