🔨 DDL (Data Definition Language)
CREATE TABLE
-- Basic table creation with constraints
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE,
hire_date DATE DEFAULT SYSDATE,
salary NUMBER(10,2) CHECK (salary > 0),
dept_id NUMBER REFERENCES departments(dept_id),
CONSTRAINT emp_email_uk UNIQUE (email),
CONSTRAINT emp_salary_ck CHECK (salary > 0)
);Common Constraints
| Constraint | Syntax | Purpose |
|---|---|---|
| PRIMARY KEY | employee_id NUMBER PRIMARY KEY | Unique identifier, NOT NULL |
| NOT NULL | first_name VARCHAR2(50) NOT NULL | Column must have a value |
| UNIQUE | email VARCHAR2(100) UNIQUE | All values unique (allows NULL) |
| CHECK | salary NUMBER CHECK (salary > 0) | Value must satisfy condition |
| FOREIGN KEY | dept_id REFERENCES departments | Reference to another table |
| DEFAULT | hire_date DATE DEFAULT SYSDATE | Default value if not provided |
ALTER TABLE
-- Add new column
ALTER TABLE employees ADD phone_number VARCHAR2(20);
-- Modify existing column (increase size)
ALTER TABLE employees MODIFY first_name VARCHAR2(100);
-- Drop column
ALTER TABLE employees DROP COLUMN phone_number;
-- Disable/Enable constraints
ALTER TABLE employees DISABLE CONSTRAINT emp_salary_ck;
ALTER TABLE employees ENABLE CONSTRAINT emp_salary_ck;
-- Rename table
ALTER TABLE employees RENAME TO staff;
-- Rename column
ALTER TABLE employees RENAME COLUMN phone_number TO contact_phone;DROP TABLE
-- Drop table with cascading constraints
DROP TABLE employees CASCADE CONSTRAINTS;
-- Drop table keeping data (use for recycle bin)
DROP TABLE temp_data;
-- Restore from recycle bin
FLASHBACK TABLE temp_data TO BEFORE DROP;
⚠️ Warning: CASCADE CONSTRAINTS drops dependent objects. Use cautiously in production!
✏️ DML (Data Manipulation Language)
INSERT - Adding Data
-- Standard INSERT
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1001, 'John', 'Smith', 75000);
-- INSERT without column list (must provide all values in order)
INSERT INTO employees
VALUES (1002, 'Sarah', 'Jones', 'sarah@company.com', SYSDATE, 85000, 10);
-- INSERT multiple rows at once
INSERT ALL
INTO employees VALUES (1003, 'Mike', 'Brown', 72000)
INTO employees VALUES (1004, 'Lisa', 'White', 81000)
SELECT * FROM dual;
-- INSERT from SELECT (copy data)
INSERT INTO employees_backup
SELECT * FROM employees WHERE salary > 80000;UPDATE - Modifying Data
-- Update single record
UPDATE employees
SET salary = 80000
WHERE employee_id = 1001;
-- Update multiple columns
UPDATE employees
SET salary = salary * 1.1,
hire_date = SYSDATE
WHERE dept_id = 10;
-- Update using subquery
UPDATE employees
SET salary = (
SELECT AVG(salary)
FROM employees
WHERE dept_id = 20
)
WHERE employee_id = 1005;
-- Update with CASE expression
UPDATE employees
SET salary = CASE
WHEN dept_id = 10 THEN salary * 1.15
WHEN dept_id = 20 THEN salary * 1.10
ELSE salary
END;DELETE - Removing Data
-- Delete specific records
DELETE FROM employees
WHERE employee_id = 1001;
-- Delete by condition
DELETE FROM employees
WHERE salary < 30000 AND dept_id IS NULL;
-- Delete all records (use TRUNCATE for speed)
DELETE FROM employees;
-- TRUNCATE (faster, cannot rollback in some cases)
TRUNCATE TABLE temp_table;
-- Check rows affected
DELETE FROM employees WHERE salary < 30000;
DBMS_OUTPUT.put_line('Rows deleted: ' || SQL%ROWCOUNT);
💡 Tip: Use TRUNCATE for DELETE all (cannot rollback in AUTOCOMMIT mode), but DELETE can be rolled back. TRUNCATE resets identity, DELETE preserves it.
🔄 Transactions (ACID Properties)
ACID Properties
| Property | Meaning | Example |
|---|---|---|
| Atomicity | All or nothing - whole transaction succeeds or fails | Money transfer: debit & credit both succeed or both fail |
| Consistency | Database moves from one consistent state to another | All constraints validated before commit |
| Isolation | Transactions don't interfere with each other | Read locks prevent dirty reads |
| Durability | Committed data survives failures | Data written to redo logs, persists |
Transaction Control
-- Start transaction (implicit)
BEGIN; -- PL/SQL blocks use BEGIN
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
-- COMMIT: Save all changes permanently
COMMIT;
-- Example with rollback
BEGIN
UPDATE accounts SET balance = 0;
-- Oops! Wrong update, rollback
ROLLBACK; -- Undo changes
END;
-- SAVEPOINT: Rollback to checkpoint
SAVEPOINT sp1;
UPDATE accounts SET balance = 500;
-- Rollback to sp1, undoing the UPDATE
ROLLBACK TO SAVEPOINT sp1;Isolation Levels
| Level | Dirty Read | Non-repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | No | Possible | Possible |
| REPEATABLE READ | No | No | Possible |
| SERIALIZABLE | No | No | No |
-- Set transaction isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Set read-only transaction (for reports)
SET TRANSACTION READ ONLY;
-- Default is READ COMMITTED✅ Best Practices
DDL Best Practices
- Use meaningful names: employee_id instead of eid
- Define constraints: NOT NULL, UNIQUE, CHECK at creation time
- Use REFERENCES for FK: Maintain referential integrity
- Partition large tables: For better performance
- Plan for growth: Use appropriate data types (VARCHAR2 vs CHAR)
- Document structure: Add comments to explain complex designs
DML Best Practices
- Use transactions: Always group related operations
- Avoid N+1 queries: Use JOIN instead of loops
- Use bind variables: Prevent SQL injection, improve reusability
- Batch operations: Insert/update in batches for performance
- Always have WHERE clause: Prevent accidental bulk deletes
- Test on development: Before running on production
-- Example: Safe transaction with error handling
BEGIN
INSERT INTO audit_log (action, timestamp)
VALUES ('Employee Update Started', SYSDATE);
UPDATE employees
SET salary = salary * 1.1
WHERE dept_id = 10;
IF SQL%ROWCOUNT = 0 THEN
RAISE NO_DATA_FOUND;
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
DBMS_OUTPUT.put_line('No employees found');
END;✓ Learning Checklist - Module 3