DBMS LAB PRACTICAL FILE
Name – Aanya Singh
Registration Number – 23BCE10956
Slot – C14+E11+E12
Course Code : CSE3001
Name of Faculty Member : DR. RAVI VERMA
INDEX
Experiment No. Page No.
1 2
2 2
3 2
4 3
5 3
6 3
7 3
8 4
9 4
10 4
11 4
12 5
13 5
14 6
15 6
16 7
17 7
18 7
19 8
1
1. Delete duplicate row from the table.
CREATE DATABASE company_db;
USE company_db;
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
salary DECIMAL(10,2)
);
INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES
('John', 'Doe', '2023-01-01', 50000),
('Jane', 'Smith', '2022-12-15', 60000),
('Michael', 'Johnson', '2023-03-10', 45000),
('Emily', 'Brown', '2023-04-05', 55000),
('David', 'Lee', '2022-11-20', 70000);
DELETE FROM your_table
WHERE id IN (
SELECT id
FROM your_table
GROUP BY column1, column2, ... -- Replace with relevant columns
HAVING COUNT(*) > 1
);
2. Display the alternate row from table
SELECT *
FROM employees
WHERE MOD(emp_id, 2) = 1;
3. Delete alternate row from table.
2
DELETE FROM employees
WHERE MOD(emp_id, 2) = 1;
4. Update multiple rows in using single update
statement.
UPDATE employees
SET salary = salary * 1.1
WHERE hire_date < '2023-01-01';
5. Find the third highest paid and third lowest
paid salary.
-- Third highest salary
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 2, 1;
-- Third lowest salary
SELECT salary
FROM employees
ORDER BY salary ASC
LIMIT 2, 1;
6. Display the 3rd, 4th, 9th rows from table.
(SELECT * FROM employees LIMIT 2, 1)
UNION ALL
(SELECT * FROM employees LIMIT 3, 1)
UNION ALL
(SELECT * FROM employees LIMIT 8, 1);
7. Display the name, which is start with j, k, l
or m.
SELECT first_name
FROM employees
WHERE first_name LIKE 'j%' OR first_name LIKE 'k%' OR first_name LIKE 'l%' OR
first_name LIKE 'm%';
3
8. Show all employees who were hired the first
half of the month.
SELECT *
FROM employees
WHERE DAY(hire_date) <= 15;
9. Display the three record in the first row and
two records in the second row and one
record in the third row in a single sql
statements.
(SELECT * FROM employees LIMIT 3)
UNION ALL
(SELECT * FROM employees LIMIT 2 OFFSET 3)
UNION ALL
(SELECT * FROM employees LIMIT 1 OFFSET 5);
10. Write a sql statements for rollback commit
and save points.
ROLLBACK;
COMMIT;
SAVEPOINT my_savepoint;
11. Write a pl/sql for select, insert, update
and delete statements.
DECLARE
v_emp_id employees.emp_id%TYPE;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
v_hire_date employees.hire_date%TYPE;
v_salary employees.salary%TYPE;
BEGIN
-- SELECT
SELECT emp_id, first_name, last_name, hire_date, salary
INTO v_emp_id, v_first_name, v_last_name, v_hire_date, v_salary
FROM employees
WHERE emp_id = 1; -- Replace with your desired condition
4
-- INSERT
INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('New Employee', 'New Last Name', SYSDATE, 40000);
-- UPDATE
UPDATE employees
SET salary = salary * 1.1
WHERE emp_id = 1;
-- DELETE
DELETE FROM employees
WHERE emp_id = 2;
END;
12. Write a pl/sql block to delete a record. If
delete operation is successful return 1 else
return 0.
DECLARE
rows_deleted NUMBER;
BEGIN
DELETE FROM employees
WHERE emp_id = 10; -- Replace with your condition
rows_deleted := SQL%ROWCOUNT;
IF rows_deleted > 0 THEN
DBMS_OUTPUT.PUT_LINE('Record deleted successfully.');
RETURN 1;
ELSE
DBMS_OUTPUT.PUT_LINE('No record deleted.');
RETURN 0;
END IF;
END;
13. Display name, hire date of all employees
using cursors.
DECLARE
CURSOR emp_cur IS SELECT first_name, hire_date FROM employees;
emp_rec emp_cur%ROWTYPE;
5
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.hire_date);
END LOOP;
CLOSE emp_cur;
END;
14. Display details of first 5 highly paid
employees using cursors.
DECLARE
CURSOR top_employees IS SELECT first_name, last_name, salary FROM employees
ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;
emp_rec top_employees%ROWTYPE;
BEGIN
OPEN top_employees;
LOOP
FETCH top_employees INTO emp_rec;
EXIT WHEN top_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name || ' ' ||
emp_rec.salary);
END LOOP;
CLOSE top_employees;
END;
15. Write a database trigger which fires if you
try to insert, update, or delete after 7’o’
clock.
CREATE TRIGGER after_7pm_trigger
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE, 'HH24') >= 19 THEN
RAISE_APPLICATION_ERROR(-20001, 'Cannot perform operation after 7 PM');
END IF;
END;
6
16. Write a data base trigger, which acts just
like primary key and does not allow
duplicate values.
CREATE UNIQUE INDEX idx_unique_employee ON employees(emp_id);
17. Create a data base trigger, which performs
the action of the on delete cascade.
CREATE TRIGGER delete_dependent_records
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
DELETE FROM dependent_table
WHERE emp_id = :OLD.emp_id;
END;
18. Write a data base trigger, which should
not delete from emp table if the day is
Sunday.
CREATE TRIGGER prevent_sunday_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE, 'DY') = 'SUNDAY' THEN
RAISE_APPLICATION_ERROR(-20002, 'Cannot delete records on Sundays');
END IF;
END;
19. In this subject the students are supposed to
prepare a small database application in
complete semester like financial accounting
7
system, Railway reservation system, institute
timetable management system. Student record
system, library management system, hospital
management system etc. in RDBMS as follows:
CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
author VARCHAR(100),
publication_year INT,
ISBN VARCHAR(20),
genre VARCHAR(50)
);
CREATE TABLE members (
member_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
address VARCHAR(255),
contact_no VARCHAR(20)
);
CREATE TABLE loans (
loan_id INT PRIMARY KEY AUTO_INCREMENT,
book_id INT,
member_id INT,
issue_date DATE,
due_date DATE,
return_date DATE,
FOREIGN KEY (book_id) REFERENCES books(book_id),
FOREIGN KEY (member_id) REFERENCES members(member_id)
);