Introduction to Oracle Database: What is a Sample Schema? How to Set Up a Learning Environment Using the SCOTT Schema

When learning Oracle Database, one of the most effective approaches is hands-on practice using a sample schema.
This article clearly explains what sample schemas are, introduces key examples, and walks you through how to install and use the SCOTT schema using easy-to-understand diagrams.

---

🔰 1. What Is a Sample Schema?

A sample schema is a predefined set of users and tables designed for learning, testing, and experimentation in Oracle Database.

• Simulates real-world data structures
• Comes with preloaded data
• Perfect for safe SQL practice and educational resources

---

📘【Diagram】Structure of a Schema

Schema (SCOTT)
├── Table: EMP (Employee Information)
├── Table: DEPT (Department Information)
├── Table: BONUS (Bonus Information)
└── Table: SALGRADE (Salary Grades)

A schema is a collection of database objects grouped under a single user, like a design blueprint.

---

💡 2. Why Are Sample Schemas So Useful?

Reason	Details
✅ Safe experimentation	No risk of harming production data
✅ Realistic data modeling	Simulates business domains like HR or sales
✅ Learning compatibility	Most textbooks and online courses use these schemas in examples

---

📚 3. Popular Sample Schemas in Oracle

Oracle officially provides several well-known sample schemas.

Schema Name	Description and Use Cases
SCOTT	Basic example; contains employee and department tables
HR	For learning human resources data like hire dates, salaries
OE	For simulating order entry, product, and customer management

---

✅ Why SCOTT Schema Is Ideal for Beginners

• Very simple with only a few tables
• Commonly referenced in learning resources
• Covers essential SQL operations: SELECT, JOIN, subqueries, etc.

---

🛠 4. How to Install the SCOTT Schema

SCOTT is not installed by default. Follow the steps below to enable it in your database.

---

🔧 Method A: For Non-CDB Environments

-- Log in as SYSDBA and run:
@?/rdbms/admin/utlsampl.sql

✅ ? refers to your $ORACLE_HOME path.
This will create the SCOTT user and its sample tables.

---

🔧 Method B: For CDB + PDB Environments (Oracle 12c and later)

1. Switch to the target PDB from CDB$ROOT:

ALTER SESSION SET CONTAINER = PDB1;

Or connect directly:

sqlplus sys/password@PDB1 AS SYSDBA

2. Run the script inside the PDB:

@?/rdbms/admin/utlsampl.sql

---

🗃 Installed Objects

Table Name	Description
EMP	Employee Information
DEPT	Department Information
BONUS	Bonus Table
SALGRADE	Salary Grade Table

Default credentials:
User: SCOTT Password: TIGER

---

🧪 5. Test Your Setup

-- Connect to SCOTT schema
CONNECT scott/tiger@PDB1

-- List all tables
SELECT table_name FROM user_tables;

---

🧱 6. What You Can Learn Using SCOTT Schema

SQL Feature	Example Use Case
SELECT	Basic data retrieval
WHERE	Filtering data
ORDER BY	Sorting results
JOIN	Combining employee and department tables
GROUP BY	Aggregating data by department or job
Subqueries	EXISTS, IN, correlated subqueries

---

📘【Diagram】JOIN Example (EMP + DEPT)

SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

┌────────┬───────────┐
│ ename  │ dname     │
├────────┼───────────┤
│ SMITH  │ ACCOUNTING│
│ ALLEN  │ SALES     │
└────────┴───────────┘

---

✅ 7. Summary

• Starting Oracle practice with sample schemas is the safest and smartest approach.
• SCOTT schema is particularly beginner-friendly.
• Use utlsampl.sql to install the schema in minutes.
• For CDB setups, always switch to a PDB before running the script.

[Reference]
SELECT