Querying Data with SELECT in SQLite: The Complete DQL Guide

Every useful thing you do with a database starts with a question: “Who earns the most?” “How many employees are in each city?” “What is the average salary per department?” SQL answers all of these with the SELECT statement. This page walks you through every major SELECT feature, from the simplest column retrieval to grouped aggregations with HAVING, using a consistent employees database throughout.

The Sample Data

All examples use these two tables. Set them up using the SQLite Setup page if you have not already. employee (selected rows shown)

employee_id	employee_name	salary	city	department_id
101	Rahul Sharma	65000	Hyderabad	1
102	Priya Reddy	72000	Bengaluru	1
103	Arjun Kumar	58000	Chennai	1
104	Sneha Patel	81000	Pune	1
105	Anitha Rao	56000	Hyderabad	2
106	Karthik Nair	61000	Kochi	2
107	Ajay Verma	50000	Hyderabad	3
108	Neha Kapoor	64000	Mumbai	3
109	Harsha Vardhan	76000	Hyderabad	4
110	Gopal Krishna	53000	Vizag	NULL

SELECT — Retrieve Columns

SELECT tells SQL which columns to return. FROM tells it which table to read.

All Columns

SELECT *
FROM employee;

* is a wildcard meaning “every column”. Use it for quick exploration, but name columns explicitly in production code so schema changes do not silently break your queries.

Specific Columns

SELECT employee_name, salary
FROM employee;

Column Aliases with AS

Give a column a temporary display name using AS. Aliases make output readable and are required when you rename calculated expressions.

SELECT
    employee_name  AS name,
    salary         AS monthly_salary,
    salary * 12    AS annual_salary
FROM employee;

name	monthly_salary	annual_salary
Rahul Sharma	65000	780000
Priya Reddy	72000	864000

You cannot use an alias defined in SELECT inside a WHERE clause. SQL executes WHERE before SELECT, so the alias does not yet exist at that point. Use the original expression or a subquery instead.

DISTINCT — Remove Duplicates

DISTINCT eliminates duplicate values from the result.

-- All cities (with duplicates)
SELECT city FROM employee;

-- Unique cities only
SELECT DISTINCT city FROM employee;

SELECT DISTINCT department_id
FROM employee
WHERE department_id IS NOT NULL;

WHERE — Filter Rows

WHERE evaluates a condition for each row and includes only the rows where the condition is TRUE.

Comparison Operators

SELECT * FROM employee WHERE salary > 60000;
SELECT * FROM employee WHERE city = 'Hyderabad';
SELECT * FROM employee WHERE salary != 50000;
SELECT * FROM employee WHERE salary >= 70000;

Operator	Meaning
`=`	Equal to
`!=` or `<>`	Not equal to
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal
`<=`	Less than or equal

Logical Operators: AND, OR, NOT

Combine multiple conditions with logical operators.

-- AND: both conditions must be true
SELECT * FROM employee
WHERE city = 'Hyderabad'
AND   salary > 60000;

-- OR: at least one condition must be true
SELECT * FROM employee
WHERE city = 'Hyderabad'
OR    city = 'Bengaluru';

-- NOT: inverts the condition
SELECT * FROM employee
WHERE NOT city = 'Hyderabad';

BETWEEN — Range Check

SELECT employee_name, salary
FROM employee
WHERE salary BETWEEN 55000 AND 70000;

BETWEEN is inclusive — both boundary values are included.

IN — List Membership

SELECT * FROM employee
WHERE department_id IN (1, 3);

-- Equivalent using OR:
SELECT * FROM employee
WHERE department_id = 1
OR    department_id = 3;

IN is cleaner and often faster when matching against a list of values.

LIKE — Pattern Matching

LIKE searches text columns for patterns. Two wildcards are available:

Wildcard	Matches
`%`	Any sequence of zero or more characters
`_`	Exactly one character

SELECT * FROM employee WHERE employee_name LIKE 'A%';    -- starts with A
SELECT * FROM employee WHERE employee_name LIKE '%a';    -- ends with a
SELECT * FROM employee WHERE employee_name LIKE '%ar%';  -- contains "ar"
SELECT * FROM employee WHERE employee_name LIKE 'K____'; -- K + exactly 4 chars

IS NULL / IS NOT NULL

NULL means “unknown value”. You cannot test for NULL using =; you must use IS NULL.

-- Employees with no department assigned
SELECT employee_name
FROM employee
WHERE department_id IS NULL;

-- Employees who have a department
SELECT employee_name
FROM employee
WHERE department_id IS NOT NULL;

Practice: Filter employees

Write a query that returns the name and salary of employees from Hyderabad who earn more than ₹55,000.

Solution

SELECT employee_name, salary
FROM employee
WHERE city = 'Hyderabad'
AND   salary > 55000;

ORDER BY — Sort Results

ORDER BY sorts the result set. Default order is ascending (ASC). Use DESC for descending.

-- Ascending (lowest salary first)
SELECT employee_name, salary
FROM employee
ORDER BY salary;

-- Descending (highest salary first)
SELECT employee_name, salary
FROM employee
ORDER BY salary DESC;

-- Sort by department, then by salary descending within each department
SELECT employee_name, department_id, salary
FROM employee
ORDER BY department_id ASC, salary DESC;

LIMIT and OFFSET — Pagination

LIMIT caps the number of rows returned. OFFSET skips a given number of rows before starting to return results. Together they power pagination.

-- Top 3 highest-paid employees
SELECT employee_name, salary
FROM employee
ORDER BY salary DESC
LIMIT 3;

-- Page 2: rows 4–6 (skip the first 3)
SELECT employee_name, salary
FROM employee
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

Always pair LIMIT with ORDER BY. Without ordering, the database returns rows in an undefined sequence and your “page 2” might overlap with “page 1”.

Aggregate Functions

Aggregate functions collapse many rows into a single value. They are most useful when combined with GROUP BY.

Function	What it computes
`COUNT(*)`	Total number of rows
`COUNT(column)`	Rows where the column is not NULL
`SUM(column)`	Total of all values
`AVG(column)`	Arithmetic mean
`MIN(column)`	Smallest value
`MAX(column)`	Largest value

SELECT COUNT(*)         AS total_employees  FROM employee;
SELECT AVG(salary)      AS avg_salary        FROM employee;
SELECT MAX(salary)      AS highest_salary    FROM employee;
SELECT MIN(salary)      AS lowest_salary     FROM employee;
SELECT SUM(salary)      AS total_payroll     FROM employee;

-- Count only employees who have a department assigned
SELECT COUNT(department_id) AS employees_with_dept FROM employee;

Practice: Salary statistics

Write a query that returns the average, minimum, and maximum salary for all employees in one result row.

Solution

SELECT
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employee;

GROUP BY — Aggregate Per Group

GROUP BY splits the rows into groups and applies aggregate functions to each group independently.

-- Count employees in each city
SELECT city, COUNT(*) AS headcount
FROM employee
GROUP BY city
ORDER BY headcount DESC;

-- Average salary per department
SELECT
    department_id,
    COUNT(*)       AS headcount,
    AVG(salary)    AS avg_salary,
    MAX(salary)    AS top_salary
FROM employee
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY avg_salary DESC;

Every column in the SELECT clause must either be listed in GROUP BY or wrapped in an aggregate function. You cannot select a non-grouped, non-aggregated column.

HAVING — Filter Groups

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Use HAVING whenever your filter condition involves an aggregate function.

-- Departments where the average salary exceeds ₹60,000
SELECT
    department_id,
    AVG(salary) AS avg_salary
FROM employee
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > 60000;

-- Cities with more than one employee
SELECT city, COUNT(*) AS headcount
FROM employee
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY headcount DESC;

WHERE vs. HAVING — Quick Rule

	WHERE	HAVING
Filters	Individual rows	Groups (after GROUP BY)
Runs	Before GROUP BY	After GROUP BY
Can use aggregates	❌ No	✅ Yes
Can use regular columns	✅ Yes	✅ Yes

SQL Execution Order

You write SQL in one order, but the database runs each clause in a different sequence. Understanding this explains many confusing behaviours (like why you cannot use SELECT aliases in WHERE).

  Clause written    →    Executed in this order
  ─────────────────────────────────────────────
  SELECT                  1. FROM
  FROM              →     2. WHERE
  WHERE                   3. GROUP BY
  GROUP BY                4. HAVING
  HAVING                  5. SELECT
  ORDER BY                6. ORDER BY
  LIMIT                   7. LIMIT / OFFSET

Full Query Example

This query uses every clause covered on this page. Read it clause by clause following the execution order above.

SELECT
    city,
    COUNT(*)       AS headcount,
    AVG(salary)    AS avg_salary,
    MAX(salary)    AS top_salary
FROM employee
WHERE department_id IS NOT NULL        -- exclude unassigned employees
GROUP BY city
HAVING COUNT(*) >= 2                   -- only cities with 2+ employees
ORDER BY avg_salary DESC
LIMIT 5;

Practice: Departmental payroll report

Write a query that returns, for each department (excluding NULL), the department ID, the number of employees, and the total payroll — but only for departments whose total payroll exceeds ₹100,000. Sort by total payroll descending.

Solution

SELECT
    department_id,
    COUNT(*)    AS headcount,
    SUM(salary) AS total_payroll
FROM employee
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING SUM(salary) > 100000
ORDER BY total_payroll DESC;

Next: SQL JOINs

Learn how to combine rows from multiple tables using INNER JOIN, LEFT JOIN, and more to answer cross-table questions.

Project Development

SQL & Databases

Data Analysis

Querying Data with SELECT in SQLite: The Complete DQL Guide

The Sample Data

SELECT — Retrieve Columns

All Columns

Specific Columns

Column Aliases with AS

DISTINCT — Remove Duplicates

WHERE — Filter Rows

Comparison Operators

Logical Operators: AND, OR, NOT

BETWEEN — Range Check

IN — List Membership

LIKE — Pattern Matching

IS NULL / IS NOT NULL

Practice: Filter employees

ORDER BY — Sort Results

Aggregate Functions

Practice: Salary statistics

GROUP BY — Aggregate Per Group

HAVING — Filter Groups

WHERE vs. HAVING — Quick Rule

SQL Execution Order

Full Query Example

Practice: Departmental payroll report

Next: SQL JOINs

​The Sample Data

​SELECT — Retrieve Columns

​All Columns

​Specific Columns

​Column Aliases with AS

​DISTINCT — Remove Duplicates

​WHERE — Filter Rows

​Comparison Operators

​Logical Operators: AND, OR, NOT

​BETWEEN — Range Check

​IN — List Membership

​LIKE — Pattern Matching

​IS NULL / IS NOT NULL

​Practice: Filter employees

​ORDER BY — Sort Results

​LIMIT and OFFSET — Pagination

​Aggregate Functions

​Practice: Salary statistics

​GROUP BY — Aggregate Per Group

​HAVING — Filter Groups

​WHERE vs. HAVING — Quick Rule

​SQL Execution Order

​Full Query Example

​Practice: Departmental payroll report

Next: SQL JOINs

The Sample Data

SELECT — Retrieve Columns

All Columns

Specific Columns

Column Aliases with AS

DISTINCT — Remove Duplicates

WHERE — Filter Rows

Comparison Operators

Logical Operators: AND, OR, NOT

BETWEEN — Range Check

IN — List Membership

LIKE — Pattern Matching

IS NULL / IS NOT NULL

Practice: Filter employees

ORDER BY — Sort Results

LIMIT and OFFSET — Pagination

Aggregate Functions

Practice: Salary statistics

GROUP BY — Aggregate Per Group

HAVING — Filter Groups

WHERE vs. HAVING — Quick Rule

SQL Execution Order

Full Query Example

Practice: Departmental payroll report