Filtering and sorting data are essential operations in SQL, allowing you to retrieve specific subsets of data from a database based on specified conditions and order the results as needed. Here are examples of filtering and sorting using SQL queries:
1. Filtering Data:
Example 1: Basic WHERE Clause
-- Selecting books published after the year 2000
SELECT Title, PublishYear
FROM Books
WHERE PublishYear > 2000;
Example 2: Combining Conditions
-- Selecting books by a specific author published after 1995
SELECT Title, PublishYear
FROM Books
WHERE AuthorID = 1 AND PublishYear > 1995;
Example 3: Using LIKE for Partial Matches
-- Selecting authors with a last name starting with 'R'
SELECT FirstName, LastName
FROM Authors
WHERE LastName LIKE 'R%';
2. Sorting Data:
Example 1: Sorting in Ascending Order
-- Selecting all books and sorting by publish year in ascending order
SELECT Title, PublishYear
FROM Books
ORDER BY PublishYear ASC;
Example 2: Sorting in Descending Order
-- Selecting all books and sorting by publish year in descending order
SELECT Title, PublishYear
FROM Books
ORDER BY PublishYear DESC;
Example 3: Sorting by Multiple Columns
-- Selecting books and sorting by author ID in ascending order and then by publish year in descending order
SELECT Title, AuthorID, PublishYear
FROM Books
ORDER BY AuthorID ASC, PublishYear DESC;
3. Combining Filtering and Sorting:
-- Selecting books by a specific author published after 2000 and sorting by publish year in descending order
SELECT Title, PublishYear
FROM Books
WHERE AuthorID = 1 AND PublishYear > 2000
ORDER BY PublishYear DESC;
These examples demonstrate how to use the WHERE clause for filtering data based on specific conditions and the ORDER BY clause for sorting data. Adjust the conditions and columns based on your database structure and requirements.