Introduction to Oracle SQL: Basic SELECT Statements with SCOTT Schema

The SELECT statement is the foundation of SQL and the first command you’ll use when working with an Oracle Database.
In this comprehensive beginner guide, you’ll learn:

• Basic SELECT syntax
• Filtering data with WHERE
• Sorting results using ORDER BY
• Text-based diagrams for clarity
• How to install Oracle’s SCOTT sample schema

---

📘 1. What is a SELECT Statement?

A SELECT statement allows you to retrieve specific data from tables in an Oracle Database.
It’s the most fundamental SQL statement used in both development and operations.

---

📊 [Diagram] SELECT Statement in Action

┌──────────────┐
│   Table: EMP │
├────┬──────┬────────┤
│ID  │NAME  │JOB     │
├────┼──────┼────────┤
│7369│SMITH │CLERK   │
│7499│ALLEN │SALESMAN│
└────┴──────┴────────┘

SELECT empno, ename FROM emp;
↓
+--------+--------+
| EMPNO  | ENAME  |
+--------+--------+
| 7369   | SMITH  |
| 7499   | ALLEN  |
+--------+--------+

---

🧱 2. Basic Syntax of SELECT

SELECT column1, column2, ...
FROM table_name;

---

✅ Example

SELECT empno, ename, job
FROM emp;

This command retrieves employee number, name, and job from the emp table.

---

✅ Selecting All Columns

SELECT *
FROM emp;

In practice, it’s recommended to specify only required columns for better performance and clarity.

---

🎯 3. Filtering with WHERE Clause

SELECT column1, column2, ...
FROM table_name
WHERE condition;

---

✅ Example: Filter rows with job = ‘CLERK’

SELECT empno, ename, job
FROM emp
WHERE job = 'CLERK';

---

✅ Numeric comparison

SELECT empno, ename
FROM emp
WHERE empno >= 7500;

---

✅ NULL check with IS NULL

SELECT empno, ename
FROM emp
WHERE comm IS NULL;

Note: You must use IS NULL or IS NOT NULL.
= NULL or != NULL will not work in Oracle SQL.

---

🔃 4. Sorting with ORDER BY

Use ORDER BY to sort query results.

SELECT empno, ename
FROM emp
ORDER BY empno DESC;

• ASC (ascending): default
• DESC (descending): optional keyword for reverse order

---

📊 [Diagram] Before and After ORDER BY

Before:
EMPNO
-----
7369
7900
7499

ORDER BY empno DESC:
EMPNO
-----
7900
7499
7369

---

🧪 5. Practice Examples

-- Employees with salary >= 3000 in ascending order
SELECT empno, ename, sal
FROM emp
WHERE sal >= 3000
ORDER BY sal;

-- SALESMANs who have NULL commission
SELECT empno, ename
FROM emp
WHERE job = 'SALESMAN'
  AND comm IS NULL;

---

🧰 6. Installing the SCOTT Sample Schema

The tables used in this article (e.g., emp, salgrade) are part of the SCOTT sample schema, which is a learning environment provided by Oracle.

You must install the schema manually using Oracle’s built-in script.

---

✅ Installation Steps (for non-CDB and CDB)

If you’re using a non-CDB Oracle environment, you can run this directly.

-- Connect as SYSDBA and execute:
@?/rdbms/admin/utlsampl.sql

? expands to your $ORACLE_HOME path automatically.

---

💡 Note for CDB/PDB users

In CDB-based environments (default for 12c and later), switch to a PDB first:

-- From CDB
ALTER SESSION SET CONTAINER = PDB1;

-- Or connect directly to PDB
CONNECT sys/password@PDB1 AS SYSDBA

-- Then run:
@?/rdbms/admin/utlsampl.sql

---

✅ What gets created?

• User: SCOTT
• Password: TIGER
• Tables: EMP, DEPT, BONUS, SALGRADE

---

🔚 7. Summary

Concept	Purpose
SELECT	Retrieve data
FROM	Specify the source table
WHERE	Apply row-level filtering
ORDER BY	Sort the result set
SCOTT Schema	Oracle’s sample tables for SQL practice

---

✅ Final Thoughts

The SELECT statement is your first step into the world of Oracle SQL.
Using the SCOTT schema is a great way to learn real-life table structures and practice confidently.

In future articles, we will explore:

• Aggregate functions
• GROUP BY
• JOINs between multiple tables

If you’d like help installing SCOTT or writing more queries, feel free to reach out.

Let’s keep learning Oracle SQL together!

[Reference]
SELECT