EMP Table Script in SQL Server

EMP Table Script in SQL Server | The emp and dept tables are given for practice purposes. It helps beginners to learn, practice different SQL queries. In this post, we have given emp and dept table script in SQL Server.

DEPT table script in SQL Server,

CREATE TABLE dept (
  deptno INT NOT NULL,
  dname VARCHAR(14),
  loc VARCHAR(13)
)
begin
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')
end

The DEPT table contains deptno, dname, and loc columns. Among these columns deptno is of INT type, dname and loc are of VARCHAR type.

EMP Table Script in SQL Server,

CREATE TABLE emp (
  empno INT PRIMARY KEY,
  ename VARCHAR(10),
  job VARCHAR(9),
  mgr INT NULL,
  hiredate DATETIME,
  sal NUMERIC(7,2),
  comm NUMERIC(7,2) NULL,
  dept INT
)
begin
INSERT INTO EMP VALUES
  (7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20)
INSERT INTO EMP VALUES
  (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30)
INSERT INTO EMP VALUES
  (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30)
INSERT INTO EMP VALUES
  (7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20)
INSERT INTO EMP VALUES
  (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30)
INSERT INTO EMP VALUES
  (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30)
INSERT INTO EMP VALUES
  (7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10)
INSERT INTO EMP VALUES
  (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20)
INSERT INTO EMP VALUES
  (7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10)
INSERT INTO EMP VALUES
  (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30)
INSERT INTO EMP VALUES
  (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20)
INSERT INTO EMP VALUES
  (7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30)
INSERT INTO EMP VALUES
  (7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20)
INSERT INTO EMP VALUES
  (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10)
end

The EMP table contains total of 8 columns, these columns are:- empno, ename, job, mgr, hiredate, sal, comm, and dept. Among these columns empno, mgr, & dept are of INT type, sal, & comm are of NUMERIC type, ename & job are of VARCHAR type, and hiredate column is of DATETIME.

SALGRADE Table Script in SQL Server,

CREATE TABLE SALGRADE (
  GRADE NUMERIC,
  LOSAL NUMERIC,
  HISAL NUMERIC
)
begin
INSERT INTO SALGRADE VALUES (1, 700, 1200)
INSERT INTO SALGRADE VALUES (2, 1201, 1400)
INSERT INTO SALGRADE VALUES (3, 1401, 2000)
INSERT INTO SALGRADE VALUES (4, 2001, 3000)
INSERT INTO SALGRADE VALUES (5, 3001, 9999)
end

These three tables are the most important tables in SQL Server which will be used to practice different SQL queries in SQL Server.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *