1

LAB REPORT
CSE312: Database Management System Lab

Submitted To
Ms. Tamanna Sultana
Lecturer
Department of CSE, Daffodil International University

Submitted By
Student ID: 222-15-6188
Section: 62_A1
Student Name: Maria Tasnim
 2

Table: CSE312 Course Outcomes (COs) with Mappings

Comp Compl
Lear lex ex
Knowl
PO ning Engin Engin
COs CO Statements edge
s Dom eering eering
Profile
ains Proble Activit
m ies
Demonstrate a comprehensive understanding of K2
C2
fundamental database management concepts, including PO K3 EP1
CO1 A2
the relational data model, normalization techniques, 1 K4 EP4
P2
and SQL basics. K8
Design, implement and optimize relational K2
databases, incorporating advanced SQL queries, C3 K3 EP1
PO
CO2 indexing techniques and query optimization A3 K4 EP2 EA3
3
strategies. P3 K6 EP7
K8
Understand and Analyze security measures,
distributed database architectures and emerging
trends in database management, demonstrating an C4
PO EP4
CO3 understanding of the broader context and A4 K6
5
challenges in the field. P3

Table: Lab Wise Activity List

Lab Class Proposed Activity CO

No.

Lab 1,2 Lab Setup and DDL (Create, Alter, Drop, Truncate) CO1

Lab 3,4,5 DML (Select, Insert, Update, Delete Operation) and CO2
Keys

Lab 6,7 Sub Query, Aggregate Function, Joining. Wildcards CO2

e.t.c

Lab 8,9 Union, Trigger, View, Stored Procedure e.t.c CO2

Lab 10 DCL (Grant, Revoke) and TCL (Commit, Savepoint and CO2
Rollback)

Lab 11 Complete Database Design and Analysis with a few CO3

important Complex Query.
 3

LAB REPORT

01
Topic: DDL & DML
CO Mapping: CO1, CO2

Date of Assignment Distribution: 03 December 2024

Date of Assignment Submission: 07 December 2024
 4

Experiment No: 01 Experiment Name: DML (Select, Insert, Update, Delete

Operation) and Keys and DML (Select, Insert, Update, Delete
Operation) and Keys.

Experiment Details:

Objective:
The tasks in this experiment are designed to demonstrate various SQL operations, such as CRUD
operations (Create, Read, Update, Delete), Alter operations, and Basic SQL Queries to manage
and retrieve data effectively.

Database Schema:
The following tables will be created:

1. Students Table:
○ student_id (INT, Primary Key, Auto Increment): Unique identifier for each student.
○ first_name (VARCHAR(50)): First name of the student.
○ last_name (VARCHAR(50)): Last name of the student.
○ email (VARCHAR(100), Unique): Email address of the student.
○ date_of_birth (DATE): Date of birth of the student.
2. Courses Table:
○ course_id (INT, Primary Key, Auto Increment): Unique identifier for each course.
○ course_name (VARCHAR(100), Unique): Name of the course.
○ course_description (TEXT): Description of the course.
3. Enrollments Table:
○ enrollment_id (INT, Primary Key, Auto Increment): Unique identifier for each
enrollment record.
○ student_id (INT, Foreign Key referencing Students.student_id): References the
student_id from the Students table.
○ course_id (INT, Foreign Key referencing Courses.course_id): References the
course_id from the Courses table.
○ enrollment_date (DATE): The date when the student enrolled in the course.

Task 1: CRUD Operations

1. Create Operations:
○ Insert 5 records in the Students table with appropriate values for each field.
○ Insert 4 records in the Courses table with relevant course details.
2. Read Operations:
○ Retrieve the list of all students, displaying all student details.
 5

○ Retrieve the names of students who have enrolled in a specific course (e.g.,
"Mathematics").
3. Update Operation:
○ Update the email address of a student based on their student_id.
4. Delete Operation:
○ Delete a student from the Students table who has not enrolled in any course.

Task 2: Alter Operations

1. Add a new column phone_number (VARCHAR(15)) to the Students table. This will allow
the system to store students' contact numbers.
2. Change the datatype of the course_description column in the Courses table from TEXT
to VARCHAR(255) to accommodate course descriptions with a smaller and more defined
length.

Task 3: Basic SQL Queries

1. Retrieve students born after January 1, 2000:

○ Query to list all students whose birthdate is after January 1, 2000.
2. Calculate the average number of students enrolled per course:
○ Query to calculate the average number of students enrolled in each course.
3. Get the total number of students:
○ Query to retrieve the total number of records (students) in the Students table.
4. Retrieve the names of students enrolled in both "Mathematics" and "Science"
courses:
○ Use a set operation (e.g., INTERSECT) to get the names of students enrolled in
both courses.
5. Rename columns student_id as ID and first_name as Name when retrieving student data.
6. For each course, display the student names enrolled in that course:
○ Query to list all students enrolled in each course.

Task 4: Keys and Constraints

1. Foreign Key Explanation:

○ The student_id in the Enrollments table is a Foreign Key because it references the
student_id in the Students table, ensuring data integrity by linking the student’s
enrollment to their corresponding record.
2. Enforce Foreign Key Constraint:
○ Demonstrate how the foreign key constraint is enforced when inserting data into
the Enrollments table. For example, attempting to insert an enrollment record with
 6

a student_id that does not exist in the Students table will result in a constraint
violation.
3. Retrieve all enrollments with student names and course names:
○ Query to retrieve a list of all enrollments, including the student names and
corresponding course names.

Step-by-Step Procedure:

Database Schema

CREATE DATABASE Student_Management_System;

use Student_Management_System;

CREATE TABLE Student(

student_id int auto_increment primary key,
first_name VARCHAR (50),
last_name VARCHAR (50),
email VARCHAR(100) UNIQUE,
date_of_birth DATE
);

CREATE TABLE Courses (

course_id int auto_increment primary key,
course_name VARCHAR(100) UNIQUE,
course_description TEXT
);

CREATE TABLE Enrollments (

enrollment_id int auto_increment primary key,
student_id INT,
course_id INT,
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES Student(student_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

INSERT INTO Courses (course_name, course_description) VALUES

('Mathematics', 'Introduction to Mathematics concepts and problem-
solving'),
('DBMS', 'Database Management System'),
('Computer Networks', 'Study of Computer Networking System'),
('Computer Science', 'Basic programming and computer science');

Obtained Output:
 7

Student Table: Desired

Output?

Course Table:

YES

Enrollment Table:

Task 1: CRUD Operations:

01. Create: Insert 5 records in the Student table and 4 records in the Courses table.

INSERT INTO Student (first_name, last_name, email, date_of_birth)

VALUES
('Jannatul', 'Zoti', 'JannatulZoti@example.com', '2001-05-14'),
('Tamanna', 'Sultana', 'TamannaSultana@example.com', '1999-08-21'),
('Sarbajit', 'Paul', 'SarbajitPaul@example.com', '2001-03-10'),
('Maruf', 'Rahman', 'MarufRahman@example.com', '2002-12-30'),
('Rittik', 'Turjy', 'RittikTurjy@example.com', '2002-07-19');

INSERT INTO Courses (course_name, course_description) VALUES

('Mathematics', 'Introduction to Mathematics concepts and problem-
solving'),
('DBMS', 'Database Management System'),
('Computer Networks', 'Study of Computer Networking System'),
('Computer Science', 'Basic programming and computer science');

Obtained Output:

Student Table: Desired

Output?
 8

YES
Courses Table:

02. Read:
o Retrieve the list of all students.
SELECT * FROM Student;

Obtained Output:

Student Table: Desired

Output?

YES

o Retrieve the names of students who have enrolled in a specific course

INSERT INTO Enrollments (student_id, course_id, enrollment_date)

VALUES
(1, 1, '2024-01-10'),
(2, 2, '2024-01-11'),
(3, 3, '2024-01-12'),
(4, 4, '2024-01-13'),
(5, 1, '2024-01-14');
 9

SELECT first_name, last_name

FROM Student
WHERE student_id IN (
SELECT student_id
FROM Enrollments
WHERE course_id = (
SELECT course_id
FROM Courses
WHERE course_name = 'Mathematics'
)

);

Obtained Output:

Desired
Output?

YES

03. Update: Update the email of a student based on the student_id.

UPDATE Student
SET email = 'spamemail@example.com'
WHERE student_id = 3;

Obtained Output:

Desired
Output?

YES

04. Delete: Delete a student who has not enrolled in any course from the Student table.

DELETE FROM Student

WHERE student_id NOT IN (SELECT student_id FROM Enrollments);
 10

Obtained Output:

Desired
Output
?

YES

Task 2: Alter Operations

01. Add a column phone_number (VARCHAR(15)) to the Student table.

ALTER TABLE Student

ADD phone_number VARCHAR(15);

Obtained Output:

Desired
Output?

YES

02. Change the course_description column in Courses from TEXT to VARCHAR(255).

ALTER TABLE Courses

MODIFY course_description VARCHAR(255);

Obtained Output:
Desired
Output?

YES
 11

Task 3: Basic SQL Queries

01. Retrieve the details of all students born after January 1, 2000:

SELECT *
FROM Student
WHERE date_of_birth > '2000-01-01';

Obtained Output:

Desired
Output?

YES

02. Calculate the average number of students enrolled per course

SELECT AVG(student_count) AS average_students_per_course

FROM (
SELECT course_id, COUNT(student_id) AS student_count
FROM Enrollments
GROUP BY course_id
) AS course_enrollments;

Obtained Output:

Desired
Output?

YES

03. Get the total number of students in the students table

SELECT COUNT(*) AS total_students

FROM Student;

Obtained Output:
 12

Desired
Output?

YES

04. Retrieve the names of students enrolled in both "Mathematics" and "Science" courses
using a set operation:

SELECT first_name, last_name

FROM Student
WHERE student_id IN (
SELECT student_id
FROM Enrollments
WHERE course_id = (SELECT course_id FROM Courses WHERE
course_name = 'Mathematics')
)
AND student_id IN (
SELECT student_id
FROM Enrollments
WHERE course_id = (SELECT course_id FROM Courses WHERE
course_name = 'Science')
);

Obtained Output:

Desired
Output
?

YES

05. Rename student_id as ID and first_name as Name when retrieving student data:

SELECT student_id AS ID, first_name AS Name, last_name, email,

date_of_birth, phone_number
FROM Student;

Obtained Output:
 13

Desired
Output
?

YES

06. For each course, display the student names enrolled in that course:

SELECT c.course_name, s.first_name, s.last_name

FROM Courses c
JOIN Enrollments e ON c.course_id = e.course_id
JOIN Student s ON e.student_id = s.student_id
ORDER BY c.course_name;

Obtained Output:

Desired
Output
?

YES

Task 4: Keys and Constraints

01. Explain why the student_id in the Enrollments table is a Foreign Key:
The student_id in the Enrollments table is a Foreign Key because it references the
student_id in the Student table. This relationship ensures referential integrity, meaning that
each enrollment must correspond to an existing student. It prevents orphan records in the
Enrollments table, ensuring that every enrollment entry is linked to a valid student.

02. Demonstrate how you would enforce a foreign key constraint when inserting data
into the Enrollments table:
To enforce a foreign key constraint when inserting data into the Enrollments table, you can
use the following SQL command:
SELECT c.course_name, s.first_name, INSERT INTO Enrollments
(enrollment_id, student_id, course_id)
VALUES (1, 123, 456);
 14

Before executing this command, the database checks that student_id 123 exists in the
Student table. If it does not, the insert operation will fail, maintaining the integrity of the
relationship.

03. Retrieve all enrollments along with student names and course names:
We can retrieve all enrollments along with student names and course names using the
following SQL query:

SELECT e.enrollment_id, s.student_name, c.course_name

FROM Enrollments e
JOIN Students s ON e.student_id = s.student_id
JOIN Courses c ON e.course_id = c.course_id;
This query joins the Enrollments table with the Students and Courses tables to provide a
comprehensive view of enrollments, student names, and course names.
Obtained Output:

Desired
Output
?

YES

Observation:
In this lab, we successfully designed and managed a relational database for a Student Management
System. The CRUD operations—Create, Read, Update, and Delete—enabled smooth data
manipulation, allowing us to easily insert, retrieve, modify, and remove records. We demonstrated
the adaptability of the schema by adding a phone_number column and modifying column types to
meet evolving requirements. SQL queries were utilized to gain valuable insights from the data,
such as filtering students based on birthdate, calculating averages, and performing complex joints
to retrieve related information. The use of Foreign Keys ensured data integrity by establishing
referential relationships between tables, while constraints helped maintain valid data entry.
Overall, this exercise emphasized the importance of structured database design and the efficient
use of queries for effective database management.
 15

LAB REPORT

02
Topic: Advanced SQL Techniques for Efficient Data Retrieval, Aggregation,
and Conditional Manipulation.
CO Mapping: CO2

Date of Assignment Distribution: 03 December 2024

Date of Assignment Submission: 07 December 2024
 16

Experiment No: 02 Experiment Name: Nested query and Limit keyword.

Experiment Details:
Exploring Nested Queries and the LIMIT Keyword in SQL for Conditional Data Extraction and
Ranking

Schema
Faculty (Id, Name, Designation, Salary)
Student_b_i(Id, Name, Email, Phone)
Course_b_i(CID, CName, Credit)
Employee (ID, Name, Age, Salary)
Enrollment (SID, CID)
Takes (FID, CID)

1. Find out the Faculty who gets the second highest salary.
2. Find faculty/faculty members who take DBMS courses.
3. Find out those students who attend course/s by MMI.
4. Find out the teacher’s basic info on whom Rahim attends class/es.

Step-by-Step Procedure:

Database Schema

CREATE TABLE Faculty(

ID int primary key,
Name VARCHAR(50),
Designation VARCHAR(50),
Salary decimal(10,2)
);

CREATE TABLE Student_b_i(

ID int primary key,
Name VARCHAR(50),
Email VARCHAR(50),
Phone VARCHAR(50)
);

CREATE TABLE Course_b_i(

CID int primary key,
CName VARCHAR(50),
Credit VARCHAR(50)
);
 17

CREATE TABLE Employee (

ID int primary key,
Name VARCHAR(50),
Age int,
Salary DECIMAL(10, 2)
);

CREATE TABLE Enrollment(

SID int,
CID int,
primary key (SID,CID),
foreign key (SID) references Student_b_i(ID),
foreign key (CID) references Course_b_i(CID)
);

CREATE TABLE Takes(

FID int,
CID int,
primary key (FID,CID),
foreign key (FID) references Faculty(ID),
foreign key (CID) references Course_b_i(CID)
);

INSERT INTO Faculty (ID, Name, Designation, Salary)

VALUES
(1, 'Most. Jannatul Firdousi Zoti', 'Lecturer', 700000),
(2, 'Sarbajit Paul Bappy', 'Professor', 920000),
(3, 'Rittik Chandra Das Turjy', 'Assistant Professor', 910000),
(4, 'Maruf Rahman', 'Lab Assistant', 650000);

INSERT INTO Student_b_i (ID, Name, Email, Phone)

VALUES
(1, 'Rahim', 'rahim@example.com', '01734067890'),
(2, 'Boltu', 'sarah@example.com', '01987654321'),
(3, 'Ali', 'ali@example.com', '01512233445'),
(4, 'Rina', 'rina@example.com', '01556677889');

INSERT INTO Course_b_i (CID, CName, Credit)

VALUES
(101, 'DBMS', 3),
(102, 'Data Structures', 4),
(103, 'Algorithms', 4),
(104, 'Computer Networks', 3);

INSERT INTO Employee (ID, Name, Age, Salary)

VALUES
(1, 'Arid', 30, 75000),
(2, 'Bob', 40, 80000),
(3, 'Fuad', 35, 70000),
(4, 'Fahad', 28, 65000);
 18

INSERT INTO Enrollment (SID, CID)

VALUES
(1, 101),
(1, 102),
(2, 101),
(2, 103),
(3, 102),
(4, 104),
(3, 103),
(4, 101);

INSERT INTO Takes (FID, CID)

VALUES
(1, 101),
(2, 102),
(3, 103),
(4, 101);

● Find out the Faculty who gets the second highest salary.

SELECT Name, Salary

FROM Faculty
WHERE Salary = (
SELECT MAX(Salary)
FROM Faculty
WHERE Salary < (SELECT MAX(Salary) FROM Faculty)
);

Obtained Output:
Desired
Output?

YES

● Find those faculty/faculties who take DBMS course

SELECT Name
FROM Faculty
WHERE ID IN (
SELECT FID
FROM Takes
JOIN Course_b_i ON Takes.CID = Course_b_i.CID
 19

WHERE CName = 'DBMS'

);

Obtained Output:
Desired
Output?

YES

● Find out those students who attend course/s by MMI

SELECT S.Name
FROM Faculty F
JOIN Takes T ON F.ID = T.FID
JOIN Course_b_i C ON T.CID = C.CID
JOIN Enrollment E ON C.CID = E.CID
JOIN Student_b_i S ON E.SID = S.Id
WHERE F.Name = 'MMI';

Obtained Output:
Desired
Output?

YES

● Find out the teacher’s basic info whom Rahim attends class/es.

SELECT DISTINCT F.ID, F.Name, F.Designation, F.Salary

FROM Faculty F
JOIN Takes T ON F.ID = T.FID
JOIN Course_b_i C ON T.CID = C.CID
JOIN Enrollment E ON C.CID = E.CID
JOIN Student_b_i S ON E.SID = S.Id
WHERE S.Name = 'Rahim';
 20

Obtained Output:

Desired
Output?

YES

Observation:
In this experiment, we executed various SQL queries on a university database to retrieve specific
data. The tasks included identifying the faculty member with the second-highest salary using a
subquery, locating faculty members teaching the "DBMS" course through a join between the
Faculty, Takes, and Course_b_i tables, and fetching students who are enrolled in courses taught
by "MMI". Additionally, we retrieved information about faculty members teaching courses
attended by the student "Rahim". These queries demonstrated the effective use of subqueries, joins,
and filtering across interconnected tables to efficiently extract and manipulate data within a
relational database.
 21

Experiment No: 02 Experiment Name: Distinct, Group By, Having, And, Or, not.

Experiment Details:
To examine the application of nested queries and the LIMIT keyword in SQL for retrieving targeted data
based on specific conditions and for ranking purposes.

Schema
Faculty (Id, Name, Designation, Salary)
Student_b_i(Id, Name, Email, Phone)
Course_b_i(CID, CName, Credit)
Employee (ID, Name, Age, Salary)
Enrollment (SID, CID)
Takes (FID, CID)

Tasks to Perform
1. Find out the average salary of Senior Lecturers.
2. Show the details of the highest paid Senior Lecturer.
3. Find out the courses taught by the teacher who gets a salary ranging from 500,000 to
1,000,000.
4. Find out the faculty information having salary 500,000 and designation "SL".
5. Find out those students who took courses DBMS or CN.
6. Find out the students who did not enroll in any course having 3 credits.

Step-by-Step Procedure:
Database Schema

CREATE TABLE Faculty (

Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR (100),
Designation VARCHAR (50),
Salary DECIMAL (10, 2)
);

CREATE TABLE Student_b_i (

Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR (100),
Email VARCHAR (100),
Phone VARCHAR (15)
);

CREATE TABLE Course_b_i (

 22

CID INT PRIMARY KEY AUTO_INCREMENT,

CName VARCHAR (100),
Credit INT
);

CREATE TABLE Employee (

ID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR (100),
Age INT,
Salary DECIMAL (10, 2)
);

CREATE TABLE Enrollment (

SID INT,
CID INT,
FOREIGN KEY (SID) REFERENCES Student_b_i(Id),
FOREIGN KEY (CID) REFERENCES Course_b_i(CID),
PRIMARY KEY (SID, CID)
);

CREATE TABLE Takes (

FID INT,
CID INT,
FOREIGN KEY (FID) REFERENCES Faculty (Id),
FOREIGN KEY (CID) REFERENCES Course_b_i(CID),
PRIMARY KEY (FID, CID)
);

INSERT INTO Faculty (Name, Designation, Salary)

VALUES
('Most. Jannatul Firdousi Zoti', 'Senior Lecturer', 800000),
('Sarbajit Paul Bappy', 'Senior Lecturer', 950000),
('Maruf Rahman', 'Lecturer', 450000),
('Rittik Chandra Das Turjy', 'Senior Lecturer', 650000);

INSERT INTO Student_b_i (Name, Email, Phone)

VALUES
('Rahim', 'rahim@example.com', '01725678940'),
('Sumi', 'sumi@example.com', '01989854321'),
('John', 'john@example.com', '01712334455');

INSERT INTO Course_b_i (CName, Credit)

VALUES
('DBMS', 3),
('CN', 3),
('Math', 2),
('Programming', 4);

INSERT INTO Enrollment (SID, CID)

VALUES
(1, 1),
 23

(1, 2),
(2, 3),
(3, 1);

INSERT INTO Takes (FID, CID)

VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 1);

1. Find out the average salary of Senior Lecturers.

SELECT AVG(Salary) AS AverageSalaryOfSeniorLecturers

FROM Faculty
WHERE Designation = 'Senior Lecturer;

Obtained Output:
Desired
Output?

YES

2. Show the details of the highest paid Senior Lecturer.

SELECT *
FROM Faculty
WHERE Designation = 'Senior Lecturer'
ORDER BY Salary DESC
LIMIT 1;

Obtained Output:
Desired
Output?

YES

3. Find out the courses taught by the teacher who gets a salary ranging from 500,000 to
1,000,000.
 24

SELECT c.CName
FROM Takes t
JOIN Faculty f ON t.FID = f.Id
JOIN Course_b_i c ON t.CID = c.CID
WHERE f.Salary BETWEEN 500000 AND 1000000;

Obtained Output:
Desired
Output?

YES

4. Find out the faculty information having salary 500,000 and designation "SL".

SELECT *
FROM Faculty
WHERE Salary = 500000 AND Designation = 'Senior Lecturer';

Obtained Output:
Desired
Output?

YES

5. Find out those students who took courses DBMS or CN.

SELECT DISTINCT s.Name

FROM Student_b_i s
JOIN Enrollment e ON s.Id = e.SID
JOIN Course_b_i c ON e.CID = c.CID
WHERE c.CName IN ('DBMS', 'CN');
 25

Obtained Output:

Desired
Output?

YES

6. Find out the students who did not enroll in any course having 3 credits.

SELECT s.Name
FROM Student_b_i s
WHERE NOT EXISTS (
SELECT 1
FROM Enrollment e
JOIN Course_b_i c ON e.CID = c.CID
WHERE e.SID = s.Id AND c.Credit = 3
);

Desired
Output?

YES

Observation:
In this experiment, we employed a range of SQL queries to accomplish tasks such as calculating
average salaries, filtering faculty by specific salary ranges, and retrieving data from multiple tables
using joins. These queries made use of subqueries, aggregation functions, and various filtering
techniques to extract detailed insights. Additionally, the tasks involved joining related tables like
Faculty, Student_b_i, Course_b_i, and Enrollment to retrieve specific records, demonstrating how
relational databases allow for complex data retrieval based on certain conditions. This exercise
reinforced the importance of mastering SQL for efficiently managing and querying data within
relational databases.
 26

LAB REPORT

03
Topic: SQL Join Operation
CO Mapping: CO2

Date of Assignment Distribution: 3 December 2024

Date of Assignment Submission: 10 December 2024
 27

Experiment No: 03 Experiment Name: Exploring SQL Joins for Effective Student
and Course Data Retrieval

Experiment Details:
To investigate and apply various SQL join techniques (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL
JOIN) for retrieving and managing data related to student enrollments in CSE courses, with an emphasis
on the relationships between students and courses.

Schema:
• Students (Student_ID, Name, Age, Class)
• Courses (Course_ID, Course_Name, Credits)
• Enrollments (Enrollment_ID, Student_ID, Course_ID, ENrollment_Date)

1. Write an SQL query to display the following details:

• Student_ID, Name, Course_Name, and Enrollment_Date for all students who are enrolled
in any course.
Step:
o Use an INNER JOIN to fetch data from the Students, Courses, and Enrollments tables.
2. Write an SQL query to display the following details:
• Student_ID, Name, Course_Name, and Enrollment_Date for all students, including those
who are not enrolled in any course.
Step:
o Use LEFT JOIN to combine data from the Students and Enrollments tables.
3. Write an SQL query to display the following details:
• Course_ID, Course_Name, Student_ID, Name, and Enrollment_Date for all courses,
including those that have no students enrolled.
Step:
o Use a RIGHT JOIN to combine data from the Courses and Enrollments tables.
4. Write an SQL query to display the following details:
• Student_ID, Name, Course_Name, and Enrollment_Date for all students and courses,
including unmatched entries from both sides.
Step:
o Use a FULL JOIN to combine data from the Students and Courses tables via the
Enrollments table.
 28

Step-by-Step Procedure:
Database Schema

CREATE TABLE Students (

Student_ID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100),
Age INT,
Class VARCHAR(50)
);

CREATE TABLE Courses (

Course_ID INT PRIMARY KEY AUTO_INCREMENT,
Course_Name VARCHAR(100),
Credits INT
);

CREATE TABLE Enrollments (

Enrollment_ID INT PRIMARY KEY AUTO_INCREMENT,
Student_ID INT,
Course_ID INT,
Enrollment_Date DATE,
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID),
FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
);

INSERT INTO Students (Name, Age, Class)

VALUES
('Most. Jannatul Firdousi Zoti', 20, 'CSE101'),
('Sarbajit Paul Bappy', 22, 'CSE102'),
('Rittik Chandra Das Turjy', 21, 'CSE103'),
('Maruf Rahman', 23, 'CSE104');

INSERT INTO Courses (Course_Name, Credits)

VALUES
('Database Systems', 3),
('Computer Networks', 4),
('Data Structures', 3),
('Operating Systems', 4);

INSERT INTO Enrollments (Student_ID, Course_ID, Enrollment_Date)

VALUES
(1, 1, '2023-09-01'),
(1, 2, '2023-09-01'),
(2, 1, '2023-09-02'),
(3, 3, '2023-09-03');
 29

1. Student_ID, Name, Course_Name, and Enrollment_Date for all students who are enrolled in
any course. (INNER JOIN)

SELECT s.Student_ID, s.Name, c.Course_Name, e.Enrollment_Date

FROM Students s
INNER JOIN Enrollments e ON s.Student_ID = e.Student_ID
INNER JOIN Courses c ON e.Course_ID = c.Course_ID;

Obtained Output:
Desired
Output?

YES

2. Student_ID, Name, Course_Name, and Enrollment_Date for all students, including those who
are not enrolled in any course. (LEFT JOIN)

SELECT s.Student_ID, s.Name, c.Course_Name, e.Enrollment_Date

FROM Students s
LEFT JOIN Enrollments e ON s.Student_ID = e.Student_ID
LEFT JOIN Courses c ON e.Course_ID = c.Course_ID;

Obtained Output:

Desired
Output?

YES

4. Course_ID, Course_Name, Student_ID, Name, and Enrollment_Date for all courses,

including those that have no students enrolled. (Right Join)

SELECT c.Course_ID, c.Course_Name, s.Student_ID, s.Name,

e.Enrollment_Date
 30

FROM Courses c
RIGHT JOIN Enrollments e ON c.Course_ID = e.Course_ID
RIGHT JOIN Students s ON e.Student_ID = s.Student_ID;

Obtained Output:

Desired
Output?

YES

4. Student_ID, Name, Course_Name, and Enrollment_Date for all students and courses, including
unmatched entries from both sides.

SELECT s.Student_ID, s.Name, c.Course_Name, e.Enrollment_Date

FROM Students s
LEFT JOIN Enrollments e ON s.Student_ID = e.Student_ID
LEFT JOIN Courses c ON e.Course_ID = c.Course_ID

UNION

SELECT s.Student_ID, s.Name, c.Course_Name, e.Enrollment_Date

FROM Students s
RIGHT JOIN Enrollments e ON s.Student_ID = e.Student_ID
RIGHT JOIN Courses c ON e.Course_ID = c.Course_ID;

Obtained Output:

Desired
Output?

YES
 31

Observation:
In this experiment, we examined various SQL join techniques (INNER JOIN, LEFT JOIN, RIGHT
JOIN, and FULL JOIN) to manage and retrieve student-course enrollment information. The
INNER JOIN allowed us to fetch data only for students who were enrolled in courses, while the
LEFT JOIN ensured that all students were included, regardless of their enrollment status. The
RIGHT JOIN highlighted all courses, even those without any enrolled students, and the FULL
JOIN merged both students and courses, presenting all records, including those without matches
from either side. These join operations are crucial for effectively working with related data, each
serving different purposes depending on the need to include or exclude unmatched entries.
 32

LAB REPORT

04
Topic: COMMIT/ROLLBACK in Database Transactions
CO Mapping: CO2

Date of Assignment Distribution: 3 December 2024

Date of Assignment Submission: 7 December 2024
 33

Experiment No: 04 Experiment Name: COMMIT/ROLLBACK in Database

Transactions.

Experiment Details:
The goal of this experiment is to investigate and apply the COMMIT and ROLLBACK operations
within SQL transactions.

1. Create a table to work with transactions

o CREATE TABLE TransactionsDemo ( ID INT PRIMARY KEY, Name VARCHAR(50),
Balance DECIMAL(10, 2) );
2. Insert some initial data
o INSERT INTO TransactionsDemo (ID, Name, Balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 1500.00);
3. Begin a transaction:
o Syntax: START TRANSACTION;
4. Perform an update operation:
o UPDATE TransactionsDemo SET Balance = Balance - 200 WHERE Name = 'Alice';
5. Check the changes:
o SELECT * FROM TransactionsDemo;
6. Save the changes using COMMIT:
o Syntax: COMMIT;
7. Verify the committed changes:
o SELECT * FROM TransactionsDemo;
8. Begin another transaction:
o Syntax: START TRANSACTION;
9. Perform an update operation:
o UPDATE TransactionsDemo SET Balance = Balance + 100 WHERE Name = 'Bob';
10. Check the changes before rollback:
o SELECT * FROM TransactionsDemo;
11. Undo the changes using ROLLBACK:
o Syntax: ROLLBACK;
12. Verify that the changes were not applied:
o SELECT * FROM TransactionsDemo;
13. Start a transaction and create a savepoint:
o Syntax: START TRANSACTION;
 34

▪ INSERT INTO TransactionsDemo (ID, Name, Balance) VALUES (3, 'Charlie',

1200.00);
▪ SAVEPOINT Save1;
14. Perform another operation:
o UPDATE TransactionsDemo SET Balance = Balance - 300 WHERE Name = 'Charlie';
15. Roll back to the savepoint:
o Syntax: ROLLBACK TO Save1;

Step-by-Step Procedure:

Database Schema

1. Create a table to work with transactions

CREATE TABLE TransactionsDemo (

ID INT PRIMARY KEY,
Name VARCHAR(50),
Balance DECIMAL(10, 2)
);

2. Insert some initial data:

INSERT INTO TransactionsDemo (ID, Name, Balance)

VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 1500.00);

Obtained Output:
Desired
Output?

YES

3. Begin a transaction:
START TRANSACTION;
 35

4. Perform an update operation:

UPDATE TransactionsDemo SET Balance = Balance - 200 WHERE Name =

'Alice';

5. Check the changes:

SELECT * FROM TransactionsDemo;

Obtained Output:
Desired
Output?

YES

6. Save the changes using COMMIT:

COMMIT;

7. Verify the committed changes:

SELECT * FROM TransactionsDemo;

Obtained Output:
Desired
Output?

YES

8. Begin another transaction:

START TRANSACTION;

9. Perform an update operation:

UPDATE TransactionsDemo SET Balance = Balance + 100 WHERE Name =

'Bob';
 36

10. Check the changes before rollback:

SELECT * FROM TransactionsDemo;

Obtained Output:
Desired
Output?

YES

11. Undo the changes using ROLLBACK:

ROLLBACK;

12. Verify that the changes were not applied:

SELECT * FROM TransactionsDemo;

Obtained Output:
Desired
Output?

YES

13. Start a transaction and create a savepoint:

START TRANSACTION;

14. Perform another operation:

INSERT INTO TransactionsDemo (ID, Name, Balance) VALUES (3,

'Charlie', 1200.00);
SAVEPOINT Save1;

15. Roll back to the savepoint:

UPDATE TransactionsDemo SET Balance = Balance - 300 WHERE Name =

'Charlie';

ROLLBACK TO Save1;
 37

SELECT * FROM TransactionsDemo;

Obtained Output:

Desired
Output?

YES

Observation:
In this experiment, we examined the application of transactions to maintain data integrity within
the database. By utilizing the COMMIT command, we effectively saved the changes made during
a transaction, while the ROLLBACK command allowed us to reverse any undesired modifications.
Additionally, the SAVEPOINT feature provided a way to set intermediate checkpoints within a
transaction, enabling partial rollbacks if necessary. These techniques demonstrate how transactions
can provide both flexibility and reliability in managing database operations.
 38

LAB REPORT

05
Topic: Trigger, View, Procedure
CO Mapping: CO3

Date of Assignment Distribution: 3 December 2024

Date of Assignment Submission: 7 December 2024
 39

Experiment No: 05 Experiment Name: Efficient Employee Data Management Using

Stored Procedures, Triggers, and Views.

Experiment Details:
This experiment demonstrates the use of stored procedures, triggers, and views in SQL for
effective data management. It covers the creation of a stored procedure to update employee
salaries, a trigger to automatically log salary modifications into an audit table, and a view to present
employee and department details. These tasks showcase how SQL can be leveraged for
streamlined data handling, automated tracking, and enhanced reporting.

Schema
Employees (Emp_ID, Name, Department_ID, Salary)
Departments (Dept_ID, Dept_Name, Location)
Salary_Audit (Audit_ID, Emp_ID, Old_Salary, New_Salary)

Tasks to Perform
Task 1: Create and run a stored procedure to do the following:
1. Accept an employee ID (Emp_ID) as an IN parameter.
2. Get the employee's name as an OUT parameter and return it.
3. Accept a percentage as an INOUT parameter, then increase the employee's salary by that
percentage and return the updated salary.
Task 2: Create a trigger that does the following:
1. When an update to the Salary column in the Employees table occurs, automatically log the
change to a new table called Salary_Audit.
2. The Salary_Audit table should include: [Audit_ID (primary key), Emp_ID, Old_Salary,
New_Salary].
Task 3: Design a view that shows the following employee data:
Emp_ID, Name, Department, Salary, and Dept_Location.

Step-by-Step Procedure:
Database Schema

CREATE TABLE Employees (

Emp_ID INT PRIMARY KEY,
Name VARCHAR(100),
Department_ID INT,
Salary DECIMAL(10, 2)
);
 40

CREATE TABLE Departments (

Dept_ID INT PRIMARY KEY,
Dept_Name VARCHAR(100),
Location VARCHAR(100)
);

INSERT INTO Employees (Emp_ID, Name, Department_ID, Salary)

VALUES
(1, 'Most. Jannatul Firdousi Zoti', 1, 50000.00),
(2, 'Sarbajit Paul Bappy', 2, 60000.00),
(3, 'Rittik Chandra Das Turjy', 1, 55000.00),
(4, 'Maruf Rahman', 3, 70000.00);

INSERT INTO Departments (Dept_ID, Dept_Name, Location)

VALUES
(1, 'CSE', 'Dhaka'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

Task 1:
1. Accept an employee ID (Emp_ID) as an IN parameter.
2. Get the employee's name as an OUT parameter and return it.
3. Accept a percentage as an INOUT parameter, then increase the employee's salary by that
percentage and return the updated salary.

DELIMITER //

CREATE PROCEDURE UpdateEmployeeSalary (

IN Emp_ID INT,
OUT Emp_Name VARCHAR(100),
INOUT Salary_Percentage DECIMAL(5, 2)
)
BEGIN
SELECT Name INTO Emp_Name FROM Employees WHERE Emp_ID = Emp_ID;
UPDATE Employees
SET Salary = Salary + (Salary * Salary_Percentage / 100)
WHERE Emp_ID = Emp_ID;
SELECT Salary INTO Salary_Percentage FROM Employees WHERE Emp_ID
= Emp_ID;
END //

DELIMITER;
 41

Obtained Output:

Desired
Output?

YES

Task 2:
1. When an update to the Salary column in the Employees table occurs, automatically log the
change to a new table called Salary_Audit.
2. The Salary_Audit table should include: [Audit_ID (primary key), Emp_ID, Old_Salary,
New_Salary].

CREATE TABLE Salary_Audit (

Audit_ID INT PRIMARY KEY AUTO_INCREMENT,
Emp_ID INT,
Old_Salary DECIMAL(10, 2),
New_Salary DECIMAL(10, 2)
);

DELIMITER //

CREATE TRIGGER SalaryUpdateAudit

AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
INSERT INTO Salary_Audit (Emp_ID, Old_Salary, New_Salary)
VALUES (OLD.Emp_ID, OLD.Salary, NEW.Salary);
END //

DELIMITER;

Obtained Output:

Desired
Output?
 42

YES

Task 3:
1. Design a view that shows the following employee data:

CREATE VIEW EmployeeDetails AS

SELECT e.Emp_ID, e.Name, d.Dept_Name AS Department, e.Salary,
d.Location AS Dept_Location
FROM Employees e
JOIN Departments d ON e.Department_ID = d.Dept_ID;

Obtained Output:

Desired
Output?

YES

Observation:
In this experiment, we focused on the implementation of stored procedures, triggers, and views to
enhance data management and integrity. We created a stored procedure that facilitates employee
salary updates and returns the updated salary. Additionally, a trigger was set up to automatically
capture any salary changes and log them in an audit table, ensuring transparency and
accountability. We also developed a view to consolidate employee and department information,
streamlining data access. This experiment underscored the value of stored procedures for handling
dynamic data operations, triggers for maintaining data integrity and audit trails, and views for
efficient data retrieval and presentation.
 43

After all report

Appendix
Appendix A: Course Outcomes, Complex Engineering Problems (EP) and Complex Engineering
Activities (EA) Addressing.

Table 1: Addressing CO with Justification

SN COs Attainment Justification

01 CO1,CO2 Yes These lab activities cover CO1 and CO2 by creating databases
with interconnected tables using primary and foreign keys, and
performing CRUD operations. The tasks involve using SQL
commands like CREATE, ALTER, DROP, INSERT, and
SELECT, while optimizing query performance in MySQL.

02 CO2 Yes These lab activities focus on CO2 by exploring advanced SQL
queries, including nested queries, LIMIT, and ranking
techniques, with a focus on query optimization and execution
time analysis in MySQL. They enhance skills in writing
efficient, complex queries for accurate data retrieval.

03 CO2 Yes These lab activities achieve CO2 by applying various SQL
joins (INNER, LEFT, RIGHT, FULL) to retrieve data from
related tables, emphasizing table relationships and efficient
data retrieval strategies in relational databases.

04 CO2 Yes These lab activities address CO2 by exploring transaction

management with COMMIT, ROLLBACK, and
SAVEPOINT, ensuring data consistency and integrity through
controlled execution and rollback of database operations.

05 CO2,CO3 Yes These lab activities cover CO2 and CO3 by using triggers,
stored procedures, and views to address database challenges.
Triggers ensure data integrity, stored procedures handle
complex operations, and views simplify data access and
improve security. These activities enhance skills in
maintaining consistency, security, and performance in
databases.
 44

Table 2: Addressing CO (1 to 3), Knowledge Profile (K), Attainment of Complex

Engineering Problems (EP):

[Must attain EP1 and (from EP2-EP7 at least another one)]

SN Engineering Attainment CO Justification Knowledge References

Problem (EP) Profile (K) (Page
Definition Number)

01 EP1: Depth of Yes CO1, The labs require both K2, K3, K4 Lab Report
Knowledge CO2 basic and advanced SQL 01
Required knowledge, including (Page: 3-14)
database design,
optimization, indexing,
and query execution.

02 EP2: Range of Yes CO2 Optimizing queries while K3, K4, K6 Lab Report
Conflicting ensuring data integrity 02
Requirements across tables, with a (Page: 15-25)
focus on execution time
and efficient data
management.

03 EP4: Familiarity Yes CO2 Tackling real-world K6, K8 Lab Report

of Issues challenges like data 03
integrity, database (Page: 26-31)
security, and distributed
architecture in advanced
labs.

04 EP7: Yes CO2 Ensuring data K2, K4 Lab Report

Interdependence consistency by managing 04
interdependencies (Page: 32-37)
between related tables,
including joins, foreign
keys, and triggers.
 45

05 EP6: Advanced Yes CO3 Solving real-world K6, K8 Lab Report

Integration of database management 05
Concepts problems by (Page: 38-42)
implementing complex
queries, triggers, views,
and stored procedures,
integrating various
database concepts.