Noida Institute of Engineering & Technology,

Greater Noida
(An Autonomus Institute)

LAB FILE
Session: 2025-26

Branch: Computer Science Semester: IVth

Subject Name: DBMS LAB Subject Code: BCSE0452Z
Student Name: Amit Kumar Verma Faculty Name: Mr. Vinay Pathak
Roll no: 2401330120023

Department of Computer Science & Engineering

NOIDA INSTITUTE OF ENGINEERING & TECHNOLOGY
19, KNOWLEDGE PARK-II, INSTITUTIONAL AREA,
GREATER NOIDA, (U. P.) - 201 306, INDIA
 INDEX
[Link]. Program Name Date Signature Remarks
1 Understand and implement the different ER diagram notation with
their relationship and Cardinalities.
2 Creating ER Diagram for company Database. Company database
have entities like employee, departments, projects and dependents
also implement the relationship and cardinalities between the
entities with their relevant attribute.
3 Implement DDL, DML, DCL & TCL commands
4 Implementation of I/O Constraint: Primary Key, composite primary
key, Foreign Key with on delete set null and on delete set null
constraint, Unique Key
5 Implementation of Business Constraint: Null, Not Null, Default,
Check.
6 Practicing Queries using Like, Between, Aliases, distinct Operator
& Predicate. And Implement Aggregate Functions
7 Implementation of Queries using Where, Group by, Having and
Order by Clause.
8 Create a table EMPLOYEE with following schema:-(Emp_no,
E_name, E_address, E_ph_no, Dept_no,
Dept_name,Job_id, Designation, Salary)
Write SQL statements for the following query.
i. List the E_no, E name, Salary of all employees working for
MANAGER.
ii. Display all the details of the employee whose salary is more than
the Sal of any IT PROFF.
iii. List the employees in the ascending order of Designations of
those joined after 1981.
iv. List the employees along with their Experience and Daily
v. List the employee who are either ‘CLERK’ or ‘ANALYST’.
vi. List the employees who joined on 1-MAY-81, 3-DEC-81, 17-
DEC-81.
vii. List the e_name those are starting with ‘S’.
viii. Display total salary spent for each job category.
ix. Display lowest paid employee details under each manager.
x. Display number of employees working in each department and
their
department name.
xi. Display the details of employees sorting the salary in increasing
order.
xii. Show the record of employee earning salary greater than 16000
in each
department.
xiii. Add constraints to check, while entering the empno value
(i.e) empno> 100.
xiv. Define the field DEPTNO as unique.
xv. Create a primary key constraint for the column (EMPNO).
Create a primary key constraint for the column (EMPNO).
9 Implementation of Queries using set theory operators UNION,
INTERSECT, MINUS.
10 Implementation of Queries using Inner Join: Natural Join, Equi
Join & Non Equi Join, Outer Join.
11 Implementation of Queries nested Queries or Sub Queries: - IN,
NOT IN, Exists, Not Exists, All and Any.
12 Apply the theory operators, join’s and nested queries on company
database (Case Study-1)
Write the SQL Queries for the following statement.
I. Retrieve the names of employees in department 5 who
work more than 10 hours per week on the
'ProductX'project.
II. List the names of employees who have a dependent with
the same first name as themselves.
III. Find the names of employees that are directly supervised
by 'Franklin Wong'.
IV. For each project, list the project name and the total hours
per week (by all employees) spent on that
project.
V. Retrieve the names of all employees who work on every
project controlled by department 5.
VI. Retrieve the names of all employees who do not work
on every project
VII. For each department, retrieve the department name, and
the average salary of employees working in
that department.
VIII. Retrieve the average salary of all female employees.
IX. Find the names and addresses of all employees who work
on at least one project located in Houston
but whose department has no location in Houston.
X. List the last names of department managers who have
no dependents.
XI. Retrieve the names of all employees who work in
the department that has the employee with the
highest salary among all employees.
13 Implementation and apply all the set theory operators, join and
nested queries concept on Case study 1.
I. Make a list of all project members for projects that involve
an employee whose name is SCOTT either as a worker or as
a manager of the department that controls the project.
II. To retrieve the Social Security numbers of all
employees who either work in department 5 or directly
supervise an employee who works in department 5.
III. To retrieve the SSN of all employee who work as a
supervisor not a manager.

IV. We want a list of all employee names as well as the name

of the departments they manage if they happen to
manage a department; if they do not manage one, we can
indicate it with a NULL value.
V. Retrieve the names of employees who have no dependents.
VI. List the names of all employees with two or more dependents.
VII. List the names of managers who have at least one dependent.
VIII. Retrieve the names of all employees who do not
have supervisors.
IX. Retrieve the name of each employee who
has a dependent with the same Last name as the
employee.
14 Implementation of Indexing, Views and sequence
15 I. Write a PL/SQL Program to Add Two Numbers
II. Write PL/SQL Program for Fibonacci Series Write PL/SQL
III. Program to Find Greatest of Three Numbers
16 Write a Pl/SQL code block to calculate the area of a circle for a
value of radius varying from 3 to 7. Store the radius and the
corresponding values of calculated area in an empty table named
Areas, consisting of two columns Radius and Area.
17 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 values and new values:
18 Implementation of commit and rollback statement with amount
transfer example.
19 Implementation of the MongoDB Shell commands
20 Implementation of the CRUD Operation in MongoDB

2401330120023
 Program 1

Understand and implement the different ER diagram notation with their relationship and Cardinalities.

ER Diagram
An Entity Relationship (ER) Diagram is a graphical representation used in Database Management Systems (DBMS)
to model the structure of a database. It shows entities, attributes, relationships, and cardinality constraints.

ER Diagram Notations Entity

An Entity represents a real-world object that can be stored in the database. It is represented by a Rectangle.

Attribute
Describes properties of an entity. Representation: Oval (Ellipse).

Types of Attributes:

Key Attribute
Key attributes are specialized attributes that uniquely identify each entity within an entity set. It can be represented
by underlining the attribute name in Entity-Relationship (ER) diagrams.

Multivalued Attribute
It can be an attribute that can hold multiple values for a single entity instance, unlike singlevalued attributes.
Represented by a double ellipse in Entity-Relationship (ER) diagrams

Derived Attribute
A derived attribute is a data field in a database or model that is calculated or derived from other existing attributes,
rather than being stored directly.
It can be represented in entity-relationship (ER) diagrams with a dotted ellipse.

Composite Attribute
It is an attribute that can be divided into smaller, simpler, and meaningful sub-attributes, rather than being atomic.

2401330120023
Relationship
Shows association between two entities. Representation: Diamond shape.
Weak Entity

Represented by double rectangle

Depends on another entity for identification

Cardinalities in ER Diagram
Cardinality defines how many instances of one entity relate to another entity. One-to-One (1:1)
One entity relates to only one entity.

One-to-Many (1:N)
One entity relates to many entities.

Many-to-One (N:1)
Many entities relate to one entity.

Many-to-Many (M:N)
Many entities relate to many entities.

2401330120023
For Example:

2401330120023
 Program 2

Creating ER Diagram for company Database. Company database have entities like employee,
departments, projects and dependents also implement the relationship and cardinalities between the
entities with their relevant attribute.

ER Diagram for Company Database:

2401330120023
The Department entity stores information about different departments in the organization.
Its attributes include Dept_ID, Dept_Name, and Contact, which uniquely identify and describe each
department.
The Project entity represents projects handled by the organization.

It contains attributes such as Proj_ID, Proj_Name, and Proj_Type.

A manages relationship exists between Department and Project.

This relationship follows a one-to-many (1:N) cardinality, indicating that one department can manage
multiple projects.
The Employee entity represents employees working in the company.
It includes attributes such as Emp_ID, Emp_Name, Email, Phone, and DOB to maintain employee
records.
The appoints relationship connects Department and Employee.
This relationship also follows a one-to-many (1:N) cardinality, meaning one department can appoint
multiple employees.
The works_on relationship links Employee and Project entities.
It is a many-to-many (M:N) relationship because multiple employees can work on multiple projects.
The attribute Hours indicates the number of hours an employee contributes to a project.

The Dependant entity represents family members who depend on an employee.

Its attributes include Dpd_Name, Gender, and Relation.

The supports relationship connects Employee and Dependant.

This relationship follows a one-to-many (1:N) cardinality, where one employee can support multiple
dependents.

2401330120023
 Program 3
Implement DDL, DML, DCL & TCL commands

1. DDL (Data Definition Language)

DDL (Data Definition Language) is used to define, create, modify, and delete the structure of database
objects such as databases, tables, and columns. These commands affect the schema or structure of the
database rather than the data stored in it. DDL commands are automatically committed in the database.

Common DDL Commands

CREATE: The CREATE command is used to create a new database or table.

Code:
CREATE TABLE student (
id INT,
name VARCHAR(50),
age INT
);
DESC student;

Output:

ALTER : The ALTER command is used to modify the structure of an existing table.

Code:
ALTER TABLE student ADD city VARCHAR(50); DESC student;

Output:

2401330120023
TRUNCATE : The TRUNCATE command is used to remove all records from a table but keep the table
structure.

Code:
TRUNCATE TABLE student; SELECT * FROM student;

Output:

DROP: The DROP command is used to delete a table or database permanently.

Code:
DROP TABLE student;

Output:

2401330120023
2. DML (Data Manipulation Language)
DML (Data Manipulation Language) is used to manage and manipulate the data stored in database tables.
These commands allow users to insert, update, delete, and retrieve data from the database. DML commands
work on the data inside the tables rather than changing the structure of the database.
Common DML Commands

INSERT: The INSERT command is used to add new records into a table.

Code:
CREATE TABLE student (
id INT,
name VARCHAR(50),
age INT,
city VARCHAR(50)
);
INSERT INTO student VALUES (1, 'Varun Shukla', 20, 'Delhi');
INSERT INTO student VALUES (2, 'Vikas Rathore', 19, 'Mumbai');
INSERT INTO student VALUES (3, 'Shivam Nishad', 21, 'Lucknow');
SELECT * FROM student;

Output:

SELECT: The SELECT command is used to retrieve or display data from a table.

Code:
SELECT name, age FROM student WHERE age > 19;

2401330120023
Output:

UPDATE: The UPDATE command is used to modify existing data in a table.

Code:
UPDATE student SET age = 22 WHERE name = 'Vikas Rathore';
SELECT * FROM student;

Output:

DELETE: The DELETE command is used to remove records from a table.

Code:
DELETE FROM student WHERE id = 3;
SELECT * FROM student;

Output:

2401330120023
3. DCL (Data Control Language)
DCL (Data Control Language) is used to control access to the database. These commands allow
administrators to grant or remove permissions for users to perform operations on database objects such as
tables and views.
Common DCL Commands

GRANT: The GRANT command is used to give specific privileges to users on database objects.

Code:
CREATE USER 'varun'@'localhost' IDENTIFIED BY 'varun@123';
CREATE USER 'vikas'@'localhost' IDENTIFIED BY 'vikas@123';
GRANT SELECT, INSERT ON [Link] TO 'varun'@'localhost';
GRANT SELECT ON [Link] TO 'vikas'@'localhost';
REVOKE INSERT ON [Link] FROM 'varun'@'localhost';

Output:

REVOKE: The REVOKE command is used to remove privileges previously granted to a user.

Code:
CREATE USER 'student1'@'localhost' IDENTIFIED BY '1234';
GRANT ALL PRIVILEGES ON college.* TO 'student1'@'localhost';
REVOKE ALL PRIVILEGES ON college.* FROM 'student1'@'localhost';

Output:

2401330120023
4. TCL (Transaction Control Language)
TCL (Transaction Control Language) is used to manage transactions in a database. These commands help
maintain data consistency and integrity by controlling changes made by DML statements.
Common TCL Commands

COMMIT: The COMMIT command is used to permanently save all the changes made during the current
transaction.

Code:
SET autocommit = 0; SELECT * FROM student;
UPDATE student SET city = 'Agra' WHERE id = 1; COMMIT;
DELETE FROM student WHERE id = 2; ROLLBACK;
SELECT * FROM student;

Output:

ROLLBACK: The ROLLBACK command is used to undo changes made during the current transaction.

Code:
ROLLBACK;

Output:

2401330120023
 Program 4

Implementation of I/O Constraint: Primary Key, composite primary key, Foreign Key with on delete
set null and on delete set null constraint, Unique Key

1. Primary Key
A Primary Key uniquely identifies each record in a table. It cannot contain NULL values and must be unique
for every row.

Code:
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

Output:

2. Composite Primary Key

A Composite Primary Key is a primary key that consists of two or more columns. The combination of these
columns uniquely identifies each record.

Code:
CREATE TABLE enrollment ( student_id INT,
course_id INT,
enroll_date DATE,
PRIMARY KEY (student_id, course_id)
);

Output:

2401330120023
3. Foreign Key with ON DELETE SET NULL
A Foreign Key links two tables. ON DELETE SET NULL means that when the referenced row in the parent
table is deleted, the foreign key value in the child table is automatically set to NULL.

Code:
CREATE TABLE dept (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE student2 (
Id INT PRIMARY KEY,
Name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(dept_id) ON DELETE SET NULL
);
INSERT INTO dept VALUES (10, 'Science'), (20, 'Commerce'), (30, 'Arts');
INSERT INTO student2 VALUES (1, 'Varun Shukla', 10), (2, 'Vikas Rathore', 20);

Output:

5. ON DELETE SET NULL CONSTRAINT

used to create a relationship between two tables. It ensures that a value in one table must exist in another
table.

Code:
DELETE FROM dept WHERE dept_id = 10;
SELECT * FROM student2;

Output:

6. Unique Key
A Unique Key ensures that all values in a column are different. Unlike Primary Key, a Unique Key column
can contain NULL values.

2401330120023
Code:
CREATE TABLE student4 ( id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
DESC student4;
INSERT INTO student4 VALUES (1, 'Varun Shukla', 'varun@[Link]');
INSERT INTO student4 VALUES (2, 'Vikas Rathore', 'vikas@[Link]');
SELECT * FROM student4;

Output:

2401330120023
 Program 5

Implementation of Business Constraint: Null, Not Null, Default, Check.

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value. Every row must contain a
value for that column.

Code:
CREATE TABLE student_notnull ( id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL
);
DESC student_notnull;
INSERT INTO student_notnull VALUES (1, 'Varun Shukla', 'Delhi');
SELECT * FROM student_notnull;

Output:

2. NULL Constraint
The NULL constraint allows a column to store NULL values. This is the default behavior in SQL if no
constraint is specified.

Code:
CREATE TABLE student_null ( id INT PRIMARY KEY,
name VARCHAR(50) NULL,
city VARCHAR(50) NULL
);
DESC student_null;
INSERT INTO student_null(id, name) VALUES (1, 'Varun Shukla');
INSERT INTO student_null VALUES (2, NULL, NULL);
SELECT * FROM student_null;

2401330120023
Output:

3. DEFAULT Constraint
The DEFAULT constraint provides a default value for a column when no value is specified during an
INSERT operation.

Code:
CREATE TABLE student_default ( id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) DEFAULT 'Delhi',
status VARCHAR(20) DEFAULT 'Active'
);
DESC student_default;
INSERT INTO student_default(id, name)VALUES (1, 'Varun Shukla');
INSERT INTO student_default VALUES (2, 'Vikas Rathore', 'Mumbai', 'Active');
INSERT INTO student_default(id, name, city) VALUES (3, 'Shivam Nishad', 'Lucknow');
SELECT * FROM student_default;

2401330120023
Output:

4. CHECK Constraint
constraint limits the values that can be inserted into a column based on a condition. If the condition is false,
the insertion or update is rejected.

Code:
CREATE TABLE student_check ( id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT, fee INT,
CHECK (age >= 18), CHECK (fee > 0)
);
DESC student_check;
INSERT INTO student_check VALUES (1, 'Varun Shukla', 20, 5000);
INSERT INTO student_check VALUES (2, 'Vikas Rathore', 19, 4500);
SELECT * FROM student_check;

2401330120023
Output:

2401330120023
 Program 6

Practicing Queries using Like, Between, Aliases, distinct Operator & Predicate. And Implement
Aggregate Functions.

Create Table of Student

Code:
CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(50),
age INT,
city VARCHAR(50),
fee INT,
dept VARCHAR(30)
);
INSERT INTO student VALUES
(1, 'Varun Shukla', 20, 'Delhi', 5000, 'Science'),
(2, 'Vikas Rathore', 19, 'Mumbai', 4500, 'Commerce'),
(3, 'Shivam Nishad', 21, 'Lucknow', 6000, 'Science'),
(4, 'Rohit Verma', 22, 'Delhi', 7000, 'Arts'),
(5, 'Rahul Gupta', 20, 'Kanpur', 4000, 'Commerce'),
(6, 'Neha Sharma', 23, 'Mumbai', 8000, 'Science'); SELECT * FROM student;

Output:

1. LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Wildcard
characters: % (any sequence of characters), _ (single character).

Code:
SELECT * FROM student WHERE name LIKE 'V%';
SELECT * FROM student WHERE name LIKE '%Nishad';
SELECT * FROM student WHERE name LIKE '

2401330120023
Output:

2. BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The range is inclusive on both ends.

Code:
SELECT * FROM student WHERE age BETWEEN 19 AND 21;

Output:

3. Aliases (AS)
Aliases are used to give a table or a column a temporary name. They make column names more readable in
the output.

Code:
SELECT name AS Student_Name, age AS Student_Age, fee AS Fees FROM student;

Output:

2401330120023
4. PREDICATE Operator
A Predicate in SQL is a condition used in a WHERE clause to filter records from a table.

Code:
SELECT * FROM student WHERE city IN ('Delhi', 'Mumbai');

Output:

5. DISTINCT Operator
The DISTINCT keyword is used to return only unique (different) values. It removes duplicate values from
the result.

Code:
SELECT DISTINCT city FROM student;

Output:

6. Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value. Common functions:
COUNT(), SUM(), AVG(), MAX(), MIN().

Code:
AVG(), MAX(), MIN().
SELECT COUNT(*) AS Total, SUM(fee) AS Sum_Fee,
AVG(fee) AS Avg_Fee, MAX(fee) AS Max_Fee, MIN(fee) AS Min_Fee FROM student;

2401330120023
Output:

2401330120023
 Program 7

Implementation of Queries using Where, Group by, Having and Order by Clause.

1. WHERE Clause
The WHERE clause is used to filter records. It extracts only those records that fulfil a specified condition.

Code:
SELECT name, age, city FROM student WHERE age > 20;

Output:

2. GROUP BY Clause
The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It
is often used with aggregate functions.

Code:
SELECT dept, COUNT(*) AS Total FROM student
GROUP BY dept;

Output:

3. HAVING Clause
The HAVING clause is used to filter groups based on aggregate function results. It is like WHERE but for
groups. HAVING is always used after GROUP BY.

Code:
SELECT dept, COUNT(*) AS Total FROM student
GROUP BY dept HAVING COUNT(*) > 1;

2401330120023
Output:

4. ORDER BY Clause
The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order. By
default, it sorts in ascending order.

Code:
SELECT name, age FROM student ORDER BY age ASC;
SELECT name, fee FROM student ORDER BY fee DESC;

Output:

2401330120023
 Program 8

Create a table EMPLOYEE with following schema: (Emp_no, E_name, E_address, E_ph_no, Dept_no,
Dept_name, Job_id, Designation, Salary).

Table Creation :

Code:
CREATE TABLE EMPLOYEE ( Emp_no INT PRIMARY KEY, E_name VARCHAR(50),
E_address VARCHAR(100),
E_ph_no VARCHAR(15), Dept_no INT,
Dept_name VARCHAR(30),
Job_id VARCHAR(20),
Designation VARCHAR(30),
Salary DECIMAL(10,2),
Hiredate DATE
);
INSERT INTO EMPLOYEE VALUES
(101,'Varun Shukla', 'Delhi', '9876543210',10,'IT','J001','MANAGER', 25000,'1982-06-15'),
(102,'Vikas Rathore','Mumbai', '9988776655',20,'HR','J002','CLERK', 15000,'1981-09-08'),
(103,'Shivam Nishad','Lucknow','9111223344',10,'IT','J001','ANALYST', 18000,'1982-11-10'),
(104,'Rohit Verma', 'Agra', '9776655443',30,'Finance','J002','IT PROFF',14000,'1981-12-03'),
(105,'Rahul Gupta', 'Kanpur', '9123456789',20,'HR','J003','MANAGER', 28000,'1983-03-20');
SELECT * FROM EMPLOYEE;

Output:

2401330120023
Write SQL statements for the given queries.

1. MANAGER employees

Code:
SELECT Emp_no, E_name, Salary FROM EMPLOYEE WHERE Designation = 'MANAGER'; List
Emp_no, E_name, Salary of employees working as MANAGER

Output:

2. Salary > any IT PROFF

Code:
SELECT Emp_no, E_name, Designation, Salary FROM EMPLOYEE
WHERE Salary > ANY (SELECT Salary FROM EMPLOYEE WHERE Designation = 'IT PROFF');
Employees whose salary > any IT PROFF

Output:

Ascending order of Designations, joined after 1981

3. Ascending Designation, joined after 1981

Code:
SELECT Emp_no, E_name, Designation, Hiredate
FROM EMPLOYEE WHERE YEAR(Hiredate) > 1981
ORDER BY Designation ASC;

2401330120023
Output:

Employees with Experience and Daily Salary

4. Experience and Daily Salary

Code:
SELECT E_name, TIMESTAMPDIFF(YEAR, Hiredate, CURDATE())
AS Experience, ROUND(Salary/30, 2)
AS Daily_Salary FROM EMPLOYEE;

Output:

Employees who are Clerk or Analyst

5. CLERK or ANALYST

Code:
SELECT Emp_no, E_name, Designation, Salary
FROM EMPLOYEE
WHERE Designation IN ('CLERK', 'ANALYST');

2401330120023
Output:

Employees who joined on 1-MAY-81, 3-DEC-81, 17-DEC-81

6. Joined on specific dates

Code:
SELECT Emp_no, E_name, Hiredate FROM EMPLOYEE
WHERE Hiredate IN ('1981-05-01','1981-12-03','1981-12-17','1980-01-19');

7. Dept 10 or 20

Code:
SELECT * FROM EMPLOYEE WHERE Dept_no IN (10, 20);

Output:

Names starting with 'S'

8. E_name starting with 'S'

Code:
SELECT E_name FROM EMPLOYEE WHERE E_name LIKE 'S%';

9. First 5 chars of names starting with 'H'

Code:
SELECT E_name, SUBSTRING(E_name,1,5) AS First_Five
FROM EMPLOYEE WHERE E_name LIKE 'H%';

2401330120023
Output:

Total salary per job category

10. Except PRESIDENT & MGR ordered by Salary ASC Code:

SELECT * FROM EMPLOYEE WHERE Designation
NOT IN ('PRESIDENT','MGR') ORDER BY Salary ASC;

11. Total salary per Job category

Code:
SELECT Job_id, SUM(Salary) AS Total_Salary
FROM EMPLOYEE GROUP BY Job_id;

Output:

Lowest paid employee under each manager (department)

12. Lowest paid per dept

Code:
SELECT Dept_no, MIN(Salary) AS Min_Salary FROM EMPLOYEE GROUP BY Dept_no;

13. Employee count per dept

Code:
SELECT Dept_no, Dept_name, COUNT(*) AS Num_Employees
FROM EMPLOYEE GROUP BY Dept_no, Dept_name;

14. Sorted by Salary ASC

Code:
SELECT * FROM EMPLOYEE ORDER BY Salary ASC;

2401330120023
15. Salary > 16000 per dept

Code:
SELECT Dept_no, E_name, Salary FROM EMPLOYEE WHERE Salary > 16000 ORDER BY Dept_no;

16. CHECK constraint Emp_no > 100

Code:
ALTER TABLE EMPLOYEE ADD CONSTRAINT chk_empno CHECK (Emp_no > 100);

17. UNIQUE on Dept_no

Code:
ALTER TABLE EMPLOYEE ADD CONSTRAINT uq_deptno UNIQUE (Dept_no);

18. PRIMARY KEY already defined on Emp_no during CREATE TABLE Output:

2401330120023
 Program 9

Implementation of Queries using set theory operators UNION, INTERSECT, MINUS.

Create Two Tables

Code:
CREATE TABLE science_student (id INT, name VARCHAR(50),
city VARCHAR(50),
dept VARCHAR(30));
CREATE TABLE commerce_student (id INT, name VARCHAR(50),
city VARCHAR(50),
dept VARCHAR(30));
INSERT INTO science_student VALUES (1,'Varun Shukla','Delhi','Science'),
(2,'Shivam Nishad','Lucknow','Science'),
(3,'Neha Sharma','Mumbai','Science');
INSERT INTO commerce_student VALUES (3,'Neha Sharma','Mumbai','Commerce'),
(4,'Vikas Rathore','Mumbai','Commerce'),
(5,'Rohit Verma','Delhi','Commerce'),
(6,'Rahul Gupta','Kanpur','Commerce');

Output:

UNION Operator
The UNION operator combines the result sets of two or more SELECT statements. It removes duplicate rows
from the result. Both SELECT statements must have the same number of columns with compatible data
types.

Code:
SELECT id, name, city FROM science_student UNION
SELECT id, name, city FROM commerce_student;

Output:

2401330120023
UNION ALL Operator

Code:
SELECT id, name, city FROM science_student UNION ALL
SELECT id, name, city FROM commerce_student;
UNION ALL returns all rows including duplicates from both SELECT statements.

Output:

INTERSECT Operator

Code:
SELECT [Link], [Link], [Link] FROM science_student s
INNER JOIN commerce_student c ON [Link] = [Link] AND [Link] = [Link];
The INTERSECT operator returns only the rows that appear in both SELECT statements. It gives common
records.

Output:

MINUS / EXCEPT Operator

The MINUS (or EXCEPT) operator returns rows that are present in the first SELECT statement but not in
the second. It subtracts results.

Code:
SELECT [Link], [Link], [Link] FROM science_students
LEFT JOIN commerce_student c
ON [Link] = [Link] WHERE [Link] IS NULL;

2401330120023
Output:

2401330120023
 Program 10

Implementation of Queries using Inner Join: Natural Join, Equi Join & Non Equi Join, Outer Join.

Create Table of Student and Department

Code:
CREATE TABLE dept (
dept_id INT PRIMARY KEY, dept_name VARCHAR(30), hod VARCHAR(50)
);
CREATE TABLE student (
Id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50),
dept_id INT, salary INT
);
INSERT INTO dept VALUES
(10,'Science','Dr. Sharma'),(20,'Commerce','Dr. Verma'),
(30,'Arts','Dr. Gupta'),(40,'Maths','Dr. Singh');
INSERT INTO student VALUES (1,'Varun Shukla','Delhi',10,25000), (2,'Vikas
Rathore','Mumbai',20,15000),
(3,'Shivam Nishad','Lucknow',10,18000), (4,'Rohit Verma','Delhi',30,14000),
(5,'Rahul Gupta','Kanpur',20,28000), (6,'Neha Sharma','Mumbai',NULL,20000);

Output:

Natural Join
A NATURAL JOIN automatically joins tables based on columns with the same name and compatible data
types. No ON clause is needed.

Code:
SELECT * FROM student NATURAL JOIN dept;

Output:

2401330120023
Equi Join
An Equi Join is an inner join where rows from two tables are combined using the equality (=) operator on
related columns.

Code:
SELECT [Link], [Link], [Link], [Link], d.dept_name, [Link]
FROM student s, dept d
WHERE s.dept_id = d.dept_id;

Output:

Non Equi Join

A Non Equi Join uses operators other than = (such as >, <, BETWEEN) to join tables. Commonly used to
assign salary grades.

Code:
SELECT [Link], [Link], [Link], d.dept_name
FROM student s, dept d
WHERE [Link] > 15000 AND s.dept_id != d.dept_id;

Output:

2401330120023
Outer Join
An Outer Join returns all rows from one or both tables, with NULL for non-matching rows.

1. Left Outer Join

Code:
SELECT [Link], [Link], [Link], [Link], d.dept_name
FROM student s LEFT OUTER JOIN dept d ON s.dept_id = d.dept_id;

2. Right Outer Join

Code:
SELECT [Link], [Link], [Link], d.dept_name
FROM student s RIGHT OUTER JOIN dept d ON s.dept_id = d.dept_id;

3. Full Outer Join

Code:
SELECT [Link], [Link], d.dept_name FROM student s LEFT JOIN dept d ON s.dept_id = d.dept_id UNION
SELECT [Link], [Link], d.dept_name FROM student s RIGHT JOIN dept d ON s.dept_id = d.dept_id;

Output:

LEFT OUTER JOIN – All rows from left table: RIGHT OUTER JOIN – All rows from right table.

2401330120023
 Program 11

Implementation of Queries nested Queries or Sub Queries: IN, NOT IN, Exists, Not Exists, All and
Any.

Create Table of Student and Department

IN Operator: The IN operator is used in a WHERE clause to check if a value matches any value in a
subquery or list.

Code:
-- IN Operator
SELECT name, salary FROM student
WHERE salary IN (SELECT salary FROM student WHERE dept_id = 10);

Output:

NOT IN Operator: The NOT IN operator returns rows where the value does not match any value in the
subquery result.

Code:
SELECT name, salary FROM student
WHERE salary NOT IN (SELECT salary FROM student WHERE dept_id = 20);

Output:

EXISTS Operator
The EXISTS operator returns TRUE if the subquery returns at least one row. It is used to check for the
existence of records

2401330120023
Code:
SELECT name, salary FROM student s WHERE EXISTS (
SELECT 1 FROM student s2
WHERE s2.dept_id = s.dept_id AND [Link] > 20000
);

Output:

NOT EXISTS Operator

The NOT EXISTS operator returns TRUE if the subquery returns no rows. It finds records that have no
matching rows in another table.

Code:
SELECT name, salary FROM student s WHERE NOT EXISTS (
SELECT 1 FROM student s2
WHERE s2.dept_id = s.dept_id AND [Link] > 25000
);

Output:

ALL Operator
The ALL operator returns TRUE if the comparison is true for ALL values returned by the subquery.

Code:
SELECT name, salary FROM student
WHERE salary < ALL (SELECT salary FROM student WHERE dept_id = 10);

2401330120023
Output:

ANY Operator:
The ANY operator returns TRUE if the comparison is true for at least ONE value in the subquery.

Code:
SELECT name, salary FROM student
WHERE salary = ANY (SELECT salary FROM student WHERE dept_id = 20);

Output:

2401330120023
 Program 12

Apply the theory operators, join's and nested queries on company database (Case Study-1). Write
the SQL Queries for the following statements.

Setup — Company Database Schema & Data

Code:
CREATE TABLE DEPARTMENT (Dno INT PRIMARY KEY,
Dname VARCHAR(30),
MgrSSN VARCHAR(15),
MgrStartDate DATE);
CREATE TABLE EMPLOYEE (SSN VARCHAR(15) PRIMARY KEY,
Fname VARCHAR(20),
Lname VARCHAR(20),
Bdate DATE,
Address VARCHAR(60),
Sex CHAR(1),
Salary DECIMAL(10,2),
SuperSSN VARCHAR(15),
Dno INT);
CREATE TABLE PROJECT (Pno INT PRIMARY KEY,
Pname VARCHAR(30),
Plocation VARCHAR(30), Dno INT);
CREATE TABLE WORKS_ON (ESSN VARCHAR(15),
Pno INT,
Hours DECIMAL(5,1),
PRIMARY KEY (ESSN, Pno));
CREATE TABLE DEPENDENT (ESSN VARCHAR(15),
Dep_name VARCHAR(30),
Sex CHAR(1),
Bdate DATE,
Relationship VARCHAR(20));

Output:

Q1: Dept 5 employees working >10 hrs/wk on ProductX

Code:
SELECT [Link], [Link], [Link] FROM EMPLOYEE E
JOIN WORKS_ON W ON [Link] = [Link] JOIN PROJECT P ON [Link] = [Link]
WHERE [Link] = 5 AND [Link] = 'ProductX' AND [Link] > 10;
Employees in Dept 5 working >10 hrs on ProductX

2401330120023
Output:

Employees with dependent having same first name

Q2: Employees with dependent having same first name

Code:
SELECT [Link], [Link], D.Dep_name, [Link] FROM EMPLOYEE E
JOIN DEPENDENT D ON [Link] = [Link]
WHERE [Link] = D.Dep_name;

Output:

Employees supervised by Franklin Wong

Q3: Supervised by Franklin Wong

Code:
SELECT Fname, Lname, Salary, Dno FROM EMPLOYEE
WHERE SuperSSN = (SELECT SSN FROM EMPLOYEE
WHERE Fname='Franklin' AND Lname='Wong');

Output:

2401330120023
Total hours per week per project

Q4: Total hours per project

Code:
SELECT [Link], [Link], SUM([Link]) AS Total_Hours
FROM PROJECT P JOIN WORKS_ON W
ON [Link] = [Link] GROUP BY [Link], [Link];

Output:

Q5: Employees working on every dept-5 project

Code:
SELECT Fname, Lname FROM EMPLOYEE E
WHERE NOT EXISTS ( SELECT Pno FROM PROJECT WHERE Dno=5
EXCEPT SELECT Pno FROM WORKS_ON WHERE ESSN=[Link]);

Output:

Employees NOT working on every project

Q6: Employees NOT working on every project

2401330120023
Code:
SELECT DISTINCT [Link], [Link] FROM EMPLOYEE E
WHERE [Link] IN (SELECT ESSN FROM WORKS_ON)
AND (SELECT COUNT(DISTINCT Pno)
FROM WORKS_ON WHERE ESSN=[Link]) < (SELECT COUNT(*) FROM PROJECT);

Output:

Average salary per department

Q7: Avg salary per department

Code:
SELECT [Link], [Link], AVG([Link]) AS Avg_Salary
FROM EMPLOYEE E JOIN DEPARTMENT D
ON [Link]=[Link] GROUP BY [Link], [Link];
Output:

Average salary of female employees

Q8: Avg salary of female employees

Code:
SELECT AVG(Salary) AS Avg_Female_Salary
FROM EMPLOYEE WHERE Sex='F';

2401330120023
Output:

Employees on Houston project but dept not in Houston

Q9: Houston project, dept has no Houston location

Code:
SELECT DISTINCT [Link], [Link], [Link], [Link], [Link] FROM EMPLOYEE E JOIN
WORKS_ON W ON [Link]=[Link] JOIN PROJECT P ON [Link]=[Link]
WHERE [Link]='Houston' AND [Link] NOT IN (SELECT Dno FROM PROJECT WHERE
Plocation='Houston');

Output:

2401330120023
 Program 13

Implementation and apply all the set theory operators, join and nested queries concept on Case study.
Setup — Company Database (Case Study 1)

Project members for projects involving SCOTT as worker or manager Code:

1. Project members involving SCOTT

Code:
SELECT DISTINCT [Link],
[Link],
[Link] FROM EMPLOYEE E
JOIN WORKS_ON W ON [Link]=[Link] JOIN PROJECT P ON [Link]=[Link]
WHERE [Link] IN (SELECT Pno FROM WORKS_ON WHERE ESSN=(SELECT SSN FROM
EMPLOYEE WHERE
Fname='Scott')) UNION
SELECT DISTINCT [Link],
[Link],
[Link] FROM EMPLOYEE E
JOIN WORKS_ON W ON [Link]=[Link] JOIN PROJECT P ON [Link]=[Link] JOIN DEPARTMENT
D ON
[Link]=[Link]
WHERE [Link]=(SELECT SSN FROM EMPLOYEE WHERE Fname='Scott');

Output:

SSN of employees in Dept 5 or supervising Dept 5 employee

2401330120023
2. SSNs in dept 5 OR supervising dept-5 employee

Code:
SELECT SSN FROM EMPLOYEE WHERE Dno=5 UNION
SELECT DISTINCT SuperSSN FROM EMPLOYEE
WHERE SuperSSN IS NOT NULL AND Dno=5;

Output:

SSN of supervisors who are NOT managers

3. Supervisors NOT managers

Code:
SELECT DISTINCT SuperSSN AS SSN FROM EMPLOYEE WHERE SuperSSN IS NOT NULL
AND SuperSSN NOT IN (SELECT MgrSSN FROM DEPARTMENT);

Output:

All employees and the department they manage (NULL if none)

4. All employees with dept they manage

2401330120023
Code:
SELECT [Link], [Link], [Link] AS Manages
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON [Link]=[Link];

Output:

Employees with no dependents

5. Employees with NO dependents

Code:
SELECT [Link], [Link] FROM EMPLOYEE E
WHERE NOT EXISTS (SELECT 1 FROM DEPENDENT WHERE ESSN=[Link]);

Output:

Employees with 2 or more dependents

6. Employees with 2 or more dependents

Code:
SELECT [Link], [Link], COUNT(D.Dep_name) AS Dep_Count
FROM EMPLOYEE E JOIN DEPENDENT D
ON [Link]=[Link] GROUP BY [Link], [Link], [Link] HAVING
COUNT(D.Dep_name)>=2;

2401330120023
Output:

Managers with at least one dependent

7. Managers with at least one dependent

Code:
SELECT [Link], [Link], [Link] AS Dept_Managed FROM EMPLOYEE E
JOIN DEPARTMENT D ON [Link]=[Link]
WHERE [Link] IN (SELECT DISTINCT ESSN FROM DEPENDENT);

Output:

Employees without supervisors

8. Employees with NO supervisor

Code:
SELECT SSN, Fname, Lname, Dno, Salary
FROM EMPLOYEE WHERE SuperSSN IS NULL;

2401330120023
Output:

9. Employees with dependent having same LAST name

Code:
SELECT [Link], [Link], D.Dep_name, [Link] FROM EMPLOYEE E
JOIN DEPENDENT D ON [Link]=[Link] WHERE [Link]=D.Dep_name;

Output:

2401330120023
 Program 14

Implementation of Indexing, Views and sequence

Indexing
An INDEX is a database object that speeds up data retrieval operations. It creates an internal data structure
that allows the database engine to find rows faster without scanning the entire table. Setup – Student Table

Code:
CREATE TABLE student (
id INT PRIMARY KEY, name VARCHAR(50) NOT NULL,
age INT, city VARCHAR(50), dept VARCHAR(30), salary INT
);

Output:

Creating an Index

Code:
INSERT INTO student VALUES
(1,'Varun Shukla', 20,'Delhi', 'Science', 25000), (2,'Vikas Rathore',19,'Mumbai', 'Commerce',15000),
(3,'Shivam Nishad',21,'Lucknow','Science', 18000), (4,'Rohit Verma', 22,'Delhi', 'Commerce',28000),
(5,'Rahul Gupta', 20,'Kanpur', 'Arts', 14000),
(6,'Neha Sharma', 23,'Mumbai', 'Science', 20000);
Indexing
CREATE INDEX idx_city ON student (city); CREATE UNIQUE INDEX idx_name ON student (name);
CREATE INDEX idx_dept_salary ON student (dept, salary); SHOW INDEX FROM student;

Output:

2401330120023
Drop an Index

Code:
DROP INDEX idx_city ON student;

Output:

Views
A VIEW is a virtual table based on the result of a SELECT statement. It does not store data itself but presents
data from one or more tables.

Creating a Simple View

Code:
CREATE VIEW science_students AS
SELECT id, name, age, city, salary FROM student
WHERE dept='Science'; SELECT * FROM science_students;

Output:

Creating a Complex View (with JOIN)

Code:
CREATE VIEW dept_salary_stats AS
SELECT dept, COUNT(*) AS Total, AVG(salary) AS Avg_Salary, MAX(salary) AS Max_Salary,
MIN(salary) AS Min_Salary
FROM student GROUP BY dept; SELECT * FROM dept_salary_stats;

2401330120023
Output:

Modifying a View

Code:
UPDATE science_students SET salary=26000 WHERE id=1; SHOW FULL TABLES WHERE
Table_type='VIEW';

Output:

Removing a View

Code:
DROP VIEW dept_salary_stats;

Output:

Sequence (PostgreSQL)
A SEQUENCE is a database object that generates a series of unique numeric values. It is commonly used to
auto-generate primary key values.

2401330120023
Creating a Sequence

Code:
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT, item VARCHAR(50), amount DECIMAL(8,2)
);
INSERT INTO orders(student_id, item, amount) VALUES (1,'Notebook',120.00),(2,'Pen
Set',45.00),(3,'Calculator',350.00),(1,'Bag',850.00);
SELECT * FROM orders;

Output:

Insert a Sequence

Code:
CREATE SEQUENCE roll_seq START WITH 1001 INCREMENT BY 1;
CREATE TABLE roll (roll_no INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO roll VALUES (NEXT VALUE FOR roll_seq, 'Varun Shukla');
INSERT INTO roll VALUES (NEXT VALUE FOR roll_seq, 'Vikas Rathore');
INSERT INTO roll VALUES (NEXT VALUE FOR roll_seq, 'Shivam Nishad');
SELECT * FROM roll;

2401330120023
Output:

Removing a Sequence

Code:
ALTER SEQUENCE roll_seq RESTART WITH 2001;
DROP SEQUENCE roll_seq;

Output:

2401330120023
 Program 15

PL/SQL Program to Add Two Numbers. PL/SQL (Procedural Language/SQL) extends SQL with
procedural constructs. Every PL/SQL block has a DECLARE, BEGIN, and END section.

Code:
DECLARE
a NUMBER := 10;
b NUMBER := 20; s NUMBER;
BEGIN
s := a + b; DBMS_OUTPUT.PUT_LINE('Sum = ' || s);
END;
/

Output: Sum = 30

PL/SQL Program for Fibonacci Series

The Fibonacci series is a sequence where each number is the sum of the two preceding numbers: 0, 1, 1, 2,
3, 5, 8...

Code:
DECLARE
a NUMBER := 0; b NUMBER := 1; c NUMBER; i NUMBER := 1; n NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Fibonacci Series:'); DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
WHILE i <= n - 2 LOOP
c := a + b; DBMS_OUTPUT.PUT_LINE(c);
a := b; b := c; i := i + 1; END LOOP;
END;
/

2401330120023
Output: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34

PL/SQL Program to Find Greatest of Three Numbers

Code:
DECLARE
a NUMBER := 25; b NUMBER := 40; c NUMBER := 30;
greatest NUMBER; BEGIN
IF a >= b AND a >= c THEN greatest := a; ELSIF b >= a AND b >= c THEN greatest := b; ELSE greatest
:= c;
END IF;
DBMS_OUTPUT.PUT_LINE('Greatest Number = ' || greatest); END;
/
Output: Greatest Number = 40

2401330120023
 Program 16

Write a Pl/SQL code block to calculate the area of a circle for a value of radius varying from 3 to
7. Store the radius and the corresponding values of calculated area in an empty table named
Areas, consisting of two columns Radius and Area.

Concept:
Area of a circle = π × r² where π ≈ 3.14159
A FOR loop is used to iterate the radius from 3 to 7. Each calculated area is inserted into the AREAS
table.

Code:
CREATE TABLE AREAS (Radius NUMBER, Area NUMBER(10,4));
DECLARE
r NUMBER;
area NUMBER;
pi CONSTANT NUMBER := 3.14159265; BEGIN
FOR r IN 3..7 LOOP
area := pi * r * r;
INSERT INTO AREAS VALUES (r, ROUND(area, 4));
DBMS_OUTPUT.PUT_LINE('Radius: ' || r || ' | Area: ' || ROUND(area, 4)); END LOOP;
COMMIT;
END;
/
SELECT * FROM AREAS;

Output:

2401330120023
 Program – 17

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 values and new values.

Row Level Trigger (INSERT, UPDATE, DELETE)

A Row Level Trigger is a trigger that is executed once for each row affected by an INSERT, UPDATE,
or DELETE
operation on a table.
It fires automatically when a row is inserted, updated, or deleted.
It works on each row individually (not on whole table).
It can access old and new values using :OLD and :NEW.
Useful for auditing, validation, and tracking changes.

INSERT Trigger

An INSERT Trigger is a trigger that is fired automatically whenever a new record is inserted into a
table.
It executes after/before inserting a row.
It uses :NEW to access new values.
Useful for validating or displaying inserted data.

Code:
DELIMITER //
CREATE TRIGGER insert_salary_trigger AFTER INSERT ON customers
FOR EACH ROW BEGIN
INSERT INTO salary_log
VALUES (CONCAT('New Salary: ', [Link])); END //

DELIMITER ;

UPDATE Trigger
An UPDATE Trigger is fired automatically whenever an existing record is updated in a table.
It uses both :OLD and :NEW values.
Helps in tracking changes between old and new data.
Useful for auditing purposes.

2401330120023
DELIMITER //

CREATE TRIGGER update_salary_trigger AFTER UPDATE ON customers

FOR EACH ROW BEGIN
INSERT INTO salary_log
VALUES (CONCAT('Old Salary: ', [Link], ' New Salary: ', [Link],
' Difference: ', ([Link] - [Link])));
END //

DELIMITER :

DELETE Trigger

A DELETE Trigger is fired automatically whenever a record is deleted from a table.

It uses :OLD values
Helps in tracking deleted data.
Useful for logging and auditing. DELIMITER //
CREATE TRIGGER delete_salary_trigger AFTER DELETE ON customers
FOR EACH ROW BEGIN
INSERT INTO salary_log
VALUES (CONCAT('Deleted Salary: ', [Link]));
END //

DELIMITER ;

2401330120023
 Program 18

Implementation of commit and rollback statement with amount transfer example.

1. COMMIT
A COMMIT statement is used to permanently save all changes made during the current transaction in
the database
It makes all changes (INSERT, UPDATE, DELETE) permanent.
Once committed, changes cannot be undone.
It ensures data consistency after successful completion of operations.

START TRANSACTION:
UPDATE accounts
SET balance = balance - 1000 WHERE acc_id = 1;
UPDATE accounts
SET balance = balance + 1000 WHERE acc_id = 2; COMMIT;

Output:

2. ROLLBACK
A ROLLBACK statement is used to undo all changes made during the current transaction and restore
the database to its previous state .
It cancels all changes made after the last COMMIT.
Used when an error occurs during transaction.
Helps maintain data integrity

START TRANSACTION:
UPDATE accounts
SET balance = balance - 1000 WHERE acc_id = 1; ROLLBACK;

Output:

2401330120023
 Program 19
Implementation of the MongoDB Shell Create Database (MongoDB Shell)

In MongoDB, a database is created using the use command.

If the database does not exist, it is created automatically.
The show databases command is used to display all available databases.
A database becomes visible only after inserting data into it.

MONGOSH
use college show databases

Output:

INSERT INTO COLLECTION

The insertOne() method is used to insert a single document into a collection.

Data is stored in JSON-like format (BSON).
Each document has a unique _id field generated automatically.
The message acknowledged: true indicates successful insertion.

Code:
[Link]({Name:"Jack",Age:23,City:"New York"})
{
acknowledged: true,
insertedId: ObjectId('67acb06850ceb99f7c526642')
}
[Link]({Name:"Alice",Age:20,City:"London"})
{
acknowledged: true,
insertedId: ObjectId('67acb10150ceb99f7c526643')
}

2401330120023
[Link]({Name:"Eve",Age:30,City:"London"})
{
acknowledged: true,
InsertedId: ObjectId('67acb12850ceb99f7c526644')
}

Output:

RENAME COLLECTION

The renameCollection() method is used to change the name of a collection.

It replaces the old collection name with a new one.
The operation returns { ok: 1 } when successful.
The data inside the collection remains unchanged after renaming.

Code:
[Link]("new_student")
{
ok: 1
}
db.new_student.find({})
{
_id: ObjectId('67acb06850ceb99f7c526642'), Name: 'Jack',
Age: 23,
City: 'New York'
}

2401330120023
{
_id: ObjectId('67acb10150ceb99f7c526643'), Name: 'Alice',
Age: 20,
City: 'London'
}
{
_id: ObjectId('67acb12850ceb99f7c526644'), Name: 'Eve',
Age: 30,
City: 'London'
}

Output:

DROP DATABASE

The dropDatabase() method is used to delete an entire database in MongoDB.

It permanently removes the database along with all its collections and documents.
The command must be executed after selecting the database using use.
The operation returns { ok: 1 } when the database is successfully deleted.

Code:
[Link](){ ok:1, dropped: ‘college’}

2401330120023
Output:

FIND DOCUMENT IN COLLECTION

The find() method is used to retrieve documents from a collection.

Passing {} as a parameter returns all documents.
It displays all fields such as _id, Name, Age, and City.
This operation is part of the Read (R) operation in CRUD.

Code:
[Link]({Name:"Jack",Age:23,City:"New York"})
{
acknowledged: true,
insertedId: ObjectId('67acb45150ceb99f7c526645')
}
[Link]({Name:"Alice",Age:20,City:"London"})
{
acknowledged: true,
insertedId: ObjectId('67acb45e50ceb99f7c526646')
}
[Link]({Name:"Eve",Age:30,City:"London"})
{
acknowledged: true,
insertedId: ObjectId('67acb46a50ceb99f7c526647')
}
[Link]({})
{
_id: ObjectId('67acb45150ceb99f7c526645'), Name: 'Jack',
Age: 23,
City: 'New York'
}
{
_id: ObjectId('67acb45e50ceb99f7c526646'), Name: 'Alice',
Age: 20,
City: 'London'
}

2401330120023
Output:

2401330120023
 Program 20
Implementation of the CRUD Operation in MongoDB

INSERT OPERATION (CREATE)

An Insert Operation is used to add new documents into a collection in MongoDB.
It is used to store new data in the database
insertMany() is used to insert multiple documents
Data is stored in JSON-like format

Code:
use admin
[Link]([
{ name: "Alice", age: 30, city: "New York" },
{ name: "Bob", age: 25, city: "Los Angeles" }
]);

Output:

READ OPERATION
A Read Operation is used to retrieve data from a collection.
find() method is used to fetch data
It can filter data using conditions
Returns documents matching the query

Code:
[Link]({ city: "New York" });

Output:

UPDATE OPERATION
An Update Operation is used to modify existing documents in a collection.
updateOne() is used to update a single document
$set operator is used to change values
Helps in modifying existing data

2401330120023
Code:
[Link](
{ name: "Alice" },
{ $set: { city: "San Francisco" } }
);

Output:

DELETE OPERATION
A Delete Operation is used to remove documents from a collection. deleteOne() is
used to delete a single document
Removes data permanently
Condition is required to select the document

Code:
[Link]({ name: "Bob" });

Output:

2401330120023