cs8481 DBMS Lab Manual

ANNAI MIRA COLLEGE OF ENGINEERING AND TECHNOLOGY
NH-46, Chennai-Bengaluru National Highways, Arappakkam,

Vellore-632517, Tamil Nadu, India
Telephone: 04172-292925 Fax: 04172-292926
Email:amcet.rtet@gmail.com/info@amcet.in Web: www.amcet.in
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
CS8481 / DATABASE MANAGEMENT SYSTEMS
Name : …………………………………….
Register Number :…………………………………….
Year & Branch :…………………………………….
Semester :……………………………………
Academic Year :………………………….................

ANNAI MIRA COLLEGE OF ENGINEERING AND TECHNOLOGY
NH-46, Chennai-Bengaluru National Highways, Arappakkam,
Vellore-632517, TamilNadu, India
Telephone: 04172-292925 Fax: 04172-292926
CERTIFICATE
This is to Certify that the bonafide record of the practical work done
by………………………………… Register Number of IV year B.E (Computer Science and
Engineering) submitted for the B.E-Degree practical examination(VII Semester) in CS8481– DATABASE
MANAGEMENT SYSTEMS during the academic year 2020 – 2021.
Staff in –Charge Head of the Department
Submitted for the practical examination held on ------------------
Internal Examiner External Examiner

TABLE OF CONTENT
S.NO DATE LIST OF EXPERIMENTS PG.NO SIGNATURE
5.7.2021 Data Definition Commands, Data Manipulation

Commands for inserting, deleting, updating
1 and retrieving Tables and Transaction Control
statements
2 Database Querying – Simple queries, Nested

5.7.2021 queries, Sub queries and Joins
3 Views, Sequences, Synonyms

9.7.2021
4 Database Programming: Implicit and Explicit
9.7.2021 Cursors
5 Procedures and Functions
12.7.2021
6 Triggers
12.7.2021
Exception Handling
7 15.7.2021
Database Design using ER modeling,
8 15.7.2021 normalization and Implementation for any
application
9 20.7.2021 Database Connectivity with Front End Tools
10 24.7.2021 Case Study using real life database applications

Ex. No: 1 DDL COMMANDS
AIM:
To create a database and write SQL queries to retrieve information from the
database.
DESCRIPTION:
Data Definition Language
DDL (Data Definition Language) statements are used to create, change the objects
of a database. Typically a database administrator is responsible for using DDL
statements or pro-duction databases in a large database system. The commands used
are:
• Create - It is used to create a table.
• Alter - This command is used to add a new column, modify the existing column
defi-nition and to include or drop integrity constraint.
• Drop - It will delete the table structure provided the table should be empty.
• Truncate - If there is no further use of records stored in a table and the structure
has to be retained, and then the records alone can be deleted.
• Desc - This is used to view the structure of the table.
PROCEDURE:
Step 1: Create table by using create table command with column name, data type and size.
Step 2: Display the table by using desc command.
Step 3: Add any new column with the existing table by alter table command.
Step 4: Modify the existing table with modify command.
Step 5: Delete the records in files by truncate command.
Step 6: Delete the Entire table by drop command
1. Create
Syntax:
Create table tablename(column_name1 datatype(size),
column_name2 datatype(size), column_name3
datatype(size),………);
Example:
SQL> Create table Student(Stud_name char(20), Stud_id varchar2(10), Stud_dept

varchar2(20), Stud_age number(5));
Table created.
SQL> desc Student;

Name Null? Type
STUD_NAME CHAR(20)
STUD_ID VARCHAR2(10)
STUD_DEPT VARCHAR2(20)
STUD_AGE NUMBER(5)
2. Alter
table Syntax:
Alter table tablename add(column_name datatype(size));
Alter table tablename modify(column_name datatype(size));
Alter table tablename drop(column_name);
Example:
SQL> Alter table Student add(Stud_addr

varchar2(20)); Table altered.
SQL> desc Student;
Name Null? Type
STUD_NAME CHAR(20)
STUD_AGE NUMBER(5)
STUD_ADDR VARCHAR2(20)
SQL> Alter table Student modify (Stud_age number(10));
Table altered.
SQL> desc Student;
Name Null? Type
STUD_NAME CHAR(20)
STUD_AGE NUMBER(10)
SQL> Alter table Student drop (Stud_age number(10));

Table altered.
SQL> desc Student;
Name Null? Type
STUD_NAME CHAR(20)
3. Truncate Table
Syntax:
Truncate table tablename;
Example:
SQL> Truncate table Student;
Table truncated.
SQL> desc Student
Name Null? Type
STUD_NAME CHAR(20)
STUD_AGE NUMBER(10)
4. Drop
Table Syntax:
Drop table tablename;
Example:
SQL> Drop table Student;

Table dropped.
SQL> desc Student;

ERROR: ORA-04043: object Student does not exist
DML COMMANDS
AIM:
To Study and Practice Insertion, Deletion, Modifying, Altering, Updating and
Viewing records based on conditions in RDBMS.
DESCRIPTION:
Data Manipulation Language
DML commands are the most frequently used SQL commands and is used to
query and manipulate the existing database objects. Some of the commands are
1. Insert
2. Select
3. Update
4. Delete
PROCEDURE:
Step 1: Create table by using create table command.
Step 2: Insert values into the table
Step 3: Delete any records from the table
Step 4: Update any values in the table.
Step 5: Display the values from the table by using select command.
SQL> Create table Student(Stud_name char(20), Stud_id varchar2(10), Stud_
dept varchar2(20), Stud_age number(5));
Table created.
SQL> desc Student;

Name Null? Type
STUD_NAME CHAR(20)
STUD_AGE NUMBER(5)
1. Insert: This is used to add one or more rows to a table. The values are separated by
com-mas and the data types char and date are enclosed in apostrophes. The values must
br entered in the same order as they are defined.
Syntax:
Insert into tablename values(‘&column_name1’, ‘&column_name1’,
‘&column_name1’,…..);
Example:
SQL> Insert into Student1 values(‘&stud_name’, ‘&stud_id’, ‘&stud_dept’,

‘&stud_age’);
Enter value for stud_name: Ram
Enter value for stud_id: 101
Enter value for stud_dept: MECH
Enter value for stud_age: 14
old 1: Insert into Student1 values(‘&stud_name’, ‘&stud_id’, ‘&stud_dept’,
‘&stud_age’)
new 1: Insert into Student1 values(‘Ram’, ‘101’, ‘MECH’, ‘14’)
1 row created.

‘&stud_age’);
Enter value for stud_name: Vicky
Enter value for stud_dept: EEE
‘&stud_age’)
new 1: Insert into Student1 values(‘Vicky’, ‘102’, ‘EEE’, ‘15’)
1 row created.

‘&stud_age’);
Enter value for stud_name:Sarath
Enter value for stud_dept: CSE
‘&stud_age’)
new 1: Insert into Student1 values(‘Sarath’, ‘102’, ‘CSE’, ‘15’)
1 row created.

‘&stud_age’);
Enter value for stud_name: Devi
Enter value for stud_dept: EEE
‘&stud_age’)
new 1: Insert into Student1 values(‘Devi’, ‘104’, ‘EEE’, ‘14’)
1 row created.
2. Select Command: It is used to retrieve information from the table. It is generally

referred to as querying the table. We can either display all columns in a table or only
specify column from the table.
Syntax:
Select * from tablename;
Example:
SQL> select * from Student1;
STUD_NAME STUD_ID STUD_DEPT STUD_AGE
Ram 101 MECH 14

Vicky 102 EEE 15
Sarath 103 CSE 15
Devi 104 EEE 14
4 rows selected
3. Update Command: It is used to alter the column values in a table. A single column
may be updated or more than one column could be updated.
Syntax:
Update tablename set column_name=’value’ where condition;
Example:
SQL> Update Student1 set stud_id=’109’ where stud_name=’Sarath’; 1

row updated.
Ram 101 MECH 14

Vicky 102 EEE 15
Sarath 109 CSE 15
Devi 104 EEE 14
4 rows selected
4. Delete Command: After inserting row in a table we can also delete them if required.
The delete command consists of a from clause followed by an optional where clause.
Syntax:
Delete from tablename where condition;
Example:
SQL> Delete from Student1 where stud_dept=’CSE’; 1

row deleted.
Ram 101 MECH 14

Vicky 102 EEE 15
Devi 104 EEE 14
3 rows selected
TCL(TRNSACTION CONTROL LANGUAGE)
SAVEPOINT:
Syntax for save point:
SQL> SAVEPOINT <SAVE POINT NAME>;
SQL> SAVEPOINT S1;
Savepoint created.
SQL> SELECT * FROM EMP;
EMPNO ENAME DESIGNATIN SALARY

- -
101 NAGARAJAN LECTURER 16000
102 SARAVANAN ASST. PROF 16000
104 CHINNI HOD, PROF 45000
SQL> INSERT INTO EMP

VALUES(105,'PARTHASAR','STUDENT',100); 1 row created.
105 PARTHASAR STUDENT 100

ROLL BACK
Syntax for save point:
SQL> ROLL BACK <SAVE POINT NAME>;
SQL> ROLL BACK S1;

Rollback complete.


103 PANNERSELVAM ASST. PROF 20000
COMMIT
Syntax for commit:
SQL> COMMIT;
SQL> COMMIT;
Commit complete.
DCL (DATA CONTROL LANGUAGE)
CREATING A USER
SQL>CONNECT SYSTEM/MANAGER; SQL>CREATE USER "USERNAME"
IDENTIFIED BY "PASSWORD" SQL>GRANT DBA TO "USERNAME"
SQL>CONNECT "USERNAME"/"PASSWORD";
EXAMPLE
CREATING A USER
SQL>CONNECT SYSTEM/MANAGER;
SQL>CREATE USER CSE2 IDENTIFIED BY

CSECSE; SQL>GRANT DBA TO CSE2;
SQL>CONNECT CSE2/CSECSE;
SQL>REVOKE DBA FROM CSE2;
DRL-DATA RETRIEVAL IMPLEMENTING ON

SELECT COMMANDS
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 2000
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 3000
7521 WARD SALESMAN 7698 22-FEB-81 1250 5000
7566 JONES MANAGER 7839 02-APR-81 2975 2000

4 rows selected.
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
7369 SMITH 800

7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
SQL>select ename,job,sal,deptno from emp where sal not between 1500 and 5000;
ENAME JOB SAL DEPTNO
SMITH CLERK 800 20

WARD SALESMAN 1250 30
MARTIN SALESMAN 1250 30
ADAMS CLERK 1100 20
JAMES CLERK 950 30
MILLER CLERK 1300 10
6 rows selected.
SQL> select empno,ename,sal from emp where sal in (800,5000);
EMPNO ENAME SAL
7369 SMITH 800

7839 KING 5000
SQL> select empno,ename,sal from emp where comm is null;
EMPNO ENAME SAL
7369 SMITH 800

7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7876 ADAMS 1100

900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
10 rows selected.
SQL> select empno,ename,sal from emp where comm is not null;
EMPNO ENAME SAL
499 ALLEN 1600

7521 WARD 1250
7654 MARTIN 1250
7844 TURNER 1500
SQL> select empno,ename,job,sal from emp where ename like'S%';
EMPNO ENAME JOB SAL
7369 SMITH CLERK 800

7788 SCOTT ANALYST 3000
SQL> select empno,ename,job,sal from emp where job not like'S%';
EMPNO ENAME JOB SAL

7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
SQL> select ename,job,sal from emp where sal>2500;
ENAME JOB SAL
JONES MANAGER 2975

BLAKE MANAGER 2850
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
SQL> select ename,job,sal from emp where sal<2500;
ENAME JOB SAL

SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
CLARK MANAGER 2450
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
9 rows selected.
SQL> select empno,ename,job,sal from emp order by sal;
EMPNO ENAME JOB SAL

7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
EMPNO ENAME JOB SAL

7902 FORD ANALYST 3000
7839 KING PRESIDENT 5000
14 rows selected.
SQL> select empno,ename,job,sal from emp order by sal desc;
EMPNO ENAME JOB SAL
7839 KING PRESIDENT 5000

7902 FORD ANALYST 3000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
7934 MILLER CLERK 1300
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
EMPNO ENAME JOB SAL
7876 ADAMS CLERK 1100

7900 JAMES CLERK 950
14 rows selected.
RESULT:
Thus the Insertion, Deletion, Modifying, Altering, Updating and Viewing
records based on conditions in RDBMS were executed and verifie
Ex No: 2 Database Querying – Simple queries, Nested queries, Sub
queries and Joins
AIM:
To Study and Practice Constraints, Nested Queries and joining records based on
conditions in RDBMS.
A) CONSTRAINTS :
1. NOT NULL:
Used to set the not null constraint to the specified column name which will not allow
null values.
Syntax:
Create table tablename(fieldname1 datatype(constraint)not null,fieldname2 datatype,
…………….fieldnamen datatype);
Example:
SQL> create table notnull (eno varchar2(10) not null,ename varchar2(10),esalary
number(20)); Table created.
SQL> insert into notnull values(‘&eno’,’&ename’,’&esalary’);

Enter value for eno: 1
Enter value for ename: arul
Enter value for esalary: 20000
old 1: insert into notnull values(‘&eno’,’&ename’,’&esalary’)
new 1: insert into notnull values(‘1’,’arul’,’20000’)
1 row created.
SQL> /
Enter value for eno:
Enter value for ename: raj
Enter value for esalary: 30000
old 1: insert into notnull values(‘&eno’,’&ename’,’&esalary’)
new 1: insert into notnull values(‘’,’raj’,’30000’)
insert into notnull values(‘’,’raj’,’30000’)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“SCOTT”.”NOTNULL”.”ENO”)
2. CHECK:
Check constraint specify conditions that each tuple must satisfy.
Syntax:
Create table tablename(Fieldname1 datatype(constraint),Fieldname2 datatype,
………………….Fieldname3 datatype);
Example:
SQL> create table con( empid varchar2(20) not null,empname
varchar2(20),empsalary number(10) check(empsalary>10000));
Table created.
SQL> insert into con
values(‘&empid’,’&empname’,’&empsalary’); Enter value for
empid: 1
Enter value for empname: kumar

Enter value for empsalary: 20000
old 1: insert into con values(‘&empid’,’&empname’,’&empsalary’)
new 1: insert into con values(‘1’,’kumar’,’20000’)
1 row created.
SQL> /
Enter value for empid: 2
Enter value for empname: raja
Enter value for empsalary: 9000
old 1: insert into con values(‘&empid’,’&empname’,’&empsalary’)
new 1: insert into con values(‘2’,’raja’,’9000’)
insert into con values(‘2’,’raja’,’9000’)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0010283) violated
3. UNIQUE:
Used to set unique constraint to the specified column name which will not allow
redundant values
Syntax:
Create table tablename(fieldname1 datatype(constraint)unique,fieldname2 datatype,
…………….Fieldname3 datatype);
Example:
SQL> create table conn(eno varchar2(10) unique,ename varchar2(20));
Table created.
SQL> insert into conn values(‘&eno’,’&ename’);

Enter value for ename: hello
old 1: insert into conn values(‘&eno’,’&ename’)
new 1: insert into conn values(‘1’,’hello’)
1 row created.
SQL> /
Enter value for ename: hi
old 1: insert into conn values(‘&eno’,’&ename’)
new 1: insert into conn values(‘1’,’hi’)
insert into conn values(‘1’,’hi’)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0010285) violated
4. PRIMARY KEY:
Primary key is a constraint for both unique and not null.
Syntax:
Create table tablename(Fieldname1 datatype(constraint)unique,fieldname2 datatype,
Example:
SQL> create table con(empid varchar2(10),empname varchar2(20)
primary key); Table created.
values(‘&empid’,’&empname’); Enter value for
empid: 1

old 1: insert into con values(‘&empid’,’&empname’)
new 1: insert into con values(‘1’,’kumar’)
1 row created.
SQL> /
new 1: insert into con values(‘2’,’kumar’)
insert into con values(‘2’,’kumar’)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0010286) violated
5. ADDING CONSTRIANT:
Used to set any constraint to the specified column at the last by specifying the constraint
type and field name.
Syntax:
Create table tablename(Fieldname1 datatype(constraint)unique,fieldname2 datatype,
constraint constraintname constrainttype(fieldname));
Example:
SQL> create table con(empid varchar2(10),empname varchar2(10),constraint c1
primary key(empid));
Table created.
values(‘&empid’,’&empname’); Enter value for
empid: 1
Enter value for empname: anand

new 1: insert into con values(‘1’,’anand’)
1 row created.
SQL> /
Enter value for empname: vijay
new 1: insert into con values(‘1’,’vijay’)
insert into con values(‘1’,’vijay’)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.C1) violated
6. ADD CONSTRAINT(ALTER)
Used to set the constraint for the table already created by using alter command.
Syntax:
Alter table tablename add constraint constraintname (fieldname)datatype,primary key.
Example:
SQL> create table con(empid varchar2(10),empname
varchar2(10)); Table created.
SQL> alter table con add constraint c1 primary
key(empid); Table altered.
SQL> desc con;

Name Null? Type
---
NOT NULL
EMPID VARCHAR2(10)
EMPNAME VARCHAR2(10)
7. DROP CONSTRAINT:
Used to drop the constraint.
Syntax:
Alter table tablename drop constraint constraintname.
Example:
SQL> alter table con drop constraint c1;
Table altered.
SQL> desc con;

Name Null? Type
EMPID VARCHAR2(10)
8. REFERENTIAL INTEGRITY:
Used to refer the primary key of the parent table from the childtable.
Syntax:
a) Create table tablename(Fieldname1 datatype primary key,fieldname2 datatype,
b) Create table tablename(Fieldname1 datatype references,Parent tablename(fieldname)
…………….Fieldname n datatype);
Example:
SQL> create table parent(eno varchar2(10),ename varchar2(10)
SQL> insert into parent values(‘&eno’,’&ename’);

Enter value for ename: ajay
old 1: insert into parent values(‘&eno’,’&ename’)
new 1: insert into parent values(‘1’,’ajay’)
1 row created.
SQL> /
Enter value for ename: bala
new 1: insert into parent values(‘2’,’bala’)
1 row created.
SQL> create table child (eno varchar2(10),ename varchar2(10) references
parent(ename)); Table created.
SQL> insert into child values(‘&eno’,’&ename’);

Enter value for ename: ajay
old 1: insert into child values(‘&eno’,’&ename’)
new 1: insert into child values(‘1’,’ajay’)
1 row created.
SQL> /
Enter value for ename: balaji
old 1: insert into child values(‘&eno’,’&ename
new 1: insert into child values(‘2’,’balaji’)

insert into child values(‘2’,’balaji’)
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0010290) violated - parent key not
Found
9. ON DELETE CASCADE:
The changes done in parent table is reflected in the child table when references are made .
Syntax:
Create table tablename(Fieldname1 datatype references,Parent
tablename(fieldname), On delete cascade);
Example:
SQL> create table parent(eno varchar2(10),ename varchar2(10)
SQL> insert into parent values(‘&eno’,’&ename’);

Enter value for ename: a
new 1: insert into parent values(‘1’,’a’)
1 row created.
SQL> create table child(eno varchar2(10),ename varchar2(10) references

parent(ename) on delete cascade);
Table created.
SQL> insert into child values(‘&eno’,’&ename’);

Enter value for ename: a

old 1: insert into child values(‘&eno’,’&ename’)
new 1: insert into child values(‘2’,’a’)
1 row created.
SQL> select * from parent;
ENO ENAME
1 a
SQL> select * from child;

ENO ENAME
2 a
SQL> delete from parent where eno=1;
1 row deleted.
SQL> select * from parent;
no rows selected
SQL> select * from child;
no rows selected
B) SUBQUERY:
SINGLE ROW SUB-QUERY:

Syntax:
Select <fieldname> from <tablename1> where <fieldname>=
(select<fieldname>from<fieldnam e2>where (condition);
1. UNION:
Syntax:
Select <fieldlist> from <tablename1> where (condition) union
select<fieldlist> from<tablename2> where (condition);
2. INTERSECT:
Syntax:
Select <fieldlist> from <tablename1> where (condition) intersect
3. IN:
Syntax:
Select <fieldlist> from <tablename1> where (condition) in
4. BETWEEN:
Syntax:
Select <fieldlist> from <tablename1> where (condition) between
5. LIKE:
Syntax:
Select <fieldlist> from <tablename> where <fieldname> like <expression>;
6. NOT LIKE:
Syntax:
Select <fieldlist> from <tablename> where <fieldname> not like <expression>;
7. ALL:
Syntax:
Select <fieldlist> from <tablename1>where
<fieldname> all Select <fieldlist> from <tablename2>
where (condition);
8. ANY:
Syntax:
Select <fieldlist> from <tablename1> where
(condition) any Select <fieldlist> from <tablename2>
where(condition);
C) JOINS:
1. EQUI JOIN:
It retrieves rows from two tables having a common column using ‘=’ operators.
Syntax:
Select <tablename1.fieldlist,tablename2.fieldlist> from
<tablename1><tablename2> where
<tablename1.keyfield>=<tablename2.keyfield>;
2. INNER JOIN :
Inner join returns the matching rows from the tables that are being joined.
Syntax:
Select tablename1.fieldlist,tablename2.fieldlist from tablename1 inner join
tablename2 on tablename1.keyfield = tablename2.keyfield;
3. OUTER JOIN :
Outer join returns the both matching and non-matching rows for the tables that are
being joined. An outer join is an extended form of the inner join. The rows in one table
having no matching rows in the other table will also appear in the result table with nulls.
LEFT OUTER JOIN :
Syntax:
Select tablename1.fieldlist,tablename2.fieldlist from tablename1 left outer join
RIGHT OUTER JOIN :
Syntax:
Select tablename1.fieldlist,tablename2.fieldlist from tablename1 right outer join
FULL OUTER JOIN :
Syntax:
Select tablename1.fieldlist,tablename2.fieldlist from tablename1 full outer join
OUTPUT / EXAMPLE (NESTED QUERIES):

SQL> create table employee(empno varchar2(10),empname
varchar2(10),empsalary number(10));
Table created.
SQL> drop table employee1;
Table dropped.
SQL> create table employee1(empno varchar2(10),empname
varchar2(10),empsalary number(10));
Table created.
SQL> insert into employee values(‘&empno’,’&empname’,’&empsalry’);
Enter value for empno: 1
Enter value for empname: arun
Enter value for empsalry: 10000
old 1: insert into employee values(‘&empno’,’&empname’,’&empsalry’)
new 1: insert into employee values(‘1’,’arun’,’10000’)
1 row created.
SQL> /
Enter value for empname: bala
old 1: insert into employee values(‘&empno’,’&empname’,’&empsalry’)
new 1: insert into employee values(‘2’,’bala’,’20000’)
1 row created.
SQL> insert into employee1 values(‘&empno’,’&empname’,’&empsalry’);
Enter value for empname: arun
old 1: insert into employee1 values(‘&empno’,’&empname’,’&empsalry’)
new 1: insert into employee1 values(‘1’,’arun’,’10000’)

1 row created.
SQL> /
Enter value for empname: chitra
old 1: insert into employee1 values(‘&empno’,’&empname’,’&empsalry’)
new 1: insert into employee1 values(‘3’,’chitra’,’40000’)
1 row created.
SQL> select * from employee;

EMPNO EMPNAME EMPSALARY
1 arun 10000
2 bala 20000
SQL> select * from employee1;
EMPNO EMPNAME
EMPSALARY
1 arun 10000
3 chitra 40000
Output for Subquery:
SQL> select * from employee where empsalary=(select min(empsalary)from employee1);
EMPNO EMPNAME EMPSALARY
1 arun 10000
UNION:
SQL> select empname,empno from employee where(empsalary>10000)
union select empname,empno from employee1 where(empsalary>10000);
EMPNAME EMPNO
bala 2
chitra 3
INTERSECT:
SQL> select empname,empno from employee where(empsalary>9000)
intersect select empname,empno from employee1 where(empsalary>9000);
EMPNAME EMPNO
arun 1
IN:
SQL> select empname from employee where empsalary in(select empsalary from
employee1);
EMPNAME
arun
SQL> select empno,empname from employee where empno in(select
empsalary from employee1);
no rows selected
SQL> select empno,empname from employee where empno in(select empno from
employee1);
EMPNO EMPNAME
1 Arun
BETWEEN:
SQL> select empno,empname from employee where empsalary between 10000 and 30000;
EMPNO EMPNAME
1 arun
2bala
LIKE:
SQL> select empname,empno from employee where empname like ‘b%’;
EMPNAME EMPNO
bala 2
NOT LIKE:
SQL> select empname,empno from employee where empname not like ‘b%’;
EMPNAME EMPNO
arun 1
ALL:
SQL> select empname,empsalary from employee1 where empsalary > all (select
empsalary from employee where empsalary>10000);
EMPNAME EMPSALARY
chitra 40000
ANY:
SQL> select empname,empsalary from employee1 where empsalary>any
(select min(empsalary) from employee);
EMPNAME EMPSALARY
chitra 40000
OUTPUT / EXAMPLE(JOINS):
SQL> create table employee (empid varchar2(10),empname varchar2(10),deptid
varchar2(10) primary key);
Table created.
SQL> insert into employee values(‘&empid’,’&empname’,’&deptid’);

Enter value for empid: 10cse01
Enter value for empname: anand
Enter value for deptid: cse
old 1: insert into employee values(‘&empid’,’&empname’,’&deptid’)
new 1: insert into employee values(‘10cse01’,’anand’,’cse’)
1 row created.
SQL> /
Enter value for empid: 10ece02
Enter value for empname: bala
Enter value for deptid: ece
new 1: insert into employee values(‘10ece02’,’bala’,’ece’)
1 row created.
SQL> /
Enter value for empid: 10mech03
Enter value for empname: karthi
Enter value for deptid: MECH
new 1: insert into employee values(‘10mech03’,’karthi’,’MECH’)
1 row created.
SQL> create table department(deptid varchar2(10) primary key,deptname
varchar2(20)); Table created.
SQL> insert into department
values(‘&deptid’,’&deptname’); Enter value for deptid:
cse
Enter value for deptname: computerscience

old 1: insert into department values(‘&deptid’,’&deptname’)
new 1: insert into department values(‘cse’,’computerscience’)
1 row created.
SQL> /
Enter value for deptid: ece
Enter value for deptname: electronics
new 1: insert into department values(‘ece’,’electronics’)
1 row created.
SQL> /
Enter value for deptid: mech
Enter value for deptname: mechanical

new 1: insert into department values(‘mech’,’mechanical’)
1 row created.
EMPID EMPNAME DEPTID
10cse01 anand cse

10ece02 bala ece
10mech03 karthi MECH
SQL> select * from department;

DEPTID DEPTNAME
ece electronics
cse computerscience
mech mechanical
EQUI JOIN
SQL> select
employee.empid,employee.empname,department.deptid,department.deptname from
employee,department where employee.deptid = department.deptid;
EMPID EMPNAME DEPTID DEPTNAME ---
10ece02 bala ece electronics

10cse01 anand cse computerscience
INNER JOIN:
SQL> select employee.empid,department.deptname from employee inner join
department on employee.deptid = department.deptid;
EMPID DEPTNAME
10ece02 electronics
10cse01 computerscience
SQL> select * from employee inner join department on
employee.deptid=department.deptid; EMPID EMPNAME DEPTID DEPTID

DEPTNAME
10ece02 bala ece ece electronics
10cse01 anand cse cse computerscience
LEFT OUTER JOIN:
SQL> select employee.empname,department.deptname from employee left
outer join department on employee.deptid=department.deptid;
EMPNAME DEPTNAME
anand computerscience
bala electronics
karthi
RIGHT OUTER JOIN:

SQL> select employee.empname,department.deptname from employee right
outer join
department on employee.deptid=department.deptid;
EMPNAME DEPTNAME
bala electronics
mechanical
FULL OUTER JOIN:
SQL> select employee.empname,department.deptname from employee full

outer join department on employee.deptid=department.deptid;
EMPNAME DEPTNAME
bala electronics
karthi mechanical
RESULT:
Thus the Constraints, Nested Queries and joining records based on conditions in
RDBMS were executed and verified
Ex. No: 3 Views, Sequences ,Synonyms
AIM:
To write queries on views for display the particular tables.
DESCRIPTION:
In SQL, a view is a virtual table based on the result-set of an SQL statement. A view
con-tains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database. A view is an object that gives the user the
logical view of data from the underlying table. Any relation that is not part of the logical
model but is made visible to the user as a virtual relation is called a view. They are
generally used to avoid duplication of data. Views are created for the following reasons,
• Data simplicity
• To provide data security
• Structural simplicity (because view contains only limited number of rows and
col-umns)
•
PROCEDURE:
Step 1: Create table with some attributes.
Step 2: Create view from single table with all attributes.
Step 3: Create view from single table with selected attributes.
Step 4: Create View from two tables with all attributes.
Step 5: Create View from two tables with selected attributes.
Step 6: Use DML commands in views.
Step 7: Drop the View.
1. Create a view from single table containing all columns from the
base table. SQL> desc emp1;
Name Null? Type
EID NUMBER(3)
ENAME VARCHAR2(10)
AGE NUMBER(3)
SALARY NUMBER(6)
DOB DATE
SQL> create view viiew as select * from emp1;

View created.
SQL> desc view;

Name Null? Type
EID NUMBER(3)
ENAME VARCHAR2(10)
AGE NUMBER(3)
SALARY NUMBER(6)
DOB DATE
2. Create view from single table with selected columns.
SQL> create view viiew1 as select eid,ename,salary from
emp1; View created.
SQL> desc view1;
Name Null? Type

EID NUMBER(3)
ENAME VARCHAR2(10)
SALARY NUMBER(6)
3. Create view from two table containing all columns from the base
table. SQL> create view viiew2 as select * from emp1 full natural
join emp2; View created.
SQL> desc viiew2;

Name Null? Type
SALARY NUMBER(6)
EID NUMBER(3)
ENAME VARCHAR2(10)
AGE NUMBER(3)
DOB DATE
EMPID NUMBER(3)
4. Create view from two table with selected columns.
SQL> create view viiew3 as select ename, age, empid from
emp1,emp2; View created.
SQL> desc viiew3;
Name Null? Type
ENAME VARCHAR2(10)
AGE NUMBER(3)
EMPID NUMBER(3)
DML Commands in Views:
1. INSERT:
SQL> insert into viiew1 values(‘&eid’,’&ename’,’&salary’);
Enter value for eid: 1
Enter value for ename: anu
Enter value for salary: 25000
old 1: insert into viiew1 values(‘&eid’,’&ename’,’&salary’)
new 1: insert into viiew1 values(‘1’,’anu’,’25000’)
1 row created.
SQL> /
Enter value for ename: abi
new 1: insert into viiew1 values(‘2’,’abi’,’20000’)
1 row created.
SQL> /
Enter value for ename: cibi
new 1: insert into viiew1 values(‘3’,’cibi’,’37000’)

1 row created.
SQL> /
Enter value for ename: babu
new 1: insert into viiew1 values(‘4’,’babu’,’24000’)
1 row created.
SQL> /
Enter value for ename: soman
new 1: insert into viiew1 values(‘5’,’soman’,’27000’)
1 row created.
SQL> /
Enter value for ename: billu
new 1: insert into viiew1 values(‘6’,’billu’,’28000’)
1 row created
SQL> select * from viiew1;
EID ENAME SALARY
1 anu 25000
2 abi 20000
3 cibi 37000
4 babu 24000
5 soman 27000
6 billu 28000
2. UPDATE:
SQL>update viiew1 set eid=’8’ where
ename=’abi’; 1 row updated
EID ENAME SALARY
1 anu 25000
8 abi 20000
3 cibi 37000
4 babu 24000
5 soman 27000
6 billu 28000

EID ENAME SALARY
1 anu 25000
2 abi 20000
3 cibi 37000
4 babu 24000
6 billu 28000
3. DELETE
SQL> delete from viiew1 where eid=’5’;
1 row deleted.

EID ENAME SALARY
1 anu 25000
2 abi 20000
3 cibi 37000
4 babu 24000
6 billu 28000
5 rows selected.
DROP VIEW:
SQL> drop view viiew2;
View dropped.
SQL> desc viiew2;
ERROR:
ORA-04043: object viiew2 does not exist
SYNONYMS
AIM
To write query for synonyms to minimize the impact of moving or renaming the
database objects.
DESCRIPTION:
Synonym is an alternative name for a table, view, sequence, operator, procedure,

stored function, package, materialized view, Java class schema object, user-defined
object type, or another synonym. A synonym places a dependency on its target object
and becomes invalid if the target object is changed or dropped.
PROCEDURE:
Step 1: Create the database which contains several tables using DDL
and DML command.
Step 2: Select particular table from the database provide permission
using grand command.
Step 3: Create temporary table name for that particular table.

Step 4: Display the table using select command.
EXAMPLE
In your database you have a schema called pacet. This schema contains a
table called EMPLOYEES.
To query the table you use the following SELECT statement:

SELECT * FROM PACET.EMPLOYEES;
Grant select privileges to all database users

GRANT SELECT ON PACET.EMPLOYEES TO PUBLIC;
Create a public synonym for your table

GRANT SELECT ON PACET.EMPLOYEES TO PRIVATE;
Create a public synonym for your table
CREATE PUBLIC SYNONYM EMPLOYEE_DATA FOR PACET.EMPLOYEES;
From now on, anyone can query your table using the
synonym SELECT * FROM EMPLOYEE_DATA;

SEQUENCES
AIM:
To write query for sequence to database object from which multiple users may
generate unique integers.
DESCRIPTION:
A sequence is a user-defined schema bound object that generates a sequence of

numeric val-ues according to the specification with which the sequence was created.
The sequence of numeric values is generated in an ascending or descending order at a
defined interval and can be configured to restart (cycle) when exhausted. Sequences,
unlike identity columns, are not associated with specific tables. Applications refer to a
sequence object to retrieve its next value. The relationship between sequences and
tables is controlled by the application. User applications can reference a sequence object
and coordinate the values across multiple rows and tables.
PROCEDURE:
Step 1: Create the sequence using create sequence command.
Step 2: Delete the sequence using drop sequence command.
Step 3: Save the current point transaction using savepoint command.
Step 4: Perform undo operation using rollback command.
1. Creat
e
Syntax:
CREATE SEQUENCE
sequence_name MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY
value CACHE value;
Example:
CREATE SEQUENCE employee
MINVALUE 1
MAXVALUE 888888888888888888888
START WITH 1
INCREMENT BY 1
CACHE 20;
2. Drop
Syntax
:
DROP SEQUENCE
sequence_name; Example:
DROP SEQUENCE employee;
3. SAVEPOINT
Syntax:
SAVEPOINT identifier
RELEASE SAVEPOINT identifier
Example:
UPDATE employees
SET salary = 7000
WHERE last_name = ‘SURESH’;
SAVEPOINT sureshsave;
UPDATE employees
SET salary = 12000
WHERE last_name = ‘SUNDAR’;
SAVEPOINT sundarsave;
4. ROLLBACK
Syntax:
ROLLBACK [WORK] TO [SAVEPOINT] identifier
Example:
SELECT SUM (salary) FROM employees;
ROLLBACK TO SAVEPOINT sureshsave;
UPDATE employees
SET salary = 11000
WHERE last_name = ‘SUNDAR’;
COMMIT;
RESULT:
Thus the views,sequences and synonyms was executed and verified successfully.
EX NO 4 DATABASE PROGRAMMING: IMPLICIT AND
EXPLICIT CURSORS
IMPLICIT CURSORS
SYNTAX:
Exercise:
Write a PL/ SQL code for calculating total mark, percentage, grade for all the
students in a student management system using implicit cursors.
SQL> create table student(id number, name varchar2(10), dept varchar2(10), percent
number,m1 number,m2 number, m3 number, tot number, g varchar2(1));
Table created.
SQL> select * from student;
ID NAME DEP PERCENT M1 M2 M3 TOT G
1 Anu it 0 90 89 80 0
2 Beena cse 0 98 91 95 0
3 Bindhu it 0 87 67 86 0
4 Varun it 0 67 46 50 0
5 Rahul cse 0 81 82 83 0
SQL> declare
2. cursor c is select * from student;
3. ctot number;
4. cgra varchar2(1);
3. cper number;
4. begin
5. for I in c
6. loop
7. ctot= i.m1+i.m2+i.m3;
8. cper :=ctot/3;
9. update student set tot = ctot where id =i.id;
10. update student set percent = cper where id =i.id;
11. if(cper between 91 and 100)then
12. cgra:= ‘S’
13. elsif(cper between 81 and 90)then
14. cgra:= ‘A’
16. cgra:= ‘B’
18. cgra:= ‘C’
20. cgra:= ‘D’
22. cgra:= ‘E’
23. else
24. cgra:= ‘F’
25. end if;
26. update student set g = cgra where id =i.id;
1 end loop;
2 end;
3 /
PL/ SQL procedure successfully completed.

SQL> select * from student;
ID NAME DEP PERCENT M1 M2 M3 TOT G
1 Anu it 86.3333333 90 89 80 259 A
2 Beena cse 94.6666667 98 91 95 284 S
3 Bindhu it 80 87 67 86 240 B
4 Varun it 54.3333333 67 46 50 163 E
5 Rahul cse 82 81 82 83 246 A

EXPLICIT CURSORS
SYNTAX:
cursor cursor_name is select * from table name;
To open the cursor:
open cursor_name;
To close the cursor:
close cursor_name;
Exercise:
Write PL/ SQL code for calculating hra , da, netsalary for all the employees in the Payroll
Processing using Explicit cursor(uses employee table).
EMPNO NAME HRADA PF NETSAL BASICPAY
101 AAA 0 0 0 0 15000
102 BBB 0 0 0 0 18000
103 CCC 0 0 0 0 20000
104 DDD 0 0 0 0 10000
105 EEE 0 0 0 0 25000

SQL> declare
3 cursor c is select * from employee;
4 i employee% rowtype;
5 hrasal number;
6 dasal number;
7 pfsal number;
8 netsalary number;
9 begin
10 open c;
11 loop;
12 fetch c into i;
13 if c% notfound ten exit;
14 endif;
15 hrasal:=i.basicpay*0.1;
16 dasal:=i.basicpay*0.08;
17 pfsal:=i.basicpay*0.12;
18 netsalaray:= i.basicpay + hrasal + dasal + pfsal;
19 update employee set hra = hrasal, da= dasal, pf= pfsal, netsal= netsalaray where
empno=i.empno;
20 end loop;
21 close c;
22 end;
23 /
PL/ SQL procedure successfully completed.
EMPNO NAME HRA DA PF NETSAL BASICPAY
101 AAA 1500 1200 1800 15900 15000
102 BBB 1800 1440 2160 19080 18000
103 CCC 2000 1600 2400 21200 20000
104 DDD 1000 800 1200 10600 10000
105 EEE 2500 2000 3000 26500 25000

RESULT
Thus the implicit and explicit cursor was executed and verified successfully.
Ex: No: 5 PROCEDURES AND FUNCTIONS
AIM:
To implement and execute PL/SQL Block that handles all types of exceptions in
Oracle Database using Procedural Language concepts.
PROCEDURE:
Step 1: Declare the necessary variables.
Step 2: Begin the program.
Step 3: Assign Values to the variables.
Step 4: Write the looping condition
Step 5: Perform calculation.
Step 6: End loop.
Step 7: Execute the procedure
1. Create
Syntax:
CREATE [OR REPLACE] PROCEDURE proc_name [list of
parameters] IS Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
Example:
SQL> create or replace procedure sum(n
number) is i number;
sum1 number;
begin
sum1:=0;
i:=0;
while i<=n
loop
sum1:=sum1+i;
i:=i+1;
end loop;
dbms_output.put_line(sum1);
end;
/
Procedure created.
Execution of procedure:
EXECUTE [or EXEC] procedure_name;

SQL> EXEC SUM(4);
10
PL/SQL procedure successfully completed.
2. Drop:
Syntax:
Drop Procedure procedure_name;
Example:
Drop Procedure Sum;
3. Exception Handling:
Syntax:
DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
WHEN Others THEN
END;
a) Named System Exceptions

Suppose a NO_DATA_FOUND exception is raised in a proc, we can write a code to
handle the exception as given below.
BEGIN
Execution section
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line (‘A SELECT...INTO did not return any
row.’); END;
b) Unnamed System Exceptions

Let’s consider the product table and order_items table from sql joins. Here
product_id is a primary key in product table and a foreign key in order_items table. If
we try to delete a product_id from the product table when it has child records in
order_id table an exception will be thrown with oracle code number -2292.
DECLARE
Child_rec_exception EXCEPTION;
PRAGMA
EXCEPTION_INIT (Child_rec_exception, -2292);
BEGIN
Delete FROM product where product_id= 104;
EXCEPTION
WHEN Child_rec_exception
THEN Dbms_output.put_line(‘Child records are present for this
product_id.’); END;
c) User-defined Exceptions
Let’s consider the product table and order_items table from sql joins to explain
user-defined exception.
Let’s create a business rule that if the total no of units of any particular product
sold is more than 20, then it is a huge quantity and a special discount should be
provided.
DECLARE
huge_quantity EXCEPTION;
CURSOR product_quantity is
SELECT p.product_name as name, sum(o.total_units) as units
FROM order_tems o, product p
WHERE o.product_id = p.product_id;
quantity order_tems.total_units%type;
up_limit CONSTANT order_tems.total_units%type
:= 20; message VARCHAR2(50);
BEGIN
FOR product_rec in product_quantity LOOP
quantity := product_rec.units;
IF quantity > up_limit THEN
message := ‘The number of units of product ‘ || product_rec.name ||
‘ is more than 20. Special discounts should be
provided. Rest of the records are
skipped. ‘
RAISE huge_quantity;
ELSIF quantity < up_limit THEN
v_message:= ‘The number of unit is below the discount limit.’;
END IF;
dbms_output.put_line (message);
END LOOP;
EXCEPTION
WHEN huge_quantity THEN
dbms_output.put_line (message);
END;
FUNCTIONS
A) Implementation of Factorial Using Function
Aim :
To create a function for Factorial problem.
Procedure:
Step 1: Declare the necessary variables.

Step 2: Begin the program.
Step 3: Assign Values to the variables.
Step 4: Write the looping condition
Step 5: Perform calculation.
Step 6: End loop.
Step 7: Create the function
Step 8: Create the main procedure and call the function
Step 9: Execute the procedure.
SQL> create function fnfact(n number)

2 return number is
3 b number;
4 begin
5 b:=1;
6 for i in 1..n
7 loop
8 b:=b*i;
9 end loop;
10 return b;
11 end;
12 /
Function created.
SQL> Declare
2 n number:=&n;
3 y number;
4 begin
5 y:=fnfact(n);
6 dbms_output.put_line(y);
7 end;
8 /
Enter value for n: 5

old 2: n number:=&n;
new 2: n number:=5;
120

B) Accessing Database Using Function:
Aim:
To write a Functional procedure to search an address from the given database.
Procedure:
Step 1: start
Step 2: create the table with essential attributes.
Step 3: initialize the function to carry out the searching procedure..
Step 4: frame the searching procedure for both positive and negative searching.
Step 5: execute the function for both positive and negative result .
Step 6: stop
Execution:
SQL> create table phonebook(phone_no number(6) primary key,username
varchar2(30),doorno varchar2(10), street varchar2(30),place varchar2(30),pincode
char(6));
Table created.
SQL> insert into phonebook values(20312,’vijay’,’120/5D’,’bharathi

street’,’NGO colony’,’629002’);
1 row created.
SQL> insert into phonebook values(29467,’vasanth’,’39D4’,’RK

bhavan’,’sarakkal vilai’,’629002’);
1 row created.
SQL> select * from phonebook;
PHONE_NO DOORNO STREET PLACEPINCODE

USERNAME
bharathi
20312 vijay 120/5D street NGO colony 629002
29467 vasanth 39D4 RK bhavan sarakkal vilai 629002
SQL> create or replace function findAddress(phone in number) return

varchar2 as address varchar2(100);
begin
select username||’,’||doorno ||’,’||street ||’,’||place||’,’||pincode into address from phonebook
where phone_no=phone;
return address;
exception
when no_data_found then return ’address not found’;
end;
/
Function created.
SQL>declare
address varchar2(100);
begin
address:=findaddress(20312);
dbms_output.put_line(address);
end;
/
Output 1:
vijay,120/5D,bharathi street,NGO colony,629002

SQL> declare
2 address varchar2(100);
3 begin
4 address:=findaddress(23556);
5 dbms_output.put_line(address);
6 end;
7 /
Output2:
address not found
RESULT:
Thus the Procedure and Function process was executed and verified successfully.
Ex.No: 6 TRIGGERS
AIM:
To Study and Practice Creation, Insertion, Deletion and updating triggers based on
condi-tions in RDBMS.
DESCRIPTION:
Database Triggers
A trigger is a statement that is executed automatically by the system as a side effect
of a modification to the database. The parts of a trigger are,
• Trigger statement: Specifies the DML statements and fires the trigger body. It
also specifies the table to which the trigger is associated.
• Trigger body or trigger action: It is a PL/SQL block that is executed when the
trig-gering statement is used.
• Trigger restriction: Restrictions on the trigger can be achieved
The different uses of triggers are as follows,

• To generate data automatically
• To enforce complex integrity constraints
• To customize complex securing authorizations
• To maintain the replicate table
• To audit data modifications
TYPES OF TRIGGERS The various types of triggers are as follows,

Before: It fires the trigger before executing the trigger statement.
After: It fires the trigger after executing the trigger statement.
For each row: It specifies that the trigger fires once per row.
For each statement: This is the default trigger that is invoked. It specifies that the
trigger fires once per statement.
VARIABLES USED IN TRIGGERS

• :new
• :old
These two variables retain the new and old values of the column updated in the
database.
The values in these variables can be used in the database triggers for data manipulation.
Syntax:
Create or replace trigger <Trigger_name> before/after insert/delete/update on
<Table_ name> for each row/statement
DELCARE
<Declaration statements>
BEGIN
<Executable Statements>
END:
SQL> create table emply101(eid varchar(20),ename char(25), age

number(10), salary number(10));
Table created.
SQL> desc emply101;

Name Null? Type
EID VARCHAR2(20)
ENAME CHAR(25)
AGE NUMBER(10)
SALARY NUMBER(10)
SQL> insert into emply101 values(‘&eid’,’&ename’,’&age’,’&salary’);

Enter value for eid: e1
Enter value for ename: anu
Enter value for age: 25
old 1: insert into emply101 values(‘&eid’,’&ename’,’&age’,’&salary’)
new 1: insert into emply101 values(‘e1’,’anu’,’25’,’20000’)
1 row created.
SQL> /
Enter value for ename: abi
new 1: insert into emply101 values(‘e2’,’abi’,’24’,’25700’)
1 row created.
SQL> /
Enter value for ename: babu
new 1: insert into emply101 values(‘e3’,’babu’,’30’,’34000’)
1 row created.
SQL> /
Enter value for ename: cibi
new 1: insert into emply101 values(‘e4’,’cibi’,’32’,’32000’)
1 row created.
SQL> /
Enter value for ename: brindha
new 1: insert into emply101 values(‘e5’,’brindha’,’28’,’31000’)
1 row created.
SQL> select * from emply101;
EID ENAME AGE SALARY
e1 anu 25 20000
e2 abi 24 25700
e3 babu 30 34000
e4 cibi 32 32000
e5 brindha 23 31000
SQL> create table emply102(eid varchar(20),ename char(25),age

number(10),salary number(10));
Table created.
SQL> desc emply102;
Name Null? Type
EID VARCHAR2(20)
ENAME CHAR(25)
AGE NUMBER(10)
SALARY NUMBER(10)
SQL> create or replace trigger tfg1 before insert on emply101 for each row
2 begin
3 insert into emply102 values(:new.eid,:new.ename,:new.age,:new.salary);
4 end;
5 /
Trigger created.

Enter value for ename: thara
new 1: insert into emply101 values(‘e7’,’thara’,’28’,’30000’)
1 row created.

e7 thara 28 30000

Enter value for ename: siju
new 1: insert into emply101 values(‘e8’,’siju’,’29’,’36000’)
1 row created.
--
e7 thara 28 30000
e8 siju 29 36000
SQL> create or replace trigger trgrr15 after delete or update on emply101 for each row
2 begin
3 if deleting then
4 insert into emply102 values(:old.eid,:old.ename,:old.age,:old.salary);
5 elsif updating then
6 insert into emply102 values(:old.eid,:old.ename,:old.age,:old.salary);
7 end if;
8 end;
9 /
Trigger created.
e1 anu 25 20000
e2 abi 24 25700
e3 babu 30 34000
e4 cibi 32 32000
e5 brindha 23 31000
e7 thara 28 30000
e8 siju 29 s36000
7 rows selected.
SQL> delete from emply101 where
ename=’babu’; 1 row deleted.

--
e7 thara 28 30000
e8 siju 29 36000
e3 babu 30 34000
SQL> update emply101 set ename=’sanju’ where
eid=’e5’; 1 row updated.
e7 thara 28 30000
e8 siju 29 36000
e3 babu 30 34000
e5 brindha 23 31000
RESULT:
Thus the Study and Practice Creation, Insertion, Deletion and updating triggers based on
conditions in RDBMS was executed and verified
EX NO 7 EXCEPTION HANDLING
SYNTAX:
DECLARE
<declaration section>
BEGIN
<executable commands>
EXECPTION
<exception handling>
End:
1. Create an exception if balance of an account number is less than 500.

SQL>select * from bank;
ACCNO DNAME BAL
101 trichy 37000
102 Deini 450
103 Mumbai 6000
104 chennai 2000
SQL>set serveroutput on;
SQL>declare
2 b number
3 n number
4 balanlow exception
5 balanok exception
4. begin
5. n:=&n;
6. select bal into b from bank where accno=n;
7. if (b<500)then
8. raise balanlow;
9. else
10. raise balanok;
11. end if;
12. exception
13. when balanlow then
14. dbms_output.put_line(‘YOUR balace is low’||b);
15. when balanok then
16. Dbms_output.put_line(YOUR balance is’||b);
17. end;
Enter value for n:102
Old 7: n:=&n;
New 7: n:=102;
YOUR balance is low 450

PL/SQL procedure successfully completed
SQL>
Enter value for n:103

Old 7: n:=&n;
New 7: n:=103;
YOUR balance is low 6000
PL/SQL procedure successfully completed
RESULT
Thus the exception handling was executed and verified successfully
Ex.No: 8 FRONT END TOOLS
AIM:
To Study about Front End tools.
INTRODUCTION:
Visual Basic is an object oriented programming development system for
creating applications.
FORMS:
In visual basic, a window is called form.
CONTROLS:
The icons with which the user interacts are called controls.
OBJECTS:
The forms and controls are collectively called as objects.
PROPERTIES:
Objects include properties that generally define their appearance or behavior.
METHODS:
Some objects also include special program statements called methods.
TITLE BAR:
It includes the project name, an icon that closes, minimize, maximize VB at the top
right
corner.
MENU BAR:
It consist of menus such as File, Edit, View, Project, Format,……..
TOOL BAR:
It consists of icons which duplicates several of the more commonly used menu
selections like forms, save, open and so on.
FORM DESIGN AND PROJECT CONTAINER WINDOW:
The form design window is where the user interface is actually designed. This is
accomplished by selecting the desired control icons from the toolbox and placing them
in the form design.
TOOLBOX:
The toolbox contains icons that represent commonly used controls, such as label,
text box, command button, picture box, frame, checkbox, combo box, option button,
scroll bar, and so on.
PROPERTIES WINDOW:
Each object has its own unique list of properties. The properties window allows
you to change or assign the properties associated with a particular object.
PROJECT WINDOW:
The project window displays the hierarchical list of files associated with a given
project.
CODE EDITOR WINDOW:

The code editor window will display the visual basic code associated with the
currently active form. By double clicking the on a control icon we can open the code
editor window.
FORM LAYOUT WINDOW:
Form Layout Window allows you to specify the screen location of the forms
within a project. To change the form location, simply drag the form icon to the desired
position.
VISUAL BASIC CONTROL TOOLS

CHECK BOX:
It provides a means of specifying a yes/no response. Within a group of check
boxes, any number of boxes can be checked.
COMBO BOX:
It provides the collection of text items one of which may be selected from the list .
COMMAND BUTTON:
Provides a means of initiating an event action by the user clicking on the button.
DATA:
Provides a means of displaying information from an existing database.
DIRECTORY LIST BOX:
Provides a means of selecting paths and directories (folders) within the current
drive.
FILE LIST BOX:
Provides a means of selecting files within the current directory.
FRAME:
Provides container for other controls. It is usually used to contain a group of
option buttons, check boxes or graphical shapes.
HORIZONTAL SCROLL BAR:
Allows a horizontal scroll bar to be added to a control.
VERTICAL SCROLL BAR:
Allows a vertical scroll bar to be added to a control.
IMAGE BOX:
Used to display graphical Objects, and to initiate event actions.
LABEL:
Used to display text on a form. The text cannot be reassigned during program
execution.
LINE:
Used to draw straight line segments within forms.
LIST BOX:
Provides a collection of text items.
OLE CONTAINER:
Allows a data object to be transferred from another windows application and
embedded within the visual basic applications.
OPTION BUTTON:
Provides a means of selecting one of several different options. Within a group of
option buttons one and only one can be selected.
SHAPE:
Used to draw circles, ellipse, squares and rectangles within the forms.
TEXT BOX:
It is used to enter text.
Tools in the Visual Basic Toolbox
Icon Tool Name What This Tool Does
Pointer Selects objects
Picture box Draws a box to display graphics
Label Draws a box to display text
Text box Draws a box that can display text and let the user type in text
Frame Groups two or more objects together
Command button Draws a command button
Check box Draws a check box
Option (or radio) Draws a radio button

button
Combo box Draws a combo box
List box Draws a list box
Horizontal scroll bar Draws a horizontal scroll bar
Vertical scroll bar Draws a vertical scroll bar
Timer Places a timer on a form
Draws a drive list box that displays all the disk drives
Drive list box available
Draws a directory list box that displays a directory on a

Directory list box particular
disk drive
File list box Draws a file list box that displays files in a specific directory
Shape Draws a geometric shape such as a circle or a square
Line Draws a line
Image box Draws a box to display graphics
Data control Draws a control to link a program to a database file
OLE Draws a box to insert an OLE object
RESULT:
Thus the Front end tools were studied.
EX: NO: 9 FORM DESIGN
AIM
To design a form using Visual Basic.
PROCEDURE
Step 1: Start
Step 2: Create the form with essential controls in tool box.
Step 3: Write the code for doing the appropriate functions.
Step 4: Save the forms and project.
Step 5: Execute the form.
Step 6: Stop.
EXECUTION OF BIODATA
FORM1
Private Sub Command1_Click()
List1.AddItem Text1.Text
If Option1.Value = True Then
gender = “male”
End If
If Option2.Value = True Then
gender = “female”
End If
List1.AddItem gender
If Check1.Value = 1 And Check2.Value = 0 Then
area = “software Engineering”
End If
area = “software Engineering & Networks”

End If
area = “ Networks”
End If
List1.AddItem area
End Sub
If List1.ListIndex <> 0 Then
List1.RemoveItem (0)
End If
End Sub
End
End Sub
SNAPSHOT:
EXECUTION OF CALCULATOR:
FORM1
Dim a As Integer
a = Val(Text1.Text) + Val(Text2.Text)
MsgBox (“Addition of Two numbers is” + Str(a))
End Sub
Dim b As Integer
b = Val(Text1.Text) - Val(Text2.Text)
MsgBox (“Subraction of Two numbers is” + Str(b))
End Sub
Dim c As Integer
c = Val(Text1.Text) * Val(Text2.Text)
MsgBox (“Multiplication of Two numbers is” + Str(c))
End Sub
Dim d As Integer
d = Val(Text1.Text) / Val(Text2.Text)
MsgBox (“Division of Two numbers is” + Str(d))
End Sub
End
End Sub
SNAPSHOT:
RESULT:
Thus the program for resume and calculator using Visual Basic was executed and
verified.
EX.NO: 10 MENU DESIGN
AIM:
To design a menu using Visual Basic.
PROCEDURE:
STEP 1: Start
STEP 2: Create the form with essential controls and insert the menu using menu editor.
STEP 3: Write the code for doing the appropriate functions.
STEP 4: Save the forms and project.
STEP 5: Execute the form.
STEP 6: Stop
EXECUTION:
FORM 1
Private Sub mapple_Click()
MsgBox (“You selected Apple”)
End Sub
Private Sub mblue_Click()
MsgBox (“You selected Blue color”)
End Sub
Private Sub mcircle_Click()
MsgBox (“You selected Circle”)
End Sub
Private Sub mexit_Click()
End
End Sub
Private Sub mgrapes_Click()
MsgBox (“You selected Grapes”)
End Sub
Private Sub mgreen_Click()
MsgBox (“You selected Green color “)
End Sub
Private Sub morange_Click()
MsgBox (“You selected Orange”)
End Sub
Private Sub mrectangle_Click()
MsgBox (“You selected Rectangle”)
End Sub
Private Sub mred_Click()
MsgBox (“You selected Red color”)
End Sub
Private Sub mtriangle_Click()
MsgBox (“You selected Triangle”)
End Sub
SNAPSHOT:
RESULT:
Thus the program for menu creation with menu editor has been developed and
executed successfully.
EX NO: 11 HOSPITAL MANAGEMENT SYSTEM
AIM
To develop a Hospital Management Application for Maintaining the Hospital
Process-ing.
PROCEDURE
Step1: Using Visual Studio 6.0 creates a necessary form to interacting with the database.
Step2: Connect database through Microsoft ADO Data Control 6.0(OLEDB).
Step3: To bring the ADO control into Visual Studio toolbox.
Step4: Click the Project Menu and then click the components.
Step5: Click the Control Tab; it will display number controls with checkbox for
choosing appropriate component.
Step6: Mark the Check box of Microsoft ADO Data Control 6.0(OLEDB), then click
Apply and ok button.
Step7: The ADO control is added in the Toolbox.
Step8: Place the ADO Control into the form in which the data base is required to connect.
Step9: Right click on the Control -> Click the ADODC properties->Property window
page appear.
Step10: Go to the General tab in that source of connection frame, select the use
connection string radio button, then click the build button.
Step11: Select the Provider tab, it show different number of providers, select the
Microsoft Jet 4.0 OLE DB provider.
Step12: Connect the data control to the form. Give the database name, user name and
pass-word
Step13: Click the test connection button. If entered correct password and username and
server name. It shows a Message Box test connection was succeeded.
Step14: Click the Record source tab, select command type combo box and choose
command type as 2-adCmdTable
Step15: Click the Table or Stored procedure combo box it will shows the tables from
the database. We can select the table which is going to be manipulated from the front
end.
Step16: Finally Click Apply and ok button.
Step17: Click the textbox and choose data source and data field for the
corresponding table.
Step18: Write the appropriate code for the necessary controls to manipulate
the process in run environment
Step19: Run the program by click run button in the standard toolbar.
EXECUTION
FORM1
If Text1.Text = “sundar” And Text2.Text = “1234” Then
Form2.Show
Unload Me
Else
MsgBox (“Login Failed! Try Again. .. ”)
Me.Refresh
End If
End Sub
Me.Refresh
End Sub
FORM2
Private Sub Add_Doctorname_Click()
Form3.Show
End Sub
Private Sub Add_Medicinename_Click()
Form5.Show
End Sub
Private Sub Add_Patientname_Click()
Form4.Show
End Sub
Private Sub Delete_Doctorname_Click()
Form3.Show
End Sub
Private Sub Delete_Medicinename_Click()
Form5.Show
End Sub
Private Sub Delete_Patientname_Click()
Form4.Show
End Sub
Private Sub Search_Doctorname_Click()
Form3.Show
End Sub
Private Sub Search_Medicinename_Click()
Form5.Show
End Sub
Private Sub Search_Patientname_Click()
Form4.Show
End Sub
FORM3
On Error GoTo Arr
Adodc1.Recordset.AddNew
MsgBox “Successfully Added”
Exit Sub
Arr:
MsgBox “Error in Adding”
End Sub
Private Sub Command2_Click(Index As Integer)
On Error GoTo Arr
Adodc1.Recordset.Update
MsgBox “Successfully updated”
Exit Sub
Arr:
MsgBox “Error in updating”
End Sub
On Error GoTo Arr
Adodc1.Recordset.Delete
MsgBox “Successfully Deleted”
Exit Sub
Arr:
MsgBox “Error in Deleting”
End Sub
Adodc1.Recordset.MoveFirst
End Sub
Adodc1.Recordset.MoveNext
End Sub
Adodc1.Recordset.MovePrevious
End Sub
Adodc1.Recordset.MoveLast
End Sub
Adodc1.Refresh
DataGrid1.Refresh
Adodc1.Recordset.Find “D_Id=” & Val(Text5.Text)
End Sub
Private Sub Form_Load()
Adodc1.Visible = False
End Sub
FORM4
On Error GoTo Arr
Exit Sub
Arr:
End Sub
On Error GoTo Arr
Exit Sub
Arr:
End Sub
On Error GoTo Arr
Exit Sub
Arr:
End Sub
End Sub
End Sub
End Sub
End Sub
Adodc2.Refresh
DataGrid2.Refresh
Adodc2.Recordset.Find “P_Id=” & Val(Text5.Text)
End Sub

End Sub
FORM5
On Error GoTo Arr
Exit Sub
Arr:
End Sub
On Error GoTo Arr
Exit Sub
Arr:
End Sub
On Error GoTo Arr
Exit Sub
Arr:
End Sub
End Sub
End Sub
End Sub
End Sub
Adodc3.Refresh
DataGrid3.Refresh
Adodc3.Recordset.Find “M_Id=” & Val(Text5.Text)
End Sub
End Sub
SCREENSHOTS:
RESULT:
Thus the project for hospital management system was designed in Visual Basic
using Microsoft Access as backend and successfully executed.
EX NO: 12 RAILWAY RESERVATION SYSTEM
AIM
To develop a railway reservation application for maintains the reservation processing.
PROCEDURE
Step1: Using Visual Studio 6.0 creates a necessary form to interacting with the database.
Step2: Connect database through Microsoft ADO Data Control 6.0(OLEDB).
Step3: To bring the ADO control into Visual Studio toolbox.
Step4: Click the Project Menu and then click the components.
Step5: Click the Control Tab; it will display number controls with checkbox for
choosing appropriate component.
Step6: Mark the Check box of Microsoft ADO Data Control 6.0(OLEDB), then click
Apply and ok button.
Step7: The ADO control is added in the Toolbox.
Step8: Place the ADO Control into the form in which the data base is required to connect.
Step9: Right click on the Control -> Click the ADODC properties->Property window
page appear.
Step10: Go to the General tab in that source of connection frame, select the use
connection string radio button, then click the build button.
Step11: Select the Provider tab, it show different number of providers, select the
Microsoft Jet 4.0 OLE DB provider.
Step12: Connect the data control to the form. Give the database name, user name and
pass-word
Step13: Click the test connection button. If entered correct password and username and
server name. It shows a Message Box test connection was succeeded.
Step14: Click the Record source tab, select command type combo box and choose
command type as 2-adCmdTable
Step15: Click the Table or Stored procedure combo box it will shows the tables from
the da-tabase. We can select the table which is going to be manipulated from the front
end.
Step16: Finally Click Apply and ok button.
Step17: Click the textbox and choose data source and data field for the
corresponding table.
Step18: Write the appropriate code for the necessary controls to manipulate
the process in run environment
Step19: Run the program by click run button in the standard toolbar.
EXECUTION:
FORM1
If Text3.Text = “sundar” And Text4.Text = “123” Then
Unload Me
MDIForm1.Show
Else
MsgBox (“Invalid Username/Password”)
End If
End Sub
End
End Sub
FORM2
Combo1.Text = “”
Check1.Value = False
Option6.Value = False
End Sub

DTPicker1.Value = Date$
Text1.Text = Temp1
s1 = “select * from reservation”
Set cn1 = New ADODB.Connection
cn1.CursorLocation = adUseClient
cn1.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=E:\Railway\Rail-way Reservation.mdb;Persist Security Info=False”
cn1.Open
Set rs1 = New ADODB.Recordset
rs1.CursorType = adOpenDynamic
rs1.LockType = adLockOptimistic
rs1.ActiveConnection = cn1
rs1.Open s1

cn2.Open
rs2.Open s2

cn3.Open
rs3.Open s3

cn4.Open
rs4.Open s4
cn5.Open
rs5.Open s5

cn6.Open
rs6.Open s6
Set Text5.DataSource = rs1

Text5.DataField = “Passenger_name”
Text11.DataField = “Age”
Set Combo1.DataSource = rs1
Combo1.DataField = “Sex”
Text2.DataField = “Train_No”
Text3.DataField = “Train_Name”
Text4.DataField = “From”
Text17.DataField = “To”
Text21.DataField = “Senior_Citizen”
Text22.DataField = “Date_Travel”
Text23.DataField = “class”
Text60.DataField = “PNR_NO”
Text67.DataField = “Seat_no”

Text24.DataField = “Train_no”
Text25.DataField = “Train_name”
Text27.DataField = “to”
Text30.DataField = “Class”



rs1.AddNew
rs2.AddNew
rs3.AddNew
rs4.AddNew
rs5.AddNew
rs6.AddNew
Text1.Text = Temp1
Adodc1.Recordset.Find “Train_No =” & Text1.Text, 0, adSearchForward
Text2.Text = Text1.Text
Text60.Text = Text59 + 1
End Sub
Private Sub check1_Click()
If Text11.Text < 60 Then
MsgBox (“Age Should Be More Than 60”)
End If
End Sub

End If
End Sub

End If
End Sub

End If
End Sub

End If
End Sub
Private Sub Option6_Click()

Option6.Value = False
End If
End Sub
Form3
Private Sub Text10_Change()
If Val(Text10.Text) > 0 And Val(Text10.Text) < 18 Then
Text2.Text = Text2.Text + 1
ElseIf Val(Text10.Text) > 18 And Val(Text10.Text) < 60 Then
ElseIf Val(Text10.Text) > 60 Then
End If
End Sub

End If
End Sub
End If
End Sub

End If
End Sub

End If
End Sub
End If
End Sub

If Text4.Text <> “” And Text4.Text <> “0” Then
End If
End Sub

End If
End Sub

End If
End Sub
End If
End Sub

End If
End Sub

End If
End Sub
FORM4
Private Sub Combo1_Click()
Adodc1.Refresh
Adodc1.Recordset.Find “Train_No =” & Combo1.Text, 0,
adSearchForward If Adodc1.Recordset.EOF = True Then MsgBox
(“Train not Available”)
End If
End Sub
Temp1 = Combo1.Text
Unload Me
Load Form2
Form2.Show
End Sub
Unload Me
End Sub
FORM5
Unload Me
End Sub

If MsgBox(“ Are you sure you want to cancel this ticket?”, vbYesNo, “RRS”) = vbYes
Then s1 = “delete from reservation where PNR_NO = “ & Text1.Text & “ “ Connect
(s1)
Temp5 = Text1.Text
n7 = Text2.Text
Unload Me
Load Form7
Form7.Show
End If
End Sub
s = “select * from reservation where PNR_NO = “ & Text1.Text &
“ “ Connect (s)
Set DataGrid1.DataSource = rs
Text2.Text = rs.RecordCount
End Sub
FORM6
Unload Me
End Sub

s = “select * from reservation where date_travel = ‘ “ + Combo1.Text +
“ ‘ “ Connect (s)
Set DataGrid1.DataSource = rs
End Sub
FORM7

Form12.Show
End Sub
Private Sub mnuAbt_Click()
frmAbout.Show
End Sub
Private Sub mnuCan_Click()
Form6.Show
End Sub
Private Sub mnuCascade_Click()
MDIForm1.Arrange vbCascade
End Sub
Private Sub mnuFD_Click()
Form13.Show
End Sub
Private Sub mnuFL_Click()
Form11.Show
End Sub
Private Sub mnuRepRes_Click()
Form8.Show
End Sub
Private Sub mnuRes_Click()
Form4.Show
End Sub
Private Sub mnuSearch_Click()
Form4.Show
SCREENSHOTS:
A.133
A.134-A
A.135
A.136-A
RESULT:
Thus the project for railway reservation system was designed in Visual Basic
using Microsoft Access as backend and successfully execute

cs8481 DBMS Lab Manual

Uploaded by

Copyright:

Available Formats

cs8481 DBMS Lab Manual

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

cs8481 DBMS Lab Manual

Uploaded by

Copyright:

Available Formats

ANNAI MIRA COLLEGE OF ENGINEERING AND TECHNOLOGY

NH-46, Chennai-Bengaluru National Highways, Arappakkam,

Email:amcet.rtet@gmail.com/info@amcet.in Web: www.amcet.in

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

CS8481 / DATABASE MANAGEMENT SYSTEMS

Register Number :…………………………………….

Year & Branch :…………………………………….

Academic Year :………………………….................

NH-46, Chennai-Bengaluru National Highways, Arappakkam,

Vellore-632517, TamilNadu, India

Telephone: 04172-292925 Fax: 04172-292926

Staff in –Charge Head of the Department

Submitted for the practical examination held on ------------------

Internal Examiner External Examiner

S.NO DATE LIST OF EXPERIMENTS PG.NO SIGNATURE

5.7.2021 Data Definition Commands, Data Manipulation

2 Database Querying – Simple queries, Nested

3 Views, Sequences, Synonyms

10 24.7.2021 Case Study using real life database applications

SQL> Create table Student(Stud_name char(20), Stud_id varchar2(10), Stud_dept

SQL> desc Student;

SQL> Alter table Student add(Stud_addr

SQL> desc Student;

Name Null? Type

Name Null? Type

SQL> Alter table Student drop (Stud_age number(10));

SQL> desc Student;

Name Null? Type

Name Null? Type

SQL> Drop table Student;

SQL> desc Student;

SQL> desc Student;

SQL> Insert into Student1 values(‘&stud_name’, ‘&stud_id’, ‘&stud_dept’,

SQL> Insert into Student1 values(‘&stud_name’, ‘&stud_id’, ‘&stud_dept’,

SQL> Insert into Student1 values(‘&stud_name’, ‘&stud_id’, ‘&stud_dept’,

SQL> Insert into Student1 values(‘&stud_name’, ‘&stud_id’, ‘&stud_dept’,

2. Select Command: It is used to retrieve information from the table. It is generally

Ram 101 MECH 14

SQL> Update Student1 set stud_id=’109’ where stud_name=’Sarath’; 1

SQL> select * from Student1;

STUD_NAME STUD_ID STUD_DEPT STUD_AGE

Ram 101 MECH 14

SQL> Delete from Student1 where stud_dept=’CSE’; 1

SQL> select * from Student1;

STUD_NAME STUD_ID STUD_DEPT STUD_AGE

Ram 101 MECH 14

SQL> SAVEPOINT <SAVE POINT NAME>;

SQL> SAVEPOINT S1;

SQL> SELECT * FROM EMP;

EMPNO ENAME DESIGNATIN SALARY

SQL> INSERT INTO EMP

105 PARTHASAR STUDENT 100

Syntax for save point:

SQL> ROLL BACK <SAVE POINT NAME>;

SQL> ROLL BACK S1;

SQL> SELECT * FROM EMP;

101 NAGARAJAN LECTURER 16000

SQL>CREATE USER CSE2 IDENTIFIED BY