MySQL Data Filtering and Sorting

Introduction

In this lab, we will explore essential techniques for filtering and sorting data in MySQL. You'll learn how to use WHERE clauses to filter data based on specific conditions, sort results using ORDER BY, and limit output using the LIMIT clause. These skills are fundamental for any database user, allowing you to retrieve exactly the data you need in the order you want it. Through hands-on exercises, you'll gain practical experience with these crucial database operations and understand how to effectively query and organize data in MySQL.

Skills Graph

Basic Data Filtering with WHERE

In this step, we'll learn how to use the WHERE clause to filter data based on specific conditions. The WHERE clause is one of the most important features in SQL, allowing you to retrieve only the data that meets your criteria.

First, let's connect to MySQL and select our database:

sudo mysql -u root

Once connected:

USE bookstore;

Simple Comparison Operations

Let's start with basic comparison operators:

-- Books priced over $40
SELECT title, price
FROM books
WHERE price > 40;

You should see output like this:

+-------------------------+-------+
| title                   | price |
+-------------------------+-------+
| The MySQL Guide         | 45.99 |
| Advanced Database Concepts | 54.99 |
| Database Administration | 49.99 |
| The Perfect Index       | 42.99 |
+-------------------------+-------+

Common comparison operators include:

• = (equal to)
• <> or != (not equal to)
• > (greater than)
• < (less than)
• >= (greater than or equal to)
• <= (less than or equal to)

Let's try another example finding books published in a specific year:

SELECT title, author, publication_year
FROM books
WHERE publication_year = 2023;

Using AND and OR

We can combine multiple conditions using AND and OR:

-- Technical books published in 2023
SELECT title, genre, publication_year, price
FROM books
WHERE genre = 'Technical'
AND publication_year = 2023;

Let's try OR to find books that are either very new or very expensive:

SELECT title, publication_year, price
FROM books
WHERE publication_year = 2023
OR price >= 50;

Working with NULL and Boolean Values

To find books that are out of stock:

SELECT title, price, in_stock
FROM books
WHERE in_stock = FALSE;

Pattern Matching with LIKE

In this step, we'll explore pattern matching using the LIKE operator, which allows us to search for patterns in text data. This is particularly useful when you don't know the exact text you're looking for.

The LIKE operator uses two special characters:

• % represents zero or more characters
• _ represents exactly one character

Let's try some examples:

-- Find books with "SQL" anywhere in the title
SELECT title, author
FROM books
WHERE title LIKE '%SQL%';

This query finds any books where "SQL" appears anywhere in the title. You should see output like:

+------------------+----------------+
| title            | author         |
+------------------+----------------+
| SQL for Beginners| Sarah Johnson  |
| SQL Mystery Tales| Jennifer White |
+------------------+----------------+

Let's try some more pattern matching:

-- Find books starting with "The"
SELECT title, author
FROM books
WHERE title LIKE 'The%';

-- Find books with "Database" in the title
SELECT title, author, price
FROM books
WHERE title LIKE '%Database%';

Note that LIKE is case-insensitive in MySQL by default. If you need case-sensitive matching, you can use BINARY:

-- Case-sensitive search
SELECT title, author
FROM books
WHERE title LIKE BINARY 'The%';

Sorting Results with ORDER BY

In this step, we'll learn how to sort our query results using the ORDER BY clause. This allows us to arrange our data in a meaningful order, either ascending (ASC) or descending (DESC).

Single Column Sorting

Let's start with basic sorting by price:

-- Sort books by price (ascending by default)
SELECT title, price
FROM books
ORDER BY price;

You'll see the books listed from least expensive to most expensive. To reverse the order:

-- Sort books by price in descending order
SELECT title, price
FROM books
ORDER BY price DESC;

The output will show the most expensive books first:

+---------------------------+-------+
| title                     | price |
+---------------------------+-------+
| Advanced Database Concepts| 54.99 |
| Database Administration   | 49.99 |
| The MySQL Guide           | 45.99 |
| The Perfect Index         | 42.99 |
...
+---------------------------+-------+

Multiple Column Sorting

We can sort by multiple columns, which is useful when the first sort column has duplicate values:

-- Sort by genre and then by price within each genre
SELECT title, genre, price
FROM books
ORDER BY genre, price DESC;

You can mix ascending and descending sorts:

-- Sort by genre (ascending) and price (descending)
SELECT title, genre, price
FROM books
ORDER BY genre ASC, price DESC;

Limiting Results with LIMIT

In this step, we'll learn how to use the LIMIT clause to restrict the number of rows returned by a query. This is particularly useful when working with large datasets or when you only need to see a few examples.

Basic LIMIT Usage

To see just the top 3 most expensive books:

SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3;

You should see only three rows:

+---------------------------+-------+
| title                     | price |
+---------------------------+-------+
| Advanced Database Concepts| 54.99 |
| Database Administration   | 49.99 |
| The MySQL Guide           | 45.99 |
+---------------------------+-------+

Using LIMIT with Offset

LIMIT can take two numbers: the offset (how many rows to skip) and the count (how many rows to return):

-- Skip the first 3 books and show the next 3
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3, 3;

This will show the 4th, 5th, and 6th most expensive books.

A common use case is pagination. For example, to show "page 2" with 5 items per page:

SELECT title, author, price
FROM books
ORDER BY title
LIMIT 5, 5;

This skips the first 5 results (page 1) and shows the next 5 (page 2).

Combining Everything

Let's put together everything we've learned to create a more complex query:

SELECT title, author, price
FROM books
WHERE genre = 'Technical'
AND price > 30
ORDER BY price DESC
LIMIT 3;

This query:

1. Filters for technical books over $30
2. Sorts them by price in descending order
3. Shows only the top 3 results

Summary

In this lab, we've covered essential techniques for filtering and sorting data in MySQL:

1. Using WHERE clauses with comparison and logical operators to filter data
2. Pattern matching with the LIKE operator for text searches
3. Sorting results using ORDER BY with single and multiple columns
4. Limiting result sets using LIMIT and OFFSET

These skills form the foundation for effective data retrieval and organization in MySQL.