This article provides an introduction to the SELECT query in SQLite. You can use this article as a step by step tutorial for learing SQLite. The SELECT query in SQLite is the most basic query that you need to learn.
Basics of SELECT query in SQLite
The query below is a simple example of a SELECT query.
SELECT 3 + 1;
Above SELECT query will return the following result:
This approach is valid for several expressions. Each expression must be separated by a comma (“,”). See the query example below.
This returns the following query result:
Having gone through these two simple examples of a SELECT query in SQLite I will now show how to query data from a table in a database
Query data from SQLite database table with SELECT
If we assume database that contians a customers table then we could query data from that table using a SELECT query. The SELECT query could look like this:
SELECT * FROM customer;
This will return all data contained by the customers table. Here is another query example:
SELECT * FROM customer WHERE orderqty > 100;
This will return all rows in the customers table for which the orderqty is greater than 100. Another example:
SELECT DISTINCT id FROM customer WHERE orderqty > 100 and rating > 3.1;
This will return unqiue entries of the id column for which orderqty was greater than 100 and the rating was creater than 3.1.
SQLite GROUP BY clause of SELECT statement
The SELECT query in SQLite can be combined with the optional GROUP BY clause. The GROUP BY clause groups table or column rows based on specified columns and their entries. This is helpful when applying functions. Below is a SQLite example:
SELECT product, COUNT(id) FROM customer GROUP BY product;
Above query groups customers by product and returns the product and the number of customers (identified by the ID) by product.
ORDER BY clause for SELECT statement
Below I add another clause to above SELECT statement: ORDER BY.
The ORDER BY clause sorts the rows returned by the total amount of customers per product, in descending order.
You can find more content related to SQLite on our blog. Here are some exemplary contributions that might be of interest to you: