SQLite Tutorial: Everything You Need To Know

If you have worked with relational database systems, it is likely that you have heard of popular database systems like such as MySQL, SQL Server or PostgreSQL. SQLite is another extremely useful RDBMS that is very simple to set up and operate. Also, it has many distinct features over other relational databases. This SQLite tutorial teaches basic concepts that you need to know with the help of extensive hands-on practices.

The topics discussed in this article are:

• What is SQLite?
  • Features of SQLite
• Installing SQLite on Windows
• SQLite Commands
  • Database Commands
  • Table Commands
  • CRUD Operations
  • SQLite Clauses/Conditions
  • Joins in SQLite
• Disadvantages of SQLite

SQLite Tutorial: What is SQLite?

Here is the industry standard definition of SQLite:

You can consider SQLite as a “lighter” version of other complex RDBMS (Oracle, SQL, etc.), where its database engine is configured for independent processing (in-process library) i.e. a server-less, self-contained, zero-configuration and transactional. It is known for its portability, reliability, and strong performance even in low-memory environments. Also, SQLite is a popular choice as an embedded database for local/client storage in end programs or applications, unlike other RDBMS, where the client-server DB engine is configured.

Features of SQLite

SQLite offers many distinct features such as:

• Serverless: Most SQL databases are implemented as a separate server process, but SQLite does not have a separate server process. It is a serverless database engine. It reads and writes directly to ordinary disk files.
• Zero-Configuration: It requires no configuration to get it running. This means, there is no server process that needs to be started, stopped, or configured like in a client/ server system.
• Manifest Typing: SQLite uses manifest typing, which allows the storage of any amount of any data type into any column without no matter the column’s declared datatype. Note that there are certain exceptions to this rule.
• Lightweight: As the name implies, the SQLite library is very lightweight. Thing is, although the space it uses varies depending on the system where it’s installed, it can take up less than 600KiB of space.
• Portable: Unlike other DBMS, an entire SQLite database is stored in a single file. This file can be shared via removable media or file transfer protocol very easily.
• Diverse Choice: Many programming languages provide bindings for SQLite, including C, C++, C#, Java, JavaScript, Ruby, Python, and many more.
• Free: SQLite is free and open-source. To work with SQLite a commercial license is not required.

As listed above SQLite is known for its zero-configuration which means no complex setup or administration is really required. In the next part of this SQLite tutorial, let’s see how to install SQLite on your system.

SQLite Tutorial: Installing SQLite on Windows

The steps to follow are:

Step1: Go to the official SQLite website and click on the suitable link to download precompiled binaries.

Step2: Download the SQLite command-line zip file(here: sqlite-tools-win32-x86-3270200.zip) and expand these files in a folder of your choice.

This SQLite command-line tool will contain the following SQLite products

• SQLite core: The SQLite core contains the actual database engine and public API.
• SQLite3 command-line tool: The sqlite3 application is a command-line tool that is built on top of the SQLite core.
• Tcl extension:  This library is essentially a copy of the SQLite core with the Tcl bindings tacked on.
• SQLite analyzer tool: The SQLite analyzer tool is used to analyze database files.

Step3: After that, initiating the SQLite command line is as simple as clicking on the sqlite3 application, which will make the command line pop up.

If you want to test further, simply type .help command from sqlite> prompt to see all available commands in sqlite3 like as shown below.

Note: By default, an SQLite session uses the in-memory database, therefore, all changes will be gone when the session ends.

Simple enough right? Then, let’s get started with SQLite commands.

SQLite Tutorial: SQLite Commands

This section of the SQLite tutorial presents basic SQL statements that you can use with SQLite.

Note: SQLite commands end with a semi-colon (;). It tells SQLite that your command is complete and should be run. Also, you can spread your command across multiple lines and use the semi-colon on the last line.

Database Commands

This section consists of those commands, by which you can deal with your database. The commands are:

• SQLite Create Database

SQLite does not use the CREATE DATABASE statement like in other relational database management systems, such as MySQL, SQL Server, etc. To create a new database in SQLite, simply enter sqlite3 followed by the name of the file that you wish to use for the database. The following code creates a database file called StudentDetails.db:

Example

sqlite3 StudentDetails.db;

sqlite> .databases 
main: D:sqliteStudentDetails.db;

• SQLite Attach Database

When you have multiple databases, you can use only one at a time. In SQLite, the ATTACH DATABASE statement is used to attach a particular database for the current connection. After this command, all SQLite statements will be executed under the attached database.

Example

 sqlite> ATTACH DATABASE 'DepartmentDetails.db' AS 'Department';

sqlite> .databases
main: D:sqliteStudentDetails.db;
Department: D:sqliteDepartmentDetails.db

• SQLite Detach Database

In SQLite, the DETACH DATABASE statement is used to detach the alias-named database from a database connection that was previously attached by using the ATTACH statement. If the same database file has been attached with multiple aliases, then this command will disconnect only the given name and the rest of the attachment will still continue to exist. The databases within the in-memory or temporary database will be destroyed completely and the content will be lost.

Example

sqlite> .databases
main: D:sqliteStudentDetails.db;
Department: D:sqliteDepartmentDetails.db
Student: D:sqliteStudentDetails.db
DeptInformation: D:sqliteDepartmentDetails.db

sqlite> DETACH DATABASE 'Department';

sqlite> .databases
main: D:sqliteStudentDetails.db;
Student: D:sqliteStudentDetails.db
DeptInformation: D:sqliteDepartmentDetails.db

Table Commands

Here we will learn how to deal with tables when using SQLite. 

• SQL Create Table

In SQLite, the CREATE TABLE statement is used to create a new table. While creating the table, you need to name the table and define its column and data types of each column.

Syntax:

CREATE TABLE table_name(
         Column1 column_type [constraints]
         Column2 column_type [constraints]
         [.....]
          );

Example

CREATE TABLE StudentInfo(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
DEPARTMENTID INTEGER NOT NULL,
PHONE TEXT DEFAULT 'UNKNOWN',
FOREIGN KEY(DEPARTMENTID) REFERENCES DepartmentInfo(DeptID)
); 

You can check whether the table was created or not by using the .tables command as shown below. Note that I have already created a table called DepartmentInfo where DeptID is the primary key. Departments table has a Foreign key constraint to the Students table.

 

sqlite> .tables
StudentInfo Contacts Emp_Master

• SQLite Drop Table

In SQLite, the DROP TABLE statement allows you to remove or delete a table from the SQLite database. Once the table is dropped, all the data it contains are permanently removed from the database. Any associated indexes and triggers are also removed. If there is any foreign key constraint enabled on that table, then that will remove equivalently for each row in the table and any triggers associated with the table also will be dropped.

Syntax

DROP TABLE [ IF EXISTS ] table_name;

Example

 DROP TABLE Department;
Error: no such table: Department

DROP TABLE Company;
sqlite> .tables
StudentInfo

Note: IF EXISTS, is an optional clause. If specified, the DROP TABLE statement will not raise an error if one of the tables does not exist.

Also, there is an SQLite Alter Table statement, that we will understand in the next few sections of this article. Now that we have created a table, let’s check out how to insert, delete and alter the data.

SQLite Tutorial: CRUD Operations

• SQLite Insert Query

After creating the table, SQLite Insert Into command can be used to create new rows in the specified table. There are two meaningful forms of the SQLite insert statement. The first form uses a VALUES clause to specify a list of values to insert.

Syntax

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

Example

INSERT INTO StudentInfo ( ID, NAME, AGE, ADDRESS, DEPARTMENTID, PHONE)
VALUES (1,'Dean', 20, 'California', 2, '934*******');

Output

SELECT *from StudentInfo;
ID          NAME        AGE         ADDRESS     DEPARTMENTID  PHONE
----------  ----------  ----------  ----------  ----------  ----------
1           Dean        20          California  2  934*******

Here, a single new row is created and each value is recorded into its respective column. Note that both lists must have the same number of items. Here, the list of the columns is optional. We can also insert data to the table without specifying the list of columns. 

Example

INSERT INTO StudentInfo 
VALUES ( 2, 'SAM', 22, 'Texas', 2, '976*******');

Output

 SELECT *from StudentInfo;
ID          NAME        AGE         ADDRESS     DEPARTMENTID  PHONE
----------  ----------  ----------  ----------  ----------  ----------
1           Dean        20          California  2  934*******
2           SAM         22          Texas        2  976*******

SQLite also offers a feature to insert multiple rows in a single INSERT statement. The syntax is as shown below.

Example

INSERT INTO StudentInfo
VALUES (3,'John',23,'Norway',1,'923*******'),
(4,'Mitch',22,'Houston',3,'934*******');

Output

 Select *from StudentInfo;
1|Dean|20|California|2|934*******
2|SAM|22|Texas|2|976*******
3|John|23|Norway|1|923*******
4|Mitch|22|Houston|3|934*******

As you can see, the format of the output is not quite similar to the one before. So, how do you change the format of output in SQLite? Let’s format the output so that our results are a bit easier to read.

• Formating

You can use .mode to change the output mode. The above example uses .mode list, which displays the results as a list. Also, you can use .headers statement to specify whether or not to display column headers. Once you made the changes, you can view the setting using .show command.

Example

 sqlite>.mode 'column'
sqlite> .headers on
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "n"
stats: off
width:
filename: StudentDetails.db

Output

 SELECT *FROM StudentInfo;

ID NAME AGE ADDRESS DEPARTMENT PHONE
---------- ---------- ---------- ---------- ---------- ----------
1 Dean 20 California 2 934*******
2 SAM 22 Texas 2 976*******
3 John 23 Norway 1 923*******
4 Mitch 22 Houston 3 934*******

• SQLite Select Query 

In SQLite, the Select statement is used to fetch data from a table, which returns data in the form of a result table. These result tables are also called result sets.  Using SQLite select statement we can perform simple calculations or multiple expressions based on our requirements. We’ve already used a SELECT statement previously when we inserted data.

Syntax

SELECT [ALL | DISTINCT] result [FROM table-list]
[WHERE expr]

• DISTINCT – When we use distinct keyword in a select statement it returns only distinct rows of data.
• ALL – If we use ALL keyword in a select statement it returns all the rows of data even if it is duplicated.
• FROM table-list – It is a list of tables from which you want to get data.
• WHERE expression – The WHERE expression is used to define our custom conditions to get the required data from tables.

Example1

 SELECT ID, NAME FROM StudentInfo WHERE AGE < 21;

Output

ID NAME
---------- ----------
1 Dean

Example2

Select NAME FROM StudentInfo WHERE DEPARTMENTID
= (SELECT DeptID FROM DepartmentInfo WHERE DeptName = 'Psychology');

Output

//fetches people from department whose id is 2

NAME
----------
Dean
SAM

• SQLite Update Query

In SQLite, the UPDATE statement can be used to modify the existing records in a table. The WHERE clause of SQLite can be used in order to specify exactly which rows should be updated. You can easily update all rows, some rows, or none, depending on the filtering conditions applied by the WHERE clause.

Syntax

UPDATE table_name  
SET column1 = value1, column2 = value2...., columnN = valueN  
WHERE [condition];

Example

UPDATE StudentInfo SET DEPARTMENTID = 4 WHERE ID = '2';

Output

 SELECT *FROM StudentInfo;
ID          NAME        AGE         ADDRESS     DEPARTMENTID  PHONE
----------  ----------  ----------  ----------  ------------  ----------
1           Dean        20          California  2             934*******
2           SAM         22          Texas       4             976*******
3           John        23          Norway      1             923*******
4           Mitch       22          Houston     3             934*******

• SQLite Delete Query

In SQLite, the DELETE statement can be used to delete the record from the table. You can easily delete all rows, some rows, or none, depending on the filtering conditions applied by the WHERE clause.

Example

 DELETE FROM DepartmentInfo WHERE DeptName = 'Science';

Output

 SELECT *FROM DepartmentInfo;
DeptID DeptName
---------- -----------
1 Mathematics
2 Psychology
3 Sports
4 Music

If you try to delete a record that is referenced by a foreign key, you will get an error. You will need to delete the foreign key records first, before deleting the primary key record. Let’s try to delete department science.

Example

DELETE FROM DepartmentInfo WHERE DeptName = 'Music';
Error: FOREIGN KEY constraint failed

So, we need to delete the foreign key records before we delete the primary key. 

DELETE FROM StudentInfo WHERE DEPARTMENTID = 4;

sqlite> DELETE FROM DepartmentInfo WHERE DeptName = 'Music';

sqlite> SELECT *FROM DepartmentInfo;
DeptID      DeptName
----------  -----------
1           Mathematics
2           Psychology
3           Sports

 SELECT *FROM StudentInfo;
ID          NAME        AGE         ADDRESS     DEPARTMENTID  PHONE
----------  ----------  ----------  ----------  ------------  ----------
1           Dean        20          California  2             934*******
3           John        23          Norway      1             923*******
4           Mitch       22          Houston     3             934*******

Now you know how to edit the records in the SQLite Database table. Moving further in this SQLite tutorial blog, let’s discuss different clauses and conditions that you come across in SQLite most frequently.

SQLite Clauses/Conditions

Before getting started with clauses, here’s the complete syntax of the SELECT statement in SQLite.

Syntax

SELECT [ALL | DISTINCT] result [FROM table-list]
[WHERE expr]
[GROUP BY expr-list]
[HAVING expr]
[compound-op select]*
[ORDER BY sort-expr-list]
[LIMIT integer [(OFFSET|,) integer]]

Note: I have updated the StudentInfo and DepartmentInfo tables as shown below.

//Student 
Table ID          NAME        AGE         ADDRESS     DEPARTMENTID  PHONE
----------  ----------  ----------  ----------  ------------  ----------
1           Dean        20          California  2             934*******
3           John        23          Norway      1             923*******
4           Mitch       22          Houston     3             934*******
2           SAM         22          Texas       4             976*******
5           Johny       23          Norway      2             945*******
6           Robin       23          Norway      2             UNKNOWN

//Department Details
DeptID      DeptName
----------  -----------
1           Mathematics
2           Psychology
3           Sports
4           Music
5           Science

• SQLite WHERE

In SQLite, the WHERE clause is used to impose restrictions on the SELECT statement by defining one or more conditions to get the required data from tables in the database. If the condition specified satisfied or true, it returns specific value from the table. As you have seen before The WHERE clause not only is used in the SELECT statement, but it is also used in UPDATE, DELETE statement, etc.

Example

SELECT NAME FROM StudentInfo WHERE AGE = 23;NAME
----------
John
Johny
Robin

In SQLite, there are a number of relational operators that can be used with the WHERE clause. 

• SQLite GROUP BY

In SQLite, the GROUP BY clause is used to aggregate data into a single row where the value of one or more specified columns is repeated. This clause is used with WHERE clause in the SELECT statement and precedes the ORDER BY clause.

Syntax

SELECT result
FROM [table-list]
GROUP BY [expr-list]

SELECT NAME, ADDRESS FROM StudentInfo GROUP BY NAME;

NAME ADDRESS
---------- ----------
Dean California
John Norway
Johny Norway
Mitch Houston
Robin Norway
SAM Texas

Notice that the grouping process has two steps. First, the GROUP BY expression is used to arrange table rows into different groups. Once the groups are defined, the SELECT statement defines how those groups are flattened down into a single row.

• SQLite ORDER BY

Generally, SQLite tables store data in unspecified order and it will return records in the same unspecified order while fetching data using SQLite select statement. In such cases, you can use the ORDER BY clause is used to sort column records either in ascending or descending order. In the example below, I have grouped and ordered9in descending order) the data based on the address.

Syntax

SELECT expressions
FROM tables-list
[WHERE conditions]
ORDER BY column1, column2,... [ ASC | DESC ];

Example

SELECT ADDRESS, COUNT(ADDRESS) FROM StudentInfo GROUP BY ADDRESS ORDER BY ADDRESS DESC;
ADDRESS COUNT(ADDRESS)
---------- --------------
Texas 1
Norway 3
Houston 1
California 1

• SQLite HAVING BY

In SQLite, the HAVING clause is identical to WHERE clause. HAVING clause is a further condition applied after aggregation takes place along with the group by in select statement. Generally in SQLite, WHERE clause is used to apply a condition to individual elements in a table and the HAVING clause is used to add filter conditions based on the groups created by Group By clause.

Example

SELECT ADDRESS, COUNT(ADDRESS) FROM StudentInfo 
GROUP BY ADDRESS 
HAVING COUNT(*)>1;

ADDRESS     COUNT(ADDRESS)
----------  --------------
Norway      3

• SQLite Limit Clause

In SQLite, the LIMIT clause is used to set a limit to the records returned by the select statement. Let’s consider an example to understand the concept.

Syntax

SELECT expressions
FROM tables-list
[WHERE conditions]
LIMIT number_rows OFFSET offset_value;

Example

SELECT NAME, ADDRESS FROM StudentInfo LIMIT 4 OFFSET 2;
NAME        ADDRESS
----------  ----------
Mitch       Houston
SAM         Texas
Johny       Norway
Robin       Norway 

OFFSET is optional and it defines how many rows to skip at the beginning of the result set based on offset_value.

• SQLite AND & OR

In SQLite, the AND & OR operators are used to perform multiple conditions on select, insert, update and delete statements based on our requirements. SQLite AND operator will return rows or records which satisfy the conditions defined by using AND operator. 

Example1

SELECT NAME FROM StudentInfo WHERE AGE = 22 AND ADDRESS = 'Texas';
NAME
----------
SAM

OR condition is used to define multiple conditions in SQLite statements and it will return rows or records from statement if any of one condition satisfied.

Example2

SELECT NAME FROM StudentInfo WHERE (AGE = 22 AND ADDRESS = 'Norway') OR ADDRESS = 'Norway';
NAME
----------
John
Johny
Robin

• SQLite GLOB Operator

In SQLite, the GLOB operator is used to check whether the given string value matches a specific pattern or not. In case if string value matches with pattern value then it will return true and it’s similar to the LIKE operator. Also, GLOB is case sensitive. 

Syntax

SELECT * FROM table_name
WHERE column_name GLOB 'search-expression'

Example

SELECT *FROM StudentInfo WHERE NAME GLOB 'Joh*';
ID NAME AGE ADDRESS DEPARTMENTID PHONE
---------- ---------- ---------- ---------- ------------ ----------
3 John 23 Norway 1 923*******
5 Johny 23 Norway 2 945*******

• SQLite Distinct 

In SQLite, the DISTINCT keyword will scan the result set of the SELECT statement and eliminates any duplicate rows. Also, NULL values consider as duplicates so if we use the DISTINCT clause with a column that has NULL values then it will keep only one row of a NULL value. When you apply DISTINCT for multiple columns, then the statement returns each unique combination of coulnm1 and column2.

Example

SELECT DISTINCT AGE FROM StudentInfo;
AGE
----------
20
23
22

• SQLite IN Operator

In SQLite, the IN operator is used to determine whether the given value matches a list of given values or the result returned by the subquery.

Example

SELECT NAME FROM StudentInfo WHERE ADDRESS IN ('Texas', 'Houston');
NAME
----------
Mitch
SAM

• SQLite UNION & UNION ALL

In SQLite, the UNION operator is used to combine the result sets of 2 or more SELECT statements and it removes duplicate rows between the various SELECT statements. Remember that the SELECT statements which we used with the UNION operator must have the same number of fields in the result sets with similar data types.

Syntax

SELECT expression1, expression2,... expression_n
FROM tables
[WHERE conditions]

UNION / UNION ALL

SELECT expression1, expression2,... expression_n
FROM tables
[WHERE conditions];

Example

 SELECT DEPARTMENTID FROM StudentInfo 
UNION
SELECT DeptId FROM DepartmentInfo ORDER BY DEPARTMENTID ASC;

DEPARTMENTID
------------
1
2
3
4
5

UNION ALL operator is used to combine the result sets of 2 or more SELECT statements and it will return all the rows including the duplicates.

Example

SELECT DEPARTMENTID FROM StudentInfo UNION ALL SELECT DeptId FROM DepartmentInfo ORDER BY DEPARTMENTID ASC;
DEPARTMENTID
------------
1
1
2
2
2
2
3
3
4
4
5

With this, we have covered the most basic commands that you might have to use when working with SQLite. Moving forward with this SQLite tutorial, let’s check out the join statement in SQLite.

Joins in SQLite

In SQLite, Joins are used to combine records from two or more tables in a database and get records based on our requirements. Different type of JOINS available in SQLite are:

• Inner Join –  INNER JOIN is used to combine and return only matching records from multiples tables based on the conditions defined in SQLite statements.
• Outer Join  – SQLite Outer Join will select matching rows from multiple tables the same as Inner Join and some other rows outside of the relationship. In simple terms, we can say SQLite OUTER JOIN is an addition of INNER JOIN. Generally, we have three types of Outer Joins in SQL standard those are LEFT, RIGHT and FULL Outer Joins but SQLite supports only LEFT OUTER JOIN.
• Cross Join – It is used to get the Cartesian product of rows by matching each row of the first table with every row of the second table.
• Self Join – It is used to join the same table with itself. To use Self Join we need to create different alias names for the same table to perform operations based on our requirements.

The concept is similar to that of other relational database systems like SQL. So, to know more you can refer to this article on SQL Joins.

With this, we have covered the basic SQLite commands. Advanced concepts are not covered here. So, stay tuned for another article on advanced SQLite concepts. Even with all the good features that SQLite has to offer, it has certain disadvantages as well.

SQLite Tutorial: Disadvantages of SQLite

Listed below are the demerits of using SQLite:

• It does not work well in client/ server Architecture.
• An SQLite database size is limited to 2GB in most cases.
• SQLite has not Implemented RIGHT OUTER JOIN and FULL OUTER JOIN. With SQLite, we can only implement LEFT OUTER JOIN.
• Views in SQLite are read-only. We can’t use DML statements (Insert, Update, and Delete) with Views.
• We cannot use GRANT and REVOKE statements with SQLite.

With this, we come to an end to this SQLite Tutorial.

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this SQLite Tutorial and I will get back to you.