Test Your Oracle CASE Statement Knowledge with This SQL Quiz

Progress

True / False

The CASE statement in Oracle is used to perform conditional logic in SQL queries.

True False

The CASE statement allows for conditional logic within SQL queries, similar to if-else statements in programming languages.

True / False

In Oracle, 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 CASE statement returns NULL.

True / False

The CASE statement in Oracle can be used in both SELECT and UPDATE statements.

True False

The CASE statement can be used in SELECT, UPDATE, and other SQL statements to provide conditional logic.

True / False

In Oracle, the CASE statement can only evaluate equality conditions.

True False

The CASE statement can evaluate a variety of conditions, including equality, inequality, and other comparisons.

True / False

The CASE statement in Oracle can be nested within another CASE statement.

True False

CASE statements can be nested to handle complex conditional logic within SQL queries.

True / False

In Oracle, the CASE statement can include aggregate functions within its conditions.

True False

Aggregate functions can be used within CASE statement conditions to evaluate based on aggregated data.

True / False

In Oracle, the CASE statement can be used in the ORDER BY clause to sort results conditionally.

True False

The CASE statement can be used in the ORDER BY clause to apply conditional sorting to the result set.

True / False

The CASE statement in Oracle can be used to return different data types based on conditions.

True False

All return values in a CASE statement must be of the same data type or compatible types to avoid data type errors.

True / False

In Oracle, performance of queries with CASE statements can be optimized using indexes and careful query design.

True False

Proper indexing and query optimization can help improve the performance of queries that use CASE statements.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using the CASE statement effectively, understanding its syntax, and optimizing its performance in SQL queries.

True False

The OCP certification covers advanced topics, including the effective use of the CASE statement, understanding its syntax, and optimizing its performance in SQL queries.

Q11

Single Choice

What is the primary purpose of the CASE statement in ORACLE SQL?

To execute a series of SQL commands To evaluate a condition and return one of multiple possible results To join two tablesTo filter rows based on a condition

The CASE statement in Oracle SQL is used to evaluate conditions and return different values based on the outcome of those conditions.

Q12

Single Choice

How many ELSE clauses can you include in a single CASE statement in ORACLE SQL?

One Two As many as neededNone

In Oracle SQL, a CASE statement can include only one ELSE clause, which provides a default value if none of the WHEN conditions are met.

Q13

Single Choice

Which keyword is used to end a CASE statement in ORACLE SQL?

END CASE FINISH ENDCLOSE

The CASE statement in Oracle SQL is terminated using the END keyword

Q14

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT employee_id,
 CASE
 WHEN salary > 50000 THEN 'High'
 WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
 ELSE 'Low'
 END AS salary_level
FROM employees;

Employees' salary levels as High, Medium, or Low based on their salary The total salary of all employees The count of employees in each salary levelThe average salary of employees

The query categorizes employees' salaries into three levels: High, Medium, and Low, based on their salary amount.

Q15

Single Choice

Which of the following ORACLE SQL queries correctly uses the CASE statement to return 'Above Average' for salaries above the average salary and 'Below Average' otherwise?

SELECT employee_id, CASE WHEN salary > AVG(salary) THEN 'Above Average' ELSE 'Below Average' END AS salary_comparison FROM employees; SELECT employee_id, CASE WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'Above Average' ELSE 'Below Average' END AS salary_comparison FROM employees; SELECT employee_id, CASE WHEN salary > AVG(salary) THEN 'Above Average' ELSE 'Below Average' END AS salary_comparison FROM employees GROUP BY employee_id;SELECT employee_id, CASE WHEN salary = 'Above Average' THEN salary ELSE 'Below Average' END AS salary_comparison FROM employees;

This query correctly compares each employee's salary against the average salary using a subquery and the CASE statement.

Q16

Single Choice

Which ORACLE SQL query correctly implements a CASE statement to categorize employees as 'Senior' if their experience is more than 10 years, and 'Junior' otherwise?

SELECT employee_id, CASE WHEN experience > 10 THEN 'Senior' ELSE 'Junior' END AS experience_level FROM employees; SELECT employee_id, CASE WHEN experience = 10 THEN 'Senior' ELSE 'Junior' END AS experience_level FROM employees; SELECT employee_id, CASE WHEN experience > 10 THEN 'Junior' ELSE 'Senior' END AS experience_level FROM employees;SELECT employee_id, CASE WHEN experience BETWEEN 1 AND 10 THEN 'Senior' ELSE 'Junior' END AS experience_level FROM employees;

This query correctly uses the CASE statement to categorize employees based on their years of experience.

Q17

Single Choice

Consider the following Oracle SQL query:What does this query return?

The total salary of employees in each department The total salary of 'SA_REP' job holders in each department The total salary of all employeesThe department with the highest salary

The query sums the salaries of employees with the job ID 'SA_REP' in each department using the CASE statement.

Q18

Single Choice

Which ORACLE SQL query correctly uses the CASE statement to replace NULL values in the salary column with 'Unknown'?

SELECT employee_id, CASE WHEN salary IS NULL THEN 'Unknown' ELSE salary END AS adjusted_salary FROM employees; SELECT employee_id, CASE WHEN salary IS NOT NULL THEN 'Unknown' ELSE salary END AS adjusted_salary FROM employees; SELECT employee_id, CASE WHEN salary IS NULL THEN salary ELSE 'Unknown' END AS adjusted_salary FROM employees;SELECT employee_id, CASE WHEN salary IS NOT NULL THEN salary ELSE 'Unknown' END AS adjusted_salary FROM employees;

This query correctly uses the CASE statement to replace NULL values in the salary column with 'Unknown'.

Q19

Single Choice

Which of the following ORACLE SQL queries will calculate a bonus for employees based on the following logic: 10% of the salary for 'Manager' job, 5% of the salary for 'Clerk' job, and 2% for all others?

SELECT employee_id, CASE WHEN job_id = 'Manager' THEN salary * 0.10 WHEN job_id = 'Clerk' THEN salary * 0.05 ELSE salary * 0.02 END AS bonus FROM employees; SELECT employee_id, CASE WHEN job_id = 'Manager' THEN salary * 0.10 ELSE CASE WHEN job_id = 'Clerk' THEN salary * 0.05 ELSE salary * 0.02 END END AS bonus FROM employees; SELECT employee_id, CASE WHEN job_id = 'Clerk' THEN salary * 0.05 ELSE salary * 0.02 END AS bonus FROM employees;SELECT employee_id, CASE WHEN job_id = 'Clerk' THEN salary * 0.05 WHEN job_id = 'Manager' THEN salary * 0.10 ELSE salary * 0.02 END AS bonus FROM employees;

This query correctly uses the CASE statement to calculate the bonus based on the job ID.

Q20

Single Choice

Given the following ORACLE SQL query:What does this query do?

Categorizes products based on whether their price is below, above, or equal to the average price Returns only products with a price below average Returns the average price of all productsFilters products based on price range

This query uses a CASE statement to compare each product's price to the average price of all products and categorizes them accordingly.

Q21

Multiple Choice

Which SQL code snippet demonstrates a basic use of the CASE statement to categorize employees based on their salary in Oracle?

SQL Code

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

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

SELECT employee_id, salary, CASE WHEN salary > 50000 THEN 'High' WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees; SELECT employee_id, salary, CASE WHEN salary > 60000 THEN 'High' WHEN salary BETWEEN 30000 AND 60000 THEN 'Medium' ELSE 'Low' END AS salary_category FROM employees; SELECT employee_id, salary, CASE WHEN salary > 50000 THEN 'Low' WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium' ELSE 'High' END AS salary_category FROM employees;SELECT employee_id, salary, FROM employees WHERE salary > 50000;

Options A and B correctly use the CASE statement to categorize employees based on their salary. Option C is incorrect because it categorizes incorrectly, and Option D is incorrect as it does not use the CASE statement.

Q22

Multiple Choice

Which SQL code snippet uses the CASE statement to handle NULL values in Oracle?

SQL Code

SELECT employee_id,
 CASE
 WHEN manager_id IS NULL THEN 'No Manager'
 ELSE manager_id
 END AS manager_status
FROM employees;

SELECT employee_id,
 CASE
 WHEN commission_pct IS NULL THEN 'No Commission'
 ELSE TO_CHAR(commission_pct * 100) || '%'
 END AS commission_status
FROM employees;

SELECT employee_id,
 CASE
 WHEN hire_date IS NULL THEN 'Hire Date Unknown'
 ELSE TO_CHAR(hire_date, 'MM/DD/YYYY')
 END AS hire_date_status
FROM employees;

SELECT employee_id, CASE WHEN manager_id IS NULL THEN 'No Manager' ELSE manager_id END AS manager_status FROM employees; SELECT employee_id, CASE WHEN commission_pct IS NULL THEN 'No Commission' ELSE TO_CHAR(commission_pct * 100) || '%' END AS commission_status FROM employees; SELECT employee_id, CASE WHEN hire_date IS NULL THEN 'Hire Date Unknown' ELSE TO_CHAR(hire_date, 'MM/DD/YYYY') END AS hire_date_status FROM employees;SELECT employee_id, FROM employees WHERE hire_date IS NULL;

Options A, B, and C correctly use the CASE statement to handle NULL values in different contexts. Option D is incorrect because it does not handle NULL values using the CASE statement.

Q23

Multiple Choice

Which SQL code snippet demonstrates the use of the CASE statement within a GROUP BY clause in Oracle?

SQL Code

SELECT department_id,
 CASE
 WHEN AVG(salary) > 50000 THEN 'High Average Salary'
 ELSE 'Low Average Salary'
 END AS avg_salary_category
FROM employees
GROUP BY department_id;

SELECT department_id,
 CASE
 WHEN COUNT(employee_id) > 10 THEN 'Large Department'
 ELSE 'Small Department'
 END AS department_size
FROM employees
GROUP BY department_id;

SELECT department_id, CASE WHEN AVG(salary) > 50000 THEN 'High Average Salary' ELSE 'Low Average Salary' END AS avg_salary_category FROM employees GROUP BY department_id; SELECT department_id, CASE WHEN COUNT(employee_id) > 10 THEN 'Large Department' ELSE 'Small Department' END AS department_size FROM employees GROUP BY department_id; SELECT department_id, CASE WHEN COUNT(employee_id) > 10 THEN 'Large Department' ELSE 'Small Department' END AS department_size FROM employees;SELECT department_id, salary FROM employees GROUP BY department_id;

Options A and B correctly use the CASE statement within a GROUP BY clause to categorize departments. Option C is incorrect because it does not group by department_id, and Option D is incorrect as it does not use the CASE statement within a GROUP BY clause.

Q24

Multiple Choice

Which SQL code snippet uses the CASE statement to create a custom ordering of data in Oracle?

SQL Code

SELECT employee_id, job_title
FROM employees
ORDER BY
 CASE
 WHEN job_title = 'Manager' THEN 1
 WHEN job_title = 'Developer' THEN 2
 ELSE 3
 END;

SELECT department_id, salary
FROM employees
ORDER BY
 CASE
 WHEN salary > 60000 THEN 1
 WHEN salary BETWEEN 40000 AND 60000 THEN 2
 ELSE 3
 END;

SELECT employee_id, job_title FROM employees ORDER BY CASE WHEN job_title = 'Manager' THEN 1 WHEN job_title = 'Developer' THEN 2 ELSE 3 END; SELECT department_id, salary FROM employees ORDER BY CASE WHEN salary > 60000 THEN 1 WHEN salary BETWEEN 40000 AND 60000 THEN 2 ELSE 3 END; SELECT employee_id, job_title FROM employees ORDER BY CASE WHEN job_title = 'Developer' THEN 1 WHEN job_title = 'Manager' THEN 2 ELSE 3 END;SELECT employee_id, job_title FROM employees ORDER BY job_title;

Options A and B correctly use the CASE statement to create a custom ordering of data. Option C is incorrect because it does not order correctly, and Option D is incorrect as it does not use the CASE statement for ordering.

Q25

Multiple Choice

Which SQL code snippet demonstrates the use of the CASE statement to apply conditional logic to multiple columns in Oracle?

SQL Code

SELECT employee_id,
 CASE
 WHEN salary > 50000 AND commission_pct IS NOT NULL THEN 'High Salary with Commission'
 WHEN salary > 50000 THEN 'High Salary'
 ELSE 'Standard Salary'
 END AS salary_status
FROM employees;

SELECT employee_id,
 CASE
 WHEN job_title = 'Manager' AND department_id = 10 THEN 'Top Manager'
 WHEN job_title = 'Manager' THEN 'Manager'
 ELSE 'Staff'
 END AS job_status
FROM employees;

SELECT employee_id, CASE WHEN salary > 50000 AND commission_pct IS NOT NULL THEN 'High Salary with Commission' WHEN salary > 50000 THEN 'High Salary' ELSE 'Standard Salary' END AS salary_status FROM employees; SELECT employee_id, CASE WHEN job_title = 'Manager' AND department_id = 10 THEN 'Top Manager' WHEN job_title = 'Manager' THEN 'Manager' ELSE 'Staff' END AS job_status FROM employees; SELECT employee_id, CASE WHEN salary > 50000 THEN 'High Salary' ELSE 'Standard Salary' END AS salary_status FROM employees;SELECT employee_id, job_title FROM employees;

Options A and B correctly use the CASE statement to apply conditional logic to multiple columns. Option C is incorrect because it does not consider multiple columns, and Option D is incorrect as it does not use the CASE statement.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced use of the CASE statement for calculating a new column value based on multiple conditions in Oracle?

SQL Code

SELECT order_id,
 CASE
 WHEN order_total > 1000 AND shipping_cost IS NOT NULL THEN order_total + shipping_cost
 WHEN order_total > 1000 THEN order_total + 50
 ELSE order_total
 END AS final_total
FROM orders;

SELECT order_id,
 CASE
 WHEN customer_type = 'VIP' THEN order_total * 0.9
 WHEN customer_type = 'Regular' THEN order_total * 0.95
 ELSE order_total
 END AS discounted_total
FROM orders;

SELECT order_id, CASE WHEN order_total > 1000 AND shipping_cost IS NOT NULL THEN order_total + shipping_cost WHEN order_total > 1000 THEN order_total + 50 ELSE order_total END AS final_total FROM orders; SELECT order_id, CASE WHEN customer_type = 'VIP' THEN order_total * 0.9 WHEN customer_type = 'Regular' THEN order_total * 0.95 ELSE order_total END AS discounted_total FROM orders; SELECT order_id, CASE WHEN order_total > 1000 THEN order_total + 50 ELSE order_total END AS final_total FROM orders;SELECT order_id, order_total FROM orders;

Options A and B correctly demonstrate advanced use of the CASE statement for calculating a new column value based on multiple conditions. Option C is incorrect because it does not apply multiple conditions, and Option D is incorrect as it does not use the CASE statement.

Q27

Multiple Choice

Which SQL code snippet demonstrates the use of the CASE statement to create a calculated column for a report in Oracle?

SQL Code

SELECT employee_id, salary,
 CASE
 WHEN salary > 50000 THEN salary * 1.1
 ELSE salary * 1.05
 END AS adjusted_salary
FROM employees;

SELECT department_id, salary,
 CASE
 WHEN department_id = 10 THEN salary * 1.2
 ELSE salary * 1.1
 END AS department_adjusted_salary
FROM employees;

SELECT employee_id, salary, CASE WHEN salary > 50000 THEN salary * 1.1 ELSE salary * 1.05 END AS adjusted_salary FROM employees; SELECT department_id, salary, CASE WHEN department_id = 10 THEN salary * 1.2 ELSE salary * 1.1 END AS department_adjusted_salary FROM employees; SELECT employee_id, salary * 1.1 FROM employees;SELECT employee_id, salary FROM employees;

Options A and B correctly use the CASE statement to create a calculated column for a report. Option C is incorrect because it does not provide a complete calculation, and Option D is incorrect as it does not use the CASE statement for a calculated column.

Q28

Multiple Choice

Which SQL code snippet demonstrates certification-level use of the CASE statement to apply complex conditional logic to sales data in Oracle?

SQL Code

SELECT sale_id,
 CASE
 WHEN sale_amount > 500 AND sale_date >= SYSDATE - 30 THEN sale_amount * 1.05
 WHEN sale_amount <= 500 AND sale_date < SYSDATE - 30 THEN sale_amount * 1.02
 ELSE sale_amount
 END AS adjusted_sale_amount
FROM sales;

SELECT sale_id,
 CASE
 WHEN region = 'North' AND sale_amount > 1000 THEN sale_amount * 1.1
 WHEN region = 'South' AND sale_amount > 500 THEN sale_amount * 1.05
 ELSE sale_amount
 END AS region_adjusted_sales
FROM sales;

SELECT sale_id, CASE WHEN sale_amount > 500 AND sale_date >= SYSDATE - 30 THEN sale_amount * 1.05 WHEN sale_amount <= 500 AND sale_date < SYSDATE - 30 THEN sale_amount * 1.02 ELSE sale_amount END AS adjusted_sale_amount FROM sales; SELECT sale_id, CASE WHEN region = 'North' AND sale_amount > 1000 THEN sale_amount * 1.1 WHEN region = 'South' AND sale_amount > 500 THEN sale_amount * 1.05 ELSE sale_amount END AS region_adjusted_sales FROM sales; SELECT sale_id, CASE WHEN sale_amount > 500 THEN sale_amount * 1.05 ELSE sale_amount END AS adjusted_sale_amount FROM sales;SELECT sale_id, sale_amount FROM sales;

Options A and B correctly demonstrate certification-level use of the CASE statement to apply complex conditional logic to sales data. Option C is incorrect because it does not include all conditions, and Option D is incorrect as it does not use the CASE statement.

Q29

Multiple Choice

Which SQL code snippet demonstrates the use of the CASE statement to dynamically select different columns based on conditions in Oracle?

SQL Code

SELECT employee_id,
 CASE
 WHEN job_title = 'Manager' THEN department_id
 WHEN job_title = 'Developer' THEN salary
 ELSE hire_date
 END AS dynamic_column
FROM employees;

SELECT employee_id,
 CASE
 WHEN department_id = 10 THEN salary * 1.2
 WHEN department_id = 20 THEN salary * 1.1
 ELSE salary
 END AS adjusted_salary
FROM employees;

SELECT employee_id, CASE WHEN job_title = 'Manager' THEN department_id WHEN job_title = 'Developer' THEN salary ELSE hire_date END AS dynamic_column FROM employees; SELECT employee_id, CASE WHEN department_id = 10 THEN salary * 1.2 WHEN department_id = 20 THEN salary * 1.1 ELSE salary END AS adjusted_salary FROM employees; SELECT employee_id, CASE WHEN department_id = 10 THEN salary * 1.2 ELSE salary END AS adjusted_salary FROM employees;SELECT employee_id, department_id, salary FROM employees;

Options A and B correctly use the CASE statement to dynamically select different columns based on conditions. Option C is incorrect because it does not select different columns dynamically, and Option D is incorrect as it does not use the CASE statement for dynamic column selection.

Q30

Multiple Choice

Which SQL code snippet demonstrates the use of the CASE statement to create a summary report with conditional logic applied to aggregated data in Oracle?

SQL Code

SELECT department_id, COUNT(employee_id) AS num_employees,
 CASE
 WHEN AVG(salary) > 60000 THEN 'High Average Salary'
 ELSE 'Moderate Average Salary'
 END AS salary_status
FROM employees
GROUP BY department_id;

SELECT region, SUM(sales_amount) AS total_sales,
 CASE
 WHEN SUM(sales_amount) > 100000 THEN 'High Revenue Region'
 ELSE 'Moderate Revenue Region'
 END AS revenue_status
FROM sales
GROUP BY region;

SELECT department_id, COUNT(employee_id) AS num_employees, CASE WHEN AVG(salary) > 60000 THEN 'High Average Salary' ELSE 'Moderate Average Salary' END AS salary_status FROM employees GROUP BY department_id; SELECT region, SUM(sales_amount) AS total_sales, CASE WHEN SUM(sales_amount) > 100000 THEN 'High Revenue Region' ELSE 'Moderate Revenue Region' END AS revenue_status FROM sales GROUP BY region; SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;SELECT region, sales_amount FROM sales GROUP BY region;

Options A and B correctly use the CASE statement to create a summary report with conditional logic applied to aggregated data. Option C is incorrect because it does not group by the necessary columns, and Option D is incorrect as it does not use the CASE statement for the summary report.

Oracle Database Quiz Questions