ZetCode

SQLite SELECT statement

last modified July 6, 2020

This part of the SQLite tutorial covers SQLite's implementation of the SELECT statement in detail.

SQLite retrieve all data

The following SQL statement is one of the most common ones. It is also one of the most expensive ones.

sqlite> SELECT * FROM Cars;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600 

Here we retrieve all data from the Cars table.

SQLite select specific columns

We can use the SELECT statement to retrieve specific columns. The column names follow the SELECT word.

sqlite> SELECT Name, Price FROM Cars;
Name        Price     
----------  ----------
Audi        52642     
Mercedes    57127     
Skoda       9000      
Volvo       29000     
Bentley     350000    
Citroen     21000     
Hummer      41400     
Volkswagen  21600    

We retrieve the Name and the Price columns. The column names are separated by commas.

SQLite rename column names

We can rename the column names of the returned result set. For this, we use the AS clause.

sqlite> SELECT Name, Price AS 'Price of car' FROM Cars;
Name        Price of car
----------  ------------
Audi        52642       
Mercedes    57127       
Skoda       9000        
Volvo       29000       
Bentley     350000      
Citroen     21000       
Hummer      41400       
Volkswagen  21600  

With the above SQL statement, we rename the Price column to Price of car.

SQLite limit data output

As we mentioned above, retrieving all data is expensive when dealing with large amounts of data. We can use the LIMIT clause to limit the data amount returned by the statement.

sqlite> SELECT * FROM Cars LIMIT 4;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000    

The LIMIT clause limits the number of rows returned to 4.

sqlite> SELECT * FROM Cars LIMIT 2, 4;
Id          Name        Price     
----------  ----------  ----------
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000  

This statement selects four rows skipping the first two rows.

The OFFSET clause following LIMIT specifies how many rows to skip at the beginning of the result set. This is an alternative solution to the previous one.

sqlite> SELECT * FROM Cars LIMIT 4 OFFSET 2;
Id          Name        Price     
----------  ----------  ----------
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000  

Here we select all data from max four rows, and we begin with the third row. The OFFSET clause skips the first two rows.

SQLite order data

We use the ORDER BY clause to sort the returned data set. The ORDER BY clause is followed by the column on which we do the sorting. The ASC keyword sorts the data in ascending order, the DESC in descending order.

sqlite> SELECT * FROM Cars ORDER BY Price;
Id          Name        Price     
----------  ----------  ----------
3           Skoda       9000      
6           Citroen     21000     
8           Volkswagen  21600     
4           Volvo       29000     
7           Hummer      41400     
1           Audi        52642     
2           Mercedes    57127     
5           Bentley     350000  

The default sorting is in ascending order. The ASC clause can be omitted.

sqlite> SELECT Name, Price FROM Cars ORDER BY Price DESC;
Name        Price     
----------  ----------
Bentley     350000    
Mercedes    57127     
Audi        52642     
Hummer      41400     
Volvo       29000     
Volkswagen  21600     
Citroen     21000     
Skoda       9000  

In the above SQL statement, we select Name and Price columns from the Cars table and sort it by the Price of the cars in descending order. So the most expensive cars come first.

SQLite order data by more columns

It is possible to order data by more than one column.

sqlite> INSERT INTO Cars(Name, Price) VALUES('Fiat', 9000);
sqlite> INSERT INTO Cars(Name, Price) VALUES('Tatra', 9000);

For this example, we add two additional cars with 9000 price.

sqlite> SELECT * FROM Cars ORDER BY Price, Name DESC;
Id          Name        Price     
----------  ----------  ----------
10          Tatra       9000      
3           Skoda       9000      
9           Fiat        9000      
6           Citroen     21000     
8           Volkswagen  21600     
4           Volvo       29000     
7           Hummer      41400     
1           Audi        52642     
2           Mercedes    57127     
5           Bentley     350000  

In the statement, we sort the data by two columns: price and name. The name is in descending order.

SQLite select specific rows with WHERE

The next set of examples uses the Orders table.

sqlite> SELECT * FROM Orders;
Id          OrderPrice  Customer  
----------  ----------  ----------
1           1200        Williamson
2           200         Robertson 
3           40          Robertson 
4           1640        Smith     
5           100         Robertson 
6           50          Williamson
7           150         Smith     
8           250         Smith     
9           840         Brown     
10          440         Black     
11          20          Brown  

Here we see all the data from the Orders table.

Next, we want to select a specific row.

sqlite> SELECT * FROM Orders WHERE Id=6;
Id          OrderPrice  Customer  
----------  ----------  ----------
6           50          Williamson

The above SQL statement selects a row that has Id 6.

sqlite> SELECT * FROM Orders WHERE Customer="Smith";
Id          OrderPrice  Customer  
----------  ----------  ----------
4           1640        Smith     
7           150         Smith     
8           250         Smith     

The above SQL statement selects all orders from the Smith customer.

We can use the LIKE clause to look for a specific pattern in the data.

sqlite> SELECT * FROM Orders WHERE Customer LIKE 'B%';
Id          OrderPrice  Customer  
----------  ----------  ----------
9           840         Brown     
10          440         Black     
11          20          Brown 

This SQL statement selects all orders from customers whose names begin with letter B.

SQLite remove duplicate items

The DISTINCT clause is used to select only unique items from the result set.

sqlite> SELECT Customer FROM Orders WHERE Customer LIKE 'B%';
Customer  
----------
Brown     
Black     
Brown  

This time we have selected customers whose names begin with B. We can see that Brown appears twice. To remove duplicates, we use the DISTINCT keyword.

sqlite> SELECT DISTINCT Customer FROM Orders WHERE Customer LIKE 'B%';
Customer  
----------
Black     
Brown   

This is the correct solution.

SQLite group data

The GROUP BY clause is used to combine database records with identical values into a single record. It is often used with the aggregate functions.

Say we wanted to find out the sum of each customers' orders.

sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;
Total       Customer  
----------  ----------
440         Black     
860         Brown     
340         Robertson 
2040        Smith     
1250        Williamson

The sum function returns the total sum of a numeric column. The GROUP BY clause divides the total sum among the customers. So we can see that Black has ordered items for 440 or Smith for 2040.

We cannot use the WHERE clause when aggregate functions are used. We use the HAVING clause instead.

sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders 
        GROUP BY Customer HAVING sum(OrderPrice)>1000;
Total       Customer  
----------  ----------
2040        Smith     
1250        Williamson

The above SQL statement selects customers whose total orders where greater than 1000 units.

In this part of the SQLite tutorial, we described the SQL SELECT statement in more detail.