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