EMP DEPT and SALGRADE Tables Queries & Script

EMP DEPT Table Script in Oracle | 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.

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

If accidentally, you had dropped/deleted the employee table and department table in SQL then using the below queries you can re-create them.

Department Table in SQL

Structure of the DEPT table in SQL Oracle,

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,

SELECT * FROM dept;

Output:-

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

EMP Table in SQL

The structure of the emp table in oracle is given below. Create EMP Table in SQL,

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 employee table in Oracle contains empno, ename, job, mgr, hiredate, sal, comm, deptno columns. It also contains the primary key constraint on empno column, a foreign key constraint with deptno column.

EMP Table Script in Oracle,

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;

Now let us display the emp table records. The EMP table records are,

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

Output:-

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 SQL queries. The EMP table is associated with the DEPT table and the EMP table contains primary constraints on the EMPNO column and foreign key constraints 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.

Description of The EMP Table

To get the description of the emp table we can use the “desc” command which gives information about table structure, column name, and its datatype with size. Description of The EMP Table can be displayed as follows:-

DESC emp;

Output:-

Column	  Null?         Type
--------  -----------   --------------
EMPNO     NOT NULL      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)

SALGRADE Table

The Structure of the SALGRADE 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,

SELECT * FROM salgrade;

Output:-

     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 do you find anything incorrect? Let us know in the comments. Thank you!

Also See:-

2 thoughts on “EMP DEPT and SALGRADE Tables Queries & Script”

  1. After running the emp, dept, and salgrade table script unable to perform any operation based on ename column and job column all others are working.
    SQL> select * from emp where job=’manager’;
    no rows selected

    SQL> select * from emp where ename=’clark’;
    no rows selected

    And on the same table if performing other operations it’s done
    SQL> select * from emp where to_char(hiredate,’yyyy’) in(‘1980′,’1987′,’1981’);

    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

    13 rows selected.
    please kindly help me out.

    1. The table and column names are case-insensitive but the table data is case-sensitive. Oracle treats ‘manager’ and ‘MANAGER’ completely differently. The emp table has ‘MANAGER’ but not the ‘manager’. Please try select * from emp where job=’MANAGER’; And select * from emp where ename=’CLERK’;

Leave a Comment

Your email address will not be published.