The Best SQLite Tutorial You'll Ever Need

SQLite is one of the most popular and easy-to-use relational database systems. It possesses many features over other relational databases. Many big MNCs such as Adobe, use SQLite as the application file format for their Photoshop Lightroom product. Airbus, a European multinational aerospace corporation, uses SQLite in the flight software for the A350 XWB family of aircraft. You will learn various concepts and get hands-on practice in this SQLite tutorial.

In this SQLite Tutorial, you will learn the following topics:

  • What is SQLite?
  • History of SQLite
  • Why use SQLite?
  • When to use SQLite?
  • Installing SQLite on Windows
  • SQLite Commands
  • Limitations of SQLite

What Is SQLite?

SQLite is an embedded, server-less relational database management system. It is an in-memory open-source library with zero configuration and does not require any installation. Also, it is very convenient as it’s less than 500kb in size, which is significantly lesser than other database management systems.

Sqlite

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

History of SQLite

2000

D. Richard Hipp designed SQLite to fulfill the purpose of ‘no administration required’ for operating a program.

2000

In August, SQLite 1.0 was released with the GNU database manager.

2011



Hipp announced to add the UNQl interface to the SQLite database to develop UNQLite (Document-oriented database).

Why Use SQLite?

  • SQLite is an open-source software. The software does not require any license after installation.
  • SQLite is serverless as it doesn't need a different server process or system to operate.
  • SQLite facilitates you to work on multiple databases on the same session simultaneously, thus making it flexible.
  • SQLite is a cross-platform DBMS that can run on all platforms, including macOS, Windows, etc. 
  • SQLite doesn't require any configuration. It needs no setup or administration.

When to Use SQLite?

  • SQLite is used to develop embedded software for devices like televisions, cell phones, cameras, etc.
  • It can manage low to medium-traffic HTTP requests.
  • SQLite can change files into smaller size archives with lesser metadata.
  • SQLite is used as a temporary dataset to get processed with some data within an application.
  • Beginners use SQLite for learning and training purposes, as it requires no installation and configuration.

Installing SQLite on Windows

Now, you will see how to download and install SQLite on Windows in this SQLite tutorial.

Follow these steps:

Step1: Go to the official SQLite website and download precompiled binaries from the Windows section.

Step2: Download the file (sqlite-tools-win32-x86-3270200.zip) as shown in the figure below and extract these files in a folder of your choice.

SQLiteTutorial_1

Step3: Create a folder named sqlite in the C directory and copy the sqlite3.exe file in the sqlite folder.

SQLiteTutorial_2

Step4: Now, open the Command Prompt and navigate to the C:\sqlite folder. Type sqlite3 and press enter. You will see the following output:

SQLiteTutorial_3

Now, you can write various SQLite commands in the Command Prompt.

SQLite Commands

Now, you will look at some basic commands in this SQLite tutorial.

CREATE TABLE STATEMENT

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

Syntax:

CREATE TABLE database_name.table_name(  

   col datatype  PRIMARY KEY(one or more columns),  

   col datatype,  

   col datatype,  

   .....  

   column datatype,  

);

You will understand this command better with the help of an example.

SQLiteTutorial_4.

Now, check the created table:

SQLiteTutorial_5.

INSERT STATEMENT

In SQLite, the INSERT INTO statement is used to add new rows of data into a table. After you create the table, this command is used to insert records into the table.

Syntax:

INSERT INTO Table_name (col1, col2,.........,col n)

VALUES (value1, value2, value3,..........,values n);

SQLiteTutorial_6

You can view the output by using the SELECT statement.

A SELECT statement is used to fetch data from a given table.

SQLiteTutorial_7.

UPDATE STATEMENT

In SQLite, the UPDATE query is used to modify the existing records in a table. It is used with the WHERE clause to select specific rows. Otherwise, it will update all the rows.

Syntax:

UPDATE table_name  

SET column1 = value1, column2 = value2...., columnN = valueN  

WHERE [condition]; 

SQLiteTutorial_8.
SQLiteTutorial_9

DELETE STATEMENT

The delete command is used to delete specific records (selected rows) from the table.

Syntax:

DELETE FROM table_name  

WHERE [conditions];

SQLiteTutorial_10
SQLiteTutorial_11.

WHERE STATEMENT

The where clause extracts only those records that fulfill a stated condition.

Syntax:

SELECT column1, column2,.... columnN   

FROM table_name  

WHERE [condition]  

SQLiteTutorial_12

SQLiteTutorial_13.

AND STATEMENT

It is a conjunctive operator which makes multiple comparisons with different operators in the same SQLite statement.

Syntax:

SELECT column1, column2, columnN   

FROM table_name  

WHERE [condition1] AND [condition2]...AND [conditionN];   

SQLiteTutorial_14

SQLiteTutorial_15

GROUP BY STATEMENT

The GROUP BY statement groups rows that have the same values into summary rows.

Syntax:

SELECT column-list  

FROM table_name  

WHERE [ conditions ]  

GROUP BY column1, column2....columnN  

ORDER BY column1, column2....columnN   

SQLiteTutorial_16.

SQLiteTutorial_17.

HAVING STATEMENT

The having clause is mostly used with aggregated functions and it is also used to specify conditions for a particular output.

Syntax:

SELECT column1, column2  

FROM table1, table2  

WHERE [ conditions ]  

GROUP BY column1, column2  

HAVING [ conditions ]  

ORDER BY column1, column2  

SQLiteTutorial_18

SQLiteTutorial_19

ORDER BY STATEMENT

The ORDER BY clause sorts the result in ascending or descending order based on one or more columns.

Syntax:

SELECT column-list   

FROM table_name   

[WHERE condition]   

[ORDER BY column1, column2, .. columnN] [ASC | DESC]; 

SQLiteTutorial_20

SQLiteTutorial_21.

MIN Function

The MIN function is used to fetch the smallest value of an expression.

Syntax:

SELECT MIN(aggregate_expression)  

FROM tables  

[WHERE conditions];

SQLiteTutorial_22

MAX Function

The MAX function is used to fetch the largest value of an expression.

Syntax:

SELECT MAX(aggregate_expression)  

FROM tables  

[WHERE conditions];  

SQLiteTutorial_23

AVG Function

The AVG function yields the average value of a numeric column.

Syntax:

SELECT AVG(aggregate_expression)  

FROM tables  

[WHERE conditions];  

SQLiteTutorial_24

SUM Function

The SUM function is used to yield the total sum of a numeric column.

Syntax:

SELECT SUM(aggregate_expression)  

FROM tables  

[WHERE conditions];  

SQLiteTutorial_25

SQLite COUNT Function

The COUNT function is used to fetch the number of rows that fulfill a specified expression.

Syntax:

SELECT COUNT(aggregate_expression)  

FROM tables  

[WHERE conditions];  

SQLiteTutorial_26.

LIKE COMMAND

The SQLite LIKE operator is used to search for a specified pattern using wildcards.

There are two wildcards used with LIKE Operator:

  • The percent sign (%) represents zero, one, or multiple characters
  • The underscore sign (_) represents one, a single character

Syntax:

SELECT FROM table_name  

WHERE column LIKE '%XXXX%';

SQLiteTutorial_27

SQLiteTutorial_28 

DISTINCT STATEMENT

The DISTINCT clause with the SELECT statement is used to return only distinct records.

It is used when you have many duplicate values in the table.

Syntax:

SELECT DISTINCT column1, column2,.....columnN   

FROM table_name  

WHERE [condition]

SQLiteTutorial_29.   

The above query returns unique address values from the table.

SQLiteTutorial_30

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

BETWEEN STATEMENT

The BETWEEN operator selects values within a given range. It has both the starting and the ending values included.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

SQLiteTutorial_31

The above query returns the students whose age is between 20 years and 30 years.

SQLiteTutorial_32

CASE STATEMENT

The SQLite CASE expression evaluates through conditions and fetches a value when it fulfills the first condition. As it fulfills the first condition, it will stop reading the condition and return the result. If no conditions are true, it will return the value in the ELSE clause.

The CASE statement is similar to the IF-THEN-ELSE statement.

Syntax:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    WHEN conditionN THEN resultN

    ELSE result

END;

SQLiteTutorial_33

In the example above, you return the value “teenagers” for students whose age is less than 20. For the students whose age is greater than 20, you must assign a value “not teenagers.”

SQLiteTutorial_34

Limitations of SQLite

  • SQLite only supports LEFT OUTER JOIN. It neither supports RIGHT nor FULL OUTER JOIN.
  • SQLite only allows normal file access permissions. It does not support GRANT and REVOKE commands as SQLite reads and writes to the disk files.
  • In SQLite, using the ALTER table statement, you can only add a column or rename a table.
  • SQLite doesn't support FOR EACH STATEMENT triggers. It only supports FOR EACH ROW triggers.
  • In SQLite, VIEWs are read-only, and you can't write INSERT, DELETE, or UPDATE statements into the view.
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

Conclusion:

In this SQLite Tutorial, you learned SQLite from scratch and understood all the essential topics of SQLite. SQLite, being in the public domain, is used by most developers. 

Do you have any questions regarding this article, SQLite Tutorial? If you do, then please put it in the comments section. Our team will help you solve them at the earliest!

To get certified in SQL and start your career in it, check this course link: SQL Training

Happy learning!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.