Selecting And Filtering Data

Why this matters

As a Data Visualization Engineer, you must pull the right data before you can build accurate charts. Selecting and filtering data lets you:

• Extract only the columns needed for visuals (e.g., date, metric, category).
• Filter to a time period, region, or product line to match dashboard scope.
• Remove bad or missing values so charts don’t mislead stakeholders.

Real tasks you’ll do:

• Show last quarter’s revenue by category.
• Exclude test orders and null customer locations.
• Return the top 10 products for a drill-down chart.

Concept explained simply

SQL helps you choose what to return (SELECT), from which table (FROM), and which rows to keep (WHERE). Then you can sort and limit the results for your chart.

Mental model

• FROM = stage: which table(s) are on stage.
• WHERE = filter: which rows are allowed through.
• SELECT = camera frame: which columns appear.
• ORDER BY = sorter: arrange rows for top-N or ordered charts.
• LIMIT = bouncer: keep only the first N rows.

Logical execution order to remember: FROM → WHERE → SELECT → ORDER BY → LIMIT. This explains why you cannot use SELECT aliases in WHERE, but you can use them in ORDER BY.

Filtering basics: operators you’ll use daily

• Comparisons: =, != or <>, <, <=, >, >=
• Logic: AND, OR, NOT
• Ranges and sets: BETWEEN a AND b (inclusive), IN (...), NOT IN (...)
• Text patterns: LIKE '%text%', NOT LIKE '%x%'. In some databases, ILIKE is case-insensitive.
• Null checks: IS NULL, IS NOT NULL (never use = NULL)

Worked examples

Imagine a simple analytics schema:

• orders(order_id, order_date, customer_id, status, total_amount)
• customers(customer_id, state, signup_date)
• products(product_id, category, product_name)

1) Select specific columns

SELECT order_id, order_date, total_amount
FROM orders;

Tip: Only select the fields your chart needs. It keeps dashboards fast.

2) Filter by date and status

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= '2023-01-01'
  AND order_date <  '2023-04-01'
  AND status = 'Completed'
ORDER BY order_date ASC;

3) Use IN and NOT IN for cleaner filters

SELECT order_id, status
FROM orders
WHERE status IN ('Completed', 'Refunded');

IN is nicer than chaining OR conditions.

4) Text search with LIKE (and ILIKE where supported)

-- Case-sensitive
SELECT product_name
FROM products
WHERE product_name LIKE '%Pro%';

-- Case-insensitive (e.g., PostgreSQL)
SELECT product_name
FROM products
WHERE product_name ILIKE '%pro%';

5) Handle NULLs explicitly

SELECT customer_id, state
FROM customers
WHERE state IS NOT NULL;

Remember: use IS NULL / IS NOT NULL. Using = NULL will never match.

6) Get the latest 5 completed orders (ORDER BY + LIMIT)

SELECT order_id, order_date, total_amount
FROM orders
WHERE status = 'Completed'
ORDER BY order_date DESC
LIMIT 5;

Practice exercises

Complete these and then check solutions below (mirrors the Exercises section on this page):

• ☐ ex1: Return Q1 2023 completed orders (id, date, amount), sorted by date ascending, limited to 5 rows.
• ☐ ex2: Find products in categories Electronics or Accessories whose name contains "Pro" (case-insensitive), excluding any with missing names.

Common mistakes and how to spot them

• Using = NULL instead of IS NULL. Fix: always use IS (NOT) NULL.
• Forgetting the upper bound in date filters. Fix: use half-open ranges like order_date >= '2023-01-01' AND order_date < '2023-04-01'.
• LIMIT without ORDER BY. Fix: sort first to make LIMIT meaningful.
• Expecting SELECT aliases in WHERE. Fix: logical order runs WHERE before SELECT. Repeat the expression or use a subquery/CTE if needed.
• Case-sensitive LIKE missing matches. Fix: use ILIKE (if supported) or lower(...) on both sides.

Practical projects

• Build a "Recent Activity" card: latest 10 completed orders with amounts and dates.
• Create a "Search products" input demo: show results using ILIKE '%term%'.
• Make a "Data quality check" report: list customers with missing state (IS NULL) to fix input pipelines.

Learning path

1. This subskill: Selecting columns and filtering rows.
2. Next: Sorting and limiting results for top-N visuals.
3. Then: Aggregations with GROUP BY and HAVING.
4. Joins: Bring dimensions into facts for richer charts.
5. Subqueries and common table expressions for clarity.
6. Window functions: moving averages, ranks, period comparisons.

Who this is for

Early-career Data Visualization Engineers, BI developers, and analysts who need reliable queries for dashboards.

Prerequisites

• Basic familiarity with databases (tables, rows, columns).
• Any SQL editor access to practice (local or hosted).

Next steps

• Recreate the worked examples in your SQL environment.
• Complete the exercises and confirm with the solutions.
• Take the quick test to check retention.

Mini challenge

Write a query to list the 10 most recent orders from customers with a known state, excluding statuses 'Canceled' and 'Test'. Show order_id, order_date, state, status. Sort by order_date descending.

Quick Test

Everyone can take the test for free. If you are logged in, your progress will be saved.