Test

PROGRAM NO:01.

NUMBER CONVERSION

FORM DESIGN:

CODING:

Dim a, gt, t As Integer

Private Sub cmdclear_Click()

Text1.Text = " "

hexlbl.Caption = " "

binlbl.Caption = " "

octlbl.Caption = " "

End Sub

Private Sub cmdexit_Click()

End

End Sub

Private Sub cmdgo_Click()

hexlbl.Caption = Hex(Val(Text1.Text))

octlbl.Caption = Oct(Val(Text1.Text))

binlbl.Caption = ""

gt = Val(Text1.Text)

Do While True

a = gt Mod 2

binlbl.Caption = a & binlbl.Caption

t = gt / 2

If (t = 1 Or t = 0) Then

binlbl.Caption = t & binlbl.Caption

Exit Sub

End If

gt = t

Loop

End Sub

OUTPUT:

PROGRAM NO:02.

ADDING ITEMS TO LIST BOX AND COMBO BOX

FORM DESIGN:

CODING:

Private Sub cmdclear_Click()

Text1.Text = " "

Text1.SetFocus

cmdtexttolist.Enabled = False

End Sub

Private Sub cmdexit_Click()

End

End Sub

Private Sub cmdlisttocombo_Click()

Combo1.AddItem List1.Text

cmdtexttolist.Enabled = False

cmdlisttocombo.Enabled = False

End Sub

Private Sub cmdtexttolist_Click()

List1.AddItem Text1.Text

End Sub

Private Sub Form_Load()

cmdtexttolist.Enabled = False

cmdlisttocombo.Enabled = False

End Sub

Private Sub List1_Click()

cmdlisttocombo.Enabled = True

End Sub

Private Sub Text1_Change()

cmdtexttolist.Enabled = True

End Sub

OUTPUT:

PROGRAM NO:03.

CALCULATOR

FORM DESIGN:

CODING:

Dim op As String

Dim v1, v2 As Integer

Dim result

Private Sub cmdadd_Click()

op = "+"

v1 = Val(Text1.Text)

Text1.Text = ""

End Sub

Private Sub cmdclear_Click()

Text1.Text = " "

End Sub

Private Sub cmddiv_Click()

op = "/"

v1 = Val(Text1.Text)

Text1.Text = ""

End Sub

Private Sub cmdequal_Click()

v2 = Val(Text1.Text)

Select Case op

Case "/"

result = v1 / v2

Case "*"

result = v1 * v2

Case "-"

result = v1 - v2

Case "+"

result = v1 + v2

End Select

Text1.Text = result

End Sub

Private Sub cmdmul_Click()

op = "*"

v1 = Val(Text1.Text)

Text1.Text = ""

End Sub

Private Sub cmdnumbers_Click(Index As Integer)

Text1.Text = Text1.Text + cmdnumbers(Index).Caption

End Sub

Private Sub cmdsub_Click()

op = "-"

v1 = Val(Text1.Text)

Text1.Text = ""

End Sub

OUTPUT:

PROGRAM NO:04.

COMMON DIALOG CONTROL

FORM DESIGN:

CODING:

Private Sub cmdexit_Click()

End

End Sub

Private Sub cmdfontwindow_Click()

CommonDialog1.ShowFont

End Sub

Private Sub cmdopenwindow_Click()

CommonDialog1.ShowOpen

End Sub

Private Sub cmdsavewindow_Click()

CommonDialog1.ShowSave

End Sub

OUTPUT:

PROGRAM NO:05.

QUESTIONAIRES

FORM DESIGN:

CODING:

Dim result As Integer

Private Sub cmdend_Click()

End

End Sub

Private Sub cmdnext1_Click()

If Option1ans2.Value Then result = result + 1

FrameQuestion1.Visible = False

FrameQuestion2.Visible = True

End Sub

Private Sub cmdnext2_Click()

If Option2ans2.Value Then result = result + 1

FrameQuestion2.Visible = False

FrameQuestion3.Visible = True

End Sub

Private Sub cmdnext3_Click()

If Option3ans2.Value Then result = result + 1

FrameQuestion3.Visible = False

FrameQuestion4.Visible = True

End Sub

Private Sub cmdnext4_Click()

If Option4ans2.Value Then result = result + 1

FrameQuestion4.Visible = False

FrameQuestion5.Visible = True

End Sub

Private Sub cmdnext5_Click()

If Option5ans2.Value Then result = result + 1

FrameQuestion5.Visible = False

FrameResult.Visible = True

lblresult.Caption = "Result : " & result * 20 & "%"

End Sub

OUTPUT:

PROGRAM NO:06.

MENU DRIVEN PROGRAM USING MDI WINDOW

FORM DESIGN:

CODING FOR MDI FORM:

Private Sub biggest_Click()

form2.Show

End Sub

Private Sub exit1_Click()

End

End Sub

Private Sub smallest_Click()

Form3.Show

End Sub

CODING FOR BIGGEST TWO NUMBERS:

Dim x, y As Integer

Private Sub cmdresult_Click()

x = Val(Text1.Text)

y = Val(Text2.Text)

If (x > y) Then

MsgBox (x & " is biggest")

Else

MsgBox (y & " is biggest")

End If

End Sub

CODING FOR SMALLEST TWO NUMBERS:

Dim x, y As Integer

Private Sub cmdresult_Click()

x = Val(Text1.Text)

y = Val(Text2.Text)

If (x <>

MsgBox (x & " is Smallest")

Else

MsgBox (y & " is Smallest")

End If

End Sub

OUTPUT:

ORACLE

PROGRAM NO:07.

DATA DEFINITION BASICS

CREATE THE FOLLOWING TABLES:

1.CATEGORY HEADER:

SQL> create table cat_head06(cat_code number(3) primary key);

Table created.

SQL> desc cat_head06;

Name Null? Type

----------------------------------------- -------- ----------------------------

CAT_CODE NOT NULL NUMBER(3)

2.ROUTE HEADER:

SQL> create table route_head06(route_id number(3) primary key,

2 cat_code number(3),foreign key(cat_code) references cat_head06(cat_code));

Table created.

SQL> desc route_head06;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROUTE_ID NOT NULL NUMBER(3)

CAT_CODE NUMBER(3)

SQL> alter table route_head06 add (origin varchar2(6),

2 destination varchar2(6),distance_kms number(3));

Table altered.

SQL> desc route_head06;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROUTE_ID NOT NULL NUMBER(3)

CAT_CODE NUMBER(3)

ORIGIN VARCHAR2(6)

DESTINATION VARCHAR2(6)

DISTANCE_KMS NUMBER(3)

3.PLACE HEADER:

SQL> create table place_head06(place_id number(3) primary key);

Table created.

SQL> desc place_head06;

Name Null? Type

----------------------------------------- -------- ----------------------------

PLACE_ID NOT NULL NUMBER(3)

SQL> alter table place_head06 add (bus_station varchar2(6));

Table altered.

SQL> desc place_head06;

Name Null? Type

----------------------------------------- -------- ----------------------------

PLACE_ID NOT NULL NUMBER(3)

BUS_STATION VARCHAR2(6)

4.ROUTE DETAIL:

SQL> create table route_detail06(route_id number(3),place_id number(3),

2 foreign key(route_id) references route_head06(route_id),

3 foreign key(place_id) references place_head06(place_id));

Table created.

SQL> desc route_detail06;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROUTE_ID NUMBER(3)

PLACE_ID NUMBER(3)

SQL> alter table route_detail06 add (day varchar2(6));

Table altered.

SQL> desc route_detail06;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROUTE_ID NUMBER(3)

PLACE_ID NUMBER(3)

DAY VARCHAR2(6)

5.TICKET HEADER:

SQL> create table ticket_head06(ticket_no number(3) primary key);

Table created.

SQL> desc ticket_head06;

Name Null? Type

----------------------------------------- -------- ----------------------------

TICKET_NO NOT NULL NUMBER(3)

6.TICKET DETAIL:

SQL> create table ticket_detail06(ticket_no number(3),

2 foreign key(ticket_no) references ticket_head06(ticket_no));

Table created.

SQL> desc ticket_detail06;

Name Null? Type

----------------------------------------- -------- ----------------------------

TICKET_NO NUMBER(3)

SQL> alter table ticket_detail06 add (passengername varchar2(6),

2 gender varchar2(3),age number(3),seatno number(3),fare number(3));

Table altered.

SQL> desc ticket_detail06;

Name Null? Type

----------------------------------------- -------- ----------------------------

TICKET_NO NUMBER(3)

PASSENGERNAME VARCHAR2(6)

GENDER VARCHAR2(3)

AGE NUMBER(3)

SEATNO NUMBER(3)

FARE NUMBER(3)

(1). Alter the table ticket_header to add a check constraint on ticket_no to accept values between 1 and 500.

SQL> alter table ticket_head06 add constraint chkticket_no check(ticket_no between 1 and 500);

Table altered.

SQL> desc ticket_head06;

Name Null? Type

----------------------------------------- -------- ----------------------------

TICKET_NO NOT NULL NUMBER(3)

(2). Alter table route_header to add a column with data type as long.

SQL> alter table route_head06 add(fare long);

Table altered.

SQL> desc route_head06;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROUTE_ID NOT NULL NUMBER(3)

CAT_CODE NUMBER(3)

ORIGIN VARCHAR2(6)

DESTINATION VARCHAR2(6)

DISTANCE_KMS NUMBER(3)

FARE LONG

PROGRAM NO:08.

DATA MANIPULATION BASICS

(A)INSERT VALUES TO ABOVE TABLES:

1.CATEGORY HEADER:

SQL> insert into cat_head06 values (&cat_code);

Enter value for cat_code: 1

old 1: insert into cat_head06 values (&cat_code)

new 1: insert into cat_head06 values (1)

1 row created.

SQL> /

Enter value for cat_code: 2

old 1: insert into cat_head06 values (&cat_code)

new 1: insert into cat_head06 values (2)

1 row created.

SQL> /

Enter value for cat_code: 3

old 1: insert into cat_head06 values (&cat_code)

new 1: insert into cat_head06 values (3)

1 row created.

SQL> /

Enter value for cat_code: 4

old 1: insert into cat_head06 values (&cat_code)

new 1: insert into cat_head06 values (4)

1 row created.

SQL> select * from cat_head06;

CAT_CODE

----------

1

2

3

4

2.ROUTE HEADER:

SQL> insert into route_head06 values (&route_id,&cat_code,

2 &fare,'&origin','&destination',&distance_kms);

Enter value for route_id: 101

Enter value for cat_code: 1

old 1: insert into route_head06 values (&route_id,&cat_code,

new 1: insert into route_head06 values (101,1,

Enter value for fare: 400

Enter value for origin: madras

Enter value for destination: cochin

Enter value for distance_kms: 300

old 2: &fare,'&origin','&destination',&distance_kms)

new 2: 400,'madras','cochin',300)

1 row created.

SQL> /

Enter value for route_id: 102

Enter value for cat_code: 2

old 1: insert into route_head06 values (&route_id,&cat_code,

new 1: insert into route_head06 values (102,2,

Enter value for fare: 600

Enter value for origin: madras

Enter value for destination: covai

Enter value for distance_kms: 450

old 2: &fare,'&origin','&destination',&distance_kms)

new 2: 600,'madras','covai',450)

1 row created.

SQL> /

Enter value for route_id: 103

Enter value for cat_code: 3

old 1: insert into route_head06 values (&route_id,&cat_code,

new 1: insert into route_head06 values (103,3,

Enter value for fare: 600

Enter value for origin: madras

Enter value for destination: blr

Enter value for distance_kms: 500

old 2: &fare,'&origin','&destination',&distance_kms)

new 2: 600,'madras','blr',500)

1 row created.

SQL> select * from route_head06;

ROUTE_ID CAT_CODE ORIGIN DESTIN DISTANCE_KMS FARE

--------------- ---------------- ---------- ----------- ----------------------- ------------

101 1 madras cochin 300 400

102 2 madras covai 450 600

103 3 madras blr 500 600

3.PLACE HEADER:

SQL> insert into place_head06 values (&place_id,'&bus_station');

Enter value for place_id: 1

Enter value for bus_station: covai

old 1: insert into place_head06 values (&place_id,'&bus_station')

new 1: insert into place_head06 values (1,'covai')

1 row created.

SQL> /

Enter value for place_id: 2

Enter value for bus_station: blr

old 1: insert into place_head06 values (&place_id,'&bus_station')

new 1: insert into place_head06 values (2,'blr')

1 row created.

SQL> /

Enter value for place_id: 3

Enter value for bus_station: madras

old 1: insert into place_head06 values (&place_id,'&bus_station')

new 1: insert into place_head06 values (3,'madras')

1 row created.

SQL> select * from place_head06;

PLACE_ID BUS_ST

-------------- -----------

1 covai

2 blr

3 madras

4.ROUTE DETAIL:

SQL> insert into route_detail06 values (&route_id,&place_id,'&day');

Enter value for route_id: 101

Enter value for place_id: 1

Enter value for day: monday

old 1: insert into route_detail06 values (&route_id,&place_id,'&day')

new 1: insert into route_detail06 values (101,1,'monday')

1 row created.

SQL> /

Enter value for route_id: 102

Enter value for place_id: 2

Enter value for day: tues

old 1: insert into route_detail06 values (&route_id,&place_id,'&day')

new 1: insert into route_detail06 values (102,2,'tues')

1 row created.

SQL> /

Enter value for route_id: 103

Enter value for place_id: 3

Enter value for day: friday

old 1: insert into route_detail06 values (&route_id,&place_id,'&day')

new 1: insert into route_detail06 values (103,3,'friday')

1 row created.

SQL> select * from route_detail06;

ROUTE_ID PLACE_ID DAY

-------------- --------------- -----------

101 1 monday

102 2 tues

103 3 Friday

5.TICKET HEADER:

SQL> insert into ticket_head06 values (&ticket_no);

Enter value for ticket_no: 1

old 1: insert into ticket_head06 values (&ticket_no)

new 1: insert into ticket_head06 values (1)

1 row created.

SQL> /

Enter value for ticket_no: 2

old 1: insert into ticket_head06 values (&ticket_no)

new 1: insert into ticket_head06 values (2)

1 row created.

SQL> /

Enter value for ticket_no: 3

old 1: insert into ticket_head06 values (&ticket_no)

new 1: insert into ticket_head06 values (3)

1 row created.

SQL> select * from ticket_head06;

TICKET_NO

-----------------

1

2

3

6.TICKET DETAIL:

SQL> insert into ticket_detail06 values (&ticket_no,'&passengername',

2 '&gender',&age,&seatno,&fare);

Enter value for ticket_no: 1

Enter value for passengername: asar

old 1: insert into ticket_detail06 values (&ticket_no,'&passengername',

new 1: insert into ticket_detail06 values (1,'asar',

Enter value for gender: mle

Enter value for age: 21

Enter value for seatno: 6

Enter value for fare: 400

old 2: '&gender',&age,&seatno,&fare)

new 2: 'mle',21,6,400)

1 row created.

SQL> /

Enter value for ticket_no: 2

Enter value for passengername: vivek

old 1: insert into ticket_detail06 values (&ticket_no,'&passengername',

new 1: insert into ticket_detail06 values (2,'vivek',

Enter value for gender: mle

Enter value for age: 21

Enter value for seatno: 03

Enter value for fare: 400

old 2: '&gender',&age,&seatno,&fare)

new 2: 'mle',21,03,400)

1 row created.

SQL> /

Enter value for ticket_no: 3

Enter value for passengername: ravi

old 1: insert into ticket_detail06 values (&ticket_no,'&passengername',

new 1: insert into ticket_detail06 values (3,'ravi',

Enter value for gender: mle

Enter value for age: 21

Enter value for seatno: 04

Enter value for fare: 400

old 2: '&gender',&age,&seatno,&fare)

new 2: 'mle',21,04,400)

1 row created.

SQL> select * from ticket_detail06;

TICKET_NO PASSEN GEN AGE SEATNO FARE

----------------- ----------- ------- ------- ------------ --------

1 asar mle 21 6 400

2 vivek mle 21 3 400

3 ravi mle 21 4 400

(B) Display only those routes that originate in madras and terminate at cochin:

SQL> select * from route_head06 where origin='madras' and destination='covai';

ROUTE_ID CAT_CODE ORIGIN DESTIN DISTANCE_KMS FARE

-------------- ---------------- ----------- - ---------- ----------------------- ---------

102 2 madras covai 450 600

(C) Display only distinct category code from the table route_header in descending manner:

SQL> select distinct cat_code from route_head06 order by cat_code desc;

CAT_CODE

----------------

3

2

1

(D) Update the table route_header to set the distance between madras and coimbatore

as 500:

SQL> update route_head06 set distance_kms=500 where origin='madras' and destination='covai';

1 row updated.

SQL> select * from route_head06;

ROUTE_ID CAT_CODE ORIGIN DESTIN DISTANCE_KMS FARE

-------------- ---------------- ----------- - --------- ---------------------- ---------

101 1 madras cochin 300 400

102 2 madras covai 500 600

103 3 madras blr 500 600

PROGRAM NO:09.

QUERIES

(A)Select rows from ticket_details such that ticket number greater than any ticket_number in Ticket_header:

SQL> select * from ticket_detail06 where ticket_no>any(select ticket_no from ticket_head06);

TICKET_NO PASSEN GEN AGE SEATNO FARE

----------------- ----------- ------- ------ ------------ --------

2 vivek mle 21 3 400

3 ravi mle 21 4 400

(B)Select rows from route_header such that the route_id are greater than all route_id in route_detail

Where place id is “100”.

SQL> select * from route_head06 where route_id>all

2 (select route_id from route_detail06 where place_id=100);

ROUTE_ID CAT_CODE ORIGIN DESTIN DISTANCE_KMS FARE

-------------- ---------------- ----------- ----------- ------------------------ ----------

101 1 madras cochin 300 400

102 2 madras covai 500 600

103 3 madras blr 500 600

(C)Create view tick from ticket_header with Ticket_no,Origin,Destination,Route_id.

SQL> create view ticket_view as select th.ticket_no,rh.origin,destination,route_id

2 from ticket_head06 th,route_head06 rh;

View created.

SQL> select * from ticket_view;

TICKET_NO ORIGIN DESTIN ROUTE_ID

---------------- ---------- ----------- ---------------

1 madras cochin 101

2 madras cochin 101

3 madras cochin 101

1 madras covai 102

2 madras covai 102

3 madras covai 102

1 madras blr 103

2 madras blr 103

3 madras blr 103

9 rows selected.

PROGRAM NO:10.

REPORT

(A)Generate a report from the table ticket_detail for the particular ticket_no:

SQL> ttitle center 'ticket details' skip 2

SQL> column ticket_no heading ticket_no justify center

SQL> column passengername heading passengername justify center

SQL> column age heading age justify center

SQL> column gender heading gender justify center

SQL> column seatno heading seatno justify center

SQL> column fare heading fare justify center

SQL> column ticket_no format 99

SQL> column passengername format a13

SQL> column age format 99

SQL> column gender format a7

SQL> column seatno format 999

SQL> column fare format 9999

SQL> compute sum of fare on ticket_no

SQL> break on ticket_no skip 2

SQL> select ticket_no,passengername,age,gender,seatno,fare from ticket_detail06 order by ticket_no;

TICKET DETAILS

ticket_no passengername age gender seatno fare

----------- -------------------- ---- --------- -------- -----

1 asar 21 mle 6 400

********* -----

sum 400

2 vivek 21 mle 3 400

********* -----

sum 400

3 ravi 21 mle 4 400

********* -----

sum 400

PROGRAM NO:11.

PL / SQL BLOCK

(A)Write a PL/SQL block to update the bus_station to be “ERODE” where place_id is ‘01’ or ‘05’ [place_header]:

SQL> select * from place_head06;

PLACE_ID BUS_ST

-------------- -----------

1 covai

2 blr

3 madras

SQL> declare

2 cursor plhd is select * from place_head06;

3 pldet plhd%rowtype;

4 plid number(3);

5 busst varchar2(6);

6 begin

7 open plhd;

8 loop

9 fetch plhd into pldet;

10 exit when plhd%NOTFOUND;

11 update place_head06 set BUS_STATION='Erode' where PLACE_ID=1;

12 end loop;

13 close plhd;

14 end;

15 /

PL/SQL procedure successfully completed.

SQL> select * from place_head06;

PLACE_ID BUS_ST

-------------- -----------

1 Erode

2 blr

3 madras

(B)Write a PL/SQL block to satisfy the following condition by accepting the route_id

as user input.if the distance is less than 500 than update tha fare to be 200 :

SQL> select * from route_head06;

ROUTE_ID CAT_CODE ORIGIN DESTIN DISTANCE_KMS FARE

-------------- ---------------- ----------- ----------- ------------------------ ----------

101 1 madras cochin 300 400

102 2 madras covai 500 600

103 3 madras blr 500 600

SQL> declare

2 cursor plhd is select * from route_head06;

3 pldet plhd%rowtype;

4 fare1 long;

5 dis1 number(3);

6 routeid1 number(3);

7 begin

8 open plhd;

9 loop

10 fetch plhd into pldet;

11 exit when plhd%NOTFOUND;

12 routeid1 :=&routeid1;

13 update route_head06 set FARE=200 where distance_kms<500 style=""> route_id=routeid1;

14 end loop;

15 close plhd;

16 end;

17 /

Enter value for routeid1: 101

old 12: routeid1 :=&routeid1;

new 12: routeid1 :=101;

PL/SQL procedure successfully completed.

SQL> select * from route_head06;

ROUTE_ID CAT_CODE ORIGIN DESTIN DISTANCE_KMS FARE

-------------- ---------------- ----------- ----------- ------------------------ ----------

101 1 madras cochin 300 200

102 2 madras covai 500 600

103 3 madras blr 500 600

(C)Write a Database trigger before insert for each row on the table route_detail not allowing transaction on saturday/sunday :

SQL> select * from route_detail06;

ROUTE_ID PLACE_ID DAY

-------------- --------------- -----------

101 1 monday

102 2 tues

103 3 Friday

SQL> create or replace trigger rdtrigger before insert on route_detail06 for each row

2 begin

3 if :new.DAY = 'Sat' or :new.DAY='sunday' then

4 raise_application_error(-20001,'Saturday and sunday no transaction');

5 end if;

6 end;

7 /

Trigger created.

SQL> insert into route_detail06 values(103,3,'sat');

1 row created.

SQL> insert into route_detail06 values(103,3,'Sat');

insert into route_detail06 values(103,3,'Sat')

*

ERROR at line 1:

ORA-20001: Saturday and sunday no transaction

ORA-06512: at "SCOTT.RDTRIGGER", line 3

ORA-04088: error during execution of trigger 'SCOTT.RDTRIGGER'

SQL> insert into route_detail06 values(103,3,'sunday');

insert into route_detail06 values(103,3,'sunday')

*

ERROR at line 1:

ORA-20001: Saturday and sunday no transaction

ORA-06512: at "SCOTT.RDTRIGGER", line 3

ORA-04088: error during execution of trigger 'SCOTT.RDTRIGGER'

(D)Write a Database trigger before delete for each row not allowing deletion and give the appropriate message on the table route_details:

SQL> create or replace trigger rddeltr before delete on route_detail06 for each row

2 begin

3 raise_application_error(-20001,'You are not have a authority for delete');

4 end;

5 /

Trigger created.

SQL> delete route_detail06 where route_id=101;

delete route_detail06 where route_id=101

*

ERROR at line 1:

ORA-20001: You are not have a authority for delete

ORA-06512: at "SCOTT.RDDELTR", line 2

ORA-04088: error during execution of trigger 'SCOTT.RDDELTR'

PROJECT

PROGRAM NO:12.

STUDENT DATABASE MANAGEMENT SYSTEM

FORM DESIGN:

CODING:

Private Sub cmdadd_Click()

Data1.Recordset.AddNew

MsgBox ("You are ready for adding a new record")

End Sub

Private Sub cmddelete_Click()

TxtRegNo.Text = ""

TxtName.Text = ""

TxtAge.Text = ""

TxtDOB.Text = ""

TxtAddress.Text = ""

TxtHosteldayscholar.Text = ""

TxtPercentage.Text = ""

TxtDepartment.Text = ""

Data1.Recordset.Delete

MsgBox ("One record was deleted")

End Sub

Private Sub cmdexit_Click()

End

End Sub

Private Sub cmdsave_Click()

Data1.Recordset.Update

MsgBox ("One record was saved")

End Sub

OUTPUT: