SQLite Overview with Installation_pRoCess and self-start examples:SQL

SQLite is embedded relational database management system. It is self-contained, serverless, zero configuration and transactional SQL database engine. SQLite is free to use for any purpose commercial or private. In other words, "SQLite is an open source, zero-configuration, self-contained, stand alone, transaction relational database engine designed to be embedded into an application".

SQLite is different from other SQL databases because unlike most other SQL databases, SQLite does not have a separate server process. It reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. SQLite was designed by D. Richard Hipp for the purpose of no administration required for operating a program in 2000.

SQLite Features

Following is a list of features which makes SQLite popular among other lightweight databases:

SQLite is open-source. License is not required to work with it.
SQLite doesn't require a different server process or system to operate.
It facilitates you to work on multiple databases on the same session on the same time.
SQLite doesn't require configuration. No setup or administration required.
SQLite is a cross-platform DBMS.
The length of the columns is variable and is not fixed. It facilitates you to allocate only the space a field needs.
Provide large number of API's: SQLite provides API for a large range of programming languages.
SQLite is written in ANSI-C and provides simple and easy-to-use API.

SQLite Limitations

There are few unsupported features of SQLite which are listed below:-

Right Outer Join and Full Outer Join is not Supported.
The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported.
FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.
The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

SQLite Installation

SQLite doesn't require configuration. No setup or administration required. Lets see how to install SQLite on different Systems such as Windows, Linux.

Install SQLite on Windows

Step 1: Go to SQLite official website download page http://www.sqlite.org/download.html And download precompiled binaries from Windows section.

Step 2: Download the sqlite-dll and sqlite-shell zip file. Or sqlite-tools-win32-x86-3170000.zip file.

Step 3: Create a folder C:\>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files.

Step 4: Add C:\>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result.

Install SQLite on Linux

Today, almost all the Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine.

$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Follow following Steps

Step 1: Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section.

Step 2: Run the following command :

$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix = /usr/local
$make
$make install

The above command will install SQLite on Linux.

SQLite Commands

SQLite commands are similar to SQL commands. There are three types of SQLite commands:

DDL: Data Definition Language

CREATE: Creates a new table, a view of a table, or other object in database.
ALTER: It is used to modify an existing database object like a table.
DROP: Deletes an entire table, a view of a table or other object in the database.

DML: Data Manipulation Language

INSERT: This command is used to create a record.
UPDATE: It is used to modify the records.
DELETE: It is used to delete records.

DQL: Data Query Language

SELECT: This command is used to retrieve certain records from one or more table.

SQLite Create Table Command

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

Syntax:

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

Example

CREATE TABLE SCTPL(  
   ID int  PRIMARY KEY not null,  
   NAME TEXT,  
   Address TEXT,   
);

You can view the tables created in SQLite using .table command and .schema command is used to view the table details.

SQLite Insert Query

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

There are two types of basic syntaxes for INSERT INTO statement:

Syntax-1:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)    
VALUES (value1, value2, value3,...valueN);

Here, column1, column2, column3,...columnN specifies the name of the columns in the table into which you have to insert data.

You don't need to specify the columns name in the SQlite query if you are adding values to all the columns in the table. But you should make sure that the order of the values is in the same order of the columns in the table.

Then the syntax will be like this:

Syntax-2:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example

Inserting values by first method:

INSERT INTO SCTPL (id, name,address)   
VALUES (101, Suven Consultants,Chembur);

Inserting values by second method:

INSERT INTO SCTPL VALUES (102,Suven Consultants,Thane);

SQLite SELECT Query

In SQLite database, SELECT statement is used to fetch data from a table. When we create a table and insert some data into that, we have to fetch the data whenever we require. That's why select query is used.

Syntax:

SELECT column1, column2, columnN FROM table_name;
OR
SELECT * FROM table_name;

Example

SELECT * FROM SCTPL;

Search This Blog

{Learn-Intern-Certify}©