DEPARTMENT OF INFORMATION TECHNOLOGY

DATA BASE SYSTEMS LABORATORY

II Year [Link] IT/IV Semester

2019 REGULATION

LAB MANUAL

1|Page
 ABOUT THE LABORATORY
REQUIREMENTS

Hardware Requirements:
Pentium processor, 2GB RAM, Keyboard, Mouse and Monitor

Software Requirements:
ORACLE 8i,9i.

Lab Equipment:
Lab Equipment for a Batch of 30 Students

Standalone desktops with Windows - 30 Nos

2|Page
 DATBASE MANAGEMENT SYSTEM LABORATORY

INTRODUCTION

One of the most important purposes of development of computer systems was the database
application which could have been used on them. Data processing drove growth of computer processor
speed.

In fact, data processing predates the computers; punched cards were used in the US for collecting
data for census during beginning of 20th century. Earliest data processing was done by punched cards
on mechanical devices. The real development in data processing speed, storage of data and
development of DB applications started much later i.e. from 1950s.

Magnetic tapes were used to store data and being read from it. These database applications had
hierarchical structure and used network systems. They were extremely efficient when used with the
original query, exactly developed for them, but the DB was not designed to handle new queries or
transactions. Also the magnetic tapes must be in same sorted order so as to retrieve the authentic data.

Later in 60s hard disks came about and data retrieval was faster and did not need be stored
sequentially. This period was also remarkable in terms of advancement in DB Systems. Later in 1970
Edgar Codd, father or Relational Database Model, conceptualized a new structure for Database
construction and wrote a groundbreaking paper ‘A Relational Model of Data for Large Shared Data
Banks’. He freed database from procedural ways of querying and marked the beginning of Data
Abstraction i.e. hiding details of how Database is implemented to application programmers and end
users. System R, based on Codd’s concept was developed by IBM and it was first to have a language
for querying called SQL or Structured Query Language. Later, System R was further developed to a
mainstream commercial DBMS product known as DB2.

Object oriented programming was rapidly developing in the 80s and it also helped break into what
we know as Object Oriented Databases. The idea was to treat data as objects and it became easier to
conceptualize and program using this idea. Another great development which happened was processing
speed of processors and also conceptualization of indexing which greatly increased data access times,
and performances of DB.

3|Page
 90s was a time of a World Wide Web, so unprecedented like world had never seen before. The data
was here on the internet. Databases to which links were forwarded were varied and different and it
needed a technique to interchange data efficiently. Also the database had to be of very high availability
working 24x7. XML or eXtended Markup Language is a standard for providing data exchange among
different databases and WebPages.

More recently, there has been a growing trend of NoSQL database. These are different from so
called classical databases and do not rely on Relational Model for their structure. They do not query
data using Structured Query Language but UnQL or Unstructured Query Language which is still in
development stage (it is similar to XQuery). These databases are generally used when working with
huge quantities of data. Some examples are Mongo DB, CouchBase, HBase used by facebook, Big
Table used by Google and Dynamo DB used by Amazon.

4|Page
 OVERVIEW
DBMS stands for Database Management System. We can break it like this DBMS = Database +
Management System. Database is a collection of data and Management System is a set of programs to
store and retrieve those data. Based on this we can define DBMS like this: DBMS is a collection of
inter-related data and set of programs to store & access those data in an easy and effective manner.

Need of Database:Database systems are basically developed for large amount of data. When dealing
with huge amount of data, there are two things that require optimization: Storage of data and retrieval
of data.

Storage:According to the principles of database systems, the data is stored in such a way that it
acquires lot less space as the redundant data (duplicate data) has been removed before storage. Let’s
take a layman example to understand this:
In a banking system, suppose a customer is having two accounts, one is saving account and another is
salary account. Let’s say bank stores saving account data at one place (these places are called tables we
will learn them later) and salary account data at another place, in that case if the customer information
such as customer name, address etc. are stored at both places then this is just a wastage of storage
(redundancy/ duplication of data), to organize the data in a better way the information should be stored
at one place and both the accounts should be linked to that information somehow.

The same thing we achieve in DBMS.

 Fast Retrieval of data: Along with storing the data in an optimized and systematic manner, it
is also important that we retrieve the data quickly when needed. Database systems ensure that
the data is retrieved as quickly as possible.
 Redundancy of data: Data is said to be redundant if same data is copied at many places. If a
student wants to change Phone number, he has to get it updated at various sections. Similarly,
old records must be deleted from all sections representing that student.
 Inconsistency of Data: Data is said to be inconsistent if multiple copies of same data does not
match with each other. If Phone number is different in Accounts Section and Academics
Section, it will be inconsistent. Inconsistency may be because of typing errors or not updating
all copies of same data.

5|Page
  Difficult Data Access: A user should know the exact location of file to access data, so the
process is very cumbersome and tedious. If user wants to search student hostel allotment
number of a student from 10000 unsorted students’ records, how difficult it can
[Link] Access: File System may lead to unauthorized access to data. If a student gets
access to file having his marks, he can change it in unauthorized way.
 No Concurrent Access: The access of same data by multiple users at same time is known as
concurrency. File system does not allow concurrency as data can be accessed by only one user
at a time.
 No Backup and Recovery: File system does not incorporate any backup and recovery of data if
a file is lost or corrupted.

Application of Database Management System:

 Telecom: There is a database to keeps track of the information regarding calls made, network
usage, customer details etc. Without the database systems it is hard to maintain that huge
amount of data that keeps updating every millisecond.
 Industry: Where it is a manufacturing unit, warehouse or distribution centre, each one needs a
database to keep the records of ins and outs. For example distribution centre should keep a track
of the product units that supplied into the centre as well as the products that got delivered out
from the distribution centre on each day; this is where DBMS comes into picture.
 Banking System: For storing customer info, tracking day to day credit and debit transactions,
generating bank statements etc. All this work has been done with the help of Database
management systems.
 Sales: To store customer information, production information and invoice details.
 Airlines: To travel though airlines, we make early reservations, this reservation information
along with flight schedule is stored in database.
 Education sector: Database systems are frequently used in schools and colleges to store and
retrieve the data regarding student details, staff details, course details, exam details, payroll
data, attendance details, fees details etc. There is a hell lot amount of inter-related data that
needs to be stored and retrieved in an efficient manner.
 Online shopping: You must be aware of the online shopping websites such as Amazon,
Flipkart etc. These sites store the product information, your addresses and preferences, credit
details and provide you the relevant list of products based on your query. All this involves a
Database management system.

6|Page
 IMPORTANCE OF INFORMATION

DATABASE MANAGEMENT SYSTEM

 Data management system is needed for data access within the company
 It is needed to maintain strong relationships between data
 This system allows newer and better updates

7|Page
  It helps brand managers to search data in a better manner

Advantages of Database Management System (DBMS)

1. Improved data sharing: An advantage of the database management approach is, the DBMS helps to

create an environment in which end users have better access to more and better-managed [Link]

access makes it possible for end users to respond quickly to changes in their environment.

2. Improved data security: The more users access the data, the greater the risks of data security

breaches. Corporations invest considerable amounts of time, effort, and money to ensure that corporate

data are used properly. A DBMS provides a framework for better enforcement of data privacy and

security policies.

3. Better data integration: Wider access to well-managed data promotes an integrated view of the

organization’s operations and a clearer view of the big picture. It becomes much easier to see how

actions in one segment of the company affect other segments.

4. Minimized data inconsistency: Data inconsistency exists when different versions of the same data

appear in different places. For example, data inconsistency exists when a company’s sales department

stores a sales representative’s name as “Bill Brown” and the company’s personnel department stores

that same person’s name as “William G. Brown,” or when the company’s regional sales office shows

the price of a product as $45.95 and its national sales office shows the same product’s price as $43.95.

The probability of data inconsistency is greatly reduced in a properly designed database.

5. Improved data access: The DBMS makes it possible to produce quick answers to ad hoc queries.
From a database perspective, a query is a specific request issued to the DBMS for data manipulation—
for example, to read or update the data. Simply put, a query is a question, and an ad hoc query is a spur-
of-the-moment question. The DBMS sends back an answer (called the query result set) to the
application. For example, end users, when dealing with large amounts of sales data, might want quick
answers to questions (ad hoc queries) such as:

8|Page
- What was the dollar volume of sales by product during the past six months?

- What is the sales bonus figure for each of our salespeople during the past three months?

- How many of our customers have credit balances of 3,000 or more?

6. Improved decision making: Better-managed data and improved data access make it possible to

generate better-quality information, on which better decisions are based. The quality of the information

generated depends on the quality of the underlying data. Data quality is a comprehensive approach to

promoting the accuracy, validity, and timeliness of the data. While the DBMS does not guarantee data

quality, it provides a framework to facilitate data quality initiatives.

7. Increased end-user productivity: The availability of data, combined with the tools that transform
data into usable information, empowers end users to make quick, informed decisions that can make the
difference between success and failure in the global economy. Till now we have seen different benefits
of database management systems. But it has certain limitations or disadvantages.

SYLLABUS
1. Data Definition Commands, Data Manipulation Commands for inserting, deleting, updating and
retrieving Tables and Transaction Control statements
2. Database Querying – Simple queries, Nested queries, Sub queries and Joins
3. Views, Sequences, Synonyms
4. Database Programming: Implicit and Explicit Cursors
5. Procedures and Functions

9|Page
6. Triggers
7. Exception Handling
8. Database Design using ER modeling, normalization and Implementation for any application
9. Database Connectivity with Front End Tools
10. Case Study using real life database applications

COURSE OBJECTIVES AND OUTCOMES

COURSE OBJECTIVES:

The students should be made to:

 To understand data definitions and data manipulation commands

 To learn the use of nested and join queries
 To understand functions, procedures and procedural extensions of data bases
 To be familiar with the use of a front end tool
 To understand design and implementation of typical database applications

10 | P a g e
COURSE OUTCOME:

At the end of the course, the students should be able to

 Use typical data definitions and manipulation commands.

 Design applications to test Nested and Join Queries
 Implement simple applications that use Views
 Implement applications that require a Front-end Tool
 Critically analyze the use of Tables, Views, Functions and Procedures

Mapping Course Outcome with Programme Outcome

11 | P a g e
 Use typical data definitions and
Course Outcomes

Implement simple applications

andanalyze the use of Tables,

Implement applications that

manipulation commands.

Design applications to test

require a Front-end Tool

Nested and Join Queries
Program Outcomes

Views, Functions and

that use Views

Procedures
Pa Engineering Knowledge: Apply
knowledge of mathematics, science,
engineering fundamentals and an engineering H H H H
specialization for building engineering
models.
Pb Problem Analysis: Identify and solve
engineering problems reaching conclusions H H H H
using mathematics and engineering sciences.

Pc Design/Development of Solutions: Design

and develop solutions for engineering H H H H
problems that meet specified needs.

Pd Conduct Investigations of Complex

Problems: Conduct investigations of complex
H M M H
problems including design of experiments and
analysis to provide valid solutions.

Pe Modern Tool Usage: Create and apply

appropriate techniques, resources, and modern
engineering tools for executing engineering M H M H
activities.

PfThe Engineer and Society: Apply

reasoning of the societal, safety issues and the
consequent responsibilities relevant to
engineering practice.

PgEnvironment and Sustainability:

Understand the impact of engineering

12 | P a g e
 Use typical data definitions and
Course Outcomes

Implement simple applications

andanalyze the use of Tables,

Implement applications that

manipulation commands.

Design applications to test

require a Front-end Tool

Nested and Join Queries
Program Outcomes

Views, Functions and

that use Views

Procedures
solutions in the environment and exhibit the
knowledge for sustainable development.

Ph Ethics: Apply ethical principles and

commit to professional ethics, responsibilities
and norms of engineering practice.

Pi Individual and Team Work: Function

effectively as an individual, and as a member
or leader in diverse teams in multi-
disciplinary settings.

PjCommunication: Communicate effectively

to the engineering community and the outside
world and also to write effective reports.

PkProject Management and Finance:

Understand engineering and management
M H H H
principles and apply them to handle projects
in multi disciplinary environments.

Pl Life-Long Learning: Recognize the need

for life-long learning and apply in the context M H H H
of technological change.

LIST OF EXPERIMENTS

13 | P a g e
[Link] NAME OF THE EXERCISE PAGE No.

ORACLE -SQL

1 (a) INTRODUCTION TO DBMS AND ORACLE

1 (b) INTRODUCTION TO SQL

BASIC COMMANDS

DATA DEFINITION LANGUAGE COMMANDS AND DATA

2 (a)
MANIPULATION LANGUAGE (DML) COMMANDS

2 (b) DCL AND TCL COMMANDS

DATABASE QUERIES

3(a) SQL OPERATORS AND FUNCTIONS

3 (b) DATABASE QUERIES - NESTED QUERIES& SUB QUERIES

3 (c) DATABASE QUERIES - JOINS

DATABASE OBJECTS

4 DATABASE VIEWS.,SYNONYMS,SEQUENCES AND INDEX

PL/SQL PROGRAMMING

5 PL/SQL PROGRAMMING

DATABASE PROGRAMMING

6 (a) CURSOR MANAGEMENT AND EXCEPTION HANDLING

6 (b) PROCEDURES AND FUNCTIONS

6 (c) TRIGGERS

14 | P a g e
[Link] NAME OF THE EXERCISE PAGE No.

VISUAL BASIC

DATA BASE DESIGN USING ER MODEL AND

7 (a)
NORMALIZATION

7 (b) DATABASE CONNECTIVITY WITH FRONT END TOOLS

REAL LIFE DATABASE APPLICATIONS

DESIGN AND IMPLEMENTATION OF PAYROLL

8 (a)
PROCESSING SYSTEM

8 (b) DESIGN AND IMPLEMENTATION OF BANKING SYSTEM

DESIGN AND IMPLEMENTATION OF LIBRARY

8 (c)
INFORMATION SYSTEM

CONTENT BEYOND SYLLABUS

9 PACKAGES

15 | P a g e
 Ex:No : 1 (a) INTRODUCTION TO DBMS AND ORACLE

INTRODUCTION TO DBMS:

OVERVIEW OF RDBMS:

RDBMS is the acronym for Relational Data Base Management System. The
concept of relational database is known since 1980’s but the idea of Data Base Management System is
definitely old. The most famous RDBMS packages are Oracle, Sybase, and Informix.

WHAT IS DBMS?

A Data Base Management System is essentially a collection of interrelated data and a set of
programs to access this data. This collection of data is called the Database. The Primary objective of a
DBMS is to provide a convenient environment to retrieve and store database information. Database
systems support single user and multiuser environment. While on one hand DBMS permits only one
person to access the database at a given time, on the other RDBMS allows many users simultaneous
access to the database.

A Database System consists of two Parts namely, DBMS and Database Application.

 DBMS is the program that organizes and maintains the information whereas the Database Application
is the program that lets us view, retrieve and update information stored in the DBMS.
 DBMS has to protect against unintentional changes that could be caused by users and applications. In
case of multi user system, it must be capable of notifying any database change to the other user.

DBMS offers following services:

 Data Definition
 Data maintenance
 Data Manipulation
 Data display
 Data Integrity

16 | P a g e
INTRODUCTION TO ORACLE:

Every business enterprise maintains large volumes of data for its operations. With more
and more people accessing this data for their work the need to maintain its integrity and relevance
increases. Normally, with the traditional methods of storing data and information in files, the chance
that the data loses its integrity and validity are very high.

Oracle 8 is an Object Relational Database Management System (ORDBMS). It offers

capabilities of both relational and object oriented data base systems. In general, objects can be defined
as reusable software codes which are location independent and perform a specific task on any
application environment with little or no change to the code.

Oracle products are based on a concept known as the client/Server Technology. This
concept involves segregating the processing of an application between two systems. One performs all
activities related to the database (server) and the other performs activities that help the user to interact
with the application (client). A Client or front-end database application also interacts with the database
by requesting and receiving information from the database server. It acts as an interface between the
user and the database. Further, it also checks for validation against the data entered by the user. The
commonly used front end tools of oracle are SQL * Plus V 8, Oracle forms 5.0 and Reports 3.0. The
database server or backend is used to manage the database tables optimally among multiple clients who
concurrently request the server for the sane data. It also enforces data integrity across all client
applications and controls database access and other security requirements.

TOOLS OF ORACLE:

The tools provided by Oracle are so user friendly that a person with minimum skillls in
the field of computer can access them with ease. The main tools are

SQL * Plus , PL/SQL, Forms and Reports

SQL * PLUS:
SQL * Plus is a Structured Query Language supported by Oracle. Through SQl* Plus we
can store, retrieve, edit, enter and run SQL commands and PL/SQL blocks. Using SQl * Plus we can
perform calculations, list column definitions for any table and can format query results in the form of a
report.

17 | P a g e
 PL/SQL:

PL/SQL is an extension of SQL. PL./SQL statements can contain any number of SQL
statements integrated with flow of control statements. Thus PL/SQL combines the data manipulating
power of SQL with data processing power of procedural languages.

FORMS:

This is a graphical tool used for generating and executing form-based aplications. A
form basically comprises blocks and fields. Multiple tables can be accessed over a single form, based
on the application with the help of transaction commands. Oracle Forms Build is the design component
of Oracle forms. We can build, generate and run an Oracle forms application from the builder.

REPORTS:

It is an application development tool of Oracle for developing, executing, displaying and

printing reports. We can create a wide variety of reports. Oracle reports are powerful, yet easy to
[Link] reasons for choosing Oracle 8 as the RDBMS for effectively managing the data are

 Ability to retrieve data spread across multiple tables.

 Oracle specific SQL *Plus functions used when required to query the database especially to decide
future course of action.
 Provisions to maintain integrity of the database to avois data duplication and have constant checks on
the validity of data.
 Ability to break the most frequently used object, the table into smaller units thereby reducing the risk to
loss of data.
 With a number of clients accessing the database, Oracle allows explicit locking of data. Concurrent
access of data for manipulation can be prevented in this way.
 Storing of information out-of-line with the table is also a major advantage. This allows unstructured
information to be stored in a different location with the pointers to the location present in the table.

RESULT:
Thus the introduction to DBMS, ORACLE and SQL are StudieD.

18 | P a g e
 Ex: No : 1 (b) INTRODUCTION TO SQL

INTRODUCTION TO SQL:

SQL was invented and developed by IBM in early 1970’s. SQL stands for Structured Query
Language. IBM was able to demonstrate how to control relational database using SQL. The SQL
implemented by ORACLE CORPORATION is 100% compliant with the ANSI/ ISO standard SQL
data language. Oracle’s database language is SQL, which is used for storing and retrieving information
in Oracle. A table is a Primary database object of SQL that is used to store data. A table that holds data
in the form of rows and columns.
In order to communicate with the database, SQL supports the following categories of
commands: -
 Data Definition Language - create, alter and drop commands.
 Data Manipulation Language - insert, select, delete and update commands.
 Transaction Control Language - commit, savepoint and rollback commands.
 Data control Language - grant and revoke commands.

DATA DEFINITION LANGUAGE:

DDL is used to create an object ( table ), alter the structure of an object and also to drop the
object created. A table is a unit of storage that holds data in the form of rows and columns. DDl is used
for table defintion.

DATA MANIPULATION LANGUAGE:

DML commands are most frequently used SQL commands. They are used to query and
manipulate existing objects like tables.

TRANSACTION CONTROL LANGUAGE:

A transaction is a logical unit of work. All changes made to the database can be referred to as a
transaction. Transaction changes can be made permanent to a database only if they are committed. A
transaction begins with an executable SQL statement snd ends explicitly with either rollback or commit
commands and implicitly i.e., automatically, when a DDL statement is used.

19 | P a g e
 BENEFITS OF SQL:

 Non-procedural Language, because more than one record can be accessed rather than one record at a
time.
 It is the common language for all relational databases. In other words it is portable and it requires very
few modifications so that it can work on other databases.
 Very simple commands for Querying, inserting, deleting and modifying data and objects.

SQL Vs SQL *Plus:

SQL is a standard language common to all relatinal databases. SQL is a database language used
for storing and retrieving data from the database. Most relational Database Management Systems
provide extensions to SQL to make it easier for application developers.

SQL *Plus is an Oracle specific Program which accepts SQL commands and PL/SQL blocks
and executes them. SQL *Plus enables manipulation of SQL commands and PL/SQL blocks. It also
performs many additional tasks as well. Through SQL *Plus we can

 enter, edit, store, retrieve and run SQL commands and PL/SQL blocks.
 format, perform calculations, store and print query results in the form of reports.
 list column definitions for any table.
 Access and copy data between SQL databases.
 Send messages to and accept responses from an end user.

RESULT:

Thus the introduction to SQL are Studied.

20 | P a g e
Ex: No: 2 (a)DATA DEFINITION LANGUAGE COMMANDS AND
DATA MANIPULATION LANGUAGE (DML) COMMANDS

AIM:
To implement Data Definition Language (DDL) and Data Manipulation Language(DML)
commands in ORACLE.

DESCRIPTION:

DDL COMMANDS:( DATA DEFINITION LANGUAGE):

DDL commands are used to create an object, alter the structure of an object and also drop
the object created.

CREATE COMMAND:

This command is used to create a table or an object.

Syntax:create table <tablename>(<column name1> datatype, <column name2 > datatype,....);

ALTER COMMAND:

This command is used to add a field or modify the definition of field or column.

Syntax:alter table <tablename> add (<column name1> datatype, <column name2> datatype, .....);

alter table <tablename> modify (<column name1> datatype, <column name2> datatype, ...);

TRUNCATE COMMAND:

This command is used to delete all the rows of a table but not the structure of the table.

Syntax:truncate table <tablename>;

DROP COMMAND:

This command is used to delete the entire table along with the structure.

Syntax: drop table <tablename>;

21 | P a g e
Note:

I) DESC COMMAND:

This command is used to describe the table structure.( field or column name, datatype, null?)

Syntax: desc <table name>

II) DATA TYPES:

char, varchar(size),varchar2(size),date,number,number(size)

date –default format – dd/mon/yy (Eg. 27-jan-78)

III) ORACLE IMPLICITLY COMMITS:

The current transaction before and after every Data Definition Language statement.

22 | P a g e
 DML COMMANDS:( DATA MANIPULATION LANGUAGE):

DML commands are used to insert, view, update and delete the values of an object.

 INSERT COMMAND:

This command is used to insert a set of data values into the tables as defined in the create table

command.

Syntax:

insert into <tablename>values (value1,value2,.....,valuen);

insert into <table name>values(&columnname1,&columnname2,.....,&columnname n);

Note: varchar type field or column must be enclosed within single [Link].’&column’

 SELECT COMMAND:

This command is used to view particular data records or columns.

Syntax:

select <column name1,....>from <tablename>;

select * from <tablename>; - to view all records.

select distinct <columnname> from <tablename>;

select * from <tablename>orderby<columnname>; - default –ascending order.

select * from <tablename>orderby<columnname> desc;

- Records are sorted in descending order w.r.t column name

select * from <tablename> where <condition>;

23 | P a g e
 UPDATE COMMAND:

This command is used to update and change the data values of the table.

Syntax:

update <tablename> set <column>=value where <condition>;

 DELETE COMMAND:

This command is used to delete a particular record or all records of the table.

Syntax:

delete from <tablename> where<condition>;

delete * from <tablename>; -- to delete all the records or rows of a table.

-- similar to truncate command.

IMPLEMENTATION OF SQL COMMANDS(DDL &DML):

SQL> create table Tel(name varchar2(10),telno number(7));

Table created.
SQL> desc tel;

Name Null? Type

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

NAME VARCHAR2(10)

TELNO NUMBER(7)

SQL> alter table teladd(city varchar2(10));

Table altered.

24 | P a g e
SQL> desc tel;

Name Null? Type

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

NAME VARCHAR2(10)

TELNO NUMBER(7)

CITY VARCHAR2(10)

SQL> alter table telmodify(city varchar2(15));

Table altered.

SQL> desc tel;

Name Null? Type

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

NAME VARCHAR2(10)

TELNO NUMBER(7)

CITY VARCHAR2(15)

SQL> insert into tel values('Aarthi',2502157,'Chennai');

1 row created.

SQL> insert into tel values('&name',&telno,'&city');

Enter value for name: Banu

Enter value for telno: 2345678

Enter value for city: Mumbai

old 1: insert into tel values('&name',&telno,'&city')

new 1: insert into tel values('Banu',2345678,'Mumbai')

1 row created.

25 | P a g e
SQL> insert into tel values('&name',&telno,'&city');

Enter value for name: Banu

Enter value for telno: 2345678

Enter value for city: Mumbai

old 1: insert into tel values('&name',&telno,'&city')

new 1: insert into tel values('Banu',2345678,'Mumbai')

1 row created.

SQL> /

Enter value for name: Chitra

Enter value for telno: 2502387

Enter value for city: Tvl

old 1: insert into tel values('&name',&telno,'&city')

new 1: insert into tel values('Chitra',2502387,'Tvl')

1 row created.

SQL> /

Enter value for name: Sankar

Enter value for telno: 2330289

Enter value for city: Tvl

old 1: insert into tel values('&name',&telno,'&city')

new 1: insert into tel values('Sankar',2330289,'Tvl')

1 row created.

26 | P a g e
SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Banu 2345678 Mumbai

Chitra 2502387 Tvl

Sankar 2330289 Tvl

SQL> select distinct city from tel;

CITY

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

Chennai

Mumbai

Tvl

SQL> select * from tel order by telno;

NAME TELNO CITY

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

Sankar 2330289 Tvl

Banu 2345678 Mumbai

Aarthi 2502157 Chennai

Chitra 2502387 Tvl

SQL> select * from tel order by name desc;

NAME TELNO CITY

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

Sankar 2330289 Tvl

27 | P a g e
Chitra 2502387 Tvl

Banu 2345678 Mumbai

Aarthi 2502157 Chennai

SQL> select * from tel where name='Sankar';

NAME TELNO CITY

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

Sankar 2330289 Tvl

SQL> select * from tel where name='&name';

Enter value for name: Chitra

old 1: select * from tel where name='&name'

new 1: select * from tel where name='Chitra'

NAME TELNO CITY

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

Chitra 2502387 Tvl

SQL> /

Enter value for name: Banu

old 1: select * from tel where name='&name'

new 1: select * from tel where name='Banu'

NAME TELNO CITY

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

Banu 2345678 Mumbai

SQL> updatetel set city='Tirunelveli' where name='Chitra';

1 row updated.

28 | P a g e
SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Banu 2345678 Mumbai

Chitra 2502387 Tirunelveli

Sankar 2330289 Tvl

SQL> update tel set city='Tirunelveli' where name='&name';

Enter value for name: Sankar

old 1: update tel set city='Tirunelveli' where name='&name'

new 1: update tel set city='Tirunelveli' where name='Sankar'

1 row updated.

SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Banu 2345678 Mumbai

Chitra 2502387 Tirunelveli

Sankar 2330289 Tirunelveli

SQL> delete from tel where name='Chitra';

1 row deleted.

29 | P a g e
SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Banu 2345678 Mumbai

Sankar 2330289 Tirunelveli

SQL> truncate table tel;

Table truncated.

SQL> select * from tel;

no rows selected

SQL> desc tel;

Name Null? Type

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

NAME VARCHAR2(10)

TELNO NUMBER(7)

CITY VARCHAR2(15)

SQL> drop table tel;

Table dropped.
SQL> desc tel;

ERROR:ORA-04043: object tel does not exist

RESULT:

Thus the Data Definition Language and Data Manipulation Language commands are
implemented in ORACLE with database tables.

30 | P a g e
 Ex: No: 2 (b) DCL AND TCL COMMANDS

AIM:
To implement Transaction Control Language (TCL) and Data Control Language(DCL)
commands in ORACLE.

DESCRIPTION:

TCL COMMANDS:( TRANSACTION CONTROL LANGUAGE):

 SAVEPOINT Command:

This command is used to save and store the transaction done till a point.

Syntax: savepoint<savepoint_id>;

 ROLL BACK COMMAND:

This command is used to undo the transaction up to a save point or commit.

Syntax:

roll back;

roll back to <savepoint_id>;

 COMMIT COMMAND:

This command is used to save and end the transaction

Syntax:

commit;

31 | P a g e
 DCL COMMANDS:( DATA CONTROL LANGUAGE):

 GRANT COMMAND:

This command is used to used to grant privileges on tables to other users.

Syntax:

grant <privileges> on <tablename> to <username>;

 REVOKE COMMAND:

This command is used to the privileges on tables from users..

Syntax:

revoke< privileges> on<tablename> from <username>;

Note:

Priviledges: -- insert,select,update,delete..

IMPLEMENTATION OF SQL COMMANDS(TCL &DCL):

SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Banu 2345678 Mumbai

Sankar 2330289 Tirunelveli

SQL>commit;

Commit complete.

32 | P a g e
SQL> insert into tel values(‘Uma’,2345678,’Tirunelveli’);

1 row created

SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Banu 2345678 Mumbai

Sankar 2330289 Tirunelveli

Uma 2345678 Tirunelveli

SQL> roll back;

Roll back complete

SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Banu 2345678 Mumbai

Sankar 2330289 Tirunelveli

SQL> save point s1;

SQL>delete from tel where name=’Banu’;

1 row deleted

33 | P a g e
SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Sankar 2330289 Tirunelveli

SQL> roll back to s1;

SQL> select * from tel;

NAME TELNO CITY

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

Aarthi 2502157 Chennai

Banu 2345678 Mumbai

Sankar 2330289 Tirunelveli

RESULT:

Thus the Transaction Control Language (TCL) and Data Control Language(DCL) commands
are implemented in ORACLE with database tables.

34 | P a g e
[Link] : 3 (a) SQL OPERATORS & FUNCTIONS

AIM:
To study and implement the SQL operators & functions in Oracle.

DESCRIPTION:

SQL OPERATORS:
Operators in SQL can be classified as Arithmetic, Relational and Logical Operators

ARITHMETIC OPERATORS:
Operations Operator
Addition +
Subtraction -
Multiplication *
Division /
Modulo %
RELATIONAL OPERATORS:
These operators are used in search conditions in SQL.
Operations Operator
Equal to =
Not Equalto !=
Less than <
Greater than >
Less than or Equalto <=
Greater than or equalto >=
LOGICAL OPERATORS:
Between….and … : This operator searches between the specified values
In : This searches only for the given set of values.
Like : This is used for pattern matching. Wild card characters such as
% represents any number of characters.
– represents a single character

SQL FUNCTIONS:
The SQL functions can be classified into:
 Numeric Functions.
 Character Functions.
 Date Functions.

35 | P a g e
NUMBER FUNCTIONS:
1. abs(no) : The abs function returns the absolute value ( ie.,positive value even if the
negative value is given.) of a number.
2. ceil(no) : The ceil function rounds off the decimal number to the highest value.
3. floor(no) : The floor function rounds off the number to the lowest value.
4. exp(no) : The exp function is used to write the exponent value of the given number.
5. mod(m,n) : The mod function is used to write the remainder of the ratio m:n.
6. pow(m,n) : The power function is used to n th power of the number m.
7. round(m,n) : The round function is used to round the given number to its nearest value with n
decimal places.
8. sqrt(no) : The sqrt function is used to find the square root of the given number.
9. sin(no),cos(no),tan(no) :Sin, cos, tan are used to find the sine, co-sine and tangential values
for the given degree.
10. trunc(m,n) : The trunc function is used to print the lowest truncated floating point number to
the n specified digits.
Syntax: select function_name(parameter value)from dual;
select function_name(column-name)from<table-name>l;

CHARACTER FUNCTIONS
1. initcap(string) : This function capitalizes the first character of the given string.
2. upper(string) , lower(string) : The upper, lower functions are used to convert the strings to
upper or lower case respectively.
3. ltrim(string, charset) : It trims the character set from the right most part of the
string
4. rtrim(string,charset) : It trims the character set from the left most part of the
string
5. translate(string,search-string,translate-char) : It translates the search string in the given
string
to the translate character.
6. replace(string,search-string,repstring) :The replace function is used to replace the given
string with the specified string.
7. substr(string,m,n) : Returns the string with n characters from the m th position of
the given string.
8. chr(no) : This function returns the character equivalent of the number.
9. the ASCII value for the specified character or specified number.
10. concatenation : || is used as concatenation operator.
11. length(string) : The length function is used to find the length (or number of
Characters) of the given string.
Syntax:select character function (‘string parameter’) from dual;

DATE FUNCTIONS:
36 | P a g e
 1. add_months(Date,n) : The add_months function returns a date after adding date with
specified number of months.
2. last_day(Date) : The last_day function returns the date of the last day of the
corresponding month.
3. months_between(Date1,Date2): The months_between function is used to find the number
of months between the two given dates.
4. round(Date,format) : The round function returns date rounded to unit specified by the
format.
5. next_day(Date, Day) : The next_day function displays the date of day which follows
immediately the date.
6. trunc(Date,format) :The trunc function returns the date with time portion of the day
truncated to the unit (day, month or year)specified by the format.
7. greatest(Date1,.. Daten) : The greatest function returns the greatest or latest date present
in the argument.
8. The Sysdate – System date.
Syntax: Select date function (parameter) from dual;

Special Operators:
In / not in – used to select aequi from a specific set of values
Any - used to compare with a specific set of values
Between / not between – used to find between the ranges
Like / not like – used to do the pattern matching

Conversion Function
1. to_char()
Syntax: to_char(d,[format]);
This function converts date to a value of varchar type in a form specified by date format. If format is
negelected then it converts date to varchar2 in the default date format.
Example: select to_char (sysdate, ’dd-mm-yy’) from dual;

2. to_date()
Syntax: to_date(d,[format]);
This function converts character to date data format specified in the form character.
Example: select to_date(‘aug 15 2009’,’mm-dd-yy’) from dual;

Miscellaneous Functions
1. uid– This function returns the integer value (id) corresponding to the user currently
logged in.
Example: select uid from dual;
2. user – This function returns the logins user name.
37 | P a g e
Example: select user from dual;
2. nvl– The null value function is mainly used in the case where we want to consider null
values as zero.
Syntax; nvl(exp1, exp2)
If exp1 is null, return exp2. If exp1 is not null, return exp1.
Example: select custid, shipdate, nvl(total,0) from order;
4. vsize: It returns the number of bytes in expression.
Example: select vsize(‘tech’) from dual;

Group Functions
A group function returns a result based on group of rows.
1. avg Example: select avg (total) from student;
[Link] Example: select max (percentagel) from student;
[Link] Example: select min (marksl) from student;
4. sum Example: select sum(price) from product;

Count Function
In order to count the number of rows, count function is used.
1. count(*) – It counts all, inclusive of duplicates and nulls.
Example: select count(*) from student;
2. count(col_name)– It avoids null value.
Example: select count(total) from order;
2. count(distinct col_name) – It avoids the repeated and null values.
Example: select count(distinct ordid) from order;
Group by clause
This allows us to use simultaneous column name and group functions.
Example: Select max(percentage), deptname from student group by deptname;
Having clause
This is used to specify conditions on rows retrieved by using group by clause.
Example: Select max(percentage), deptname from student group by deptname having
count(*)>=50;

38 | P a g e
SQL> select * from mark;
SQL> select 5+5 sum from dual;
SQL> select 55-6 diff from dual;
SQL> select 55*9 mul from dual;
SQL> select 589/3 from dual;
SQL> update mark set total=m1+m2+m3;
SQL> select * from mark;
SQL> select * from mark where m2>90;
SQL> select * from mark where m1<80;
SQL> select * from mark where m1=99;
SQL> select * from mark where m1!=99;
SQL> select * from mark where total<=280;
SQL> select * from mark where total>=280;
SQL> select * from mark where m3 between 90 and 99;
SQL> select * from mark where rno in (1,3,5);
SQL> select * from mark where name like '%i';
SQL> select * from mark where name like 'ra_';
SQL> select * from mark where name like 'n__e%';
SQL> select abs(-100) from dual;
SQL> select ceil(avg) from mark;
SQL> select floor(avg) from mark;
SQL> select mod(98,5) from dual;
SQL> select round(65.1246,1) from dual;
SQL> select sqrt(56) from dual;
SQL> select sin(180) from dual;
SQL> select trunc(5,3) from dual;
SQL> select initcap(name) from mark;
SQL> select upper(name) from mark;
SQL> select lower(name) from mark;
SQL> select chr(255) from dual;
SQL> select 'Micro'||'soft' from dual;
SQL> select length(name) from mark where rno=5;
SQL> select sysdate from dual;
SQL> select next_day(sysdate,'monday') from dual;
SQL> select months_between('05-jan-2013','05-jan-2014') from dual;
SQL> select last_day(sysdate) from dual;
SQL> select add_months(sysdate,500) from dual;
SQL> select greatest('23-aug-2014','31-aug-2103') from dual;

RESULT
Thus the SQL operators and functions were executed and the output was verified.
39 | P a g e
Ex: No:3(b) DATABASE QUERIES –NESTED QUERIES& SUB QUERIES

AIM:
To execute and verify the SQL commands for sub Queries& Nested Queries.

DESCRIPTION:

Sub queries are used to retrieve data from table that depend on the values in the table [Link]
queries can also return more than one value. The following operators can be used between the
comparison operators and sub query. The operators are ‘any’, ‘all’, ‘in’, ‘not’.

NESTED QUERIES:

SQL> select * from sstud1;

SNAME PLACE

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

prajanchennai

anandchennai

kumarchennai

ravi Chennai

SQL> select * from sstud2;

SNAME DEPT MARKS

-------------------- ---------- ---------
prajancse 700
anand it 650
vasucse 680
ravi it 600

40 | P a g e
SQL> select sname from sstud1 where [Link] in ( select [Link] from sstud2 );
SNAME
--------------------
anand
prajan
ravi
SQL> select sname from sstud1 where [Link] not in ( select [Link] from sstud2 );
SNAME
--------------------
kumar
SQL> select sname from sstud2 where marks >some(select marks from sstud2 where dept='cse');
SNAME
--------------------
prajan
SQL> selectsname from sstud2 where marks >= some (select marks from sstud2 where dept='cse' );
SNAME
--------------------
prajan
vasu
SQL> select sname from sstud2 where marks > any ( select marks from sstud2 where dept='cse' );
SNAME
--------------------
prajan
SQL> select sname from sstud2 where marks >= any ( select marks from sstud2 where dept='cse' );
SNAME
--------------------
prajan
vasu
SQL> select sname from sstud2 where marks > all ( select marks from sstud2 where dept='cse' );
no rows selected
SQL> select sname from sstud2 where marks < all ( select marks from sstud2 where dept='cse' );
SNAME

41 | P a g e
--------------------
anand
ravi
SQL> select sname from sstud1 where exists ( select [Link] from sstud2 where
[Link]=[Link] );
SNAME
--------------------
prajan
anand
ravi
SQL> select sname from sstud1 where not exists ( select [Link] from sstud2 where
[Link]=[Link] );
SNAME
--------------------
kumar

RESULT
The Nested Queries & Sub queries using DML commands were executed and the output was
verified.

42 | P a g e
 Ex: No: 3(c) DATABASE QUERIES - JOINS

AIM:
To relate data through joins using arithmetic and group expressions in Oracle.

DESCRIPTION:

A Join is actually performed by a where clause which combines the specified rows of a table.

TYPES OF JOINS:

 Simple Join
 Self Join
 Outer Join
 Create two tables with necessary fields.

SIMPLE JOIN:

It is the most common type of join which retrieves the rows from the two tables having a
common column.

EQUI JOIN:

To join (concatenate) two tables using Equi Join – Join using Equality Operator.

Syntax:select [Link], [Link] from table1, table2 where [Link]=[Link];

NON EQUI-JOIN:

To join two tables using Non-Equi Join – Join Using Inequality Operators like <, <=,>,>=,!=.
It specifies relationship between columns belonging to different tables by making use of relational
operators.
Syntax: select [Link], table2. fields from table1, table2 where [Link]<[Link];

SELF JOIN:

To join a table to itself using Self join – Joining a table to itself by creating aliases.

43 | P a g e
It can compare each row of the table to itself and also with the rows of the same table.

Syntax: select a.field1, b.field2 from table1 a, table1 b where a.field1<b.field2;

OUTER JOIN:

To join two tables using Outer Join – Equi or Non Equi join with non-matching records. (+) –
denotes the table from which non matching records are to be retrieved.

It extends the result of simple join. It returns all rows returned by a simple join as well as those
rows from any table that do not match any row from other table.

Syntax: select table1. fields, [Link] from table1, table2 where [Link] (+) < [Link];

IMPLEMENTATION:

SQL> select * from details;

ROLL NAME CITY

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

101 Asha CBE

102 Banu TVL

103 Chitra Delhi

SQL> select * from marks;

ROLL MARK1 MARK2

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

101 90 30

102 78 90

103 56 46

44 | P a g e
 104 20 20

SQL> select * from details s,marks u where [Link]=[Link];

ROLL NAME CITY ROLL MARK1 MARK2

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

101 Asha CBE 101 90 30

102 Banu TVL 102 78 90

103 Chitra Delhi 103 56 46

SQL> select * from details s,marks u where [Link]<[Link];

ROLL NAME CITY ROLL MARK1 MARK2

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

101 Asha CBE 102 78 90

101 Asha CBE 103 56 46

102 Banu TVL 103 56 46

101 Asha CBE 104 20 20

102 Banu TVL 104 20 20

103 Chitra Delhi 104 20 20

6 rows selected.

45 | P a g e
SQL> select * from details s,marks u where [Link]=[Link](+);

ROLL NAME CITY ROLL MARK1 MARK2

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

101 Asha CBE 101 90 30

102 Banu TVL 102 78 90

103 Chitra Delhi 103 56 46

SQL> select * from details s,marks u where [Link](+)=[Link];

ROLL NAME CITY ROLL MARK1 MARK2

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

101 Asha CBE 101 90 30

102 Banu TVL 102 78 90

103 Chitra Delhi 103 56 46

104 20 20

SQL> select * from marks u,marks s where [Link]=u.mark1;

no rows selected

46 | P a g e
SQL> select * from marks u,marks s where s.mark1=u.mark2;

ROLL MARK1 MARK2 ROLL MARK1 MARK2

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

104 20 20 104 20 20

102 78 90 101 90 30

RESULT:

Thus the different types of joins are studied and implemented in Oracle.

47 | P a g e
Ex:No: 4 DATABASE VIEWS, SYNONYMS, SEQUENCES AND INDEX

AIM:
To create views, synonyms, sequences, indexes and save points using DDL, DML and DCL
statements

DESCRIPTION:

VIEWS:

A view is the tailored presentation af data contained in one or more table and can also be said as
restricted view to the datas in the tables. A view is a “virtual table” or a “stored query” which takes the
output of a query and treats it as a table. The table upon which a view is created is called as base table.

A view is a logical table based on a table or another view. A view contains no data of its own but is like
a window through which data from tables can be viewed or changed. The tables on which a view is
based are called base tables. The view is stored as a SELECT statement in the data dictionary

ADVANTAGES OF A VIEW:

[Link] level of table security.

[Link] data complexity.

[Link] the usage by combinig multiple tables into a single table.

[Link] in different perspective.

SYNTAX:

Create [or replace ] view <view name> [column alis names] as <query> [with <options>

conditions];

EXAMPLE:

Create or replace view empview as select * from emp;

48 | P a g e
TYPES OF VIEW:

HORIZONTAL -enforced by where cause

VERTICAL - enforced by selecting the required columns

TYPES OF VIEWS

• Updatable views – Allow data manipulation

• Read only views – Do not allow data manipulation

TO CREATE THE TABLE ‘FVIEWS’

SQL> create table fviews( name varchar2(20),no number(5), sal number(5), dno number(5));

Table created.

SQL> insert into fviews values('xxx',1,19000,11);

1 row created.

SQL> insert into fviews values('aaa',2,19000,12);

1 row created.

SQL> insert into fviews values('yyy',3,40000,13);

1 row created.

SQL> select * from fviews;

NAME NO SAL DNO

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

xxx 1 19000 11

aaa 2 19000 12

yyy 3 40000 13

TO CREATE THE TABLE ‘DVIEWS’

SQL> create table dviews( dno number(5), dname varchar2(20));

Table created.

SQL> insert into dviews values(11,'x');

1 row created.

49 | P a g e
SQL> insert into dviews values(12,'y');

1 row created.

SQL> select * from dviews;

DNO DNAME

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

11 x

12 y

CREATING THE VIEW ‘SVIEW’ ON ‘FVIEWS’ TABLE

SQL> create view sview as select name,no,sal,dno from fviews where dno=11;

View created.

SQL> select * from sview;

NAME NO SAL DNO

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

xxx 1 19000 11

Updates made on the view are reflected only on the table when the structure of the table and the view are not
similar -- proof

SQL> insert into sview values ('zzz',4,20000,14);

1 row created.

SQL> select * from sview;

NAME NO SAL DNO

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

xxx 1 19000 11

SQL> select * from fviews;

NAME NO SAL DNO

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

xxx 1 19000 11

aaa 2 19000 12

50 | P a g e
yyy 3 40000 13

zzz 4 20000 14

Updates made on the view are reflected on both the view and the table when the structure of the table and the
view are similar – proof

CREATING A VIEW ‘IVIEW’ FOR THE TABLE ‘FVIEWS’

SQL> create view iview as select * from fviews;

View created.

SQL> select * from iview;

NAME NO SAL DNO

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

xxx 1 19000 11

aaa 2 19000 12

yyy 3 40000 13

zzz 4 20000 14

PERFORMING UPDATE OPERATION

SQL> insert into iview values ('bbb',5,30000,15);

1 row created.

SQL> select * from iview;

NAME NO SAL DNO

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

xxx 1 19000 11

bbb 5 30000 15

SQL> select * from fviews;

NAME NO SAL DNO

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

xxx 1 19000 11

51 | P a g e
aaa 2 19000 12

yyy 3 40000 13

zzz 4 20000 14

bbb 5 30000 15

CREATE A NEW VIEW ‘SSVIEW’ AND DROP THE VIEW

SQL> create view ssview( cusname,id) as select name, no from fviews where dno=12;

View created.

SQL> select * from ssview;

CUSNAME ID

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

aaa 2

SQL> drop view ssview;

View dropped.

TO CREATE A VIEW ‘COMBO’ USING BOTH THE TABLES ‘FVIEWS’ AND ‘DVIEWS’

SQL> create view combo as select name,no,sal,[Link],dname from fviews,dviews where

[Link]=[Link];

View created.

SQL> select * from combo;

NAME NO SAL DNO DNAME

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

xxx 1 19000 11 x

aaa 2 19000 12 y

TO PERFORM MANIPULATIONS ON THIS VIEW

SQL> insert into combo values('ccc',12,1000,13,'x');

insert into combo values('ccc',12,1000,13,'x')

ERROR at line 1:

52 | P a g e
ORA-01779: cannot modify a column which maps to a non key-preserved table

This shows that when a view is created from two different tables no manipulations can be performed using
that view and the above error is displayed.

Synonyms

 A synonym is an alias, that is, a form of shorthand used to simplify the task of referencing a
database object.
 There are two categories of synonyms, public and private.
 A public synonym can be accessed by any system user.
 The individual creating a public synonym does not own the synonym – rather, it will belong to
the PUBLIC user group that exists within Oracle.
 Private synonyms, on the other hand, belong to the system user that creates them and reside in
that user's schema.
 A system user can grant the privilege to use private synonyms that they own to other system
users.
 In order to create synonyms, we will need to have the CREATE SYNONYM privilege.
 This privilege will be granted to us by the DBA.
 We must have the CREATE PUBLIC SYNONYM privilege in order to create public synonyms.
 If we own a synonym, we have the right to drop (delete) the synonym. The DROP SYNONYM
command is quite simple.
 DROP SYNONYM synonym_name;
 In order to drop a public synonym we must include the PUBLIC keyword in the DROP
SYNONYM command.
 In order to drop a public synonym, we must have the DROP PUBLIC SYNONYM privilege.
 DROP PUBLIC SYNONYM synonym_name;

Examples:
SQL> select * from class;

NAME ID
---------- ----------
anu 1
brindha 2
chinthiya 3
divya 4
ezhil 5
fairoz 7
hema 9

7 rows selected.

53 | P a g e
Create synonym:
SQL> create synonym c1 for class;

Synonym created.
SQL> insert into c1 values('kalai',20);

1 row created.

SQL> select * from class;

NAME ID
---------- ----------
anu 1
brindha 2
chinthiya 3
divya 4
ezhil 5
fairoz 7
hema 9
kalai 20

8 rows selected.

SQL> select * from c1;

NAME ID
---------- ----------
anu 1
brindha 2
chinthiya 3
divya 4
ezhil 5
fairoz 7
hema 9
kalai 20

8 rows selected.
SQL> insert into class values('Manu',21);

1 row created.
54 | P a g e
 SQL> select * from c1;

NAME ID
---------- ----------
anu 1
brindha 2
chinthiya 3
divya 4
ezhil 5
fairoz 7
hema 9
kalai 20
Manu 21

9 rows selected.
Drop Synonym:
SQL> drop synonym c1;

Synonym dropped.

SQL> select * from c1;

select * from c1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Sequences

 Oracle provides the capability to generate sequences of unique numbers, and they are called
sequences.
 Just like tables, views, indexes, and synonyms, a sequence is a type of database object.
 Sequences are used to generate unique, sequential integer values that are used as primary key
values in database tables.
 The sequence of numbers can be generated in either ascending or descending order.

Creation of table:
SQL> create table class(name varchar(10),id number(10));
Table created.

Insert values into table:

55 | P a g e
SQL> insert into class values('&name',&id);
Enter value for name: anu
Enter value for id: 1
old 1: insert into class values('&name',&id)
new 1: insert into class values('anu',1)

1 row created.

SQL> /
Enter value for name: brindha
Enter value for id: 02
old 1: insert into class values('&name',&id)
new 1: insert into class values('brindha',02)

1 row created.

SQL> /
Enter value for name: chinthiya
Enter value for id: 03
old 1: insert into class values('&name',&id)
new 1: insert into class values('chinthiya',03)

1 row created.

SQL> select * from class;

NAME ID
---------- ----------
anu 1
brindha 2
chinthiya 3

Create Sequence:
SQL> create sequence s_1
2 start with 4
3 increment by 1
4 maxvalue 100
5 cycle;

Sequence created.
SQL> insert into class values('divya',s_1.nextval);
56 | P a g e
 1 row created.

SQL> select * from class;

NAME ID
---------- ----------
anu 1
brindha 2
chinthiya 3
divya 4
Alter Sequence:
SQL> alter sequence s_1
2 increment by 2;

Sequence altered.

SQL> insert into class values('fairoz',s_1.nextval);

1 row created.

SQL> select * from class;

NAME ID
---------- ----------
anu 1
brindha 2
chinthiya 3
divya 4
ezhil 5
fairoz 7

Drop Sequence:
SQL> drop sequence s_1;

Sequence dropped.

Indexes

57 | P a g e
  An index can be created in a table to find data more quickly and efficiently.
 The users cannot see the indexes; they are just used to speed up searches/queries.
 Updating a table with indexes takes more time than updating a table without; because the
indexes also need an update. So we should only create indexes on columns (and tables) that will
be frequently searched against.
Syntax:
Create Index:
CREATE INDEX index_name ON table_name (column_name)

SQL> create table splr(sname varchar(10),sid number(10),scity varchar(10));

Table created.

SQL> insert into splr values('hcl',01,'chennai');

1 row created.

SQL> insert into splr values('dell',04,'madurai');

1 row created.

SQL> insert into splr values('HP',02,'kovai');

1 row created.

SQL> insert into splr values('Lenovo',03,'trichy');

1 row created.

SQL> select * from splr;

SNAME SID SCITY

---------- ---------- ----------
hcl 1 chennai
dell 4 madurai
HP 2 kovai
Lenovo 3 trichy

SQL> create index sp1 on splr(sid);

Index created.
58 | P a g e
 SQL> create index sp2 on splr(sid,scity);

Index created.
Drop Index:
SQL> drop index sp1;

Index dropped.

SQL> drop index sp2;

Index dropped.

RESULT:

Thus the database views.,synonyms,sequences and indexare studied and implemented in Oracle.

Ex: No :5 PL/SQL PROGRAMMING

59 | P a g e
 AIM:
To write a PL/SQL program for reversing an integer number.

DESCRIPTION:

Syntax: declare
<declaration/initialisation>statements;
begin
<conditional control statements>;
<Iteration Statements>;
end;
ALGORITHM:

 Start
 Declare the variables.
 erform the process using sql functions & constructs.
 Display the result.
 Stop.
PROGRAM:

declare
n number;
temp number;
rem number;
rev number:=0;
begin
dbms_output.put_line(‘Enter the number to be reversed’);
n:=&n;
temp:=n;
if n > 0 then
while temp > 0

60 | P a g e
 loop
rem:=mod(temp,10);
rev:=rev*10+rem;
temp:=floor(temp/10);
end loop;
dbms_output.put_line(‘The reversed number’|| rev);
else
dbms_output.put_line(‘Invalid’);
end if;
end;
OUTPUT:

> set serveroutput on;

>@ [Link];
Enter the number to be reversed : 3452
The reversed number is 2543.
>@ [Link];
Enter the number to be reversed : 0
Invalid.

AIM:

61 | P a g e
 To write a PL/SQL program to print the details of the employee whose salary is greater than

the given salary.

DESCRIPTION:

%type : It can be used to assign an attribute to a variable.

%rowtype : All the attributes in the relation can be placed in a variable using %rowtype.

PROGRAM:

declare
s [Link]%type;
[Link]%type;
low exception;
begin
s:=&s;
select salary into sal from emp where ssn= s;
if sal<5000 then
raise low;
else
dbms_output.put_line(‘Salary of’||s||’is’||sal);
end if;
exception
when low then
dbms_output.put_line(‘low salary’);
when no_data_found then
dbms_output.put_line(‘No employee found’);
end;
OUTPUT:

62 | P a g e
> set serveroutput on;
>@ [Link];
Enter the value for s: 12345
old s:=&s;
new s:=12345
Salary of 12345 is 20000
PL/SQL procedure successfully completed.
>@ [Link];
Enter the value for s: 12456
old s:=&s;
new s:=12456
low salary
PL/SQL procedure successfully completed.
>@ [Link];
Enter the value for s: 124
old s:=&s;
new s:=124
No employee found
PL/SQL procedure successfully completed.

RESULT:

Thus the PL/SQL Program to print the details of the employee whose salary is greater
than 5000 andfor reversing an integer number are executed and the output is verified.

Ex: No:6 (a) CURSOR MANAGEMENT & EXCEPTION HANDLING

63 | P a g e
 AIM:

To write a PL/SQL program to update the grade of the students using cursors.

DESCRIPTION:

A cursor is a handle or a pointer to context area. Through the cursor, a PL/SQL program can
control the context area and what happens to it after processing the statement.

Types of Cursors:

 Static
 Dynamic
 REF cursors
Static Cursors:

It is a cursor whose select statements are known at compile time.

Types:

 Explicit Cursor – Cursor name is assigned and processing takes place in 4 steps.
 Implicit Cursor – It is taken care by PL/SQL.
Dynamic Cursors:

It is implemented through built-in packages.(DBMS_SQL)

REF Cursors:

Types:

 Strong Cursor
 Weak Cursor

Explicit Cursors:

64 | P a g e
 A cursor variable is a reference type (pointer). It can name different storage locations as the
program runs. The set of rows returned by a query is called an Active set. The set of rows returned by a
query can contain zero or more multiple rows as defined by the query. There are four processing steps.

 Cursor declaration
 Open
 Fetch
 Close
Cursor Declaration:

cursor <cursor_name> is <query>;

Open:It executes the query identifier, sets the active set open and positions the cursor to point to the
first row.

open <cursor_name>;

Fetch:Retrieves the current row and advances the cursor to the next row. To fetch the remaining rows ,
fetch statement should be used in a loop.

fetch <cursor_name> into <variable_name>;

Close:After processing the last row, the cursor is disabled.

close <cursor_name>;

Cursor Options:

%FOUND - It is true if fetch succeeds in returning a row.

%NOTFOUND - It indicates whether fetch statement returns row from active set.
If it fails, %notfound evaluates to true.

%ROWCOUNT - Returns the number of rows fetched.(It is initially zero)

%ISOPEN - If the cursor is already open, it evaluates to true, else false.

PROGRAM:

65 | P a g e
declare
var marks %rowtype;
tot number;
per number;
cursor c is select * from marks;
begin
if c%isopen then
dbms_output.put_line('cursor already open');
else
open c;
end if;
dbms_output.put_line(' Rollno Name Percent Grade');
loop
fetch c into var;
tot:=var.m1+var.m2+var.m3;
per:=floor((tot/300)*100);
if per>=90 then
update marks set grade='A' where rno=[Link];
dbms_output.put_line([Link]|| ' '||[Link]||' '||per ||' '|| [Link]);
elsif per>=75 and per<90 then
update marks set grade='B' where rno=[Link];
dbms_output.put_line([Link]|| ' '||[Link]||' '||per ||' '|| [Link]);
elsif per>=50 and per<75 then
update marks set grade='C' where rno=[Link];
dbms_output.put_line([Link]|| ' '||[Link]||' '||per ||' '|| [Link]);
else
update marks set grade='D' where rno=[Link];

66 | P a g e
 dbms_output.put_line([Link]|| ' '||[Link]||' '||per ||' '|| [Link]);
end if;
exit when c %NOTFOUND;
end loop;
dbms_output.put_line('There are ' ||C %ROWCOUNT || ' rows');
close c;
end;
/

OUTPUT:

SQL> select * from marks;

RNO NAME M1 M2 M3 GRA
---------- --------- ---------- ---------- --------- -----------
101 Anu 90 90 90 A
102 Baskar 25 25 25 D
103 Chitra 85 85 85 B
104 Sankar 100 100 100 A
105 Jegan 55 60 65 C
SQL> @ [Link]
Rollno Name Percent Grade
101 Anu 90 A
102 Baskar 25 D
103 Chitra 85 B
104 Sankar 100 A
105 Jegan 60 C

There are 5 rows

PL/SQL procedure successfully completed.

67 | P a g e
RESULT:

Thus the PL/SQL program to update the grade of a student using cursor is executed.

Ex: No: 6 (b) PROCEDURES AND FUNCTIONS

68 | P a g e
AIM:
To write a PL/SQL Procedure and functions for various applications

PROCEDURE

DESCRIPTION:

Syntax:create or replace procedure <procedure_name> (parameters defn) is

<declaration/initialisation>statements;
begin
<conditional control statements>;
<Iteration Statements>;
end;
ALGORITHM:

Step 1: Begin
Step 2: Declare the local variable.
Step 3: Get the Input.
Step 4: Call the reverse procedure.
Step 5: End
PROCEDURE:

Step 1: Start
Step 2: Declare the local variable.
Step 3: Perform the process using the SQL functions and constructs.
Step 4: Print the result
Step 5: Return.

PROGRAM:
69 | P a g e
Main Program:

declare
n number;
begin
n:=&n;
rev(n);
end;

Procedure:

create or replace rev(n number) is

temp number;
rem number;
rev number:=0;
begin
temp:=n;
if n > 0 then
while temp > 0
loop
rem:=mod(temp,10);
rev:=rev*10+rem;
temp:=floor(temp/10);
end loop;
dbms_output.put_line(‘The reversed number’|| rev);
else
dbms_output.put_line(‘Invalid’);
end if;
end;

70 | P a g e
OUTPUT:
>exec rev(123);
The reversed number is 321.
> @[Link]
Enter the value for n:345
The reversed number is 543.

FUNCTION

DESCRIPTION:

Syntax:create or replace function <function_name> (parameters defn) return

return datatype is
<declaration/initialisation>statements;
begin
<conditional control statements>;
<Iteration Statements>;
return variable;
end;
ALGORITHM:

MAIN PROGRAM:

Step 1: Begin
Step 2: Declare the local variable.
Step 3: Get the Input.
Step 4: Call the function and assign the returned value to a variable.
Step 5: End
PROCEDURE:

Step 1: Start

71 | P a g e
Step 2: Declare the local variable.
Step 3: Perform the process using the SQL functions and constructs.
Step 4: Return the computed factorial value.
PROGRAM:

MAIN PROGRAM:

declare
n number;
f number;
begin
n:=&n;
f:=fact(n);
dbms_output.put_line(‘The factorial of ‘|| n ||’is’||f);
end;
FUNCTION:

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

fa number:=1;
inumber :=1;
begin
while i<=n
loop
fa :=fa*I;
i=i+1;
end loop;
return fa;
end;

OUTPUT:

72 | P a g e
> @ [Link]
Enter the value for n: 3
The factorial of 3 is 6

RESULT:

Thus the PL/SQL Procedure for reversing a number is executed and the output is verified.

Ex: No: 6 (c) TRIGGERS

AIM:

73 | P a g e
 To write a PL/SQL program to invoke triggers.

DESCRIPTION:

Trigger is a stored procedure that is fired when an insert, update or delete statement is issued against the
table.

ADVANTAGES:

 To generate data automatically

 To enforce complex constraints.
 To provide complex security authorizations
 To audit data modification.
PARTS OF A TRIGGER: Trigger has 3 Parts:

 Trigger Statement – It specifies DML Statements such as update, insert, and delete. It fires the trigger
body and specifies the associated table.
 Trigger Body - It is a PL/SQL block that is executed when triggering statement is issued.
 Trigger Restriction – It is specified with when clause in the trigger.

SYNTAX OF A TRIGGER:

create or replace trigger <trigger_name> [before/after] [insert/update/delete]

on <table_name> for each [row/statement] [when<condition>]

declare

begin

..

end;

Note: The function raise_application_error with the parameters error number and error message is used
to generate error message when the trigger is invoked. The error number ranges from –20000 to –
20999.

74 | P a g e
 ALGORITHM:

 Create a trigger for insertion for each row.

 Declare a cursor which contains the roll number field
 Before insertion check if the roll number already exists in the table.
 If it exists raise an application error and display “roll no exists”
 Else perform insertion.

PROGRAM:

create or replace trigger trig1 before insert on student for each row
declare
r [Link]%type;
cursor c is select roll from student;
begin
open c;
loop
fetch c into r;
if :[Link] =r then
raise_application_error(-20005,”Roll number already exists”);
end if;
exit when c%NOTFOUND;
end loop;
close c;
end;
OUTPUT:
SQL>@[Link]
SQL>Trigger created
SQL>insert into student values(61,’Anu’,60,50);

75 | P a g e
 ORA-20005: Roll number already exists

RESULT:

Thus the PL/SQL program to invoke triggers is executed.

Ex: No: 7 (a)DATA BASE DESIGN USING ER DIAGRAM AND NORMALIZATION

AIM:

To study about database design using ER Diagram and normalization for employee database.

76 | P a g e
 DESCRIPTION:

ER DIAGRAM:

Entity relationship diagram is a graphical representation of relationship among entities.

ENTITY:

It is an object with physical or conceptual existence. It is notated as with Capitalized letters

representing the name of the entity.

ATTRIBUTES: Attributes are the properties or characteristics that describe an entity.

RELATIONSHIP:

It is a set of relationship of same type (or) it is a relationship among two entity types.

It is notated as

ADVANTAGES OF ER MODEL:

 It gives much flexibility in designing a data base schema and to model a database.
DATABASE NORMALIZATION:

The process of reducing data redundancy is known as normalization.

NORMALISING DATABASE:

Raw Database:Massive collection of data is done. These data are not in manageable order. A database
that is not normalized may contain data that resides in one or more different tables for no apparent
reason. This could be barred for security reasons, disk usage, speed of execution and efficiency of
database updates and data integrity.
Logical Database Design:I It is a process of arranging data into logical organized groups that can be
easily maintained. Organized and referenced by the end user. This is the first step in providing database
environment.

NEEDS OF USER:

77 | P a g e
 The end user is the people who use the database. Input from the end users should be considered in order
to achieve a well-designed database. There should be a good user’s front-end tool with the database that
could provide optimal performance.

DATA REDUNDANCY:

The data should not be redundant.(ie.,) the duplication of data should be kept minimum.

NORMAL FORMS:

These are the ways of measuring the level and depth to which the database has been normalized.

The most three common normal forms are

 First Normal Form (I NF)

 Second Normal Form (II NF)
 Third Normal Form (III NF)

Each subsequent normal form depends upon the normalization steps taken in previous normal form.

Raw
Database

78 | P a g e
 I Normal

Form

II Normal

Form

III Normal

Form

Fig1. Normalization Process.

FIRST NORMAL FORM (I NF)

The objective of I NF is to divide the base data into logical units called tables. A primary key is
assigned to most of the tables.
SECOND NORMAL FORM (I NF)

The objective of 2NF is to take the data that is only partly dependent on the primary key and
store that data in another table.

THIRD NORMAL FORM (I NF)

The objective of 3NF is to remove the data in the table that is not dependent on the primary key.

BENEFITS OF NORMALIZATION:

 Create overall database organization

 Reduction of redundant data.
 Data consistency within database

79 | P a g e
 A much more flexible database design
 A better handle on database security.
DENORMALISATION:

It is the process of taking a normalized database and modifying the table structures to allow
controlled redundancy for increased database performance.

1NF:

Table :

EMPLOYEE CUSTOMER

Emp_id Cust_id

Address address

City city

State state

Pincode pincode

Phone phone

Dept fax

Position contact no

Salary Cust_name

Join_date

Emp_name

2NF:

EMPLOYEE CUSTOMER

Emp_id Cust_id

80 | P a g e
 Address address

City city

State state

Pincode pincode

Phone phone

fax

position

EMP_WORK Cust_name

Emp_id

Dept

contact no

Salary ,

Join_date

81 | P a g e
RESULT:

Thus the Data base design using ER model and Normalization is executed.

Ex: No: 7 (b) DATABASE CONNECTIVITY WITH FRONT END TOOL

AIM:

To provide Database connectivity with Front End Tools

82 | P a g e
 STEPS:

 Select a new standard exe project.

 In the ‘Project’ menu select ‘Components’.
 In the ‘Component’ box select ‘Microsoft ADO Data Control 6.0(OLEDB)’ and click ‘ok’. This will
add the control to the tool box.
 Add the ADO Data control to the bottom of the form and set its caption to ‘Employee Details’
 Right click the ADO Data Control and choose ADODC properties.
 In the screen that appears, choose ‘Use Connection String’ option. Click the ‘Build’ button to start
building a connection string. The data link properties screen appears.
 Click ‘ Microsoft OLEDB provider for ORACLE’ to use this driver to connect to the database. Click
the ‘Next’ button.
 The ‘Connection’ tab becomes active. Provide the server and username with password.
 Click the ‘Test connection’ button. On successful connection, a confirmation message appears. Click
the ‘ok’ button.

In the ‘Property Page’, click the ‘Authentication’ tab and provide the username along with password.
 Click the ‘Record Source’ property to specify the name of the table. A dialog box appears.
 In the ‘Command type’ list box, select ‘2-adcmdTable’.
 Select the table name ‘EMP’ from column ‘Table or Stored Procedure name’.
 Close the dialog box by clicking the ‘ok’ button.
Place the textboxes corresponding to the number of fields in the table. Set its ‘Data Source Property’ to
its corresponding Data control name. (ADODC1). The data field is set to the corresponding field of the
table.

Note:

1. The records can be added, updated and deleted using the following statements:

‘[Link]’ ,’[Link]’,’[Link]’.

83 | P a g e
[Link] browse over the records of the table, we use‘[Link]’ ,
’[Link]’, ’[Link]’ .

RESULT:

Thus the Database Connectivity with Front End Tools is executed.

Ex: No: 8 (a) DESIGN AND IMPLEMENTATIONOFPAYROLL PROCESSING SYSTEM

AIM:

To develop and implement a payroll processing system using visual basic with Oracle as backend.

DESCRIPTION:

84 | P a g e
 The payroll processing system is used to maintain employee information and calculate the
monthly pay for employees. It is used for efficient employee database administration. The details about
the employee ,department and salary is maintained in the database. This is the user friendly system for
database maintenance.

MODULES AND FORMS:

[Link] form

[Link] administration form

[Link] calculation form

TABLES:

[Link] table

[Link] table

[Link]-DETAILS table

[Link] table

EMPLOYEE:

Fieldname Datatype

Ssn number(2)

85 | P a g e
Name varchar2(20)

Dob date

Desgination varchar2(18)

Deptnonumber(2)

Salary number(8)

SALARY –DETAILS:

Fieldname Datatype

Ssn number(2)

Desgn number(7,2)

Ta number(7,2)

Hra number(7,2)

Other allow number(7,2)

Gpf number(7,2)

It number(7,2)

Basic number(7,2)

DEPARTMENT:

Fieldname Datatype

86 | P a g e
Dno number(2)

Dname varchar2(20)

Dmgrssn number(6)

Dlocn varchar2(20)

MONTH-SALARY:

Fieldname Datatype

Ssn number(2)

Name varchar2(20)

Basic number(7,2)

Ta number(7,2)

Hra number(7,2)

Other allow number(7,2)

Gpf number(7,2)

It number(7,2)

Total number(5)

Month varcahr2(15)

Code:

Login Form:

Public LoginSucceeded As Boolean

Private Sub cmdCancel_Click()

'set the global var to false

87 | P a g e
 'to denote a failed login

LoginSucceeded = False

[Link]

End Sub

Private Sub cmdOK_Click()

'check for correct password

If txtUserName = "Administrator" And txtPassword = "1" Then

'place code to here to pass the

'success to the calling sub

'setting a global var is the easiest

LoginSucceeded = True

[Link]

[Link]

Else

If txtUserName = "User" And txtPassword = "1" Then

'place code to here to pass the

'success to the calling sub

'setting a global var is the easiest

LoginSucceeded = True

[Link]

[Link]

Else

MsgBox "Invalid Password, Try Again!", , "Login"

[Link]

SendKeys "{Home}+{End}"

88 | P a g e
 End If

End If

End Sub

ADMIN FORM:

MOVE FIRST:

Private Sub Command1_Click()

If [Link] Then

[Link]

Else

[Link]

End If

end sub

MOVE PREVIOUS:

Private Sub Command2_Click()

If [Link] Then

[Link]

Else

[Link]

End If

End Sub

Move Next:

Private Sub Command3_Click()

If [Link] Then

[Link]

Else

89 | P a g e
[Link]

End If

End Sub

MOVE LAST:

Private Sub Command4_Click()

If [Link] Then

[Link]

Else

[Link]

End If

End Sub

INSERT:

Private Sub Command5_Click()

[Link]

End Sub

UPDATE:

Private Sub Command6_Click()

[Link]

End Sub

DELETE:

Private Sub Command7_Click()

[Link]

End Sub

EXIT:

90 | P a g e
Private Sub Command9_Click()

End

End Sub

91 | P a g e
92 | P a g e
93 | P a g e
94 | P a g e
`

RESULT:

Thus a simple payroll processing system is designed and implemented using visual basic with
Oracle as backend.

95 | P a g e
Ex: No: 8 (b) DESIGN AND IMPLEMENTATION OF BANKING SYSTEM

AIM:

To develop a module for Banking System using visual basic with Oracle as backend.

DESCRIPTION:

The Banking System is used to maintain Account information and calculate the Interest for
Customers. It is used for Efficient Banking database administration. The details about the customer
Account Number and Details of the customers are maintained in the database. This is the user friendly
system for database maintenance.

MODULES AND FORMS:

1. Login Form

2. Database Administrator Form

3. Guest Form

TABLES:

1. CUSTOMER Table

Customer:

Fieldname Datatype

Account Number number(2)

Account Name varchar2(20)

Address varchar2(25)

Pincode number(6)

Phone number(10)

Balance number(20)

96 | P a g e
Code:

Login Form:

Private Sub cmdCancel_Click()

End

End Sub

Private Sub cmdOK_Click()

If (txtUserName = "administrator" And txtPassword = "ebin") Then

[Link]

[Link]

ElseIf (txtUserName = "guest" And txtPassword = "") Then

[Link]

[Link]

Else

MsgBox "Invalid Password, try again!", , "Login"

[Link] = ""

[Link]

End If

End Sub

Private Sub Form_Activate()

[Link]

[Link] = ""

txtPassword = ""

End Sub

97 | P a g e
Administrator Form:

Private Sub Command1_Click()

[Link]

End Sub

Private Sub Command2_Click()

[Link]

MsgBox "Record Modified", , "Message"

End Sub

Private Sub Command3_Click()

[Link]

MsgBox "Record Deleted", , "Message"

[Link]

End Sub

Private Sub Command4_Click()

[Link]

End Sub

Private Sub Command5_Click()

If ([Link] = True) Then

[Link]

Else

[Link]

End If

End Sub

Private Sub Command6_Click()

If ([Link] = True) Then

98 | P a g e
[Link]

Else

[Link]

End If

End Sub

Private Sub Command7_Click()

[Link]

End Sub

Private Sub Command8_Click()

[Link]

[Link]

End Sub

Private Sub Form_Activate()

[Link]

[Link] = False

End Sub

Guest Form:

Private Sub Command1_Click()

[Link]

[Link]

End Sub

99 | P a g e
Login Form:

Administrator Form:

100 | P a g e
Guest Form:

RESULT:

Thus a simple Banking system is designed and implemented using visual basic with Oracle as
backend.

101 | P a g e
 Ex: No: 8 (c) DESIGN AND IMPLEMENTATION OF LIBRARY MANAGEMENT

AIM:

To develop a module for Library Management using Visual Basic with the backend database as
oracle.

DESCRIPTION:

Library Management program is used to maintain the library information. This is used to
maintain all the book details and student details. We make use of the books details table. We can select
and manipulate all the records of those tables. So that insert, delete and update operations can be done
using this program . Let us see what are all the components used in this program and how to make the
settings.

COMPONENTS AND REFERENCES:

In this mini project we have not used any external components. Only some DLL files are [Link]
stands for Dynamic Linking Library. They are available on the references option on VB. To select them
do the following:

Start VB 6.0go to Project menuselect References option

Then the references window [Link] that we have to select the following references
for our project.

i)Visual Basic for Applications

ii)Visual Basic run time objects and procedures

iii)Visual Basic objects and procedures

iv)Microsoft ActiveX DataObjects 2.5 Library

The components used are only common objects. They are

 Label
 Textbox
 Command button
 Frame
 Combo box
 Menus

102 | P a g e
Modules And Frames:

*This form is used for user login: This consists of fields like username and password. There is a
Label box given for each field in login form. Here the user verification can be done .

* This is the form used for book details maintenance: This form contains book details such as: Book
no., Book name, Author and Is the book available. There is a Label box given for each book field
details. We can get the details of a particular book from this book [Link] we can add, delete or edit
the book details.

Table Details:

Books table:

Field Name Data Type Size Constraint Description

BookNo Number 2 Primary Key Book number

Bookname Varchar2 30 Book Name

Author Varchar2 30 Author Name

IsAvail Varchar2 5 Is Book Available

Yes-Book
Available

No-Book not
available

Insert :

Private Sub Command1_Click()

[Link]

MsgBox "Records successfully added"

End Sub

103 | P a g e
Edit:

Private Sub Command2_Click()

If [Link] = ([Link](0)) Then

[Link](0) = [Link]

[Link](1) = [Link]

[Link](2) = [Link]

[Link](3) = [Link]

[Link]

End If

MsgBox ("Record Modified")

End Sub

Delete :

Private Sub Command3_Click()

[Link]

Dim n, i As Integer

i=0

n = Val([Link])

Do While Not [Link]

If n = ([Link](0)) Then

i=1

[Link]

[Link] = " "

[Link] = " "

104 | P a g e
[Link] = " "

[Link] = " "

MsgBox "Record Deleted"

Exit Do

End If

[Link]

Loop

If i = 0 Then

[Link] = " "

[Link] = " "

[Link] = " "

[Link] = " "

[Link] = " "

MsgBox "Record not founded"

End If

End Sub

Search:

Private Sub Command4_Click()

[Link]

Dim n, i As Integer

i=0

n = Val(InputBox("Enter the Book No"))

Do While Not [Link]

If (n = ([Link](0))) Then

i=1

105 | P a g e
[Link] = [Link](1)

[Link] = [Link](2)

[Link] = [Link](3)

MsgBox "Record search successful"

Exit Do

End If

[Link]

Loop

If i = 0 Then

[Link] = " "

[Link] = " "

[Link] = " "

[Link] = " "

MsgBox "Record not founded"

End If

End Sub

Move First:

Private Sub Command8_Click()

[Link]

End Sub

Move Previous:

Private Sub Command9_Click()

[Link]

[Link] = True

If [Link] Then

106 | P a g e
MsgBox "First record reached"

[Link] = False

Else

End If

End Sub

Move Last:

Private Sub Command7_Click()

[Link]

End Sub

Move Next:
Private Sub Command10_Click()

[Link] = True

[Link]

If [Link] Then

MsgBox "Last record"

[Link] = False

Else

End If

End Sub

End:

Private Sub Command5_Click()

End

End Sub

107 | P a g e
RESULT:

Thus a simple Library Management system is designed and implemented using visual basic with
Oracle as backend.

108 | P a g e
Ex: No: 9PACKAGES

AIM:

To design a package to find the sum, difference, product, quotient of two numbers.

DESCRIPTION:

Syntax:

Package:

Create or replace package package_name is

procedure procedure_name1(argument, datatype …..)

procedure procedure_namen(argument, datatype …..)

function function_name1(argument, datatype ….)

function function_namen(argument, datatype ….)

end package_name;

Package body:

create or replace package body package_name as

procedure procedure_name1(argument, datatype…) is

declarations

begin

executable statements;

end procedure_name1;

…..

procedure procedure_namen(argument, datatype…) is

declarations

109 | P a g e
begin

executable statements;

end procedure_name2;

function function_name1(argument, datatype…) return data type is

declarations

begin

executable statements;

end function_name1;

…..

function function_namen(argument, datatype…) return data type is

declarations

begin

executable statements;

end function_namen;

end package_name;

Main program:

declare

local declarations;

begin

package_name.procedure_name1(arguments);

….

package_name.procedure_namen(arguments);

var1=package_name.function_name1(arguments);

…..

110 | P a g e
 varn= package_name.procedure_namen(arguments);

end;

ALGORITHM:

 Start
 Create the package and package body.
 Write a PL/SQL program to call the functions and procedures in the package.
 Display the result
 Stop.

PROGRAM:

Package specification:

create or replace package pack is

procedure add(m number,n number);

procedure sub(m number,n number);

function mul(m number, n number);

function div(m number,n number);

Package Body:

create or replace package body pack as

C number;

procedure add(m number,n number) is

begin

C:=m+n;

dbms_output.put_line(‘sum is’|| C);

end add;

procedure sub(m number,n number) is

begin

C:=m - n;

111 | P a g e
 dbms_output.put_line(‘Difference is’|| C);

end sub;

procedure mul(m number,n number) is

begin

C:=m*n;

return C;

end mul;

procedure div(m number,n number) is

begin

C:=floor(m/n);

return C;

end div;

end pack;

Main Program:

declare

d number;

a number;

b number;

begin

a:=&a;

b:=&b;

[Link](a,b);

[Link](a,b);

d:=mul(a,b);

dbms_output.put_line(‘Product is’||d);

112 | P a g e
 d:=div(a,b);

dbms_output.put_line(‘ Quotient is’||d);

end;

OUTPUT:

> @ [Link]

Enter the value for a: 23

Enter the value for b:5

Sum is 28

Difference is 18
Product is 115

Quotient is 3

RESULT:

Thus the package is designed and the procedures and functions in the package are invoked from
the main program and executed successfully.

113 | P a g e
VIVA QUESTIONS

1. Define the terms DDL:

Data base schema is specified by a set of definitions expressed by a special language called
a data definition language.

2. What are the categories of SQL command?

SQL commands are divided in to the following categories:
Data Delimitation language
Data manipulation language
Data control language
Transaction Control Language

3. What is integrity constraint?

An integrity constraint is a mechanism used by oracle to prevent invalid data entry into the table.
It has enforcing the rules for the columns in a table.

4. List the types of constraint.

a) Domain Integrity
b) Entity Integrity
c) Referential Integrity

5. Primary Key Constraint

A primary key avoids duplication of rows and does not allow null values. It can be defined on
one or more columns in a table and is used to uniquely identify each row in a table. These
values should never be changed and should never be null.

6. Referential Integrity
It enforces relationship between tables. To establish parent-child relationship between 2 tables
having a common column definition, we make use of this constraint. To implement this, we
should define the column in the parent table as primary key and same column in the child table
as foreign key referring to the corresponding parent entry.

DML
1. What is DML?

DML commands are the most frequently used SQL commands and is used to query and
manipulate the existing database objects.
2. What are DML command?
Some of the commands are Insert, Select, Update, Delet
3. Give the general form of SQL Queries? Select
A1, A2…………., An From
R,1R2……………, R m Where P

114 | P a g e
 4. What is the use of rename operation?
Rename operation is used to rename both relations and an attributes. It uses the as clause,
taking the form: Old-name as new-name

5. Define tuple variable?

Tuple variables are used for comparing two tuples in the same relation. The tuple
variables are defined in the from clause by way of the as clause.

6. Write the syntax to retrieve specific columns from a table:

Syntax: Select column_name1, …..,column_namen from table name;

DCL:
1. Define DCL?

The DCL language is used for controlling the access to the table and hence securing the
database. DCL is used to provide certain privileges to a particular user. Privileges are rights to
be allocated.

2. List the DCL commands used in data bases

The privilege commands are namely, Grant and Revoke

3. What type of privileges can be granted?

The various privileges that can be granted or revoked are,

o Select
o Insert
o Delete
o Update
o References
o Execute
o All

3. Write the syntax for grant command

Grant <database_priv [database_priv…..] > to <user_name> identified by

<password>[,<password…..];

Grant <object_priv> | All on <object> to <user | public> [ With Grant Option ];

5. What are TCL commands?

*Commit *Rollback *save point

115 | P a g e
FUNCTION:

1. Define function?

Function is a group of code that accepts zero or more arguments and both return one or
more results. Both are used to manipulate individual data items.
2. Write the two types of functions

i. Single row functions

[Link] functions

3. What are single row functions?

A single row function or scalar function returns only one value for every row
queries in table. Single row function can appear in a select command and can also
be
included in a where clause. The single row function can be broadly classified as,
o Date Function o Numeric Function
o Character Function o Conversion Function
o Miscellaneous
Function
4. List some character funcitons
initcap(char);
lower (char);
upper (char);
ltrim (char,[set]); rtrim (char,[set]);

SUB QUERIES:

1. What is the use of sub Queries?

A sub Queries is a select-from-where expression that is nested with in another Queries. A
common use of sub Queries is to perform tests for set membership, make set comparisons, and
determine set cardinality

VIEW:

1. What is a view?
A view is a logical table based on a table or another view. A view contains no data of its own
but is like a window through which data from tables can be viewed or changed.
2. List any two advantages of view?
1. Hides data complexity.
2. Simplifies the usage by combining multiple tables into a single table.

116 | P a g e
PROCEDURE:

1. What is procedure? Write its advantages.

A procedure is a block that can take parameters (sometimes referred to as arguments) and be
invoked. Advantages:
Procedures promote reusability and maintainability.
They can be used in number of applications.

If the definition changes, only the procedure are affected, this greatly simplifies maintenance.
2. List the three types of argument passed in to the procedure
IN: Specifies that a value for the argument must be specified when calling the procedure

OUT: Specifies that the procedure passes a value for this argument back to it’s calling environment
after execution ie. used to return values to a caller of the sub-program.

INOUT: Specifies that a value for the argument must be specified when calling the procedure and that
procedure passes a value for this argument back to it’s calling environment after execution.
3. Is the function return value?
Yes, function’s return value because every function must return a value, this clause is
required.

PL / SQL

1. What is meant by branching in PL/SQL:

Sequence of statements can be executed on satisfying certain condition. If statements are
being used and different forms of if are:
1. Simple IF 2. If then else 3. Else if 4. Nested if
2. What are selection statements?
1. Switch case statement
3. Define iterations IN PL/SQL
Sequence of statements can be executed any number of times using loop construct.
4. Classify the iteration statements `in PL/SQL

It is broadly classified into: [Link]

Loop
2. For Loop
3. While Loop

MENUS:

1. Define menus
Menus consist of a menu bar with menu names, each of which drops down to display a
list of menu commands. You can use menu commands in place of or addition to command buttons

117 | P a g e
 to active procedures. Menu commands are actually controls; they have properties and events. Each
event has name property and click event, similar to action command buttons.
2. Define submenus.
The drop down list of commands below a menu name is called a menu. When a command
on the menu has another list of commands that pops up, the new list is called submenu. The filled
triangle to the right of the command indicates a menu command has a sub menu.

VISUAL BASIC:

1. What is the purpose of Visual Basic file types: .vbp, .frm, .bas, and .ocx?

Visual Project consist of at least two, and usually more, files as follows:
.vbp file: This file is called the project file, is a small text file that holds the names of the otherfiles
in the project, as well as some information about the VB environment.
.frm file: Each of your form in the project is saved in a file with extension. To begin your projectyour
project will have only one form. Later you can expect your projects to have several forms, with
one .frm file for each form. A from holds the description of all objects and their properties for each
form, as well as the basic code that you have written to respond to the events. Theseare also referred as
form modules.

.bas file: Optionally your project can have this file. These file holds basic statements that can
beaccessed from any form. As soon as you begin .bas file are called standard code modules.

.ocx file: additional controls, called custom controls, are stored with a file .ocx extension. If
youinclude controls in your projects that are not part of the standard control set, the .ocx file names
will be included in the project.

2. What is ODBC?
Open Data Base Connectivity is an administrative tool which is used to connect different types
of databases through data source name.
3. What is use of ADODC?

ActiveX Data Object Data Control is a data control which is used to connect Visual basic controls
to a database. That is, it connects the front end data with the back end.

118 | P a g e
REFERENCE BOOKS:

1. Abraham Silberschatz, Henry F. Korth, S. Sudharshan, “Database SystemConcepts”, Fifth Edition,

Tata McGraw Hill, 2006 (Unit I and Unit-V ) .

2. [Link], [Link], [Link], “An Introduction to Database Systems”,Eighth Edition,

Pearson Education, 2006.( Unit II, III and IV)

WEB REFERENCES:

[Link]

[Link]

119 | P a g e