Mastering Database Management

0 of 78 lessons complete (0%)

Basic SQL Commands with CRUD Operations

Filtering and Sorting Data

You don’t have access to this lesson

Please register or sign in to access the course content.

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.