SQLite data type is an attribute that specifies the type of data of any object. Each column, variable and expression has related data type in SQLite.

You would use these data types while creating your tables. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

SQLite Storage Classes

Each value stored in an SQLite database has one of the following storage classes −

SQLite storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths.

SQLite Affinity Type

SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities −

SQLite Affinity and Type Names

Following table lists down various data type names which can be used while creating SQLite3 tables with the corresponding applied affinity.

Boolean Datatype

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Date and Time Datatype

SQLite does not have a separate storage class for storing dates and/or times, but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values.

You can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

Consider a case when you have multiple databases available and you want to use any one of them at a time. SQLite ATTACH DATABASE statement is used to select a particular database, and after this command, all SQLite statements will be executed under the attached database.

Syntax

Following is the basic syntax of SQLite ATTACH DATABASE statement.

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

The above command will also create a database in case the database is already not created, otherwise it will just attach database file name with logical database ”Alias-Name”.

Example

If you want to attach an existing database testDB.db, then ATTACH DATABASE statement would be as follows −

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

Use SQLite .database command to display attached database.

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

The database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment, otherwise you will get the following warning message.

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

In SQLite, sqlite3 command is used to create a new SQLite database. You do not need to have any special privilege to create a database.

Syntax

Following is the basic syntax of sqlite3 command to create a database: −

$sqlite3 DatabaseName.db

Always, database name should be unique within the RDBMS.

Example

If you want to create a new database <testDB.db>, then SQLITE3 statement would be as follows −

$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>

The above command will create a file testDB.db in the current directory. This file will be used as database by SQLite engine. If you have noticed while creating database, sqlite3 command will provide a sqlite> prompt after creating a database file successfully.

Once a database is created, you can verify it in the list of databases using the following SQLite .databases command.

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

You will use SQLite .quit command to come out of the sqlite prompt as follows −

sqlite>.quit
$

The .dump Command

You can use .dump dot command to export complete database in a text file using the following SQLite command at the command prompt.

$sqlite3 testDB.db .dump > testDB.sql

The above command will convert the entire contents of testDB.db database into SQLite statements and dump it into ASCII text file testDB.sql. You can perform restoration from the generated testDB.sql in a simple way as follows −

$sqlite3 testDB.db < testDB.sql

At this moment your database is empty, so you can try above two procedures once you have few tables and data in your database. For now, let”s proceed to the next chapter.

SQLite is followed by unique set of rules and guidelines called Syntax. This chapter lists all the basic SQLite Syntax.

Case Sensitivity

The important point to be noted is that SQLite is case insensitive, i.e. the clauses GLOB and glob have the same meaning in SQLite statements.

Comments

SQLite comments are extra notes, which you can add in your SQLite code to increase its readability and they can appear anywhere; whitespace can occur, including inside expressions and in the middle of other SQL statements but they cannot be nested.

SQL comments begin with two consecutive “-” characters (ASCII 0x2d) and extend up to and including the next newline character (ASCII 0x0a) or until the end of input, whichever comes first.

You can also use C-style comments, which begin with “/*” and extend up to and including the next “*/” character pair or until the end of input, whichever comes first. C-style comments can span multiple lines.

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

SQLite Statements

All the SQLite statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., and all the statements end with a semicolon (;).

SQLite ANALYZE Statement

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

SQLite AND/OR Clause

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

SQLite ALTER TABLE Statement

ALTER TABLE table_name ADD COLUMN column_def...;

SQLite ALTER TABLE Statement (Rename)

ALTER TABLE table_name RENAME TO new_table_name;

SQLite ATTACH DATABASE Statement

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

SQLite BEGIN TRANSACTION Statement

BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;

SQLite BETWEEN Clause

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

SQLite COMMIT Statement

COMMIT;

SQLite CREATE INDEX Statement

CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );

SQLite CREATE UNIQUE INDEX Statement

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

SQLite CREATE TABLE Statement

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

SQLite CREATE TRIGGER Statement

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

SQLite CREATE VIEW Statement

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

SQLite CREATE VIRTUAL TABLE Statement

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

SQLite COMMIT TRANSACTION Statement

COMMIT;

SQLite COUNT Clause

SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;

SQLite DELETE Statement

DELETE FROM table_name
WHERE {CONDITION};

SQLite DETACH DATABASE Statement

DETACH DATABASE ''Alias-Name

SQLite DISTINCT Clause

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

SQLite DROP INDEX Statement

DROP INDEX database_name.index_name;

SQLite DROP TABLE Statement

DROP TABLE database_name.table_name;

SQLite DROP VIEW Statement

DROP INDEX database_name.view_name;

SQLite DROP TRIGGER Statement

DROP INDEX database_name.trigger_name;

SQLite EXISTS Clause

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

SQLite EXPLAIN Statement

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

SQLite GLOB Clause

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

SQLite GROUP BY Clause

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

SQLite HAVING Clause

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

SQLite INSERT INTO Statement

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

SQLite IN Clause

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

SQLite Like Clause

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

SQLite NOT IN Clause

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

SQLite ORDER BY Clause

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

SQLite PRAGMA Statement

PRAGMA pragma_name;

For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);

SQLite RELEASE SAVEPOINT Statement

RELEASE savepoint_name;

SQLite REINDEX Statement

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

SQLite ROLLBACK Statement

ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;

SQLite SAVEPOINT Statement

SAVEPOINT savepoint_name;

SQLite SELECT Statement

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

SQLite UPDATE Statement

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

SQLite VACUUM Statement

VACUUM;

SQLite WHERE Clause

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

This chapter will take you through simple and useful commands used by SQLite programmers. These commands are called SQLite dot commands and exception with these commands is that they should not be terminated by a semi-colon (;).

Let”s start with typing a simple sqlite3 command at command prompt which will provide you with SQLite command prompt where you will issue various SQLite commands.

$sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

For a listing of the available dot commands, you can enter “.help” any time. For example −

sqlite>.help

The above command will display a list of various important SQLite dot commands, which are listed in the following table.

Let”s try .show command to see default setting for your SQLite command prompt.

sqlite>.show
     echo: off
  explain: off
  headers: off
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite>

Make sure there is no space in between sqlite> prompt and dot command, otherwise it will not work.

Formatting Output

You can use the following sequence of dot commands to format your output.

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

The above setting will produce the output in the following format.

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

The sqlite_master Table

The master table holds the key information about your database tables and it is called sqlite_master. You can see its schema as follows −

sqlite>.schema sqlite_master

This will produce the following result.

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

SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed. This chapter will take you through the process of setting up SQLite on Windows, Linux and Mac OS X.

Install SQLite on Windows

• Step 1 − Go to , and download precompiled binaries from Windows section.

• Step 2 − Download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files.

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

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

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>

Install SQLite on Linux

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

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

If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Following are the following steps to install SQLite −

• Step 1 − Go to and download sqlite-autoconf-*.tar.gz from source code section.

• Step 2 − Run the following command −

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

The above command will end with SQLite installation on your Linux machine. Which you can verify as explained above.

Install SQLite on Mac OS X

Though the latest version of Mac OS X comes pre-installed with SQLite but if you do not have installation available then just follow these following steps −

• Step 1 − Go to , and download sqlite-autoconf-*.tar.gz from source code section.

• Step 2 − Run the following command −

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

The above procedure will end with SQLite installation on your Mac OS X machine. Which you can verify by issuing the following command −

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

Finally, you have SQLite command prompt where you can issue SQLite commands for your exercises.