Database Management System

Bachelor of Technology
Computer Science & Engineering

Lab File
For
Database Management System
E2UC302B
Session:2025-26

Submitted By:-

Student Name: Sneha Kumari

Admission No: 24scse1011020 Submitted To: [Link] Hassan
Semester:3rd (Assistant Professor)
Section: 01

SCHOOL OF COMPUTER SCIENCE AND ENGINEERING

GALGOTIAS UNIVERSITY, GREATER NOIDA
UTTAR PRADESH
India

Sneha Kumari :24SCSE1011020 Page 1

 Index

[Link]. Name of Program Date Signature Page No

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

Sneha Kumari :24SCSE1011020 Page 2

 EXPERIMENT 1.
AIM : Draw an E-R diagram and convert entities and relationships to a relation table for a given
scenario.
(Two assignments shall be carried out i.e. consider two different scenarios (e.g. bank, College)

Assignment 1: BANK MANAGEMENT SYSTEM

Entities & Attributes

Customer
Customer_ID (PK)
Name
Phone
Address

Account
Account_No (PK)
Type
Balance

Branch
Branch_ID (PK)
Branch_Name Location

Transaction
Transaction_ID (PK)
Date
Amount
Type
Relationships

Customer owns Account (1:M)

Branch maintains Account (1:M)
Account has Transaction (1:M)

Sneha Kumari :24SCSE1011020 Page 3

ER DIAGRAM:

Mapping entities and relationships into relation table:

Assignment 2: COLLEGE MANAGEMENT SYSTEM

Entities & Attributes

Student
Student_ID (PK)
First_Name
Last_Name
MIS

Sneha Kumari :24SCSE1011020 Page 4

Address
Birth_Date
Admission
Student_Num (PK)
Date_of_Enrollment Course_Name

Time_Table
Time_Table_ID (PK)
Date
Time
Attribute

Lecturer
Lecturer_ID (PK)
First_Name
Last_Name
Address
Attribute

Subjects
Subject_ID (PK)
Subject_Unit Attribute

Relationships

Student takes Admission (1:1)

Lecturer lectures Subjects (M:N)
Admission is linked with Time_Table (1:M)
Time_Table connects Lecturer and Subjects

ER DIAGRAM:

Sneha Kumari :24SCSE1011020 Page 5

Mapping entities and relationships into relation table:

Sneha Kumari :24SCSE1011020 Page 6

 Experiment 2.
Aim: Implementation of DDL commands of SQL with suitable examples. (a) Create table (b) Alter table
(c) Drop Table
(a) The CREATE TABLE command is used to create a new table in the database.
Example:
CREATE TABLE Student (
Student_ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(30));

Output:

(b) The ALTER TABLE command is used

Example:
ALTER TABLE Student
DROP Department;
Output:

© The DROP TABLE command is used

Example:
DROP TABLE Student;

Sneha Kumari :24SCSE1011020 Page 7

 Experiment 3.
Aim: Implementation of DML commands of SQL with suitable examples. (a) Insert table (b) Update
table (c) Delete Table
(a) The INSERT command is used
Example:
INSERT INTO Student (Student_ID, Name, Age, Department)
VALUES (101, 'Rani', 20, 'CSE');
Output:

(b)The UPDATE command is used

Example:
UPDATE Student
SET Age = 21
WHERE Student_ID = 101;
Output:

The DELETE command is used

Example:
DELETE FROM Student
WHERE Student_ID = 101;
Output:

Sneha Kumari :24SCSE1011020 Page 8

 Experiment 4.
Aim: Implementation of different types of functions with suitable examples. Number Function Aggregate
Function Character Function Conversion Function Date Function
Number Functions:
ABS() – absolute value
ROUND() – rounds a number
FLOOR() – largest integer ≤ value
CEIL() – smallest integer ≥ value
MOD() – remainder

Example:
SELECT ABS(-15);

Output:

SELECT ROUND(12.56, 1);

Output:

SELECT CEIL(5.2);

Output:

SELECT FLOOR(5.9);

Output:

SELECT MOD(10, 3);

Output:

Aggregate Functions
COUNT() – counts rows

Sneha Kumari :24SCSE1011020 Page 9

SUM() – total
AVG() – average
MAX() – highest value
MIN() – lowest value

Examples:
SELECT COUNT(*) FROM Student;

Output:

SELECT SUM(Marks) FROM Student;

Output:

Character Functions:
UPPER() – converts to uppercase
LOWER() – converts to lowercase
LENGTH() – length of string
SUBSTRING() – extracts part of string
CONCAT() – joins strings

Examples:
SELECT UPPER('sql');

Output:

SELECT SUBSTRING('Computer', 1, 4);

Output:

Conversion Functions

Sneha Kumari :24SCSE1011020 Page 10

CAST()
CONVERT()
Examples:
SELECT CAST(123.45 AS SIGNED);

Output:

SELECT CONVERT(123.45, INT);

Output:

Date Functions
CURDATE() – current date
NOW() – current date & time
YEAR() – extracts year
MONTH() – extracts month
DAY() – extracts day

Examples:
SELECT CURDATE();

Output:

SELECT NOW();

Output:

Sneha Kumari :24SCSE1011020 Page 11

 Experiment 5.
Aim: Implementation of different types of operators in SQL.
Arithmetic Operators:
Example:
SELECT Marks + 5 AS Updated_Marks FROM Student;

Output:

Comparison Operators:
Example:
SELECT * FROM Student WHERE Marks > 80;
Output:

Logical Operators:
Example:
SELECT * FROM Student
WHERE Marks > 70 AND Department = 'CSE';

Output:

Special Operators:
Example:
SELECT Name, Marks FROM Student WHERE Marks BETWEEN 70 AND 90;
Output:

SELECT Name FROM Student WHERE Name LIKE 'R%';

Output:

Sneha Kumari :24SCSE1011020 Page 12

Set Operators:
Example:
SELECT Name FROM Student_CSE
UNION
SELECT Name FROM Student_IT;

Output:

Sneha Kumari :24SCSE1011020 Page 13

 Experiment 6.
Aim: a. Creating Tables(With and Without Constraints(Key/Domain) b. Creating Table
(a) Creating Tables (With and Without Constraints)
1. Creating a Table Without Constraints
This table has no rules. It accepts duplicate rows, NULL values, and any data content as long as
it matches the data type.
Syntax & Example:
CREATE TABLE SimpleProduct (
ProductID INT,
ProductName VARCHAR(50),
Price DECIMAL(10, 2));

2. Creating a Table With Key & Domain Constraints

Here we add rules to enforce data validity.
 Key Constraints: Ensure uniqueness (e.g., PRIMARY KEY, UNIQUE).

 Domain Constraints: Ensure data follows specific formats or ranges (e.g., NOT NULL,
CHECK, DEFAULT).

Example:
CREATE TABLE Products (
ProductID INT PRIMARY KEY, -- Key: Unique ID, cannot be null
ProductCode VARCHAR(20) UNIQUE, -- Key: Must be unique across all rows
ProductName VARCHAR(50) NOT NULL, -- Domain: Cannot be empty
Price DECIMAL(10, 2) CHECK (Price > 0),-- Domain: Price must be positive
Category VARCHAR(30) DEFAULT 'General' -- Domain: specific default value if none
provided
);
Explanation of Constraints:
 PRIMARY KEY: Uniquely identifies each record.

 UNIQUE: Ensures all values in this column are different.

 NOT NULL: Ensures a value must be provided.

 CHECK: Ensures the value meets a specific condition (Price > 0).

 DEFAULT: Inserts a default value if the user skips this column during insertion.

(b) Creating Tables (With Referential Integrity Constraints)

Referential Integrity is achieved using Foreign Keys.
To implement this, you generally need two tables:
1. Parent Table: The table containing the primary data (referenced).

2. Child Table: The table that references the parent (referencing).

Sneha Kumari :24SCSE1011020 Page 14

Step 1: Create the Parent Table
First, we create the table that holds the core data.
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50) NOT NULL
);
Step 2: Create the Child Table (With Foreign Key)
Now we create a table that links back to Departments.
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
DeptID INT,
-- Defining the Referential Integrity Constraint
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Sneha Kumari :24SCSE1011020 Page 15

 Experiment 7.
Aim: To create tables and perform the following Queries: a. Simple Queries b. Queries with Aggregate
functions (Max/Min/Sum/Avg/Count) c. Queries with Aggregate functions (group by and having clause)
d. Queries involving- Date Functions, String Functions, Math Functions.
To demonstrate these queries, we first need a clear relational schema. Defining two tables: Departments
and Employees.
1. Creating Tables and Inserting Data
First, let's create the tables and populate them with sample data so the queries have something to
work with.

-- Parent Table
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50),
Location VARCHAR(50)
);
-- Child Table
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10, 2),
JoinDate DATE,
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

-- Inserting Sample Data

INSERT INTO Departments VALUES (1, 'IT', 'New York');
INSERT INTO Departments VALUES (2, 'HR', 'London');
INSERT INTO Departments VALUES (3, 'Sales', 'Paris');

INSERT INTO Employees VALUES (101, 'Alice Smith', 70000, '2020-01-15', 1);
INSERT INTO Employees VALUES (102, 'Bob Jones', 50000, '2021-03-10', 2);
INSERT INTO Employees VALUES (103, 'Charlie Brown', 75000, '2019-06-23', 1);
INSERT INTO Employees VALUES (104, 'David White', 45000, '2022-11-05', 3);
INSERT INTO Employees VALUES (105, 'Eve Black', 52000, '2021-08-19', 2);

(a) Simple Queries

These are basic queries using SELECT, FROM, and WHERE clauses to filter rows.
Query 1: List all employees working in Department 1 (IT).

Sneha Kumari :24SCSE1011020 Page 16

SELECT * FROM Employees
WHERE DeptID = 1;
Output:

Query 2: List the name and salary of employees earning more than 50,000.
SELECT Name, Salary
FROM Employees
WHERE Salary > 50000;
Output:

(b) Queries with Aggregate Functions

These queries perform calculations on multiple rows to return a single value.
Query 3: Find the total amount of money the company pays in salaries.
SELECT SUM(Salary) AS Total_Salary_Cost FROM Employees;
Output:

Query 4: Find the minimum, maximum, and average salary in the company.
SELECT MIN(Salary) AS Lowest_Salary,
MAX(Salary) AS Highest_Salary,
AVG(Salary) AS Average_Salary
FROM Employees;
Output:

Query 5: Count how many employees are currently working in the company.
SELECT COUNT(*) AS Total_Employees FROM Employees;
Output:

(c) Queries with Group By and Having

These queries group rows together based on a column and then filter those groups.
Query 6 (GROUP BY): Count the number of employees in each department.
SELECT DeptID, COUNT(*) AS Employee_Count

Sneha Kumari :24SCSE1011020 Page 17

FROM Employees
GROUP BY DeptID;
Output:

Query 7 (HAVING): Find departments that have an average salary greater than 60,000. Note:
WHERE filters rows, HAVING filters groups.
SELECT DeptID, AVG(Salary) AS Avg_Dept_Salary
FROM Employees
GROUP BY DeptID
HAVING AVG(Salary) > 60000;
Output:

(d) Queries involving Built-in Functions

These queries transform data using Date, String, and Math functions.
Query 8 (Date Function): Find the number of years each employee has worked (assuming
current date is 2023-12-31).
SELECT Name,
DATEDIFF('2023-12-31', JoinDate) / 365 AS Years_Worked
FROM Employees;
Output:

Query 9 (String Function): Display employee names in uppercase and the first 3 letters of their
name.
SELECT UPPER(Name) AS Upper_Name,
SUBSTR(Name, 1, 3) AS Short_Name
FROM Employees;
Output:

Sneha Kumari :24SCSE1011020 Page 18

Query 10 (Math Function): Calculate a 10% bonus for each employee and round it to the
nearest whole number.
SELECT Name,
Salary,
ROUND(Salary * 0.10, 0) AS Bonus_Amount
FROM Employees;
Output:

Sneha Kumari :24SCSE1011020 Page 19

 Experiment 8.
Aim: To create tables and perform the following Queries: a. Inner Join b. Outer Join c. Natural Join.
1. Setup: Creating Tables and Inserting Data
We will create two tables: Students and Courses.

Scenario: Some students are enrolled in courses, some are not. Some courses have students, some are
empty.
-- Table 1: Courses
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50)
);

-- Table 2: Students
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
CourseID INT, -- Foreign Key
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- Insert Data
INSERT INTO Courses VALUES (101, 'Math');
INSERT INTO Courses VALUES (102, 'Science');
INSERT INTO Courses VALUES (103, 'History'); -- Course with no students

INSERT INTO Students VALUES (1, 'Alice', 101); -- Matches Math

INSERT INTO Students VALUES (2, 'Bob', 102); -- Matches Science
INSERT INTO Students VALUES (3, 'Charlie', NULL);-- Student with no course

(a) Inner Join

Query: Find students who are currently enrolled in a course and display the course name.
SELECT [Link], [Link]
FROM Students
INNER JOIN Courses ON [Link] = [Link];
Output:

(b) Outer Join

There are three types:

Sneha Kumari :24SCSE1011020 Page 20

1. Left Outer Join
Returns all records from the left table (Students), and the matched records from the right table
(Courses).
SELECT [Link], [Link]
FROM Students
LEFT JOIN Courses ON [Link] = [Link];
Output:

2. Right Outer Join

Returns all records from the right table (Courses), and the matched records from the left table
(Students).
SELECT [Link], [Link]
FROM Students
RIGHT JOIN Courses ON [Link] = [Link];
Output:

3. Full Outer Join

Returns all records when there is a match in either left or right table. (Note: MySQL does not
support FULL JOIN directly; you usually simulate it using UNION).
SELECT [Link], [Link]
FROM Students
LEFT JOIN Courses
ON [Link] = [Link]

UNION

SELECT [Link], [Link]

FROM Students
RIGHT JOIN Courses
ON [Link] = [Link];
Output:

Sneha Kumari :24SCSE1011020 Page 21

(c) Natural Join
A Natural Join creates an implicit join based on all columns in the two tables that have the
same name and data type. You do not need to specify the ON clause.
 In our tables, both have a column named CourseID. The database automatically joins
them using this column.
SELECT *
FROM Students
NATURAL JOIN Courses;
Output:

Sneha Kumari :24SCSE1011020 Page 22

 Experiment 9.
Aim: To perform the following: a. Creating Views b. Dropping views c. Selecting from a view
[Link] Tables
To demonstrate views, assume we have the following Employees table:
EmpID Name Department Salary
1 Alice IT 6000
2 Bob HR 4500
3 Charlie IT 7000

(a) Creating Views

You use the CREATE VIEW statement to define the virtual table.
Example 1: A Simple View (Data Protection)
Suppose you want to give a manager access to employee names and departments, but hide their
salaries. You can create a view for this.
CREATE VIEW EmployeePublicDetails AS
SELECT EmpID, Name, Department
FROM Employees;
Example 2: A Complex View (Simplifying Logic)
Suppose you frequently need to see only employees in the 'IT' department with high salaries.
Instead of typing the WHERE clause every time, you create a view.
CREATE VIEW HighPaidITStaff AS
SELECT EmpID, Name, Salary
FROM Employees
WHERE Department = 'IT' AND Salary > 5000;

(b) Dropping Views

If a view is no longer needed, you can remove its definition from the database using DROP
VIEW.
Note: Dropping a view does not delete the data in the underlying tables. It only deletes the
virtual table definition.
Syntax:
DROP VIEW view_name;
Example:
To delete the HighPaidITStaff view :
DROP VIEW HighPaidITStaff;

(c) Selecting from a View

Once a view is created, you query it exactly like a normal table. The database runs the saved
query in the background and returns the fresh results.
Querying the Simple View
SELECT * FROM EmployeePublicDetails;

Sneha Kumari :24SCSE1011020 Page 23

Querying the Complex View with Filters
You can even add more filters on top of the view.
SELECT * FROM HighPaidITStaff
WHERE Salary > 6500;
Result: This effectively runs the saved query plus your new filter, returning only "Charlie"
(Salary 7000).

Sneha Kumari :24SCSE1011020 Page 24

 Experiment 10.
Aim: To Implement Groupby & Having Clause, OrderbyClause, Indexing.
These commands are essential for organizing, filtering, and optimizing how you retrieve data
from a database.
To demonstrate these, let’s assume a Sales table:
SaleID Product Category Amount
1 Laptop Electronics 1200
2 Mouse Electronics 20
3 Chair Furniture 150
4 Desk Furniture 300
5 Monitor Electronics 200
6 Sofa Furniture 800

1. Group By & Having Clause

The GROUP BY statement groups rows that have the same values into summary rows, like "find
the total sales for each category."
The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions (like SUM or COUNT).
 GROUP BY: Aggregates data by specific columns.

 HAVING: Filters the groups created by GROUP BY.

Scenario: We want to find the total sales amount for each category, but only for categories that
have sold more than $1000 in total.
Implementation:
SELECT Category, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Category
HAVING SUM(Amount) > 1000;
Output:

Step-by-Step Execution:
1. Group: The database groups rows by 'Electronics' and 'Furniture'.
2. Aggregate: It sums the amounts:

o Electronics: 1200 + 20 + 200 = 1420

o Furniture: 150 + 300 + 800 = 1250

Sneha Kumari :24SCSE1011020 Page 25

 3. Filter (Having): Checks if Total > 1000. Both are > 1000, so both are returned. (If
Furniture total was 900, it would be hidden).

2. Order By Clause
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
 ASC: Ascending order (Default).

 DESC: Descending order.

Scenario: List all sales sorted by the most expensive items first.
Implementation:
SELECT * FROM Sales
ORDER BY Amount DESC;
Output:

Multi-level Sorting:
You can also sort by multiple columns. For example, sort by Category (A-Z), and then by
Amount (High-Low) within that category:
SELECT * FROM Sales
ORDER BY Category ASC, Amount DESC;

Output:

3. Indexing
An Index is a data structure (usually a B-Tree) that improves the speed of data retrieval
operations on a database table at the cost of additional writes and storage space.
(a) Creating an Index
Scenario: You frequently search for sales by Product name. To make this search faster:
Syntax:
CREATE INDEX idx_product
ON Sales (Product);
(b) Creating a Unique Index

Sneha Kumari :24SCSE1011020 Page 26

This forces all values in the indexed column to be unique (automatically created for Primary
Keys).
CREATE UNIQUE INDEX idx_sale_id
ON Sales (SaleID);
(c) Dropping an Index
If an index is no longer needed (e.g., it slows down INSERT operations too much), you can
remove it.
DROP INDEX idx_product ON Sales;

Sneha Kumari :24SCSE1011020 Page 27

 Experiment 11.
Aim: To write a cursor to select the five highest-paid employees from the table.
Given the table EMPLOYEE(EmpNo,Name,Salary,Designation,DeptID)

A Cursor in PL/SQL is a database object used to retrieve data from a result set one row at a time. It is
particularly useful when you need to process individual rows sequentially.
To find the top 5 highest-paid employees, we must sort the employees by salary in descending order and
then fetch only the first 5 rows using the cursor.
PL/SQL Block Implementation
DECLARE
-- 1. Declare variables to hold the fetched data
v_EmpNo [Link]%TYPE;
v_Name [Link]%TYPE;
v_Salary [Link]%TYPE;
v_Designation [Link]%TYPE;

-- 2. Declare the Cursor

-- We assume we want the highest salaries, so we ORDER BY Salary DESC
CURSOR emp_cursor IS
SELECT EmpNo, Name, Salary, Designation
FROM EMPLOYEE
ORDER BY Salary DESC;

BEGIN
-- 3. Open the Cursor
OPEN emp_cursor;

DBMS_OUTPUT.PUT_LINE('--- Top 5 Highest Paid Employees ---');

-- 4. Loop to fetch the first 5 records

FOR i IN 1..5 LOOP
-- Fetch the current row into variables
FETCH emp_cursor INTO v_EmpNo, v_Name, v_Salary, v_Designation;

-- Exit if there are fewer than 5 employees in total

EXIT WHEN emp_cursor%NOTFOUND;

-- Display the record

DBMS_OUTPUT.PUT_LINE('Rank ' || i || ': ' || v_Name ||
' (ID: ' || v_EmpNo || ') - Salary: ' || v_Salary);
END LOOP;

Sneha Kumari :24SCSE1011020 Page 28

 -- 5. Close the Cursor
CLOSE emp_cursor;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
IF emp_cursor%ISOPEN THEN
CLOSE emp_cursor;
END IF;
END;
/
Result Set 1:
Message
--- Top 5 Highest Paid Employees ---

Result Set 2:

Employee_Details
Rank 1: Anika (ID: 101) - Salary: 90000
Rank 2: Rahul (ID: 102) - Salary: 85000
Rank 3: Neha (ID: 103) - Salary: 80000
Rank 4: Aman (ID: 104) - Salary: 75000
Rank 5: Pooja (ID: 105) - Salary: 70000
Explanation of the Steps
1. Declare Section:
o We define variables (v_EmpNo, etc.) matching the column types of the table
using %TYPE.

o The Cursor Query: We define emp_cursor with ORDER BY Salary DESC. This
ensures that when we start fetching, the first record we grab is the highest salary,
the second is the next highest, and so on.

2. Open Cursor:
o OPEN emp_cursor executes the query and prepares the result set in memory.

3. Loop (1..5):
o We specifically loop 5 times because the requirement is for the "five highest-
paid."
o FETCH: Retrieves the current row pointer's data into our variables and moves
the pointer down.

Sneha Kumari :24SCSE1011020 Page 29

 o EXIT WHEN: A safety check. If the table only has 3 employees, the cursor will
be empty on the 4th loop. %NOTFOUND returns TRUE if the fetch fails,
allowing us to break the loop gracefully.

4. Close Cursor:
o Always close the cursor to free up database memory resources.

Sneha Kumari :24SCSE1011020 Page 30

 Experiment 12.
Aim: To perform the following: a. Begin Transactions b. End Transaction

Transactions allow you to group multiple SQL commands into a single unit of work. This ensures data
integrity: either all commands succeed, or none of them do (the "All or Nothing" principle).
Implementation of transaction controls using a classic Bank Transfer scenario involving two
related updates.
1. Setup: The Related Table
Let's assume we have an Accounts table.
SQL
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountName VARCHAR(50),
Balance DECIMAL(10, 2)
);

-- Initial State
INSERT INTO Accounts VALUES (1, 'Alice', 1000.00);
INSERT INTO Accounts VALUES (2, 'Bob', 500.00);

(a) Begin Transaction

This command marks the starting point of the transaction. Any DML operations (Insert, Update,
Delete) performed after this point are temporary and not visible to other users until confirmed.
Syntax:
START TRANSACTION; or BEGIN;
(b) End Transaction
There are two ways to "end" a transaction:
1. COMMIT: Saves all changes permanently.

2. ROLLBACK: Cancels all changes and returns the database to the state it was in before
the transaction began.

Scenario: Transfer $100 from Alice to Bob

We need to subtract money from Alice and add it to Bob. Both must happen, or neither should
happen.
Case 1: The Successful Transaction (COMMIT)
1. Start the Transaction
BEGIN TRANSACTION;

2. Deduct $100 from Alice

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

Sneha Kumari :24SCSE1011020 Page 31

3. Add $100 to Bob
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

4. End the Transaction (Save Changes)

COMMIT;
Result: Alice has $900, Bob has $600. The data is permanently saved.

Case 2: The Failed Transaction (ROLLBACK)

Suppose we deduct money from Alice, but then realize Bob's account account is invalid or the
system crashes before we can credit him. We must undo the first step.
1. Start the Transaction
BEGIN TRANSACTION;
2. Deduct $100 from Alice
UPDATE Accounts
SET Balance = Balance – 100 WHERE AccountID = 1;
3. Simulate an error (e.g., trying to credit a non-existent account)
-- Let's say we check for Account 99 and find it doesn't exist.
4. End the Transaction (Undo Changes)
ROLLBACK;
Result: The database cancels the deduction from Alice. Her balance returns to $1000. It is as if
the transaction never happened.

Sneha Kumari :24SCSE1011020 Page 32

 Experiment 13.
Aim: To perform the following: a. Create roles b. Assign Privileges c. Revoke Privileges

DCL (Data Control Language) commands manage security and access within the database.
Instead of granting permissions to every single user individually, we often use Roles.
A Role is a container for a set of privileges. You assign privileges to the role, and then assign the
role to users.
The implementation using the Employees and Departments tables defined in previous steps.
(a) Creating Roles
This command creates a named group of privileges.
Syntax:
CREATE ROLE role_name;
Example: Let's create two roles: one for a generic Intern (who should only read data) and one
for a Manager (who can modify data).
Create a role for read-only access
CREATE ROLE InternRole;

Create a role for read-write access

CREATE ROLE ManagerRole;

(b) Assigning Privileges (GRANT)

We use the GRANT command to give specific permissions to the role.
Privilege Types:
 SELECT: Read data.

 INSERT, UPDATE, DELETE: Modify data.

 ALL PRIVILEGES: Full control.

1. Granting Table Permissions to Roles

-- The Intern can only view the Employees table
GRANT SELECT ON Employees TO InternRole;

-- The Manager can view, add, and update Employees and Departments
GRANT SELECT, INSERT, UPDATE ON Employees TO ManagerRole;
GRANT SELECT, INSERT, UPDATE ON Departments TO ManagerRole;
2. Assigning Roles to Users
Once the roles have permissions, we assign the roles to actual database users (e.g., user_alice or
user_bob).
-- Assign the Manager role to Alice
GRANT ManagerRole TO user_alice;

-- Assign the Intern role to Bob

GRANT InternRole TO user_bob;

Sneha Kumari :24SCSE1011020 Page 33

Result: Alice now inherits all privileges of the ManagerRole (Select, Insert, Update), and Bob
inherits the InternRole privileges (Select only).

(c) Revoking Privileges (REVOKE)

We use the REVOKE command to take back permissions. You can revoke specific privileges
from a role, or revoke a role from a user.
1. Revoking a Specific Privilege
Scenario: We decided Managers should no longer be allowed to delete records (if they had that
permission) or update them. Let's remove the UPDATE permission from the Manager role.
REVOKE UPDATE ON Employees FROM ManagerRole;
Effect: Alice (and any other user with ManagerRole) can instantly no longer update employee
records.
2. Revoking a Role from a User
Scenario: Bob has finished his internship. We want to remove his access entirely.
SQL
REVOKE InternRole FROM user_bob;

Sneha Kumari :24SCSE1011020 Page 34

 Experiment 14.
Aim: Write a Pl/SQL program using a FOR loop to insert ten rows into a database table.

CREATE TABLE student (

id NUMBER,
name VARCHAR2(20)
);

PL/SQL Program
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO student (id, name)
VALUES (i, 'Student' || i);
END LOOP;

COMMIT;
END;
/

Sneha Kumari :24SCSE1011020 Page 35

 Experiment 15.
Aim: Perform the following: Inserting/Updating/Deleting Records in a Table, Saving (Commit) and
Undoing (rollback)
To perform these operations effectively, we combine DML (Data Manipulation Language) with TCL
(Transaction Control Language).
1. Initial Setup
First, let's assume we have a simple empty table.
SQL
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
Price DECIMAL(10, 2)
);

2. Inserting, Updating, and Deleting (The DML Operations)

(a) Inserting Records
This adds new rows to the table.
SQL
INSERT INTO Products VALUES (1, 'Laptop', 1200.00);
INSERT INTO Products VALUES (2, 'Mouse', 25.00);
INSERT INTO Products VALUES (3, 'Keyboard', 45.00);
(b) Updating Records
This modifies existing data. Always use a WHERE clause to avoid updating every row.
SQL
-- Increase the price of the Mouse by $5
UPDATE Products
SET Price = 30.00
WHERE ProductID = 2;
(c) Deleting Records
This removes specific rows.
SQL
-- Remove the Keyboard from the table
DELETE FROM Products
WHERE ProductID = 3;

3. Saving (COMMIT) and Undoing (ROLLBACK)

Transactions control whether these DML changes are permanent or temporary.
Scenario A: Undoing Changes (ROLLBACK)
Use this when you make a mistake or a process fails midway.
SQL
1. Start the Transaction
BEGIN TRANSACTION;

Sneha Kumari :24SCSE1011020 Page 36

2. Perform an operation (e.g., Accidentally delete the wrong product)
DELETE FROM Products WHERE ProductID = 1; -- Laptop is gone!

3. Verify the state (If you ran a SELECT here, the Laptop would be missing)

4. Undo the mistake

ROLLBACK;

Result: The transaction is cancelled. The Laptop is back in the table.

Scenario B: Saving Changes (COMMIT)
Use this when you are sure the data is correct.
1. Start the Transaction
BEGIN TRANSACTION;

2. Perform operations
INSERT INTO Products VALUES (4, 'Monitor', 150.00);
UPDATE Products SET Price = 1100.00 WHERE ProductID = 1;
3. Save the changes permanently
COMMIT;

Result: The Monitor is added and the Laptop price is updated.

You can no longer use ROLLBACK to undo this.

Sneha Kumari :24SCSE1011020 Page 37

Sneha Kumari :24SCSE1011020 Page 38