Oracle Cheat Sheet

SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE FROM dual;

SELECT TO_DATE('2026-08-26', 'YYYY-MM-DD') FROM dual;

SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI') FROM orders;

SELECT TRUNC(created_at)        AS day,
       TRUNC(created_at, 'MM')  AS month
FROM orders;

SELECT created_at + 7            AS in_a_week,
       ADD_MONTHS(created_at, 3) AS in_a_quarter,
       created_at + 1/24         AS in_an_hour
FROM orders;

SELECT delivered_at - created_at              AS days,
       MONTHS_BETWEEN(delivered_at, created_at) AS months
FROM orders;

SELECT EXTRACT(YEAR FROM created_at), TO_CHAR(created_at, 'MM') FROM orders;

SELECT NVL(discount, 0), NVL2(discount, 'has one', 'none') FROM orders;

SELECT COALESCE(nickname, first_name, 'there') FROM customers;

SELECT DECODE(status, 'N', 'New', 'S', 'Shipped', 'Other') FROM orders;

SELECT CASE WHEN total > 100 THEN 'large' ELSE 'small' END FROM orders;

SELECT total / NULLIF(item_count, 0) FROM orders;

SELECT first_name || ' ' || last_name AS name FROM customers;

SELECT SUBSTR(sku, 1, 3) AS prefix, SUBSTR(sku, -4) AS suffix FROM order_items;

SELECT INSTR(email, '@') FROM customers;

SELECT * FROM customers WHERE REGEXP_LIKE(email, '^[a-z.]+@acme\.(io|dev)$');

SELECT REGEXP_SUBSTR(url, 'utm_source=([^&]+)', 1, 1, NULL, 1) FROM sessions;

SELECT REGEXP_SUBSTR(tags, '[^,]+', 1, LEVEL) AS tag
FROM posts
CONNECT BY LEVEL <= REGEXP_COUNT(tags, ',') + 1;

SELECT customer_id, LISTAGG(sku, ', ') WITHIN GROUP (ORDER BY sku)
FROM order_items GROUP BY customer_id;

SELECT TO_NUMBER('1234.50', '99999D99') FROM dual;

SELECT LPAD(code, 8, '0'), TRIM(name), UPPER(email) FROM customers;

SELECT * FROM orders ORDER BY created_at DESC
FETCH FIRST 50 ROWS ONLY;

SELECT * FROM (
  SELECT * FROM orders ORDER BY created_at DESC
) WHERE ROWNUM <= 50;

SELECT * FROM orders ORDER BY created_at DESC
OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY;

SELECT customer_id, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM orders;

SELECT RANK() OVER (ORDER BY total DESC), DENSE_RANK() OVER (ORDER BY total DESC)
FROM orders;

WITH recent AS (
  SELECT * FROM orders WHERE created_at > SYSDATE - 30
)
SELECT customer_id, COUNT(*) FROM recent GROUP BY customer_id;

SELECT * FROM (SELECT customer_id, status, total FROM orders)
PIVOT (SUM(total) FOR status IN ('new' AS new_total, 'shipped' AS shipped_total));

SELECT LEVEL, employee_id, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

INSERT INTO orders (customer_id, total) VALUES (1, 49.90);

INSERT ALL
  INTO orders (customer_id, total) VALUES (1, 49.90)
  INTO orders (customer_id, total) VALUES (2, 12.00)
SELECT * FROM dual;

UPDATE orders o
SET total = (SELECT t.amount FROM order_totals t WHERE t.order_id = o.id)
WHERE EXISTS (SELECT 1 FROM order_totals t WHERE t.order_id = o.id);

MERGE INTO orders t
USING orders_staging s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.total = s.total
WHEN NOT MATCHED THEN INSERT (id, total) VALUES (s.id, s.total);

DELETE FROM customers
WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM customers GROUP BY email);

COMMIT;
ROLLBACK;

CREATE TABLE orders (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  total NUMBER(10,2),
  created_at DATE DEFAULT SYSDATE
);

CREATE TABLE orders_backup AS SELECT * FROM orders;

ALTER TABLE orders ADD (status VARCHAR2(20) DEFAULT 'new');

ALTER TABLE orders RENAME TO customer_orders;
ALTER TABLE orders RENAME COLUMN total TO amount;

ALTER TABLE orders MODIFY (total NUMBER(12,2));

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1 NOCACHE;
SELECT order_seq.NEXTVAL FROM dual;

SELECT table_name FROM user_tables ORDER BY table_name;
SELECT owner, table_name FROM all_tables WHERE owner = 'APP';

SELECT column_name, data_type, nullable
FROM user_tab_columns WHERE table_name = 'ORDERS' ORDER BY column_id;

SELECT banner FROM v$version;
SELECT USER, SYS_CONTEXT('USERENV', 'DB_NAME') FROM dual;