Oracle SQL Emp and Dept Tables For SQL Server

Below are Oracle’s most popular “emp” and “dept” tables compatible with SQL Server.

— 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) );

— Dept Table data:
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’);

— Emp Table data:
INSERT INTO Emp VALUES( 7839, ‘KING’, ‘PRESIDENT’, NULL, CONVERT(DATETIME,’17-11-1981′,103), 5000, NULL, 10 );
INSERT INTO Emp VALUES( 7698, ‘BLAKE’, ‘MANAGER’, 7839, CONVERT(DATETIME,’1-5-1981′,103), 2850, NULL, 30 );
INSERT INTO Emp VALUES( 7782, ‘CLARK’, ‘MANAGER’, 7839, CONVERT(DATETIME,’9-6-1981′,103), 2450, NULL, 10 );
INSERT INTO Emp VALUES( 7566, ‘JONES’, ‘MANAGER’, 7839, CONVERT(DATETIME,’2-4-1981′,103), 2975, NULL, 20 );
INSERT INTO Emp VALUES( 7788, ‘SCOTT’, ‘ANALYST’, 7566, CONVERT(DATETIME,’13-JUL-87′,103), 3000, NULL, 20 );
INSERT INTO Emp VALUES( 7902, ‘FORD’, ‘ANALYST’, 7566, CONVERT(DATETIME,’3-12-1981′,103), 3000, NULL, 20 );
INSERT INTO Emp VALUES( 7369, ‘SMITH’, ‘CLERK’, 7902, CONVERT(DATETIME,’17-12-1980′,103), 800, NULL, 20 );
INSERT INTO Emp VALUES( 7499, ‘ALLEN’, ‘SALESMAN’, 7698, CONVERT(DATETIME,’20-2-1981′,103), 1600, 300, 30 );
INSERT INTO Emp VALUES( 7521, ‘WARD’, ‘SALESMAN’, 7698, CONVERT(DATETIME,’22-2-1981′,103), 1250, 500, 30 );
INSERT INTO Emp VALUES( 7654, ‘MARTIN’, ‘SALESMAN’, 7698, CONVERT(DATETIME,’28-9-1981′,103), 1250, 1400, 30 );
INSERT INTO Emp VALUES( 7844, ‘TURNER’, ‘SALESMAN’, 7698, CONVERT(DATETIME,’8-9-1981′,103), 1500, 0, 30 );
INSERT INTO Emp VALUES( 7876, ‘ADAMS’, ‘CLERK’, 7788, CONVERT(DATETIME,’13-JUL-87′, 103), 1100, NULL, 20 );
INSERT INTO Emp VALUES( 7900, ‘JAMES’, ‘CLERK’, 7698, CONVERT(DATETIME,’3-12-1981′,103), 950, NULL, 30 );
INSERT INTO Emp VALUES( 7934, ‘MILLER’, ‘CLERK’, 7782, CONVERT(DATETIME,’23-1-1982′,103), 1300, NULL, 10 );
Go