Q 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 theuser.
2. Insert the 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.

Create table Employee(Empno integer,ename varchar(20),job varchar(10),manager_no

integer,sal decimal(10,2),commission decimal(10,2));

1. Create the user

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

Grant all permissions to the user

GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost' WITH GRANT OPTION;

Flush privileges to apply changes

FLUSH PRIVILEGES;

'new_user' is the username you want to create.

'localhost' is the host from which the user can connect. You can replace it with '%' to allow
connections from any host, but be cautious with this option for security reasons.
'password' is the password for the new user. Make sure to use a strong password.
*.* specifies that you're granting all privileges on all databases and tables. You can limit this
to specific databases or tables if needed.
WITH GRANT OPTION allows the user to grant permissions to other users.
After executing these SQL commands, the user 'new_user' will have all permissions on all
databases and tables in your MySQL server.

2. -- Start a transaction
START TRANSACTION;

-- Insert three records into the employee table

INSERT INTO employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,
COMMISSION) VALUES
(1, 'John Doe', 'Manager', 0, 5000, 1000),
(2, 'Jane Smith', 'Developer', 1, 4000, NULL),
(3, 'Alice Johnson', 'Analyst', 1, 3500, 500);

-- Rollback the transaction

ROLLBACK;

Three records are being inserted into the employee table.

The START TRANSACTION command begins a transaction.
The INSERT INTO ... VALUES statement inserts three records into the employee table with
the specified attributes.
The ROLLBACK command rolls back the changes made within the transaction, effectively
undoing the INSERT statements.
After executing these SQL commands, the three records inserted into the employee table will
be rolled back, and the table will return to its state before the transaction started.

3. -- Adding primary key constraint

ALTER TABLE employee
ADD CONSTRAINT pk_employee_id PRIMARY KEY (empno);

-- Adding not null constraint

ALTER TABLE employee
MODIFY COLUMN ename VARCHAR(20) NOT NULL;

4. INSERT INTO employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,

COMMISSION) VALUES
(NULL, NULL, NULL, 0, 5000, 1000),
Q 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.

create table Employee(Empno integer,ename varchar(20),job varchar(10),manager_no

integer,sal number(10,2),commission number(10,2));

1. ALTER TABLE employee ADD commission decimal(10, 2) DEFAULT 0.00 NOT

NULL;

commission is the name of the new column.

DECIMAL(10, 2) specifies the data type for the commission column. It means that the
column will store decimal numbers with a precision of 10 digits, where 2 digits are reserved
for the fractional part.
DEFAULT 0.00 sets the default value for the column to 0.00 if no value is provided during
insertion.
NOT NULL constraint ensures that the column cannot contain NULL values.

Note: MySQL does not support defining domains directly. Instead, you specify the data type
and any constraints associated with the column directly in the ALTER TABLE statement.

2. INSERT INTO employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,

COMMISSION) VALUES
(4, 'Jay', 'Developer', 0, 5000, 1000),
(5, 'Ram', 'Manager', 1, 4000, NULL),
(6, 'Steven', 'Analyst', 1, 3500, 500);
(7, 'Rahim', 'Architect', 1, 4000, NULL),
(8, 'Robert', 'Project Lead', 1, 3500, 500);

3. ALTER TABLE employee MODIFY COLUMN job VARCHAR(100);

Job is a column in the employee table, and we are changing it's data type to
VARCHAR(100)

4. ALTER TABLE employee CHANGE sal salary decimal(10,2);

5. DELETE FROM employee WHERE Empno = 105;

Q 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 employeetable
3. Find the Maximum age from employee table.
4. Find the Minimum age from employeetable.
5. Find salaries of employee in Ascending Order.
Find grouped salaries of employees.

1. Create Table Employee(E_id int, E_name varchar(20), Age integer, Salary decimal(10,2));

Note: Insert values for Employee table for minimum of 5 rows, before executing the
following queries.

Insert into Employee values(4, 'Jay', 28,25000)

Insert into Employee values(5, 'John', 38,35000)
Insert into Employee values(6, 'Jabbar', 24,65000)
Insert into Employee values(7, 'Somu', 48,29000)
Insert into Employee values(8, 'Smith', 25,24000)

2.SELECT COUNT(E_id) AS total_employee FROM employee;

total_employee
5

3. SELECT Max(Age) AS max_age FROM employee;

max_age
48

4. SELECT Min(Age) AS min_age FROM employee;

min_age
24

5. SELECT E_name, salary FROM employee ORDER BY salary ASC;

Output
E_name Salary
Smith 24000
Jay 25000
Somu 29000
John 35000
Jabbar 65000

6. SELECT salary,count(*) FROM employee GROUP BY salary;

Salary count(*)
24000 1
25000 1
29000 1
35000 1
Salary count(*)
65000 1
Q 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)

Crate table using the following for creating table and inserting values, before executing the
query.

Create table CUSTOMERS(ID int primary key,NAME varchar(20) NOT NULL,AGE

int,ADDRESS varchar(30),SALARY decimal(10,2));

Insert into CUSTOMERS values(12,'ram',25,'jp nagar bangalore',25000);

Insert into CUSTOMERS values(13,'rahim',35,'rr nagar mangalore',40000);
Insert into CUSTOMERS values(14,'robert',29,'sm nagar tumkur',35000);

1. CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff decimal(10,2);
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
Q 5) Create cursor for Employee table & extract the values from the table. Declare the
variables ,Open the cursor & extrct the values from the cursor. Close the cursor.
Employee(E_id, E_name, Age, Salary)

Crate table using the following for creating table and inserting values, before executing the
query.

Create Table Employee(E_id int, E_name varchar(20), Age integer, Salary decimal(10,2));

Insert into Employee values((4, 'Jay', 25,26000);

Insert into Employee values((5, 'Kar', 35,29000);
Insert into Employee values((6, 'Sta', 36,36000);
Insert into Employee values((7, 'Uday', 28,46000);

1. DECLARE
z_empid Employee.E_id%TYPE;
z_empname Employee.E_name%TYPE;
z_age Employee.Age%TYPE;
z_salary Employee.Salary%TYPE;
CURSOR employee_cursor IS -- declaring a cursor
SELECT E_id,
E_name,
Age,
Salary
FROM Employee;

BEGIN
OPEN employee_cursor; -- opening the cursor
LOOP
FETCH employee_cursor -- fetching records from the cursor
INTO z_empid,
z_empname,
z_age
z_salary;
EXIT
WHEN employee_cursor%NOTFOUND;
SELECT CONCAT('Employee ID: ', E_id, ', Name: ', e_name, ', Age: ', age, ', Salary: ',
salary) AS employee_info;
END LOOP;
CLOSE employee_cursor; --closing the cursor
END;
/

-- Close the cursor

CLOSE emp_cursor;
Q 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.

DELIMITER //

CREATE PROCEDURE merge_roll_call_data()

BEGIN
-- Declare variables
DECLARE n_id INT;
DECLARE n_name VARCHAR(100);
DECLARE o_id INT;
DECLARE o_name VARCHAR(100);

-- Declare and open cursor for N_RollCall table

DECLARE n_cursor CURSOR FOR
SELECT id, name
FROM N_RollCall;
OPEN n_cursor;

-- Loop through N_RollCall table

n_loop: LOOP
-- Fetch data from N_RollCall table
FETCH n_cursor INTO n_id, n_name;
IF (n_id IS NULL) THEN
LEAVE n_loop;
END IF;

-- Check if data already exists in O_RollCall table

SELECT id INTO o_id FROM O_RollCall WHERE id = n_id;

-- If data doesn't exist, insert into O_RollCall table

IF (o_id IS NULL) THEN
INSERT INTO O_RollCall (id, name) VALUES (n_id, n_name);
END IF;
END LOOP;

-- Close cursor
CLOSE n_cursor;

END //

In this stored procedure:

We declare variables to store data retrieved from the N_RollCall and O_RollCall tables.
We declare a cursor (n_cursor) to loop through the N_RollCall table.
Inside the loop, we fetch data from N_RollCall and check if the same data exists in
O_RollCall. If it doesn't, we insert it into O_RollCall.
The cursor is closed at the end of the procedure.
Q 7) Install an Open Source NoSQL Data base MangoDB & perform basic CRUD(Create,
Read, Update & Delete) operations. Execute MangoDB basic Queries using CRUD
operations.

Prerequisites to Install MongoDB

It would help to have a few things in place before installing MongoDB, such as Operating
System Compatibility, Disc Space, and Administrative Privilege. As per the official
documentation of MongoDB, the recommended system for Installing MongoDB is Windows
Server 2016 and 2019.

Also, If you have an Intel computer, it should be at least a Sandy Bridge Core processor, a
later version, or a Tiger Lake Celeron or Pentium processor. It would help to have a
Bulldozer processor or a later version for AMD computers.

How to Install MongoDB on Windows:

Step 1: Visit the official MongoDB website, https://www.mongodb.com/, to download
MongoDB to your system. Now, select the products tab. Then, navigate to the ‘community
edition’ item.

Step 2: You need to select a specific version, platform, and package per the project
requirement. For Windows, we must choose Version 7.0.0 or current, Platform Windows OS
Platform, and MSI Package as demonstrated below. you can click the download button now.
It allows you to download the setup.

Step 3: Navigate to the installer now. This is where the setup is downloaded. Double-click on
the MSI file to start the installation. The Installation Screen appears as shown below. After
this, click on the next option.

Step 4: An installation wizard should pop up as depicted below. Read the End-User License
Agreement, accept the terms and conditions, and select the “Next” button.

Step 5: Now, select the Complete or Custom setup type. Select the complete setup option,
which installs MongoDB at the default location. Select Complete Setup and then click the
"Next" option as outlined below.

Step 6: Click on the “Install MongoD as a Service” button and Select the “Next” option as
captured below.

Step 7: After this, you will be given the option of installing MongoDB Compass. Uncheck
the box if you do not want it installed on your device, and click the “Next” button to proceed
further.

Step 8: Now allow administrator access and click the “Install” button to start the MongoDB
Installation as shown below.

Step 9: The installation of MongoDB will start as outlined below.

Step 10: After the Installation, click the ‘Finish’ button to close the installer screen.

]
MongoDB Shell Installation on Windows

After Installing MongoDB, we will install its Shell that interacts with MongoDB
deployments. It must be installed separately, as the MongoDB installer does not include it.
Follow the below steps to install MongoDB Shell on Windows.

Step 1: Get the official website https://www.mongodb.com/try/download/shell to download

MongoDB Shell on your system. Select the platform’s Windows 64-bit (8.1+) option and
click the ‘Download’ button.

Step 2: You can extract the downloaded zip file and store it in the desired location. You will
locate the bin folder, as shown in the picture below in the next step.

Step 3: Click on the ‘mongosh’ file to launch the MongoDB shell. The Shell would appear as
depicted below:

Environment Variable Setup

We must add the MongoDB path as an environment variable before running the Server. The
steps to set the environment variable are given below:

Step 1: Navigate to the MongoDB installation directory on “C Drive.” Go to the Server> 7.0
> bin folder. Now, you can create a copy of the path as shown below.

Step 2: Next. you can navigate to the windows search. You can type ‘environment variable’
into the search box. After that, choose the ‘Edit environment variables for your account’ item.

Step 3: Now you can select the environment variables in the advanced settings as shown
below:

Step 4: Double-click the “Path” option in the system variable.

Step 5: Select “New” to set a new path variable. After this, paste the copied path here, then
click “OK.” The environmental variables have been added successfully.

Step 6: You can run the MongoDB local server from any path in your computer’s terminal.
After you’ve added the environment variable to the system, use the Command Prompt to run
the ‘mongod - -version‘ command to see if MongoDB is appropriately installed. It should
produce the following result:

MongoDB Installation FAQs

1. How do I install MongoDB Compass?

Starting with MongoDB 4.0, you can install MongoDB as a Windows service or simply the
binaries. Optional. Select Install MongoDB Compass (Default) to have the wizard install
MongoDB Compass. When you’re finished, click Install. Mongosh is not included in the.msi
installer.

2. Does MongoDB support 32-bit x86 platforms?

No, 32-bit x86 platforms are not supported by MongoDB. It stopped supporting 32-bit
platforms with MongoDB 3.2. MongoDB supports a64bit x86 processor or its equivalent.
3. If a MongoDB database does not exist in your system, what would be the result?

MongoDB creates a database if a MongoDB database doesn't exist. Similarly, MongoDB

creates collections if collections don't exist. You can use a non-existent database and execute
the related operations.

4. Does MongoDB require a predefined schema?

MongoDB typically doesn't need a specific schema to store data. It allows you to change the
schema without creating any new database.

5. How can I start a MongoDB instance?

First, go to the MongoDB installation directory. Then execute the ‘mongod’ command to
initialize the mongoDB server. After that, you can use the –dbpath flag to indicate if the data
directory is not in the default location.

Conclusion
MongoDB provides a versatile and scalable solution for a variety of data needs. It has
changed the database environment with its flexible features. This article should have given
you useful insights on mongoDB. You have also learned how to install MongoDB on your
system. If you desire to explore more about MongoDB, you can sign up for a MindMajix's
MongoDB training. You will get certification and stay ahead in the job market.

CRUD Operations

MongoDB is a popular document-oriented NoSQL database that allows users to store and
manipulate data in a flexible, schema-less manner. One of the core functionalities of
MongoDB is its ability to perform CRUD (Create, Read, Update, and Delete) operations
on documents in its collections. In this article, we will provide a beginner’s guide to
performing CRUD operations in MongoDB, with examples for each operation.

Creating and Inserting Documents

To create a new document in MongoDB, we first need to define the structure of the
document. In MongoDB, documents are represented as JSON-like objects, with fields and
values.

For example, let’s say we want to create a new document to represent a user. We could define
the structure of the document like this:

{
"name": "John Smith",
"email": "[email protected]",
"age": 32
}
To insert this document into a MongoDB collection, we would use the insertOne() method.
Here's an example:

db.users.insertOne({
"name": "John Smith",
"email": "[email protected]",
"age": 32
})
This would create a new document in the users collection with the specified fields and values.

Reading Documents
To read documents from a MongoDB collection, we use the find() method. This method
returns a cursor to the documents that match the specified query. For example, to find all
documents in the users collection, we could run:

db.users.find()
This would return a cursor to all documents in the users collection.

We could also specify a query to filter the results. For example, to find all users with an age
greater than 30, we could run:

db.users.find({ "age": { $gt: 30 } })

This would return a cursor to all documents in the users collection where the age field is
greater than 30.

Updating Documents
To update a document in MongoDB, we use the updateOne() method. This method takes two
parameters: a query to match the document to be updated, and an update object to specify the
changes to be made. For example, to update the email address of a user with the name "John
Smith", we could run:

db.users.updateOne(
{ "name": "John Smith" },
{ $set: { "email": "[email protected]" } }
)
This would update the email address of the first document in the users collection where the
name field is "John Smith".

Deleting Documents
To delete a document in MongoDB, we use the deleteOne() method. This method takes a
query to match the document to be deleted. For example, to delete the first document in the
users collection where the name field is "John Smith", we could run:

db.users.deleteOne({ "name": "John Smith" })

This would delete the first document in the users collection where the name field is "John
Smith".