Mar 04, 2019 
287.5k
13
DecCyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
Cyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
The control statement is the heart of the most programming languages as they control the execution of a set of statements. These control statements are available in the SQL as well and generally exploited for query filtration and query optimization through careful selection of tuples that match your requirements. In this blog post, we will explore the SQL CASE Statement and why it is needed by the SQL programmers.
Get yourself dive into deep knowledge of sql server case statement!
The Case Statement in SQL is the way of handling if/then logic. It evaluates a set of conditions and returns one of the possible result expressions. The Case Statement in SQL can be used in two possible formats as given below:
In both formats, you can use an optional Else statement. The Case statement can be used with any clause or statement that allows a valid expression. For example, the CASE statement can be used in statements such as Update, Select, Insert, Delete, Set, etc. the popular clauses where Case statement is used frequently like Order By, Having, Where, IN, select_list, etc. The simple syntax for a Case Statement in SQL can be written as below.
Learn SQL Server in the Easiest Way
-- Syntax for SQL Server and Azure SQL Database
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
CASE
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Read: How to Insert Multiple Rows Using Stored Procedure in SQL?
The simple CASE statement operates by comparing the first expression by When clause for the equivalency. If both expressions are equal, the value in the THEN clause is returned. Here are the following conditions for simple case expressions.
Read: DDL, DML, DCL, TCL & DQL -- The Complete SQL Commands Tutorial
Read: SQL Cheat Sheet With Powerful Tips & Tricks
The CASE statement is slightly more complicated and substantially more useful functionality that comes from pairing it with aggregate functions. For example, you want to count rows that satisfy a certain condition, you must use the CASE statement here to calculate the output and it will give NULL and non-null based on the condition. If you wanted to calculate rows on the basis of multiple conditions then use the “Group by” clause, in that case, to make the query simple as shown below.
SELECT CASE WHEN year = 'FR' THEN 'FR'
ELSE 'Not FR' END AS year_group,
COUNT(1) AS count
FROM benn.college_football_players
GROUP BY CASE WHEN year = 'FR' THEN 'FR'
ELSE 'Not FR' END
If you will not use aggregate functions here then the query will be lengthy and more prone to errors. Using a Case statement with the aggregate function may be complicated at first glance but little practice will make you an expert in using the CASE statement. If you still struggle in writing a CASE statement then practice problems online or join some online training program to become an SQL pro.
Read: What is Foreign Key in SQL? How to Set, Add, Create & Use of Foreign Key
It is possible to display data either horizontally or vertically. in the above example, data is represented vertically. When you wanted to represent data horizontally using the CASE statement, it is called the pivoting or a pivot table in Excel. Rearranging data from horizontal to vertical orientation or vice versa can be quite difficult. So, it is necessary to learn the concept in-depth and practice as many problems as possible.here is a quick example, how to display data horizontally using the CASE statement in SQL.
Read: SQL Server Views - Everything You Should Know
SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
COUNT(CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) AS jr_count,
COUNT(CASE WHEN year = 'SR' THEN 1 ELSE NULL END) AS sr_count
FROM benn.college_football_players
SQL Server Training & Certification
The next common clause that is used frequently with the CASE statement is Order by clause. It is used to arrange rows in a specific order as required. For example, if there is one salary column where you want to arrange the employee column on the basis of their salary from top to bottom then you should simply use Order By clause here. It is a common clause for the SQL database, so necessary to practice by SQL developers to manage huge data files within a database. The career path of a SQL Server DBA
SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
ELSE CountryRegionName END;
Let’s get started with SQL Server online training
Read: Why Is There Need for Notification In Jenkins?
Further, the CASE statement can also be used with the Update statement. For example, if an employee takes 10 days’ leaves, his salary would be deducted from the accordingly and updated in the database. In the same way, we can club multiple expressions, statements, and clauses in SQL according to requirements . The only thing is that you should know how to use them perfectly.
USE AdventureWorks2012;
GO
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) SQL Server Training & Certification
Moreover, when we learn SQL statements, theoretical knowledge is not much help but you should have practical experience in managing databases. If you start as a beginner then join some training program first to learn the basics. Once you are completed with SQL basics, you can move to the advanced stage too. After completion of training, attempt certification exam and get certified. Salary Structure of a SQL Server Developer and Admin
Based on research, certified SQL developers usually get jobs quickly with attractive salary packages. They are given more preference over non-certified candidates because of their practical knowledge and hands-on experience on related tools. SQL is one of the popular evolving IT fields with massive career opportunities and salary options. If you are also planning to start a career in SQL then join Oracle certification course at JanBask Training and take your career to new heights.
Read: SSIS Package - SSIS DB, Security and Upgrades
FaceBook
Twitter
LinkedIn
Pinterest
Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Jan 04, 2022
984.3k
984.3k