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

Try the lookup 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.

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.

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.