SQL (Structured Query Language) is a powerful tool for managing and manipulating data in relational databases. One of the most useful features of SQL is the ability to use the "CASE" statement to make decisions based on the values in a particular column. The "CASE" statement can be used in a variety of ways, including in SELECT, UPDATE, and DELETE statements. In this article, we will explore the use of the "CASE" statement in SQL, including how to use multiple "CASE" statements in a single query, and provide code examples to illustrate the concepts.
The basic syntax of the "CASE" statement in SQL is as follows:
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
The "CASE" statement begins with the keyword "CASE", followed by one or more "WHEN" clauses. Each "WHEN" clause includes a condition and a result. If the condition is true, the corresponding result will be returned. If none of the conditions are true, the result in the "ELSE" clause will be returned.
To illustrate this concept, consider the following example. We have a table called "employees" with columns "employee_id", "employee_name", and "employee_salary". We want to create a query that returns the employee's name and their salary, but we want to add a column called "salary_range" that indicates whether the employee's salary is above or below the average salary. We can use a "CASE" statement to accomplish this.
SELECT employee_name, employee_salary,
CASE
WHEN employee_salary > (SELECT AVG(employee_salary) FROM employees) THEN 'Above Average'
ELSE 'Below Average'
END AS salary_range
FROM employees;
Now, let's look at how we can use multiple "CASE" statements in a single query. For example, we have a table named "sales" with columns "product_name", "quantity", and "price". We want to create a query that returns the total revenue for each product, but we also want to add a column called "profit_range" that indicates whether the product is profitable or not. We can use multiple "CASE" statements to accomplish this.
SELECT product_name, SUM(quantity * price) as revenue,
CASE
WHEN SUM(quantity * price) > 1000 THEN 'Profitable'
ELSE 'Not Profitable'
END AS profit_range,
CASE
WHEN SUM(quantity * price) > 2000 THEN 'Highly Profitable'
WHEN SUM(quantity * price) > 1000 THEN 'Moderately Profitable'
ELSE 'Not Profitable'
END AS profit_range2
FROM sales
GROUP BY product_name;
In this example, we have used two "CASE" statements. The first "CASE" statement is used to determine whether the product is profitable or not based on the total revenue. The second "CASE" statement is used to determine the level of profitability based on the total revenue.
It is worth noting that, in addition to the above examples, the "CASE" statement can also be used in UPDATE and DELETE statements. For example, you can use a "CASE" statement in an UPDATE statement to update the values in a column based on certain conditions
Sure, one of the most powerful features of the "CASE" statement is the ability to use it in conjunction with other SQL constructs, such as the "WHERE" clause or "GROUP BY" clause.
For example, you can use a "CASE" statement in the "WHERE" clause to filter the results of a query based on certain conditions. For example, you can use a "CASE" statement to filter the results of a query to only show the employees whose salary is above or below the average salary.
SELECT employee_name, employee_salary
FROM employees
WHERE
CASE
WHEN employee_salary > (SELECT AVG(employee_salary) FROM employees) THEN 'Above Average'
ELSE 'Below Average'
END = 'Above Average';
Another example is that you can use a "CASE" statement in the "GROUP BY" clause to group the results of a query based on certain conditions. For example, you can use a "CASE" statement to group the results of a query by the product's profitability.
SELECT
CASE
WHEN SUM(quantity * price) > 1000 THEN 'Profitable'
ELSE 'Not Profitable'
END AS profit_range,
SUM(quantity * price) as revenue
FROM sales
GROUP BY
CASE
WHEN SUM(quantity * price) > 1000 THEN 'Profitable'
ELSE 'Not Profitable'
END;
Also, you can use the "CASE" statement in combination with aggregate functions, such as SUM, COUNT, AVG, etc. This allows you to perform calculations on the results of a query based on certain conditions. For example, you can use a "CASE" statement to calculate the total salary for employees whose salary is above or below the average salary.
SELECT
SUM(CASE WHEN employee_salary > (SELECT AVG(employee_salary) FROM employees) THEN employee_salary ELSE 0 END) AS above_average_salary,
SUM(CASE WHEN employee_salary <= (SELECT AVG(employee_salary) FROM employees) THEN employee_salary ELSE 0 END) AS below_average_salary
FROM employees;
In conclusion, the "CASE" statement is a powerful tool in SQL that allows you to make decisions based on the values in a particular column. It can be used in a variety of ways, including in SELECT, UPDATE, and DELETE statements, and in combination with other SQL constructs such as "WHERE" and "GROUP BY" clauses, and aggregate functions.
Popular questions
- What is the basic syntax of the "CASE" statement in SQL?
The basic syntax of the "CASE" statement in SQL is as follows:
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END
- How can the "CASE" statement be used in conjunction with the "WHERE" clause to filter the results of a query?
You can use a "CASE" statement in the "WHERE" clause to filter the results of a query based on certain conditions. For example, you can use a "CASE" statement to filter the results of a query to only show the employees whose salary is above or below the average salary.
SELECT employee_name, employee_salary
FROM employees
WHERE
CASE
WHEN employee_salary > (SELECT AVG(employee_salary) FROM employees) THEN 'Above Average'
ELSE 'Below Average'
END = 'Above Average';
- How can the "CASE" statement be used in conjunction with the "GROUP BY" clause to group the results of a query?
You can use a "CASE" statement in the "GROUP BY" clause to group the results of a query based on certain conditions. For example, you can use a "CASE" statement to group the results of a query by the product's profitability.
SELECT
CASE
WHEN SUM(quantity * price) > 1000 THEN 'Profitable'
ELSE 'Not Profitable'
END AS profit_range,
SUM(quantity * price) as revenue
FROM sales
GROUP BY
CASE
WHEN SUM(quantity * price) > 1000 THEN 'Profitable'
ELSE 'Not Profitable'
END;
- Can the "CASE" statement be used in combination with aggregate functions such as SUM, COUNT, AVG, etc?
Yes, you can use the "CASE" statement in combination with aggregate functions, such as SUM, COUNT, AVG, etc. This allows you to perform calculations on the results of a query based on certain conditions. For example, you can use a "CASE" statement to calculate the total salary for employees whose salary is above or below the average salary.
SELECT
SUM(CASE WHEN employee_salary > (SELECT AVG(employee_salary) FROM employees) THEN employee_salary ELSE 0 END) AS above_average_salary,
SUM(CASE WHEN employee_salary <= (SELECT AVG(employee_salary) FROM employees) THEN employee_salary ELSE 0 END) AS below_average_salary
FROM employees;
- Can the "CASE" statement be used in UPDATE and DELETE statements?
Yes, you can use a "CASE" statement in UPDATE and DELETE statements to update or delete the values in a column based on certain conditions. For example, you can use a "CASE" statement in an UPDATE statement to update the values in a column based on certain conditions.
UPDATE employees
SET employee_salary =
CASE
WHEN employee_salary > (SELECT AVG(employee_salary) FROM employees) THEN employee_salary * 1.1
ELSE employee_salary * 1.05
END
Tag
SQL
