create table Employee(

emp_id varchar2(10),
emp_name varchar2(20),
emp_desc varchar2(15),
dept_name varchar2(20),
emp_city varchar2(10),
primary key(emp_name)
);

insert into Employee values(30001,'Vaibhavi','Accountant','Sales','Pune');

insert into Employee values(30002,'Mohana','Associate','QualityControl','Solapur');
insert into Employee values(30003,'Advait','FinancialManager','Finance','Pune');
insert into Employee values(30004,'Prasad','Manager','Production','Pune');
insert into Employee values(30005,'Gaurav','Analyst','CustomerCare','Mumbai');
insert into Employee values(30006,'Aditi','Accountant','Payroll','Pune');
insert into Employee values(30007,'Anushka','Associate','HumanResources','Pune');
insert into Employee values(30008,'Rohan','Analyst','Sales','Mumbai');
insert into Employee values(30009,'Isha','Accountant','Finance','Nashik');
insert into Employee values(30010,'Janhavi','Associate','Production','Jalgaon');

create table Department(

dept_name varchar2(20),
manager_name varchar2(20),
primary key(dept_name)
);

insert into Department values('Sales','Yash');

insert into Department values('QualityControl','Piyush');
insert into Department values('Finance','Aditya');
insert into Department values('Production','Safal');
insert into Department values('CustomerCare','Anand');
insert into Department values('HumanResources','Jay');

create table Workers(

emp_name varchar2(20),
dept_name varchar2(20),
emp_salary number(7) not null,
primary key(emp_name),
foreign key(dept_name) references Department(dept_name)
);

insert into Workers values('Vaibhavi','Sales',20000);

insert into Workers values('Mohana','QualityControl',25000);
insert into Workers values('Advait','Finance',35000);
insert into Workers values('Prasad','Production',50000);
insert into Workers values('Gaurav','CustomerCare',15000);
insert into Workers values('Aditi','HumanResources',17000);
insert into Workers values('Isha','Finance',30000);

select * from employee;

select * from department;
select * from workers;

alter table Department

add dept_id number(5);

desc Department;

create view view_emp as

select emp_name,dept_name
from Employee
where emp_city='Pune';
select * from view_emp;

drop table Department;

create table Employee(
emp_id number(10),
emp_name string(20),
emp_desc string(15),
dept_name string(20),
emp_city string(10),
primary key(emp_name)
);

insert into Employee values(30001,'Vaibhavi','Accountant','Sales','Pune');

insert into Employee values(30002,'Mohana','Associate','QualityControl','Solapur');
insert into Employee values(30003,'Advait','FinancialManager','Finance','Pune');
insert into Employee values(30004,'Prasad','Manager','Production','Pune');
insert into Employee values(30005,'Gaurav','Analyst','CustomerCare','Mumbai');
insert into Employee values(30006,'Aditi','Accountant','Payroll','Pune');
insert into Employee values(30007,'Anushka','Associate','HumanResources','Pune');
insert into Employee values(30008,'Rohan','Analyst','Sales','Mumbai');
insert into Employee values(30009,'Isha','Accountant','Finance','Nashik');
insert into Employee values(30010,'Janhavi','Associate','Production','Jalgaon');

create table Department(

dept_name string(20),
manager_name string(20),
primary key(dept_name)
);

insert into Department values('Sales','Yash');

insert into Department values('QualityControl','Piyush');
insert into Department values('Finance','Aditya');
insert into Department values('Production','Safal');
insert into Department values('CustomerCare','Anandrao');
insert into Department values('HumanResources','Jay');

create table Workers(

emp_name string(20),
dept_name string(20),
emp_salary number(7) not null,
primary key(emp_name),
foreign key(dept_name) references Department(dept_name)
);

insert into Workers values('Vaibhavi','Sales',20000);

insert into Workers values('Mohana','QualityControl',25000);
insert into Workers values('Advait','Finance',35000);
insert into Workers values('Prasad','Production',50000);
insert into Workers values('Gaurav','CustomerCare',15000);
insert into Workers values('Aditi','Payroll',17000);
insert into Workers values('Isha','Finance',30000);

select emp_name from Employee;

select emp_name from Workers where emp_salary >= 30000;

select emp_name from Employee where dept_name='Sales';

select emp_name from Employee where emp_city='Pune';

select * from Employee where emp_city='Pune';

select * from Employee where emp_city='Solapur';

select * from Employee where emp_city='Mumbai';

select emp_name from Employee where emp_name like 'A%';

select emp_name,max(emp_salary) as "Highest Salary" from Workers;

select emp_name,min(emp_salary) as "Lowest Salary" from Workers;

select * from Employee order by emp_name asc;

select * from Employee order by emp_name desc;

select D.dept_name,W.emp_name,W.emp_salary
from Department as D,Workers as W
where D.dept_name=W.dept_name and D.manager_name='Piyush';

-- Inner join
select Department.dept_name,Workers.emp_salary
from Department
inner join Workers
on Workers.dept_name=Department.dept_name;

-- Left join
select Department.dept_name,Workers.emp_salary
from Department
left join Workers
on Workers.dept_name=Department.dept_name;

-- Right Outer join

select Department.dept_name,Workers.emp_salary
from Department
right join Workers
on Workers.dept_name=Department.dept_name;

-- Full join
select Department.dept_name,Workers.emp_salary
from Department
full join Workers
on Workers.dept_name=Department.dept_name;

-- Subqueries
select emp_name,dept_name from Employee
where emp_name in (select emp_name from Employee where emp_city='Pune');

update Department
set manager_name='Parth'
where dept_name in (select dept_name from Workers where emp_name='Gaurav');
select * from Department where dept_name='CustomerCare';

delete from Workers

where emp_name in(select emp_name from Employee where dept_name='Production');
select * from Workers;

select dept_name from Department where exists(select emp_name from Employee where
emp_city='Pune');

select emp_id,Employee.emp_name,emp_salary
from Employee,Workers
where Employee.emp_name=Workers.emp_name and emp_city='Pune';

select emp_name,dept_name
from Employee
order by dept_name desc;

select distinct dept_name

from Employee;

select avg(emp_salary)
from Workers;

select count(emp_name)
from Employee
where emp_city='Pune';

select sum(emp_salary)
from Workers
where dept_name='Finance';

select distinct emp_city from Employee;

select emp_name,emp_salary
from Workers
where emp_salary between 10000 and 20000;

select emp_name
from Employee
where exists(select emp_salary from Workers where Employee.emp_name=Workers.emp_name
and emp_salary>=20000);
create or replace function calfine(days number)
return number is
amount number:=0;
begin
IF(days>15 and days<30) then
amount:=days*5;
ELSIF(days>30) then
amount:=days*50;
ELSE
amount:=0;
END IF;
return amount;
end;
/

set serveroutput on;

declare
roll_num number(2);
book_name varchar2(20);
nm varchar2(25);
s varchar2(10);
date_fine date;
d_issue date;
days number(2);
amt number(5);
begin
roll_num:=&roll_num;
book_name:=&book_name;
select roll_no,name,name_of_book,status,date_of_issue into
roll_num,nm,book_name,s,d_issue from borrower where roll_no=roll_num;

days:=sysdate-d_issue;
dbms_output.put_line('Roll no = '||roll_num);
dbms_output.put_line('Name = '||nm);
dbms_output.put_line('Book name = '||book_name);
dbms_output.put_line('Status '||s);
dbms_output.put_line('Days are '||days);

amt:=calfine(days);
dbms_output.put_line('Fine amount is '||amt);
end;
/

Output:
Microsoft Windows [Version 10.0.19044.2075]
(c) Microsoft Corporation. All rights reserved.

C:\Users\VAIBHAVI>sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 16 13:13:40 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table borrower(roll_no number(2),name

varchar2(25),date_of_issue date,name_of_book varchar2(20),status
varchar2(10));

Table created.

SQL> create table fine(roll_no number(2),date_fine date,amount number(5));

Table created.

SQL> insert into borrower values(27,'Vaibhavi','20-september-2022','IOT','I');

1 row created.

SQL> insert into borrower values(37,'Mohana','09-september-2022','HCI','I');

1 row created.

SQL> insert into borrower values(42,'Aditi','05-october-2022','CNS','I');

1 row created.

SQL> insert into borrower values(01,'Anushka','27-september-2022','SPOS','I');

1 row created.

SQL> select * from borrower;

ROLL_NO NAME DATE_OF_I NAME_OF_BOOK STATUS

---------- ------------------------- --------- -------------------- ----------
27 Vaibhavi 20-SEP-22 IOT I
37 Mohana 09-SEP-22 HCI I
42 Aditi 05-OCT-22 CNS I
1 Anushka 27-SEP-22 SPOS I

SQL> create or replace function calfine(days number)

2 return number is
3 amount number:=0;
4 begin
5 IF(days>15 and days<30) then
6 amount:=days*5;
7 ELSIF(days>30) then
8 amount:=days*50;
9 ELSE
10 amount:=0;
11 END IF;
12 return amount;
13 end;
14 /
Function created.
SQL>@library

Enter value for roll_num: 37

old 12: roll_num:=&roll_num;
new 12: roll_num:=37;
Enter value for book_name: 'HCI'
old 13: book_name:=&book_name;
new 13: book_name:='HCI';

Roll no = 37
Name = Mohana
Book name = HCI
Status I
Days are 38
Fine amount is 1900

PL/SQL procedure successfully completed.

SQL>@library

Enter value for roll_num: 42

old 12: roll_num:=&roll_num;
new 12: roll_num:=42;
Enter value for book_name: 'CNS'
old 13: book_name:=&book_name;
new 13: book_name:='CNS';
Roll no = 42
Name = Aditi
Book name = CNS
Status I
Days are 12
Fine amount is 0

PL/SQL procedure successfully completed.

create or replace function calfine(days number)
return number is
amount number:=0;
begin
IF(days>15 and days<30) then
amount:=days*5;
ELSIF(days>30) then
amount:=days*50;
ELSE
amount:=0;
END IF;
return amount;
end;
/

set serveroutput on;

declare
roll_num number(2);
book_name varchar2(20);
nm varchar2(25);
s varchar2(10);

date_fine date;
d_issue date;
days number(2);
amt number(5);
begin
roll_num:=&roll_num;
book_name:=&book_name;
select roll_no,name,name_of_book,status,date_of_issue into
roll_num,nm,book_name,s,d_issue from borrower where roll_no=roll_num;

days:=sysdate-d_issue;

dbms_output.put_line('Roll no = '||roll_num);
dbms_output.put_line('Name = '||nm);
dbms_output.put_line('Book name = '||book_name);
dbms_output.put_line('Status '||s);
dbms_output.put_line('Days are '||days);

amt:=calfine(days);
dbms_output.put_line('Fine amount is '||amt);

--Exception handling

IF(amt>0) THEN
insert into fine values(roll_num,sysdate,amt);
dbms_output.put_line('One row inserted');

update borrower
set status='R'
where roll_num=roll_no;
END IF;
exception
when no_data_found then
dbms_output.put_line('Record not found');
end;
/

Output:
Microsoft Windows [Version 10.0.19044.2075]
(c) Microsoft Corporation. All rights reserved.

C:\Users\VAIBHAVI>sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 16 17:57:00 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from borrower;

ROLL_NO NAME DATE_OF_I NAME_OF_BOOK STATUS

---------- ------------------------- --------- -------------------- ----------
27 Vaibhavi 20-SEP-22 IOT I
37 Mohana 09-SEP-22 HCI I
42 Aditi 05-OCT-22 CNS I
1 Anushka 27-SEP-22 SPOS I

SQL> desc fine;

Name Null? Type

----------------------------------------- -------- ----------------------------
ROLL_NO NUMBER(2)
DATE_FINE DATE
AMOUNT NUMBER(5)

SQL> create or replace function calfine(days number)

2 return number is
3 amount number:=0;
4 begin
5 IF(days>15 and days<30) then
6 amount:=days*5;
7 ELSIF(days>30) then
8 amount:=days*50;
9 ELSE
10 amount:=0;
11 END IF;
12 return amount;
13 end;
14 /
Function created.
SQL>@library
Enter value for roll_num: 01
old 12: roll_num:=&roll_num;
new 12: roll_num:=01;
Enter value for book_name: 'SPOS'
old 13: book_name:=&book_name;
new 13: book_name:='SPOS';

Roll no = 1
Name = Anushka
Book name = SPOS
Status I
Days are 20
Fine amount is 100
One row inserted

PL/SQL procedure successfully completed.

SQL>@library

Enter value for roll_num: 27

old 12: roll_num:=&roll_num;
new 12: roll_num:=27;
Enter value for book_name: 'IOT'
old 13: book_name:=&book_name;
new 13: book_name:='IOT';
Roll no = 27
Name = Vaibhavi
Book name = IOT
Status I
Days are 27
Fine amount is 135
One row inserted

PL/SQL procedure successfully completed.

SQL> select * from fine;

ROLL_NO DATE_FINE AMOUNT

---------- --------- ----------
1 16-OCT-22 100
27 16-OCT-22 135

SQL> select * from borrower;

ROLL_NO NAME DATE_OF_I NAME_OF_BOOK STATUS

---------- ------------------------- --------- -------------------- ----------
27 Vaibhavi 20-SEP-22 IOT R
37 Mohana 09-SEP-22 HCI I
42 Aditi 05-OCT-22 CNS I
1 Anushka 27-SEP-22 SPOS R
create or replace function proc_grades(t IN number)
return varchar
is
grade varchar(20);
begin
if(t<=1500 and t>=990) then
grade:='Distinction';
elsif(t<=989 and t>=900) then
grade:='First Class';
elsif(t<=899 and t>=825) then
grade:='Higher Second Class';
else
grade:='Fail';
end if;
return grade;
end;
/

set serveroutput on;

declare
roll_num number(2);
stud_name varchar2(20);
stud_class varchar(30);

total number(5);
begin
roll_num:=&roll_num;
select roll_no,s_name,total_marks into roll_num,stud_name,total from
stud_marks6 where roll_no=roll_num;
dbms_output.put_line('Roll number = '||roll_num);
dbms_output.put_line('Name of student = '||stud_name);
dbms_output.put_line('Total marks = '||total);

stud_class:=proc_grades(total);
dbms_output.put_line('Grade is '||stud_class);

insert into stud_result6 values(roll_num,stud_name,stud_class);

dbms_output.put_line('One row inserted');
end;
/

Output:
Microsoft Windows [Version 10.0.19044.2075]
(c) Microsoft Corporation. All rights reserved.

C:\Users\VAIBHAVI>sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 16 18:35:13 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table stud_marks(roll_no number(2),s_name
varchar(20),total_marks number(5));

Table created.

SQL> create table stud_result(roll_no number(2),s_name varchar(20),s_class

varchar(30));

Table created.

SQL> insert into stud_marks values(42,'Aditi',1200);

1 row created.

SQL> insert into stud_marks values(01,'Anushka',920);

1 row created.

SQL> insert into stud_marks values(37,'Mohana',830);

1 row created.

SQL> insert into stud_marks values(27,'Vaibhavi',800);

1 row created.
SQL> select * from stud_marks;
ROLL_NO S_NAME TOTAL_MARKS
---------- -------------------- -----------
42 Aditi 1200
1 Anushka 920
37 Mohana 830
27 Vaibhavi 800

SQL> create or replace function proc_grades(t IN number)

2 return varchar
3 is
4 grade varchar(20);
5 begin
6 if(t<=1500 and t>=990) then
7 grade:='Distinction';
8 elsif(t<=989 and t>=900) then
9 grade:='First Class';
10 elsif(t<=899 and t>=825) then
11 grade:='Higher Second Class';
12 else
13 grade:='Fail';
14 end if;
15 return grade;
16 end;
17 /
Function created.
SQL>@grade_proc
Enter value for roll_num: 42
old 8: roll_num:=&roll_num;
new 8: roll_num:=42;
Roll number = 42
Name of student = Aditi
Total marks = 1200
Grade is Distinction
One row inserted

PL/SQL procedure successfully completed.

SQL>@grade_proc

Enter value for roll_num: 37

old 8: roll_num:=&roll_num;
new 8: roll_num:=37;
Roll number = 37
Name of student = Mohana
Total marks = 830
Grade is Higher Second Class
One row inserted

PL/SQL procedure successfully completed.

SQL>@grade_proc
Enter value for roll_num: 01
old 8: roll_num:=&roll_num;
new 8: roll_num:=01;
Roll number = 1
Name of student = Anushka
Total marks = 920
Grade is First Class
One row inserted

PL/SQL procedure successfully completed.

SQL> select * from stud_result6;

ROLL_NO S_NAME S_CLASS

---------- -------------------- ------------------------------
42 Aditi Distinction
37 Mohana Higher Second Class
1 Anushka First Class
set serveroutput on;
declare
roll O_RollCall.roll_no%type;
a O_RollCall%Rowtype ;

begin
For a in(select roll_no,name,age,marks from O_RollCall where roll_no not
in(select roll_no from N_RollCall))

loop
insert into N_RollCall values(a.roll_no,a.name,a.age,a.marks);
end loop;
dbms_output.put_line('Values copied');
end;
/

Output:
Microsoft Windows [Version 10.0.19044.2075]
(c) Microsoft Corporation. All rights reserved.

C:\Users\VAIBHAVI>sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 16 18:57:34 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table O_RollCall(roll_no number(2),name varchar2(25),age

number(2),marks number(3));

Table created.

SQL> create table N_RollCall(roll_no number(2),name varchar2(25),age

number(2),marks number(3));

Table created.

SQL> insert into O_RollCall values(27,'Vaibhavi',20,100);

1 row created.

SQL> insert into O_RollCall values(37,'Mohana',20,190);

1 row created.

SQL> insert into O_RollCall values(42,'Aditi',19,187);

1 row created.

SQL> insert into O_RollCall values(01,'Anushka',20,175);

1 row created.

SQL> insert into N_RollCall values(27,'Vaibhavi',20,100);

1 row created.

SQL> insert into N_RollCall values(29,'Advait',20,190);

1 row created.

SQL> insert into N_RollCall values(26,'Prasad',20,185);

1 row created.

SQL> insert into N_RollCall values(04,'Safal',20,179);

1 row created.

SQL> select * from O_RollCall;

ROLL_NO NAME AGE MARKS

---------- ------------------------- ---------- ----------
27 Vaibhavi 20 100
37 Mohana 20 190
42 Aditi 19 187
1 Anushka 20 175
SQL> select * from N_RollCall;

ROLL_NO NAME AGE MARKS

---------- ------------------------- ---------- ----------
27 Vaibhavi 20 100
29 Advait 20 190
26 Prasad 20 185
4 Safal 20 179

SQL> set serveroutput on;

SQL> declare
2 roll O_RollCall.roll_no%type;
3 a O_RollCall%Rowtype ;
4
5 begin
6 For a in(select roll_no,name,age,marks from O_RollCall where roll_no
not in(select roll_no from N_RollCall))
7
8 loop
9 insert into N_RollCall values(a.roll_no,a.name,a.age,a.marks);
10 end loop;
11 dbms_output.put_line('Values copied');
12 end;
13 /

Values copied
PL/SQL procedure successfully completed.

SQL> select * from N_RollCall;

ROLL_NO NAME AGE MARKS

---------- ------------------------- ---------- ----------
27 Vaibhavi 20 100
29 Advait 20 190
26 Prasad 20 185
4 Safal 20 179
42 Aditi 19 187
1 Anushka 20 175
37 Mohana 20 190

7 rows selected.
create or replace function calfine(days number)
return number is
amount number:=0;
begin
IF(days>15 and days<30) then
amount:=days*5;
ELSIF(days>30) then
amount:=days*50;
ELSE
amount:=0;
END IF;
return amount;
end;
/

create or replace trigger modify_library

after update
on library
for each row
enable

begin
insert into audit(roll_no,name,date_of_issue,name_of_book,status)

values(:old.roll_no,:old.name,:old.date_of_issue,:old.name_of_book,:old.status)
;

dbms_output.put_line('One row inserted in Audit table');

end;
/

set serveroutput on;

declare
roll_num number(2);
book_name varchar2(20);
nm varchar2(25);
s varchar2(10);

date_fine date;
d_issue date;
days number(2);
amt number(5);
begin
roll_num:=&roll_num;
book_name:=&book_name;
select roll_no,name,name_of_book,status,date_of_issue into
roll_num,nm,book_name,s,d_issue from library where roll_no=roll_num;

days:=sysdate-d_issue;

dbms_output.put_line('Roll no = '||roll_num);
dbms_output.put_line('Name = '||nm);
dbms_output.put_line('Book name = '||book_name);
dbms_output.put_line('Status '||s);
dbms_output.put_line('Days are '||days);
amt:=calfine(days);
dbms_output.put_line('Fine amount is '||amt);

end;
/

Output:
Microsoft Windows [Version 10.0.19044.2075]
(c) Microsoft Corporation. All rights reserved.

C:\Users\VAIBHAVI>sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 16 19:06:02 2022

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table library(roll_no number(2),name varchar2(25),date_of_issue

date,name_of_book varchar2(20),status varchar2(10));
Table created.

SQL> create table audit(roll_no number(2),name varchar2(25),date_of_issue

date,name_of_book varchar2(20),status varchar2(10));

Table created.

SQL> insert into library values(27,'Vaibhavi','09-september-2022','IOT','I');

1 row created.

SQL> insert into library values(37,'Mohana','01-october-2022','HCI','I');

1 row created.

SQL> insert into library values(42,'Aditi','10-october-2022','CNS','I');

1 row created.

SQL> insert into library values(01,'Anushka','20-september-2022','SPOS','I');

1 row created.

SQL> select * from library;

ROLL_NO NAME DATE_OF_I NAME_OF_BOOK STATUS

---------- ------------------------- --------- -------------------- ----------
27 Vaibhavi 09-SEP-22 IOT I
37 Mohana 01-OCT-22 HCI I
42 Aditi 10-OCT-22 CNS I
1 Anushka 20-SEP-22 SPOS I

SQL> select * from audit;

no rows selected

SQL> desc audit;

Name Null? Type
----------------------------------------- -------- ----------------------------
ROLL_NO NUMBER(2)
NAME VARCHAR2(25)
DATE_OF_ISSUE DATE
NAME_OF_BOOK VARCHAR2(20)
STATUS VARCHAR2(10)

SQL> update library3

set name_of_book='DBMS'
where roll_no=37;

1 row updated.

SQL> select * from library;

ROLL_NO NAME DATE_OF_I NAME_OF_BOOK STATUS
---------- ------------------------- --------- -------------------- ----------
27 Vaibhavi 09-SEP-22 IOT I
37 Mohana 01-OCT-22 DBMS I
42 Aditi 10-OCT-22 CNS I
1 Anushka 20-SEP-22 SPOS I

SQL> select * from audit;

ROLL_NO NAME DATE_OF_I NAME_OF_BOOK STATUS

---------- ------------------------- --------- -------------------- ----------
37 Mohana 01-OCT-22 HCI I
Assignment 10

1.Creating database:
test> use mystudents
switched to db mystudents

2.Creating collection:
mystudents> db.createCollection("student")
{ ok: 1 }

3.Insert:
mystudents> db.student.insertOne({
... name:"Vaibhavi",
... roll_no:1,
... Year:"Third",
... sports:"Badminton",
... marks:80})
{
acknowledged: true,
insertedId: ObjectId("6370e5ea13ac0618388dce78")
}
mystudents> db.student.insertMany([{
... name:"Mohana",
... roll_no:8,
... Year:"Second",
... sports:"Badminton",
... marks:55
... },
... {
... name:"Advait",
... roll_no:2,
... Year:"First",
... sports:"Football",
... marks:74
... },
... {
... name:"Prasad",
... roll_no:3,
... Year:"Second",
... sports:"Cricket",
... marks:50
... },
... {
... name:"Aditi",
... roll_no:4,
... Year:"Third",
... sports:"Badminton",
... marks:30
... },
... {
... name:"Anushka",
... roll_no:5,
... Year:"First",
... sports:"Football",
... marks:90
... },
... {
... name:"Safal",
... roll_no:6,
... Year:"First",
... sports:"Cricket",
... marks:63
... },
... {
... name:"Anvesh",
... roll_no:7,
... Year:"Third",
... sports:"Badminton",
... marks:32
... }])
{
acknowledged: true,
insertedIds: {
'0': ObjectId("6370e60013ac0618388dce79"),
'1': ObjectId("6370e60013ac0618388dce7a"),
'2': ObjectId("6370e60013ac0618388dce7b"),
'3': ObjectId("6370e60013ac0618388dce7c"),
'4': ObjectId("6370e60013ac0618388dce7d"),
'5': ObjectId("6370e60013ac0618388dce7e"),
'6': ObjectId("6370e60013ac0618388dce7f")
}
}
mystudents> db.student.find({Year:"Third"})
[
{
_id: ObjectId("6370e5ea13ac0618388dce78"),
name: 'Vaibhavi',
roll_no: 1,
Year: 'Third',
sports: 'Badminton',
marks: 80
},
{
_id: ObjectId("6370e60013ac0618388dce7c"),
name: 'Aditi',
roll_no: 4,
Year: 'Third',
sports: 'Badminton',
marks: 30
},
{
_id: ObjectId("6370e60013ac0618388dce7f"),
name: 'Anvesh',
roll_no: 7,
Year: 'Third',
sports: 'Badminton',
marks: 32
}
]

4.Display records who play football:

mystudents> db.student.find({sports:"Football"})
[
{
_id: ObjectId("6370e60013ac0618388dce7a"),
name: 'Advait',
roll_no: 2,
Year: 'First',
sports: 'Football',
marks: 74
},
{
_id: ObjectId("6370e60013ac0618388dce7d"),
name: 'Anushka',
roll_no: 5,
Year: 'First',
sports: 'Football',
marks: 90
}
]

5.Updating records:
mystudents> db.student.updateOne({roll_no:2},{$set:{marks:70}})
{
acknowledged: true,
insertedId: null,
matchedCount: 1,
modifiedCount: 1,
upsertedCount: 0
}
mystudents> db.student.find({roll_no:2})
[
{
_id: ObjectId("6370e60013ac0618388dce7a"),
name: 'Advait',
roll_no: 2,
Year: 'First',
sports: 'Football',
marks: 70
}
]

6.Delete records:
mystudents> db.student.deleteOne({roll_no:8})
{ acknowledged: true, deletedCount: 1 }
Assignment 11

Creating database:
test> use mystudents
switched to db mystudents

Indexes:
1.Creating Index:
mystudents> db.student.createIndex({name:1},{name:"Student's names"})
Student's names

2.Find Index:
mystudents> db.student.getIndexes()
[
{ v: 2, key: { _id: 1 }, name: '_id_' },
{ v: 2, key: { name: 1 }, name: "Student's names" }
]

3.Display Indexes:
mystudents> db.student.find()
[
{
_id: ObjectId("6370e5ea13ac0618388dce78"),
name: 'Vaibhavi',
roll_no: 1,
Year: 'Third',
sports: 'Badminton',
marks: 80
},
{
_id: ObjectId("6370e60013ac0618388dce7a"),
name: 'Advait',
roll_no: 2,
Year: 'First',
sports: 'Football',
marks: 70
},
{
_id: ObjectId("6370e60013ac0618388dce7b"),
name: 'Prasad',
roll_no: 3,
Year: 'Second',
sports: 'Cricket',
marks: 50
},
{
_id: ObjectId("6370e60013ac0618388dce7c"),
name: 'Aditi',
roll_no: 4,
Year: 'Third',
sports: 'Badminton',
marks: 30
},
{
_id: ObjectId("6370e60013ac0618388dce7d"),
name: 'Anushka',
roll_no: 5,
Year: 'First',
sports: 'Football',
marks: 90
},
{
_id: ObjectId("6370e60013ac0618388dce7e"),
name: 'Safal',
roll_no: 6,
Year: 'First',
sports: 'Cricket',
marks: 63
},
{
_id: ObjectId("6370e60013ac0618388dce7f"),
name: 'Anvesh',
roll_no: 7,
Year: 'Third',
sports: 'Badminton',
marks: 32
}
]

4.Drop Index:
mystudents> db.student.dropIndex("Student's names")
{ nIndexesWas: 2, ok: 1 }

Aggregation:
1. $match:
mystudents>db.student.aggregate([{$match:{sports:{$in:["Football","Cricket"]
}}}])
[
{
_id: ObjectId("6370e60013ac0618388dce7a"),
name: 'Advait',
roll_no: 2,
Year: 'First',
sports: 'Football',
marks: 70
},
{
_id: ObjectId("6370e60013ac0618388dce7b"),
name: 'Prasad',
roll_no: 3,
Year: 'Second',
sports: 'Cricket',
marks: 50
},
{
_id: ObjectId("6370e60013ac0618388dce7d"),
name: 'Anushka',
roll_no: 5,
Year: 'First',
sports: 'Football',
marks: 90
},
{
_id: ObjectId("6370e60013ac0618388dce7e"),
name: 'Safal',
roll_no: 6,
Year: 'First',
sports: 'Cricket',
marks: 63
}
]

2. $sort:
mystudents> db.student.aggregate({$sort:{marks:-1}})
[
{
_id: ObjectId("6370e60013ac0618388dce7d"),
name: 'Anushka',
roll_no: 5,
Year: 'First',
sports: 'Football',
marks: 90
},
{
_id: ObjectId("6370e5ea13ac0618388dce78"),
name: 'Vaibhavi',
roll_no: 1,
Year: 'Third',
sports: 'Badminton',
marks: 80
},
{
_id: ObjectId("6370e60013ac0618388dce7a"),
name: 'Advait',
roll_no: 2,
Year: 'First',
sports: 'Football',
marks: 70
},
{
_id: ObjectId("6370e60013ac0618388dce7e"),
name: 'Safal',
roll_no: 6,
Year: 'First',
sports: 'Cricket',
marks: 63
},
{
_id: ObjectId("6370e60013ac0618388dce7b"),
name: 'Prasad',
roll_no: 3,
Year: 'Second',
sports: 'Cricket',
marks: 50
},
{
_id: ObjectId("6370e60013ac0618388dce7f"),
name: 'Anvesh',
roll_no: 7,
Year: 'Third',
sports: 'Badminton',
marks: 32
},
{
_id: ObjectId("6370e60013ac0618388dce7c"),
name: 'Aditi',
roll_no: 4,
Year: 'Third',
sports: 'Badminton',
marks: 30
}
]

3. $count:
mystudents>db.student.aggregate([{$match:{sports:{$in:["Football","Cricket"]
}}},{$count:"totalCount"}])
[ { totalCount: 4 } ]
Assignment 12

Create database:
test> use mystudents
switched to db mystudents

Create collection:
mystudents> db.createCollection("student_info")
{ ok: 1 }

mystudents> db.student_info.insertOne({
... name:"Vaibhavi",
... roll_no:27,
... Year:"Second",
... sports:"Badminton",
... marks:65})
{
acknowledged: true,
insertedId: ObjectId("63710ac823940cd204dbd0ac")
}

mystudents> db.student_info.insertOne({
... name:"Anushka",
... roll_no:01,
... Year:"Third",
... sports:"Hockey",
... marks:80})
{
acknowledged: true,
insertedId: ObjectId("63710ad223940cd204dbd0ad")
}

mystudents> db.student_info.insertOne({
... name:"Aditi",
... roll_no:42,
... Year:"Third",
... sports:"Cricket",
... marks:70})
{
acknowledged: true,
insertedId: ObjectId("63710ada23940cd204dbd0ae")
}

mystudents> db.student_info.insertOne({
... name:"Mohana",
... roll_no:37,
... Year:"Second",
... sports:"Football",
... marks:97})
{
acknowledged: true,
insertedId: ObjectId("63710ae223940cd204dbd0af")
}
MapReduce function:
mystudents> var map=function(){emit(this.Year,this.marks);};

mystudents> var reduce=function(Year,marks){return Array.sum(marks);};

mystudents> db.student_info.mapReduce(map,reduce,{out:"result"});
{ result: 'result', ok: 1 }

mystudents> db.result.find()
[
{ _id: 'Second', value: 227 },
{ _id: 'Third', value: 150 }
]