Department of Computer Engineering

Course: TE [2019]
Database Management
System Lab Manual
 Table of Contents
Sr. Page.
Topic
No. No.
1. Vision, Mission, Quality Policy 4

2 Syllabus 5

2. How to Use This Manual 7

3. PEOs and Pos 9

4. Course Objective 11

5. Laboratory Objective
6. Experiment Learning Outcome (ELO) 13

7. Lab Plan
8. References
 Vision
“To satisfy the aspirations of youth force, who wants to lead
Nation towards prosperity through techno-economic
development.”

Mission
“To provide, nurture and maintain an environment of high
academic excellence, research and entrepreneurship for all aspiring
students, which will prepare them to face global challenges
maintaining high ethical and moral standards.”

Syllabus DBMSL
Group A- Database Programming Languages – SQL, PL/SQL
1. E_R Modeling
Decide a case study related to real time application
2. Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as
Table, View, Index, Sequence, Synonym Design at least 10 SQL queries for suitable database
application using SQL DML statements:
Insert, Select, Update, Delete with operators, functions, and set operator.
3. Design at least 10 SQL queries for suitable database application using SQL DML statements:
all types of Join, Sub-Query and View.
4 &5 Unnamed PL/SQL code block: Use of Control structure and Exception handling is mandatory.
Write a PL/SQL block of code for the following requirements:-
Schema:
1. Borrower(Roll_no, Name, DateofIssue, NameofBook, Status)
2. Fine(Roll_no,Date,Amt)

fine
amount will be Rs 5per day.

to fine table.

Frame the problem statement for writing PL/SQL block inline with above statement.
6. PL/SQL Stored Procedure and Stored Function.
Write a Stored Procedure namely proc_Grade for the categorization of student.if marks scored
by students in examination is <=1500 and marks>=990 then student will be placed in
distinction category if marks scored are between 989 and900 category is first class, if marks
899 and 825 category is Higher Second Class
Write a PL/SQL block for using procedure created with above requirement.
Stud_Marks(name, total_marks) Result(Roll,Name, Class)
Frame the separate problem statement for writing PL/SQL Stored Procedure and
function, inline with above statement. The problem statement should clearly state the
requirements.
 Cursors: (All types: Implicit, Explicit, Cursor FOR Loop, Parameterized Cursor)
7. 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.
Frame the separate problem statement for writing PL/SQL block to implement all types
of Cursors inline with above statement. The problem statement should clearly state the
requirements.
Database Trigger (All Types: Row level and Statement level triggers, Before and After
8. Triggers). Write a database trigger on Library table. The System should keep track of the
records that are being updated or deleted. The old value of updated or deleted records should
be added in Library_Audit table.
Frame the problem statement for writing Database Triggers of all types, in-line with
above statement. The problem statement should clearly state the requirements.

Group B Large Scale Databases

1. Study of Open Sourse NOSQL Database: MongoDB (Installation, Basic CRUD operations,
Execution)
2. Implement aggregation and indexing with suitable example using MongoDB.
3. Implement Map reduces operation with suitable example using MongoDB.
4. Encode and Decode JSON Objects using Java/Perl/PHP/Python/Ruby
Group C Mini Project : Database Project Life Cycle
Using the database concepts covered in Part-A & Part-B & connectivity concepts covered ,
1. students in group are expected to design and develop database application with following
details:
Requirement Gathering and Scope finalization
Database Analysis and Design:

Implementation :

Testing : Data Validation

Group of students should submit the Project Report which will be consist of documentation
related to different phases of Software Development Life Cycle: Title of the Project, Abstract,
Introduction, scope, Requirements, Data Modeling features, Data Dictionary, Relational
Database Design, Database Normalization, Graphical User Interface, Source Code, Testing
document, Conclusion. Instructor should maintain progress report of mini project throughout
the semester from project group and assign marks as a part of the term work
How to Use This Manual
This Manual assumes that the facilitators are aware of Collaborative Learning Methodologies.
This Manual will only provide them tool they may need to facilitate the session on Computer
Organization module in collaborative learning environment.
The Facilitator is expected to refer this Manual before the session.

K A D I
Applying Problem Design & Investigation of
Knowledge Analysis Development problems
(PO:a) (PO:b) (PO:c) (PO:d)

M E E T
Modern Tool Engineer & Environment Ethics
Usage Society Sustainability (PO:i)
(PO:e) (PO:f) (PO:h)

T O M I
Individual & Communicati Project Life Long
Team work on Management & Learning (PO:l)
(PO:g) (PO:k) Finance (PO:j)
Disk Approach- Digital Blooms Taxonomy

This Manual uses icons as visual cues to the interactivities during the session.

Icons Graduate Attributes

Applying Knowledge
Problem Analysis
Design and Development
Investigation of Problem
Modern Tool Usage
Engineer and Society
Environment Sustainability
Ethics
 Individual and Teamwork
Communication
Project Management and Finance
Lifelong Learning

Blooms Taxonomy
Remembering

Understanding

Applying

Analyzing

Evaluating

Creating

 This icon is used to indicate instructions for faculties.

 This icon is used to indicate a statement to be made by faculty.

 This icon is used to indicate a list of additional resources.

 This icon indicates an activity to be conducted.

This icon indicates questions to be asked by faculty.
 Program Education Outcome
Student will be able:-
To prepare globally competent graduates having strong fundamentals, domain
1 knowledge, updated with modern technology to provide the effective
solutions for engineering problems.
To prepare the graduates to work as a committed professional with strong
2 professional ethics and values, sense of responsibilities, understanding of
legal, safety, health, societal, cultural and environmental issues.
To prepare committed and motivated graduates with research attitude,
3
lifelong learning, investigative approach, and multidisciplinary thinking.
To prepare the graduates with strong managerial and communication skills to
4
work effectively as individual as well as in teams.

Program Outcome
Student will be able:-
To apply knowledge of mathematics, science, engineering fundamentals,
1 problem solving skills, algorithmic analysis and mathematical modeling to
the solution of complex engineering problems.
To analyze the problem by finding its domain and applying domain specific
2
skills
To understand the design issues of the product/software and develop effective
3 solutions with appropriate consideration for public health and safety, and
cultural, societal, and environmental considerations.
To find solutions of complex problems by conducting investigations applying
4
suitable techniques.
5 To adapt the usage of modern tools and recent software.
To contribute towards the society by understanding the impact of Engineering
6
on global aspect.
7 To understand environment issues and design a sustainable system.
8 To understand and follow professional ethics.
To function effectively as an individual and as member or leader in diverse
9
teams and interdisciplinary settings.
10 To demonstrate effective communication at various levels.
To apply the knowledge of Computer Engineering for development of
11
projects, finance and management.
To keep in touch with current technologies and inculcate the practice of
12
lifelong learning.
 Course Outcome (Modified in 18-19 SEM-I)
Student will be able to:
C 304.1: Design E-R Model for given requirements and convert the same into database tables.
C 304.2: Construct queries by making use of database techniques such as SQL & PL/SQL.
C 304.3: Make use of modern database techniques such as NOSQL
C 304.4: Explain transaction Management in relational database System.
C 304.5: Analyze the use of appropriate architecture in real time environment.
C 304.6: Implement advanced database Programming concepts like Big Data using HADOOP

Experiment Learning Outcome

Student will be able to:-
Understand and use the concept of relational databases (MySQL), in design of
ELO1 databases.
Demonstrate the use of SQL objects to design and develop database using DDL
ELO2 statements.
Use of SQL DML statements to design and develop for suitable database
ELO3 application
Understand and use the concept of relational databases using PL/SQL, in design
ELO4 and Develop application.
ELO5 Understand and use the concept of NOSQL databases, in design of Databases.
Use of NOSQL basic operation in design and develop for suitable NOSQL database
ELO6 application.
ELO7 Create and use of JSON objects for database application programs.
Use the concepts of DBMS (SQL, PL/SQL and NOSQL) in design and
ELO8 implementation of application program
 EXPERIMENT- 1

CONCEPT DESIGN WITH E-R MODEL

AIM: To Relate the entities appropriately. Apply cardinalities for each relationship. Identify strong and weak
entities. Indicate the type of relationships (total/partial). Incorporate generalization, aggregation and specialization
etc wherever required.

E-R Model

Bus
 BusNo
 Source
 Destination
 CoachType

SCHEMA

Bus: Bus(BusNo :String ,Source : String, Destination: String, Coach Type: String)

Ticket
 TicketNo
 DOJ
 Address
 ContactNo
 BusNo
 SeatNo
 Source
 Destination

SCHEMA

Ticket (TicketNo: string, DOJ: date, Address: string, ContactNo : string, BusNo:String
SeatNo : Integer, Source: String, Destination: String)

Passenger
 PassportID
 TicketNo
 Name
 ContactNo
 Age
 Sex
 Address

SCHEMA

Passenger (PassportID: String, TicketNo :string, Name: String, ContactNo: string, Age:
integer, Sex: character, Address: String)
 Reservation
 PNRNo
 DOJ
 No_of_seats
 Address
 ContactNo
 BusNo
 SeatNo

SCHEMA
Reservation(PNRNo: String, DOJ: Date, NoofSeats: integer , Address: String ,ContactNo: String, ,
BusNo: String,SeatNo:Integer)
Cancellation
 PNRNo
 DOJ
 SeatNo
 ContactNo
 Status

SCHEMA

Cancellation (PNRNo: String, DOJ: Date, SeatNo: integer, ContactNo: String, Status:
String)
CONCEPT DESIGN WITH E-R MODEL
 EXPERIMENT – 2
RELATIONAL MODEL

AIM: To Represent all the entities (Strong, Weak) in tabular fashion. Represent relationships in a tabular fashion.

1. Bus: Bus(BusNo: String, Source: String, Destination: String, CoachType: String)

ColumnName Datatype Constraints Type of Attributes

BusNo Varchar(10) Primary Single-value

key
Source Varchar(20) Single-value
Destination Varchar(20) Simple
CoachType Varchar(10) Simple

Mysql>create table Bus(BusNo varchar(10),source varchar(20),Destination varchar(20),coachType

varchar(10),primary key(BusNo));

Mysql>desc Bus;

Ticket:
Ticket(TicketNo: string, DOJ: date, Address:string,ContactNo: string, BusNo:String, SeatNo :Integer, Source: String,
Destination: String)
 ColumnName Datatype Constraints Type of Attributes
TicketNo Varchar(20) Primary Key Single-valued

DOJ Date Single-valued

Address Varchar(20) Composite
ContactNo Integer Multi-valued
BusNo Varchar(10) Foreign Key Single-valued
SeatNo Integer Simple
Source Varchar(10) Simple
Destination Varchar(10) Simple

Mysql> create table ticket(ticketno varchar(20), doj date,address varchar(20),contactno int, busno
varchar(20),seatno int,source varchar(10),destination varchar(10),primary key(ticketno,busno) foreign key(busno)
references bus(busno);

Mysql>desc Ticket;

Passenger:
Passenger(PassportID: String, TicketNo:string,Name: String, ContactNo:string,Age: integer, Sex: character,
Address: String);

Type of
ColumnName Datatype Constraints
Attributes
PassportID Varchar(15) Primary Key Single-valued
TicketNo Varchar(20) Foreign Key Single-valued
 Name Varchar(20) Composite
ContactNo Varchar(20) Multi-valued
Age Integer Single-valued
Sex character Simple
Address Varchar(20) Composite

Mysql> Create table passenger(passportID varchar(15) ,TicketNo varchar(15),Name varchar(15),ContactNo

varchar(20),Age integer, sex char(2),address varchar(20), primary key(passportID,TicketNo),foreign
key(TicketNo) references Ticket(TicketNo));

Mysql> desc passenger;

Reservation:

Reservation(PNRNo: String, DOJ: Date, NoofSeats: integer , Address: String ,ContactNo:

String, , BusNo: String,SeatNo:Integer)

ColumnName Datatype Constraints Type of Attributes

PNRNo Varchar(20) Primary Single-valued
Key
DOJ date Single-valued
No_of_Seats Integer Simple
Address Varchar(20) Composite
ContactNo Varchar(10) Multi-valued
 BusNo Varchar(10) Foreign Single-valued
Key
SeatNo Integer Simple
Mysql> Create table Resevation(PNRNo varchar(20),DOJ date,NoofSeates integer,Address varchar(20),ContactNo
varchar(20),BusNo varchar(20),SeatNo integer, primary key(PNRNo,BusNo),foreign key(BusNo) references
Bus(BusNo));

Mysql> desc reservation;

Cancellation:

Cancellation (PNRNo: String,DOJ: Date, SeatNo: integer,ContactNo: String,Status:

String)

ColumnName Datatype Constraints Type of Attributes

PNRNo Varchar(10) Primary Key Single-valued
DOJ date Single-valued
SeatNo Integer Simple
ContactNo Varchar(15) Multi-valued
Status Varchar(10) Simple

Mysql> create table cancellation(PNRNo varchar(10),DOJ date,SeatNo integer, ContactNo

varchar(15),Status varchar(10), primary key(PNRNo), foreign key(PNRNo) references
reservation(PNRNo));

Mysql> desc cancellation;

 EXPERIMENT – 3
NORMALIZATION

AIM: Apply the database Normalization techniques for designing relational database tables to minimize
duplication of information like 1NF, 2NF, 3NF, BCNF.

Normalization is a process of converting a relation to be standard form by decomposition a larger relation into
smaller efficient relation that depicts a good database design.

 1NF: A Relation scheme is said to be in 1NF if the attribute values in the relation are atomic.i.e., Mutli –valued
attributes are not permitted.

 2NF: A Relation scheme is said to be in 2NF,iff and every Non-key attribute is fully functionally dependent on
primary Key.

 3NF: A Relation scheme is said to be in 3NF,iff and does not have transitivity dependencies. A Relation is said
to be 3NF if every determinant is a key for each & every functional dependency.

 BCNF: A Relation scheme is said to be BCNF if the following statements are true for eacg FD P->Q in set F of
FDs that holds for each FD. P->Q in set F of FD’s that holds over R. Here P is the subset of attributes of R & Q
is a single attribute of R.

The given FD is a trival

P is a super key.
 Normalized tables are:-

Mysql> create table Bus2(BusNo varchar(20) primary key,Source varchar(20),Destination varchar(20));

Mysql>Create table passenger4(PPN varchar(15) Primary key,Name varchar(20),Age integer,Sex char,Address

varchar(20));

Mysql> Create table PassengerTicket(PPN varchar(15) Primary key,TicketNo integer);

Mysql> Create table Reservation2(PNRNO integer Primary key, JourneyDate DateTime,NoofSeats int,Address
varchar(20),ContactNo Integer);

Mysql> create table Cancellation2(PNRNO Integer primary key,JourneyDate DateTime,NoofSeats

Integer,Address varchar(20),ContactNo Integer,foreign key(PNRNO) references Reservation2(PNRNO));

Mysql> Create table Ticket2(TicketNo Integer Primary key,JourneyDate DateTime, Age Int(4),Sex char(2),Source
varchar(20),Destination varchar(20),DeptTime varchar(2));
 EXPERIMENT – 4
PRACTICING DDL COMMANDS

AIM : Creating Tables and altering the Tables

Mysql>Create table passenger2(passportId Integer Primary Key,Name varchar(10) Not

Null,Age Integer Not Null,Sex char,Address varchar(20) Not Null);

Mysql> desc passenger2;

USING ALTER COMMAND

Adding Extra column to Existing Table

Mysql>Alter table passenger3 add column TicketNo varchar(10);

Mysql>Alter Table passenger3 add Foreign key(TicketNo) references Ticket(TicketNo);

Mysql>Alter Table passenger3 Modify column Name varchar(20);

Mysql>Alter table passenger drop foreign key fk1;

Mysql> Alter table passenger2 Drop column TicketNo;

 EXPERIMENT – 5
PRACTICING DML COMMANDS

AIM: Create a DML Commands are used to manage data within the scheme objects.

DML Commands:

INSERT COMMAND ON BUS2 & PASSENGER2 RELATIONS

mysql> select * from Bus2; Empty set (0.00 sec)

mysql> insert into Bus2 values(1234,'Hyderabad','Tirupathi');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Bus2 values(2345,'Hyderabad','Banglore');

Query OK, 1 row affected (0.01 sec)

mysql> insert into Bus2 values(23,'Hyderabad','Kolkata');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Bus2 values(45,'Tirupathi','Banglore');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Bus2 values(34,'Hyderabad','Chennai');

Query OK, 1 row affected (0.03 sec)

mysql> select * from Bus2;
mysql> select * from Passenger2;

Empty set (0.00 sec)

mysql> insert into Passenger2 values(145,'Ramesh',45,'M','abc123');

Query OK, 1 row affected (0.05 sec)

mysql> insert into Passenger2 values(278,'Geetha',36,'F','abc124');

Query OK, 1 row affected (0.02 sec)

mysql> insert into Passenger2 values(4590,'Ram',30,'M','abc12');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Passenger2 values(6789,'Ravi',50,'M','abc14');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Passenger2 values(5622,'Seetha',32,'F','abc55');

Query OK, 1 row affected (0.03 sec)

mysql> select * from Passenger2;

 UPDATE COMMAND ON BUS2 RELATION

UPDATE Selected Rows & Multiple Rows

mysql> Update Bus2 SET Source='Secundrabad' where BusNo=1234; Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

 DELETE COMMAND ON BUS2 RELATION

DELETES Selected Rows and Multiple Rows

mysql> Delete from Bus2 where BusNo=1234; Query OK, 1 row affected (0.05 sec)

mysql> select * from Bus2;

mysql> Delete from Bus2 where Source=’Secundrabad’; Query OK, 1 row affected (0.05 sec)

mysql> select * from Bus2;

 EXPERIMENT – 6
Querying (using ANY, ALL, IN, Exists, NOT EXISTS, UNION, INTERSECT, Constraints etc.)

Aim: Practice the following Queries:

1. Display unique PNR_NO of all passengers

2. Display all the names of male passengers.
3. Display the ticket numbers and names of all the passengers.
4. Find the ticket numbers of the passengers whose name start with ‘r’ and ends with ‘h’.
5. Find the names of Passengers whose age is between 30 and 45.
6. Display all the passengers names beginning with ‘A’.
7. Display the sorted list of Passengers names
mysql> insert into passenger2 values(82302,'Smith',23,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82303,'Neha',23,'F','Hyderabad');

Query OK, 1 row affected (0.01 sec)

mysql> insert into passenger2 values(82304,'Neha',35,'F','Hyderabad');

Query OK, 1 row affected (0.03 sec)

mysql> insert into passenger2 values(82306,'Ramu',40,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82308,'Aakash',40,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82402,'Aravind',42,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82403,'Avinash',42,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82502,'Ramesh',23,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

mysql> insert into passenger2 values(82602,'Rajesh',23,'M','Hyderabad');

Query OK, 1 row affected (0.02 sec)

RESERVATION2

mysql> insert into reservation2 values(10201,'2012-02-20 10:20:25',05,'HYD',9654 235242);

Query OK, 1 row affected (0.03 sec)

mysql> insert into reservation2 values(10202,'2012-02-22 10:22:25',05,'HYD',9654 232451);

Query OK, 1 row affected (0.02 sec)

mysql> insert into reservation2 values(10203,'2012-03-22 10:30:25',05,'DELHI',96 54587960);

Query OK, 1 row affected (0.01 sec)

mysql> insert into reservation2 values(10204,'2013-03-22 11:30:25',05,'CHENNAI', 9845761254);

Query OK, 1 row affected (0.02 sec)

1. Display unique PNR_NO of all reservation Mysql>Select

DISTINCT PNR_NO from Reservation;

PNR_No
10201
10202
10203
10204
2. Display all the names of male passengers.

mysql> Select p.name from passenger2 p

where p.passportid IN (select p2.passportid from passenger2 p2
where p2.sex='M');
3. Display the ticket numbers and names of all the passengers.

mysql> select t.ticketno,p.name from passengerticket t,passenger2 p where t.passportid = p.passportid;

4. Find the ticket numbers of the passengers whose name start with ‘r’ and ends with ‘h’.

MySQL> SELECT Name FROM Passenger WHERE name LIKE ‘R%H’

Name
Rajesh
Ramesh
Ramesh
5. Find the names of Passengers whose age is between 30 and 45.

MySQL> SELECT Name FROM PASSENGER WHERE AGE BETWEEN 30 AND 45

6. Display all the passengers names beginning with ‘A’.

MySQL> SELECT * FROM PASSENGER WHERE NAME LIKE ‘A%’;

Name
Akash
Arivind
Avinash
7. Display the sorted list of Passengers names

MySQL> SELECT NAME FROM PASSENGER ORDER BY NAME;

 EXPERIMENT – 7
Querying Aggregate Functions(COUNT,SUM,AVG,MAX and MIN)

Aim: To Practice Queries using Aggregate functions for the following

1. Write a Query to display the information present in the passenger and cancellation
tables
2. Display the number of days in a week on which the AP123 bus is available
3. Find number of tickets booked for each PNR_No using GROUP BY CLAUSE
4. Find the distinct PNR Numbers that are present.

1. Write a Query to display the information present in the passenger and cancellation tables

MYSQL> CREATE TABLE CANCELLATION2(PNRNO INT PRIMARY KEY,JOURNEYDATE DATETIME,

NOOFSEATS INT,ADDRESS VARCHAR(20),CONTACTNO INT,STATUS VARCHAR(10),FOREIGN
KEY(PNRNO) REFERENCES RESERVATION2(PNRNO));

mysql> INSERT INTO CANCELLATION2 VALUES(10201,'2012-02-20

10:20:25',2,'HYD',9654235242,'CONFIRM');

mysql> INSERT INTO CANCELLATION2 VALUES(10202,'2012-02-22

10:22:25',2,'HYD',9654232451,'CONFIRM');

mysql> INSERT INTO CANCELLATION2 VALUES(10203,'2012-03-22

10:30:25',2,'DELHI',9654587960,'CONFIRM');
MySQL> SELECT * FROM RESERVATION UNION

SELECT * FROM CANCELLATION;

2. Display the Minimum age of the Passenger

MySQL> SELECT MIN(AGE) as MINAGE FROM PASSENGER;

3. Find number of tickets booked for each PNR_No using GROUP BY CLAUSE

MySQL> SELECT PNRNO,SUM(No_of_SEATS) AS SUM_OF_SEATS FROM

RESERVATION2 GROUP BY PNRNO;

4 Find the distinct PNR Numbers that are present.

MySQL> SELECT DISTINCT PNR_NO FROM RESERVATION2;

 5 Mysql> select sum(Noofseats) from Cancellation2;

6 Find the total number of cancelled seats.

MySQL> select sum(noofseats) as canceled_seats from cancellation2;

Creation and Droping of Views

mysql> create table students(sid int primary key,name varchar(15),login varchar(15), age
int,gpa real); mysql> create table Enrolled(sid int,cid int,grade varchar(5),primary
key(sid,cid), foreign key(sid) references students(sid));

mysql>create view BStudents(name,sid,course) AS SELECT

s.name,s.sid,E.cid from students s,enrolled E where s.sid=e.sid AND

E.grade='B';

Syntax: Drop view viewname;

Mysql> Drop view Bstudents; Mysql> Drop view Goodstudents;

DEPARTMENT OF CSE DBMS

 EXPERIMENT – 8
TRIGGERS
Aim: Creation of insert trigger, delete trigger and update trigger.

MySQL>CREATE TABLE BUS(BUSNO VARCHAR(10) NOT NULL, SOURCE

VARCHAR(10), DESTINATION VARCHAR(10), CAPACITY INT(2), PRIMARY
KEY(BUSNO));

MySQL>INSERT INTO BUS VALUES('AP123','HYD','CHENNAI','40');

CREATE TABLE BUS_AUDIT1(ID INT NOT NULL AUTO_INCREMENT, SOURCE

VARCHAR(10) NOT NULL, CHANGEDON DATETIME DEFAULT NULL, ACTION
VARCHAR(10) DEFAULT NULL, PRIMARY KEY(ID));

DEPARTMENT OF CSE DBMS

CREATE TRIGGER BEFORE_BUS_UPDATE BEFORE UPDATE ON BUS

FOR EACH ROW BEGIN

INSERT INTO BUS_AUDIT1

SET action='update', source=OLD.source, changedon=NOW(); END$$

UPDATE :

MySQL>UPDATE BUS SET SOURCE='KERALA' WHERE BUSNO='AP123'$$

DEPARTMENT OF CSE DBMS

 SNo Source Changedon Action
1 Banglore 2014:03:23 12:51:00 Insert
2 Kerela 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete

INSERT:

CREATE TRIGGER BEFORE_BUS_INSERT BEFORE INSERT ON BUS

FOR EACH ROW BEGIN

INSERT INTO BUS_AUDIT1

SET action='Insert', source=NEW.source, changedon=NOW();

END$$ MYSQL>INSERT INTO BUS

VALUES('AP789','VIZAG','HYDERABAD',30)$$

SNo Source Changedon Action

1 Banglore 2014:03:23 12:51:00 Insert
2 Kerela 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete

DEPARTMENT OF CSE DBMS

CREATE TRIGGER BEFORE_BUS_DELETE BEFORE DELETE ON BUS

FOR EACH ROW BEGIN

DELETE FROM BUS_AUDIT1

SET action='Insert', source=NEW.source, changedon=NOW();

END$$DELETE FROM BUS WHERE SOURCE=’HYDERABAD’$$

SNo Source Changedon Action

1 Banglore 2014:03:23 12:51:00 Insert
2 Kerela 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete

Examples

CREATE TRIGGER updcheck1 BEFORE UPDATE ON passengerticket FOR EACH ROW

BEGIN

IF NEW.TicketNO > 60 THEN

SET New.TicketNo = New.TicketNo; ELSE

SET New.TicketNo = 0; END IF;

END;

DEPARTMENT OF CSE DBMS

DEPARTMENT OF CSE DBMS
 EXPERIMENT – 9
PROCEDURES

Aim: Creation of stored Procedures and Execution of Procedures and Modification of

Procedures.

Ex1:

CREATE PROCEDURE BUS_PROC1() BEGIN

SELECT * FROM BUS;

END$$

CALL BUS_PROC1()$$

Ex2:
CREATE PROCEDURE SAMPLE2() BEGIN
DECLARE X INT(3); SET X=10;
SELECT X;

END$$
Mysql> CALL SAMPLE2()$$

DEPARTMENT OF CSE DBMS

Ex3: CREATE PROCEDURE SIMPLE_PROC(OUT PARAM1 INT) BEGIN

SELECT COUNT(*) INTO PARAM1 FROM BUS;

END$$

Mysql> CALL SIMPLE_PROC(@a)$$ Mysql> select @a;

DEPARTMENT OF CSE DBMS

 EXPERIMENT – 10
Cursors
Aim: Declare a cursor that defines a result set. Open the cursor to establish the result set.
Fetch the data into local variables as needed from the cursor, one row at a time. Close the
cursor when done.

Cursors
In MySQL, a cursor allows row-by-row processing of the result sets. A
cursor is used for the result set and returned from a query. By using a
cursor, you can iterate, or by step through the results of a query and
perform certain operations on each row. The cursor allows you to iterate
through the result set and then perform the additional processing only on
the rows that require it.

In a cursor contains the data in a loop. Cursors may be different from

SQL commands that operate on all the rows in the returned by a
query at one time.

There are some steps we have to follow, given below :

□ Declare a cursor

□ Open a cursor statement

□ Fetch the cursor

□ Close the cursor

1 . Declaration of Cursor : To declare a cursor you must use the

DECLARE statement. With the help of the variables, conditions and
handlers we need to declare a cursor before we can use it. first of all we will
give the cursor a name, this is how we will refer to it later in the procedure.
We can have more than one cursor in a single procedure so its necessary to
give it a name that will in some way tell us what its doing. We then need to
specify the select statement we want to associate with the cursor. The SQL
statement can be any valid SQL statement and it is possible to use a
dynamic where clause using variable or parameters as we have seen
previously.

DEPARTMENT OF CSE DBMS

 Syntax : DECLARE cursor_name CURSOR FOR select_statement;

2 . Open a cursor statement : For open a cursor we must use the open
statement.If we want to fetch rows from it you must open thecursor.

Syntax : OPEN cursor_name;

3 . Cursor fetch statement : When we have to retrieve the next row

from the cursor and move the cursor to next row then you need to fetch
the cursor.

Synatx : FETCH cursor_name INTO var_name;

If any row exists, then the above statement fetches the next row and cursor
pointer moves ahead to the next row.

4 . Cursor close statement : By this statement closed the open cursor.

Syntax: CLOSE_name;

By this statement we can close the previously opened cursor. If it is not

closed explicitly then a cursor is closed at the end of compound statement
in which that was declared.

Delimiter $$

Create procedure p1(in_customer_id int) begin

declare v_id int;
declare v_name varchar(20); declare v_finished integer default 0;
declare c1 cursor for select sid,sname from students where sid=in_customer_id; declare
continue handler for NOT FOUND set v_finished=1;
open c1; std:LOOP
fetch c1 into v_id,v_name; if v_finished=1 then
leave std; end if;
select concat(v_id,v_name); end LOOP std;
close c1; end;

DEPARTMENT OF CSE DBMS

DEPARTMENT OF CSE DBMS
 ADDITIONAL PROGRAMMS

EMPLOYEES TABLE

mysql> create table Employees(ssn varchar(15),name varchar(20),lot int,PRIMARY

KEY(ssn)); mysql> insert into Employees values('123-22-3666','Attishoo',48);

mysql> insert into Employees values('321-31-5368','Smiley',22); mysql> insert into

Employees values('131-24-3650','Smethurst',35);

DEPARTMENT OF CSE DBMS

DEPARTMENT TABLE

mysql> create table Departments(did int,dname varchar(10),budget real, PRIMARY

KEY(did));

mysql> insert into Departments values(05,'CSE',500000);

mysql> insert into Departments values(04,'ECE',400000);

mysql> insert into Departments values(03,'ME',300000);

mysql> insert into Departments values(01,'CE',100000);

DEPARTMENT OF CSE DBMS

 Sailors , Reserves , Boats Tables

Mysql> Create table Sailors(Sid integer PRIMARY KEY,sname varchar(15), rating int,age
real); Mysql>Create table Reserves(Sid int,Bid int,Day Date);

Mysql>Create table Boats(Bid int,Bname varchar(15),Color varchar(15);

DEPARTMENT OF CSE DBMS

mysql> select S.sname from sailors S, reserves R where S.sid=R.sid AND R.bid=103;

mysql> select sname from sailors s,Reserves R where S.sid=R.sid AND bid=103; mysql>
select R.sid from Boats B,Reserves R where B.bid=R.bid AND B.color='red';

mysql> select S.sname from sailors S,reserves R,Boats B where S.sid=R.sid AND
R.bid=B.bid AND B.color='red';

mysql> select B.color from Sailors S,Reserves R,Boats B where S.sid=R.sid AND
R.bid=B.bid AND S.sname='Lubber';

DEPARTMENT OF CSE DBMS

mysql> select S.sname,S.rating+1 AS rating from Sailors S,Reserves R1,Reserves R2 where
S.sid=R1.sid AND S.sid=R2.sid AND R1.day=R2.day AND R1.bid<>R2.bid;

mysql> select S1.sname AS name1,S2.sname AS name2 from sailors S1,sailors S2 where

2*S1.rating=S2.rating-1;

DEPARTMENT OF CSE DBMS

 USING UNION , INTERSECT , AND EXCEPT

1).Find the names of sailors who have reserved a red or a green boat.

OR
2). Find the names of sailors who have reserved both a red and a green boat.

SELECT S.SNAME

FROM SAILORS S,RESERVES R,BOATS B

WHERE S.SID=R.SID AND R.BID=B.BID AND B.COLOR='red' INTERSECT

SELECT S2.SNAME

FROM SAILORS S2,RESERVES R2,BOATS B2

WHERE S2.SID=R2.SID AND R2.BID=B2.BID AND B2.COLOR='green';

NESTED QUERIES

1) Find the Names of sailors who have reserved boat 103

2) Find the names of Sailors who have reserved a red Boat

DEPARTMENT OF CSE DBMS

3) Find the names of Sailors who have NOT reserved a red Boat

Correlated Nested Queries:

1) Find the names of Sailors who have reserved a red Boat

Set Comparison Operators:

1) Find sailors whose rating is better than some sailor called Horatio

DEPARTMENT OF CSE DBMS

2) Find the sailors with the highest rating.

mysql> SELECT S.sid FORM Sailors WHERE S.rating>=ALL(SELECT S2.rating FROM

Sailors S2);

The GROUP BY and HAVING Clauses:

1) Find the age of the youngest sailor for each rating level.

2) Find the age of the youngest sailor who is eligible to vote for each rating level with at
least two such sailors

DEPARTMENT OF CSE DBMS

 3) For each red boat , find the number of reservations for this boat

4) Find the average age of sailors for each rating level that has at least two sailors

DEPARTMENT OF CSE DBMS

 SELECT MAX(column_name)
FROM table_name
WHERE condition;

 COUNT():The COUNT() function returns the number of rows that matches a specified
criteria.

Syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

 AVG():The AVG() function returns the average value of a numeric column.

Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

 SUM():The SUM() function returns the total sum of a numeric column.

Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

 ORDERBY(): The ORDER BY keyword is used to sort the result-set in ascending or

descending order. The ORDER BY keyword sorts the records in ascending order by default.
To sort the records in descending order, use the DESC keyword.

Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

What is operators?
An operator is a reserved word or a character used primarily in an SQL statement's WHERE
clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are
DEPARTMENT OF CSE DBMS
used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions
in a statement.

The Operators are: AND, OR, NOT, BETWEEN, LESS THAN,GREATER THAN.

 AND: The WHERE clause can be combined with AND operators. The AND operators are
used to filter records based on more than one condition. The AND operator displays a record
if all the conditions separated by AND is TRUE.

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

 OR: The WHERE clause can be combined with OR operators. The OR operators are used to
filter records based on more than one condition: The OR operator displays a record if any of
the conditions separated by OR is TRUE.

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

 NOT:The WHERE clause can be combined with AND, OR, and NOT operators.The NOT
operator displays a record if the condition(s) is NOT TRUE.

Syntax:

SELECT column1, column2, ...

FROM table_name
WHERE NOT condition;

 BETWEEN: The BETWEEN operator selects values within a given range. The values can
be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are
included.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

 LESS THAN: Less than operator is used to find out less value from table. We can used
WHERE clause.

Syntax:
DEPARTMENT OF CSE DBMS
 SELECT * from table_name WHERE condition;

 GREATER THAN: Greater than operator is used to find out greater value from table. We
can used WHERE clause.

Syntax:
SELECT * from table_name WHERE condition;

Conclusion: We have studied SQL DML commands and SQL Functions and Operators and perform
all the queries on database table.

DEPARTMENT OF CSE DBMS

 Assignment No-4
Title:- To design SQL queries using join operation.
Objective:-Understand the concept of SQL JOIN operation and its types, to Design SQL queries
using join operation .
Theory:-
JoIn:-
The SQL Joins clause is used to combine records from two or more tables in a database. A
JOIN is a means for combining fields from two tables by using values common to each. Consider the
following two tables
Types of join:-

 INNER JOIN

 OUTER JOIN
 Left outer join
 Right outer join
 full outer join

 NATURAL JOIN

 CROSS JOIN or CARTESION JOIN

1.) INNER JOIN:-
This is a simple JOIN in which the result is based on matched data as per the
equality condition specified in the query.
Inner Join Syntax is:-
SELECT column-name-list
from table-name1
INNER JOIN
table-name2
WHERE table-name1.column-name = table-name2.column-name;

Inner JOIN query will be,

SELECT * from class, class_info where class.id = class_info.id;

2.) Natural JOIN:-

Natural Join is a type of Inner join which is based on column having same name and same
datatype present in both the tables to be joined.
Natural Join Syntax is:-
DEPARTMENT OF CSE DBMS
SELECT *
from table-name1
NATURAL JOIN
table-name2;

3.) Outer JOIN:-

Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,

 Left Outer Join

 Right Outer Join
 Full Outer Join
Left Outer Join:-
The left outer join returns a result table with the matched data of two tables then remaining rows of
the left table and null for the right table's column.

Left Outer Join syntax is,

SELECT column-name-list
from table-name1
LEFT OUTER JOIN
table-name2
on table-name1.column-name = table-name2.column-name;

Left outer Join Syntax for Oracle is,

select column-name-list
from table-name1,
table-name2
on table-name1.column-name = table-name2.column-name(+);

Right Outer Join:-

The right outer join returns a result table with the matched data of two tables then remaining
rows of the right table and null for the left table's columns.
Right Outer Join Syntax is,
select column-name-list
from table-name1
RIGHT OUTER JOIN
table-name2
on table-name1.column-name = table-name2.column-name;

Right outer Join Syntax for Oracle is,

select column-name-list
from table-name1,
table-name2
on table-name1.column-name(+)
DEPARTMENT OF CSE = table-name2.column-name; DBMS
Full Outer Join:-
The full outer join returns a result table with the matched data of two table then remaining
rows of both left table and then the right table.
Full Outer Join Syntax is,
select column-name-list
from table-name1
FULL OUTER JOIN
table-name2
on table-name1.column-name = table-name2.column-name;

4.) CROSS JOIN/CARTESION JOIN:-

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records
from two or more joined tables. Thus, it equates to an inner join where the join-condition always
evaluates to either True or where the join-condition is absent from the statement.

Syntax
The basic syntax of the CARTESIAN JOIN or the CROSS JOIN is as follows −
SELECT table1.column1, table2.column2...
FROM table1, table2 [, table3 ]

Conclusion: Here we understood the concept of SQL join operation and its types. Learned how to
design sub queries using join.

DEPARTMENT OF CSE DBMS

 Case study on PLSQL
Q1. WHAT IS PL/SQL?
ANS: In Oracle database management, PL/SQL is a procedural language extension to Structured
Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural
programming language.
Q2. FEATURES OF PLSQL?
ANS: PL/SQL has the following features −
• PL/SQL is tightly integrated with SQL.
• It offers extensive error checking.
• It offers numerous data types.
• It offers a variety of programming structures.
• It supports structured programming through functions and procedures.
• It supports object-oriented programming.
• It supports the development of web applications and server pages.

Q3.DIFFERENCE BETWEEN SQL AND PL/SQL?

ANS:
S. No. SQL PL/SQL
SQL Stands for Structured Query PL/SQL stands for Programming Language
1.
Language SQL.
SQL is used to execute single query PL/SQL is used to execute block of code or
2.
or statement at a time. program that have multiple statements.
SQL tells the database what to do
PL/SQL tells the database how to do things. So
3. but not how to do. So it is
it is procedural.
declarative.
SQL can be used in PL/SQL
4. PL/SQL can‟t be used in SQL statement.
programs.
SQL is used with various database
5. systems like MySQL, SQL Server, PL/SQL is used only with Oracle database.
Oracle, DB2, etc.
An example of PL/SQL program is given
below.
An example of SQL query is given
below. BEGIN
6.
SELECT * FROM Customers; dbms_output.put_line(„Hello Workd‟);

END;/

Q4.WHAT ARE THE ADVANTAGES OF PL/SQL?

DEPARTMENT OF CSE DBMS
ANS: Advantages of PL/SQL are as follows:
 1. PL/SQL is structured as it consists of blocks of code and hence streamlined. This makes
PL/SQL highly productive.
2. It is highly portable, has immense error handling mechanisms.
3. High performance as lines of code can be sent to oracle. This reduces traffic.
4. With the user of stored procedures, PL/SQL is highly secured.
5. Extremely flexible and easy to learn with syntaxes like SELECT, INSERT, UPDATE etc.
6. Support SQL data manipulation.
7. Provide facilities like conditional checking, branching and looping.
8. Provide fast code execution since it sends SQL statement as a block to the oracle engine.

Q5. ARCHITECTURES OF PL/SQL?

ANS:

PL/SQL Engine:
The PL/SQL compilation and run-time system is an engine that compiles and runs PL/SQL units.
The engine can be installed in the database or in an application development tool, such as Oracle
Forms.
In either environment, the PL/SQL engine accepts as input any valid PL/SQL unit. The engine runs
procedural statements, but sends SQL statements to the SQL engine in the database, as shown in
figure.
Typically, the database processes PL/SQL units.
When an application development tool processes PL/SQL units, it passes them to its local PL/SQL
engine. If a PL/SQL unit contains no SQL statements, the local engine processes the entire PL/SQL
unit. This is useful if the application development tool can benefit from conditional and iterative
control.
For example, Oracle Forms applications frequently use SQL statements to test the values of field
entries and do simple computations. By using PL/SQL instead of SQL, these applications can avoid
calls to the database.
PL/SQL Units and Compilation Parameters
A PL/SQL unit is one of these:
DEPARTMENT OF CSE DBMS
• PL/SQL anonymous block
 • FUNCTION
• LIBRARY
• PACKAGE
• PACKAGE BODY
• PROCEDURE
• TRIGGER
• TYPE
• TYPE BODY
Q6.BLOCK STRUCTURE OF PL/SQL?
ANS:

Block Structure
PL/SQL blocks have a pre-defined structure in which the code is to be grouped. Below are different
sections of PL/SQL blocks
• Declaration section
• Execution section
• Exception-Handling section
The below picture illustrates the different PL/SQL block and their section order.

Declaration Section
This is the first section of the PL/SQL blocks. This section is an optional part. This is the section in
which the declaration of variables, cursors, exceptions, subprograms, pragma instructions and
collections that are needed in the block will be declared. Below are few more characteristics of this
part.
• This particular section is optional and can be skipped if no declarations are needed.
• This should be the first section in a PL/SQL block, if present.
DEPARTMENT OF CSE DBMS
 • This section starts with the keyword 'DECLARE' for triggers and anonymous block. For
other subprograms this keyword will not be present, instead the part after the subprogram
name definition marks the declaration section.
• This section should be always followed by execution section.
Execution Section
Execution part is the main and mandatory part which actually executes the code that is written inside
it. Since the PL/SQL expects the executable statements from this block this cannot be an empty
block, i.e., it should have at least one valid executable code line in it. Below are few more
characteristics of this part.
• This can contain both PL/SQL code and SQL code.
• This can contain one or many blocks inside it as a nested blocks.
• This section starts with the keyword 'BEGIN'.
• This section should be followed either by 'END' or Exception-Handling section (if present)
Exception-Handling Section:
The exception are unavoidable in the program which occurs at run-time and to handle this Oracle has
provided an Exception-handling section in blocks. This section can also contain PL/SQL statements.
This is an optional section of the PL/SQL blocks.
• This is the section where the exception raised in the execution block is handled.
• This section is the last part of the PL/SQL block.
• Control from this section can never return to the execution block.
• This section starts with the keyword 'EXCEPTION'.
• This section should be always followed by the keyword 'END'.
The Keyword 'END' marks the end of PL/SQL block. Below is the syntax of the PL/SQL block
structure.

Q7. WRITE INSTALLATION PROCEDURE OF PL/SQL ON WINDOWS OR FEDORA:

ANS: Installation Steps of PL/SQL on windows7/8/10.
1. Download Oracle 11g Express Edition and SQL Developer software from oracle.com from
official site.

2. Extract the .rar files of Oracle 11g Express Edition and SQL at specified location of your system..
DEPARTMENT OF CSE DBMS
3. Open DISK1 folder which is extracted file of Oracle 11g Express Edition .
Then right click on setup icon , run as administrator.
4. It will start installing click on next.

5. Then click on “I accept the terms in the license aggrement”.Then click on next.

6. Specify the installation location .

DEPARTMENT OF CSE DBMS
7. Enter password here.
Password:system.

8. Click on next and install.

DEPARTMENT OF CSE DBMS

9. Click on Finish.

DEPARTMENT OF CSE DBMS

 Oracle Database11g Express Edition - Install Wizard

InstallShield \ lizard Complete

Setup has finished installing Oracle Database 11g Express

Edition on your computer.

DATABASE
EXPRE S S E D ITION

Back Finnish Cancel

DEPARTMENT OF CSE DBMS

 Assignment No:-5

Title:- Unnamed PL/SQL code block: Use of Control structure and Exception handling is
mandatory. Write a PL/SQL block of code for the following requirements:-
Schema:
1. Borrower(Roll_no, Name, DateofIssue, NameofBook, Status)
2. Fine(Roll_no,Date,Amt)
 Accept roll_no & name of book from user.
 Check the number of days (from date of issue), if days are between 15 to 30 then fine amount
will be Rs 5per day.
 If no. of days>30, per day fine will be Rs 50 per day & for days less than 30, Rs. 5 per day.
 After submitting the book, status will change from I to R.
 If condition of fine is true, then details will be stored into fine table.

Objective:-To understand the of Control structure and Exception handling of pl/sql block structure.
Theroy:-
Q. 1.What is an anonymous/ Unnamed block in PL SQL?

ANS:-
The PL/SQL anonymous block statement is an executable statement that can contain
PL/SQL control statements and SQL statements. It can be used to implement procedural logic in a
scripting language. ... The exception section must begin with the keyword EXCEPTION, and
continues until the end of the block in which it appears.

Unnamed block statement (PL/SQL)

The PL/SQL anonymous block statement is an executable statement that can contain PL/SQL
control statements and SQL statements. It can be used to implement procedural logic in a scripting
language. In PL/SQL contexts, this statement can be compiled and executed by the DB2® data
server.

The anonymous block statement, which does not persist in the database, can consist of up to three
sections: an optional declaration section, a mandatory executable section, and an optional exception
section.

The optional declaration section, which can contain the declaration of variables, cursors, and types
that are to be used by statements within the executable and exception sections, is inserted before the
executable BEGIN-END block.

The optional exception section can be inserted near the end of the BEGIN-END block. The
exception section must begin with the keyword EXCEPTION, and continues until the end of the
block in which it appears.

Description:-

DECLARE:-

DEPARTMENT OF CSE DBMS

 An optional keyword that starts the DECLARE statement, which can be used to declare data
types, variables, or cursors. The use of this keyword depends upon the context in which the
block appears.
declaration
Specifies a variable, cursor, or type declaration whose scope is local to the block. Each
declaration must be terminated by a semicolon.
BEGIN:-
A mandatory keyword that introduces the executable section, which can include one or more
SQL or PL/SQL statements. A BEGIN-END block can contain nested BEGIN-END blocks.
statement
Specifies a PL/SQL or SQL statement. Each statement must be terminated by a semicolon.
EXCEPTION:-
An optional keyword that introduces the exception section.
WHEN exception-condition
Specifies a conditional expression that tests for one or more types of exceptions.
THEN handler-statement
Specifies a PL/SQL or SQL statement that is executed if a thrown exception matches an
exception in exception-condition. Each statement must be terminated by a semicolon.
END:-
A mandatory keyword that ends the block.

Design and Implementation:

Create borrower table:-
Create table borrower(roll_no number(5),name varchar2(20),Dateofissue date,NameBook
varchar2(20),status varchar(20));

Insert record in borrower table:-

Insert into borrower values(1,‟om‟,‟18-sep-2017‟,‟DBMS‟,‟I‟); Insert into
borrower values(2,‟priya‟,‟19-sep-2017‟,‟C++‟,‟I‟); Insert into
borrower values(3,‟omkar‟,‟20-sep-2017‟,‟java‟,‟I‟); Create fine
table:
Create table fine(roll_no number(5),sdate date,Amt number(5));

Write PROCEDURE PL/SQL Block:-

DECLARE
Roll_No NUMBER(3);
BookName varchar2(50);
IssueDate DATE;
DEPARTMENT OF CSE DBMS
CurrentDate DATE;
 NoOfDays Number(2);
Amount Number;
BEGIN
DBMS_OUTPUT.PUT_LINE('Enter Student Roll Number');
Roll_No := &rollno;
DBMS_OUTPUT.PUT_LINE('Enter Book Name');
BookName := '&bookname';
CurrentDate := trunc(SYSDATE);
SELECTDateOfIssue into IssueDate FROM Borrower WHERE RollNo = Roll_No AND
NameOfBook =BookName;
SELECTtrunc(SYSDATE) - IssueDate INTO NoOfDays from dual;
DBMS_OUTPUT.PUT_LINE('No of Days' || NoOfDays);
IF (NoOfDays> 30) THEN
Amount := NoOfDays * 50;
ELSIF (NoOfDays>= 15 AND NoOfDays<=30) THEN
Amount := NoOfDays * 5;
END IF;
IF Amount > 0 THEN
INSERT INTO Fine values (Roll_No, sysdate, Amount);
END IF;
UPDATE Borrower SET Status = 'R' WHERE RollNo=Roll_No;
END;
* /END OF PROCEDURE/*
Execute procedure
Conclusion:- Here we understood the concept of unnamed block of PL/SQL structure. Designed the
library due management application using unnamed block of PL/SQL.

DEPARTMENT OF CSE DBMS

 Assignment 6
Title: Cursors: (All types: Implicit, Explicit, Cursor FOR Loop, Parameterized Cursor) 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. Frame the separate problem
statement for writing PL/SQL block to implement all types of Cursors in-line with above statement.
The problem statement should clearly state the requirements.
Objective: Learning the cursor and its type, how to define cursors on database to operate and
manage the database.
Theory:
Definition :
A cursor is a temporary work area created in the system memory when a SQL statement is
executed. A cursor contains information on a select statement and the rows of data accessed by it.
This temporary work area is used to store the data retrieved from the database, and manipulate this
data.
Explanation :
When an SQL statement is processed, Oracle creates a memory area known as context area.
A cursor is a pointer to this context area. It contains all information needed for processing the
statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information on a
select statement and the rows of data accessed by it.
A cursor is used to referred to a program to fetch and process the rows returned by the SQL
statement, one at a time.

Types of Cursor :
There are two types of cursors:
1] Implicit Cursors
2] Explicit Cursors

1] Implicit Cursors :
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed,
when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors
and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is
associated with this statement. For INSERT operations, the cursor holds the data that needs to be
inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be
affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always
has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL
cursor has additional
DEPARTMENT attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS,
OF CSE designed
DBMS
for use with the FORALL statement. The following table provides the description of the most used
attributes −

S.No Attribute & Description

%FOUND

Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or

1
more rows or a SELECT INTO statement returned one or more rows. Otherwise,
it returns FALSE.

%NOTFOUND

The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or

2
DELETE statement affected no rows, or a SELECT INTO statement returned no
rows. Otherwise, it returns FALSE.

%ISOPEN

3 Always returns FALSE for implicit cursors, because Oracle closes the SQL
cursor automatically after executing its associated SQL statement.

%ROWCOUNT

4 Returns the number of rows affected by an INSERT, UPDATE, or DELETE

statement, or returned by a SELECT INTO statement.

NOTE: Any SQL cursor attribute will be accessed as sql%attribute_name

2] Explicit Cursors:
Explicit cursors are programmer-defined cursors for gaining more control over the
context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It
is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps −

 Declaring the cursor for initializing the memory

 Opening the cursor for allocating the memory
 Fetching the cursor for retrieving the data
Closing the
DEPARTMENT OFcursor
CSE to release the allocated memory DBMS
Declaring the Cursor :

Declaring the cursor defines the cursor with a name and the associated SELECT statement.
Syntax for explicit cursor decleration :
1. CURSOR name IS
2. SELECT statement;

Opening the Cursor :

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows
returned by the SQL statement into it.
Syntax for cursor open :
1. OPEN cursor_name;

Fetching the Cursor :

Fetching the cursor involves accessing one row at a time.

Syntax for cursor fetch:
FETCH cursor_name INTO variable_list;

Closing the Cursor :

Closing the cursor means releasing the allocated memory.

Syntax for cursor close :
Close cursor_name;

Advantages of Cursor :
1] Using cursor to getting multiple values.
2] Where Current of Clause: this is use full when the primary key is not present.
3] Cursors can be faster than a while loop but they do have more overhead.
4] we can do RowWise validation or in other way you can perform operation on each Row.

Conclusion: Here we understood the concept of cursor and its types and how to define and use of
cursors on database table to read and fetch the data.

DEPARTMENT OF CSE DBMS

 Assignment No: 7
Title: PL/SQL Stored Procedure and Stored Function.
Write a Stored Procedure namely proc_Grade for the categorization of student. If marks scored by
students in examination is <=1500 and marks>=990 then student will be placed in distinction
category,if marks scored are between 989 and900 category is first class, if marks 899 and 825
category is Higher Second Class Write a PL/SQL block for using procedure created with above
requirement.
Stud_Marks(name, total_marks)
Result(Roll,Name, Class)
Frame the separate problem statement for writing PL/SQL Stored Procedure and function, inline
with above statement.
Objectives: To understand the concept of stored procedure and stored function.
Theory:
Stored procedure:
A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name,
which are stored in a relational database management system as a group, so it can be reused and
shared by multiple programs.
Stored procedures can access or modify data in a database, but it is not tied to a specific database or
object, which offers a number of advantages.
Benefits of using stored procedures:
A stored procedure provides an important layer of security between the user interface and the
database. It supports security through data access controls because end users may enter or change
data, but do not write procedures. A stored procedure preserves data integrity because information is
entered in a consistent manner. It improves productivity because statements in a stored procedure
only must be written once.
Creating a Procedure:
A procedure is created with the CREATE OR REPLACE PROCEDURE statement.
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT]
type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
DEPARTMENT OF CSE DBMS
Where,
- procedure-name specifies the name of the procedure.
- [OR REPLACE] option allows the modification of an existing procedure.
- The optional parameter list contains name, mode and types of the parameters. IN represents the
value that will be passed from outside and OUT represents the parameter that will be used to
return a value outside of the procedure.
- procedure-body contains the executable part.
- The AS keyword is used instead of the IS keyword for creating a standalone procedure.
Stored function:
A stored function (also called a user function or user-defined function) is a set of PL/SQL statements
you can call by name. Stored functions are very similar to procedures, except that a function returns
a value to the environment in which it is called. User functions can be used as part of a SQL
expression.
Creating a Function
A standalone function is created using the CREATE FUNCTION statement.
Syntax:
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT]
type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Where,
-function-name specifies the name of the function.
-[OR REPLACE] option allows the modification of an existing function.
-The optional parameter list contains name, mode and types of the parameters. IN represents
the value that will be passed from outside and OUT represents the parameter that will be used
to return a value outside of the procedure.
-The function must contain a return statement.
-The RETURN clause specifies the data type you are going to return from the function.
- function-body
DEPARTMENT OFcontains
CSE the executable part. DBMS
 -The AS keyword is used instead of the IS keyword for creating a standalone function.
Design And Implementation:
step1:
create a table as stud_marks( rollno , Name ,marks);
step 2:
create a table as Result (rollno , Name, class);
step 3:
write a procedure as proc_grade
If marks scored by students in examination is <=1500 and marks>=990 then student will be placed
indistinction category,if marks scored are between 989 and900 category is first class, if marks 899
and 825 category is Higher Second Class.
Conclusion: Here we understood the concept of stored procedure and function that could be helpful
for accessing and retrieving data efficiently from the database.

DEPARTMENT OF CSE DBMS

 Assignment 8

Title: Database Trigger (All Types: Row level and Statement level triggers, Before and After
Triggers). Write a database trigger on Library table. The System should keep track of the records
that are being updated or deleted. The old value of updated or deleted records should be added in
Library_Audit table. Frame the problem statement for writing Database Triggers of all types, in-line
with above statement. The problem statement should clearly state the requirements.

Objective: Understand the concept of Triggers and use of triggers on database table.

Theory:

Definition :

A trigger is defined for a specific table and one or more events. In most database
management systems you can only define one trigger per table.
Syntax :
-- SQL Server Syntax --
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name

ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
assembly_name.class_name.method_name

DEPARTMENT OF CSE DBMS

Explanation :

What is a Database Trigger?

A database trigger is special stored procedure that is run when specific actions occur within a
database. Most triggers are defined to run when changes are made to a table‟s data. Triggers can be
defined to run instead of or after DML (Data Manipulation Language) actions such as INSERT,
UPDATE, and DELETE.
Triggers help the database designer ensure certain actions, such as maintaining an audit file, are
completed regardless of which program or user makes changes to the data.
The programs are called triggers since an event, such as adding a record to a table, fires their
execution.
Triggers and their implementations are specific to database vendors. In this article we‟ll focus on
Microsoft SQL server; however, the concepts are the same or similar in Oracle and MySQL.
Note: All the examples for this lesson are based on Microsoft SQL Server Management Studio and
the AdventureWorks2012 database. You can get started using these free tools using my
Guide Getting Started Using SQL Server.

Events

The triggers can occur AFTER or INSTEAD OF a DML action. Triggers are associated with the
database DML actions INSERT, UPDATE, and DELETE. Triggers are defined to run when these
actions are executed on a specific table.

Triggering Event or Statement

A triggering event or statement is the SQL statement that causes a trigger to be fired. A triggering
event can be an INSERT, UPDATE, or DELETE statement on a table.
For example, in Figure 15 - 3, the triggering statement is
. . . UPDATE OF parts_on_hand ON inventory . . .

which means that when the PARTS_ON_HAND column of a row in the INVENTORY table is
updated, fire the trigger. Note that when the triggering event is an UPDATE statement, you can
DEPARTMENT
include a columnOFlistCSE DBMSINSERT
to identify which columns must be updated to fire the trigger. Because
and DELETE statements affect entire rows of information, a column list cannot be specified for
these options.
A triggering event can specify multiple DML statements, as in
. . . INSERT OR UPDATE OR DELETE OF inventory . . .

which means that when an INSERT, UPDATE, or DELETE statement is issued against the
INVENTORY table, fire the trigger. When multiple types of DML statements can fire a trigger,
conditional predicates can be used to detect the type of triggering statement. Therefore, a single
trigger can be created that executes different code based on the type of statement that fired the trigger.

Trigger Restriction
A trigger restriction specifies a Boolean (logical) expression that must be TRUE for the trigger to
fire. The trigger action is not executed if the trigger restriction evaluates to FALSE or UNKNOWN.
A trigger restriction is an option available for triggers that are fired for each row. Its function is to
control the execution of a trigger conditionally. You specify a trigger restriction using a WHEN
clause. For example, the REORDER trigger in Figure 15 - 3 has a trigger restriction. The trigger is
fired by an UPDATE statement affecting the PARTS_ON_HAND column of the INVENTORY table,
but the trigger action only fires if the following expression is TRUE:
new.parts_on_hand < new.reorder_point

Trigger Action
A trigger action is the procedure (PL/SQL block) that contains the SQL statements and PL/SQL code
to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.
Similar to stored procedures, a trigger action can contain SQL and PL/SQL statements, define
PL/SQL language constructs (variables, constants, cursors, exceptions, and so on), and call stored
procedures. Additionally, for row trigger, the statements in a trigger action have access to column
values (new and old) of the current row being processed by the trigger. Two correlation names
provide access to the old and new values for each column.

Types of Triggers
When you define a trigger, you can specify the number of times the trigger action is to be executed:
once for every row affected by the triggering statement (such as might be fired by an UPDATE
statement that updates many rows), or once for the triggering statement, no matter how many rows it
affects.
Row Triggers A row trigger is fired each time the table is affected by the triggering statement. For
example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for
each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger
is not executed at all.
Row triggers are useful if the code in the trigger action depends on data provided by the triggering
statement or rows that are affected. For example, Figure 15 - 3 illustrates a row trigger that uses the
values of each row affected by the triggering statement.
Statement Triggers A statement trigger is fired once on behalf of the triggering statement, regardless
of the number of rows in the table that the triggering statement affects (even if no rows are affected).
For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE
trigger is fired only once, regardless of how many rows are deleted from the table.
DEPARTMENT OF CSE DBMS
Statement triggers are useful if the code in the trigger action does not depend on the data provided by
the triggering statement or the rows affected. For example, if a trigger makes a complex security
check on the current time or user, or if a trigger generates a single audit record based on the type of
triggering statement, a statement trigger is used.
BEFORE vs. AFTER Triggers
When defining a trigger, you can specify the trigger timing. That is, you can specify whether the
trigger action is to be executed before or after the triggering statement. BEFORE and AFTER apply
to both statement and row triggers.
BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This
type of trigger is commonly used in the following situations:
• BEFORE triggers are used when the trigger action should determine whether the triggering
statement should be allowed to complete. By using a BEFORE trigger for this purpose, you
can eliminate unnecessary processing of the triggering statement and its eventual rollback in
cases where an exception is raised in the trigger action.
• BEFORE triggers are used to derive specific column values before completing a triggering
INSERT or UPDATE statement.
AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed.
AFTER triggers are used in the following situations:
• AFTER triggers are used when you want the triggering statement to complete before
executing the trigger action.
• If a BEFORE trigger is already present, an AFTER trigger can perform different actions on
the same triggering statement.
Combinations
Using the options listed in the previous two sections, you can create four types of triggers:
• BEFORE statement trigger Before executing the triggering statement, the trigger action is
executed.
• BEFORE row trigger Before modifying each row affected by the triggering statement and
before checking appropriate integrity constraints, the trigger action is executed provided that
the trigger restriction was not violated.
• AFTER statement trigger After executing the triggering statement and applying any deferred
integrity constraints, the trigger action is executed.
• AFTER row trigger After modifying each row affected by the triggering statement and
possibly applying appropriate integrity constraints, the trigger action is executed for the
current row provided the trigger restriction was not violated. Unlike BEFORE row triggers,
AFTER row triggers lock rows.
You can have multiple triggers of the same type for the same statement for any given table. For
example you may have two BEFORE STATEMENT triggers for UPDATE statements on the EMP
table. Multiple triggers of the same type permit modular installation of applications that have
triggers on the same tables. Also, Oracle snapshot logs use AFTER ROW triggers, so you can design
your own AFTER ROW trigger in addition to the Oracle-defined AFTER ROWtrigger.
You DEPARTMENT
can create asOF
many
CSE
triggers of the preceding different types as you need for each type of DML
DBMS
statement (INSERT, UPDATE, or DELETE). For example, suppose you have a table, SAL, and you
want to know when the table is being accessed and the types of queries being issued. A global
session variable, STAT.ROWCNT, is initialized to zero by a BEFORE statement trigger, then it is
increased each time the row trigger is executed, and finally the statistical information is saved in the
table STAT_TAB by the AFTER statement trigger.

Example –ROW LEVEL AFTER UPDATE TRIGGER

CREATE OR REPLACE TRIGGER "TRIGGER_ROW_LVL_AFTER_UPDATE"
AFTER UPDATE OF BOOK_STATUS_ID ON LIBRARY
FOR EACH ROW
BEGIN
INSERT INTO LIBRARY_AUDIT_ROW_LVL(BOOK_ID, BOOK_STATUS_ID,
UPDATE_TS) VALUES (:old.BOOK_ID, :old.BOOK_STATUS_ID,
CURRENT_TIMESTAMP);
END;

Example –ROW LEVEL BEFORE UPDATE TRIGGER

CREATE OR REPLACE TRIGGER "TRIGGER_ROW_LVL_BEFORE_UPDATE"
BEFORE UPDATE OF BOOK_STATUS_ID ON LIBRARY
FOR EACH ROW
BEGIN
INSERT INTO LIBRARY_AUDIT_ROW_LVL(BOOK_ID, BOOK_STATUS_ID,
UPDATE_TS) VALUES (:old.BOOK_ID, :old.BOOK_STATUS_ID,
CURRENT_TIMESTAMP);
END;

Example – STATEMENT LEVEL AFTER DELETE TRIGGER

CREATE OR REPLACE TRIGGER TRIGGER_STMT_LVL_AFTER_DELETE
AFTER DELETE ON LIBRARY
BEGIN
INSERT INTO LIBRARY_AUDIT_STMT_LVL (STMT_TYPE, UPDATE_TS) VALUES
('AFTER DELETE', CURRENT_TIMESTAMP);
END;

Example – STATEMENT LEVEL BEFORE DELETE TRIGGER

CREATE OR REPLACE TRIGGER TRIGGER_STMT_LVL_BEFORE_DELETE
BEFORE DELETE ON LIBRARY
BEGIN
INSERT INTO LIBRARY_AUDIT_STMT_LVL (STMT_TYPE, UPDATE_TS) VALUES
('BEFORE DELETE', CURRENT_TIMESTAMP);
END;
Conclusion: Here we understood what are triggers and events, types of triggers how triggers are
used on database, and come to know that how use-full triggers are to manage the database.

DEPARTMENT OF CSE DBMS

 Group B Large Scale Databases
EXPERIMENT NO. B -1
Study of Open Sourse NOSQL Database
Title:- Study of Open Sourse NOSQL Database: MongoDB (Installation, Basic CRUD operations,
Execution)

Objective: Understand installation procedure of MongoDB and execution of basic CRUD operatios.
Prerequisites:
Students should have a basic understanding of database, text editor and execution of programs, etc. Because
we are going to develop high performance database, so it will be good if you have an understanding on the
basic concepts of Database (RDBMS).
Theory: MongoDB is an open-source document database and leading NoSQL database. MongoDB
is written in C++. This tutorial will give you great understanding on MongoDB concepts needed to
create and deploy a highly scalable and performance-oriented database.
MongoDB is a cross-platform, document oriented database that provides, high performance, high
availability, and easy scalability. MongoDB works on concept of collection and document.

Database

Database is a physical container for collections. Each database gets its own set of files on the file
system. A single MongoDB server typically has multiple databases.

Collection

Collection is a group of MongoDB documents. It is the equivalent of an RDBMS table. A collection

exists within a single database. Collections do not enforce a schema. Documents within a collection
can have different fields. Typically, all documents in a collection are of similar or related purpose.

Document

A document is a set of key-value pairs. Documents have dynamic schema. Dynamic schema means
that documents in the same collection do not need to have the same set of fields or structure, and
common fields in a collection's documents may hold different types of data.

DEPARTMENT OF CSE DBMS

The following table shows the relationship of RDBMS terminology with MongoDB.

RDBMS MongoDB

Database Database

Table Collection

Tuple/Row Document

column Field

Table Join Embedded Documents

Primary Key Primary Key (Default key _id provided by

mongodb itself)

Installation Procedure in FEDORA 19

Step #1: Add the MongoDB Repository

For a refresher on editing files with vim see: vim /etc/yum.repos.d/mongodb.repo
Option A: If you are running a 64-bit system, add the following information to the file you‟ve
created, using i to insert:
[mongodb]
name=MongoDBRepository
baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64/
gpgcheck=0
enabled=1
Then exit and save the file with the command :wq .
Option B: If you are running a 32-bit system, add the following information to the file you‟ve
created, using i to insert:
[mongodb]
name=MongoDBRepository
baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/i686/
gpgcheck=0
enabled=1
Then exit and save the file with the command :wq .
Step #2: Install MongoDB
As a matter of best practice we‟ll update our packages:
yum -y update
At this point, installing MongoDB is as simple as running just one command:
yum -y install mongodb-org mongodb-org-server
Step #3: Get MongoDB Running
Start-Up MongoDB
systemctl start mongod
Check MongoDB Service Status
systemctl status mongod
Start the MongoDB
DEPARTMENT OF CSE Service at Boot DBMS
systemctl enable mongod
 Summary List of Status Statistics (Continuous)
mongostat
Summary List of Status Statistics (5 Rows, Summarized Every 2 Seconds)
mongostat --rowcount 5 2
Enter the MongoDB Command Line
mongo

By default, running this command will look for a MongoDB server listening on port 27017 on the
localhost interface. If you‟d like to connect to a MongoDB server running on a different port, then
use the –port option. For example, if you wanted to connect to a local MongoDB server listening on
port 22222, then you‟d issue the following command:
mongo --port 22222
Shutdown MongoDB
systemctl stop mongod

Sample Document

Following example shows the document structure of a blog site, which is simply a comma separated
key value pair.
{
_id: ObjectId(7df78ad8902c)
title: 'MongoDB Overview',
description: 'MongoDB is no sql database',
by: 'tutorials point',
url: 'http://www.tutorialspoint.com',
tags: ['mongodb', 'database', 'NoSQL'],
likes: 100,
comments: [
{
user:'user1',
message: 'My first comment',
dateCreated: new Date(2011,1,20,2,15),
like: 0
},
{
user:'user2',
message: 'My second comments',
dateCreated: new Date(2011,1,25,7,45),
like: 5
}
]
}
_id is a 12 bytes hexadecimal number which assures the uniqueness of every document. You can
provide _id while inserting the document. If you don‟t provide then MongoDB provides a unique id
for every document. These 12 bytes first 4 bytes for the current timestamp, next 3 bytes for machine
id, next 2 bytes for process id of MongoDB server and remaining 3 bytes are simple incremental
VALUE.
DEPARTMENT OF CSE DBMS
Conclusion: The installation procedure of MongoDB is successfully understood.
 EXPERIMENT NO. B -2
CRUD Operations on MONGODB
Title: - Design and Develop MongoDB Queries using CRUD operations. (Use CRUD operations,
SAVE method, logical operators)

Objective: Understand CRUD operations in MongoDB and execution of basic CRUD operatios.
Prerequisites:
Students should have a basic understanding of database, text editor and execution of programs, etc. Because
we are going to develop high performance database, so it will be good if you have an understanding on the
basic concepts of Database (RDBMS) and Basics of mongodb.
Theory: CRUD operations create, read, update, and delete documents.
I. Create Operations
Create or insert operations add new documents to a collection. If the collection does not currently
exist, insert operations will create the collection.
MongoDB provides the following methods to insert documents into a collection:
 db.collection.insertOne() New in version 3.2
 db.collection.insertMany() New in version 3.2
In MongoDB, insert operations target a single collection. All write operations in MongoDB
are atomic on the level of a single document.
For examples, see Insert Documents.

II. Read Operations

Read operations retrieve documents from a collection; i.e. queries a collection for documents.
MongoDB provides the following methods to read documents from a collection:
 db.collection.find()

DEPARTMENT OF CSE DBMS

 You can specify query filters or criteria that identify the documents to return.

III. Update Operations

Update operations modify existing documents in a collection.
For examples, see Update Documents.
>db.TE.update({Roll:2},{$set:{Name:"Wagholi"}});
Where „TE‟ is Collection
MongoDB provides the following methods to update documents of a collection:
 db.collection.updateOne() New in version 3.2
 db.collection.updateMany() New in version 3.2
 db.collection.replaceOne() New in version 3.2
In MongoDB, update operations target a single collection. All write operations in MongoDB
are atomic on the level of a single document.
You can specify criteria, or filters, that identify the documents to update. These filters use the same
syntax as read operations.

IV. Delete Operations

Delete operations remove documents from a collection.
For examples:
> db.TE.remove({Roll:3});

MongoDB provides the following methods to delete documents of a collection:

 db.collection.deleteOne() New in version 3.2
 db.collection.deleteMany() New in version 3.2 In MongoDB, delete operations target a
single collection.
All write operations in MongoDB are atomic on the level of a single document.
You can specify criteria, or filters, that identify the documents to remove. These filters use the
same syntax as read operations.
SAVE Method:
db.collection.save();
Updates an existing document or inserts a new document, depending on its document parameter.
The save() method has the following form:
The save() method uses either the insert or the update command, which use the default write
concern. To specify a different write concern, include the write concern in the options parameter.
Insert:
If the document does not contain an _id field, then the save() method calls the insert() method.
During the operation, the mongo shell will create an ObjectId and assign it to the _id field.
Update:
If the document contains an _id field, then the save() method is equivalent to an update with
DEPARTMENT OF CSE DBMS
the upsert option set to true and the query predicate on the _id field.
Examples

In the following example, save() method performs an insert since the document passed to the method
does not contain the _id field:
> db.products.save( { item: "book", qty: 40 } );
In the following example, save() performs an update with upsert:true since the document contains
an _id field:
> db.products.save( { _id: 100, item: "water", qty: 30 } );

Logical Query Operators

Name Description
$and Joins query clauses with a logical AND returns all documents that match the conditions of both clauses.
$not Inverts the effect of a query expression and returns documents that do not match the query expression
$nor Joins query clauses with a logical NOR returns all documents that fail to match both clauses.
$or Joins query clauses with a logical OR returns all documents that match the conditions of either clause.

Example:
[student@localhost ~]$ su
Password:
[root@localhost student]# systemctl start mongod.service
[root@localhost student]# mongo
MongoDB shell version: 2.2.4
connecting to: test
> show dbs;
Bank 0.203125GB
local (empty)
> use Bank;
switched to db Bank
> show tables;
Employee
system.indexes
> db.Employee.find();
{ "_id" : ObjectId("596888d2a015fef80611e763"), "R_no" : 1, "Name" : "ABC" }
{ "_id" : ObjectId("59688a32a015fef80611e764"), "R_no" : 2, "Name" : "pqr" }
> db.Employee.find({},{_id:0});
{ "R_no" : 1, "Name" : "ABC" }
DEPARTMENT OF CSE DBMS
 { "R_no" : 2, "Name" : "pqr" }
> db.Employee.find({R_no:1},{_id:0});
{ "R_no" : 1, "Name" : "ABC" }
> db.Employee.insert({R_no:3,Name:'XYZ'});
> db.Employee.insert({R_no:4,Name:'lmn'});
> db.Employee.find({},{_id:0});
{ "R_no" : 1, "Name" : "ABC" }
{ "R_no" : 2, "Name" : "pqr" }
{ "R_no" : 3, "Name" : "XYZ" }
{ "R_no" : 4, "Name" : "lmn" }

> db.Employee.find({R_no:1});
{ "_id" : ObjectId("596888d2a015fef80611e763"), "R_no" : 1, "Name" : "ABC" }

> db.Employee.find({R_no:{$in:[1,2]}});
{ "_id" : ObjectId("596888d2a015fef80611e763"), "R_no" : 1, "Name" : "ABC" }
{ "_id" : ObjectId("59688a32a015fef80611e764"), "R_no" : 2, "Name" : "pqr" }
> db.Employee.find({R_no:{$not: {$in:[1,2]}} });
{ "_id" : ObjectId("59780a076510413ed9577b03"), "R_no" : 3, "Name" : "XYZ" }
{ "_id" : ObjectId("59780a106510413ed9577b04"), "R_no" : 4, "Name" : "lmn" }
> db.Employee.find( {$or :[{R_no:{$gte:3}}, {Name:'lmn'}] });
{ "_id" : ObjectId("59780a076510413ed9577b03"), "R_no" : 3, "Name" : "XYZ" }
{ "_id" : ObjectId("59780a106510413ed9577b04"), "R_no" : 4, "Name" : "lmn" }
> db.Employee.find( {$or :[{R_no:{$lte:3}}, {Name:'lmn'}] });
{ "_id" : ObjectId("596888d2a015fef80611e763"), "R_no" : 1, "Name" : "ABC" }
{ "_id" : ObjectId("59688a32a015fef80611e764"), "R_no" : 2, "Name" : "pqr" }
{ "_id" : ObjectId("59780a076510413ed9577b03"), "R_no" : 3, "Name" : "XYZ" }
{ "_id" : ObjectId("59780a106510413ed9577b04"), "R_no" : 4, "Name" : "lmn" }
> db.Employee.find( {$and :[{R_no:4}, {Name:'lmn'}] });
{ "_id" : ObjectId("59780a106510413ed9577b04"), "R_no" : 4, "Name" : "lmn" }

Conclusion: The CRUD Operations SAVE Method and Logical operators in MongoDB is
successfully understood.

DEPARTMENT OF CSE DBMS

Sample Command for Basic Operations:
[Vilas@localhost bin]$ ./mongo
MongoDB shell version: 2.6.1
connecting to: test

> show databases;

VBK 0.078GB
admin (empty)
local 0.078GB
newsletter 0.078GB

> use COEM // Created new Database named as "COEM"

switched to db COEM

> db.createCollection("TE") // Created new Collection named as "TE"

{ "ok" : 1 }

> db.TE.insert({Roll:1,Name:"ABC",Address:"Pune",Per:76})
WriteResult({ "nInserted" : 1 })

> db.TE.insert({Roll:2,Name:"PQR",Address:"Pune",Per:75})
WriteResult({ "nInserted" : 1 })

> db.TE.insert({Roll:3,Name:"LMN",Address:"Hadapsar",Per:70})
WriteResult({ "nInserted" : 1 })

> db.TE.find({})
{ "_id" : ObjectId("541963be2741c7552caef0a9"), "Roll" : 1, "Name" : "ABC", "Address" : "Pune",
"Per" : 76 }
{ "_id" : ObjectId("541963cb2741c7552caef0aa"), "Roll" : 2, "Name" : "PQR", "Address" : "Pune",
"Per" : 75 }
{ "_id" : ObjectId("541963dc2741c7552caef0ab"), "Roll" : 3, "Name" : "LMN", "Address" :
"Hadapsar", "Per" : 70 }

> db.TE.find({})
{ "_id" : ObjectId("541963be2741c7552caef0a9"), "Roll" : 1, "Name" : "ABC", "Address" : "Pune",
"Per" : 76 }
{ "_id" : ObjectId("541963cb2741c7552caef0aa"), "Roll" : 2, "Name" : "PQR", "Address" : "Pune",
"Per" : 75 }
{ "_id" : ObjectId("541963dc2741c7552caef0ab"), "Roll" : 3, "Name" : "LMN", "Address" :
"Hadapsar", "Per" : 70 }

> db.TE.update({Roll:2},{$set:{Name:"Wagholi"}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

DEPARTMENT OF CSE
> db.TE.find({}) DBMS
{ "_id" : ObjectId("541963be2741c7552caef0a9"), "Roll" : 1, "Name" : "ABC", "Address" : "Pune",
"Per" : 76 }
{ "_id" : ObjectId("541963cb2741c7552caef0aa"), "Roll" : 2, "Name" : "Wagholi", "Address" :
"Pune", "Per" : 75 }
{ "_id" : ObjectId("541963dc2741c7552caef0ab"), "Roll" : 3, "Name" : "LMN", "Address" :
"Hadapsar", "Per" : 70 }

> db.TE.remove({Roll:3})
WriteResult({ "nRemoved" : 1 })

> db.TE.find({})
{ "_id" : ObjectId("541963be2741c7552caef0a9"), "Roll" : 1, "Name" : "ABC", "Address" : "Pune",
"Per" : 76 }
{ "_id" : ObjectId("541963cb2741c7552caef0aa"), "Roll" : 2, "Name" : "Wagholi", "Address" :
"Pune", "Per" : 75 }

> db.TE.drop()
true

> db.TE.find({})

> show collections

system.indexes

> show databases;

COEM 0.078GB
VBK 0.078GB
admin (empty)
local 0.078GB
newsletter 0.078GB

> db.dropDatabase("COEM")
2014-09-17T16:17:18.278+0530 dropDatabase doesn't take arguments at src/mongo/shell/db.js:141

> db.dropDatabase()
{ "dropped" : "COEM", "ok" : 1 }

> show databases;

VBK 0.078GB
admin (empty)
local 0.078GB
newsletter 0.078GB

DEPARTMENT OF CSE DBMS

 EXPERIMENT NO. B -3
Aggregation and indexing in MONGODB
Title: - Implement aggregation and indexing with suitable example using MongoDB.

Objective: Understand aggregation and indexing operations in MongoDB

Prerequisites:
Basics of mongodb.

Theory: Aggregation operations process data records and return computed results. Aggregation
operations group values from multiple documents together, and can perform a variety of operations
on the grouped data to return a single result. MongoDB provides three ways to perform aggregation:
the aggregation pipeline, the map-reduce function, and single purpose aggregation methods.

MongoDB‟s aggregation framework is modeled on the concept of data processing pipelines.

Documents enter a multi-stage pipeline that transforms the documents into an aggregated result.
The most basic pipeline stages provide filters that operate like queries and document
transformations that modify the form of the output document.

Other pipeline operations provide tools for grouping and sorting documents by specific field or fields
as well as tools for aggregating the contents of arrays, including arrays of documents. In addition,
pipeline stages can use operators for tasks such as calculating the average or concatenating a string.
The pipeline provides efficient data aggregation using native operations within MongoDB, and is the
preferred method for data aggregation in MongoDB.

The aggregation pipeline can operate on a sharded collection. The aggregation pipeline can use
indexes to improve its performance during some of its stages. In addition, the aggregation pipeline
has an internal optimization phase. See Pipeline Operators and Indexes andAggregation Pipeline
Optimization for details.

Examples:

> db.student.find({})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
DEPARTMENT OF CSE DBMS
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65 }
> db.student.find({})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56}
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" : 60}
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52}
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65 }
> db.student.aggregate({$project:{Rno:1}})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8 }
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10 }
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11 }
> db.student.aggregate({$project:{Name:1}})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Name" : "A" }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Name" : "B" }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Name" : "C" }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Name" : "P" }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Name" : "Q" }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Name" : "R" }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Name" : "S" }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Name" : "L" }
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Name" : "M" }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Name" : "T" }
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Name" : "D" }
> db.student.aggregate({$sort:{Name:1}})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"),
DEPARTMENT OF CSE "Rno" : 10, "Name" : "T", "Dept" : "Comp",DBMS
"Marks" : 53
}
> db.student.aggregate({$sort:{Name:-1}})
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56 }
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
> db.student.aggregate({$sort:{Rno:-1}})
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65}
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52}
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56}
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" : 55}
> db.student.aggregate({$sort:{Rno:1}})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52}
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65 }
> db.student.aggregate({$limit:4},{$sort:{Rno:1}})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56}
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
> db.student.aggregate({$limit:4},{$sort:{Rno:-1}})
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56 }
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
> db.student.aggregate({$sort:{Rno:-1}},{$limit:4})
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65}
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac14c3343ed16abd76171"),
DEPARTMENT OF CSE "Rno" : 9, "Name" : "M", "Dept" : "Mech", DBMS
"Marks" : 52 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52 }
> db.student.aggregate({$sort:{Marks:-1}},{$limit:4})
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }

> db.student.aggregate({$group:{"_id":"$Dept","Count":{$sum:1}}})
{ "_id" : "Mech", "Count" : 2 }
{ "_id" : "Comp", "Count" : 5 }
{ "_id" : "Civil", "Count" : 4 }
> db.student.aggregate({$group:{"_id":"$Marks","Count":{$sum:1}}})
{ "_id" : 65, "Count" : 1 }
{ "_id" : 52, "Count" : 2 }
{ "_id" : 61, "Count" : 2 }
{ "_id" : 53, "Count" : 1 }
{ "_id" : 60, "Count" : 2 }
{ "_id" : 56, "Count" : 1 }
{ "_id" : 62, "Count" : 1 }
{ "_id" : 55, "Count" : 1 }
> db.student.aggregate({$group:{"_id":"$Marks","Count":{$avg:1}}})
{ "_id" : 65, "Count" : 1 }
{ "_id" : 52, "Count" : 1 }
{ "_id" : 61, "Count" : 1 }
{ "_id" : 53, "Count" : 1 }
{ "_id" : 60, "Count" : 1 }
{ "_id" : 56, "Count" : 1 }
{ "_id" : 62, "Count" : 1 }
{ "_id" : 55, "Count" : 1 }
> db.student.find({Dept:"Civil"})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65 }
> db.student.find({Dept:"Comp"})
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}

> db.student.find({$or:[{Dept:"Comp"},{Dept:"Civil"}]})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" :55 }
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56}
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65}

> db.runCommand({aggregate:"student",pipeline:[{$project:{Rno:1}}]})
DEPARTMENT OF CSE DBMS
{
 "result" : [
{
"_id" : ObjectId("53fac0cd3343ed16abd76169"),
"Rno" : 1
},
{
"_id" : ObjectId("53fac0da3343ed16abd7616a"),
"Rno" : 2
},
{
"_id" : ObjectId("53fac0ed3343ed16abd7616b"),
"Rno" : 3
},
{
"_id" : ObjectId("53fac1013343ed16abd7616c"),
"Rno" : 4
},
{
"_id" : ObjectId("53fac10e3343ed16abd7616d"),
"Rno" : 5
},
{
"_id" : ObjectId("53fac11a3343ed16abd7616e"),
"Rno" : 6
},
{
"_id" : ObjectId("53fac12e3343ed16abd7616f"),
"Rno" : 7
},
{
"_id" : ObjectId("53fac1433343ed16abd76170"),
"Rno" : 8
},
{
"_id" : ObjectId("53fac14c3343ed16abd76171"),
"Rno" : 9
},
{
"_id" : ObjectId("53fac18f3343ed16abd76172"),
"Rno" : 10
},
{
"_id" : ObjectId("53fac1a33343ed16abd76173"),
"Rno" : 11
}
],
"ok" : 1
}
> db.runCommand({aggregate:"student",pipeline:[{$project:{Rno:1}},{$limit:3}]})
{
"result" : [
{
"_id" : ObjectId("53fac0cd3343ed16abd76169"),
DEPARTMENT OF CSE"Rno" : 1 DBMS
},
 {
"_id" : ObjectId("53fac0da3343ed16abd7616a"),
"Rno" : 2
},
{
"_id" : ObjectId("53fac0ed3343ed16abd7616b"),
"Rno" : 3
}
],
"ok" : 1
}
> db.student.aggregate({$group:{"_id":"$Dept","AVERAGE":{$avg:"$Marks"}}})
{ "_id" : "Mech", "AVERAGE" : 52 }
{ "_id" : "Comp", "AVERAGE" : 59.4 }
{ "_id" : "Civil", "AVERAGE" : 59 }

> db.student.aggregate({$group:{"_id":"$Dept","AVERAGE":{$avg:"$Marks"}}})
{ "_id" : "Mech", "AVERAGE" : 52 }
{ "_id" : "Comp", "AVERAGE" : 59.4 }
{ "_id" : "Civil", "AVERAGE" : 59 }

> db.student.aggregate({$group:{"_id":"$Dept","MINIMUM":{$min:"$Marks"}}})
{ "_id" : "Mech", "MINIMUM" : 52 }
{ "_id" : "Comp", "MINIMUM" : 53 }
{ "_id" : "Civil", "MINIMUM" : 55 }
> db.student.aggregate({$group:{"_id":"$Dept","MAXIMUM":{$max:"$Marks"}}})
{ "_id" : "Mech", "MAXIMUM" : 52 }
{ "_id" : "Comp", "MAXIMUM" : 62 }
{ "_id" : "Civil", "MAXIMUM" : 65 }
> db.student.aggregate({$group:{"_id":"$Dept","TOTAL":{$sum:"$Marks"}}})
{ "_id" : "Mech", "TOTAL" : 104 }
{ "_id" : "Comp", "TOTAL" : 297 }
{ "_id" : "Civil", "TOTAL" : 236 }
> db.student.aggregate({$group:{"_id":"$Dept","COUNT":{$sum:1}}})
{ "_id" : "Mech", "COUNT" : 2 }
{ "_id" : "Comp", "COUNT" : 5 }
{ "_id" : "Civil", "COUNT" : 4 }
> db.student.aggregate({$group:{"_id":"$Dept","First":{$first:"$Marks"}}})
{ "_id" : "Mech", "First" : 52 }
{ "_id" : "Comp", "First" : 60 }
{ "_id" : "Civil", "First" : 55 }
> db.student.aggregate({$group:{"_id":"$Dept","Last":{$last:"$Marks"}}})
{ "_id" : "Mech", "Last" : 52 }
{ "_id" : "Comp", "Last" : 53 }
{ "_id" : "Civil", "Last" : 65 }
> db.student.aggregate({$limit:4},{$sort:{Rno:1}})
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" : 55}
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56}
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" :60 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
> db.student.aggregate({$skip:2},{$sort:{Rno:1}})
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" : 60 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"),
DEPARTMENT OF CSE "Rno" : 5, "Name" : "Q", "Dept" : "Comp", DBMS
"Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65}
> db.student.aggregate({$skip:2},{$sort:{Rno:1}})
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" : 60 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65}
> db.student.aggregate({$sort:{Rno:1}},{$skip:2})
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" : 60 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52}
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac18f3343ed16abd76172"), "Rno" : 10, "Name" : "T", "Dept" : "Comp", "Marks" : 53
}
{ "_id" : ObjectId("53fac1a33343ed16abd76173"), "Rno" : 11, "Name" : "D", "Dept" : "Civil", "Marks" : 65}
> db.student.aggregate({$sort:{Rno:-1}},{$skip:2})
{ "_id" : ObjectId("53fac14c3343ed16abd76171"), "Rno" : 9, "Name" : "M", "Dept" : "Mech", "Marks" : 52 }
{ "_id" : ObjectId("53fac1433343ed16abd76170"), "Rno" : 8, "Name" : "L", "Dept" : "Mech", "Marks" : 52}
{ "_id" : ObjectId("53fac12e3343ed16abd7616f"), "Rno" : 7, "Name" : "S", "Dept" : "Comp", "Marks" : 62 }
{ "_id" : ObjectId("53fac11a3343ed16abd7616e"), "Rno" : 6, "Name" : "R", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac10e3343ed16abd7616d"), "Rno" : 5, "Name" : "Q", "Dept" : "Comp", "Marks" : 61 }
{ "_id" : ObjectId("53fac1013343ed16abd7616c"), "Rno" : 4, "Name" : "P", "Dept" : "Comp", "Marks" : 60 }
{ "_id" : ObjectId("53fac0ed3343ed16abd7616b"), "Rno" : 3, "Name" : "C", "Dept" : "Civil", "Marks" : 60}
{ "_id" : ObjectId("53fac0da3343ed16abd7616a"), "Rno" : 2, "Name" : "B", "Dept" : "Civil", "Marks" : 56}
{ "_id" : ObjectId("53fac0cd3343ed16abd76169"), "Rno" : 1, "Name" : "A", "Dept" : "Civil", "Marks" : 55}

> db.tea.find({})
{ "_id" : ObjectId("53fabda13343ed16abd76165"), "rno" : 1, "name" : "vilas" }
{ "_id" : ObjectId("53fabded3343ed16abd76166"), "rno" : 2, "name" : "vilas", "dept" : "Comp" }
{ "_id" : ObjectId("53fabdfa3343ed16abd76167"), "rno" : 3, "name" : "PQR", "dept" : "Comp" }
{ "_id" : ObjectId("53fabe073343ed16abd76168"), "rno" : 4, "name" : "ABC", "dept" : "Civil" }

> db.Ass15.insert({Rno:1,Name:"ABC",Mobile:[99999,8888]})
WriteResult({ "nInserted" : 1 })
> db.Ass15.find({})
{ "_id" : ObjectId("53feb6b04ff96d10965820c2"), "Rno" : 1, "Name" : "ABC", "Mobile" : [ 99999, 8888 ] }
> db.Ass15.insert({Rno:2,Name:"PQR",Mobile:[222222,555555]})
WriteResult({ "nInserted" : 1 })
> db.Ass15.find({})
{ "_id" : ObjectId("53feb6b04ff96d10965820c2"), "Rno" : 1, "Name" : "ABC", "Mobile" : [ 99999, 8888 ] }
{ "_id" : ObjectId("53feb6da4ff96d10965820c3"),
DEPARTMENT OF CSE "Rno" : 2, "Name" : "PQR", "Mobile" : [ 222222,
DBMS555555 ]
}
> db.Ass15.aggregate({$unwind:"$Mobile"})
{ "_id" : ObjectId("53feb6b04ff96d10965820c2"), "Rno" : 1, "Name" : "ABC", "Mobile" : 99999 }
{ "_id" : ObjectId("53feb6b04ff96d10965820c2"), "Rno" : 1, "Name" : "ABC", "Mobile" : 8888 }
{ "_id" : ObjectId("53feb6da4ff96d10965820c3"), "Rno" : 2, "Name" : "PQR", "Mobile" : 222222 }
{ "_id" : ObjectId("53feb6da4ff96d10965820c3"), "Rno" : 2, "Name" : "PQR", "Mobile" : 555555 }

INDEXES:

Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must
perform a collection scan, i.e. scan every document in a collection, to select those documents that
match the query statement. If an appropriate index exists for a query, MongoDB can use the index to
limit the number of documents it must inspect.

Indexes are special data structures [1] that store a small portion of the collection‟s data set in an easy
to traverse form. The index stores the value of a specific field or set of fields, ordered by the value of
the field. The ordering of the index entries supports efficient equality matches and range-based query
operations. In addition, MongoDB can return sorted results by using the ordering in the index. The
following diagram illustrates a query that selects and orders the matching documents using an index:

Fundamentally, indexes in MongoDB are similar to indexes in other database systems. MongoDB
defines indexes at the collection level and supports indexes on any field or sub-field of the
documents in a MongoDB collection.

Default _id Index

MongoDB creates a unique index on the _id field during the creation of a collection. The _id index
prevents clients from inserting two documents with the same value for the _id field. You cannot drop
this index on the _id field.

Create an Index

db.collection.createIndex( <key and index type specification>, <options> )

DEPARTMENT OF CSE DBMS

Index Types
MongoDB provides a number of different index types to support specific types of data and queries.

Single Field

In addition to the MongoDB-defined _id index, MongoDB supports the creation of user-defined
ascending/descending indexes on a single field of a document.

Compound Index

MongoDB also supports user-defined indexes on multiple fields, i.e. compound indexes.

The order of fields listed in a compound index has significance. For instance, if a compound index
consists of { userid: 1, score: -1 }, the index sorts first by userid and then, within each userid value,
sorts by score.

DEPARTMENT
Examples:OF CSE DBMS
root@Afroz:~#service mongod start
root@Afroz:~# mongo
MongoDB shell version: 3.2.11
connecting to: test
> show dbs
afroz 0.000GB
employee 0.000GB
local 0.000GB
> use emplloyee
switched to db emplloyee
> use employee
switched to db employee
> show tables;
doc
emp
> db.emp.find({},{_id:0})
{ "empID" : 111, "name" : "pratik", "design" : "ceo", "salary" : 3000 }
{ "empID" : 112, "name" : "jatin", "design" : "manager", "salary" : 5000 }
{ "empID" : 113, "name" : "afroz", "design" : "developer", "salary" : 1500 }
{ "empID" : 114, "name" : "manish", "design" : "developer", "salary" : 10000 }
{ "empID" : 115, "name" : "sanket", "design" : "developer", "salary" : 15000 }
{ "empID" : 116, "name" : "abhijeet", "design" : "developer", "salary" : 25000 }
> db.emp.ensureIndex({empID:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.emp.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "employee.emp"
},
{
"v" : 1,
"key" : {
"empID" : 1
},
"name" : "empID_1",
"ns" : "employee.emp"
}
]
> db.emp.find()
{ "_id" : ObjectId("59a6427fd8da53a4381c8ea6"), "empID" : 111, "name" : "pratik", "design" : "ceo",
"salary" : 3000 }
{ "_id" : ObjectId("59a64299d8da53a4381c8ea7"), "empID" : 112, "name" : "jatin", "design" : "manager",
"salary" : 5000 }
{ "_id" : ObjectId("59a642a8d8da53a4381c8ea8"),
DEPARTMENT OF CSE "empID" : 113, "name" : "afroz", "design" : DBMS
"developer",
"salary" : 1500 }
{ "_id" : ObjectId("59a642d1d8da53a4381c8ea9"), "empID" : 114, "name" : "manish", "design" :
"developer", "salary" : 10000 }
{ "_id" : ObjectId("59a642ead8da53a4381c8eaa"), "empID" : 115, "name" : "sanket", "design" : "developer",
"salary" : 15000 }
{ "_id" : ObjectId("59a642ffd8da53a4381c8eab"), "empID" : 116, "name" : "abhijeet", "design" :
"developer", "salary" : 25000 }
> db.emp.ensureIndex({name:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.emp.getIndexes()
[ {
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "employee.emp"
},
{
"v" : 1,
"key" : {
"empID" : 1
},
"name" : "empID_1",
"ns" : "employee.emp"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"name" : "name_1",
"ns" : "employee.emp"
}]
> db.emp.dropIndex({name:1})
{ "nIndexesWas" : 3, "ok" : 1 }
> db.emp.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "employee.emp"
},
{
"v" : 1,
"key" : {
DEPARTMENT OF CSE"empID" : 1 DBMS
},
 "name" : "empID_1",
"ns" : "employee.emp"
}
]>
Conclusion: The Aggregation and Indexing in MongoDB is successfully understood.

DEPARTMENT OF CSE DBMS

 EXPERIMENT NO. B -4
Map Reduce in MONGODB
Title: - Implement Map reduces operation with suitable example using MongoDB.

Objective: Understand map reduce operations in MongoDB

Prerequisites:
Basics of mongodb.

Theory: Map-reduce is a data processing paradigm for condensing large volumes of data into useful
aggregated results. For map-reduce operations, MongoDB provides the mapReduce database command.

Map-reduce is a data processing paradigm for condensing large volumes of data into useful aggregatedresults.
For map-reduce operations, MongoDB provides the mapReduce database command.
Consider the following map-reduce operation:

Syntax:
************* MAP REDUCE ***************************

function mapf() {
// 'this' holds current document to inspect
emit(key, value);
}

function reducef(key,value_array)
{ return reduced_value;
}

db.mycollection.mapReduce(mapf, reducef[, options])

options
{[query : <query filter object>]
[, sort : <sort the query. useful for optimization>]
[, limit : <number of objects to return from collection>]
[, out : <output-collection name>]
[, keeptemp: <true|false>]
[, finalize : <finalizefunction>]
[, scope : <object where fields go into javascript global scope >]
[, verbose : true]} OF CSE
DEPARTMENT DBMS
Exmple:

> use VBK

switched to db VBK
> show tables;
Ass15
MRExample
bank8
bankMRE
bankMRE1
student
system.indexes
tea
users
> db.createCollection("TE_Marks")
{ "ok" : 1 }
> db.TE_Marks.insert({RollNo:1,Name:"ABC",Subject:"DMSA",Mark:12})
WriteResult({ "nInserted" : 1 })
> db.TE_Marks.insert({RollNo:1,Name:"ABC",Subject:"OSD",Mark:13})
WriteResult({ "nInserted" : 1 })
> db.TE_Marks.insert({RollNo:1,Name:"ABC",Subject:"DCWSN",Mark:15})
WriteResult({ "nInserted" : 1 })
> db.TE_Marks.insert({RollNo:1,Name:"ABC",Subject:"TOC",Mark:10})
WriteResult({ "nInserted" : 1 })
> db.TE_Marks.insert({RollNo:2,Name:"PQR",Subject:"DMSA",Mark:18})
WriteResult({ "nInserted" : 1 })
> db.TE_Marks.insert({RollNo:2,Name:"PQR",Subject:"OSA",Mark:13})
WriteResult({ "nInserted" : 1 })
> db.TE_Marks.insert({RollNo:2,Name:"PQR",Subject:"DCWSN",Mark:12})
WriteResult({ "nInserted" : 1 })
> db.TE_Marks.insert({RollNo:2,Name:"PQR",Subject:"TOC",Mark:13})
WriteResult({ "nInserted" : 1 })
> db.TE_Marks.find({})
{ "_id" : ObjectId("540ed8558ce9c9c60ab58273"), "RollNo" : 1, "Name" : "ABC", "Subject" : "DMSA", "Mark" : 12 }
{ "_id" : ObjectId("540ed8658ce9c9c60ab58274"), "RollNo" : 1, "Name" : "ABC", "Subject" : "OSD", "Mark" : 13 }
{ "_id" : ObjectId("540ed8718ce9c9c60ab58275"), "RollNo" : 1, "Name" : "ABC", "Subject" : "DCWSN", "Mark" : 15
}
{ "_id" : ObjectId("540ed87e8ce9c9c60ab58276"), "RollNo" : 1, "Name" : "ABC", "Subject" : "TOC", "Mark" : 10 }
{ "_id" : ObjectId("540ed8988ce9c9c60ab58277"), "RollNo" : 2, "Name" : "PQR", "Subject" : "DMSA", "Mark" : 18 }
{ "_id" : ObjectId("540ed8a18ce9c9c60ab58278"), "RollNo" : 2, "Name" : "PQR", "Subject" : "OSA", "Mark" : 13 }
{ "_id" : ObjectId("540ed8ac8ce9c9c60ab58279"), "RollNo" : 2, "Name" : "PQR", "Subject" : "DCWSN", "Mark" : 12 }
{ "_id" : ObjectId("540ed8bd8ce9c9c60ab5827a"), "RollNo" : 2, "Name" : "PQR", "Subject" : "TOC", "Mark" : 13 }
> var Mapfunction = function(){emit(this.RollNo,this.Mark)}
> var Reducefunction = function(key,values){return Array.sum(values)}
> db.TE_Marks.mapReduce(Mapfunction,Reducefunction,{'out':'MR_Result'})
{
"result" : "MR_Result",
"timeMillis" : 311,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 2,
"output" : 2
},
"ok" : 1,
}
> showDEPARTMENT
tables; OF CSE DBMS
Ass15
MRExample
MR_Result
TE_Marks
bank8
bankMRE
bankMRE1
student
system.indexes
tea
users
> db.MR_Result.find({})
{ "_id" : 1, "value" : 50 }
{ "_id" : 2, "value" : 56 }

// Average can be calculated by using...

> var Reducefunction = function(key,values){return Array.avg(values)}

Conclusion: We understood the concept of Map Reduce in MongoDB.

DEPARTMENT OF CSE DBMS

 EXPERIMENT NO. B -5
Basic 5 Operations in MONGODB
Title: - Implement Map reduces operation with suitable example using MongoDB.

Objective: Understand basic oprerations in MongoDB

Prerequisites:
Basics of mongodb.

Theory: MongoDB is a cross-platform, document oriented database that provides, high performance, high
availability, and easy scalability. MongoDB works on concept of collection and document.

Example:
> show databases;
VBK 0.078GB
admin (empty)
local 0.078GB
newsletter 0.078GB

> use COEM // Created new Database named as "COEM"

switched to db COEM

> db.createCollection("TE") // Created new Collection named as "TE"

{ "ok" : 1 }

> db.TE.insert({Roll:1,Name:"ABC",Address:"Pune",Per:76})
WriteResult({ "nInserted" : 1 })

> db.TE.insert({Roll:2,Name:"PQR",Address:"Pune",Per:75})
WriteResult({ "nInserted" : 1 })

> db.TE.insert({Roll:3,Name:"LMN",Address:"Hadapsar",Per:70})
WriteResult({ "nInserted" : 1 })

> db.TE.find({})
{ "_id" : ObjectId("541963be2741c7552caef0a9"), "Roll" : 1, "Name" : "ABC", "Address" : "Pune", "Per" : 76 }
{ "_id" : ObjectId("541963cb2741c7552caef0aa"), "Roll" : 2, "Name" : "PQR", "Address" : "Pune", "Per" : 75 }
{ "_id" : ObjectId("541963dc2741c7552caef0ab"), "Roll" : 3, "Name" : "LMN", "Address" : "Hadapsar", "Per" :
70 }

> db.TE.find({})
{ "_id" : ObjectId("541963be2741c7552caef0a9"), "Roll" : 1, "Name" : "ABC", "Address" : "Pune", "Per" : 76 }
{ "_id" : ObjectId("541963cb2741c7552caef0aa"), "Roll" : 2, "Name" : "PQR", "Address" : "Pune", "Per" : 75 }
{ "_id" : ObjectId("541963dc2741c7552caef0ab"), "Roll" : 3, "Name" : "LMN", "Address" : "Hadapsar", "Per" :
70 }

> db.TE.update({Roll:2},{$set:{Name:"Wagholi"}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

> db.TE.find({})
{ "_id" : ObjectId("541963be2741c7552caef0a9"), "Roll" : 1, "Name" : "ABC", "Address" : "Pune", "Per" : 76 }
DEPARTMENT OF CSE DBMS
{ "_id" : ObjectId("541963cb2741c7552caef0aa"), "Roll" : 2, "Name" : "Wagholi", "Address" : "Pune", "Per" : 75
}
{ "_id" : ObjectId("541963dc2741c7552caef0ab"), "Roll" : 3, "Name" : "LMN", "Address" : "Hadapsar", "Per" :
70 }

> db.TE.remove({Roll:3})
WriteResult({ "nRemoved" : 1 })

> db.TE.find({})
{ "_id" : ObjectId("541963be2741c7552caef0a9"), "Roll" : 1, "Name" : "ABC", "Address" : "Pune", "Per" : 76 }
{ "_id" : ObjectId("541963cb2741c7552caef0aa"), "Roll" : 2, "Name" : "Wagholi", "Address" : "Pune", "Per" : 75
}

> db.TE.drop()
true

> db.TE.find({})

> show collections

system.indexes

> show databases;

COEM 0.078GB
VBK 0.078GB
admin (empty)
local 0.078GB
newsletter 0.078GB

> db.dropDatabase("COEM")
2014-09-17T16:17:18.278+0530 dropDatabase doesn't take arguments at src/mongo/shell/db.js:141

> db.dropDatabase()
{ "dropped" : "COEM", "ok" : 1 }

> show databases;

VBK 0.078GB
admin (empty)
local 0.078GB
newsletter 0.078GB

Conclusion: We understood the basic operations in MongoDB.

DEPARTMENT OF CSE DBMS