Notes

Dim a, b As Integer//Declaration of variablesDimcAsString
Private Sub Command1_Click()//Code for the conversion buttona = Val(Text1.Text)//Gettinginputvalue
c = ""

Do While (a > 0)//Code for binary conversionb=aMod2
If b = 0 Thenc ="0" + c
Else

c ="1"+c

End If

a = Int(a / 2)LoopText2.Text=c
a= Val(Text1.Text)

Text3.Text = Oct(a)//Octal conversionText4.Text = Hex(a)//Hexadecimal conversionEndSub
Private Sub Command2_Click()//Code for clear buttonText1.Text=""
Text2.Text = ""Text3.Text = ""Text4.Text = ""EndSub

Private Sub Command_click()//Code for End buttonEnd
End Sub


2.
PrivateSubCommand1_Click()

List1.AddItem (Text1.Text) // add the item to the list boxText1.Text=Empty//ToclearthetextboxText1.SetFocus //To make the cursor placed in the text boxEndSub

PrivateSubCommand2_Click()

Combo1.AddItem(List1.Text)//tomovetheselecteditemfromthelistboxtothecomboboxEndSub

3.
Dim a As Single//Variable declarationDimopAs String

PrivateSubForm_Load()//Toclearthevaluesop=""
txtNumber.Text = ""EndSub

PrivateSubcmdNumber_Click(IndexAsInteger)//ToselectthenumbertxtNumber.Text=txtNumber.Text+cmdNumber(Index).Caption
End Sub

Private Sub cmdOperator_Click(Index As Integer) //To select theoperatora =Val(txtNumber.Text)

txtNumber.Text = ""SelectCaseIndex
Case0
op="+"
Case1
op="-"
Case2
op="*"
Case3
op = "/"Case 4
Op=“%”
End SelectEndSub

Private Sub cmdDot_Click() //To give the float valuestxtNumber.Text=txtNumber.Text+"."
End Sub

PrivateSubcmdEqual_Click()//ToproducetheresultSelectCaseop
Case"+"
a = a + Val(txtNumber.Text)Case "-"
a=a-Val(txtNumber.Text)Case "*"
a = a * Val(txtNumber.Text)Case "/"
a=a/Val(txtNumber.Text)Case “%”
a=a%Val(txtNumber.Text)
End SelecttxtNumber.Text=a
End Sub


Private Sub cmdClear_Click() //To clear the valuestxtNumber.Text=""
op = ""End Sub

Private Sub cmdOn_Click() //To give the on optiontxtNumber.Enabled= True
End Sub

Private Sub cmdEnd_Click() //To end the formUnloadMe
End Sub
4.
PrivateSubcmdSave_Click()
OnErrorResumeNext
cdl.Filter=“TextFiles(*.txt)|*.txt|AllFiles(*.*) |(*.*)cdl.showSave
If Err then Exit SubRTBox.SaveFilecdl.Filename
End Sub


PrivateSubcmdOpen_Click()
OnErrorResumeNext
cdl.Filter=“TextFiles(*.txt)|*.txt|AllFiles(*.*) |(*.*)cdl.showOpen
If Err then Exit SubRTBox.LoadFilecdl.Filename
End Sub


Private Sub cmdFont_Click()cdl.Flags = cdlCFScreenFontscdl.showFont
RTBox.SelFontName = cdl.FontNameRTBox.SelBold = cdl.FontBoldRTBox.SelItalic = cdl.FontItalicRTBox.SelFontSize = cdl.FontSizeRTBox.SelStrikethru=cdl.FontStrikethruRTBox.SelUnderline=cdl.FontUnderline
End Sub


PrivateSubcmdClear_Click()RTBox.Text= ““
End Sub

Private Sub cmdExit_Click()UnloadMe
End Sub

5.
PROGRAM
Private Sub mnuaddform_Click()LoadForm1
Load Form2Load Form3Load Form4Load Form5Load Form6Form1.ShowForm2.ShowForm3.ShowForm4.ShowForm5.ShowForm6.ShowEndSub

Private Sub mnublue_Click()MDIForm1.ActiveForm.BackColor=vbBlueEndSub

Private Sub mnucascade_Click()MDIForm1.Arrange(vbCascade)EndSub

Private Sub mnuexit_Click()UnloadForm1
Unload Form2Unload Form3Unload Form4Unload Form5Unload Form6Unload MDIForm1EndSub

Private Sub mnugreen_Click()MDIForm1.ActiveForm.BackColor = vbGreenEndSub

Private Sub mnuhorizantal_Click()MDIForm1.Arrange(vbHorizontal)EndSub

Private Sub mnured_Click()MDIForm1.ActiveForm.BackColor=vbRedEndSub

Private Sub mnuvertical_Click()MDIForm1.Arrange(vbVertical)

6.Dim cn As New ADODB.ConnectionDim rs As New ADODB.RecordsetDimstr,qryAs String

Private Sub cmdAdd_Click()txtrollno.Text= ""txtname.Text= ""txtdept.Text=""txtclass.Text= ""
txtper.Text= ""txtrollno.SetFocusEndSub

PrivateSubcmdDelete_Click()
DimrnoAsString
rno=InputBox("Entertherollnotodeletetherecord")
qry="deletefromstud_detwhererollno='"&Trim(UCase(rno))&"'"cn.Executeqry
MsgBox "record deleted"EndSub

PrivateSubcmdFirst_Click()rs.MoveFirst
MoveFieldsEndSub

Private Sub cmdLast_Click()rs.MoveLast
MoveFieldsEndSub

Private Sub cmdNext_Click()rs.MoveNext
If rs.EOF Thenrs.MoveLast
EndIfMoveFieldsEndSub

Private Sub cmdPrevious_Click()rs.MovePrevious
If rs.BOF Thenrs.MoveFirst
EndIfMoveFieldsEndSub

PrivateSubcmdSave_Click()
DimqAsString
q="insertintostud_detvalues('"&Trim(UCase(txtrollno.Text))&"','"&Trim(UCase(txtname.Text)) & "','" & Trim(UCase(txtdept.Text)) & "','" &Trim(UCase(txtclass.Text))&"',"&Val(txtper.Text)&")"
MsgBox qcn.Executeq
MsgBox "record saved"EndSub

PrivateSubForm_Load()
str= "select*fromstud_det"
Setcn=NewADODB.Connection
cn.Open "Provider=msdaora;Data Source=cas; User Id=cs3b15; Password=cs3b15"rs.Openstr,cn,adOpenKeyset,adLockOptimistic,adCmdText
MoveFieldsEndSub

Private Sub MoveFields()txtrollno.Text = rs.Fields("rollno")txtname.Text = rs.Fields("name")txtdept.Text = rs.Fields("dept")txtclass.Text=rs.Fields("class")txtper.Text=rs.Fields("percentage")EndSub

Orcal lab program
PROGRAM1


EMPLOYEEDATABASE



AIM:


To create a table for Employee details with Employee number as primary key followingFields:Name,Designation,Gender, Age,DateofjoiningandSalary.ToperformvariousqueriesUsinganyone Comparison,Logical,Set,SortingandGroupingoperators.

ALGORITHM:

Step 1: Open Oracle using sql plus with username and password. Sql prompt window willAppear.
Step2:Createanemployeetablewiththefollowingfields:Employeenumber, Name,Designation,Gender,Age,DateofjoiningandSalary.
Step3:EmployeetableiscreatedwithEmployeenumber asprimarykey.Step4:Performcomparisonoperationusing<,<=,>,>=operators.
Step5: PerformlogicaloperationusingAND,OR&NOToperators.

Step 6: Perform set operation using UNION,UNION ALL & INTERSECT operators.Step 7: Perform Sorting and Grouping operation using MIN,MAX& SUM finctions.Step6:Executethequeries.



CREATEATABLEFOREMPLOYEEDETAILS:

createtableemployee(empnonumberprimarykey,empnamevarchar2(20),
designation varchar2(30),gendervarchar2(6),
age number,dojdate,
salarynumber);


Tablecreated.

DESCRIBEATABLE:

SQL>descemployee;

Name Null? Type



EMPNO NOTNULL NUMBEREMPNAME VARCHAR2(20)
DESIGNATION VARCHAR2(30)

GENDER VARCHAR2(6)

AGE NUMBER

DOJ DATE

SALARY NUMBER

INSERTAVALUESFORAEMPLOYEETABLE:

SQL>insertintoemployeevalues(&empno,'&empname','&designation','&gender',&age,'&doj',&salary);


SELECTALLTHEROWSFROMTHEEMPLOYEETABLE:

Sql>select*fromemployee;





EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101
arjun
manager
male
30
12-JAN-14
35000

103
manoj
clerk
male
26
28-MAR-15
28000

102
john
hr
male
32
17-APR-15
30000

105
peter
marketingmanager
male
35
01-FEB-15
35000

104
pooja
projectdeveloper
female
27
14-APR-15
25000

107
aishu
tester
female
25
20-JUL-15
24000

108
yamuna
clerk
female
30
11-JAN-15
31000


7rowsselected.

COMPARISON:
SQL>select*fromemployeewheresalary>30000;


EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY


-


101

arjun

manager
male

30
12-JAN-14
35000

105

peter

marketingmanager
male

35
01-FEB-15
35000

108

yamuna

clerk
female

30
11-JAN-15
31000




SQL>select* fromemployeewhereagebetween 25 and30;


EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY


-

101 arjun manager male 30 12-JAN-14 35000

103
manoj
clerk
male
26
28-MAR-15
28000

104
pooja
projectdeveloper
female
27
14-APR-15
25000

107
aishu
tester
female
25
20-JUL-15
24000

108
yamuna
clerk
female
30
11-JAN-15
31000




SQL>select *fromemployeewhereempnamelike'%a';


EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



104 pooja projectdeveloper female 27 14-APR-15 25000

108 yamuna clerk female 30 11-JAN-15 31000


SQL>select *fromemployeewheresalary in(35000,30000,28000);


EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101
arjun
manager
male
30
12-JAN-14
35000

103
manoj
clerk
male
26
28-MAR-15
28000

102
john
hr
male
32
17-APR-15
30000

105
peter
marketingmanager
male
35
01-FEB-15
35000




SQL>select*fromemployeewhereempno=103;


EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



103 manoj clerk male 26 28-MAR-15 28000



LOGICAL:

SQL>select*fromemployeewheresalary<50000andsalary>30000;
EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101
arjun
manager
male
30
12-JAN-14
35000

105
peter
marketingmanager
male
35
01-FEB-15
35000

108
yamuna
clerk
female
30
11-JAN-15
31000




SQL>select *fromemployeewheredesignation='manager'ordesignation='admin';


EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101 arjun manager male 30 12-JAN-14 35000


SQL>select *fromemployeewherenotsalary<30000;


EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101
arjun
manager
male
30
12-JAN-14
35000

102
john
hr
male
32
17-APR-15
30000

105
peter
marketingmanager
male
35
01-FEB-15
35000

108
yamuna
clerk
female
30
11-JAN-15
31000


4rowsselected.



SORTING:

SQL>select* fromemployeeorderbyempno;
EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101
arjun
manager
male
30
12-JAN-14
35000

102
john
hr
male
32
17-APR-15
30000

103
manoj
clerk
male
26
28-MAR-15
28000

104
pooja
projectdeveloper
female
27
14-APR-15
25000

105
peter
marketingmanager
male
35
01-FEB-15
35000

107
aishu
tester
female
25
20-JUL-15
24000

108
yamuna
clerk
female
30
11-JAN-15
31000


7rowsselected.

SQL>select* fromemployeeorderby empno desc;
EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



108
yamuna
clerk
female
30
11-JAN-15
31000

107
aishu
tester
female
25
20-JUL-15
24000

105
peter
marketing manager
male
35
01-FEB-15
35000

104
pooja
projectdeveloper
female
27
14-APR-15
25000

103
manoj
clerk
male
26
28-MAR-15
28000

102
john
hr
male
32
17-APR-15
30000

101
arjun
manager
male
30
12-JAN-14
35000


7rowsselected.





SETOPERATION:

SQL> select * from employee where salary>30000 union select * from employee whereage between25and30;
EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101
arjun
manager
male
30
12-JAN-14
35000

103
manoj
clerk
male
26
28-MAR-15
28000

104
pooja
projectdeveloper
female
27
14-APR-15
25000

105
peter
marketingmanager
male
35
01-FEB-15
35000

107
aishu
tester
female
25
20-JUL-15
24000

108
yamuna
clerk
female
30
11-JAN-15
31000


6rowsselected.


SQL>select *fromemployeewheresalary>30000unionallselect*fromemployeewhere agebetween25and30;


EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101
arjun
manager
male
30
12-JAN-14
35000


105

peter

marketingmanager

male

35

01-FEB-15

35000

108
yamuna
clerk
female
30
11-JAN-15
31000

101
arjun
manager
male
30
12-JAN-14
35000

103
manoj
clerk
male
26
28-MAR-15
28000

104
pooja
project developer
female
27
14-APR-15
25000

107
aishu
tester
female
25
20-JUL-15
24000

108
yamuna
clerk
female
30
11-JAN-15
31000


8rowsselected.


SQL> select * from employee where salary>30000 intersectselect * from employeewhere agebetween25and30;
EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



101 arjun manager male 30 12-JAN-14 35000

108 yamuna clerk female 30 11-JAN-15 31000


SQL> select * from employee where salary>30000 minus select * from employee whereage between25and30;
EMPNOEMPNAME DESIGNATION GENDER AGE DOJ SALARY



105 peter marketingmanager male 35 01-FEB-15 35000


GROUPFUNCTION;

SQL>selectcount(*)fromemployeewheregender='female';
COUNT(*)



3


SQL>selectsum(salary)fromemployee;
SUM(SALARY)



208000


SQL>selectavg(salary)fromemployee;
AVG(SALARY)




29714.2857


SQL>selectmax(salary)fromemployee;
MAX(SALARY)



35000

SQL>selectmin(salary)fromemployee;
MIN(SALARY)



24000



PROGRAM2






AIM:
LIBRARYMANAGEMENT


WriteaqueryforLibraryManagementsystem.


ALGORITHM:

Step1:Create aLibraryTable

Step2:Create aLibraryTransactionTableStep3:Insertvaluesinto twotables
Step 4: Format the Library TableStep5:Stopthe process




















-



CREATEALIBRARYMASTERTABLE:

SQL> create table library_master(accno number primary key,title varchar2(25),author varchar2(25),2ratenumber);


Tablecreated.

CREATEALIBRARYTRANSACTIONTABLE:

SQL>createtablelibrary_transaction(useridnumber,accnonumberreferenceslibrary_master(accno),2date_of_issuedate,
3date_of_returndate);


Tablecreated.

INSERTVALUESINTOLIBRARY_MASTERTABLE:

SQL> insert into library_master values(&accno,'&title','&author',&rate);Entervalueforaccno:1001
Enter value for title: c programmingEnter value for author:balaguruswamyEntervalueforrate:300
old1:insertintolibrary_mastervalues(&accno,'&title','&author',&rate)

new1:insertintolibrary_mastervalues(1001,'cprogramming','balaguruswamy',300)


1rowcreated.SQL>/
Entervalueforaccno:1002

Enter value for title: c++ programmingEntervalueforauthor:balaguru
Entervalueforrate:350

old1:insertintolibrary_mastervalues(&accno,'&title','&author',&rate)

new1:insert intolibrary_mastervalues(1002,'c++programming','balaguru',350)



1rowcreated.SQL>/
Entervalueforaccno:1004

Enter value for title: data structureEnter value for author: michaelEntervalueforrate:400
old 1: insert into library_master values(&accno,'&title','&author',&rate)new1:insertintolibrary_master values(1004,'data structure','michael',400)

1rowcreated.SQL>/
Entervalueforaccno:1005

Enter value for title: oracle & RDBMSEntervalueforauthor:johnpeterEntervalueforrate:320
old1:insertintolibrary_mastervalues(&accno,'&title','&author',&rate)

new1:insertintolibrary_mastervalues(1005,'oracle& RDBMS','johnpeter',320)


1rowcreated.


SELECTALLROWSFROM LIBRARY_MASTER:

SQL>select*fromlibrary_master;

ACCNOTITLE AUTHOR RATE

-

1001
cprogramming
balaguruswamy
300

1002
c++programming
balaguru
350

1004
datastructure
michael
400

1005
oracle&RDBMS
johnpeter
320




INSERTVALUESINTOLIBRARY_TRANSACTIONTABLE:

SQL>insertintolibrary_transactionvalues(&userid,&accno,'&date_of_issue','&date_of_return');Entervalueforuserid:01
Entervalueforaccno:1002

Enter value for date_of_issue: 12-jun-16Enter valuefor date_of_return:13-jul-16
old1:insertintolibrary_transactionvalues(&userid,&accno,'&date_of_issue','&date_of_return')new1:insertintolibrary_transactionvalues(01,1002,'12-jun-16','13-jul-16')

1rowcreated.SQL>/
Enter value for userid: 02Entervalueforaccno:1005
Enter value for date_of_issue: 25-jul-16Enter valuefordate_of_return:24-aug-16
old1: insert into library_transaction values(&userid,&accno,'&date_of_issue','&date_of_return')new1:insertintolibrary_transactionvalues(02,1005,'25-jul-16','24-aug-16')

1rowcreated.SQL>/
Enter value for userid: 03Entervalueforaccno:1004
Enter value for date_of_issue: 01-jun-16Entervaluefordate_of_return:30-jun-16
old1:insertintolibrary_transactionvalues(&userid,&accno,'&date_of_issue','&date_of_return')new1:insertintolibrary_transactionvalues(03,1004,'01-jun-16','30-jun-16')

1rowcreated.



SELECTALLROWSFROMLIBRARY_TRANSACTION:

SQL>select*fromlibrary_transaction;


USERIDACCNODATE_OF_IDATE_OF_R

- - -

1
1002
12-JUN-16
13-JUL-16

2
1005
25-JUL-16
24-AUG-16

3
1004
01-JUN-16
30-JUN-16





FORMATINGTHELIBRARYMANAGEMENTSYSTEM

SQL>setlinesize75

SQL>TTitlecenter'LIBRARYMANAGEMENT' skip1-

>center******************

SQL>column accno heading 'ACCOUNT NUMBER'SQL>columntitleheading'BOOK_NAME'
SQL>column date_of_issue heading 'DATE_OF_ISSUE'SQL>column date_of_retrun heading 'DATE_OF_RETURN'SQL>setunderline-
SQL>breakonrowskip1

SQL>select m.accno,m.title,t.date_of_issue from library_master m,library_transaction t wherem.accno=t.accnoanddate_of_return='24-aug-16';


LIBRARYMANAGEMENT

**********************

ACCOUNTNUMBERBOOK_NAME DATE_OF_ISSUE

-

1005oracle&RDBMS 25-JUL-16


PROGRAM3
INVENTORYMANAGEMENT



AIM

To write aPL/SQLtoupdatetheratefieldby20%morethanthe currentrateinInventorytablewhich has the following fields: Prono, ProName and Rate. After updating the Table a new field(Alter) called for Number of item and place for values for the new field without using PL/SQLblock.

ALGORITHM:

Step1:Logininto SQLplus.

Step2:Createthetablecalledinventorywithprono,proname,rateandinsertthevalues.Step3:Insert5valuesintothetable.
Step4: Update the table to add new items and places.Step5: Update the column rate with (rate+(rate*0.2).Step6:StoptheProcess.






CREATEATABLEASINVENTORY:
SQL>createtable inventory(prononumberprimarykey,2pronamevarchar(20),
3ratenumber);


Tablecreated.


INSERTVALUESINTOINVENTORYTABLE:

SQL> insert into inventory values(&prono,'&proname',&rate);Entervalueforprono:1001
Enter value for proname: soapEntervalueforrate:30
old1: insert into inventory values(&prono,'&proname',&rate)new1: insertintoinventoryvalues(1001,'soap',30)

1rowcreated.

SQL>/

Entervalueforprono:1002Enter value for proname: horlicksEntervalueforrate:100
old1: insert into inventory values(&prono,'&proname',&rate)new1: insertinto inventoryvalues(1002,'horlicks',100)

1rowcreated.

SQL>/

Entervaluefor prono:1003

Entervalueforproname:milkybarEntervalueforrate:50
old1: insert into inventory values(&prono,'&proname',&rate)new1: insertinto inventoryvalues(1003,'milkybar',50)

1rowcreated.

SQL>/

Entervaluefor prono:1004

Enter value for proname: chocobarEntervalueforrate:40
old1: insert into inventory values(&prono,'&proname',&rate)new1: insertinto inventoryvalues(1004,'chocobar',40)

1row created.


SELECTALLTHEROWSININVENTORYTABLE:

SQL>select*frominventory;


PRONOPRONAME RATE



1001soap
30

1002horlicks
100

1003milkybar
50

1004chocobar
40




UPDATETHECOLUMNASRATE:
SQL>get inventory_update.sql

begin

update inventory set rate=rate+(rate*20/100);3commit;
4* end;SQL>/
PL/SQLproceduresuccessfullycompleted.


SELECTALLTHEROWSININVENTORYTABLE:

SQL>select*frominventory;

PRONOPRONAME
RATE


1001soap

36

1002horlicks
120

1003milkybar
60

1004chocobar
48



ADDINGANEWCOLUMNASNO_OF_ITEMSININVENTORYTABLE:

SQL>altertableinventoryaddno_of_itemsnumber;


Tablealtered.


DESCRIBEAINVENTORYTABLE:

SQL>descinventory;Name


Null?

Type


PRONO
PRONAMERATE


NOT

NULLNUMBER
VARCHAR2(20)NUMBER


NO_OF_ITEMS NUMBER


UPDATEANEXISTINGCOLUMNSININVENTORYTABLE:

SQL>updateinventorysetno_of_items=caseprono2when1001then10
when1002then8

when1003then6

when1004then4

end;


4rowsupdated.

SELECTALLTHEROWSININVENTORYTABLE:

SQL>select*frominventory;


PRONOPRONAME RATENO_OF_ITEMS



1001soap
36
10

1002horlicks
120
8

1003milkybar
60
6

1004chocobar
48
4







PROGRAM4


AIM


STUDENTDATABASE

TowriteaPL/SQLtosplitthestudenttableintotwotables basedonresult(Onetablefor

―Pass‖andanotherfor―Fail‖). Usecursorforhandlingrecordsofstudenttable. AssumeNecessaryfields andcreateastudentdetails table.

ALGORITHM:

Step1: OpenOracleusing sqlpluswithusernameand password.SqlpromptwindowwillAppear.
Step2:Createastudenttablewiththefollowingfields:rollno, Name,Totalmarks,result.
Step 3: Create two more tables one is for holding the pass students data and another for failedStudentsdetails.
Step4:WritecodeinPL/SQL for creatingacursor.

Step5:InPL/SQLwritecodefor splittingthestudentsdatabasedontheir resulttopasstableand

Failtable.

Step6:Executetheprocedure.





CREATEASTUDENTTABLE:
SQL> create table student31(rollno number,name varchar2(20),2totalmarksnumber,resultvarchar2(4));
Tablecreated.


INSERTRECORDSTOSTUDENTTABLE:

SQL> insert into student31 values(&rollno,'&name',&total,'&result');Entervalueforrollno:101
Entervalueforname:nishaEnter value for total: 320Entervalueforresult:pass
old1:insert intostudent31values(&rollno,'&name',&total,'&result')new1: insertinto student31values(101,'nisha',320,'pass')

1 row created.SQL> /
Enter value for rollno: 102Enter value for name: paviEnter value for total: 400Entervalueforresult:pass
old1:insert intostudent31values(&rollno,'&name',&total,'&result')new1: insertinto student31values(1002,'pavi',400,'pass')

1rowcreated.

SQL>/

Entervalueforrollno:103

Enter value for name: ragulEnter value for total: 200Entervalueforresult:fail
old1:insert intostudent31values(&rollno,'&name',&total,'&result')new1: insertintostudent31 values(103,'ragul',200,'fail')

1rowcreated.

SQL>/

Entervaluefor rollno:104Enter value for name: ashwinEnter valuefortotal: 100Entervaluefor result:fail
old1:insert intostudent31values(&rollno,'&name',&total,'&result')new1: insertinto student31 values(104,'ashwin',100,'fail')

1 row createdSQL> /
Entervaluefor rollno:105Entervalueforname:neethuEnter valuefortotal: 300Entervalueforresult:pass
old1: insert into student31 values(&rollno,'&name',&total,'&result')new1: insertinto student31values(105,'neethu',300,'pass')

1rowcreated.

SQL>/

Enter valuefor rollno: 106Entervalueforname:carolyn

Enter value for total: 360Entervalueforresult:pass
old1: insert into student31 values(&rollno,'&name',&total,'&result')new1: insertinto student31 values(106,'carolyn',360,'pass')
1rowcreated.
SQL>/

Enter value for rollno: 107Enter value for name: kayalEnter value for total: 220Entervalueforresult:fail
old1: insert into student31 values(&rollno,'&name',&total,'&result')new1: insertintostudent31 values(107,'kayal',220,'fail')

1 row created.SQL> /
Entervalueforrollno:108Entervalueforname:prabhaEnter valuefortotal: 240Entervaluefor result:pass
old1:insertintostudent31values(&rollno,'&name',&total,'&result')new1: insertintostudent31 values(108,'prabha',240,'pass')

1row created.

SELECTALLROWSFROMSTUDENTTABLE:

SQL>select* fromstudent31;


ROLLNONAME TOTALMARKSRESU



101
nisha
320
pass

102
pavi
400
pass

103
ragul
200
fail

104
ashwin
100
fail

105
neethu
300
pass

106
carolyn
360
pass

107
kayal
220
fail

108
prabha
240
pass




8rowsselected.


SPLITTHESTUDENTTABLEINTO2TABLEBASEDONRESULT:

SQL>declare

2query1varchar2(100):='createtablestudent31_passas(select*fromstudent31where3result=''pass'')';
4query2varchar2(100):='createtablestudent31_failas(select*fromstudent31where5result=''fail'')';
begin

execute immediate query1;8 execute immediate query2;9end;
10/


PL/SQL procedure successfully completed.SQL> select*fromstudent31_pass;
ROLLNONAME TOTALMARKSRESU



101 nisha 320 pass

102
pavi
400
pass

105
neethu
300
pass

106
carolyn
360
pass

108
prabha
240
pass






SQL>select*fromstudent31_fail;

ROLLNONAME TOTALMARKSRESU



103
ragul
200
fail

104
ashwin
100
fail

107
kayal
220
fail




USECURSORFORHANDLINGRECORDS:

SQL>declare

2cursorcpassisselect*fromstudent31_pass;3cursorcfail isselect* fromstudent31_fail;
begin

dbms_output.put_line('LISTOFPASSEDSTUDENTS');6forpassrec incpassloop
dbms_output.put_line(passrec.name);

endloop;

dbms_output.put_line('LISTOFFAILEDSTUDENTS');10forfailrecincfailloop
dbms_output.put_line(failrec.name);

endloop;

end;

14/



LISTOFPASSEDSTUDENTS

nishapavineethucarolynprabha
LIST OFFAILEDSTUDENTS

ragulashwinkayal
PL/SQLproceduresuccessfullycompleted.




PROGRAM5
TRIGGERFORINVENTORYMANAGEMENTSYSTEM

AIM:

Tocreateadatabasetriggertoimplementonmaster andtransactiontableswhichareBased oninventorymanagementsystemforcheckingdata validity.

ALGORITHM:

STEP-1: Open the Oracle application by giving the username and the password.STEP-2:Createtablebookmasterwiththefieldsbid, bknameandNoofitem.
STEP-3:InsertthevaluestobookmastertableandselectallrowsfromthetableByusingselect*fromtablecommand.
STEP-4:Create another table booktransaction with the fields tid, bid and No ofItems.
STEP-5:InsertthevaluestobooktransactiontableandselectalltherowsfromtheTablebyusingselect*followedbythe tablename command.
STEP-6:Settheserver outputon.

STEP-7:Createthetriggerwiththenamecheck1.STEP-8:Executethetrigger.
STEP-9:Stopthe process.





CREATEINVENTORYMASTERTABLE:-
create table inventory_master1(orderid number primary key,custid number,orderdatedate,amountnumber);


Tablecreated

CREATEINVENTORYTRANSACTIONTABLE:-

createtableinventory_trans1(orderidnumberreferencesinventory_master1,productidnumber,productnamevarchar2(30),quantitynumber,unitprice number);

Tablecreated

CREATETRIGGERONINVENTORYMASTER:-
setserveroutputoncreateorreplacetrigger
check1beforeinsertoninventory_master1for eachrow
declarebegin
if(:new.amount<0)then
raise_application_error(-20040,'invalid amount');endif;
end;

Triggercreated.

INSERTINTOINVENTORYTABLES:-
insertintoinventory_master1values(&orderid,&custid,'&orderdate',&amount);

CREATETRIGGERON INVENTORYTRANSACTION:-
createor replacetrigger
check2 before insert on inventory_trans1 for each rowdeclare
beginif(:new.quantity<0)then
raise_application_error(-20040,'invalid quantity');endif;
end;

Triggercreated.



INSERTINTOINVENTORYTRANSACTION:-

insertintoinventory_trans1values(&orderid,&productid,'&productname',&quantity,&unitprice);
SQL>insertintoinventory_trans1values(&orderid,&productid,'&productname',&quantity,&unitprice);Entervaluefororderid:5001
Entervalueforproductid:88
Enter value for productname: TelevisionEntervalueforquantity:10
Enervalueforunitprice:32000
old1: insert into inventory values(&orderid,&productid,’&productname’,&quantity,&unitprice)new1:insertintoinventoryvalues(5001,88,Television,10,32000)

1 rowinserted
SQL>insertintoinventory_trans1values(&orderid,&productid,'&productname',&quantity,&unitprice);Entervaluefororderid:5002
Entervalueforproductid:89
Entervalueforproductname:AirconditionerEntervalueforquantity:-10
Enervalueforunitprice:40000
old1: insert into inventory values(&orderid,&productid,’&productname’,&quantity,&unitprice)new 1:insertintoinventoryvalues(5002,89,’Airconditioner’,-10,40000)
ORA-20040:INVALIDQUANTITY
ORA-06512:AT "SURESH.CHECK2", LINE16
ORA-04088:ERRORDURING EXECUTIONOFTRIGGER'SURESH.CHECK2'

*************************************************************************




PROGRAM6


AIM:


EXCEPTIONHANDLING FORBANK DATABASE


TOwriteaPL/SQLtoraisethefollowingExceptioninBankAccountManagementtableWhendepositamountis zero.
ALGORITHM:

Step1:LoginintoSQLplus.

Step2:Createthetable bankaccountwithaccno,name,depositcolumn.

Step3:CreateaPL/SQLstatementtocheckwhetherthegivenamountfordeposit<=0usingifConditionand whenthe conditionistrue ifwilldisplayinvaliddepositnumber.
Step4:IftheconditionisfalsethenitwillinsertanvaluefortherequiredattributestocreateaRecordinbankaccount.
Step5:Usethequeryselect* frombank acctodisplaytheinserted recordsinthetable.Step6:DisplaytheOutput
Step7: StoptheProcess.





QUERY


CREATEBANKACCOUNTMANAGEMENTTABLE:-

createtablebank_acc(accnonumberprimarykey,namevarchar(20),depositnumber);


CREATEAN EXCEPTIONFOR BANKACCOUNTMANAGEMENTTABLE:-

setserveroutputon


declare

vaccnonumber:=&vaccno;vname varchar2(20):= '&vname';vdepositnumber:=&vdeposit;zerodepositexception;
begin

if vdeposit<=0 thenraise zerodeposit;else
insertintobank_acc(accno,name,deposit)values(vaccno,vname,vdeposit);dbms_output.put_line('Recordissuccessfullyinserted');
endif;exception
whenzerodepositthendbms_output.put_line('Invalid deposit amount');end;
SQL>SELECT* FROMBANK_ACC;

ACCNO NAME DEPOSIT

SAR001
SURESH
4000

SAR002
ANOOP
80000

SAR003
SARAN
90000




EXCEPTIONHANDLINGUSINGPL/SQL:

ENTER VALUE FOR ACCNO: SAR001OLD9:A:='&ACCNO';
NEW9:A:='SAR001';
ENTER VALUE FOR DEPOSIT: 3500OLD10:D:='&DEPOSIT';
NEW10:D:='3500';

Recordissuccessfullyinserted

PL/SQLPROCEDURESUCCESSFULLYCOMPLETED.

SQL>SELECT* FROMBANK_ACC;

ACCNO
UNAME
ACCTYPE
BAL

SAR001
SURESH
SAVINGS
500

SAR002
ANOOP
CURRENT
80000

SAR003
SARAN
SAVINGS
90000


ENTER VALUE FOR ACCNO: SAR002OLD9:A:='&ACCNO';
NEW9:A:='SAR002';
ENTER VALUE FOR DEPOSIT: 0OLD10:D:='&DEPOSIT';
NEW10:D:='0';

Invaliddepositamount

PL/SQLPROCEDURESUCCESSFULLYCOMPLETED.