DATABASE MANAGEMENT SYSTEMS

LAB File

Katihar Engineering College, Katihar

 Introduction
1. DDL: Data Definition Language (DDL) statements are used to define the database structure or
schema.

DDL Commands: Create, Alter, Drop, Rename, Truncate

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

RENAME - rename an object

2. DML: Data Manipulation Language (DML) statements are used for managing data within schema
objects and to manipulate data of a database objects.

DML Commands: Insert, Update, Delete, Select

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

SELECT - retrieve data from the a database

3. DCL: Data Control Language (DCL) statements are used to create roles, permissions, and referential
integrity as well it is used to control access to database by securing it. To control the data of a database.

DCL Commands: Grant, Revoke

GRANT - gives user's access privileges to database

REVOKE -withdraw access privileges given with the GRANT command

4. TCL: Transaction Control (TCL) statements are used to manage the changes made by DML
statements. It allows statements to be grouped together into logical transactions.

TCL Commands: Commit, Rollback, Save point

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

1|Page
Syntax with examples

1. DDL (Data Definition Language) Commands: CREATE, ALTER and DROP.

CREATE: This command useful for creating creating table.

Syntax:

create table [table name] (column1 datatype[size], column 2 datatype[size],… column n datatype[size] );

Ex:

SQL >create table student (s_rollno number(10) primary key,s_name varchar2(10), gender varchar2(5),dob
date,addr1 varchar2(10),addr2 varchar2(10),city varchar2(10), percentage number(4));

SQL> DESC STUDENT;

Name Null? Type

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

S_ROLLNO NOT NULL NUMBER(10)

S_NAME VARCHAR2(10)

GENDER VARCHAR2(5)

DOB DATE

ADDR1 VARCHAR2(10)

ADDR2 VARCHAR2(10)

CITY VARCHAR2(10)

PERCENTAGE NUMBER(4)

SQL > select s_rollno,s_name from student;

no rows selected.

Create table by using Constraints:

Constraints are two types:

1. Table Level Constraints.

2. Column Level Constraints.

1. NOT NULL:

a) Not null constraint at column level.

Syntax:

2|Page
<col><datatype>(size)not null

SQL > create table emp(e_id varchar(5) NOT NULL,e_name varchar(10), e_design varchar(10),dept
varchar(10),mgr varchar(10),salary number(10));

2. UNIQUE :

Unique constraint at column level.

Syntax: <col><datatype>(size)unique

Ex:-

SQL > create table depositor(customer_name varchar(10),acc_no number(15) UNIQUE, brach_name

varchar(10));

Unique constraint at table level:

Syntax:

Create table tablename(col=format,col=format,unique(<col1>,<col2>));

Ex:-

SQL > create table depositor1(customer_name varchar(10),acc_no number(15), brach_name

varchar(10),UNIQUE(acc_no));

3. PRIMARY KEY:

Primary key constraint at column level

Syntax:

<col><datatype>(size)primary key;

Ex:-

SQL> create table customer(customer_id number (5) PRIMARY KEY, customer_name

varchar(10),customer_street varchar(10),brach_name varchar(10));

Primary key constraint at table level.

Syntax:

Create table tablename(col=format,col=format primary key(col1>,<col2>);

Ex:-

SQL > create table customer1(customer_id number (5),customer_name varchar(10),customer_street

varchar(10),brach_name varchar(10),PRIMARY KEY(customer_id));

4. CHECK:

3|Page
Check constraint constraint at column level.

Syntax: <col><datatype>(size) check(<logical expression>)

Ex:-create table loan(loan_no varchar(10),customer_name varchar(10), balance number (10)

CHECK(balance>1000));

Check constraint constraint at table level.

Syntax: check(<logical expression>)

Ex:- create table loan1(loan_no varchar(10),customer_name varchar(10), balance number (10),

CHECK(balance>1000));

5. FOREIGN KEY:

Foreign key constraint at column level.

Syntax:

Column_name Datatype(size) REFERENCES parent_table_name (parent_column_name)

Ex:- CREATE TABLE books (book_id NUMBER(3), book_title VARCHAR2(30), book_price

NUMBER(3), book_author_id NUMBER(3) REFERENCES author(author_id ) );

Foreign key constraint at table level

Syntax:

CONSTRAINT constraint_name FOREIGN KEY(child_table_column) REFERENCES

Parent_table_name(parent_table_column)

Ex:-CREATE TABLE books (book_id NUMBER(3) CONSTRAINT bok_bi_pk PRIMARY

KEY, book_title VARCHAR2(30), book_price NUMBER(3), book_author_id
NUMBER(3),CONSTRAINT bok_ai_fk FOREIGN KEY (book_author_id) REFERENCES
author(author_id) );

4|Page
 CREATION OF TABLES

1) Create a table called Employee with the following structure.

Name Type
Empno Number
Ename Varchar2(10)
Job Varchar2(10)
Mgr Number
Sal Number

a. Add a column commission with domain to the Employee table.

b. Insert any five records into the table.
c. Update the column details of job
d. Rename the column of Employ table using alter command.
e. Delete the employee whose Empno is 105.

SOLUTION:
SQL> create table employee(empno number,ename varchar2(10),job varchar2(10),mgr
number,sal number);
Table created.
SQL> desc employee;
Name Null? Type
--------------------------- -------- ----------------------------
EMPNO NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER
SAL NUMBER

a. Add a column commission with domain to the Employee table.

SQL> alter table employee add(commission number);

Table altered.
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER
SAL NUMBER
COMMISSION NUMBER

b. Insert any five records into the table.

SQL> insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission');

Enter value for empno: 101
Enter value for ename: abhi
Enter value for job: manager

5|Page
Enter value for mgr: 1234
Enter value for sal: 10000
Enter value for commission: 70
old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')
new 1: insert into employee values(101,'abhi','manager',1234,10000,'70')
1 row created.

SQL> /
Enter value for empno: 102
Enter value for ename: rohith
Enter value for job: analyst
Enter value for mgr: 2345
Enter value for sal: 9000
Enter value for commission: 65
old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')
new 1: insert into employee values(102,'rohith','analyst',2345,9000,'65')
1 row created.
SQL> /
Enter value for empno: 103
Enter value for ename: david
Enter value for job: analyst
Enter value for mgr: 3456
Enter value for sal: 9000
Enter value for commission: 65
old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')
new 1: insert into employee values(103,'david','analyst',3456,9000,'65')
1 row created.

SQL> /
Enter value for empno: 104
Enter value for ename: rahul
Enter value for job: clerk
Enter value for mgr: 4567
Enter value for sal: 7000
Enter value for commission: 55
old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')
new 1: insert into employee values(104,'rahul','clerk',4567,7000,'55')
1 row created.

SQL> /
Enter value for empno: 105
Enter value for ename: pramod
Enter value for job: salesman
Enter value for mgr: 5678
Enter value for sal: 5000
Enter value for commission: 50
old 1: insert into employee values(&empno,'&ename','&job',&mgr,&sal,'&commission')
new 1: insert into employee values(105,'pramod','salesman',5678,5000,'50')
1 row created.

SQL> select * from employee;

6|Page
 EMPNO ENAME JOB MGR SAL COMMISSION
---------- ---------- ---------- ---------- ---------- ----------
101 abhi manager 1234 10000 70
102 rohith analyst 2345 9000 65
103 david analyst 3456 9000 65
104 rahul clerk 4567 7000 55
105 pramod salesman 5678 5000 50

c. Update the column details of job

SQL> update employee set job='trainee' where empno=103;

1 row updated.

SQL> select * from employee;

EMPNO ENAME JOB MGR SAL COMMISSION
---------- ---------- ---------- ---------- ---------- ----------
101 abhi manager 1234 10000 70
102 rohith analyst 2345 9000 65
103 david trainee 3456 9000 65
104 rahul clerk 4567 7000 55
105 pramod salesman 5678 5000 50

d. Rename the column of Employ table using alter command.

SQL> alter table employee rename column mgr to manager_no;

Table altered.

SQL> desc employee;

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MANAGER_NO NUMBER
SAL NUMBER
COMMISSION NUMBER

e. Delete the employee whose Empno is 105.

SQL> delete employee where empno=105;

1 row deleted.

SQL> select * from employee;

EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------- ---------- ---------- ---------- ---------- ----------
101 abhi manager 1234 10000 70
102 rohith analyst 2345 9000 65
103 david trainee 3456 9000 65
104 rahul clerk 4567 7000 55

7|Page
2) Create department table with the following structure.

Name Type
Deptno Number
Deptname Varchar2(10)
location Varchar2(10)

a. Add column designation to the department table.

b. Insert values into the table.
c. List the records of dept table grouped by deptno.
d. Update the record where deptno is 9.
e. Delete any column data from the table.

SOLUTION:
SQL> create table department(deptno number,deptname varchar2(10),location varchar2(10));
Table created.

SQL> desc department;

Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER
DEPTNAME VARCHAR2(10)
LOCATION VARCHAR2(10)

a. Add column designation to the department table.

SQL> alter table department add(designation varchar2(10));

Table altered.

SQL> desc department;

Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER
DEPTNAME VARCHAR2(10)
LOCATION VARCHAR2(10)
DESIGNATION VARCHAR2(10)

b. Insert values into the table.

SQL> insert into department values(&deptno,'&deptname','&location','&designation');

Enter value for deptno: 9
Enter value for deptname: accounting
Enter value for location: hyderabad
Enter value for designation: manager
old 1: insert into department values(&deptno,'&deptname','&location','&designation')
new 1: insert into department values(9,'accounting','hyderabad','manager')

1 row created.

SQL> /

8|Page
Enter value for deptno: 10
Enter value for deptname: research
Enter value for location: chennai
Enter value for designation: professor
old 1: insert into department values(&deptno,'&deptname','&location','&designation')
new 1: insert into department values(10,'research','chennai','professor')

1 row created.
SQL> /
Enter value for deptno: 11
Enter value for deptname: sales Enter
value for location: banglore Enter
value for designation: salesman
old 1: insert into department values(&deptno,'&deptname','&location','&designation')
new 1: insert into department values(11,'sales','banglore','salesman')
1 row created.
SQL> /
Enter value for deptno: 12
Enter value for deptname: operations
Enter value for location: mumbai
Enter value for designation: operator
old 1: insert into department values(&deptno,'&deptname','&location','&designation')
new 1: insert into department values(12,'operations','mumbai','operator')
1 row created.

SQL> insert into department values(&deptno,'&deptname','&location','&designation');

Enter value for deptno: 9
Enter value for deptname: accounting
Enter value for location: chennai
Enter value for designation: manager
old 1: insert into department values(&deptno,'&deptname','&location','&designation')
new 1: insert into department values(9,'accounting','chennai','manager')
1 row created.

SQL> select * from department ;

DEPTNO DEPTNAME LOCATION DESIGNATION

---------------- ------------------- ----------------- -------------------
9 accounting hyderabad manager
10 research chennai professor
11 sales banglore salesman
12 operations mumbai operator
9 accounting chennai manager

c. List the records of dept table grouped by deptno.

SQL> select deptno,deptname from department group by deptno,deptname;

DEPTNO DEPTNAME
---------- ----------
9 accounting

9|Page
 12 operations
10 research
11 sales

d. Update the record where deptno is 9.

SQL> update department set designation='accountant' where deptno=9;

2 rows updated.
SQL> select * from department;
DEPTNO DEPTNAME LOCATION
DESIGNATION
---------------- ------------------- ----------------- -------------------
9 accounting hyderabad accountant
10 research chennai professor
11 sales banglore salesman
12 operations mumbai operator
9 accounting chennai accountant

e. Delete any column data from the table.

SQL> alter table department

drop(designation); Table altered.
SQL> select * from department;
DEPTNO DEPTNAME LOCATION
---------- --------------- ----------
9 accounting hyderabad
10 research chennai
11 sales banglore
12 operations mumbai
9 accounting Chennai
 QUERIES USING DDL AND DML

1. a. Create a user and grant all permissions to the user.

b. Insert the any three records in the employee table and use rollback. Check the result.
c. Add primary key constraint and not null constraint to the employee table.
d. Insert null values to the employee table and verify the result.

SOLUTION:

a) create a user and grant all permissions to the user.

CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;

--Create user query

CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;

--Provide roles

GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;

--Assigning privileges

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO <USER NAME>;

GRANT UNLIMITED TABLESPACE TO <USER NAME>;

--Provide access to tables.

GRANT SELECT, UPDATE, INSERT, DELETE ON <TABLE NAME> TO <USER NAME>;

b) Insert the any three records in the employee table and use rollback. Check the result.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------------- ---------- ---------- --------------------- ------ -------------------
101 abhi manager 1234 1100 70
102 rohith analyst 2345 9000 65
103 david trainee 3456 9000 65
104 rahul clerk 4567 7000 55

SQL> insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission);

Enter value for empno: 105
Enter value for ename: aravind
Enter value for job: salesman
Enter value for manager_no: 5678
Enter value for sal: 5000
Enter value for commission: 50
old 1: insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission)
new 1: insert into employee values(105,'aravind','salesman',5678,5000,50)

12 | P a g e
1 row created.

SQL> rollback;
Rollback complete.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------------- ---------- ---------- --------------------- ------ -------------------
101 abhi manager 1234 1100 70
102 rohith analyst 2345 9000 65
103 david trainee 3456 9000 65
104 rahul clerk 4567 7000 55

c) Add primary key constraint and not null constraint to the employee table.
SQL> alter table employee modify(empno number primary key, ename varchar2(10) not null);
Table altered.
SQL> desc employee;
Name Null? Type
-------------------------- -------- -------------------
EMPNO NOT NULL NUMBER
ENAME NOT NULL VARCHAR2(10)
JOB VARCHAR2(10)
MANAGER_NO NUMBER
SAL NUMBER
COMMISSION NUMBER

d) Insert null values to the employee table and verify the result.
SQL> desc employee;
Name Null? Type
-------------------- ------------------ ----------------------------
EMPNO NOT NULL NUMBER
ENAME NOT NULL VARCHAR2(10)
JOB NOT NULL VARCHAR2(10)
MANAGER_NO NUMBER
SAL NOT NULL NUMBER
COMMISSION NUMBER

SQL> insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission);

Enter value for empno: 105
Enter value for ename: mohith
Enter value for job: salesman
Enter value for manager_no: 5678
Enter value for sal: null
Enter value for commission: 50
old 1: insert into employee values(&empno,'&ename','&job',&manager_no,&sal,&commission)
new 1: insert into employee values(105,'mohith','salesman',5678,null,50)
insert into employee values(105,'mohith','salesman',5678,null,50)
*

13 | P a g e
2. a. create a user and grant all permissions to the user.
b. Insert values in the department table and use commit.
c. Add constraints like unique and not null to the department table.
d. Insert repeated values and null values into the table.

SOLUTION:

a) create a user and grant all permissions to the user.

CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;

--Create user query

CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;

--Provide roles

GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;

--Assigning privileges

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO <USER NAME>;

GRANT UNLIMITED TABLESPACE TO <USER NAME>;

--Provide access to tables.

GRANT SELECT, UPDATE, INSERT, DELETE ON <TABLE NAME> TO <USER NAME>;

b) Insert values in the department table and use commit.

SQL> insert into department values(&deptno,'&deptname','&location');

Enter value for deptno: 13
Enter value for deptname: sales
Enter value for location: delhi
old 1: insert into department values(&deptno,'&deptname','&location')
new 1: insert into department values(13,'sales','delhi')
1 row created.

SQL> commit;
Commit complete.

SQL> select * from department;

DEPTNO DEPTNAME LOCATION
---------- -------------- ----------------
9 accounting hyderabad
10 research chennai
11 sales banglore
12 operations mumbai
9 accounting chennai
13 sales delhi

14 | P a g e
6 rows selected.
c) Add constraints like unique and not null to the department table.

SQL> alter table department modify(deptno number unique);

Table altered.

SQL> alter table department modify(location varchar2(10) not null);

Table altered.

SQL> DESC DEPARTMENT;

Name Null? Type
-------------------- ---------------- -------------------
DEPTNO NUMBER
DEPTNAME VARCHAR2(10)
LOCATION NOT NULL VARCHAR2(10)

d) Insert repeated values and null values into the table.

SQL> insert into department values(&deptno,'&deptname','&location');

Enter value for deptno: 10
Enter value for deptname: research
Enter value for location:
old 1: insert into department values(&deptno,'&deptname','&location')
new 1: insert into department values(10,'research','')
insert into department values(10,'research','')
SQL> insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 10
Enter value for deptname: research
Enter value for location: hyderabad
old 1: insert into department values(&deptno,'&deptname','&location')
new 1: insert into department values(10,'research','hyderabad')
insert into department values(10,'research','hyderabad')
 QUERIES USING AGGREGATE FUNCTIONS

AIM :- Queries using aggregate functions(COUNT,AVG,MIN,MAX,SUM),Group

by,Order by,Having.

E_id E_name Age Salary

101 Anu 22 9000
102 Shane 29 8000
103 Rohan 34 6000
104 Scott 44 10000
105 Tiger 35 8000
106 Alex 27 7000
107 Abhi 29 8000

(i) Create Employee table containing all Records.

SQL> create table emp(eid number,ename varchar2(10),age number,salary number);
Table created.
SQL> desc emp;
Name Null? Type
----------------------- -------- ----------------------------
EID NUMBER
ENAME VARCHAR2(10)
AGE NUMBER
SALARY NUMBER

(ii)Count number of employee names from employee table.

SQL> select count(ename) from emp;
COUNT(ENAME)
-------------------------
7
(iii)Find the Maximum age from employee table.
SQL> select max(age) from emp;
MAX(AGE)
-----------------
44
(iv)Find the Minimum age from employee table.
SQL> select min(age) from emp;
MIN(AGE)
----------------
22
(v)Display the Sum of age employee table.
SQL> select sum(age) from emp;
SUM(AGE)
----------------
220

17 | P a g e
(vi)Display the Average of age from Employee table.
SQL> select avg(age) from emp;
AVG(AGE)
----------------
31.4285714
(vii)Create a View for age in employee table.
SQL> create or replace view A as select age from emp where age<30;
View created.
(viii)Display views
SQL> select * from A;
AGE
-------------
22
29
27
29
(ix)Find grouped salaries of employees.(group by clause)
SQL> select salary from emp group by salary;
SALARY
--------------
9000
10000
8000
6000
7000
(x).Find salaries of employee in Ascending Order.(order by clause)
SQL> select ename,salary from emp order by salary;
ENAME SALARY
------------ -------------
rohan 6000
alex 7000
shane 8000
abhi 8000
tiger 8000
anu 9000
scott 10000

7 rows selected.
(xi) Find salaries of employee in Descending Order.
SQL> select ename,salary from emp order by salary desc;
ENAME SALARY
-------------- ---------------
scott 10000
anu 9000
shane 8000
abhi 8000
tiger 8000
 alex 7000
rohan 6000
7 rows selected.
(xii)Having Clause.
SQL> select ename,salary from emp where age<29 group by ename,salary having
salary<10000;
ENAME SALARY
----------- --------------
alex 7000
anu 9000
 PROGRAMS ON PL/SQL

1 a) Write a PL/SQL block to find the maximum number from given three numbers.

declare
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=&c;
if (a>b and a>c) then
dbms_output.put_line('a is maximum ' || a);
elsif (b>a and b>c) then
dbms_output.put_line('b is maximum ' || b);
else
dbms_output.put_line('c is maximum ' || c);
end if;
end;
/

1b) write a PL/SQL program for swapping 2 numbers.

declare
a number(3);
b number(3);
begin
a:=&a;
b:=&b;
dbms_output.put_line(„Before swapping a= „||a||‟ and b= „||b);
a:=a+b;
b:=a-b;
a:=a-b;
dbms_output.put_line(„After swapping a= „||a||‟ and b= „||b);
end;
/

2 a) Write a PL/SQL program to find the total and average of 4 subjects and display the
grade

declare
java number(10);
dbms number(10);
co number(10);
mfcs number(10);
total number(10);
avgs number(10);
per number(10);
begin
dbms_output.put_line('ENTER THE MARKS');

21 | P a g e
 java:=&java;
dbms:=&dbms;
co:=&co;
mfcs:=&mfcsl;
total:=(java+dbms+co+mfcs);
per:=(total/600)*100;
if java<40 or dbms<40 or co<40 or mfcs<40 then
dbms_output.put_line('FAIL');
if per>75 then
dbms_output.put_line('GRADE A');
elsif per>65 and per<75 then
dbms_output.put_line('GRADE B');
elsif per>55 and per<65 then
dbms_output.put_line('GRADE C');
else
dbms_output.put_line('INVALID INPUT');
end if;
dbms_output.put_line('PERCENTAGE IS '||per);
end;
/

2 b) Write a program to accept a number and find the sum of the digits

declare
n number(5):=&n;
s number:=0;
r number(2):=0;
begin
while n !=0
loop
r:=mod(n,10);
s:=s+r;
n:=trunc(n/10);
end loop;
dbms_output.put_line('sum of digits of given number is '||s);
end;
/
3 a) PL/SQL Program to accept a number from user and print number in reverse order.

declare
num1 number(5);
num2 number(5);
rev number(5);
begin
num1:=&num1;
rev:=0;
while num1>0
loop
num2:=num1 mod 10;
rev:=num2+(rev*10);
num1:=floor(num1/10);
end loop;

22 | P a g e
 dbms_output.put_line('Reverse number is: '||rev);
end;
/
3b) Write a PL / SQL program to check whether the given number is prime or not.
declare
num number;
i number:=1;
c number:=0;
begin
num:=&num;
for i in 1..num
loop
if((mod(num,i))=0)
then
c:=c+1;
end if;
end loop;
if(c>2)
then
dbms_output.put_line(num||' not a prime');
else
dbms_output.put_line(num||' is prime');
end if;
end;
/
4 a) Write a PL/SQL program to find the factorial of a given number.
declare
i number(4):=1;
n number(4):=&n;
f number(4):=1;
begin
for i in 1..n
loop
f:=f*i;
end loop;
Dbms_output.put_line('the factorial of '||n||' is:'||f);
end;
/
4 b) calculate the area of a circle for a value of radius varying from 3 to 7. Store the radius
and the corresponding values of calculated area in table areas. Consisting of two columns
radius and area
Declare
pi constant number(4,2) := 3.14;
radius number(5);
area number(14,2);
Begin
radius := 3;
While radius <=7
Loop
area := pi* power(radius,2);
Insert into areas values (radius, area);

23 | P a g e
 radius:= radius+1;
end loop;
end;
/
5a) Write a PL/SQL program to accept a string and remove the vowels from the string.
(When ‘hello’ passed to the program it should display ‘Hll’ removing e and o from the
world Hello).
set serveroutput on
set verify off
accept vstring prompt "Please enter your string: ";
declare
vnewstring varchar2(100);
begin
vnewstring := regexp_replace('&vstring', '[aeiouAEIOU]','');
dbms_output.put_line('The new string is: ' || vnewstring);
end;
/
5 b) Write a PL/SQL program to accept a number and a divisor. Make sure the divisor is
less than or equal to 10. Else display an error message. Otherwise Display the remainder.

select remainder(37,5) "remainder" from dual ;

24 | P a g e
 Function
1) Create a function to find the factorial of a given number and hence find NCR.
SQL> create or replace function fact(n number)
return number is
a number:=n;
f number:=1;
i number;
begin
for i in 1..n
loop
f:=f*a;
a:=a-1;
end loop;
return f;
end;
/

25 | P a g e
 SQL> create or replace function ncr(n number ,r number)
return number is
n1 number:=fact(n);
r1 number:=fact(r);
nr1 number:=fact(n-r);
result number;
begin
result:=(n1)/(r1*n
r1); return result;
end;
/

3) Print Fibonacci series using local functions.

sql>create or replace function fib (n positive) return
integer is begin
if (n = 1) or (n = 2) then -- terminating
condition return 1;
else
return fib(n - 1) + fib(n - 2); -- recursive call
end if;
end fib;
/
-- Test Fibonacci Series:
SQL>SELECT fib(1), fib(2), fib(3), fib(4), fib(5) FROM dual;
27 | P a g e
 TRIGGERS

1. Create a row level trigger for the customers table that would fire for INSERT or UPDATE
or DELETE operations performed on the CUSTOMERS table. This trigger will display the
salary difference between the old values and new values:

CUSTOMERS table:

ID NAME AGE ADDRESS SALARY

1 Alive 24 Khammam 2000
2 Bob 27 Kadappa 3000
3 Catri 25 Guntur 4000
4 Dena 28 Hyderabad 5000
5 Eeshwar 27 Kurnool 6000
6 Farooq 28 Nellur 7000

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
Trigger created.

Here following two points are important and should be noted carefully:

OLD and NEW references are not available for table level triggers, rather you can use them for
record level triggers.

If you want to query the table in the same trigger, then you should use the AFTER keyword,
because triggers can query the table or change it again only after the initial changes are applied and the table
is back in a consistent state.

Above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE
operation on the table, but you can write your trigger on a single or multiple operations, for example
BEFORE DELETE, which will fire whenever a record will be deleted using DELETE operation on the
table.

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which
will create a new record in the table:
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Kriti', 22, 'HP',
7500.00 );

28 | P a g e
When a record is created in CUSTOMERS table, above create trigger display_salary_changes will be
fired and it will display the following result:

Old salary:
New salary: 7500
Salary difference:

2) Trigger before deleting a record from emp table. Trigger will insert the row to
be deleted into another table and also record the user who has deleted the record.

SQL> CREATE OR REPLACE TRIGGER employee_before_delete

2 BEFORE DELETE
3 ON employee
4 FOR EACH ROW
5 DECLARE
6 v_username varchar2(10);
7 BEGIN
8 -- Find username of person performing the DELETE on the table
9 SELECT user INTO v_username
10 FROM dual;
11 -- Insert record into audit table
12 INSERT INTO employee_audit (id, salary, delete_date,deleted_by )
13 VALUES (:old.id,:old.salary, sysdate, v_username );
14 END;
15 /

Trigger created.

SQL> delete from employee;

8 rows deleted.

SQL> select * from employee_audit;

ID SALARY DELETE_DA DELETED_BY

---- ---------- --------- ---------------
01 1234.56 09-SEP-06 JAVA2S
02 6661.78 09-SEP-06 JAVA2S
03 6544.78 09-SEP-06 JAVA2S
04 2344.78 09-SEP-06 JAVA2S

31 | P a g e
05 2334.78 09-SEP-06 JAVA2S
06 4322.78 09-SEP-06 JAVA2S
07 7897.78 09-SEP-06 JAVA2S
08 1232.78 09-SEP-06 JAVA2S

8 rows selected.

SQL> drop table employee_audit;

Table dropped.

33 | P a g e
 WEEK-8
CURSORS

DEFINITION OF A CURSOR

1. Cursor can be created to store the values from table temporally.

2. In execution these values fetch from cursor for access the data base
Create cursor fetch the values from the table
Declare the variables
Open the cursor
Fetch the values from the cursor
Close the cursor

CURSOR EXAMPLE:
declare
cursor xx is select empno,ename,sal from emp26;
a_empno emp26.empno%type;
a_ename emp26.ename%type;
a_sal emp26.sal%type;
begin
open xx;
loop
fetch xx into a_empno,a_ename,a_sal; exit when xx%
not found; dbms_output.put_line(a_empno||'
'||a_ename||' '||a_sal); end loop;
close xx;
end;