- Lab
- Data
SQL Fundamentals: Advanced Filtering Techniques
In this lab, you'll explore advanced SQL filtering techniques to enhance your data querying skills. You'll learn how to effectively use the IN, BETWEEN, and LIKE operators to filter data across various conditions, and handle NULL values with precision. This lab is designed for beginners who have completed prior SQL fundamentals and are looking to deepen their understanding of data manipulation in SQL, providing a solid foundation for more complex queries.
Path Info
Table of Contents
-
Challenge
Using IN and BETWEEN
SQL Guide
For each task, replace or update the existing SQL code in the top pane (
main.sql) with your answer to the task and run it to see the results in the bottom pane (SQL Viewer).
Step 1: Using IN and BETWEEN
To review the concepts covered in this step, please refer to the course Searching Multiple Values with BETWEEN and IN module of the Introduction to SQL Course.
Using the
INandBETWEENoperators provides more flexible and targeted data filtering options. This step focuses on using these operators to select data based on a specific set of values or a range of values.💾 Database Structure
Employee Table
| Column | Type | Description | |----------------|--------------|------------------------------| |
EmployeeID| INT | Primary Key, Auto Increment | |Name| VARCHAR(255) | Employee's Name | |HireDate| DATE | Date of Hire | |Position| VARCHAR(50) | Job Position | |Department| VARCHAR(50) | Department | |MiddleInitial| CHAR(1) | Middle Initial |
Artifacts Table
| Column | Type | Description | |----------------|--------------|------------------------------| |
ArtifactID| INT | Primary Key, Auto Increment | |ArtifactName| VARCHAR(255) | Name of the Artifact | |Year| INT | Year of Creation |
Product Table
| Column | Type | Description | |---------------|--------------|------------------------------| |
ProductID| INT | Primary Key, Auto Increment | |ProductName| VARCHAR(255) | Name of the Product | |Category| VARCHAR(50) | Product Category | |Price| DECIMAL(10, 2)| Product Price |📚 Real-World Context
Imagine you work as an HR analyst or a curator in a museum. You often need to filter data based on specific criteria, such as selecting employees in certain departments for a training program or retrieving artifacts from a specific historical period. Using `IN` and `BETWEEN` allows you to streamline these queries, making it easier to extract relevant data quickly. Whether you’re managing a workforce or cataloging artifacts, these operators are essential for effective data filtering. -
Challenge
Searching with LIKE
Step 2: Searching with LIKE
The
LIKEoperator is used for pattern matching in SQL, allowing you to search for specific patterns within text data. This step focuses on usingLIKEto find records that match, contain, or exclude specific text patterns.💾 Database Structure
Employee Table
| Column | Type | Description | |----------------|--------------|------------------------------| |
EmployeeID| INT | Primary Key, Auto Increment | |Name| VARCHAR(255) | Employee's Name | |HireDate| DATE | Date of Hire | |Position| VARCHAR(50) | Job Position | |Department| VARCHAR(50) | Department | |MiddleInitial| CHAR(1) | Middle Initial |
Artifacts Table
| Column | Type | Description | |----------------|--------------|------------------------------| |
ArtifactID| INT | Primary Key, Auto Increment | |ArtifactName| VARCHAR(255) | Name of the Artifact | |Year| INT | Year of Creation |
Product Table
| Column | Type | Description | |---------------|--------------|------------------------------| |
ProductID| INT | Primary Key, Auto Increment | |ProductName| VARCHAR(255) | Name of the Product | |Category| VARCHAR(50) | Product Category | |Price| DECIMAL(10, 2)| Product Price |📚 Real-World Context
Consider you’re working in product management for a retail company. You need to filter products based on their names to create targeted marketing campaigns. For instance, you might want to promote all products that start with 'Green' or exclude them from a particular sale. Using the `LIKE` operator allows you to search for patterns in product names efficiently, ensuring that your campaigns target the right products and resonate with your audience. -
Challenge
Using NULL
Step 3: Using NULL
NULL values in SQL represent missing or unknown data. Handling NULL values properly in queries is crucial for accurate data analysis. This step focuses on selecting records with or without NULL values.
💾 Database Structure
Employee Table
| Column | Type | Description | |----------------|--------------|------------------------------| |
EmployeeID| INT | Primary Key, Auto Increment | |Name| VARCHAR(255) | Employee's Name | |HireDate| DATE | Date of Hire | |Position| VARCHAR(50) | Job Position | |Department| VARCHAR(50) | Department | |MiddleInitial| CHAR(1) | Middle Initial |
Artifacts Table
| Column | Type | Description | |----------------|--------------|------------------------------| |
ArtifactID| INT | Primary Key, Auto Increment | |ArtifactName| VARCHAR(255) | Name of the Artifact | |Year| INT | Year of Creation |
Product Table
| Column | Type | Description | |---------------|--------------|------------------------------| |
ProductID| INT | Primary Key, Auto Increment | |ProductName| VARCHAR(255) | Name of the Product | |Category| VARCHAR(50) | Product Category | |Price| DECIMAL(10, 2)| Product Price |📚 Real-World Context
Imagine you work in data quality management or human resources. Handling NULL values is a common task, whether you’re identifying incomplete employee records, validating data for analysis, or preparing reports. Knowing how to filter data based on the presence or absence of NULL values ensures that your analyses are accurate and your decisions are based on complete and reliable data.
What's a lab?
Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.
Provided environment for hands-on practice
We will provide the credentials and environment necessary for you to practice right within your browser.
Guided walkthrough
Follow along with the author’s guided walkthrough and build something new in your provided environment!
Did you know?
On average, you retain 75% more of your learning if you get time for practice.