Oracle Tutorial for Beginners

Introduction

Are you overwhelmed by complex database concepts and struggling to find a clear starting point for learning Oracle SQL and PL/SQL? Many beginners feel lost in the technical jargon and vast documentation.

This Oracle tutorial for beginners is designed to demystify Oracle, providing a straightforward path from absolute zero to foundational knowledge. We focus on practical examples and simple explanations to build your confidence quickly.

Ready to get going? Click here to see the full Oracle course syllabus and get started!

Why Students or Freshers Learn Oracle?

Learning Oracle (SQL and PL/SQL) is a powerful move for students and freshers because it offers stability and high demand in the tech job market.

• Global Industry Standard: Oracle Database is widely used by Fortune 500 companies, banks, and enterprises across the globe for mission-critical data management.
• Essential IT Skill: Knowing SQL is a fundamental, in-demand skill for roles like Database Administrator (DBA), SQL Developer, and Data Analyst.
• Career Advancement: Oracle certification (e.g., OCA) increases your credibility and can lead to higher-paying jobs and accelerated career growth.
• Data Dominance: In the age of Big Data, expertise in a robust RDBMS like Oracle positions you at the heart of any business’s data strategy.

Ready to Ace Your Interview? Click here for the Top Oracle Interview Questions and Answers for Freshers!

Step-by-Step Oracle Tutorial for Beginners

Welcome to your comprehensive, step-by-step tutorial for learning Oracle Database from the ground up! This guide is designed specifically for beginners, covering everything from installation to writing your first powerful SQL and PL/SQL code.

We’ll focus on practical steps and clear explanations to get you comfortable with one of the world’s most robust database systems.

Step 1. Setup your Oracle Environment-Installation

The first hurdle for many beginners is the installation process. We will simplify this by recommending the Oracle Database Express Edition (XE). It’s a free, fully functional version of Oracle, ideal for learning, development, and small applications.

1.1 Download Oracle Database XE

Go to the Oracle Website: Navigate to the official Oracle Technology Network (OTN) download page for Oracle Database XE.

• Choose the Right Version: We recommend the latest stable version of Oracle Database XE (e.g., 21c XE or 18c XE) for your operating system (Windows, Linux, or macOS via Docker).
• License: You would need to accept the license agreement before downloading.
• Download the Installer: Download the appropriate installer file (usually a .zip file for Windows).

1.2 Installing rcap (Example for Windows)

• Extract the Files: Unzip the downloaded file in any temporary directory.
• Run Installer: Run setup.exe by double click.
• Follow the Wizard:
  • License agreements are accepted.
  • Choose an Install Location. The default is usually OK.
  • Crucial Step: Set the SYS/SYSTEM password. Remember this password! It is the master password for the administrative user accounts. For learning purposes, a simple, memorable password is sufficient.
  • Click Install. The installation may take some minutes.
• Finish: Once complete, the installer will display the connection details, including the port number (usually 1521).

1.3 Connecting to the Database: SQL Developer

Now that the database is installed, you need a tool to interact with it. SQL Developer is Oracle’s free, official graphical user interface (GUI) tool and is highly recommended.

• Download SQL Developer: Download it from the Oracle website (it requires Java, but newer versions often bundle a Java Development Kit).
• Start SQL Developer: Launch the application (no installation needed, just unzip and run the executable).
• Establish a new relationship:
  • Click the green plus icon (+) or right-click and choose File → New → Database Connection.
  • Connection Name: XE_Connection  (or whatever you’d like to name it)
  • Username: SYSTEM
  • Password: The password you created during setup.
  • Hostname: localhost
  • Port: 1521 (default)
  • Service Name: XEPDB1 (for newer versions of XE) XE (for older ones).
  • Click Test. Once the Status is “Success,” click Connect.

You are now connected to your Oracle Database!

Step 2: Structured Query Language – SQL

SQL is the language you use to communicate with the database. It is split into several categories, but we’ll focus on the essential ones for beginners: DML and DDL.

2.1 About DDL or Data Definition Language

DDL statements are used to define or change the database structure. The most common DDL statements are CREATE, ALTER, and DROP.

Creating Your First Table (CREATE TABLE)

Let’s create a table called EMPLOYEES, where we will store simple data about employees.

CREATE TABLE EMPLOYEES (

    employee_id NUMBER(6) PRIMARY KEY,

    first_name VARCHAR2(20),

    last_name VARCHAR2(25) NOT NULL,

    hire_date DATE DEFAULT SYSDATE,

    salary NUMBER(8, 2)

);

Explanation:

• employee_id NUMBER(6) PRIMARY KEY: A unique identifier (up to 6 digits) for each employee. The PRIMARY KEY constraint ensures every ID is unique and not null.
• first_name: VARCHAR2(20): A variable-length string (text) up to 20 characters.
• last_name VARCHAR2(25) NOT NULL: Must have a value.
• hire_date DATE DEFAULT SYSDATE: Stores a date value, defaulting to the current system date if not specified.
• salary NUMBER(8, 2): Stores a number up to 8 digits in total, with 2 digits after the decimal point.

Modifying a Table (ALTER TABLE)

If you would like to add a new column later, you use ALTER TABLE:

ALTER TABLE EMPLOYEES

ADD (department_id NUMBER(4));

Removing a Table (DROP TABLE)

Be very careful with this command, as it permanently deletes the table and all its data!

DROP TABLE EMPLOYEES;

2.2 How to Gain Understanding of DML

DML statements are used to manage the data within the tables. The core DML commands are INSERT, SELECT, UPDATE, and DELETE.

Data Insertion (INSERT INTO)

Inserting Data into the EMPLOYEES Table:

INSERT INTO EMPLOYEES (employee_id, first_name, last_name, salary)

VALUES (100, ‘John’, ‘Doe’, 60000.00);

INSERT INTO EMPLOYEES

VALUES (101, ‘Jane’, ‘Smith’, ’01-JAN-2023′, 75000.50, 50); — Inserting all values

Retrieve Data (SELECT) – The Most Commonly Used Command!

The SELECT statement is the heart of SQL. It’s used to retrieve data from the database.

Select All Columns and All Rows:

SELECT *

FROM EMPLOYEES;

Select Particular Columns:

SELECT employee_id, first_name, salary

FROM EMPLOYEES;

Filtering Data (WHERE Clause):

SELECT first_name, last_name, salary

FROM EMPLOYEES

WHERE salary > 70000; — Find employees earning more than 70,000

Ordering Results (ORDER BY Clause):

SELECT *

FROM EMPLOYEES

WHERE department_id = 50

ORDER BY last_name ASC; — Sorts by last name in ascending order

Changing Exisiting Data (UPDATE)

Change John Doe’s last name and salary:

UPDATE EMPLOYEES

SET last_name = ‘Wilson’, salary = 65000

WHERE employee_id = 100;

— WARNING: Running UPDATE without a WHERE clause will update ALL rows!

Deleting Data DELETE

To remove the record for the employee with Employee ID 101:

DELETE FROM EMPLOYEES

WHERE employee_id = 101;

— WARNING: Running DELETE without a WHERE clause will delete ALL rows!

2.3 Transaction Control (COMMIT and ROLLBACK)

In Oracle, DML operations (INSERT, UPDATE, DELETE) are not permanent until you confirm the transaction.

• COMMIT: This makes all the changes permanent in the database.
• ROLLBACK: All changes since the last COMMIT are undone.

INSERT INTO EMPLOYEES (employee_id, last_name) VALUES (102, ‘Brown’);

— At this point, only you can see the change.

ROLLBACK;

— The INSERT is now undone. The row for Brown is gone.

INSERT INTO EMPLOYEES (employee_id, last_name) VALUES (103, ‘Taylor’);

COMMIT;

— The change is permanent and visible to everyone.

Step 3: Advanced concepts in SQL

Once you grasp these basics, these concepts will help write powerful queries.

3.1 Joining Tables

Databases are built on relationships. JOIN clauses combine rows from two or more tables based on a related column between them (often a Foreign Key).

Suppose you have a DEPARTMENTS table:

CREATE TABLE DEPARTMENTS (

    department_id NUMBER(4) PRIMARY KEY,

    department_name VARCHAR2(30)

);

INSERT INTO DEPARTMENTS VALUES (50, ‘IT’);

INSERT INTO EMPLOYEES (employee_id, last_name, department_id) VALUES (104, ‘Miller’, 50);

COMMIT;

Inner Join – Most Common

Returns only the rows that possess common values within both tables.

SELECT e.last_name, d.department_name

FROM EMPLOYEES e

INNER JOIN DEPARTMENTS d

ON e.department_id = d.department_id;

Note: e and d are aliases or short cuts for the table names.

Outer Joins (LEFT/RIGHT Outer Join)

Returns matching rows plus all rows from one side, even if there’s no match on the other side.

— LEFT JOIN: Show ALL employees, and their department name if they have one.

SELECT e.last_name, d.department_name

FROM EMPLOYEES e

LEFT OUTER JOIN DEPARTMENTS d

ON e.department_id = d.department_id;

3.2 Aggregate Functions and Grouping

Aggregate functions perform a calculation against a set of rows and return a single value.

• COUNT(): Count of rows.
• SUM(): Sum of values.
• AVG(): Average of values.
• MAX()/MIN(): Highest/Lowest value.

— How many employees do we have?

SELECT COUNT(*) FROM EMPLOYEES;

— What is the average salary?

SELECT AVG(salary) FROM EMPLOYEES;

Grouping Results (GROUP BY)

To apply an aggregate function to groups of rows (e.g., to find the average salary per department):

SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary

FROM EMPLOYEES

GROUP BY department_id

ORDER BY department_id;

Filtering Groups (HAVING)

The WHERE clause filters individual rows; the HAVING clause filters groups after they’ve been created.

— Find departments where the average salary is over 60,000

SELECT department_id, AVG(salary)

FROM EMPLOYEES

GROUP BY department_id

HAVING AVG(salary) > 60000;

Step 4: Overview of PL/SQL – Procedural Language/SQL

While SQL is great for interacting with data, it lacks procedural capabilities (like loops or conditional logic). PL/SQL is Oracle’s extension that combines the power of SQL with the features of a programming language.

4.1 The Basic PL/SQL Block

Every PL/SQL code unit (like a Stored Procedure or an Anonymous Block) is built around a standard structure:

DECLARE

    — Optional: Define variables, constants, and cursors here

    v_employee_count NUMBER;

BEGIN

    — Mandatory: The main executable code block

    SELECT COUNT(*) INTO v_employee_count FROM EMPLOYEES;

Print the result (in SQL Developer, you must enable this: SET SERVEROUTPUT ON;)

    DBMS_OUTPUT.PUT_LINE(‘Total Employees: ‘ || v_employee_count);

EXCEPTION

    — Optional: Code to handle errors

    WHEN NO_DATA_FOUND THEN

        DBMS_OUTPUT.PUT_LINE(‘An error occurred: No data found.’);

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred.’);

END;

/

4.2 Conditional Logic IF-THEN-ELSE

The following conditional statements can be used to control the flow of execution:

DECLARE

    v_emp_id EMPLOYEES.employee_id%TYPE := 104;

    v_salary EMPLOYEES.salary%TYPE;

BEGIN

    SELECT salary INTO v_salary

    FROM EMPLOYEES

    WHERE employee_id = v_emp_id;

    IF v_salary > 70000 THEN

        DBMS_OUTPUT.PUT_LINE(‘High Earner’);

    ELSIF v_salary > 50000 THEN

        DBMS_OUTPUT.PUT_LINE(‘Mid-Range Earner’);

    ELSE

        DBMS_OUTPUT.PUT_LINE(‘Entry Level’);

    END IF;

END;

/

Note: EMPLOYEES.employee_id%TYPE is a data type anchor. It automatically uses the data type of the specified column, making your code more robust to schema changes.

4.3 Stored Procedures and Functions

These are named PL/SQL blocks that are stored in the database and can be executed multiple times.

• Procedures primarily perform actions (like updating data) and do not have to return a value.
• Functions primarily compute a value and hence must return a value.

Example Stored Procedure:

CREATE OR REPLACE PROCEDURE give_raise (

    p_employee_id IN EMPLOYEES.employee_id%TYPE,

    p_raise_percent IN NUMBER

)

IS

BEGIN

    UPDATE EMPLOYEES

    SET salary = salary * (1 + p_raise_percent / 100)

    WHERE employee_id = p_employee_id;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE(‘Salary updated for employee ‘ || p_employee_id);

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        DBMS_OUTPUT.PUT_LINE(‘Error: Employee not found.’);

    WHEN OTHERS THEN

        ROLLBACK;

        DBMS_OUTPUT.PUT_LINE(‘Unexpected Error during update.’);

END give_raise;

/

Executing the Procedure:

— In a new SQL Worksheet:

EXEC give_raise(p_employee_id => 104, p_raise_percent => 5);

— Check the update

SELECT employee_id, salary FROM EMPLOYEES WHERE employee_id = 104;

Step 5: Continuing with Oracle

Now you have completed the essential steps of setting up Oracle, writing basic and intermediate SQL queries, and understanding the foundation of PL/SQL programming.

To further study :

• Joins Practice: Practice More Complex Multi-table Joins
• Constraints: Learn about UNIQUE, CHECK and FOREIGN KEY constraints.
• Mastering Subqueries: Learn to use one SELECT statement inside another.
• Delve into Cursors: For advanced PL/SQL, learn to process rows one by one using cursors.

Theoretical knowledge is great, but practical application is where mastery begins. To truly solidify your understanding of Oracle SQL and PL/SQL, you need to tackle real-world problems.

Ready to put your skills into practice with common database scenarios? Click here for a curated list of Oracle SQL and PL/SQL Challenges with Detailed Solutions!

Real Time Examples for Oracle Tutorial for Learners

To make learning concrete, here are some real-world scenarios where Oracle Database is essential, and the core SQL concepts used to manage them:

E-commerce Inventory System Management

Scenario: A large online retailer uses Oracle to track millions of products, customer orders, and warehouse stock levels in real time. When a customer places an order, the system must immediately check inventory, reduce the stock count, and update the order status.

Core SQL Concepts:

• UPDATE: Used to decrease the stock quantity in the PRODUCTS table immediately after a purchase.
• INSERT: To insert a new record in the ORDERS and ORDER_ITEMS tables.
• Transactions (COMMIT/ROLLBACK): Crucial to ensure that an order is either fully processed (stock deducted, order placed – COMMIT) or completely cancelled if any step fails (e.g., payment failure – ROLLBACK).

Banking and Financial Transactions

Scenario: A bank uses Oracle to manage all customer accounts, balances, and transaction history. When a money transfer occurs, the system must debit one account and credit another. This requires absolute data integrity and speed.

Core SQL Concepts:

• SELECT… FOR UPDATE: Used to lock the rows of the source and destination accounts to prevent simultaneous modification (a critical feature for concurrent transactions).
• Stored Procedures (PL/SQL): A complex transfer logic is wrapped in a stored procedure to ensure all steps (debit, credit, logging) execute as a single, atomic unit, guaranteeing reliability.

Healthcare Patient Record Management (EHR)

Scenario: A hospital system uses Oracle to store sensitive patient records, appointment schedules, and medical histories. Queries must quickly retrieve aggregated patient data for reporting and analysis while maintaining strict security. 

Core SQL Concepts: 

• JOIN: Used heavily to link patient demographic tables, treatment history tables, and doctor tables to generate a complete patient view. 
• GROUP BY and Aggregate Functions (COUNT, AVG): Used by analysts to calculate statistics like the number of appointments per doctor or the average length of stay per illness. 

Ready to Build Something Real? Click here for great Oracle Project Ideas to enhance your beginner portfolio!

FAQs About Oracle Tutorial for Beginners

Conclusion

You’ve successfully completed the foundational steps of this Oracle tutorial, from environment setup to writing powerful SQL and PL/SQL code. Remember, the journey from beginner to expert is built on consistent practice. The skills you’ve gained in querying, defining, and manipulating data are the bedrock of nearly every enterprise application worldwide. Don’t let your learning stop here! 

Ready to gain certified, in-depth knowledge and master advanced Oracle topics? Enroll now in our full Oracle Course in Chennai and transform your career!