SEP DBMS LAB MANUAL

PART-A
1. Execute DDL Commands
Consider the table:
STUDENT (regno number, name varchar2, dob date, marks number)
a) Create the above table with suitable constraints.
b) Remove the existing attribute marks from the table.
c) Change the data type of regno from number to varchar2.
d) Add a new attribute phno to the existing table.
e) Insert 5 tuples into the table.
f) Display the tuples in table

Answer
a) CREATE TABLE STUDENT
(
regno NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
dob DATE,
marks NUMBER
);

b)ALTER TABLE STUDENT DROP COLUMN marks;

c)ALTER TABLE STUDENT MODIFY regno VARCHAR2(20);

d) ALTER TABLE STUDENT ADD phno VARCHAR2(15);

e) INSERT INTO STUDENT3 VALUES (001, 'Alice', '12-05-2005', 9987943421);

INSERT INTO STUDENT3 VALUES (003, 'Seetha', '08-06-2003', 7432789865);
INSERT INTO STUDENT3 VALUES (005, 'Johm', '01-03-2004', 8952341761);
INSERT INTO STUDENT3 VALUES (006, 'Ram', '18-09-2005', 864567234);
INSERT INTO STUDENT3 VALUES (0011, 'Geetha', '23-05-2003', 7653489712);

f) SELECT * FROM STUDENT;

Dr.Chandrika G, HOD, SIGS Page 1

 SEP DBMS LAB MANUAL

2. Execute DML Commands

Consider the table:
LIBRARY (bid number, title varchar2, author varchar2, publisher varchar2,
year_of_pub number, price number)
a) create the above table.
b) Enter 5 tuples into the table.
c) Display all the tuples from the table.
d) Display different publishers from table.
e) Update price of all books with 5% GST amount.
f) Delete the details of book published by a specific Author.
g) Arrange the tuples in the alphabetical order of book title.
h) List the details of all books whose price range between 100 rs and 300 rs.

Answer
a)
CREATE TABLE LIBRARY
(
bid NUMBER PRIMARY KEY,
title VARCHAR2(255),
au thor VARCHAR2(255),
publisher VARCHAR2(255),
year_of _pub NUMBER,
price NUMBER
);
b)
INSERT INTO LIBRARY VALUES (101, 'Sri RAMAYANA Darshanam', 'Sri KUVEMPU', 'IBH Prakashana',
1972, 880);
INSERT INTO LIBRARY VALUES (1 06, 'MacBeth', 'William Shakespear e', 'Dover Publica tions', 2000,
300);
INSERT INTO LIBRARY VALUES (1 07, 'Wings of fire', 'APJ Abdul Kalam', '‎Universities Press', 2008,
450);
INSERT INTO LIBRARY VALUES (1 11, 'Naaku Tanti', 'Dr . Da. Ra. Bendre', 'Srimath a Prakashana',
1983, 789);
INSERT INTO LIBRARY VALUES (109, 'HAYAVADANA', 'Girish Karnad', 'OXFORD INDIA PERENNIALS',
2012, 299);

Dr.Chandrika G, HOD, SIGS Page 2

 SEP DBMS LAB MANUAL

c)select * from LIBRARY;

d) SELECT DISTINCT publisher FROM LIBRARY ;

e) UPDATE LIBRARY SET price = price + (price * 0.05);

f) DELETE FROM LIBRARY WHERE bid = 109;

g) SELECT * FROM LIBRARY ORDER BY title ASC;

h) SELECT * FROM LIBRARY WHERE price BETWEEN 300 AND 500;

3. Execute DQL and group functions

Consider the table EMPLOYEE (EmpNo, EmpName, Dept, Salary, DOJ, Branch)
Perform the following operations:
a) Create the table
b) Insert 5 tuples into the table
c) Retrieve average salary of all employee
d) Retrieve number of employees
e) Retrieve distinct number of employee
f) Retrieve total salary of employee group by employee name and count similar names
g) Display details of employees whose salary is greater than 50000.

Answer:
a) CREATE TABLE EMPLOYEE
(
EmpNo NUMBER PRIMARY KEY,
EmpName VARCHAR2(100),
Dept VARCHAR2(50),
Salary NUMBER(10, 2),
DOJ DATE,
Branch VARCHAR2(100)
);
b)
INSERT INTO EMPLOYEE VALUES (101, 'Anita ' , 'HR' , 48000.00,' 15-06-2018','Mumbai' );
Dr.Chandrika G, HOD, SIGS Page 3
 SEP DBMS LAB MANUAL

INSERT INTO EMPLOYEE VALUES (102, 'Rajesh ', 'Finance' , 55000.00,'10-04-2019' ,' Delhi' );
INSERT INTO EMPLOYEE VALUES (103, 'chaitra' , 'IT', 62000.00,' 10-04-2019','Pune' );
INSERT INTO EMPLOYEE VALUES (104, ' Thanush', 'Marketing' , 51000.00,' 22-05-2013','Hyderabad' );
INSERT INTO EMPLOYEE VALUES (105, 'Sara Ali', 'HR' , 47000.00,' 18-11-2022',' Bangalore' );

c) SELECT AVG(Salary) AS Avgsal FROM EMPLOYEE;

d) SELECT COUNT(*) AS To talemp FROM EMPLOYEE;

e) SELECT COUNT(DISTINCT DOJ) AS DOJemp FROM EMPLOYEE;

f) SELECT
EmpName,
SUM(Salary) AS To tsal,
COUNT(*) AS Namecount
FROM EMPLOYEE
GROUP BY EmpName;

g) SELECT *FROM EMPLOYEE

WHERE Salary > 50000;

Dr.Chandrika G, HOD, SIGS Page 4

 SEP DBMS LAB MANUAL

4. Demonstrate the use of TCL and DCL Commands:

TCL:
1. Create a table accounts ( account_id, account_holder_name, balance)
2. Insert some sample data into the accounts table.
3. Start a transaction and perform the following operations:
- Debit 1000 from account_id 1.
- Credit 1000 to account_id 2.
4. Use the COMMIT command to s1ave the changes.
5. Start a new transaction and perform the following operations:
- Debit 500 from account_id 1.
- Credit 500 to account_id 3.
6. Use the ROLLBACK command to undo the changes.
7. Create the save point and execute ROLLBACK TO previously created save point.

DCL:
1. Create a new user called user1 with a password.
2. Grant SELECT, INSERT, and UPDATE privileges on the accounts table to user1.
3. Login as user1 and perform the following operations:
- Select data from the accounts table.
- Insert a new row into the accounts table.
- Update an existing row in the accounts table.
4. Revoke the INSERT privilege from user1.
5. Login as user1 and try to insert a new row into the accounts table.

Answer:
TCL COMMANDS:

1)
CREATE TABLE accounts
(
account_id INT PRIMARY KEY,
account_holder_name VARCHAR(100),
balance DECIMAL(10, 2)
);

2)
INSERT INTO accounts VALUES (1, ' Ajay', 10000.00);
Dr.Chandrika G, HOD, SIGS Page 5
 SEP DBMS LAB MANUAL

INSERT INTO accounts VALUES (2, ' Balu' , 8000.00);

INSERT INTO accounts VALUES (3, ' Chandu', 5000.00);
3)
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

4) COMMIT;

5)
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 3;

6) ROLLBACK TO;

7) COMMIT;

DCL COMMANDS:

Dr.Chandrika G, HOD, SIGS Page 6

 SEP DBMS LAB MANUAL

1) CREATE USER user1 IDENTIFIED BY sigs123;

2) GRANT SELECT, INSERT, UPDATE ON acc oun t s TO user1;
3) Lo gin as user1
a. SELECT * FRO M acc oun t s;
b. INSERT INTO acc oun t s VALUES (4, 'Bhavya', 12000.00);
c. UPDATE acc oun t s SET balance =15000 WHERE acc oun t _id = 2;
4) REVOKE INSERT ON acc oun t s FRO M user1;
5)

5. Implement the Nested Queries.

An INVENTORY database has the following table.

ITEMS (itemcode number, name varchar2, price number)

PURCHASE (itemcode number, qty number)

a) Create the tables with the above attributes.

b) Enter 5 tuples into the tables.
c) List the items purchased.
d) List the items which are not purchased by anyone.

Answer:
a)
CREATE TABLE ITEMS
(
itemcode NUMBER PRIMARY KEY,
name VARCHAR2(50),
price NUMBER
);

CREATE TABLE PURCHASE

(
Dr.Chandrika G, HOD, SIGS Page 7
 SEP DBMS LAB MANUAL

itemcode NUMBER,
qty NUMBER,
FOREIGN KEY (itemcode) REFERENCES ITEMS(itemcode)
);

b)
INSERT INTO ITEMS VALUES (101, 'Pen', 10);
INSERT INTO ITEMS VALUES (102, 'Notebook', 25);
INSERT INTO ITEMS VALUES (103, 'Pencil', 5);
INSERT INTO ITEMS VALUES (104, 'Eraser', 3);
INSERT INTO ITEMS VALUES (105, 'Marker', 15);

-- Inserting into PURCHASE (only some items are purchased)

INSERT INTO PURCHASE VALUES (101, 50); -- Pen

INSERT INTO PURCHASE VALUES (102, 30); -- Notebook
INSERT INTO PURCHASE VALUES (105, 20); -- Marker

c)
SELECT I.itemcode, I.name, I.price, P.qty
FROM ITEMS I
JOIN PURCHASE P ON I.itemcode = P.itemcode;

d)
SELECT * FROM ITEMS
WHERE itemcode NOT IN
(
SELECT itemcode
FROM PURCHASE
);

6. Implement Join operations in SQL

The COMPANY database consists of the tables:

Dr.Chandrika G, HOD, SIGS Page 8

 SEP DBMS LAB MANUAL

EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)

DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)

Create tables, Insert 5 tuples each and perform the following

a) Give a 10 percent raise in salary for all employees working in the ‘Research’
Department.
b) Retrieve the name of each employee Controlled by department number 5 (use EXISTS
operator).
c) Retrieve the name of each dept and number of employees working in each department
which has at least 2 employees
d) Retrieve the name of employees and their department name (using NATURAL JOIN)
e) Perform EQUI join operation on the given tables.
f) Perform NON-EQUI join operation on the given tables.
g) Perform OUTER join operations on the given tables.

Answer:

CREATE TA BL E DEPA RTMENT

(
DNo INT PRIMA RY KEY,
DName VARCHAR(50),
Mgr SSN VARCHAR(9),
Mgr Star tDate DATE
);

CREATE TA BL E EMP
(
SSN VARCHAR(9) PRIMA RY KEY,
Name VARCHAR(50),
Add ress VARCHAR(100),
Sex CHA R(1),
Salary DECI MAL(10,2),
Sup erSSN VARCHAR(9),
DNo INT,
FOREIGN KEY (DNo) REFERENCES DEPA RTMENT(DNo)
);

INSERT INTO DEPA RTMENT VALUES (1, 'Research', '123456789', '15-01-2010');

INSERT INTO DEPA RTMENT VALUES (2, 'Sales', '987654321', '10-05-2015');

Dr.Chandrika G, HOD, SIGS Page 9

 SEP DBMS LAB MANUAL

INSERT INTO DEPA RTMENT VALUES (3, 'HR', '456789123', '25-03-2025');

INSERT INTO DEPA RTMENT VALUES (4, 'IT', '321654987', '30-06-2022');
INSERT INTO DEPA RTMENT VALUES (5, 'Finance', '654987321', '01-09-2008');

INSERT INTO EM P VALUES ('111111111' , ' Alice', 'Add r1' , ' F', 50000, NULL, 1);
INSERT INTO EM P VALUES ('222222222' , ' Bob' , 'Add r2' , ' M', 60000, ' 111111111', 2);
INSERT INTO EM P VALUES ('333333333' , ' Charli e', 'Add r3' , ' M', 55000, ' 222222222' , 1);
INSERT INTO EM P VALUES ('444444444' , ' Diana' , ' Add r4' , ' F', 70000, ' 111111111', 5);
INSERT INTO EM P VALUES ('555555555' , ' Et han' , ' Add r5' , ' M', 48000, '333333333' , 5);

a) UPDA TE EMP
SET Salary = Salary * 1.10
WHERE DNo = (SELECT DNo FROM DEPA RTMENT WHERE DName = 'Research');

b) SELECT Name FROM EMP E

WHERE EXISTS
(
SELECT 1
FROM DEPA RTMENT D
WHERE D.DNo = 5 AND D.DNo = E.DNo
);

c) SELECT D.DName, COUNT(E.SSN) AS e1

FROM DEPA RTMENT D
JOIN EMP E ON D.DNo = E.DNo
GROUP BY D.DName
HAVING COUNT(E.SSN) >= 2;

d) SELECT Name, DName FROM EMP

NA TURA L JOINDEPA RTMENT;

e) SELECT E.Name, D.DName

FROM EMP E
JOINDEPA RTMENT D ON E.DNo = D.DNo;

Dr.Chandrika G, HOD, SIGS Page 10

 SEP DBMS LAB MANUAL

f) SELECT E.Name, D.DName

FROM EMP E, DEPA RTMENT D
WHERE E.Salary > LENGTH(D.DName) * 10000;

g) SELECT E.Name, D.DName

FROM EMP E
LEFT JOIN DEPA RTMENT D ON E.DNo = D.DNo;

SELECT E.Name, D.DName

FROM EMP E
RIGHT JOINDEPA RTMENT D ON E.DNo = D.DNo;

SELECT E.Name, D.DName

FROM EMP E
FULL OUTER JOINDEPA RTMENT D ON E.DNo = D.DNo;

SELECT E.Name, D.DName

FROM EMP E
LEFT JOIN DEPA RTMENT D ON E.DNo = D.DNo
UNION
SELECT E.Name, D.DName
FROM EMP E
RIGHT JOIN DEPA RTMENT D ON E.DNo = D.DNo;

7. Create the following tables:

Passenger (PassportID, Name, Age, Sex, Address) and

Dr.Chandrika G, HOD, SIGS Page 11

 SEP DBMS LAB MANUAL

Reservation(PNRno, Journey Date, No_of_seats, Address, ContactNo.)

Query the tables using ANY, ALL, IN, Exists, NOT EXISTS, UNION, INTERSECT
etc.
1. Display unique PNR_NO of all passengers
2. Display all the names of male passengers.
3. Display the ticket numbers and names of all the passengers.
4. Find the ticket numbers of the passengers whose name start with ‘r’ and ends with
‘h’.
5. Find the names of Passengers whose age is between 30 and 45.
6. Display all the passengers’ names beginning with ‘A’.
7. Display the sorted list of Passengers names
Answer:

CREATE TABLE Passenger1

(
PassportID VARCHAR(20) PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Sex CHAR(1),
Address VARCHAR(200)
);

CREATE TABLE Reservation2

(
PNRno VARCHAR(20) PRIMARY KEY,
PassportID VARCHAR(20),
JourneyDate DATE,
No_of_seats INT,
Address VARCHAR(200),
ContactNo VARCHAR(15),
FOREIGN KEY (PassportID) REFERENCES Passenger1(PassportID)
);

INSERT INTO Passen ger 1 VALUES('P001', 'Rahu l', 35, ' M', 'Delhi');
INSERT INTO Passen ger 1 VALUES('P002', 'Anita', 28, 'F', 'Mumbai');
INSERT INTO Passen ger 1 VALUES('P003', 'Ramesh', 42, ' M', 'Kolkata');

Dr.Chandrika G, HOD, SIGS Page 12

 SEP DBMS LAB MANUAL

INSERT INTO Passen ger 1 VALUES('P004', 'Amit', 33, ' M', 'Bangalor e');
INSERT INTO Passen ger 1 VALUES('P005', 'Resh', 40, 'F', 'Chenn ai');

select * from Pass enger1;

INSERT INTO Reservation 2 VALUES('PNR1001', 'P001', '10-08-25', 2, 'Delhi', '9876543210');

INSERT INTO Reservation 2 VALUES('PNR1002', 'P002', '11-08-25', 1, ' Mumbai', '8765432109');
INSERT INTO Reservation 2 VALUES('PNR1003', 'P003', '12-08-25', 3, 'Kolkata', '7654321098');
INSERT INTO Reservation 2 VALUES('PNR1004', 'P004', '13-08-25', 1, 'Bangalor e', '6543210987');
INSERT INTO Reservation 2 VALUES('PNR1005', 'P005', '14-08-25', 2, 'Chenn ai', '5432109876');

select * from RESERVA TION2;

1. SELECT DISTINCT PNRno FRO M Reservat ion 2;

2. SELECT Name FROM Passen ger 1 WHERE Sex = ' M';

3. SELECT R.PNRno , P.Name FROM Reservation 2 R

JOIN Passen ger 1 P ON R.Passpo rt ID = P.Passpo rt ID;

4. SELECT R.PNRno FROM Reservation 2 R

JOIN Passen ger 1 P ON R.Passpo rt ID = P.Passpo rt ID
WHERE LOWER(P.Name) LIKE 'r%h';

5. SELECT Name FROM Passen ger 1 WHERE Age BETWEEN 30 AND 40;
6. SELECT Name FROM Passen ger 1 WHERE Name LIKE 'A%';
7. SELECT Name FROM Passen ger 1 ORDER BY Name;

SELECT Name FROM Passen ger 1

WHERE Age > ANY (SELECT Age FROM Passen ger WHERE Age < 30);
Dr.Chandrika G, HOD, SIGS Page 13
 SEP DBMS LAB MANUAL

SELECT Name FROM Passen ger 1

WHERE Age > ALL (SELECT Age FROM Passen ger WHERE Sex = 'F');

SELECT Name FROM Passen ger 1 P

WHERE EXISTS (
SELECT 1 FROM Reservation 2 R WHERE R.Passpo rt ID = P.Passpo rt ID
);

SELECT Name AS Info FROM Passen ger 1

UNION
SELECT Add ress FROM Reservation 2;

SELECT Add ress FROM Passen ger 1

INTERSECT
SELECT Add ress FROM Reservation 2;

8.Create views for a particular table

The RAILWAY RESERVATION SYSTEM database consists of the tables:

TRAIN(TrainNo, TrainName, StartPlace, Destination)

AVAILABILITY(TrainNo, Class, StartPlace, Destination, No_of_seats )

a) Create view sleeper to display train no, start place, destination which have sleeper class
and perform the following
 insert new record
 update destination= ’Manglore ’ where train no=’RJD16’
 delete a record which have train no=’KKE55’
b) Create view details to display train no, train name, class
c) Create view total_seats to display train number, start place, use count function to no
of seats, group by start place and perform the following
 insert new record
 update start place= ’Hubli’ where train no=’JNS8’
 delete last row of the view
d) Rename view sleeper to class
e) Delete view details

Dr.Chandrika G, HOD, SIGS Page 14

 SEP DBMS LAB MANUAL

Answer:

CREATE TABLE TRAIN

(
TrainNo VARCHAR(10) PRIMARY KEY,
TrainName VARCHAR(100),
StartPlace VARCHAR(100),
Destination VARCHAR(100)
);

Table TRA IN created.

INSERT INTO TRA IN VALUES ('R16', 'Kon kan Expr ess', 'Pun e', 'Goa');
INSERT INTO TRA IN VALUES ('R17', 'sht habd i Expr ess', 'Hub li ', 'Bangalor e');
INSERT INTO TRA IN VALUES ('R18', 'vand e matharam Expr ess', 'varanasi', 'Ayod ya');

CREATE TA BL E AVA ILA BILITY

(
Tr ainNo VARCHAR(10),
Class VARCHAR(20),
Star tPlace VARCHAR(100),
Destinat ion VARCHAR(100),
No_of_seats INT,
FOREIGN KEY (Tr ainNo) REFERENCES TRA IN(Tr ainNo)
);

Table AVA ILA BILITY created.

a)
CREATE VIEW sleeper AS
SELECT Tr ainNo, Star tPlace, Destinat ion
FROM AVA ILA BILITY
WHERE Class = 'Sleeper ';

View SLEEPER created.

Dr.Chandrika G, HOD, SIGS Page 15

 SEP DBMS LAB MANUAL

 INSERT INTO sleeper VALUES ('R16', 'Pun e','Goa');

INSERT INTO sleeper VALUES ('R17', 'Hub li ', 'Bangalor e');
INSERT INTO sleeper VALUES ('R18', 'varanasi', 'Ayod ya');

 UPDA TE sleeper
SET Destinat ion = ' Manglor e'
WHERE Tr ainNo = 'R16';

 DELETE FROM sleeper

WHERE Tr ainNo = 'R17';

b)
CREATE VIEW det ails AS
SELECT T.TrainNo, T.TrainName, A.Class
FROM TRA IN T
JOIN AVA ILA BILITY A ON T.TrainNo = A.Tr ainNo;

c)
CREATE VIEW total_seat s AS
SELECT Tr ainNo, Star tPlace, COUNT(No_of_seats) AS seat_c oun t
FROM AVA ILA BILITY
GROUP BY Tr ainNo, Star tPlace;

d) INSERT INTO TRA IN VALUES ('RD19', 'Rajdh ani Expr ess', ' Mumbai', 'Pun e');

SELECT * FROM TRA IN;

 INSERT INTO AVA ILA BILITY VALUES ('RD19', 'Sleeper ', ' Mumbai', 'Pun e', 100);

SELECT * FROM AVA ILA BILITY;

 UPDA TE AVA ILA BILITY

SET Star tPlace = 'TUMKUR'
WHERE Tr ainNo = 'RD19';

Dr.Chandrika G, HOD, SIGS Page 16

 SEP DBMS LAB MANUAL

 DELETE FROM AVA ILA BILITY

WHERE Tr ainNo = (
SELECT Tr ainNo FROM (
SELECT Tr ainNo FROM AVA ILA BILITY
ORDER BY Tr ainNo DESC
)
WHERE ROWNUM = 1
);

d) CREATE VIEW class AS

SELECT A.Tr ainNo, A.Start Place, A.Dest in at ion
FROM AVA ILA BILITY A
WHERE A.Class = 'Sleeper ';

View CLA SS created.

e) DROP VIEW det ails;

Dr.Chandrika G, HOD, SIGS Page 17

 SEP DBMS LAB MANUAL

PART-B
1) Write a PL/SQL program to enter any tw o nu mbers and find ou t their sum,
dif f erence, produ ct , quo t ient and remaind er.

DECLA RE
nu m1 NUMBER;
nu m2 NUMBER;
sum_val NUMBER;
diff NUMBER;
pr od NUMBER;
quo t NUMBER;
rem NUMBER;
BEGIN
-- Accept two nu mbers dynamicall y
nu m1 := &Ent er_Fir st _Number ;
nu m2 := &Ent er_Second _Number ;

-- Per for m ar ithmetic op er ation s

sum_val := nu m1 + nu m2;
diff: = nu m1 - nu m2;
pr od := nu m1 * nu m2;

IF nu m2 != 0 THEN
quo t := nu m1 / nu m2;
rem : = MOD(nu m1, nu m2);
ELSE
quo t := NULL;
rem : = NULL;
END IF;

-- Display result s
DBMS_OUTPUT.PUT_LINE('Sum: ' | | sum_val);
DBMS_OUTPUT.PUT_LINE('Differen ce: ' | | diff);
DBMS_OUTPUT.PUT_LINE('Pr odu ct : ' | | pr od);
IF nu m2 != 0 THEN
DBMS_OUTPUT.PUT_LINE('Quo tient: ' | | quo t);
DBMS_OUTPUT.PUT_LINE('Remaind er : ' | | rem);
ELSE
Dr.Chandrika G, HOD, SIGS Page 18
 SEP DBMS LAB MANUAL

DBMS_OUTPUT.PUT_LINE('Division by zer o is no t all owed.');

END IF;
END;
/
OUTPUT
Sum: 11
Differ ence: -3
Pr odu ct : 28
Quo tient : .5714285714285714285714285714285714285714
Remaind er : 4
PL/ SQL pr ocedu r esuccessfull y completed.

2) Write PL/SQL procedu re to compu t efact orial of a nu mber using recu rsi on .

DECLA RE
n NUMBER;
result NUMBER;
-- Recur sive fun ction to calc ulate factor ial
FUNCTION factor ial(x NUMBER) RETURN NUMBER IS
BEGIN
IF x = 0 THEN
RETURN 1;
ELSE
RETURN x * factor ial(x - 1);
END IF;
END;
BEGIN
-- Inpu t nu mber (run time inpu t)
n := &Ent er_Number ;

-- Call the recur sive fun ction

result := factor ial(n);

-- Display the result

DBMS_OUTPUT.PUT_LINE('Fact orial of ' | | n | | ' is: ' | | result );
END;
/
OUTPUT

Dr.Chandrika G, HOD, SIGS Page 19

 SEP DBMS LAB MANUAL

Factorial of 5 is: 120

PL/ SQL pr ocedu r esuccessfull y completed.

3) Given the table EMPLOYEE (EmpNo, Name, Salary, Designation, DeptID)

write a cursor in PL/SQL to select the five highest paid employees from
the table.

CREATE TABLE EMPLOYEE1

(
EmpNo NUMBER(6) PRIMARY KEY,
Name VARCHAR2(100),
Sal NUMBER(10, 2),
Desig VARCHAR2(50),
Did NUMBER(4)
);

INSERT INTO EMPLOYEE1 VALUES (101, 'Alice Smith',85000, 'Manager',10);

INSERT INTO EMPLOYEE1 VALUES (102, 'Bob Johnson', 95000, 'Director', 20);
INSERT INTO EMPLOYEE1 VALUES (103, 'Charlie Davis', 72000, 'Developer', 30);
INSERT INTO EMPLOYEE1 VALUES (104, 'Dana Lee', 105000, 'CTO', 10);
INSERT INTO EMPLOYEE1 VALUES (105, 'Eli Brown', 68000, 'Developer', 20);
INSERT INTO EMPLOYEE1 VALUES (106, 'Fiona White', 91000, 'Architect', 30);
INSERT INTO EMPLOYEE1 VALUES (107, 'George Black', 60000, 'Analyst', 10);
INSERT INTO EMPLOYEE1 VALUES (108, 'Hannah Green', 112000, 'CEO', 1);
INSERT INTO EMPLOYEE1 VALUES (109, 'Ian Gray', 78000, 'Tester', 20);
INSERT INTO EMPLOYEE1 VALUES (110, 'Jane Blue', 97000, 'Lead Engineer', 30);

SET SERVEROUTPUT ON;

DECLARE
-- Define the cursor
CURSOR high_paid_emp_cursor IS
SELECT EmpNo, Name, Sal, Desig, Did
FROM (
SELECT * FROM EMPLOYEE1
ORDER BY Sal DESC
)
WHERE ROWNUM <= 5;

-- Define a variable of the cursor row type

emp_rec high_paid_emp_cursor%ROWTYPE;

BEGIN
OPEN high_paid_emp_cursor;

LOOP

Dr.Chandrika G, HOD, SIGS Page 20

 SEP DBMS LAB MANUAL

FETCH high_paid_emp_cursor INTO emp_rec;

EXIT WHEN high_paid_emp_cursor%NOTFOUND;

-- Print each employee's details

DBMS_OUTPUT.PUT_LINE
(
'EmpNo: ' || emp_rec.EmpNo ||
', Name: ' || emp_rec.Name ||
', Salary: ' || emp_rec.Sal ||
', Designation: ' || emp_rec.Desig ||
', DeptID: ' || emp_rec.Did
);
END LOOP;

CLOSE high_paid_emp_cursor;
END;
/

OUTPUT:
EmpNo: 108, Name: Hannah Green, Salary: 112000, Designation: CEO, DeptID: 1
EmpNo: 104, Name: Dana Lee, Salary: 105000, Designation: CTO, DeptID: 10
EmpNo: 110, Name: Jane Blue, Salary: 97000, Designation: Lead Engineer, DeptID:30
EmpNo: 102, Name: Bob Johnson, Salary: 95000, Designation: Director, DeptID: 20
EmpNo: 106, Name: Fiona White, Salary: 91000, Designation: Architect, DeptID: 30

PL/SQL procedure successfully completed.

4) Write PL/SQL program to increase the salary for the employees of a particular
department and enter the no of records updated, date, time, deptno and name of the
person who increased the salary into another table called cursor_ret.

CREATE TABLE cursor_ret

(
ucount NUMBER,
udate DATE,
dno NUMBER(4),
uby VARCHAR2(100)
);

DECLARE
Dr.Chandrika G, HOD, SIGS Page 21
 SEP DBMS LAB MANUAL

-- Input values
p_dno NUMBER := 10; -- Change as needed
p_percent_increase NUMBER := 10; -- e.g., 10% increase
p_uby VARCHAR2(100) := 'Admin User';

-- Counter for updated records

vcount NUMBER := 0;

-- Cursor to select employees from the given department

CURSOR emp_cursor IS
SELECT EmpNo, Sal
FROM EMPLOYEE1
WHERE Did = p_dno
FOR UPDATE;

BEGIN
FOR emp_rec IN emp_cursor LOOP
-- Update salary
UPDATE EMPLOYEE1
SET Sal = Sal + (Sal * p_percent_increase / 100)
WHERE CURRENT OF emp_cursor;

vcount := vcount + 1;
END LOOP;

-- Log the operation in cursor_ret

INSERT INTO cursor_ret (ucount, udate, dno, uby)
VALUES (vcount, SYSDATE, p_dno, p_uby);

COMMIT;

DBMS_OUTPUT.PUT_LINE(vcount || ' record(s) updated for department ' || p_dno);

END;
/

OUTPUT:

Dr.Chandrika G, HOD, SIGS Page 22

 SEP DBMS LAB MANUAL

3 record(s) updated for department 10

PL/SQL procedure successfully completed.

select * from EMPLOYEE1;

EMPNO NAME SAL DESIG DID

101 Alice Smith 93500 Manager 10
102 Bob Johnson 95000 Director 20
103 Charlie Davis 72000 Developer 30
104 Dana Lee 115500 CTO 10
105 Eli Brown 68000 Developer 20
106 Fiona White 91000 Architect 30
107 George Black 66000 Analyst 10
108 Hannah Green 112000 CEO 1
109 Ian Gray 78000 Tester 20
110 Jane Blue 97000 Lead Engineer 30

5) 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 & new Salary on UPDATE operation
and display the new or old salary for INSERT and DELETE operations, respectively.
CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY)

CREATE TABLE CUSTOMERS

(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100),
AGE NUMBER,
ADDRESS VARCHAR2(200),
SALARY NUMBER(10, 2)
);

CREATE OR REPLACE TRIGGER trg_customer

AFTER INSERT OR UPDATE OR DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserted Salary: ' || :NEW.SALARY);

ELSIF DELETING THEN

DBMS_OUTPUT.PUT_LINE('Deleted Salary: ' || :OLD.SALARY);

ELSIF UPDATING THEN

Dr.Chandrika G, HOD, SIGS Page 23

 SEP DBMS LAB MANUAL

DBMS_OUTPUT.PUT_LINE('Salary changedby: ' || (:NEW.SALARY -

:OLD.SALARY));
END IF;
END;
/

INSERT INTO CUSTOMERS VALUES (1, 'John Doe', 30, 'New York', 50000);

UPDATE CUSTOMERS SET SALARY = 55000 WHERE ID = 1;

DELETE FROM CUSTOMERS WHERE ID = 1;

OUTPUT:

Table CUSTOMERS created.

Trigger TRG_CUSTOMER compiled

Inserted Salary: 50000

1 row inserted.

Salary changed by: 5000

1 row updated.

Deleted Salary: 55000

1 row deleted.

6) Given the table MOVIE (MID, MTitle, Language, Director, Year) write a function in

Dr.Chandrika G, HOD, SIGS Page 24

 SEP DBMS LAB MANUAL

PL/SQL to find the total number of Movies in the table.

CREATE TABLE MOVIE

(
MID NUMBER PRIMARY KEY,
MTitle VARCHAR2(200),
Language VARCHAR2(50),
Director VARCHAR2(100),
Year NUMBER(4)
);

INSERT INTO MOVIE VALUES (1, 'Inception', 'English', 'Christopher Nolan', 2010);
INSERT INTO MOVIE VALUES (2, 'Parasite', 'Korean', 'Bong Joon-ho', 2019);
INSERT INTO MOVIE VALUES (3, 'Spirited Away', 'Japanese', 'Hayao Miyazaki', 2001);
INSERT INTO MOVIE VALUES (4, 'The Godfather', 'English', 'Francis Ford Coppola',
1972);
INSERT INTO MOVIE VALUES (5, 'Amélie', 'French', 'Jean-Pierre Jeunet', 2001);
INSERT INTO MOVIE VALUES (6, 'Lagaan', 'Hindi', 'Ashutosh Gowariker', 2001);
INSERT INTO MOVIE VALUES (7, 'Roma', 'Spanish', 'Alfonso Cuarón', 2018);

CREATE OR REPLACE FUNCTION get_movies

RETURN NUMBER
IS
total_moviesNUMBER;
BEGIN
SELECT COUNT(*) INTO total_movies
FROM MOVIE;

RETURN total_movies;
END;
/

DECLARE
movie_count NUMBER;
BEGIN
movie_count := get_movies;
DBMS_OUTPUT.PUT_LINE('Total Movies: ' || movie_count);
END;
/

OUTPUT:

Dr.Chandrika G, HOD, SIGS Page 25

 SEP DBMS LAB MANUAL

Table MOVIE created.

Function GET_MOVIES compiled

Total Movies: 7

PL/SQL procedure successfully completed.

SELECT get_movies FROM dual;

GETMOVIES
7

7) Given the Table CUSTOMERS (CID, CName, Address) write a PL/SQL program
which asks for customer ID, if the user enters invalid ID then the exception invalid_id
is raised.

CREATE TABLE CUSTOMER

(
CID NUMBER PRIMARY KEY,
CName VARCHAR2(100),
Address VARCHAR2(200)
);

DECLARE
-- Variables
v_cid NUMBER := &Enter_Customer_ID; -- Prompts user for input
v_name CUSTOMER.CName%TYPE;

-- User-defined exception
invalid_id EXCEPTION;

-- Declare PRAGMA to associate the exception with no_data_found (optional but clearer)
PRAGMA EXCEPTION_INIT(invalid_id, -20001);

BEGIN
-- Try to fetch the customer name for the given ID
SELECT CName INTO v_name

Dr.Chandrika G, HOD, SIGS Page 26

 SEP DBMS LAB MANUAL

FROM CUSTOMER
WHERE CID = v_cid;

-- If found, display the name

DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_name);

EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Raise custom exception if no matching record is found
RAISE_APPLICATION_ERROR(-20001, 'Invalid customer ID: ' || v_cid);

WHEN invalid_id THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
/

OUTPUT:

Enter value for enter_customer_id: 786

ORA-20001: Invalid customer ID: 786

8) Wr ite a PL/ SQL pr ogram to demon st rate Packages.

1) CREATE TABLE emp1

(
eid NUMBER PRIMARY KEY,
ename VARCHAR2(100),
esal NUMBER
);

2) CREATE OR REPLACE PACKAGE emp_pkg AS

PROCEDURE add_emp(pid NUMBER, pname VARCHAR2, psalary NUMBER);
FUNCTION get_sal(pid NUMBER) RETURN NUMBER;
PROCEDURE usal(pid NUMBER, pnewsal NUMBER);
END emp_pkg;
/

3) CREATE OR REPLACE PACKAGE BODY emp_pkg AS

Dr.Chandrika G, HOD, SIGS Page 27

 SEP DBMS LAB MANUAL

PROCEDURE add_emp(pid NUMBER, pname VARCHAR2, psalary NUMBER) IS

BEGIN
INSERT INTO emp1(eid, ename, esal)
VALUES(pid, pname, psalary);
END add_emp;

FUNCTION get_sal(pid NUMBER) RETURN NUMBER IS

v_salary emp1.esal%TYPE;
BEGIN
SELECT esal INTO v_salary
FROM emp1
WHERE eid = pid;
RETURN v_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_sal;

PROCEDURE usal(pid NUMBER,pnewsal NUMBER) IS

BEGIN
UPDATE emp1
SET esal =pnewsal
WHERE eid = pid;
END usal;

END emp_pkg;
/

OUTPUT:

Package EMP_PKG compiled

Package Body EMP_PKG compiled

Updated Salary: 60000

Dr.Chandrika G, HOD, SIGS Page 28

 SEP DBMS LAB MANUAL

Dr.Chandrika G, HOD, SIGS Page 29