SQLite

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that's growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain.

What is SQLite? 

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is the one database, which is zero-configured, that means like other database you do not need to configure it in your system.

SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. The SQLite accesses its storage files directly.

Or,

SQLite is an Open Source Database which is embedded into Android. SQLite is available on every Android device.

SQLite require very less memory  at runtime (approx. 250 KByte).

Enternally SQLite database creates filesystem so this may be slow, Therefore it is recommended to perform database operations inside the AsyncTask class.

The package android.database contains all general classes for working with databases. android.database.sqlite contains the SQLite specific classes.

SQLite is an embedded relational database engine. Its developers call it a self-contained, serverless, zero-configuration and transactional SQL database engine. It is very popular and there are hundreds of millions copies worldwide in use today. SQLite is used in the Solaris 10 and Mac OS operating systems and by iPhone and Skype. The Qt4 library has built-in support for SQLite as well as the Python and PHP languages. Many popular applications use SQLite internally such as Firefox, Google Chrome or Amarok.

SQLite implements most of the SQL-92 standard for SQL. The SQLite engine is not a standalone process. Instead, it is statically or dynamically linked into the application. The SQLite library is small. It can require less than 300 KiB. An SQLite database is a single ordinary disk file that can be located anywhere in the directory hierarchy. It is a cross platform file. It can be used on various operating systems, both 32 and 64 bit architectures. SQLite was written in the C programming language. It has bindings for many languages, including C++, Java, C#, Python, Perl, Ruby, Visual Basic, and Tcl. The source code of SQLite is in public domain.

A relational database is a collection of data organized in tables. There are relations among the tables. The tables are formally described. They consist of rows and columns. SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems. A table is a set of values that is organized using a model of vertical columns and horizontal rows. The columns are identified by their names. A schema of a database system is its structure described in a formal language. It defines the tables, the fields, relationships, views, indexes, procedures, functions, queues, triggers and other elements. A database row represents a single, implicitly structured data item in a table. It is also called a tuple or a record. A column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed. A field is a single item that exists at the intersection between one row and one column. A primary key uniquely identifies each record in the table. A foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. A trigger is a procedural code that is automatically executed in response to certain events on a particular table in a database. A view is a specific look on data in from one or more tables. It can arrange data in some specific order, highlight or hide some data. A view consists of a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables a view does not form part of the physical schema. It is a dynamic, virtual table computed or collated from data in the database. A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back. An SQL result set is a set of rows from a database returned by the SELECT statement. It also contains meta-information about the query such as the column names and the types and sizes of each column. An index is a data structure that improves the speed of data retrieval operations on a database table.

Why SQLite? : 

SQLite does not require a separate server process or system to operate (serverless). 

SQLite comes with zero-configuration, which means no setup or administration needed. 

A complete SQLite database is stored in a single cross-platform disk file. 

SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted. 

SQLite is self-contained, which means no external dependencies. 

SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads. 

SQLite supports most of the query language features found in the SQL92 (SQL2) standard. 

SQLite is written in ANSI-C and provides simple and easy-to-use API. 

SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT). 

History:

2000 -- D. Richard Hipp had designed SQLite for the purpose of no administration required for operating a program. 

2000 -- In August, SQLite 1.0 released with GNU Database Manager.

2011 -- Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database).

SQLite Limitations:

Feature	Description
RIGHT OUTER JOIN	Only LEFT OUTER JOIN is implemented.
FULL OUTER JOIN	Only LEFT OUTER JOIN is implemented.
ALTER TABLE	The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT not supported.
Trigger support	FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
VIEWs	VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.
GRANT and REVOKE	The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

SQLite Commands: 

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

DDL - Data Definition Language:

Command	Description
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.

DML - Data Manipulation Language:

Command	Description
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

DQL - Data Query Language:

SELECT

Retrieves certain records from one or more tables