1.
*CREATE TABLE*
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT );
2. *ALTER TABLE*
ALTER TABLE Students ADD Email VARCHAR(100);
3. *DROP TABLE*
DROP TABLE Students;
4. *INSERT INTO*
INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES (101, 'Neha',
'Kashyap', 22);
5. *UPDATE*
UPDATE Students SET Age = 21 WHERE StudentID = 1;
6. *DELETE*
DELETE FROM Students WHERE StudentID = 2;
7. *SELECT*
SELECT * FROM Students;
8. *WHERE*
SELECT * FROM Students WHERE Age > 18;
9. *DISTINCT*
SELECT DISTINCT Age FROM Students;
10. *SAVEPOINT*
SAVEPOINT sp1;
11. *SQL Operators*
**AND**
SELECT * FROM Students WHERE Age > 18 AND FirstName = 'Manish';
*BETWEEN*
SELECT * FROM Students WHERE Age BETWEEN 18 AND 25;
12. *SET Operations*
*UNION*
SELECT FirstName FROM Students UNION SELECT FirstName FROM Teachers;
*INTERSECT*
SELECT FirstName FROM Students INTERSECT SELECT FirstName FROM Teachers;
13. **JOIN Operations**
**CROSS JOIN**
SELECT * FROM Students CROSS JOIN Courses;
**INNER JOIN**
SELECT * FROM Students INNER JOIN Courses ON Students.StudentID =
Courses.StudentID;
**NATURAL JOIN**
SELECT * FROM Students NATURAL JOIN Courses;
**OUTER JOIN (LEFT)**
**LEFT JOIN**
SELECT * FROM Students LEFT JOIN Courses ON Students.StudentID =
Courses.StudentID;
**SELF JOIN**
SELECT A.StudentID, B.FirstName FROM Students A, Students B WHERE A.StudentID !=
B.StudentID;
14. HELLO WORLD Program in PL/SQL?
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
15. Program to display Sum of two numbers in PL/SQL?
DECLARE
num1 NUMBER := 10;
num2 NUMBER := 20;
sum NUMBER;
BEGIN
sum := num1 + num2;
DBMS_OUTPUT.PUT_LINE('The sum of ' || num1 || ' and ' || num2 || ' is ' || sum);
END;
16. Program to find the Largest of three numbers in PL/SQL?
DECLARE
num1 NUMBER := 10;
num2 NUMBER := 20;
num3 NUMBER := 30;
largest NUMBER;
BEGIN
IF num1 > num2 AND num1 > num3 THEN
largest := num1;
ELSIF num2 > num1 AND num2 > num3 THEN
largest := num2;
ELSE
largest := num3;
END IF;
DBMS_OUTPUT.PUT_LINE('The largest number is ' || largest);
END;
17. Program to find the Factorial of a number (using a loop) in PL/SQL?
DECLARE
num NUMBER := 5;
factorial NUMBER := 1;
BEGIN
FOR i IN 1..num LOOP
factorial := factorial * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The factorial of ' || num || ' is ' || factorial);
END;
18. Program to display Fibonacci series in PL/SQL?
DECLARE
num NUMBER := 10;
a NUMBER := 0;
b NUMBER := 1;
temp NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Fibonacci series up to ' || num || ' terms:');
DBMS_OUTPUT.PUT(a || ' ');
IF num > 1 THEN
DBMS_OUTPUT.PUT(b || ' ');
END IF;
FOR i IN 3..num LOOP
temp := a + b;
a := b;
b := temp;
DBMS_OUTPUT.PUT(b || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END;
19. Program to check if a number is prime in PL/SQL?
DECLARE
num NUMBER := 23;
is_prime BOOLEAN := TRUE;
BEGIN
IF num <= 1 THEN
is_prime := FALSE;
ELSE
FOR i IN 2..TRUNC(SQRT(num)) LOOP
IF MOD(num, i) = 0 THEN
is_prime := FALSE;
EXIT;
END IF;
END LOOP;
END IF;
IF is_prime THEN
DBMS_OUTPUT.PUT_LINE(num || ' is a prime number.');
ELSE
DBMS_OUTPUT.PUT_LINE(num || ' is not a prime number.');
END IF;
END;
20. Program to Demonstrate Exception Handling in PL/SQL?
DECLARE
num1 NUMBER := 10;
num2 NUMBER := 0;
result NUMBER;
BEGIN
BEGIN
result := num1 / num2;
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;
DBMS_OUTPUT.PUT_LINE('Program execution continues...');
END;
21. Program to use a cursor to Retrieve data from a table in PL/SQL?
-- Create a table
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(255),
salary NUMBER
);
-- Insert some data into the table
INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 50000);
INSERT INTO employees (employee_id, name, salary)
VALUES (2, 'Jane Doe', 60000);
INSERT INTO employees (employee_id, name, salary)
VALUES (3, 'Bob Smith', 70000);
-- Create a PL/SQL block that uses a cursor to retrieve data from the table
DECLARE
CURSOR employee_cursor IS
SELECT employee_id, name, salary
FROM employees;
employee_record employee_cursor%ROWTYPE;
BEGIN
OPEN employee_cursor;
LOOP
FETCH employee_cursor INTO employee_record;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || employee_record.employee_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || employee_record.name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || employee_record.salary);
DBMS_OUTPUT.PUT_LINE('------------------------');
END LOOP;
CLOSE employee_cursor;
END;
