SCOTT account

11g XE does not have a scott account.1
If you installed 11g XE by selecting the installation partition as C:\, the path of the SCOTT schema script file is as follows.
C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql 2
Use this file to create a scott account. Connect to SQL * Plus with the SYSTEM account.

C:\ Command Prompt
C:\Users\kim>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on 화 6월 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 11.2.0.2.0 - 64bit Production

SQL>

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

If there are no errors, the file has been successfully executed. Identify the users connected to SQL * Plus.

C:\ Command Prompt
SQL> show user
USER is "SCOTT"
SQL>

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

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

Exit SQL * Plus by executing exit.

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

For each user account, there are Oracle objects such as tables, views, sequences, indexes and so on. When you run the scott.sql schema script file, a SCOTT account is created and a table is created in the SCOTT account. Reconnect to the SCOTT account with SQL * Plus and check the table in the SCOTT account.

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

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

SQL> select * from tab;

TNAME                                                        TABTYPE
---------------------------------------------------------------------
BONUS                                                        TABLE
DEPT                                                         TABLE
EMP                                                          TABLE
SALGRADE                                                     TABLE

SQL>

The scott account has tables BONUS, DEPT, EMP, and SALGRADE. Let's check the table structure. To view the DEPT table structure, run desc dept;

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

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

SQL>

In the above execution result, the NAME is the column name. The TYPE is the data type specified in the column. Just as you specify a data type for a variable in Java, the database assigns the data type to the column. Only the values of the specified data type can be stored in the corresponding 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. Calculation, and comparison are impossible. 0 is neither a space character.
Oracle's main data types
NUMBER(PRECISION,SCALE)
Data type for storing 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.
DATE
Data type for storing date and time data.
VARCHAR2(Number)
It is a data type for storing variable strings. The number in parentheses means the number of bytes. If the data type of the column is VARCHAR2(15), a maximum of 15 bytes can be stored.

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

SQL>

SQL statements are case-insensitive. However, this does not mean that the data is not case-sensitive. The DEPT table 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. The select statement is used to retrieve records from the table. 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)

SQL>

What the EMP table column means are:

  1. EMPNO(Employee number)
  2. ENAME(Employee Name)
  3. JOB
  4. MGR(My supervisor employee number)
  5. HIREDATE
  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;

EMPNO ENAME  JOB           MGR  HIREDATE   SAL     COMM    DEPTNO   
---- -------------------------- --------   ------- ------- -------
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.

SQL>

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

SQL>

The SALGRADE table is a table of salary levels. GRADE is the salary grade, LOSAL is the minimum salary by grade, and HISAL is the maximum salary column by grade. 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

SQL>
Comments
  1. If you installed Oracle Database 11g Release 2, but not XE, the SCOTT account is created. But SCOTT is locked. Therefore, you must log in as an administrator and run alter user scott account unlock to unlock. When you first access the scott account after unlocking, Oracle instructs you to change your password for scott, and change it to tiger for consistency in subsequent JDBC articles.
  2. For Linux, the same file is utlsampl.sql.