Oracle SQL Cheat Sheet: Every Command You Need (2026)

Basic CRUD

The four fundamental operations in Oracle SQL. Every query you write uses at least one of them.

SELECT - Read data

-- Select specific columns
SELECT first_name, last_name, email
FROM employees;

-- Select all columns
SELECT * FROM products;

-- Select with alias
SELECT first_name AS name, email AS contact
FROM employees;

-- Select from DUAL (Oracle-specific)
SELECT SYSDATE FROM DUAL;
SELECT 2 + 2 AS result FROM DUAL;

INSERT INTO - Add rows

-- Insert a single row
INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane@example.com');

-- INSERT ALL: multi-table insert (Oracle-specific)
INSERT ALL
  INTO orders (order_id, product_id, qty) VALUES (1, 100, 2)
  INTO orders (order_id, product_id, qty) VALUES (2, 200, 1)
  INTO order_archive (order_id, product_id) VALUES (1, 100)
SELECT * FROM DUAL;

-- Conditional multi-table insert
INSERT ALL
  WHEN amount > 1000 THEN INTO high_value_orders (id, amount) VALUES (id, amount)
  WHEN amount <= 1000 THEN INTO standard_orders (id, amount) VALUES (id, amount)
SELECT id, amount FROM pending_orders;

UPDATE - Modify existing rows

-- Update specific rows
UPDATE employees
SET email = 'newemail@example.com'
WHERE employee_id = 42;

-- Update multiple columns
UPDATE products
SET price = price * 1.10, updated_at = SYSDATE
WHERE category = 'electronics';

DELETE - Remove rows

-- Delete specific rows
DELETE FROM orders
WHERE status = 'cancelled' AND order_date < DATE '2025-01-01';

-- Delete all rows (use with caution)
DELETE FROM temp_logs;

MERGE - Upsert (Oracle-specific)

-- Insert or update in one statement
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.updated_at = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (id, name, created_at)
    VALUES (s.id, s.name, SYSDATE);

Filtering and Sorting

Control which rows are returned and in what order.

WHERE, AND, OR

SELECT * FROM orders
WHERE status = 'shipped'
  AND total_amount > 100
  AND (region = 'US' OR region = 'EU');

IN and BETWEEN

-- IN: match any value in a list
SELECT * FROM products
WHERE category IN ('electronics', 'books', 'clothing');

-- BETWEEN: inclusive range
SELECT * FROM orders
WHERE order_date BETWEEN DATE '2026-01-01' AND DATE '2026-03-31';

LIKE and IS NULL

-- % matches any sequence of characters
SELECT * FROM employees WHERE email LIKE '%@gmail.com';

-- _ matches exactly one character
SELECT * FROM products WHERE sku LIKE 'SKU-____';

-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE manager_id IS NULL;

ORDER BY

-- Sort ascending (default) and descending
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- NULLS FIRST / NULLS LAST (Oracle-specific control)
SELECT employee_name, commission_pct
FROM employees
ORDER BY commission_pct DESC NULLS LAST;

ROWNUM (legacy pagination)

-- Top N rows (Oracle 11g and earlier)
SELECT * FROM (
    SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;

FETCH FIRST / OFFSET (12c+)

-- Top 10 rows (Oracle 12c+)
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

-- Pagination: skip 20, take 10
SELECT * FROM employees
ORDER BY hire_date DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Aggregation

Summarize data across multiple rows into a single result.

COUNT, SUM, AVG, MIN, MAX

SELECT
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order
FROM orders
WHERE order_date >= DATE '2026-01-01';

GROUP BY and HAVING

-- Revenue by category
SELECT category, COUNT(*) AS num_products, SUM(price) AS total_value
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY total_value DESC;

Remember: WHERE filters rows before grouping. HAVING filters groups after aggregation.

LISTAGG (Oracle-specific)

-- Concatenate values into a comma-separated string
SELECT department_id,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS employees
FROM employees
GROUP BY department_id;

-- With DISTINCT (Oracle 19c+)
SELECT department_id,
       LISTAGG(DISTINCT job_id, ', ') WITHIN GROUP (ORDER BY job_id) AS jobs
FROM employees
GROUP BY department_id;

ROLLUP, CUBE, GROUPING SETS

-- ROLLUP: subtotals + grand total
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id);

-- CUBE: all possible subtotal combinations
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department_id, job_id);

-- GROUPING SETS: specific subtotals
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS(
    (department_id, job_id),
    (department_id),
    ()
);

JOINs

Combine rows from two or more tables based on a related column.

INNER JOIN

SELECT o.order_id, o.order_date, c.customer_name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

LEFT / RIGHT / FULL OUTER JOIN

-- All customers, even those with no orders
SELECT c.customer_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

-- All products, even those never ordered
SELECT p.product_name, SUM(oi.quantity) AS total_sold
FROM order_items oi
RIGHT OUTER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name;

-- All rows from both tables
SELECT c.customer_name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

CROSS JOIN

-- Every combination of rows (cartesian product)
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;

Oracle Legacy (+) Join Syntax

-- Old-style Oracle outer join (avoid in new code)
-- LEFT JOIN equivalent:
SELECT c.customer_name, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id(+);

-- RIGHT JOIN equivalent:
SELECT c.customer_name, o.order_id
FROM customers c, orders o
WHERE c.customer_id(+) = o.customer_id;

The (+) operator is Oracle-specific legacy syntax. Use standard ANSI JOIN syntax in new code for readability and portability.

NATURAL JOIN

-- Joins on all columns with matching names (use with caution)
SELECT * FROM employees NATURAL JOIN departments;

Subqueries and CTEs

Break complex queries into manageable parts.

Scalar Subquery

-- Returns a single value
SELECT employee_name, salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Subquery with IN and EXISTS

-- IN: customers who placed orders over $500
SELECT customer_name, email
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders WHERE total_amount > 500
);

-- EXISTS: products ordered at least once
SELECT p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

WITH (CTE - Common Table Expression)

-- Monthly revenue with running total
WITH monthly_revenue AS (
    SELECT
        TRUNC(order_date, 'MM') AS month,
        SUM(total_amount) AS revenue
    FROM orders
    GROUP BY TRUNC(order_date, 'MM')
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_revenue
ORDER BY month;

Recursive CTE (alternative to CONNECT BY)

-- Oracle 11gR2+: recursive WITH
WITH org_tree (employee_id, employee_name, manager_id, lvl) AS (
    SELECT employee_id, employee_name, manager_id, 1
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id, t.lvl + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.employee_id
)
SELECT LPAD(' ', (lvl - 1) * 4) || employee_name AS org_chart, lvl
FROM org_tree
ORDER BY lvl, employee_name;

Materialized CTEs

-- Force Oracle to materialize (cache) the CTE result
WITH expensive_calc AS (
    SELECT /*+ MATERIALIZE */ customer_id, SUM(total_amount) AS lifetime_value
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_name, ec.lifetime_value
FROM customers c
JOIN expensive_calc ec ON c.customer_id = ec.customer_id
WHERE ec.lifetime_value > 10000;

Window / Analytic Functions

Perform calculations across a set of rows related to the current row without collapsing them into a single output row. Oracle was one of the first databases to support these.

ROW_NUMBER, RANK, DENSE_RANK, NTILE

SELECT
    employee_name,
    department_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

ROW_NUMBER gives unique sequential numbers. RANK leaves gaps after ties (1,2,2,4). DENSE_RANK has no gaps (1,2,2,3). NTILE(n) divides rows into n roughly equal buckets.

LAG and LEAD

-- Compare each month's revenue to the previous month
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

FIRST_VALUE and LAST_VALUE

SELECT
    employee_name,
    department_id,
    salary,
    FIRST_VALUE(employee_name) OVER (
        PARTITION BY department_id ORDER BY salary DESC
    ) AS highest_paid,
    LAST_VALUE(employee_name) OVER (
        PARTITION BY department_id ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_paid
FROM employees;

SUM() OVER with ROWS BETWEEN

-- Running total
SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

-- 3-day moving average
SELECT order_date, amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_3day
FROM daily_sales;

Oracle-Specific Features

Features unique to Oracle that you will not find in other databases.

DUAL Table

-- DUAL: a special one-row table for evaluating expressions
SELECT SYSDATE FROM DUAL;
SELECT USER FROM DUAL;
SELECT 100 * 1.08 AS price_with_tax FROM DUAL;

SEQUENCES

-- Create a sequence
CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Use NEXTVAL and CURRVAL
INSERT INTO orders (order_id, customer_id)
VALUES (order_seq.NEXTVAL, 42);

SELECT order_seq.CURRVAL FROM DUAL;

NVL, NVL2, DECODE

-- NVL: replace NULL with a default
SELECT NVL(commission_pct, 0) AS commission FROM employees;

-- NVL2: return one value if NOT NULL, another if NULL
SELECT NVL2(commission_pct, 'Commissioned', 'Salaried') AS pay_type
FROM employees;

-- DECODE: inline CASE-like switching
SELECT employee_name,
    DECODE(department_id,
        10, 'Admin',
        20, 'Marketing',
        30, 'Sales',
        'Other') AS dept_name
FROM employees;

ROWID and ROWNUM

-- ROWID: physical address of a row (fastest way to access a specific row)
SELECT ROWID, employee_name FROM employees WHERE employee_id = 100;

-- Delete duplicates using ROWID
DELETE FROM employees
WHERE ROWID NOT IN (
    SELECT MIN(ROWID) FROM employees GROUP BY employee_id
);

-- ROWNUM: pseudo-column assigned during query execution
SELECT * FROM employees WHERE ROWNUM <= 5;

Hierarchical Queries (CONNECT BY)

-- Traverse an org chart (tree structure)
SELECT
    LEVEL,
    LPAD(' ', (LEVEL - 1) * 4) || employee_name AS org_chart,
    employee_id,
    manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

-- SYS_CONNECT_BY_PATH: show full path
SELECT
    employee_name,
    SYS_CONNECT_BY_PATH(employee_name, ' > ') AS full_path,
    LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

DBMS_OUTPUT and Synonyms

-- PL/SQL output
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello from Oracle PL/SQL');
END;
/

-- Create a synonym (alias for a schema object)
CREATE SYNONYM emp FOR hr.employees;
CREATE PUBLIC SYNONYM all_orders FOR sales.orders;

String Functions

Manipulate and transform text data in Oracle.

-- || : concatenation operator
SELECT first_name || ' ' || last_name AS full_name FROM employees;

-- SUBSTR: extract part of a string (1-based indexing)
SELECT SUBSTR(phone_number, 1, 3) AS area_code FROM employees;

-- TRIM, LTRIM, RTRIM
SELECT TRIM(name) FROM products;
SELECT LTRIM('   hello') FROM DUAL;  -- 'hello'

-- UPPER, LOWER, INITCAP
SELECT UPPER(email) FROM employees;
SELECT LOWER(city) FROM addresses;
SELECT INITCAP('hello world') FROM DUAL;  -- 'Hello World'

-- LENGTH
SELECT name, LENGTH(name) AS name_length FROM products WHERE LENGTH(name) > 50;

-- REPLACE
SELECT REPLACE(url, 'http://', 'https://') FROM links;

-- INSTR: find position of substring
SELECT INSTR('hello world', 'world') AS pos FROM DUAL;  -- 7

-- LPAD / RPAD: pad strings
SELECT LPAD(employee_id, 6, '0') AS padded_id FROM employees;  -- '000042'
SELECT RPAD(name, 20, '.') FROM products;

-- REGEXP_LIKE: regex pattern matching
SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[a-z]+@company\.com$');

-- REGEXP_SUBSTR: extract regex match
SELECT REGEXP_SUBSTR('abc123def', '[0-9]+') AS numbers FROM DUAL;  -- '123'

-- REGEXP_REPLACE: regex substitution
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS digits_only FROM employees;

Date and Time

Oracle has rich date/time support. Dates in Oracle always include a time component.

-- SYSDATE: current date + time
SELECT SYSDATE FROM DUAL;

-- SYSTIMESTAMP: current timestamp with timezone
SELECT SYSTIMESTAMP FROM DUAL;

-- ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE, 3) AS three_months_later FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -6) AS six_months_ago FROM DUAL;

-- MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(DATE '2026-06-15', DATE '2026-01-01') FROM DUAL;  -- 5.45...

-- TRUNC: truncate to a unit
SELECT TRUNC(SYSDATE, 'MM') AS first_of_month FROM DUAL;
SELECT TRUNC(SYSDATE, 'YYYY') AS first_of_year FROM DUAL;

-- EXTRACT: pull out a date part
SELECT EXTRACT(YEAR FROM SYSDATE) AS yr,
       EXTRACT(MONTH FROM SYSDATE) AS mo,
       EXTRACT(DAY FROM SYSDATE) AS dy
FROM DUAL;

-- TO_DATE: string to date
SELECT TO_DATE('2026-03-12', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('12-Mar-2026', 'DD-Mon-YYYY') FROM DUAL;

-- TO_CHAR: date to formatted string
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'Day, DD Month YYYY') FROM DUAL;

-- TO_TIMESTAMP
SELECT TO_TIMESTAMP('2026-03-12 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- INTERVAL arithmetic
SELECT SYSDATE + INTERVAL '7' DAY AS one_week_later FROM DUAL;
SELECT SYSDATE - INTERVAL '2' HOUR AS two_hours_ago FROM DUAL;

-- LAST_DAY: last day of the month
SELECT LAST_DAY(SYSDATE) FROM DUAL;

-- NEXT_DAY: next occurrence of a weekday
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;

Table Operations

Define and modify your database structure in Oracle.

CREATE TABLE

CREATE TABLE employees (
    employee_id   NUMBER(10) PRIMARY KEY,
    first_name    VARCHAR2(100) NOT NULL,
    last_name     VARCHAR2(100) NOT NULL,
    email         VARCHAR2(255) UNIQUE NOT NULL,
    hire_date     DATE DEFAULT SYSDATE,
    salary        NUMBER(10,2),
    department_id NUMBER(10) REFERENCES departments(department_id),
    is_active     NUMBER(1) DEFAULT 1
);

ALTER TABLE

-- Add a column
ALTER TABLE employees ADD (phone VARCHAR2(20));

-- Modify a column type
ALTER TABLE products MODIFY (price NUMBER(10,2));

-- Add a constraint
ALTER TABLE orders ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Drop a column
ALTER TABLE employees DROP COLUMN phone;

-- Rename a column (Oracle 9i+)
ALTER TABLE employees RENAME COLUMN email TO email_address;

DROP TABLE and TRUNCATE

-- Drop table (moves to recycle bin by default)
DROP TABLE temp_imports;

-- Drop and bypass recycle bin
DROP TABLE old_orders PURGE;

-- Truncate: remove all rows instantly (no rollback)
TRUNCATE TABLE temp_logs;

CREATE INDEX (B-tree and Bitmap)

-- Standard B-tree index
CREATE INDEX idx_emp_email ON employees(email);

-- Composite index
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

-- Bitmap index (good for low-cardinality columns)
CREATE BITMAP INDEX idx_emp_dept ON employees(department_id);

-- Function-based index
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- Drop an index
DROP INDEX idx_emp_email;

CREATE VIEW and MATERIALIZED VIEW

-- Regular view
CREATE OR REPLACE VIEW active_employees AS
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE is_active = 1;

-- Materialized view (physically stored, refreshable)
CREATE MATERIALIZED VIEW mv_monthly_sales
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT TRUNC(order_date, 'MM') AS month,
       SUM(total_amount) AS revenue,
       COUNT(*) AS order_count
FROM orders
GROUP BY TRUNC(order_date, 'MM');

CREATE TABLESPACE and COMMENT ON

-- Create a tablespace
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/mydb/app_data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

-- Add comments to tables and columns
COMMENT ON TABLE employees IS 'Core employee records';
COMMENT ON COLUMN employees.salary IS 'Annual salary in USD';

Pro Tips

Write faster, more efficient Oracle queries and understand what the optimizer is doing.

EXPLAIN PLAN

-- Generate an execution plan
EXPLAIN PLAN FOR
SELECT c.customer_name, COUNT(o.order_id)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

-- View the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

AUTOTRACE

-- In SQL*Plus or SQL Developer
SET AUTOTRACE ON
SELECT * FROM employees WHERE department_id = 30;
SET AUTOTRACE OFF

-- AUTOTRACE TRACEONLY: show plan without results
SET AUTOTRACE TRACEONLY EXPLAIN

AWR Reports

-- Generate an AWR report (DBA privilege required)
-- Lists top SQL by elapsed time, CPU, I/O
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- Query AWR snapshots directly
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
WHERE begin_interval_time > SYSDATE - 1
ORDER BY snap_id DESC;

Index Types

• B-tree: Default index. Best for high-cardinality columns (unique or near-unique values). Use for primary keys, email addresses, timestamps.
• Bitmap: Best for low-cardinality columns (status, gender, region). Excellent for data warehouse queries with multiple AND/OR conditions. Avoid in OLTP with frequent DML.
• Function-based: Index on an expression like UPPER(last_name). Essential when queries filter on transformed column values.
• Reverse key: Distributes sequential inserts across index blocks. Reduces contention on sequences.

Partitioning

-- RANGE partitioning (by date)
CREATE TABLE orders (
    order_id NUMBER, order_date DATE, amount NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01'),
    PARTITION p_2026_q1 VALUES LESS THAN (DATE '2026-04-01'),
    PARTITION p_2026_q2 VALUES LESS THAN (DATE '2026-07-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

-- LIST partitioning (by region)
CREATE TABLE sales (
    sale_id NUMBER, region VARCHAR2(20), amount NUMBER
)
PARTITION BY LIST (region) (
    PARTITION p_us VALUES ('US', 'CA'),
    PARTITION p_eu VALUES ('UK', 'DE', 'FR'),
    PARTITION p_asia VALUES ('JP', 'IN', 'SG')
);

-- HASH partitioning (even distribution)
CREATE TABLE transactions (
    txn_id NUMBER, account_id NUMBER, amount NUMBER
)
PARTITION BY HASH (account_id) PARTITIONS 8;

Flashback Queries

-- See data as it was 10 minutes ago
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE employee_id = 100;

-- Flashback to a specific SCN
SELECT * FROM employees AS OF SCN 123456789;

-- Flashback version query: see all changes to a row
SELECT versions_starttime, versions_endtime, versions_operation,
       employee_name, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP
WHERE employee_id = 100;

Flashback queries require undo data to be available. Configure UNDO_RETENTION appropriately for your needs.

For more SQL fundamentals, see the general SQL cheat sheet. Learn how JOINs work in depth in our SQL JOINs explained guide, or start from scratch with What is SQL?. Need to migrate? Check our Oracle to PostgreSQL and Oracle to DB2 converters.

Frequently Asked Questions

What is different about Oracle SQL compared to standard SQL?

Oracle SQL extends standard SQL with features like hierarchical queries (CONNECT BY PRIOR), the DUAL table, sequences (NEXTVAL/CURRVAL), MERGE statements, DECODE function, NVL/NVL2, ROWID/ROWNUM pseudo-columns, analytic functions, and PL/SQL procedural extensions. Oracle also uses its own date handling and pagination syntax (ROWNUM or FETCH FIRST in 12c+).

How do I limit rows in Oracle SQL?

In Oracle 11g and earlier, use ROWNUM in a WHERE clause: SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 10. In Oracle 12c and later, use the standard FETCH FIRST syntax: SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY. The 12c+ syntax also supports OFFSET for pagination.

What is CONNECT BY in Oracle?

CONNECT BY is Oracle's proprietary syntax for hierarchical (tree-structured) queries. Combined with START WITH and PRIOR, it lets you traverse parent-child relationships. For example, querying an org chart: SELECT employee_name, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id. The LEVEL pseudo-column indicates depth in the tree.

What is the DUAL table in Oracle?

DUAL is a special one-row, one-column table that exists in every Oracle database. It is used to SELECT expressions that don't come from a real table, such as SELECT SYSDATE FROM DUAL or SELECT 2 + 2 FROM DUAL. In Oracle 23c+, FROM DUAL is optional for simple expressions.

Can AI generate Oracle SQL for me?

Yes. Tools like AI2SQL let you describe what you need in plain English and generate Oracle-specific SQL automatically, including PL/SQL blocks, analytic functions, and hierarchical queries. This saves time on syntax details while you focus on the logic and data you need.