What is SQLite? Everything You Need to Know

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.

History of SQLite

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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.

How to Install 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.

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

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

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

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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.

Now, check the created table:

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);

You can view the output by using the SELECT statement.

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

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]; 

DELETE STATEMENT

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

Syntax:

DELETE FROM table_name  

WHERE [conditions];

WHERE STATEMENT

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

Syntax:

SELECT column1, column2,.... columnN   

FROM table_name  

WHERE [condition]  

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];   

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   

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  

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]; 

MIN Function

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

Syntax:

SELECT MIN(aggregate_expression)  

FROM tables  

[WHERE conditions];

MAX Function

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

Syntax:

SELECT MAX(aggregate_expression)  

FROM tables  

[WHERE conditions];  

AVG Function

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

Syntax:

SELECT AVG(aggregate_expression)  

FROM tables  

[WHERE conditions];  

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];  

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];  

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%';

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]

The above query returns unique address values from the table.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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;

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

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;

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.”

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. 

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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 Certification Course

Happy learning!