Chapter 5 Quiz: SQL Fundamentals

Test your understanding of SELECT, WHERE, ORDER BY, scalar functions, CASE, NULL handling, and result set control. Choose the best answer for each question.

Question 1

What does the following query return?

SELECT * FROM MERIDIAN.BRANCHES;

A) Only the first row of the BRANCHES table B) All columns and all rows from the BRANCHES table C) Only the primary key columns from the BRANCHES table D) An error, because you must specify column names

Answer: B. The asterisk (*) is a wildcard that selects all columns, and without a WHERE clause, all rows are returned.

Question 2

Why should you avoid using SELECT * in production application code?

A) It causes a syntax error on DB2 for z/OS B) It locks the entire table for the duration of the query C) It retrieves unnecessary columns and breaks silently if the table structure changes D) It is slower than naming every column individually, regardless of the number of columns

Answer: C. SELECT * retrieves all columns (wasting resources for unneeded data) and makes your application vulnerable to schema changes.

Question 3

Which of the following correctly finds rows where MIDDLE_NAME has no value?

A) WHERE MIDDLE_NAME = NULL B) WHERE MIDDLE_NAME = '' C) WHERE MIDDLE_NAME IS NULL D) WHERE MIDDLE_NAME EQUALS NULL

Answer: C. NULL cannot be tested with the equals operator; you must use IS NULL.

Question 4

What is the result of the expression 5 + NULL in DB2?

A) 5 B) 0 C) NULL D) An error

Answer: C. Any arithmetic operation involving NULL produces NULL.

Question 5

Given the following query, what is the sort order?

SELECT FIRST_NAME, LAST_NAME, STATE_CODE
FROM MERIDIAN.CUSTOMERS
ORDER BY STATE_CODE, LAST_NAME DESC;

A) State code descending, then last name descending B) State code ascending, then last name descending C) State code ascending, then last name ascending D) Last name descending, then state code ascending

Answer: B. The default sort direction is ascending. DESC applies only to the column it follows (LAST_NAME).

Question 6

What does the BETWEEN predicate include?

A) Only values strictly between the two bounds (exclusive) B) Values including the lower bound but excluding the upper bound C) Values including both the lower and upper bounds (inclusive) D) It depends on the data type

Answer: C. BETWEEN is inclusive on both ends.

Question 7

How does DB2 evaluate the following WHERE clause?

WHERE STATE_CODE = 'CT' OR STATE_CODE = 'MA' AND CUSTOMER_TYPE = 'I'

A) (STATE_CODE = 'CT' OR STATE_CODE = 'MA') AND CUSTOMER_TYPE = 'I' B) STATE_CODE = 'CT' OR (STATE_CODE = 'MA' AND CUSTOMER_TYPE = 'I') C) The query produces an error due to ambiguity D) The result depends on the DB2 platform (z/OS vs. LUW)

Answer: B. AND has higher precedence than OR, so it is evaluated first. The clause returns all CT customers plus only individual MA customers.

Question 8

What is the purpose of the COALESCE function?

A) It concatenates multiple string values B) It returns the first non-NULL value from a list of arguments C) It converts a value to a different data type D) It eliminates duplicate rows from a result set

Answer: B. COALESCE evaluates its arguments left to right and returns the first one that is not NULL.

Question 9

Which DB2 syntax limits a result set to 10 rows?

A) LIMIT 10 B) TOP 10 C) FETCH FIRST 10 ROWS ONLY D) ROWNUM <= 10

Answer: C. DB2 uses the SQL standard FETCH FIRST N ROWS ONLY syntax. LIMIT is MySQL/PostgreSQL, TOP is SQL Server, and ROWNUM is Oracle.

Question 10

What does NULLIF(X, 0) return?

A) 0 if X is NULL B) NULL if X is 0; otherwise X C) X if X is not equal to 0; otherwise an error D) Always returns NULL

Answer: B. NULLIF returns NULL if the two arguments are equal; otherwise it returns the first argument.

Question 11

What does the following CASE expression return when RISK_RATING is 3?

CASE
    WHEN RISK_RATING <= 2 THEN 'Low'
    WHEN RISK_RATING <= 4 THEN 'Medium'
    WHEN RISK_RATING = 5  THEN 'High'
END

A) 'Low' B) 'Medium' C) 'High' D) NULL

Answer: B. DB2 evaluates WHEN conditions in order. RISK_RATING 3 is not <= 2, but it is <= 4, so 'Medium' is returned.

Question 12

What happens when a CASE expression has no ELSE clause and no WHEN condition matches?

A) DB2 returns an empty string B) DB2 returns 0 C) DB2 returns NULL D) DB2 raises an error

Answer: C. Without an ELSE clause, an unmatched CASE returns NULL.

Question 13

In DB2, which of the following is a valid way to concatenate two strings?

A) FIRST_NAME + ' ' + LAST_NAME B) FIRST_NAME || ' ' || LAST_NAME C) FIRST_NAME & ' ' & LAST_NAME D) CONCAT_WS(' ', FIRST_NAME, LAST_NAME)

Answer: B. DB2 uses the || operator for string concatenation. The + operator is for arithmetic only. CONCAT_WS is not a DB2 function.

Question 14

What does SELECT DISTINCT STATE_CODE, CITY FROM MERIDIAN.CUSTOMERS eliminate?

A) Rows where STATE_CODE is duplicated, regardless of CITY B) Rows where CITY is duplicated, regardless of STATE_CODE C) Rows where the combination of STATE_CODE and CITY is duplicated D) Nothing — DISTINCT does not work with multiple columns

Answer: C. DISTINCT applies to the entire row. Only rows where all selected columns match are considered duplicates.

Question 15

What value does the DB2 special register CURRENT DATE return?

A) The date and time when the database was created B) The current system date (no time component) C) The current date and time as a TIMESTAMP D) The date of the last database backup

Answer: B. CURRENT DATE returns the current date as a DATE type. CURRENT TIMESTAMP returns date and time.

Question 16

Which function extracts the year from a DATE column?

A) EXTRACT(YEAR FROM date_column) B) YEAR(date_column) C) DATEPART(YEAR, date_column) D) Both A and B are valid in DB2

Answer: D. DB2 supports both the YEAR() function and the EXTRACT(YEAR FROM ...) syntax.

Question 17

What is the danger of NOT IN when the subquery might return NULL values?

A) It causes a syntax error B) It returns too many rows C) It returns no rows at all, because the comparison with NULL yields UNKNOWN D) It raises a runtime warning but returns correct results

Answer: C. If any value in the NOT IN list is NULL, the entire NOT IN predicate evaluates to UNKNOWN for every row, and no rows are returned.

Question 18

On DB2 for LUW with ascending ORDER BY, where do NULL values appear by default?

A) First (at the top) B) Last (at the bottom) C) They are excluded from the result D) Their position depends on the column's data type

Answer: B. On DB2 for LUW, NULLs sort high by default, so they appear last in ascending order.

Question 19

What does the following expression return?

SELECT SUBSTR('MNB-CHK-000001', 5, 3) FROM SYSIBM.SYSDUMMY1;

A) 'MNB' B) 'CHK' C) '-CH' D) 'CHK-'

Answer: B. SUBSTR starts at position 5 (the 'C') and returns 3 characters: 'CHK'.

Question 20

Which is the correct DB2 syntax for skipping the first 10 rows and returning the next 5?

A) LIMIT 5 OFFSET 10 B) OFFSET 10 ROWS FETCH FIRST 5 ROWS ONLY C) SKIP 10 TAKE 5 D) FETCH ROWS 11 TO 15

Answer: B. DB2 uses OFFSET with FETCH FIRST for pagination.

Question 21

What does TRIM(BOTH ' ' FROM ' Hello ') return?

A) 'Hello ' B) ' Hello' C) 'Hello' D) 'Hello '

Answer: C. TRIM with BOTH removes the specified character from both the leading and trailing ends of the string.

Question 22

True or False: Without an ORDER BY clause, DB2 guarantees that rows are returned in the order they were inserted.

A) True B) False

Answer: B (False). Without ORDER BY, the row order is undefined and may vary depending on the access path chosen by the optimizer.

Question 23

What is the purpose of SYSIBM.SYSDUMMY1 in DB2?

A) It stores system configuration parameters B) It is a single-row table used for evaluating expressions that do not reference user tables C) It stores temporary query results D) It is a view of all user-defined tables in the database

Answer: B. SYSDUMMY1 has exactly one row, making it useful for SELECT expressions like SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1.

Question 24

Which of the following correctly converts CURRENT_BALANCE (DECIMAL) to an INTEGER in DB2?

A) INT(CURRENT_BALANCE) B) CAST(CURRENT_BALANCE AS INTEGER) C) INTEGER(CURRENT_BALANCE) D) Both B and C are valid

Answer: D. Both CAST(... AS INTEGER) and the INTEGER() function are valid ways to convert to integer in DB2.

Question 25

A query includes ORDER BY 3 DESC. What does the 3 refer to?

A) The third row of the result set B) The third column listed in the SELECT clause C) The third column defined in the table's CREATE TABLE statement D) A literal value of 3

Answer: B. Positional references in ORDER BY refer to the column's position in the SELECT list.

Scoring Guide

Quiz for Chapter 5 of "IBM DB2: From First Query to Enterprise Architecture"