Sqlite 简明教程

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

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

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

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

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

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
CPU Time: user 0.000000 sys 0.000000

sqlite>.schema sqlite_master

CREATE TABLE sqlite_master (
   type text,
   name text,
   tbl_name text,
   rootpage integer,
   sql text
);

sqlite> .help -- This is a single line comment

$sqlite3 DatabaseName.db

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

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db

sqlite>.quit
$

$sqlite3 testDB.db .dump > testDB.sql

$sqlite3 testDB.db < testDB.sql

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

sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';

sqlite> .database
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db

sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP';
Error: database TEMP is already in use
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database TEMP is already in use

DETACH DATABASE 'Alias-Name';

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db
3    currentDB        /home/sqlite/testDB.db

sqlite> DETACH DATABASE 'currentDB';

sqlite>.databases
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    test             /home/sqlite/testDB.db

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

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

sqlite>.tables
COMPANY     DEPARTMENT

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

DROP TABLE database_name.table_name;

sqlite>.tables
COMPANY       test.COMPANY

sqlite>DROP TABLE COMPANY;
sqlite>

sqlite>.tables
sqlite>

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

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

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

INSERT INTO first_table_name [(column1, column2, ... columnN)]
   SELECT column1, column2, ...columnN
   FROM second_table_name
   [WHERE condition];

SELECT column1, column2, columnN FROM table_name;

SELECT * FROM table_name;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

ID          NAME        SALARY
----------  ----------  ----------
1           Paul        20000.0
2           Allen       15000.0
3           Teddy       20000.0
4           Mark        65000.0
5           David       85000.0
6           Kim         45000.0
7           James       10000.0

sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;

ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

tbl_name
----------
COMPANY

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
)

A&B = 0000 1100

A|B = 0011 1101

~A  = 1100 0011

The Bitwise operators supported by SQLite language are listed in the following table. Assume variable *A* holds 60 and variable *B* holds 13, then −

link:../sqlite/sqlite_bitwise_operators.html[Show Examples]
[%autowidth]
|===

|Operator|Description|Example
|&|Binary AND Operator copies a bit to the result, if it exists in both operands.|(A & B) will give 12 which is 0000 1100
|||Binary OR Operator copies a bit, if it exists in either operand.|(A | B) will give 61 which is 0011 1101
|~|Binary Ones Complement Operator is unary and has the effect of 'flipping' bits.|(~A ) will give -61 which is 1100 0011 in 2's complement form due to a signed binary number
|<<|Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand.|A << 2 will give 240 which is 1111 0000
|>>|Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand.|A >> 2 will give 15 which is 0000 1111
|===


== SQLite - Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.

SQL expressions are like formulas and they are written in query language. You can also use to query the database for a specific set of data.


==== Syntax

Consider the basic syntax of the SELECT statement as follows −

[source]

Following are the different types of SQLite expressions.


=== SQLite - Boolean Expressions

SQLite Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax −

[source]

Consider COMPANY table with the following records −

[source]

Following is a simple examples showing the usage of SQLite Boolean Expressions −

[source]

=== SQLite - Numeric Expression

These expressions are used to perform any mathematical operation in any query. Following is the syntax −

[source]

Here, numerical_expression is used for mathematical expression or any formula. Following is a simple example showing the usage of SQLite Numeric Expressions.

[source]

There are several built-in functions such as *avg(), sum(), count(),* etc., to perform what is known as *aggregate data calculations* against a table or a specific table column.

[source]

=== SQLite - Date Expressions

Date Expressions returns the current system date and time values. These expressions are used in various data manipulations.

[source]

== SQLite - WHERE Clause

SQLite *WHERE* clause is used to specify a condition while fetching the data from one table or multiple tables.

If the given condition is satisfied, means true, then it returns the specific value from the table. You will have to use WHERE clause to filter the records and fetching only necessary records.

The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which will be covered in subsequent chapters.


=== Syntax

Following is the basic syntax of SQLite SELECT statement with WHERE clause.

[source]

=== Example

You can specify a condition using link:../sqlite/sqlite_operators.html[Comparision or Logical Operators] such as >, <, =, LIKE, NOT, etc. Consider COMPANY table with the following records −

[source]

Following is a simple examples showing the usage of SQLite Logical Operators. Following SELECT statement lists down all the records where AGE is greater than or equal to 25 *AND* salary is greater than or equal to 65000.00.

[source]

Following SELECT statement lists down all the records where AGE is greater than or equal to 25 *OR* salary is greater than or equal to 65000.00.

[source]

Following SELECT statement lists down all the records where AGE is not NULL, which means all the records because none of the record has AGE equal to NULL.

[source]

Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki'.

[source]

Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki'.

[source]

Following SELECT statement lists down all the records where AGE value is either 25 or 27.

[source]

Following SELECT statement lists down all the records where AGE value is neither 25 nor 27.

[source]

Following SELECT statement lists down all the records where AGE value is in BETWEEN 25 AND 27.

[source]

Following SELECT statement makes use of SQL sub-query, where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by the sub-query −

[source]

32
25
23
25
27
22
24

----

Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from the outside query is greater than the age in the result returned by the sub-query.

[source]
----
sqlite> SELECT * FROM COMPANY
   WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

----


== SQLite - AND & OR Operators

SQLite *AND* & *OR* operators are used to compile multiple conditions to narrow down the selected data in an SQLite statement. These two operators are called *conjunctive operators*.

These operators provide a means to make multiple comparisons with different operators in the same SQLite statement.


=== The AND Operator

The *AND* operator allows the existence of multiple conditions in a SQLite statement's WHERE clause. While using AND operator, complete condition will be assumed true when all the conditions are true. For example, [condition1] AND [condition2] will be true only when both condition1 and condition2 are true.


==== Syntax

Following is the basic syntax of AND operator with WHERE clause.

[source]
----
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

----

You can combine *N* number of conditions using AND operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE.


==== Example

Consider COMPANY table with the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following SELECT statement lists down all the records where AGE is greater than or equal to 25 *AND* salary is greater than or equal to 65000.00.

[source]
----
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

----


=== The OR Operator

The OR operator is also used to combine multiple conditions in a SQLite statement's WHERE clause. While using OR operator, complete condition will be assumed true when at least any of the conditions is true. For example, [condition1] OR [condition2] will be true if either condition1 or condition2 is true.


==== Syntax

Following is the basic syntax of OR operator with WHERE clause.

[source]
----
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

----

You can combine *N* number of conditions using OR operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE.


==== Example

Consider COMPANY table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following SELECT statement lists down all the records where AGE is greater than or equal to 25 *OR* salary is greater than or equal to 65000.00.

[source]
----
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

----


== SQLite - UPDATE Query

SQLite *UPDATE* Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows, otherwise all the rows would be updated.


=== Syntax

Following is the basic syntax of UPDATE query with WHERE clause.

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

----

You can combine *N* number of conditions using AND or OR operators.


=== Example

Consider COMPANY table with the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example, which will update ADDRESS for a customer whose ID is 6.

[source]
----
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;

----

Now, COMPANY table will have the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          Texas       45000.0
7           James       24          Houston     10000.0

----

If you want to modify all ADDRESS and SALARY column values in COMPANY table, you do not need to use WHERE clause and UPDATE query will be as follows −

[source]
----
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

----

Now, COMPANY table will have the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          Texas       20000.0
2           Allen       25          Texas       20000.0
3           Teddy       23          Texas       20000.0
4           Mark        25          Texas       20000.0
5           David       27          Texas       20000.0
6           Kim         22          Texas       20000.0
7           James       24          Texas       20000.0

----


== SQLite - DELETE Query

SQLite *DELETE* Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete the selected rows, otherwise all the records would be deleted.


=== Syntax

Following is the basic syntax of DELETE query with WHERE clause.

[source]
----
DELETE FROM table_name
WHERE [condition];

----

You can combine *N* number of conditions using AND or OR operators.


=== Example

Consider COMPANY table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example, which will DELETE a customer whose ID is 7.

[source]
----
sqlite> DELETE FROM COMPANY WHERE ID = 7;

----

Now COMPANY table will have the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0

----

If you want to DELETE all the records from COMPANY table, you do not need to use WHERE clause with DELETE query, which will be as follows −

[source]
----
sqlite> DELETE FROM COMPANY;

----

Now, COMPANY table does not have any record as all the records have been deleted by DELETE statement.


== SQLite - LIKE Clause

SQLite *LIKE* operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. There are two wildcards used in conjunction with the LIKE operator −

. The percent sign (%)

. The underscore (_)

The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations.


=== Syntax

Following is the basic syntax of % and _.

[source]
----
SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'

----

You can combine *N* number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.


=== Example

Following table lists a number of examples showing WHERE part having different LIKE clause with '%' and '_' operators.
[%autowidth]
|===

|Sr.No.|Statement & Description
|1|*WHERE SALARY LIKE '200%'*
Finds any values that start with 200

|2|*WHERE SALARY LIKE '%200%'*
Finds any values that have 200 in any position

|3|*WHERE SALARY LIKE '_00%'*
Finds any values that have 00 in the second and third positions

|4|*WHERE SALARY LIKE '2_%_%'*
Finds any values that start with 2 and are at least 3 characters in length

|5|*WHERE SALARY LIKE '%2'*
Finds any values that end with 2

|6|*WHERE SALARY LIKE '_2%3'*
Finds any values that has a 2 in the second position and ends with a 3

|7|*WHERE SALARY LIKE '2___3'*
Finds any values in a five-digit number that starts with 2 and ends with 3

|===

Let us take a real example, consider COMPANY table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example, which will display all the records from COMPANY table where AGE starts with 2.

[source]
----
sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example, which will display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text.

[source]
----
sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

----


== SQLite - GLOB Clause

SQLite *GLOB* operator is used to match only text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the GLOB operator will return true, which is 1. Unlike LIKE operator, GLOB is case sensitive and it follows syntax of UNIX for specifying THE following wildcards.

. The asterisk sign (*)

. The question mark (?)

The asterisk sign (*) represents zero or multiple numbers or characters. The question mark (?) represents a single number or character.


=== Syntax

Following is the basic syntax of *** and *?*.

[source]
----
SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
or
SELECT FROM table_name
WHERE column GLOB 'XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '????'

----

You can combine *N* number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.


=== Example

Following table lists a number of examples showing WHERE part having different LIKE clause with '*' and '?' operators.
[%autowidth]
|===

|Sr.No.|Statement & Description
|1|*WHERE SALARY GLOB '200*'*
Finds any values that start with 200

|2|*WHERE SALARY GLOB '*200*'*
Finds any values that have 200 in any position

|3|*WHERE SALARY GLOB '?00*'*
Finds any values that have 00 in the second and third positions

|4|*WHERE SALARY GLOB '2??'*
Finds any values that start with 2 and are at least 3 characters in length

|5|*WHERE SALARY GLOB '*2'*
Finds any values that end with 2

|6|*WHERE SALARY GLOB '?2*3'*
Finds any values that have a 2 in the second position and end with a 3

|7|*WHERE SALARY GLOB '2???3'*
Finds any values in a five-digit number that start with 2 and end with 3

|===

Let us take a real example, consider COMPANY table with the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example, which will display all the records from COMPANY table, where AGE starts with 2.

[source]
----
sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB '2*';

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example, which will display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text −

[source]
----
sqlite> SELECT * FROM COMPANY WHERE ADDRESS  GLOB '*-*';

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

----


== SQLite - LIMIT Clause

SQLite *LIMIT* clause is used to limit the data amount returned by the SELECT statement.


=== Syntax

Following is the basic syntax of SELECT statement with LIMIT clause.

[source]
----
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

----

Following is the syntax of LIMIT clause when it is used along with OFFSET clause.

[source]
----
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

----

SQLite engine will return rows starting from the next row to the given OFFSET as shown below in the last example.


=== Example

Consider COMPANY table with the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example, which limits the row in the table according to the number of rows you want to fetch from table.

[source]
----
sqlite> SELECT * FROM COMPANY LIMIT 6;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0

----

However in certain situations, you may need to pick up a set of records from a particular offset. Here is an example, which picks up 3 records starting from the 3rd position.

[source]
----
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

----


== SQLite - ORDER BY Clause

SQLite *ORDER BY* clause is used to sort the data in an ascending or descending order, based on one or more columns.


=== Syntax

Following is the basic syntax of ORDER BY clause.

[source]
----
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

----

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be available in the column-list.


=== Example

Consider COMPANY table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example, which will sort the result in descending order by SALARY.

[source]
----
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

----

Following is an example, which will sort the result in descending order by NAME and SALARY.

[source]
----
sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
5           David       27          Texas       85000.0
7           James       24          Houston     10000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0

----

Following is an example, which will sort the result in descending order by NAME.

[source]
----
sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
1           Paul        32          California  20000.0
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
5           David       27          Texas       85000.0
2           Allen       25          Texas       15000.0

----


== SQLite - GROUP BY Clause

SQLite *GROUP BY * clause is used in collaboration with the SELECT statement to arrange identical data into groups.

GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.


=== Syntax

Following is the basic syntax of GROUP BY clause. GROUP BY clause must follow the conditions in the WHERE clause and must precede ORDER BY clause if one is used.

[source]
----
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

----

You can use more than one column in the GROUP BY clause. Make sure whatever column you are using to group, that column should be available in the column-list.


=== Example

Consider COMPANY table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

If you want to know the total amount of salary on each customer, then GROUP BY query will be as follows −

[source]
----
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

----

This will produce the following result −

[source]
----
NAME        SUM(SALARY)
----------  -----------
Allen       15000.0
David       85000.0
James       10000.0
Kim         45000.0
Mark        65000.0
Paul        20000.0
Teddy       20000.0

----

Now, let us create three more records in COMPANY table using the following INSERT statements.

[source]
----
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 );
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 );
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );

----

Now, our table has the following records with duplicate names.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

----

Again, let us use the same statement to group-by all the records using NAME column as follows −

[source]
----
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

----

This will produce the following result.

[source]
----
NAME        SUM(SALARY)
----------  -----------
Allen       15000
David       85000
James       20000
Kim         45000
Mark        65000
Paul        40000
Teddy       20000

----

Let us use ORDER BY clause along with GROUP BY clause as follows −

[source]
----
sqlite>  SELECT NAME, SUM(SALARY)
   FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

----

This will produce the following result.

[source]
----
NAME        SUM(SALARY)
----------  -----------
Teddy       20000
Paul        40000
Mark        65000
Kim         45000
James       20000
David       85000
Allen       15000

----


== SQLite - HAVING Clause

HAVING clause enables you to specify conditions that filter which group results appear in the final results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by GROUP BY clause.


=== Syntax

Following is the position of HAVING clause in a SELECT query.

[source]
----
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

----

HAVING clause must follow GROUP BY clause in a query and must also precede ORDER BY clause if used. Following is the syntax of the SELECT statement, including HAVING clause.

[source]
----
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

----


=== Example

Consider COMPANY table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

----

Following is the example, which will display the record for which the name count is less than 2.

[source]
----
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000
5           David       27          Texas       85000
6           Kim         22          South-Hall  45000
4           Mark        25          Rich-Mond   65000
3           Teddy       23          Norway      20000

----

Following is the example, which will display the record for which the name count is greater than 2.

[source]
----
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
10          James       45          Texas       5000

----


== SQLite - DISTINCT Keyword

SQLite *DISTINCT* keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only the unique records.

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.


=== Syntax

Following is the basic syntax of DISTINCT keyword to eliminate duplicate records.

[source]
----
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

----


=== Example

Consider COMPANY table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

----

First, let us see how the following SELECT query returns duplicate salary records.

[source]
----
sqlite> SELECT name FROM COMPANY;

----

This will produce the following result.

[source]
----
NAME

Now, let us use *DISTINCT* keyword with the above SELECT query and see the result.

[source]

This will produce the following result, where there is no duplicate entry.

[source]

Paul
Allen
Teddy
Mark
David
Kim
James

----


== SQLite - PRAGMA

SQLite *PRAGMA* command is a special command to be used to control various environmental variables and state flags within the SQLite environment. A PRAGMA value can be read and it can also be set based on the requirements.


==== Syntax

To query the current PRAGMA value, just provide the name of the pragma.

[source]
----
PRAGMA pragma_name;

----

To set a new value for PRAGMA, use the following syntax.

[source]
----
PRAGMA pragma_name = value;

----

The set mode can be either the name or the integer equivalent but the returned value will always be an integer.


=== auto_vacuum Pragma

The *auto_vacuum* pragma gets or sets the auto-vacuum mode. Following is the simple syntax.

[source]
----
PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

----

Where *mode* can be any of the following −
[%autowidth]
|===

|Sr.No.|Pragma Value & Description
|1|*0 or NONE*
Auto-vacuum is disabled. This is the default mode which means that a database file will never shrink in size unless it is manually vacuumed using the VACUUM command.

|2|*1 or FULL*
Auto-vacuum is enabled and fully automatic which allows a database file to shrink as data is removed from the database.

|3|*2 or INCREMENTAL*
Auto-vacuum is enabled but must be manually activated. In this mode the reference data is maintained, but free pages are simply put on the free list. These pages can be recovered using the *incremental_vacuum pragma* any time.

|===


=== cache_size Pragma

The *cache_size* pragma can get or temporarily set the maximum size of the in-memory page cache. Following is the simple syntax.

[source]
----
PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

----

The *pages* value represents the number of pages in the cache. The built-in page cache has a default size of 2,000 pages and a minimum size of 10 pages.


=== case_sensitive_like Pragma

The *case_sensitive_like* pragma controls the case-sensitivity of the built-in LIKE expression. By default, this pragma is false which means that the built-in LIKE operator ignores the letter case. Following is the simple syntax.

[source]
----
PRAGMA case_sensitive_like = [true|false];

----

There is no way to query for the current state of this pragma.


=== count_changes Pragma

*count_changes* pragma gets or sets the return value of data manipulation statements such as INSERT, UPDATE and DELETE. Following is the simple syntax.

[source]
----
PRAGMA count_changes;
PRAGMA count_changes = [true|false];

----

By default, this pragma is false and these statements do not return anything. If set to true, each of the mentioned statement will return a one-column, one-row table consisting of a single integer value indicating impacted rows by the operation.


=== database_list Pragma

The *database_list* pragma will be used to list down all the databases attached. Following is the simple syntax.

[source]
----
PRAGMA database_list;

----

This pragma will return a three-column table with one row per open or attached database giving database sequence number, its name and the file associated.


=== encoding Pragma

The *encoding* pragma controls how strings are encoded and stored in a database file. Following is the simple syntax.

[source]
----
PRAGMA encoding;
PRAGMA encoding = format;

----

The format value can be one of *UTF-8, UTF-16le*, or *UTF-16be*.


=== freelist_count Pragma

The *freelist_count* pragma returns a single integer indicating how many database pages are currently marked as free and available. Following is the simple syntax.

[source]
----
PRAGMA [database.]freelist_count;

----

The format value can be one of *UTF-8, UTF-16le*, or *UTF-16be*.


=== index_info Pragma

The *index_info* pragma returns information about a database index. Following is the simple syntax.

[source]
----
PRAGMA [database.]index_info( index_name );

----

The result set will contain one row for each column contained in the index giving column sequence, column index with-in table and column name.


=== index_list Pragma

*index_list* pragma lists all of the indexes associated with a table. Following is the simple syntax.

[source]
----
PRAGMA [database.]index_list( table_name );

----

The result set will contain one row for each index giving index sequence, index name and flag indicating whether the index is unique or not.


=== journal_mode Pragma

The *journal_mode* pragma gets or sets the journal mode which controls how the journal file is stored and processed. Following is the simple syntax.

[source]
----
PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

----

There are five supported journal modes as listed in the following table.
[%autowidth]
|===

|Sr.No.|Pragma Value & Description
|1|*DELETE*
This is the default mode. Here at the conclusion of a transaction, the journal file is deleted.

|2|*TRUNCATE*
The journal file is truncated to a length of zero bytes.

|3|*PERSIST*
The journal file is left in place, but the header is overwritten to indicate the journal is no longer valid.

|4|*MEMORY*
The journal record is held in memory, rather than on disk.

|5|*OFF*
No journal record is kept.

|===


=== max_page_count Pragma

The *max_page_count* pragma gets or sets the maximum allowed page count for a database. Following is the simple syntax.

[source]
----
PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

----

The default value is 1,073,741,823 which is one giga-page, which means if the default 1 KB page size, this allows databases to grow up to one terabyte.


=== page_count Pragma

The *page_count* pragma returns in the current number of pages in the database. Following is the simple syntax −

[source]
----
PRAGMA [database.]page_count;

----

The size of the database file should be page_count * page_size.


=== page_size Pragma

The *page_size* pragma gets or sets the size of the database pages. Following is the simple syntax.

[source]
----
PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

----

By default, the allowed sizes are 512, 1024, 2048, 4096, 8192, 16384, and 32768 bytes. The only way to alter the page size on an existing database is to set the page size and then immediately VACUUM the database.


=== parser_trace Pragma

The *parser_trace* pragma controls printing the debugging state as it parses SQL commands. Following is the simple syntax.

[source]
----
PRAGMA parser_trace = [true|false];

----

By default, it is set to false but when enabled by setting it to true, the SQL parser will print its state as it parses SQL commands.


=== recursive_triggers Pragma

The *recursive_triggers* pragma gets or sets the recursive trigger functionality. If recursive triggers are not enabled, a trigger action will not fire another trigger. Following is the simple syntax.

[source]
----
PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

----


=== schema_version Pragma

The *schema_version* pragma gets or sets the schema version value that is stored in the database header. Following is the simple syntax.

[source]
----
PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

----

This is a 32-bit signed integer value that keeps track of schema changes. Whenever a schema-altering command is executed (like, CREATE... or DROP...), this value is incremented.


=== secure_delete Pragma

The *secure_delete* pragma is used to control how the content is deleted from the database. Following is the simple syntax.

[source]
----
PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

----

The default value for the secure delete flag is normally off, but this can be changed with the SQLITE_SECURE_DELETE build option.


=== sql_trace Pragma

The *sql_trace* pragma is used to dump SQL trace results to the screen. Following is the simple syntax.

[source]
----
PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

----

SQLite must be compiled with the SQLITE_DEBUG directive for this pragma to be included.


=== synchronous Pragma

The *synchronous* pragma gets or sets the current disk synchronization mode, which controls how aggressively SQLite will write data all the way out to physical storage. Following is the simple syntax.

[source]
----
PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

----

SQLite supports the following synchronization modes as listed in the table.
[%autowidth]
|===

|Sr.No.|Pragma Value & Description
|1|*0 or OFF*
No syncs at all

|2|*1 or NORMAL*
Sync after each sequence of critical disk operations

|3|*2 or FULL*
Sync after each critical disk operation

|===


=== temp_store Pragma

The *temp_store* pragma gets or sets the storage mode used by temporary database files. Following is the simple syntax.

[source]
----
PRAGMA temp_store;
PRAGMA temp_store = mode;

----

SQLite supports the following storage modes.
[%autowidth]
|===

|Sr.No.|Pragma Value & Description
|1|*0 or DEFAULT*
Use compile-time default. Normally FILE.

|2|*1 or FILE*
Use file-based storage.

|3|*2 or MEMORY*
Use memory-based storage.

|===


=== temp_store_directory Pragma

The *temp_store_directory* pragma gets or sets the location used for temporary database files. Following is the simple syntax.

[source]
----
PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

----


=== user_version Pragma

The *user_version* pragma gets or sets the user-defined version value that is stored in the database header. Following is the simple syntax.

[source]
----
PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

----

This is a 32-bit signed integer value, which can be set by the developer for version tracking purpose.


=== writable_schema Pragma

The *writable_schema* pragma gets or sets the ability to modify system tables. Following is the simple syntax.

[source]
----
PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

----

If this pragma is set, tables that start with sqlite_ can be created and modified, including the sqlite_master table. Be careful while using pragma because it can lead to complete database corruption.


== SQLite - Constraints

Constraints are the rules enforced on a data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table.

Following are commonly used constraints available in SQLite.

. *NOT NULL Constraint* − Ensures that a column cannot have NULL value.


. *DEFAULT Constraint * − Provides a default value for a column when none is specified.


. *UNIQUE Constraint* − Ensures that all values in a column are different.


. *PRIMARY Key* − Uniquely identifies each row/record in a database table.


. *CHECK Constraint* − Ensures that all values in a column satisfies certain conditions.



=== NOT NULL Constraint

By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column.

A NULL is not the same as no data, rather, it represents unknown data.


==== Example

For example, the following SQLite statement creates a new table called COMPANY and adds five columns, three of which, ID and NAME and AGE, specifies not to accept NULLs.

[source]
----
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

----


=== DEFAULT Constraint

The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.


==== Example

For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, SALARY column is set to 5000.00 by default, thus in case INSERT INTO statement does not provide a value for this column, then by default, this column would be set to 5000.00.

[source]
----
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

----


=== UNIQUE Constraint

The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having an identical age.


==== Example

For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with the same age −

[source]
----
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

----


=== PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique IDs.

We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a 'longstanding coding oversight', primary keys can be NULL in SQLite. This is not the case with other databases.

A primary key is a field in a table which uniquely identifies each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a *composite key*.

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).


==== Example

You already have seen various examples above where we have created COMPANY table with ID as a primary key.

[source]
----
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

----


=== CHECK Constraint

CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.


==== Example

For example, the following SQLite creates a new table called COMPANY and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY Zero.

[source]
----
CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

----


=== Dropping Constraint

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.


== SQLite - JOINS

SQLite *Joins* clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

SQL defines three major types of joins −

. The CROSS JOIN

. The INNER JOIN

. The OUTER JOIN

Before we proceed, let's consider two tables COMPANY and DEPARTMENT. We already have seen INSERT statements to populate COMPANY table. So just let's assume the list of records available in COMPANY table −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Another table is DEPARTMENT with the following definition −

[source]
----
CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

----

Here is the list of INSERT statements to populate DEPARTMENT table −

[source]
----
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

----

Finally, we have the following list of records available in DEPARTMENT table −

[source]
----
ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineering 2
3           Finance     7

----


=== The CROSS JOIN

CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y row, respectively, the resulting table will have x*y row. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to only use them when appropriate.

Following is the syntax of CROSS JOIN −

[source]
----
SELECT ... FROM table1 CROSS JOIN table2 ...

----

Based on the above tables, you can write a CROSS JOIN as follows −

[source]
----
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

----

The above query will produce the following result −

[source]
----
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineering
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineering
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineering
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineering
7           Mark        Finance
1           David       IT Billing
2           David       Engineering
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineering
7           Kim         Finance
1           James       IT Billing
2           James       Engineering
7           James       Finance

----


=== The INNER JOIN

INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, the column values for each matched pair of rows of A and B are combined into a result row.

An INNER JOIN is the most common and default type of join. You can use INNER keyword optionally.

Following is the syntax of INNER JOIN −

[source]
----
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

----

To avoid redundancy and keep the phrasing shorter, INNER JOIN conditions can be declared with a *USING* expression. This expression specifies a list of one or more columns.

[source]
----
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

----

A NATURAL JOIN is similar to a *JOIN...USING*, only it automatically tests for equality between the values of every column that exists in both tables −

[source]
----
SELECT ... FROM table1 NATURAL JOIN table2...

----

Based on the above tables, you can write an INNER JOIN as follows −

[source]
----
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

----

The above query will produce the following result −

[source]
----
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
7           James       Finance

----


=== The OUTER JOIN

OUTER JOIN is an extension of INNER JOIN. Though SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL, SQLite only supports the *LEFT OUTER JOIN*.

OUTER JOINs have a condition that is identical to INNER JOINs, expressed using an ON, USING, or NATURAL keyword. The initial results table is calculated the same way. Once the primary JOIN is calculated, an OUTER JOIN will take any unjoined rows from one or both tables, pad them out with NULLs, and append them to the resulting table.

Following is the syntax of LEFT OUTER JOIN −

[source]
----
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

----

To avoid redundancy and keep the phrasing shorter, OUTER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns.

[source]
----
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

----

Based on the above tables, you can write an outer join as follows −

[source]
----
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

----

The above query will produce the following result −

[source]
----
EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineering
            Teddy
            Mark
            David
            Kim
7           James       Finance

----


== SQLite - UNION Clause

SQLite *UNION* clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be of the same length.


==== Syntax

Following is the basic syntax of *UNION*.

[source]
----
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

----

Here the given condition could be any given expression based on your requirement.


==== Example

Consider the following two tables, (a) link:../sqlite/company.sql[COMPANY] table as follows −

[source]
----
sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

----

(b) Another table is link:../sqlite/department.sql[DEPARTMENT] as follows −

[source]
----
ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

----

Now let us join these two tables using SELECT statement along with UNION clause as follows −

[source]
----
sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID

         UNION

         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

----

This will produce the following result.

[source]
----
EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

----


=== The UNION ALL Clause

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

The same rules that apply to UNION apply to the UNION ALL operator as well.


==== Syntax

Following is the basic syntax of *UNION ALL*.

[source]
----
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

----

Here the given condition could be any given expression based on your requirement.


==== Example

Now, let us join the above-mentioned two tables in our SELECT statement as follows −

[source]
----
sqlite>  SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID

         UNION ALL

         SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

----

This will produce the following result.

[source]
----
EMP_ID      NAME                  DEPT
----------  --------------------  ----------
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance
1           Paul                  IT Billing
2           Allen                 Engineering
3           Teddy                 Engineering
4           Mark                  Finance
5           David                 Engineering
6           Kim                   Finance
7           James                 Finance

----


== SQLite - NULL Values

SQLite *NULL* is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.

A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.


=== Syntax

Following is the basic syntax of using *NULL* while creating a table.

[source]
----
SQLite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

----

Here, *NOT NULL* signifies that the column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL which means these columns could be NULL.

A field with a NULL value is one that has been left blank during record creation.


=== Example

The NULL value can cause problems when selecting data, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results. Consider the following table, link:../sqlite/company.sql[COMPANY] with the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Let us use UPDATE statement to set a few nullable values as NULL as follows −

[source]
----
sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

----

Now, COMPANY table will have the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22
7           James       24

----

Next, let us see the usage of *IS NOT NULL* operator to list down all the records where SALARY is not NULL.

[source]
----
sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NOT NULL;

----

The above SQLite statement will produce the following result −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

----

Following is the usage of *IS NULL* operator, which will list down all the records where SALARY is NULL.

[source]
----
sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

----

The above SQLite statement will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22
7           James       24

----


== SQLite - ALIAS Syntax

You can rename a table or a column temporarily by giving another name, which is known as *ALIAS*. The use of table aliases means to rename a table in a particular SQLite statement. Renaming is a temporary change and the actual table name does not change in the database.

The column aliases are used to rename a table's columns for the purpose of a particular SQLite query.


=== Syntax

Following is the basic syntax of *table* alias.

[source]
----
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

----

Following is the basic syntax of *column* alias.

[source]
----
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

----


=== Example

Consider the following two tables, (a) link:../sqlite/company.sql[COMPANY] table is as follows −

[source]
----
sqlite> select * from COMPANY;
ID          NAME                  AGE         ADDRESS     SALARY
----------  --------------------  ----------  ----------  ----------
1           Paul                  32          California  20000.0
2           Allen                 25          Texas       15000.0
3           Teddy                 23          Norway      20000.0
4           Mark                  25          Rich-Mond   65000.0
5           David                 27          Texas       85000.0
6           Kim                   22          South-Hall  45000.0
7           James                 24          Houston     10000.0

----

(b) Another table is link:../sqlite/department.sql[DEPARTMENT] as follows −

[source]
----
ID          DEPT                  EMP_ID
----------  --------------------  ----------
1           IT Billing            1
2           Engineering           2
3           Finance               7
4           Engineering           3
5           Finance               4
6           Engineering           5
7           Finance               6

----

Now, following is the usage of *TABLE ALIAS* where we use C and D as aliases for COMPANY and DEPARTMENT tables respectively −

[source]
----
sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

----

The above SQLite statement will produce the following result −

[source]
----
ID          NAME        AGE         DEPT
----------  ----------  ----------  ----------
1           Paul        32          IT Billing
2           Allen       25          Engineering
3           Teddy       23          Engineering
4           Mark        25          Finance
5           David       27          Engineering
6           Kim         22          Finance
7           James       24          Finance

----

Consider an example for the usage of *COLUMN ALIAS* where COMPANY_ID is an alias of ID column and COMPANY_NAME is an alias of name column.

[source]
----
sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

----

The above SQLite statement will produce the following result −

[source]
----
COMPANY_ID  COMPANY_NAME  AGE         DEPT
----------  ------------  ----------  ----------
1           Paul          32          IT Billing
2           Allen         25          Engineering
3           Teddy         23          Engineering
4           Mark          25          Finance
5           David         27          Engineering
6           Kim           22          Finance
7           James         24          Finance

----


== SQLite - Triggers

SQLite *Triggers* are database callback functions, which are automatically performed/invoked when a specified database event occurs. Following are the important points about SQLite triggers −

. SQLite trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs or whenever an UPDATE occurs on one or more specified columns of a table.


. At this time, SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence, explicitly specifying FOR EACH ROW is optional.


. Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted, or updated using references of the form *NEW.column-name* and *OLD.column-name*, where column-name is the name of a column from the table that the trigger is associated with.


. If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.


. The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification, or removal of the associated row.


. Triggers are automatically dropped when the table that they are associated with is dropped.


. The table to be modified must exist in the same database as the table or view to which the trigger is attached and one must use just *tablename* not *database.tablename*.


. A special SQL function RAISE() may be used within a trigger-program to raise an exception.



==== Syntax

Following is the basic syntax of creating a *trigger*.

[source]
----
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

----

Here, *event_name* could be INSERT, DELETE, and UPDATE database operation on the mentioned table *table_name*. You can optionally specify FOR EACH ROW after table name.

Following is the syntax for creating a trigger on an UPDATE operation on one or more specified columns of a table.

[source]
----
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
   -- Trigger logic goes here....
END;

----


==== Example

Let us consider a case where we want to keep audit trial for every record being inserted in COMPANY table, which we create newly as follows (Drop COMPANY table if you already have it).

[source]
----
sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

----

To keep audit trial, we will create a new table called AUDIT where the log messages will be inserted, whenever there is an entry in COMPANY table for a new record.

[source]
----
sqlite> CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

----

Here, ID is the AUDIT record ID, and EMP_ID is the ID which will come from COMPANY table and DATE will keep timestamp when the record will be created in COMPANY table. Now let's create a trigger on COMPANY table as follows −

[source]
----
sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

----

Now, we will start actual work, Let's start inserting record in COMPANY table which should result in creating an audit log record in AUDIT table. Create one record in COMPANY table as follows −

[source]
----
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

----

This will create one record in COMPANY table, which is as follows −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

----

Same time, one record will be created in AUDIT table. This record is the result of a trigger, which we have created on INSERT operation in COMPANY table. Similarly, you can create your triggers on UPDATE and DELETE operations based on your requirements.

[source]
----
EMP_ID      ENTRY_DATE
----------  -------------------
1           2013-04-05 06:26:00

----


=== Listing Triggers

You can list down all the triggers from *sqlite_master* table as follows −

[source]
----
sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

----

The above SQLite statement will list down only one entry as follows −

[source]
----
name

If you want to list down triggers on a particular table, then use AND clause with table name as follows −

[source]

The above SQLite statement will also list down only one entry as follows −

[source]

audit_log

----


=== Dropping Triggers

Following is the DROP command, which can be used to drop an existing trigger.

[source]
----
sqlite> DROP TRIGGER trigger_name;

----


== SQLite - Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an *index* is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers.

An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.


=== The CREATE INDEX Command

Following is the basic syntax of *CREATE INDEX*.

[source]
----
CREATE INDEX index_name ON table_name;

----


==== Single-Column Indexes

A single-column index is one that is created based on only one table column. The basic syntax is as follows −

[source]
----
CREATE INDEX index_name
ON table_name (column_name);

----


==== Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows −

[source]
----
CREATE UNIQUE INDEX index_name
on table_name (column_name);

----


==== Composite Indexes

A composite index is an index on two or more columns of a table. The basic syntax is as follows −

[source]
----
CREATE INDEX index_name
on table_name (column1, column2);

----

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.

Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.


==== Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

*Example*

Following is an example where we will create an index in link:../sqlite/company.sql[COMPANY] table for salary column −

[source]
----
sqlite> CREATE INDEX salary_index ON COMPANY (salary);

----

Now, let's list down all the indices available in COMPANY table using *.indices* command as follows −

[source]
----
sqlite> .indices COMPANY

----

This will produce the following result, where sqlite_autoindex_COMPANY_1 is an implicit index which got created when the table itself was created.

[source]
----
salary_index
sqlite_autoindex_COMPANY_1

----

You can list down all the indexes database wide as follows −

[source]
----
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

----


=== The DROP INDEX Command

An index can be dropped using SQLite *DROP* command. Care should be taken when dropping an index because performance may be slowed or improved.

Following is the basic syntax is as follows −

[source]
----
DROP INDEX index_name;

----

You can use the following statement to delete previously created index.

[source]
----
sqlite> DROP INDEX salary_index;

----


==== When Should Indexes Be Avoided?

Although indexes are intended to enhance the performance of a database, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered.

Indexes should not be used in −

. Small tables.

. Tables that have frequent, large batch update or insert operations.

. Columns that contain a high number of NULL values.

. Columns that are frequently manipulated.


== SQLite - INDEXED BY Clause

The "INDEXED BY index-name" clause specifies that the named index must be used in order to look up values on the preceding table.

If index-name does not exist or cannot be used for the query, then the preparation of the SQLite statement fails.

The "NOT INDEXED" clause specifies that no index shall be used when accessing the preceding table, including implied indices created by UNIQUE and PRIMARY KEY constraints.

However, the INTEGER PRIMARY KEY can still be used to look up entries even when "NOT INDEXED" is specified.


=== Syntax

Following is the syntax for INDEXED BY clause and it can be used with DELETE, UPDATE or SELECT statement.

[source]
----
SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);

----


=== Example

Consider table link:../sqlite/company.sql[COMPANY] We will create an index and use it for performing INDEXED BY operation.

[source]
----
sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>

----

Now selecting the data from table COMPANY you can use INDEXED BY clause as follows −

[source]
----
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

----


== SQLite - ALTER TABLE Command

SQLite *ALTER TABLE* command modifies an existing table without performing a full dump and reload of the data. You can rename a table using ALTER TABLE statement and additional columns can be added in an existing table using ALTER TABLE statement.

There is no other operation supported by ALTER TABLE command in SQLite except renaming a table and adding a column in an existing table.


=== Syntax

Following is the basic syntax of *ALTER TABLE* to RENAME an existing table.

[source]
----
ALTER TABLE database_name.table_name RENAME TO new_table_name;

----

Following is the basic syntax of *ALTER TABLE* to add a new column in an existing table.

[source]
----
ALTER TABLE database_name.table_name ADD COLUMN column_def...;

----


=== Example

Consider the link:../sqlite/company.sql[COMPANY] table with the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Now, let's try to rename this table using ALTER TABLE statement as follows −

[source]
----
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

----

The above SQLite statement will rename COMPANY table to OLD_COMPANY. Now, let's try to add a new column in OLD_COMPANY table as follows −

[source]
----
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

----

COMPANY table is now changed and following will be the output from SELECT statement.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY      SEX
----------  ----------  ----------  ----------  ----------  ---
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

It should be noted that newly added column is filled with NULL values.


== SQLite - TRUNCATE TABLE Command

Unfortunately, we do not have TRUNCATE TABLE command in SQLite but you can use SQLite *DELETE * command to delete complete data from an existing table, though it is recommended to use DROP TABLE command to drop the complete table and re-create it once again.


=== Syntax

Following is the basic syntax of DELETE command.

[source]
----
sqlite> DELETE FROM table_name;

----

Following is the basic syntax of DROP TABLE.

[source]
----
sqlite> DROP TABLE table_name;

----

If you are using DELETE TABLE command to delete all the records, it is recommended to use *VACUUM* command to clear unused space.


=== Example

Consider link:../sqlite/company.sql[COMPANY] table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is the example to truncate the above table −

[source]
----
SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;

----

Now, COMPANY table is truncated completely and nothing will be the output from SELECT statement.


== SQLite - Views

A view is nothing more than a SQLite statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQLite query.

A view can contain all rows of a table or selected rows from one or more tables. A view can be created from one or many tables which depends on the written SQLite query to create a view.

Views which are kind of virtual tables, allow the users to −

. Structure data in a way that users or classes of users find natural or intuitive.


. Restrict access to the data such that a user can only see limited data instead of a complete table.


. Summarize data from various tables, which can be used to generate reports.


SQLite views are read-only and thus you may not be able to execute a DELETE, INSERT or UPDATE statement on a view. However, you can create a trigger on a view that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.


=== Creating Views

SQLite views are created using the *CREATE VIEW* statement. SQLite views can be created from a single table, multiple tables, or another view.

Following is the basic CREATE VIEW syntax.

[source]
----
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

----

You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query. If the optional TEMP or TEMPORARY keyword is present, the view will be created in the temp database.


==== Example

Consider link:../sqlite/company.sql[COMPANY] table with the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Following is an example to create a view from COMPANY table. This view will be used to have only a few columns from COMPANY table.

[source]
----
sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

----

You can now query COMPANY_VIEW in a similar way as you query an actual table. Following is an example −

[source]
----
sqlite> SELECT * FROM COMPANY_VIEW;

----

This will produce the following result.

[source]
----
ID          NAME        AGE
----------  ----------  ----------
1           Paul        32
2           Allen       25
3           Teddy       23
4           Mark        25
5           David       27
6           Kim         22
7           James       24

----


=== Dropping Views

To drop a view, simply use the DROP VIEW statement with the *view_name*. The basic DROP VIEW syntax is as follows −

[source]
----
sqlite> DROP VIEW view_name;

----

The following command will delete COMPANY_VIEW view, which we created in the last section.

[source]
----
sqlite> DROP VIEW COMPANY_VIEW;

----


== SQLite - Transactions

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating, updating, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

Practically, you will club many SQLite queries into a group and you will execute all of them together as part of a transaction.


=== Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID.

. *Atomicity* − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.


. *Consistency* − Ensures that the database properly changes states upon a successfully committed transaction.


. *Isolation* − Enables transactions to operate independently of and transparent to each other.


. *Durability* − Ensures that the result or effect of a committed transaction persists in case of a system failure.



=== Transaction Control

Following are the following commands used to control transactions:

. *BEGIN TRANSACTION* − To start a transaction.


. *COMMIT* − To save the changes, alternatively you can use *END TRANSACTION* command.


. *ROLLBACK* − To rollback the changes.


Transactional control commands are only used with DML commands INSERT, UPDATE, and DELETE. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.


==== BEGIN TRANSACTION Command

Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. However, a transaction will also ROLLBACK if the database is closed or if an error occurs. Following is the simple syntax to start a transaction.

[source]
----
BEGIN;
or
BEGIN TRANSACTION;

----


==== COMMIT Command

COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

Following is the syntax for COMMIT command.

[source]
----
COMMIT;
or
END TRANSACTION;

----


==== ROLLBACK Command

ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Following is the syntax for ROLLBACK command.

[source]
----
ROLLBACK;

----

*Example*

Consider link:../sqlite/company.sql[COMPANY] table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Now, let's start a transaction and delete records from the table having age = 25. Then, use ROLLBACK command to undo all the changes.

[source]
----
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;

----

Now, if you check COMPANY table, it still has the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Let's start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes.

[source]
----
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;

----

If you now check COMPANY table is still has the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----


== SQLite - Subqueries

A Subquery or Inner query or Nested query is a query within another SQLite query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators such as =, <, >, >=, <=, IN, BETWEEN, etc.

There are a few rules that subqueries must follow −

. Subqueries must be enclosed within parentheses.


. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.


. An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.


. Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.


. BETWEEN operator cannot be used with a subquery; however, BETWEEN can be used within the subquery.



=== Subqueries with SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

[source]
----
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
   (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

----


==== Example

Consider link:../sqlite/company.sql[COMPANY] table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Now, let us check the following sub-query with SELECT statement.

[source]
----
sqlite> SELECT *
   FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY
      WHERE SALARY > 45000) ;

----

This will produce the following result.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

----


=== Subqueries with INSERT Statement

Subqueries can also be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.

Following is the basic syntax is as follows −

[source]
----
INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

----


==== Example

Consider a table COMPANY_BKP with similar structure as COMPANY table and can be created using the same CREATE TABLE using COMPANY_BKP as the table name. To copy the complete COMPANY table into COMPANY_BKP, following is the syntax −

[source]
----
sqlite> INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY) ;

----


=== Subqueries with UPDATE Statement

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

Following is the basic syntax is as follows −

[source]
----
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

----


==== Example

Assuming, we have COMPANY_BKP table available which is a backup of COMPANY table.

Following example updates SALARY by 0.50 times in COMPANY table for all the customers, whose AGE is greater than or equal to 27.

[source]
----
sqlite> UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

----

This would impact two rows and finally COMPANY table would have the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  10000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----


=== Subqueries with DELETE Statement

Subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

Following is the basic syntax is as follows −

[source]
----
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
      FROM TABLE_NAME)
   [ WHERE) ]

----


==== Example

Assuming, we have COMPANY_BKP table available which is a backup of COMPANY table.

Following example deletes records from COMPANY table for all the customers whose AGE is greater than or equal to 27.

[source]
----
sqlite> DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
   WHERE AGE > 27 );

----

This will impact two rows and finally COMPANY table will have the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----


== SQLite - AUTOINCREMENT

SQLite *AUTOINCREMENT* is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using *AUTOINCREMENT* keyword when creating a table with specific column name to auto increment.

The keyword *AUTOINCREMENT* can be used with INTEGER field only.


=== Syntax

The basic usage of *AUTOINCREMENT* keyword is as follows −

[source]
----
CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

----


=== Example

Consider COMPANY table to be created as follows −

[source]
----
sqlite> CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

----

Now, insert the following records into table COMPANY −

[source]
----
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

----

This will insert 7 tuples into the table COMPANY and COMPANY will have the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----


== SQLite - Injection

If you take user input through a webpage and insert it into a SQLite database there's a chance that you have left yourself wide open for a security issue known as SQL Injection. In this chapter, you will learn how to help prevent this from happening and help you secure your scripts and SQLite statements.

Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQLite statement that you will unknowingly run on your database.

Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the following example, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars - modify these rules as needed.

[source]
----
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){
   $db = new SQLiteDatabase('filename');
   $result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
   echo "username not accepted";
}

----

To demonstrate the problem, consider this excerpt −

[source]
----
$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username = '{$name}'");

----

The function call is supposed to retrieve a record from the users table where the name column matches the name specified by the user. Under normal circumstances, *$name* would only contain alphanumeric characters and perhaps spaces, such as the string ilia. However in this case, by appending an entirely new query to $name, the call to the database turns into a disaster: the injected DELETE query removes all records from users.

There are databases interfaces which do not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails but SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.


=== Preventing SQL Injection

You can handle all escape characters smartly in scripting languages like PERL and PHP. Programming language PHP provides the function *string sqlite_escape_string()* to escape input characters that are special to SQLite.

[source]
----
if (get_magic_quotes_gpc()) {
   $name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = '{$name}'");

----

Although the encoding makes it safe to insert the data, it will render simple text comparisons and *LIKE* clauses in your queries unusable for the columns that contain the binary data.

*Note* − *addslashes()* should NOT be used to quote your strings for SQLite queries; it will lead to strange results when retrieving your data.


== SQLite - EXPLAIN

SQLite statement can be preceded by the keyword "EXPLAIN" or by the phrase "EXPLAIN QUERY PLAN" used for describing the details of a table.

Either modification causes the SQLite statement to behave as a query and to return information about how the SQLite statement would have operated if the EXPLAIN keyword or phrase had been omitted.

. The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only.


. The details of the output format are subject to change from one release of SQLite to the next.


. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is variable and only partially documented.



=== Syntax

syntax for *EXPLAIN* is as follows −

[source]
----
EXPLAIN [SQLite Query]

----

syntax for *EXPLAIN QUERY PLAN* is as follows −

[source]
----
EXPLAIN  QUERY PLAN [SQLite Query]

----


=== Example

Consider link:../sqlite/company.sql[COMPANY] table with the following records −

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----

Now, let us check the following sub-query with SELECT statement −

[source]
----
sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;

----

This will produce the following result.

[source]
----
addr        opcode      p1          p2          p3
----------  ----------  ----------  ----------  ----------
0           Goto        0           19
1           Integer     0           0
2           OpenRead    0           8
3           SetNumColu  0           5
4           Rewind      0           17
5           Column      0           4
6           RealAffini  0           0
7           Integer     20000       0
8           Lt          357         16          collseq(BI
9           Rowid       0           0
10          Column      0           1
11          Column      0           2
12          Column      0           3
13          Column      0           4
14          RealAffini  0           0
15          Callback    5           0
16          Next        0           5
17          Close       0           0
18          Halt        0           0
19          Transactio  0           0
20          VerifyCook  0           38
21          Goto        0           1
22          Noop        0           0

----

Now, let us check the following *Explain Query Plan * with SELECT statement −

[source]
----
SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary >= 20000;

order       from        detail
----------  ----------  -------------
0           0           TABLE COMPANY

----


== SQLite - VACUUM

VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

VACUUM command may change the ROWID of entries in tables that do not have an explicit INTEGER PRIMARY KEY. The VACUUM command only works on the main database. It is not possible to VACUUM an attached database file.

VACUUM command will fail if there is an active transaction. VACUUM command is a no-op for in-memory databases. As the VACUUM command rebuilds the database file from scratch, VACUUM can also be used to modify many database-specific configuration parameters.


=== Manual VACUUM

Following is a simple syntax to issue a VACUUM command for the whole database from command prompt −

[source]
----
$sqlite3 database_name "VACUUM;"

----

You can run VACUUM from SQLite prompt as well as follows −

[source]
----
sqlite> VACUUM;

----

You can also run VACUUM on a particular table as follows −

[source]
----
sqlite> VACUUM table_name;

----


=== Auto-VACCUM

SQLite Auto-VACUUM does not do the same as VACUUM rather it only moves free pages to the end of the database thereby reducing the database size. By doing so it can significantly fragment the database while VACUUM ensures defragmentation. Hence, Auto-VACUUM just keeps the database small.

You can enable/disable SQLite auto-vacuuming by the following pragmas running at SQLite prompt −

[source]
----
sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum

----

You can run the following command from the command prompt to check the auto-vacuum setting −

[source]
----
$sqlite3 database_name "PRAGMA auto_vacuum;"

----


== SQLite - Date & Time

SQLite supports five date and time functions as follows −
[%autowidth]
|===

|Sr.No.|Function|Example
|1|date(timestring, modifiers...)|This returns the date in this format: YYYY-MM-DD
|2|time(timestring, modifiers...)|This returns the time as HH:MM:SS
|3|datetime(timestring, modifiers...)|This returns YYYY-MM-DD HH:MM:SS
|4|julianday(timestring, modifiers...)|This returns the number of days since noon in Greenwich on November 24, 4714 B.C.
|5|strftime(timestring, modifiers...)|This returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below.
|===

All the above five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. Following section will give you detail on different types of time strings and modifiers.


=== Time Strings

A time string can be in any of the following formats −
[%autowidth]
|===

|Sr.No.|Time String|Example
|1|YYYY-MM-DD|2010-12-30
|2|YYYY-MM-DD HH:MM|2010-12-30 12:10
|3|YYYY-MM-DD HH:MM:SS.SSS|2010-12-30 12:10:04.100
|4|MM-DD-YYYY HH:MM|30-12-2010 12:10
|5|HH:MM|12:10
|6|YYYY-MM-DD*T*HH:MM|2010-12-30 12:10
|7|HH:MM:SS|12:10:01
|8|YYYYMMDD HHMMSS|20101230 121001
|9|now|2013-05-07
|===

You can use the "T" as a literal character separating the date and the time.


=== Modifiers

The time string can be followed by zero or more modifiers that will alter date and/or time returned by any of the above five functions. Modifiers are applied from the left to right.

Following modifers are available in SQLite −

. NNN days

. NNN hours

. NNN minutes

. NNN.NNNN seconds

. NNN months

. NNN years

. start of month

. start of year

. start of day

. weekday N

. unixepoch

. localtime

. utc


=== Formatters

SQLite provides a very handy function *strftime()* to format any date and time. You can use the following substitutions to format your date and time.
[%autowidth]
|===

|Substitution|Description
|%d|Day of month, 01-31
|%f|Fractional seconds, SS.SSS
|%H|Hour, 00-23
|%j|Day of year, 001-366
|%J|Julian day number, DDDD.DDDD
|%m|Month, 00-12
|%M|Minute, 00-59
|%s|Seconds since 1970-01-01
|%S|Seconds, 00-59
|%w|Day of week, 0-6 (0 is Sunday)
|%W|Week of year, 01-53
|%Y|Year, YYYY
|%%|% symbol
|===


==== Examples

Let's try various examples now using SQLite prompt. Following command computes the current date.

[source]
----
sqlite> SELECT date('now');
2013-05-07

----

Following command computes the last day of the current month.

[source]
----
sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31

----

Following command computes the date and time for a given UNIX timestamp 1092941466.

[source]
----
sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

----

Following command computes the date and time for a given UNIX timestamp 1092941466 and compensate for your local timezone.

[source]
----
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 13:51:06

----

Following command computes the current UNIX timestamp.

[source]
----
sqlite> SELECT strftime('%s','now');
1393348134

----

Following command computes the number of days since the signing of the US Declaration of Independence.

[source]
----
sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023

----

Following command computes the number of seconds since a particular moment in 2004.

[source]
----
sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572

----

Following command computes the date of the first Tuesday in October for the current year.

[source]
----
sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01

----

Following command computes the time since the UNIX epoch in seconds (like strftime('%s','now') except includes fractional part).

[source]
----
sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598

----

To convert between UTC and local time values when formatting a date, use the utc or localtime modifiers as follows −

[source]
----
sqlite> SELECT time('12:00', 'localtime');
05:00:00

----

[source]
----
sqlite> SELECT time('12:00', 'utc');
19:00:00

----


== SQLite - Useful Functions

SQLite has many built-in functions to perform processing on string or numeric data. Following is the list of few useful SQLite built-in functions and all are case in-sensitive which means you can use these functions either in lower-case form or in upper-case or in mixed form. For more details, you can check official documentation for SQLite.
[%autowidth]
|===

|Sr.No.|Function & Description
|1|*SQLite COUNT Function*
SQLite COUNT aggregate function is used to count the number of rows in a database table.

|2|*SQLite MAX Function *
SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column.

|3|*SQLite MIN Function*
SQLite MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

|4|*SQLite AVG Function *
SQLite AVG aggregate function selects the average value for certain table column.

|5|*SQLite SUM Function*
SQLite SUM aggregate function allows selecting the total for a numeric column.

|6|*SQLite RANDOM Function*
SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

|7|*SQLite ABS Function*
SQLite ABS function returns the absolute value of the numeric argument.

|8|*SQLite UPPER Function*
SQLite UPPER function converts a string into upper-case letters.

|9|*SQLite LOWER Function*
SQLite LOWER function converts a string into lower-case letters.

|10|*SQLite LENGTH Function*
SQLite LENGTH function returns the length of a string.

|11|*SQLite sqlite_version Function*
SQLite sqlite_version function returns the version of the SQLite library.

|===

Before we start giving examples on the above-mentioned functions, consider COMPANY table with the following records.

[source]
----
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

----


=== SQLite COUNT Function

SQLite COUNT aggregate function is used to count the number of rows in a database table. Following is an example −

[source]
----
sqlite> SELECT count(*) FROM COMPANY;

----

The above SQLite SQL statement will produce the following.

[source]
----
count(*)

=== SQLite MAX Function

SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column. Following is an example −

[source]

The above SQLite SQL statement will produce the following.

[source]