Control Statements in SQL Server  :

Conditional Statements in SQL Server  :-

(A)

if <cond>
              Begin
                                Statements
              End.

Note  :-  If more than one statements is executed begin & end is required ,else optional.

(B)

if <cond>
               Begin
                                Statements
                End
Else
               Begin
                                Statements
                End.

(C)

if <cond>
              Begin
                                    Statements
             End
Else if <cond>
              Begin
                                      Statements
                End
Else
                Begin
                                   Statements
                 End.

(D)

if <cond>
                Begin
If <cond>
                Begin
                                 Statements
                End
Else
                 Begin
                                    Statements
                 End
Else
                  Begin
                                  Statements
                  End.

Loops in SQL Server :-

While    <condition>
Begin
Statements
End
Write a program to input an empno & check the employee commission,if the commission is null update the commission to rs 500 otherwise increment the commission by 200.

Declare @eno int

Declare  @comm smallmoney

Set @eno=7566

Select @comm =comm from emp

Where empno=@eno

If @ comm is null

Update emp set comm =500 where empno=@eno

Else

Update emp set comm=comm+200 where empno=@eno.

Input an empno update the employee salary based on the job of employee

Clerk   10%

Salesman 15%

Manager   20%

Others       5%

Answer : Declare @eno int,@job varchar

Set @eno=7566

Select @job=job from emp where empno= @eno

If @ job=’clerk’

Update emp set sal=sal*1.1 where empno=@eno

Else if @job=’salesman’

Update emp set sal=sal*1.15 where empno=@eno

Else if @job =’manager’

Update  emp set sal =sal*1.2 where empno=@eno

Else

Update emp set sal =sal *1.05 where empno =@eno

ACCT-MAST Table :-

Accno          Acname         Bal

1                    A               5000

2                    B                 6000

ACCT-TRANS  Table  :-

TRID        TDATE       TAMT       TTYPE        ACCNO

1000            10               1000            D                 1

1001            10               2000             W               2

Write a program  to do bank transaction :    

Declare    @acno     int,@bal    money

Declare     @ttype     char(1),     @tamt money

Set @acno=1

Set @ttype=’w’

Set  @tamt =1000

Select @bal =bal from acct-mast where accno=@acno

If @ttype=’w’ and  @tamt>@bal

Print  ‘insufficient balance’

Else if @ttype=’w’ and @tamt<@bal

Begin

Update acc-mast set bal=bal-@tamt

Where accno=@acno

Insert into acct-trans(type,tdate,tamt,accno)

Else if @ ttype =’D’

Begin

Update acct-mast set bal =bal@tamt

Where  accno=@acno.

Insert into acct-trans(ttype,tdate,tamt,accno)

Values(‘D’,getdate(),@tamt,@acno)

End

Else

Print ‘invalid transaction type’

Lib_mast :   

Bno          btitle      bauthor         issued

1               dbms      xyz                no

2                java       abc                yes

Lib_trans :

Bno      doi       dod(date of due)

1           11/11      21/11

Write a program to process library transaction:

  • Create table lib_mast (bno varchar(20), btitle  varchar (20) bauthor varchar(20),issued char (1))
  • Create table lib_trans(bno varchar(20),doi date time ,dod date time)
  • Insert into lib_mast values(1,’dbms’,’abc’,’n’)

Program :

Declare @dno int,@iss char (1)

Declare @dod date time

Set @bno=1

Select @iss=issued from lib_mast

Where bno=@bno.

If @iss=’y’

Print ‘book alredy issued’

Else

Begin

Set @dod=dateadd(dd,10,getdata())

Insert into lib_trans values (@bno,getdate(),@dod)

Update lib_mast set issued =’y’

Where bno=@bno