MongoDB Answers

From mi-linux
Jump to navigationJump to search

Main Page >> MongoDB >>MongoDB Workbook >> Exercise Answers

You should only look at the answers once you have attempted them yourself!


Answers to Section 2

Exercise 2.1

  • Compare how you added the above data and how it differs from INSERT records in a relational database

A SQL INSERT statement for department 30 would be:

INSERT INTO dept(deptno, dname, loc) VALUES (30,'SALES','CHICAGO');

Apart from the syntax differences, you could get away with not defining the field names again, for example, this would work too:

INSERT INTO dept VALUES (30,'SALES','CHICAGO');

This is because the table has already been defined, so there is a well defined schema in existence. MongoDB is schema-less, so you need to define some sort of structure as the documents are added.

  • Try and add Department 30
db.dept.insert(
 {
    _id: 30,
    deptno: 30,
    dname:  "SALES",
    loc:    "CHICAGO"
})

Exercise 2.2

  • Add the employees for Department 30.

Remember this time, some employees will have a commission (COMM):

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7499 ALLEN SALESMAN 7698 20-FEB-95 1600 300 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850   30
7900 JAMES CLERK 7698 03-DEC-81 950   30
7654 MARTIN SALESMAN 7698 28-SEP-93 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-94 1250 500 30

The following example adds all the documents at the same time, you can also produce individual insert statements for each document:

db.emp.insert( [
{
	empno: 	7499,
 	ename: 	"ALLEN",
 	job: 	"SALESMAN",
 	mgr:	7698,
	hiredate: new Date('1995-02-20'),
 	sal:	1600,
	comm:	300,
 	deptno:	30
},
{
	empno: 	7698,
 	ename: 	"BLAKE",
 	job: 	"MANAGER",
 	mgr:	7839,
 	hiredate: new Date ('1981-05-01'),
 	sal:	2850,
 	deptno:	30
},
{
	empno: 	7900,
 	ename: 	"JAMES",
 	job: 	"CLERK",
 	mgr:	7695,
 	hiredate: new Date ('1993-12-03'),
 	sal:	950,
 	deptno:	30
},
{
	empno: 	7654,
 	ename: 	"MARTIN",
 	job: 	"SALESMAN",
 	mgr:	7698,
 	hiredate: new Date('1993-09-28'),
 	sal:	1250,
	comm:	1400,
 	deptno:	30
},
{
	empno: 	7844,
 	ename: 	"TURNER",
 	job: 	"SALESMAN",
	mgr:	7698,
 	hiredate: new Date('1981-09-08'),
 	sal:	1500,
	comm:	0,
 	deptno:	30
},
{
	empno: 	7521,
 	ename: 	"WARD",
 	job: 	"SALESMAN",
 	mgr:	7698,
 	hiredate: new Date('1994-02-22'),
 	sal:	1250,
	comm:	500,
 	deptno:	30
}
])

Exercise 2.3

2.3.1 Try the $lookup function with the collections swapped:

db.emp.aggregate([ {
      $lookup:  {
          from: "dept",
          localField: "deptno",
          foreignField: "deptno",
          as: "dept"
        }
   }
 ])

Explain how the results differ to the previous example.

This is performing an outer join and displays the dept details as a nested document. In this case each employee is linked to one department only, so the dept array will contain just one document.

2.3.2 List all the documents in the dept collection, sorted by loc.

db.dept.aggregate(   [  
   {$sort: {loc:1}}   
] ).pretty()

Exercise 2.4

  • 2.4.1 Update the name of department 40 to: COMPUTING
 db.dept.update({deptno:40}, 
  {$set:  {dname: 'COMPUTING'}})


  • 2.4.1 Update the salary of employee number 7788 in department 20 to 3500
 db.emp.update({empno: 7788}, 
  {$set:  {sal: 3500}})

Check that the update has happened correctly:

db.emp.find({empno:7788})

Next Step

Answers to Section 3.