MongoDB Answers

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

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})

Answers to Section 3

Exercise 3.1

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

Think about how this differs from using INSERT statements in Oracle to add the data. An SQL INSERT generally only insert one record at a time to one table.

• Try and add Department 30. Note it has some values for the comm field.

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

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

Exercise 3.2

2.2.1 Update the name of department 40 to: COMPUTING

 db.deptCollection.update({deptno:40}, 
  {$set:  {dname: 'COMPUTING'}})

2.2.1 Update the salary of employee number 7788 in department 20 to 3500

db.deptCollection.update (
{ deptno: 20 , "employees.empno": 7788}, 
  { $set: {"employees.$.sal" : 3500} }  
)

Always check that your update has worked. As a clue, the system should output a WriteResult, telling you how many records were updated:

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

Next Step

Return to the Workbook.