SQL Intermediate: Advanced Filtering

Name: SQL Intermediate: Advanced Filtering
Author: Eric Grose

Unleashing the power of AND, OR, NOT, and IN operators for efficient queries.

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

In these lessons, we'll explore all the advanced filtering functions in SQL. In this lesson, we'll focus on the foundational aspects of using the WHERE clause to filter data. SQL gives us the ability to filter and manipulate data to solve problems and use cases. Before we start diving into the WHERE clause, let's create two tables to perform this type of analysis. The first table will be our customers table, which contains a customer ID as our primary key, the customer's first name, their last name, and the city where they reside. Let's go ahead and create this table by pressing CTRL -ENTER. Our second table will be the orders table. This table has an order ID as a primary key, contains the order status, as well as the customer ID for the customer who made the order. To link these tables, we'll be using the foreign key constraint on the customer ID column. Since this column is present in both the customers and the order table, we can use the references and list the customers and customer ID table. This will link these tables. Let's go ahead and create this table in this relationship by pressing CTRL -ENTER. Next, let's insert five rows into each of these tables so we can analyze this data. First, we'll insert into the customers table. We'll list the column headers within this table, and then we'll use the values keyword followed by the rows themselves. Go ahead and insert these rows by pressing CTRL -ENTER. Now let's continue this work by adding rows into the orders table. Again, we have listed the column headers, used the values keyword, as well as listed the data for the individual rows. Let's go ahead and insert these rows by pressing CTRL -ENTER. Now that we have each of these tables in their rows, we can go ahead and start our analysis. Let's say there's a scenario where we're only interested in data from customers who live in Los Angeles. We'll use SELECT star to bring back all columns. We'll use the FROM clause to list our table, in this case, the customers table. And now here's where the WHERE clause comes into play. For this query, there's a condition where the city column must equal Los Angeles. Using the WHERE clause, we will only retrieve the rows who meet this condition. Go ahead and run this by pressing CTRL -ENTER. And we see here in our output, only the customers in the city column of Los Angeles are returned. For our next example, say we want to only return the records with the completed status. Again, we'll go ahead and use SELECT star to bring all columns. This time, we'll use the FROM clause to indicate we want to use the orders table. To set a condition, we'll use the WHERE clause, which in this case states that the order status must equal completed. Let's go ahead and run this by pressing CTRL -ENTER. And we see only the rows with an order status have completed our return. We can also use multiple conditions with the WHERE clause. We can use the AND or OR keywords to build our logic. Let's modify our requirement to now say we're interested in customers from Los Angeles and whose first name is John. For this scenario, we'll go ahead and bring back all columns by using SELECT star. We'll signify our table in the FROM clause, in this case, the customers table, where the city column is equal to Los Angeles. But now we're using the AND keyword to add more criteria to our query. The second condition that must be met is that the first name column also equals John. Both of these conditions must be met in order for a row to be returned. Let's go ahead and take a look at this by pressing CTRL -ENTER. We see the only row that meets both of these conditions is returned. Next, let's look at our OR operator by building a query that looks at customers in Los Angeles or New York. So again, we'll select all columns. This data resides in our customer table. One of the conditions is that the customer is in the city of Los Angeles. But now we're using the OR operator to define our second condition of living in New York. With the OR operator, either one of these conditions can be met to return a row. Let's go ahead and take a look at this by pressing CTRL -ENTER. And we see here that the rows that contain Los Angeles or New York are returned in this result set. Or say we're interested in orders that are either completed or pending. We'll go ahead and use SELECT star to select all columns. Signify that this data is in the orders table. And using the WHERE clause and the OR operator to find both of these order statuses. Let's go ahead and run this by pressing CTRL -ENTER. And we see that the OR operator is more flexible in that either one of these conditions can be met to return a row. Let's take a look at another operator, which is known as the NOT operator and can be used for more complex filtering. For this query, we're going to use SELECT star to bring back all columns. Again, using the FROM clause to list the customers table. But now we're using the WHERE NOT to signify that we want the rows that do not meet this condition. In this scenario, it's where the city equals Los Angeles. So this query will return all rows where the city is not Los Angeles. Let's go ahead and run this by pressing CTRL -ENTER. And we see that all other cities are returned in this result set. With SQL syntax, there's oftentimes more than one way to write the same query. For this query, we could simply use these two signs or the NOT EQUALS operator to fetch the same query. With these two signs next to each other, it's essentially saying a NOT EQUALS sign. Let's go ahead and run this by pressing CTRL -ENTER and we see again the same rows returned. Whichever one is more comfortable to you, go ahead and use that. We've just begun to scratch the surface on advanced filtering techniques. Stay tuned for the next lesson where we learn how to incorporate wildcards, how to deal with null values, as well as the BETWEEN and IN operators. Thanks for watching.

Intermediate Provider : Eric Grose 3 Lessons · 20m in Arabic, English

Course Description

We venture beyond basic filtering to take full advantage of SQL's rich set of filtering functions such as AND, OR, NOT, and IN. Starting with a review of the foundational SQL filtering via the WHERE clause, we then immerse ourselves in more sophisticated techniques, including wildcards, NULL value handling, and the nuanced BETWEEN and IN operators. These lessons emphasize hands-on experience and encourage you to apply your learning to craft complex queries that respond to real-world scenarios. Along the way, we will address the potential pitfalls and best practices of using advanced filtering functions. By the end of these lessons, you will not only understand these advanced tools but also be confident in implementing them to enhance data retrieval and analysis in your SQL databases.

In these lessons on advanced filtering techniques, you will learn about the efficient use of advanced filtering commands like AND, OR, NOT, and IN. We will review the basics of SQL filtering using the WHERE clause, delve into advanced techniques such as using wildcards, handling NULL values, and utilizing the BETWEEN and IN operators. By the end of these lessons, you will be able to design and implement complex filtering to retrieve desired data efficiently from your SQL databases.

What You'll Learn

Apply advanced SQL filtering functions including AND, OR, NOT, and IN to retrieve data efficiently
Filter data using the WHERE clause as the foundation for SQL queries
Use wildcards and handle NULL values in filtering operations
Construct nuanced queries with the BETWEEN and IN operators
Design and implement complex filtering to retrieve desired data from SQL databases
Identify potential pitfalls and apply best practices when using advanced filtering functions

Key Takeaways

The WHERE clause provides the foundation for filtering data in SQL queries.
Advanced filtering functions such as AND, OR, NOT, and IN extend SQL's filtering capabilities beyond the basics.
Techniques like wildcards, NULL value handling, and the BETWEEN and IN operators enable more sophisticated, nuanced queries.
Combining advanced filtering tools lets you design and implement complex queries that retrieve desired data efficiently.
The lessons emphasize hands-on experience applying filtering techniques to real-world scenarios, along with best practices and common pitfalls.

Frequently Asked Questions

What does this intermediate SQL course cover?

It covers advanced filtering in SQL, starting with a review of the WHERE clause and moving into advanced techniques including wildcards, NULL value handling, and the BETWEEN and IN operators, along with the AND, OR, NOT, and IN functions.

What skills will I gain from this course?

You will gain skills in advanced filtering, advanced logic, extended SQL, query understanding, and SQL tuning.

Is this course hands-on?

Yes. The lessons emphasize hands-on experience and encourage you to apply your learning to craft complex queries that respond to real-world scenarios.

What lessons are included in this course?

The course includes three lessons: The Basics of Filtering in SQL, Exploring Advanced Filtering Techniques, and Implementing Advanced Filtering in SQL.

What will I be able to do by the end of the course?

By the end, you will be able to design and implement complex filtering to retrieve desired data efficiently from your SQL databases, and feel confident implementing these advanced tools to enhance data retrieval and analysis.