Basic Queries

Basic Queries

Imagine walking into a library and wanting to find a book. You wouldn't take every book home — instead, you'd tell the librarian: "I'd like to find science fiction novels published after 2020, sorted by publication date from newest to oldest, and show me the first 5." This process corresponds to the core steps of an SQL query: selecting data, filtering conditions, sorting, and limiting quantity.

In the previous lesson, we learned how to create databases and tables, and insert data. This lesson will learn how to precisely query the data we need from a database.

Core Concepts

1. SELECT Statement

SELECT is the most commonly used statement in SQL, used to retrieve data from database tables. Its basic structure is:

SELECT column1, column2, ...
FROM table_name;

• After SELECT, specify the columns to query
• After FROM, specify which table to query from
• Each SQL statement ends with a semicolon ;

2. Querying Specific Columns vs. All Columns

Query specific columns (recommended):

SELECT name, salary FROM employees;

Query all columns:

SELECT * FROM employees;

3. WHERE Condition Filtering

The WHERE clause is used to filter records, returning only rows that meet the conditions:

SELECT column1, column2
FROM table_name
WHERE condition;

4. Comparison Operators

5. Logical Operators: AND / OR

• AND: All conditions must be true
• OR: At least one condition must be true

SELECT name, salary, department_id
FROM employees
WHERE salary > 50000 AND department_id = 1;

SELECT name, salary, department_id
FROM employees
WHERE department_id = 1 OR department_id = 2;

6. IN / BETWEEN / LIKE

IN — Match any value in a set:

SELECT name, department_id
FROM employees
WHERE department_id IN (1, 2, 3);

BETWEEN — Match values within a range (inclusive of boundaries):

SELECT name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 80000;

LIKE — Fuzzy string matching:

• % matches any number of characters (including zero)
• _ matches exactly one character

-- Employees whose names start with "J"
SELECT name FROM employees WHERE name LIKE 'J%';

-- Employees whose names are 4 characters long and start with "John"
SELECT name FROM employees WHERE name LIKE 'John_';

7. ORDER BY Sorting

ORDER BY is used to sort the result set:

• ASC: Ascending (default, can be omitted)
• DESC: Descending

-- Sort by salary from high to low
SELECT name, salary
FROM employees
ORDER BY salary DESC;

-- Sort by department ascending, then by salary descending
SELECT name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

8. LIMIT Row Limiting

LIMIT is used to restrict the number of returned records:

-- Return only the first 5 records
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Skip the first 5, return the next 5 (pagination)
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

Basic Syntax/Usage

Complete Structure of a Query Statement

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column [ASC|DESC]
LIMIT count [OFFSET offset];

The writing order of clauses must be strictly followed:

Common Techniques

-- Use aliases to make column names more readable
SELECT name AS employee_name, salary AS employee_salary
FROM employees;

-- Remove duplicate values
SELECT DISTINCT department_id
FROM employees;

-- Use expressions in SELECT
SELECT name, salary, salary * 12 AS annual_salary
FROM employees;

Examples

Example: Query Employees in a Specific Department

Requirement: Query all employee names and salaries in department ID 1, sorted by salary from high to low.

SELECT name, salary
FROM employees
WHERE department_id = 1
ORDER BY salary DESC;

Expected Output:

+----------+--------+
| name     | salary |
+----------+--------+
| John     |  85000 |
| Bob      |  62000 |
| Alice    |  55000 |
+----------+--------+

Explanation:

• WHERE department_id = 1 keeps only rows where department ID is 1
• ORDER BY salary DESC sorts by salary in descending order
• Results contain only the name and salary columns

Example: Multi-condition Query

Requirement: Query employees with salary between 60000 and 90000, and department ID is 1 or 2. Show name, department ID, and salary. Sort by department ID ascending, then salary descending.

SELECT name, department_id, salary
FROM employees
WHERE salary BETWEEN 60000 AND 90000
  AND department_id IN (1, 2)
ORDER BY department_id ASC, salary DESC;

Expected Output:

+----------+---------------+--------+
| name     | department_id | salary |
+----------+---------------+--------+
| John     |             1 |  85000 |
| Bob      |             1 |  62000 |
| Jane     |             2 |  75000 |
+----------+---------------+--------+

Explanation:

• BETWEEN 60000 AND 90000 filters the salary range
• IN (1, 2) filters departments
• AND combines the two conditions
• Sort by department ascending first, then by salary descending within the same department

Example: Fuzzy Query and Pagination

Requirement: Find employees whose names contain "J", sorted by hire date descending, showing only records 2 to 3.

SELECT name, hire_date, salary
FROM employees
WHERE name LIKE '%J%'
ORDER BY hire_date DESC
LIMIT 2 OFFSET 1;

Expected Output:

+--------+------------+--------+
| name   | hire_date  | salary |
+--------+------------+--------+
| Jane   | 2022-03-15 |  68000 |
| Julia  | 2021-08-20 |  52000 |
+--------+------------+--------+

Explanation:

• LIKE '%J%' matches records where the name contains "J" at any position
• ORDER BY hire_date DESC sorts by hire date from newest to oldest
• LIMIT 2 OFFSET 1 skips the first record, returns the 2nd and 3rd (i.e., page 2 with 2 records per page)

Scenarios

Scenario 1: Filter High-Salary Employees by Salary Range

HR needs to export a list of employees earning more than 70000, including name, department, and salary, sorted by salary from high to low.

SELECT e.name AS employee_name,
       d.name AS department_name,
       e.salary AS salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 70000
ORDER BY e.salary DESC;

Notes:

• Uses JOIN to associate the departments table to get department names (covered in detail in later lessons)
• e and d are table aliases that simplify writing
• Aliases use descriptive names for clearer output

Scenario 2: Paginated Employee List

A web application's employee list page shows 10 records per page. When the user clicks page 3, it needs to skip the first 20 records and display records 21-30.

-- Page 3, 10 records per page
SELECT id, name, department_id, salary
FROM employees
ORDER BY id ASC
LIMIT 10 OFFSET 20;

Pagination Formula: OFFSET = (page_number - 1) × records_per_page

❓ FAQ

Q: What's the difference between SELECT * and specifying column names? A: SELECT * returns all columns in the table, while specifying column names returns only the columns you need. In production environments, it's recommended to explicitly specify column names — queries are faster, more readable, and less prone to errors when table structures change.

Q: Can I use aliases in WHERE? A: No. SQL's execution order is FROM → WHERE → SELECT → ORDER BY. When WHERE executes, aliases haven't been generated yet. If you need to filter by an alias, use a subquery or HAVING (covered in later lessons).

Q: Does BETWEEN include boundary values? A: Yes. BETWEEN 10 AND 20 is equivalent to >= 10 AND <= 20. Both endpoint values are included in the results.

Q: Is LIKE case-sensitive? A: It depends on the database's collation. MySQL is case-insensitive by default, while PostgreSQL is case-sensitive by default. To enforce case sensitivity, PostgreSQL can use LIKE, and MySQL can use the BINARY keyword.

📖 Summary

This lesson covered the core skills of SQL querying:

1. SELECT — Specify columns to query; use * to query all columns
2. WHERE — Filter rows with conditions, supporting comparison operators (=, >, <, <>, >=, <=)
3. Logical Operators — AND (both), OR (either); use parentheses to control priority
4. IN — Match any value in a set
5. BETWEEN — Match values within a range (inclusive of boundaries)
6. LIKE — Fuzzy matching; % matches multiple characters, _ matches a single character
7. ORDER BY — Sort; ASC ascending (default), DESC descending
8. LIMIT / OFFSET — Limit returned rows, enabling pagination

Query statement writing order: SELECT → FROM → WHERE → ORDER BY → LIMIT

📝 Exercises

Exercise 1: Basic Query

Query all records in the departments table, showing only the name and location columns.

Exercise 2: Condition Filtering and Sorting

Query employees in the employees table with salary greater than or equal to 60000 and hire date after January 1, 2022. Show name, salary, and hire date, sorted by salary descending.

Exercise 3: Comprehensive Query

Query employees in the employees table with department ID 1 or 3, and names not starting with "J". Show name, department ID, and salary. Sort by department ID ascending, salary descending. Return only the first 3 records.

Next Lesson: Data Operations: INSERT, UPDATE, DELETE

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us