-- 11==============================
CREATE TABLE student1(
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
age NUMBER
);
SET SERVEROUTPUT ON;
DECLARE
v_id [Link]%TYPE := 101;
v_id [Link]%TYPE := 102;
v_name [Link]%TYPE;
v_age [Link]%TYPE;
BEGIN
-- INSERT Operation
INSERT INTO student1 VALUES (v_id, 'Rahul', 21);
DBMS_OUTPUT.PUT_LINE('Record Inserted');
INSERT INTO student1 VALUES (v_id, 'Rimpal', 20);
DBMS_OUTPUT.PUT_LINE('Record Inserted');
-- SELECT Operation
SELECT name, age INTO v_name, v_age FROM student1 WHERE id = v_id;
DBMS_OUTPUT.PUT_LINE('Selected: ' || v_name || ' - ' || v_age);
-- UPDATE Operation
UPDATE student1 SET age = 22 WHERE id = v_id;
DBMS_OUTPUT.PUT_LINE('Record Updated');
-- SELECT after Update
SELECT name, age INTO v_name, v_age FROM student1 WHERE id = v_id;
DBMS_OUTPUT.PUT_LINE('Updated: ' || v_name || ' - ' || v_age);
-- DELETE Operation
DELETE FROM student1 WHERE id = v_id;
DBMS_OUTPUT.PUT_LINE('Record Deleted');
END;
-- 12=========================================================
SET SERVEROUTPUT ON;
DECLARE
v_result NUMBER; -- variable to hold return value
BEGIN
DELETE FROM student
WHERE id = 101; -- change id as needed
IF SQL%ROWCOUNT > 0 THEN
v_result := 1; -- record deleted successfully
ELSE
v_result := 0; -- no record found to delete
END IF;
DBMS_OUTPUT.PUT_LINE('Return Value: ' || v_result);
EXCEPTION
WHEN OTHERS THEN
v_result := 0; -- any error also treated as failure
DBMS_OUTPUT.PUT_LINE('Return Value: ' || v_result);
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
select *from student;
insert into student values(101,'Rohit','cse');
-- 13========================
SET SERVEROUTPUT ON;
DECLARE
-- Cursor to fetch employee name and hire date
CURSOR emp_cursor IS
SELECT ename, hiredate
FROM emp;
v_name [Link]%TYPE;
v_hiredate [Link]%TYPE;
BEGIN
OPEN emp_cursor; -- Cursor opened
LOOP
FETCH emp_cursor INTO v_name, v_hiredate;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ' | Hire Date: ' || v_hiredate);
END LOOP;
CLOSE emp_cursor; -- Cursor closed
END;
/
CREATE TABLE emp (
eid NUMBER PRIMARY KEY,
ename VARCHAR2(50),
hiredate DATE
);
INSERT INTO emp VALUES (101, 'Rahul', TO_DATE('10-05-2023', 'DD-MM-YYYY'));
INSERT INTO emp VALUES (102, 'Amit', TO_DATE('15-07-2022', 'DD-MM-YYYY'));
INSERT INTO emp VALUES (103, 'Sneha', TO_DATE('20-03-2024', 'DD-MM-YYYY'));
select * from emp;
==================================14==========
SET SERVEROUTPUT ON;
DECLARE
-- Cursor to fetch top 5 highest paid employees
CURSOR emp_cursor IS
SELECT id, ename, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY; -- top 5 salaries
v_id [Link]%TYPE;
v_ename [Link]%TYPE;
v_salary [Link]%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_id, v_ename, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
'ID: ' || v_id ||
' | Name: ' || v_ename ||
' | Salary: ' || v_salary
);
END LOOP;
CLOSE emp_cursor;
END;
/
CREATE TABLE employee (
id NUMBER PRIMARY KEY,
ename VARCHAR2(50),
salary NUMBER,
hiredate DATE
);
-- Insert 10 Records
INSERT INTO employee VALUES (1, 'Rahul', 50000, TO_DATE('10-01-2021', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (2, 'Amit', 45000, TO_DATE('12-03-2020', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (3, 'Sneha', 60000, TO_DATE('20-07-2019', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (4, 'Karan', 55000, TO_DATE('15-09-2022', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (5, 'Riya', 70000, TO_DATE('30-11-2021', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (6, 'Nikhil', 48000, TO_DATE('25-04-2023', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (7, 'Priya', 52000, TO_DATE('05-05-2022', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (8, 'Vikas', 75000, TO_DATE('18-12-2018', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (9, 'Anjali', 47000, TO_DATE('10-02-2020', 'DD-MM-YYYY'));
INSERT INTO employee VALUES (10, 'Deepak', 65000, TO_DATE('14-08-2019', 'DD-MM-YYYY'));
COMMIT;