Home > DeveloperSection > Articles > SQLite - SELECT Query

SQLite - SELECT Query


SQLite Sqlite  Sqlite3 
Ratings:
0 Comment(s)
 1176  View(s)
Rate this:

 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.


Don't want to miss updates? Please click the below button!

Follow MindStick