Chapter 4: Filtering and Sorting Data

Introduction

In this chapter, we will explore the techniques for filtering and sorting data in SQL queries. By utilizing the WHERE clause, comparison and logical operators, and the ORDER BY clause, you can precisely control the data you retrieve from the database and arrange it in a desired order.

4.1 Using the WHERE Clause

The WHERE clause is used to filter data based on specified conditions. It allows you to selectively retrieve rows that meet certain criteria. Here's an example:

sqlCopy code

SELECT * FROM customers WHERE country = 'USA';

In the above example, we retrieve all rows from the "customers" table where the "country" column has the value 'USA'. This filters the results to only include customers from the USA. You can use various operators like "=" (equal to), "<" (less than), ">" (greater than), and more to create more complex conditions.

4.2 Comparison and Logical Operators

SQL provides a range of comparison and logical operators that can be used in conjunction with the WHERE clause to create powerful filtering conditions. Some commonly used operators include:

  • "=": Equal to

  • "<>": Not equal to

  • "<": Less than

  • ">": Greater than

  • "<=": Less than or equal to

  • ">=": Greater than or equal to

  • "AND": Logical AND

  • "OR": Logical OR

  • "NOT": Logical NOT

For example:

sqlCopy code

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

In the above example, we retrieve products from the "products" table where the price is greater than 100 and the category is 'Electronics'. This combines both comparison and logical operators to create a more specific filtering condition.

4.3 Sorting Results with ORDER BY

The ORDER BY clause allows you to sort the retrieved data based on one or more columns. By default, sorting is done in ascending order. Here's an example:

sqlCopy code

SELECT * FROM employees ORDER BY last_name ASC, first_name ASC;

In the above example, we retrieve all rows from the "employees" table and sort them first by the "last_name" column in ascending order, and then by the "first_name" column in ascending order. This arranges the results alphabetically by last name and within each last name, it further sorts them by first name.

You can also use the "DESC" keyword to sort in descending order. For example:

sqlCopy code

SELECT * FROM orders ORDER BY order_date DESC;

In this case, we retrieve all rows from the "orders" table and sort them in descending order based on the "order_date" column. This gives us the latest orders first.

By combining the WHERE clause, comparison and logical operators, and the ORDER BY clause, you can effectively filter and sort data to retrieve the specific information you need from your database.

Throughout this chapter, we have provided detailed explanations and practical examples to help you understand the concepts of filtering and sorting data in SQL. Make sure to practice these examples and experiment with different conditions and sorting options to solidify your understanding. In the next chapter, we will explore advanced querying techniques such as joins, subqueries, and common table expressions.

All Lessons Pages

Excellence Academy