I85PREP
⌘ K
IFoA CB2 study materials
IFoA CB2 study resources

IFoA CB2 practice questions

One question at a time. Build your IFoA CB2 understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1CB2 syllabus domain (not enumerated in source)

In R, which function is used to read a comma-separated values file into a data frame with a single call?

  1. read.table()
  2. readLines()
  3. scan()
  4. read.csv() Correct answer.

Explanation

read.csv() is a wrapper around read.table() with sep set to a comma and header defaulting to TRUE, returning a data frame directly. read.table() would require the separator to be specified explicitly. scan() and readLines() return vectors, not data frames.

Question 2CB2 syllabus domain (not enumerated in source)

A data frame df has a column named total. Which expression returns just that column as a vector in base R?

  1. df$total Correct answer.
  2. df[["total", exact = FALSE]]
  3. df["total"]
  4. df[ , "total", drop = FALSE]

Explanation

The dollar operator extracts a single column and simplifies it to a vector. Indexing with drop = FALSE keeps a one-column data frame, and single-bracket selection with one argument selects columns as a data frame rather than a vector.

Question 3CB2 syllabus domain (not enumerated in source)

Which statement best describes a relational database table in the context of R data analysis?

  1. A matrix whose rows must always equal its columns
  2. A text file in which every line is a single unparsed string
  3. A nested list of vectors stored in memory and manipulated with lapply
  4. A collection of rows (records) and named columns (fields) where each column holds one variable Correct answer.

Explanation

A relational table is a rectangular structure of records and fields, closely analogous to an R data frame. Each column corresponds to a variable with a consistent type, and each row is one observation.

Question 4CB2 syllabus domain (not enumerated in source)

In SQL, which clause restricts the rows returned after grouping has been applied?

  1. WHERE
  2. HAVING Correct answer.
  3. LIMIT
  4. ORDER BY

Explanation

HAVING filters groups after aggregation, so it can reference aggregate functions such as COUNT or SUM. WHERE filters individual rows before grouping and cannot generally test aggregates.

Question 5CB2 syllabus domain (not enumerated in source)

Which R function returns the number of rows and columns of a data frame as a two-element integer vector?

  1. str()
  2. dim() Correct answer.
  3. length()
  4. nrow()

Explanation

dim() returns a vector of length two giving the number of rows and then the number of columns. length() on a data frame returns the number of columns, and nrow() returns only the row count.

Question 6CB2 syllabus domain (not enumerated in source)

Which of the following is a valid reason to normalise a relational database?

  1. To remove the need for primary keys
  2. To guarantee faster queries in every case
  3. To reduce redundant storage and avoid update anomalies Correct answer.
  4. To increase the number of duplicate rows

Explanation

Normalisation organises attributes into tables to eliminate redundancy, which in turn prevents insertion, update and deletion anomalies. Performance is not guaranteed to improve and may in fact require joins.

Question 7CB2 syllabus domain (not enumerated in source)

In R, what does the assignment x <- c(3, 1, 2) followed by sort(x) produce?

  1. The vector 3 2 1
  2. An error because c() cannot take numeric arguments
  3. The vector 1 2 3, with x itself unchanged Correct answer.
  4. The vector 1 2 3, with x reordered in place

Explanation

sort() returns a new sorted vector and does not modify its argument. To reorder x itself you would assign the result back, for example x <- sort(x).

Question 8CB2 syllabus domain (not enumerated in source)

Which R object type is most appropriate for storing a single logical flag such as whether a model has converged?

  1. A data frame with one row
  2. A length-one character vector
  3. A list containing a function
  4. A length-one logical vector Correct answer.

Explanation

A single TRUE or FALSE value is naturally stored as a length-one logical vector. Character vectors would store text, and data frames or lists add unnecessary structure for one flag.

Question 9CB2 syllabus domain (not enumerated in source)

A data frame df has columns age and group. Which base R expression returns the mean age separately for each group?

  1. tapply(df$age, df$group, mean) Correct answer.
  2. mapply(mean, df$age, df$group)
  3. sapply(df$age, mean)
  4. apply(df$age, df$group, mean)

Explanation

tapply() splits the first argument by the grouping factor in the second argument and applies the function to each subset. apply() works over margins of an array, while sapply() and mapply() do not perform grouped splitting in this way.

Question 10CB2 syllabus domain (not enumerated in source)

A SQL query joins a customers table to an orders table using an inner join. What is returned?

  1. All customers, with NULLs where no order exists
  2. The Cartesian product of both tables
  3. All orders, including those with no matching customer
  4. Only rows where a customer matches at least one order Correct answer.

Explanation

An inner join keeps only rows for which the join condition is satisfied in both tables. Customers without orders and orders without matching customers are both excluded.

Question 11CB2 syllabus domain (not enumerated in source)

In R, a vector v contains some NA values. Which expression computes the mean of the non-missing values?

  1. mean(na.omit(v), na.rm = FALSE)
  2. mean(v, na.rm = TRUE) Correct answer.
  3. mean(v[-NA])
  4. mean(v, rm.na = TRUE)

Explanation

The argument na.rm = TRUE tells mean() to remove missing values before computing. Note the exact spelling na.rm; a misspelled argument is silently absorbed by the ... argument and has no effect.

Question 12CB2 syllabus domain (not enumerated in source)

Which statement about primary keys in a relational table is correct?

  1. A table may have several primary keys simultaneously
  2. A primary key must always be an auto-incrementing integer
  3. A primary key uniquely identifies each row and cannot contain NULL values Correct answer.
  4. A primary key may contain duplicate values provided they are not adjacent

Explanation

A primary key enforces uniqueness and non-nullness for every row, guaranteeing a reliable identifier. It can be a composite of several columns, and its type is not restricted to integers.

Question 13CB2 syllabus domain (not enumerated in source)

In R, what is the result of the expression 1:3 + c(10, 20, 30, 40)?

  1. An error is raised because the lengths differ
  2. A warning is issued and the result is 11 22 33 44 Correct answer.
  3. The result is 11 22 33 41
  4. The result is 11 22 33

Explanation

R recycles the shorter vector to match the longer one, giving 1+10, 2+20, 3+30 and then 1+40. When the longer length is not a multiple of the shorter, R emits a warning about non-multiple lengths, which is the case here.

Question 14CB2 syllabus domain (not enumerated in source)

A SQL query contains SELECT dept, COUNT(*) FROM staff GROUP BY dept. Which statement is true?

  1. It counts only departments with more than one member
  2. It returns every staff row with a running count
  3. It fails unless staff is sorted by dept first
  4. It returns one row per distinct department with its staff count Correct answer.

Explanation

GROUP BY collapses rows sharing the same department value into a single output row, and COUNT(*) counts the rows in each group. Sorting is irrelevant to grouping, and no filtering threshold is applied.

Question 15CB2 syllabus domain (not enumerated in source)

In R, which call fits a simple linear regression of y on x using the lm function and stores the result in fit?

  1. fit <- lm(~ y + x, data = df)
  2. fit <- lm(df$y, df$x)
  3. fit <- lm(y = x, data = df)
  4. fit <- lm(y ~ x, data = df) Correct answer.

Explanation

The lm formula interface uses a tilde, with the response on the left and predictors on the right, and data names the data frame holding those variables. This is the standard and most robust form.

Question 16CB2 syllabus domain (not enumerated in source)

A data frame has a factor column with levels stored in an arbitrary order. Why might this matter for a regression model in R?

  1. Factors cannot be used as predictors in lm at all
  2. The first factor level is treated as the baseline, so reported coefficients depend on the level order Correct answer.
  3. Factors are automatically converted to their integer codes and used numerically
  4. The factor order changes the residual sum of squares

Explanation

R creates dummy variables for all but the first level, treating that first level as the reference category. Reordering levels therefore changes which coefficients appear and how they are interpreted, though the fitted values are unchanged.

Question 17CB2 syllabus domain (not enumerated in source)

Which R function reshapes a data frame from wide to long format in the tidyverse?

  1. gather_rows()
  2. pivot_longer() Correct answer.
  3. pivot_wider()
  4. spread()

Explanation

pivot_longer() takes multiple columns and stacks them into key-value pairs, producing long format. pivot_wider() performs the reverse operation, and gather_rows() is not a tidyverse function.

Question 18CB2 syllabus domain (not enumerated in source)

In R, what does the expression df[df$score > 50, ] return when df is a data frame?

  1. The single column score for rows above 50
  2. An error because the row index is logical
  3. All rows of df for which score exceeds 50, keeping all columns Correct answer.
  4. A logical vector indicating which scores exceed 50

Explanation

The first index position selects rows using a logical vector, and the trailing comma with an empty second position keeps all columns. Logical row indexing is a standard and valid data frame operation.

Question 19CB2 syllabus domain (not enumerated in source)

A SQL table stores orders with a customer_id column, and some orders have no matching customer record. Which join returns all orders together with customer details where available?

  1. orders CROSS JOIN customers
  2. orders LEFT JOIN customers ON orders.customer_id = customers.id Correct answer.
  3. orders INNER JOIN customers ON orders.customer_id = customers.id
  4. customers LEFT JOIN orders ON orders.customer_id = customers.id

Explanation

A left join keeps every row of the left table, here orders, and fills NULLs where no match exists on the right. Putting customers on the left would instead preserve all customers and drop unmatched orders.

Question 20CB2 syllabus domain (not enumerated in source)

In R, which function is the idiomatic way to apply a function to each element of a list and return a list of the same length?

  1. sapply()
  2. apply()
  3. lapply() Correct answer.
  4. Reduce()

Explanation

lapply() always returns a list of the same length as its input, regardless of element results. sapply() attempts to simplify the output to a vector or matrix, and apply() operates over array margins.

Question 21CB2 syllabus domain (not enumerated in source)

A data frame has a column of type character containing numeric text such as "12.5". Which call converts it to numeric in base R?

  1. as.numeric(df$col) Correct answer.
  2. as.character(df$col)
  3. factor(df$col)
  4. numeric(df$col)

Explanation

as.numeric() coerces character strings that represent numbers into double-precision values. as.character() goes the other way, and factor() creates categorical levels rather than numbers.

Question 22CB2 syllabus domain (not enumerated in source)

In R, a function is defined as f <- function(x) { y <- x * 2; y + 1 }. What does f(3) return?

  1. 4
  2. 6
  3. An error because y is not returned
  4. 7 Correct answer.

Explanation

The last evaluated expression in the function body is returned automatically, so y + 1 with y equal to 6 gives 7. The intermediate assignment to y is local to the function and needs no explicit return.

Question 23CB2 syllabus domain (not enumerated in source)

In SQL, what is the effect of the WHERE clause on the execution of an aggregate query such as SELECT dept, AVG(salary) FROM staff WHERE grade > 3 GROUP BY dept?

  1. Groups whose average salary is too low are removed
  2. The WHERE clause has no effect because GROUP BY overrides it
  3. Rows with grade 3 or lower are removed before grouping, so averages use only the remaining rows Correct answer.
  4. The WHERE clause is applied after grouping and filters whole departments

Explanation

WHERE is evaluated before grouping, so it restricts the rows that enter each group. HAVING would be the clause used to filter groups after aggregation.

Question 24CB2 syllabus domain (not enumerated in source)

In R, a numeric vector contains the values 2, 4, 4, 8 and NA. What does median(v, na.rm = TRUE) return?

  1. 5
  2. 4.5
  3. NA
  4. 4 Correct answer.

Explanation

Removing the NA leaves 2, 4, 4 and 8, an even count, so the median is the mean of the two middle values 4 and 4, which is 4. Without na.rm the result would be NA.

Question 25CB2 syllabus domain (not enumerated in source)

A left join between a 5-row table A and a 7-row table B on a key is performed, where three rows of A have no match in B and one key in B matches two rows of A. How many rows does the result contain?

  1. 5
  2. 8
  3. 7 Correct answer.
  4. 12

Explanation

Each of the five rows of A contributes at least one output row. The three unmatched rows give three rows with NULLs, and the one key matching two rows of A yields two rows, so the total is 7.

Question 26CB2 syllabus domain (not enumerated in source)

In R, a data frame df stores a column named region as a character vector. Which expression counts how many rows belong to the region "North"?

  1. nrow(df$region == "North")
  2. count(df$region)
  3. sum(df$region == "North") Correct answer.
  4. length(df$region == "North")

Explanation

The comparison df$region == "North" produces a logical vector with one TRUE or FALSE per row, and sum() coerces TRUE to 1 and FALSE to 0, giving the number of matching rows. This is the standard base R idiom for a conditional row count.

Question 27CB2 syllabus domain (not enumerated in source)

In SQL, a table sales has columns region and amount. Which query returns the total amount for each region, showing only regions whose total exceeds 1000?

  1. SELECT region, SUM(amount) FROM sales HAVING SUM(amount) > 1000
  2. SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 1000 GROUP BY region
  3. SELECT region, SUM(amount) FROM sales GROUP BY region WHERE SUM(amount) > 1000
  4. SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 1000 Correct answer.

Explanation

Conditions on aggregated values are expressed with HAVING, which is evaluated after GROUP BY, so SUM(amount) > 1000 filters whole groups. WHERE is evaluated before grouping and cannot reference an aggregate over a group.

Question 28CB2 syllabus domain (not enumerated in source)

In R, which expression creates a new column named ratio in the data frame df equal to height divided by weight?

  1. df[ratio] <- df$height / df$weight
  2. df$ratio == df$height / df$weight
  3. df <- ratio(df$height / df$weight)
  4. df$ratio <- df$height / df$weight Correct answer.

Explanation

The $<- assignment operator adds or replaces a column in a data frame, and vectorised division of the two columns produces one value per row. The result is stored under the name ratio.

Question 29CB2 syllabus domain (not enumerated in source)

Which SQL clause orders the rows of a result set by the column total in descending order?

  1. GROUP BY total DESC
  2. ORDER BY total DESC Correct answer.
  3. ORDER total DESC
  4. SORT BY total DESC

Explanation

ORDER BY sorts the final result set, and the DESC keyword reverses the default ascending order. It is applied after grouping and filtering.

Question 30CB2 syllabus domain (not enumerated in source)

In R, what does the function is.na() return when applied to a vector containing missing values?

  1. A numeric vector with the missing values removed
  2. A character vector showing the missing positions
  3. A single logical value indicating whether any value is missing
  4. A logical vector with TRUE where the element is missing Correct answer.

Explanation

is.na() is vectorised and returns a logical vector of the same length as its input, with TRUE at positions that are NA. It does not remove or summarise the missing values.

Question 31CB2 syllabus domain (not enumerated in source)

In R, which operator extracts the element in the second row and third column of a matrix m?

  1. m(2, 3)
  2. m[2, 3] Correct answer.
  3. m[3, 2]
  4. m[[2, 3]]

Explanation

Matrix indexing uses square brackets with the row index first and the column index second, so m[2, 3] selects row 2 and column 3. This ordering is a common source of error.

Question 32CB2 syllabus domain (not enumerated in source)

A relational table stores student module results. Which column combination would best act as a composite primary key?

  1. student_id together with student_name
  2. module_code alone
  3. student_id together with module_code Correct answer.
  4. student_name together with module_code

Explanation

A composite primary key must uniquely identify each row and should consist of attributes that together are unique and stable. Student and module identifiers together identify one enrolment record.

Question 33CB2 syllabus domain (not enumerated in source)

In R, what is the value of the expression sum(c(TRUE, FALSE, TRUE, NA))?

  1. TRUE
  2. 2
  3. 3
  4. NA Correct answer.

Explanation

Arithmetic on a vector containing NA propagates missingness, so sum() returns NA unless na.rm = TRUE is supplied. The presence of NA makes the total unknown.

Question 34CB2 syllabus domain (not enumerated in source)

In R, a vector v <- c(5, 9, 2, 7). Which expression returns the elements of v that are greater than 4?

  1. v[isTRUE(v > 4)]
  2. v[which(v) > 4]
  3. subset(v > 4)
  4. v[v > 4] Correct answer.

Explanation

The logical vector v > 4 is used inside the brackets to select only the positions where the condition is TRUE. Logical indexing is the standard base R filtering idiom.

Question 35CB2 syllabus domain (not enumerated in source)

In SQL, a table employees has a nullable column manager_id that references employees.employee_id. Which statement about this self-referencing foreign key is correct?

  1. A NULL manager_id is permitted because the foreign key constraint does not apply to NULL values Correct answer.
  2. A NULL manager_id is rejected because every foreign key value must match an existing row
  3. A NULL manager_id is permitted only if a row with employee_id NULL exists
  4. The foreign key must be dropped before any employee row can be inserted

Explanation

A foreign key constraint checks that non-null values match a referenced key, but NULL means unknown and is not checked for referential integrity. This allows top-level employees with no manager.

Question 36CB2 syllabus domain (not enumerated in source)

In R, the function f <- function(x, y = 10) { x + y } is defined. What does the call f(5) return?

  1. 5
  2. 15 Correct answer.
  3. An error because y is missing
  4. 10

Explanation

Default argument values are used when the caller does not supply them, so y takes the value 10 and x is 5. The function therefore returns 5 + 10.

Question 37CB2 syllabus domain (not enumerated in source)

In R, which call merges two data frames a and b by their common column id while keeping only rows with matches in both?

  1. merge(a, b, by = "id", all = TRUE)
  2. rbind(a, b)
  3. merge(a, b, by = "id") Correct answer.
  4. merge(a, b, by = "id", all.x = TRUE)

Explanation

The default for merge() is an inner join, so all = FALSE keeps only rows whose id appears in both data frames. Supplying all variants changes which unmatched rows are retained.

Question 38CB2 syllabus domain (not enumerated in source)

In SQL, a table orders has a column status and a table customers has a column country. Which query returns the number of orders per country, including countries with no orders?

  1. SELECT c.country, COUNT(o.status) FROM orders o RIGHT JOIN customers c ON c.customer_id = o.customer_id GROUP BY o.status
  2. SELECT c.country, COUNT(o.status) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.country Correct answer.
  3. SELECT c.country, COUNT(o.status) FROM customers c CROSS JOIN orders o GROUP BY c.country
  4. SELECT c.country, COUNT(o.status) FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.country

Explanation

A LEFT JOIN starting from customers preserves every customer row, so countries with no matching orders still appear, and COUNT(o.status) counts only non-null matches. Grouping by country then aggregates correctly.

Question 39CB2 syllabus domain (not enumerated in source)

In R, what does the expression as.numeric(factor(c("low", "high", "low"))) return?

  1. c(1, 2, 1) Correct answer.
  2. c("low", "high", "low")
  3. An error because the values are not numeric
  4. c(1, 3, 1)

Explanation

Factors store levels as integer codes in alphabetical order of the levels, so low is level 1 and high is level 2. Coercing to numeric returns those underlying codes rather than the text.

Question 40CB2 syllabus domain (not enumerated in source)

In R, a vector v contains c(3, NA, 7, 1). What does order(v, na.last = TRUE) return?

  1. c(1, 3, 7, NA)
  2. c(4, 1, 3, 2) Correct answer.
  3. c(1, 3, 4, 2)
  4. c(2, 1, 3, 4)

Explanation

order() returns the indices that would sort the vector, so the smallest value 1 is at index 4, then 3 at index 1, then 7 at index 3. With na.last = TRUE the NA index is placed at the end.

Question 41CB2 syllabus domain (not enumerated in source)

In R, which statement about the difference between a list and an atomic vector is correct?

  1. A list can only hold numeric elements, whereas an atomic vector can hold any type
  2. An atomic vector can hold elements of different types, whereas a list cannot
  3. A list and an atomic vector are identical in R
  4. A list can hold elements of different types, whereas an atomic vector requires all elements to share one type Correct answer.

Explanation

Lists are recursive objects whose elements may be of any type, including other lists, while atomic vectors are homogeneous and coerce mixed input to a single type. This distinction underlies many data structures in R.

Question 42CB2 syllabus domain (not enumerated in source)

In SQL, a table events has a timestamp column occurred_at. Which expression extracts the calendar year from that column in standard SQL?

  1. YEAR(occurred_at)
  2. EXTRACT(YEAR FROM occurred_at) Correct answer.
  3. occurred_at.YEAR
  4. SUBSTRING(occurred_at, 1, 4)

Explanation

Standard SQL provides EXTRACT(field FROM source) to pull a component such as YEAR from a datetime value. It is portable across compliant database systems.

Question 43CB2 syllabus domain (not enumerated in source)

In R, a data frame df has a numeric column score and a factor column group. Which call computes the median score for each group using base R?

  1. mapply(df$score, df$group, median)
  2. tapply(df$score, df$group, median) Correct answer.
  3. apply(df$score, df$group, median)
  4. sapply(df$score, df$group, median)

Explanation

tapply() splits the first argument by the grouping factor in the second argument and applies the function to each subset. It returns one result per group.

Question 44CB2 syllabus domain (not enumerated in source)

In R, a data frame df has a column price containing values such as 12.5 and 8. Which expression replaces every missing price with the column mean?

  1. df$price <- mean(df$price, na.rm = TRUE)
  2. df$price[is.na(df$price)] <- mean(df$price, na.rm = TRUE) Correct answer.
  3. df$price[is.na(df$price)] <- mean(df$price)
  4. df$price[df$price == NA] <- mean(df$price, na.rm = TRUE)

Explanation

Logical indexing with is.na() selects the missing positions, and the mean computed with na.rm = TRUE supplies the replacement value. Only the NA entries are overwritten.

Question 45CB2 syllabus domain (not enumerated in source)

In SQL, a table accounts has a column balance. Which query returns accounts whose balance is strictly above the average balance of all accounts?

  1. SELECT account_id FROM accounts GROUP BY balance HAVING balance > AVG(balance)
  2. SELECT account_id FROM accounts WHERE balance > AVG(balance)
  3. SELECT account_id FROM accounts WHERE balance > (SELECT AVG(balance) FROM accounts) Correct answer.
  4. SELECT account_id FROM accounts HAVING balance > AVG(balance)

Explanation

A scalar subquery computes the overall average once, and the outer WHERE compares each row's balance against that single value. Aggregates cannot be used directly in WHERE over the same rows.

Question 46CB2 syllabus domain (not enumerated in source)

In R, a function is defined as f <- function(n) { if (n <= 1) return(1); n * f(n - 1) }. What does f(4) return?

  1. 4
  2. An error because the function calls itself
  3. 10
  4. 24 Correct answer.

Explanation

The function is recursive and computes the factorial, multiplying 4 by 3 by 2 by 1 and stopping at the base case n <= 1. Recursion is permitted in R functions.

Question 47CB2 syllabus domain (not enumerated in source)

In SQL, a table readings has duplicate rows for the same sensor_id and reading_time. Which query removes exact duplicate rows from the result?

  1. SELECT DISTINCT sensor_id, reading_time, value FROM readings Correct answer.
  2. SELECT COUNT(DISTINCT sensor_id) FROM readings
  3. SELECT sensor_id, reading_time, value FROM readings GROUP BY value
  4. SELECT UNIQUE sensor_id, reading_time, value FROM readings

Explanation

SELECT DISTINCT removes duplicate rows from the result set by comparing all selected columns together. It is standard SQL and applies to the full row of selected values.

Question 48CB2 syllabus domain (not enumerated in source)

In R, a data frame df has columns id and value, and df2 has columns id and label. Which call produces a data frame with all rows of df and matching labels where available?

  1. merge(df, df2, by = "id", all.y = TRUE)
  2. merge(df, df2, by = "id")
  3. merge(df, df2, by = "id", all.x = TRUE) Correct answer.
  4. merge(df, df2, by = "id", all = TRUE)

Explanation

Setting all.x = TRUE performs a left join, keeping every row of df and filling label with NA where df2 has no match. This is the requested behaviour.

Question 49CB2 syllabus domain (not enumerated in source)

In R, a vector x <- c(2, 4, 6, 8) is given. What does the expression x[-2] return?

  1. An error because negative indices are not allowed
  2. c(4)
  3. c(2, 6, 8) Correct answer.
  4. c(4, 6, 8)

Explanation

A negative index in R excludes the element at that position rather than selecting it. Here position 2, which holds 4, is removed and the remaining elements are returned.

Question 50CB2 syllabus domain (not enumerated in source)

In SQL, a table staff has columns dept and salary. Which query returns the departments whose average salary exceeds the average salary of the whole company?

  1. SELECT dept FROM staff GROUP BY dept HAVING AVG(salary) > (SELECT AVG(salary) FROM staff) Correct answer.
  2. SELECT dept FROM staff GROUP BY dept WHERE AVG(salary) > (SELECT AVG(salary) FROM staff)
  3. SELECT dept FROM staff WHERE AVG(salary) > (SELECT AVG(salary) FROM staff)
  4. SELECT dept FROM staff HAVING AVG(salary) > AVG(salary)

Explanation

The outer query groups by department and uses HAVING to compare each group's average with a scalar subquery holding the company-wide average. HAVING is the correct clause for filtering on aggregates.