SQLite SELECT Statement

SQLite'sSELECTSELECT statement is used to fetch data from SQLite database tables, returning data in the form of result tables. These result tables are also called result sets.

Syntax

The basic syntax of SQLite's SELECT statement is as follows:

SELECT column1, column2, columnN FROM table_name;

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

SELECT * FROM table_name;

Examples

Assume 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

The following is an example that uses the SELECT statement to fetch and display all these records. Here, the first two commands are used to set up the correctly 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 only want to fetch specified 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 the Width of Output Columns

Sometimes, due to the default width of the columns to be displayed.mode column, in this case, the output is truncated. At this point, you can use.width num, num....command to set the width of the display columns, as shown below:

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

The above.widthcommand 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 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
7           James                 24          Houston     10000.0

Schema Information

Because all thedot commandsare only available in the SQLite prompt, so when you are programming with SQLite, you should use the following with thesqlite_mastertable's SELECT statement to list all tables created in the database:

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

Assuming that the only COMPANY table already exists in testDB.db, the following result will be produced:

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

You can list the complete information about the COMPANY table, as shown below:

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

Assuming that the only COMPANY table already exists 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
)