SQL Query Fundamentals

Introduction

SQL (Structured Query Language) is the principal language used to retrieve and analyze data stored in relational databases. For business analysts, SQL is not merely a technical tool for extracting records. It is a language for expressing questions about customers, products, transactions, employees, performance, and risk in a form that a database management system can execute efficiently.

A typical analytical question can be translated into a sequence of operations: identify the relevant table, select required columns, filter rows, calculate derived values, group records, summarize measures, and sort the result. SQL provides clauses and functions for each of these activities. This chapter develops those capabilities using standard SQL concepts. Particular database products—such as PostgreSQL, MySQL, SQL Server, and Oracle—may differ in function names, date syntax, and null-handling functions, so production work should always be checked against the relevant dialect.

The examples use a simplified sales database. Assume the following tables:

customers

products

sales

The tables are related through customer_id and product_id. Querying one table is the main concern of this chapter; joining tables is treated in greater depth elsewhere, although a brief case application will use a join where appropriate.

Learning Objectives

By the end of this chapter, students should be able to:

1. Construct queries using SELECT and FROM.
2. Filter rows using WHERE and comparison, logical, and membership operators.
3. Sort and limit results using ORDER BY and related clauses.
4. Create calculated columns with arithmetic and conditional expressions.
5. Apply scalar, text, numeric, date, and aggregate functions.
6. Explain why NULL is not equivalent to zero or an empty string.
7. Summarize records using GROUP BY and filter groups using HAVING.
8. Interpret SQL's logical processing order and avoid common errors.
9. Design reliable, readable queries for business analysis.

1. The Relational Query Perspective

A relational table consists conceptually of rows and columns. A row represents an observation, such as one sale, while a column represents an attribute of that observation. SQL queries produce a result relation: a temporary set of rows and columns generated from stored data.

The basic query is:

SELECT customer_name, region
FROM customers;

SELECT specifies the columns to return, and FROM specifies the source table. SQL is declarative: the analyst states what result is required rather than prescribing the database's physical retrieval algorithm. The query optimizer may use indexes, parallel execution, or alternative access paths while preserving the requested result.

A wildcard returns all columns:

SELECT *
FROM products;

Although convenient for exploration, SELECT * is usually poor practice in production reports because it returns unnecessary data, makes outputs unstable when the schema changes, and can expose sensitive columns. Explicit column lists are clearer and safer.

Removing duplicate values

SQL normally preserves duplicate rows in a result. DISTINCT removes duplicate combinations of selected columns:

SELECT DISTINCT region
FROM customers;

DISTINCT applies to the entire selected row. Therefore:

SELECT DISTINCT region, segment
FROM customers;

returns each distinct region–segment combination, not distinct regions and distinct segments independently. DISTINCT can be computationally expensive on large results, so it should be used deliberately rather than as a substitute for understanding duplicate-producing logic.

Naming output columns

Aliases improve readability:

SELECT customer_name AS customer,
       region AS sales_region
FROM customers;

An alias changes the displayed label, not the underlying column. Some database systems allow quoted aliases containing spaces, but simple identifiers are preferable in reusable analytical workflows.

2. Filtering Rows with WHERE

WHERE restricts individual rows before grouping or aggregation:

SELECT sale_id, sale_date, quantity
FROM sales
WHERE quantity >= 10;

Common comparison operators are =, <>, <, >, <=, and >=. Text values normally require single quotation marks:

SELECT customer_id, customer_name
FROM customers
WHERE region = 'West';

SQL string comparison and case sensitivity vary by database collation. Analysts should not assume that 'west' and 'West' are equivalent.

Logical operators

AND requires both conditions to be true; OR requires at least one; NOT reverses a condition.

SELECT sale_id, quantity, discount_rate
FROM sales
WHERE quantity >= 10
  AND discount_rate <= 0.15;

AND generally has higher precedence than OR. Parentheses should be used when the intended logic could be misunderstood:

SELECT customer_id, region, segment
FROM customers
WHERE region = 'West'
  AND (segment = 'Corporate' OR segment = 'Strategic');

Without parentheses, the query may be interpreted as:

(region = 'West' AND segment = 'Corporate') OR segment = 'Strategic'

which includes Strategic customers from every region.

Range and membership conditions

BETWEEN includes both boundary values:

SELECT sale_id, sale_date
FROM sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-03-31';

For timestamps, a half-open interval is often safer because it includes the start and excludes the next boundary. For example, a system storing timestamps can use:

WHERE sale_date >= '2025-01-01'
  AND sale_date <  '2025-04-01'

IN tests membership in a list:

SELECT customer_id, customer_name
FROM customers
WHERE region IN ('North', 'West');

LIKE supports pattern matching. The percent sign represents any sequence of characters, and the underscore represents one character:

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A%';

This returns names beginning with A. Wildcard behavior and case sensitivity can vary by system.

3. Sorting and Presenting Results

ORDER BY sorts the result after it has been selected:

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC;

ASC is ascending and is the default; DESC is descending. Multiple sort keys are evaluated from left to right:

SELECT customer_name, region, segment
FROM customers
ORDER BY region ASC, customer_name ASC;

Rows with the same region are then ordered by customer name. NULL ordering differs among database products, so explicit handling may be required when null placement matters.

A query should not be assumed to return rows in insertion order or primary-key order unless ORDER BY is specified. This is an important professional principle: an unordered relational result has no guaranteed presentation sequence.

Many systems provide a row-limiting clause, such as:

SELECT product_name, unit_price
FROM products
ORDER BY unit_price DESC
FETCH FIRST 5 ROWS ONLY;

Other systems use LIMIT or TOP. The syntax is dialect-specific. Limiting without ORDER BY produces an arbitrary subset rather than a reliable top-five result.

4. Expressions and Derived Columns

SQL can calculate values from existing columns. For a sale, suppose gross value is quantity multiplied by unit price, and net value incorporates a discount. When unit_price is available through a related product table, the expression may be written as:

SELECT quantity,
       unit_price,
       discount_rate,
       quantity * unit_price AS gross_value,
       quantity * unit_price * (1 - discount_rate) AS net_value
FROM sales_detail;

Here sales_detail is assumed to contain unit_price. Arithmetic operators include +, -, *, and /. Parentheses make the intended order explicit. Numeric precision matters: monetary values should use an appropriate fixed-precision DECIMAL type rather than an approximate floating-point type when exact financial reporting is required.

An alias usually cannot be referenced in another expression in the same SELECT list because SELECT expressions are conceptually evaluated together. A nested query or common table expression can make a derived value reusable:

SELECT sale_id, net_value
FROM (
    SELECT sale_id,
           quantity * unit_price * (1 - discount_rate) AS net_value
    FROM sales_detail
) AS calculated_sales
WHERE net_value > 1000;

A derived column is not automatically stored in the database. It is calculated when the query runs unless materialized through a view, table, or other design mechanism.

5. Functions for Transformation

Functions accept values and return values. They are commonly divided into scalar functions, which operate on one row at a time, and aggregate functions, which operate across multiple rows.

Text functions

Common text operations include:

SELECT customer_name,
       UPPER(customer_name) AS uppercase_name,
       LOWER(region) AS normalized_region,
       LENGTH(customer_name) AS name_length
FROM customers;

Function names and length semantics may differ. For multilingual data, character length and byte length are not necessarily the same. TRIM removes leading or trailing whitespace in many systems:

SELECT TRIM(customer_name) AS cleaned_name
FROM customers;

Text normalization should be performed thoughtfully. Converting case may support matching, but it does not prove that two differently spelled names identify the same entity.

Numeric functions

ROUND, CEILING, FLOOR, ABS, and MOD are widely available, although exact syntax varies:

SELECT unit_price,
       ROUND(unit_price, 2) AS price_rounded,
       ABS(unit_price - 100) AS distance_from_100
FROM products;

ROUND(value, 2) requests two decimal places in many systems. Rounding for display should be distinguished from rounding used in calculations; repeated intermediate rounding can produce cumulative differences.

Date and time functions

Dates are used for filtering, grouping, and interval analysis. Typical functions extract components such as year or month:

SELECT sale_date,
       EXTRACT(YEAR FROM sale_date) AS sale_year,
       EXTRACT(MONTH FROM sale_date) AS sale_month
FROM sales;

The syntax is not universal. Date calculations also require attention to time zones, calendars, and whether sale_date is a date or timestamp. A date is not interchangeable with a formatted text string.

6. NULL and Three-Valued Logic

NULL represents missing, unknown, or inapplicable information. It is not the same as zero, an empty string, or the word 'NULL'. Because a comparison involving an unknown value cannot be established as true or false, SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN.

For example, this condition does not identify missing discounts:

WHERE discount_rate = NULL

The correct test is:

WHERE discount_rate IS NULL

and the non-null test is:

WHERE discount_rate IS NOT NULL

A WHERE clause returns only rows for which the condition is TRUE. Rows producing FALSE or UNKNOWN are excluded. Consequently, a query such as:

WHERE discount_rate <> 0.10

does not include rows where discount_rate is NULL.

COALESCE returns the first non-null expression:

SELECT sale_id,
       COALESCE(discount_rate, 0) AS assumed_discount_rate
FROM sales;

This substitutes zero, but the substitution is a business assumption. It is valid only when a missing discount is defined to mean no discount. Otherwise, the query may conceal data-quality problems. NULLIF returns NULL when two expressions are equal and is sometimes used to avoid division by zero:

SELECT revenue / NULLIF(quantity, 0) AS revenue_per_unit
FROM product_summary;

If quantity is zero, NULLIF produces NULL rather than zero, avoiding an error in systems that reject division by zero. The resulting NULL still requires interpretation.

7. Aggregate Functions and Summarization

Aggregate functions summarize multiple rows. Common aggregates are COUNT, SUM, AVG, MIN, and MAX.

SELECT COUNT(*) AS number_of_sales,
       SUM(quantity) AS units_sold,
       AVG(quantity) AS average_units,
       MIN(quantity) AS smallest_order,
       MAX(quantity) AS largest_order
FROM sales;

COUNT(*) counts rows. COUNT(column) counts non-null values in that column. SUM, AVG, MIN, and MAX generally ignore NULL values. Therefore, AVG(discount_rate) describes the average of recorded discounts, not necessarily the average discount across all sales if missing values exist.

The arithmetic mean is conceptually:

where xᵢ is an observed value and n is the number of included non-null observations. SQL AVG follows this general principle, subject to the database's data type and numeric precision rules. Averages can be misleading when observations represent unequal quantities. For example, average transaction discount and discount weighted by sales value answer different business questions.

COUNT(DISTINCT customer_id) counts unique non-null customers:

SELECT COUNT(DISTINCT customer_id) AS active_customers
FROM sales;

A query mixing an aggregate and a non-aggregated column generally requires GROUP BY.

8. GROUP BY and HAVING

GROUP BY partitions rows into groups and calculates aggregates separately for each group:

SELECT sales_rep,
       COUNT(*) AS transactions,
       SUM(quantity) AS units_sold
FROM sales
GROUP BY sales_rep;

Each selected expression must normally be either an aggregate or a grouping expression. Grouping by a column with NULL creates a group for NULL values in many systems; that group represents records with missing group information.

Multiple grouping columns produce one group for each distinct combination:

SELECT sales_rep, product_id,
       SUM(quantity) AS units_sold
FROM sales
GROUP BY sales_rep, product_id;

WHERE filters rows before grouping. HAVING filters groups after aggregation:

SELECT sales_rep,
       SUM(quantity) AS units_sold
FROM sales
WHERE sale_date >= '2025-01-01'
GROUP BY sales_rep
HAVING SUM(quantity) >= 100;

The WHERE condition excludes old transactions before totals are calculated. HAVING then retains only representatives whose qualifying total is at least 100 units. Using HAVING for a row-level condition is often inefficient and conceptually incorrect.

A useful logical processing model is:

1. FROM and JOIN identify source rows.
2. WHERE filters individual rows.
3. GROUP BY forms groups.
4. Aggregate functions calculate group results.
5. HAVING filters groups.
6. SELECT forms output expressions.
7. DISTINCT removes duplicate output rows if requested.
8. ORDER BY sorts the result.
9. LIMIT, FETCH, or TOP restricts the final output.

This is a conceptual order, not necessarily the physical execution order chosen by the optimizer. It explains why a SELECT alias may not be available in WHERE and why aggregate results cannot normally be filtered with WHERE.

9. Conditional Logic with CASE

CASE expresses conditional business logic. The searched form is especially flexible:

SELECT sale_id,
       quantity,
       CASE
           WHEN quantity >= 20 THEN 'High volume'
           WHEN quantity >= 10 THEN 'Medium volume'
           ELSE 'Low volume'
       END AS order_class
FROM sales;

Conditions are tested in order. Once a condition is true, later conditions are not used for that row. Therefore, order matters: placing quantity >= 10 before quantity >= 20 would classify every quantity of 20 or more as Medium volume.

CASE can be used in aggregation to create conditional measures:

SELECT
    SUM(CASE WHEN discount_rate > 0 THEN 1 ELSE 0 END) AS discounted_sales,
    SUM(CASE WHEN quantity >= 10 THEN quantity ELSE 0 END) AS high_volume_units
FROM sales;

This technique is often called conditional aggregation. In systems supporting the FILTER clause, an equivalent expression may be available, but CASE is broadly portable.

CASE can also make missing data visible:

SELECT sale_id,
       CASE
           WHEN discount_rate IS NULL THEN 'Missing'
           WHEN discount_rate = 0 THEN 'No discount'
           ELSE 'Discount applied'
       END AS discount_status
FROM sales;

Categorization thresholds should be documented because they affect managerial interpretation and may influence incentives or performance evaluations.

10. An Integrated Applied Case

Background

The fictional Northstar Retail Company sells business equipment through several representatives. Management wants to identify representatives who generated at least 100 units of sales during the first quarter of 2025, while distinguishing transactions with missing discount data.

Problem

A simple report of all transactions is too detailed for management. The required output is one row per representative containing transaction count, units sold, average recorded discount, and the number of discounted transactions. Representatives should be evaluated only on sales made during the first quarter.

Analysis and approach

The query is:

SELECT
    sales_rep,
    COUNT(*) AS transaction_count,
    SUM(quantity) AS units_sold,
    ROUND(AVG(discount_rate), 4) AS average_recorded_discount,
    SUM(CASE
            WHEN discount_rate > 0 THEN 1
            ELSE 0
        END) AS discounted_transactions
FROM sales
WHERE sale_date >= '2025-01-01'
  AND sale_date <  '2025-04-01'
GROUP BY sales_rep
HAVING SUM(quantity) >= 100
ORDER BY units_sold DESC;

The half-open date interval includes 1 January through 31 March for date-time data. WHERE limits the source rows. GROUP BY creates one group per representative. SUM, COUNT, and AVG produce measures. The CASE expression counts rows with a positive recorded discount; missing values fall into ELSE 0 and are therefore not treated as discounted. HAVING retains only representatives meeting the volume threshold. ORDER BY ranks the qualifying representatives.

Outcome and interpretation

The result supports a management discussion about high-volume representatives. However, average_recorded_discount must be labeled carefully: it excludes NULL discounts rather than treating them as zero. If missing discounts indicate incomplete records, a separate data-quality report should be produced. The report also measures units, not revenue or profit; a representative selling many low-value products could rank above one selling fewer high-margin products.

Learning points

1. Business questions must be translated into explicit row, group, and output requirements.
2. Date boundaries should be chosen consistently with the data type.
3. HAVING is appropriate for thresholds applied to aggregate results.
4. NULL handling changes the meaning of averages and counts.
5. A technically correct query can still support a poor decision if the chosen measure does not match the managerial objective.

11. Quality, Performance, and Governance

Readable SQL is easier to validate. Use meaningful aliases, consistent indentation, and one condition per line. Comment unusual business rules, especially assumptions about NULL values, date boundaries, and classification thresholds.

Validation should include row-count checks, duplicate checks, boundary tests, and comparison with known totals. Test records exactly at thresholds such as quantity = 10 or sale_date = '2025-04-01'. Check whether NULL values are expected and whether aggregates exclude them appropriately.

Filtering early can reduce the amount of data processed, although the optimizer may transform queries independently. Avoid unnecessary DISTINCT, select only required columns, and filter on indexed columns where appropriate. Functions applied to a filtered column can sometimes prevent efficient index use, depending on the database system.

SQL also has governance implications. Queries may expose personally identifiable information, confidential sales performance, or commercially sensitive prices. Apply least-privilege access, avoid exporting unnecessary fields, protect credentials, and follow organizational retention and privacy policies. Query logs and shared notebooks may themselves contain sensitive values.

AI-assisted SQL generation can accelerate drafting, but generated queries require human review. An AI system may misunderstand business definitions, mishandle NULL, invent column names, or omit access-control considerations. Analysts remain responsible for validating logic, data provenance, and the consequences of reported results.

Challenges and Limitations

SQL query fundamentals do not eliminate broader analytical difficulties. A query may be syntactically valid but semantically wrong because the analyst misunderstood the grain of a table. Counting rows in a transaction-line table, for example, may count items rather than orders. Aggregates may be distorted by duplicate rows introduced through joins. Missing data can create uncertainty rather than a value that should simply be replaced. Date and time zones can shift records across reporting periods. Finally, a descriptive summary does not establish causation: a representative's high sales may reflect territory, product mix, seasonality, or customer assignment.

Reliable analysis therefore combines SQL skill with data modeling, domain knowledge, validation, documentation, and ethical judgment.

Chapter Summary

SQL expresses business questions through relational operations. SELECT and FROM identify required output and source data; WHERE filters individual rows; ORDER BY controls presentation; expressions and functions transform values; and CASE implements conditional logic. Aggregate functions summarize observations, while GROUP BY produces one result per group and HAVING filters those groups. NULL represents missing or unknown information and requires IS NULL, IS NOT NULL, COALESCE, or other explicit treatment. SQL's logical processing order explains why row filters belong in WHERE and aggregate filters belong in HAVING. Professional query development also requires explicit assumptions, testing, performance awareness, privacy protection, and critical interpretation of measures.

Key Terms

SQL: Structured Query Language used to communicate with relational databases.

SELECT: Clause that specifies output columns or expressions.

FROM: Clause that identifies the source table or query.

WHERE: Clause that filters individual source rows.

ORDER BY: Clause that sorts the final result.

DISTINCT: Keyword that removes duplicate output rows.

Alias: Temporary name assigned to a column or table expression.

Expression: Combination of values, operators, and functions that produces a result.

Scalar function: Function evaluated separately for each row.

Aggregate function: Function that summarizes values across rows.

NULL: Marker representing missing, unknown, or inapplicable information.

COALESCE: Function returning the first non-null expression.

GROUP BY: Clause that partitions rows into groups for aggregation.

HAVING: Clause that filters groups after aggregation.

CASE: Conditional expression used to assign values according to rules.

Conditional aggregation: Aggregation performed over rows selected by a conditional expression.

Three-valued logic: SQL logic involving TRUE, FALSE, and UNKNOWN.

Grain: The level of detail represented by one row in a table.

Predicate: Expression that evaluates a condition used for filtering.

Query optimizer: Database component that selects an efficient execution strategy.

Review Questions

Short Answer Questions

1. What is the purpose of the SELECT and FROM clauses?
2. How does WHERE differ from HAVING?
3. Why must NULL be tested with IS NULL rather than = NULL?
4. What does COUNT(*) count, and how does it differ from COUNT(column_name)?
5. Why is ORDER BY necessary when a specific result order is required?

Long Answer Questions

1. Explain SQL's conceptual logical processing order and show how it guides query construction.
2. Discuss the effects of NULL on comparisons, averages, counts, and conditional logic.
3. Explain how GROUP BY, aggregate functions, and HAVING work together in business reporting.
4. Compare row-level filtering, grouping, and conditional aggregation using illustrative SQL examples.
5. Discuss the professional responsibilities involved in writing, validating, and governing analytical SQL queries.

Analytical / Application Questions

1. Write a query that reports each region's number of customers and retains only regions with at least 50 customers. Explain why the threshold belongs in HAVING.
2. A sales report treats every NULL discount as zero and reports an average discount of 3%. Analyze what assumptions this makes and propose a more transparent alternative.
3. Design a query that classifies transactions as Low, Medium, or High volume using thresholds of fewer than 10, 10–19, and at least 20 units. Explain why the order of CASE conditions matters.

Multiple Choice Questions with Answers

Question 1

Which clause filters individual rows before grouping?

A. HAVING

B. WHERE

C. ORDER BY

D. DISTINCT

Correct Answer: B. WHERE

Question 2

What is the correct way to test whether discount_rate is missing?

A. discount_rate = NULL

B. discount_rate <> NULL

C. discount_rate IS NULL

D. discount_rate IN NULL

Correct Answer: C. discount_rate IS NULL

Question 3

Which function counts all rows, including rows where a particular column is NULL?

A. COUNT(column_name)

B. COUNT(*)

C. SUM(column_name)

D. AVG(column_name)

Correct Answer: B. COUNT(*)

Question 4

A query must retain only representatives whose total quantity is at least 100. Which clause is appropriate after GROUP BY?

A. WHERE SUM(quantity) >= 100

B. ORDER BY SUM(quantity) >= 100

C. HAVING SUM(quantity) >= 100

D. DISTINCT SUM(quantity) >= 100

Correct Answer: C. HAVING SUM(quantity) >= 100

Question 5

What does COALESCE(discount_rate, 0) return when discount_rate is NULL?

A. NULL

B. An error

C. Zero

D. The text 'NULL'

Correct Answer: C. Zero

Question 6

Which statement about BETWEEN is correct in standard SQL usage?

A. It excludes both boundary values

B. It includes both boundary values

C. It includes only the lower boundary

D. It can be used only with text values

Correct Answer: B. It includes both boundary values

Question 7

What is the main purpose of CASE in a SELECT statement?

A. To create conditional logic

B. To permanently alter a table

C. To sort rows alphabetically

D. To create a database connection

Correct Answer: A. To create conditional logic

Question 8

Which query correctly returns the five most expensive products in systems supporting FETCH syntax?

A. SELECT product_name, unit_price FROM products FETCH FIRST 5 ROWS ONLY ORDER BY unit_price DESC

B. SELECT product_name, unit_price FROM products ORDER BY unit_price DESC FETCH FIRST 5 ROWS ONLY

C. SELECT product_name, unit_price FROM products WHERE unit_price DESC FETCH FIRST 5 ROWS ONLY

D. SELECT product_name, unit_price FROM products GROUP BY unit_price DESC FETCH FIRST 5 ROWS ONLY

Correct Answer: B. SELECT product_name, unit_price FROM products ORDER BY unit_price DESC FETCH FIRST 5 ROWS ONLY

Question 9

What does COUNT(DISTINCT customer_id) generally measure?

A. Total number of transaction rows

B. Number of unique non-null customers

C. Number of customers with NULL identifiers

D. Average number of customers per transaction

Correct Answer: B. Number of unique non-null customers

Question 10

Why are parentheses important in a condition combining AND and OR?

A. They determine the table's primary key

B. They control the intended logical grouping of conditions

C. They convert NULL to zero

D. They force the query to use an index

Correct Answer: B. They control the intended logical grouping of conditions