Oracle Database Learning: Part 3 – SQL Fundamentals: DDL & DML

Oracle SQL Categories

• DDL – Data Definition Language: CREATE, ALTER, DROP, TRUNCATE, RENAME
• DML – Data Manipulation Language: INSERT, UPDATE, DELETE, MERGE
• DCL – Data Control Language: GRANT, REVOKE
• TCL – Transaction Control Language: COMMIT, ROLLBACK, SAVEPOINT
• DQL – Data Query Language: SELECT

Oracle Data Types

DDL: CREATE TABLE

-- Create a sample HR schema
CREATE TABLE departments (
    dept_id    NUMBER(4)    GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dept_name  VARCHAR2(50) NOT NULL,
    location   VARCHAR2(100),
    created_at DATE         DEFAULT SYSDATE
);

CREATE TABLE employees (
    emp_id     NUMBER(6)     GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR2(50)  NOT NULL,
    last_name  VARCHAR2(50)  NOT NULL,
    email      VARCHAR2(100) NOT NULL UNIQUE,
    phone      VARCHAR2(20),
    hire_date  DATE          DEFAULT SYSDATE NOT NULL,
    job_title  VARCHAR2(50),
    salary     NUMBER(10,2)  CHECK (salary > 0),
    dept_id    NUMBER(4),
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Constraints Summary

DDL: ALTER TABLE

-- Add a column
ALTER TABLE employees ADD (manager_id NUMBER(6));

-- Modify a column
ALTER TABLE employees MODIFY (phone VARCHAR2(30));

-- Rename a column
ALTER TABLE employees RENAME COLUMN phone TO mobile;

-- Drop a column
ALTER TABLE employees DROP COLUMN mobile;

-- Add constraint after table creation
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary BETWEEN 1000 AND 999999);

-- Disable / Enable constraint
ALTER TABLE employees DISABLE CONSTRAINT chk_salary;
ALTER TABLE employees ENABLE CONSTRAINT chk_salary;

Sequences

-- Create sequence (used before IDENTITY columns in 11g/12c)
CREATE SEQUENCE emp_seq
    START WITH 1000
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Use in INSERT
INSERT INTO employees (emp_id, first_name, ...)
VALUES (emp_seq.NEXTVAL, 'John', ...);

-- Check current value
SELECT emp_seq.CURRVAL FROM dual;

DML: INSERT

-- Single row
INSERT INTO departments (dept_name, location)
VALUES ('Engineering', 'Bangalore');

-- Multiple rows (Oracle 23ai supports standard multi-row VALUES)
INSERT ALL
  INTO departments (dept_name, location) VALUES ('HR', 'Mumbai')
  INTO departments (dept_name, location) VALUES ('Finance', 'Delhi')
  INTO departments (dept_name, location) VALUES ('IT', 'Hyderabad')
SELECT 1 FROM dual;

COMMIT;

DML: UPDATE

-- Simple update
UPDATE employees
SET salary = salary * 1.10
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Engineering');

-- Update using subquery in SET
UPDATE employees e
SET e.job_title = (
    SELECT d.dept_name || ' Lead'
    FROM departments d
    WHERE d.dept_id = e.dept_id
)
WHERE e.manager_id IS NULL;

DML: DELETE vs TRUNCATE

-- DELETE: DML — logs each row, can be rolled back, fires triggers
DELETE FROM employees WHERE hire_date < DATE '2000-01-01';
ROLLBACK; -- reversible

-- TRUNCATE: DDL — removes all rows, NOT reversible, much faster
TRUNCATE TABLE employees;
-- Cannot rollback TRUNCATE

DML: MERGE (Upsert)

-- MERGE: Insert or Update in one statement
MERGE INTO employees tgt
USING staging_employees src
ON (tgt.email = src.email)
WHEN MATCHED THEN
    UPDATE SET
        tgt.salary    = src.salary,
        tgt.job_title = src.job_title
WHEN NOT MATCHED THEN
    INSERT (first_name, last_name, email, salary, dept_id)
    VALUES (src.first_name, src.last_name, src.email, src.salary, src.dept_id);

Transaction Control

-- Savepoints allow partial rollback
INSERT INTO employees (...) VALUES (...);
SAVEPOINT sp1;

INSERT INTO employees (...) VALUES (...);
SAVEPOINT sp2;

-- Roll back only to sp1 (second insert is undone)
ROLLBACK TO SAVEPOINT sp1;

-- Commit everything up to sp1
COMMIT;

Lab 3: Build an HR Schema

-- Verify tables
SELECT table_name FROM user_tables ORDER BY 1;

-- Check constraints
SELECT constraint_name, constraint_type, table_name
FROM user_constraints WHERE table_name IN ('EMPLOYEES','DEPARTMENTS');

-- Test FK violation
INSERT INTO employees (first_name, last_name, email, dept_id)
VALUES ('Test', 'User', 'test@email.com', 9999); -- ORA-02291

-- Flashback query (if undo available)
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

Common Errors

ORA-00001: unique constraint violated – Inserting duplicate value into a UNIQUE/PK column.

ORA-01400: cannot insert NULL into non-null column – NOT NULL constraint on column with no default.

ORA-02291: integrity constraint violated (parent key not found) – FK value doesn't exist in parent table.

-- Diagnose constraint errors
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints WHERE table_name = 'EMPLOYEES';

Summary

• Use VARCHAR2, NUMBER, DATE as primary data types in Oracle
• IDENTITY columns replace sequences in Oracle 12c+
• TRUNCATE is DDL (non-reversible); DELETE is DML (reversible)
• MERGE is Oracle's upsert: insert-or-update in one atomic statement

Next: Part 4 – Advanced SQL: Joins, Subqueries & Analytical Functions