SQLite Tutorial

SQLite is an open-source, self-contained, zero-configuration, and stand-alone transaction relational database engine that is designed to be integrated into an application. This tutorial will get you up and running with SQLite quickly,  and get you acquainted with SQLite programming.

SQLite Tutorial - Table of Contents

• What is SQLite?
• Features of SQLite
• Who uses SQLite?
• How To Download & Install SQLite Tools?
• SQLite Commands
• SQLite Syntax
• SQLite Datatypes
• SQLite Operators
• SQLite Expressions
• SQL vs SQLite
• SQLite Advantages and Disadvantages

What is SQLite?

SQLite is an embedded database management system i.e., based on relational databases. It's a self-contained transactional SQL database engine that's serverless and requires no configuration. SQLite can be used for both commercial and personal purposes. To put it another way, "SQLite is an open-source, zero-configuration, stand-alone, transaction relational database engine designed to be embedded within an application."

SQLite is distinguished from other SQL databases by the lack of a separate server process. It is capable of reading and writing to conventional disc files. A complete SQL database, comprising many tables, indices, triggers, and views, is included in a single disc file.

If you are looking forward to enhancing your career as an Oracle PL SQL Developer? Check out the "Oracle PL SQL Training" and get certified today

Features of SQLite

Self-contained, serverless, zero-configuration, and transactional are some of SQLite's notable features.

1. Serverless: The majority of SQL database engines are run as a separate server process. Interprocess communication (usually TCP/IP) is used by programmes that want to access the database to send requests to the server and get responses. This isn't how SQLite works. The process that needs to access the database uses SQLite to read and write straight from the database files on the disc. There is no need for a server to act as an intermediary.

Being serverless has both benefits and drawbacks. The key benefit is that there is no need to install, establish, configure, initialize, administer, or debug a separate server process. SQLite is a "zero-configuration" database engine for this reason. SQLite-based programmes do not require any administrative assistance to set up the database engine before they can be run. An SQLite database can be used by any programme that can access the disc.

A database engine that uses a server, on the other hand, can provide stronger protection against flaws in the client application since stray pointers in the client cannot destroy memory on the server. Furthermore, because a server is a single permanent process, it can more precisely restrict database access, allowing for finer grain locking and improved concurrency.

The majority of SQL database engines are client/server. SQLite is the only serverless database that this author is aware of that permits many programs to access the same database at the same time.

2. Self-Contained: SQLite is self-contained, requiring only the most basic operating system and library support. Embedded gadgets like iPhones, Android phones, gaming consoles, and portable media players can now use SQLite.

In ANSI-C, SQLite was created. The source code for sqlite3.c and its header file sqlite3.h is available in a large sqlite3.c and sqlite3.h file. Simply drop these files into your project and compile them with your code if you want to build an SQLite-based application.

3. Zero-configuration: Before SQLite can be used, it does not need to be "installed." There is no such thing as a "setup." It is not necessary to start, stop, or configure any server processes. An administrator does not need to establish a new database instance or provide users access permissions. There are no configuration files used by SQLite. To notify the system that SQLite is running, nothing needs to be done. After a system crash or a power outage, no action is necessary to recover. Nothing needs to be fixed.

SQLite is a simple and effective database management system. Once you get them up and running, other, more well-known database engines perform admirably. However, setting up and configuring the system for the first time might be daunting.

4. Transactional: A transaction is a logical unit of work executed against a database. Transactions are logically ordered units or series of work completed by a user or dynamically by a database application.

A transaction is when one or more database updates are propagated. When you create, update, or delete a record from a table, you are completing a transaction on the table. To preserve data integrity and resolve database issues, it's critical to keep track of transactions.

All updates and queries in a transactional database seem to be Atomic, Consistent, Isolated, and Durable (ACID). Even though the transaction is stopped by a programme crash, an operating system crash, or a power outage, SQLite supports serializable transactions that are atomic, consistent, isolated, and persistent.

We repeat and build on the previous sentence for emphasis: Even if the act of writing the alteration to the disc is halted, all modifications in SQLite occur either completely or not at all during a single transaction.

• software failure
•  operating system failure
•  power outage

The claim in the preceding paragraph is thoroughly tested in the SQLite regression test suite, which uses a specialized test harness to simulate the impact of operating system crashes and power outages on a database file.

Who uses SQLite?

• Google: SQLite is used by Google in their Mac Desktop, Google Gears, the Android mobile operating system, and the Chrome Web Browser.
• Dropbox: Dropbox's archiving and syncing service employs SQLite as its primary data store on the client-side.
• Mozilla: Mozilla's Firefox, Web Browser, and Thunderbird Email Reader all employ SQLite as the primary meta-data storage format.
• McAfee: McAfee's antivirus programmes use SQLite.
  
• Flame: Flame is a malware surveillance software that employs SQLite.
  
• Microsoft: Based on traffic on the SQLite mailing list, it appears that at least one Microsoft group is utilizing SQLite in the creation of game software.
  
• Skype: In the Skype client for Mac OS X and Windows, SQLite is used.
  
• Adobe: For their Photoshop Lightroom package, Adobe employs SQLite as the application file format.
  
• Airbus: SQLite is used in Airbus' flight software.
  
• Apple: SQLite is used by Apple in a variety of Mac OS X applications, including Apple Mail, Safari, and Aperture. SQLite is used by Apple in the iPhone, iPod touch, and iTunes applications.
  
• Intuit: SQLite appears to be used by Intuit in QuickBooks and TurboTax.
  
• PHP: SQLite2 and SQLite3 are incorporated into the PHP programming language.
  
• Python: Since Python 2.5, SQLite has been included with the Python programming language.
  
• REALbasic: REALbasic comes with an upgraded version of SQLite that supports AES encryption as part of the REALbasic programming environment.

How To Download & Install SQLite Tools?

Download SQLite Tools

To download SQLite, go to the official SQLite website's download page.

1. To begin, go to https://www.sqlite.org.
2. Next, go to https://www.sqlite.org/download.html and download the software.

SQLite comes with a number of tools for working on a variety of systems, including Windows, Linux, and Mac. To download, you must first choose the right version.

To work with SQLite on Windows, for example, you'll need to download the command-line shell software, as illustrated in the screenshot below.

The downloaded file is in ZIP format and is relatively small in size.

Run SQLite Tools

SQLite is easy to set up and use.

1. To begin, make a new folder on your hard drive, such as C:\sqlite.
2. Second, copy the contents of the preceding section's file to the C:\sqlite folder. As seen below, the C:\sqlite folder should contain three programmes:

 

 

 

To begin, open a command prompt and navigate to the C:sqlite directory.

C:\cd c:\sqlite
C:\sqlite>

Second, type sqlite3 and hit enter; the following output should appear:

C:\sqlite>sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent 

database.
sqlite>

Next, at the sqlite> prompt, execute the. help command to get a list of all sqlite3 commands.

sqlite> .help
.archive ...           Manage SQL archives: ".archive --help" for details
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.cd DIRECTORY          Change the working directory to DIRECTORY
...

Fourth, you must use the. quit command to terminate sqlite>:

sqlite> .quit

c:\sqlite>

Install the SQLite Graphical User Interface (GUI) Tool

However, you might wish to use an intuitive GUI tool to interact with SQLite databases on occasion.

There are a variety of graphical interfaces for managing SQLite databases, ranging from freeware to commercial licensing.

SQLiteStudio

SQLiteStudio is a free graphical user interface (GUI) application for maintaining SQLite databases. It's open-source, portable, user-friendly, and cross-platform. The SQLite tool also includes several of the most significant SQLite database functionalities, such as importing and exporting data in a variety of formats, including CSV, XML, and JSON.

The SQLiteStudio installer or portable version can be downloaded from the download page. After that, extract (or install) the download file to a folder, such as C:sqlitegui, and run it.

The following diagram depicts how to start SQLiteStudio:

SQLite Commands

Commands in SQLite are identical to SQL commands. SQLite instructions are divided into three categories:

1. Data Definition Language (DDL) is a programming language that allows you to define data.

2. Data Manipulation Language (DML) is a programming language for manipulating data.

3. Data Query Language (DQL) is a programming language that allows you to ask questions about data.

• • Data Definition Language: This group contains three commands:
    1. CREATE: This command creates a table, a view of a table, or another database object.
    2. ALTER: This command is used to change an existing database object, such as a table.
    3. DROP: The DROP command deletes a complete table, a view of a table, or another database object.
  • Data Manipulation language: In the data manipulation language group, there are three commands:
    1. INSERT: This command creates a new record.
    2. UPDATE: It's used to make changes to the records.
    3. DELETE: This command is used to remove records from a database.
  • Data Query Language
    1. SELECT: This command retrieves specific records from one or more tables

1. SQLite dot Command: SQLite dot Command is a command that allows you to work with SQLite

A collection of SQLite dot commands is shown below. A semicolon is not used to end these commands (;)

2. .help Command

Use the ".help" command to see a list of dot commands at any moment.

Sqlite> .help  

The following is a list of significant SQLite dot commands. In the table below, you'll find a list of commands with descriptions:

3. .show Command

To check the default settings of your SQLite command prompt, use the.show command.

4. Special Dot Commands

To format your output, you'll need to utilize a couple of dot commands. The following are the instructions:

• .header on
• .mode column
• .timer on.

SQLite Syntax

Syntax is a set of principles that SQLite adheres to. The basic SQLite syntax is listed in this chapter. Sensitivity to individual circumstances.

Case Sensitivity: The crucial thing to remember is that SQLite does not care about the case, so the words GLOB and glob in SQLite statements have the same meaning.

Comments: 

• Comments are often used to make your SQLite code more readable.
• There is no way to nest comments.
• Two consecutive "-" characters start a comment.
• It can also exist with "/*" and extend all the way to the following "*/" character pair.

SQLite Statements: All SQLite statements begin with SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, and other keywords. A semicolon will be used to terminate the statement (;).

SQLite ANALYZE Statement

Syntax:

ANALYZE;  
or  
ANALYZE database_name;  
or  
ANALYZE database_name.table_name;

SQLite AND/OR Clause

Syntax:

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION-1 {AND|OR} CONDITION-2;

SQLite ALTER TABLE Statement

Syntax:

ALTER TABLE table_name ADD COLUMN column_def...; 

SQLite ALTER TABLE Statement (Rename)

Syntax:

ALTER TABLE table_name RENAME TO new_table_name;

SQLite ATTACH DATABASE Statement

Syntax:

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

SQLite BEGIN TRANSACTION Statement

Syntax:

BEGIN;  
or  
BEGIN EXCLUSIVE TRANSACTION;  

SQLite BETWEEN Clause

Syntax:

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name BETWEEN val-1 AND val-2;  
SQLite COMMIT Statement:  
COMMIT;  

SQLite CREATE INDEX Statement

Syntax:

CREATE INDEX index_name  
ON table_name ( column_name COLLATE NOCASE );  

SQLite CREATE UNIQUE INDEX Statement

Syntax:

CREATE UNIQUE INDEX index_name  
ON table_name ( column1, column2,...columnN);

SQLite CREATE TABLE Statement

Syntax:

CREATE TABLE table_name(  
   column1 datatype,  
   column2 datatype,  
   column3 datatype,  
   .....  
   columnN datatype,  
   PRIMARY KEY( one or more columns ));

SQLite CREATE TRIGGER Statement

Syntax:

CREATE TRIGGER database_name.trigger_name   
BEFORE INSERT ON table_name FOR EACH ROW  
BEGIN   
   stmt1;   
   stmt2;  
   ....  
END; 

SQLite CREATE VIEW Statement

Syntax:

CREATE VIEW database_name.view_name  AS  
SELECT statement....;

SQLite CREATE VIRTUAL TABLE Statement

Syntax:

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );  
or  
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

SQLite COMMIT TRANSACTION Statement

Syntax:

COMMIT;

 SQLite COUNT Clause

Syntax:

SELECT COUNT(column_name)  
FROM   table_name  
WHERE  CONDITION;

SQLite DELETE Statement

Syntax:

DELETE FROM table_name  
WHERE  {CONDITION};

SQLite DETACH DATABASE Statement

Syntax:

DETACH DATABASE 'Alias-Name';  

SQLite DISTINCT Clause

Syntax:

SELECT DISTINCT column1, column2....columnN  
FROM   table_name;

SQLite DROP INDEX Statement

Syntax:

DROP INDEX database_name.index_name; 

SQLite DROP TABLE Statement

Syntax:

DROP TABLE database_name.table_name;  

SQLite DROP VIEW Statement

Syntax:

DROP INDEX database_name.view_name;  

SQLite DROP TRIGGER Statement

Syntax:

DROP INDEX database_name.trigger_name;  

SQLite EXISTS Clause

Syntax:

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name EXISTS (SELECT * FROM   table_name );  

SQLite EXPLAIN Statement

Syntax:

EXPLAIN INSERT statement...;  
or   
EXPLAIN QUERY PLAN SELECT statement...;  

SQLite GLOB Clause

Syntax:

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name GLOB { PATTERN };  

SQLite GROUP BY Clause

Syntax:

SELECT SUM(column_name)  
FROM   table_name  
WHERE  CONDITION  
GROUP BY column_name;  

SQLite HAVING Clause

Syntax:

SELECT SUM(column_name)  
FROM   table_name  
WHERE  CONDITION  
GROUP BY column_name  
HAVING (arithmetic function condition);  

SQLite INSERT INTO Statement

Syntax:

INSERT INTO table_name( column1, column2....columnN)  
VALUES ( value1, value2....valueN);  

SQLite IN Clause

Syntax:

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name IN (val-1, val-2,...val-N);  

SQLite Like Clause

Syntax:

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name LIKE { PATTERN };

SQLite NOT IN Clause

Syntax:

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name NOT IN (val-1, val-2,...val-N);  

SQLite ORDER BY Clause

Syntax:

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION  
ORDER BY column_name {ASC|DESC};

SQLite PRAGMA Statement

Syntax:

PRAGMA pragma_name;  

//For example:
PRAGMA page_size;  
PRAGMA cache_size = 1024;  
PRAGMA table_info(table_name);

SQLite RELEASE SAVEPOINT Statement

Syntax:

RELEASE savepoint_name;  

SQLite REINDEX Statement

Syntax:

REINDEX collation_name;  
REINDEX database_name.index_name;  
REINDEX database_name.table_name;  

SQLite ROLLBACK Statement

Syntax:

ROLLBACK;  
or  
ROLLBACK TO SAVEPOINT savepoint_name;  

SQLite SAVEPOINT Statement

Syntax:

SAVEPOINT savepoint_name;  

SQLite SELECT Statement

Syntax:

SELECT column1, column2....columnN  
FROM   table_name;  

SQLite UPDATE Statement

Syntax:

UPDATE table_name  
SET column1 = value1, column2 = value2....columnN=valueN  
[ WHERE  CONDITION ];  

SQLite VACUUM Statement

Syntax:

VACUUM;  
SQLite WHERE Clause:  
SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION; 

SQLite Datatypes

Any object's data type is specified using SQLite data types. SQLite assigns a data type to each column, variable, and expression. When making a table, several data kinds are used. The dynamic type system in SQLite is more general. A value's data type is associated with the value itself, not with its container, in SQLite.

Types of SQLite Data Types

1. SQLite Storage Classes

The following storage classes apply to the stored values in an SQLite database:

Storage Class	Description
NULL	It indicates a null value.
INTEGER	It indicates that the value is a signed integer that is stored in 1, 2, 3, 4, 6, or 8 bytes, depending on its magnitude.
REAL	It indicates that the value is a floating point value recorded as an IEEE floating point number of 8 bytes.
text	It indicates that the value is a text string that will be stored using database encoding (utf-8, utf-16be or utf-16le)
BLOB	It signifies that the value is a blob of data that has been saved in the same format as it was entered.

2. SQLite Affinity Types

Column type affinity is supported by SQLite. Each column still can store any form of data, but its affinity is the recommended storage class for that column.

In an SQLite3 database, the below type affinity is utilised to assign.

Affinity Types	Description
TEXT	All data with the storage classes NULL, TEXT, or BLOB is stored in this column.
NUMERIC	All five storage classes may be used in this column.
INTEGER	With the exception of a cast expression, it works similarly to a numeric affinity column.
REAL	It functions similarly to a numeric affinity column, with the exception that it converts integer values to floating-point.
NONE	A column with affinity NONE does not encourage one storage type over another and does not persuade data to go from one to the other.

3. SQLite Affinity and Type Names

The names of several data types that can be utilized when building SQLite tables are listed below.

Corresponding Affinity	Data Types
INTEGER	INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8
TEXT	CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB
NONE	There is no data type specified for the BLOB.
REAL	REAL DOUBLE-DOUBLE PRECISION FLOAT
NUMERIC	NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME

4. Date and Time Data Type

Dates and timings are not stored separately in SQLite. Dates and timings can be stored as TEXT, REAL, or INTEGER values, though.

Storage Class	Date Format
TEXT	It defines a date in the format "yyyy-mm-dd hh:mm:ss
REAL	It specifies the number of days since noon on November 24, 4714 B.C. in Greenwich.
INTEGER	It indicates how many seconds have passed since 1970-01-01 00:00:00 UTC.

 

 

 

 

 

 

5. Boolean Data Type

A separate Boolean storage class isn't available in SQLite. Integers 0 (false) and 1 (true) are used to store Boolean values (true).

Learn Top Oracle PL SQL Interview Questions and Answers that help you grab high-paying jobs

SQLite Operators

SQLite operators are restricted words or characters that are utilized in SQLite queries when the WHERE clause is used to conduct operations such as comparisons and arithmetic.

In SQLite statements, operators can be used to describe conditions and as conjunctions for multiple conditions.

SQLite has four different types of operators:

1. Arithmetic operators
2. Comparison operators
3. Logical operators
4. Bitwise operators.

1. SQLite Arithmetic Operators

The various arithmetic operators in SQLite are listed in the table below. There have two variables "a" and "b" in this table, each with a value of 50 and 100.

Operator	Description	Example
+	The value of both sides of the operator are added with the addition operator.	a+b=150
-	The right-hand operand is subtracted from the left-hand operand using the subtraction operator.	a-b=-50
*	The multiplication operator multiplies both sides' values.	a*b = 5000
/	The division operator divides the operands on the left by the operands on the right.	a/b = 0.5
%	The modulus operator returns the remainder after dividing the left and right-hand operands.	b/a = 0

 

 

 

 

 

 

 

2. SQLite Comparison Operator

The comparison operators available in SQLite are listed in the table below. We have two variables "a" and "b" in this table, each with a value of 50 and 100.

Operator	Description	Example
==	It's used to see if the values of two operands are equal, and if they are, then the condition is true.	(a==b)is not true
=	It's used to see if the values of two operands are equal, and if they are, then the condition is true.	(a=b) is not true
!=	It's used to see if the values of two operands are equivalent; if they aren't, the condition is true.	(a!=b) is true
<>	It's used to see if the values of two operands are equivalent; if they aren't, the condition is true.	(a<>b) is true
>	It's used to see if the left operand's value is greater than the right operand's value, and if it is, the condition is true.	(a>b) is not true
<	It's used to see if the left operand's value is less than the right operand's value, and if that's the case, the condition is true.	(a<b) is true
>=	It's used to see if the left operand's value is larger than or equal to the right operand's value, and if it is, then the condition is true.	(a>=b)is not true
<=	It's used to see if the left operand's value is less than or equal to the right operand's value, and if it is, then the condition is true.	(a<=b) is true
!<	It's used to see if the left operand's value is larger than or equal to the right operand's value, and if it is, the condition is true.	(a!<b) is false
!>	It's used to see if the left operand's value is less than the right operand's value; if it is, the condition is true.	(a!>b) is true

 

 

 

 

 

 

 

 

 

 

 

 

3. SQLite Logical Operator

The logical operators in SQLite are listed below:

Operator	Description
AND	In the WHERE clause of a SQL query, the AND operator enables for many conditions to appear.
BETWEEN	Given the minimum and maximum values, the BETWEEN operator is used to find values that are within a range of values.
EXISTS	The EXISTS operator is used to look for a row in a table that meets a set of conditions.
IN	It's the inverse of the IN operator, which compares a value to a list of literal values.
NOT IN	When a value is compared to a list of literal values that has been supplied, the IN operator is used.
LIKE	When employing wildcard operators to compare values, the LIKE operator is employed.
GLOB	When utilizing wildcard operators to compare values, the GLOB operator is employed. In addition, unlike like, glob is case sensitive.
NOT	The NOT operator flips the meaning of the logical operator it's attached to. EXISTS, NOT BETWEEN, NOT IN, and so on. These are referred to as negate operators.
OR	The where clause of a SQL query uses the OR operator to combine numerous conditions.
IS NULL	When comparing a value to a null value, the NULL operator is used.
IS	The IS operator functions similarly to the = operator.
IS NOT	The IS NOT operator functions similarly to the!= operator.
||	This operator is used to combine two strings and create a new one.
UNIQUE	The UNIQUE operator checks each row of a table for uniqueness (no duplicates).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4. SQLite Bitwise Operators

Bitwise operators in SQLite deal with bits and execute bit-by-bit operations.

The truth table for Binary AND (&) and Binary OR (|) can be found here:

p	q	p&q	p|q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

 

 

 

 

 

 

 

Let's say "a" and "b" have values of 60 and 13, respectively. As a result, a and b have binary values of:

a= 0011 1100

b= 0000 1101

a&b = 0000 1100

a|b = 0011 1101

~a = 1100 0011

Operator	Description	Example
&	If a bit exists in both operands, the binary AND operator adds it to the result.	(a&b) will give 12 which is 0000 1100
/	If a bit exists in both operands, the Binary OR Operator replicates it.	(a/b will give 61 which is 0011 1101
~	The Binary Ones Complement Operator is a unary operation that 'flips' bits.	(~a) will give -61 which is 1100 0011 is 2’s complement form due to a signed binary number
<<	Left Shift Operator is a binary operation. The value of the left operand is shifted to the right by the number of bits given by the right operand.	a<< 2 will give 240 which is 1111 0000
>>	Right-shifting binary operator The value of the left operand is shifted right by the right operand's number of bits.	a >> 2 will give 15 which is 0000 1111

 

 

 

 

 

 

 

 

SQLite Expressions

SQLite Expressions are a set of values, operators, and SQL functions. To evaluate a value, these expressions are used.

SELECT statements are used with SQLite expressions written in query language.

Syntax:

SELECT column1, column2, columnN   
FROM table_name   
WHERE [CONDITION | EXPRESSION];  

SQLite expressions are divided into three categories:

• SQLite Boolean Expression
  
• SQLite Numeric Expressions
  
• SQlite Date Expression
  

1. SQLite Boolean Expression: The data is fetched using SQLite Boolean expressions based on a single value that matches.

Syntax:

SELECT column1, column2, columnN   
FROM table_name   
WHERE SINGLE-VALUE MATCHING EXPRESSION;

Example:

We already have a table named "STUDENT" that has the following information:

Refer to this simple example of SQLite Boolean expression.

SELECT * FROM STUDENT WHERE FEES = 20000; 

Output:

2. SQLite Numeric Expressions: To do any mathematical operations in the query, SQLite Numeric expression is employed.

Syntax:

SELECT numerical_expression as  OPERATION_NAME  
[FROM table_name WHERE CONDITION] ;  

Example1:

SELECT (25 + 15) AS ADDITION;

Output:

There are various built-in functions in numerical expressions, such as average(), sum(), count(), and so on. Aggregate data calculation functions are what these functions are called.

SELECT COUNT(*) AS "RECORDS" FROM STUDENT;

Output:

3. SQlite Date Expression: The current system date and time information are obtained using SQlite Date expressions.

Syntax:

SELECT CURRENT_TIMESTAMP;

Output:

SQL vs SQLite

The fundamental distinction between SQL and SQLite is that SQL stands for Structured Query Language and is a database query language. SQLite is a lightweight database. It might be conceivable to add an extension to the database-accessing computer language.

Differences between SQL and SQLite

SQL	SQLite
SQL stands for Structured Query Language, and it is used to query a Relational Database System. C is the programming language used to create it.	SQLite is an ANSI-C-based, embeddable relational database management system.
SQL is a standard that explains how to establish a relational schema, insert or update data in relationships, start and terminate transactions, and so on.	SQLite is a file-based database. SQLite differs from other SQL databases in that it does not have a separate server process, as do most other SQL databases.
DDL, DML, Embedded SQL, and Dynamic SQL are the main components of SQL.	SQLite contains a lot of SQL functionality, but it's slow and doesn't allow stored procedures.
SQL stands for Structured Query Language, and it is a query language that is used with databases such as MySQL, Oracle, Microsoft SQL Server, IBM DB2, and others. It isn't actually a database.	SQLite is a database that can be carried about with you. To access that database, you'll need an extension of SQLite in whichever programming language you're using. All of the PC and mobile applications are available.
To link to and to provide a variety of functionalities, a traditional SQL database must be run as a service, such as OracleDB.	Such features are not available in the SQLite database system.
SQL is a query language used by SQL databases. It's not a database in and of itself.	SQLite is a SQL database management system in and of itself.

 

 

 

 

 

 

 

 

 

 

SQLite Advantages and Disadvantages

SQLite is a well-known database that has been used effectively with on-disk file formats for a variety of desktop applications, including version control, economic analytical techniques, multimedia cataloging and editing suites, CAD packages, and record-keeping programmes, to mention a few.

Advantages:

1. Lightweight: Lightweight SQLite is a small database that can be used in a variety of devices, including televisions, mobile phones, cameras, and other home electronic devices.
2. Better Performance:
   • The SQLite database allows for quick reading and writing operations. It's over 35% faster than the File system.
   • Rather than reading and storing the entire file in memory, it only loads the data that is required.
   • When you update small sections of a file, it merely overwrites the altered sections.
3. No Installation Needed: SQLite is a simple database engine. It does not require any installation or configuration. Simply install the SQLite libraries on your computer, and you're ready to go.
4. Reliable:
   • It continuously updates your material, ensuring that little or no work is lost in the event of a power outage or crash.
   • Compared to custom-written file I/O codes, SQLite is less prone to problems.
   • Because SQLite queries are smaller than similar procedural codes, the likelihood of problems is low.
5. Portable:
   • SQLite is compatible with both big- and little-endian architectures and all 32-bit and 64-bit operating systems.
   • Multiple processes can share the same application file and read and write while interacting with one another.
   • It works with all programming languages and has no issues with compatibility.
6. Accessible:
   • A large number of third-party solutions are available to access SQLite databases.
   • If the content of a SQLite database is lost, it is more likely to be recovered. Code lasts for a shorter period of time than data.
7. Reduce Cost and Complexity
   • Material is accessed and modified using succinct SQL queries rather than long and error-prone procedural queries, it lowers application costs.
   • By simply adding additional tables and/or columns, SQLite can be readily expanded in future iterations. Backward compatibility is also preserved.

Disadvantages:

• You do not have the option of using stored procedures when there is no ongoing troubleshooting process and the database scale may cause synchronization issues.
• Because of the lightness, there should be certain limitations, one of which is that a big number of concurrent users may cause the system to crash.
• There are no distribution properties or guarantees in SQLite because it is designed for a very specific use case. There isn't much tuning for SQLite because it was designed to be basic.
• SQLite is not suitable for large applications and has a number of drawbacks. One of the causes is that when processing a huge amount of data, query speed can be slow.
• When used with applications that require multiple users to access a database, it is unreliable. SQLite is only capable of handling low-to-medium-traffic websites.

Conclusion

We’ve reached to the end of the blog. We've covered all of the essentials of SQLite in this tutorial. We hope  this blog has helped you gain a better understanding of the subject. If you have any questions, please leave a comment below and we will respond as soon as possible.