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:
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.
Learn more about SQL Server and its framework from SQL Course.
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
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.