How to use Case and While statement in Batches: SQL

Database developer can use the CASE as well as While statement in situation where several conditions need to be evaluated.

Using CASE statement

The CASE statement evaluates a list of conditions and returns one of the possible results. You can use the IF statement to do the same task. However, you can use a CASE statement when there are more than two conditions that check a common variable for different values. The syntax of the CASE statement is:

CASE
WHEN Boolean_expression THEN expression
[ [WHEN Boolean_expression THEN expression] […..] ]
[ELSE expression]
END
Where,

• Boolean_expression specifies a bollean expression that is evaluated when using the CASE construct.
• Expression is the resultant expression that is executed when the Boolean expression evaluates to TRUE. This can be a constant, a column name, a function, a query, or any combination of arithmetic, bit-wise, and string operators.

In a simple CASE construct, a variable or an expression is compared with the Boolean expression in each WHEN clause. If any of these expressions evaluate to TRUE, then the expression specified with the THEN clause is executed. If the expression does not evaluate to TRUE, the expression with the ELSE statement is executed.

Consider the following example where a case construct is included in the SELECT statement to display the marital status as ‘Married’ or Single’:

SELECT EmployeeID, ‘Marital status’ =
CASE MaritalStatus
WHEN ‘M’ THEN ‘Married’
WHEN ‘S’ THEN ‘Single’
ELSE ‘Not specified’
END
FROM HumanResources.Employee
GO

Using the While Statement

You can use the WHILE statement in a batch to allow a set of T-SQL statement to execute repeatedly as long as the given condition holds true. The syntax of the WHILE statement is:

WHILE Boolean_expression
{sql_statement | statement_block}
[BREAK]
{sql_statement | statement_block}
[CONTINUE]
Where,

• Boolean_expression is an expression that evaluates to TRUE or FALSE.
• Sql_statement is any SQL statement.
• Statement_block is a group of SQL statements.
• BREAK causes the control to exit from the WHILE loop.
• CONTINUE causes the WHILE loop to restart, skipping all the statements after the CONTINUE keyword.

The SQL Server provides the BREAK and CONTINUE statements to control the statement within the WHILE loop. The BREAK statement causes an exit from the WHILE loop. Any statements that appear after the END keyword, which marks the end of the loop, are executed after the BREAK statement is executed. The CONTINUE statement causes the WHILE loop to restart, skipping any statements after this statement inside the loop.

Consider the following example where the HR department of AdventureWorks, Inc. has decided to review the salary of all the employees. As per the current HR policy, the average hourly salary rate of all the employees should be approximately $20. You need to increase the hourly salary of all the employees until the average hourly salary reaches near $20. In addition, you need to ensure that the maximum hourly salary should not exceed $127.

WHILE (SELECT AVG(Rate) +1 FROM HumanResources.EmployeePayHistory) <20
BEGIN
UPDATE HumanResources.EmployeePayHIstory
SET Rate = Rate +1
FROM HumanResources.EmployeePayHistory
IF (SELECT max (Rate) +1 FROM
HumanResources.EmployeePayHistory)>127
BREAK
ELSE
CONTINUE
END

How to Implement Object Oriented Design in JAVA

The basic unit of object-orientation in Java is the class. The class is often is described as a blueprint for an object. You can think of an object as an entity having a unique identity, characteristics and behaviour. For instance, a ceilingFan is an object : it has a unique identity ; its characteristics are : it has 3 or 4 blades, it has a motor, a colour etc. ; its behaviour is : it rotates the air at some specific speed. Similarly, a Student is an object. Its characteristics are: its rollno, name class, marks etc. Its behaviour is: it takes test, it attends classes etc.

To create an object in Java, you need a class. It allows a programmer to define all of the properties (i.e. characteristics) and methods (i.e. behaviour) that internally define an object, all of the API (Application programming interface) method that externally define an object. Therefore, we can say that a class is the BLUEPRINT of an object. A class define the types of shared characteristics, such as:

• The set of attributes i.e. characteristics through data.
• The set of behaviour i.e. behaviour through method/functions

In its role as a blueprint, the class specifies what an actual object will look like. But it is not an object. Objects are actually instances of classes. You can think of a class as a cookie cutter and an instance as an actual cookie. Similarly, you can think of a class as a blueprint of a house and an instance as an actual house.

Class as Basis of all Computation 

In Java, the class forms, the basis of all computation. Anything that has to exist as a part of a Java program has to exist as a part of class, whether that is a variable or a function or any other code-fragment. Unlike other OOP languages such as C++ that allows the existence of variables and functions outside any class. The reason being that Java is a pure Object Oriented Language. Here all functionality revolves around classes and object, as in real world. Therefore, if you want to use certain variable and functions in Java, you have to make them part of a class. ALL JAVA programs consist of objects (data and behaviour) that “interact” with each other by calling methods. All data is stored within objects which are instances of a class.

See, without classes can be no objects and without objects, no computation can take place in Java. Thus, classes form the basis of all computation in Java.

Defining Classes

A Java program consists of objects, from various classes, interacting with one another. Before we go into the details of how you can define classes, let’s review some of the general properties of classes. A value of class type is called an object. An object is usually referred to as an instance of the class rather than as a value of the class, but it is a value of the class type. An object is a value of the class type much like a value, such as 5, of a primitive type, like int, is a value of a variable of that type. 

However, an object typically has multiple pieces of data and has methods (action) it can take. Each object can have different data but all objects of the class have the same types of data and all objects in a class have the same methods. We generally say that data and methods belong to the object, and that is an acceptable point of view. The data certainly does belong to the object, but since all objects in a class have the same methods, it also would be correct to say that the methods actually belong to the class.

In this section we are going to learn about how to define classes in Java. Now consider the code fragment shown below that defines a class called City.

Public class City {
String name ;    // variable name will be name of the city
Long population ;    // will hold City’s population
Void display( )    {
Label1.setText(“city name :” + name) ;
Label2.setText(“population :” + population) ;
}
}
Let us examine this code line by line. The firstline Public class City Defines the name of the class which is City. The keyword class ensures that it is a class and the keyword public means it is available in entire program. When you add a top-level container frame in your GUI application, then a public class having the frame name is created in your application. In other words, the top-level frame is represented through a public class.

The brace following the public Class City
    { marks the beginning of class’ block.

The next two lines
    String name;
    Long population; declares the data members of the class to define its characteristics.

Each of these lines declares one instance variable name. You can think of and object of the class as a complex item with instance variables inside of it. So, you can think of an instance variable as a smaller variable inside each object of the class. In this case, the instance variables are called name and population.

The copy of instance variables is created for each object of the class. The next four lines
    void display ( )
    {
        Label1.setText(“City name :” + name);
        Label2.setText(“population :” + population);
    }

Define a method of the class which defines the behaviour of the class. The name of the method is display( ). By looking at its code, you can easily make out what its functionality is like. The last line
    }
marks the end of the class’ block.

Null Statement and Character Manipulation in JAVA

In Java programs, statements are terminated with a semicolon (;). The simplest statement of them all is the empty, or null statement.
; it is a null statement

A null statement is useful in those instances where the syntax of the language requires the presence of a statement but where the logic of the program does not. We will see it in loops and their bodies.

Character Manipulation

Consider the following code. What does it print?

        :
System.out.print(“H” + “a”);
System.out.print(‘H’ + ‘a’);
        :
Aren’t you thinking that it would produce output as:    HaHa
But if you the program, you’ll find that the output produced is:    Ha169

As expected, the first call to System.out.print prints Ha. Its argument is the expression “H” + “a”, which performs the obvious string concatenation.

The second call to system.out.print is another story. Its argument is the expression ‘H’ + ‘a’. The problem is that ‘H’ and ‘a’ are char literals. Because neither operand is of type string, the + operator performs addition rather than string concatenation. Thus it adds ‘H”s value i.e. 72 and ‘a”s value I.e. 97 and gives 169. (A-Z have values 65-90; a-z have values 97-122).

You can force the + operator to perform string concatenation rather than addition by ensuring that at least one of its operands is a string. The common idiom is to begin a sequence of concatenations with the empty string (“ “), as follows:
    System.out.print(“ “ + ‘H’ + ‘a’) ;

But this approach can lead to some confusions also. Can you guess what the following statement prints?
    System.out.println(“2 + 2 = “ + 2 + 2);
---------------------------------------------------

Yes, you are right. It produces:    2 + 2 = 22

To perform the addition of expression 2+2 shown above, you need to convert it to Expression by enclosing it in parenthesis i.e. as
(2+2): System.Out.Println(“2+2 = ”+ (2+2));

The + operator performs string concatenation if and only If at least one of its operands is of type string: otherwise, it performs addition with primitive types.

Type of Statements used in JAVA

Statements are roughly equivalent to sentences in natural languages. A statement forms a complete unit of execution. The following types of expressions can be made into a statement by terminating the expression with a semicolon ( ; )

• Assignment expressions
• Any use of ++ or - -
• Method calls
• Object creation expressions

These kinds of statement are called expression statement. Here are some example of expression statements :

Avalue = 8933.234;                                          // assignment statement
Avalue++ ;                                                        // increment statement
System . out . println (avalue);                         // method call statement
Integer integerobject = new integer (4);           // object creation statement

In addition to these kinds of expression statements, there are two other kinds of statements. A declaration statement declares a variable. You’ve seen many examples of declaration statement.
Double avalue = 8933.234;            // declaration statement

A control flow statement regulates the order in which statements get executed. For loop and  if statements are both examples of control flow statements.

Block

A block is group of zero or more statements between balanced braces and can be used anywhere a single statement is allowed. The following listing shows two blocks.

If  (character . I suppercase(achar))
{
    Labe l1 . settext (“the character” + achar + “is upper case .”) ;
}
Else
{
    Labe l1. Settext (“the character” + achar + “is lower case .”) ;
    Label2. Settext(“thank you”) ;
}

Character.isLowerCase( )    tests whether a character is in lowercase.
Character.isUpperCase( )        tests whether a character is in uppercase.
Character.toLowerCase( )        converts the case of a character to lowercase
Character.toUpperCase( )        converts the case of a character to uppercase

First Block:

 If (character.isUpperCase(achar))
{            // block1 begins
    Label1.Settext (“ The character “ + achar + “ is upper case.”) ;
}            // end of block 1
Else
 

Another Block:
{            // block2 begins
Label1.Settext(“ The character “ + achar + “ is lower case . “) ;
}            // end of block2

See, the beginning and end of blocks have been marked.

A Block is a group of zero or more statements between balanced braces and can be used anywhere a single statement is allowed.

In this book, we shall be following conventional style where opening brace of the block is not put in a separate line, rather it is placed in continuation with the previous statement (whose part the block is).

For instance, rather than showing
If (a > b) { : }

We shall be writing
If (a > b)   {     :  }
Opening brace of the block in continuation with previous statement