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.

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.

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.

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.

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

This will produce the following result.

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.

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
10          James       45          Texas       5000

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.

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.

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 −

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.

sqlite> SELECT * FROM COMPANY LIMIT 6;

This will produce the following result.

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 3^rd position.

sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

This will produce the following result.

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

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.

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 −

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

This will produce the following result −

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.

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.

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 −

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

This will produce the following result.

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 −

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

This will produce the following result.

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

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.

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.

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.

sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

This will produce the following result.

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.

sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

This will produce the following result.

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.

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

This will produce the following result.

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

PRAGMA pragma_name;

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

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.

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

Where mode can be any of the following −

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 −

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.

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.

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.

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.

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.

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.

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.

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

SQLite supports the following synchronization modes as listed in the table.

temp_store Pragma

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

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite supports the following storage modes.

temp_store_directory Pragma

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

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.

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.

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

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

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.

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