DBMS Lab Programs

DBMS lab programs help you convert database theory into working SQL practice. A good lab file should show table creation, constraints, DML operations, joins, aggregate qu

DBMS lab programs help you convert database theory into working SQL practice. A good lab file should show table creation, constraints, DML operations, joins, aggregate queries, views, triggers, stored procedures, cursors, transactions, indexes, and user privileges. These programs are useful for semester practical exams, viva preparation, and interview revision because they cover the operations students are most often asked to demonstrate on MySQL, PostgreSQL, Oracle, or SQL Server.

Before running the programs, create a clean database such as college_db and execute each lab in order. The examples below use Department and Employee tables so that every later query has consistent sample data. If your college uses Oracle, replace AUTO_INCREMENT with a sequence or identity column. If your college uses PostgreSQL, use SERIAL or GENERATED AS IDENTITY where needed.

Related practice topics: DDL Commands, DML Commands, Joins, Nested Queries, and Transaction Management.

How to Use These DBMS Lab Programs

Run the CREATE TABLE statements first, insert sample records, and then test one concept at a time. After every UPDATE, DELETE, trigger, or transaction command, run a SELECT query to verify the output. For viva, do not only memorize the query; understand what table is changed, which rows are affected, and whether the command is DDL, DML, DCL, TCL, or a procedural SQL feature.

Common mistakes in DBMS lab exams include forgetting primary keys, inserting child records before parent records, using DELETE without a WHERE clause, and confusing WHERE with HAVING. Keep screenshots or outputs for each lab because many practical files require both query and result.

Lab 1 — Creating Tables with Constraints

sql exampleWoHoTech

-- Create Department Table
CREATE TABLE Department (
    dept_id     INT PRIMARY KEY,
    dept_name   VARCHAR(50) NOT NULL,
    location    VARCHAR(100)
);

-- Create Employee Table
CREATE TABLE Employee (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    salary      DECIMAL(10, 2) CHECK (salary > 0),
    dept_id     INT,
    hire_date   DATE,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Lab 2 — DML Operations

sql exampleWoHoTech

-- Insert Records
INSERT INTO Department VALUES (1, 'Computer Science', 'Block A');
INSERT INTO Department VALUES (2, 'Mathematics', 'Block B');
INSERT INTO Department VALUES (3, 'Physics', 'Block C');

INSERT INTO Employee VALUES (101, 'Alice Johnson', 75000, 1, '2020-06-15');
INSERT INTO Employee VALUES (102, 'Bob Smith',    62000, 2, '2019-03-10');
INSERT INTO Employee VALUES (103, 'Carol White',  88000, 1, '2021-01-20');
INSERT INTO Employee VALUES (104, 'David Brown',  55000, 3, '2022-07-01');

-- Update Records
UPDATE Employee SET salary = salary * 1.10 WHERE dept_id = 1;

-- Delete Records
DELETE FROM Employee WHERE emp_id = 104;

-- Select with Conditions
SELECT emp_name, salary FROM Employee WHERE salary > 70000 ORDER BY salary DESC;

Lab 3 — SQL Joins

sql exampleWoHoTech

-- INNER JOIN
SELECT e.emp_name, e.salary, d.dept_name
FROM Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id;

-- LEFT OUTER JOIN
SELECT e.emp_name, d.dept_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id;

-- Self Join (find employees with same department)
SELECT A.emp_name AS Employee1, B.emp_name AS Employee2, A.dept_id
FROM Employee A, Employee B
WHERE A.dept_id = B.dept_id AND A.emp_id <> B.emp_id;

Lab 4 — Aggregate Functions and GROUP BY

sql exampleWoHoTech

-- Count employees per department
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM Department d
LEFT JOIN Employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

-- Average salary per department
SELECT dept_id,
       AVG(salary)  AS avg_salary,
       MAX(salary)  AS max_salary,
       MIN(salary)  AS min_salary,
       SUM(salary)  AS total_salary
FROM Employee
GROUP BY dept_id
HAVING AVG(salary) > 60000;

Lab 5 — Subqueries and Nested Queries

sql exampleWoHoTech

-- Find employees earning more than average salary
SELECT emp_name, salary
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);

-- Find department with highest total salary
SELECT dept_id, SUM(salary) AS total
FROM Employee
GROUP BY dept_id
HAVING SUM(salary) = (
    SELECT MAX(dept_total)
    FROM (SELECT SUM(salary) AS dept_total FROM Employee GROUP BY dept_id) AS sub
);

-- Correlated subquery: employees in CS department
SELECT emp_name FROM Employee e
WHERE EXISTS (
    SELECT 1 FROM Department d
    WHERE d.dept_id = e.dept_id AND d.dept_name = 'Computer Science'
);

Lab 6 — Views

sql exampleWoHoTech

-- Create a view for high-salary employees
CREATE VIEW HighSalaryEmployees AS
SELECT emp_id, emp_name, salary, dept_id
FROM Employee
WHERE salary > 70000;

-- Query the view
SELECT * FROM HighSalaryEmployees;

-- Create view with join
CREATE VIEW EmployeeDepartmentView AS
SELECT e.emp_id, e.emp_name, e.salary, d.dept_name, d.location
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

-- Drop a view
DROP VIEW HighSalaryEmployees;

Lab 7 — Triggers

sql exampleWoHoTech

-- Trigger to log salary changes
CREATE TABLE SalaryAudit (
    audit_id    INT AUTO_INCREMENT PRIMARY KEY,
    emp_id      INT,
    old_salary  DECIMAL(10, 2),
    new_salary  DECIMAL(10, 2),
    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER salary_update_trigger
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO SalaryAudit (emp_id, old_salary, new_salary)
        VALUES (OLD.emp_id, OLD.salary, NEW.salary);
    END IF;
END;

Lab 8 — Stored Procedures

sql exampleWoHoTech

-- Procedure to get employee details by department
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept INT)
BEGIN
    SELECT emp_id, emp_name, salary
    FROM Employee
    WHERE dept_id = dept
    ORDER BY salary DESC;
END //
DELIMITER ;

-- Call the procedure
CALL GetEmployeesByDept(1);

-- Procedure with OUT parameter
DELIMITER //
CREATE PROCEDURE GetAvgSalary(IN dept INT, OUT avg_sal DECIMAL(10,2))
BEGIN
    SELECT AVG(salary) INTO avg_sal
    FROM Employee
    WHERE dept_id = dept;
END //
DELIMITER ;

-- Call with output
CALL GetAvgSalary(1, @result);
SELECT @result AS AverageSalary;

Lab 9 — Cursors

sql exampleWoHoTech

DELIMITER //
CREATE PROCEDURE ProcessAllEmployees()
BEGIN
    DECLARE done     INT DEFAULT FALSE;
    DECLARE v_id     INT;
    DECLARE v_name   VARCHAR(100);
    DECLARE v_salary DECIMAL(10,2);

    DECLARE emp_cursor CURSOR FOR
        SELECT emp_id, emp_name, salary FROM Employee;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO v_id, v_name, v_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Process each row
        SELECT CONCAT('Processing: ', v_name, ' | Salary: ', v_salary) AS info;
    END LOOP;

    CLOSE emp_cursor;
END //
DELIMITER ;

Lab 10 — Transaction Management

sql exampleWoHoTech

-- Transaction with COMMIT
START TRANSACTION;

UPDATE Employee SET salary = salary + 5000 WHERE emp_id = 101;
UPDATE Employee SET salary = salary - 5000 WHERE emp_id = 102;

-- Verify before commit
SELECT emp_id, salary FROM Employee WHERE emp_id IN (101, 102);

COMMIT;

-- Transaction with ROLLBACK
START TRANSACTION;

DELETE FROM Employee WHERE dept_id = 2;

-- Oops, wrong deletion — rollback
ROLLBACK;

-- Using SAVEPOINT
START TRANSACTION;

UPDATE Employee SET salary = 90000 WHERE emp_id = 101;
SAVEPOINT sp1;

UPDATE Employee SET salary = 95000 WHERE emp_id = 102;
SAVEPOINT sp2;

-- Roll back to sp1 only
ROLLBACK TO sp1;

COMMIT;

Lab 11 — Index Creation

sql exampleWoHoTech

-- Create index on frequently queried column
CREATE INDEX idx_emp_salary ON Employee(salary);

-- Composite index
CREATE INDEX idx_emp_dept_salary ON Employee(dept_id, salary);

-- Unique index
CREATE UNIQUE INDEX idx_emp_name ON Employee(emp_name);

-- View indexes
SHOW INDEX FROM Employee;

-- Drop index
DROP INDEX idx_emp_salary ON Employee;

Lab 12 — DCL Commands

sql exampleWoHoTech

-- Create a user
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password123';

-- Grant privileges
GRANT SELECT, INSERT ON Employee TO 'dbuser'@'localhost';
GRANT ALL PRIVILEGES ON Department TO 'dbuser'@'localhost';

-- Revoke privileges
REVOKE INSERT ON Employee FROM 'dbuser'@'localhost';

-- Show grants
SHOW GRANTS FOR 'dbuser'@'localhost';

Related practice topics: DDL Commands, DML Commands, Joins, Nested Queries, and Transaction Management.

How to Use These DBMS Lab Programs

Lab 1 — Creating Tables with Constraints

sql exampleWoHoTech

-- Create Department Table
CREATE TABLE Department (
    dept_id     INT PRIMARY KEY,
    dept_name   VARCHAR(50) NOT NULL,
    location    VARCHAR(100)
);

-- Create Employee Table
CREATE TABLE Employee (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    salary      DECIMAL(10, 2) CHECK (salary > 0),
    dept_id     INT,
    hire_date   DATE,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Lab 2 — DML Operations

sql exampleWoHoTech

-- Insert Records
INSERT INTO Department VALUES (1, 'Computer Science', 'Block A');
INSERT INTO Department VALUES (2, 'Mathematics', 'Block B');
INSERT INTO Department VALUES (3, 'Physics', 'Block C');

INSERT INTO Employee VALUES (101, 'Alice Johnson', 75000, 1, '2020-06-15');
INSERT INTO Employee VALUES (102, 'Bob Smith',    62000, 2, '2019-03-10');
INSERT INTO Employee VALUES (103, 'Carol White',  88000, 1, '2021-01-20');
INSERT INTO Employee VALUES (104, 'David Brown',  55000, 3, '2022-07-01');

-- Update Records
UPDATE Employee SET salary = salary * 1.10 WHERE dept_id = 1;

-- Delete Records
DELETE FROM Employee WHERE emp_id = 104;

-- Select with Conditions
SELECT emp_name, salary FROM Employee WHERE salary > 70000 ORDER BY salary DESC;

Lab 3 — SQL Joins

sql exampleWoHoTech

-- INNER JOIN
SELECT e.emp_name, e.salary, d.dept_name
FROM Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id;

-- LEFT OUTER JOIN
SELECT e.emp_name, d.dept_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id;

-- Self Join (find employees with same department)
SELECT A.emp_name AS Employee1, B.emp_name AS Employee2, A.dept_id
FROM Employee A, Employee B
WHERE A.dept_id = B.dept_id AND A.emp_id <> B.emp_id;

Lab 4 — Aggregate Functions and GROUP BY

sql exampleWoHoTech

-- Count employees per department
SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM Department d
LEFT JOIN Employee e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

-- Average salary per department
SELECT dept_id,
       AVG(salary)  AS avg_salary,
       MAX(salary)  AS max_salary,
       MIN(salary)  AS min_salary,
       SUM(salary)  AS total_salary
FROM Employee
GROUP BY dept_id
HAVING AVG(salary) > 60000;

Lab 5 — Subqueries and Nested Queries

sql exampleWoHoTech

-- Find employees earning more than average salary
SELECT emp_name, salary
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);

-- Find department with highest total salary
SELECT dept_id, SUM(salary) AS total
FROM Employee
GROUP BY dept_id
HAVING SUM(salary) = (
    SELECT MAX(dept_total)
    FROM (SELECT SUM(salary) AS dept_total FROM Employee GROUP BY dept_id) AS sub
);

-- Correlated subquery: employees in CS department
SELECT emp_name FROM Employee e
WHERE EXISTS (
    SELECT 1 FROM Department d
    WHERE d.dept_id = e.dept_id AND d.dept_name = 'Computer Science'
);

Lab 6 — Views

sql exampleWoHoTech

-- Create a view for high-salary employees
CREATE VIEW HighSalaryEmployees AS
SELECT emp_id, emp_name, salary, dept_id
FROM Employee
WHERE salary > 70000;

-- Query the view
SELECT * FROM HighSalaryEmployees;

-- Create view with join
CREATE VIEW EmployeeDepartmentView AS
SELECT e.emp_id, e.emp_name, e.salary, d.dept_name, d.location
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

-- Drop a view
DROP VIEW HighSalaryEmployees;

Lab 7 — Triggers

sql exampleWoHoTech

-- Trigger to log salary changes
CREATE TABLE SalaryAudit (
    audit_id    INT AUTO_INCREMENT PRIMARY KEY,
    emp_id      INT,
    old_salary  DECIMAL(10, 2),
    new_salary  DECIMAL(10, 2),
    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER salary_update_trigger
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO SalaryAudit (emp_id, old_salary, new_salary)
        VALUES (OLD.emp_id, OLD.salary, NEW.salary);
    END IF;
END;

Lab 8 — Stored Procedures

sql exampleWoHoTech

-- Procedure to get employee details by department
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept INT)
BEGIN
    SELECT emp_id, emp_name, salary
    FROM Employee
    WHERE dept_id = dept
    ORDER BY salary DESC;
END //
DELIMITER ;

-- Call the procedure
CALL GetEmployeesByDept(1);

-- Procedure with OUT parameter
DELIMITER //
CREATE PROCEDURE GetAvgSalary(IN dept INT, OUT avg_sal DECIMAL(10,2))
BEGIN
    SELECT AVG(salary) INTO avg_sal
    FROM Employee
    WHERE dept_id = dept;
END //
DELIMITER ;

-- Call with output
CALL GetAvgSalary(1, @result);
SELECT @result AS AverageSalary;

Lab 9 — Cursors

sql exampleWoHoTech

DELIMITER //
CREATE PROCEDURE ProcessAllEmployees()
BEGIN
    DECLARE done     INT DEFAULT FALSE;
    DECLARE v_id     INT;
    DECLARE v_name   VARCHAR(100);
    DECLARE v_salary DECIMAL(10,2);

    DECLARE emp_cursor CURSOR FOR
        SELECT emp_id, emp_name, salary FROM Employee;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO v_id, v_name, v_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Process each row
        SELECT CONCAT('Processing: ', v_name, ' | Salary: ', v_salary) AS info;
    END LOOP;

    CLOSE emp_cursor;
END //
DELIMITER ;

Lab 10 — Transaction Management

sql exampleWoHoTech

-- Transaction with COMMIT
START TRANSACTION;

UPDATE Employee SET salary = salary + 5000 WHERE emp_id = 101;
UPDATE Employee SET salary = salary - 5000 WHERE emp_id = 102;

-- Verify before commit
SELECT emp_id, salary FROM Employee WHERE emp_id IN (101, 102);

COMMIT;

-- Transaction with ROLLBACK
START TRANSACTION;

DELETE FROM Employee WHERE dept_id = 2;

-- Oops, wrong deletion — rollback
ROLLBACK;

-- Using SAVEPOINT
START TRANSACTION;

UPDATE Employee SET salary = 90000 WHERE emp_id = 101;
SAVEPOINT sp1;

UPDATE Employee SET salary = 95000 WHERE emp_id = 102;
SAVEPOINT sp2;

-- Roll back to sp1 only
ROLLBACK TO sp1;

COMMIT;

Lab 11 — Index Creation

sql exampleWoHoTech

-- Create index on frequently queried column
CREATE INDEX idx_emp_salary ON Employee(salary);

-- Composite index
CREATE INDEX idx_emp_dept_salary ON Employee(dept_id, salary);

-- Unique index
CREATE UNIQUE INDEX idx_emp_name ON Employee(emp_name);

-- View indexes
SHOW INDEX FROM Employee;

-- Drop index
DROP INDEX idx_emp_salary ON Employee;

Lab 12 — DCL Commands

sql exampleWoHoTech

-- Create a user
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password123';

-- Grant privileges
GRANT SELECT, INSERT ON Employee TO 'dbuser'@'localhost';
GRANT ALL PRIVILEGES ON Department TO 'dbuser'@'localhost';

-- Revoke privileges
REVOKE INSERT ON Employee FROM 'dbuser'@'localhost';

-- Show grants
SHOW GRANTS FOR 'dbuser'@'localhost';

DBMS Lab Programs

How to Use These DBMS Lab Programs

Lab 1 — Creating Tables with Constraints

Lab 2 — DML Operations

Lab 3 — SQL Joins

Lab 4 — Aggregate Functions and GROUP BY

Lab 5 — Subqueries and Nested Queries

Lab 6 — Views

Lab 7 — Triggers

Lab 8 — Stored Procedures

Lab 9 — Cursors

Lab 10 — Transaction Management

Lab 11 — Index Creation

Lab 12 — DCL Commands

Continue learning this concept

DBMS Lab Programs

How to Use These DBMS Lab Programs

Lab 1 — Creating Tables with Constraints

Lab 2 — DML Operations

Lab 3 — SQL Joins

Lab 4 — Aggregate Functions and GROUP BY

Lab 5 — Subqueries and Nested Queries

Lab 6 — Views

Lab 7 — Triggers

Lab 8 — Stored Procedures

Lab 9 — Cursors

Lab 10 — Transaction Management

Lab 11 — Index Creation

Lab 12 — DCL Commands

Continue learning this concept