CASE expression in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the CASE expression in PostgreSQL?

The CASE expression lets you perform conditional logic inside SQL queries, returning different values based on conditions, similar to an IF-THEN-ELSE in programming.

beginner

Write the basic syntax of a simple CASE expression in PostgreSQL.

CASE expression syntax:<br>CASE input_expression<br> WHEN value1 THEN result1<br> WHEN value2 THEN result2<br> ELSE default_result<br>END

intermediate

How does a searched CASE expression differ from a simple CASE expression?

A searched CASE tests multiple boolean conditions directly (WHEN condition THEN result), while a simple CASE compares one expression to multiple values.

beginner

What happens if no WHEN condition matches and there is no ELSE clause in a CASE expression?

If no WHEN matches and ELSE is missing, the CASE expression returns NULL.

beginner

Can CASE expressions be used in SELECT, WHERE, and ORDER BY clauses in PostgreSQL?

Yes, CASE expressions can be used anywhere an expression is allowed, including SELECT, WHERE, ORDER BY, and more.

What does the CASE expression return if no WHEN condition matches and ELSE is provided?

A An error

B The first WHEN condition's value

C NULL

D The value in ELSE clause

If no WHEN matches, CASE returns the ELSE value if provided.

Which of the following is a valid use of CASE in PostgreSQL?

A Inside SELECT to change output values

B Only in WHERE clause

C Only in ORDER BY clause

D Only in UPDATE statements

CASE can be used anywhere expressions are allowed, including SELECT.

What keyword starts a searched CASE expression?

A WHEN

B CASE

C IF

D ELSE

All CASE expressions start with the CASE keyword.

In a simple CASE expression, what is compared to the WHEN values?

A The ELSE clause

B Boolean conditions

C An input expression

D The SELECT statement

Simple CASE compares one input expression to each WHEN value.

What is the result of a CASE expression without ELSE and no matching WHEN?

A NULL

B 0

C Error

D First WHEN value

Without ELSE, CASE returns NULL if no WHEN matches.

Explain how to use a searched CASE expression in PostgreSQL with an example.

Think about checking conditions like age > 18 or status = 'active'.

Describe the difference between simple CASE and searched CASE expressions in PostgreSQL.

One compares values, the other checks conditions.