Effective data analysis hinges on your ability to filter data precisely and efficiently, enabling targeted insights and informed decisions. SQL filtering is an essential skill every analytics engineer must master. Whether you’re isolating customer segments, refining product lists, or pinpointing specific employee records, mastering SQL filtering techniques ensures you’re working with accurate, relevant datasets.
In this tutorial, “SQL Tasks – Filtering Rows,” you’ll dive into core SQL concepts such as WHERE clauses, logical operators (AND, OR, NOT), and comparison operators (equality, inequality, greater than, less than). These skills help you clearly define criteria for retrieving data, resulting in streamlined queries and actionable analytics. Enhance your ability to swiftly sift through extensive datasets to reveal exactly the information you need, transforming raw data into valuable insights.
🚀 Jump Right to Exercise Tasks: SQL Tasks – Filtering Rows
Basic Filtering with WHERE Conditions
The WHERE clause is fundamental in SQL, used to selectively retrieve rows from tables that meet specific criteria. Whether you’re filtering data by numerical thresholds, textual values, or date ranges, WHERE conditions provide clarity and precision to your queries. For example, retrieving customer details who joined after a specific date helps identify recent customer activity.
Practical Example
SELECT customer_id, first_name, last_name
FROM customers
WHERE join_date > '2020-01-01';Example Solution:
customer_id | first_name | last_name
------------|------------|-----------
101 | John | Doe
102 | Jane | SmithKey Takeaways:
- Precisely filter data based on defined criteria.
- Optimize the accuracy of data-driven insights.
Combining Conditions with AND, OR, NOT
Logical operators (AND, OR, NOT) allow complex filtering conditions by combining multiple criteria within queries. For example, retrieving products based on both price and stock quantity conditions allows businesses to pinpoint items meeting specific inventory and pricing strategies, enhancing operational efficiency and decision-making.
Practical Example
SELECT product_name, unit_price
FROM products
WHERE unit_price > 100 AND stock_quantity > 50;Example Solution:
product_name | unit_price
---------------|-----------
Premium Chair | 150.00
Executive Desk | 250.00Key Takeaways:
- Create detailed filtering conditions.
- Exclude or include specific data subsets easily.
Filtering by Text and Specific Values
Filtering by specific text or categorical values is critical in situations where precise matches are required. For instance, isolating suppliers from a particular city allows businesses to conduct targeted geographical analysis or manage logistics efficiently, thus ensuring effective resource allocation.
Practical Example
SELECT supplier_name, city
FROM suppliers
WHERE city = 'Seattle';Example Solution:
supplier_name | city
-----------------|--------
Northwest Supply | Seattle
GreenTech Inc. | SeattleKey Takeaways:
- Precisely target specific textual values.
- Enhance data-driven decision-making based on categorical data.
Date Range Filtering
Date-based filtering allows analysts to retrieve data within specific time frames, crucial for trend analysis, reporting, and forecasting. By selecting orders within a defined period, businesses can assess performance, customer behavior, and seasonal patterns effectively.
Practical Example
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date BETWEEN '2022-03-01' AND '2022-03-31';Example Solution:
order_id | customer_id | order_date
---------|-------------|-----------
2001 | 101 | 2022-03-05
2002 | 103 | 2022-03-20Key Takeaways:
- Efficiently retrieve data within defined time periods.
- Support accurate and timely business insights.
What You’ll Gain from Completing This Exercise
Master critical SQL filtering skills, from basic conditions to complex logical combinations, text-based filtering, and precise date-range queries, improving your analytical and reporting capabilities.
How to Complete the Exercise Tasks
- Write your SQL code: Enter your query in the provided SQLite editor.
- Run your query: Click “Run” to execute.
- Check your solution: Click “Check Answer” to validate.
- Reset the editor: Click “Reset” to restart.
Earn XP, Unlock Rewards, and Track Progress!
Log in to earn XP, unlock rewards, and automatically save progress to advance on leaderboards.
Schema Information
| Column Name | Data Type | Description |
|---|---|---|
| customer_id | INTEGER | Unique identifier for each customer |
| first_name | TEXT | Customer’s first name |
| last_name | TEXT | Customer’s last name |
| TEXT | Customer’s email address | |
| city | TEXT | City of residence |
| join_date | DATE | Date the customer joined |
| phone | TEXT | Customer’s phone number |
| Column Name | Data Type | Description |
|---|---|---|
| order_id | INTEGER | Unique identifier for each order |
| customer_id | INTEGER | ID of the customer who placed the order |
| employee_id | INTEGER | ID of the employee who handled the order |
| order_date | DATE | Date when the order was placed |
| status | TEXT | Order status (e.g., Shipped, Cancelled, Pending) |
| total_amount | REAL | Total amount for the order |
| Column Name | Data Type | Description |
|---|---|---|
| product_id | INTEGER | Unique identifier for each product |
| product_name | TEXT | Name of the product |
| category_id | INTEGER | Category to which the product belongs |
| unit_price | REAL | Price per unit of the product |
| stock_quantity | INTEGER | Quantity of items available in stock |
| creation_date | DATE | Date the product was added to the system |
| Column Name | Data Type | Description |
|---|---|---|
| supplier_id | INTEGER | Unique identifier for each supplier |
| supplier_name | TEXT | Name of the supplier |
| city | TEXT | City where the supplier is located |
| products_supplied | TEXT | List of product IDs supplied (e.g., [90,91,33,121]) |
| Column Name | Data Type | Description |
|---|---|---|
| movement_id | INTEGER | Unique identifier for each movement record |
| product_id | INTEGER | Product being moved |
| change_quantity | INTEGER | Change in quantity (+/-) |
| movement_date | DATE | Date of the inventory movement |
| reason | TEXT | Explanation (Sale, Restock, Adjustment, etc.) |
| Column Name | Data Type | Description |
|---|---|---|
| employee_id | INTEGER | Unique identifier for each employee |
| first_name | TEXT | Employee’s first name |
| last_name | TEXT | Employee’s last name |
| department_id | INTEGER | Identifier of the department |
| hire_date | DATE | Date the employee was hired |
| salary | INTEGER | Employee’s salary |
| Column Name | Data Type | Description |
|---|---|---|
| department_id | INTEGER | Unique identifier for each department |
| department_name | TEXT | Name of the department |
| location | TEXT | Location of the department |
