Oracle SQL Training

2: Introduction to Databases & SQL

Concepts:

 Database: Structured collection of data stored electronically.

 RDBMS (Relational Database Management System):

Organizes data into tables with rows (records) and columns
(attributes).

 Tables:

o Row = One record

o Column = Attribute of a record

 SQL (Structured Query Language): Standard language for

interacting with RDBMS.

SQL Categories:

 DDL (Data Definition Language): CREATE, ALTER, DROP

 DML (Data Manipulation Language): INSERT, UPDATE, DELETE

 DQL (Data Query Language): SELECT

 TCL (Transaction Control Language): COMMIT, ROLLBACK,

SAVEPOINT

 DCL (Data Control Language): GRANT, REVOKE

Example Diagram: ER diagram showing tables and PK/FK relationships.

3: Data Definition Language (DDL)

 Purpose: Define, modify, or delete database structures.

 Key Commands: CREATE, ALTER, DROP, TRUNCATE

Example – CREATE TABLE:

CREATE TABLE employees (

emp_id NUMBER PRIMARY KEY,

first_name VARCHAR2(50) NOT NULL,

last_name VARCHAR2(50),
 email VARCHAR2(100) UNIQUE,

hire_date DATE DEFAULT SYSDATE,

salary NUMBER(10,2) CHECK (salary > 0)

);

ALTER Table Example:

ALTER TABLE employees ADD dept_id NUMBER;

ALTER TABLE employees MODIFY salary NUMBER(12,2);

ALTER TABLE employees DROP COLUMN dept_id;

DROP/TRUNCATE Example:

DROP TABLE employees;

TRUNCATE TABLE employees;

Constraints Overview: Primary Key, Foreign Key, Unique, Not Null,

Check, Default

4: Data Manipulation Language (DML)

 INSERT: Add new rows

INSERT INTO employees (emp_id, first_name, last_name, email, salary)

VALUES (101, 'John', 'Doe', '[Link]@[Link]', 6000);

 UPDATE: Modify existing rows

UPDATE employees SET salary = 7000 WHERE emp_id = 101;

 DELETE: Remove rows

DELETE FROM employees WHERE emp_id = 101;

Notes: Always use WHERE in UPDATE/DELETE to avoid affecting all rows.

5: Querying Data (SELECT / DQL)

 Basic SELECT:

SELECT * FROM employees;

SELECT first_name, last_name, salary FROM employees;

 Filtering with WHERE:

SELECT * FROM employees WHERE salary > 5000;

 Sorting with ORDER BY:

SELECT first_name, salary FROM employees ORDER BY salary DESC;

 Operators: =, <, >, <=, >=, !=, AND, OR, NOT, BETWEEN, IN,
LIKE, IS NULL

6: SQL Functions

 Single-Row Functions: Operate on individual rows

o Character: UPPER(first_name), LOWER(last_name),

SUBSTR(first_name,1,3)

o Number: ROUND(salary,0), TRUNC(salary), MOD(salary,1000)

o Date: SYSDATE, ADD_MONTHS(hire_date,6),

MONTHS_BETWEEN(SYSDATE,hire_date)

 Aggregate Functions: COUNT(*), SUM(salary), AVG(salary),

MAX(salary), MIN(salary)
Example:

SELECT dept_id, SUM(salary) FROM employees GROUP BY dept_id;

7: Grouping & Filtering Data

 GROUP BY: Aggregate rows by column

 HAVING: Filter groups after aggregation

SELECT dept_id, AVG(salary)

FROM employees

GROUP BY dept_id

HAVING AVG(salary) > 5000;

 Notes: WHERE filters rows before aggregation; HAVING filters

aggregated results
8: Joins

 Inner Join: Matching rows

 Left Join: All left rows + matched right rows

 Right Join: All right rows + matched left rows

 Full Join: All rows from both tables

 Cross Join: Cartesian product

 Self Join: Join table with itself

Example:

SELECT e.first_name, d.dept_name

FROM employees e

JOIN departments d ON e.dept_id = d.dept_id;

9: Subqueries

 Single-Row Subquery:

SELECT first_name FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

 Multi-Row Subquery:

SELECT first_name FROM employees

WHERE dept_id IN (SELECT dept_id FROM departments WHERE

location='NY');

 Correlated Subquery:

SELECT e.first_name FROM employees e

WHERE [Link] > (SELECT AVG(salary) FROM employees WHERE dept_id

= e.dept_id);

10: Data Integrity & Indexes

 Indexes: Improve SELECT performance

CREATE INDEX idx_emp_salary ON employees(salary);

  Constraints: Primary Key, Foreign Key, Unique, Not Null, Check,
Default

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id)

REFERENCES departments(dept_id);

11: Transactions (TCL)

UPDATE employees SET salary = salary + 500;

SAVEPOINT before_raise;

UPDATE employees SET salary = salary + 1000 WHERE dept_id = 10;

ROLLBACK TO before_raise;

COMMIT;

 Ensures atomicity, consistency, isolation, durability (ACID

properties).

12: Views & Synonyms

 View Example:

CREATE VIEW emp_view AS

SELECT first_name, last_name, salary

FROM employees

WHERE salary > 5000;

 Synonym Example:

CREATE SYNONYM emp_syn FOR employees;

SELECT * FROM emp_syn;

13: Sequences

CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 1;

INSERT INTO employees (emp_id, first_name) VALUES (emp_seq.NEXTVAL,

'Alice');

 NEXTVAL generates next number; CURRVAL shows current number

14: Advanced SQL Concepts (Expanded)

 Inline Views:

SELECT dept_id, AVG(salary) AS avg_sal

FROM (SELECT * FROM employees WHERE salary > 3000)

GROUP BY dept_id;

 CTE (WITH clause):

WITH dept_avg AS (

SELECT dept_id, AVG(salary) AS avg_sal

FROM employees

GROUP BY dept_id

SELECT dept_id, avg_sal

FROM dept_avg

WHERE avg_sal > 5000;

 Analytical Functions:

SELECT emp_id, dept_id, salary,

RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS

dept_rank

FROM employees;

 Set Operators: UNION, UNION ALL, INTERSECT, MINUS

15: Loading Data from External Systems (Expanded)

1. SQL*Loader: Bulk load CSV/TXT

LOAD DATA

INFILE '[Link]'

INTO TABLE employees

FIELDS TERMINATED BY ','

(emp_id, first_name, last_name, email, salary)

 Run: sqlldr userid=username/password control=[Link]

2. External Tables: Query CSV directly

CREATE TABLE employees_ext (

emp_id NUMBER,

first_name VARCHAR2(50),

last_name VARCHAR2(50),

salary NUMBER(10,2)

ORGANIZATION EXTERNAL (

TYPE ORACLE_LOADER

DEFAULT DIRECTORY ext_dir

ACCESS PARAMETERS (

RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY ','

LOCATION ('[Link]')

REJECT LIMIT UNLIMITED;

SELECT * FROM employees_ext;

3. Data Pump: Export/import

expdp hr/hr DIRECTORY=dp_dir DUMPFILE=[Link] TABLES=employees

impdp hr/hr DIRECTORY=dp_dir DUMPFILE=[Link] TABLES=employees

4. DB Links: Query remote database

SELECT * FROM employees@remote_db;

16: JSON Data in Oracle

CREATE TABLE orders_json (

order_id NUMBER PRIMARY KEY,

 order_data CLOB

);

INSERT INTO orders_json VALUES (1, '{"customer":"John

Doe","amount":500}');

 Query JSON:

SELECT json_value(order_data, '$.customer') AS customer,

json_value(order_data, '$.amount') AS amount

FROM orders_json;

17: Exposing JSON via ORDS

 Enable ORDS for schema, create REST module & GET endpoint.

 Example URL: [Link]

 Returns JSON output for client applications.

18: Recent Oracle Features

 Automatic Indexing

 SQL Macros

 JSON Enhancements (JSON_TRANSFORM)

 Analytical/Aggregate functions

 Blockchain / Immutable Tables

 Automatic Materialized Views

 Security enhancements
19: Introduction to PL/SQL

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World');

END;

 Variables, loops, IF conditions

 Cursors (Implicit/Explicit)

 Procedures & Functions

20: Summary / Key Takeaways

 SQL basics → Advanced SQL → PL/SQL → JSON & ORDS

 Hands-on practice is essential

 Keep learning new features (19c/21c/23c)

 Focus on data integrity, indexing, and performance