CONTENTS

PAGE
[Link] DATE NAME OF THE EXPERIMENT SIGNATURE
NO
`1 SQL-DDL COMMANDS

2 SQL-DDM COMMANDS

3 SQL-TCL COMMANDS

4 PL/SQL-FIBONACCI SERIES

5 PL/SQL-FACTORIAL

6 PL/SQL-STRING REVERSE

7 PL/SQL-SUM OF SERIES

8 PL/SQL-TRIGGER

CURSOR-STUDENTS MARK ANALYSIS

9
USING CURSOR

APPLICATION-LIBRARY
10
MANAGEMENT SYSTEM

APPLICATION-STUDENT MARK
11
ANALYSIS
 1. DDL Commands

--Create table

CREATE TABLE Students ( id NUMBER(4) PRIMARY KEY, name VARCHAR2(20),age NUMBER(3));

-- Alter table: add a column

ALTER TABLE Students

ADD (grade

VARCHAR2(2));

--Rename table

ALTER TABLE Students

RENAME TO Learners;

--Truncate data later if needed

TRUNCATE TABLE Learners;

--Drop table

DROP TABLE Learners;

OUTPUT:

Table created.

Table altered.

Table renamed.

Table truncated(fast remove data).

Table dropped
 2. DML COMMAND

--Create for data operations

CREATE TABLE Employees (emp_id NUMBER(3) PRIMARY KEY, ename

VARCHAR2(15),salary NUMBER(7,2));

--Insert rows

INSERT INTO Employees VALUES (101, 'Alice', 50000);

INSERT INTO Employees VALUES (102, 'Bob', 60000);

--Update salary

UPDATE Employees

SET salary = salary * 1.10

WHERE emp_id = 101;

--Delete row

DELETE FROM Employees

WHERE emp_id = 102;

--Select results

SELECT * FROM Employees;

OUTPUT :

EMP_ID ENAME SALARY

101 Alice 55000.00
 3. TCL COMMAND

BEGIN

--Update salary for all employees

UPDATE Employees

SET salary = salary + 1000;

SAVEPOINT sp1;

--Delete someone

DELETE FROM Employees WHERE emp_id = 101;

--Oops, rollback delete only

ROLLBACK TO sp1;

--Commit the raise

COMMIT;

END;

OUTPUT :

EMP_ID ENAME SALARY

101 Alice 56000.00
 [Link]/SQL

[Link] SERIES

DECLARE

a NUMBER := 0;

b NUMBER := 1;

temp NUMBER;

n NUMBER := 10; -- Change as needed

i NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('Fibonacci series:');

DBMS_OUTPUT.PUT_LINE(a);

DBMS_OUTPUT.PUT_LINE(b);

FOR i IN 2..n LOOP

temp := a + b;

a := b;

b := temp;

DBMS_OUTPUT.PUT_LINE(temp);

END LOOP;

END;

/
OUTPUT:

Fibonacci series:

13

21

34

55
 5. FACTORIAL

DECLARE

f NUMBER := 1;

n NUMBER := 5; -- Compute 5!

BEGIN

FOR i IN 1..n LOOP

f:=f*i;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Factorial of '||n||' is '||f);

END;

OUTPUT :

Factorial of 5 is 120
 6. STRING REVERSE

DECLARE

input_str VARCHAR2(100) := 'Oracle';

rev VARCHAR2(100) := ";

BEGIN

FOR I IN REVERSE 1..LENGTH(input_str) LOOP

rev := rev || SUBSTR(input_str, i, 1);

END LOOP;

DBMS_OUTPUT.PUT_LINE('Original: ' || input_str);

DBMS_OUTPUT.PUT_LINE('Reversed: ' || rev);

END;

Output :

Original: Oracle

Reversed: elcarO
 7. SUM OF SERIES

DECLARE

n NUMBER := 10;

total NUMBER := 0;

BEGIN

FOR i IN 1..n LOOP

total: total + i;

END LOOP:

DBMS_OUTPUT.PUT_LINE('Sum of 1..'||n||' = '"||total);

END;

OUTPUT :

Sum of 1…10=55
 8. Trigger

First, set up tables:

CREATE TABLE employees (emp_id NUMBER PRIMARY KEY,ename

VARCHAR2(50),salary NUMBER);

CREATE TABLE emp_audit (emp_id NUMBER,ename VARCHAR2(50),action time

TIMESTAMP,action_type VARCHAR2(10));

Then create a trigger:

CREATE OR REPLACE TRIGGER trg_emp_insert

AFTER INSERT ON employees

FOR EACH ROW

BEGIN

INSERT INTO emp_audit(emp id, ename, action time, action type)

VALUES(:NEW.emp_id, :[Link], SYSTIMESTAMP, INSERT);

END;

Testing the trigger:

INSERT INTO employees VALUES (1, 'Alice', 50000);

COMMIT;

SELECT * FROM emp_audit;

OUTPUT:

EMP_ID ENAME ACTION/_TIME ACTION/_TYPE

2025-06-10
1 Alice INSERT
14:23:45.123456
 [Link]

9. Student Mark Analysis Using Cursor

Table Setup

CREATE TABLE students

( regno NUMBER PRIMARY

KEY, name VARCHAR2(50),

mark1 NUMBER,

mark2 NUMBER,

mark3 NUMBER);

INSERT INTO students VALUES (1, 'Ram', 40, 55, 65);

INSERT INTO students VALUES (2, 'Sita', 80, 90, 85);

INSERT INTO students VALUES (3, 'Gopal', 35, 45, 25);

--add more rows as needed

COMMIT;

SET SERVEROUTPUT ON;

DECLARE

CURSOR stud_cur IS

SELECT regno, name, mark1, mark2, mark3 FROM students;

v_reg [Link]%TYPE;
v_name [Link]%TYPE;

v_m1 students.mark1%TYPE;

v_m2 students.mark2%TYPE;

v_m3 students.mark3%TYPE;

v_total NUMBER;

v_avg NUMBER;

v_grade VARCHAR2(10);

BEGIN

OPEN stud_cur;

LOOP

FETCH stud cur INTO v reg, v name, v ml, v m2, v m3;

EXIT WHEN stud cur%NOTFOUND:

v total:=v_m1+v_m2 + v_m3;

v_avg :=v_total/3;

-- Assign grade based on average

IF v_avg<40 THEN

v_grade := 'FAIL';

ELSIF v_avg < 50

THEN
v_grade := 'C';

ELSIF v_avg < 60 THEN

v_grade := 'B';

ELSIF v_avg < 80 THEN v_grade := 'A';

ELSE

v_grade := 'HONOURS';

END IF;

DBMS_OUTPUT.PUT_LINE(

'RegNo: '||v_reg ||

'| Name: '||v_name ||

'| Total: '||v_total ||

'| Avg: '||ROUND(v_avg,2) ||

'| Grade: '||v_grade

);

END LOOP;

CLOSE stud_cur;

END;

/
Output:

RegNo: 1| Name: Ram | Total: 160 | Avg: 53.33 |Grade: B

RegNo: 2 | Name: Sita | Total: 255 | Avg: 85.00 |Grade: HONOURS

RegNo: 3 |Name:Gopal | Total: 105 |Avg: 35.00 | Grade: FAIL

 IV APPLICATION

10. Library Management System

--Create Books, Authors, Branches, Copies, Borrowing tables

CREATE TABLE BOOK

( Book_id INT PRIMARY

KEY, Title VARCHAR(100),

Publisher VARCHAR(50),

Pub_Year YEAR

);

CREATE TABLE BOOK_AUTHORS (

Book_id INT,

Author_Name VARCHAR(50),

PRIMARY KEY (Book_id, Author_Name),

FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id)

);

CREATE TABLE LIBRARY_BRANCH (

Branch_id INT PRIMARY KEY,

Branch_Name VARCHAR(50),

Address VARCHAR(100) );

CREATE TABLE BOOK_COPIES (

Book_id INT,

Branch_id INT,

No_of_Copies INT,

PRIMARY KEY (Book_id, Branch_id),

FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id),

FOREIGN KEY (Branch_id) REFERENCES LIBRARY_BRANCH(Branch_id) );

CREATE TABLE BOOK_LENDING (

Book_id INT,

Branch_id INT,

Card_No INT,

Date_Out DATE,

Due_Date DATE,

PRIMARY KEY (Book_id, Branch_id, Card_No, Date_Out),

FOREIGN KEY (Book_id, Branch_id) REFERENCES BOOK_ COPIES(Book_id,

Branch_id) );

--Sample Data

INSERT INTO BOOK VALUES

(1, ‘Database Systems’, ‘OXFORD’, 2018),

(2, ‘Introduction to Algorithms’, ‘MIT Press’, 2020);

INSERT INTO BOOK_AUTHORS VALUES

(1, ‘Raghu Ramakrishnan’),

(2, ‘Thomas Cormen’);

INSERT INTO LIBRARY_BRANCH VALUES

(10, ‘Main Branch’, ‘Chennai’),

(20, ‘East Branch’, ‘Tambaram’);

INSERT INTO BOOK_COPIES VALUES

(1, 10, 5),

(2, 10, 3),

(1, 20, 2);

INSERT INTO BOOK_LENDING VALUES

(1, 10, 1001, ‘2025-06-01’,’2025-06-15’),

(2, 10, 1002, ‘2025-06-03’,’2025-06-17’);

List all books and their authors:

SELECT [Link], a.Author_Name

FROM BOOK b

JOIN BOOK_AUTHORS a ON b.Book_id = a.Book_id;

Count available copies per branch:

SELECT lb.Branch_Name, b. Title, bc.No_of_Copies

FROM BOOK_COPIES bc

JOIN LIBRARY_BRANCH 1b ON bc. Branch_id=lb.Branch_id

JOIN BOOK b ON bc.Book_id = b.Book_id;

OUTPUT:

Title Author_name
Database systems Raghu Ramakrishnan
Introduction to Thomas Cormen
Algorithms

Branch_Name Title No_Of_Copies

Main Branch Database systems 5
Main Branch Introduction to 3
Algorithms
East Branch Database System 2
 11. STUDENT MARK ANALYSIS

CREATE TABLE STDMARKS (Roll_No INT,Student_Name VARCHAR(30),Subject

VARCHAR(30),Marks INT);

INSERT INTO STDMARKS VALUES

(1,’RAVI’,’C++’,75),

(1, ‘RAVI’,’ORACLE’,84),

(1,’RAVI, ‘JAVA’,95),

(1.’RAVI,’OS’,62),

(2,’VIVEK’,’C++’,46),

(2,’VIVEK’,’ORACLE’,70).

(2,’VIVEK’,’JAVA’,55),

(2,’VIVEK’, ‘OS’,56),

(3,’RAJ’,’C++’,82),

(3,’RAJ’, ‘ORACLE’,84),

(3,’RAJ, ‘JAVA’,74),

(3,’RAJ’, ‘OS’,92);

Average marks per student:

SELECT Roll_No, Student_Name, AVG(Marks) AS Avg_Marks

FROM STDMARKS

GROUP BY Roll_No, Student_Name;

Top scorer by subject:

WITH MaxMarks AS (

SELECT Subject, MAX(Marks) AS MaxMark

FROM STDMARKS

GROUP BY Subject

SELECT s. Student_Name, [Link], [Link]

FROM MaxMarks mm

JOIN STDMARKS m ON [Link] = [Link] AND [Link] = [Link];

JOIN STDMARKS s ON [Link] No = m.Roll_No AND [Link] =[Link];

OUTPUT:

Student_Name Subject Marks

RAVI JAVA 95
RAJ OS 92
RAJ C++ 82
RAJ ORACLE 84

Roll_No Student_Name Avg_Marks

1 RAVI 79.00
2 VIVEK 56.75
3 RAJ 83.00