Test Your PostgreSQL CASE Statement Knowledge with This Quiz

Progress

True / False

In PostgreSQL, the CASE statement can be used within a SELECT statement to perform conditional logic.

True False

The CASE statement allows for conditional logic to be embedded within a SELECT statement to return different values based on specified conditions.

True / False

In PostgreSQL, the CASE statement must always include an ELSE clause.

True False

The ELSE clause is optional in a CASE statement. If no ELSE clause is provided and no conditions are met, the result is NULL.

True / False

In PostgreSQL, the CASE statement can be used to update rows in an UPDATE statement.

True False

The CASE statement can be utilized within an UPDATE statement to conditionally update rows based on specified criteria.

True / False

In PostgreSQL, the CASE statement can be used in combination with aggregate functions like SUM and COUNT.

True False

The CASE statement can be used within aggregate functions to conditionally aggregate values based on specific conditions.

True / False

In PostgreSQL, nested CASE statements are not allowed.

True False

PostgreSQL allows for nested CASE statements, meaning you can place a CASE statement within another CASE statement.

True / False

In PostgreSQL, the CASE statement can only be used in the SELECT clause.

True False

The CASE statement can be used in various clauses such as SELECT, UPDATE, INSERT, and ORDER BY.

True / False

In PostgreSQL, the CASE statement can be used within a WHERE clause to filter rows based on conditional logic.

True False

The CASE statement can be used within a WHERE clause to apply conditional logic for filtering rows.

True / False

In PostgreSQL, using a CASE statement within a JOIN condition can significantly improve query performance.

True False

While the CASE statement can be used within a JOIN condition, it can potentially complicate the query and impact performance negatively if not used appropriately.

True / False

In PostgreSQL, the CASE statement can evaluate complex expressions, including subqueries, within each WHEN clause.

True False

Each WHEN clause in a CASE statement can contain complex expressions, including subqueries, allowing for sophisticated conditional logic.

Q10

True / False

In PostgreSQL, a CASE statement without an ELSE clause will return a default value specified by the database configuration.

True False

If no ELSE clause is provided in a CASE statement and none of the WHEN conditions are met, the result is NULL, not a default value from the database configuration.

Q11

Single Choice

What does the CASE statement in PostgreSQL allow you to do?

Perform aggregate functions Conditionally execute sequences of statements Define constraints on tablesCreate indexes

The CASE statement in PostgreSQL allows you to perform conditional logic within queries, similar to an if-else structure in programming languages.

Q12

Single Choice

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

CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' CASE WHERE age > 18 THEN 'Adult' ELSE 'Minor' CASE age > 18 THEN 'Adult' ELSE 'Minor' ENDCASE WHEN age > 18 DO 'Adult' ELSE 'Minor' END

The correct syntax for a CASE statement in PostgreSQL is CASE WHEN condition THEN result ELSE result END.

Q13

Single Choice

How do you ensure that a CASE statement in PostgreSQL handles all possible cases?

Use WHEN for each case and omit ELSE Use only ELSE without any WHEN C) Include an ELSE clause to handle unmatched casesCASE automatically handles all cases

The ELSE clause in a CASE statement is used to handle any cases that do not match the specified conditions.

Q14

Single Choice

Given the following PostgreSQL query, what will the output be for age = 25?

SQL Code

SELECT
 name,
 CASE
 WHEN age < 18 THEN 'Minor'
 WHEN age >= 18 AND age < 65 THEN 'Adult'
 ELSE 'Senior'
 END AS age_group
FROM users;

Minor Adult SeniorNULL

Since age = 25 falls within the range >= 18 AND < 65, the CASE statement will return 'Adult'.

Q15

Single Choice

How can you combine a CASE statement with aggregate functions in PostgreSQL?

It’s not possible to use CASE with aggregate functions Use CASE within an aggregate function like SUM Use CASE outside of an aggregate functionUse CASE only in SELECT statements, not with aggregates.

You can use CASE inside an aggregate function to conditionally include rows in the aggregate calculation

Q16

Single Choice

What will be the result of the following PostgreSQL CASE statement?

SQL Code

SELECT
 CASE
 WHEN salary > 50000 THEN 'High'
 WHEN salary < 30000 THEN 'Low'
 ELSE 'Medium'
 END AS salary_range
FROM employees
WHERE salary IS NULL;

High Low MediumNULL

The WHERE salary IS NULL clause filters out rows where salary is not null, so the CASE statement will not evaluate any rows, resulting in NULL.

Q17

Single Choice

What will be the output of the following query if status is NULL in PostgreSQL?

SQL Code

SELECT
 CASE
 WHEN status = 'active' THEN 'Active User'
 WHEN status = 'inactive' THEN 'Inactive User'
 ELSE 'Unknown'
 END AS user_status
FROM users;

Active User Inactive User UnknownNULL

The ELSE clause in the CASE statement will catch the NULL value and return 'Unknown'.

Q18

Single Choice

How can you optimize the following PostgreSQL query using CASE?

SQL Code

SELECT
 CASE
 WHEN age BETWEEN 13 AND 19 THEN 'Teenager'
 WHEN age < 13 THEN 'Child'
 ELSE 'Adult'
 END AS age_group,
 COUNT(*)
FROM users
GROUP BY age_group;

Add an ELSE NULL to the CASE statement Use the HAVING clause instead of WHERE Move the CASE statement inside the GROUP BY clause.The query is already optimized

The query correctly uses the CASE statement to classify users and then groups them using the GROUP BY clause.

Q19

Single Choice

Consider the following PostgreSQL query. What will be the result if employee_id does not exist in the employees table?

SQL Code

SELECT
 employee_id,
 CASE
 WHEN employee_id IS NOT NULL THEN 'Exists'
 ELSE 'Does Not Exist'
 END AS employee_status
FROM employees;

Exists Does Not Exist NULLNo rows returned

If employee_id does not exist in the table, the query will return no rows.

Q20

Single Choice

How can you use a CASE statement to handle NULL values explicitly in PostgreSQL?

SQL Code

SELECT
 product_id,
 CASE
 WHEN price IS NULL THEN 'Price Missing'
 ELSE 'Price Available'
 END AS price_status
FROM products;

CASE cannot handle NULL values. Use ELSE NULL in the CASE statement Use WHEN price IS NOT NULL THEN 'Price Available'The CASE statement can handle NULLs directly with conditions.

The CASE statement in PostgreSQL can directly handle NULL values by using WHEN condition IS NULL within its conditions.

Q21

Multiple Choice

Which SQL scripts correctly use the CASE statement to categorize employees based on their salary in PostgreSQL?

SELECT employee_id, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees; SELECT employee_id, salary, CASE salary > 70000 THEN 'High' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees; SELECT employee_id, salary, CASE WHEN salary > 70000 THEN 'High' ELSE CASE WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'Low' END END AS salary_category FROM employees;SELECT employee_id, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary < 40000 THEN 'Low' ELSE 'Medium' END AS salary_category FROM employees;

The CASE statement allows you to perform conditional logic within a query. The correct scripts will categorize salaries based on specified conditions.

Q22

Multiple Choice

Identify the SQL scripts that correctly use the CASE statement to assign bonus percentages in PostgreSQL.

SELECT employee_id, performance_rating, CASE WHEN performance_rating = 'Excellent' THEN 0.20 WHEN performance_rating = 'Good' THEN 0.10 ELSE 0.05 END AS bonus_percentage FROM employees; SELECT employee_id, performance_rating, CASE WHEN performance_rating = 'Excellent' THEN 20 WHEN performance_rating = 'Good' THEN 10 ELSE 5 END AS bonus_percentage FROM employees; SELECT employee_id, performance_rating, CASE WHEN performance_rating = 'Excellent' THEN 0.20 WHEN performance_rating = 'Good' THEN 0.10 ELSE 0.05 END AS bonus_percentage FROM employees WHERE performance_rating IS NOT NULL;SELECT employee_id, performance_rating, CASE WHEN performance_rating = 'Excellent' THEN 20 WHEN performance_rating = 'Good' THEN 10 ELSE 5 END AS bonus_percentage FROM employees WHERE performance_rating IS NOT NULL;

The CASE statement can be used to apply conditional logic to determine bonus percentages based on performance ratings.

Q23

Multiple Choice

Which SQL scripts correctly use the CASE statement to generate a custom label for departments based on the number of employees in PostgreSQL?

SELECT department_id, department_name, CASE WHEN COUNT(employee_id) > 50 THEN 'Large' WHEN COUNT(employee_id) BETWEEN 20 AND 50 THEN 'Medium' ELSE 'Small' END AS department_size FROM departments JOIN employees ON departments.department_id = employees.department_id GROUP BY departments.department_id, departments.department_name; SELECT department_id, department_name, CASE WHEN COUNT(employee_id) > 50 THEN 'Large' WHEN COUNT(employee_id) < 20 THEN 'Small' ELSE 'Medium' END AS department_size FROM departments JOIN employees ON departments.department_id = employees.department_id GROUP BY departments.department_id, departments.department_name; SELECT department_id, department_name, CASE WHEN COUNT(employee_id) > 50 THEN 'Large' ELSE CASE WHEN COUNT(employee_id) BETWEEN 20 AND 50 THEN 'Medium' ELSE 'Small' END END AS department_size FROM departments JOIN employees ON departments.department_id = employees.department_id GROUP BY departments.department_id, departments.department_name;SELECT department_id, department_name, CASE WHEN COUNT(employee_id) > 50 THEN 'Large' WHEN COUNT(employee_id) BETWEEN 20 AND 50 THEN 'Medium' ELSE 'Small' END AS department_size FROM departments LEFT JOIN employees ON departments.department_id = employees.department_id GROUP BY departments.department_id, departments.department_name;

The CASE statement is useful for generating custom labels based on conditional logic. The correct scripts will assign labels such as 'Small', 'Medium', or 'Large' based on the employee count.

Q24

Multiple Choice

Determine the SQL scripts that correctly use the CASE statement to apply discounts based on order quantity in PostgreSQL.

SELECT order_id, quantity, CASE WHEN quantity > 100 THEN 0.15 WHEN quantity BETWEEN 50 AND 100 THEN 0.10 ELSE 0.05 END AS discount FROM orders; SELECT order_id, quantity, CASE WHEN quantity > 100 THEN 15 WHEN quantity BETWEEN 50 AND 100 THEN 10 ELSE 5 END AS discount FROM orders; SELECT order_id, quantity, CASE WHEN quantity > 100 THEN 0.15 WHEN quantity BETWEEN 50 AND 100 THEN 0.10 ELSE 0.05 END AS discount FROM orders WHERE quantity IS NOT NULL;SELECT order_id, quantity, CASE WHEN quantity > 100 THEN 15 WHEN quantity BETWEEN 50 AND 100 THEN 10 ELSE 5 END AS discount FROM orders WHERE quantity IS NOT NULL;

The CASE statement allows you to apply discounts based on specific conditions such as order quantity. The correct scripts will demonstrate how to calculate discounts dynamically.

Q25

Multiple Choice

Which SQL scripts correctly use the CASE statement to handle NULL values in PostgreSQL?

SELECT customer_id, CASE WHEN phone_number IS NULL THEN 'N/A' ELSE phone_number END AS phone_number FROM customers; SELECT customer_id, CASE WHEN phone_number = NULL THEN 'N/A' ELSE phone_number END AS phone_number FROM customers; SELECT customer_id, CASE phone_number WHEN NULL THEN 'N/A' ELSE phone_number END AS phone_number FROM customers;SELECT customer_id, CASE WHEN phone_number IS NOT NULL THEN phone_number ELSE 'N/A' END AS phone_number FROM customers;

The CASE statement can be used to handle NULL values by providing a default value. The correct scripts will demonstrate how to replace NULL values with 'N/A'.

Q26

Multiple Choice

Identify the SQL scripts that correctly use the CASE statement within an ORDER BY clause in PostgreSQL.

SELECT employee_id, job_title, salary FROM employees ORDER BY job_title, CASE WHEN salary > 70000 THEN 'High' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'Low' END; SELECT employee_id, job_title, salary FROM employees ORDER BY job_title, CASE WHEN salary > 70000 THEN 1 WHEN salary BETWEEN 40000 AND 70000 THEN 2 ELSE 3 END; SELECT employee_id, job_title, salary FROM employees ORDER BY CASE WHEN salary > 70000 THEN 'High' WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium' ELSE 'Low' END, job_title;SELECT employee_id, job_title, salary FROM employees ORDER BY CASE WHEN salary > 70000 THEN 1 WHEN salary BETWEEN 40000 AND 70000 THEN 2 ELSE 3 END, job_title;

The CASE statement can be used within the ORDER BY clause to apply custom sorting logic. The correct scripts will demonstrate how to sort employees by job title and salary category.

Q27

Multiple Choice

Which SQL scripts correctly use the CASE statement to modify column values during data retrieval in PostgreSQL?

SELECT customer_id, name, CASE WHEN total_purchases > 10000 THEN 'Preferred' ELSE 'Regular' END AS customer_type FROM customers; SELECT customer_id, name, CASE total_purchases WHEN > 10000 THEN 'Preferred' ELSE 'Regular' END AS customer_type FROM customers; SELECT customer_id, name, CASE WHEN total_purchases > 10000 THEN 'Preferred' ELSE 'Regular' END AS customer_type FROM customers WHERE total_purchases IS NOT NULL;SELECT customer_id, name, CASE total_purchases WHEN > 10000 THEN 'Preferred' ELSE 'Regular' END AS customer_type FROM customers WHERE total_purchases IS NOT NULL;

The CASE statement can be used to modify or enhance column values during data retrieval. The correct scripts will add a 'Preferred' label to customers based on their purchase history.

Q28

Multiple Choice

Determine the SQL scripts that correctly use the CASE statement to handle multiple conditions in PostgreSQL.

SELECT student_id, test_score, CASE WHEN test_score >= 90 THEN 'A' WHEN test_score >= 80 THEN 'B' WHEN test_score >= 70 THEN 'C' ELSE 'D' END AS final_grade FROM students; SELECT student_id, test_score, CASE test_score WHEN >= 90 THEN 'A' WHEN >= 80 THEN 'B' WHEN >= 70 THEN 'C' ELSE 'D' END AS final_grade FROM students; SELECT student_id, test_score, CASE WHEN test_score >= 90 THEN 'A' WHEN test_score >= 80 THEN 'B' WHEN test_score >= 70 THEN 'C' ELSE 'D' END AS final_grade FROM students WHERE test_score IS NOT NULL;SELECT student_id, test_score, CASE test_score WHEN >= 90 THEN 'A' WHEN >= 80 THEN 'B' WHEN >= 70 THEN 'C' ELSE 'D' END AS final_grade FROM students WHERE test_score IS NOT NULL;

The CASE statement allows for multiple conditions to be evaluated sequentially. The correct scripts will apply different grades based on test scores.

Q29

Multiple Choice

Identify the SQL scripts that correctly use nested CASE statements in PostgreSQL.

SELECT order_id, quantity, CASE WHEN quantity > 100 THEN CASE WHEN quantity > 200 THEN 0.20 ELSE 0.15 END WHEN quantity BETWEEN 50 AND 100 THEN 0.10 ELSE 0.05 END AS discount FROM orders; SELECT order_id, quantity, CASE WHEN quantity > 100 THEN 0.15 ELSE CASE WHEN quantity BETWEEN 50 AND 100 THEN 0.10 ELSE 0.05 END END AS discount FROM orders; SELECT order_id, quantity, CASE WHEN quantity > 100 THEN CASE WHEN quantity > 200 THEN 0.20 ELSE 0.15 END WHEN quantity BETWEEN 50 AND 100 THEN 0.10 ELSE 0.05 END AS discount FROM orders WHERE quantity IS NOT NULL;SELECT order_id, quantity, CASE WHEN quantity > 100 THEN 0.15 ELSE CASE WHEN quantity BETWEEN 50 AND 100 THEN 0.10 ELSE 0.05 END END AS discount FROM orders WHERE quantity IS NOT NULL;

Nested CASE statements allow for more complex conditional logic. The correct scripts will use nested CASE statements to apply multiple levels of conditions for discount calculations.

Q30

Multiple Choice

Which SQL scripts correctly use the CASE statement to create calculated fields in PostgreSQL?

SELECT order_id, quantity, price_per_item, (quantity * price_per_item) * CASE WHEN quantity > 100 THEN 0.85 ELSE 0.95 END AS total_price FROM orders; SELECT order_id, quantity, price_per_item, (quantity * price_per_item) * CASE WHEN quantity > 100 THEN 85 ELSE 95 END AS total_price FROM orders; SELECT order_id, quantity, price_per_item, (quantity * price_per_item) * CASE WHEN quantity > 100 THEN 0.85 ELSE 0.95 END AS total_price FROM orders WHERE quantity IS NOT NULL;SELECT order_id, quantity, price_per_item, (quantity * price_per_item) * CASE WHEN quantity > 100 THEN 85 ELSE 95 END AS total_price FROM orders WHERE quantity IS NOT NULL;

The CASE statement can be used to create calculated fields that dynamically change based on conditions. The correct scripts will calculate the total price of an order including the applied discount.

PostgreSQL Database Quiz Questions