SQL Primer: Key Concepts and Examples

9. SQL Primer: Key Concepts and Examples

This section is a short, view-based primer on the SQL you need for BriteCore reports. All examples use the logical layer views described in Section 7. Run them in SQL Editor to build muscle memory.

9.1 Keys and Foreign Keys

Views expose columns that identify rows and link views together. A key (for example, revision_id or claim_id) uniquely identifies a row within that view. A foreign key is a column in one view that refers to a key in another. For example, m_inforce_policies.revision_id references v_revisions.revision_id. Joins use these keys to match rows.

-- revision_id links in-force policies to v_revisions (policy_number, etc.)
SELECT i.revision_id, r.policy_number
FROM m_inforce_policies i
JOIN v_revisions r ON r.revision_id = i.revision_id
LIMIT 5;

9.2 SELECT Statements

Choose which columns to return and from which view. Use * only for exploration. For reports, list columns explicitly.

SELECT claim_number, claim_status, loss_date
FROM v_claims
LIMIT 10;

9.3 WHERE Statements

Filter rows before any grouping or aggregation. Conditions use =, <>, >, <, >=, <=, IN (...), LIKE, IS NULL, IS NOT NULL, and AND / OR.

SELECT claim_number, loss_cause
FROM v_claims
WHERE claim_active_flag = 1
AND loss_date >= '2024-01-01';

9.4 JOIN Statements

Combine two or more views by matching key columns. INNER JOIN keeps only rows that match in both views. LEFT JOIN keeps all rows from the left view and fills in the right view where matched, or NULL when there is no match.

-- INNER JOIN: only claims that have a matching policy type
SELECT c.claim_number, pt.policy_type
FROM v_claims c
JOIN v_policy_types pt ON pt.policy_type_id = c.policy_type_id
WHERE c.claim_active_flag = 1
LIMIT 10;

9.5 Aggregation (SUM, COUNT, AVG, MIN, MAX)

Aggregation collapses many rows into one row, or one row per group. You choose how to combine values:

• COUNT(*) or COUNT(column) - number of rows (COUNT(column) ignores NULLs)

• SUM(column) - sum of numeric values.

• AVG(column) - average value (AVG ignores NULLs).

• MIN(column) and MAX(column) - smallest or largest value.

Every column in the SELECT list must either be in GROUP BY or be inside an aggregate function.

SELECT COUNT(*) AS claim_count, SUM(1) AS same_count
FROM v_claims
WHERE claim_active_flag = 1;

9.6 GROUP BY: What Happens to Rows

GROUP BY groups rows that share the same values in the grouped columns. Each group becomes one row. For any other column, you must decide how to represent the group:

• Aggregate it with SUM, COUNT, AVG, MIN, or MAX.

• Pick one value with MIN(column) or MAX(column).

• Concatenate values with GROUP_CONCAT(column) in MySQL.

You cannot return a column that is not in GROUP BY and not inside an aggregate or GROUP_CONCAT; the database would not know which row's value to show.

-- One row per policy_type_id: count claims and pick one loss_cause per group
SELECT c.policy_type_id,
COUNT(*) AS num_claims,
MIN(c.loss_cause) AS sample_loss_cause
FROM v_claims c
WHERE c.claim_active_flag = 1
GROUP BY c.policy_type_id;

-- One row per claim_status
SELECT claim_status, COUNT(*) AS cnt
FROM v_claims
WHERE claim_active_flag = 1
GROUP BY claim_status;

-- Concatenate all loss_cause values per status
SELECT claim_status,
GROUP_CONCAT(loss_cause ORDER BY loss_cause SEPARATOR '; ') AS causes
FROM v_claims
WHERE claim_active_flag = 1
AND loss_cause IS NOT NULL
GROUP BY claim_status;

9.7 HAVING: Filter on Aggregates

WHERE filters rows before grouping. HAVING filters groups after aggregation, so you can use aggregate expressions there.

SELECT claim_status, COUNT(*) AS cnt
FROM v_claims
WHERE claim_active_flag = 1
GROUP BY claim_status
HAVING COUNT(*) > 5;

9.8 ORDER BY and LIMIT

ORDER BY sorts the result set. LIMIT caps the number of rows returned. Use both to get a top-N list or an ordered sample.

SELECT policy_number, term_effective_date
FROM v_revisions
ORDER BY term_effective_date DESC
LIMIT 20;

9.9 Other Concepts Worth Knowing

• DISTINCT - remove duplicate rows: SELECT DISTINCT claim_status FROM v_claims;

• Column alias - rename a field in the output: SELECT inforce_premium AS premium FROM m_inforce_policies;

• NULL - use IS NULL / IS NOT NULL; use COALESCE(col, 0) to turn NULL into a value.

• Dates - YEAR(date_col), DATE_FORMAT(date_col, '%Y-%m'), and CURDATE() are common for filters and grouping

• CAST - for example, CAST('<<$StartDate>>' AS DATE) for parameters