Case when then else SQL statement

One of the SQL statement judgment methods
Case has two formats. Simple case functions and case search functions.
-- Simple case Function
Case sex
When '1' then 'male'
When '2' then 'female'
Else 'others' end
-- Case search function
Case when sex = '1' then 'male'
When sex = '2' then 'female'
Else 'others' end

These two methods can achieve the same function. Simple case functions are relatively simple in writing, but compared with case search functions, there are some functional limitations, such as writing case functions.
Note that the case function returns only the first value that meets the condition, and the rest of the case will be automatically ignored.

Example:
There is a table with three fields: Chinese, mathematics, and English. There are 3 records indicating 70 points in Chinese, 80 points in mathematics, and 58 points in English, please use an SQL statement to query these three records and display them according to the following conditions (and write your ideas ):
If the value is greater than or equal to 80, it indicates excellent. If the value is greater than or equal to 60, it indicates passing the test. If the value is less than 60, it indicates failing.
Display format:
Chinese, mathematics, and English
Pass excellent fail
------------------------------------------
Select
(Case when language> = 80 then 'excellent'
When language> = 60 then 'pass'
Else 'failed') as language,
(Case when mathematics> = 80 then 'excellent'
When mathematics> = 60 then 'pass'
Else 'failed') as mathematics,
(Case when English> = 80 then 'excellent'
When English> = 60 then 'pass'
Else 'failed') as English,
From table

Case may be one of the keywords most misused in SQL. Although you may have used this keyword before to create a field, it also has more features. For example, you can use case in the WHERE clause.

First, let's take a look at the case syntax. In a general SELECT statement, the syntax is as follows:

Select <mycolumnspec> =
           Case
           When <A> then <somethinga>
           When <B> then <somethingb>
           Else <somethinge>
           End

In the above Code, you need to replace the content in angle brackets with specific parameters. The following is a simple example:

Use pubs
           Go
           Select
           Title,
           'Price range' =
           Case
           When price is null then 'unpriced'
           When price <10 then 'bargain'
           When price between 10 and 20 then 'average'
           Else 'Gift to impress relatives'
           End
           From titles
           Order by price
           Go

This is a typical example of case, but you can do more with case. For example, the case in the group by clause below:

Select 'number of tidles ', count (*)
           From titles
           Group
           Case
           When price is null then 'unpriced'
           When price <10 then 'bargain'
           When price between 10 and 20 then 'average'
           Else 'Gift to impress relatives'
           End
           Go

You can even combine these options to add an order by clause, as shown below:

Use pubs
           Go
           Select
           Case
           When price is null then 'unpriced'
           When price <10 then 'bargain'
           When price between 10 and 20 then 'average'
           Else 'Gift to impress relatives'
           End as range,
           Title
           From titles
           Group
           Case
           When price is null then 'unpriced'
           When price <10 then 'bargain'
           When price between 10 and 20 then 'average'
           Else 'Gift to impress relatives'
           End,
           Title
           Order
           Case
           When price is null then 'unpriced'
           When price <10 then 'bargain'