SHRI RAMSWAROOP MEMORIAL

UNIVERSITY

DATABASE MANAGEMENT SYSTEM

(UCS 3201)
LAB FILE
SESSION: 2024-2025

SUBMITTED TO: SUBMITTED BY:

MRS. SWETA SINGH NAME: DHRUV SINGH
ASSISTANT PROFESSOR ROLL NO.: 202310101310035
GROUP: BCA-3A
 INDEX

FACULTY
S.NO NAME OF ACTIVITY DATE REMARKS
SIGNATURE

Queries for DDL and

1
DML commands
SQL Queries Using
2
Logical Operators
SQL Queries Using SQL
3
Operators
SQL Queries Using
4 Character, Number, Date,
and Group Functions
SQL Queries For
5
Relational Algebra
SQL Queries For
6 Extracting Data From
More Than One Table
SQL Queries For Sub
7
Queries, Nested Queries
Write Program Of
8
PL/SQL
Concepts for Rollback,
9 Commit, Savepoints &
Checkpoints
Create VIEWS,
10 CURSORS And
TRIGGERS
 Lab Activity 1
 Write the queries for Data Definition and Data
Manipulation Language

1. Data Definition Language (DDL) Queries

 CREATE TABLE
Syntax:

CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
...
);

Example Query:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(10, 2),
DepartmentID INT
);

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

EMPLOYEE TABLE:-

DEPARTMENT TABLE:-

 ALTER TABLE
Syntax:

ALTER TABLE table_name

ADD (column_name datatype constraint);
 Example Query:

ALTER TABLE Employees

ADD (HireDate DATE);

OUTPUT:-

 TRUNCATE TABLE
Syntax:

TRUNCATE TABLE table_name;

Example Query:

TRUNCATE TABLE Employees;

OUTPUT:-

 DROP TABLE
Syntax:

DROP TABLE table_name;

Example Query:

DROP TABLE Employees;

2. Data Manipulation Language (DML) Queries

 INSERT INTO
Syntax:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example Query:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary, HireDate,

DepartmentID)
VALUES
(1, 'John', 'Doe', 50000, '2020-01-15', 10),
(2, 'Jane', 'Smith', 60000, '2019-03-20', 20),
(3, 'Michael', 'Johnson', 55000, '2018-05-10', 30),
(4, 'Alice', 'Brown', 70000, '2021-07-25', 10),
(5, 'Emma', 'Davis', 75000, '2020-09-30', 20),
 (6, 'David', 'Miller', 65000, '2017-11-13', 30),
(7, 'Sarah', 'Wilson', 80000, '2016-12-05', 20),
(8, 'Chris', 'Moore', 72000, '2021-02-18', 10),
(9, 'Sophia', 'Taylor', 53000, '2019-07-22', 30),
(10, 'Daniel', 'Thomas', 58000, '2022-03-03', 10),
(11, 'Julius', 'Nova', 65000, '2022-08-25', 20);

INSERT INTO Departments (DepartmentID, DepartmentName)

VALUES
(10, 'HR'),
(20, 'Finance'),
(30, 'Engineering');

OUTPUT:-

EMPLOYEES TABLE

DEPARTMENT TABLE

 UPDATE
Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example Query:

UPDATE Employees
SET Salary = 55000
WHERE EmployeeID = 1;

OUTPUT:-
 DELETE
Syntax:

DELETE FROM table_name

WHERE condition;

Example Query:

DELETE FROM Employees

WHERE EmployeeID = 11;

OUTPUT:-
 Lab Activity 2
 Write SQL Queries Using Logical Operators
 AND Operator
Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE condition1 AND condition2;

Example Query:
SELECT FirstName, LastName
FROM Employees
WHERE Salary > 50000 AND DepartmentID = 10;

OUTPUT:-

 OR Operator
Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE condition1 OR condition2;

Example Query:

SELECT FirstName, LastName

FROM Employees
WHERE DepartmentID = 10 OR DepartmentID = 20;

OUTPUT:-
 NOT Operator
Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE NOT condition;

Example Query:

SELECT FirstName, LastName

FROM Employees
WHERE NOT DepartmentID = 10;

OUTPUT:-
 Lab Activity 3
 Write SQL Queries Using SQL Operators
 Comparison Operators
Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE column1 operator value;

Example Query:
SELECT FirstName, LastName
FROM Employees
WHERE Salary > 50000;

OUTPUT:-

 Arithmetic Operators
Syntax:

SELECT column1 + column2 AS result

FROM table_name;

Example Query:

SELECT FirstName, Salary, Salary * 0.1 AS Bonus

FROM Employees;

OUTPUT:-
 String Operators
Syntax:

SELECT CONCAT(coulmn1, ' ', column2) AS concatenated_column

FROM table_name;

Example Query:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName

FROM Employees;

OUTPUT:-
 Lab Activity 4
 Write SQL Queries Using Character, Number, Date, and
Group Functions

1. Character Functions

 UPPER and LOWER

Syntax:
UPPER(string)
LOWER(string)

Example Query:

SELECT UPPER(FirstName) AS UpperFirstName, LOWER(LastName) AS LowerLastName

FROM Employees;

OUTPUT:-

2. Number Functions

 ROUND
Syntax:

ROUND(number, decimal_places)

Example Query:

SELECT Salary, ROUND(Salary, -3) AS RoundedSalary

FROM Employees;

OUTPUT:-
3. Date Functions

 CURDATE
Syntax:

CURDATE()

Example Query:

SELECT FirstName, HireDate, DATEDIFF(CURDATE(), HireDate) AS DaysEmployed

FROM Employees;

OUTPUT:-

4. Group Functions

 AVERAGE
Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example Query:
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

OUTPUT:-
 SUM
Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example Query:

-- Sum of all employee salaries

SELECT DepartmentID, SUM(Salary) AS TotalSalaries
FROM Employees
GROUP BY DepartmentID;

OUTPUT:-

 COUNT
Syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example Query:

-- Count the total number of employees

SELECT DepartmentID, COUNT(EmployeeID) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID;

OUTPUT:-

 MIN
Syntax:
 SELECT MIN(column_name)
FROM table_name
WHERE condition;

Example Query:

-- Minimum salary among employees

SELECT DepartmentID, MIN(Salary) AS MinimumSalary
FROM Employees
GROUP BY DepartmentID;

OUTPUT:-

 MAX
Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Example Query:

-- Maximum salary among employees

SELECT DepartmentID, MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY DepartmentID;

OUTPUT:-

 FIRST Element By Using LIMIT

Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name ASC
LIMIT 1;

Example Query:

-- First employee by EmployeeID

SELECT FirstName, LastName
FROM Employees
ORDER BY EmployeeID ASC
LIMIT 1;
 OUTPUT:-

 LAST Element By Using LIMIT

Syntax:

SELECT column1, column2, ...

FROM table_name
ORDER BY column_name DESC
LIMIT 1;

Example Query:

-- First employee by EmployeeID

SELECT FirstName, LastName
FROM Employees
ORDER BY EmployeeID DESC
LIMIT 1;

OUTPUT:-

 GROUP_CONCAT
Syntax:

SELECT GROUP_CONCAT(column_name SEPARATOR 'separator')

FROM table_name
GROUP BY column_name;

Example Query:

-- List all employees in each department

SELECT DepartmentID, GROUP_CONCAT(FirstName SEPARATOR ', ') AS Employees
FROM Employees
GROUP BY DepartmentID;

OUTPUT:-
 Lab Activity 5
Write SQL Queries For Relational Algebra
1. Selection (σ)
 Selection Syntax:

σ (condition) (Relation/Table_Name);

Example Query:

σ (Salary > 60000) (Employees);

OUTPUT:-

2. Projection (π)
 Projection Syntax:

π (column_name) (Relation/Table_Name);

Example Query:

π (FirstName, LastName) (Employees);

OUTPUT:-

3. Union (∪)
 Union Syntax:

R1 ∪ R2;
 Example Query:

π (FirstName) [σ (DepartmentID = 10) (Employees))

∪
π (FirstName) [σ (DepartmentID = 20) (Employees));
OUTPUT:-

4. Intersection (∩)
 Intersection Syntax:

R1 ∩ R2;

Example Query:

π (FirstName) [σ (DepartmentID = 10) (Employees))

∩
π (FirstName) [σ (Salary > 60000) (Employees));
OUTPUT:-

5. Cartesian Product (×)

 Cartesian Syntax:
R1 × R2;

Example Query:

Employees × Departments;
OUTPUT:-
6. Rename (ρ)
 Rename Syntax:

ρ (new_name, old_name) (Relation);

Example Query:

ρ (First_Name, FirstName) (Employees);

OUTPUT:-
 Lab Activity 6
 Write SQL Queries For Extracting Data from More Than
One Table

1. Inner Join
 Inner Join Syntax:

SELECT column1, column2, ...

FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Example Query:

SELECT e.FirstName, e.LastName, d.DepartmentName

FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

OUTPUT:-

2. Left Join (or Left Outer Join)

 Left Join Syntax:

SELECT column1, column2, ...

FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example Query:

SELECT e.FirstName, e.LastName, d.DepartmentName

FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

OUTPUT:-
3. Right Join (or Right Outer Join)
 Right Join Syntax:

SELECT column1, column2, ...

FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Example Query:

SELECT e.FirstName, e.LastName, d.DepartmentName

FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

OUTPUT:-

4. Cross Join
 Cross Join Syntax:

SELECT column1, column2, ...

FROM table1
CROSS JOIN table2;

Example Query:

SELECT e.FirstName, d.DepartmentName

FROM Employees e
CROSS JOIN Departments d;
OUTPUT:-
 Lab Activity 7
 Write SQL Queries For Subqueries, Nested Queries
1. Subquery Example
 Subquery Syntax:
SELECT column1, column2
FROM table_name
WHERE column3 operator (SELECT column3 FROM table_name WHERE condition);

Example Query:

SELECT FirstName, LastName

FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

OUTPUT:-

2. Nested Query Example

 Nested Query Syntax:

SELECT column1, column2

FROM table_name
WHERE column1 IN (SELECT column1 FROM table_name WHERE condition);

Example Query:

SELECT FirstName, LastName

FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Employees WHERE DepartmentID =
10);

OUTPUT:-
 Lab Activity 8
Write Program Of PL/SQL Programming
 PL/SQL Procedure (Stored Procedure in MySQL)
Syntax:

DELIMITER $$

CREATE PROCEDURE procedure_name (IN|OUT|INOUT parameter_name datatype)

BEGIN
-- Declare variables (optional)
DECLARE variable_name datatype;

-- Procedural logic (e.g., SQL statements)

-- Example: SELECT, IF-ELSE, LOOPS, etc.

SELECT column_name INTO variable_name

FROM table_name
WHERE condition;

-- We can also use IF, WHILE, etc.

IF condition THEN
-- Do something
END IF;

END $$

DELIMITER ;

Example Procedure:

DELIMITER $$

CREATE PROCEDURE CalculateBonus(IN emp_id INT, OUT bonus DECIMAL(10,2))

BEGIN
-- Declare a variable to store the salary
DECLARE salary DECIMAL(10,2);

-- Retrieve the employee's salary from the Employees table

SELECT Salary INTO salary
FROM Employees
WHERE EmployeeID = emp_id;

-- Calculate the bonus as 10% of the salary

SET bonus = salary * 0.10;

END $$

DELIMITER ;

How To Call The Procedure:

CALL CalculateBonus(1, @bonus);

SELECT @bonus;
 OUTPUT:-

 PL/SQL Function (Function in MySQL)

Syntax:

DELIMITER $$

CREATE FUNCTION function_name (parameter_name datatype)

RETURNS return_datatype
DETERMINISTIC
BEGIN
-- Declare variables (optional)
DECLARE variable_name datatype;

-- Procedural logic (e.g., calculations or SQL statements)

-- Example: SELECT, calculations, etc.
SET variable_name = expression;

-- Return the result

RETURN variable_name;
END $$

DELIMITER ;

Example Function:

DELIMITER $$

CREATE FUNCTION CalculateBonus (emp_id INT)

RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
-- Declare a variable to store the salary
DECLARE salary DECIMAL(10,2);
DECLARE bonus DECIMAL(10,2);

-- Select the employee's salary from the Employees table

SELECT Salary INTO salary
FROM Employees
WHERE EmployeeID = emp_id;

-- Calculate the bonus as 10% of the salary

SET bonus = salary * 0.10;
 -- Return the calculated bonus
RETURN bonus;
END $$

DELIMITER ;

How To Call The Procedure:

SELECT CalculateBonus(1) AS EmployeeBonus;

OUTPUT:-
 Lab Activity 9
 Concepts for ROLLBACK, COMMIT, SAVEPOINT, &
CHECKPOINT
 SAVEPOINT
Syntax:

SAVEPOINT savepoint_name;

Example Query:

-- Start a new transaction

START TRANSACTION;

-- Perform some updates

UPDATE Employees
SET Salary = Salary + 5000
WHERE EmployeeID = 1;

-- Set a savepoint
SAVEPOINT savepoint1;

-- Perform another update

UPDATE Employees
SET Salary = Salary + 3000
WHERE EmployeeID = 2;

OUTPUT:-

 ROLLBACK
Syntax:

ROLLBACK;

Example Query:
 -- Rollback to the savepoint (undo the second update)
ROLLBACK TO SAVEPOINT savepoint1;

OUTPUT:-

 COMMIT
Syntax:

COMMIT;

Example Query:

-- Perform another update

UPDATE Employees
SET Salary = Salary + 3000
WHERE EmployeeID = 2;

-- Commit the changes made before the savepoint

COMMIT;

OUTPUT:-
 CHECKPOINT
In MySQL, there is no explicit CHECKPOINT command like in some other
database systems (e.g., PostgreSQL or SQL Server). The concept of a checkpoint
is managed internally by MySQL's storage engines (such as InnoDB), but it’s not
something we control directly via SQL commands.

What is a Checkpoint in Databases?

A checkpoint is a mechanism used by databases to ensure that all modified data

pages in memory (buffers) are flushed to disk, ensuring data integrity. In MySQL
(InnoDB), checkpoints are automatically triggered, but we can't explicitly run them
using SQL.

In MySQL:

 MySQL performs checkpoints automatically at regular intervals to minimize the

work that needs to be done during recovery (after a crash or restart).
 We do not need to explicitly manage checkpoints as it’s handled internally.
 Lab Activity 10
 Create VIEWS, CURSORS, and TRIGGERS
 Creating Views
Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example Query:

CREATE VIEW EmployeeDetails AS

SELECT FirstName, LastName, Salary, DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Querying the View:

SELECT * FROM EmployeeDetails;

OUTPUT:-

 Creating Cursors
Syntax:

DELIMITER $$

CREATE PROCEDURE procedure_name()

BEGIN
-- Declare variables
DECLARE done INT DEFAULT FALSE;
DECLARE variable_name datatype;

-- Declare cursor
DECLARE cur CURSOR FOR SELECT column_name FROM table_name;

-- Handler to exit the loop when no more rows are found

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 -- Open cursor
OPEN cur;

-- Loop through the result set

read_loop: LOOP
FETCH cur INTO variable_name;
IF done THEN
LEAVE read_loop;
END IF;
-- Process the row (e.g., perform some operation with the data)
END LOOP;

-- Close cursor
CLOSE cur;
END $$

DELIMITER ;

Example Block:

DELIMITER $$

CREATE PROCEDURE simple_cursor_example()

BEGIN
-- Declare a variable to store the employee name
DECLARE done INT DEFAULT FALSE;
DECLARE employeeName VARCHAR(50);

-- Declare a cursor to fetch employee first names from the Employees

table
DECLARE cur CURSOR FOR SELECT FirstName FROM Employees;

-- Handler to exit the loop when no more rows are found

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Open the cursor

OPEN cur;

-- Loop to fetch and process each row

read_loop: LOOP
FETCH cur INTO employeeName;
IF done THEN
LEAVE read_loop;
END IF;
-- For now, we will just select the employee's name
SELECT employeeName;
END LOOP;

-- Close the cursor

CLOSE cur;
END $$

DELIMITER ;
 OUTPUT:-

 Creating Triggers
Syntax:
CREATE TRIGGER trigger_name
BEFORE | AFTER INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
-- Trigger body (e.g., actions to perform)
END;

Example Trigger for Insert Operations:

DELIMITER $$

CREATE TRIGGER after_employee_insert

AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
-- Insert into a log table when a new employee is added
INSERT INTO EmployeeLog(EmployeeID, Action, ActionTime)
VALUES (NEW.EmployeeID, 'INSERT', NOW());
END $$

DELIMITER ;

OUTPUT:-