SQL MCQs - Advanced Querying: CASE, NULL Logic, Set Operators, EXISTS (Set 5)

Q1. What does this return for a row where score = 45?

CASE WHEN score >= 80 THEN 'A'
     WHEN score >= 60 THEN 'B'
END

a) 'B'

b) An empty string

c) NULL

d) An error

Q2. What does COALESCE(NULL, NULL, 'guest', 'admin') return?

a) 'guest, admin'

b) 'admin'

c) NULL

d) 'guest'

Q3. Why write revenue / NULLIF(orders, 0)?

a) To round the result

b) NULLIF returns NULL when orders is 0, so the result is NULL instead of a division-by-zero error

c) To convert revenue to an integer

d) To make the division faster

Q4. A column has the values 10, 20 and NULL. What does AVG(col) return?

a) NULL

b) 10

c) An error

d) 15

Q5. Which predicate treats two NULLs as equal when comparing a.col and b.col?

a) a.col IS NOT DISTINCT FROM b.col

b) a.col == b.col

c) a.col = b.col

d) a.col LIKE b.col

Q6. Under SQL three-valued logic, what is NULL AND FALSE?

a) FALSE

b) NULL (unknown)

c) An error

d) TRUE

Q7. Table t has ids 1, 2 and 3. How many rows does SELECT * FROM t WHERE id NOT IN (1, NULL) return?

a) 2

b) 3

c) 0

d) 1

Q8. How does GROUP BY region treat rows where region is NULL?

a) They are dropped

b) The query fails

c) All NULL rows are placed together in one group

d) Each NULL row forms its own group

Q9. What does INTERSECT return?

a) The Cartesian product

b) Distinct rows that appear in the results of both queries

c) All rows from both queries

d) Rows from the first query that are not in the second

Q10. What does this query return?

SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

a) Cities that have suppliers but no customers

b) Distinct cities that have customers but no suppliers

c) All cities from both tables

d) Cities that have both

Q11. Which statement about EXISTS is correct?

a) EXISTS cannot be correlated

b) The columns listed inside the EXISTS subquery are returned to the outer query

c) EXISTS returns NULL when the subquery has NULL values

d) EXISTS only checks whether the subquery returns at least one row, so SELECT 1 and SELECT * inside behave the same

Q12. Which self join lists each pair of employees in the same department exactly once?

a) FROM emp a CROSS JOIN emp b

b) FROM emp a JOIN emp b ON a.dept = b.dept AND a.id < b.id

c) FROM emp a JOIN emp b ON a.dept = b.dept

d) FROM emp a JOIN emp b ON a.dept = b.dept AND a.id <> b.id

Q13. In PostgreSQL, how would you count orders per calendar month from a timestamp column?

a) GROUP BY NOW()

b) GROUP BY created_at

c) GROUP BY MONTH

d) GROUP BY DATE_TRUNC('month', created_at)

Q14. In PostgreSQL, what does DATE '2024-03-01' - DATE '2024-02-01' return?

a) 1 month

b) 29

c) 30

d) 28

Q15. Which query turns rows of (product, quarter, amount) into one row per product with columns q1 and q2?

a) SELECT product, amount FROM sales UNION SELECT quarter, amount FROM sales

b) SELECT DISTINCT quarter FROM sales

c) SELECT product, SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1, SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2 FROM sales GROUP BY product

d) SELECT product, quarter FROM sales ORDER BY quarter