EMP DEPT and SALGRADE Tables Queries

The EMP, DEPT, and SALGRADE are pre-defined tables in the Oracle database given for practice purposes. These tables are available under the SCOTT user.

Table Of Contents

The EMP, DEPT, and SALGRADE are pre-defined tables that are already available under SCOTT user in the Oracle database. For references purpose, all DML and DCL queries for these tables are given here.

If accidentally, you had dropped/deleted any table then using below queries you can re-create them.

DEPT Table

Structure of the DEPT table,

CREATE TABLE dept (
  deptno NUMBER(2,0),
  dname  VARCHAR2(14),
  loc    VARCHAR2(13),
  CONSTRAINT pk_dept PRIMARY KEY (deptno)
);

Values in DEPT table,

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');
COMMIT;

Don’t forget to commit the records else records won’t be inserted permanently. It will be stored only in the buffer. Finally the DEPT table records are,

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

EMP Table

Structure of the EMP table,

CREATE TABLE emp (
  empno    NUMBER(4,0),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4,0),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2,0),
  CONSTRAINT pk_emp PRIMARY KEY (empno),
  CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);

The values for the EMP table,

INSERT INTO emp VALUES(
 7839, 'KING', 'PRESIDENT', null,
 to_date('17-11-1981','dd-mm-yyyy'),
 5000, null, 10 );

INSERT INTO emp VALUES(
 7698, 'BLAKE', 'MANAGER', 7839,
 to_date('1-5-1981','dd-mm-yyyy'),
 2850, null, 30);

INSERT INTO emp VALUES(
 7782, 'CLARK', 'MANAGER', 7839,
 to_date('9-6-1981','dd-mm-yyyy'),
 2450, null, 10);

INSERT INTO emp VALUES(
 7566, 'JONES', 'MANAGER', 7839,
 to_date('2-4-1981','dd-mm-yyyy'),
 2975, null, 20);

INSERT INTO emp VALUES(
 7788, 'SCOTT', 'ANALYST', 7566,
 to_date('13-JUL-87','dd-mm-rr') - 85,
 3000, null, 20);

INSERT INTO emp VALUES(
 7902, 'FORD', 'ANALYST', 7566,
 to_date('3-12-1981','dd-mm-yyyy'),
 3000, null, 20 );

INSERT INTO emp VALUES(
 7369, 'SMITH', 'CLERK', 7902,
 to_date('17-12-1980','dd-mm-yyyy'),
 800, null, 20 );

INSERT INTO emp VALUES(
 7499, 'ALLEN', 'SALESMAN', 7698,
 to_date('20-2-1981','dd-mm-yyyy'),
 1600, 300, 30);

INSERT INTO emp VALUES(
 7521, 'WARD', 'SALESMAN', 7698,
 to_date('22-2-1981','dd-mm-yyyy'),
 1250, 500, 30 );

INSERT INTO emp VALUES(
 7654, 'MARTIN', 'SALESMAN', 7698,
 to_date('28-9-1981','dd-mm-yyyy'),
 1250, 1400, 30 );

INSERT INTO emp VALUES(
 7844, 'TURNER', 'SALESMAN', 7698,
 to_date('8-9-1981','dd-mm-yyyy'),
 1500, 0, 30);

INSERT INTO emp VALUES(
 7876, 'ADAMS', 'CLERK', 7788,
 to_date('13-JUL-87', 'dd-mm-rr') - 51,
 1100, null, 20 );

INSERT INTO emp VALUES(
 7900, 'JAMES', 'CLERK', 7698,
 to_date('3-12-1981','dd-mm-yyyy'),
 950, null, 30 );

INSERT INTO emp VALUES(
 7934, 'MILLER', 'CLERK', 7782,
 to_date('23-1-1982','dd-mm-yyyy'),
 1300, null, 10 );

COMMIT;

Finally the EMP table records are,

SQL> SET pagesize 150;
SQL> SET line 150;
SQL> SELECT * FROM emp;

EMPNO ENAME    JOB          MGR HIREDATE      SAL  COMM  DEPTNO
----- -------- --------- ------ --------- ------- ------ ------
7839 KING      PRESIDENT        17-NOV-81    5000            10
7698 BLAKE     MANAGER     7839 01-MAY-81    2850            30
7782 CLARK     MANAGER     7839 09-JUN-81    2450            10
7566 JONES     MANAGER     7839 02-APR-81    2975            20
7788 SCOTT     ANALYST     7566 19-APR-87    3000            20
7902 FORD      ANALYST     7566 03-DEC-81    3000            20
7369 SMITH     CLERK       7902 17-DEC-80     800            20
7499 ALLEN     SALESMAN    7698 20-FEB-81    1600     300    30
7521 WARD      SALESMAN    7698 22-FEB-81    1250     500    30
7654 MARTIN    SALESMAN    7698 28-SEP-81    1250    1400    30
7844 TURNER    SALESMAN    7698 08-SEP-81    1500       0    30
7876 ADAMS     CLERK       7788 23-MAY-87    1100            20
7900 JAMES     CLERK       7698 03-DEC-81     950            30
7934 MILLER    CLERK       7782 23-JAN-82    1300            10

The EMP table is the most important table given in the Oracle database for practice the SQL queries. The EMP table is associated with the DEPT table and the EMP table contains primary constraint on the EMPNO column and foreign key constraint on the DEPTNO table. Similarly, the DEPT table contains a foreign key constraint on the DEPTNO table. In the EMP table, HIREDATE column is given to practice DATE functions, SAL is given to practice Number functions, and e.t.c.


SALGRADE Table

The Structure of the BONUS table,

CREATE TABLE salgrade (
  grade NUMBER,
  losal NUMBER,
  hisal NUMBER
);

The values for the SALGRADE table are,

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

COMMIT;

Finally, the SALGRADE table records are,

SQL> SELECT * FROM salgrade;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

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

Also See:-

Leave a Reply