Section 2: Filtering and Sorting Data

In this section, we'll explore advanced SQL techniques for filtering and sorting data. These skills are crucial for extracting specific information from large datasets and presenting it in a meaningful way.

Filtering Data with WHERE

The WHERE clause allows us to filter data based on specific conditions. Various comparison operators can be used to refine the results.

Using the WHERE Clause for Conditional Retrieval

Consider the following example where we retrieve employees hired after a certain date:

SELECT *

FROM employees

WHERE hire_date > '2020-01-01';

This query returns all columns for employees hired after January 1, 2020.

Comparison Operators (=, <>, <, >, <=, >=)

Equality (=) and Inequality (<>)

-- Employees with the first name 'John'

SELECT *

FROM employees

WHERE first_name = 'John';

-- Employees not named 'John'

SELECT *

FROM employees

WHERE first_name <> 'John';

Less Than (<) and Greater Than (>)

-- Employees born before 1990

SELECT *

FROM employees

WHERE birth_date < '1990-01-01';

-- Employees hired after 2010

SELECT *

FROM employees

WHERE hire_date > '2010-01-01';

Less Than or Equal To (<=) and Greater Than or Equal To (>=)

-- Employees born in or after 1980

SELECT *

FROM employees

WHERE birth_date >= '1980-01-01';

-- Employees hired on or before 2015

SELECT *

FROM employees

WHERE hire_date <= '2015-12-31';

Sorting Data with ORDER BY

The ORDER BY clause is used to sort the result set in ascending or descending order.

Sorting Results in Ascending and Descending Order

-- Sorting employees by hire date in ascending order

SELECT *

FROM employees

ORDER BY hire_date;

-- Sorting employees by birth date in descending order

SELECT *

FROM employees

ORDER BY birth_date DESC;

Sorting by Multiple Columns

-- Sorting employees by last name in ascending order, then by first name in descending order

SELECT *

FROM employees

ORDER BY last_name ASC, first_name DESC;

This section covers the essential techniques for filtering data using the WHERE clause with various comparison operators and sorting results with the ORDER BY clause. These skills are fundamental for refining your queries and extracting precise information from databases. Keep practicing to solidify your understanding!