EMP and DEPT datasets for MongoDB

Since Oracle’s “EMP” and “DEPT” datasets are useful for practice, I’ve provided converted scripts for SQL Server and MySQL in previous articles. This tutorial will guide you through the process of creating an example dataset and practice adding entries into MongoDB.

The table definition in SQL is as given below:

–Table definition – Dept
CREATE TABLE Dept(
DeptNo INT,
DName VARCHAR(14),
Loc VARCHAR(13),
CONSTRAINT pk_Dept PRIMARY KEY (DeptNo) );

— Tabe Definition – Emp: 
CREATE TABLE Emp(
EmpNo INT,
EName VARCHAR(10),
Job VARCHAR(9),
Mgr INT,
HireDate date,
Sal INT,
Comm INT,
DeptNo INT,
CONSTRAINT pk_Emp PRIMARY KEY (EmpNo),
CONSTRAINT fk_DeptNo FOREIGN KEY (DeptNo) REFERENCES Dept (DeptNo) );

Let’s implement it in MongoDB.

–Create the “Emp” and “Dept” collections

> db.createCollection(“emp”, { capped : true, size : 5242880, max : 5000 } )
> db.createCollection(“dept”, { capped : true, size : 5242880, max : 5000 } )

–Inserting records into “Dept” collection
db.dept.insert({“deptno” : 10,”dname” : “ACCOUNTING”,”loc” : “NEW YORK”})
db.dept.insert({“deptno” : 20,”dname” : “RESEARCH”,”loc” : “DALLAS”})
db.dept.insert({“deptno” : 30,”dname” : “SALES”,”loc” : “CHICAGO”})
db.dept.insert({“deptno” : 40,”dname” : “OPERATIONS”,”loc” : “BOSTON”})

–Inserting records into “Emp” collection
db.emp.insert({“empno” : 7839,”ename” : “King”,”job” : “PRESIDENT”,”mgr” : null,”hiredate” : “1981-11-17″,”sal” : 5000,”comm” : null,”deptno” : 10})
db.emp.insert({“empno” : 7698,”ename” : “Blake”,”job” : “MANAGER”,”mgr” : 7839,”hiredate” : “1981-05-01″,”sal” : 2850,”comm” : null,”deptno” : 30})
db.emp.insert({“empno” : 7782,”ename” : “Clark”,”job” : “MANAGER”,”mgr” : 7839,”hiredate” : “1981-06-09″,”sal” : 2450,”comm” : null,”deptno” : 10})
db.emp.insert({“empno” : 7566,”ename” : “Jones”,”job” : “MANAGER”,”mgr” : 7839,”hiredate” : “1981-04-02″,”sal” : 2975,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7788,”ename” : “Scott”,”job” : “ANALYST”,”mgr” : 7566,”hiredate” : “1981-04-19″,”sal” : 3000,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7902,”ename” : “Ford”,”job” : “ANALYST”,”mgr” : 7566,”hiredate” : “1981-03-12″,”sal” : 3000,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7369,”ename” : “Smith”,”job” : “CLERK”,”mgr” : 7902,”hiredate” : “1980-12-17″,”sal” : 800,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7499,”ename” : “Allen”,”job” : “SALESMAN”,”mgr” : 7698,”hiredate” : “1981-02-20″,”sal” : 1600,”comm” : 300,”deptno” : 30})
db.emp.insert({“empno” : 7521,”ename” : “Ward”,”job” : “SALESMAN”,”mgr” : 7698,”hiredate” : “1981-02-22″,”sal” : 1250,”comm” : 500,”deptno” : 30})
db.emp.insert({“empno” : 7654,”ename” : “Martin”,”job” : “SALESMAN”,”mgr” : 7698,”hiredate” : “1981-09-28″,”sal” : 1250,”comm” : 1400,”deptno” : 30})
db.emp.insert({“empno” : 7844,”ename” : “Turner”,”job” : “SALESMAN”,”mgr” : 7698,”hiredate” : “1981-09-08″,”sal” : 1500,”comm” : 0,”deptno” : 30})
db.emp.insert({“empno” : 7876,”ename” : “Adams”,”job” : “CLERK”,”mgr” : 7788,”hiredate” : “1987-05-23″,”sal” : 1100,”comm” : null,”deptno” : 20})
db.emp.insert({“empno” : 7900,”ename” : “James”,”job” : “CLERK”,”mgr” : 7698,”hiredate” : “1981-03-12″,”sal” : 950,”comm” : null,”deptno” : 30})
db.emp.insert({“empno” : 7934,”ename” : “Miller”,”job” : “CLERK”,”mgr” : 7782,”hiredate” : “1982-01-23″,”sal” : 1300,”comm” : null,”deptno” : 10})

–Verify the data
> db.emp.find( {} )
> db.dept.find( {} )

EmpDeptTables

Hope you find this article helpful.

Please subscribe for more interesting updates.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s