where to use SQL Case statement ? and when ?and give example?

[gosai jahnvi]

where to use SQL Case statement ? and when ?and give example?

[Luuk]

You can use it to select even or odd numbers:

SELECT 
    i,
    CASE WHEN i%2=1 THEN i ELSE 0 END  AS odd,
    CASE WHEN i%2=0 THEN i ELSE 0 END  AS even
FROM TEST
WHERE i<1000

output:

i    odd    even
1    1    0
2    0    2
3    3    0
4    0    4
5    5    0
6    0    6
7    7    0
8    0    8
9    9    0
10    0    10
11    11    0

In simple cases (like above) there are some alternatives like IIF:

SELECT 
    i,
    IIF (i%2=1, i, 0)  AS odd,
    IIF (i%2=0, i, 0)  AS even
FROM TEST
WHERE i<12

but when expanding this example, you will no longer want to write this using IIF:

SELECT 
    i,
    CASE i%4 
           WHEN 3 THEN 3 
           WHEN 2 THEN 2 
           WHEN 1 THEN 1 
           ELSE 0 END AS remainderAfterDivBy4
FROM TEST
WHERE i<12

output:

i    remainderAfterDivBy4
1    1
2    2
3    3
4    0
5    1
6    2
7    3
8    0
9    1
10    2
11    3

[Rabbit]

Where?
Pretty much anywhere, in the select clause, or a join clause, or a where clause, or an order by clause, etc.

When?
Whenever you need to output different values based on a condition.

Examples?
See Luuk's response above.