SQL DDL, DML, and PL/SQL

DATA DEFINITION LANGUAGE (DDL)

CREATE TABLE:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    table_constraints
);

Data Types:

• Numeric: INT, DECIMAL(p,s), FLOAT, DOUBLE
• String: CHAR(n), VARCHAR(n), TEXT
• Date/Time: DATE, TIME, DATETIME, TIMESTAMP
• Binary: BLOB, BYTEA
• Boolean: BOOLEAN
• JSON: JSON, JSONB (PostgreSQL)

Constraints:

1. PRIMARY KEY:

   • Uniquely identifies each row
   • Cannot be NULL
   • One per table

   CREATE TABLE Employee (
       EmpID INT PRIMARY KEY,
       Name VARCHAR(100)
   );
   

2. FOREIGN KEY:

   • References primary key in another table
   • Enforces referential integrity

   CREATE TABLE Order (
       OrderID INT PRIMARY KEY,
       CustomerID INT,
       FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
           ON DELETE CASCADE
           ON UPDATE CASCADE
   );
   

   Actions:

   • CASCADE: Delete/update related rows
   • SET NULL: Set foreign key to NULL
   • SET DEFAULT: Set to default value
   • NO ACTION/RESTRICT: Reject operation

3. UNIQUE:

   • Ensures all values different
   • Can have multiple per table
   • Can be NULL (unless NOT NULL specified)

   Email VARCHAR(100) UNIQUE
   

4. NOT NULL:

   • Value required

   Name VARCHAR(100) NOT NULL
   

5. CHECK:

   • Custom constraint

   Age INT CHECK (Age >= 18 AND Age <= 100)
   Salary DECIMAL(10,2) CHECK (Salary > 0)
   

6. DEFAULT:

   • Default value if not specified

   Status VARCHAR(20) DEFAULT 'Active'
   CreatedDate DATE DEFAULT CURRENT_DATE
   

ALTER TABLE:

-- Add column
ALTER TABLE Employee ADD Email VARCHAR(100);

-- Modify column
ALTER TABLE Employee MODIFY Salary DECIMAL(12,2);
-- or ALTER COLUMN in some databases

-- Drop column
ALTER TABLE Employee DROP COLUMN Email;

-- Add constraint
ALTER TABLE Employee ADD CONSTRAINT chk_salary CHECK (Salary > 0);

-- Drop constraint
ALTER TABLE Employee DROP CONSTRAINT chk_salary;

-- Rename table
ALTER TABLE Employee RENAME TO Staff;

DROP TABLE:

DROP TABLE Employee;  -- Delete table and data
DROP TABLE IF EXISTS Employee;  -- No error if doesn't exist

TRUNCATE TABLE:

TRUNCATE TABLE Employee;  -- Delete all rows, keep structure
-- Faster than DELETE, cannot rollback in some databases

CREATE INDEX:

CREATE INDEX idx_name ON Employee(Name);
CREATE UNIQUE INDEX idx_email ON Employee(Email);
CREATE INDEX idx_dept_sal ON Employee(DeptID, Salary);  -- Composite

CREATE VIEW:

CREATE VIEW HighEarners AS
SELECT Name, Salary, DeptID
FROM Employee
WHERE Salary > 50000;

-- Query view like a table
SELECT * FROM HighEarners WHERE DeptID = 10;

DATA MANIPULATION LANGUAGE (DML)

INSERT:

-- Single row
INSERT INTO Employee (EmpID, Name, DeptID, Salary)
VALUES (101, 'Alice', 10, 60000);

-- Multiple rows
INSERT INTO Employee VALUES
    (102, 'Bob', 20, 55000),
    (103, 'Charlie', 10, 70000);

-- From SELECT
INSERT INTO HighSalaryEmp
SELECT * FROM Employee WHERE Salary > 50000;

UPDATE:

-- Update specific rows
UPDATE Employee
SET Salary = Salary * 1.10
WHERE DeptID = 10;

-- Update multiple columns
UPDATE Employee
SET Salary = 65000, DeptID = 20
WHERE EmpID = 101;

-- Update with subquery
UPDATE Employee
SET Salary = (SELECT AVG(Salary) FROM Employee)
WHERE EmpID = 101;

DELETE:

-- Delete specific rows
DELETE FROM Employee WHERE DeptID = 30;

-- Delete all rows (slower than TRUNCATE)
DELETE FROM Employee;

-- Delete with subquery
DELETE FROM Employee
WHERE Salary < (SELECT AVG(Salary) FROM Employee);

SELECT:

-- Basic SELECT
SELECT Name, Salary FROM Employee;

-- WHERE clause
SELECT * FROM Employee WHERE Salary > 50000;

-- ORDER BY
SELECT * FROM Employee ORDER BY Salary DESC;

-- LIMIT/OFFSET (pagination)
SELECT * FROM Employee LIMIT 10 OFFSET 20;

-- DISTINCT
SELECT DISTINCT DeptID FROM Employee;

-- Aggregate functions
SELECT 
    DeptID,
    COUNT(*) AS NumEmployees,
    AVG(Salary) AS AvgSalary,
    MIN(Salary) AS MinSalary,
    MAX(Salary) AS MaxSalary,
    SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY DeptID
HAVING AVG(Salary) > 55000;

-- Joins
SELECT e.Name, d.DeptName
FROM Employee e
INNER JOIN Department d ON e.DeptID = d.DeptID;

-- Subqueries
SELECT Name
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);

-- EXISTS
SELECT Name
FROM Employee e
WHERE EXISTS (
    SELECT 1 FROM WorksOn w
    WHERE w.EmpID = e.EmpID
);

-- CASE expression
SELECT Name, Salary,
    CASE
        WHEN Salary < 50000 THEN 'Low'
        WHEN Salary BETWEEN 50000 AND 70000 THEN 'Medium'
        ELSE 'High'
    END AS SalaryGrade
FROM Employee;

PL/SQL BASICS

Block Structure:

DECLARE
    -- Variable declarations
    v_name VARCHAR2(100);
    v_salary NUMBER := 50000;  -- Default value
    c_bonus CONSTANT NUMBER := 0.10;  -- Constant
BEGIN
    -- Executable statements
    SELECT Name, Salary INTO v_name, v_salary
    FROM Employee WHERE EmpID = 101;
    
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
    -- Exception handling
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Variables and Data Types:

DECLARE
    v_count NUMBER;
    v_name VARCHAR2(100);
    v_hire_date DATE;
    v_is_active BOOLEAN := TRUE;
    
    -- %TYPE: Match column data type
    v_salary Employee.Salary%TYPE;
    
    -- %ROWTYPE: Match entire row
    v_emp Employee%ROWTYPE;
BEGIN
    v_count := 10;
    v_emp.Name := 'Alice';
END;
/

Control Structures:

IF Statement:

IF v_salary > 70000 THEN
    DBMS_OUTPUT.PUT_LINE('High earner');
ELSIF v_salary > 50000 THEN
    DBMS_OUTPUT.PUT_LINE('Medium earner');
ELSE
    DBMS_OUTPUT.PUT_LINE('Low earner');
END IF;

CASE Statement:

CASE v_grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
    ELSE DBMS_OUTPUT.PUT_LINE('Average');
END CASE;

Loops:

Simple LOOP:

LOOP
    v_count := v_count + 1;
    EXIT WHEN v_count > 10;
END LOOP;

WHILE LOOP:

WHILE v_count <= 10 LOOP
    v_count := v_count + 1;
END LOOP;

FOR LOOP:

FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

-- Reverse
FOR i IN REVERSE 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

CURSORS

Implicit Cursors: Automatically created by Oracle for SQL statements

BEGIN
    UPDATE Employee SET Salary = Salary * 1.10;
    
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('At least one row updated');
    END IF;
END;
/

Explicit Cursors: Programmer-declared for SELECT statements

DECLARE
    CURSOR emp_cursor IS
        SELECT EmpID, Name, Salary
        FROM Employee
        WHERE DeptID = 10;
    
    v_emp_id Employee.EmpID%TYPE;
    v_name Employee.Name%TYPE;
    v_salary Employee.Salary%TYPE;
BEGIN
    OPEN emp_cursor;
    
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_name, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
    END LOOP;
    
    CLOSE emp_cursor;
END;
/

Cursor FOR Loop: Simpler syntax

DECLARE
    CURSOR emp_cursor IS SELECT * FROM Employee;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.Name || ': ' || emp_rec.Salary);
    END LOOP;
END;
/

Cursor with Parameters:

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT * FROM Employee WHERE DeptID = p_dept_id;
BEGIN
    FOR emp_rec IN emp_cursor(10) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.Name);
    END LOOP;
END;
/

PROCEDURES

CREATE OR REPLACE PROCEDURE GiveSalaryRaise(
    p_emp_id IN NUMBER,
    p_percentage IN NUMBER,
    p_new_salary OUT NUMBER
) IS
    v_current_salary NUMBER;
BEGIN
    -- Get current salary
    SELECT Salary INTO v_current_salary
    FROM Employee
    WHERE EmpID = p_emp_id;
    
    -- Calculate new salary
    p_new_salary := v_current_salary * (1 + p_percentage/100);
    
    -- Update
    UPDATE Employee
    SET Salary = p_new_salary
    WHERE EmpID = p_emp_id;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Salary updated from ' || v_current_salary || 
                         ' to ' || p_new_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
        RAISE;
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- Call procedure
DECLARE
    v_new_sal NUMBER;
BEGIN
    GiveSalaryRaise(101, 10, v_new_sal);
    DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new_sal);
END;
/

FUNCTIONS

CREATE OR REPLACE FUNCTION CalculateAnnualSalary(
    p_emp_id IN NUMBER
) RETURN NUMBER IS
    v_monthly_salary NUMBER;
    v_annual_salary NUMBER;
BEGIN
    SELECT Salary INTO v_monthly_salary
    FROM Employee
    WHERE EmpID = p_emp_id;
    
    v_annual_salary := v_monthly_salary * 12;
    
    RETURN v_annual_salary;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- Use in SQL
SELECT Name, CalculateAnnualSalary(EmpID) AS AnnualSalary
FROM Employee;

-- Use in PL/SQL
DECLARE
    v_annual NUMBER;
BEGIN
    v_annual := CalculateAnnualSalary(101);
    DBMS_OUTPUT.PUT_LINE('Annual: ' || v_annual);
END;
/

TRIGGERS

Automatically execute in response to events (INSERT, UPDATE, DELETE)

Types:

• BEFORE Trigger: Executes before operation
• AFTER Trigger: Executes after operation
• Row-level Trigger: Once per affected row (FOR EACH ROW)
• Statement-level Trigger: Once per statement

-- Audit trigger
CREATE OR REPLACE TRIGGER Audit_Salary_Changes
AFTER UPDATE OF Salary ON Employee
FOR EACH ROW
BEGIN
    INSERT INTO SalaryAudit (EmpID, OldSalary, NewSalary, ChangeDate, ChangedBy)
    VALUES (:OLD.EmpID, :OLD.Salary, :NEW.Salary, SYSDATE, USER);
END;
/

-- Validation trigger
CREATE OR REPLACE TRIGGER Check_Salary_Range
BEFORE INSERT OR UPDATE OF Salary ON Employee
FOR EACH ROW
BEGIN
    IF :NEW.Salary < 30000 OR :NEW.Salary > 200000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary must be between 30000 and 200000');
    END IF;
END;
/

-- Auto-increment trigger (before sequences were common)
CREATE OR REPLACE TRIGGER Employee_ID_Trigger
BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
    IF :NEW.EmpID IS NULL THEN
        SELECT Employee_Seq.NEXTVAL INTO :NEW.EmpID FROM DUAL;
    END IF;
END;
/

-- Prevent operations trigger
CREATE OR REPLACE TRIGGER Prevent_Weekend_Changes
BEFORE INSERT OR UPDATE OR DELETE ON Employee
BEGIN
    IF TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
        RAISE_APPLICATION_ERROR(-20002, 'No changes allowed on weekends');
    END IF;
END;
/

Trigger Pseudo-records:

• :OLD: Old column values (UPDATE, DELETE)
• :NEW: New column values (INSERT, UPDATE)

EXCEPTION HANDLING

Pre-defined Exceptions:

BEGIN
    -- Some operations
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- SELECT returns no rows
        DBMS_OUTPUT.PUT_LINE('No data found');
        
    WHEN TOO_MANY_ROWS THEN
        -- SELECT INTO returns multiple rows
        DBMS_OUTPUT.PUT_LINE('Too many rows');
        
    WHEN DUP_VAL_ON_INDEX THEN
        -- Duplicate key violation
        DBMS_OUTPUT.PUT_LINE('Duplicate value');
        
    WHEN VALUE_ERROR THEN
        -- Arithmetic, conversion, truncation error
        DBMS_OUTPUT.PUT_LINE('Value error');
        
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Division by zero');
        
    WHEN OTHERS THEN
        -- Catch all
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/

User-defined Exceptions:

DECLARE
    e_salary_too_high EXCEPTION;
    v_salary NUMBER;
BEGIN
    SELECT Salary INTO v_salary FROM Employee WHERE EmpID = 101;
    
    IF v_salary > 150000 THEN
        RAISE e_salary_too_high;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('Salary is acceptable');
EXCEPTION
    WHEN e_salary_too_high THEN
        DBMS_OUTPUT.PUT_LINE('Salary exceeds maximum allowed');
END;
/

RAISE_APPLICATION_ERROR:

BEGIN
    IF some_condition THEN
        RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
    END IF;
END;
/

PACKAGES

Group related procedures, functions, types together

-- Package specification (interface)
CREATE OR REPLACE PACKAGE EmployeePkg IS
    PROCEDURE HireEmployee(p_name VARCHAR2, p_dept_id NUMBER, p_salary NUMBER);
    FUNCTION GetEmployeeCount RETURN NUMBER;
    PROCEDURE FireEmployee(p_emp_id NUMBER);
END EmployeePkg;
/

-- Package body (implementation)
CREATE OR REPLACE PACKAGE BODY EmployeePkg IS
    
    PROCEDURE HireEmployee(p_name VARCHAR2, p_dept_id NUMBER, p_salary NUMBER) IS
    BEGIN
        INSERT INTO Employee (Name, DeptID, Salary)
        VALUES (p_name, p_dept_id, p_salary);
        COMMIT;
    END;
    
    FUNCTION GetEmployeeCount RETURN NUMBER IS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count FROM Employee;
        RETURN v_count;
    END;
    
    PROCEDURE FireEmployee(p_emp_id NUMBER) IS
    BEGIN
        DELETE FROM Employee WHERE EmpID = p_emp_id;
        COMMIT;
    END;
    
END EmployeePkg;
/

-- Usage
BEGIN
    EmployeePkg.HireEmployee('John Doe', 10, 60000);
    DBMS_OUTPUT.PUT_LINE('Total employees: ' || EmployeePkg.GetEmployeeCount);
END;
/