Basic SQL Select Statements in SQLite

Display All Columns for a Table

 
SELECT * FROM person;

Limit Rows Returned

 
SELECT id,
       firstname,
       lastname,
       gender,
       age,
       active_flag
  FROM person
  limit 5;

Get Table Row Count

 
select count(*) from person;

Aliasing Column Names

 
SELECT id,
       firstname as "Last Name",
       lastname as "First Name"
  FROM person;

Row Restriction

 
SELECT id,
       firstname,
       lastname,
       gender,
       age,
       active_flag
  FROM person
  WHERE age > 30
    AND gender = 'M';

 
SELECT id,
       firstname,
       lastname,
       gender,
       age,
       active_flag
  FROM person
  WHERE age > 30
    OR gender = 'M';

Note: For the query with the 'OR' rows are returned for rows where the age column is empty. This means when writing to a numeric column that you need to enter zero or you can filter your query as follows to exclude any numeric column data that does not contain a value as follows.

 
  WHERE (age > 30 AND age <> '' AND age IS NOT NULL)
    OR  gender = 'M'

Sorting Data

 
SELECT firstname,
       lastname,
       gender,
       age,
       active_flag
  FROM person
 WHERE (age > 30 AND 
        age <> '' AND 
        age IS NOT NULL) 
 ORDER BY lastname,age desc;

Note: desc = descending order, asc = ascending order. Ascending is implied if not specified.


Schema and Data use on this page