SQL MCQs - CTEs, Window Functions, Views, Stored Procedures and Triggers (Set 3)

Q1. What does a WITH clause (common table expression) do?

a) Creates an index

b) Defines a named temporary result set that the following query can reference

c) Creates a permanent table

d) Starts a transaction

Q2. A recursive CTE needs:

a) An anchor member and a recursive member that references the CTE, combined with UNION ALL

b) Only a single SELECT

c) A stored procedure

d) A trigger

Q3. Salaries are 100, 90, 90 and 80. What does RANK() OVER (ORDER BY salary DESC) return?

a) 1, 2, 2, 3

b) 1, 2, 3, 4

c) 1, 1, 2, 3

d) 1, 2, 2, 4

Q4. For the same salaries, what does DENSE_RANK() return?

a) 1, 2, 2, 3

b) 1, 2, 2, 4

c) 1, 1, 2, 2

d) 1, 2, 3, 4

Q5. What does PARTITION BY do in a window function?

a) Divides rows into groups so the function is computed separately within each group, without collapsing the rows

b) Sorts the final output

c) Works the same as GROUP BY

d) Splits the table into separate physical files

Q6. Which expression gives a running total of amount by date?

a) SUM(amount) GROUP BY date

b) COUNT(amount) OVER ()

c) SUM(amount) OVER (ORDER BY date)

d) MAX(amount) OVER (PARTITION BY date)

Q7. What does LAG(amount) OVER (ORDER BY date) return?

a) The next row's amount

b) The previous row's amount, or NULL for the first row

c) The average amount

d) The first amount in the table

Q8. Why does this query fail?

SELECT name,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM emp
WHERE rn <= 3;

a) ROW_NUMBER needs PARTITION BY

b) Window functions are evaluated after WHERE, so rn cannot be used there; wrap it in a subquery or CTE

c) Aliases cannot contain letters

d) LIMIT is required

Q9. How do you get the top 3 earners in each department?

a) ORDER BY salary DESC LIMIT 3

b) MAX(salary) GROUP BY dept

c) GROUP BY dept HAVING COUNT(*) = 3

d) Use ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) in a CTE and keep rows with rn ≤ 3

Q10. What does NTILE(4) OVER (ORDER BY score) do?

a) Divides the ordered rows into 4 groups of roughly equal size and labels them 1 to 4

b) Returns the top 4 rows

c) Multiplies scores by 4

d) Returns every fourth row

Q11. Which statement about a regular (non-materialized) view is correct?

a) It stores a copy of the data

b) It cannot be used in joins

c) It is a saved query that runs against the base tables each time the view is used

d) It is always faster than the underlying query

Q12. What is a key difference of a materialized view?

a) It cannot be queried

b) It stores the query result physically and must be refreshed to show new data

c) It is the same as a CTE

d) It updates instantly on every insert in all databases

Q13. What does WITH CHECK OPTION do on an updatable view?

a) Rejects inserts or updates through the view that would create rows the view itself would not show

b) Makes the view read-only

c) Adds an index

d) Checks the syntax of the view

Q14. In most databases, what is a key difference between a stored function and a stored procedure?

a) A function returns a value and can be used inside a SELECT; a procedure is called with CALL or EXEC and can manage transactions

b) Procedures cannot take parameters

c) Functions cannot read tables

d) There is no difference

Q15. Inside a row-level AFTER UPDATE trigger, how do you refer to the values before and after the change?

a) THIS and THAT

b) BEFORE and AFTER

c) PREV and NEXT

d) OLD and NEW