OUTPUT:

mysql> use Suraj;

Reading table information for completion of table and column

names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> create table o_rollcall(roll_no int,name varchar(20),address

varchar(20));

Query OK, 0 rows affected (0.28 sec)

mysql> create table n_rollcall(roll_no int,namevarchar(20),address

varchar(20));

Query OK, 0 rows affected (0.27 sec)

mysql> insert into o_rollcall values('1','Ani','Nandura'); Query OK, 1 rowaffected

(0.05 sec)

mysql> insert into o_rollcall values('2','Sam','MP');

Query OK, 1 row affected (0.06 sec)

mysql> insert into o_rollcall values('3','Siddhi','Nsk');

Query OK, 1 row affected (0.05 sec)

mysql> insert into o_rollcall values('4','Tejas','Dhule');

Query OK, 1 row affected (0.05 sec)

mysql> insert into o_rollcall values('5','Suraj','Satara');

Query OK, 1 row affected (0.04 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_rollcallwhere
roll_no>r1;
-> declare continue handler for not found setexit_loop=true;
-> open c1;
-> e_loop:loop
-> fetch c1 into r2;
-> if not exists(select * from n_rollcall whereroll_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.00 sec)

mysql> call p3(3);

-> //
Query OK, 0 rows affected (0.10 sec)

mysql> select * from n_rollcall;

-> //
+ + + +

| roll_no | name
| address |
+ + + +
| 4 | Tejas | Dhule |
| 5 | Suraj |
| Satara
+ + + +
2 rows in set (0.00 sec)

mysql> call p3(0);

-> //

Query OK, 0 rows affected (0.22 sec)

mysql> select * from n_rollcall;

-> //
+ + + +
| roll_no | name
| address |
+ + + +
| 4 | Tejas | Dhule |
| 5 | Suraj | Satara |
| 1 | Ani | Nandura |
| 2 | Sam | MP |
| 3 | Siddhi | Nsk |+---------+---------+ --------- +
5 rows in set (0.00 sec)

mysql> insert into o_rollcall values('6','Patel','Kolhapur');

-> //

Query OK, 1 row affected (0.04 sec)

mysql> call p3(4);

-> //

Query OK, 0 rows affected (0.05 sec)

mysql> select * from n_rollcall;

-> //
+ + + +
| roll_no | name
| address
|
+ + + +
| 4 | Tejas | Dhule |
| 5 | Suraj | Satara |
| 1 | Ani | Nandura |
| 2 | Sam | MP |
| 3 | Siddhi | Nsk |
| 6 | Patel | Kolhapur |
+ + + +
6 rows in set (0.00 sec)
mysql>
OUTPUT:
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','Samarth','1400');

Query OK, 1 row affected (0.01 sec)

mysql> insert into marks values('2','Suraj','980');

Query OK, 1 row affected (0.01 sec)

mysql> insert into marks values('3','Sam','880');

Query OK, 1 row affected (0.01 sec)

mysql> insert into marks values('4','Siddhi','820');

Query OK, 1 row affected (0.01 sec)

mysql> insert into marks values('5','Ani','740');

Query OK, 1 row affected (0.01 sec)

mysql> insert into marks values('6','patil','640');

Query OK, 1 row affected (0.01 sec)

mysql> delimiter //
mysql> create procedure proc_result(in marks int,out class
-> char(20))
-> begin
-> if(marks<1500&&marks>990)
-> then
-> set class='Distincton';
-> 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='Passed';
-> end if;
-> if(marks<649)
-> then
-> set class='Fail';
-> end if;
-> end;
 -> //
Query OK, 0 rows affected, 5 warnings (0.01 sec)
mysql> create function final_result4(R1 int)
-> returns int
-> READS SQL DATA
-> DETERMINISTIC
-> 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_result4(2);

-> //
+------------------+
| final_result4(2) |
+------------------+
| 2|
+------------------+
1 row in set (0.01 sec)

mysql> select final_result4(3);//

+------------------+
| final_result4(3) |
+------------------+
| 3|
+------------------+
1 row in set (0.01 sec)

mysql> select final_result4(4);//

+------------------+
| final_result4(4) |
+------------------+
| 4|
+------------------+
1 row in set (0.01 sec)

mysql> select final_result4(5);//

+------------------+
| final_result4(5) |
+------------------+
| 5|
+------------------+
1 row in set (0.00 sec)
mysql> select * from result;
-> //
+---------+--------+---------------------+
| roll_no | name | class |
+---------+--------+---------------------+
| 2 | Suraj | First Class |
| 3 | Sam | Higher Second Class |
| 4 | Siddhi | Second Class |
| 5 | Ani | Passed |
+---------+--------+---------------------+
4 rows in set (0.00 sec)
OUTPUT:

mysql> use Suraj;

Database changed

mysql> show tables;

Empty set (0.02 sec)

mysql> create table client_master(client_no int,client_name varchar(20),address

varchar(50),city varchar(10),pincode int,state varchar(20), bal_due float,primary
key(client_no));

Query OK, 0 rows affected (0.51 sec)

mysql> select * from client_master;

Empty set (0.02 sec)

mysql> insert into client_master

values('001','Suraj','nasik','nasik','422004','MH','5000');

Query OK, 1 row affected (0.14 sec)

mysql> insert into client_master values('002','Sam','nasik','nasik','422004','MH','10000');

Query OK, 1 row affected (0.09 sec)

mysql> insert into client_master values('003','abd','nasik','nasik','422003','MH','5000');

Query OK, 1 row affected (0.06 sec)

mysql> insert into client_master values('004','abd','nasik','nasik','422003','MH','5000');

Query OK, 1 row affected (0.05 sec)

mysql> insert into client_master values('005','abc','nasik','nasik','422003','MH','5000');

Query OK, 1 row affected (0.06 sec)

mysql> select * from client_master;
+-----------+-------------+---------+-------+---------+-------
+---------+
| client_no | client_name | address | city
| bal_due |
| pincode | state
+-----------+-------------+---------+-------+---------+-------
+---------+
|
| 1 | Suraj
5000 | | nasik | nasik | 422004 | MH
|
| 2 | Sam
10000 | | nasik | nasik | 422004 | MH
|
| 3 | abd
5000 | | nasik | nasik | 422003 | MH
|
| 4 | abd
5000 | | nasik | nasik | 422003 | MH
|
| 5 | abc
5000 | | nasik | nasik | 422003 | MH
+-----------+-------------+---------+-------+---------+-------
+---------+
5 rows in set (0.00 sec)

mysql> select client_name,client_no from client_master;

+-------------+-----------+
| client_name | client_no |
+-------------+-----------+
| Suraj | 1 |
| Sam | 2 |
| abd | 3 |
| abd | 4 |
| abc | 5 |+-------------+ ----------+

5 rows in set (0.00 sec)

mysql> insert into client_master values('006','xyz','nasik','nasik','422004','MH','6000');

Query OK, 1 row affected (0.15 sec)

mysql> select client_name,client_no from client_master;

+-------------+-----------+
| client_name | client_no |
+-------------+-----------+
| Suraj | 1 |
| Sam | 2 |
| abd | 3 |
| abd | 4 |
| abc | 5 |
| xyz | 6 |

6 rows in set (0.08 sec)

mysql> create table product_master(product_no int,description varchar(20),profit_per

float,unit_measure varchar(10),quantity int,reorder int,sell_price float,cost_price float,primary
key(product_no));

Query OK, 0 rows affected (0.77 sec)

mysql> insert into product_master values('001','shampoo','1','one','4','2','10','15');

Query OK, 1 row affected (0.17 sec)

mysql> insert into product_master values('002','oil','13','one','4','2','11','16');

Query OK, 1 row affected (0.06 sec)

mysql> alter table client_master add telephone_no int;

Query OK, 0 rows affected (1.04 sec)

Records: 0
Duplicates: 0
Warnings: 0

mysql> select * from client_master;

+-----------+-------------+---------+-------+---------+-------
+---------+--------------+
| client_no | client_name | address | city
| bal_due | telephone_no |
| pincode | state
+-----------+-------------+---------+-------+---------+-------
+---------+--------------+
|
|1|
Suraj
5000 |
|
| 2 | Sam
10000 |
|
| 3 | abd
5000 |
|
| 4 | abd
5000 |
|
| 5 | abc
5000 |
|
| 6 | xyz
6000 |
| nasik
| nasik | 422004 | MH
| nasik | 422004 | MH
| nasik | 422003 | MH
| nasik | 422003 | MH
| nasik | 422003 | MH
| nasik | 422004 | MH
NULL |
| nasik
NULL |
| nasik
NULL |
| nasik
NULL |
| nasik
NULL |
| nasik
NULL |
+-----------+-------------+---------+-------+---------+-------
+---------+--------------+
6 rows in set (0.00 sec)

mysql> select * from product_master;

+------------+-------------+------------+--------------
+----------+---------+------------+------------+
| product_no | description | profit_per | unit_measure |
quantity | reorder | sell_price | cost_price |
+------------+-------------+------------+--------------
+----------+---------+------------+------------+
|
4|
1 | shampoo
|
2|
10 |
1 | one
15 |
||
4|
2 | oil
2|
|
11 |
13 | one
16 |
|
+------------+-------------+------------+--------------
+----------+---------+------------+------------+
2 rows in set (0.00 sec)

mysql> create index client_search on client_master(client_no);

Query OK, 0 rows affected (0.42 sec)

Records: 0
Duplicates: 0
Warnings: 0

mysql> create table auto(roll_no int NOT NULL AUTO_INCREMENT,name

varchar(20),primary key(roll_no));

Query OK, 0 rows affected (0.36 sec)

mysql> select * from auto;

Empty set (0.01 sec)

mysql> insert into auto values('1','abc');

Query OK, 1 row affected (0.07 sec)

mysql> insert into auto values('2','adc');

Query OK, 1 row affected (0.08 sec)

mysql> alter table auto auto_increment=100;

Query OK, 0 rows affected (0.07 sec)

Records: 0
Duplicates: 0

mysql> select * from auto;

+---------+------+
| roll_no | name |
+---------+------+
| 1 | abc |
| 2 | adc |

Warnings: 0+---------+ ----- +

2 rows in set (0.00 sec)

mysql> insert into auto values(null,'abd');

Query OK, 1 row affected (0.05 sec)

mysql> select * from auto;

+---------+------+
| roll_no | name |
+---------+------+
| 1 | abc |
| 2 | adc |
| 100 | abd |
+---------+------+
3 rows in set (0.00 sec)

mysql> insert into auto values(null,'reh');

Query OK, 1 row affected (0.06 sec)

mysql> select * from auto;

+---------+------+
| roll_no | name |
+---------+------+
| 1 | abc |
| 2 | adc |
| 100 | abd |
| 101 | reh |
+---------+------+
4 rows in set (0.00 sec)

mysql> update client_master set client_name="nut" where client_no='4';

Query OK, 1 row affected (0.09 sec)

Rows matched: 1
Changed: 1
Warnings: 0

mysql> select * from client_master;

+-----------+-------------+---------+-------+---------+-------
+---------+--------------+
| client_no | client_name | address | city
| bal_due | telephone_no |
| pincode | state
+-----------+-------------+---------+-------+---------+-------
+---------+--------------+
|
| 1 | Suraj
5000 | NULL |
| nasik
|
| 2 | Sam
10000 | NULL |
|
| 3 | abd
5000 | NULL |
|
| 4 | nut
5000 | NULL |
|
| 5 | abc
5000 | NULL |
|
| 6 | xyz
6000 | NULL |
| nasik
| nasik
| nasik
| nasik
| nasik
| nasik | 422004 | MH
| nasik | 422004 | MH
| nasik | 422003 | MH
| nasik | 422003 | MH
| nasik | 422003 | MH
| nasik | 422004 | MH
+-----------+-------------+---------+-------+---------+-------
+---------+--------------+
6 rows in set (0.00 sec)

mysql> create index client_find on client_master(client_name,city);

affected (0.41 sec)

Records: 0
Duplicates: 0

mysql> show tables;

+----------------+
| Tables_in_Suraj |
+----------------+
| auto
|
Warnings: 0
Query OK, 0 rows| client_master
|
| product_master |
+----------------+
3 rows in set (0.08 sec)

mysql> select * from product_master;

+------------+-------------+------------+--------------
+----------+---------+------------+------------+
| product_no | description | profit_per | unit_measure |
quantity | reorder | sell_price | cost_price |
+------------+-------------+------------+--------------
+----------+---------+------------+------------+
|
4 | 1 | shampoo
|
2|
10 | 1 | one
15 | |
|
4 | 2 | oil
2 | 13 | one
16 | |
|
11 |
+------------+-------------+------------+--------------
+----------+---------+------------+------------+
2 rows in set (0.00 sec)

mysql> desc product_master;

+--------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| product_no | int(11)
| description | profit_per
| NO
| PRI | NULL | |
| varchar(20) | YES | | NULL | |
| float | YES | | NULL | |
| unit_measure | varchar(10) | YES | | NULL | |
| quantity | int(11) | YES | | NULL | |
| reorder | int(11) | YES | | NULL | |
| sell_price | float | YES | | NULL | |
| cost_price | float | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
8 rows in set (0.05 sec)

mysql> alter table client_master rename to c_master;

Query OK, 0 rows affected (0.25 sec)

mysql> insert into product_master values('003','nutela','15','three','40','5','110','123');

Query OK, 1 row affected (0.05 sec)

mysql> alter table product_master modify sell_price float(10,2);

Query OK, 0 rows affected (0.06 sec)

Records: 0
Duplicates: 0
Warnings: 0
mysql> desc product_master;

+--------------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| product_no | int(11)
| description | profit_per
| NO
| PRI | NULL | |
| varchar(20) | YES | | NULL | |
| float | YES | | NULL | |
| unit_measure | varchar(10) | YES | | NULL | |
| quantity | int(11) | YES | | NULL | |
| reorder | int(11) | YES | | NULL | |
| sell_price | float(10,2) | YES | | NULL | |
| cost_price | float | | NULL | |
| YES
+--------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> create view client as select client_no,client_name from c_master;

Query OK, 0 rows affected (0.05 sec)

mysql> select * from client;

+-----------+ ----------- +| client_no | client_name |

+-----------+-------------+
| 5 | abc |
| 3 | abd |
| 1 | Suraj |
| 4 | nut |
| 2 | Sam |
| 6 | xyz |
+-----------+-------------+
6 rows in set (0.23 sec)

mysql>
OUTPUT:

mysql> show databases;

+--------------------+
| Database
|
+--------------------+
| information_schema |
|A|
| Suraj |
| DPCOE |
| RITEN |
| mysql |
| Harsh |
| performance_schema |
| Riten |
| sys |
| time |
+--------------------+
11 rows in set (0.11 sec)

mysql> use Suraj;

Database changed

mysql> create table Employee(emp_no int,emp_name varchar(20),date date,position

varchar(20));

Query OK, 0 rows affected (0.75 sec)

mysql> alter table Employee add salary int;

Query OK, 0 rows affected (0.68 sec)

Records: 0
Duplicates: 0
Warnings: 0

mysql> insert into Employee values('01','abc','2018-07-11','clerk','50000');

Query OK, 1 row affected (0.08 sec)

mysql> insert into Employee values('02','Suraj','2018-05-11','ceo','150000');

Query OK, 1 row affected (0.08 sec)

mysql> insert into Employee values('03','xyz','2018-05-21','hr','100000');

Query OK, 1 row affected (0.04 sec)

mysql> insert into Employee values('04','aqwgy','2018-06-21','te','10000');

Query OK, 1 row affected (0.03 sec)

mysql> insert into Employee values('05','sfhjfh','2018-07-21','gt','12000');

Query OK, 1 row affected (0.03 sec)

mysql> create table TE(emp_no int,emp_namevarchar(20),join_date date,position

varchar(20),salary int);

Query OK, 0 rows affected (0.36 sec)

mysql> insert into TE values('01','abc','2018-07-11','clerk','50000');

Query OK, 1 row affected (0.03 sec)

mysql> insert into TE values('02','Suraj','2018-05-11','ceo','150000');

Query OK, 1 row affected (0.04 sec)

mysql> insert into TE values('03','xyz','2018-05-21','hr','100000');

Query OK, 1 row affected (0.04 sec)

mysql> insert into TE values('04','aqwgy','2018-06-21','te','10000');

Query OK, 1 row affected (0.05 sec)

mysql> insert into TE values('05','sfhjfh','2018-07-21','gt','12000');

Query OK, 1 row affected (0.04 sec)

mysql> select * from TE;

+--------+----------+------------+----------+--------+
| emp_no | emp_name | join_date
| position | salary |
+--------+----------+------------+----------+--------+
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | sfhjfh | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
5 rows in set (0.04 sec)

mysql> select * from Employee;

+--------+----------+------------+----------+--------+
| emp_no | emp_name | date
| position | salary |
+--------+----------+------------+----------+--------+
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | sfhjfh | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
5 rows in set (0.00 sec)

mysql> update TE set emp_name='gjgj' where emp_no='5';

Query OK, 1 row affected (0.13 sec)

Rows matched: 1
Changed: 1
Warnings: 0

mysql> select * from TE;

+--------+----------+------------+----------+--------+
| emp_no | emp_name | join_date
| position | salary |
+--------+----------+------------+----------+--------+
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | gjgj | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
5 rows in set (0.00 sec)

mysql> select * from Employee union select * from TE;

+--------+----------+------------+----------+--------+
| emp_no | emp_name | date
| position | salary |
+--------+----------+------------+----------+--------+
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | sfhjfh | 2018-07-21 | gt |
12000 |
| 5 | gjgj | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
6 rows in set (0.01 sec)

mysql> select * from Employee union all select * from TE;

+--------+----------+------------+----------+ ------- +| emp_no | emp_name | date

| position | salary |
+--------+----------+------------+----------+--------+
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | sfhjfh | 2018-07-21 | gt |
12000 |
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | gjgj | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
10 rows in set (0.00 sec)

mysql> select distinct emp_no from Employee where emp_no in(select emp_no from TE);
+--------+
| emp_no |
+--------+
|1|
|2|
|3|
|4|
|5|
+--------+
5 rows in set (0.03 sec)

mysql> select * from Employee;

+--------+----------+------------+----------+--------+
| emp_no | emp_name | date
| position | salary |
+--------+----------+------------+----------+ ------- +| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | sfhjfh | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
5 rows in set (0.00 sec)

mysql> select * from TE;

+--------+----------+------------+----------+--------+
| emp_no | emp_name | join_date
| position | salary |
+--------+----------+------------+----------+--------+
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | gjgj | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
5 rows in set (0.00 sec)

mysql> select distinct emp_name from Employee where emp_name in(select emp_name fromTE);
+----------+
| emp_name |
+----------+
| abc |
| Suraj |
| xyz |
| aqwgy |
+----------+
4 rows in set (0.00 sec)

mysql> select * from Employee;

+--------+----------+------------+----------+--------+
| emp_no | emp_name | date
| position | salary |
+--------+----------+------------+----------+--------+
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | sfhjfh | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
5 rows in set (0.00 sec)

mysql> select * from TE;

+--------+----------+------------+----------+--------+
| emp_no | emp_name | join_date
| position | salary |
+--------+----------+------------+----------+--------+
| 1 | abc | 2018-07-11 | clerk |
50000 |
| 2 | Suraj | 2018-05-11 | ceo | 150000 |
| 3 | xyz | 2018-05-21 | hr | 100000 |
| 4 | aqwgy | 2018-06-21 | te |
10000 |
| 5 | gjgj | 2018-07-21 | gt |
12000 |
+--------+----------+------------+----------+--------+
5 rows in set (0.00 sec)

mysql> select distinct emp_name from Employee where emp_name in(select emp_name fromTE);

+----------+
| emp_name |
+----------+
| abc |
| Suraj |
| xyz || aqwgy
|
+----------+
4 rows in set (0.00 sec)

mysql> select min(salary) from Employee;

+-------------+
| min(salary) |
+-------------+
|
10000 |
+-------------+
1 row in set (0.04 sec)

mysql> select max(salary) from Employee;

+-------------+
| max(salary) |
+-------------+
|
150000 |
+-------------+
1 row in set (0.00 sec)

mysql> select sum(salary) from Employee;

+-------------+
| sum(salary) |
+-------------+
|
322000 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(salary) from Employee;

+-------------+
| avg(salary) |
+-------------+|
64400.0000 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(salary) from Employee;
+---------------+
| count(salary) |
+---------------+
|
5|
+---------------+
1 row in set (0.00 sec)
OUTPUT:

mysql> show databases;

+ +
| Database
|
+ +
| information_schema |
|A|
| Suraj |
| COMPUTER |
|H|
| DPCOE |
| RUTU |
| mysql |
| Sam |
| Saml |
| performance_schema |
| Rutu |
| sys |
| time |
+ +
14 rows in set (0.21 sec)

mysql> use Suraj;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -ADatabase changed

mysql> show tables;

+ +
| Tables_in_Suraj |
+ +
| Employee |
| TE |
| auto |
| c_master |
| product_master |
+ +
5 rows in set (0.00 sec)

mysql> create table _master(product_no int,description varchar(20),profit_per

float,unit_measure varchar(10),quantity int,reorder int,sell_price float,cost_price
float,primary key(product_no));

Query OK, 0 rows affected (0.55 sec)

mysql> create table customer(cust_no int,cust_name varchar(20),cust_add

varchar(20),phone_no int,primary key(cust_no));

Query OK, 0 rows affected (0.28 sec)

mysql> create table capital(cap_no int,cap_name varchar(20),state_no int,primary

key(cap_no));

Query OK, 0 rows affected (0.27 sec)

mysql> create table state(state_no int,state_name varchar(20),state_code int,capital

varchar(20),primary key(state_no));

Query OK, 0 rows affected (0.28 sec)

mysql> insert into capital values('01','MH','01');

Query OK, 1 row affected (0.12 sec)

mysql> insert into capital values('02','RAJ','02');

Query OK, 1 row affected (0.04 sec)

mysql> insert into capital values('03','GOA','03');

Query OK, 1 row affected (0.05 sec)

mysql> insert into capital values('04','GUJ','04');

Query OK, 1 row affected (0.05 sec)

mysql> insert into capital values('05','KAR','05'); Query

OK, 1 row affected (0.04 sec)

mysql> insert into state values('01','MH','01','MUM');

Query OK, 1 row affected (0.03 sec)

mysql> insert into state values('02','RAJ','02','JAI');

Query OK, 1 row affected (0.03 sec)

mysql> insert into state values('03','GOA','03','PAN');

Query OK, 1 row affected (0.04 sec)

mysql> insert into state values('04','GUJ','04','SUR');

Query OK, 1 row affected (0.04 sec)

mysql> insert into state values('05','KAR','05','BAN');

Query OK, 1 row affected (0.03 sec)

mysql> select * from capital;

+ + + +
| cap_no | cap_name | state_no |
+ + + +| 1 | MH | 1 |
| 2 | RAJ | 2 |
| 3 | GOA | 3 |
| 4 | GUJ | 4 |
| 5 | KAR | 5 |
+ + + +
5 rows in set (0.01 sec)

mysql> select * from state;

+ + + + +
| state_no | state_name | state_code | capital |
+ + + + +
| 1 | MH | 1 | MUM |
| 2 | RAJ | 2 | JAI |
| 3 | GOA | 3 | PAN |
| 4 | GUJ | 4 | SUR |
| 5 | KAR | 5 | BAN |
+ + + + +
5 rows in set (0.00 sec)

mysql> select capital.cap_no, state.state_no from capital inner join state on

capital.cap_no=state.state_no;
+ + +
| cap_no | state_no |
+ + +
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
+ + +
5 rows in set (0.06 sec)

mysql> UPDATE state SET state_no="78" where state_no='1';

Query OK, 1 row affected (0.04 sec)

Rows matched: 1
Changed: 1
Warnings: 0

mysql> UPDATE state SET state_no="58" where state_no='2';

Query OK, 1 row affected (0.04 sec)

Rows matched: 1
Changed: 1
Warnings: 0

mysql> UPDATE state SET state_no="46" where state_no='3';

Query OK, 1 row affected (0.03 sec)

Rows matched: 1
Changed: 1
Warnings: 0

mysql> UPDATE state SET state_no="489" where state_no='4';

Query OK, 1 row affected (0.05 sec)

Rows matched: 1
Changed: 1
Warnings: 0

mysql> UPDATE state SET state_no="458" where state_no='5';

Query OK, 1 row affected (0.03 sec)
Rows matched: 1
Changed: 1
Warnings: 0

mysql> insert into state values('05','MP','05','BHO');

Query OK, 1 row affected (0.03 sec)

mysql> select capital.cap_no, state.state_no from capital inner join state on

capital.cap_no=state.state_no;
+ + +
| cap_no | state_no |
+ + +
|
5|
5|
+ + +
1 row in set (0.00 sec)
mysql> select capital.cap_no, state.state_no from capital left join state on
capital.cap_no=state.state_no;
+ + +
| cap_no | state_no |
+ + +
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
|5|5|
+ + +
5 rows in set (0.00 sec)
mysql> select capital.cap_no, state.state_no from capital left join state on
capital.cap_no=state.state_name;
+ + +
| cap_no | state_no |
+ + +
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+ + +
5 rows in set, 20 warnings (0.00 sec)
mysql> select capital.cap_no, state.state_no from capital right join state on
capital.cap_no=state.state_no;

+ + +
| cap_no | state_no |
+ + +
|
5|
5 || NULL | 46 |
| NULL | 58 |
| NULL | 78 |
| NULL | 458 |
| NULL | 489 |
+ + +
6 rows in set (0.00 sec)
mysql> select * from capital;
+ + + +
| cap_no | cap_name | state_no |
+ + + +
| 1 | MH | 1 |
| 2 | RAJ | 2 |
| 3 | GOA | 3 |
| 4 | GUJ | 4 |
| 5 | KAR | 5 |
+ + + +
5 rows in set (0.00 sec)
mysql> select * from state;
+ + + + +
| state_no | state_name | state_code | capital |
+ + + + +
| 5 | MP | 5 | BHO |
| 46 | GOA | 3 | PAN |
| 58 | RAJ | 2 | JAI |
| 78 | MH | 1 | MUM |
| 458 | KAR | 5 | BAN |
| 489 | GUJ | 4 | SUR |
+ + + + +
6 rows in set (0.00 sec)
mysql> select * from capital;
+ + + +
| cap_no | cap_name | state_no |
+ + + +
| 1 | MH | 1 |
| 2 | RAJ | 2 |
| 3 | GOA | 3 |
| 4 | GUJ | 4 |
| 5 | KAR | 5 |
+ + + +
5 rows in set (0.00 sec)
mysql> select capital.cap_no, state.state_no from capital inner join state on
capital.cap_no=state.state_no;
+ + +
| cap_no | state_no |
+ + +
|
5|
5|
+ + +
1 row in set (0.00 sec)

mysql> select capital.cap_no,capital.cap_name,state.capital,state.state_no from

capital inner join state on capital.cap_no=state.state_no;
+ + + + +
| cap_no | cap_name | capital | state_no |
+ + + + +
|
5 | KAR
| BHO
|
5|
+ + + + +
1 row in set (0.00 sec)
mysql> select capital.cap_no,capital.cap_name,state.capital,state.state_no from
capital left join state on capital.cap_no=state.state_no;
+ + + + +
| cap_no | cap_name | capital | state_no |
+ + + + +
| 1 | MH | NULL | NULL |
| 2 | RAJ | NULL | NULL |
| 3 | GOA | NULL | NULL |
| 4 | GUJ | NULL | NULL |
| 5 | KAR | BHO | 5 |
+ + + + +
5 rows in set (0.00 sec)
mysql> select capital.cap_no,capital.cap_name,state.capital,state.state_no from
capital right join state on capital.cap_no=state.state_no;
+ + + + +
| cap_no | cap_name | capital | state_no |
+ + + + +
|
5 | KAR
| BHO | 5 |
| NULL | NULL | PAN | 46 |
| NULL | NULL | JAI | 58 |
| NULL | NULL | MUM | 78 |
| NULL | NULL | BAN | 458 |
| NULL | NULL | SUR | 489 |
+ + + + +
6 rows in set (0.00 sec)

mysql> select capital.cap_no,capital.cap_name,state.capital,state.state_no from capital

left join state on capital.cap_no=state.state_no union
selectcapital.cap_no,capital.cap_name,state.capital,state.state_no from capital right
join state on capital.cap_no=state.state_no;
+ + + + +
| cap_no | cap_name | capital | state_no |
+ + + + +
| 1 | MH | NULL | NULL |
| 2 | RAJ | NULL | NULL |
| 3 | GOA | NULL | NULL |
| 4 | GUJ | NULL | NULL |
| 5 | KAR | BHO | 5 |
| NULL | NULL | PAN | 46 |
| NULL | NULL | JAI | 58 |
| NULL | NULL | MUM | 78 |
| NULL | NULL | BAN | 458 |
| NULL | NULL | SUR | 489 |
+ + + + +
10 rows in set (0.00 sec)

mysql> select * from capital c1, state s1 where c1.cap_no=s1.state_no;

+ + + + +
+ + +
| cap_no | cap_name | state_no | state_no | state_name |
state_code | capital |
+ + + + +
+ + +
|
5 | KAR
5 | BHO
|
|
5|
5 | MP
|
+ + + + +
+ + +
1 row in set (0.00 sec)

mysql> select * from capital c1, state s1 where c1.cap_no! =s1.state_no;

+ + + + +
+ + +
| cap_no | cap_name | state_no | state_no | state_name |
state_code | capital |
+ + + + +
+ + +
|
1 | MH
5 | BHO
| | 1 | 5 | MP |
|
2 | RAJ
5 | BHO
| | 2 | 5 | MP |
|
3 | GOA
5 | BHO
| | 3 | 5 | MP |
|
4 | GUJ
5 | BHO
| | 4 | 5 | MP |
|
1 | MH
3 | PAN
| | 1 | 46 | GOA |
|
2 | RAJ
3 | PAN
| | 2 | 46 | GOA |
|
3 | GOA
3 | PAN
| | 3 | 46 | GOA |
|
4 | GUJ
3 | PAN
| | 4 | 46 | GOA |
|
5 | KAR
3 | PAN
| | 5 | 46 | GOA |
|
1 | MH
2 | JAI
| | 1 | 58 | RAJ |
|
2 | RAJ
2 | JAI
| | 2 | 58 | RAJ |
|
3 | GOA
2 | JAI
| | 3 | 58 | RAJ |
|
4 | GUJ
2 | JAI
| | 4 | 58 | RAJ |
|
5 | KAR
2 | JAI
| | 5 | 58 | RAJ |
|
1 | MH
1 | MUM
| | 1 | 78 | MH |
|
2 | RAJ
1 | MUM
| | 2 | 78 | MH ||
3 | GOA
1 | MUM
| | 3 | 78 | MH |
|
4 | GUJ
1 | MUM
| | 4 | 78 | MH |
|
5 | KAR
1 | MUM
| | 5 | 78 | MH |
|
1 | MH
5 | BAN
| | 1 | 458 | KAR |
|
2 | RAJ
5 | BAN
| | 2 | 458 | KAR |
|
3 | GOA
5 | BAN
| | 3 | 458 | KAR |
|
4 | GUJ
5 | BAN
| | 4 | 458 | KAR |
|
5 | KAR
5 | BAN
| | 5 | 458 | KAR |
|
1 | MH
4 | SUR
| | 1 | 489 | GUJ |
|
2 | RAJ
4 | SUR
| | 2 | 489 | GUJ |
|
3 | GOA
4 | SUR
| | 3 | 489 | GUJ |
|
4 | GUJ
4 | SUR
| | 4 | 489 | GUJ |
|
5 | KAR
4 | SUR
| | 5 | 489 | GUJ |
+ + + + +
+ + +
29 rows in set (0.00 sec)

mysql> select * from state where state_no=(select state_no from state where
state_name='MH');
+ + + + +
| state_no | state_name | state_code | capital |
+ + + + +
|
78 | MH
|
1 | MUM
|
+ + + + +
1 row in set (0.06 sec)

mysql> select * from state where state_no=(select state_no from state where
state_name='GUJ');
+ + + + +
| state_no | state_name | state_code | capital |
+ + + + +
|
489 | GUJ
|
4 | SUR
|
+ + + + +
1 row in set (0.00 sec)

mysql> select * from state where state_no=(select capital.state_no from capital where
cap_name='MH');

Empty set (0.00 sec)

mysql> select * from state where state_no=(select capital.state_no from capital where
cap_name='GUJ');
Empty set (0.00 sec)
mysql> select * from state where state_no=(select capital.state_no from capital where
cap_name='RAJ');
Empty set (0.00 sec)
mysql> select * from state where state_no=(select capital.state_no from capital where
cap_name='KAR');
+ + + + +
| state_no | state_name | state_code | capital |
+ + + + +
|
5 | MP
|
5 | BHO
|
+ + + + +
1 row in set (0.00 sec)
OUTPUT:

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| abc |
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
| xyz |
+--------------------+
7 rows in set (0.00 sec)

mysql> use student;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+-------------------+
| Tables_in_student |
+-------------------+
| student |
+-------------------+
1 row in set (0.01 sec)

mysql> use student;

Database changed
mysql> create table borrower(rollno int primary key,name varchar(20),dateofissue
date,nameofbook
 -> varchar(20),status varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> create table fine(rollno int,foreign key(rollno) references

borrower(rollno),returndate date,
-> amount int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into borrower values(1,'abc','2021-06-01','SEPM','I');

Query OK, 1 row affected (0.01 sec)

mysql> insert into borrower values(2,'xyz','2021-05-01','OOP','I');

Query OK, 1 row affected (0.01 sec)

mysql> insert into borrower values(3,'pqr','2021-06-15','DBMS','I');

Query OK, 1 row affected (0.01 sec)

mysql> insert into borrower values(4,'def','2021-06-30','DSA','I');

Query OK, 1 row affected (0.01 sec)

mysql> insert into borrower values(5,'lmn','2021-07-05','ADS','I');

Query OK, 1 row affected (0.01 sec)

mysql> select * from borrower;

+--------+------+-------------+------------+--------+
| rollno | name | dateofissue | nameofbook | status |
+--------+------+-------------+------------+--------+
| 1 | abc | 2021-06-01 | SEPM |I |
| 2 | xyz | 2021-05-01 | OOP |I |
| 3 | pqr | 2021-06-15 | DBMS |I |
| 4 | def | 2021-06-30 | DSA |I |
| 5 | lmn | 2021-07-05 | ADS |I |
+--------+------+-------------+------------+--------+
5 rows in set (0.00 sec)
Program:

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 dateofissue into issuedate from borrower where rollno=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 rollno=roll;
-> end $
Query OK, 0 rows affected (0.01 sec)

mysql> call calc_fine_lib(1);

-> call calc_fine_lib(2);
-> call calc_fine_lib(3);
-> call calc_fine_lib(4);
-> call calc_fine_lib(5);
-> select * from fine;
-> call calc_fine_lib(1)$
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.04 sec)

+--------+------------+--------+
| rollno | returndate | amount |
+--------+------------+--------+
| 1 | 2023-10-12 | 41800 |
| 2 | 2023-10-12 | 43350 |
| 3 | 2023-10-12 | 41100 |
| 4 | 2023-10-12 | 40350 |
| 5 | 2023-10-12 | 40100 |
+--------+------------+--------+
5 rows in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> call calc_fine_lib(2)$

Query OK, 0 rows affected (0.01 sec)

mysql> call calc_fine_lib(3)$

Query OK, 0 rows affected (0.01 sec)

mysql> select * from borrower;

-> $
+--------+------+-------------+------------+--------+
| rollno | name | dateofissue | nameofbook | status |
+--------+------+-------------+------------+--------+
| 1 | abc | 2021-06-01 | SEPM |R |
| 2 | xyz | 2021-05-01 | OOP |R |
| 3 | pqr | 2021-06-15 | DBMS |R |
| 4 | def | 2021-06-30 | DSA |R |
| 5 | lmn | 2021-07-05 | ADS |R |
+--------+------+-------------+------------+--------+
5 rows in set (0.00 sec)

mysql> select * from fine$

+--------+------------+--------+
| rollno | returndate | amount |
+--------+------------+--------+
| 1 | 2023-10-12 | 41800 |
| 2 | 2023-10-12 | 43350 |
| 3 | 2023-10-12 | 41100 |
| 4 | 2023-10-12 | 40350 |
| 5 | 2023-10-12 | 40100 |
| 1 | 2023-10-12 | 41800 |
| 2 | 2023-10-12 | 43350 |
| 3 | 2023-10-12 | 41100 |
+--------+------------+--------+
8 rows in set (0.00 sec)
OUTPUT:

mysql> use info;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table borrower2(roll_no int,name varchar(20),date_of_issue

date,book varchar2);

Query OK, 0 rows affected (0.44 sec)

mysql>insert into borrower2 values('1','Suraj','2018-06-

10','wings_of_fire','avaliable','APJ');

Query OK, 1 row affected (0.07 sec)

mysql> insert into borrower2 values('2','Siddhi','2018-05-

11','leaves_life','not_avaliable','borwarkar');

Query OK, 1 row affected (0.05 sec)

mysql> insert into borrower2 values('3','Supriya','2018-

02-12','unusal','avaliable','johar');

Query OK, 1 row affected (0.04 sec)

mysql> insert into borrower2 values('4','Sam','2018-06-
20','skylimit','avaliable','ingale');

Query OK, 1 row affected (0.05 sec)

mysql> insert into borrower2 values('5','Ani','2018-04-

20','highway','not_avaliable','klm');

Query OK, 1 row affected (0.05 sec)

mysql> select *from borrower1;

+ + + +
+ + +
| roll_no | name
| author
|
| date_of_issue | book_name
| status
+ + + +
+ + +
|
| APJ
1 | Suraj
|
| 2018-06-10
| wings_of_fire | avaliable
|
2 | Siddhi
| 2018-05-11
not_avaliable | borwarkar | | leaves_life |
|
3 | Supriya
| johar
| | unusal | avaliable
|
4 | Sam | 2018-06-20
| ingale
| | skylimit | avaliable
|
5 | Ani
| 2018-04-20
not_avaliable | klm
| | highway |
| 2018-02-12
+ + + +
+ + +
5 rows in set (0.00 sec)

//INSERT TRIGGER

mysql> delimiter //
mysql> create trigger library after insert on borrower1 foreach row
-> begin
-> insert into audit1
values(new.roll_no,new.name,new.date_of_issue,new.book_name,ne
w.status,new.author,current_timestamp);
-> end;
-> //Query OK, 0 rows affected (0.10 sec)
mysql> insert into borrower1 values('6','xyz','2018-09-
06','aaa','avaliable','xxx');
-> //
Query OK, 1 row affected (0.07 sec)
mysql> select * from borrower1;
-> //
+ + + +
+ + +
| roll_no | name
| author
|
| date_of_issue | book_name
| status
+ + + +
+ + +
|
| APJ
1 | Suraj
|
| 2018-06-10
| wings_of_fire | avaliable
|
2 | Siddhi
| 2018-05-11
not_avaliable | borwarkar | | leaves_life |
|
3 | Supriya
| johar
| | unusal | avaliable
|
4 | Sam | 2018-06-20
| ingale
| | skylimit | avaliable
|
5 | Ani
| 2018-04-20
not_avaliable | klm
| | highway |
|
| xxx | aaa | avaliable
6 | xyz
|
| 2018-02-12
| 2018-09-06
+ + + +
+ + +
6 rows in set (0.00 sec)

mysql> select * from audit1;

-> //
+ + + + +
+--------+ -------------------- +| roll_no | name | date_of_issue | book_name | status
author | ts
|
|
+ + + + +
+ + +
|
6 | xyz | 2018-09-06
| 2018-08-29 15:46:13 |
| aaa
| avaliable | xxx
+ + + + +
+ + +
1 row in set (0.00 sec)

// UPDATE TRIGGER
mysql> delimiter //
mysql> create trigger library1 after update on borrower1 foreach
row
->
begin
->
insert into audit1 values(new.roll_no,new.name,new.date_of_issue,new.book_name,ne
w.status,new.author,current_timestamp);
-> end;
-> //

Query OK, 0 rows affected (0.08 sec)

mysql> update borrower1 set roll_no='8',book_name='leaf' where name='xyz';

-> //
Query OK, 1 row affected (0.04 sec)
Rows matched: 1
Changed: 1
Warnings: 0

mysql> select *from borrower1;

-> //
+ + + +
+ + +
| roll_no | name
| author
|
| date_of_issue | book_name
| status+ + + +
+ + +
|
| APJ
1 | Suraj
|
| 2018-06-10
| wings_of_fire | avaliable
|
2 | Siddhi
| 2018-05-11
not_avaliable | borwarkar | | leaves_life |
|
3 | Supriya
| johar
| | unusal | avaliable
|
4 | Sam | 2018-06-20
| ingale
| | skylimit | avaliable
|
5 | Ani
| 2018-04-20
not_avaliable | klm
| | highway |
|
| xxx | leaf | avaliable
8 | xyz
|
| 2018-02-12
| 2018-09-06
+ + + +
+ + +
6 rows in set (0.00 sec)
OUTPUT:

> use Suraj;

switched to db Suraj

> db.createCollection('Student');
{ "ok" : 1 }

> db.Student.insert({'Rno':'1','Name':'Rutik','Class':'TE COMP'});

WriteResult({ "nInserted" : 1 })

> db.Student.insert({'Rno':'2','Name':'Suraj','Class':'TE COMP'});

WriteResult({ "nInserted" : 1 })

>db.Student.insert({'Rno':'3','Name':'Pranav','Class':'TE COMP'});WriteResult({
"nInserted" : 1 })

> db.Student.insert({'Rno':'4','Name':'Harsh','Class':'TE COMP'});

WriteResult({ "nInserted" : 1 })

> db.Student.insert({'Rno':'5','Name':'Riten','Class':'TE COMP'});

WriteResult({ "nInserted" : 1 })

> db.Student.insert({'Rno':'6','Name':'Riten','Class':'TE COMP'});

WriteResult({ "nInserted" : 1 })

> db.Student.find();
{ "_id" : ObjectId("5ba1d618f5bbacd4ad81568d"), "Rno" : "1","Name" :
"Rutik", "Class" : "TE COMP" }
{ "_id" : ObjectId("5ba1d625f5bbacd4ad81568e"), "Rno" : "2","Name" :
"Suraj", "Class" : "TE COMP" }
{ "_id" : ObjectId("5ba1d63af5bbacd4ad81568f"), "Rno" : "3","Name" :
"Pranav", "Class" : "TE COMP" }
{ "_id" : ObjectId("5ba1d647f5bbacd4ad815690"), "Rno" : "4","Name" :
"Harsh", "Class" : "TE COMP" }
{ "_id" : ObjectId("5ba1d65ef5bbacd4ad815691"), "Rno" : "5","Name" :
"Riten", "Class" : "TE COMP" }
{ "_id" : ObjectId("5ba1d66df5bbacd4ad815692"), "Rno" : "6", "Name" :
"Riten", "Class" : "TE COMP" }

> db.Student.find().pretty();
{
"_id" : ObjectId("5ba1d618f5bbacd4ad81568d"),
"Rno" : "1",
"Name" : "Rutik",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d625f5bbacd4ad81568e"),
"Rno" : "2","Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh",

"Class" : "TE COMP"

}
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d66df5bbacd4ad815692"),
"Rno" : "6",
"Name" : "Riten",
"Class" : "TE COMP"
}

> db.Student.update({'Name':'Harsh'},{$set: {'Name':'Henry'}});

WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1})

> db.Student.find().pretty();
{
"_id" : ObjectId("5b8fad4ef00832a0a50b5036"),
"Rno" : "1",
"Name" : "Rutik",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad62f00832a0a50b5037"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad70f00832a0a50b5038"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad7ff00832a0a50b5039"),
"Rno" : "4",
"Name" : "Henry",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad8df00832a0a50b503a"),

"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fada4f00832a0a50b503b"),"Rno" : "6","Name" :
"Riten",
"Class" : "TE COMP"
}

> db.Student.remove({'ADD':'MP'});
WriteResult({ "nRemoved" : 1 })

> db.Student.find().pretty();
{
"_id" : ObjectId("5b8fad62f00832a0a50b5037"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad70f00832a0a50b5038"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad7ff00832a0a50b5039"),
"Rno" : "4",
"Name" : "Henry",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad8df00832a0a50b503a"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
{"_id" : ObjectId("5b8fada4f00832a0a50b503b"),
"Rno" : "6",
"Name" : "Riten",
"Class" : "TE COMP"
}
>db.Student.save({_id:ObjectId("5b8fad4ef00832a0a50b5036"),"RNO
":"1","NAME":"RUTIK","CLASS":"TE COMP","ADD":"MP"});

WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1})

> db.Student.find().pretty();
{
"_id" : ObjectId("5b8fad4ef00832a0a50b5036"),
"RNO" : "1",
"NAME" : "RUTIK",
"CLASS" : "TE COMP",
"ADD" : "MP"
}
{
"_id" : ObjectId("5b8fad62f00832a0a50b5037"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad70f00832a0a50b5038"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad7ff00832a0a50b5039"),
"Rno" : "4","Name" : "Henry",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad8df00832a0a50b503a"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fada4f00832a0a50b503b"),
"Rno" : "6",
"Name" : "Riten",
"Class" : "TE COMP"
}

> db.Student.find({$and:[{"Name":"Rutik"},{"Rno":"2"}]});
> db.Student.find({$and:[{"Name":"Rutik"},
{"Rno":"1"}]}).pretty();
{
"_id" : ObjectId("5ba1d618f5bbacd4ad81568d"),
"Rno" : "1",
"Name" : "Rutik",
"Class" : "TE COMP"
}
> db.Student.find({$and:[{"Name":"Rutik"},{"Rno":"2"}]}).pretty();

> db.Student.find({$or:[{"Name":"Rutik"},{"Rno":"2"}]}).pretty();
{
"_id" : ObjectId("5ba1d618f5bbacd4ad81568d"),
"Rno" : "1",
"Name" : "Rutik","Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d625f5bbacd4ad81568e"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
> db.Student.find({$or:[{"Name":"Rutik"},{"Class":"TE
COMP"}]}).pretty();
{
"_id" : ObjectId("5ba1d618f5bbacd4ad81568d"),
"Rno" : "1",
"Name" : "Rutik",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d625f5bbacd4ad81568e"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",

"Name" : "Harsh","Class" : "TE COMP"

}
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d66df5bbacd4ad815692"),
"Rno" : "6",
"Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find({$nor:[{"Name":"Rutik"},{"Class":"TE
COMP"}]}).pretty();

> db.Student.find({$nor:[{"Name":"Rutik"},
{"Rno":"2"}]}).pretty();
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),"Rno" : "5","Name" :
"Riten",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d66df5bbacd4ad815692"),
"Rno" : "6",
"Name" : "Riten",
"Class" : "TE COMP"
}
db.Student.find( {"Rno": { $not:{$lt:"3"}}}).pretty();
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),

"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d66df5bbacd4ad815692"),
"Rno" : "6","Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $eq:"5"}}).pretty();
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $ne:"5"}}).pretty();
{
"_id" : ObjectId("5ba1d618f5bbacd4ad81568d"),
"Rno" : "1",
"Name" : "Rutik",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d625f5bbacd4ad81568e"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4","Name" : "Harsh",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d66df5bbacd4ad815692"),
"Rno" : "6",
"Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $gt:"5"}}).pretty();
{
"_id" : ObjectId("5ba1d66df5bbacd4ad815692"),
"Rno" : "6",
"Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $gte:"5"}}).pretty();
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d66df5bbacd4ad815692"),
"Rno" : "6",
"Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $lt:"5"}}).pretty();
{
"_id" : ObjectId("5ba1d618f5bbacd4ad81568d"),"Rno" : "1","Name" :
"Rutik",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d625f5bbacd4ad81568e"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $lte:"5"}}).pretty();
{
"_id" : ObjectId("5ba1d618f5bbacd4ad81568d"),
"Rno" : "1",
"Name" : "Rutik",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d625f5bbacd4ad81568e"),
"Rno" : "2","Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $lt:"5",$gt:"2"}}).pretty();
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh","Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $lte:"5",$gte:"2"}}).pretty();
{
"_id" : ObjectId("5ba1d625f5bbacd4ad81568e"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $lte:"5",$gt:"2"}}).pretty();
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav","Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d65ef5bbacd4ad815691"),
"Rno" : "5",
"Name" : "Riten",
"Class" : "TE COMP"
}
> db.Student.find( {"Rno": { $lt:"5",$gte:"2"}}).pretty();
{
"_id" : ObjectId("5ba1d625f5bbacd4ad81568e"),
"Rno" : "2",
"Name" : "Suraj",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d63af5bbacd4ad81568f"),
"Rno" : "3",
"Name" : "Pranav",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5ba1d647f5bbacd4ad815690"),
"Rno" : "4",
"Name" : "Harsh",
"Class" : "TE COMP"}
OUTPUT:

> use comp;

switchedtodbcomp
//CREATE COLLECTION WEBSITE
> db.createCollection('website');
{"ok":1 }

//INSERT VALUES IN WEBSITE

>
db.website.insert({'roll':'1','name':'suraj','amount':1000,'ur l':'www.yahoo.com'});
WriteResult({"nInserted":1})

>db.website.insert({'roll':'2','name':'harsh','amount':2000,'url':'www.yahoo.com'});

WriteResult({"nInserted":1})

>db.website.insert({'roll':'3','name':'supriya','amount':3000,'url':'www.google.com'
});

WriteResult({"nInserted":1})

>db.website.insert({'roll':'4','name':'ash','amount':4000,'url':'www.gmail.com'})
;
WriteResult({"nInserted":1})

>db.website.insert({'roll':'5','name':'ash','amount':1000,'url':'dpcoe.com'});

WriteResult({ "nInserted" : 1 })

//SUMAGGREGATE

> db.website.aggregate({$group:{_id:"$name","total":{$sum:"$amount"}}});
{"_id":"ash","total":5000}
{"_id":"supriya","total":3000}
{"_id":"harsh","total":2000}
{"_id":"suraj","total":2000}

//AVG AGGREGATE

> db.website.aggregate({$group:{_id:"$name","total":
{$avg:"$amount"}}});
{"_id":"ash","total":2500}
{"_id":"supriya","total":3000}
{"_id":"harsh","total":2000}
{"_id":"suraj","total":1000}
//MINAGGREGATION

> db.website.aggregate({$group:{_id:"$name","total":{$min:"$amount"}}});
{"_id":"ash","total":1000}
{"_id":"supriya","total":3000}
{"_id":"harsh","total":2000}
{"_id":"suraj","total":1000}

//MAXAGGREGATION

> db.website.aggregate({$group:{_id:"$name","total":{$max:"$amount"}}});
{"_id":"ash","total":4000}
{"_id":"supriya","total":3000}
{"_id":"harsh","total":2000}
{"_id":"suraj","total":1000}

//FIRSTAGGREGATION

> db.website.aggregate({$group:{_id:"$name","total":{$first:"$amount"}}});
{"_id":"ash","total":4000}
{"_id":"supriya","total":3000}
{"_id":"harsh","total":2000}
{"_id":"suraj","total":1000}

//LASTAGGREGATION

> db.website.aggregate({$group:{_id:"$name","total":{$last:"$amount"}}});
{"_id":"ash","total":1000}
{"_id":"supriya","total":3000}
{"_id":"harsh","total":2000}
{"_id":"suraj","total":1000}

//PUSHAGGREGATION

> db.website.aggregate({$group:{_id:"$name","total":
{$push:"$amount"}}});
{"_id":"ash","total":[4000,1000]}
{"_id":"supriya","total":[3000]}
{"_id":"harsh","total":[2000]}
{"_id":"suraj","total":[1000,1000]}
//COUNTAGGREGATION

> db.website.aggregate({$group:{_id:"$name","total":{$sum:1}}});
{"_id":"ash","total":2}
{"_id":"supriya","total":1}
{"_id":"harsh","total":1}
{"_id":"suraj","total":2}

//ADDTOSETAGGREGATE

> db.website.aggregate({$group:
{_id:"$name","total"{$addToSet:"$amount"}}});
{"_id":"ash","total":[1000,4000]}
{"_id":"supriya","total":[3000]}
{"_id":"harsh","total":[2000]}
{"_id":"suraj","total":[1000]}

//INDEXING

> db.createCollection('website1');
{"ok":1 }

> db.website1.insert({'r':1,'name':'suraj'});
WriteResult({ "nInserted" : 1 })

> db.website1.find().pretty()
{"_id":ObjectId("5ba3509a444926329738012d"),"roll":1,
"name" : "suraj" }
{"_id":ObjectId("5ba35293444926329738012e"),"roll":1,
"name" : "suraj" }

> db.website1.createIndex({'name':1})
{"numIndexesBefore":2,"note":"allindexesalready exist", "ok" : 1
}//CREATE INDEXING

> db.website1.createIndex({'name':-1})
{
"createdCollectionAutomatically":false,
"numIndexesBefore" : 2,
"numIndexesAfter":3,
"ok":1
}
> db.website1.getIndexses()
2018-09-20T13:28:09.628+0530TypeError:Property'getIndexses' of
object om.website is not a
function

> db.website1.getIndexes() [
{"v":1,
"key":{

"_id":1
},
"name":"_id_",
"ns":"suraj.website1"
},
{
"v":1,
"key":{
"name":1
},
"name" : "name_1",
"ns":"suraj.website1"
},
{
"v":1,
"key":{
"name":-1
},
"name" : "name_-1",
"ns":"suraj.website1"
}
]
> db.website1.createIndex({'name':-1})
{"numIndexesBefore":3,"note":"allindexesalready exist", "ok" : 1
}

//DROPINDEX
> db.website.dropIndex({'name':-1})
{ "nIndexesWas" : 3, "ok" : 1
}>db.website1.dropIndex({'name':1})
{"nIndexesWas":2,"ok":1}>db.website1.dropIndex({'name':1})
{
"nIndexesWas":1,
"ok":0,
"errmsg":"can'tfindindexwithkey:{name:1.0}"
}

//GETINDEXING

> db.website1.getIndexes() [
{
"v":1, "key":{
"_id":1
},
"name":"_id_",
"ns":"suraj.website1"
}
]

> db.website1.find().pretty()
{"_id":ObjectId("5ba3509a444926329738012d"),"roll":1,
"name" : "suraj" }
{"_id":ObjectId("5ba35293444926329738012e"),"roll":1,
"name" : "suraj" }

>
> db.website1.createIndex({'name':1})
{
"createdCollectionAutomatically":false,
"numIndexesBefore" : 1,
"numIndexesAfter":2,
"ok":1
}

> db.website1.getIndexes()[
{
"v":1,
"key":{
"_id":1
},
"name":"_id_",
"ns":"suraj.website1"
},
{
"v":1,
"key":{"name":1
},
"name" : "name_1",
"ns":"suraj.website1"
}
]

> db.website1.dropIndex({'name':1})
{"nIndexesWas":2,"ok":1}

> db.website1.getIndexes() [
{
"v":1,
"key":{
"_id":1
},
"name":"_id_",
"ns":"suraj.website1"
}
]

> db.website1.createIndex({'name':1,'r':-1})
{"createdCollectionAutomatically":false,
"numIndexesBefore" : 1,
"numIndexesAfter":2,
"ok":1
}

> db.website1.getIndexes() [
{
"v":1,
"key":{
"_id":1
},
"name":"_id_",
"ns":"suraj.website1"
},
{
"v":1,
"key":{
"name":1,
"r":-1
},
"name":"name_1_r_-1", "ns" :
"suraj.website1"
}
](i-search)`db.website1.insert({'roll':1,'name':'suraj'});':
OUTPUT:

> use Suraj

switched to db Suraj

> db.createCollection('Journal');
{ "ok" : 1 }

>db.Journal.insert({'book_id':1,'book_name':'JavacdOOP','amt':500,'status':'A
vailable'});
WriteResult({ "nInserted" : 1 })

> db.Journal.insert({'book_id':1,'book_name':'JavaOOP','amt':400,'status':'Not
Available'});
WriteResult({ "nInserted" : 1 }

>db.Journal.insert({'book_id':1,'book_name':'Java','amt':300,'s tatus':'Not
Available'});
WriteResult({ "nInserted" : 1 })

>db.Journal.insert({'book_id':2,'book_name':'Java','amt':300,'s tatus':'Available'});
WriteResult({ "nInserted" : 1 })>

>db.Journal.insert({'book_id':2,'book_name':'OPP','amt':200,'st atus':'Available'});
WriteResult({ "nInserted" : 1 })

>db.Journal.insert({'book_id':2,'book_name':'C+','amt':200,'status':'Available'}
);
WriteResult({ "nInserted" : 1 })

>db.Journal.insert({'book_id':3,'book_name':'C+','amt':150,'status':'Available'}
);
WriteResult({ "nInserted" : 1 })

> db.Journal.insert({'book_id':3,'book_name':'C+
+','amt':200,'status':'Not Available'});
WriteResult({ "nInserted" : 1 })

> db.Journal.insert({'book_id':4,'book_name':'OPP C+
+','amt':300,'status':'Not Available'});
WriteResult({ "nInserted" : 1 })

> db.Journal.insert({'book_id':5,'book_name':'OPP C+
+','amt':400,'status':'Available'});
WriteResult({ "nInserted" : 1 })

> db.Journal.insert({'book_id':5,'book_name':'C+
+','amt':400,'status':'Available'});
WriteResult({ "nInserted" : 1 })

> db.Journal.insert({'book_id':5,'book_name':'C++
Java','amt':400,'status':'Not Available'}); WriteResult({
"nInserted" : 1 })

> var mapfunction=function(){ emit(this.book_id,this.amt)};

> var reducefunction=function(key,value){return Array.sum(value);};

> db.Journal.mapReduce(mapfunction,reducefunction,
{'out':'new'});
{
"result" : "new",
"timeMillis" : 49,"counts" : {
"input" : 12,
"emit" : 12,
"reduce" : 4,
"output" : 5
},
"ok" : 1
}

> db.Journal.mapReduce(mapfunction,reducefunction,
{'out':'new'}).find().pretty();
{ "_id" : 1, "value" : 1200 }
{ "_id" : 2, "value" : 700 }
{ "_id" : 3, "value" : 350 }
{ "_id" : 4, "value" : 300 }
{ "_id" : 5, "value" : 1200 }
>
>
> db.new.find().pretty();
{ "_id" : 1, "value" : 1200 }
{ "_id" : 2, "value" : 700 }
{ "_id" : 3, "value" : 350 }
{ "_id" : 4, "value" : 300 }
{ "_id" : 5, "value" : 1200 }