SELECT Statements and Filtering: Your First ICM Queries

Picking Specific Columns with SELECT

In Lesson 01, we used SELECT * to grab everything. But SELECT * is lazy and inefficient. In production queries, always pick exactly the columns you need.

-- Instead of this:
SELECT * FROM CSC_PARTICIPANT;

-- Do this:
SELECT
  PARTICIPANT_ID,
  NAME,
  EMAIL,
  STATUS
FROM CSC_PARTICIPANT;

Benefits of explicit column selection:

• Clarity: Readers see exactly what data you want
• Performance: The database retrieves only what you need, not every column
• Stability: If someone adds a new column to the table, your query doesn't break

Column order matters only to you, not the database. Put the most important columns first.

The WHERE Clause: Filtering Data

WHERE filters rows. It comes after FROM and before ORDER BY. Here's the pattern:

SELECT PARTICIPANT_ID, NAME, STATUS, COUNTRY
FROM   CSC_PARTICIPANT
WHERE  STATUS = 'ACTIVE';

The WHERE condition STATUS = 'ACTIVE' means: only return rows where the STATUS column equals the text 'ACTIVE'. Rows with STATUS = 'INACTIVE' or STATUS = 'SUSPENDED' don't appear.

Comparison Operators

Practical ICM example: find all results above $5,000 in Q1 2026:

SELECT PARTICIPANT_ID, RESULT_AMOUNT, PERIOD_ID
FROM   CSC_RESULTS
WHERE  RESULT_AMOUNT > 5000
  AND    PERIOD_ID = '2026-Q1';

BETWEEN: Range Queries

BETWEEN is shorthand for "greater than or equal AND less than or equal":

-- Find all results between $1,000 and $5,000
SELECT PARTICIPANT_ID, RESULT_AMOUNT
FROM   CSC_RESULTS
WHERE  RESULT_AMOUNT BETWEEN 1000 AND 5000;

This is equivalent to: RESULT_AMOUNT >= 1000 AND RESULT_AMOUNT <= 5000

IN: Match Multiple Values

IN checks if a value matches any item in a list:

-- Find participants in specific regions
SELECT PARTICIPANT_ID, NAME, COUNTRY
FROM   CSC_PARTICIPANT
WHERE  COUNTRY IN ('US', 'CA', 'MX');

This returns participants from USA, Canada, or Mexico. Without IN, you'd write:

SELECT PARTICIPANT_ID, NAME, COUNTRY
FROM   CSC_PARTICIPANT
WHERE  COUNTRY = 'US'
  OR     COUNTRY = 'CA'
  OR     COUNTRY = 'MX';

IN is cleaner and faster.

LIKE: Text Matching

LIKE matches partial text patterns. Use % as a wildcard:

-- Find all participants whose name starts with 'John'
SELECT NAME, EMAIL
FROM   CSC_PARTICIPANT
WHERE  NAME LIKE 'John%';

-- Find participants whose name contains 'Smith'
WHERE  NAME LIKE '%Smith%';

-- Find participants whose email ends with '@company.com'
WHERE  EMAIL LIKE '%@company.com';

NULL: The "Missing Value" Problem

NULL is tricky. It doesn't mean zero or empty string. It means "no value" or "unknown." And the rule is: NULL = anything is always false, even NULL = NULL.

-- WRONG: This returns zero rows, even if quotas are NULL
WHERE QUOTA_AMOUNT = NULL;  -- Never works!

-- CORRECT: Use IS NULL
WHERE QUOTA_AMOUNT IS NULL;

-- CORRECT: Use IS NOT NULL
WHERE QUOTA_AMOUNT IS NOT NULL;

Real ICM scenario: quota amounts are NULL for participants who haven't been assigned to a plan yet. To find these problem cases:

SELECT PARTICIPANT_ID, NAME, COMP_PLAN_ID
FROM   CSC_PARTICIPANT
WHERE  STATUS = 'ACTIVE'
  AND    QUOTA_AMOUNT IS NULL;

This returns all active participants with no quota assigned — a red flag for compensation setup issues.

Combining Conditions with AND / OR

AND means all conditions must be true. OR means any one must be true.

-- AND: both conditions must be true
SELECT NAME
FROM   CSC_PARTICIPANT
WHERE  STATUS = 'ACTIVE'
  AND    COUNTRY = 'US';
-- Returns only US-based ACTIVE participants

-- OR: either condition can be true
SELECT NAME
FROM   CSC_RESULTS
WHERE  PERIOD_ID = '2026-Q1'
  OR     PERIOD_ID = '2026-Q2';
-- Returns results from Q1 OR Q2

You can mix AND and OR, but be careful about precedence. Use parentheses to be explicit:

-- Find active OR inactive participants in US
SELECT NAME
FROM   CSC_PARTICIPANT
WHERE  (STATUS = 'ACTIVE' OR STATUS = 'INACTIVE')
  AND    COUNTRY = 'US';

ORDER BY: Sorting Results

ORDER BY sorts your result set. ASC (ascending) is the default; DESC (descending) reverses it.

-- Sort by name alphabetically (A to Z)
SELECT NAME, EMAIL
FROM   CSC_PARTICIPANT
WHERE  STATUS = 'ACTIVE'
ORDER BY NAME ASC;

-- Sort earnings highest to lowest
SELECT PARTICIPANT_ID, RESULT_AMOUNT
FROM   CSC_RESULTS
WHERE  PERIOD_ID = '2026-Q1'
ORDER BY RESULT_AMOUNT DESC;

-- Sort by multiple columns: period first, then name
SELECT PARTICIPANT_ID, PERIOD_ID, NAME, RESULT_AMOUNT
FROM   CSC_RESULTS
ORDER BY PERIOD_ID ASC, NAME ASC;

The database evaluates ORDER BY last, after WHERE and SELECT, so you can order by columns you didn't explicitly select (though it's clearer to include them).

LIMIT and TOP: Restricting Result Count

LIMIT (SAP HANA Database) or TOP (Oracle Database) caps how many rows you get back:

SAP HANA example:

-- Get top 10 highest earners in Q1 2026
SELECT PARTICIPANT_ID, RESULT_AMOUNT
FROM   CSC_RESULTS
WHERE  PERIOD_ID = '2026-Q1'
ORDER BY RESULT_AMOUNT DESC
LIMIT 10;

Oracle equivalent:

SELECT PARTICIPANT_ID, RESULT_AMOUNT
FROM   CS_RESULTS
WHERE  PERIOD_ID = '2026-Q1'
  AND    ROWNUM <= 10
ORDER BY RESULT_AMOUNT DESC;

Putting It All Together: A Real ICM Query

Here's a production query that combines everything we've learned. A manager asks: "Which ACTIVE participants in the Northeast region have earned more than $10,000 in Q1 2026, sorted by earnings, top 20?"

SELECT
  p.PARTICIPANT_ID,
  p.NAME,
  p.REGION,
  r.RESULT_AMOUNT
FROM     CSC_PARTICIPANT p
WHERE    p.STATUS = 'ACTIVE'
  AND      p.REGION = 'Northeast'
  AND      r.RESULT_AMOUNT > 10000
  AND      r.PERIOD_ID = '2026-Q1'
ORDER BY r.RESULT_AMOUNT DESC
LIMIT    20;

Wait — this query has a problem. We're selecting from CSC_PARTICIPANT (p) but filtering on r.RESULT_AMOUNT and r.PERIOD_ID, which are in the CSC_RESULTS table. We need a JOIN! But we haven't covered that yet. That's Lesson 03.

For now, here's a query that works within SELECT/WHERE scope:

SELECT
  PARTICIPANT_ID,
  NAME,
  REGION,
  COUNTRY,
  EMAIL
FROM   CSC_PARTICIPANT
WHERE  STATUS = 'ACTIVE'
  AND    REGION = 'Northeast'
  AND    COUNTRY IN ('US', 'CA')
ORDER BY NAME ASC;

This finds all active Northeast participants from US or Canada, ordered by name. You can run this, it works, and it's production-ready.

What Comes Next

In Lesson 03 (JOINs), we'll solve the problem above: combining data from multiple tables. With JOINs, you'll write queries that answer real business questions in seconds.