Database Management System-BCS403

DBMS LAB MANUAL (BCS403)

1. Create a table called Employee & execute the following:

Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION).
i. Create a user and grant all permissions to the user.
ii. Insert the any three records in the employee table contains attributes EMPNO,
ENAME JOB, MANAGER_NO, SAL, COMMISSION and use rollback. Check the result.
iii. Add primary key constraint and not null constraint to the employee table.
iv. Insert null values to the employee table and verify the result.

Step 1: Creating the Employee table

CREATE TABLE Employee
( EMPNO VARCHAR(10),
NAME VARCHAR(20),
JOB VARCHAR(20),
MANAGER_NO INT,
SAL DECIMAL(10,2),
COMMISSION DECIMAL(10,2)
);

Output: Table EMPLOYEE created.

Step 2: Creating a user and granting permissions

CREATE USER cse IDENTIFIED BY password_exp;

Output: User cse created.
GRANT ALL PRIVILEGES to employee;
Output: Grant succeeded.

Step 3: Inserting three records and rolling back

INSERT INTO Employee VALUES (1, 'John Doe', 'Manager', NULL, 50000.00, 1000.00);
1 row inserted.

INSERT INTO Employee VALUES (2, 'Jane Smith', 'Developer', 1, 40000.00, 500.00);
1 row inserted.

INSERT INTO Employee VALUES (3, 'Alice Johnson', 'Analyst', 1, 35000.00, NULL);

Dept. of CS&E, STJIT

1
Database Management System-BCS403

1 row inserted.

commit;
Output: Commit complete.

select * from employee;

delete from employee where ename = 'John Doe';

Output: 1 row deleted.

select * from employee;

Output:

rollback;

Output: Rollback complete.

select * from employee;

Output:

Step 4: Adding primary key and not null constraints

ALTER TABLE Employee

ADD CONSTRAINT PK_Employee_EMPNO PRIMARY KEY (EMPNO);

Output:
Table EMPLOYEE altered.

Step 5: Inserting null values and verifying the result

Dept. of CS&E, STJIT

1
Database Management System-BCS403

INSERT INTO Employee VALUES(NULL, 'Test Employee', 'Tester', NULL, NULL, NULL);

Output: Error at line 1: Cannot insert NULL into(“cse”_”employe’,’empno’)

2. Create a table called Employee that contain attributes EMPNO, ENAME, JOB, MGR,
SAL & execute the following.
1. Add a column commission with domain to the Employee table.
2. Insert any five records into the table.
3. Update the column details of job
4. Rename the column of Employee table using alter command.
5. Delete the employee whose Empno is 105.

CREATE TABLE Employee (

EMPNO INT PRIMARY KEY,
ENAME VARCHAR(50),
JOB VARCHAR(50),
MGR INT,
SAL DECIMAL(10, 2)
);
Output: Table Employee created
1. Add a column commission with domain to the Employee table
ALTER TABLE Employee
ADD COLUMN commission DECIMAL(10, 2);
Output: Employee table altered
2. Insert five records into the table
INSERT INTO Employee VALUES (101, 'John Doe', 'Manager', NULL,
5000.00, 200.00);
Output: 1 row inserted
INSERT INTO Employee VALUES (102, 'Jane Smith', 'Developer', 101, 4000.00,
150.00);
Output: 1 row inserted
INSERT INTO Employee VALUES (103, 'Alice Johnson', 'Designer', 101,

Dept. of CS&E, STJIT

1
Database Management System-BCS403

3500.00, 100.00);
Output: 1 row inserted
INSERT INTO Employee VALUES (104, 'Bob Brown', 'Analyst', 102,
4500.00, 180.00);
Output: 1 row inserted

INSERT INTO Employee VALUES (105, 'Emma Davis', 'Tester', 103, 3800.00,
120.00);
Output: 1 row inserted

3. Update the column 'JOB' details

UPDATE Employee SET JOB = 'Software Engineer' WHERE ENAME = 'John Doe';
Output: Employee table updated

4. Rename the column 'Employ' table using ALTER command

ALTER TABLE Employee RENAME COLUMN Employ TO Employment;
Output: Table Altered

5. Display the records before deletion

SELECT * FROM Employee;

6. Delete the employee whose Empno is 105.

DELETE FROM Employee WHERE EMPNO = 105;
Output: 1 row deleted

7. Display the records after deletion

SELECT * FROM Employee;

Dept. of CS&E, STJIT

1
Database Management System-BCS403

3. Queries using aggregate functions (COUNT,AVG,MIN,MAX,SUM),Group by, Orderby.

Employee(E_id, E_name, Age, Salary)

i. Create Employee table containing all Records E_id, E_name, Age, Salary.
ii. Count number of employee names from employee table
iii. Find the Maximum age from employee table.
iv. Find the Minimum age from employee table.
v. Find salaries of employee in Ascending Order.
vi. Find grouped salaries of employees.

i. Creating the Employee table

CREATE TABLE Employee (

E_id INT PRIMARY KEY,
E_name VARCHAR(50),
Age INT,
Salary DECIMAL(10, 2)
);

Output:
Table Employee created

Insertion of Values into the Table:

Insert into Employee values (1, ‘Ramesh’, 35, 25000.00);

Insert into Employee values (2,'Suresh', 39, 32000.00);
Insert into Employee values (3,'Ganesh', 50, 55000.00);
Insert into Employee values (4,'Sunaina',45, 24000.00);
Insert into Employee values (5,'Rashmi', 41, 66000.00);

ii. Count number of employee names

SELECT COUNT(E_name) AS TotalEmployees FROM Employee;

Output:

iii. Find the Maximum age:

SELECT MAX(Age) AS MaxAge FROM Employee;

Output:

Dept. of CS&E, STJIT

1
Database Management System-BCS403
iv. Find the Minimum age

SELECT MIN(Age) AS MinAge FROM Employee;

Output:

v. Find salaries of employees in Ascending Order:

SELECT Salary FROM Employee ORDER BY Salary ASC;

Output:

vi. Find grouped salaries of employees:

SELECT Salary, COUNT(*) AS EmployeeCount FROM Employee GROUP BY Salary;

Output:

Dept. of CS&E, STJIT

1
Database Management System-BCS403

4. Create a row level trigger for the customers table that would fire for INSERT
or UPDATE or DELETE operations performed on the CUSTOMERS table. This
trigger will display the salary difference between the old & new Salary.
CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)

-- Create the CUSTOMERS table

CREATE TABLE CUSTOMERS
(
ID INT PRIMARY KEY,
NAME VARCHAR(100),
AGE INT,
ADDRESS VARCHAR(255),
SALARY DECIMAL(10, 2)
);

Output:
Table CUSTOMERS created.

DESC CUSTOMERS;

Output:
Name Null? Type
------- -------- -------------
ID NOT NULL NUMBER(38)
NAME VARCHAR2(100)
AGE NUMBER(38)
ADDRESS VARCHAR2(255)
SALARY NUMBER(10,2)

INSERT INTO CUSTOMERS VALUES (1, 'John Doe', 30, '123 Main St', 50000);

Output:
1 row inserted.

INSERT INTO CUSTOMERS VALUES (2, 'Jane Smith', 35, '100 WashingtonDC', 75000);

Output:
1 row inserted.

INSERT INTO CUSTOMERS VALUES (3, 'Alice', 25, '104 Sanfransisco', 75000);

Output:
1 row inserted.

Dept. of CS&E, STJIT

1
Database Management System-BCS403

-- Commit the transaction

COMMIT;

Output:
Commit complete.

select * from customers;

Output:

UPDATE CUSTOMERS SET SALARY=85000 Where ID=2;

Output:
1 row updated.

select * from customers;

Output:

--See the difference in output with TRIGGER and update query. RUN the following
TRIGGER first.
SET SERVEROUTPUT ON;

-- Create the trigger

CREATE OR REPLACE TRIGGER salary_difference_trigger
BEFORE INSERT OR UPDATE OR DELETE ON CUSTOMERS
FOR EACH ROW
DECLARE
old_salary NUMBER;
new_salary NUMBER;
BEGIN
IF INSERTING OR UPDATING THEN

Dept. of CS&E, STJIT

1
Database Management System-BCS403
old_salary := NVL(:OLD.SALARY, 0);
new_salary := NVL(:NEW.SALARY, 0);
DBMS_OUTPUT.PUT_LINE('Salary difference: ' || (new_salary - old_salary));
ELSIF DELETING THEN
old_salary := NVL(:OLD.SALARY, 0);
DBMS_OUTPUT.PUT_LINE('Salary before deletion: ' || old_salary);
END IF;
END;
/

Output:

Trigger SALARY_DIFFERENCE_TRIGGER compiled

--RUN the following query

UPDATE CUSTOMERS SET SALARY=90000 Where ID=2;

Output:

Trigger SALARY_DIFFERENCE_TRIGGER compiled

1 row updated.

SELECT * FROM CUSTOMERS;

Output:

Output:

Trigger Salary difference: -40000

1 row updated.

select * from customers;

Output:

Dept. of CS&E, STJIT

1
Database Management System-BCS403

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (4, 'James
Bond', 40, '121 california', 58000);

Output:

Salary difference: 58000

1 row inserted.

select * from customers;

Output:

delete from customers where id=4;

Output:

Salary before deletion: 58000

1 row deleted.

select * from customers;

Output:

Dept. of CS&E, STJIT

1
Database Management System-BCS403
5. Create a cursor for the Employee table & extract the values from the table. Declare
the variables, open the cursor & extract the values from the cursor. Close the cursor.
Employee(E_id,E_name, Age, Salary)

STEP 1: Create table Employee

CREATE TABLE Employee (

E_id INT,
E_name VARCHAR(255),
Age INT,
Salary DECIMAL(10, 2)
);

OUTPUT:
Table created

STEP 2: Insert values into Employee table:

BEGIN
INSERT INTO Employee VALUES(1, 'Samarth', 30, 50000.00);
INSERT INTO Employee VALUES(2, 'Ramesh Kumar', 25, 45000.00);
INSERT INTO Employee VALUES (3, 'Seema Banu', 35, 62000.00);
INSERT INTO Employee VALUES (4, 'Dennis Anil', 28, 52000.00);
INSERT INTO Employee VALUES (5, 'Rehman Khan', 32, 58000.00);
END;

OUTPUT:
Statement Processed.

STEP 3: Create a procedure

CREATE OR REPLACE PROCEDURE fetch_employee_data

IS
emp_id Employee.E_id%TYPE;
emp_name Employee.E_name%TYPE;
emp_age Employee.Age%TYPE;
emp_salary Employee.Salary%TYPE;

CURSOR emp_cursor IS

SELECT E_id, E_name, Age, Salary
FROM Employee;

BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_id, emp_name, emp_age, emp_salary;
EXIT WHEN emp_cursor%NOTFOUND;

Dept. of CS&E, STJIT

1
Database Management System-BCS403
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ', Name: ' || empname || ',
Age: ' || emp_age || ', Salary: ' || emp_salary);
END LOOP;
CLOSE emp_cursor;
END;
/

OUTPUT:
Procedure created.

STEP 4: Run the procedure

BEGIN
fetch_employee_data;
END;

OUTPUT:

Employee ID: 1, Name: Samarth, Age: 30, Salary: 50000

Employee ID: 2, Name: Ramesh Kumar, Age: 25, Salary: 45000
Employee ID: 3, Name: Seema Banu, Age: 35, Salary: 62000
Employee ID: 4, Name: Dennis Anil, Age: 28, Salary: 52000
Employee ID: 5, Name: Rehman Khan, Age: 32, Salary: 58000

Statement processed.

Dept. of CS&E, STJIT

1
Database Management System-BCS403

6. Write a PL/SQL block of code using parameterized Cursor, that will merge the data
available in the newly created table N_RollCall with the data available in the table
O_RollCall. If the data in the first table already exist in the second table then that
data should be skipped.
Solution:
To accomplish this task in MySQL, we can use a stored procedure with
a parameterized cursor to merge data from one table
(N_RollCall) into another table (O_RollCall) while skipping existing data. We’ll
iterate through the records of N_RollCall and insert them into O_RollCall only if
they do not already exist.

Step 1: First, let’s create the N_RollCall and O_RollCall tables with similar structure:

CREATE TABLE N_RollCall (

student_id INT PRIMARY KEY,
student_name VARCHAR(255),
birth_date DATE
);

OUTPUT:
Table created

CREATE TABLE O_RollCall (

Student _id INT PRIMARY KEY,
student_name VARCHAR(255),
birth_date DATE
);

OUTPUT:
Table created

Step 2: Add Sample Records to both tables (date format: MM-DD-YYYY)

Begin
INSERT INTO O_RollCall VALUES (1,'Shivanna','08-15-1995');
INSERT INTO O_RollCall VALUES (3,'Cheluva','12-10-1990');
end;

OUTPUT:
Statement Processed.

Select * from O_Rollcall;

Dept. of CS&E, STJIT

1
Database Management System-BCS403

Begin
INSERT INTO N_RollCall VALUES(1, 'Shivanna', '08-15-1995');
INSERT INTO N_RollCall VALUES(2, 'Bhadramma','03-22-1998');
INSERT INTO N_RollCall VALUES(3, 'Cheluva', '12-10-1990');
INSERT INTO N_RollCall VALUES(4, 'Devendra', '05-18-2000');
INSERT INTO N_RollCall VALUES(5, 'Eshwar', '09-03-1997');
end;
/

OUTPUT:
Statement Processed.

Select * from N-Rollcall;

OUTPUT:

Step 3: Define the Stored Procedure

Next, let’s define the merge_rollcall_data stored procedure to merge records from
N_RollCall into O_RollCall, skipping existing records:

CREATE OR REPLACE PROCEDURE merge_rollcall_data AS

-- Declare variables
n_id N_RollCall.student_id%TYPE;
n_name N_RollCall.student_name%TYPE;
n_birth_date N_RollCall.birth_date%TYPE;
v_count NUMBER;

-- Cursor declaration
CURSOR n_cursor IS
SELECT student_id, student_name, birth_date
FROM N_RollCall;
BEGIN

Dept. of CS&E, STJIT

1
Database Management System-BCS403
-- Open the cursor
OPEN n_cursor;
-- Start looping through cursor results
LOOP
-- Fetch data from cursor into variables
FETCH n_cursor INTO n_id, n_name, n_birth_date;
-- Exit loop if no more rows to fetch
EXIT WHEN n_cursor%NOTFOUND;
-- Check if the data already exists in O_RollCall
SELECT COUNT(*) INTO v_count FROM O_RollCall WHERE student_id = n_id;
IF v_count = 0 THEN
-- Insert the record into O_RollCall
INSERT INTO O_RollCall (student_id, student_name, birth_date)
VALUES (n_id, n_name, n_birth_date);
END IF;
END LOOP;

-- Close the cursor

CLOSE n_cursor;
END merge_rollcall_data;
/

OUTPUT:
Procedure created.

Step 4: Execute the Stored Procedure

Finally, execute the merge_rollcall_data stored procedure to merge records from
N_RollCall into O_RollCall while skipping existing records:

Begin
merge_rollcall_data;
End;

OUTPUT:
Statement Processed.

Step 5: Verify Records in O_RollCall

select * from O_rollcall;

Dept. of CS&E, STJIT

1
Database Management System-BCS403

7. Install an Open Source NoSQL Database MongoDB and perform basic CRUD
(Create, Read, Update and Delete) operations. Execute MongoDB basic queries
using CRUD operations.

Step 1: Install MongoDB and perform the following:

*Open MongoDB, click on current.
Step 2: Click on ‘+’ symbol found beside the databases.
a. Provide database name.
b. Provide the collection name.
c. Click on create database.
d. Minimize and close MongoDB.
e. Open the command prompt and run MongoDB by giving “mongosh” and press
enter.
f. Use newly created database and press enter.
Step 3: CRUD operations
Create: To create data, initially we should insert the values into database in two
possibilities.
i. insertOne:
databasename>db.collectionname.insertOne
({
Title: “C Programming”,
Author: “Reema Tareja”,
Category: “Programming”,
Year: 2024 })
ii. insertMany:
databasename>db.collectionname.insertMany
([{

Dept. of CS&E, STJIT

1
Database Management System-BCS403

Title: “C Programming”,
Author: “Reema Tareja”,
Category: “Programming”,
Year: 2024 },
{
Title: “Unix Programming”,
Author: “Sumitaba Das”,
Category: “Programming”,
Year: 2014 },
{
Title: “ Big Data Analytics”,
Author: “RajKamal”,
Category: “Data Science”,
Year: 2020 }]).

b. Read: To read the data, values should be inserted first.

databasename> db.collectionname.find().pretty()
It displays the records found in the database.

c. To find the documents matching the condition:

databasename> db.collectionname.find({ year: {$gt: 2010 } } )
It displays the documents that matches the documents in the
database.

d. To update a single document:

databasename> db.collectionname.UpdateOne (
{ Title: “Data Structures “ },
{ $set: { Author: “DS (New Name)” } }.
It updates the document with title “Data Structures” and now the author will
bw the updated one.

Dept. of CS&E, STJIT

1
Database Management System-BCS403

e. To delete:
databasename> db.collectionname.deleteOne (
{ Title: “Data Structures” } )
It deletes the document with title “Data Structures” from the database.

Dept. of CS&E, STJIT

1