SCOTT account

Oracle 11g XE does not have the Scott account.
If you installed Oracle Database 11g Release 2 but not XE, the SCOTT account exits. But the SCOTT account is locked. To unlock, you need to log in as an administrator and run the following:

C:\ Command Prompt
alter user scott account unlock;

When you first access the Scott account in 11g Release 2, Oracle instructs you to change Scott's password. Change it to the tiger for consistency in subsequent JDBC articles.

If you installed 11g XE by selecting the installation partition as C:\, the SCOTT schema script file path is as follows.
Use this file to create a Scott account. Connect to SQL * Plus with the SYSTEM account.

C:\ Command Prompt

SQL*Plus: Release Production on Tue Jun 7 13:29:14 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: system
Enter password: <Admin Password>

Connected to:
Oracle Database 11g Express Edition Release - 64bit Production


SQL * Plus is an Oracle command-line program that can execute SQL. Run the SCOTT schema script file. To run a file in SQL * Plus, prepend the full path to the file with @.

C:\ Command Prompt
SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

If there are no errors, identify the users as below.

C:\ Command Prompt
SQL> show user

You can see that the connected user has changed from SYSTEM to SCOTT. Change the password for the SCOTT account to the tiger.

C:\ Command Prompt
SQL> alter user scott identified by tiger;
User altered.

Exit SQL * Plus by executing exit.

C:\ Command Prompt
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release
- 64bit Production

Users can use their Oracle objects -- tables, views, sequences, and indexes. The scott.sql schema script file creates the SCOTT account and tables for the SCOTT. Reconnect to the SCOTT account with SQL * Plus and confirm tables in the SCOTT account.

C:\ Command Prompt
C:\Users\java>sqlplus scott/tiger

Connected to:
Oracle Database 11g Express Edition Release - 64bit Production

SQL> select * from tab;

TNAME                                                        TABTYPE
BONUS                                                        TABLE
DEPT                                                         TABLE
EMP                                                          TABLE
SALGRADE                                                     TABLE


The Scott account has tables BONUS, DEPT, EMP, and SALGRADE. To view the DEPT table structure, run the following.

C:\ Command Prompt
SQL> desc dept;
NAME                                      NULL?      TYPE
----------------------------------------- -------- -------------------

DEPTNO                                    NOT NULL NUMBER(2)
DNAME                                              VARCHAR2(14)
LOC                                                VARCHAR2(13)


NAME is the column name.
TYPE is the data type specified in the column.

The first column is named DEPTNO, can not be null, and its type is NUMBER.
The second column is named DNAME and can be null, and its type is VARCHAR2.
The third column is named LOC and can be null, and its type is VARCHAR2.

NULL means that the column does not contain any value. Neither space character nor zero. The calculation and comparison with NULL are impossible. The result of the arithmetic operation involving NULL is NULL. You cannot compare values with NULL.
Oracle's main data types
It is a data type for numbers. PRECISION: Total digits including decimal point.
SCALE: Decimal places.
For example, 72.5 is NUMBER(3,1), 10 is NUMBER(2), 7788 is NUMBER(4), and all numeric types are NUMBER.
It is a data type that stores dates and times
It is a data type that stores variable-length character strings.
The number in parentheses means the number of bytes.
VARCHAR2(15) column can store a maximum of 15 bytes string.

Let's look at the records in the DEPT table. (Record means one row)

C:\ Command Prompt
SQL> select * from dept;

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


A select statement retrieves records from the table. SQL statements are case-insensitive. However, this does not mean that the data is case-insensitive. The DEPT is a department table. The DEPTNO column stores the department number, the DNAME column stores the department name, and the LOC column stores the department location information.

Let's look at the structure of the EMP table.

C:\ Command Prompt
SQL> desc emp;

 NAME                                      NULL?      TYPE
 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)


What the EMP table column means are:

  1. EMPNO(Employee number)
  2. ENAME(Employee Name)
  3. JOB
  4. MGR(Supervisor-employee number)
  5. HIREDATE(Hire date)
  6. SAL(Salary)
  7. COMM(Commission)
  8. DEPTNO(Department number)

Retrieve all employee records contained in the EMP table.

C:\ Command Prompt
SQL> select * from emp;

---- -------------------------- --------   ------- ------- -------
7369 SMITH   CLERK         7902 80/12/17   800               20   
7499 ALLEN   SALESMAN      7698 81/02/20   1600     300      30   
7521 WARD    SALESMAN      7698 81/02/22   1250     500      30   
7566 JONES   MANAGER       7839 81/04/02   2975              20   
7654 MARTIN  SALESMAN      7698 81/09/28   1250     1400     30   
7698 BLAKE   MANAGER       7839 81/05/01   2850              30   
7782 CLARK   MANAGER       7839 81/06/09   2450              10   
7839 KING    PRESIDENT          81/11/17   5000              10   
7844 TURNER  SALESMAN      7698 81/09/08   1500       0      30   
7900 JAMES   CLERK         7698 81/12/03   950               30   
7902 FORD    ANALYST       7566 81/12/03   3000              20   
7934 MILLER  CLERK         7782 82/01/23   1300              10   

12 rows selected.


There are a total of 12 employees. Only employees whose job (JOB) is sales (SALESMAN) have values in the COMM (commission) column. The commission column for the remaining employees is NULL.

Since KING is a PRESIDENT, the employee number of his manager is NULL.

Check the structure of the SALGRADE table.

C:\ Command Prompt
SQL> desc salgrade;
NAME                                      NULL?      TYPE
----------------------------------------- -------- -------

GRADE                                              NUMBER
LOSAL                                              NUMBER
HISAL                                              NUMBER


The SALGRADE table is a table of salary levels. GRADE is the salary grade, In the same salary grade, LOSAL is the minimum salary, and HISAL is the maximum salary.

Retrieve all the data from the SALGRADE table.

C:\ Command Prompt
SQL> select * from salgrade;

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


Create a SCOTT account on Linux

Start SQL*PLUS and login as sys:

sqlplus sys as sysdba

SQL*Plus: Release Production on Wed May 9 12:12:16 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password: **********

Connected to:
Oracle Database 11g Express Edition Release - 64bit Production