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 −

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

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 −

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

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 a simple examples showing the usage of SQLite Boolean Expressions −

sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           James        24          Houston   10000.0

SQLite – Numeric Expression

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

SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

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

sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

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.

sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
RECORDS = 7

SQLite – Date Expressions

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

sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35

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.

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 −

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.

sqlite> UPDATE COMPANY SET ADDRESS = ''Texas'' WHERE ID = 6;

Now, COMPANY table will have 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          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 −

sqlite> UPDATE COMPANY SET ADDRESS = ''Texas'', SALARY = 20000.00;

Now, COMPANY table will have the following records −

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

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 −

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.

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.

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.

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.

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

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.

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.

sqlite> DELETE FROM COMPANY WHERE ID = 7;

Now COMPANY table will have 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

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 −

sqlite> DELETE FROM COMPANY;

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

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.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

Example

You can specify a condition using such as >, <, =, LIKE, NOT, etc. 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 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.

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

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.

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

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.

sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

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 NAME starts with ”Ki”, does not matter what comes after ”Ki”.

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE ''Ki%

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

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

sqlite> SELECT * FROM COMPANY WHERE NAME GLOB ''Ki*

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

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

sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

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

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

sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

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

sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

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

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 −

sqlite> SELECT AGE FROM COMPANY
   WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

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

sqlite> SELECT * FROM COMPANY
   WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

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

What is an Operator in SQLite?

An operator is a reserved word or a character used primarily in an SQLite statement”s WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

Operators are used to specify conditions in an SQLite statement and to serve as conjunctions for multiple conditions in a statement.

• Arithmetic operators
• Comparison operators
• Logical operators
• Bitwise operators

SQLite Arithmetic Operators

Assume variable a holds 10 and variable b holds 20, then SQLite arithmetic operators will be used as follows −

SQLite Comparison Operators

Assume variable a holds 10 and variable b holds 20, then SQLite comparison operators will be used as follows

SQLite Logical Operators

Here is a list of all the logical operators available in SQLite.

SQLite Bitwise Operators

Bitwise operator works on bits and performs bit-by-bit operation. Following is the truth table for & and |.

Assume if A = 60; and B = 13, then in binary format, they will be as follows −

A = 0011 1100

B = 0000 1101

—————–

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 −

SQLite SELECT statement is used to fetch the data from a SQLite database table which returns data in the form of a result table. These result tables are also called result sets.

Syntax

Following is the basic syntax of SQLite SELECT statement.

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2 … are the fields of a table, whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax −

SELECT * FROM table_name;

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 to fetch and display all these records using SELECT statement. Here, the first three commands have been used to set a properly formatted output.

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

Finally, you will get 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
7           James       24          Houston     10000.0

If you want to fetch only selected fields of COMPANY table, then use the following query −

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

The above query will produce the following result.

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

Setting Output Column Width

Sometimes, you will face a problem related to the truncated output in case of .mode column which happens because of default width of the column to be displayed. What you can do is, you can set column displayable column width using .width num, num…. command as follows −

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

The above .width command sets the first column width to 10, the second column width to 20 and the third column width to 10. Finally, the above SELECT statement will give 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
7           James                 24          Houston     10000.0

Schema Information

As all the dot commands are available at SQLite prompt, hence while programming with SQLite, you will use the following SELECT statement with sqlite_master table to list down all the tables created in your database.

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

Assuming you have only COMPANY table in your testDB.db, this will produce the following result.

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

You can list down complete information about COMPANY table as follows −

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

Assuming you have only COMPANY table in your testDB.db, this will produce the following result.

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

SQLite INSERT INTO Statement is used to add new rows of data into a table in the database.

Syntax

Following are the two basic syntaxes of INSERT INTO statement.

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

Here, column1, column2,…columnN are the names of the columns in the table into which you want to insert data.

You may not need to specify the column(s) name in the SQLite query if you are adding values for all the columns of the table. However, make sure the order of the values is in the same order as the columns in the table. The SQLite INSERT INTO syntax would be as follows −

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

Example

Consider you already have created COMPANY table in your testDB.db as follows −

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

Now, the following statements would create six records in COMPANY table.

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

You can create a record in COMPANY table using the second syntax as follows −

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

All the above statements would create the following records in COMPANY table. In the next chapter, you will learn how to display all these records from a table.

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

Populate One Table Using Another Table

You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate the first table. Here is the syntax −

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

For now, you can skip the above statement. First, let”s learn SELECT and WHERE clauses which will be covered in subsequent chapters.

SQLite DROP TABLE statement is used to remove a table definition and all associated data, indexes, triggers, constraints, and permission specifications for that table.

You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever.

Syntax

Following is the basic syntax of DROP TABLE statement. You can optionally specify the database name along with table name as follows −

DROP TABLE database_name.table_name;

Example

Let us first verify COMPANY table and then we will delete it from the database.

sqlite>.tables
COMPANY       test.COMPANY

This means COMPANY table is available in the database, so let us drop it as follows −

sqlite>DROP TABLE COMPANY;
sqlite>

Now, if you try .TABLES command, then you will not find COMPANY table anymore.

sqlite>.tables
sqlite>

It shows nothing which means the table from your database has been dropped successfully.

SQLite DETACH DATABASE statement is used to detach and dissociate a named database from a database connection which was previously attached using ATTACH statement. If the same database file has been attached with multiple aliases, then DETACH command will disconnect only the given name and rest of the attachment will still continue. You cannot detach the main or temp databases.

If the database is an in-memory or temporary database, the database will be destroyed and the contents will be lost.

Syntax

Following is the basic syntax of SQLite DETACH DATABASE ”Alias-Name” statement.

DETACH DATABASE ''Alias-Name

Here, ”Alias-Name” is the same alias, which you had used while attaching the database using ATTACH statement.

Example

Consider you have a database, which you created in the previous chapter and attached it with ”test” and ”currentDB” as we can see using .database command.

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

Let”s try to detach ”currentDB” from testDB.db using the following command.

sqlite> DETACH DATABASE ''currentDB

Now, if you will check the current attachment, you will find that testDB.db is still connected with ”test” and ”main”.

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