ST.
ANDREWS INSTITUTE
OF TECHNOLOGY & MANAGEMENT
Gurgaon Delhi (NCR)
Approved by AICTE, Govt. of India, New Delhi
Affiliated to Maharshi Dayanand University
‘A ’ Grade State University, accredited by NAAC
+
Session: 2023 – 2024
Bachelor of Technology
Computer Science and Engineering
A Practical File
Database Management System Lab
Subject Code: LC-CSE-209G
Submitted to: Submitted By:
Mr. Rahul Name: Nidhi
(Assistant Professor)
Sem: 3rd
[Link].
List of Experiments
S. No Name CO Date Remarks
Creation of a database and writing SQL queries to
1 retrieve information from the database
CO1 13/09/2023
Performing Insertion, Deletion, Modifying,
2 Altering, Updating and Viewing records based CO1 13/09/2023
on conditions.
Creation of Views, Synonyms, Sequence,
3 Indexes, Save point CO1 20/09/2023
4 Creating an employee database to set various CO2 11/10/2023
constraints.
Creating relationship between the databases.
5 CO2 11/10/2023
Study of PL/SQL block
6 CO3 18/10/2023
Write a PL/SQL block to satisfy some
7 conditions by accepting input from the user. CO3 18/10/2023
Write a PL/SQL block that handles all types of
8 exceptions.
CO3 25/10/2023
Creation of Procedures.
9 CO3 25/10/2023
Creation of database triggers and functions.
10 CO3 08/11/2023
Mini project.
11 CO4 08/11/2023
Program 1: Creation of a database and writing SQL queries to retrieve
information from the database.
CODE:
create database colleges;
use colleges;
create table SAITM(
Branch_ID int primary key,
Branch_Name varchar (40),
Number_Of_Students int ,
Faculties_Assigned int,
HOD_Email varchar (50)
)
insert into SAITM values(12451,'[Link]',350,15,'ap_cse_2023@[Link]'),
(12452,'BBA',250,10,'ap_bba_2023@[Link]'),
(12453,'BCA',300,11,'ap_bca_2023@[Link]'),
(12454,'MBA',60,6,'mba_2023@[Link]'),
(12455,'MTech',15,5,'mtech_2023@[Link]')
select * from SAITM;
OUTPUT:
Program 2: Performing Insertion, Deletion, Modifying, Altering, Updating
and Viewing records based on conditions.
CODE:
Insertion:
insert into SAITM values(12456,'MCA',20,4,'mca_2023@[Link]');
select * from SAITM;
OUTPUT:
Deletion:
Delete from SAITM where Branch_ID=12456;
select * from SAITM;
OUTPUT:
Updation/Modification:
update SAITM set HOD_Email='ap_mba_2023@[Link]' where Branch_ID=12454;
select * from SAITM;
OUTPUT:
Altering Table:
ALTER TABLE SAITM add HOD_Contact int;
Select * from SAITM
Output:
Program 3: Creation of Views, Synonyms, Sequence, Indexes,
Save point.
CODE:
[Link] View:
CREATE OR ALTER VIEW max_students AS
select Branch_ID,Branch_Name,Number_Of_Students
FROM SAITM
where Number_Of_Students>=200;
select * from max_students;
OUTPUT:
[Link] Synonyms:
create synonym SAITM_STUDENTS FOR SAITM
select *from SAITM_STUDENTS;
OUTPUT:
[Link] Sequence:
create table UG_Students(
stud_ID int ,
Stu_Name varchar(40),
Branch varchar(40),
Gmail varchar (50)
)
CREATE SEQUENCE ID_seq
minvalue 1
maxvalue 10
start with 2
no cycle
cache 5
increment by 1;
insert into UG_Students
values(1,'Richel','[Link]','richel@[Link]');
insert into UG_Students
values(next value for ID_seq,'Smith','BBA','smith@[Link]');
insert into UG_Students
values(next value for ID_seq,'Anuj','MBA','anuj@[Link]');
select * from UG_Students;
OUTPUT:
[Link] Indexes:
create index idx_Stud_ID on UG_Students(Stud_ID);
select * from UG_Students;
OUTPUT:
[Link] Savepoints:
BEGIN Transaction;
insert into UG_Students(stud_ID,Stu_Name,Branch,Gmail)
values(3,'Ramesh','BCA','ramesh12@[Link]');
SAVE Transaction my_savepoint;
insert into UG_Students(stud_ID,Stu_Name,Branch,Gmail)
values(4,'Pratyush','BBA','prat@[Link]');
insert into UG_Students(stud_ID,Stu_Name,Branch,Gmail)
values(5,'Sita','MBA','sita@[Link]');
insert into UG_Students(stud_ID,Stu_Name,Branch,Gmail)
values(6,'Amrita','BTech','amrita@[Link]');
ROLLBACK TRANSACTION my_savepoint;
select * from UG_Students;
OUTPUT:
Program 4: Creating an employee database to set various constraints.
CODE:
Table:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Department NVARCHAR(50),
Salary DECIMAL(10, 2),
HireDate DATE,
CONSTRAINT CHK_Salary CHECK (Salary >= 0),
CONSTRAINT CHK_HireDate CHECK (HireDate <= GETDATE()),
CONSTRAINT CHK_NameLength CHECK (LEN(FirstName) > 1 AND LEN(LastName) >
1),
CONSTRAINT CHK_Department CHECK (Department IN ('IT', 'HR', 'Finance'))
);
OUTPUT:
CONSTRAINTS:
Valid records:
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary,
HireDate)
VALUES
(1, 'John', 'Doe', 'IT', 60000, '2022-01-15'),
(2, 'Alice', 'Smith', 'HR', 55000, '2021-11-20'),
(3, 'Bob', 'Johnson', 'Finance', 58000, '2022-03-10');
OUTPUT:
Invalid records:
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary,
HireDate)
VALUES
(4, 'Mariam', 'Ansari', 'HR', -5000, '2023-05-25'),
(5, 'Tina', 'Thakur', 'Finance', 70000, '2024-09-30');
OUTPUT:
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary,
HireDate)
VALUES
(6, 'A', 'Bansal', 'IT', 52000, '2020-07-12'),
(7, 'Aman', '', 'HR', 48000, '2022-08-05');
OUTPUT:
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary,
HireDate)
VALUES
(8, 'Mahesh', 'Thakur', 'Marketing', 65000, '2021-12-01');
OUTPUT:
Program 5: Creating relationship between the databases.
CODE:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName NVARCHAR(50) NOT NULL
);
CREATE TABLE Employeess (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
DepartmentID INT,
CONSTRAINT FK_Department_Employeess FOREIGN KEY (DepartmentID)
REFERENCES Departments(DepartmentID)
);
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'IT'), (2, 'HR'), (3, 'Finance');
INSERT INTO Employeess (EmployeeID, FirstName, LastName, DepartmentID)
VALUES
(1, 'John', 'Doe', 1),
(2, 'Alice', 'Smith', 2);
SELECT [Link], [Link], [Link], [Link]
FROM Employeess e
JOIN Departments d ON [Link] = [Link];
OUTPUT:
Program 6: Study of PL/SQL block.
CODE:
BEGIN
CREATE TABLE temptable (
Branch_ID int,
Branch_Name varchar
);
INSERT INTO temptable (Branch_ID ,Branch_Name)
SELECT Branch_ID ,Branch_Name
FROM SAITM;
SELECT * FROM temptable;
DROP TABLE #TempTable;
END;
OUTPUT:
Program 7: Write a PL/SQL block to satisfy some conditions by accepting
input from the user.
CODE:
DECLARE @userInput INT;
SET @userInput = 5;
IF @userInput > 10
PRINT 'User input is greater than 10';
ELSE IF @userInput < 5
PRINT 'User input is less than 5';
ELSE
PRINT 'User input is between 5 and 10 (inclusive)';
OUTPUT:
Program 8: Write a PL/SQL block that handles all types of exceptions.
CODE:
BEGIN TRY
DECLARE @numerator INT = 10;
DECLARE @denominator INT = 0;
DECLARE @result INT;
SET @result = @numerator / @denominator;
PRINT 'The result of division: ' + CAST(@result AS NVARCHAR);
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134
PRINT 'Error: Division by zero occurred';
ELSE
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
OUTPUT:
BEGIN TRY
DECLARE @numerator INT = 10;
DECLARE @denominator INT = 20;
DECLARE @result INT;
SET @result = @numerator / @denominator;
PRINT 'The result of division: ' + CAST(@result AS NVARCHAR);
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134
PRINT 'Error: Division by zero occurred';
ELSE
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
OUTPUT:
BEGIN TRY
DECLARE @numerator INT = 20;
DECLARE @denominator INT = 10;
DECLARE @result INT;
SET @result = @numerator / @denominator;
PRINT 'The result of division: ' + CAST(@result AS NVARCHAR);
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134
PRINT 'Error: Division by zero occurred';
ELSE
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;
OUTPUT:
Program 9: Creation of Procedures.
CODE:
select * from UG_Students;
create procedure allrecords
AS
select *from UG_Students
GO;
OUTPUT:
select * from UG_Students;
create procedure allrecords
AS
select *from UG_Students
GO;
exec allrecords
OUTPUT:
create procedure allrecord @Branch varchar (225)
AS
select *from UG_Students where Branch=@Branch;
exec allrecord @Branch='MBA';
OUTPUT:
create procedure allrecords3 @Branch varchar (225),@Stu_Name varchar (255)
AS
select *from UG_Students where Branch=@Branch AND Stu_Name=@Stu_Name;
exec allrecords3 @Branch='BBA',@Stu_Name='Smith';
OUTPUT:
Program 10: Creation of database triggers and functions.
CODE:
1. CREATE Table totalmarks(StudentID int,Name varchar(40),score1 int,score2
int,TotalMarks int);
insert into totalmarks
values(101,'Rohit',20,30,50),(102,'Mohit',30,40,70),(103,'Nisha',40,50,90),
(104,'Naman',50,60,110),(105,'Mehak',60,70,130);
select * from totalmarks;
CREATE TRIGGER CalculateTotalMarks
ON totalmarks
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE totalmarks
SET TotalMarks = ISNULL(i.Score1, 0) + ISNULL(i.Score2, 0)
FROM totalmarks s
INNER JOIN inserted i ON [Link] = [Link];
END;
OUTPUT:
2. CREATE TRIGGER CalculateTotalMarkss
ON totalmarks
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StudentID INT, @TotalMarks INT;
SELECT @StudentID = StudentID FROM inserted;
SELECT @TotalMarks = ISNULL(Score1, 0) + ISNULL(Score2, 0)
FROM totalmarks
WHERE StudentID = @StudentID;
PRINT 'Total Marks for Student ID ' + CAST(@StudentID AS VARCHAR) + ': ' +
CAST(@TotalMarks AS VARCHAR);
END;
OUTPUT:
3. CREATE TRIGGER CalculateTotalMarkss
ON totalmarks
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StudentID INT, @TotalMarks INT;
SELECT @StudentID = StudentID FROM inserted;
SELECT @TotalMarks = ISNULL(Score1, 0) + ISNULL(Score2, 0)
FROM totalmarks
WHERE StudentID = @StudentID;
PRINT 'Total Marks for Student ID ' + CAST(@StudentID AS VARCHAR) + ': ' +
CAST(@TotalMarks AS VARCHAR);
END;
INSERT INTO totalmarks (StudentID, Name, Score1, Score2) VALUES (1, 'John Doe', 80,
75);
OUTPUT:
Program 11: Mini project.
CODE:
TOPIC: HOSPITAL MANAGEMENT SYSTEM.
1. Tables Included:
[Link].
[Link].
[Link].
[Link].
[Link].
1. DOCTORS TABLE:
CODE:
CREATE TABLE Doctors (
DoctorID INT PRIMARY KEY,
DoctorName VARCHAR(100),
Specialization VARCHAR(100)
);
OUTPUT:
2. PATIENTS TABLE:
CODE:
CREATE TABLE Patients (
PatientID INT PRIMARY KEY,
PatientName VARCHAR(100),
Age INT,
Gender VARCHAR(10)
);
OUTPUT:
3. APPOINTMENTS TABLE:
CODE:
CREATE TABLE Appointments (
AppointmentID INT PRIMARY KEY,
DoctorID INT,
PatientID INT,
AppointmentDate DATE,
FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID),
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID)
);
OUTPUT:
4. MEDICATIONS TABLE:
CODE:
CREATE TABLE Medications (
MedicationID INT PRIMARY KEY,
PatientID INT,
MedicationName VARCHAR(100),
Dosage VARCHAR(50),
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID)
);
OUTPUT:
5. TESTS TABLE:
CODE:
CREATE TABLE Tests (
TestID INT PRIMARY KEY,
PatientID INT,
TestName VARCHAR(100),
TestResult VARCHAR(100),
FOREIGN KEY (PatientID) REFERENCES Patients(PatientID)
);
OUTPUT:
INSERTING AND DISPLAYING RECORDS IN EACH TABLE:
1. DOCTORS TABLE:
CODE:
INSERT INTO Doctors VALUES (1, 'Dr. Mandeep',
'Cardiologist'),(2,'[Link]','Dentist'),(3,'[Link]','Neurologist'),
(4,'[Link] Nath','Gynecologist'),(5,'[Link] Sharma','Orthopedic Surgeon');
select * from Doctors;
OUTPUT:
2. PATIENTS TABLE:
CODE:
INSERT INTO Patients VALUES (1, 'Mohan', 35, 'Male'),(2,'Sunita',25,'Female'),(3, 'Rohan',
15, 'Male'),
(4,'Anita',20,'Female'),(5, 'Mohit', 55, 'Male'),(6, 'Sohan', 35, 'Male'),(7,'Rani',5,'Female'),
(8,'Kajal',65,'Female'),(9, 'Rajiv', 40, 'Male'),(10, 'Raman', 33, 'Male'),(11,'Neha',18,'Female'),
(12,'Jiya',22,'Female');
select * from Patients;
OUTPUT:
3. APPOINTMENTS TABLE:
CODE:
INSERT INTO Appointments VALUES (1, 1, 1, '2023-11-20'),(2, 2, 1, '2023-11-10'),(3, 2, 2,
'2023-11-21'),
(4, 3, 1, '2023-11-02'),(5, 1, 12, '2023-11-02'),(6, 5, 11, '2023-11-13'),(7, 4, 3, '2023-11-05'),
(8, 4, 9, '2023-11-04'),(9, 1, 11, '2023-11-11'),(10, 4, 8, '2023-11-07'),(11, 1, 3, '2023-11-16'),
(12, 2, 6, '2023-11-27'),(13, 3, 5, '2023-11-15');
select * from Appointments;
OUTPUT:
4. MEDICATIONS TABLE:
CODE:
INSERT INTO Medications VALUES (12451, 1, 'Aspirin', '10mg'),(12452, 12, 'Tacrine',
'100mg'),
(12453, 7, 'Mannitol IV', '250mg'),(12454, 10, 'Rabavert', '50mg'),(12455, 9, 'Idaurbicin',
'150mg'),
(12456, 6, 'Abacavir', '500mg'),(12457, 5, 'Xcopri', '10mg');
select * from Medications;
OUTPUT:
5. TESTS TABLE:
CODE:
INSERT INTO Tests VALUES (1, 1, 'Blood Test', 'Normal'),(2, 4, 'Gastric Fluid Analysis',
'Abnormal'),
(3, 11, 'PAP Smear', 'Abnormal'),(4, 7, 'Brain Scanning', 'Normal'),(5, 5, 'Endoscopy',
'Normal'),
(6, 9, 'Liver Functioning Test', 'Abnormal'),(7, 3, 'Skin Test', 'Normal');
select * from Tests;
OUTPUT: