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 |
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.