Complete Emp and Dept table with example

 let's work with a typical example involving emp (employee) and dept (department) tables. This is a common scenario in database management, often used for illustrating SQL queries and relational database concepts.

Schema Definitions

dept Table (Department Table):

• deptno (Department Number): Primary key
• dname (Department Name)
• loc (Location)

emp Table (Employee Table):

• empno (Employee Number): Primary key
• ename (Employee Name)
• job (Job Title)
• mgr (Manager ID)
• hiredate (Hire Date)
• sal (Salary)
• comm (Commission)
• deptno (Department Number): Foreign key referencing dept(deptno)

Example Data

dept Table:

-- Insert record into dept table

INSERT INTO dept (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO dept (deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO dept (deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');

Emp Table: 

-- Insert records into emp table
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

SQL Queries:

Click below links for more examples on Emp and dept table.
 Interview questions on emp and dept table (part 1)
Interview questions on emp and dept table (part 2)
Interview questions on emp and dept table (part 3)
Interview questions on emp and dept table (part 4)
Tricky interview questions employee and manager column in emp table
Tricky interview questions employee and manager column in emp table