-- =========================================================
-- ORACLE SQL DDL CONSOLIDATED REFERENCE
-- Author : Vinayak Vishweshwara Dabgar
-- Database Expert | Oracle Apps DBA | DBA | Developer
-- QA Format for quick references and interview prep
-- =========================================================
-- =========================================================
-- ORACLE SQL DDL Question-Answer Format
-- =========================================================
-- =========================================================
-- SECTION 1
-- SQL LANGUAGE FOUNDATION
-- =========================================================
-- =========================================================
-- QUESTION 1
-- WHAT IS SQL?
--
-- SQL (Structured Query Language) is the standard language
-- used to communicate with relational databases.
--
-- SQL allows users to:
-- create database structures
-- manipulate stored data
-- retrieve information
-- manage transactions
-- control access permissions
-- =========================================================
-- =========================================================
-- QUESTION 2
-- WHAT ARE THE MAIN SQL LANGUAGE CATEGORIES?
--
-- SQL commands are divided into five categories:
--
-- DDL β Data Definition Language
-- DML β Data Manipulation Language
-- DQL β Data Query Language
-- TCL β Transaction Control Language
-- DCL β Data Control Language
-- =========================================================
-- =========================================================
-- QUESTION 3
-- WHAT IS DDL (DATA DEFINITION LANGUAGE)?
--
-- DDL defines and manages database objects.
--
-- DDL controls the structure of database objects rather
-- than the data inside them.
--
-- Common objects created or modified using DDL:
--
-- tables
-- views
-- indexes
-- sequences
-- synonyms
-- users
-- procedures
-- database links
--
-- Important property:
-- DDL statements automatically perform COMMIT operations.
-- A commit occurs both before and after execution.
-- Therefore DDL cannot be rolled back.
-- =========================================================
-- =========================================================
-- QUESTION 4
-- WHAT COMMANDS BELONG TO DDL?
--
-- Common DDL commands include:
--
-- CREATE
-- ALTER
-- DROP
-- TRUNCATE
-- RENAME
-- COMMENT
-- FLASHBACK
-- PURGE
-- =========================================================
-- =========================================================
-- QUESTION 5
-- WHAT IS DML (DATA MANIPULATION LANGUAGE)?
--
-- DML commands manipulate table data.
--
-- Examples:
--
-- INSERT
-- UPDATE
-- DELETE
--
-- These commands modify rows stored inside tables.
-- =========================================================
-- =========================================================
-- QUESTION 6
-- WHAT IS DQL (DATA QUERY LANGUAGE)?
--
-- DQL retrieves information from database tables.
--
-- The primary DQL command is:
--
-- SELECT
-- =========================================================
-- =========================================================
-- QUESTION 7
-- WHAT IS TCL (TRANSACTION CONTROL LANGUAGE)?
--
-- TCL manages database transactions.
--
-- Commands include:
--
-- COMMIT
-- ROLLBACK
-- SAVEPOINT
-- =========================================================
-- =========================================================
-- QUESTION 8
-- WHAT IS DCL (DATA CONTROL LANGUAGE)?
--
-- DCL controls user privileges and permissions.
--
-- Commands include:
--
-- GRANT
-- REVOKE
-- =========================================================
-- =========================================================
-- QUESTION 9
-- WHAT IS A TABLE?
--
-- A table is the primary database object used to store
-- data in rows and columns.
--
-- Tables are the core storage structure in relational
-- databases.
-- =========================================================
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
hire_date DATE,
salary NUMBER(10,2)
);
-- =========================================================
-- QUESTION 10
-- WHAT IS A SCHEMA?
--
-- A schema is a logical container for database objects.
--
-- Each database user owns exactly one schema.
--
-- Schemas may contain:
--
-- tables
-- views
-- indexes
-- sequences
-- procedures
-- =========================================================
-- =========================================================
-- QUESTION 11
-- WHAT IS A DATABASE USER?
--
-- A user is a database account used to connect to
-- the Oracle database.
--
-- Users can:
--
-- own objects
-- execute SQL commands
-- be granted privileges
-- =========================================================
CREATE USER hr IDENTIFIED BY welcome123;
GRANT CONNECT, RESOURCE TO hr;
-- =========================================================
-- QUESTION 12
-- RELATIONSHIP BETWEEN USERS, SCHEMAS AND OBJECTS
--
-- User owns Schema
-- Schema contains Objects
--
-- Objects include:
--
-- tables
-- views
-- indexes
-- sequences
-- procedures
-- synonyms
-- =========================================================
-- =========================================================
-- DATA TYPES
-- =========================================================
-- =========================================================
-- QUESTION 13
-- WHAT ARE THE MAIN ORACLE DATA TYPE CATEGORIES?
--
-- Numeric types
-- Character types
-- Date and time types
-- =========================================================
-- =========================================================
-- QUESTION 14
-- NUMERIC DATA TYPES
--
-- NUMBER
-- FLOAT
-- INTEGER
-- =========================================================
-- =========================================================
-- QUESTION 15
-- CHARACTER DATA TYPES
--
-- VARCHAR2
-- CHAR
-- NVARCHAR2
-- =========================================================
-- =========================================================
-- QUESTION 16
-- DATE AND TIME DATA TYPES
--
-- DATE
-- TIMESTAMP
-- TIMESTAMP WITH TIME ZONE
-- =========================================================
-- =========================================================
-- QUESTION 17
-- BOOLEAN DATA TYPE (Oracle 23ai)
--
-- Oracle 23ai introduces native BOOLEAN support
-- in SQL tables.
-- =========================================================
CREATE TABLE employees_bool (
emp_id NUMBER,
is_active BOOLEAN
);
-- =========================================================
-- TABLE CREATION AND CONSTRAINTS
-- =========================================================
-- =========================================================
-- QUESTION 18
-- BASIC CREATE TABLE SYNTAX
-- =========================================================
CREATE TABLE departments (
dept_id NUMBER,
dept_name VARCHAR2(100)
);
-- =========================================================
-- QUESTION 19
-- PRIMARY KEY CONSTRAINT
--
-- Ensures unique identification of each row.
-- =========================================================
CREATE TABLE dept_pk (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(100)
);
-- =========================================================
-- QUESTION 20
-- UNIQUE CONSTRAINT
--
-- Ensures column values remain unique.
-- =========================================================
CREATE TABLE users_unique (
email VARCHAR2(200) UNIQUE
);
-- =========================================================
-- QUESTION 21
-- NOT NULL CONSTRAINT
--
-- Prevents NULL values in a column.
-- =========================================================
CREATE TABLE employees_nn (
emp_id NUMBER,
name VARCHAR2(100) NOT NULL
);
-- =========================================================
-- QUESTION 22
-- CHECK CONSTRAINT
--
-- Validates data based on a condition.
-- =========================================================
CREATE TABLE employees_check (
emp_id NUMBER,
salary NUMBER CHECK (salary > 0)
);
-- =========================================================
-- QUESTION 23
-- FOREIGN KEY CONSTRAINT
--
-- Maintains referential integrity between tables.
-- =========================================================
CREATE TABLE emp_fk (
emp_id NUMBER PRIMARY KEY,
dept_id NUMBER,
CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
);
-- =========================================================
-- ALTER TABLE OPERATIONS
-- =========================================================
-- =========================================================
-- QUESTION 24
-- ADD COLUMN
-- =========================================================
ALTER TABLE employees
ADD department_id NUMBER;
-- =========================================================
-- QUESTION 25
-- ADD MULTIPLE COLUMNS
-- =========================================================
ALTER TABLE employees
ADD (
phone_number VARCHAR2(20),
address VARCHAR2(200)
);
-- =========================================================
-- QUESTION 26
-- MODIFY COLUMN DATATYPE
-- =========================================================
ALTER TABLE employees
MODIFY salary NUMBER(12,2);
-- =========================================================
-- QUESTION 27
-- DROP COLUMN
-- =========================================================
ALTER TABLE employees
DROP COLUMN address;
-- =========================================================
-- QUESTION 28
-- RENAME COLUMN
-- =========================================================
ALTER TABLE employees
RENAME COLUMN emp_name TO full_name;
-- =========================================================
-- QUESTION 29
-- ADD CONSTRAINT
-- =========================================================
ALTER TABLE employees
ADD CONSTRAINT salary_chk
CHECK (salary > 0);
-- =========================================================
-- QUESTION 30
-- DROP CONSTRAINT
-- =========================================================
ALTER TABLE employees
DROP CONSTRAINT salary_chk;
-- =========================================================
-- QUESTION 31
-- DISABLE CONSTRAINT
-- =========================================================
ALTER TABLE employees
DISABLE CONSTRAINT salary_chk;
-- =========================================================
-- QUESTION 32
-- ENABLE CONSTRAINT
-- =========================================================
ALTER TABLE employees
ENABLE CONSTRAINT salary_chk;
TABLE MANAGEMENT OPERATIONS
INDEXES
VIEWS
MATERIALIZED VIEWS
SEQUENCES
IDENTITY COLUMNS
SYNONYMS
-- =========================================================
-- TABLE MANAGEMENT OPERATIONS
-- =========================================================
-- =========================================================
-- QUESTION 33
-- DROP TABLE
--
-- DROP TABLE removes a table permanently from the database.
-- All data and structure are deleted.
-- =========================================================
DROP TABLE employees;
-- =========================================================
-- QUESTION 34
-- DROP TABLE WITH CASCADE CONSTRAINTS
--
-- Removes dependent foreign key constraints.
-- =========================================================
DROP TABLE employees CASCADE CONSTRAINTS;
-- =========================================================
-- QUESTION 35
-- TRUNCATE TABLE
--
-- Removes all rows quickly.
--
-- TRUNCATE is faster than DELETE because it does not
-- generate undo for individual rows.
--
-- TRUNCATE cannot be rolled back.
-- =========================================================
TRUNCATE TABLE employees;
-- =========================================================
-- QUESTION 36
-- RENAME OBJECT
--
-- Used to rename database objects.
-- =========================================================
RENAME employees TO staff;
-- =========================================================
-- QUESTION 37
-- COMMENT STATEMENTS
--
-- Used to document database objects.
-- =========================================================
COMMENT ON TABLE employees
IS 'Stores employee information';
COMMENT ON COLUMN employees.salary
IS 'Monthly salary';
-- =========================================================
-- QUESTION 38
-- PURGE COMMAND
--
-- Permanently removes objects from recycle bin.
-- =========================================================
PURGE TABLE employees;
PURGE RECYCLEBIN;
-- =========================================================
-- QUESTION 39
-- RECYCLE BIN
--
-- Dropped tables are stored temporarily in the recycle bin.
-- They can be restored using FLASHBACK.
-- =========================================================
SHOW RECYCLEBIN;
-- =========================================================
-- INDEXES
-- =========================================================
-- =========================================================
-- QUESTION 40
-- WHAT IS AN INDEX?
--
-- An index improves the speed of data retrieval.
--
-- It works similarly to an index in a book.
-- =========================================================
CREATE INDEX idx_emp_name
ON employees(emp_name);
-- =========================================================
-- QUESTION 41
-- FUNCTION-BASED INDEX
--
-- Index created on an expression or function.
-- =========================================================
CREATE INDEX idx_upper_name
ON employees (UPPER(emp_name));
-- =========================================================
-- QUESTION 42
-- BITMAP INDEX
--
-- Efficient for columns with low cardinality.
-- =========================================================
CREATE BITMAP INDEX idx_gender
ON employees(gender);
-- =========================================================
-- QUESTION 43
-- PARTITIONED INDEX
--
-- Index divided into partitions matching table partitions.
-- =========================================================
-- =========================================================
-- QUESTION 44
-- AUTOMATIC INDEXING (Oracle 19c+)
--
-- Oracle automatically creates indexes using AI algorithms
-- to improve performance.
-- =========================================================
-- =========================================================
-- VIEWS
-- =========================================================
-- =========================================================
-- QUESTION 45
-- WHAT IS A VIEW?
--
-- A view is a virtual table created from a SELECT query.
--
-- Views do not store data themselves.
-- =========================================================
CREATE VIEW emp_view AS
SELECT emp_id, emp_name
FROM employees;
-- =========================================================
-- QUESTION 46
-- CREATE OR REPLACE VIEW
--
-- Replaces an existing view definition.
-- =========================================================
CREATE OR REPLACE VIEW emp_view
AS
SELECT *
FROM employees;
-- =========================================================
-- QUESTION 47
-- MATERIALIZED VIEW
--
-- Stores the result of a query physically.
-- Used for reporting and data warehousing.
-- =========================================================
-- =========================================================
-- QUESTION 48
-- MATERIALIZED VIEW LOG
--
-- Required for fast refresh of materialized views.
-- =========================================================
CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID, SEQUENCE (employee_id, department_id, salary)
INCLUDING NEW VALUES;
-- =========================================================
-- QUESTION 49
-- CREATE MATERIALIZED VIEW
-- =========================================================
CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
AS
SELECT department_id,
COUNT(*) emp_count
FROM employees
GROUP BY department_id;
-- =========================================================
-- QUESTION 50
-- MANUAL REFRESH OF MATERIALIZED VIEW
-- =========================================================
EXEC DBMS_MVIEW.REFRESH('EMP_MV','F');
-- =========================================================
-- QUESTION 51
-- ON COMMIT MATERIALIZED VIEW
-- =========================================================
CREATE MATERIALIZED VIEW emp_mv_auto
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT department_id,
COUNT(*) emp_count
FROM employees
GROUP BY department_id;
-- =========================================================
-- SEQUENCES AND IDENTITY COLUMNS
-- =========================================================
-- =========================================================
-- QUESTION 52
-- WHAT IS A SEQUENCE?
--
-- A sequence generates unique numeric values.
-- Commonly used for primary keys.
-- =========================================================
CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1;
-- =========================================================
-- QUESTION 53
-- USING SEQUENCE NEXTVAL
-- =========================================================
INSERT INTO employees(emp_id,emp_name)
VALUES(emp_seq.NEXTVAL,'John Doe');
-- =========================================================
-- QUESTION 54
-- SEQUENCE CURRVAL
-- =========================================================
SELECT emp_seq.CURRVAL FROM dual;
-- =========================================================
-- QUESTION 55
-- DROP SEQUENCE
-- =========================================================
DROP SEQUENCE emp_seq;
-- =========================================================
-- QUESTION 56
-- IDENTITY COLUMN
--
-- Automatically generates numeric values.
-- =========================================================
CREATE TABLE employees_identity (
emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
emp_name VARCHAR2(100)
);
-- =========================================================
-- QUESTION 57
-- IDENTITY COLUMN ENHANCEMENTS (23ai)
--
-- Improved identity handling and performance.
-- =========================================================
-- =========================================================
-- SYNONYMS
-- =========================================================
-- =========================================================
-- QUESTION 58
-- WHAT IS A SYNONYM?
--
-- A synonym is an alternate name for a database object.
-- =========================================================
CREATE SYNONYM emp_syn
FOR hr.employees;
-- =========================================================
-- QUESTION 59
-- PRIVATE SYNONYM
--
-- Accessible only within the schema.
-- =========================================================
CREATE SYNONYM emp
FOR hr.employees;
-- =========================================================
-- QUESTION 60
-- PUBLIC SYNONYM
--
-- Accessible by all database users.
-- =========================================================
CREATE PUBLIC SYNONYM emp_public
FOR hr.employees;
-- =========================================================
-- QUESTION 61
-- DROP SYNONYM
-- =========================================================
DROP SYNONYM emp_syn;
DROP PUBLIC SYNONYM emp_public;
-- =========================================================
-- SECTION 11
-- STORAGE AND TABLESPACE
-- =========================================================
-- =========================================================
-- QUESTION 62
-- WHAT IS A TABLESPACE?
--
-- A tablespace is a logical storage unit in Oracle.
--
-- Tablespaces contain datafiles where database objects
-- such as tables and indexes are physically stored.
-- =========================================================
CREATE TABLESPACE hr_data
DATAFILE 'hr_data01.dbf'
SIZE 100M;
-- =========================================================
-- QUESTION 63
-- STORAGE CLAUSES
--
-- Storage parameters control how space is allocated.
--
-- Examples:
--
-- PCTFREE
-- PCTUSED
-- INITRANS
-- MAXTRANS
-- STORAGE
-- =========================================================
-- =========================================================
-- QUESTION 64
-- TABLE COMPRESSION
--
-- Reduces disk storage usage and improves I/O efficiency.
-- =========================================================
CREATE TABLE compressed_sales (
sale_id NUMBER,
amount NUMBER
)
COMPRESS FOR OLTP;
-- =========================================================
-- QUESTION 65
-- IN-MEMORY TABLES
--
-- Oracle In-Memory stores column data in memory to
-- accelerate analytics queries.
-- =========================================================
CREATE TABLE sales_data (
sale_id NUMBER,
amount NUMBER
)
INMEMORY;
-- =========================================================
-- FLASHBACK TECHNOLOGY
-- =========================================================
-- =========================================================
-- QUESTION 66
-- WHAT IS FLASHBACK TABLE?
--
-- FLASHBACK TABLE restores a table to a previous
-- point in time without restoring backups.
-- =========================================================
FLASHBACK TABLE employees
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
-- =========================================================
-- QUESTION 67
-- FLASHBACK TABLE REQUIREMENTS
--
-- Requirements include:
--
-- Flashback privilege
-- Row movement enabled
-- Sufficient UNDO retention
-- =========================================================
ALTER TABLE employees ENABLE ROW MOVEMENT;
-- =========================================================
-- QUESTION 68
-- FLASHBACK DROPPED TABLE
--
-- Restores a dropped table from the recycle bin.
-- =========================================================
FLASHBACK TABLE employees TO BEFORE DROP;
-- =========================================================
-- QUESTION 69
-- FLASHBACK DATABASE
--
-- Restores the entire database to an earlier state.
-- Requires Flash Recovery Area (FRA).
-- =========================================================
ALTER DATABASE FLASHBACK ON;
-- =========================================================
-- QUESTION 70
-- STORAGE USED BY FLASHBACK
--
-- FLASHBACK TABLE uses UNDO
-- FLASHBACK DROP uses Recycle Bin
-- FLASHBACK DATABASE uses FRA
-- =========================================================
-- =========================================================
-- CREATE TABLE AS SELECT (CTAS)
-- =========================================================
-- =========================================================
-- QUESTION 71
-- WHAT IS CTAS?
--
-- CTAS creates a table using the result of a SELECT query.
-- =========================================================
CREATE TABLE emp_backup
AS
SELECT *
FROM employees;
-- =========================================================
-- QUESTION 72
-- CTAS STRUCTURE ONLY
--
-- Creates only the table structure without copying data.
-- =========================================================
CREATE TABLE emp_copy
AS
SELECT *
FROM employees
WHERE 1 = 0;
-- =========================================================
-- QUESTION 73
-- CTAS LIMITATIONS
--
-- The following are not copied:
--
-- constraints
-- indexes
-- triggers
-- privileges
-- comments
-- =========================================================
-- =========================================================
-- TABLE PARTITIONING
-- =========================================================
-- =========================================================
-- QUESTION 74
-- WHAT IS TABLE PARTITIONING?
--
-- Partitioning divides large tables into smaller segments
-- called partitions.
--
-- Benefits:
-- improved performance
-- easier maintenance
-- better scalability
-- =========================================================
-- =========================================================
-- QUESTION 75
-- RANGE PARTITIONING
-- =========================================================
CREATE TABLE sales_range (
sale_id NUMBER,
sale_dt DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_dt)
(
PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01')
);
-- =========================================================
-- QUESTION 76
-- LIST PARTITIONING
-- =========================================================
CREATE TABLE customers (
customer_id NUMBER,
region VARCHAR2(50)
)
PARTITION BY LIST (region)
(
PARTITION p_asia VALUES ('INDIA','JAPAN'),
PARTITION p_europe VALUES ('UK','GERMANY')
);
-- =========================================================
-- QUESTION 77
-- HASH PARTITIONING
-- =========================================================
CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;
-- =========================================================
-- QUESTION 78
-- INTERVAL PARTITIONING
-- =========================================================
CREATE TABLE sales_interval (
sale_id NUMBER,
sale_dt DATE
)
PARTITION BY RANGE (sale_dt)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION p_start VALUES LESS THAN (DATE '2025-01-01')
);
-- =========================================================
-- QUESTION 79
-- REFERENCE PARTITIONING
-- =========================================================
CREATE TABLE orders_child
(
order_id NUMBER,
customer_id NUMBER,
CONSTRAINT fk_orders
FOREIGN KEY(customer_id)
REFERENCES customers(customer_id)
)
PARTITION BY REFERENCE (fk_orders);
-- =========================================================
-- QUESTION 80
-- COMPOSITE PARTITIONING
--
-- Combines multiple partitioning methods.
-- =========================================================
CREATE TABLE sales_comp (
id NUMBER,
sale_date DATE,
cust_id NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (cust_id)
(
PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01')
);
-- =========================================================
-- QUESTION 81
-- SYSTEM PARTITIONING
--
-- Application controls partition placement.
-- =========================================================
-- =========================================================
-- ADVANCED TABLE TYPES
-- =========================================================
-- =========================================================
-- QUESTION 82
-- INDEX ORGANIZED TABLE
--
-- Data stored in index structure instead of heap.
-- =========================================================
CREATE TABLE customers_iot (
customer_id NUMBER PRIMARY KEY,
name VARCHAR2(100)
)
ORGANIZATION INDEX;
-- =========================================================
-- QUESTION 83
-- GLOBAL TEMPORARY TABLE
--
-- Stores session-specific or transaction-specific data.
-- =========================================================
CREATE GLOBAL TEMPORARY TABLE temp_sales (
id NUMBER,
amount NUMBER
)
ON COMMIT DELETE ROWS;
-- =========================================================
-- QUESTION 84
-- INVISIBLE COLUMNS
--
-- Hidden from SELECT * queries.
-- =========================================================
CREATE TABLE test_table (
id NUMBER,
hidden_col VARCHAR2(50) INVISIBLE
);
-- =========================================================
-- QUESTION 85
-- VIRTUAL COLUMNS
--
-- Column value derived from other columns.
-- =========================================================
CREATE TABLE sales_virtual (
quantity NUMBER,
price NUMBER,
total NUMBER GENERATED ALWAYS AS (quantity * price) VIRTUAL
);
-- =========================================================
-- QUESTION 86
-- EXTERNAL TABLES
--
-- Allows querying external files as tables.
-- =========================================================
CREATE TABLE ext_employees (
id NUMBER,
name VARCHAR2(100)
)
ORGANIZATION EXTERNAL;
-- =========================================================
-- ORACLE SQL DDL CONSOLIDATED REFERENCE
-- REORGANIZED EDITION
-- =========================================================
-- =========================================================
-- OBJECT TYPES (OBJECT ORIENTED DATABASE FEATURES)
-- =========================================================
-- =========================================================
-- QUESTION 87
-- WHAT IS AN OBJECT TYPE?
--
-- Oracle supports object-oriented programming features.
-- Object types allow defining objects with attributes
-- and methods.
-- =========================================================
CREATE TYPE person_type AS OBJECT (
id NUMBER,
name VARCHAR2(100)
);
-- =========================================================
-- QUESTION 88
-- TYPE BODY
--
-- Methods for object types are implemented in TYPE BODY.
-- =========================================================
CREATE TYPE BODY person_type
AS
MEMBER FUNCTION get_name RETURN VARCHAR2
IS
BEGIN
RETURN name;
END;
END;
-- =========================================================
-- QUESTION 89
-- OBJECT TABLE
--
-- Stores rows as objects of a defined object type.
-- =========================================================
CREATE TABLE persons
OF person_type;
-- =========================================================
-- QUESTION 90
-- VARRAY COLLECTION
--
-- VARRAY is a variable-sized array collection.
-- =========================================================
CREATE TYPE phone_list
AS VARRAY(5)
OF VARCHAR2(20);
-- =========================================================
-- QUESTION 91
-- NESTED TABLE COLLECTION
--
-- Nested tables store collections of rows.
-- =========================================================
CREATE TYPE skill_list
AS TABLE OF VARCHAR2(50);
-- =========================================================
-- DATABASE CONNECTIVITY OBJECTS
-- =========================================================
-- =========================================================
-- QUESTION 92
-- DIRECTORY OBJECT
--
-- Directory objects map Oracle database to OS directories.
-- =========================================================
CREATE DIRECTORY data_dir
AS '/u01/oracle/data';
-- =========================================================
-- QUESTION 93
-- DATABASE LINK
--
-- Database links allow access to objects in another
-- Oracle database.
-- =========================================================
CREATE DATABASE LINK remote_db
CONNECT TO hr IDENTIFIED BY password
USING 'ORCL';
-- =========================================================
-- SECTION 18
-- COMMON TABLE EXPRESSIONS (CTE)
-- =========================================================
-- =========================================================
-- QUESTION 94
-- WHAT IS A CTE?
--
-- A Common Table Expression is a temporary result set
-- defined using the WITH clause.
-- =========================================================
WITH high_salary AS (
SELECT employee_id, salary
FROM employees
WHERE salary > 8000
)
SELECT *
FROM high_salary;
-- =========================================================
-- QUESTION 95
-- RECURSIVE CTE
--
-- Recursive CTEs reference themselves and are used
-- for hierarchical queries.
-- =========================================================
WITH emp_hierarchy
(employee_id, manager_id, level_no) AS (
SELECT employee_id, manager_id, 1
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id,
e.manager_id,
h.level_no + 1
FROM employees e
JOIN emp_hierarchy h
ON e.manager_id = h.employee_id
)
SELECT *
FROM emp_hierarchy;
-- =========================================================
-- QUESTION 96
-- MULTIPLE CTEs
--
-- Multiple CTEs can be chained together.
-- =========================================================
WITH dept_count AS (
SELECT department_id, COUNT(*) cnt
FROM employees
GROUP BY department_id
),
high_dept AS (
SELECT department_id
FROM dept_count
WHERE cnt > 5
)
SELECT *
FROM high_dept;
-- =========================================================
-- QUESTION 97
-- CTE WITH INSERT
-- =========================================================
INSERT INTO employee_bonus (employee_id, bonus)
WITH bonus_calc AS (
SELECT employee_id, salary*0.10 bonus
FROM employees
)
SELECT employee_id, bonus
FROM bonus_calc;
-- =========================================================
-- ADVANCED STORAGE OBJECTS
-- =========================================================
-- =========================================================
-- QUESTION 98
-- CLUSTERS
--
-- Clusters store related tables together to improve
-- performance for joins.
-- =========================================================
CREATE CLUSTER emp_cluster
(dept_id NUMBER);
-- =========================================================
-- QUESTION 99
-- BLOCKCHAIN TABLES (Oracle 21c+)
--
-- Blockchain tables prevent tampering with historical data.
-- =========================================================
CREATE BLOCKCHAIN TABLE secure_transactions
(
id NUMBER,
amount NUMBER
)
NO DROP UNTIL 30 DAYS IDLE;
-- =========================================================
-- QUESTION 100
-- IMMUTABLE TABLES (Oracle 21c+)
--
-- Immutable tables prevent updates or deletes.
-- =========================================================
CREATE IMMUTABLE TABLE audit_log
(
id NUMBER,
activity VARCHAR2(200)
)
NO DROP UNTIL 30 DAYS IDLE;
-- =========================================================
-- QUESTION 101
-- SHARDED TABLES
--
-- Used in distributed databases to spread data
-- across multiple database instances.
-- =========================================================
-- =========================================================
-- AI DATABASE FEATURES (Oracle 23ai)
-- =========================================================
-- =========================================================
-- QUESTION 102
-- VECTOR DATA TYPE
--
-- Used for AI embeddings such as semantic search
-- and recommendation systems.
-- =========================================================
CREATE TABLE vector_data (
id NUMBER,
embedding VECTOR(1536)
);
-- =========================================================
-- QUESTION 103
-- VECTOR INDEX
--
-- Enables similarity search on vector embeddings.
-- =========================================================
CREATE VECTOR INDEX vec_idx
ON vector_data(embedding)
ORGANIZATION HNSW;
-- =========================================================
-- QUESTION 104
-- VECTOR SIMILARITY SEARCH
--
-- Used for semantic similarity queries.
-- =========================================================
SELECT *
FROM vector_data
ORDER BY embedding <-> :query_vector
FETCH FIRST 10 ROWS ONLY;
-- =========================================================
-- QUESTION 105
-- JSON RELATIONAL DUALITY VIEWS
--
-- Allows relational and JSON access to the same data.
-- =========================================================
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW employee_view
AS
SELECT e.employee_id,
e.first_name,
e.last_name
FROM employees e;
-- =========================================================
-- QUESTION 106
-- NATIVE JSON DATA TYPE
--
-- Provides optimized storage and querying of JSON data.
-- =========================================================
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
order_details JSON
);
-- =========================================================
-- QUESTION 107
-- AI MODEL STORAGE
--
-- Oracle AI Database allows storing machine learning
-- models directly inside the database.
-- =========================================================
-- =========================================================
-- QUESTION 108
-- GENERATIVE AI SUPPORT
--
-- Oracle 23ai integrates LLM-based capabilities
-- directly with SQL queries.
-- =========================================================
/*******************************************************************************************
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express
or implied warranties, including but not limited to warranties of accuracy, completeness,
reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges
and agrees that they do so at their own risk. The author shall not be held liable for any
direct, indirect, incidental, consequential, special, or exemplary damages, including but
not limited to loss of data, loss of profits, system failures, security breaches, or any
other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries,
and configurations in a controlled, non-production (test) environment prior to deploying
them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials
complies with all applicable local, national, and international laws, regulations,
and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless
the author from any claims, damages, liabilities, or expenses arising from their use.
*******************************************************************************************/