STUDENTS 3K

  • Home
  • Students+
    • Engineering Students
    • Education Loan
    • Study Abroad Guide
    • Projects Download
  • Freshers
    • Aptitude Q & A
    • Placement Papers
    • Verbal Ability
    • Interview Questions
    • IT Company Details
    • Job Updates
  • Study Resources
    • Career Guidance
    • LAB Programs
      • C Programs
      • CPP Programs [C++]
      • Java Programs
    • Question Papers
    • Learn English
    • Notice Board
  • More –>>
    • Love calculator
You are here: Home / Interview / CASE Expression in SQL

CASE Expression in SQL

The CASE statement is popularly known as CASE Expression, allows the user to execute a part of SQL statement conditionally. In SQL, CASE statement is used for storing the procedure or formula of a particular column. The CASE expression can be classified into two types namely:

  • Simple CASE Statement
  • Searched CASE Statement

Basic Syntax for Simple CASE Statement

CASE inp_exp1 /* It is just your column name */
WHEN condi_exp1 THEN reslt_exp1
[WHEN condi_exp2 THEN reslt_exp2]
[ELSE reslt_exp]
END

Difference Between Inner and Outer Join

How CASE Expression Work in SQL?

In our CASE expression syntax, the WHEN conditional statement will be evaluated only if input_exp is similar to condi_exp. If the condi_exp is returned similar to the input_exp then the analogous result_exp will be executed. If one WHEN condition is executed then other conditions will not be executed. In other words, in CASE expression a condition which matches first will be executed.

What happens when conditions are not matched in CASE expression?

If conditions mentioned in WHEN condition are not matched with CASE expression then the expression mentioned in the ELSE condition will be displayed. If ELSE statement is not used then NULL value will be returned.

Simple CASE Expression

In simple CASE statement, a selector expression will be evaluated and will be matched against one or more WHEN condition. The basic syntax for simple CASE expression is given below.

CASE selector
WHEN cond1
THEN result1;
WHEN cond2
THEN result2;
ELSE
result3;
END CASE

Searched CASE Expression

Searched CASE expression does not have any selector expression and will attempt to match against more than one WHEN condition. The basic syntax for searched CASE expression is given below.

CASE
WHEN condition1 THEN
O/p 1; //Output 1
WHEN condition2 THEN
O/p 2; //Output 2
ELSE
O/p 3; //Output 3
END CASE;

  • Related: SQL & .NET Interview questions and Answers

Applications of CASE Expressions

CASE expressions can be applied in any place where scalar expressions are allowed. Scalar expressions like WHERE and HAVING clauses present in a SELECT statement are also allowed. CASE expressions are popularly used to find Stored procedures, View and Formula of particular column.

Example for CASE Expression

Consider the following code as an example, in the code given below when the player_id is matched with barce_id then the corresponding player name will be listed as output. If the player_id and barce_id are not matched then ELSE condition will be used and “not_a_barce_playerz” will be displayed as an output.

SELECT playerz_id,
CASE Barce_id
WHEN 1 THEN ‘Iniessta’

WHEN 2 THEN ‘Messsi’

ELSE ‘not_a_barce_playerz’
END
FROM teamm_roster;

The above code can be re-written without expression clause. The rewritten SQL code without expression clause in CASE statement is given below.

SELECT playerz_id,
CASE
WHEN Barce_id = 1 THEN ‘Iniessta’
WHEN Barce_id = 2 THEN ‘Messsi’

ELSE ‘not_a_barce_playerz’
END
FROM teamm_roster;

It is not mandatory to use ELSE condition in CASE expression because the ELSE condition will not be utilized when the conditional expression is matched with result expression. If expressions did not match then NULL will be returned as output. The SQL codes for our example without ELSE statement is given below.

SELECT playerz_id,
CASE Barce_id
WHEN 1 THEN ‘Iniessta’
WHEN 2 THEN ‘Messsi’
END
FROM contacts;

  • Read: More Interview Questions

Use of CASE Expression in Table Column Formula

When table design mode is created, properties of each column with several property values like default column value and identity column value are set. The data manipulation program for the table can be customized by the user. The following SQL code can be used to create column formula for a table.

// This is an SQL CASE pucca statement
(case [Code] when (01) then ‘Place1_01′ when (02) then ‘Place2_02’ _
when(03) then ‘Place3_03’ when(04) then ‘Place4_04’ when(05) _
then ‘Place5_05’ when (06) then ‘Place6_06’ when (07) then ‘Place7_07’ _
when(08) then ‘Place8_08’ when(09) then ‘Place9_09’ else ‘Vacant’ end)

CASE Expressions are also used in viewing an object present in a table. To view the object in a table, scalar expressions like WHERE and HAVING clauses used along with SELECT statement is used in CASE expression.

Filed Under: Interview Tagged With: CASE, SQL, SQL Interview questions





Random Materials :

  • Data Mining and Data Warehousing
  • Difference Between Class and Primitive Types
  • Database Roles in SQL
  • CASE Expression in SQL
  • Concurrent Update Problem

Follow Us on Facebook

Advertisement

Company Profile

  • About Students3k
  • Contact Us
  • Earn Money Online
  • Website Archive Page
  • Privacy Policy
  • Disclaimer

Categories

  • Important Programs
  • Study Abroad
  • IT Companies
  • Career Guidance for Students
  • Teachers
  • Verbal Analogies

We Are Social

  • Facebook Fans
  • Twitter Follow
  • Google Plus
  • Pinterest Page

© Copyright 2012 - 2016 Students3k.com · All Rights Reserved · Designed By Benefits

Show