Data Analysis

SQL and Database Analysis Terms

Learn relational structures, keys, filtering, joins, grouping, aggregation, CTEs, and window functions used in analytical SQL.

12 matching terms

Relational model

Table

Meaning

A relation organized into rows and named columns.

When to use it

Use tables to store entities, events, reference data, and analytical results.

Practical example

analytics.orders(order_id, customer_id, amount, ordered_at)

Relational model

Row and column

Meaning

A row represents one record, while a column represents one attribute with a defined type.

When to use it

Confirm the grain of each row and the meaning of each column before querying.

Practical example

One row = one order; amount = gross order value.

Relational model

Primary key

Meaning

A column or column set that uniquely identifies each table row.

When to use it

Use it to enforce uniqueness and connect related records reliably.

Practical example

PRIMARY KEY (order_id)

Relational model

Foreign key

Meaning

A column or column set that references a key in another table.

When to use it

Use it to represent relationships and protect referential integrity.

Practical example

orders.customer_id REFERENCES customers.customer_id

Queries

SELECT

Meaning

An SQL statement that retrieves columns or computed expressions from a data source.

When to use it

List only needed columns to make analytical intent clear.

Practical example

SELECT order_id, amount FROM orders;

Queries

WHERE

Meaning

A clause that filters input rows before grouping and aggregation.

When to use it

Use it for row-level date, status, region, or quality conditions.

Practical example

WHERE ordered_at >= DATE '2026-01-01'

Queries

JOIN

Meaning

An operation that combines rows from multiple tables using a matching condition.

When to use it

Choose inner or outer joins based on whether unmatched rows must remain.

Caution

Check key uniqueness before joining to avoid unintended row multiplication.

Practical example

FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id

Aggregation

GROUP BY

Meaning

A clause that combines rows sharing selected values into groups for aggregation.

When to use it

Use it to summarize data by date, product, region, or another dimension.

Practical example

GROUP BY order_date, region

Aggregation

Aggregate function

Meaning

A function that produces a summary value from multiple input rows.

When to use it

Use COUNT, SUM, AVG, MIN, and MAX according to the metric definition.

Practical example

SELECT region, SUM(amount) AS revenue FROM orders GROUP BY region;

Aggregation

HAVING

Meaning

A clause that filters groups after aggregation has been computed.

When to use it

Use it for conditions based on aggregate results rather than source rows.

Practical example

HAVING COUNT(*) >= 100

Advanced analysis

Common table expression (CTE)

Meaning

A named temporary query result introduced with WITH for use in a larger statement.

When to use it

Use it to organize multi-step transformations and improve readability.

Practical example

WITH monthly_sales AS (...) SELECT * FROM monthly_sales;

Advanced analysis

Window function

Meaning

A function that calculates across related rows while preserving individual result rows.

When to use it

Use it for ranks, running totals, moving averages, and previous-period comparisons.

Practical example

SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at)