In this blog post, we will dive into the details of the WHERE clause in SQL and explore some best practices to make the most of this powerful feature.
For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu.
Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.
HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.
YOU’VE GOT MAIL!
Thanks again for the support you provided yesterday.
Today we’d really like to know all the restaurants that were launched pre-pandemic (Before 2019)
Will it be possible to get this data before the end of the day?
Mrs Ahmadu (CGO)
SQL TOPIC OF THE DAY
Understanding the WHERE Clause
The WHERE clause is used in conjunction with the SELECT statement to filter rows based on one or more conditions. It allows you to retrieve only the data that meets the specified criteria, making your queries more efficient and relevant.
Here’s a basic syntax of the SELECT statement with a WHERE clause:
|SELECT column1, column2, …|
column1, column2, …: The columns you want to retrieve.
table_name: The name of the table from which you want to fetch data.
condition: The filtering criteria that determine which rows are included in the result set.
Common Operators in WHERE Clause
The WHERE clause uses various operators to define conditions. Some of the most commonly used operators include:
- Comparison Operators:
<> or != (not equal)
< (less than)
> (greater than)
<= (less than or equal to)
>= (greater than or equal to)
- Logical Operators:
|AND (both conditions must be true)|
OR (either condition must be true)
NOT (negates a condition)
|% (matches any number of characters)|
_ (matches a single character)
Let’s explore a few practical examples to illustrate the use of the WHERE clause.
Retrieve all employee names with a salary greater than $50,000:
WHERE salary > 50000;
/*Here we assume there’s a table named employees with multiple columns*/
Find the customers names and cities from the ‘USA’:
|SELECT customer_name, city |
WHERE country = ‘USA’
/*Here we assume there’s a table named ‘customers’ with multiple columns*/
Get a list of products with names containing ‘apple’ or ‘banana’:
|SELECT * |
WHERE product_name LIKE ‘%apple%’
OR product_name LIKE ‘%banana%’;
/*Here we assume there’s a table named ‘products’ with multiple columns */
Now we can write a script to answer Mrs Ahmadu’s question (based on the dataset provided)
|SELECT name, launch_year|
WHERE launch_year < ‘2019’;
FEEDBACK FROM THE CGO
I’d really appreciate it if you can also filter this list specifically for the Nigerian restaurants?
Mrs Ahmadu (CGO)