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.
SQL Data Filtering and Sorting
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.
×