Oracle SQL Cheat Sheet

Oracle SQL and PL/SQL fundamentals covering SQL*Plus, data types, sequences, ROWNUM pagination, and stored procedures.

2 PagesAdvancedMar 5, 2026

SQL*Plus Basics

Connecting and exploring schema objects.

sql

sqlplus username/password@//host:1521/service_nameSELECT table_name FROM user_tables;DESCRIBE employees;SET LINESIZE 200SET PAGESIZE 50EXIT;

DDL & DML

Creating tables and manipulating rows.

sql

CREATE TABLE employees (  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  name VARCHAR2(100) NOT NULL,  hire_date DATE DEFAULT SYSDATE);INSERT INTO employees (name) VALUES ('Alice');SELECT * FROM employees WHERE ROWNUM <= 10;UPDATE employees SET name = 'Alice B.' WHERE id = 1;COMMIT;

PL/SQL Basics

Procedural blocks and stored procedures.

sql

BEGIN  FOR emp IN (SELECT id, name FROM employees) LOOP    DBMS_OUTPUT.PUT_LINE(emp.name);  END LOOP;END;/CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER, p_pct NUMBER) ISBEGIN  UPDATE employees SET salary = salary * (1 + p_pct/100) WHERE id = p_id;  COMMIT;END;/

Core Concepts & Types

Oracle-specific data types and objects.

NUMBER(p,s)- Oracle's universal numeric type with precision and scale
VARCHAR2(n)- variable-length string, preferred over the fixed-length CHAR
SYSDATE / SYSTIMESTAMP- functions returning the current date/time
ROWNUM- pseudo-column numbering result rows before ORDER BY is applied
Sequence- CREATE SEQUENCE seq_name for generating unique numbers
Synonym- an alias for a table or view, often used for cross-schema access

Pro Tip

Filtering with ROWNUM before ORDER BY gives unpredictable "top N" results — wrap the query in a subquery, apply ORDER BY there, then filter ROWNUM on the outer query (or use FETCH FIRST n ROWS ONLY on 12c+).

Was this cheat sheet helpful?

MariaDB Cheat Sheet

Microsoft SQL Server Cheat Sheet