SCOTT

From Oracle FAQ
Jump to: navigation, search

Scott is a database user used for demonstration purposes containing the famous EMP, DEPT, BONUS and SALGRADE tables. According to legend, this account was named after Bruce Scott (co-author and co-architect of Oracle v1 to v3) and the password was the name of his daughter's cat, Tiger.

The EMP table
empno name sal deptno
3415 Harry 3000 10
2241 Sally 2000 20
3401 George 3100 10
2202 Harriet 1905 20
The DEPT table
deptno dname
10 Finance
20 Sales

Starting with Oracle 9i additional sample/demo schemas are available. These schemas contains additional database objects to allow demonstration of newer database features. They are:

  • HR - Human resources, basic topics, supports Oracle Internet Directory
  • OE - Order entry, intermediate topics, various datatypes
  • PM - Product media, used for multimedia data types
  • QS - Queued shipping, shows advanced queuing (renamed IX in Oracle 10g)
  • SH - Sales history, large amount of data, analytic processing

Contents

[edit] Install

The scott/tiger demo is not automatically installed as was the case with earlier Oracle versions. To install the SCOTT schema into your database, run one of the following scripts from a DBA account:

SQL> @ ?/rdbms/admin/scott.sql
SQL> @ ?/sqlplus/demo/demobld.sql
SQL> @ ?/rdbms/admin/utlsampl.sql

[edit] Log in

Log in with user "scott" and password "tiger". Example:

$ sqlplus scott/tiger

If the SCOTT account is locked, you can unlock it with the following command:

SQL> ALTER USER scott ACCOUNT UNLOCK;

[edit] Original SCOTT's tables since Oracle 4

SCOTT> DESC bonus

 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 ENAME                                     VARCHAR2(10 CHAR)
 JOB                                       VARCHAR2(9 CHAR)
 SAL                                       NUMBER
 COMM                                      NUMBER

SCOTT> SELECT * FROM bonus ORDER BY ename;

no rows selected
SCOTT> DESC emp

 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 EMPNO                            NOT NULL NUMBER(4)
 ENAME                                     VARCHAR2(10 CHAR)
 JOB                                       VARCHAR2(9 CHAR)
 MGR                                       NUMBER(4)
 HIREDATE                                  DATE
 SAL                                       NUMBER(7,2)
 COMM                                      NUMBER(7,2)
 DEPTNO                                    NUMBER(2)

SCOTT> SELECT * FROM emp ORDER BY empno;

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

 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 DEPTNO                           NOT NULL NUMBER(2)
 DNAME                                     VARCHAR2(14 CHAR)
 LOC                                       VARCHAR2(13 CHAR)

SCOTT> SELECT * FROM dept ORDER BY deptno;

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

4 rows selected.
SCOTT> DESC salgrade

 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 GRADE                            NOT NULL NUMBER
 LOSAL                                     NUMBER
 HISAL                                     NUMBER

SCOTT> SELECT * FROM salgrade ORDER BY grade;

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

5 rows selected.

[edit] Also see

Bruce Scott - Scott schema was named after him

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #