Practice Lab

phpMyAdmin is a free, open-source tool used to

manage MySQL and MariaDB databases. It's a
web-based application that provides a graphical
user interface (GUI).

• SQL CREATE DATABASE Statement

Syntax:
CREATE DATABASE databasename;
Ex
CREATE DATABASE practiceDB;

• SQL DROP DATABASE Statement

Syntax:
DROP DATABASE databasename;
Ex
DROP DATABASE practiceDB;

• SQL CREATE TABLE Statement

Syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

Ex:
 CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

• SQL DROP TABLE Statement

Syntax:
DROP TABLE table_name;
Ex
DROP TABLE Persons;

• SQL TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table,
but not the table itself.

Syntax:

TRUNCATE TABLE table_name;

Ex:

TRUNCATE TABLE persons;

SELECT * FROM persons;

• SQL INSERT INTO Statement

Syntax:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

Ex

INSERT INTO Persons

VALUES (1, ‘Kumar’, ‘Ram’, ‘Vinobanagar’,'shimoga');
 INSERT INTO Persons
VALUES (2, ‘Mohan’, ‘Chandra’, ‘Teachers
layout’,'mysore');

SELECT * FROM persons;

Output:

 DESCRIBE TABLE
The DESCRIBE TABLE statement, also commonly
written as DESC TABLE or DESCRIBE, is
a MySQL command used to retrieve metadata about a
table.

Syntax:
DESC table_name;
Ex:
DESC persons;

Output:
• SELECT indicates which columns you'd like
to view, and FROM identifies the table that
they live in.
Ex:

SELECT PersonID, City

FROM persons;

Output:

• To select every column in a table, you can

use * instead of the column names:
Syntax:
SELECT *
FROM table_name;

Ex:

SELECT *
FROM persons;

SELECT *
FROM persons
WHERE Address = 'vinobanagar' AND City = 'shimoga';

Output:
 SELECT *
FROM persons
WHERE City = 'shimoga' OR City = 'shimga';

Output:

Ex
SELECT *
FROM persons
WHERE NOT City = 'Mysore';

 UPDATE
The UPDATE statement is used to update or
modify data in a table. It is used to change the
values in one or more columns of a single row or
multiple rows.
Syntax:
UPDATE table_name
SET column1=value1, column2=value2,...
WHERE condition;

EX: UPDATE persons

SET LastName='Mohan'
WHERE PersonID=2;

 DELETE
The DELETE statement is used to delete
existing records in a table.
Syntax:
 DELETE FROM table_name WHERE condition;
Ex
DELETE FROM persons WHERE Lastname= ‘Mohan’;
 DATABASE MANAGEMENT SYSTEM (BCS403)
Semester 4

PRACTICAL COMPONENT OF IPCC

Question 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
a. EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION and use
rollback.
b. 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:
CREATE TABLE Employee1 (
EMPNO INT,
ENAME VARCHAR (50),
A decimal number with a
JOB VARCHAR (50), max of total precision of 10
digits. Two (2) of them after
MANAGER_NO INT,
the decimal point and eight
SAL DECIMAL (10, 2), (8) before.
COMMISSION DECIMAL (10, 2)
);
DESC Employee;
1. Create a user and grant all permissions to the user.
Query:
Type these two queries in the current user login:
CREATE USER IF NOT EXISTS 'dbuser'@'localhost' IDENTIFIED BY ‘dbuser’;
GRANT ALL PRIVILEGES ON Employee TO 'dbuser'@'localhost';

Now logout and login with the new username as dbuser1 and password as dbuser1
Employee table should be visible in the new user dbuser1

***** Now logout and login with the old username and password for next set of queries***

2. Insert any three records in the employee table contains attributes EMPNO, ENAME, JOB,
MANAGER_NO, SAL, COMMISSION and use rollback.
Solution:
Go to home-> variable-> Edit-> set autocommit=OFF-> save

Go to SQL:
Start a new transaction with one insert operation:
START TRANSACTION;
INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,
COMMISSION) VALUES (1, 'Kavana Shetty', 'Manager', NULL, 5000.00, 1000.00);
*******COMMIT commits the current transaction, making its changes permanent.
COMMIT;
SELECT * FROM Employee;

Start another transaction with two insert operations:

START TRANSACTION;

********INSERT MORE RECORDS

INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,
COMMISSION)
VALUES (2, 'Ram Charan', 'Developer', 1, 4000.00, NULL);

INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,

COMMISSION)
VALUES (3, 'Honey Singh', 'Salesperson', 2, 3000.00, 500.00);

SELECT * FROM Employee;

DELETE FROM Employee where ENAME = 'Kavana Shetty';

SELECT * FROM Employee;

ROLLBACK rolls back the current transaction, cancelling its changes.

(ROLLBACK 1 DELETE AND 2 INSERT OPERATIONS)
ROLLBACK;
SELECT * FROM Employee;

Go to home -> variable -> Edit -> set autocommit= ON -> Save

3. Add primary key constraint and not null constraint to the employee table.
Solution:
Add Primary Key Constraint
ALTER TABLE Employee
ADD CONSTRAINT pk_employee PRIMARY KEY (EMPNO);
Verify primary key constraint
DESC Employee;

Also check it under structure

INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,

COMMISSION)
VALUES (1, 'Ranjan', 'Manager', NULL, 5000.00, 1000.00);

Duplicate entry '1' for key '[Link]'

Since EMPNO field is the primary key it cannot have duplicate values, hence we see that the
insert operation fails when provided with a duplicate value.

Add Not Null Constraint

ALTER TABLE Employee
MODIFY ENAME VARCHAR (255) NOT NULL,
MODIFY JOB VARCHAR (255) NOT NULL,
 MODIFY SAL DECIMAL (10, 2) NOT NULL;
DESC Employee;

INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,

COMMISSION)
VALUES (4, 'Ranjan', 'Manager', NULL, 5000.00, 1000.00);
SELECT * FROM Employee;

INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,

COMMISSION)
VALUES (7, NULL, 'Tester', NULL, 3500.00, NULL);
ERROR 1048 (23000): Column 'ENAME' cannot be null

Question 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 Employ table using alter command.
5. Delete the employee whose Empno is 105.
 Solution:
CREATE TABLE Employee2 (EMPNO INT, ENAME VARCHAR (50), JOB VARCHAR
(50), MGR INT, SAL DECIMAL (10, 2));
DESC Employee2;

1. Add a column commission with domain to the Employee table.

ALTER TABLE Employee ADD COLUMN COMMISSION DECIMAL (10, 2);
DESC Employee;

2. Insert any five records into the table.

INSERT INTO Employee2 VALUES (101,'Radha Bai', 'Manager', NULL, 5000.00, 1000.00);
INSERT INTO Employee2 VALUES (102,'Krishna Kumar', 'Developer', 101, 4000.00,
NULL);
INSERT INTO Employee2 VALUES (103, 'Abdul Sattar', 'Salesperson', 102, 3000.00, 500.00);
INSERT INTO Employee2 VALUES (104,'Bob Johnson', 'Accountant', 101,4500.00, NULL);
INSERT INTO Employee2 VALUES (105, 'Amartya Sen', 'HR Manager', 101, 4800.00, 800.00);
SELECT * FROM Employee2;

3. Update the column details of job

UPDATE Employee2 SET JOB = 'Senior Developer' WHERE EMPNO = 102;
SELECT * FROM Employee2;

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

ALTER TABLE Employee2 CHANGE COLUMN MGR MANAGER_ID INT;
DESC Employee2;

5. Delete the employee whose Empno is 105.

DELETE FROM Employee2 WHERE EMPNO = 105;
SELECT * FROM Employee2;

Question 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.
Solution:
1. Creating the Employee Table
CREATE TABLE Employee3 (E_id INT PRIMARY KEY, E_name VARCHAR (255), Age
INT, Salary DECIMAL (10, 2));
DESC Employee3;
O/P:

2. Insert 6 Records into the Employee Table

INSERT INTO Employee3 VALUES (1, 'Samarth', 30, 50000.00);
INSERT INTO Employee3 VALUES (2, 'Ramesh Kumar', 25, 50000.00);
INSERT INTO Employee3 VALUES (3, 'Seema Banu', 35, 60000.00);
INSERT INTO Employee3 VALUES (4, 'Dennis Anil', 28, 58000.00);
INSERT INTO Employee3 VALUES (5, 'Rehman Khan', 32, 58000.00);
INSERT INTO Employee3 VALUES (6, 'Pavan Gowda', 40, 70000.00);
SELECT * from Employee3;
O/P:
3. Count Number of Employee Names
SELECT COUNT(E_name) AS TotalEmployees
FROM Employee3;
O/P:

4. Find the Maximum Age

SELECT MAX(Age) AS MaxAge
FROM Employee3;
O/P:

5. Find the Minimum Age

SELECT MIN(Age) AS MinAge
FROM Employee3;
O/P:

6. Find Salaries of Employees in Ascending Order

SELECT E_name, Salary
FROM Employee3
 ORDER BY Salary ASC; //Also replace ASC by DESC for descending order
O/P:

7. Find Grouped Salaries of Employees

SELECT Salary, COUNT (*) AS EmployeeCount
FROM Employee3
GROUP BY Salary;
O/P:

In these queries:
• COUNT(E_name) counts the number of non-NULL values in the E_name column.
• MAX(Age) finds the maximum age among the employees.
• MIN(Age) finds the minimum age among the employees.
• ORDER BY Salary ASC sorts the employees based on their salaries in ascending
order.
• GROUP BY Salary groups employees by their salaries and counts the number of
employees for each salary.

Question 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)

Solution:-
1. Create CUSTOMERS Table
Query:
CREATE TABLE CUSTOMERS4 (
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (255),
AGE INT,
ADDRESS VARCHAR (255),
SALARY DECIMAL (10, 2)
);
DESC CUSTOMERS4;
Output:

2. Create Trigger for INSERT Operation

Query:
DELIMITER //
CREATE TRIGGER after_insert_salary_difference
AFTER INSERT ON CUSTOMERS4
FOR EACH ROW
BEGIN
SET @my_sal_diff = CONCAT('salary inserted is ', [Link]);
END;//
DELIMITER ;
3. Create Trigger for UPDATE Operation
Query:
DELIMITER //
CREATE TRIGGER after_update_salary_difference
AFTER UPDATE ON CUSTOMERS4
FOR EACH ROW
BEGIN
DECLARE old_salary DECIMAL(10, 2);
DECLARE new_salary DECIMAL(10, 2);
SET old_salary = [Link];
SET new_salary = [Link];
SET @my_sal_diff = CONCAT ('salary difference after update is ',
[Link] - [Link]);
END;//
DELIMITER ;

4. Create Trigger for DELETE Operation

Query:
DELIMITER //
CREATE TRIGGER after_delete_salary_difference
AFTER DELETE ON CUSTOMERS4
FOR EACH ROW
BEGIN
SET @my_sal_diff = CONCAT('salary deleted is ', [Link]);
END;//
DELIMITER ;
O/P:
5. Testing the Trigger:
[execute below two queries together]
Query:
INSERT INTO CUSTOMERS4 (NAME, AGE, ADDRESS, SALARY)
VALUES ('Shankara', 35, '123 Main St', 50000.00);
SELECT @my_sal_diff AS SAL_DIFF;

O/P:

[execute below two queries together]

Query:
UPDATE CUSTOMERS4
SET SALARY = 55000.00
WHERE ID = 1;
SELECT @my_sal_diff AS SAL_DIFF;

O/P:

[execute below two queries together]

Query:
DELETE FROM CUSTOMERS4
WHERE ID = 1;
SELECT @my_sal_diff AS SAL_DIFF;

O/P:

Question 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.
Employee (E_id, E_name, Age, Salary)

Solution:
1. Creating the Employee Table and insert few records

CREATE TABLE Employee (

E_id INT PRIMARY KEY AUTO_INCREMENT,
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);

2. Create a Stored Procedure with Cursor

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
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 check strored procedure, select the database (not the table) and then click on
Routines*******

3. Execute the Stored Procedure

CALL 'fetch_employee_data'();
Question 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, 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. Iterate through the records of N_RollCall and insert them into O_RollCall only if
they do not already exist.

1. Create the Tables

First, create the N_RollCall and O_RollCall tables with similar structure:
solution:
-- 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
);

2. Add Sample Records to both tables

-- 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');

3. Define the Stored Procedure

Define the merge_rollcall_data stored procedure to merge records from N_RollCall into
O_RollCall, skipping existing records:

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 EXISTS (

SELECT 1

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 ;

4. Execute the Stored Procedure

CALL 'merge_rollcall_data'();

5. Verify Records in O_RollCall

-- Select all records from O_RollCall

SELECT * FROM O_RollCall;

Question 7

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

Solution

1. Installing Open Source NoSQL Data base MongoDB

2. Perform basic CRUD (Create, Read, Update & Delete) operations.
1. Start MongoDB.
Launch the MongoDB daemon using the following command:
sudo systemctl start mongod

2. Start the MongoDB Shell

Launch the MongoDB shell to perform basic CRUD operations.
Mongosh

3. Switch to a Database (Optional):

If you want to use a specific database, switch to that database using
the use command. If the database doesn’t exist, MongoDB will create it implicitly
when you insert data into it:
test> use bookDB
switched to db bookDB
bookDB>

4. Create the ProgrammingBooks Collection:

To create the ProgrammingBooks collection, use the createCollection() method. This
step is optional because MongoDB will automatically create the collection when you
insert data into it, but you can explicitly create it if needed:

bookDB> [Link]("ProgrammingBooks")

5. INSERT operations

a. Insert 5 Documents into the ProgrammingBooks Collection :

Now, insert 5 documents representing programming books into the

ProgrammingBooks collection using the insertMany() method:

bookDB> [Link]([
{
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",
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:

Use the insertOne() method to insert a new document into the ProgrammingBooks
collection:
bookDB> [Link]({
title: "The Pragmatic Programmer: Your Journey to Mastery",
author: "David Thomas, Andrew Hunt",
category: "Software Development",
year: 1999
})

6. Read (Query) Operations

a. Find All Documents

To retrieve all documents from the ProgrammingBooks collection:

bookDB> [Link]().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',

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',

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