The EMP and DEPT tables for MySQL

So I’m reading through the introduction of O’Reilly’s SQL Cookbook and I get to this part that says:

The majority of the examples in this book involve the use of two tables, EMP and DEPT. The EMP table is a simple 14-row table with only numeric, string, and date fields. The DEPT table is a simple four-row table with only numeric and string fields. These tables appear in many old database texts, and the many-to-one relationship between departments and employees is well understood.

I love it when I stumble upon historical gems like this. So I went searching for a quick dump of the SQL to create and populate these sample tables, and I couldn’t find any. Well, that’s not entirely true. I did stumble upon a few variations, but it appears that the canonical version comes to us courtesy of Oracle. Of course I was looking for something a little more MySQL-friendly.

So I ran the Oracle SQL through MySQL with a few changes: I changed the HIREDATE datetime column to a date column, and I changed the format of the date in the insert statements from dd-mon-yyyy to yyyy-mm-dd. Then I mysqldump-ed out the SQL to get the version you see below. Just for my own sanity, I also lowercased the table and column names. You should be able to copy and paste it directly into the MySQL command line. Happy SQLing.

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (
  empno decimal(4,0) NOT NULL,
  ename varchar(10) default NULL,
  job varchar(9) default NULL,
  mgr decimal(4,0) default NULL,
  hiredate date default NULL,
  sal decimal(7,2) default NULL,
  comm decimal(7,2) default NULL,
  deptno decimal(2,0) default NULL
);

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (
  deptno decimal(2,0) default NULL,
  dname varchar(14) default NULL,
  loc varchar(13) default NULL
);

INSERT INTO emp VALUES ('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
INSERT INTO emp VALUES ('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
INSERT INTO emp VALUES ('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
INSERT INTO emp VALUES ('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
INSERT INTO emp VALUES ('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
INSERT INTO emp VALUES ('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
INSERT INTO emp VALUES ('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
INSERT INTO emp VALUES ('7788','SCOTT','ANALYST','7566','1982-12-09','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');
INSERT INTO emp VALUES ('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');
INSERT INTO emp VALUES ('7876','ADAMS','CLERK','7788','1983-01-12','1100.00',NULL,'20');
INSERT INTO emp VALUES ('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
INSERT INTO emp VALUES ('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
INSERT INTO emp VALUES ('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');

INSERT INTO dept VALUES ('10','ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES ('20','RESEARCH','DALLAS');
INSERT INTO dept VALUES ('30','SALES','CHICAGO');
INSERT INTO dept VALUES ('40','OPERATIONS','BOSTON');

Judging by the data, this has been around since 1982.

20 Comments

Also, how about that logical error on the last line of page 2? Not exactly confidence-inspiring reading into the rest of the book.

Joe, no problem. Also, check out the SQL Cookbook’s Errata

Sebastien

Thank you very much !
I’m a student, I’ve tried to use Oracle but it’s not very well working on my computer. But I need these tables at school, so thank for your solution ;)

Sebastien, glad you found this helpful.

chan

thanks justin

Thanks for posting the sample data from “SQL Cookbook”. See also http://www.ictytranscripts.org.

Ballas

Thanks mate, but I think you need to connect to two tables somehow with a primary key (deptno).

Ballas, there was no referential integrity constraint in the original Oracle SQL, so I didn’t include it in this MySQL-friendly version. I tend to be wary of defining referential integrity constraints at the DB-level, and instead dealing with them at the app-level, to consolidate the logic in code.

shrikant

Thank you :-)

I am really thankful to you for this valuable post

Dan D'Urso

I agree with Ballas. The empno field in the EMP table and the deptno field in the DEP table should have PRIMARY KEY added. You could add a REFERENCES clause to deptno in the EMP table to establish referential integrity (but I don’t think that was in the original database which was probably built back when this was enforced at the application level).

Abhishek

Thanks for sharing this. It’s useful even today!

siddharth tomar

thnx

Naveen

Thank you.U saved my time.

Thank you so much for that! I am always thinking of creating the emp and dept tables for my general exploration and this helps immensely!

Abhay

Thank buddy…for this data…you saved my lot of time to create these two tables….:)

Sid ks

THANKS mate…..

Thanks a lot! Looking for some db samples and got it…

Affan

Thanks! You saved some precious time! :)

Name

Email (optional)

Blog (optional)