SELECT Statement


 

SQLite: SELECT Statement

This SQLite tutorial explains how to use the SQLite SELECT statement with syntax and examples.

Note: That a table is a container that resides in the database where the data is stored.

 

Description:

The SQLite SELECT statement is used to retrieve records from one or more tables. The result is stored in a result table, called the result-set.

 

Syntax:

Note: SQLite is not case sensitive. SELECT is the same as select.

However, the full syntax for the SQLite SELECT statement is:

Note: Some database systems require a semicolon at the end of each SQLite statement. Semicolon is the standard way to separate each SQLite statement in database systems that allow more than one SQLite statement to be executed.

 

Parameters or Arguments:
Note: later in this tutorial we will learn in detail description of these parameters.

ALL

Optional. If specified, it returns all matching rows.

DISTINCT

Optional. If specified, it removes duplicates from the result set. Learn more about the DISTINCT clause.

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions

The conditions that must be met for the records to be selected.

GROUP BY

Optional. It collects data across multiple records and groups the results by one or more columns. Learn more about the GROUP BY clause.

HAVING

Optional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE. Learn more about the HAVING clause.

ORDER BY

Optional. It is used to sort the records in your result set. Learn more about the ORDER BY clause.

LIMIT

Optional. If LIMIT is provided, it controls the maximum number of records to retrieve. At most, the number of records specified by number_rows will be returned in the result set. The first row returned by LIMIT will be determined by offset_value.

 

Examples:

  • The following SQLite statement selects all the columns from the “Employee” table:

    id name position hire_date
    100 chiru developer 1/1/2014
    101 arunesh manager 1/1/2012
    102 naga developer 1/1/2013
    103 nithya developer 6/1/2013
    104 jaya developer 1/1/2014
    105 uma designer 1/1/2013
    106 mathu research analyst 1/1/2013
    107 anitha research analyst 1/1/2014
    108 kannan 6/1/2013
    109 deepak
    110 kalyani

    Note: The asterisk (*) is a quick way of selecting all columns!

  • The following SQLite statement selects the “Name” columns from the “Employee” table:

    name
    chiru
    arunesh
    naga
    nithya
    jaya
    uma
    mathu
    anitha
    kannan
    deepak
  • The following SQLite statement selects the “Name” and “Position” columns from the “Employee” table:

    name position
    chiru developer
    arunesh manager
    naga Team Lead
    nithya developer
    jaya developer
    uma designer
    mathu research analyst
    anitha research analyst
    kannan
    deepak