Oracle SQL Basics

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(10,2),
    dept_id NUMBER(4),
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id)
);

-- Oracle-specific table organization
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2)
) ORGANIZATION INDEX;

-- Select with Oracle pseudocolumns
SELECT ROWID, ROWNUM, e.* 
FROM employees e
WHERE ROWNUM <= 10;

-- Oracle's DUAL table
SELECT SYSDATE, USER 
FROM DUAL;

SELECT * FROM employees 
WHERE hire_date > TO_DATE('01-JAN-2020', 'DD-MON-YYYY');

-- Oracle 12c+ syntax
SELECT * FROM employees
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- Traditional Oracle syntax
SELECT * FROM (
    SELECT a.*, ROWNUM rnum 
    FROM (SELECT * FROM employees ORDER BY hire_date) a
    WHERE ROWNUM <= 20
) WHERE rnum >= 10;

-- Single record with sequence
INSERT INTO employees 
VALUES (emp_seq.NEXTVAL, 'John', 'Doe', SYSDATE, 50000, 10);

-- Insert from another table
INSERT INTO managers
SELECT * FROM employees 
WHERE job_id = 'MANAGER';

-- Update with correlated subquery
UPDATE employees e
SET salary = (SELECT AVG(salary) FROM employees 
             WHERE dept_id = e.dept_id)
WHERE dept_id = 10;

-- Oracle's RETURNING clause
UPDATE employees
SET salary = salary * 1.1
WHERE emp_id = 100
RETURNING salary INTO :new_salary;

-- Delete with RETURNING
DELETE FROM employees
WHERE dept_id = 20
RETURNING emp_id INTO :deleted_ids;

-- Oracle's MERGE statement
MERGE INTO employees_target t
USING employees_source s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN INSERT VALUES (s.emp_id, s.name, s.salary);

-- DECODE (Oracle's IF-THEN-ELSE)
SELECT emp_name, 
       DECODE(dept_id, 10, 'Accounting', 
                      20, 'Research', 
                      'Other') AS department
FROM employees;

-- CASE expression (ANSI standard)
SELECT emp_name,
       CASE WHEN salary > 100000 THEN 'High'
            WHEN salary > 50000 THEN 'Medium'
            ELSE 'Low' END AS salary_level
FROM employees;

-- Oracle CONNECT BY for hierarchies
SELECT LEVEL, emp_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

-- RANK, DENSE_RANK, ROW_NUMBER
SELECT emp_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- Window functions
SELECT emp_name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS avg_dept_salary,
       salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg
FROM employees;

-- ANSI JOIN syntax
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- Oracle's traditional (+) syntax
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id(+); -- LEFT JOIN

-- Partitioned outer join
SELECT e.emp_name, d.dept_name, d.location
FROM employees e
PARTITION BY (e.emp_name)
RIGHT OUTER JOIN departments d ON e.dept_id = d.dept_id;

-- Flashback query (Oracle temporal)
SELECT * FROM employees
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;

-- Oracle statistical functions
SELECT 
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MEDIAN(salary) AS median_salary,
    STDDEV(salary) AS salary_stddev,
    VARIANCE(salary) AS salary_variance,
    LISTAGG(emp_name, ', ') WITHIN GROUP (ORDER BY salary) AS employees
FROM employees
GROUP BY dept_id;

-- Multiple grouping levels
SELECT 
    dept_id, 
    job_id, 
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
    (dept_id, job_id),
    (dept_id),
    (job_id),
    ()
);

-- CUBE and ROLLUP
SELECT dept_id, job_id, COUNT(*)
FROM employees
GROUP BY CUBE(dept_id, job_id);

-- Common Table Expression (CTE)
WITH dept_stats AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT e.emp_name, e.salary, d.avg_salary
FROM employees e
JOIN dept_stats d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

SELECT d.dept_name, e.emp_count
FROM departments d
JOIN (
    SELECT dept_id, COUNT(*) AS emp_count
    FROM employees
    GROUP BY dept_id
) e ON d.dept_id = e.dept_id;

-- Hierarchical subquery
SELECT emp_name
FROM employees
WHERE emp_id IN (
    SELECT emp_id
    FROM employees
    START WITH manager_id = 100
    CONNECT BY PRIOR emp_id = manager_id
);

-- Version query
SELECT versions_starttime, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP 
    SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP
WHERE emp_id = 100;

-- Create a view with check option
CREATE OR REPLACE VIEW active_employees AS
SELECT * FROM employees
WHERE status = 'ACTIVE'
WITH CHECK OPTION CONSTRAINT active_emp_chk;

-- Materialized view
CREATE MATERIALIZED VIEW emp_dept_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT d.dept_name, COUNT(*) emp_count, AVG(e.salary) avg_salary
FROM employees e JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- Create various index types
CREATE INDEX idx_emp_name ON employees(last_name, first_name);
CREATE BITMAP INDEX idx_emp_dept ON employees(dept_id);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

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

-- Oracle-specific indexes:
-- * Reverse key indexes
-- * Index-organized tables (IOT)
-- * Domain indexes

SELECT 
    INITCAP('hello world') AS initcap,
    INSTR('Oracle', 'a') AS position,
    SUBSTR('Oracle', 2, 3) AS substring,
    LPAD(salary, 10, '*') AS padded_salary,
    REGEXP_REPLACE(phone, '(\d{3})(\d{3})(\d{4})', '(\1) \2-\3') AS formatted_phone
FROM employees;

SELECT 
    SYSDATE AS current_date,
    SYSTIMESTAMP AS current_timestamp,
    LAST_DAY(hire_date) AS month_end,
    ADD_MONTHS(hire_date, 6) AS review_date,
    MONTHS_BETWEEN(SYSDATE, hire_date) AS months_employed,
    TO_CHAR(hire_date, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date,
    TO_DATE('2023-12-31', 'YYYY-MM-DD') AS converted_date
FROM employees;

SELECT 
    ROUND(123.4567, 2) AS rounded,
    TRUNC(123.4567, 2) AS truncated,
    MOD(10, 3) AS remainder,
    POWER(2, 8) AS power,
    SQRT(64) AS square_root,
    WIDTH_BUCKET(salary, 30000, 100000, 5) AS salary_bucket,
    REMAINDER(10, 3) AS alt_remainder
FROM employees;

-- Oracle transaction control
SET TRANSACTION READ WRITE;
-- or READ ONLY for read consistency

-- Explicit transaction
UPDATE accounts SET balance = balance - 100 
WHERE account_id = 123;

UPDATE accounts SET balance = balance + 100 
WHERE account_id = 456;

-- Savepoints
SAVEPOINT before_update;

UPDATE employees SET salary = salary * 1.1;

-- Rollback to savepoint
ROLLBACK TO before_update;

COMMIT;
-- or ROLLBACK;

-- Query historical data
SELECT * FROM employees
AS OF TIMESTAMP TO_TIMESTAMP('2023-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Flashback table
FLASHBACK TABLE employees TO TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE;

-- Anonymous PL/SQL block
DECLARE
    v_emp_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    SELECT emp_name, salary INTO v_emp_name, v_salary
    FROM employees
    WHERE emp_id = 100;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ' earns ' || v_salary);
    
    -- Exception handling
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Employee not found');
END;

-- Create a stored procedure
CREATE OR REPLACE PROCEDURE adjust_salary(
    p_emp_id IN NUMBER,
    p_percent IN NUMBER
) AS
    v_new_salary NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percent/100)
    WHERE emp_id = p_emp_id
    RETURNING salary INTO v_new_salary;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new_salary);
END;

-- Create a function
CREATE OR REPLACE FUNCTION get_dept_avg(p_dept_id NUMBER)
RETURN NUMBER IS
    v_avg_salary NUMBER;
BEGIN
    SELECT AVG(salary) INTO v_avg_salary
    FROM employees
    WHERE dept_id = p_dept_id;
    
    RETURN v_avg_salary;
END;