SQLite - SELECT Query

SQLite - The SELECT Query

The SELECT statement is the cornerstone of retrieving data from a SQLite database. It returns data in a tabular format called a ‘result set’.

Basic Syntax

The most basic form of a SELECT statement retrieves specific columns from a single table.

SELECT column1, column2, ... FROM table_name;

To retrieve all columns, you can use the wildcard * character. While convenient for exploration, it’s best practice in applications to specify only the columns you need.

SELECT * FROM table_name;

Filtering and Sorting Data

You can refine your results using various clauses:

• WHERE: Filters records based on a specific condition.
• ORDER BY: Sorts the result set in ascending (ASC) or descending (DESC) order.
• LIMIT: Restricts the number of rows returned. Useful for pagination.
• OFFSET: Skips a specified number of rows before beginning to return rows.

Example

Let’s assume our EMPLOYEES table has the following data:

id | first_name | last_name | age | address    | salary
---|------------|-----------|-----|------------|---------
1  | Paul       | Smith     | 32  | California | 95000.0
2  | Allen      | Jones     | 25  | Texas      | 75000.0
3  | Teddy      | Williams  | 23  | Norway     | 50000.0
4  | Mark       | Brown     | 25  | Richmond   | 85000.0
5  | David      | Miller    | 27  | Texas      | 120000.0

To get the first name, last name, and salary of all employees, sorted by salary in descending order:

sqlite> SELECT first_name, last_name, salary
   ...> FROM EMPLOYEES
   ...> ORDER BY salary DESC;

This produces the following result:

first_name | last_name | salary
-----------|-----------|---------
David      | Miller    | 120000.0
Paul       | Smith     | 95000.0
Mark       | Brown     | 85000.0
Allen      | Jones     | 75000.0
Teddy      | Williams  | 50000.0

Now, let’s select employees who are 25 years old and live in Texas, limiting the result to just one record:

sqlite> SELECT * FROM EMPLOYEES
   ...> WHERE age = 25 AND address = 'Texas'
   ...> LIMIT 1;

This query will return only the record for Allen Jones.

Working with the sqlite3 CLI

The SQLite command-line interface provides helpful ‘dot-commands’ for formatting output. These are not part of the SQL language itself.

sqlite> .headers on   -- Show column headers
sqlite> .mode column  -- Display results in columns
sqlite> .width 10 10 8 -- Set column widths for better alignment

Running the first query again with these settings would produce a neatly formatted table.

Inspecting the Database Schema

To see the structure of your database, you can query the sqlite_schema table. This is the modern, standard way to inspect schema objects.

-- List all tables in the database
sqlite> SELECT name FROM sqlite_schema WHERE type = 'table';

name
----------
DEPARTMENTS
EMPLOYEES

You can retrieve the original CREATE statement for any object.

sqlite> SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = 'EMPLOYEES';