SQL Essentials Training & Certification
- 5k Enrolled Learners
- Self Paced
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:
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.
SQLite offers many distinct features such as:
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.
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
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.
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.
This section consists of those commands, by which you can deal with your database. The commands are:
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:
sqlite3 StudentDetails.db; sqlite> .databases main: D:sqliteStudentDetails.db;
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.
sqlite> ATTACH DATABASE 'DepartmentDetails.db' AS 'Department'; sqlite> .databases main: D:sqliteStudentDetails.db; Department: D:sqliteDepartmentDetails.db
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.
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.
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.
CREATE TABLE table_name( Column1 column_type [constraints] Column2 column_type [constraints] [.....] );
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
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.
DROP TABLE [ IF EXISTS ] table_name;
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.
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.
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
INSERT INTO StudentInfo ( ID, NAME, AGE, ADDRESS, DEPARTMENTID, PHONE) VALUES (1,'Dean', 20, 'California', 2, '934*******');
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.
INSERT INTO StudentInfo VALUES ( 2, 'SAM', 22, 'Texas', 2, '976*******');
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.
INSERT INTO StudentInfo VALUES (3,'John',23,'Norway',1,'923*******'), (4,'Mitch',22,'Houston',3,'934*******');
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.
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.
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
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*******
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.
SELECT [ALL | DISTINCT] result [FROM table-list] [WHERE expr]
SELECT ID, NAME FROM StudentInfo WHERE AGE < 21;
ID NAME ---------- ---------- 1 Dean
Select NAME FROM StudentInfo WHERE DEPARTMENTID = (SELECT DeptID FROM DepartmentInfo WHERE DeptName = 'Psychology');
//fetches people from department whose id is 2 NAME ---------- Dean SAM
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.
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
UPDATE StudentInfo SET DEPARTMENTID = 4 WHERE ID = '2';
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*******
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.
DELETE FROM DepartmentInfo WHERE DeptName = 'Science';
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.
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.
Before getting started with clauses, here’s the complete syntax of the SELECT statement in SQLite.
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
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.
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.
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.
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.
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.
SELECT expressions FROM tables-list [WHERE conditions] ORDER BY column1, column2,... [ ASC | DESC ];
SELECT ADDRESS, COUNT(ADDRESS) FROM StudentInfo GROUP BY ADDRESS ORDER BY ADDRESS DESC; ADDRESS COUNT(ADDRESS) ---------- -------------- Texas 1 Norway 3 Houston 1 California 1
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.
SELECT ADDRESS, COUNT(ADDRESS) FROM StudentInfo GROUP BY ADDRESS HAVING COUNT(*)>1; ADDRESS COUNT(ADDRESS) ---------- -------------- Norway 3
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.
SELECT expressions FROM tables-list [WHERE conditions] LIMIT number_rows OFFSET offset_value;
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.
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.
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.
SELECT NAME FROM StudentInfo WHERE (AGE = 22 AND ADDRESS = 'Norway') OR ADDRESS = 'Norway'; NAME ---------- John Johny Robin
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.
SELECT * FROM table_name WHERE column_name GLOB 'search-expression'
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*******
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.
SELECT DISTINCT AGE FROM StudentInfo; AGE ---------- 20 23 22
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.
SELECT NAME FROM StudentInfo WHERE ADDRESS IN ('Texas', 'Houston'); NAME ---------- Mitch SAM
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.
SELECT expression1, expression2,... expression_n FROM tables [WHERE conditions] UNION / UNION ALL SELECT expression1, expression2,... expression_n FROM tables [WHERE conditions];
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.
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
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:
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.
Listed below are the demerits of using 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.