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