Notes

1. Insert records for 10 books from 5 authors, and at least 20 orders in total.


db.books.insert({
bookID: 'B1',
bookName: 'Wish I could tell you',
authors: [
{authorID: 'A1',authorName: 'Durjoy Datta'}
],
publicationYear: '2019',
publisher: 'Penguin Random House',
price: 120,
totalQty: 100,
pages: 246,
orders: [
{orderID: 'O14',orderDate: new Date('2019-12-02'),customerName: 'Riya Patel',price: 120,quantityOrdered: 5,discount: 5},
{orderID: 'O19',orderDate: new Date('2020-12-02'),customerName: 'Piya Patel',price: 120,quantityOrdered: 1,discount: 0}
]
})

db.books.insert({
bookID: 'B10',
bookName: 'Your Dreams are Mine Now',
authors: [
{authorID: 'A7',authorName: 'Sudeep Nagarkar'}
],
publicationYear: '2017',
publisher: 'Penguin Random House',
price: 120,
totalQty: 110,
pages: 280,
orders: [
{orderID: 'O10',orderDate: new Date('2018-12-12'),customerName: 'Parth Patel',price: 120,quantityOrdered: 5,discount: 25 },
{orderID: 'O12',orderDate: new Date('2019-11-11'),customerName: 'Alina Patel',price: 120,quantityOrdered: 10,discount: 10},
{orderID: 'O21',orderDate: new Date('2021-10-10'),customerName: 'Aarav Patel',price: 120,quantityOrdered: 1,discount: 0}
]
})


2. Update the title of a particular book.

db.books.update({bookID:'B1'},{$set: {bookName: 'Wish I Could Tell You'}})

3. Display all the books having less than 3 authors and sort by book name.

db.books.find({$expr: {$lt: [{$size: '$authors'},3]}}).sort({bookName: 1})

4. Display the number of books from each publisher.

db.books.aggregate({$group: {_id: '$publisher',total: {$sum : 1}}},{$project: {_id: 0,publisher: '$_id',totalBooksPublished: '$total'}})
for (var idx = 0; idx < this.items.length; idx++) {
var key = this.items[idx].sku;
var value = { count: 1, qty: this.items[idx].qty };
emit(key, value);
}

5.Use Map Reduce function to display the total quantity of books ordered for each date.
var map = function(){
for(var idx=0;idx<this.orders.length;idx++){
var key = this.orders[idx].orderDate;
var value = this.orders[idx].quantityOrdered;

emit(key,value);
}
}

var reduce = function(keyOrderDate,valuesquantityOrdered){
return Array.sum(valuesquantityOrdered);
}

db.books.mapReduce(map,reduce,{out: "Total_Quantity_Date_Wise"});

db.Total_Quantity_Date_Wise.find().pretty();


6. Use Map Reduce function to display the discount offered to a particular customer.

var map = function(){
for(var idx=0;idx<this.orders.length;idx++){
var key = this.orders[idx].customerName;
var totalPrice= this.orders[idx].price*this.orders[idx].quantityOrdered;
var value= (totalPrice*this.orders[idx].discount)/100;
emit(key,value);
}
}

var reduce = function(key,values){
return Array.sum(values)
}
db.books.mapReduce(map,reduce,{out: "Total_Discount_to_Customer"});

db.Total_Discount_to_Customer.find().pretty();

create database UTU;
use UTU

create table tblCity(cityId int primary key auto_increment,cityName varchar(30));
insert into tblCity(cityName) values ('Surat'),('Vapi'),('Valsad'),('Navsari'),('Baroda'),('Mumbai'),('Bardoli'),('Goa'),('Ahemdabad'),('Pune');

create table tblDepartment(did varchar(3) primary key,dname varchar(100));
insert into tblDepartment(did,dname) values('D01','Research and development'),('D02','Quality Assurance'),('D03','Sales and Marketing'),('D04','Technical Publications and Training'),('D05','Technical Support'),('D06','Human Resource Management'),('D07','Accounting and Finance');


create table tblEmployee(id int primary key,fname varchar(30),lname varchar(30),cityId int,gender char(1),designation varchar(50),salary int,departmentId varchar(3),foreign key (cityId) references tblCity(id),foreign key (departmentId) references tblDepartment(did));
insert into tblEmployee(id,fname,lname,cityId,gender,designation,salary,departmentId) values(201401,'Nehal','Patel',4,'M','Software Engineer',45000,'D04'),(201505,'Pooja','Naik',1,'F','System Analyst',58000,'D02'),(201509,'Dhruvin','Modi',1,'M','Project Lead',89500,'D06'),(201602,'Kakshil','Shah',7,'M','Trainee Engineer',38000,'D03'),(201603,'Nirav','Savani',10,'M','System Analyst',60000,'D02'),(201702,'Dhruvi','Patel',8,'F','Programmer Analyst',72000,'D04'),(201704,'Anjali','Surati',6,'F','Tester',70000,'D01'),(201706,'Nimesh','Katariya',1,'M','Software Engineer',43000,'D05'),(201807,'Urja','Naik',3,'F','Project Manager',96000,'D02'),(201808,'Sumit','Chothani',2,'M','Senior Software Engineer',62000,'D01');

1. Display all the details of employees in alphabetic order, who are from „Surat‟.
select * from tblEmployee where cityId=(select id from tblCity where cityName='surat') order by fname;
OR
select * from tblEmployee E,tblCity C where C.id=E.cityId and c.cityName='surat';
OR
select E.id,E.fname,E.lname,E.gender,E.designation,E.salary,E.departmentId,c.cityName from tblEmployee E,tblCity C where C.id=E.cityId and c.cityName='surat';

2. Display only the employee‟s names and their salary who is not having salary between 50000 to 60000.
select fname,salary from tblEmployee where salary<50000 or salary>60000;

3. Show employee first name, last name along with designation who are from Quality Assurance, Technical Support and Human Resource Management department.
select fname,lname,designation from tblEmployee where departmentId in (select did from tblDepartment where dname in ('Quality Assurance','Technical Support','Human Resource Management'));
OR
select E.fname,E.lname,E.designation from tblEmployee E,tblDepartment D where E.departmentId=D.did and D.dname in ('Quality Assurance','Technical Support','Human Resource Management');

4. Count number of females coming from „Mumbai‟ and „Goa‟.
select count(*) as 'Total Employees from Goa and Mumbai' from tblEmployee where cityId in (select id from tblCity where cityName in ('Goa','Mumbai'));
OR
select count(*) as 'Total Employees from Goa and Mumbai' from tblEmployee E,tblCity C where E.cityId=C.id and C.cityName in ('Goa','Mumbai');


5. Display all the designations of employees.
select distinct(designation) from tblEmployee;

6. Display the maximum salary among Software Engineers, System Analysts and Programmer Analysts.
select designation,max(salary) as 'Max Salary' from tblEmployee group by designation;

7. Display the list of employees working in „Technical Publications and Training‟ department and coming from „Navsari‟.
select E.fname from tblEmployee E,tblCity C,tblDepartment D where D.dname='Technical Publications and Training' and C.cityName='Navsari' and E.cityId=C.id and D.did=E.departmentId;

8. Calculate the average salary of employees who are in „Research and development‟ department and coming from „Vapi‟ and „Mumbai‟.
select avg(salary) as 'Average Salary' from tblEmployee where departmentId=(select did from tblDepartment where dname='Research and development') and cityId in (select id from tblCity where cityname in ('Vapi','Mumbai'));
OR
select avg(E.salary) as 'Average Salary' from tblEmployee E,tblCity C,tblDepartment D where D.dname='Research and development' and C.cityname in ('Vapi','Mumbai') and E.cityId=C.id and E.departmentId=D.did;

9. Assign new designations of employees as „Software Engineer‟, who are „Trainee Engineer‟ and also increase their salary by 10%.
update tblEmployee set designation="Software Engineer", salary=salary+((salary*10)/100) where designation='Trainee Engineer';

10. Remove all the information of those employees, who are coming from „Baroda‟.
delete from tblEmployee where cityId=(select id from tblCity where cityName='Baroda');

11. Show the list of employees having highest salary.
select * from tblEmployee where salary=(select max(salary) from tblEmployee);

12. Find who is having second highest salary.
select * from tblEmployee where salary=(select distinct(salary) from tblEmployee order by salary desc limit 1 offset 1);
OR
select * from tblEmployee where salary=(select distinct(salary) from tblEmployee order by salary desc limit 1,1);

13. List only the employee‟s names with salary and department‟s name having more salary than „Senior Software Engineer‟.
select E.fname,E.salary,D.dname from tblEmployee E,tblDepartment D where d.did=E.departmentId and E.salary>(select salary from tblEmployee where designation='Senior Software Engineer');

14. Display list of those cities from where, there are no employees.
select cityName from tblCity where id not in (select distinct(cityId) from tblEmployee);

15. Show the list of employee‟s names with department‟s name having two or more employees.

mysql> select E.fname,D.dname from tblEmployee E,tblDepartment D where E.departmentId=D.did and departmentId in (select did from tblEmployee E,tblDepartment D where E.departmentId=D.did group by did having count(*)>=2);