DBMS Laboratory BCS403

PROGRAM 1

Create a table called Employee & execute the following.

Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION)

1. Create a user and grant all permissions to the user.

2. Insert any three records in the employee table contains attributes EMPNO,
ENAME, JOB, MANAGER_NO, SAL, COMMISSION and use rollback. Check
the result.
3. Add primary key constraint and not null constraint to the employee table.
4. Insert null values to the employee table and verify the result.

Solution:

Dept. of CSE, NCE, Hassan Page 1

DBMS Laboratory BCS403

1. Create a user and grant all permissions to the user.

2. Insert any three records in the employee table contains attributes

EMPNO,ENAME JOB, MANAGER_NO, SAL, COMMISSION and use
rollback. Check the result.

mysql> START TRANSACTION;

insert into Employee values(1, 'Kavana Shetty', 'Manager', NULL, 50000.00, 1500.00);

mysql> COMMIT;

insert into Employee values(2, 'Shetty', 'Sales person', NULL, 5000.00, 500.00);

insert into Employee values(3, 'Karan', 'sales person', NULL, 5000.00, 1000.00);

mysql> SELECT * FROM Employee;

mysql> ROLLBACK;

3. Add primary key constraint and not null constraint to the employee table.

mysql> ALTER TABLE Employee

-> ADD CONSTRAINT pk_employee PRIMARY KEY (EMPNO);

Query OK, 0 rows affected (1.65 sec)

4. Insert null values to the employee table and verify the result.

mysql> ALTER TABLE Employee

-> MODIFY ENAME VARCHAR(255) NOT NULL,

-> MODIFY JOB VARCHAR(255) NOT NULL,

-> MODIFY SAL DECIMAL(10, 2) NOT NULL;

Query OK, 0 rows affected (1.08 sec)

Dept. of CSE, NCE, Hassan Page 2

DBMS Laboratory BCS403

When we are trying to add null value to the column after adding NOT NULL
constraint.

mysql> INSERT INTO Employee (ENAME, JOB, MANAGER_NO, SAL, COMMISSION)

-> VALUES (NULL, 'Tester', NULL, 3500.00, NULL);

ERROR 1048 (23000): Column 'ENAME' cannot be null

PROGRAM 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 Employeetable.

2. Insert any five records into the table.

3. Update the column details of job

4. Rename the column of Employ table using alter command.

5. Delete the employee whose Empno is 105.

1. Adding a Column (Commission) to the Employee Table

mysql> ALTER TABLE Employee

-> ADD COLUMN COMMISSION DECIMAL(10, 2);

2. Inserting 5 Records into the Employee Table

mysql> INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION)

-> VALUES

-> (101, 'Radha Bai', 'Manager', NULL, 5000.00, 1000.00),

-> (102, 'Krishna Kumar', 'Developer', 101, 4000.00, NULL),

-> (103, 'Abdul Sattar', 'Salesperson', 102, 3000.00, 500.00),

-> (104, 'Bob Johnson', 'Accountant', 101, 4500.00, NULL),

-> (105, 'Amartya Sen', 'HR Manager', 101, 4800.00, 800.00);

3. Updating Column Details (JOB) in the Employee Table

mysql> UPDATE Employee

Dept. of CSE, NCE, Hassan Page 3

DBMS Laboratory BCS403

-> SET JOB = 'Senior Developer'

-> WHERE EMPNO = 102;

4. Renaming a Column in the Employee Table

To rename the MGR column to MANAGER_ID:

mysql> ALTER TABLE Employee

-> CHANGE COLUMN MGR MANAGER_ID INT;

5. Deleting a Specific Employee (EMPNO = 105) from the Employee Table

mysql> DELETE FROM Employee

-> WHERE EMPNO = 105;

PROGRAM 3

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

Employee(E_id, E_name, Age, Salary)

1. Create Employee table containing all Records E_id, E_name, Age, Salary.

2. Count number of employee names from Employee table

3. Find the Maximum age from Employee table.

4. Find the Minimum age from Employee table.

5. Find salaries of employee in Ascending Order.

6. Find grouped salaries of employees.

1. Creating the Employee Table

CREATE TABLE Employee (E_id INT PRIMARY KEY,

E_name VARCHAR(255),

Age INT,

Salary DECIMAL(10, 2));

Dept. of CSE, NCE, Hassan Page 4

DBMS Laboratory BCS403

2. Populating the Employee Table with 12 Records

INSERT INTO Employee VALUES

(1, 'Samarth', 30, 50000.00),

(2, 'Ramesh Kumar', 25, 45000.00),

(3, 'Seema Banu', 35, 60000.00),

(4, 'Dennis Anil', 28, 52000.00),

(5, 'Rehman Khan', 32, 58000.00),

(6, 'Pavan Gowda', 40, 70000.00),

(7, 'Shruthi Bhat', 27, 48000.00),

(8, 'Sandesh Yadav', 29, 51000.00),

(9, 'Vikram Acharya', 33, 62000.00),

(10, 'Praveen Bellad', 26, 46000.00),

(11, 'Sophia Mary', 31, 55000.00),

(12, 'Darshan Desai', 34, 63000.00);

3. Count Number of Employee Names

SELECT COUNT(E_name) AS Total_Employees FROM Employee;

4. Find the Maximum Age

SELECT MAX(Age) AS Max_Age FROM Employee;

5. Find the Minimum Age

SELECT MIN(Age) AS Min_Age FROM Employee;

6. Find Salaries of Employees in Ascending Order

SELECT E_name, Salary

FROM Employee

Dept. of CSE, NCE, Hassan Page 5

DBMS Laboratory BCS403

ORDER BY Salary ASC;

PROGRAM 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 TABLE CUSTOMERS (ID INT PRIMARY KEY,

NAME VARCHAR(255),

AGE INT,

ADDRESS VARCHAR(255),

SALARY DECIMAL(10, 2));

---INSERT TRIGGER---

DELIMITER //

CREATE TRIGGER after_insert_salary_difference

AFTER INSERT ON CUSTOMERS

FOR EACH ROW

BEGIN

SET @my_sal_diff = CONCAT('salary inserted is ', NEW.SALARY);

END;//

DELIMITER ;

---UPDATE TRIGGER---

DELIMITER //

CREATE TRIGGER after_update_salary_difference

AFTER UPDATE ON CUSTOMERS

FOR EACH ROW

BEGIN

DECLARE old_salary DECIMAL(10, 2);

DECLARE new_salary DECIMAL(10, 2);

Dept. of CSE, NCE, Hassan Page 6

DBMS Laboratory BCS403

SET old_salary = OLD.SALARY;

SET new_salary = NEW.SALARY;

SET @my_sal_diff = CONCAT('salary difference after update is ', NEW.SALARY -

OLD.SALARY);

END;//

DELIMITER ;

---DELETE TRIGGER---

DELIMITER //

CREATE TRIGGER after_delete_salary_difference

AFTER DELETE ON CUSTOMERS

FOR EACH ROW

BEGIN

SET @my_sal_diff = CONCAT('salary deleted is ', OLD.SALARY);

END;//

DELIMITER ;

INSERT INTO CUSTOMERS VALUES (1,'Shankara', 35, '123 Main St', 50000.00);

SELECT @my_sal_diff AS SAL_DIFF;

UPDATE CUSTOMERS

SET SALARY = 55000.00

WHERE ID = 1;

SELECT @my_sal_diff AS SAL_DIFF;

DELETE FROM CUSTOMERS WHERE ID = 1;

SELECT @my_sal_diff AS SAL_DIFF;

Dept. of CSE, NCE, Hassan Page 7

DBMS Laboratory BCS403

PROGRAM 5

Create cursor for Employee table & extract the values from the table. Declare the
variables, Open the cursor & extract the values from the cursor. Close the cursor.

CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)

CREATE DATABASE COMPANY05;

USE COMPANY05;

CREATE TABLE Employee (E_id INT,

E_name VARCHAR(255),

Age INT,

Salary DECIMAL(10, 2));

INSERT INTO Employee (E_id, E_name, Age, Salary)VALUES

(1, 'Samarth', 30, 50000.00),

(2, 'Ramesh Kumar', 25, 45000.00),

(3, 'Seema Banu', 35, 62000.00),

(4, 'Dennis Anil', 28, 52000.00),

(5, 'Rehman Khan', 32, 58000.00);

DELIMITER //

CREATE PROCEDURE fetch_employee_data()

BEGIN

-- Declare variables to store cursor values

DECLARE emp_id INT;

DECLARE emp_name VARCHAR(255);

DECLARE emp_age INT;

DECLARE emp_salary DECIMAL(10, 2);

-- Declare a cursor for the Employee table --

DECLARE emp_cursor CURSOR FOR

Dept. of CSE, NCE, Hassan Page 8

DBMS Laboratory BCS403

SELECT E_id, E_name, Age, Salary FROM Employee;

-- Declare a continue handler for the cursor –

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET @finished = 1;

-- Open the cursor --

OPEN emp_cursor;

-- Initialize a variable to control cursor loop --

SET @finished = 0;

-- Loop through the cursor results --

cursor_loop: LOOP

-- Fetch the next row from the cursor into variables --

FETCH emp_cursor INTO emp_id, emp_name, emp_age, emp_salary;

-- Check if no more rows to fetch --

IF @finished = 1 THEN

LEAVE cursor_loop;

END IF;

-- Output or process each row (for demonstration, print the values)

SELECT CONCAT('Employee ID: ', emp_id, ', Name: ', emp_name, ', Age: ', emp_age, ',
Salary: ', emp_salary) AS Employee_Info;

END LOOP;

-- Close the cursor--

CLOSE emp_cursor;

END//

DELIMITER ;

-- To Execute Stored Procedure--

CALL fetch_employee_data();

Dept. of CSE, NCE, Hassan Page 9

DBMS Laboratory BCS403

PROGRAM 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.

CREATE DATABASE ROLLCALL;

USE ROLLCALL;

--Create N_RollCall table--

CREATE TABLE N_RollCall (student_id INT PRIMARY KEY,

student_name VARCHAR(255),

birth_date DATE);

-- Create O_RollCall table with common data--

CREATE TABLE O_RollCall (student_id INT PRIMARY KEY,

student_name VARCHAR(255),

birth_date DATE);

-- Insert common data into O_RollCall--

INSERT INTO O_RollCall (student_id, student_name, birth_date)VALUES

(1, 'Shivanna', '1995-08-15'),

(3, 'Cheluva', '1990-12-10');

-- Insert sample records into N_RollCall--

INSERT INTO N_RollCall (student_id, student_name, birth_date)VALUES

(1, 'Shivanna', '1995-08-15'), -- Common record with O_RollCall

(2, 'Bhadramma', '1998-03-22'),

(3, 'Cheluva', '1990-12-10'), -- Common record with O_RollCall

(4, 'Devendra', '2000-05-18'),

(5, 'Eshwar', '1997-09-03');

Dept. of CSE, NCE, Hassan Page 10

DBMS Laboratory BCS403

DELIMITER //

CREATE PROCEDURE merge_rollcall_data()

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE n_id INT;

DECLARE n_name VARCHAR(255);

DECLARE n_birth_date DATE;

-- Declare cursor for N_RollCall table--

DECLARE n_cursor CURSOR FOR

SELECT student_id, student_name, birth_date

FROM N_RollCall;

-- Declare handler for cursor--

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET done = TRUE;

-- Open the cursor--

OPEN n_cursor;

-- Start looping through cursor results--

cursor_loop: LOOP

-- Fetch data from cursor into variables

FETCH n_cursor INTO n_id, n_name, n_birth_date;

-- Check if no more rows to fetch--

IF done THEN

LEAVE cursor_loop;

END IF;

-- Check if the data already exists in O_RollCall IF NOT EXIST

(SELECT 1

Dept. of CSE, NCE, Hassan Page 11

DBMS Laboratory BCS403

FROM O_RollCall

WHERE student_id = n_id ) 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//

DELIMITER ;

-- To Execute Stored Procedure--

CALL merge_rollcall_data();

--Verify Records--

SELECT * FROM O_RollCall;

PROGRAM 7

Install an Open Source NoSQL Data base MongoDB & perform basic CRUD(Create, Read,
Update & Delete) operations. Execute MongoDB basic Queries using CRUD operations.

1. Installing Open Source NoSQL Data base MongoDB

2. Perform basic CRUD(Create, Read, Update & Delete) operations.

sudo systemctl start mongod

2. Start the MongoDB Shell

mongosh

3. Switch to a Database (Optional):

Dept. of CSE, NCE, Hassan Page 12

DBMS Laboratory BCS403

4. Create the ProgrammingBooks Collection:

5. INSERT operations

a. Insert 5 Documents into the ProgrammingBooks Collection :

bookDB> db.ProgrammingBooks.insertMany([

title: "Clean Code: A Handbook of Agile Software Craftsmanship",

author: "Robert C. Martin",

category: "Software Development",

year: 2008

},

title: "JavaScript: The Good Parts",

author: "Douglas Crockford",

category: "JavaScript",

year: 2008

},

title: "Design Patterns: Elements of Reusable Object-Oriented Software",

author: "Erich Gamma, Richard Helm, Ralph Johnson, John Vlissides",

category: "Software Design",

year: 1994

},

title: "Introduction to Algorithms",

Dept. of CSE, NCE, Hassan Page 13

DBMS Laboratory BCS403

author: "Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, Clifford Stein",

category: "Algorithms",

year: 1990

},

title: "Python Crash Course: A Hands-On, Project-Based Introduction to Programming",

author: "Eric Matthes",

category: "Python",

year: 2015

])

b. Insert a Single Document into ProgrammingBooks:

6. Read (Query) Operations

a. Find All Documents

bookDB> db.ProgrammingBooks.find().pretty()

_id: ObjectId('663eaaebae582498972202df'),

title: 'Clean Code: A Handbook of Agile Software Craftsmanship',

author: 'Robert C. Martin',

category: 'Software Development',

Dept. of CSE, NCE, Hassan Page 14

DBMS Laboratory BCS403

year: 2008

},

_id: ObjectId('663eaaebae582498972202e0'),

title: 'JavaScript: The Good Parts',

author: 'Douglas Crockford',

category: 'JavaScript',

year: 2008

},

_id: ObjectId('663eaaebae582498972202e1'),

title: 'Design Patterns: Elements of Reusable Object-Oriented Software',

author: 'Erich Gamma, Richard Helm, Ralph Johnson, John Vlissides',

category: 'Software Design',

year: 1994

},

_id: ObjectId('663eaaebae582498972202e2'),

title: 'Introduction to Algorithms',

author: 'Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, Clifford Stein',

category: 'Algorithms',

year: 1990

},

_id: ObjectId('663eaaebae582498972202e3'),

title: 'Python Crash Course: A Hands-On, Project-Based Introduction to Programming',

author: 'Eric Matthes',

Dept. of CSE, NCE, Hassan Page 15

DBMS Laboratory BCS403

category: 'Python',

year: 2015

},

_id: ObjectId('663eab05ae582498972202e4'),

title: 'The Pragmatic Programmer: Your Journey to Mastery',

author: 'David Thomas, Andrew Hunt',

category: 'Software Development',

year: 1999

b. Find Documents Matching a Condition

To find books published after the year 2000:

bookDB> db.ProgrammingBooks.find({ year: { $gt: 2000 } }).pretty()

_id: ObjectId('663eaaebae582498972202df'),

title: 'Clean Code: A Handbook of Agile Software Craftsmanship',

author: 'Robert C. Martin',

category: 'Software Development',

year: 2008

},

_id: ObjectId('663eaaebae582498972202e0'),

title: 'JavaScript: The Good Parts',

author: 'Douglas Crockford',

category: 'JavaScript',

Dept. of CSE, NCE, Hassan Page 16

DBMS Laboratory BCS403

year: 2008

},

_id: ObjectId('663eaaebae582498972202e3'),

title: 'Python Crash Course: A Hands-On, Project-Based Introduction to Programming',

author: 'Eric Matthes',

category: 'Python',

year: 2015

7. Update Operations

a. Update a Single Document

To update a specific book (e.g., change the author of a book):

bookDB>db.ProgrammingBooks.updateOne(

{ title: "Clean Code: A Handbook of Agile Software Craftsmanship" },

{ $set: { author: "Robert C. Martin (Uncle Bob)" } }

//verify by displaying books published in year 2008

bookDB> db.ProgrammingBooks.find({ year: { $eq: 2008 } }).pretty()

_id: ObjectId('663eaaebae582498972202df'),

title: 'Clean Code: A Handbook of Agile Software Craftsmanship',

author: 'Robert C. Martin (Uncle Bob)',

category: 'Software Development',

year: 2008

Dept. of CSE, NCE, Hassan Page 17

DBMS Laboratory BCS403

},

_id: ObjectId('663eaaebae582498972202e0'),

title: 'JavaScript: The Good Parts',

author: 'Douglas Crockford',

category: 'JavaScript',

year: 2008

b. Update Multiple Documents

To update multiple books (e.g., update the category of books published before 2010):

bookDB> db.ProgrammingBooks.updateMany(

{ year: { $lt: 2010 } },

{ $set: { category: "Classic Programming Books" } }

//verify the update operation by displaying books published before year 2010

bookDB> db.ProgrammingBooks.find({ year: { $lt: 2010 } }).pretty()

_id: ObjectId('663eaaebae582498972202df'),

title: 'Clean Code: A Handbook of Agile Software Craftsmanship',

author: 'Robert C. Martin (Uncle Bob)',

category: 'Classic Programming Books',

year: 2008

},

Dept. of CSE, NCE, Hassan Page 18

DBMS Laboratory BCS403

_id: ObjectId('663eaaebae582498972202e0'),

title: 'JavaScript: The Good Parts',

author: 'Douglas Crockford',

category: 'Classic Programming Books',

year: 2008

},

_id: ObjectId('663eaaebae582498972202e1'),

title: 'Design Patterns: Elements of Reusable Object-Oriented Software',

author: 'Erich Gamma, Richard Helm, Ralph Johnson, John Vlissides',

category: 'Classic Programming Books',

year: 1994

},

_id: ObjectId('663eaaebae582498972202e2'),

title: 'Introduction to Algorithms',

author: 'Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, Clifford Stein',

category: 'Classic Programming Books',

year: 1990

},

_id: ObjectId('663eab05ae582498972202e4'),

title: 'The Pragmatic Programmer: Your Journey to Mastery',

author: 'David Thomas, Andrew Hunt',

category: 'Classic Programming Books',

year: 1999

Dept. of CSE, NCE, Hassan Page 19

DBMS Laboratory BCS403

8. Delete Operations

a. Delete a Single Document

To delete a specific book from the collection (e.g., delete a book by title):

b. Delete Multiple Documents

To delete multiple books based on a condition (e.g., delete all books published before 1995):

c. Delete All Documents in the Collection:

9. Delete the Collection Using drop():

To delete a collection named ProgrammingBooks, use the drop() method with the name of
the collection:

Dept. of CSE, NCE, Hassan Page 20