SQLite - SELECT Query

Advertisements


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

Syntax:

The basic syntax of SQLite SELECT statement is as follows:

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 following syntax:

SELECT * FROM table_name;

Example:

Consider COMPANY table is having 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, first three commands have been used to set 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;

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 truncated output in case of .mode column which happens because of default width of the column to be displayed. What you can do is that you can set column displayable column width using .width num, num.... command as follows:

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

Above .width command sets first column width to 10, second column width to 20 and third column width to 10. So finally 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:

Because all the dot commads are available at SQLite prompt only, so while doing your programming with SQLite, you will use the following statement to list down all the tables created in your database using the following SELECT statement with sqlite_master table:

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
)


Advertisements
Advertisements