Oracle Database — Study Materials

Oracle SQL — Basics

Database & schema fundamentals

A database contains schemas, which organise database objects like tables and views. Schema creation requires user setup with appropriate privileges.

SQL command categories

DDL — Data Definition Language. Commands for structural changes: CREATE, ALTER, DROP, RENAME, TRUNCATE. The CREATE TABLE statement defines table structure, ALTER modifies existing tables, and DROP removes them permanently.

DML — Data Manipulation Language. Commands for data modification: INSERT adds rows, UPDATE modifies existing data, and DELETE removes rows. "You must give Commit after you executes any DML command."

TCL — Transaction Control Language. COMMIT saves transactions permanently, while ROLLBACK undoes unsaved changes.

DQL — Data Query Language. SELECT retrieves data with optional WHERE conditions. DESC displays table structure.

DCL — Data Control Language. GRANT and REVOKE manage permissions.

Practical patterns

The content includes SQL syntax patterns and examples for operations like:

• Creating student records with INSERT
• Updating specific rows with conditional WHERE clauses
• Removing data via DELETE
• Querying with various operators (>, <, =, !=, <>)

Indexes and Views in Oracle

Indexes — overview

Indexes accelerate data retrieval by storing row information and corresponding ROWID data, similar to a book's table of contents. Without indexes, Oracle performs a FULL TABLE SCAN, reading all records sequentially. B-tree is Oracle's default index type.

Index types

B-Tree Index — the standard index type, suitable for key columns.

CREATE INDEX JOB_INDX ON OT.EMPLOYEES(JOB_ID);
CREATE INDEX supplier_idx ON supplier (supplier_name, city);

Bitmap Index — used when values repeat frequently.

CREATE BITMAP INDEX EXP_1 ON HR.EMPLOYEES(GENDER);

Function-Based Index — required when functions appear in WHERE clauses.

CREATE INDEX MAD_IX ON HR.EMPLOYEES(LOWER(name));

Unique Index — enforces uniqueness on indexed columns.

CREATE UNIQUE INDEX MTC_IX ON customer(SSID);

Reverse Key Index — reverses index key values (e.g. 123 becomes 321).

CREATE INDEX MID_IX ON customer(customer_id) REVERSE;

Compressed Index — reduces space for multi-column indexes with data repetition.

CREATE INDEX INDX_COMP ON HR.EMPLOYEES (FIRST_NAME, LAST_NAME) COMPRESS 2;

Managing indexes

View indexes:

SELECT * FROM user_indexes WHERE table_name = 'CUSTOMERS';

Drop index:

DROP INDEX Index_name;

Views — definition

A virtual table created from query results without physically storing data. Views simplify complexity and enhance security by restricting column access.

CREATE VIEW syntax:

CREATE VIEW view_name AS
SELECT column1, column2 FROM table
WHERE condition;

Simple view:

CREATE VIEW empview AS
SELECT *
FROM employees
WHERE employees.manager_id = 100;

View with concatenation:

CREATE VIEW employeeview1 AS
SELECT
  employee_id,
  first_name || ' ' || last_name full_name,
  phone_NUMBER
FROM employees;

View with column aliases:

CREATE VIEW employeeview1 AS
SELECT employee_id, first_name || ' ' || last_name "full name",
  FLOOR(months_between(CURRENT_DATE, hire_date) / 12) as years
FROM employees;

Multi-table view:

CREATE OR REPLACE VIEW dept_manger AS
SELECT employee_id, first_name || ' ' || last_name as name, department_name
FROM EMPLOYEES
INNER JOIN DEPARTMENTS
  ON EMPLOYEES.EMPLOYEE_ID = DEPARTMENTS.MANAGER_ID;

Drop view:

DROP VIEW employee_view;

Advantages

• Restricts access to selected columns and data
• Simplifies complex queries through joins
• Enhances security
• Improves performance for complex queries
• Consumes no memory space

Disadvantages

• DML operations unsupported
• Becomes inactive if underlying table is dropped

Inline view

An inline view is a subquery in the FROM clause, also called a derived table or sub-select.

Example 1 — Top 10 products:

SELECT * FROM
(
  SELECT product_id, product_name, list_price
  FROM products
  ORDER BY list_price DESC
)
WHERE ROWNUM <= 10;

Example 2 — Category analysis:

SELECT category_name, max_list_price FROM
product_categories a,
(
  SELECT category_id, MAX(list_price) max_list_price
  FROM products
  GROUP BY category_id
) b
WHERE a.category_id = b.category_id
ORDER BY category_name;

Distinct, Like, Operators, Where, Null & Order by

SELECT DISTINCT

The DISTINCT statement returns only unique values from a column. When a table contains duplicate values, this clause filters for different entries.

SELECT DISTINCT column1, column2, ...
FROM table_name;

Examples:

SELECT DISTINCT working_area FROM AGENTS;
SELECT DISTINCT * FROM AGENTS;
SELECT COUNT(DISTINCT working_area) FROM AGENTS;

WHERE clause

Used to filter records based on specified conditions. Works in SELECT, UPDATE and DELETE statements.

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

Examples:

SELECT * FROM AGENTS WHERE working_area = 'Bangalore';
SELECT * FROM AGENTS WHERE COMMISSION = 0.15;

AND, OR, NOT operators

• AND — all conditions must be TRUE
• OR — at least one condition must be TRUE
• NOT — condition must NOT be TRUE

SELECT * FROM CUSTOMER WHERE GRADE > 2 AND OPENING_AMT > 6000;
SELECT * FROM CUSTOMER WHERE GRADE > 2 OR OPENING_AMT > 6000;
SELECT * FROM CUSTOMER WHERE NOT CUST_CITY = 'Bangalore';

LIKE operator

Searches for patterns in columns using wildcards:

• % — zero, one or multiple characters
• _ — exactly one character

SELECT first_name, last_name FROM contacts WHERE last_name LIKE 'St%';
SELECT first_name, last_name FROM contacts WHERE last_name LIKE '%er';
SELECT first_name, last_name FROM contacts WHERE first_name LIKE 'Je_i';
SELECT first_name, last_name FROM contacts WHERE first_name LIKE '_r%';

ORDER BY clause

Sorts results in ascending (default) or descending order.

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC | DESC;

Examples:

SELECT name, address, credit_limit FROM customers ORDER BY name ASC;
SELECT name, address, credit_limit FROM customers ORDER BY name DESC;
SELECT order_id, customer_id FROM orders ORDER BY order_date DESC;

IS NULL and IS NOT NULL

NULL represents missing or inapplicable information and cannot be compared with standard operators.

SELECT * FROM orders WHERE salesman_id IS NULL;
SELECT * FROM orders WHERE salesman_id IS NOT NULL;