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:-

• How to unlock Scott user in SQL Plus
• How to Find SID in Oracle database
• Check Oracle Database Version
• Check Database Size in Oracle
• Error ORA 01031 insufficient privileges
• PL/SQL Introduction
• PL/SQL Variable
• IF Statement in PL/SQL
• Oracle UTL_FILE Package
• Constraints in Oracle
• Rename Constraint
• Disable Constraint
• Drop Constraint
• NOT NULL Constraint
• UNIQUE Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint
• CHECK Constraint

Search