A Brief Introduction about SQLite

 

What is SQLITE?

SQLITE is one of the most popular and easy to use relational database management system .SQLITE is an embedded, server less Relational Database Management System. It is a in memory Zero Configuration and Does not Require any Installation. Also it is a lightweight as it is less than 500 kb in size. SQLite is the most widely deployed database in the world.

History of SQLITE:

• SQLite was designed originally on August 2000. It is named SQLite because it is very light weight (less than 500Kb size) unlike other database management systems like SQL Server or Oracle. SQLite was designed by D. Richard Hipp for the purpose of no administration required for operating a program..
• Version 1.0 of SQLite was released, with storage based on gdbm (GNU Database Manager). In September 2001.
• SQLite 2.0 replaced gdbm with a custom B-tree implementation, adding transaction capability. In June 2004.
• In 2011, Hipp announced to add the UNQl interface to the SQLite database to develop UNQLite (Document-oriented database)

Why SQLITE?

• SQLITE is a open Source Software The Software Does not Require any licence  After Installation.
• SQLITE is a server less software application.(does not require a server to operate).
• SQLITE comes with zero-configuration, which means no setup or administration needed.
• SQLITE facilitates you to work on multiple databases on the same session simultaneously.
• SQLITE is a cross platform DBMS that can run on all platforms. (UNIX ,Linux, Mac OS-X, Android, ios and Windows).
• SQLITE is very small and light weight, less than 400kib fully configured or less than 250kib with optional features omitted.

 

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.

 Well-Known Users of SQLite

SQLite is used by literally millions of applications with literally billions and billions of deployments. SQLite is the most widely deployed database engine in the world today.

1. Adobe uses SQLite as the application file format for their Photoshop Lightroom product. SQLite is also a standard part of the Adobe Integrated Runtime (AIR). It is reported that Acrobat Reader also uses SQLite.
2. Apple uses SQLite in many of the native applications running on Mac OS-X desktops and servers and on iOS devices such as iPhones and iPods. SQLite is also used in iTunes, even on non-Apple hardware.
3. Bosch uses SQLite in the multimedia systems install on GM, Nissan, and Suzuki automobiles
4. Microsoft uses SQLite as a core component of Windows 10, and in other products.
5. SQLite is the primary meta-data storage format for the Firefox Web Browser and the Thunderbird Email Reader from Mozilla.
6. The increasingly popular Dropbox file archiving and synchronization service is reported to use SQLite as the primary data store on the client side.

 

SQLite Commands:

The standard SQLite commands to interact with relational databases are similar to SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature.

 

1.DDL - Data Definition Language

CREATE - Creates a new table, a view of a table, or other object in database.

ALTER - Modifies an existing database object, such as a table.

 

DROP - Deletes an entire table, a view of a table or other object in the database.

 

2.DML - Data Manipulation Language

INSERT - Creates a record

 

UPDATE - Modifies records

 

DELETE - Deletes records

 

3.DQL - Data Query Language

SELECT - Retrieves certain records from one or more tables

 

Working With SQLITE:

Installing SQLite in Windows:

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

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

 

Creating Database in SQLite:

 

Step:1 -Open the windows command prompt

Step:2 – open the folder where you extracted the sqllite3.exe

Step:3 using the following syntax create database.

              Syntax: sqlite3 DatabaseName.db

 

It will create a stand alone file/library ,You can create multiple databases.

Step:4 using the following syntax you can open the specified databases based on your need.

 

                            Syntax: .open filename

.databases- used for viewing the available databases

.tables    - used to view the tables in the databases.

 

CREATING TABLES:

 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  table_name (  

   Col datatype PRIMARY KEY (one or more columns),  

   Col datatype,   col datatype,  col datatype );

 INSERT STATEMENT:

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

Select statement:

select Statement is used to view the data inside the selected table.

Select * from table name = it will fetch all the data inside the table.

Select columname from table name; = will fetch particular column data.

 

 

UPDATE STATEMENT

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 used to get the dats of rows which satisfies multiple conditions

Syntax:

SELECT column1, column2, columnN   

FROM table_name  

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

 

ORDER BY:

It is used to get the data in ascending or descending order based the data type .

                   SELECT column name FROM TABLE NAME  ORDER BY column name;

                   SELECT column name FROM TABLE NAME  ORDER BY column name desc;

LIMIT:

Used for control no of data to get,

                        SELECT column name FROM table name limit 5;

It will return only 5 rows.

DISTINCT:

It is used to get what are the different Values entered in a specific column,

           SELECT DISTINCT column name from table name;

 

 

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.