Oracle Database: SQL & PL/SQL Fundamentals

Oracle Database is a relational database management system with a rich SQL dialect and PL/SQL — a procedural extension of SQL for writing stored procedures, functions, triggers, and packages.

Oracle SQL Essentials

-- DUAL: Oracle's built-in one-row table for expressions
SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;
SELECT USER FROM DUAL;

-- String functions
SELECT UPPER('hello'), LOWER('WORLD'), LENGTH('abc') FROM DUAL;
SELECT SUBSTR('Oracle Database', 1, 6) FROM DUAL;  -- 'Oracle' (1-indexed)
SELECT INSTR('Oracle Database', 'a') FROM DUAL;    -- first 'a' position
SELECT TRIM('  hello  ') FROM DUAL;
SELECT LPAD('42', 5, '0') FROM DUAL;               -- '00042'

-- Number functions
SELECT ROUND(3.145, 2) FROM DUAL;   -- 3.15
SELECT TRUNC(3.9) FROM DUAL;        -- 3 (truncate, not round)
SELECT MOD(10, 3) FROM DUAL;        -- 1
SELECT POWER(2, 10) FROM DUAL;      -- 1024
SELECT ABS(-5), CEIL(3.1), FLOOR(3.9) FROM DUAL;

-- Date functions
SELECT SYSDATE FROM DUAL;                     -- current date+time
SELECT SYSTIMESTAMP FROM DUAL;                -- with timezone
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT MONTHS_BETWEEN(DATE '2024-12-31', DATE '2024-01-01') FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;        -- first day of month
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') FROM DUAL;

-- NVL / COALESCE (NULL handling)
SELECT NVL(commission, 0) FROM employees;
SELECT COALESCE(col1, col2, 'default') FROM table1;
SELECT NVL2(commission, 'Has commission', 'No commission') FROM employees;

Advanced SQL

-- Analytic (Window) functions
SELECT
    employee_id,
    salary,
    department_id,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
    LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
    LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary,
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
    AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

-- CONNECT BY (hierarchical queries)
SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1)*2) || last_name AS name_tree,
    employee_id,
    manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

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

-- Pivot
SELECT * FROM (
    SELECT department_id, job_id, salary FROM employees
)
PIVOT (
    SUM(salary) FOR job_id IN ('IT_PROG' AS it_prog, 'SA_REP' AS sa_rep)
);

PL/SQL

-- Anonymous block
DECLARE
    v_name   employees.last_name%TYPE;
    v_salary NUMBER(10, 2);
BEGIN
    SELECT last_name, salary
    INTO v_name, v_salary
    FROM employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Stored procedure
CREATE OR REPLACE PROCEDURE give_raise(
    p_emp_id IN  employees.employee_id%TYPE,
    p_pct    IN  NUMBER,
    p_new_sal OUT employees.salary%TYPE
) AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_pct / 100)
    WHERE employee_id = p_emp_id
    RETURNING salary INTO p_new_sal;
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee ' || p_emp_id || ' not found');
END;
/

-- Function
CREATE OR REPLACE FUNCTION get_annual_salary(p_emp_id IN NUMBER)
RETURN NUMBER AS
    v_sal NUMBER;
BEGIN
    SELECT salary * 12 INTO v_sal FROM employees WHERE employee_id = p_emp_id;
    RETURN v_sal;
END;
/

-- Cursor
DECLARE
    CURSOR c_emp IS
        SELECT employee_id, last_name, salary FROM employees WHERE department_id = 60;
BEGIN
    FOR rec IN c_emp LOOP
        DBMS_OUTPUT.PUT_LINE(rec.last_name || ': ' || rec.salary);
    END LOOP;
END;
/