DBMS Laboratory Practical Programs

===================================================================
Practical No. 2

Aim: To Study SQL DDL, DML and DCL statements (SQL QUERIES).

Title: Design and Develop SQL DDL statements which demonstrate the use of SQL
objects such as Table, View, Index, Sequence, Synonym, different constraints etc.

mysql> CREATE DATABASE A1;

Query OK, 1 row affected (1.65 sec)
mysql> USE A1; Database changed mysql> create table employee(emp_id int,emp_name
varchar(20),joining_date date, primary key(emp_id));
Query OK, 0 rows affected (2.41 sec)
mysql> desc employee;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| emp_id | int | NO | PRI | NULL | emp_name | |
| varchar(20) | YES | | NULL | | |
| |
joining_date | date | YES | | NULL
+ +
+ + + + +
3 rows in set (0.87 sec)
mysql> create table finance(pay_id int, emp_id int, em_salary int,emp_contract
date, foreign key(emp_id) references employee(emp_id),primary key(pay_id));
Query OK, 0 rows affected (0.30 sec)
mysql> desc finance;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| pay_id | int | NO | PRI | NULL | |
| emp_id | int | YES | MUL | NULL | |
| |
| em_salary | int | YES | | NULL
| |
| emp_contract | date | YES | | NULL + +
+ + + + +
4 rows in set (0.01 sec)
mysql> insert into employee values(1,'RAANA','2021/10/15');
Query OK, 1 row affected, 1 warning (0.34 sec)
mysql> insert into employee values(2,'NITIN','2020/09/20');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> insert into employee values(3,'DISHA','2020/12/10');
Query OK, 1 row affected, 1 warning (0.04 sec)
insert into employee values(4,'GANESH','2019/10/10'); Query
OK, 1 row affected, 1 warning (0.07 sec)
mysql> insert into employee values(5,'GANESH','2020/01/12');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from employee;
 mysql>
+ + + +
| emp_id | emp_name | joining_date |
+ + + +
| 1 | RAANA | 2021-10-15 |
| 2 | NITIN | 2020-09-20 |
| 3 | DISHA | 2020-12-10 |
| 4 | GANESH | 2019-10-10 |
| 5 | GANESH | 2020-01-12 |
+ + + +
5 rows in set (0.00 sec)
mysql> insert into finance values(101,1,20000,'2022-10-10');
Query OK, 1 row affected (0.06 sec)
mysql> insert into finance values(102,3,45000,'2022-01-20');
Query OK, 1 row affected (0.07 sec)
mysql> insert into finance values(103,4,40000,'2020-11-10');
Query OK, 1 row affected (0.03 sec)
mysql> insert into finance values(104,5,20000,'2021-01-15');
Query OK, 1 row affected (0.04 sec) mysql> select * from
finance;
+ + + + +
| pay_id | emp_id | em_salary | emp_contract |
+ + + + +
| 101 | 1 | 20000 | 2022-10-10 | |
102 | 3 | 45000 | 2022-01-20 |
|
| 103 | 4 | 40000 | 2020-11-10
|
| 104 | 5 | 20000 | 2021-01-15 +
+ + + +
4 rows in set (0.00 sec)
mysql> select emp_id,emp_name
-> from employee;
+ + +
| emp_id | emp_name |
+ + +
| 1 | RAANA |
| 2 | NITIN |
| 3 | DISHA |
| 4 | GANESH |
| 5 | GANESH |
+ + +
5 rows in set (0.00 sec)
alter table employee add phone_no int;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
 mysql>
+ + + + + + +
| emp_id | int | NO | PRI | NULL | emp_name | | |
varchar(20) | YES | | NULL | joining_date | date | |
| YES | | NULL | phone_no | int | YES | | NULL | |
+ + + + + | |
4 rows in set (0.00 sec) + +
mysql> select * from employee;

+ + + + +
| emp_id | emp_name | joining_date | phone_no |
+ + + + +
| 1 | RAANA | 2021-10-15 | NULL |
| 2 | NITIN | 2020-09-20 | NULL |
| 3 | DISHA | 2020-12-10 | NULL | | 4 |
GANESH | 2019-10-10 | NULL |
| 5 | GANESH | 2020-01-12 | NULL |
+ + + + +
5 rows in set (0.00 sec)
mysql> create index emp_search on employee(emp_id);
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table auto(emp_id int auto_increment, department
varchar(20),primary key(emp_id));
Query OK, 0 rows affected (0.81 sec)
mysql> desc auto;
+ + + + + +
|
+ + + + + + + Null | Key
| emp_id | int | NO | PRI | NULL | auto_increment | | Default
| department | varchar(20) | YES | | NULL | | | Extra
+ + |
+ + + + +
2 rows in set (0.03 sec)
+
| Field | Type
mysql> select * from auto;
Empty set (0.00 sec)
mysql> insert into auto values(1,'Asst. Manager');
Query OK, 1 row affected (0.06 sec)
mysql> insert into auto values(NULL,'Manager');
Query OK, 1 row affected (0.07 sec)
select * from auto;
+ + +
| emp_id | department |
+ + +
| 1 | Asst. Manager |
| 2 | Manager |
+ + +
2 rows in set (0.00 sec)
 mysql>
mysql> insert into auto values(NULL,'HR');
Query OK, 1 row affected (0.03 sec)
mysql> insert into auto values(NULL,'Manager');
Query OK, 1 row affected (0.04 sec)
mysql> insert into auto values(NULL,'HR');
Query OK, 1 row affected (0.07 sec)
mysql> select * from auto;
+ + +
| emp_id | department |
+ + +
| 1 | Asst. Manager |
| 2 | Manager |
| 3 | HR |
|
4 | Manager |
|
+ 5 | HR |
+ +
5 rows in set (0.00 sec)
mysql> show tables;
+ +
| Tables_in_a1 |
+ +
| auto |
| employee |
| finance |
+ +
3 rows in set (0.18 sec)
mysql> create view EmpDetails
-> as
-> select e.emp_id,e.emp_name,f.em_salary
-> from employee as e, finance as f
-> where e.emp_id = f.emp_id;
Query OK, 0 rows affected (0.20 sec)
select * from EmpDetails;
+ + + +
| emp_id | emp_name | em_salary |
+ + + +
| 1 | RAANA | 20000 |
| 3 | DISHA | 45000 |
| 4 | GANESH | 40000 |
| 5 | GANESH | 20000 |
+ + + +
4 rows in set (0.00 sec)

mysql> select a.emp_id,e.emp_name,[Link]

-> from employee as e, auto as a
-> where e.emp_id = a.emp_id;
+ + + +
| emp_id | emp_name | department |
 mysql>
+ + + +
| 1 | RAANA | Asst. Manager |
| 2 | NITIN | Manager |
| 3 | DISHA
| HR |
| 4 | GANESH
| Manager |
| 5 | GANESH
+ + | HR |
+ +
5 rows in set (0.04 sec)
mysql> alter table employee rename column joining_date to DOJ;
Query OK, 0 rows affected (3.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+ + + + +
| emp_id | emp_name | DOJ | phone_no |
+ + + + +
| 1 | RAANA | 2021-10-15 | NULL |
| 2 | NITIN | 2020-09-20 | NULL |
| 3 | DISHA | 2020-12-10 | NULL |
| 4 | GANESH | 2019-10-10 | NULL |
| 5 | GANESH | 2020-01-12 | NULL |
+ + + + +
5 rows in set (0.00 sec)
mysql> alter table employee drop column phone_no;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from employee;

+ + + +
| emp_id | emp_name | DOJ |
+ + + +
| 1 | RAANA | 2021-10-15 |
| 2 | NITIN | 2020-09-20 |
| 3 | DISHA | 2020-12-10 |
 | |

4 GANESH
| 2019-10-10 |
| 5 | GANESH | 2020-01-12 |
+ + + +
5 rows in set (0.00 sec)

mysql> drop table auto;

Query OK, 0 rows affected (0.47 sec)
mysql> select * from auto;
ERROR 1146 (42S02): Table '[Link]' doesn't exist
mysql> rename table employee to employee_details;
Query OK, 0 rows affected (0.20 sec)
mysql> select * from employee_details;
+ + + +
| emp_id | emp_name | DOJ |
+ + + +
| 1 | RAANA | 2021-10-15 |
| 2 | NITIN | 2020-09-20 |
| 3 | DISHA | 2020-12-10 |
| 4 | GANESH | 2019-10-10 |
| 5 | GANESH | 2020-01-12 |
+ + + +
5 rows in set (0.03 sec)
 PRACTICAL No.: 3
Aim: Design the SQL queries on the suitable database application using SQL
DML statements.
Title: Design the queries which demonstrate the use of concepts likes Insert,
Select, Update, Delete with operators, functions, and set operator etc.
Title: Design the SQL queries on the suitable database application using
SQL DML statements.

mysql> create database Practical3; Query

OK, 1 row affected (0.07 sec)
mysql> use Practical3;
Database changed
mysql> create table emp(emp_id int primary key auto_increment,fname char(20)
not null,lname char(20) not null,salary float, DOJ date); Query
OK, 0 rows affected (0.34 sec)
mysql> desc emp;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| emp_id | int | NO | PRI | NULL | | auto_increment |
fname | char(20) | NO | | NULL | | |
lname | char(20) | NO | | NULL | |
| salary | float | YES | | NULL | |
| DOJ | date | YES | | NULL | |
+ + + + + + +
5 rows in set (0.09 sec)
mysql> insert into emp values(1,'NITIN','PATIL',20000,'2020-10-14'); Query
OK, 1 row affected (0.14 sec)
mysql> insert into emp values(NULL,'NISHANT','PATIL',30000,'2019-11-10');
Query OK, 1 row affected (0.06 sec)

mysql> insert into emp values(NULL,'NISHA','PATEL',25000,'2020-08-10'); Query

OK, 1 row affected (0.05 sec)
mysql> insert into emp values(NULL,'PALLAVI','MALI',20000,'2021-11-05');
Query OK, 1 row affected (0.33 sec)

mysql> insert into emp values(NULL,'PRIYA','KAPOOR',40000,'2021-01-10'); Query

OK, 1 row affected (0.04 sec)
mysql> select * from emp;
+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 1 | NITIN | PATIL | 20000 | 2020-10-14 |
| 2 | NISHANT | PATIL | 30000 | 2019-11-10 |
| 3 | NISHA | PATEL | 25000 | 2020-08-10 | 4
| | PALLAVI | MALI | 20000 | 2021-11-05 |
| 5 | PRIYA | KAPOOR | 40000 | 2021-01-10 |
+ + + + + +
5 rows in set (0.00 sec)
mysql> update emp set salary=35000 where emp_id = 2;
Query OK, 1 row affected (0.13 sec) Rows
matched: 1 Changed: 1 Warnings: 0
 | |

mysql> select * from emp;

+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 1 | NITIN | PATIL | 20000 | 2020-10-14 |
2 NISHANT | PATIL | 35000 | 2019-11-10 |
| 3 | NISHA | PATEL | 25000 | 2020-08-10 | | 4 |
PALLAVI | MALI | 20000 | 2021-11-05 |
| 5 | PRIYA | KAPOOR | 40000 | 2021-01-10 |
+ + + + + +
5 rows in set (0.00 sec)
mysql> delete from emp where emp_id = 5;
Query OK, 1 row affected (0.59 sec)

mysql> select * from emp;

+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 1 | NITIN | PATIL | 20000 | 2020-10-14 |
| 2 | NISHANT | PATIL | 35000 | 2019-11-10 |
| 3 | NISHA | PATEL | 25000 | 2020-08-10 |
| 4 | PALLAVI | MALI | 20000 | 2021-11-05 |
+ + + + + +
4 rows in set (0.00 sec)
mysql> create table company(emp_id int, designation varchar(20),primary key(emp_id));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into company values(1,'Asst. Manager'); Query

OK, 1 row affected (0.05 sec)
mysql> insert into company values(2,'HR');
Query OK, 1 row affected (0.19 sec)

mysql> insert into company values(3,'Manager'); Query

OK, 1 row affected (0.07 sec)
mysql> insert into company values(4,'Asst. Manager');
Query OK, 1 row affected (0.04 sec)

mysql> select * from company;

+ + +
| emp_id | designation |
+ + +
| 1 | Asst. Manager |
| 2 | HR |
| 3 | Manager |
| 4 | Asst. Manager |
+ + +
4 rows in set (0.00 sec)
mysql> create table company(emp_id int, designation varchar(20));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into company values(1,'Asst. Manager');
Query OK, 1 row affected (0.05 sec)
mysql> insert into company values(2,'HR');
Query OK, 1 row affected (0.02 sec)
mysql> insert into company values(3,'Manager');
Query OK, 1 row affected (0.11 sec)
mysql> insert into company values(4,'Asst. Manager');
Query OK, 1 row affected (0.04 sec)
mysql> select * from company;
+ + +
| emp_id | designation |
+ + +
| 1 | Asst. Manager |
| 2 | HR |
| 3 | Manager |
| 4 | Asst. Manager |
+ + +
4 rows in set (0.00 sec)
mysql> select distinct salary from emp;
+ +
| salary |
+ +
| 20000 |
| 35000 |
| 25000 |
+ +
3 rows in set (0.03 sec)
mysql> select avg(salary) from emp;
+ +
| avg(salary) |
+ +
| 25000 |
+ +
1 row in set (0.09 sec)

mysql> select concat(fname,lname) from emp where salary = 20000;

+ +
| concat(fname,lname) |
+ +
| NITINPATIL |
| PALLAVIMALI |
+ +
2 rows in set (0.00 sec)
mysql> select upper(fname) from emp where salary = 25000;
+ +
| upper(fname) |
+ +
| NISHA |
+ +
1 row in set (0.05 sec)
mysql> select lower(fname) from emp;
+ +
| lower(fname) |
 | |

+ +
| nitin |
| nishant |
| nisha |
| pallavi |
| nitin |
+ +
5 rows in set (0.04 sec)
mysql> select * from emp where year(DOJ)='2021';
+ + + + + +
emp_id fname | lname | salary | DOJ |
+ + + + + +
| 4 | PALLAVI | MALI | 20000 | 2021-11-05 |
+ + + + + +
1 row in set (0.04 sec)
mysql> select * from emp where fname like 'NI%';
+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 1 | NITIN | PATIL | 20000 | 2020-10-14 |
| 2 | NISHANT | PATIL | 35000 | 2019-11-10 |
| 3 | NISHA | PATEL | 25000 | 2020-08-10 |
+ + + + + +
3 rows in set (0.04 sec)

mysql> select * from emp where emp_id in(1,2,3);

+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 1 | NITIN | PATIL | 20000 | 2020-10-14 |
| 2 | NISHANT | PATIL | 35000 | 2019-11-10 |
| 3 | NISHA | PATEL | 25000 | 2020-08-10 |
+ + + + + +
3 rows in set (0.00 sec)
mysql> select * from emp where emp_id not in(1,2,3);
+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 4 | PALLAVI | MALI | 20000 | 2021-11-05 |
+ + + + + +
1 row in set (0.00 sec)
mysql> insert into emp values(null,'NITIN','PAWAR',25000,'2021-01-12');
Query OK, 1 row affected (0.05 sec)
mysql> select * from emp;
+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 1 | NITIN | PATIL | 20000 | 2020-10-14 |
| 2 | NISHANT | PATIL | 35000 | 2019-11-10 |
| 3 | NISHA | PATEL | 25000 | 2020-08-10 |
| 4 | PALLAVI | MALI | 20000 | 2021-11-05 |
| 6 | NITIN | PAWAR | 25000 | 2021-01-12 |
+ + + + + +
 5 rows in set (0.00 sec)

mysql> select distinct fname,lname from emp where emp_id in (select emp_id from
company);
+ + +
| fname | lname |
+ + +
| NITIN | PATIL | |
NISHANT | PATIL | |
NISHA | PATEL |
| PALLAVI | MALI |
+ + +
4 rows in set (0.08 sec)
mysql> select distinct emp_id,fname,lname from emp where emp_id in (select emp_id
from company);
+ + + +
| emp_id | fname | lname |
+ + + +
| 1 | NITIN | PATIL |
| 2 | NISHANT | PATIL |
| 3 | NISHA | PATEL |
| 4 | PALLAVI | MALI |
+ + + +
4 rows in set (0.00 sec)
mysql> select * from emp where salary between 20000 and 25000;
+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 1 | NITIN | PATIL | 20000 | 2020-10-14 |
| 3 | NISHA | PATEL | 25000 | 2020-08-10 |
| 4 | PALLAVI | MALI | 20000 | 2021-11-05 |
| 6 | NITIN | PAWAR | 25000 | 2021-01-12 |
+ + + + + +
4 rows in set (0.00 sec)

mysql> select * from emp where year(DOJ) between '2019' and '2020';
+ + + + + +
| emp_id | fname | lname | salary | DOJ |
+ + + + + +
| 1 | NITIN | PATIL | 20000 | 2020-10-14 |
| 2 | NISHANT | PATIL | 35000 | 2019-11-10 |
| 3 | NISHA | PATEL | 25000 | 2020-08-10 |
+ + + + + +
3 rows in set (0.00 sec)
mysql> select max(salary) from emp;
+ +
| max(salary) |
+ +
| 35000 |
+ +
1 row in set (0.00 sec)

mysql> select avg(salary) from emp;

+ +
 | |

| avg(salary) |
+ +
| 25000 |
+ +
1 row in set (0.00 sec)
mysql> select count(fname) from emp;
+ +
| count(fname) |
+ +
5 | |
+ +
1 row in set (0.06 sec)
mysql> select sum(salary) from emp;
+ +
| sum(salary) |
+ +
| 125000 |
+ +
1 row in set (0.00 sec)
mysql> select sum(distinct salary)
from emp;
+ +
| sum(distinct salary) |
+ +
80000 | |
+ +
1 row in set (0.06
sec) mysql>

====================================================================
 | |

PRACTICAL No.: 4
Aim: SQL Queries – all types of Join, Sub-Query and View.

Title: Design at least 10 SQL queries for suitable database

application using SQL DML statements: all types of Join, Sub-Query
and View

mysql> create database

Practical4; Query OK, 1 row
affected (0.22 sec)
mysql> use
Practical4
; Database
changed

mysql> create table student(roll_no int primary key, name char(20),

address char(20), phone char(10), age int); Query OK, 0 rows
affected (0.30 sec)
mysql>
desc
student;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| roll_no | int | NO | PRI | NULL | name | |
| char(20) | YES | | NULL | |
| address | char(20) | YES | | NULL | |
| phone | char(10) | YES | | NULL | |
| |
| age | int | YES | | NULL
+ +
+ + + + +
5 rows in set (0.01 sec)
mysql> insert into student
values(1,'prisha','Anagar','9421898177',20); Query OK, 1 row
affected (0.06 sec)

mysql> insert into student

values(2,'pratik','pune','9022899970',21); Query OK, 1 row
affected (0.11 sec)
mysql> insert into student
values(3,'nishan','pune','8600917765',21); Query OK, 1 row
affected (0.06 sec)

mysql> insert into student

values(4,'Nirav','Dhule','7689001230',19); Query OK, 1 row
affected (0.04 sec)
mysql> insert into student
values(5,'Hardik','pune','9993455670',21);
Query OK, 1 row affected (0.06 sec)

mysql> select * from student;

+ + + + + +
| roll_no | name | address | phone | age |
+ + + + + +
| 1 | prisha | Anagar | 9421898177 | 20 |
| 2 | pratik | pune | 9022899970 | 21 |
| 3 | nishan | pune | 8600917765 | 4 21 |
| | Nirav | Dhule | 7689001230 | 19 |
| 21 |
5 | Hardik | pune | 9993455670 |
+ +
+ + + +
 5 rows in set (0.00 sec)
mysql> create table studCourse(cid int, roll_no int, course
char(20),foreign key(roll_no) references student(roll_no)); Query
OK, 0 rows affected (0.32 sec)
mysql> insert into studCourse
values(1,1,'Computer'); Query OK, 1 row
affected (0.06 sec) mysql> insert into
studCourse values(2,2,'Computer'); Query
OK, 1 row affected (0.05 sec)
mysql> insert into studCourse
values(2,3,'IT'); Query OK, 1 row
affected (0.02 sec)

mysql> insert into studCourse

values(3,4,'Computer'); Query OK, 1 row affected
(0.04 sec)
mysql> insert into studCourse
values(4,5,'E&TC'); Query OK, 1 row
affected (0.03 sec)

mysql> insert into studCourse

values(6,5,'Computer'); Query OK, 1 row affected
(0.06 sec)
mysql> select * from
studCourse;
+ + + +
| cid | roll_no | course |
+ + + +
| 1 | 1 | Computer |
| 2 | 2 | Computer |
| 2 | 3 | IT |
| 3 | 4 | Computer |
| 4 | 5 | E&TC |
| 6 | 5 | Computer |
+ + + +
6 rows in set (0.00 sec)
mysql> insert into student
values(7,'Krishna','Delhi','9986003456',20); Query OK, 1 row
affected (0.04 sec)

mysql> select * from student inner join studCourse on

student.roll_no = studCourse.roll_no;
+ + + + + + + +
+
| roll_no | name | address | phone | age | cid | roll_no | course |
+ + + + + + + + +
| 1 | prisha | Anagar | 9421898177 | 20 | 1 | 1 | Computer |
| 2 | pratik | pune | 9022899970 | 21 | 2 | 2 | Computer |
| 3 | nishan | pune | 8600917765 | 21 | 2 | 3 | IT |
| 4 | Nirav | Dhule | 7689001230 | 19 | 3 | 4 | Computer |
| 5 | Hardik | pune | 9993455670 | 21 | 4 | 5 | E&TC |
| 5 | Hardik | pune | 9993455670 | 21 | 6 | 5 | Computer |
+ + + + +
+ + + +
6 rows in set (0.00 sec)
mysql> select * from student left outer join studCourse on
student.roll_no = studCourse.roll_no;
+ + + + + + + +
+
 | |

| roll_no | name | address | phone | age | cid | roll_no | course

|
+ + + + + + + +
| 1 | prisha | Anagar | 9421898177 | 20 | 1 | 1 | Computer |
| 2 | pratik | pune | 9022899970 | 21 | 2 | 2 | Computer |
| 3 | nishan | pune | 8600917765 | 4 21 | 2 | 3 | IT |
| | Nirav | Dhule | 7689001230 | 19 | 3 | 4 | Computer |
| 5 | Hardik | pune | 9993455670 | 5 | E&TC
21 | 4 |
| 5 | Hardik | pune | 9993455670 | 5 | Computer |
21 | 6 |
| 7 | Krishna | Delhi | 9986003456 | NULL | NULL
20 | NULL |
+ + + + + +
+ +
7 rows in set (0.00 sec)
mysql> select * from student right outer join studCourse on
student.roll_no = studCourse.roll_no;
+ + + + + + + +
+
| roll_no | name | address | phone | age | cid | roll_no | course |
+ + + + + + + + +
| 1 | prisha | Anagar | 9421898177 | 20 | 1 | 1 | Computer |
| 2 | pratik | pune | 9022899970 | 21 | 2 | 2 | Computer |
| 3 | nishan | pune | 8600917765 | 21 | 2 | 3 | IT |
| 4 | Nirav | Dhule | 7689001230 | 19 | 3 | 4 | Computer |
21 | 4 | 5 | E&TC |
| 5 | Hardik | pune | 9993455670 |
21 | 6 | 5 | Computer |
| 5 | Hardik | pune | 9993455670 |
+ + + +
+ + + + +
6 rows in set (0.00 sec)
mysql> select * from student join
studCourse;
+ + + + + + + +
+
| roll_no | name | address | phone | age | cid | roll_no | course
|
 + + + + + + + +
| 7 | Krishna | Delhi | 9986003456 | 20 | 1 | 1 | Computer |
| 5 | Hardik | pune | 9993455670 | 4 21 | 1 | 1 | Computer |
| | Nirav | Dhule | 7689001230 | 19 | 1 | 1 | Computer |
| 3 | nishan | pune | 8600917765 | 21 | 1 | 1 | Computer |
| 2 | pratik | pune | 9022899970 | 1 21 | 1 | 1 | Computer |
| | prisha | Anagar | 9421898177 | 20 | 1 | 1 | Computer |
| 7 | Krishna | Delhi | 9986003456 | 20 | 2 | 2 | Computer |
| 5 | Hardik | pune | 9993455670 | 21 | 2 | 2 | Computer |
| 4 | Nirav | Dhule | 7689001230 | 19 | 2 | 2 | Computer |
| 3 | nishan | pune | 8600917765 | 21 | 2 | 2 | Computer |
| 21 | 2 | 2 | Computer |
2 | pratik | pune | 9022899970 | 1
| 20 | 2 | 2 | Computer |
| prisha | Anagar | 9421898177 |
| 7 | Krishna | Delhi | 9986003456 | 20 | 2 | 3 | IT |
| 21 | 2 | 3 | IT
5 | Hardik | pune | 9993455670 |
| 19 | 2 | 3 | IT
4 | Nirav | Dhule | 7689001230 |
| 21 | 2 |
3 | nishan | pune | 8600917765 | 3 | IT
| 21 | 2 |
2 | pratik | pune | 9022899970 | 1 20 | 3 | IT
| 2 |
| prisha | Anagar | 9421898177 | 20 | 3 | IT |
| 7 | Krishna | Delhi | 9986003456 | 3 |
21 | 4 | Computer |
| 3 |
5 | Hardik | pune | 9993455670 | 19 | 4 | Computer |
| 3 |
4 | Nirav | Dhule | 7689001230 | 3 | 21 | 4 | Computer |
| 3 |
nishan | pune | 8600917765 | 2 | 21 | 4 | Computer |
| 3 |
pratik | pune | 9022899970 | 1 | 20 | 4 | Computer |
| 3 |
prisha | Anagar | 9421898177 | 20 | 4 | Computer |
| 4 |
7 | Krishna | Delhi | 9986003456 | 21 | 5 | E&TC |
| 4 |
| 5 | Hardik | pune | 9993455670 | 19 | 4 | 5 | E&TC
| 4 | Nirav | Dhule | 7689001230 | 3 | 21 | 4 | 5 | E&TC
| nishan | pune | 8600917765 | 2 | 21 | 4 | 5 | E&TC
| pratik | pune | 9022899970 | 1 | 20 | 4 | 5 | E&TC
| prisha | Anagar | 9421898177 | 20 | 6 | 5 | E&TC
| 7 | Krishna | Delhi | 9986003456 | 21 | 6 | 5 | Computer |
| 5 | Hardik | pune | 9993455670 | 19 | 6 | 5 | Computer |
| 4 | Nirav | Dhule | 7689001230 | 21 | 6 | 5 | Computer |
| 3 | nishan | pune | 8600917765 | 21 | 6 | 5 | Computer |
| 2 | pratik | pune | 9022899970 | 20 | 6 | 5 | Computer |
+ 1 | prisha | Anagar | 9421898177 | + + 5 | Computer |
+ + + + +
36 rows in set (0.00 sec)
mysql> mysql> select s1.roll_no, [Link] from student s1,
studCourse s2 -> where s1.roll_no = s2.roll_no;
+ + +
| roll_no | name |
+ + +
| 1 | prisha |
| 2 | pratik |
| 3 | nishan |
| 4 | Nirav |
| 5 | Hardik |
| 5 | Hardik |
+ + +
6 rows in set (0.00 sec)
mysql> select distinct [Link],s2.roll_no from student
s1,studCourse s2 where [Link] > 20;
+ + +
| name | roll_no |
 | |

+ + +
| Hardik | 1 |
| nishan | 1 |
| pratik | 1 |
| Hardik | 2 |
| nishan | 2 |
| pratik | 2 |
| Hardik | 3 |
| nishan | 3 |
| pratik | 3 |
| Hardik | 4 |
| nishan | 4 |
| pratik | 4 |
| Hardik | 5 |
| nishan | 5 |
| pratik | 5 |
+ + +
15 rows in set (0.00 sec)
mysql> select name, age from student where age > 19 order
by(age);
+ + +
| name | age |
+ + +
| prisha | 20 |
| Krishna | 20 |
| pratik | 21 |
| nishan | 21 |
| Hardik | 21 |
+ + +
5 rows in set (0.00 sec)

mysql> select [Link], [Link],s2.roll_no,[Link] from student s1,

studCourse s2 where s1.roll_no = s2.roll_no and [Link] > 19 order
by(age);
+ + + + +
| name | age | roll_no | course |
+ + + + +
| prisha | 20 | 1 |
| pratik | 21 | Computer |
| nishan | 21 | 2 |
| Hardik | 21 | Computer |
| Hardik | 21 | 3 | IT | 5
+ + + | E&TC |
5 | Computer |
+ +
5 rows in set (0.00 sec)
mysql> select * from student where roll_no IN (select roll_no from
studCourse);
+ + + + + +
| roll_no | name | address | phone | age |
+ + + + + +
| 1 | prisha | Anagar | 9421898177 | 20 |
| 2 | pratik | pune | 9022899970 | 21 |
| 3 | nishan | pune | 8600917765 | 21 |
| 4 | Nirav | Dhule | 7689001230 | 19 |
| 5 | Hardik | pune | 9993455670 | 21 |
+ + + + + +
5 rows in set (0.00 sec)
 mysql> select * from student where roll_no in (select roll_no from
studCourse where [Link] < 20);
+ + + + + +
| roll_no | name | address | phone | age |
+ + + + + +
| 4 | Nirav | Dhule | 7689001230 | 19 |
+ + + + + +
1 row in set (0.01 sec)
mysql> select * from student left outer join studCourse on
student.roll_no = studCourse.roll_no;
+ + + + + + + +
+
| roll_no | name | address | phone | age | cid | roll_no | course
|
+ + + + + + + +
| 1 | prisha | Anagar | 9421898177 | 20 | 1 | | 2 1 | Computer |
| pratik | pune | 9022899970 | 21 | 2 | | 3 | 2 | Computer |
nishan | pune | 8600917765 | 21 | 2 | 3 | IT |
| 4 | Nirav | Dhule | 7689001230 | 19 | 3 | | 5 | 4 | Computer |
Hardik | pune | 9993455670 | 21 | 4 | | 5 | Hardik | 5 | E&TC
5 | Computer |
pune | 9993455670 | 21 | 6 |
NULL | NULL
| 7 | Krishna | Delhi | 9986003456 | 20 | NULL |
+
+ + + + + + +
7 rows in set (0.00 sec)

mysql> create view viewJoin as

-> select [Link],student.roll_no,[Link]
-> from student left outer join studCourse on
-> student.roll_no = studCourse.roll_no;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from viewJoin;
+ + + +
| name | roll_no | cid |
+ + + +
| prisha | | 1 | 1 | 2
pratik | | 2 |
| nishan | |
3 | 2 |
Nirav |
| Hardik | 4 | 3 |
| Hardik | 5 | 4 |
| Krishna | 5 | 6 |
+ + 7 | NULL |
+ +
7 rows in set (0.00 sec)

===================================================================
 | |

PRACTICAL No.: 5
AIM: Write a PL/SQL block to calculate fine for a library book by
accessing borrower information from the database.

Title: Unnamed PL/SQL code block: Use of Control structure and

Exception handling is mandatory.
mysql> create database
Lib; Query OK, 1 row
affected (0.04 sec)

mys
ql>
use
Lib
;
Database changed
mysql> create table borrower(rollno int primary key , name
varchar(20),
dateofIssue date, bname varchar(20), status
varchar(10)); Query OK, 0 rows affected (0.16 sec)
mysql> create table fine(rollno int, fdate date, amt int, foreign
key(rollno) references borrower(rollno));
Query OK, 0 rows affected (0.19 sec)
mysql>
desc
borrower;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| rollno | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| dateofIssue | date | YES | | NULL | |
| bname | varchar(20) | YES | | NULL | | |
| |
status | varchar(10) | YES | | NULL
+ +
+ + + + +
5 rows in set (0.01 sec)
mysql> desc fine;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| rollno | int | YES | MUL | NULL | |
| fdate | date | YES | | NULL | |
| amt | int | YES | | NULL | |
+ + + + + + +
3 rows in set (0.00 sec)
mysql> insert into borrower values(1,'abc','2022-10-
10','SEPM','I'); Query OK, 1 row affected (0.06 sec)
mysql> insert into borrower values(2,'xyz','2022-10-
15','DBMS','I'); Query OK, 1 row affected (0.04 sec)
mysql> insert into borrower values(3,'pqr','2022-10-
20','TOC','I'); Query OK, 1 row affected (0.03 sec)

mysql> insert into borrower values(4,'jkl','2022-10-

25','SPM','I'); Query OK, 1 row affected (0.04 sec)
mysql> insert into borrower values(5,'mno','2022-10-
05','JAVA','I');
 Query OK, 1 row affected (0.04

sec) mysql> select * from

borrower;

+ + + + + +
| rollno | name | dateofIssue | bname | status |
+ + + + + +
| 1 | abc | 2022-10-10 | SEPM | I |
| 2 | xyz | 2022-10-15 | DBMS | I |
| 3 | pqr | 2022-10-20 | TOC | I |
| 4 | jkl | 2022-10-25 | SPM | I |
| 5 | mno | 2022-10-05 | JAVA | I |
+ + + + + +
5 rows in set (0.00 sec)
mysql> select now();
+ +
| now() |
+ +
| 2022-11-10 [Link] |
+ +
1 row in set (0.00 sec)
mysql> select
curdate();
+ +
| curdate() |
+ +
| 2022-11-10 |
+ +
1 row in set (0.00 sec)
mysql> delimiter $ mysql> create procedure calc_fine_lib(in roll_new
int, in book_name varchar(20))
-> begin
-> declare fine_amt int;
-> declare diff int;
-> declare issue_date date;
-> declare exit handler for SQLEXCEPTION select 'create table
definition';
-> select dateofIssue into issue_date from borrower where rollno
= roll_new and bname = book_name;
-> select datediff(curdate(), issue_date) into diff;
-> select diff;
-> if diff>15 and diff<=30 then
-> set fine_amt = diff*5;
-> insert into fine values(roll_new,curdate(),fine_amt);
-> elseif diff>30 then
-> set fine_amt = 15*5 + (diff-30)*50;
-> insert into fine values(roll_new, curdate(),
fine_amt); -> else
-> insert into fine values(roll_new,curdate(),0);
-> end if;
-> update borrower set status = 'Returned' where rollno =
roll_new and bname = book_name; -> end $
Query OK, 0 rows affected (0.07 sec)
mysql> delimiter ; mysql>
call
calc_fine_lib(1,'SEPM');
+ +
| diff |
 | |

+ +
| 31 |
+ +
1 row in set (0.00 sec)
Query OK, 1 row affected (0.07 sec)
mysql> select * from fine;
+ + + +
| rollno | fdate | amt |
+ + + +
| 1 | 2022-11-10 | 125 |
+ + + +
1 row in set (0.00 sec)
mysql> call
calc_fine_lib(2,'DBMS');
+ +
| diff |
+ +
| 26 |
+ +
1 row in set (0.00 sec)

Query OK, 1 row affected (0.07 sec)

mysql> call calc_fine_lib(3,'TOC');

+ +
| diff |
+ +
| 21 |
+ +
1 row in set (0.00 sec)

Query OK, 1 row affected (0.06 sec)

mysql> call calc_fine_lib(5,'JAVA');

+ +
| diff |
+ +
| 36 |
+ +
1 row in set (0.00 sec)
Query OK, 1 row affected (0.07 sec)
mysql> select *
from fine;
+ + + +
| rollno | fdate | amt |
+ + + +
| 1 | 2022-11-10 | 125 |
| 2 | 2022-11-10 | 130 |
| 3 | 2022-11-10 | 105 |
| 5 | 2022-11-10 | 375 |
+ + + +
4 rows in set (0.00 sec)

mysql> select * from borrower;

+ + + + + +
| rollno | name | dateofIssue | bname | status |
+ + + + + +
| 1 | abc | 2022-10-10 | SEPM | Returned |
| 2 | xyz | 2022-10-15 | DBMS | Returned |
| 3 | pqr | 2022-10-20 | TOC | Returned |
| 4 | jkl | 2022-10-25 | SPM | I |
| 5 | mno | 2022-10-05 | JAVA | Returned |
+ + + + + +
5 rows in set (0.00 sec)
====================================================================
 | |

PRACTICAL No.: 6
Aim: To Study and implement PL/SQL programming along with Procedures and
Functions.
Title: Named PL/SQL Block: PL/SQL Stored Procedure and Stored
Function.

Title: Implement PL/SQL Stored Procedure and Stored Function. Write

a Stored Procedure for the categorization of student as per grade.

mysql> create database

DBMS6; Query OK, 1 row
affected (0.04 sec)
mysql>
use
DBMS6
;
Datab
ase
chang
ed

mysql> create table stud_marks(rollno int,name

varchar(20),total_marks int); Query OK, 0 rows affected (0.52 sec)
mysql> create table result(rollin int ,name varchar(20),class
varchar(20));
Query OK, 0 rows affected (0.68 sec)

mysql> desc stud_marks;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| rollno | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| total_marks | int | YES | | NULL | |
+ + + + + + +
3 rows in set (0.03 sec)
mysql> desc result;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
 + + + + + + +
| rollin | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
+ + + + + + +
3 rows in set (0.00 sec)

mysql> insert into stud_marks values(1,'ABC',1400);

Query OK, 1 row affected (0.03 sec)
mysql> insert into stud_marks values(2,'DEF',980);
Query OK, 1 row affected (0.06 sec)

mysql> insert into stud_marks values(3,'XYZ',880);

Query OK, 1 row affected (0.07 sec)
mysql> insert into stud_marks values(4,'JKL',820);
Query OK, 1 row affected (0.04 sec)

mysql> insert into stud_marks values(5,'OPQ',720);

Query OK, 1 row affected (0.04 sec)
mysql> select * from stud_marks;

+ + + +
| rollno | name | total_marks |
+ + + +
| 1 | ABC | 1400 |
| 2 | DEF | 980 |
| 3 | XYZ | 880 |
| 4 | JKL | 820 |
| 5 | OPQ | 720 |
+ + + +
5 rows in set (0.00 sec)
mysql>
delimit
er //
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.08 sec)
mysql> create function
final_result(roll_new int)
 | |

-> returns int

-> deterministic
-> begin
-> declare fmarks integer;
-> declare grade varchar(20);
-> declare stud_name varchar(20);
-> select stud_marks.total_marks, stud_marks.name into
fmarks,stud_name from stud_marks where stud_marks.rollno=roll_new;
-> call proc_result(fmarks,@grade);
-> insert into result values(roll_new,stud_name,@grade);
-> return roll_new;
-> end;
-> //
Query OK, 0 rows affected (0.05 sec)
mysql> select * from
stud_marks; -> //
+ + + +
| rollno | name | total_marks |
+ + + +
| 1 | ABC | 1400
| 2 | DEF | | 980
| 3 | XYZ | |
| 4 | JKL | 880 |
| 5 | OPQ | 820 |
+ 720 |
+ +
+
5 rows in set (0.00 sec)
mysql> select
final_result(2);
-> //
+ +
| final_result(2) |
+ +
2 | |
+ +
1 row in set (0.05 sec)
mysql> select *
from result;
-> //
+ + + +
| rollin | name | class |
+ + + +
| 2 | DEF | First Class |
+ + + +
1 row in set (0.00 sec)
mysql> select
final_result(1); -
> //
+ +
| final_result(1) |
+ +
1 | |
+ +
1 row in set (0.08 sec)
mysql> select
final_result(3); //
 + +
| final_result(3) |
+ +
3 | |
+ +
1 row in set (0.06 sec)

mysql> select final_result(4); //

+ +
| final_result(4) |
+ +
4 | |
+ +
1 row in set (0.06 sec)
mysql> select *
from result; ->
//
+ + + +
| rollin | name | class |
+ + + +
| 2 | DEF | First Class |
| 1 | ABC | Distincton |
| 3 | XYZ | Higher Second Class |
| 4 | JKL | Second Class |
+ + + +
4 rows in set (0.00 sec)

====================================================================
==========
| |

PRACTICAL No.: 7
AIM: To study PL/SQL programming using cursors and to apply the
same in procedure.
TITLE: Design the Database Cursor. Write a PL/SQL block to
calculate the grade of students. Write function for the same
using cursors

PROBLEM STATEMENT: Cursors: Write a PL/SQL block of code using

parameterized Cursor that will merge the data available in the newly
created table N_RollCall with the data available in the table
O_RollCall. If the data in the first table already exist in the second
table then that data should be skipped.

mysql> create database

DBMS7; Query OK, 1 row
affected (0.77 sec)

mysql> use
DBMS7;
Database
changed
mysql> create table O_RollCall(rno int primary key, name varchar(20), addr
varchar(20));
Query OK, 0 rows affected (0.31 sec)

mysql> create table N_RollCall(rno int, name varchar(20), addr

varchar(20)); Query OK, 0 rows affected (0.42 sec)

mysql> desc O_RollCall;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| rno | int | NO | PRI | NULL | name | | |
varchar(20) | YES | | NULL | addr | | |
varchar(20) | YES | | NULL | |
+ + + + + + +
3 rows in set (0.01 sec)
mysql> desc N_RollCall;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| rno | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| addr | varchar(20) | YES | | NULL | |
+ + + + + + +
3 rows in set (0.00 sec)
mysql> insert into O_RollCall
values(1,'HARDIK','NASHIK'); Query OK, 1 row
affected (0.17 sec)

mysql> insert into O_RollCall

values(3,'VIVEK','ANAGAR'); Query OK, 1 row
affected (0.09 sec)

mysql> insert into O_RollCall

values(2,'NITIN','DHULE'); Query OK, 1 row
affected (0.07 sec)
mysql> insert into O_RollCall
values(4,'NISHA','MUMBAI'); Query OK, 1 row
affected (0.18 sec)

mysql> insert into O_RollCall

values(5,'PALLAVI','PUNE'); Query OK, 1 row
affected (0.15 sec)

mysql> select * from O_RollCall;

+ + + +
| rno | name | addr |
+ + + +
| 1 | HARDIK | NASHIK |
| 2 | NITIN | DHULE |
| 3 | VIVEK | ANAGAR |
| 4 | NISHA | MUMBAI |
| 5 | PALLAVI | PUNE |
+ + + +
5 rows in set (0.00 sec)

mysql> insert into N_RollCall

values(1,'HARDIK','NASIK'); Query OK, 1 row
affected (0.10 sec)

mysql> insert into N_RollCall

values(2,'NITIN','DHULE'); Query OK, 1 row
affected (0.10 sec)

mysql> insert into N_RollCall

values(3,'VIVEK','ANAGAR'); Query OK, 1 row
affected (0.10 sec)

mysql> select * from N_RollCall;

+ + + +
| rno | name | addr |
+ + + +
| 1 | HARDIK | NASIK |
| 2 | NITIN | DHULE |
| 3 | VIVEK | ANAGAR |
+ + + +
3 rows in set (0.00

sec) mysql>

delimiter //

mysql> create procedure n1(IN rno1 int)

-> begin
-> declare rno2 int;
-> declare exit_cond boolean;
-> declare c1 cursor for select rno from O_RollCall where rno >
rno1;
-> declare continue handler for not found set exit_cond = TRUE;
-> open c1;
-> L1:loop
-> fetch c1 into rno2;
-> if not exists (select * from N_RollCall where rno = rno2)
then
-> insert into N_RollCall select * from O_RollCall where rno =
rno2;
-> end if;
| |

-> if exit_cond then

-> close c1;
-> leave L1;
-> end if;
-> end loop L1;
-> end;
-> //
Query OK, 0 rows affected (0.28 sec)

mysql>
delimiter ;
mysql> call
n1(3);
Query OK, 0 rows affected (1.16 sec)

mysql> select * from N_RollCall;

+ + + +
| rno | name | addr |
+ + + +
| 1 | HARDIK | NASIK |
| 2 | NITIN | DHULE |
| 3 | VIVEK | ANAGAR |
| 4 | NISHA | MUMBAI |
| 5 | PALLAVI | PUNE |
+ + + +
5 rows in set (0.00 sec)
====================================================================
 PRACTICAL No.: 8
AIM: To study and implement the database Trigger.

Title: Write PL/SQL block using all types of triggers on

Library table.

mysql> create database

DBMS8; Query OK, 1 row
affected (0.12 sec)

mysql> use
DBMS8;
Database
changed
mysql> create table stud(sid int primary key, name
varchar(20), class varchar(20), age int, issued char(1));
Query OK, 0 rows affected (0.57 sec)

mysql> create table returned(sid int, bname varchar(20),author

varchar(20)); Query OK, 0 rows affected (0.54 sec)

mysql> insert into stud values(1,'NEHA','COMP-

A',19,'I'); Query OK, 1 row affected (0.06 sec)

mysql> create table issued(sid int, bname

varchar(20)); Query OK, 0 rows affected (0.97
sec)

mysql> alter table stud add column bname varchar(20);

Query OK, 0 rows affected (0.71
sec) Records: 0 Duplicates: 0
Warnings: 0

mysql> desc stud;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| sid | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| |
| issued | char(1) | YES | | NULL
| |
| bname | varchar(20) | YES | | NULL
+ +
+ + + + +
6 rows in set (0.01 sec)
mysql> delimiter //
mysql> create trigger tr_ins_stud
-> before insert
-> on stud
-> for each row
-> begin
-> insert into issued values([Link],[Link]);
-> end;
-> //
Query OK, 0 rows affected (0.15 sec)

mysql> delimiter ;
mysql> insert into stud values(2,'KRUSHNA','ELECTRICAL-
A',21,'I','POWER_SUPPLY');
Query OK, 1 row affected (0.10 sec)
| |

mysql> select * from issued;

+ + +
| sid | bname |
+ + +
| 2 | POWER_SUPPLY |
+ + +
1 row in set (0.00 sec)
mysql> delimiter //
mysql> create trigger tr_del_stud
->
after
delete
-> on
stud
-> for each row
-> begin
-> delete from issued where sid = [Link];
-> end;
-> //
Query OK, 0 rows affected (0.21 sec)

mysql> delimiter ; mysql>

delete from stud where sid
= 2; Query OK, 1 row
affected (0.06 sec)

mysql> select * from

issued; Empty set
(0.00 sec)

mysql> delimiter //
mysql> create trigger tr_up_issued
->
after
update
-> on
stud
-> for each row
-> begin
-> update issued
-> set sid = [Link] where sid = [Link];
-> end;
-> //
Query OK, 0 rows affected (0.14 sec)

mysql> delimiter ;
mysql> update stud set sid = 4 where sid = 2;
Query OK, 0 rows affected (0.00
sec) Rows matched: 0 Changed:
0 Warnings: 0

mysql> select * from stud;

+ + + + + + +
| sid | name | class | age | issued | bname |
+ + + + + + +
| 1 | NEHA | COMP-A | 19 | I | NULL |
+ + + + + + +
1 row in set (0.00 sec)
mysql> select * from
issued; Empty set
(0.00 sec)

mysql> insert into stud values(2,'KRUSHNA','ELECTRICAL-

A',21,'I','POWER_SUPPLY');
Query OK, 1 row affected (0.06 sec)

mysql> select * from stud;

+ + + + + + +
| sid | name | class | age | issued | bname |
+ + + + + + +
| 1 | NEHA | COMP-A | 19 | I | NULL |
| 2 | KRUSHNA | ELECTRICAL-A | 21 | I | POWER_SUPPLY |
+ + + + + + +
2 rows in set (0.00 sec)
mysql> update stud set sid = 3 where sid = 2;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from stud;

+ + + + + + +
| sid | name | class | age | issued | bname |
+ + + + + + +
| 1 | NEHA | COMP-A | 19 | I | NULL |
| 3 | KRUSHNA | ELECTRICAL-A | 21 | I | POWER_SUPPLY |
+ + + + + + +
2 rows in set (0.00 sec)
mysql> select * from issued;
+ + +
| sid | bname |
+ + +
| 3 | POWER_SUPPLY |
+ + +
1 row in set (0.00

sec) mysql> show

triggers;

+ + + +
+ + +
+ + +
+ +
| Trigger | Event | Table | Statement
| Timing | Created | sql_mode
| Definer | character_set_client | collation_connection |
Database Collation |
+ + + +
+ + +
+ + +
+ +
| tr_ins_stud | INSERT | stud | begin insert
into issued values([Link],[Link]);
end | BEFORE | 2022-11-11 [Link].31 |
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | cp85
| cp850_general_ci |
utf8mb4_0900_ai_ci | |
tr_up_issued | UPDATE | stud |
begin update issued
 | |

set sid = [Link] where sid = [Link];

end | AFTER | 2022-11-11 [Link].15
|
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost
| cp850
| cp850_general_ci |
utf8mb4_0900_ai_ci | | tr_del_stud
| DELETE | stud | begin delete from
issued where sid = [Link];
end | AFTER | 2022-11-11
[Link].16 |
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost
| cp850 | cp850_general_ci | utf8mb4_0900_ai_ci |
+ + + +
+ + +
+ + +
+ +
3 rows in set (0.10 sec)

NOTE:

tr_ins_stud – insert (before) – on insertion on stud, it auto insert on issued

table

tr_del_stud – delete (after) – after a student record is deleted from stud, it

is then also deleted from issued

tr_up_issued – update (after) – on change of sid in stud, it auto changes all

entries of the old sid with new sid
------------------------------------------PRACTICAL No. 9------------------------------------

AIM: To connect front end tool from Mongo DB database

connectivity.

Title: Write a program to implement MogoDB database connectivity

and implement Database navigation operations (add, delete, edit
etc.).

TITLE: Implement MYSQL/Oracle database connectivity with PHP/

python/Java Implement Database navigation operations (add, delete,
edit,) using ODBC/JDBC.
------------------------------------------------------------------------------------------
import [Link].*;
import [Link].*;
import [Link];
import [Link];
import [Link];
import [Link];
import [Link].*;
public class student extends JFrame implements
ActionListener{

JFrame f;
JLabel l1, l2,l3,l4;
JTextField t1, t2,t3;
JButton b1, b2, b3, b4, b5;
Connection c;
Statement s;
ResultSet r;
student ()

{try{
f=new JFrame("Student Form");
[Link](null);[Link](true);
[Link](700, 500);

l4=new JLabel("Student Management System");

//[Link](100,01,250,250);
[Link](100, 30, 400, 30);
[Link](l4);
[Link]([Link]);
[Link](new Font("Serif", [Link],
30));
| |

l1=new
JLabel("Stud_RollNo");[Link](50,
70, 100, 50);
[Link](l1);
l2=new JLabel("Stud_Name");
[Link](50, 120, 100, 50);
[Link](l2);
l3=new JLabel("Stud_Dept");
[Link](50, 170, 100, 50);
[Link](l3);
t1=new JTextField();
[Link](150, 90, 100, 30);
[Link](t1);
t2=new JTextField();
[Link](150, 140, 100, 30);
[Link](t2);t3=new JTextField();
[Link](150, 190, 100, 30);
[Link](t3);
b1= new JButton("ADD");
[Link](200, 300, 75, 50);
[Link](b1);
[Link](this); b2=
new JButton("EDIT");
[Link](300, 300, 75, 50);
[Link](b2);
[Link](this);
b3= new JButton("DELETE");[Link](400, 300, 75, 50);
[Link](b3);
[Link](this); b5=
new JButton("EXIT");
[Link](500, 300, 75, 50);
[Link](b5);
[Link](this);
[Link]("[Link]");
c=[Link]("jdbc:mysql://loca
lhost:3306/info","root","root");
 | |

s=[Link]();
}catch(Exception e){[Link](e);}
}//ends INS Constructor
public void actionPerformed(ActionEvent ae){
try{
if([Link]()==b1){String s1="INSERT
INTO result(stud_RollNo,stud_Name,stud_Dept)
VALUES("+[Link]()+",'"+[Link]()

+"','"+[Link]() + "')";
[Link](s1);
[Link](s1);
r=[Link]("SELECT * FROM result");
[Link]("");

[Link]("");
[Link]("");
}else if([Link]()==b2){
String s2="UPDATE user1 SET
stud_Name='"+[Link]()+"' WHERE
stud_RollNo="+[Link]();
[Link](s2);
[Link](s2);
r=[Link]("SELECT * FROM result");
[Link]("");
[Link]("");[Link]("");
}else if([Link]()==b3){
String s3="DELETE FROM result WHERE
stud_RollNo="+[Link]();
[Link](s3);
[Link](s3);
r=[Link]("SELECT * FROM result");
[Link]("");

[Link]("");
[Link]("");}else if([Link]()==b5){
[Link](0); }
}catch(Exception e){[Link](e);}
}
public static void main(String args[]){
new student();

}
}

----------------------------------- Output --------------------------------------

sl2-pc5@sl2pc5-HP-Compaq-4000-Pro-SFF-PC:~$
mysql -u root -p

Enter password:
Welcome to the MySQL monitor. Commands end with
; or \g.
Your MySQL connection id is 42
Server version: 5.5.61-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates.
All rights reserved.

Oracle is a registered trademark of Oracle

Corporation and/or itsaffiliates. Other names may be trademarks oftheir
respective

owners.
Type 'help;' or '\h' for help. Type '\c' to
clear the current input statement.

mysql> create database info;

Query OK, 1 row affected (0.03 sec)
mysql> use info;

Database changed
mysql> create table result (stud_RollNo
int,stud_Name varchar(20),stud_Dept
varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> select *from result;

+-------------+-----------+-----------+
 | |

| stud_RollNo | stud_Name | stud_Dept |

+-------------+-----------+-----------+
|
1 | abc
| comp
|
+-------------+-----------+-----------+
1 row in set (0.00 sec)
//ADD DATA
mysql> select *from result;
+-------------+-----------+-----------+
| stud_RollNo | stud_Name | stud_Dept |
+-------------+-----------+-----------+
|
1 | abc
| comp
|
|
2 | harsha
| comp
|
|
3 | tej
| comp
|
|
4 | rina
| mech
|
+-------------+-----------+-----------+4 rows in set (0.00 sec)
//DELETE DATA
mysql> select *from result;
+-------------+-----------+-----------+
 | |

------------------------------------PRACTICAL No. 10 ----------------------------------------

AIM: To Design and Implement MongoDB queries using CRUD operations.
TITLE: Write and execute simple queries that demonstrate usage of
MongoDB database.

TITLE: Study of Open Source NOSQL Database: MongoDB (Installation,

Basic CRUD operations, Execution)
------------------------------------------------------------------------------------------
sl1-pc6@sl1pc6-HP-dx2480-MT-VP562PA:~$ mongo
MongoDB shell version: 2.6.10
connecting to: test
Server has startup warnings:
2018-09-05T[Link].932+0530 [initandlisten]
2018-09-05T[Link].932+0530 [initandlisten] ** NOTE: This is
a 32 bit MongoDB binary.
2018-09-05T[Link].932+0530 [initandlisten] ** 32 bit
builds are limited to less than 2GB of data (or less with
--journal).
2018-09-05T[Link].932+0530 [initandlisten] ** See
[Link]
2018-09-05T[Link].932+0530 [initandlisten]
> use Abhi;
switched to db Abhi
> [Link]('Student');
{ "ok" : 1 }
> [Link]({'Rno':'1','Name':'Piyush','Class':'TE
COMP'});
WriteResult({ "nInserted" : 1 })
> [Link]({'Rno':'2','Name':'Abhi','Class':'TE
COMP'});
WriteResult({ "nInserted" : 1 })
> [Link]({'Rno':'3','Name':'Ashley','Class':'TE
COMP'});
WriteResult({ "nInserted" : 1 })
> [Link]({'Rno':'4','Name':'Hitesh','Class':'TE
COMP'});
WriteResult({ "nInserted" : 1 })
> [Link]({'Rno':'5','Name':'Pratik','Class':'TE
COMP'});
WriteResult({ "nInserted" : 1 })
> [Link]({'Rno':'6','Name':'Pratik','Class':'TE
COMP'});
WriteResult({ "nInserted" : 1 })
> [Link]();
{ "_id" : ObjectId("5b8fad4ef00832a0a50b5036"), "Rno" : "1",
"Name" : "Piyush", "Class" : "TE COMP" }
{ "_id" : ObjectId("5b8fad62f00832a0a50b5037"), "Rno" : "2",
"Name" : "Abhi", "Class" : "TE COMP" }
{ "_id" : ObjectId("5b8fad70f00832a0a50b5038"), "Rno" : "3",
"Name" : "Ashley", "Class" : "TE COMP" }
{ "_id" : ObjectId("5b8fad7ff00832a0a50b5039"), "Rno" : "4",
"Name" : "Hitesh", "Class" : "TE COMP" }
{ "_id" : ObjectId("5b8fad8df00832a0a50b503a"), "Rno" : "5",
"Name" : "Pratik", "Class" : "TE COMP" }
{ "_id" : ObjectId("5b8fada4f00832a0a50b503b"), "Rno" : "6",
"Name" : "Pratik", "Class" : "TE COMP" }
> [Link]().pretty();
{
"_id" : ObjectId("5b8fad4ef00832a0a50b5036"),
"Rno" : "1",
"Name" : "Piyush",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad62f00832a0a50b5037"),
"Rno" : "2",
"Name" : "Abhi",
"Class" : "TE COMP"
| |

}
{
"_id" : ObjectId("5b8fad70f00832a0a50b5038"),
"Rno" : "3",
"Name" : "Ashley",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad7ff00832a0a50b5039"),
"Rno" : "4",
"Name" : "Hitesh",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad8df00832a0a50b503a"),
"Rno" : "5",
"Name" : "Pratik",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fada4f00832a0a50b503b"),
"Rno" : "6",
"Name" : "Pratik",
"Class" : "TE COMP"
}
> show dbs;
Abhi 0.078GB
admin (empty)
local 0.078GB
> [Link]({'Name':'Hitesh'},{$set:
{'Name':'Henry'}});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1
})
> [Link]().pretty();
{
"_id" : ObjectId("5b8fad4ef00832a0a50b5036"),
"Rno" : "1",
"Name" : "Piyush",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad62f00832a0a50b5037"),
"Rno" : "2",
"Name" : "Abhi",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad70f00832a0a50b5038"),
"Rno" : "3",
"Name" : "Ashley",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad7ff00832a0a50b5039"),
"Rno" : "4",
"Name" : "Henry",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad8df00832a0a50b503a"),
"Rno" : "5",
"Name" : "Pratik",
"Class" : "TE COMP"
| |

}
{
"_id" : ObjectId("5b8fada4f00832a0a50b503b"),
"Rno" : "6",
"Name" : "Pratik",
"Class" : "TE COMP"
}
> [Link]({'ADD':'MP'});
WriteResult({ "nRemoved" : 1 })
> [Link]().pretty();
{
"_id" : ObjectId("5b8fad62f00832a0a50b5037"),
"Rno" : "2",
"Name" : "Abhi",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad70f00832a0a50b5038"),
"Rno" : "3",
"Name" : "Ashley",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad7ff00832a0a50b5039"),
"Rno" : "4",
"Name" : "Henry",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad8df00832a0a50b503a"),
"Rno" : "5",
"Name" : "Pratik",
 "Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fada4f00832a0a50b503b"),
"Rno" : "6",
"Name" : "Pratik",
"Class" : "TE COMP"
}
> [Link]({'Name':'Pratik'},1);
WriteResult({ "nRemoved" : 1 })
> [Link]({'Name':'Pratik'},1);
WriteResult({ "nRemoved" : 1 })
> [Link]().pretty();
{
"_id" : ObjectId("5b8fad62f00832a0a50b5037"),
"Rno" : "2",
"Name" : "Abhi",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad70f00832a0a50b5038"),
"Rno" : "3",
"Name" : "Ashley",
"Class" : "TE COMP"
}
{
"_id" : ObjectId("5b8fad7ff00832a0a50b5039"),
"Rno" : "4",
"Name" : "Henry",
"Class" : "TE COMP"
}
> [Link]();
| |

true
> [Link]().pretty();
 | |

--------------------------ASSIGNMENT NO. 11--------------------------------------

AIM: To study and implement indexing and aggregation in MongoDB.
TITLE: Write and execute simple queries to perform indexing and aggregation.

TITLE: Implement aggregation and indexing with suitable example using

MongoDB.
------------------------------------------------------------------------------------------
//USE MONGODB
sl1-pc5@sl1pc5-dx2480-MT:~$ mongo
MongoDB shell version: 2.6.10
connecting to: test
Server has startup warnings:
2018-09-20T[Link].599+0530 [initandlisten]
2018-09-20T[Link].599+0530 [initandlisten] ** NOTE: This is
a 32 bit MongoDB binary.
2018-09-20T[Link].599+0530 [initandlisten] **
32 bit builds are limited to less than 2GB
of data (or less with --journal).
2018-09-20T[Link].599+0530 [initandlisten] **
See [Link]
2018-09-20T[Link].599+0530 [initandlisten]
//USE DATABASE
> use comp;
switched to db comp
//CREATE COLLECTION WEBSITE
> [Link]('website');
{ "ok" : 1 }
//INSERT VALUES IN WEBSITE
>
[Link]({'roll':'1','name':'harsh','amount':1000,'ur
l':'[Link]'});
WriteResult({ "nInserted" : 1 })
>
[Link]({'roll':'2','name':'jitesh','amount':2000,'u
rl':'[Link]'});
WriteResult({ "nInserted" : 1 })
>
[Link]({'roll':'3','name':'rina','amount':3000,'url
':'[Link]'});
WriteResult({ "nInserted" : 1 })
>
[Link]({'roll':'4','name':'ash','amount':4000,'url'
:'[Link]'});
WriteResult({ "nInserted" : 1 })
>
[Link]({'roll':'5','name':'ash','amount':1000,'url'
:'[Link]'});WriteResult({ "nInserted" : 1 })
//SUM AGGREGATE
> [Link]({$group:{_id:"$name","total":
{$sum:"$amount"}}});
{ "_id" : "ash", "total" : 5000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "harsh", "total" : 2000 }
//AVG AGGREGATE
> [Link]({$group:{_id:"$name","total":
{$avg:"$amount"}}});
{ "_id" : "ash", "total" : 2500 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "harsh", "total" : 1000 }
//MIN AGGREGATION
> [Link]({$group:{_id:"$name","total":
{$min:"$amount"}}});
{ "_id" : "ash", "total" : 1000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "harsh", "total" : 1000 }
//MAX AGGREGATION
> [Link]({$group:{_id:"$name","total":
{$max:"$amount"}}});
| |

{ "_id" : "ash", "total" : 4000 }

{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "harsh", "total" : 1000 }
//FIRST AGGREGATION
> [Link]({$group:{_id:"$name","total":
{$first:"$amount"}}});
{ "_id" : "ash", "total" : 4000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "harsh", "total" : 1000 }//LAST AGGREGATION
> [Link]({$group:{_id:"$name","total":
{$last:"$amount"}}});
{ "_id" : "ash", "total" : 1000 }
{ "_id" : "rina", "total" : 3000 }
{ "_id" : "jitesh", "total" : 2000 }
{ "_id" : "harsh", "total" : 1000 }
//PUSH AGGREGATION
> [Link]({$group:{_id:"$name","total":
{$push:"$amount"}}});
{ "_id" : "ash", "total" : [ 4000, 1000 ] }
{ "_id" : "rina", "total" : [ 3000 ] }
{ "_id" : "jitesh", "total" : [ 2000 ] }
{ "_id" : "harsh", "total" : [ 1000, 1000 ] }
//COUNT AGGREGATION
> [Link]({$group:{_id:"$name","total":
{$sum:1}}});
{ "_id" : "ash", "total" : 2 }
{ "_id" : "rina", "total" : 1 }
{ "_id" : "jitesh", "total" : 1 }
{ "_id" : "harsh", "total" : 2 }
//ADDTOSET AGGREGATE
> [Link]({$group:{_id:"$name","total":
{$addToSet:"$amount"}}});
{ "_id" : "ash", "total" : [ 1000, 4000 ] }
{ "_id" : "rina", "total" : [ 3000 ] }
{ "_id" : "jitesh", "total" : [ 2000 ] }
{ "_id" : "harsh", "total" : [ 1000 ] }
//INDEXING
> [Link]('website1');
{ "ok" : 1 }
> [Link]({'r':1,'name':'harsh'});
WriteResult({ "nInserted" : 1 })
> [Link]().pretty()
{ "_id" : ObjectId("5ba3509a444926329738012d"), "roll" : 1,
"name" : "harsh" }
{ "_id" : ObjectId("5ba35293444926329738012e"), "roll" : 1,
"name" : "harsh" }
> [Link]({'name':1})
{ "numIndexesBefore" : 2, "note" : "all indexes already
exist", "ok" : 1 }//CREATE INDEXING
> [Link]({'name':-1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> [Link]()
2018-09-20T[Link].628+0530 TypeError: Property 'getIndexses'
of object [Link] is not a
function
> [Link]()
[
{
| |

"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "harsh.website1"
},
{
"v" : 1,
"key" : {
"name" : 1
},
"name" : "name_1",
"ns" : "harsh.website1"
},
{
"v" : 1,
"key" : {
"name" : -1
},
"name" : "name_-1",
"ns" : "harsh.website1"
}
]
> [Link]({'name':-1})
{ "numIndexesBefore" : 3, "note" : "all indexes already
exist", "ok" : 1 }
//DROP INDEX
> [Link]({'name':-1})
{ "nIndexesWas" : 3, "ok" : 1 }>
[Link]({'name':1})
{ "nIndexesWas" : 2, "ok" : 1 }
> [Link]({'name':1})
{
"nIndexesWas" : 1,
"ok" : 0,
"errmsg" : "can't find index with key:{ name: 1.0 }"
}
//GET INDEXING
> [Link]()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "harsh.website1"
}
]
> [Link]().pretty()
{ "_id" : ObjectId("5ba3509a444926329738012d"), "roll" : 1,
"name" : "harsh" }
{ "_id" : ObjectId("5ba35293444926329738012e"), "roll" : 1,
"name" : "harsh" }
>
> [Link]({'name':1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> [Link]()
| |

[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "harsh.website1"
},
{
"v" : 1,
"key" : {"name" : 1
},
"name" : "name_1",
"ns" : "harsh.website1"
}
]
> [Link]({'name':1})
{ "nIndexesWas" : 2, "ok" : 1 }
> [Link]()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "harsh.website1"
}
]
> [Link]({'name':1,'r':-1})
{
 "createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> [Link]()
[
{
"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"
}
] (i-search)`[Link]({'roll':1,'name':'harsh'});':

OUTPUT:
| |
---------------------------ASSIGNMENT NO. 12 ----------------------------------
AIM: To study and implement MapReduce operation in MongoDB.
TITLE: Write and execute simple queries to demonstrate MapReduce operation.

TITLE: Implement Map reduces operation with suitable example using

MongoDB.
------------------------------------------------------------------------------------------
sl1-pc6@sl1pc6-HP-dx2480-MT-VP562PA:~$ mongo
MongoDB shell version: 2.6.10
connecting to: test
Server has startup warnings:
2018-09-26T[Link].854+0530 [initandlisten]
2018-09-26T[Link].854+0530 [initandlisten] ** NOTE: This is
a 32 bit MongoDB binary.
2018-09-26T[Link].855+0530 [initandlisten] ** 32 bit
builds are limited to less than 2GB of data (or less with
--journal).
2018-09-26T[Link].855+0530 [initandlisten] ** See
[Link]
2018-09-26T[Link].855+0530 [initandlisten]
> use Abhi
switched to db Abhi
> [Link]('Journal');
{ "ok" : 1 }
> [Link]({'book_id':1,'book_name':'Javacd
OOP','amt':500,'status':'Available'});
WriteResult({ "nInserted" : 1 })
> [Link]({'book_id':1,'book_name':'Java
OOP','amt':400,'status':'Not Available'});
WriteResult({ "nInserted" : 1 })
>
[Link]({'book_id':1,'book_name':'Java','amt':300,'s
| |

tatus':'Not Available'});
WriteResult({ "nInserted" : 1 })
>
[Link]({'book_id':2,'book_name':'Java','amt':300,'s
tatus':'Available'});
WriteResult({ "nInserted" : 1 })
>
[Link]({'book_id':2,'book_name':'OPP','amt':200,'st
atus':'Available'});
WriteResult({ "nInserted" : 1 })
> [Link]({'book_id':2,'book_name':'C+
+','amt':200,'status':'Available'});
WriteResult({ "nInserted" : 1 })
> [Link]({'book_id':3,'book_name':'C+
+','amt':150,'status':'Available'});
WriteResult({ "nInserted" : 1 })
> [Link]({'book_id':3,'book_name':'C+
+','amt':200,'status':'Not Available'});
WriteResult({ "nInserted" : 1 })
> [Link]({'book_id':4,'book_name':'OPP C+
+','amt':300,'status':'Not Available'});
WriteResult({ "nInserted" : 1 })
> [Link]({'book_id':5,'book_name':'OPP C+
+','amt':400,'status':'Available'});
WriteResult({ "nInserted" : 1 })
> [Link]({'book_id':5,'book_name':'C+
+','amt':400,'status':'Available'});
WriteResult({ "nInserted" : 1 })
> [Link]({'book_id':5,'book_name':'C++
Java','amt':400,'status':'Not Available'});
WriteResult({ "nInserted" : 1 })
>
>
>
> var mapfunction=function(){ emit(this.book_id,[Link])};
> var reducefunction=function(key,value){return
[Link](value);};
> [Link](mapfunction,reducefunction,
{'out':'new'});
{
"result" : "new",
"timeMillis" : 49,
 | |

"counts" : {
"input" : 12,
"emit" : 12,
"reduce" : 4,
"output" : 5
},
"ok" : 1
}
> [Link](mapfunction,re
ducefunction,
{'out':'new'}).find().pretty();
{ "_id" : 1, "value" : 1200 }
{ "_id" : 2, "value" : 700 }
{ "_id" : 3, "value" : 350 }
{ "_id" : 4, "value" : 300 }
{ "_id" : 5, "value" : 1200 }
>
>
> [Link]().pretty();
{ "_id" : 1, "value" : 1200 }
{ "_id" : 2, "value" : 700 }
{ "_id" : 3, "value" : 350 }
{ "_id" : 4, "value" : 300 }
{ "_id" : 5, "value" : 1200 }
>

OUTPUT: