8.

Exercise on GROUP BY, HAVING

Aim:
Table: Employees
EmployeeID Name Department Salary Age
1 Alice HR 60000 30
2 Bob IT 80000 35
3 Charlie IT 75000 28
4 David HR 65000 45
5 Eve Sales 70000 29
6 Frank Sales 72000 34
7 Grace IT 90000 41
Table: Sales
SaleId EmployeeID SaleAmount SaleDate
1 5 15000 2024-08-01
2 6 12000 2024-08-02
3 5 17000 2024-08-05
4 6 11000 2024-08-07
5 5 14000 2024-08-09
6 6 16000 2024-08-10

A) To Write a query to find the total salary paid for each department.
B) To Write a query to find departments with a total salary of more than 150,000.

Descrition :
GROUP BY :

The GROUP BY clause in SQL is used to arrange identical data into groups. This
clause is often used in conjunction with aggregate functions like COUNT(), SUM(),
AVG(), MAX(), and MIN() to perform calculations on each group of data.
Syntax :
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

HAVING :
The HAVING clause is used to filter groups created by the GROUP BY clause. It
allows you to specify conditions that filter which groups will appear in the final result
set. It is similar to the WHERE clause but is applied after the GROUP BY operation,
thus working on aggregated data.
Syntax :
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition;

A) To Write a query to find the total salary paid for each department.
Sql query -> SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;
Output :
Department TotalSalary
HR 125000
IT 245000
Sales 142000

B) TO Write a query to find departments with a total salary of more than 150,000.
Sql query-> SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 150000;
Output :
Department TotalSalary
IT 245000

Result :
The above sql queries are executed successfully.
9. Exercise on number functions, character functions , conversion functions and
date functions and group functions.

Aim :

Employees

EmployeeID Name Department Salary DateOfBirth JoiningDate

1 Alice HR 60000 1994-04-23 2020-01-15
2 Bob IT 80000 1989-07-10 2018-05-21
3 Charlie IT 75000 1996-03-11 2021-03-01
4 David HR 65000 1981-08-09 2017-09-25
5 Eve Sales 70000 1993-12-29 2019-12-01
6 Frank Sales 72000 1987-02-14 2020-06-18
7 Grace IT 90000 1983-11-19 2016-08-08

1. Numeric Function
A) TO Write a query to round the salary of each employee to the nearest
thousand.
B) TO Write a query to find the absolute difference between the maximum and
minimum salaries.
2. Character Function
C) TO Write a query to convert all employee names to uppercase.
D) TO Write a query to concatenate the first 3 characters of the employee’s
name with their department.
3. Conversion Functions
E) TO Write a query to convert the salary to a character string and concatenate
it with the employee's name.
4. Date Functions
F) TO Write a query to find the age of each employee based on their Date of
Birth.
5. Group Functions
G) TO Write a query to find the total number of employees in each department.

Description:
Numeric Functions:
Numeric functions perform operations on numeric data types and return numeric
values.

ABS(): Returns the absolute value of a number.

o Syntax: ABS(number)

ROUND(): Rounds a number to a specified number of decimal places.

o Syntax: ROUND(number, decimal_places)

 FLOOR(): Returns the largest integer less than or equal to a number.

o Syntax: FLOOR(number)

CEILING(): Returns the smallest integer greater than or equal to a number.

o Syntax: CEILING(number)

A) To Write a query to round the salary of each employee to the nearest

thousand.
SQL Query -> SELECT Name, Salary, ROUND(Salary, -3) AS RoundedSalary
FROM Employees;
Output :

Name Salary RoundedSalary

Alice 60000 60000
Bob 80000 80000
Charlie 75000 75000
David 65000 65000
Eve 70000 70000
Frank 72000 72000
Grace 90000 90000

B) To Write a query to find the absolute difference between the maximum and
minimum salaries.
SQL Query ->
SELECT ABS(MAX(Salary) - MIN(Salary)) AS SalaryDifference
FROM Employees;
Output :
SalaryDifference
30000

Character Functions:
Character functions operate on character strings and return strings or numbers.

 CONCAT(): Concatenates two or more strings.

o Syntax: CONCAT(string1, string2, ...)
 LENGTH(): Returns the length of a string.
o Syntax: LENGTH(string)
 SUBSTRING(): Extracts a substring from a string.
o Syntax: SUBSTRING(string, start_position, length)
 UPPER(): Converts a string to uppercase.
o Syntax: UPPER(string)
 LOWER(): Converts a string to lowercase.
o Syntax: LOWER(string)
 TRIM(): Removes leading and trailing spaces from a string.
o Syntax: TRIM(string)
C) To Write a query to convert all employee names to uppercase.
SQL -> SELECT UPPER(Name) AS UpperCaseName FROM Employees;
Output :
UpperCaseName
ALICE
BOB
CHARLIE
DAVID
EVE
FRANK
GRACE

D) To Write a query to concatenate the first 3 characters of the employee’s name

with their department.
SQL -> SELECT Name, Department, CONCAT(SUBSTRING(Name, 1, 3),
Department) AS NewCode FROM Employees;

Output:

Name Department NewCode

Alice HR AliHR
Bob IT BobIT
Charlie IT ChaIT
David HR DavHR
Eve Sales EveSales
Frank Sales FraSales
Grace IT GraIT

Date Functions:
Date functions operate on date values and return date or numeric values.

 NOW(): Returns the current date and time.

o Syntax: NOW()
 DATEADD(): Adds a specified time interval to a date.
o Syntax: DATEADD(interval, number, date)
 DATEDIFF(): Returns the difference between two dates.
o Syntax: DATEDIFF(interval, date1, date2)
 DATEPART(): Returns a specific part of a date.
o Syntax: DATEPART(part, date)
 FORMAT(): Formats a date according to the specified format.
o Syntax: FORMAT(date, format)

E) To Write a query to find the age of each employee based on their Date of
Birth.
SQL -> SELECT Name,

TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE()) AS Age

FROM Employees;

Output :

Name Age
Alice 30
Bob 35
Charlie 28
David 43
Eve 30
Frank 37
Grace 40

Conversion Functions:
Conversion functions convert a value from one data type to another.

 CAST(): Converts one data type to another.

o Syntax: CAST(expression AS target_data_type)
 CONVERT(): Converts a value from one data type to another.
o Syntax: CONVERT(target_data_type, expression)

F) To Write a query to convert the salary to a character string and concatenate it

with the employee's name.
SQL ->
SELECT Name, CAST(Salary AS CHAR) AS SalaryAsText,
CONCAT(Name, ' earns ', CAST(Salary AS CHAR)) AS SalaryStatement
FROM Employees;

Output:

Name SalaryAsText SalaryStatement

Alice 60000 Alice earns 60000
Bob 80000 Bob earns 80000
Charlie 75000 Charlie earns 75000
David 65000 David earns 65000
Eve 70000 Eve earns 70000
Frank 72000 Frank earns 72000
Grace 90000 Grace earns 90000
Group Functions

Group functions operate on a group of rows and return a single value for the group.
These functions are often used with the GROUP BY clause.

 COUNT(): Returns the number of rows.

o Syntax: COUNT(column_name)

 SUM(): Returns the sum of values in a numeric column.

o Syntax: SUM(column_name)

 AVG(): Returns the average value of a numeric column.

o Syntax: AVG(column_name)

 MAX(): Returns the maximum value of a column.

o Syntax: MAX(column_name)

 MIN(): Returns the minimum value of a column.

o Syntax: MIN(column_name)

H) To Write a query to find the total number of employees in each department.

SQL -> SELECT Department, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY Department;
Output ;
Departmen
NumEmployees
t
HR 2
IT 3
Sales 2

Result :
The above sql queries are executed successfully.
10. Exercise on set Operators

Aim : To Perform the set operators in sql.

Employees_2023 Table:

EmployeeI
Name Department
D
1 Alice HR
2 Bob IT
3 Charlie Sales

Employees_2024 Table:

EmployeeI
Name Department
D
2 Bob IT
3 Charlie Sales
4 David Marketing

Description :

Set Operators

Set Operators in SQL are used to combine the results of two or more SELECT
queries.

Syntax:

SELECT column1, column2, ...

FROM table1

Set_operator

SELECT column1, column2, ...

FROM table2;

UNION:

Combines the result sets of two or more SELECT queries, removing duplicate
rows from the final result.

SQL ->
SELECT Name, Department
FROM Employees_2023
UNION
SELECT Name, Department
FROM Employees_2024;

Output:

Name Department
Alice HR
Bob IT
Charlie Sales
David Marketing

UNION ALL

Combines the result sets of two or more SELECT queries, including all duplicates.

SQL Query->

SELECT Name, Department FROM Employees_2023

UNION ALL SELECT Name, Department FROM Employees_2024;

Output:

Name Department
Alice HR
Bob IT
Charlie Sales
Bob IT
Charlie Sales
David Marketing

INTERSECT

Returns only the rows that are common to both result sets.

SQL Query->

SELECT Name, Department FROM Employees_2023

INTERSECT SELECT Name, Department FROM Employees_2024;

Output:

Name Department
Name Department
Bob IT
Charlie Sales

EXCEPT (or MINUS)

Returns the rows from the first result set that are not present in the second result set.

SQL Query ->

SELECT Name, Department FROM Employees_2023

EXCEPT SELECT Name, Department FROM Employees_2024;

Output:

Nam
Department
e
Alice HR

Result :
The above sql queries are executed successfully.
11. Exercise on sub queries

Employees

EmployeeI
Name Department Salary DateOfBirth JoiningDate
D
1 Alice HR 60000 1994-04-23 2020-01-15
2 Bob IT 80000 1989-07-10 2018-05-21
3 Charlie IT 75000 1996-03-11 2021-03-01
4 David HR 65000 1981-08-09 2017-09-25
5 Eve Sales 70000 1993-12-29 2019-12-01
6 Frank Sales 72000 1987-02-14 2020-06-18
7 Grace IT 90000 1983-11-19 2016-08-08

A) Find the name of the employee with the highest salary.

B) List the names of employees who work in the same department as 'Bob'.
C) Find the average salary of all employees and then list employees who earn more
than this average.
D) Find employees whose salary is above the average salary in their department.

Subqueries

Subqueries are SQL queries nested inside a larger query (such as SELECT, INSERT,
UPDATE, or DELETE statement). The result of the subquery can be used as a
condition in the main query or as an intermediate result.

Types of Subqueries

 Single-row Subquery: Returns only one row.

 Multiple-row Subquery: Returns more than one row.
 Scalar Subquery: Returns a single value.
 Correlated Subquery: A subquery that references columns from the outer
query.

Syntax

SELECT column1, column2, ...FROM table_name

WHERE column_name = (SELECT column_name FROM another_table WHERE
condition);

Single-row Subquery

A) To Find the name of the employee with the highest salary.

SQL Query:

SELECT NameFROM Employees

WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Output:

Name
Grace

Multiple-row Subquery

B) To List the names of employees who work in the same department as 'Bob'.

SQL Query:

SELECT NameFROM Employees

WHERE DepartmentID = (SELECT DepartmentID FROM Employees WHERE
Name = 'Bob');

Output:

Name
Bob
Eve

Scalar Subquery

C) To Find the average salary of all employees and then list employees who earn
more than this average.

SQL Query:

SELECT Name, SalaryFROM Employees

WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Output:

Name Salary
Bob 80000
Charlie 75000
Grace 90000
Frank 72000

Correlated Subquery
D) To Find employees whose salary is above the average salary in their department.

SQL Query:

SELECT Name, SalaryFROM Employees E1

WHERE Salary > (SELECT AVG(Salary) FROM Employees E2 WHERE
E1.DepartmentID = E2.DepartmentID);

Output:

Nam
Salary
e
Bob 80000
Grace 90000

Result :
The above sql queries are executed successfully.
12. Exercise on Joins

Aim : Perform the joins operation on below tables

Employees Table:

EmployeeID Name DepartmentID

1 Alice 101
2 Bob 102
3 Charlie 103
4 David 104
5 Eve 102

Departments Table:

DepartmentID DepartmentName
101 HR
102 IT
103 Sales
105 Marketing

Description:
Joins

Joins are used to combine rows from two or more tables based on a related column.
There are 4 types of joins. They are:

 Inner Join
 Left Join
 Right Join
 Outer Join

Syntax :

SELECT columns FROM table1

joins table2 ON table1.column_name = table2.column_name;

Inner Join

An Inner Join returns records that have matching values in both tables involved in
the join. If there is no match, the record is not included in the result set.

SQL Query:
SELECT Employees.Name, Departments.DepartmentName FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

Name DepartmentName
Alice HR
Bob IT
Charlie Sales
Eve IT

2. Left Join

A Left Join returns all records from the left table (table1), and the matched records
from the right table (table2). If there is no match, the result is NULL on the right side.

SQL Query:

SELECT Employees.Name, Departments.DepartmentName

FROM EmployeesLEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Expected Output:

Name DepartmentName
Alice HR
Bob IT
Charlie Sales
David NULL
Eve IT

3. Right Join

A Right Join returns all records from the right table (table2), and the matched records
from the left table (table1). If there is no match, the result is NULL on the left side.

SQL Query:

SELECT Employees.Name, Departments.DepartmentName FROM Employees

RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:
Name DepartmentName
Alice HR
Bob IT
Charlie Sales
Eve IT
NULL Marketing

4. Full (Outer) Join

A Full Join returns all records when there is a match in either left (table1) or right
(table2) table. If there is no match, the result is NULL from the table with no match.

SQL Query:

SELECT Employees.Name, Departments.DepartmentName FROM Employees

OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Output:

Name DepartmentName
Alice HR
Bob IT
Charlie Sales
David NULL
Eve IT
NULL Marketing

Result :
The above sql queries are executed successfully.
13. Exercise on various date and number Format models

Aim :

A) To Display the current date in the format DD-MON-YYYY HH24:MI:SS.

B) TO Display employee IDs with leading zeros in a 5-digit format.

Description :

Date Format Models: Used to control the display of date and time values in SQL.
They define how date and time values are converted between their internal
representations and textual output.
· YYYY: 4-digit year.
· MM: 2-digit month.
· DD: 2-digit day.
· YYYY-MM-DD: ISO date format.
· HH:MI AM/PM: 12-hour time format with AM/PM.

Number Format Models: Used to control the display of numeric values in SQL.
They define how numbers are represented in textual output, including the use of
decimal points, commas, currency symbols, etc.
· 9: Represents a digit.
· 0: Represents a digit; displays leading zeros.
· .: Decimal point.
· ,: Group separator (e.g., thousands separator).

A) To Display the current date in the format DD-MON-YYYY HH24:MI:SS.

SQL Query:

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS

FormattedDate FROM DUAL;

Expected Output:

FormattedDate
30-AUG-2024 14:35:22

B) To Display employee IDs with leading zeros in a 5-digit format.

SQL Query:

SELECT TO_CHAR(EmployeeID, '00000') AS FormattedEmployeeID

FROM Employees;

Output:

FormattedEmployeeID
FormattedEmployeeID
00001
00002
00003

Result :
The above sql queries are executed successfully.
14. Exercise on creating tables with integrity constraints.

Aim : To Create an Employees table with a unique constraint on the Email column
and a check constraint ensuring that Salary is greater than 30000.

Description:

Integrity Constraints

Integrity Constraints are rules applied to database tables to ensure the accuracy and
consistency of the data. These constraints enforce certain conditions on data in the
database, preventing invalid data entry and maintaining relationships between tables.

Types of Integrity Constraints

 PRIMARY KEY: Ensures that each row in a table is unique and not null.
 FOREIGN KEY: Enforces a link between two tables, ensuring that a value in
one table corresponds to a value in another.
 UNIQUE: Ensures that all values in a column are unique across the table.
 NOT NULL: Ensures that a column cannot have a null value.
 CHECK: Ensures that all values in a column satisfy a specific condition.
 DEFAULT: Assigns a default value to a column when no value is specified.

Syntax :

CREATE TABLE table_name (

column1 datatype constraint1,

column2 datatype constraint2,

...

PRIMARY KEY (column_name),

FOREIGN KEY (column_name) REFERENCES other_table (column_name),

UNIQUE (column_name),

CHECK (condition)

);

SQL Query:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Salary DECIMAL(10, 2) CHECK (Salary > 30000)
);

Output:

The Employees table is created with a unique constraint on Email to prevent duplicate
email addresses, and a check constraint on Salary to ensure it is greater than 30,000.

Result :
The above sql queries are executed successfully.
15. Write programs usnig Pl/SQL control statements

15a) IF-THEN-ELSE Statement

Aim : to Write a PL/SQL program checks if a number is positive, negative, or

zero by using IF-THEN-ELSE Statement

Description:

Executes one block of code if the condition is true, and another block if it is false.

Syntax :

IF condition THEN

-- statements to execute if the condition is true

ELSE

-- statements to execute if the condition is false

END IF;

PL/SQL Program:

DECLARE
num NUMBER := 10; -- Initialize the numberBEGIN
IF num > 0 THEN
DBMS_OUTPUT.PUT_LINE('The number is positive.');
ELSIF num < 0 THEN
DBMS_OUTPUT.PUT_LINE('The number is negative.');
ELSE
DBMS_OUTPUT.PUT_LINE('The number is zero.');
END IF;END;

Output:

The number is positive.

Result :
The Above programs are executed successfully.
15b)CASE Statement

Aim : To write a PL/SQL program assigns a grade using the CASE statement.

Description: The CASE statement is used when you have multiple possible
conditions and to execute a specific block of code based on the value of an
expression.

Syntax:

grade := CASE
WHEN condition THEN stmts
WHEN condition THEN stmts
ELSE stmts
END;

PL/SQL Program:

DECLARE
score NUMBER := 85;
grade CHAR(1);BEGIN
grade := CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
WHEN score >= 60 THEN 'D'
ELSE 'F'
END;

DBMS_OUTPUT.PUT_LINE('The grade is: ' || grade);END;

Output:

The grade is: B

15c) LOOP Statement

Aim : To write a PL/SQL program calculates the factorial of a number using a

simple loop.

Description: Conditional control can also be applied within loops to control the flow
of iteration. The EXIT WHEN statement is used to exit a loop when a certain
condition is met.

Syntax :

LOOP
Executable statements
EXIT
END LOOP;

PL/SQL Program:

DECLARE
num NUMBER := 5;
factorial NUMBER := 1;
i NUMBER := 1;BEGIN
LOOP
factorial := factorial * i;
i := i + 1;
EXIT WHEN i > num;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Factorial of ' || num || ' is: ' || factorial);END;/

Output:

Factorial of 5 is: 120

Result :
The Above programs are executed successfully.
16. Exercise on Procedures

Aim: To Create a procedure that divides two numbers and handles division-by-zero
exceptions.

Description: A PL/SQL procedure is a stored subprogram that performs a specific

task. Procedures are used to encapsulate reusable code that can be executed by calling
the procedure name. They can accept parameters and return results.

Syntax for Creating a Procedure

CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter_name parameter_mode parameter_data_type [, ...]) ]
IS
-- Declarations
BEGIN
-- Executable statements
EXCEPTION
-- Exception handlers (optional)
END procedure_name;

PL/SQL Program:

CREATE OR REPLACE PROCEDURE DivideNumbers(

p_numerator IN NUMBER,
p_denominator IN NUMBER
) IS
result NUMBER;
BEGIN
IF p_denominator = 0 THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
ELSE
result := p_numerator / p_denominator;
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');END;/

Calling procedure :

BEGIN
DivideNumbers(10, 2);
DivideNumbers(10, 0);END;

Output:

Result: 5
Error: Division by zero is not allowed.
Result :
The Above programs are executed successfully.
17. Exercise on Functions

Aim: To Create a function that calculates the annual salary of an employee based on
their monthly salary.

Description: A PL/SQL function is a named PL/SQL block that performs a specific

task and returns a value. Functions are used to encapsulate reusable logic that can be
called from SQL statements or other PL/SQL blocks.

Syntax :

CREATE [OR REPLACE] FUNCTION function_name

[ (parameter_name parameter_mode parameter_data_type [, ...]) ]

RETURN return_data_type

IS

-- Declarations

BEGIN

-- Executable statements

RETURN return_value;

EXCEPTION

-- Exception handlers

END function_name;

PL/SQL Program:

CREATE OR REPLACE FUNCTION CalculateAnnualSalary(

p_monthly_salary IN NUMBER
) RETURN NUMBERAS
v_annual_salary NUMBER;BEGIN
v_annual_salary := p_monthly_salary * 12;
RETURN v_annual_salary;
END;

Calling Function :

DECLARE
v_annual_salary NUMBER;BEGIN
v_annual_salary := CalculateAnnualSalary(5000);
DBMS_OUTPUT.PUT_LINE('The annual salary is: ' || v_annual_salary);END;/
Output:

The annual salary is: 60000

Result :
The Above programs are executed successfully.
18. Exercise on Cursors

Aim: To Create a procedure that uses a cursor to count the number of employees in a
specific department.

Description: Cursors in PL/SQL are used to handle multiple rows of data returned
by a query. They allow you to iterate over the result set and process each row
individually. There are two types of cursors in PL/SQL: Implicit Cursors and
Explicit Cursors. Here, we'll focus on Explicit Cursors, which offer more control
over the query execution and result processing.

PL/SQL Program:

CREATE OR REPLACE PROCEDURE CountEmployeesByDepartment(

p_dept_id IN NUMBER
) AS
CURSOR emp_cursor IS
SELECT COUNT(*) AS employee_count FROM Employees WHERE
DepartmentID = p_dept_id;

emp_record emp_cursor%ROWTYPE;BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO emp_record;
CLOSE emp_cursor;

DBMS_OUTPUT.PUT_LINE('Number of employees in department ' ||

p_dept_id || ': ' || emp_record.employee_count);
END CountEmployeesByDepartment;

Calling procedure :

BEGIN
CountEmployeesByDepartment(4);
END;

Output:

Number of employees in department 4: 15

Result :
The Above programs are executed successfully.
19. Exercise on Triggers

Aim: Create a trigger to enforce a constraint that prevents salary from being set below
a certain minimum value (e.g., 30000).

Description: This trigger, named EnforceSalaryConstraint, will be fired before any

INSERT or UPDATE operation on the Employees table to ensure the salary is above
the minimum threshold.

PL/SQL Program:

Sql query :
CREATE OR REPLACE TRIGGER EnforceSalaryConstraint
BEFORE INSERT OR UPDATE ON EmployeesFOR EACH ROWBEGIN
IF :NEW.Salary < 30000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least
30000.');
END IF;
END;

Inserting data:

INSERT INTO Employees (EmployeeID, Name, Salary, DepartmentID)

VALUES (2, 'Bob', 25000, 2);

Output:

ORA-20001: Salary must be at least 30000.

ORA-06512: at "YOUR_SCHEMA.ENFORCESALARYCONSTRAINT", line 5
ORA-04088: error during execution of trigger
'YOUR_SCHEMA.ENFORCESALARYCONSTRAINT'
Result :
The Above programs are executed successfully.
20. Exercise on Installation of MongoDB

Aim: To successfully install MongoDB on your local machine or server and verify
that the installation is working.

Description: MongoDB is a popular NoSQL database that is widely used for its
flexibility, scalability, and ease of use. In this exercise, we will go through the steps to
install MongoDB on a local machine, either on Windows, macOS, or Linux, and then
verify that the installation is successful by running a simple MongoDB command.

Steps for Installation:

Installing MongoDB on Windows

Download MongoDB Installer:

1. Visit the official MongoDB download page: MongoDB Community

Server.
2. Select your operating system (Windows).
3. Choose the .msi package (Windows Installer) and download it.

Run the Installer:

1. Double-click the downloaded .msi file to run the installer.

2. Follow the setup wizard. Choose the "Complete" setup type.
3. Optionally, install MongoDB as a Windows Service (recommended).
4. Note the installation path (default: C:\Program
Files\MongoDB\Server\<version>\).

Set up the MongoDB environment:

1. Add MongoDB's bin directory (e.g., C:\Program

Files\MongoDB\Server\<version>\bin) to your system's PATH
variable.

Verify the Installation:

1. Open a Command Prompt window.

2. Type mongod --version to verify the installation.
21. Exercise on Creation and Dropping of Database

Aim: To create a new database, work within it, and then drop (delete) the database.

Description: Creating and dropping databases are fundamental tasks in database

management. This exercise demonstrates how to create a new database, verify its
creation, and drop it when it is no longer needed.

Steps for Creation and Dropping of Database:

1. Creating a New Database

SQL Syntax for Creating a Database:

CREATE DATABASE database_name;

Sql query:

CREATE DATABASE DiplomaDB;

Output :Query OK, 1 row affected (0.00 sec)

Verifying the Database Creation:

Sql query:

SHOW DATABASES;

Output :

DiplomaDB
Students
Employees

Dropping a Database

If the database is no longer needed, you can drop it.

SQL Syntax for Dropping a Database:

DROP DATABASE database_name;

Sql query:

DROP DATABASE DiplomaDB;

Output :Query OK, 1 row affected (0.00 sec)

Verifying the Database Deletion:

Sql query:
SHOW DATABASES;
Output :

Students
Employees
Result :
The Above programs are executed successfully.
22. Exercise on Creation and Dropping of Collections

Creating a New Collection

In MongoDB, collections are usually created implicitly when you insert the first
document. However, you can also create a collection explicitly.

Method 1: Implicit Creation

When you insert a document into a non-existing collection, MongoDB automatically

creates the collection.

Javascript code:

use DiplomaDB; // Switch to (or create) the DiplomaDB database

db.Students.insertOne({ StudentID: 1, FirstName: "John", LastName: "Doe", Age: 20,
EnrollmentDate: new Date() });

Output: { acknowledged: true, insertedId: {

“0”:ObjectId({ StudentID: 1, FirstName: "John", LastName: "Doe", Age: 20,

EnrollmentDate: 31-08-2024)

}}

Method 2: Explicit Creation

You can explicitly create a collection with options.

Javascript code:

db.createCollection("Students");
Output: [“order”,"Students", “Employees”]

Dropping a Collection

When you no longer need a collection, you can drop it.

MongoDB Syntax for Dropping a Collection:

db.collection_name.drop();

Javasrcipt code:

db.Students.drop();

Output: true

Verifying Collection Deletion:

show collections;
Output: [“order”, “Employees”]
Result :
The Above programs are executed successfully.
23. Exercise on commands of MongoDB

Aim: To practice and understand the use of essential MongoDB commands for
creating databases, collections, and documents, as well as performing CRUD (Create,
Read, Update, Delete) operations.

Description: MongoDB commands allow you to interact with the database in various
ways. This exercise covers basic MongoDB commands used for managing databases,
collections, and documents, as well as querying and updating data.

Procedure :
CRUD Operations

Insert Documents:

javascript code
db.Teachers.insertMany([
{ TeacherID: 1, FirstName: "Alice", LastName: "Brown", Subject: "Math" },
{ TeacherID: 2, FirstName: "Bob", LastName: "Smith", Subject: "Science" }
]);

Output: { acknowledged: true, insertedIds: { "0": ObjectId("..."), "1":

ObjectId("...") } }

Read Documents:

javascript Copy
db.Teachers.find().pretty();

Output:

{
"_id": ObjectId("..."),
"TeacherID": 1,
"FirstName": "Alice",
"LastName": "Brown",
"Subject": "Math"
},
{
"_id": ObjectId("..."),
"TeacherID": 2,
"FirstName": "Bob",
"LastName": "Smith",
"Subject": "Science"
}

Update Document:

javascript code
db.Teachers.updateOne({ TeacherID: 1 }, { $set: { LastName: "White" } });
Output: { acknowledged: true, matchedCount: 1, modifiedCount: 1 }

Delete Document:

javascript Copy
db.Teachers.deleteOne({ TeacherID: 2 });

Output: { acknowledged: true, deletedCount: 1 }

Result :
The Above programs are executed successfully.