MCQs Test!! VB Basic Test !! Essentials of VB !! Creating User Services !! function !! Anatomy !! GK March-2019 !! DATA TYPES IN JAVA !! Classes ,Constructor ,Methods,Inheritance !! Ecology-I !! Biology 2019 !! Gk april 2019 !! Biology (living world) !! Plant anatomy !! CELL STRUCTURES AND FUNCTION !! General biology !! Human physiology !! Data structure !! Ecology-II !! GK TEST !! Reproduction in plants !! Human reproduction !! The ABC of C !! Operating system set1 !! Operating system set2 !! Gk and current affairs !! Animal kingdom !! Number system !! Animal kingdom-II !! Gk TEST-III !! Number series !! Microorganisms !! Interview question in c !! Time and Work !! Percentage !! General history !! Average !! Cells !! Locomotion and Movement !! paging !! Bacteriology !! Page replacement policy !! Secondary memory and Disk scheduling !! Introduction of Nervous System and neurons !! Plant harmones !! Transport and Mineral Nutrition in Plants !! Neurotransmitters !! Modern Indian History !! Photosynthesis in Plants !! Modern Indian History-II !! Plant respiration !! Environment & Biodiversity !! Environment & Biodiversity - II !! Algae, Bryophytes, Pteridophytes !! Area !! Cricket !! Days of the Year (India and the World) !! Plant Pathology - I !! Indian Transport System !! Plant Pathology - II !! Aptitude !! Biology !! Botany !! C language !! Computer Science !! Core Java !! Gate !! GK !! Mysql !! Operating system !! Physics !! VB-Visual Basic !! Zoology

DATABASE & SQL/PLSQL

The CASE statement and WHILE statement
Previous Home Next

The CASE Statement

  1. SQL server provide a programming construct called CASE statement
  2. In this situation where several conditions need to be evaluated
  3. The CASE statement evaluate a list of conditions and return one of the various possible results
  4. Here we can use IF statement to do same task

Syntax:

CASE
    WHEN boolean_expression THEN expression
    [[WHEN boolean_expression THEN expression]..] 
    [ELSE expression]
END

where, boolean_expression is a Boolean expression that is evaluated when using the CASE statement.

Example:Give the position according to there Salary.

SELECT  emp_name, emp_sal, 'emp_position'= CASE
   WHEN  emp_sal=60000  THEN  'Team Leader'
   WHEN  emp_sal=45000  THEN  'Junior Developer'
   WHEN  emp_sal=80000  THEN  'Project Manager'
   WHEN  emp_sal=50000  THEN  'Developer'
ELSE  'Unknown'
END
FROM  employee

output

 

The WHILE Statement

  1. While statement is used in a batch, a stored procedure, a trigger, or a cursor
  2. Its allowed a set of T-SQL statements to execute repeatedly as long as the given condition hold true

Syntax:

WHILE boolean_expression

{sql_statement}
[BREAK]
{sql_statement}
[CONTINUE]

where, boolean_expression is an expression that evaluate to TRUE or FALSE.

sql_statement is any SQL statement.

BREAK cause the control to exit from the WHILE loop.

CONTINUE cause the WHILE loop to restart, skipping all the statements after the CONTINUE keyword.

SQL Server provides the BREAK and CONTINUE statements that help control the statements within the WHILE loop.

Example: Factorial of 5

DECLARE @num int
DECLARE @fact int
SELECT @num=5
SELECT @fact=1
WHILE @num>0
BEGIN
      SELECT @fact=@fact*@num
      SELECT @num=@num-1
END
PRINT @fact
output 120
Previous Home Next