1.
DDL Commands
CREATE TABLE StudentMarks (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Subject1 INT,
Subject2 INT,
Subject3 INT,
Total INT,
Average DECIMAL(5,2),
Grade CHAR(1)
);
INSERT INTO StudentMarks
(StudentID, StudentName, Subject1, Subject2, Subject3, Total, Average, Grade)
VALUES
(1, 'Rahul', 80, 75, 90, 245, 81.67, 'A');
SELECT * FROM StudentMarks;
[Link] Commands
-- Insert data
INSERT INTO StudentMarks (StudentID, StudentName, Subject1, Subject2,
Subject3)
VALUES (1, 'Anita', 78, 85, 90);
INSERT INTO StudentMarks (StudentID, StudentName, Subject1, Subject2,
Subject3)
VALUES (2, 'Bala', 65, 70, 75);
-- Update Total and Average
UPDATE StudentMarks
SET Total = Subject1 + Subject2 + Subject3,
Average = (Subject1 + Subject2 + Subject3) / 3.0;
-- Update Grade
UPDATE StudentMarks
SET Grade =
CASE
WHEN Average >= 90 THEN 'A'
WHEN Average >= 75 THEN 'B'
WHEN Average >= 60 THEN 'C'
WHEN Average >= 50 THEN 'D'
ELSE 'F'
END;
SELECT StudentID, StudentName, Subject1, Subject2, Subject3, Total, Average,
Grade
FROM StudentMarks;
[Link] Commands
-- Undo change with ROLLBACK
UPDATE StudentMarks
SET Subject1 = 0
WHERE StudentID = 2;
ROLLBACK;
-- Insert new record
INSERT INTO StudentMarks (StudentID, StudentName, Subject1, Subject2,
Subject3)
VALUES (3, 'Chitra', 88, 76, 92);
-- Calculate Total, Average and Grade
UPDATE StudentMarks
SET
Total = Subject1 + Subject2 + Subject3,
Average = (Subject1 + Subject2 + Subject3) / 3.0,
Grade = CASE
WHEN (Subject1 + Subject2 + Subject3) / 3.0 >= 90 THEN 'A'
WHEN (Subject1 + Subject2 + Subject3) / 3.0 >= 75 THEN 'B'
WHEN (Subject1 + Subject2 + Subject3) / 3.0 >= 60 THEN 'C'
WHEN (Subject1 + Subject2 + Subject3) / 3.0 >= 50 THEN 'D'
ELSE 'F'
END
WHERE StudentID = 3;
COMMIT;
-- Verification
SELECT * FROM StudentMarks;
SELECT StudentName, Average, Grade
FROM StudentMarks
WHERE Average > 70;
SELECT * FROM StudentMarks;
SELECT StudentName, Average, Grade
FROM StudentMarks
WHERE Average > 70;
4. Fibonacci Series
SET SERVEROUTPUT ON;
DECLARE
a NUMBER := 0;
b NUMBER := 1;
c NUMBER;
n NUMBER := 10;
i NUMBER := 3;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
WHILE i <= n LOOP
c := a + b;
DBMS_OUTPUT.PUT_LINE(c);
a := b;
b := c;
i := i + 1;
END LOOP;
END;
/
5. Factorial
-- Factorial calculation
SET SERVEROUTPUT ON;
DECLARE
n NUMBER := 5; -- Number to calculate factorial for
fact NUMBER := 1; -- Variable to store factorial
i NUMBER := 1; -- Loop counter
BEGIN
WHILE i <= n LOOP
fact := fact * i; -- Multiply fact by i
i := i + 1; -- Increment i
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial is: ' || fact); -- Display result
END;
/
6. String Reverse
DECLARE
str VARCHAR2(50) := 'PLSQL';
rev VARCHAR2(50) := ''; -- Initialize as empty string
i NUMBER;
BEGIN
-- Loop through the string in reverse order
FOR i IN REVERSE 1..LENGTH(str) LOOP
rev := rev || SUBSTR(str, i, 1);
END LOOP;
-- Display the reversed string
DBMS_OUTPUT.PUT_LINE('Reversed string is: ' || rev);
END;
/
[Link] of series
DECLARE
n NUMBER := 10;
v_sum NUMBER := 0; -- Changed 'sum' to 'v_sum' to avoid conflict
i NUMBER := 1;
BEGIN
WHILE i <= n LOOP
v_sum := v_sum + i; -- Changed 'sum' to 'v_sum'
i := i + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('sum is: ' || v_sum); -- Displaying the sum
END;
/
[Link]
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2),
last_updated TIMESTAMP
);
CREATE OR REPLACE TRIGGER trg_update_last_updated
2 BEFORE UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 :NEW.last_updated := SYSDATE;
6 END;
7 /
[Link]
-- Step 1: Create Table
CREATE TABLE Student_Marks (
Roll_No NUMBER,
Name VARCHAR2(50),
Mark1 NUMBER,
Mark2 NUMBER,
Mark3 NUMBER
);
-- Step 2: Insert Sample Data
INSERT INTO Student_Marks VALUES (1, 'John', 85, 67, 74);
INSERT INTO Student_Marks VALUES (2, 'Mary', 55, 38, 49);
INSERT INTO Student_Marks VALUES (3, 'Alex', 90, 92, 88);
INSERT INTO Student_Marks VALUES (4, 'Sara', 35, 40, 45);
-- Step 3: Cursor-based PL/SQL Block
DECLARE
CURSOR student_cursor IS
SELECT * FROM Student_Marks;
stu_rec student_cursor%ROWTYPE;
total NUMBER;
result VARCHAR2(10);
BEGIN
OPEN student_cursor;
LOOP
FETCH student_cursor INTO stu_rec;
EXIT WHEN student_cursor%NOTFOUND;
total := stu_rec.Mark1 + stu_rec.Mark2 + stu_rec.Mark3;
IF stu_rec.Mark1 >= 40 AND stu_rec.Mark2 >= 40 AND stu_rec.Mark3 >= 40 THEN
result := 'Pass';
ELSE
result := 'Fail';
END IF;
-- Assuming some output or further processing here
END LOOP;
CLOSE student_cursor;
END;
[Link] management System
-- Step 1: Create Table
CREATE TABLE Library (
BOOKID NUMBER PRIMARY KEY,
Title VARCHAR2(30),
Author VARCHAR2(50),
Status VARCHAR2(10) DEFAULT 'Available'
);
-- Step 2: Insert Sample Data
INSERT INTO Library VALUES (101, 'Database Concepts', 'Elmasri', 'Available');
INSERT INTO Library VALUES (102, 'PL/SQL Programming', 'Ullman', 'Available');
INSERT INTO Library VALUES (103, 'Java for Beginners', 'Herbert', 'Available');
-- Step 3: Procedure to Add Book
CREATE OR REPLACE PROCEDURE Add_Book(
p_BookID IN NUMBER,
p_Title IN VARCHAR2,
p_Author IN VARCHAR2
) AS
BEGIN
INSERT INTO Library(BookID, Title, Author, Status)
VALUES (p_BookID, p_Title, p_Author, 'Available');
DBMS_OUTPUT.PUT_LINE('Book added successfully.');
END;
-- Step 4: Procedure to Issue Book
CREATE OR REPLACE PROCEDURE Issue_Book(p_BookID IN NUMBER) AS
v_status VARCHAR2(10);
BEGIN
SELECT Status INTO v_status FROM Library WHERE BookID = p_BookID;
IF v_status = 'Available' THEN
UPDATE Library SET Status = 'Issued' WHERE BookID = p_BookID;
DBMS_OUTPUT.PUT_LINE('Book ID ' || p_BookID || ' issued successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Book ID ' || p_BookID || ' is already issued.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Book ID ' || p_BookID || ' does not exist.');
END;
-- Step 5: Procedure to Return Book
CREATE OR REPLACE PROCEDURE Return_Book(p_BookID IN NUMBER) AS
v_status VARCHAR2(10);
BEGIN
SELECT Status INTO v_status FROM Library WHERE BookID = p_BookID;
IF v_status = 'Issued' THEN
UPDATE Library SET Status = 'Available' WHERE BookID = p_BookID;
DBMS_OUTPUT.PUT_LINE('Book ID ' || p_BookID || ' is returned successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Book ID ' || p_BookID || ' is not issued.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Book ID ' || p_BookID || ' does not exist.');
END;
SET SERVEROUTPUT ON;
EXEC Add_Book(104, 'Python Basics', 'Guido');
EXEC Issue_Book(104);
EXEC Return_Book(104);
[Link] mark analysis
CREATE TABLE StudentDetails (
Roll_Number VARCHAR2(50),
M1 NUMBER,
M2 NUMBER,
M3 NUMBER
);
INSERT INTO StudentDetails VALUES ('Alice', 78, 69, 85);
INSERT INTO StudentDetails VALUES ('Bob', 58, 90, 80);
INSERT INTO StudentDetails VALUES ('Charlie', 62, 75, 90);
CREATE OR REPLACE FUNCTION Calc_Total(M1 NUMBER, M2 NUMBER, M3 NUMBER) RETURN NUMBER
AS
BEGIN
RETURN M1 + M2 + M3;
END;
CREATE OR REPLACE FUNCTION Calc_Average(M1 NUMBER, M2 NUMBER, M3 NUMBER) RETURN
NUMBER AS
BEGIN
RETURN (M1 + M2 + M3) / 3;
END;
CREATE OR REPLACE FUNCTION Get_Grade(avg_marks NUMBER) RETURN VARCHAR2 AS
BEGIN
IF avg_marks >= 85 THEN
RETURN 'A';
ELSIF avg_marks >= 70 THEN
RETURN 'B';
ELSIF avg_marks >= 50 THEN
RETURN 'C';
ELSE
RETURN 'D';
END IF;
END;
CREATE OR REPLACE FUNCTION Get_Result(M1 NUMBER, M2 NUMBER, M3 NUMBER) RETURN
VARCHAR2 AS
BEGIN
IF M1 >= 40 AND M2 >= 40 AND M3 >= 40 THEN
RETURN 'Pass';
ELSE
RETURN 'Fail';
END IF;
END;
To view output
SELECT * FROM StudentDetails;
SELECT Roll_Number, Calc_Total(M1, M2, M3) AS Total_Marks
FROM StudentDetails;
SELECT Roll_Number, Calc_Average(M1, M2, M3) AS Average_Marks
FROM StudentDetails;
SELECT Roll_Number, Get_Grade(Calc_Average(M1, M2, M3)) AS Grade
FROM StudentDetails;
SELECT Roll_Number, Get_Result(M1, M2, M3) AS Result
FROM StudentDetails;