SQLite - SELECT Statement

SQLite – SELECT Statement

In SQLite the SELECT Statement can be used to fetch data from a database.

The result we get after using SELECT Statement is stored in the form of result table, also known as result-set.

SQLite SELECT Syntax

The Select statement is used to get data which matches the specified criteria. Here is the basic syntax:

SELECT column_name1,column_name1
FROM table_name;

SELECT * FROM table_name;

SELECT Column Example:

So let’s consider we have created SCHOOL database. And we have created a STUDENTS table in this database.

The STUDENTS table have five columns (ID, NAME, SURNAME, AGE, ADDRESS).

The STUDENTS table also have some data inside it as shown below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Essen
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal

The following SQLite statement selects the “NAME” and “SURNAME” columns from the STUDENTS table:

sqlite> .header on
sqlite> .mode column
sqlite> SELECT NAME, SURNAME FROM STUDENTS;

In the reply of executing the above statement, we will get the following result

NAME        SURNAME 
----------  ---------- 
Mark        Osaka 
Tom         white
Patric      Rossman
Noor        Khan 
Julia       Tesar 
Tim         Netten 
John        Mevric

SELECT Column Example:

The following SQLite statement selects all the columns from the STUDENTS table:

sqlite> SELECT * FROM STUDENTS;

In the reply of executing the above statement, we will get the following result

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Essen
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal

“.header on” command

You may have noticed the we have use “.header on” and “.mode column” commands before the SELECT Statement

“.header on” command is used to display the output table header

“.mode column” command

“.mode on” command is used to select mode for the output table

Command	Description
.show	Used to display current settings for various parameters
.databases	Gives database names and files
.quit	Quit sqlite3 program
.tables	Show current tables
.schema	Display schema of table
.header	Display or hide the output table header
.mode	Select mode for the output table
.dump	Dump database in SQL text format