dbms lab manual

PRACTICAL RECORD NOTE BOOK

ANNA UNIVERSITY

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

LCR COLLEGE OF ENGINEERING & TECHNOLOGY

KANCHIPADI, THIRUTHANI TALUK

THIRUVALLUR DIST – 631 204.

PRACTICAL RECORD NOTE BOOK

ANNA UNIVERSITY

LCR COLLEGE OF ENGINEERING & TECHNOLOGY

NAME……………………………….. COURSE:

REG.NO……………………………… SEMESTER:

Certified that this is the he bonafide record of work done by the

student in the DATABASE MANAGEMENT SYSTEM LABORATORY (CS2258) in

the department of Computer Science and Engineering of LCR

College of Engineering and Technology during the year Apr/May

2010

Head Of the Department Staff-In-Charge

Submitted for the practical examination held on………………………..

Internal Examiner External Examiner

EX.NO DATE PROGRAM P.NO SIGNATURE

1 Data Definition, Table Creation, Constraints

2Insert, Select Commands, Update & Delete

Commands.

3 Nested Queries & Join Queries

4 Views

5 a PROCEDURE

5.b EXPRESSION TREE

6 BINARY SEARCH TREE

7 AVL TREE

8 PRIORITY QUEUE

9 HASHING

10 TOPOLOGICAL SORT

11 DIJKSTRA’S ALGORITHM

DATA DEFINITION, TABLE CREATION, CONSTRAINTS

Expt no: 1

Date :

AIM:

To implement a DDL commands of SQL.

SYNTAX:

The DDL commands create,desc,alter,truncate,drop

Create:

This command is used to

a) Create a tableCtreate table <table_name>(column definitions);

b) Create a alias name for tableCreate synonym alias-name for <table-name>;

c) Create a view for tableCreate view <view-name> as select col from tablename;

d) Create a new userCreate user user-name identified by password;

Alter: This command is used to

a) Modify the field widthAlter table <table_name> modify( column definition);

b) Add ,drop,rename a fieldAlter table <table_name> add (col def);Alter table <table_name> drop column col_name;Alter table <table_name> rename column col_name to new_col_name ;

c) Add integrity constaraintsAlter table <table_name> add constraint cons_name const-definition;

d) To rename a tableAlter table <table_name> rename to <new_tablename>;

Desc: This command is used to display the structure of table.

Desc <table-name>;Truncate: This command is used to delete all records and to reclaim the memory.

Truncate table <table-name> with reuse;Drop: This command is used to drop a table,view.

Drop table <table-name>;

1. Create table branch=(branch_name,city,asset). Enforce unique and check constraint on city and asset respectively.

create table branch(branch_name varchar2(20) primary key, city varchar2(20), asset number(9,2) constraint chkasset check(asset >= 100000));

Table created.

2. Create table customer=(customer_no,customer_name,address)

create table customer(customer_no varchar2(20),customer_name varchar2(20), address varchar2(20),primary key(customer_no));

Table created.

3. Create table account=(account_no, branch_name,amount). Branch_name references branch table.

create table account(account_no number(5),branch_name varchar2(20), amount number(7,2), constraint pkaccount primary key(account_no), constraint fkbranch foreign key(branch_name) references branch(branch_name));

Table created.

4. Create table loan=(loan_no,branch_name,amount). Branch_name references branch table.

create table loan(loan_no number(5) constraint pkloan primary key, branch_name varchar2(20) constraint fklbranch references branch(branch_name),amount number(7,2));

Table created.

5. Create table depositor=(customer_no,account_no). Fields references customer,account table.

create table depositor(customer_no number(5) constraint fkcno references customer(customer_no), account_no number(5) constraint fkaccno references account(account_no), constraint pkdep primary key(customer_no,account_no));

Table created.

6. Create table borrower=(customer_no,loan_no). Fields references customer, loan table.

create table borrower(customer_no number(5) constraint fkbcno references customer(customer_no), loan_no number(5) constraint fkloanno references loan(loan_no), constraint pkborrow primary key(customer_no,loan_no));

Table created.

7. Create new table from customer with all records of customer.

create table customer_dup as select * from customer;

Table created.

8. Create new table from account without including its records.

create table account_dup as select * from account where 1=2;

Table created.

9. Display the structure of branch.

desc branch;

Table Column Data Type Length Precision Scale Primary Key Nullable DefaultComment

BRANCH BRANCH_NAME Varchar2 20 - - 1 - - -

CITY Varchar2 20 - - - - -

ASSET Number - 7 2 - - -

1 - 3

10. Display the structure of customer.

desc customer;

Table ColumnData Type

Length

Precision

Scale

Primary Key

Nullable

Default

Comment

CUSTOMER

CUSTOMER_NO Number - 5 0 1 - - -

CUSTOMER_NAME

Varchar2 20 - - - - -

ADDRESS Varchar2 20 - - - - -

11. 11. Display the structure of account. desc account;


Length

Precision

Scale

Primary Key

Nullable

Default

Comment

ACCOUNT

ACCOUNT_NO

Number - 5 0 1 - - -

BRANCH_NAME

Varchar2 20 - - - - -

AMOUNT Number - 7 2 - - -

12. Display the structure of loan. Desc loan;

Table Column

Data Type

Length

Precision

Scale

Primary Key

Nullable

Default

Comment

LOAN LOAN_NO Number - 5 0 1 - - -

BRANCH_NAME

Varchar2 20 - - - - -


13. Display the structure of depositor.Desc depositor;


Length

Precision

Scale

Primary Key

Nullable

Default

Comment

DEPOSITOR

CUSTOMER_NO

Number - 5 0 1 - - -

ACCOUNT_NO

Number - 5 0 2 - - -

14. Display the structure of borrower.

Desc borrower;


Length

Precision

Scale

Primary Key

Nullable

Default

Comment

BORROWER

CUSTOMER_NO

Number - 5 0 1 - - -

LOAN_NO Number - 5 0 2 - - -

15. Display the structure of customer_dup.

Desc customer_dup;


Length

Precision

Scale

Primary Key

Nullable

Default

Comment

CUSTOMER_DUP

CUSTOMER_NO

Number - 5 0 - - -

CUSTOMER_NAME

Varchar2 20 - - - - -

ADDRESS Varchar2 20 - - - - -

16. Display the structure of account_dup.

Desc account_dup;


Length

Precision

Scale

Primary Key

Nullable

Default

Comment

ACCOUNT_DUP

ACCOUNT_NO

Number - 5 0 - - -

BRANCH_NAME

Varchar2 20 - - - - -


17. Increase the length of the field customer_name in customer .

alter table customer modify customer_name varchar2(30);

Table altered.

18. Add a new field telno to customer.

alter table customer add telno number(10);

Table altered.

19. Add not null constraint to address field of customer.

alter table customer modify address varchar2(20) not null;

Table altered.

20. Add unique constraint to telno of customer.

alter table customer modify telno number(10) constraint uniqtelno unique;

Table altered.

21. Rename a column telno to telephone of customer.

alter table customer rename column telno to telephone;

Table altered.


Desc customer;


Length

Precision

Scale

Primary Key

Nullable

Default

Comment

CUSTOMER


CUSTOMER_NAME

Varchar2 30 - - - - -

ADDRESS Varchar2 20 - - - - - -

TELEPHONE Number - 10 0 - - -

23. Drop a field telephone from customer.

alter table customer drop column telephone;

Table dropped.


Desc customer;


Length

Precision

Scale

Primary Key

Nullable

Default

Comment

CUSTOMER


CUSTOMER_NAME

Varchar2 30 - - - - -

ADDRESS Varchar2 20 - - - - - -

25. Rename a relation customer_dup to cust_dup;

alter table customer_dup rename to cust_dup;

Table altered.

26. Delete all rows from cust_dup.

truncate table cust_dup;

Table truncated.

27. Create alias name for cust_dup.

create synonym cust for cust_dup;

synonym created.

28. Create a view from account.

create view accview as select account_no,amount from account ;

view created.

29. Display the structure of view.

Desc accview;


Length

Precision

Scale

Primary Key

Nullable

Default

Comment

ACCVIEW

ACCOUNT_NO

Number - 5 0 - - - -


30. Drop a relation cust.

Drop table cust_dup;

Table dropped.

31. Create a new user.

Create user student identified by student;

User created.

INSERT, SELECT, UPDATE, DELETE COMMANDS

Expt no: 2

Date :

AIM:

To implement DML commands in RDBMS.

SYNTAX:

The DML commands are insert,select,update,delete.

Insert:This command is used to insert records into the table

Insert into <table-name> values(list of values);

Select:This command is used to display the records

Select * from <table-name> where condition;

Update:This command is used to change the values of records.

Update <table-name> set col-name=value where condition;

Delete:This command is used to delete records from table

Delete from <table-name> where condition;

1. Insert 3 records into branch table.

insert into branch values('Adayar','Chennai',100000);

insert into branch values('T.ngr','Chennai',200000);

insert into branch values('Avadi','Thiruvallur',300000);

3 rows inserted.

2. Insert 3 records into customer table.

insert into customer values(101,'kumar','Thiruvallur');

insert into customer values(102,'Ram','Avadi');

insert into customer values(103,'Krishnan','Adayar');

3 rows inserted.

3. Insert 3 records into account table.

insert into account values(1001,'Adayar',10000);

insert into account values(1002,'Avadi',20000);

insert into account values(1003,'T.ngr',20000);

3 rows inserted.

4. Insert 3 records into depositor table.

insert into depositor values(101,1001);



3 rows inserted.

5. Insert 3 records into loan table.

insert into loan values(2001,'Adayar',10000);

insert into loan values(2002,'Adayar',20000);

insert into loan values(2003,'T.ngr',30000);

3 rows inserted.

6. Insert 3 records into borrower table.

insert into borrower values(101,2001);



3 rows inserted.

7. Find city in which the branches are located.

select distinct city from branch;

CITY

Thiruvallur

Chennai

8. Find all the details of customer.

select * from customer;

CUSTOMER_NO CUSTOMER_NAME ADDRESS

101 kumar Thiruvallur

102 Ram Avadi

103 Krishnan Adayar

9. Find name of customer whose address is either Avadi or Adayar.

select customer_name,address from customer where address in('Avadi','Adayar');

CUSTOMER_NAME ADDRESS

Ram Avadi

Krishnan Adayar

10. Find customer whose name starts with ‘k’

select customer_name from customer where customer_name like'k%';

CUSTOMER_NAME

kumar

11. Find customer whose name starts with ‘K’ and ends with ‘n’.

select customer_name from customer where customer_name like'K%n';

CUSTOMER_NAME

Krishnan

12. Find customer whose name third letter is ‘m’

select customer_name from customer where customer_name like'__m%';

CUSTOMER_NAME

kumar

Ram

13. Find the length of the word ‘CSE”

select length('CSE') from dual;

LENGTH('CSE')

3

14. Find no of records in account table.

select count(account_no) "no of records" from account;

no of records

3

15. Find the system date.

select sysdate from dual;

SYSDATE

22-JAN-10

16. Find the asset of ‘Adayar’ branch.

select asset from branch where branch_name='Adayar';

ASSET

150000

17. Add 5000 to the balance of all account.

update account set amount=amount+5000;

3 rows updated.

18. Delete account with account_no 1003.

delete from account where account_no=1003;

ORA-02292: integrity constraint (SYSTEM.FKACCNO) violated - child record found18.

19. Delete record form borrower table with loan no 2003.

delete from borrower where loan_no=2003;

20. Delete record form loan table with loan no 2003.

delete from loan where loan_no=2003;

21. Change the branch name of a loan with loan_no 2002.

update loan set branch_name='Adayar' where loan_no=2002;

22. Find customers who have either loan or account at the bank.

select customer_no from depositor union select customer_no from borrower;

CUSTOMER_NO

101

102

103

23. Find customers who have both account and loan at the bank

select customer_no from depositor intersect select customer_no from borrower;

CUSTOMER_NO

101

102

24. Find customers who have account but not loan at the bank

select customer_no from depositor minus select customer_no from borrower;

CUSTOMER_NO

103

NESTED QUERIES AND JOIN QUERIES

Expt no: 3

Date:

AIM:

To implement join queries and nested queries in RDBMS

Queries:

1. Find all customers who have both loan and account at the bank.

select distinct customer_no from depositor where customer_no in(select customer_no from borrower);

CUSTOMER_NO

102

101

2. Find all customers who have an account but not a loan at the bank.

select distinct customer_no from depositor where customer_no not in(select customer_no from borrower);

CUSTOMER_NO

103

3. Find all customers who have a account and loan at the ‘Adayar’ branch.

select customer_no,customer_name from customer where customer_no=(select distinct customer_no from borrower, loan where borrower.loan_no=loan.loan_no and branch_name='Adayar' and (branch_name,customer_no) in(select branch_name,customer_no from depositor,account where depositor.account_no=account.account_no));

CUSTOMER_NO CUSTOMER_NAME

101 kumar

4.Find the names of all branches that have assets greater than those of at least one branch located in ‘Chennai’.

select branch_name from branch where asset> some(select asset from branch where city='Chennai');

5. Find the branch that has highest average balance.

select branch_name from account group by branch_name having avg(amount)>=all(select avg(amount) from account group by branch_name);

BRANCH_NAME

BRANCH_NAME

T.ngr

Avadi

T.ngr

6. Delete a duplicate row from the borrower table.


select * from borrower;

delete from borrower a where a.rowid>(select min(b.rowid) from borrower b where

b.customer_no=a.customer_no);

select * from borrower;

7. Find customer no and loan amount of the customer who have a loan at the bank.

select customer_no,amount from loan join borrower on loan.loan_no=borrower.loan_no;

8. select customer who have account but not loan at the bank.

select depositor.customer_no from depositor left join borrower on depositor.customer_no=borrower.customer_no where borrower.customer_no is null;

9. select customer who have loan but not account at the bank.

insert into customer values(104,'Avadi','Avadi');

insert into loan values(104,'Avadi',50000);


CUSTOMER_NO LOAN_NO

101 2001

101 2002

102 2002

CUSTOMER_NO LOAN_NO

101 2001

102 2002

CUSTOMER_NO AMOUNT

101 10000

102 20000

CUSTOMER_NO

103

select borrower.customer_no from depositor right join borrower on depositor.customer_no=borrower.customer_no where depositor.customer_no is null;

10. Find all customers who have either loan or account(but not both) at the bank.

select * from depositor full join borrower on depositor.customer_no=borrower.customer_no where account_no is null or loan_no is null;

CUSTOMER_NO ACCOUNT_NO CUSTOMER_NO LOAN_NO

103 1003 - -

- - 104 2004

VIEWS

Expt no:4

Date :

CUSTOMER_NO

104

AIM:

To implement the concept of views in RDBMS.

SYNTAX:

Create [or replace] view <viewname> [col alias name] as <query> ;

SQL COMMANDS:

1.create a table spersonel with the following details.(regno (p.k),name,address,phone).

create table spersonel(regno number(11) primary key,name varchar(20),address varchar(20), phone

number(11));

Table created.

2. create a table sacademic with the following details.(regno (p.k),attpercent).

create table sacademic(regno number(11) primary key,attpercent number(3));

Table created.

3.insert a record into spersonel table.

insert into spersonel values(11401104027,'jayakumar','Thiruvallur',9500983170);

1 row(s) inserted.

4.insert a record into sacademic table.

insert into sacademic values(11401104027,70);

1 row(s) inserted.

5.create a join view from sacademic and spersonel table.

create or replace view sview as select sp.regno,name,attpercent,phone from spersonel sp,sacademic sa where sp.regno=sa.regno and sa.attpercent<75;

view created.

6.Display the view

select * from sview;

REGNO NAME ATTPERCENT PHONE

11401104027 jayakumar 70 9500983170

7.update the spersonel tables’s column in a view.

update sview set name='jai' where regno=11401104027;

1 row(s) updated.

8.update the sacademic tables’s column in a view.

update sview set attpercent=72 where regno=11401104027;

1 row(s) updated.

9.Display the view

Select * from sview;

REGNO NAME ATTPERCENT PHONE

11401104027 jai 72 9500983170

10.Display the sacademic table.

select * from sacademic;

REGNO ATTPERCENT

11401104027 72

11.Display the spersonel table

select * from spersonel;

REGNO NAME ADDRESS PHONE

11401104027 jai Thiruvallur 9500983170

12.update the spersonel tables’s column in a view using non key attribute in where condition.

update sview set name='jaikumar' where phone=9500983170;

1 row(s) updated.

13.Display the spersonel table

Select * from spersonel;

REGNO NAME ADDRESS PHONE

11401104027 jaikumar Thiruvallur 9500983170

14.insert a record into the view.

insert into sview values(11401104028,'jp',74,9841098410);

ORA-01776: cannot modify more than one base table through a join view

RESULT:

Thus the view has been implemented successfully.

FUNCTION

Expt no : 5. a

Date :

AIM:

To write a PL/SQL program to find the factorial of a given number using function.

ALGORITHM:

1. Get the value of n.

2. Initialize i=1 and fact=1.

3. Multiply fact and n. store the product in fact.

4. Increment the value of I by 1.

5. If i<=n then go to step 3. Otherwise go to step 6.

6. Print the value of fact.

PROGRAM:

Function.sql

create or replace function factorial(n number) return number is

i number:=1;

fact number:=1;

begin

while i<=n

loop

fact:=fact*i;

i:=i+1;

end loop;

return (fact);

end;

functionimp.sql

declare

a number;

b number;

begin

a:=&a;

b:=factorial(a);

dbms_output.put_line('The factorial is '||b);

end;

OUTPUT:

Function created. set serveroutput on;

Enter value for a: 5

old 5: a:=&a;

new 5: a:=5;

The factorial is 120

PL/SQL procedure successfully completed.

RESULT:

Thus the function has been implemented successfully.

PROCEDURE

Expt no : 5.b

Date :

AIM:

To write a PL/SQL program to print the address of customer using procedure.

ALGORITHM:

1. Get the customerno number .

2. Select address from customer for a given customerno.

3. Print the address.

PROGRAM:

create or replace procedure printaddress(no number) is

caddress customer.address%type;

begin

select address into caddress from customer where customer_no=no;

dbms_output.put_line('The address is:'||caddress);

exception

when no_data_found then

dbms_output.put_line('No such customer');

end;

procedureimp.sql

declare

cno customer.customer_no%type;

begin

cno:=&cno;

printaddress(cno);

end;

OUTPUT:

Procedure created.

Set serveroutput on.

Enter value for a: 5

old 5: cno:=&cno;

new 5: cno:=5;

The address is Thiruvallur

PL/SQL procedure successfully completed.

RESULT:

Thus the procedure has been implemented successfully.

FRONT END TOOLS

Expt no:6

Date :

AIM:

To study of front end tool VB.

VISUAL BASIC:

VB is a front end tool from Microsoft used to develop application rapidly. Here we learn About the Development Environment The project explorer windows Running a form Making your first *.exe Understanding the tool bar Introducing Source code Command Button properties Explanations of global modules Opening an existing Visual Basic project. Explore the forms and the source code behind the an existing project in design mode Recognise and understand the function of the main component of the Visual Basic environment eg. toolbar's , toolboxes , project window, properties window and most importantly the source code window. Saving your project to a file. Button Properties.

The Development Environment

Learning the ins and outs of the Development Environment before you learn visual basic is somewhat like learning for a test you must know where all the functions belong and what their purpose is. First we will start with labeling the development environment.

The above diagram shows the development environment with all the important points labelled. Many of Visual basic functions work similar to Microsoft word eg the Tool Bar and the tool box is similar to other products on the market which work off a single click then drag the width of the object required. The Tool Box contains the control you placed on the form window. All of the controls that appear on the Tool Box controls on the above picture never runs out of controls as soon as you place one on the form another awaits you on the Tool Box ready to be placed as needed.

The project explorer window

The Project explorer window gives you a tree-structured view of all the files inserted into the application. You can expand these and collapse branches of the views to get more or less detail (Project explorer). The project explorer window displays forms, modules or other separators which are supported by the visual basic like class'es and Advanced Modules. If you want to select a form on its own simply double click on the project explorer window for a more detailed look. And it will display it where the Default form is located.

Properties Window

Some programmers prefer the Categorisized view of the properties window. By defaulting, the properties window displays its properties alphabetically (with the exception of the name value) when you click on the categorized button the window changes to left picture.

The Default Layout

When we start Visual Basic, we are provided with a VB project.A VB project is a collection of the following modules and files.

The global module( that contains declaration and procedures) The form module(that contains the graphic elements of the VB application along with the

instruction )

The general module (that generally contains general-purpose instructions not pertaining to anything graphic on-screen)

The class module(that contains the defining characteristics of a class, including its properties and methods)

The resource files(that allows you to collect all of the texts and bitmaps for an application in one place)

On start up, Visual Basic will displays the following windows : The Blank Form window The Project window

The Properties window

It also includes a Toolbox that consists of all the controls essential for developing a VB Application. Controls are tools such as boxes, buttons, labels and other objects draw on a form to get input or display output. They also add visual appeal.

Understanding the tool box.

You may have noticed that when you click on different controls the Properties Window changes slightly this is due to different controls having different functions. Therefore more options are needed for example if you had a picture then you want to show an image. But if you wanted to open a internet connection you would have to fill in the remote host and other such settings. When you use the command ( ) you will find that a new set of properties come up the following will provide a description and a property.

Opening an existing Visual Basic project.

Microsoft have included some freebies with visual basic to show its capabilities and functions. Dismantling or modifying these sample projects is a good way to understand what is happening at runtime. These files can be located at your default directory /SAMPLES/

To Open these projects choose 'Open Project' from the 'File' menu. Then Double click on the samples folder to open the directory then Double click on any project to load it.

Opening a new visual basic file & Inserting Source code.

From looking at the examples it time to make your own application. Choose 'New Project' from the 'File' menu. Use the blank form1 to design a simple interface for an estate agents database, have some textboxes for names and other details. Insert some controls and make it look professional. Textboxes can be used to store there name and other details, make sure you put a picture box in for a picture of the house.

Now insert the following source code for your application.

Private Sub Form_Load()Picture1.Picture = LoadPicture("C:\Program Files\VB\Graphics\Icons\Misc\MISC42.ICO")End Sub

Running and viewing the project in detail.

Once an application is loaded it can be run by click on the icon from the toolbar, to pause press

and to terminate use .

Once a project is loaded, the name of the form(s) that it contains is displayed in the project window. To view a form in design mode, select the form required by clicking with the mouse to highlight its name, then clicking on the view form button.

In this example the project has been loaded and the maillist.frm has been selected for viewing. This Ms Mail example project useds 6 forms and 1 modules.

In Design mode, when the form is viewed, the code attached to any screen object may be inspected by double clicking on that object. The screen shots below show the interface of the Ms Mail example

(.../samples/Comtool/VBMail/MaiLLST.FRM) to view the code for this form select from the project window item.

Private Sub SetupOptionForm(BasePic As Control)

BasePic.Top = 0BasePic.Left = 0 BasePic.Visible = TrueBasePic.enabled = TrueOKBt.Top = BasePic.Height + 120Me.width = BasePic.Width + 120 Me.Heigh = OkBt.Top + OkBt.Height + 495

End Sub

Making your first *.exe!?

To make an excutable from a project choose 'MakeMake project.exe from the 'File' menu. Then click once on the Make project.exe choose a default location to store your executable, you can also change some advanced options by clicking on the Options.. tag before saving your exe

The above image will be displayed in the comment's value type some comments company name name etc... The Title tag represents the caption you will see if you press Control + Alt + Del. And the icon is the icon that will be available on the execute icon. As you can see it is quite simple to understand. All the comments, data and name appear when you click on the compiled (execute) exe and click properties.

Saving your visual basic project.

Save your work to disk. Use the Windows Explorer or any desktop windows to check that all files have been saved. There should be one Visual Basic Project (.VBP) file and separate Form (.FRM) and Module (.BAS) files for each form and module used in the current project.

Button Properties for reference

, Command Button & labels properties

Property Description

Name The name of the object so you can call it at runtime

BackColor This specifies the command button's background color. Click the BackColor's palette down arrow to see a list of common Windows control colours, you must change this to the style property from 0 - standard to 1 - graphical

Cancel Determines whether the command button gets a Click event if the user presses escape

Caption Holds the text that appears on the command button.

Default Determins if the command button responds to an enter keypress even if another control has the focus

Enable Determines whether the command button is active. Often, you'll change the enable property at runtime with code to prevent the user pressing the button

Font Produces a Font dialog box in which you can set the caption's font name , style and size.

Height Positions the height of the object - can be used for down

Left Positions the left control - can be used for right

MousePointer If selected to an icon can change the picture of the mouse pointer over that object

Picture Hold's the name of an icon graphic image so that it appears as a picture instead of a

Button for this option to work the graphical tag must be set to 1

Style This determins if the Command Button appears as a standard windows dialog box or a graphical image

Tab index Specifies the order of the command button in tab order

Tab Stop Whether the object can be tabbed to ( this can be used in labels which have no other function )

Tool Tip Text If the mouse is held over the object a brief description can be displayed (for example hold your mouse over one of the above pictures to see this happening

Visible If you want the user to see the button/label select true other wise just press false

Width Show the width of the object

RESULT:

Thus the study of VB is done successfully.

FORMS

Expt no:7

Date :

AIM:

To design a simple form and implement student attendance entry using in VB.

SOFTWARE REQUIRED:

1.Visual Basic 6.0

2.Oracle or Ms-Access

TABLES:

1.studatt=(regno,name,dept,year)

2.absdate=(regno,date)

3.att=(adate)

ALGORITHM:

1. Create a 3 tables and insert values to studatt

2. Open VB project.

3. Design a form for attendance entry.

4. Form should contain fields for name,regno,date,dept,year.

5. When the user submits data, display the total working day and percentage.

6. Save and run the program by pressing F5.

PROGRAM:

Dim con As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim rs1 As New Recordset

Dim rs2 As New Recordset

Private Sub cmbregno_Click()

Dim rno As Integer

rs.MoveFirst

While Not rs.EOF

If rs(0) = cmbregno.Text Then

txtname.Text = rs(1)

Exit Sub

Else

rs.MoveNext

End If

Wend

End Sub

Private Sub cmdcheck_Click()

cmbregno.Clear

rs.Open "select * from studatt where dept='" & cmbdept.Text & "' and year='" & cmbclass.Text & "'", con, adOpenDynamic, adLockOptimistic

rs.MoveFirst

While Not rs.EOF

cmbregno.AddItem (rs(0))

rs.MoveNext

Wend

End Sub

Private Sub cmdexit_Click()

End

End Sub

Private Sub cmdsubmit_Click()

Dim rs3 As New ADODB.Recordset

Dim totdays As Integer

Dim totabs As Integer

totdays = 0

totabs = 0

con.Execute ("insert into absdate values(" & cmbregno.Text & ",'" & CDate(Calendar1.Value) & "')")

rs3.Open "select * from att where adate='" & Calendar1.Value & "'", con, adOpenDynamic, adLockOptimistic

If rs3.EOF Or rs3.BOF Then

con.Execute ("insert into att values('" & Calendar1.Value & "')")

End If

MsgBox "Record saved"

rs1.Open "select * from att", con, adOpenDynamic, adLockOptimistic

rs1.MoveFirst

While Not rs1.EOF

totdays = totdays + 1

rs1.MoveNext

Wend

rs2.Open "select * from absdate where regno=" & cmbregno.Text & "", con, adOpenDynamic, adLockOptimistic

rs2.MoveFirst

While Not rs2.EOF

totabs = totabs + 1

rs2.MoveNext

Wend

txtday.Text = totdays

txtper.Text = (totdays - totabs) / totdays

rs.Close

rs1.Close

rs2.Close

rs3.Close

End Sub

Private Sub Form_Load()

con.Open ("provider=microsoft.jet.oledb.4.0;data source=student.mdb;persist security info=false")

End Sub

OUTPUT:

RESULT:

Thus the form has been designed and executed successfully.

TRIGGER

Expt no:8

Date :

AIM:

To write a PL/SQL program to insert a record into ven_mas table whenever a record is inserted

into vendor_master.

ALGORITHM:

1. Define an insert trigger on cusomer.

Get the values of record to be inserted using :new operator.

Insert those values into custdup table.

2. Insert a record into customer.

3. Display the values of both tables.

PROGRAM:

create or replace trigger customer_trigger after insert on customer for each row

declare

cno customer.customer_no%type;

cname customer.customer_name%type;

address customer.address%type;

begin

cno := :new.customer_no;

cname := :new.customer_name;

address := :new.address;

insert into custdup values (cno,cname,address);

dbms_output.put_line('row inserted in custdup as well');

end;

OUTPUT:

select * from customer;



102 Ram Avadi

103 Krishnan Adayar

104 Avadi Avadi

select * from custdup;



102 Ram Avadi

103 Krishnan Adayar

104 Avadi Avadi

insert into customer values(105,'sathish','usilampatti');

row inserted in cusdup as well

1 row inserted.

Select * from custdup;



102 Ram Avadi

103 Krishnan Adayar

104 Avadi Avadi

105 sathish usilampatti

RESULT:

Thus the trigger has been written and executed successfully.

MENU DESIGN

Expt no:9

Date :

AIM:

To design a simple menu and implement text editor using menu editor in VB.

SOFTWARE REQUIRED:

Visual Basic 6.0

ALGORITHM:

1. Open VB project.

2. Click the right mouse button in form window and select menu editor.

3. Create File(open,new,save,saveas) and edit(cut,copy,paste,find) menu.

4. Write a code in the click event of the respective menu item to implement text editor.


PROGRAM:

Dim filename As String

Dim filenum

Dim filelen As Integer

Dim flag As Boolean

Private Sub copy_Click()

Clipboard.SetText rtxarea.SelText

End Sub

Private Sub cut_Click()

rtxarea.SelText = ""

End Sub

Private Sub find_Click()

Dim str As String

Dim pos As Integer

str = InputBox("Enter the string")

pos = InStr(1, rtxarea.Text, str, vbTextCompare)

If pos > 0 Then

rtxarea.SelStart = pos - 1

Else

MsgBox "Text not found"

End If

End Sub


flag = False

End Sub

Private Sub new_Click()

flag = True

rtxarea.Text = ""

rtxarea.Visible = True

End Sub

Private Sub open_Click()

rtxarea.Visible = True

filenum = FreeFile

CommonDialog1.ShowOpen

filename = ""

filename = CommonDialog1.filename

If filename <> "" Then

Open filename For Input As filenum

rtxarea.Text = Input(LOF(filenum), filenum)

Close #filenum

End If

End Sub

Private Sub paste_Click()

rtxarea.SelText = Clipboard.GetText

End Sub

Private Sub save_Click()

filenum = FreeFile

If flag = True Then

CommonDialog1.ShowSave

filename = ""



Open filename For Output As filenum

Print #filenum, rtxarea.Text

MsgBox "Saved", vbOKOnly

End If

Else




End If

flag = False

Close #filenum

End Sub

Private Sub saveas_Click()

filenum = FreeFile

CommonDialog1.ShowSave

filename = ""


MsgBox filename




Close #filenum


End If

End Sub

OUTPUT:

RESULT:

Thus the text editor has been implemented and executed successfully.

REPORT

Expt no:10

Date :

AIM:

To generate students attendance report using reports in VB.

SOFTWARE REQUIRED:

1.Visual Basic 6.0


TABLES:

1.studatt=(regno,name,percentage )

ALGORITHM:

1. Create a table as mentioned above.

2. Open VB project.

3. Design a to select a kind of report(100% or <75%).

4. Include data report and Design report to display the records.

5. Generate a recordset based on user option and assign it to data report.


PROGRAM:



Private Sub Command1_Click()

Dim opt As String

Dim p As Integer

opt = Combo1.Text

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\vb project\student report\stud.mdb;Persist Security Info=False")

If opt = "Attendance<75%" Then

rs.Open "select * from studatt where per<75", con, adOpenKeyset, adLockPessimistic

Else

If opt = "Attendance=100%" Then

rs.Open "select * from studatt where per=100", con, adOpenKeyset, adLockPessimistic

End If

End If

Set DataReport2.DataSource = rs

Me.Hide

DataReport2.Show

End Sub

OUTPUT:

BANKING SYSTEM

Expt no:11

Date :

AIM:

To design and implement Banking system.

SOFTWARE REQUIRED:

1.Visual Basic 6.0


TABLES:

1.account=(accountno,balance)

2.customer=(customerno,cusomername,address,phone)

3.depositor=(accountno,type,amount,accessdate)

ALGORITHM:

1. Create a 3 tables as mentioned above.

2. Open VB project.

3. Design a form to add new customer and account details.

4. Design a form to make a transaction.

5. Maintain the transaction details in depositor table.

6. Display the transaction details when required.


PROGRAM:

MDI form:

Private Sub deposit_Click()

frmdeposit.Show

End Sub

Private Sub details_Click()

frmdetails.Show

End Sub

Private Sub exit_Click()

End

End Sub

Private Sub open_Click()

frmopen.Show

End Sub

Private Sub refresh_Click()

Unload frmdeposit

Unload frmdetails

Unload frmopen

End Sub

Frmdeposit



Dim dep As Boolean, wit As Boolean

Dim ch As String, d As String

Private Sub cmdok_Click()

Dim bal As Integer

rs.open "select * from account where accountno='" & txtaccountno & "'", con, adOpenDynamic, adLockOptimistic

If rs.RecordCount = 0 Then

MsgBox "Invalid account"

Else

If dep = True Then

bal = rs(1)

bal = bal + Val(txtamount.Text)

con.Execute ("update account set balance=" & bal & " where accountno='" & txtaccountno & "'")

MsgBox "record updated"

Else

If wit = True Then

bal = rs(1)

If bal < Val(txtamount.Text) Then

MsgBox "not enough balance"

Else

bal = bal - Val(txtamount.Text)

con.Execute ("update account set balance=" & bal & " where accountno='" & txtaccountno & "'")

End If

End If

End If

d = Date 'Format$(Date, mm / dd / yyyy)

con.Execute ("insert into depositor values('" & txtaccountno.Text & "','" & ch & "'," & txtamount.Text & " ,'" & d & "')")

MsgBox "depositor is updated"

End If

dep = False

wit = False

rs.Close

End Sub


con.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\vb project\expt09\BANK.MDB;Persist Security Info=False")

End Sub

Private Sub optdeposit_Click()

dep = True

wit = False

ch = "D"

End Sub

Private Sub Optwithdraw_Click()

wit = True

dep = False

ch = "W"

End Sub

Frmopen



Dim rs1 As New ADODB.Recordset

Private Sub cmdadd_Click()

If cmdadd.Caption = "Add" Then

clear

enable

cmdadd.Caption = "save"

Else

con.Execute ("insert into customer values('" & txtcustomerid.Text & "','" & txtcustomername.Text & "','" & txtaddress.Text & "'," & txtphone.Text & ")")

con.Execute ("insert into account values('" & txtaccountno.Text & "'," & txtbalance.Text & ")")

con.Execute ("insert into depositor values('" & txtaccountno.Text & "','" & txtcustomerid.Text & "','" & " " & "','" & " " & "'," & 0 & ")")

MsgBox "Record inserted"

rs.Close

rs1.Close

openrecord

cmdadd.Caption = "Add"

disable

End If

End Sub

Private Sub cmdclose_Click()

End

End Sub

Private Sub cmddelete_Click()

con.Execute ("delete from customer where customerid='" & txtcustomerid.Text & "'")

con.Execute ("delete from account where accountno='" & txtaccountno.Text & "'")

con.Execute ("delete from depositor where accountno='" & txtaccountno.Text & "'")

MsgBox "Record deleted"

rs.Close

rs1.Close

openrecord

End Sub

Private Sub cmdedit_Click()

If cmdedit.Caption = "Edit" Then

cmdedit.Caption = "save"

enable

txtaccountno.Enabled = False

txtcustomerid.Enabled = False

Else

con.Execute ("update customer set customername='" & txtcustomername.Text & "',address='" & txtaddress.Text & "',phone=" & txtphone.Text & " where customerid='" & txtcustomerid.Text & "'")

con.Execute ("update account set balance=" & txtbalance.Text & " where accountno='" & txtaccountno.Text & "'")

MsgBox "Record updated"

rs.Close

rs1.Close

openrecord

cmdedit.Caption = "Edit"

disable

End If

End Sub

Private Sub cmdmovefirst_Click()

rs.MoveFirst

rs1.MoveFirst

movefields

End Sub

Private Sub cmdmovelast_Click()

rs.MoveLast

rs1.MoveLast

movefields

End Sub

Private Sub cmdmovenext_Click()

rs.MoveNext

rs1.MoveNext

If rs.EOF <> True And rs.EOF <> True Then

movefields

Else

MsgBox "This is the last record"

End If

End Sub

Private Sub cmdmoveprevious_Click()

rs.MovePrevious

rs1.MovePrevious

If rs.BOF <> True And rs1.BOF <> True Then

movefields

Else

MsgBox "This is the first record"

End If

End Sub


con.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\vb project\expt09\BANK.MDB;Persist Security Info=False")

openrecord

rs.MoveFirst

movefields

disable

End Sub

Private Sub movefields()

txtcustomerid.Text = rs(0)

txtcustomername.Text = rs(1)

txtaddress.Text = rs(2)

txtphone.Text = rs(3)

txtaccountno.Text = rs1(0)

txtbalance.Text = rs1(1)

End Sub

Private Sub openrecord()

rs.open "select * from customer", con, adOpenDynamic, adLockOptimistic

rs1.open "select * from account", con, adOpenDynamic, adLockOptimistic

End Sub

Private Sub clear()

txtcustomerid.Text = ""

txtcustomername.Text = ""

txtaddress.Text = ""

txtphone.Text = ""

txtaccountno.Text = ""

txtbalance.Text = ""

End Sub

Private Sub enable()

txtcustomerid.Enabled = True

txtcustomername.Enabled = True

txtaddress.Enabled = True

txtphone.Enabled = True

txtaccountno.Enabled = True

txtbalance.Enabled = True

End Sub

Private Sub disable()

txtcustomerid.Enabled = False

txtcustomername.Enabled = False

txtaddress.Enabled = False

txtphone.Enabled = False

txtaccountno.Enabled = False

txtbalance.Enabled = False

End Sub

OUTPUT:

MDI form:

Frmdetails:

Frmdeposit:

Frmopen:

dbms lab manual

Documents

select distinct

bank select

nested queries

spersonel

table created

primary key

table creation

table altered