SQLite  - SELECT Query 


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 expressions

FROM tables

WHERE conditions;

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

SELECT [ ALL | DISTINCT ]

expressions

FROM tables

WHERE conditions

[ GROUP BY expressions ]

[ HAVING condition ]

[ ORDER BY expression [ ASC | DESC ] ]

[ LIMIT number_rows OFFSET offset_value ];

 

Parameters or Arguments

 

ALL : Optional. If specified, it returns all matching rows.

DISTINCT : Optional. If specified, it removes duplicates from the result set.

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.

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.

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.

 

Example - Select all fields from one table

 

SELECT *

FROM employees

WHERE employee_id< 150

ORDER BY last_name ASC;

In this example, we've used * to signify that we wish to select all fields from the employees table where the employee_id is less than 150. The result set is sorted by last_name in ascending order.

 

Example - Select individual fields from one table

 


SELECT employee_id, last_name, first_name

FROM employees

WHERE employee_id < 150

ORDER BY last_name ASC, employee_id DESC;

In this example we fetch individual fields from the table, as opposed to all fields from the table. Here we would return only the employee_idlast_name, and first_name fields from the employees table where the employee_id is less than 150. The results are sorted by last_name in ascending order and then employee_id in descending order.

 

Example - Select fields from multiple tables

 


SELECT employees.employee_id, employees.last_name, positions.title

FROM employees

INNER JOIN positions

ON employees.employee_id = positions.employee_id

ORDER BY positions.title;

In this example we joins two tables together to gives us a result set that displays the employee_id, last_name, and title fields where the employee_id value matches in both the employees and positions table. The results are sorted by title in ascending order.

Leave Comment