Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Insert Sqlite Explain ❯

SQLite Select Statement

The SELECT statement in SQLite is used to retrieve data from an SQLite database table, which is returned in the form of a result table. These result tables are also known as result sets.

Syntax

The basic syntax of the SELECT statement in SQLite is as follows:

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2... are the fields of the table whose values you want to retrieve. If you want to retrieve all available fields, you can use the following syntax:

SELECT * FROM table_name;

Example

Suppose the COMPANY table has 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

Below is an example that uses the SELECT statement to retrieve and display all these records. The first two commands are used to set the output in the correct format.

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 only want to retrieve specific fields from the COMPANY table, 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, the default width of the columns in .mode column causes the output to be truncated. In such cases, you can use the .width num, num.... command to set the display width of the columns, as shown below:

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

The .width command above sets the width of the first column to 10, the second column to 20, and the third column to 10. Therefore, the above SELECT statement will yield 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

Since all dot commands are only available in the SQLite prompt, when you program with SQLite, you need to use the following SELECT statement with the sqlite_master table to list all tables created in the database:

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

Assuming there is only one COMPANY table already existing in testDB.db, the following result will be produced:

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

You can list the complete information about the COMPANY table as follows:

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

Assuming there is only one COMPANY table already existing in testDB.db, the following result will be produced:

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