CPSC 343 Review Information — Exam 1

Exam 1 is a written in-class (one hour) exam covering querying with SQL. Expect two kinds of questions:

short answer questions about SQL concepts — definitions, how a construct behaves, and when to use one construct over another
reading and writing SQL queries, as in the homeworks — writing a query to answer a described question, determining and expressing in plain language what a given query computes, or predicting the result of a query on given data

Short answers should be short — generally 1-3 sentences, or 1-2 sentences per part for a question with several parts. A list rather than complete sentences is fine where appropriate. Time is limited, so don't write more than necessary!

The topics covered on the exam are listed below. The items under "Self check" are study goals — examples of the kinds of things you should be able to do rather than actual exam questions or a complete list of what the exam might ask. As a starting point for studying, try to explain each topic without notes (including an example of your own where appropriate) and then answer the associated self check items. As a pacing guide, you should be able to start writing answers for definition/recall questions right away and "read/write a query" questions after a minute or two of thinking.

For practice reading and writing queries, see the "Example question types" and "Practice" sections at the end.

SQL Foundations

Topics:

SQL sublanguages: DQL, DML, DDL, DCL
Terminology and concepts: tables, rows, columns; primary key, foreign key; notation for relational schemas
Bags vs. sets: duplicates in query results
Writing literal values: strings, DATE, TIME, TIMESTAMP
NULL and three-valued logic

Self check:

Name the SQL sublanguages and describe what each is for.
Explain the difference between bag and set semantics and which one SQL tables and query results follow.
Explain what happens when an operation or comparison involves NULL.
Describe how AND, OR, and NOT work in three-valued logic.
Explain why = NULL doesn't work and what to use instead.

Basic Queries

Topics:

SELECT ... FROM ... WHERE
Expressions and renaming in SELECT (AS); *
WHERE conditions: comparisons, AND/OR/NOT, IS [NOT] NULL, [NOT] LIKE with % and _ wildcards
ORDER BY (ASC/DESC, multiple columns), DISTINCT, LIMIT

Self check:

Describe what each of the SELECT, FROM, and WHERE clauses contributes to the result of a query.
Explain the meaning of the % and _ wildcards in a LIKE pattern.
Explain when ORDER BY is applied relative to SELECT and why that means you can sort on a column that isn't in the result.
Explain how DISTINCT determines which rows are duplicates.
Explain why LIMIT is usually combined with ORDER BY.

Joins

Topics:

CROSS JOIN (Cartesian product)
Inner joins: JOIN ... ON, JOIN ... USING, NATURAL JOIN
Self-joins and aliases
Outer joins: LEFT, RIGHT, FULL (and MySQL's lack of support for FULL)
ON vs. WHERE conditions in outer joins
Anti-joins (LEFT JOIN ... IS NULL)
Best practices: when to use which join form

Self check:

Explain the relationship between CROSS JOIN, CROSS JOIN with WHERE, and JOIN ... ON.
Explain the difference between JOIN ... ON, JOIN ... USING, and NATURAL JOIN, including which columns appear in the result.
Explain why NATURAL JOIN is risky and why JOIN ... ON or JOIN ... USING is preferred.
Explain what a self-join is and why it requires aliases.
Explain when an alias is required.
Explain when an outer join is needed instead of an inner join.
Distinguish LEFT, RIGHT, and FULL outer joins and explain why LEFT JOIN is preferred over RIGHT JOIN.
Explain why moving a condition between ON and WHERE can change the result of an outer join but not an inner join.
Explain what an anti-join is and how it is constructed.
Describe the guidelines for what belongs in an ON condition and when to use JOIN vs. CROSS JOIN with WHERE.

Aggregation and Grouping

Topics:

Aggregation operators: COUNT, SUM, AVG, MIN, MAX; DISTINCT within an aggregate
COUNT(*) vs. COUNT(column) vs. COUNT(DISTINCT column)
GROUP BY; what may appear in SELECT with grouping; grouping by the primary key
HAVING vs. WHERE
Order in which the clauses of a query are applied
NULL in aggregation and grouping

Self check:

Explain what an aggregation operator does, with and without GROUP BY.
Distinguish COUNT(*), COUNT(column), and COUNT(DISTINCT column).
Explain how NULL is handled in aggregation and in grouping, and what an aggregate on an empty set returns.
Explain which columns may appear in SELECT and HAVING in a grouped query and why.
Distinguish HAVING from WHERE, including when each is applied and what kinds of conditions each can contain.
Describe the order in which the clauses of a query (FROM, WHERE, GROUP BY, HAVING, ORDER BY, SELECT) are applied.
Explain how grouping by a primary key differs from grouping by a non-key column (such as a name) in terms of the groups that are formed.

Subqueries

Topics:

Subqueries in WHERE, FROM, and SELECT
Single-value subqueries used as constants
Operations on subquery results: EXISTS, IN, ALL, ANY (and NOT)
Correlated vs. uncorrelated subqueries
Strategies for writing queries

Self check:

Describe where subqueries can be used in a query and any requirements for each placement (e.g. a single value, an alias).
Explain the meaning of EXISTS, IN, ALL, and ANY when applied to a subquery result.
Explain what makes a subquery correlated and how that affects how it must be evaluated.
Explain why NOT IN can give unexpected results when the subquery may contain NULLs and what to use instead.

Set Operations

Topics:

UNION, INTERSECT, EXCEPT and their ALL versions
When duplicates are retained or removed
Requirements for combining result sets
Best practices: set operation vs. join vs. subquery

Self check:

Explain the difference between a join and a set operation in terms of the result.
Describe what each of UNION, INTERSECT, and EXCEPT computes and how the ALL versions differ in their handling of duplicates.
Describe the requirements for the two queries combined by a set operation.
Describe the guidelines for choosing between a set operation, a join, and a subquery.

Example Question Types

Beyond the conceptual questions above, expect questions that ask you to apply your knowledge to a given schema, query, or data. For example:

Write a query to answer a described question.
Given a query, describe in plain language what it computes.
Given a query and a small table (or tables) of data, determine the result — including the effect of NULLs, DISTINCT, and ORDER BY.
Write a LIKE pattern matching a described set of values.
Determine which columns a given NATURAL JOIN actually matches on, and whether that is what was intended.
Given a query using an outer join, predict which rows appear in the result and which columns are NULL; determine how moving a condition between ON and WHERE changes the result.
Determine what each COUNT in a given query counts.
Identify the error in a given grouped query (e.g. a non-grouped column in SELECT or a condition in the wrong clause) and fix it.
Trace the clauses of a given query in the order they are applied and explain what each step produces.
Determine whether a given nested query is correlated and explain why.
Given two tables of values, determine the result of UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, or EXCEPT ALL.
Choose between a join, a subquery, and a set operation for a described problem and justify the choice.
Given two queries, determine whether they are equivalent and if not, describe a situation where they differ.

Practice

Work through the homework problems and the example problems from class (in the slides), including any you didn't complete the first time. Write each query on paper first, then run it to check.
For queries you've already done, try solving them a different way (e.g. a subquery instead of a join, a set operation instead of OR, an anti-join instead of NOT EXISTS) and convince yourself the two are equivalent — or figure out why they aren't.
For each query you write, be able to explain in plain language what it computes.