Oracle Reference

# Connect using Easy Connect syntax
sqlplus hr/password@localhost:1521/orclpdb

# Connect as SYSDBA (requires OS-level authentication)
sqlplus / as sysdba

# Execute a script and exit
sqlplus hr/password@db_alias @script.sql

DESC employees;              -- Describe table/view structure
SET PAGESIZE 100;           -- Set rows per page
SET LINESIZE 200;           -- Set line width
COLUMN salary FORMAT $99,999;-- Format specific column output
SPOOL report.txt;           -- Write output to a file
SPOOL OFF;                  -- Close the spool file
EXIT;                       -- Quit sqlplus

-- Single-line comment for a quick note

/* Multi-line comment
   block for complex logic */
SELECT * FROM dual; -- Dual is a special one-row, one-column table

SELECT 
    CAST(1234.56 AS NUMBER(6, 2)) AS p_fixed, -- Precision 6, scale 2
    CAST(123456 AS NUMBER(6)) AS p_int,       -- 6-digit integer
    1.23f AS p_float,                         -- BINARY_FLOAT (32-bit)
    1.23d AS p_double                         -- BINARY_DOUBLE (64-bit)
FROM dual;

SELECT 
    CAST('Fixed' AS CHAR(10)) AS c_char,      -- Space-padded
    CAST('Variable' AS VARCHAR2(20)) AS c_v2, -- Standard string
    TO_CLOB('Long Text') AS c_clob,           -- Character Large Object
    HEXTORAW('DEADBEEF') AS c_blob            -- Binary data (RAW/BLOB)
FROM dual;

SELECT 
    SYSDATE,                                  -- Current date and time
    SYSTIMESTAMP,                             -- High precision with TZ
    CURRENT_DATE,                             -- Session-specific date
    INTERVAL '1' YEAR - INTERVAL '2' MONTH,   -- YM Interval
    INTERVAL '10' DAY + INTERVAL '5' MINUTE   -- DS Interval
FROM dual;

CREATE TABLE app_flags (
    is_active NUMBER(1) CHECK (is_active IN (0, 1)),     -- 0=False, 1=True
    status CHAR(1) CHECK (status IN ('A', 'I', 'P')),    -- Enum-like check
    not_null_col VARCHAR2(10) NOT NULL
);

SELECT 
    first_name || ' ' || last_name AS "Full Name", -- Double quotes for case
    salary * 1.1 AS increased_salary,
    'Static Value' AS note
FROM employees;

SELECT * FROM employees
WHERE department_id IN (10, 20)
  AND last_name LIKE 'S%'
  AND salary BETWEEN 5000 AND 10000
  AND commission_pct IS NOT NULL;

SELECT last_name, salary, commission_pct
FROM employees
ORDER BY salary DESC, commission_pct ASC NULLS LAST;

-- Modern (12c+) syntax
SELECT employee_id FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- Legacy ROWNUM (requires subquery for correct ordering)
SELECT * FROM (
    SELECT e.*, ROWNUM rnum FROM (SELECT * FROM employees ORDER BY id) e
    WHERE ROWNUM <= 15
) WHERE rnum > 10;

SELECT e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
LEFT OUTER JOIN locations l ON d.location_id = l.location_id;

-- Self Join
SELECT e.last_name AS emp, m.last_name AS mgr
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

-- Cross Join
SELECT * FROM departments CROSS JOIN regions;

SELECT email FROM customers
UNION -- Distinct values (UNION ALL for duplicates)
SELECT email FROM leads
MINUS
SELECT email FROM blocked_users;

SELECT d.department_name, avg_sal.val
FROM departments d
JOIN (
    SELECT department_id, AVG(salary) AS val 
    FROM employees GROUP BY department_id
) avg_sal ON d.department_id = avg_sal.department_id;

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

SELECT 
    COUNT(DISTINCT department_id) AS dept_count,
    ROUND(AVG(salary), 2) AS avg_sal,
    LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY last_name) AS names
FROM employees
WHERE department_id = 30;

SELECT DISTINCT job_id FROM employees;

-- Scalar subquery in SELECT
SELECT last_name, (SELECT MAX(salary) FROM employees) AS top_sal
FROM employees;

-- Correlated subquery in WHERE
SELECT last_name FROM employees e
WHERE salary > (
    SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);

WITH dept_costs AS (
    SELECT department_id, SUM(salary) AS total
    FROM employees GROUP BY department_id
)
SELECT department_id FROM dept_costs 
WHERE total > (SELECT AVG(total) FROM dept_costs);

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

-- Modern Recursive WITH (11gR2+)
WITH emp_hierarchy(id, name, mgr_id, lvl) AS (
    SELECT employee_id, last_name, manager_id, 1 FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.last_name, e.manager_id, h.lvl + 1
    FROM employees e JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM emp_hierarchy;

SELECT 
    last_name, department_id, salary,
    SUM(salary) OVER(PARTITION BY department_id) AS dept_total,
    ROUND(salary / SUM(salary) OVER(PARTITION BY department_id) * 100, 2) AS pct
FROM employees;

SELECT 
    last_name, 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 drnk
FROM employees;

SELECT 
    last_name, hire_date,
    LAG(last_name) OVER(ORDER BY hire_date) AS prev_hired,
    LEAD(last_name) OVER(ORDER BY hire_date) AS next_hired
FROM employees;

-- Single row
INSERT INTO departments (department_id, department_name)
VALUES (300, 'Data Science');

-- Conditional multi-table insert
INSERT ALL
    WHEN salary > 10000 THEN INTO high_earners (id, sal) VALUES (eid, sal)
    ELSE INTO low_earners (id, sal) VALUES (eid, sal)
SELECT employee_id eid, salary sal FROM employees;

UPDATE employees SET salary = salary * 1.05
WHERE department_id = 90;

DELETE FROM job_history 
WHERE end_date < TO_DATE('01-JAN-2010', 'DD-MON-YYYY');

MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.val = s.val, t.updated_at = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (id, val, created_at) VALUES (s.id, s.val, SYSDATE);

CREATE TABLE projects (
    project_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100) NOT NULL,
    budget NUMBER(12, 2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE projects ADD (status VARCHAR2(20));
ALTER TABLE projects MODIFY (budget NUMBER(15, 2));

ALTER TABLE projects ADD CONSTRAINT uq_proj_name UNIQUE (name);
ALTER TABLE projects ADD CONSTRAINT ck_budget CHECK (budget >= 0);
-- Foreign Key with Cascade Delete
ALTER TABLE tasks ADD CONSTRAINT fk_task_proj 
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE;

CREATE INDEX idx_proj_name ON projects(name);
CREATE BITMAP INDEX idx_emp_gender ON employees(gender); -- For low cardinality
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Standard View
CREATE VIEW v_high_sal AS 
SELECT * FROM employees WHERE salary > 15000;

-- Materialized View (cached result)
CREATE MATERIALIZED VIEW mv_dept_sal_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS SELECT department_id, SUM(salary) total FROM employees GROUP BY department_id;

EXEC DBMS_MVIEW.REFRESH('mv_dept_sal_summary');

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT sp1; -- Create intermediate rollback point
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If error: ROLLBACK TO sp1;
COMMIT; -- Finalize all changes

-- Row-level trigger to audit changes
CREATE OR REPLACE TRIGGER trg_audit_sal
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO sal_audit (emp_id, old_sal, new_sal, change_date)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

EXPLAIN PLAN FOR 
SELECT * FROM employees WHERE last_name = 'King';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

CREATE ROLE report_user;
GRANT SELECT ON hr.employees TO report_user;
GRANT report_user TO jsmith;

REVOKE DELETE ON hr.departments FROM report_user;