SELECT query in SQLite

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:

3+1
4

This approach is valid for several expressions. Each expression must be separated by a comma (“,”). See the query example below. 

SELECT 3+1,3-1;

This returns the following query result:

3+13-1
42

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.

SELECT
       product,
       COUNT(id)
FROM 
      customer
GROUP BY
      product
ORDER BY COUNT(id) DESC;

The ORDER BY clause sorts the rows returned by the total amount of customers per product, in descending order.

Related content

You can find more content related to SQLite on our blog. Here are some exemplary contributions that might be of interest to you: