SQL Data Filtering and Sorting

Filtering and sorting data are essential operations when working with databases. The WHERE clause is used for filtering rows, and the ORDER BY clause is used for sorting the result set.

Here are examples demonstrating how to filter and sort data using SQL :

Filtering Data with WHERE Clause

Basic Filtering


SELECT * FROM employees
WHERE department_id = 3;

Using Comparison Operators

SELECT * FROM products
WHERE price > 50 AND category = 'Electronics';

Using IN Operator


SELECT * FROM orders
WHERE customer_id IN (1, 2, 3);

Using LIKE Operator for Pattern Matching


SELECT * FROM employees
WHERE last_name LIKE 'Smith%';

Combining Conditions with AND, OR


SELECT * FROM products
WHERE (price > 50 AND category = 'Electronics') OR (stock_quantity < 10);

Sorting Data with ORDER BY Clause

Sorting Ascending


SELECT * FROM products
ORDER BY price ASC;

Sorting Descending


SELECT * FROM products
ORDER BY price DESC;

Sorting by Multiple Columns


SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;

Combining Filtering and Sorting

Filtering and Sorting Together


SELECT * FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY order_date DESC;

Limiting the Number of Rows with Filtering and Sorting


SELECT * FROM products
WHERE price > 50
ORDER BY price DESC
LIMIT 10;

Using GROUP BY and HAVING for Aggregated Data

Grouping and Filtering Aggregated Data


SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

TThese examples demonstrate how to filter and sort data using SQL. You can customize these queries based on your specific needs. Always refer to the official MySQL documentation for detailed information and additional features.