PRACTICAL NO.

: 1
Title: Study of Open-Source Relational Databases: MySQL/Oracle and Design and Develop SQL DDL
statements which demonstrate the use of SQL objects such as Table, View,Index, Sequence.
Name: Abhishek Pawar
Roll.No : 40
Batch : T2

mysql> create database hospital;

Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| aditya_25 |
| aniket_2003 |
| cust_tri |
| hospital |
| information_schema |
| mysql |
| performance_schema |
| practical4 |
| sakila |
| sys |
| world |
+--------------------+
11 rows in set (0.00 sec)
mysql> use hospital;
Database changed
mysql> create table doctors(d_id int, d_name varchar(20), d_speciality varchar(20),d_contact
varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> desc doctors;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| d_id | int | YES | | NULL | |
| d_name | varchar(20) | YES | | NULL | |
| d_speciality | varchar(20) | YES | | NULL | |
| d_contact | varchar(10) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> use hospital;
Database changed
mysql> create table patient(p_id int,p_name varchar(20),P_contact varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql> desc patient;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| p_id | int | YES | | NULL | |
| p_name | varchar(20) | YES | | NULL | |
| P_contact | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> use hospital;
Database changed
mysql> create table appointment(a_id int,a_date date,a_reason varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> desc appointment;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| a_id | int | YES | | NULL | |
| a_date | date | YES | | NULL | |
| a_reason | varchar(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> use hospital;
Database changed
mysql> create table medical(m_id int,m_name varchar(20),m_quantity_in_stock
varchar(20),m_expire_date date);
Query OK, 0 rows affected (0.02 sec)
mysql> desc medical;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| m_id | int | YES | | NULL | |
| m_name | varchar(20) | YES | | NULL | |
| m_quantity_in_stock | varchar(20) | YES | | NULL | |
| m_expire_date | date | YES | | NULL | |
+---------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> use hospital;
Database changed
mysql> create table staff(s_id int,s_name varchar(20),s_hiredate date,s_salary varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql> desc staff;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| s_id | int | YES | | NULL | |
| s_name | varchar(20) | YES | | NULL | |
| s_hiredate | date | YES | | NULL | |
| s_salary | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> create view hospital_data as select d_name,d_speciality, p_name , a_date,a_reason from
doctors,patient, appointment;
Query OK, 0 rows affected (0.02 sec)
mysql> desc hospital_data;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| d_name | varchar(20) | YES | | NULL | |
| d_speciality | varchar(20) | YES | | NULL | |
| p_name | varchar(20) | YES | | NULL | |
| a_date | date | YES | | NULL | |
| a_reason | varchar(10) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
PRACTICAL NO. : 2
Title:. Design at least 10 SQL queries for suitable database application using SQL DML statements:
Insert, Select, Update, delete with operators, functions, and set operator, all types of Joins, Sub-
Query and View.
Name: Abhishek Pawar
Roll.No : 40
Batch : T2

mysql> use hospital;

Database changed
mysql> insert into doctors value(4,"Abhishek Pawar","cardiologist",7722034434);
Query OK, 1 row affected (0.00 sec)
mysql> select * from doctors;
+------+------------------+--------------+------------+
| d_id | d_name | d_speciality | d_contact |
+------+------------------+--------------+------------+
| 1 | ashwini gaidhani | psychologist | 9999988887 |
| 2 | Yash patel | therepist | 7777666655 |
| 3 | Roshan nikam | dentist | 5555444433 |
| 4 | Abhishek Pawar | cardiologist | 7722034434 |
+------+------------------+--------------+------------+
4 rows in set (0.00 sec)
mysql> update doctors
-> set d_contact=8888999900
-> where d_id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from doctors;
+------+------------------+--------------+------------+
| d_id | d_name | d_speciality | d_contact |
+------+------------------+--------------+------------+
| 1 | ashwini gaidhani | psychologist | 9999988887 |
| 2 | Yash patel | therepist | 7777666655 |
| 3 | Roshan nikam | dentist | 5555444433 |
| 4 | Abhishek Pawar | cardiologist | 8888999900 |
+------+------------------+--------------+------------+
4 rows in set (0.00 sec)
mysql> insert into doctors value(5,"Roshan rathod","dermatologist",7722034434),(6,"dhaneshwary
kawade","pediatrition",1122334455);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from doctors;
+------+--------------------+---------------+------------+
| d_id | d_name | d_speciality | d_contact |
+------+--------------------+---------------+------------+
| 1 | Siddharth | psychologist | 9999988887 |
| 2 | Yash patel | therepist | 7777666655 |
| 3 | Roshan nikam | dentist | 5555444433 |
| 4 | Abhishek Pawar | cardiologist | 8888999900 |
| 5 | Roshan rathod | dermatologist | 7722034434 |
| 6 | Samarth Kuwar| pediatrition | 1122334455 |
+------+--------------------+---------------+------------+
6 rows in set (0.00 sec)
mysql> delete from doctors
-> where d_id=6;
Query OK, 1 row affected (0.01 sec)
mysql> select * from doctors;
+------+------------------+---------------+------------+
| d_id | d_name | d_speciality | d_contact |
+------+------------------+---------------+------------+
| 1 | ashwini gaidhani | psychologist | 9999988887 |
| 2 | Yash patel | therepist | 7777666655 |
| 3 | Roshan nikam | dentist | 5555444433 |
| 4 | Abhishek Pawar | cardiologist | 8888999900 |
| 5 | Roshan rathod | dermatologist | 7722034434 |
+------+------------------+---------------+------------+
5 rows in set (0.00 sec)
mysql> insert into doctors value(6,"dhaneshwary kawade","pediatrition",1122334455),(7,"rushita
chaudhary","orthopedist",2222333344),(8,"Darshan Mali patil
","gynac",4444555566),(9,"maYash ghaslatewala","neurosurgeon",5555666677),(10,"sanjana
bangar","nephrologist",7777888899);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from doctors;
+------+---------------------+---------------+------------+
| d_id | d_name | d_speciality | d_contact |
+------+---------------------+---------------+------------+
| 1 | Siddharth| psychologist | 9999988887 |
| 2 | Yash patel | therepist | 7777666655 |
| 3 | Roshan nikam | dentist | 5555444433 |
| 4 | Abhishek Pawar | cardiologist | 8888999900 |
| 5 | Roshan rathod | dermatologist | 7722034434 |
| 6 | Samarth Kuwar | pediatrition | 1122334455 |
| 7 | rushita chaudhary | orthopedist | 2222333344 |
| 8 | Darshan Mali patil | gynac | 4444555566 |
| 9 | sanjana bangar | nephrologist | 7777888899 |
+------+---------------------+---------------+------------+
10 rows in set (0.00 sec)
mysql> update doctors
-> set d_speciality="psychiatrist"
-> where d_id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from doctors;
+------+---------------------+---------------+------------+
| d_id | d_name | d_speciality | d_contact |
+------+---------------------+---------------+------------+
| 1 | Siddharth| psychologist | 9999988887 |
| 2 | Yash patel | psychiatrist | 7777666655 |
| 3 | Roshan nikam | dentist | 5555444433 |
| 4 | Abhishek Pawar | cardiologist | 8888999900 |
| 5 | Roshan rathod | dermatologist | 7722034434 |
| 6 | Samarth Kuwar | pediatrition | 1122334455 |
| 7 | rushita chaudhary | orthopedist | 2222333344 |
| 8 | Darshan Mali patil | gynac | 4444555566 |
| 9 | maYash ghaslatewala | neurosurgeon | 5555666677 |
| 10 | sanjana bangar | nephrologist | 7777888899 |
+------+---------------------+---------------+------------+
10 rows in set (0.00 sec)
PRACTICAL NO. : 3
Title:Unnamed PL/SQL code block: Use of Control structure and Exception handling is mandatory.
Name: Abhishek Pawar
Roll.No : 40
Batch : T2
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| student |
| sys |
| world |
+--------------------+
7 rows in set (0.02 sec)
mysql> create database library;
Query OK, 1 row affected (0.00 sec)
mysql> use library;
Database changed
mysql> create table borrower(roll_no int primary key,name varchar(50),date_of_issue
date,name_of_book varchar(20),status varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> create table fine(roll_no int,foreign key(roll_no)references borrower(roll_no),return_date
date,amount int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into borrower values(1,'vaishnavi','2024-08-01','SEPM','I');
Query OK, 1 row affected (0.01 sec)
mysql> insert into borrower values(2,'Yash','2024-08-02','OOP','I');
Query OK, 1 row affected (0.00 sec)
mysql> insert into borrower values(3,'Ashwini','2024-08-03','DBMS','I');
Query OK, 1 row affected (0.00 sec
mysql> insert into borrower values(4,'MaYash','2024-08-03','DSA','I');
Query OK, 1 row affected (0.01 sec)
mysql> insert into borrower values(5,'Darshan Mali','2024-08-03','ADS','I');
Query OK, 1 row affected (0.01 sec)
mysql> select* from borrower;
+---------+-----------+---------------+--------------+--------+
| roll_no | name | date_of_issue | name_of_book | status |
+---------+-----------+---------------+--------------+--------+
| 1 | vaishnavi | 2024-08-01 | SEPM |I |
| 2 | Yash | 2024-08-02 | OOP |I |
| 3 | Ashwini | 2024-08-03 | DBMS |I |
| 4 | MaYash | 2024-08-03 | DSA |I |
| 5 | Darshan Mali | 2024-08-03 | ADS |I |
+---------+-----------+---------------+--------------+--------+
5 rows in set (0.01 sec)
mysql> delimiter $
mysql> create procedure calc_fine_lib(in roll int)
-> begin
-> declare fine1 int;
-> declare noofdays int;
-> declare issuedate date;
-> declare exit handler for SQLEXCEPTION select 'create table definition';
-> select date_of_issue into issuedate from borrower where roll_no=roll;
-> select datediff(curdate(),issuedate)into noofdays;
-> if noofdays>15 and noofdays<=30 then
-> set fine1=noofdays*5;
-> insert into fine values(roll,curdate(),fine1);
-> elseif noofdays>30 then
-> set fine1=((noofdays-30)*50)+30*5;
-> insert into fine values(roll,curdate(),fine1);
-> else
 -> insert into fine values(roll,curdate(),0);
-> end if;
-> update borrower set status='R' where roll_no=roll;
->
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call calc_fine_lib(1);
Query OK, 1 row affected (0.02 sec)
mysql> call calc_fine_lib(2);
Query OK, 1 row affected (0.04 sec)
mysql> call calc_fine_lib(3);
Query OK, 1 row affected (0.01 sec)
mysql> call calc_fine_lib(4);
Query OK, 1 row affected (0.01 sec)
mysql> call calc_fine_lib(5);
Query OK, 1 row affected (0.01 sec)
mysql> select * from fine;
+---------+-------------+--------+
| roll_no | return_date | amount |
+---------+-------------+--------+
| 1 | 2024-09-10 | 650 |
| 2 | 2024-09-10 | 600 |
| 3 | 2024-09-10 | 550 |
| 4 | 2024-09-10 | 550 |
| 5 | 2024-09-10 | 550 |
+---------+-------------+--------+
5 rows in set (0.00 sec)
PRACTICAL NO. : 4
Title: Write a PL/SQL block to create cursor to copy contents of one table into another .Avoid
redundancy.
Name: Abhishek Pawar
Roll.No : 40
Batch : T2

mysql> create database practical4;

Query OK, 1 row affected (0.01 sec)
mysql> use practical4;
Database changed
mysql> create table o_rollcall(roll_no int,name varchar(20), address varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> create table n_rollcall(roll_no int,name varchar(20), address varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into o_rollcall values('1','Vaishnavi','pimpalgaon');
Query OK, 1 row affected (0.01 sec)
mysql> insert into o_rollcall values('2','Yash','Nashik');
Query OK, 1 row affected (0.01 sec)
mysql> insert into o_rollcall values('3','MaYash','Nashik');
Query OK, 1 row affected (0.01 sec)
mysql> insert into o_rollcall values('4','Ashwini','Nashik');
Query OK, 1 row affected (0.01 sec)
mysql> insert into o_rollcall values('5','Roshan','Malegaon');
Query OK, 1 row affected (0.01 sec)
mysql> delimiter //
mysql> create procedure p3(in r1 int)
-> begin
-> declare r2 int;
-> declare exit_loop boolean;
-> declare c1 cursor for select roll_no from o_rollcall where roll_no>r1;
-> declare continue handler for not found set exit_loop=true;
 -> open c1;
-> e_loop:loop
-> fetch c1 into r2;
-> if not exists(select * from n_rollcall where roll_no=r2)
-> then
-> insert into n_rollcall select * from o_rollcall where roll_no=r2;
-> end if;
-> if exit_loop
-> then
-> close c1;
-> leave e_loop;
-> end if;
-> end loop e_loop;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> call p3(3);
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> select * from n_rollcall;
-> //
+---------+---------+----------+
| roll_no | name | address |
+---------+---------+----------+
| 4 | Ashwini | Nashik |
| 5 | Roshan | Malegaon |
+---------+---------+----------+
2 rows in set (0.00 sec)
mysql> select * from n_rollcall;
-> //
+---------+---------+----------+
| roll_no | name | address |
+---------+---------+----------+
| 4 | Ashwini | Nashik |
| 5 | Roshan | Malegaon |
+---------+---------+----------+
2 rows in set (0.00 sec)
mysql> insert into o_rollcall values('6','Naira','jaipur');
-> //
Query OK, 1 row affected (0.01 sec)
mysql> call p3(4);
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> select * from n_rollcall;
-> //
+---------+---------+----------+
| roll_no | name | address |
+---------+---------+----------+
| 4 | Ashwini | Nashik |
| 5 | Roshan | Malegaon |
| 6 | Naira | jaipur |
+---------+---------+----------+
3 rows in set (0.00 sec)
PRACTICAL NO. : 5
Title: To study and inplement PL/SQL programming along with procedures and functions
Name: Abhishek Pawar
Roll.No : 40
Batch : T2

mysql> create database practical5;

Query OK, 1 row affected (0.01 sec)
mysql> use practical5;
Database changed
mysql> create table marks(roll_no int , name varchar(20), total_marks varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> create table result(roll_no int , name varchar(20), class varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into marks values('1','vaishnavi','1400');
Query OK, 1 row affected (0.02 sec)
mysql> insert into marks values('2','Yash','980');
Query OK, 1 row affected (0.00 sec)
mysql> insert into marks values('3','ashwini','880');
Query OK, 1 row affected (0.00 sec)
mysql> insert into marks values('4','Roshan','820');
Query OK, 1 row affected (0.01 sec)
mysql> insert into marks values('5','MaYash','740');
Query OK, 1 row affected (0.00 sec)
mysql> insert into marks values('5','Sanjana','640');
Query OK, 1 row affected (0.00 sec)
mysql> insert into marks values('6','Aditi','640');
Query OK, 1 row affected (0.01 sec)
mysql> delimiter //
mysql> create procedure proc_result(in marks int,out class varchar(20))
-> begin
 -> if(marks<1500 && marks>990)
-> then
-> set class="Distinction";
-> end if;
-> if(marks<989 && marks>890)
-> then
-> set class="First class";
-> end if;
-> if(marks<889 && marks>825)
-> then
-> set class="Higher second class";
-> end if;
-> if(marks<824 && marks>750)
-> then
-> set class="Second Class";
-> end if;
-> if(marks<749 && marks>650)
-> then
-> set class="Passsed";
-> end if;
-> if(marks<649)
-> then
-> set class="Fail";
-> end if;
-> end;
-> //
Query OK, 0 rows affected, 5 warnings (0.01 sec)
mysql> set global
-> log_bin_trust_function_creators=1;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> create function final_result(R1 int)
 -> returns int
-> begin
-> declare fmarks integer;
-> declare grade varchar(20);
-> declare stud_name varchar(20);
-> select marks.total_marks,marks.name into fmarks,stud_name from marks where
marks.roll_no=R1;
-> call proc_result(fmarks,@grade);
-> insert into result values(R1,stud_name,@grade);
-> return R1;
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> select final_result(2);
-> //
+-----------------+
| final_result(2) |
+-----------------+
| 2|
+-----------------+
1 row in set (0.04 sec)
mysql> select final_result(1);
-> //
+-----------------+
| final_result(1) |
+-----------------+
| 1|
+-----------------+
1 row in set (0.01 sec)
mysql> select final_result(3);
-> //
+-----------------+
| final_result(2) |
+-----------------+
| 3|
+-----------------+
1 row in set (0.01 sec)
mysql> select final_result(4);
-> //
+-----------------+
| final_result(3) |
+-----------------+
| 4|
+-----------------+
1 row in set (0.01 sec)
mysql> select final_result(5);
-> //
+-----------------+
| final_result(4) |
+-----------------+
| 5|
+-----------------+
1 row in set (0.01 sec)
mysql> select final_result(6);
-> //
+-----------------+
| final_result(6) |
+-----------------+
| 6|
+-----------------+
1 row in set (0.01 sec)
mysql> select * from result;
-> //
+---------+-----------+---------------------+
| roll_no | name | class |
+---------+-----------+---------------------+
| 2 | Yash | First class |
| 1 | vaishnavi | Distinction |
| 3 | Yash | First class |
| 4 | ashwini | Higher second class |
| 5 | Roshan | Second Class |
| 6 | tina | Fail |
+---------+-----------+---------------------+
6 rows in set (0.01 sec)
PRACTICAL NO. : 7
Title: Install and study MongoDB and implement CRUD operation
Name: Abhishek Pawar
Roll.No : 40
Batch : T2
// create database
use practical7
// insert command
db.product.insertOne({
item_no: 1,
name: "Smartphone",
category: "Electronics",
price: 49999
});
db.product.insertOne({
item_no: 2,
name: "Laptop",
category: "Electronics",
price: 89999
});
db.product.insertOne({
item_no: 3,
name: "Wireless Headphones",
category: "Accessories",
price: 12999
});
// find command
db.product.find()
// update command
db.product.updateOne(
{ item_no: 2 },
 {
$set: { price: 74999 }
}
);
db.product.find()
// delete command
db.product.deleteOne({category :"Accessories" })
db.product.find()
Output:
switched to db practical7
{ "acknowledged" : true,
"insertedId" : ObjectId("67342d619efa1f82c530628d")
}
{ "acknowledged" : true,
"insertedId" : ObjectId("67342d619efa1f82c530628e")
}
{ "acknowledged" : true,
"insertedId" : ObjectId("67342d619efa1f82c530628f")}
{ "_id" : ObjectId("67342d619efa1f82c530628d"), "item_no" : 1, "name" : "Smartphone",
"category" : "Electronics", "price" : 49999 }
{ "_id" : ObjectId("67342d619efa1f82c530628e"), "item_no" : 2, "name" : "Laptop", "category" :
"Electronics", "price" : 89999 }
{ "_id" : ObjectId("67342d619efa1f82c530628f"), "item_no" : 3, "name" : "Wireless Headphones",
"category" : "Accessories", "price" : 12999 }
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
{ "_id" : ObjectId("67342d619efa1f82c530628e"), "item_no" : 2, "name" : "Laptop", "category" :
"Electronics", "price" : 74999 }
{ "_id" : ObjectId("67342d619efa1f82c530628f"), "item_no" : 3, "name" : "Wireless Headphones",
"category" : "Accessories", "price" : 12999 }
{ "acknowledged" : true, "deletedCount" : 1 }
{ "_id" : ObjectId("67342d619efa1f82c530628d"), "item_no" : 1, "name" : "Smartphone",
"category" : "Electronics", "price" : 49999 }
{ "_id" : ObjectId("67342d619efa1f82c530628e"), "item_no" : 2, "name" : "Laptop", "category" :
"Electronics", "price" : 74999 }
PRACTICAL NO. : 8
Title:Implement aggregation,indexing with suitable example in MongoDB .Demostate the following .
Name: Abhishek Pawar
Roll.No : 40
Batch : T2
//USE DATABASE
use comp;
//CREATE COLLECTION WEBSITE
db.createCollection('website');
//INSERT VALUES IN WEBSITE
db.website.insert({'roll':'1','name':'harsh','amount':1000,'ur l':'www.yahoo.com'})
db.website.insert({'roll':'2','name':'jitesh','amount':2000,'url':'www.yahoo.com '});
db.website.insert({'roll':'3','name':'rina','amount':3000,'url':'www.google.com'});
db.website.insert({'roll':'4','name':'ash','amount':4000,'url':'www.gmail.com'});
db.website.insert({'roll':'5','name':'ash','amount':1000,'url':'www.pvg.com'});
//SUM AGGREGATE
db.website.aggregate({$group:{_id:"$name","total":{$sum:"$amount"}}});
//AVG AGGREGATE
db.website.aggregate({$group:{_id:"$name","total":{$avg:"$amount"}}});
//MIN AGGREGATION
db.website.aggregate({$group:{_id:"$name","total":{$min:"$amount"}}});
//MAX AGGREGATION
db.website.aggregate({$group:{_id:"$name","total":{$max:"$amount"}}});
//FIRST AGGREGATION
db.website.aggregate({$group:{_id:"$name","total":{$first:"$amount"}}});
//LAST AGGREGATION
db.website.aggregate({$group:{_id:"$name","total":{$last:"$amount"}}});
//PUSH AGGREGATION
db.website.aggregate({$group:{_id:"$name","total":
{$push:"$amount"}}});
//COUNT AGGREGATION
db.website.aggregate({$group:{_id:"$name","total": {$sum:1}}});
//ADDTOSET AGGREGATE
db.website.aggregate({$group:{_id:"$name","total"{$addToSet:"$amount"}}});
//INDEXING
db.createCollection('website1');
db.website1.insert({'r':1,'name':'harsh'});
db.website1.find().pretty()
db.website1.createIndex({'name':1})
//CREATE INDEXING
db.website1.createIndex({'name':-1})
db.website1.getIndexses()
db.website1.getIndexes() [
{"v" : 1, },
{"v" : 1,"key" : {"name" : 1},"name" : "name_1", "ns" : "harsh.website1"},
{"v" : 1,"key" : {"name" : -1},"name" : "name_-1", "ns" : "harsh.website1"}
]
db.website1.createIndex({'name':-1})
//DROP INDEX
db.website.dropIndex({'name':-1})
db.website1.dropIndex({'name':1})
db.website1.dropIndex({'name':1})
//GET INDEXING
db.website1.getIndexes() [
{"v" : 1,}
]
db.website1.find().pretty()
db.website1.createIndex({'name':1})
db.website1.getIndexes()[
{"v" : 1,"key" : {"_id" : 1},"name" : "_id_","ns" : "harsh.website1"},
{"v" : 1,"key" : {"name" : 1},"name" : "name_1", "ns" : "harsh.website1"}
]
db.website1.dropIndex({'name':1})
db.website1.getIndexes() [
{"v" : 1,"key" : {"_id" : 1},"name" : "_id_","ns" : "harsh.website1"}
]
db.website1.createIndex({'name':1,'r':-1})
db.website1.getIndexes() [
{"v" : 1,"key" : {"_id" : 1},"name" : "_id_","ns" : "harsh.website1"},
{"v" : 1,"key" : {"name" : 1,"r" : -1},"name" : "name_1_r_-1", "ns" : "harsh.website1"}
]
db.website1.insert({'roll':1,'name':'harsh'});
STDIN
Input for the program ( Optional )
Output:
switched to db comp
{ "ok" : 0,
"errmsg" : "Collection already exists. NS: comp.website",
"code" : 48,
"codeName" : "NamespaceExists"
}
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
{ "_id" : "harsh", "total" : 2000 }
{ "_id" : "rina", "total" : 6000 }
{ "_id" : "jitesh", "total" : 4000 }
{ "_id" : "ash", "total" : 9000 }
{ "_id" : "harsh", "total" : 1000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "ash", "total" : 3000 }
{ "_id" : "harsh", "total" : 1000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "ash", "total" : 1000 }
{ "_id" : "harsh", "total" : 1000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "ash", "total" : 4000 }
{ "_id" : "harsh", "total" : 1000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "ash", "total" : 4000 }
{ "_id" : "harsh", "total" : 1000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "ash", "total" : 1000 }
{ "_id" : "harsh", "total" : [ 1000, 1000 ] }
{ "_id" : "rina", "total" : [ 3000, 3000 ] }
{ "_id" : "jitesh", "total" : [ 2000, 2000 ] }
{ "_id" : "ash", "total" : [ 4000, 4000, 1000 ] }
{ "_id" : "harsh", "total" : 2 }
{ "_id" : "rina", "total" : 2 }
{ "_id" : "jitesh", "total" : 2 }
{ "_id" : "ash", "total" : 3 }
uncaught exception: SyntaxError: expected expression, got '>' :
@(shell):1:0
{ "ok" : 1 }
WriteResult({ "nInserted" : 1 })
{ "_id" : ObjectId("6734995cd751573da24237ee"), "r" : 1, "name" : "harsh" }
{ "numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"createdCollectionAutomatically" : false,
"ok" : 1}
{ "numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"createdCollectionAutomatically" : false,
"ok" : 1}
uncaught exception: TypeError: db.website1.getIndexses is not a function :
@(shell):1:1
{ "numIndexesBefore" : 3,
"numIndexesAfter" : 3,
"note" : "all indexes already exist",
"ok" : 1}
{ "ok" : 0,
"errmsg" : "can't find index with key: { name: -1.0 }",
"code" : 27,
"codeName" : "IndexNotFound"}
{ "nIndexesWas" : 3, "ok" : 1 }
{ "ok" : 0,
"errmsg" : "can't find index with key: { name: 1.0 }",
"code" : 27,
"codeName" : "IndexNotFound"}
{ "_id" : ObjectId("6734995cd751573da24237ee"), "r" : 1, "name" : "harsh" }
{ "numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"createdCollectionAutomatically" : false,
"ok" : 1}
{ "nIndexesWas" : 3, "ok" : 1 }
{ "numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"createdCollectionAutomatically" : false,
"ok" : 1
}
WriteResult({ "nInserted" : 1 })
PRACTICAL NO. : 9
Title:Implement map reduces opetration with suitable example using MongoDB .
Name: Abhishek Pawar
Roll.No : 40
Batch : T2
use Abhi ;
db.createCollection('Journal');
db.Journal.insert({'book_id':1,'book_name':'JavacdOOP','amt':500,'status':'A vailable'});
db.Journal.insert({'book_id':1,'book_name':'JavaOOP','amt':400,'status':'Not Available'});
db.Journal.insert({'book_id':1,'book_name':'Java','amt':300,'s tatus':'Not Available'});
db.Journal.insert({'book_id':2,'book_name':'Java','amt':300,'s tatus':'Available'});
db.Journal.insert({'book_id':2,'book_name':'OPP','amt':200,'st atus':'Available'});
db.Journal.insert({'book_id':2,'book_name':'C+','amt':200,'status':'Available'});
db.Journal.insert({'book_id':3,'book_name':'C+','amt':150,'status':'Available'});
db.Journal.insert({'book_id':3,'book_name':'C++','amt':200,'status':'Not Available'});
db.Journal.insert({'book_id':4,'bookname':'OPP C++','amt':300,'status':'Not Available'});
db.Journal.insert({'book_id':5,'book_name':'OPP C++','amt':400,'status':'Available'});
db.Journal.insert({'book_id':5,'book_name':'C++','amt':400,'status':'Available'});
db.Journal.insert({'book_id':5,'book_name':'C++ Java','amt':400,'status':'Not Available'});
var mapfunction = function() {
emit(this.book_id, this.amt); };
var reducefunction = function(key, values) {
return Array.sum(values); };
// Perform the mapReduce operation
db.Journal.mapReduce(mapfunction, reducefunction, { out: 'new' });
// Query the output collection 'new'
db.new.find().pretty();

Output:

switched to db Abhi
{ "ok" : 1 }
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
{ "result" : "new", "ok" : 1 }
{ "_id" : 5, "value" : 1200 }
{ "_id" : 2, "value" : 700 }
{ "_id" : 3, "value" : 350 }
{ "_id" : 4, "value" : 300 }
{ "_id" : 1, "value" : 1200 }