Chapter 4 review (Sections 4.1–4.7): the SELECT statement

Section 4.1

SELECT basics and data types

SQL describes the set of rows you want, rather than walking through a table one row at a time, and every column it works with has a declared type.

A set-oriented language

SQL is a set-oriented, or declarative, language: you describe the result set you want, and the DBMS figures out how to produce it. There is no loop to write, no counter to increment; you never tell it to walk through rows one at a time. The basic SELECT statement has three clauses. SELECT names the columns to return, or an asterisk for every column. FROM names the table. WHERE is optional and restricts the result to rows meeting a stated condition; leaving it off returns every row.

SELECT * FROM PRODUCT;

SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM   PRODUCT
WHERE  P_PRICE > 50;

Data types: character, numeric, and date

Every column is declared with a data type, which fixes the kind of value it may hold and how the DBMS is allowed to compare and calculate with it. Chapter 4 groups the basic types into three families. Character data holds text, declared as CHAR for a fixed length that is padded with spaces, or VARCHAR for a variable length with no padding; character values are always written in single quotes. Numeric data holds numbers meant for calculation, either whole numbers such as INTEGER, or numbers with a fractional part such as NUMERIC or DECIMAL, which take a precision and a scale, for example NUMERIC(8,2) for a dollar amount with two decimal places. Date data holds calendar dates, declared as DATE; a date is stored internally in its own format so the DBMS can compare and sort it chronologically, not alphabetically the way ordinary text would be, even though a date literal is still written in quotes.

The three data type families from Section 4.1.

Family	Example types	Holds
Character	CHAR, VARCHAR	Text, always quoted, compared alphabetically
Numeric	INTEGER, NUMERIC, DECIMAL	Numbers used in arithmetic, compared by value
Date	DATE	Calendar dates, quoted but compared chronologically

Because a date is its own type rather than plain text, comparison operators on it still work exactly as expected, just chronologically instead of alphabetically.

SELECT P_CODE, P_DESCRIPT, P_INDATE
FROM   PRODUCT
WHERE  P_INDATE > '2026-01-01';
-- returns products entered into inventory after January 1, 2026

Section 4.2

Artificial intelligence and SQL

How a modern query engine leans on the same machine learning ideas used everywhere else, both to run SQL faster and to let people skip writing SQL at all.

Query optimization and dynamic indexing

Query optimization is the step the DBMS takes before a query ever runs: it works out several different ways the same SQL statement could be executed, such as which index to use or which order to join tables in, estimates the cost of each option, and picks the cheapest plan. Dynamic indexing extends that idea to the indexes themselves; instead of every index being fixed forever at the moment a designer runs CREATE INDEX, the DBMS watches the queries it actually receives and creates, adjusts, or drops indexes on its own as that workload changes over time.

Machine learning integration and natural language query processing

Machine learning integration is the use of trained models inside the database engine itself, for example to predict how long a query will take, forecast tomorrow's workload, or tune configuration settings automatically, work that a traditional optimizer would otherwise handle with a fixed set of hand-written rules. Natural language query processing lets a person ask a question in ordinary English rather than write SQL directly; software sitting in front of the database, often built on a large language model, translates that plain-language request into an executable SQL statement behind the scenes.

Supervised learning and unsupervised learning

Supervised learning is a machine learning approach in which a model trains on labeled examples, each input paired with the answer that was actually correct, so it can predict the answer for a new input it has never seen; a query optimizer might use it by training on past queries paired with how long each one truly took to run. Unsupervised learning is the opposite case: the model is given data with no correct answer attached and has to find structure in it on its own, for example grouping similar queries into clusters to reveal usage patterns nobody defined in advance.

Same goal, opposite starting point.

Approach	Trains on	Example use in a DBMS
Supervised learning	Labeled examples, each with a known correct answer	Predicting a new query's running time from past queries and their actual times
Unsupervised learning	Unlabeled data, no correct answer given	Clustering similar queries together to surface a usage pattern

Section 4.3

Refining the result

Two small additions that change what a query returns, and what its columns are called.

DISTINCT

DISTINCT removes duplicate rows from the result, written right after SELECT. It applies to the whole row, not to a single column: with two columns listed, a row is only dropped if both values duplicate an earlier row.

SELECT V_CODE FROM PRODUCT;
-- a vendor supplying five products appears five times

SELECT DISTINCT V_CODE FROM PRODUCT;
-- each vendor code appears once

Aliases

An alias is an alternative name for a column or table inside a query, written with AS. It matters most on a computed column, since a calculation has no name of its own: without an alias the heading is whatever the system generates, which is not something you would want on a report.

SELECT P_PRICE * P_QOH AS TOTAL_VALUE
FROM   PRODUCT;

Section 4.4

Combining and refining conditions

Three operators borrowed from Boolean algebra, the fixed order SQL evaluates them in, and two more ways to narrow a WHERE clause.

AND, OR, and NOT

The logical operators AND, OR, and NOT come from Boolean algebra, the branch of mathematics dealing with true and false values. A condition joined with AND is true only when both sides are true. One joined with OR is true when at least one side is true. NOT reverses the truth value of whatever follows it, and is often paired with the special operators: NOT IN, NOT BETWEEN, IS NOT NULL.

Rules of precedence

The rules of precedence fix the order SQL evaluates an expression in: arithmetic first, then comparisons, then the logical operators, and among those, NOT before AND before OR. That default order is often not what a plain-English question intended, which is exactly why parentheses matter.

WHERE P_CODE = 'A' OR P_CODE = 'B' AND P_QOH = 0
-- means: code A regardless of quantity, OR code B with zero quantity

WHERE (P_CODE = 'A' OR P_CODE = 'B') AND P_QOH = 0
-- means: either code, but only with zero quantity

Both queries are valid SQL and they return different rows. Only the parentheses tell them apart, which is why it is worth writing them even when the default order happens to already agree with what you meant.

BETWEEN

BETWEEN tests whether a value falls inside a range, and both endpoints are included. It replaces a pair of comparison operators joined with AND. The lower value must be written first; reversing them returns nothing.

WHERE P_PRICE BETWEEN 50 AND 100
-- same as: WHERE P_PRICE >= 50 AND P_PRICE <= 100

IS NULL

IS NULL is the only correct way to test for a null, and IS NOT NULL the reverse. The obvious-looking = NULL never returns anything: a null is the absence of a value, not a value itself, so there is nothing for the equals sign to compare against. The query runs without error and quietly returns no rows, which makes it a hard mistake to catch. A null is also not the same thing as a zero or a blank space; both of those are real stored values an ordinary comparison would find.

WHERE V_CODE IS NULL;
WHERE V_CODE IS NOT NULL;

Section 4.5

Pattern matching

Searching for part of a value instead of an exact match.

LIKE and the wildcard character

LIKE compares a character value against a pattern rather than an exact value. A wildcard character is a special symbol placed inside that pattern to stand in for part of a value you do not want, or do not need, to spell out exactly. SQL has two of them. The percent sign, %, stands for any number of characters, including none. The underscore, _, stands for exactly one character.

WHERE V_NAME LIKE 'Smith%';   -- begins with Smith
WHERE V_NAME LIKE '%Smith%';  -- Smith anywhere in the value
WHERE V_NAME LIKE '_ana';     -- Dana, Jana -- but not Ana

Section 4.6

Testing against a list or a subquery

Two operators that both shorten a WHERE clause, but need different things to work with.

IN and EXISTS

IN checks whether a value matches any entry in a supplied list, a shorthand for a chain of conditions joined by OR. EXISTS returns true when the query written after it returns at least one row; it does not care what is in those rows, only whether any came back at all.

WHERE V_CODE IN (21344, 24288, 25595);

WHERE EXISTS
  (SELECT * FROM PRODUCT WHERE P_QOH = 0);

What each operator actually needs to work with.

Operator	Needs	Good for
IN	A list of actual values to compare against	Matching one column against a short, known list
EXISTS	Only the presence or absence of rows from a subquery	Asking whether anything at all matches, before running a bigger report

Section 4.7

Ordering results, and making changes stick

The clause that always runs last, and the guarantee that a multi-step change happens all together or not at all.

ORDER BY

ORDER BY sorts the rows of the result and is always the last clause in a SELECT statement, since it runs after the rows have already been chosen. The default is ascending order; DESC reverses a column into descending order, and ASC states the default explicitly.

SELECT P_DESCRIPT, P_PRICE
FROM   PRODUCT
ORDER BY P_PRICE DESC;

Transactions, COMMIT, and ROLLBACK

A transaction is a logical unit of work made of one or more SQL statements that must either all take effect or all be undone. Real operations often need several statements to stay consistent with each other: recording a sale means reducing inventory and writing an invoice line, and a database that completed one without the other would be wrong. COMMIT makes the transaction's changes permanent; ROLLBACK undoes every change made since the last commit, as if the transaction never happened.

UPDATE PRODUCT SET P_QOH = P_QOH - 1 WHERE P_CODE = 'AX-123';
INSERT INTO INVOICE_LINE (P_CODE, QUANTITY) VALUES ('AX-123', 1);
COMMIT;
-- or, if something looked wrong before committing:
ROLLBACK;