Introduction to Oracle Database

In this article , I am going to give you brief about what is Oracle database . 

Before going to deep dive let's discuss some basic terminology :-

What is Data ?

Data is fact or figures , Data is any collection of facts, statistics, or information that can be stored, processed, and analyzed. It can exist in various forms, such as numbers, text, images, audio, or video.

In computing and databases, data refers to raw information that is stored in structured or unstructured formats.

• Structured data: Organized in databases, tables, and spreadsheets (e.g., customer records, sales transactions).
• Unstructured data: Includes emails, videos, social media posts, and documents.

In the context of Oracle databases, data is stored in tables within schemas and can be retrieved, modified, and managed using SQL queries.

What is Database ?

A database is an organized collection of data that is stored, managed, and accessed electronically. It allows users to store, retrieve, modify, and manage data efficiently.

Key Characteristics of a Database:

1. Structured Storage – Data is stored in tables (rows and columns) for easy organization.
2. Data Integrity – Ensures accuracy and consistency through constraints and relationships.
3. Security – Provides access control and encryption to protect sensitive data.
4. Scalability – Can handle large volumes of data and support multiple users.
5. Querying & Processing – Uses SQL (Structured Query Language) for data manipulation.

Types of Databases:

• Relational Database (RDBMS) – Uses structured tables with relationships (e.g., Oracle, MySQL, SQL Server).
• NoSQL Database – Handles unstructured or semi-structured data (e.g., MongoDB, Cassandra).
• Cloud Database – Hosted on cloud platforms (e.g., Oracle Autonomous Database, AWS RDS).
• Distributed Database – Data is distributed across multiple locations.

What is DBMS ?

A DBMS (Database Management System) is software that manages databases by enabling users to store, retrieve, update, and delete data efficiently. It provides an interface between the database and end-users or applications, ensuring data is organized, secure, and accessible.

Oracle is relational database  i.e. oracle is RDBMS (Relational Database Management System) 

Key Functions of a DBMS:

1. Data Storage & Retrieval – Organizes data in structured formats (tables, indexes, etc.).
2. Data Security & Integrity – Ensures access control, encryption, and consistency.
3. Transaction Management – Supports ACID properties (Atomicity, Consistency, Isolation, Durability).
4. Concurrency Control – Manages multiple users accessing the data simultaneously.
5. Backup & Recovery – Protects data from failures or corruption.

What is RDBMS ?

An RDBMS (Relational Database Management System) is a type of database management system that stores data in a structured, tabular format with relationships between tables. It follows the relational model proposed by E.F. Codd in 1970.

---

Key Features of RDBMS:

1. Tables as Data Storage – Data is stored in rows and columns.
2. Relationships – Tables are linked using Primary Keys and Foreign Keys.
3. ACID Compliance – Ensures Atomicity, Consistency, Isolation, and Durability for reliable transactions.
4. Data Integrity & Constraints – Enforces rules like Primary Key, Foreign Key, Unique, Not Null, Check.
5. SQL Support – Uses Structured Query Language (SQL) for data operations.
6. Concurrency Control – Manages multiple users accessing data simultaneously.

---

Examples of RDBMS:

• Oracle Database – Enterprise-level RDBMS with features like Data Guard, RAC, and Partitioning.
• MySQL – Open-source RDBMS widely used for web applications.
• SQL Server – Microsoft’s RDBMS with BI and analytics features.
• PostgreSQL – Advanced open-source RDBMS with JSON and NoSQL support.

---

Example of RDBMS Structure (EMPLOYEES & DEPARTMENTS Tables):

EMPLOYEES Table

EMP_ID	EMP_NAME	DEPT_ID	SALARY
101	John Doe	1	80000
102	Jane Roe	2	75000
103	Alex Ray	1	90000

DEPARTMENTS Table

DEPT_ID	DEPT_NAME
1	IT
2	HR

Example Query (Fetching Employees with Department Name)

SELECT e.emp_name, d.dept_name 
FROM employees e 
JOIN departments d ON e.dept_id = d.dept_id;

Oracle as an RDBMS

Oracle Database is a high-performance RDBMS that supports advanced features like:
✅ Partitioning – Improves performance for large tables.
✅ RAC (Real Application Clusters) – Ensures high availability.
✅ Data Guard – Provides disaster recovery solutions.
✅ Multitenant Architecture – Supports multiple databases in a single container.

What is  Table ?

A table in a database is a structured object that stores data in a tabular format using rows (records) and columns (fields). It is the fundamental building block of a Relational Database Management System (RDBMS) like Oracle, MySQL, or SQL Server.

Structure of a Table:

• Columns (Fields) – Define the attributes of data (e.g., EMP_ID, EMP_NAME, SALARY).
• Rows (Records) – Store individual data entries. Each row represents a unique record.
• Data Types – Define the type of data a column can store (e.g., NUMBER, VARCHAR2, DATE).

Example of a Table (EMPLOYEES Table):

EMP_ID	EMP_NAME	DEPARTMENT	SALARY	HIRE_DATE
101	John Doe	IT	80000	2023-01-15
102	Jane Roe	HR	75000	2022-11-10
103	Alex Ray	Finance	90000	2021-09-20

What is SQL ?

SQL is a standard programming language used to manage and manipulate relational databases. It allows users to store, retrieve, update, and delete data efficiently. SQL is used in all Relational Database Management Systems (RDBMS) like Oracle, MySQL, SQL Server, and PostgreSQL.

Type of SQL Statement :

SQL is divided into several categories based on its functionality:

1. Data Query Language (DQL) – Used to Retrieve Data

• SELECT – Fetches data from tables.

SELECT emp_name, salary FROM employees WHERE department = 'IT';

2. Data Definition Language (DDL) – Defines Database Structure

• CREATE – Creates a table, view, or database object.
• ALTER – Modifies an existing table structure.
• DROP – Deletes a table or database object.
• TRUNCATE – Removes all records but keeps the structure.

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100),
    salary NUMBER
);

3. Data Manipulation Language (DML) – Modifies Data

• INSERT – Adds new records.
• UPDATE – Modifies existing records.
• DELETE – Removes specific records.

INSERT INTO employees (emp_id, emp_name, salary) VALUES (101, 'John Doe', 80000);

4. Data Control Language (DCL) – Manages User Permissions

• GRANT – Assigns privileges to users.
• REVOKE – Removes privileges from users.

GRANT SELECT ON employees TO user1;

5. Transaction Control Language (TCL) – Manages Transactions

• COMMIT – Saves changes permanently.
• ROLLBACK – Reverts changes to the last save point.
• SAVEPOINT – Creates intermediate save points in a transaction.

UPDATE employees SET salary = salary + 5000 WHERE emp_id = 101;
COMMIT;