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:
Nenhum comentário:
Postar um comentário