EX.
NO 1 : DDL Commands in SQL
Aim :
To implement DDL (Data Definition Language) commands:
1. CREATE
2. ALTER
3. TRUNCATE
4. DROP
Algorithm :
Step 1. CREATE
Create a Student table with the following fields:sno, name, m1, m2, m3
SQL> CREATE TABLE stu , sno NUMBER(4), name VARCHAR(20), m1 NUMBER(3), m2
NUMBER(3),m3 NUMBER(3));
Step 2. ALTER
a) Add Columns
Add total and average fields to the existing table.
SQL> ALTER TABLE stu ADD ( total NUMBER(5),average NUMBER(5,2) );
View Table Structure:
SQL> DESC stu;
b) Delete a Column
Remove the average column from the table.
SQL> ALTER TABLE stu DROP COLUMN average;
c) Modify Column
Modify the name column data type from VARCHAR to CHAR.
SQL> ALTER TABLE stu MODIFY name CHAR(20);
d) Rename Table
Change the table name from stu to stu1.
SQL> RENAME stu TO stu1;
e) Rename Column
Rename the column sno to regno.
SQL> ALTER TABLE stu1 RENAME COLUMN sno TO regno;
Step 3. TRUNCATE
Delete all the records in the table without removing the structure.
SQL> TRUNCATE TABLE stu1;
Step 4. DROP
Delete the table structure completely.
SQL> DROP TABLE stu1;
RESULT
Thus, the DDL commands — CREATE, ALTER, TRUNCATE, and DROP — were successfully
implemented and verified.
EX.No 2 : DML COMMANDS
AIM:
To implement DML (Data Manipulation Language) commands:
1. INSERT
2. SELECT
3. UPDATE
4. DELETE
Algorithm :
Step 1 : Start
Step 2 :Create a table stu with required columns.
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
Step 3 : Insert 5 student records using INSERT INTO command.
INSERT INTO table_name VALUES (value1, value2, ..., valuen);
Step 4 : Display all records using SELECT * FROM stu.
SELECT * FROM table_name;
Step 5 : Update the total column using the formula: m1 + m2 + m3.
UPDATE table_name SET column_name = expression;
Step 6 : Update the average column using the formula: total / 3.
UPDATE table_name SET column_name = expression;
Step 7 : Display the updated records using SELECT.
SELECT * FROM stu;
Step 8 : Delete the student record where sno = 3.
DELETE FROM table_name WHERE condition;
Step 9 : Display all remaining records.
SELECT * FROM stu;
Step 10 : End
RESULT
Thus, the DML commands — INSERT,SELECT,UPDATE AND DELETE — were successfully
implemented and verified.
Ex.No :3 TCL COMMANDS
AIM
To implement Transaction Control Language commands.
1.Commit 2.Rollback 3.Savepoint
1. COMMIT
The COMMIT command is used to save all the transactions to the database that have been performed
during the current transaction.
Once a transaction is committed, it becomes permanent and cannot be undone.
This command is typically used at the end of a series of SQL statements to ensure that all changes
made during the transaction are saved.
Syntax:
COMMIT;
2. ROLLBACK
The ROLLBACK command is used to undo all the transactions that have been performed during the
current transaction but have not yet been committed.
This command is useful for reverting the database to its previous state in case an error occurs or if the
changes made are not desired.
Syntax:
ROLLBACK;
3. SAVEPOINT
The SAVEPOINT command is used to set a point within a transaction to which we can later roll
back.
This command allows for partial rollbacks within a transaction, providing more control over which parts of a
transaction to undo.
Algorithm :
Step 1 : Create Table
SQL> CREATE TABLE new (name VARCHAR(20), marks NUMBER(20) );
Step 2 : Insert initial records (ANU, BALA, HARISH)
INSERT INTO new VALUES ('ANU', 78);
Step 3 : Update name 'ANU' to 'Karthika'
UPDATE new SET name = 'Karthika' WHERE name = 'ANU';
Step 4 : Commit the Changes
COMMIT;
Step 5 : ROLLBACK — no effect due to prior commit
ROLLBACK;( No changes undone since a COMMIT was already performed.)
Step 6 : Update 'BALA' to 'YOGI' without commit ( Update & Rollback Without Commit)
UPDATE new SET name = 'YOGI' WHERE name = 'BALA';
Step 7 : Insert & Use SAVEPOINTS
INSERT INTO new VALUES ('GANESH', 100);
COMMIT;
Step 8 : Savepoint Transactions
UPDATE new SET name = 'kathir' WHERE marks = 88;
SAVEPOINT a;
INSERT INTO new VALUES ('Divya', 92);
SAVEPOINT b;
INSERT INTO new VALUES ('oviya', 90);
SAVEPOINT c;
Step 9 : View Final Table State
SELECT * FROM new;
Step 10 : END
RESULT
Thus, the Transaction Control Language commands — COMMIT, ROLLBACK, and SAVEPOINT — were
successfully implemented and verified using SQL queries.
Ex.no 5 FIBONACCI SERIES
AIM
To generate Fibonacci Series using PL/SQL Language
Algorithm :
Step 1 : Start
Step 2 : Initialize variables
Set a = -1, b = 1, and c = 0.
Step 3: Input n
Ask the user how many Fibonacci numbers to generate.
Step 4: Repeat the following steps n times (using a loop):
a. Calculate c = a + b
b. Print c
c. Update a = b
d. Update b = c
Step 5 : End loop
Step 6 : End
RESULT
Thus the program was executed successfully and the output was verified.
Ex.No 6 Factorial using PL/SQL
Aim:
To write a PL/SQL program to calculate the factorial of a given number.
Algorithm:
1. Start
2. Declare variables:
o n to store input number
o fact initialized to 1 to store factorial result
3. Accept input value for n from the user
4. Check if n is less than 0:
o If yes, display "Factorial of negative number does not exist" and stop
5. Otherwise, loop from 1 to n:
o Multiply fact by current loop counter i in each iteration
6. Display the factorial value stored in fact
7. End
Result:
The program correctly calculates the factorial for any non-negative integer input and displays the result.
Ex.no 7 String Reverse using PL/SQL
AIM:
To write a PL/SQL program that accepts a string from the user and displays the reversed string.
Algorithm:
1. Start
2. Declare variables:
o str – to store the input string
o rev_str – to store the reversed string
o len – to store the length of the string
3. Initialize rev_str as an empty string ''
4. Read the input string from the user and store it in str
5. Find the length of the string using the LENGTH() function and store it in len
6. Loop from i = len down to 1 using REVERSE loop
o Use SUBSTR(str, i, 1) to get each character from the end
o Concatenate each character to rev_str
7. Display the reversed string
8. End
Result:
Thus, the PL/SQL program to reverse a string was successfully executed and the output was verified.
Ex:No :8 SUM OF SERIES
AIM
To generate the Sum of Series using PL/SQL Program
Algorithm :
Step 1 : Start the program.
Step 2 : Declare variables:
n → the number up to which you want to find the sum (e.g., 10).
i → loop counter (start from 1).
total → to store the sum (start from 0).
Step 3 : Start a loop from 1 to n:
For each number i in the loop:
o Add i to total → total := total + i
o Increase i by 1
Step 4: After the loop ends, print the value of total.
Step 5 : End the program.
RESULT
Thus the above was executed successfully and the output was verified.
Ex.No:9 TRIGGER USING SALARY CALCULATION
AIM :
To implement Trigger using salary calculation in PL/SQL
ALGORITHM :
Step 1: Create an employee Table using customer (id, name, age, address, salary)
Step 2: Inserted sample data with salaries 30000 and 40000 for two customers.
Step 3: Created the trigger trg_salary_difference:
Fires after salary update on each row.
Calculates difference between new and old salary.
Prints a message if the difference is greater than 1000.
Step 4: Enabled output to see DBMS_OUTPUT messages:
Step 5: Finally Test the trigger
Step 6 : Updated salary for customer ID=1 by adding 1500, which is more than 1000, so
the trigger fired and printed the messages
Step 7 : End of the Program
CREATE TABLE IN ORACLE:
SQL> CREATE TABLE custome1 ( id NUMBER PRIMARY KEY, name VARCHAR2(15),age NUMBER,
address VARCHAR2(20), salary NUMBER);
Table created.
INSERT DATA IN ORACLE :
SQL> INSERT INTO custome1 VALUES (1, 'Navi', 25, 'Chennai', 30000);
1 row created.
SQL> INSERT INTO custome1 VALUES (2, 'ANU', 30, 'MADURAI', 40000);
1 row created.
SQL> select * from custome1;
ID NAME AGE ADDRESS SALARY
---------- --------------- ---------- -------------------- ----------
1 Navi 25 Chennai 30000
2 ANU 30 MADURAI 40000
PL/SQL USING TRIGGER :
SQL> CREATE OR REPLACE TRIGGER trg_salary_difference
AFTER UPDATE OF salary ON custome1
FOR EACH ROW
DECLARE
v_diff NUMBER;
BEGIN
v_diff := :NEW.salary - :OLD.salary;
IF ABS(v_diff) > 1000 THEN
DBMS_OUTPUT.PUT_LINE('Salary changed by more than 1000 for customer ID: ' || :NEW.id);
DBMS_OUTPUT.PUT_LINE('Old Salary: ' || :OLD.salary || ', New Salary: ' || :NEW.salary);
END IF;
END;
/
Trigger created.
OUTPUT:
SQL> set serveroutput on
SQL> UPDATE custome1 SET salary = salary + 1500 WHERE id = 1;
Salary changed by more than 1000 for customer ID: 1
Old Salary: 30000, New Salary: 31500
1 row updated.
SQL> select * from custome1;
ID NAME AGE ADDRESS SALARY
---------- --------------- ---------- -------------------- ----------
1 Navi 25 Chennai 31500
2 ANU 30 MADURAI 40000
RESULT
Thus the program was executed successfully and the output was verified.
Ex.No:10 STUDENT MARK ANALYSIS USING CURSOR
AIM :
To implement Student Mark Analysis using Cursor in PL/SQL
ALGORITHM :
Step 1: Start the program
Step 2: Declare a cursor to get student data (ID, name, and marks) from the student1 table
Step 3 : Declare variables to store:
Student details,Total marks,Average marks,Grade
Step 4 : Open the cursor
Step 5: Repeat the following steps for each student record:
Fetch student details into variables
Exit the loop if there are no more records
Calculate total = mark1 + mark2 + mark3
Calculate average = total / 3
Display the student ID, name, total, average, and grade
Step 6 : Close the cursor
Step 7: End the program
SOURCE CODE :
CREATE TABLE IN ORACLE:
SQL> CREATE TABLE student1 (student_id NUMBER,name VARCHAR2(50), mark1 NUMBER, mark2
NUMBER, mark3 NUMBER);
Table created.
INSERT DATA IN ORACLE :
SQL> INSERT INTO student1 VALUES (101, 'Alice', 95, 90, 85);
1 row created.
SQL> INSERT INTO student1 VALUES (102, 'Bob', 70, 65, 60);
1 row created.
SQL> INSERT INTO student1 VALUES (103, 'Charlie', 40, 35, 45);
1 row created.
SQL> commit
PL/SQL USING CURSOR :
DECLARE
-- Cursor to fetch student data
CURSOR student_cursor IS
SELECT student_id, name, mark1, mark2, mark3 FROM student1;
-- Variables to hold cursor data
v_id student1.student_id%TYPE;
v_name student1.name%TYPE;
v_m1 student1.mark1%TYPE;
v_m2 student1.mark2%TYPE;
v_m3 student1.mark3%TYPE;
-- Variables for calculation
v_total NUMBER;
v_avg NUMBER;
v_grade CHAR(1);
BEGIN
OPEN student_cursor;
LOOP
FETCH student_cursor INTO v_id, v_name, v_m1, v_m2, v_m3;
EXIT WHEN student_cursor%NOTFOUND;
-- Calculate total and average
v_total := v_m1 + v_m2 + v_m3;
v_avg := v_total / 3;
-- Grade logic
IF v_avg >= 90 THEN
v_grade := 'A';
ELSIF v_avg >= 75 THEN
v_grade := 'B';
ELSIF v_avg >= 60 THEN
v_grade := 'C';
ELSIF v_avg >= 40 THEN
v_grade := 'D';
ELSE
v_grade := 'F';
END IF;
-- Display results
DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name ||
', Total: ' || v_total || ', Avg: ' || ROUND(v_avg, 2) ||
', Grade: ' || v_grade);
END LOOP;
CLOSE student_cursor;
END;
/
OUTPUT:
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
ID: 101, Name: Alice, Total: 270, Avg: 90, Grade: A
ID: 102, Name: Bob, Total: 195, Avg: 65, Grade: C
ID: 103, Name: Charlie, Total: 120, Avg: 40, Grade: D
PL/SQL procedure successfully completed.
RESULT
Thus the program was executed successfully and the output was verified.
