PostgreSQL

Basic SQL

SQL syntax has four main categories:

• DDL: Data Definition Language
• DML: Data Manipulation Language
• DCL: Data Control Language
• TCL: Transaction Control Language

1. DDL (Data Definition Language)

-- CREATE TABLE
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);

-- ALTER TABLE: Modify existing table structure
-- Add a new column
ALTER TABLE employees ADD email VARCHAR(100);

-- Modify data type (PostgreSQL syntax)
ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(12, 2);

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

-- DROP TABLE: Permanently delete a table and all its data
DROP TABLE old_employees;

-- TRUNCATE: Remove all data but keep table structure
-- Faster than DELETE for removing all rows
TRUNCATE TABLE temp_data;

2. DML (Data Manipulation Language)

SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 80000;

-- Sorting results
SELECT * FROM employees
ORDER BY salary DESC;

-- INSERT: Add new data
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (1, 'John', 'Doe', 'Engineering', 95000);

-- Insert multiple rows at once
INSERT INTO employees VALUES
  (2, 'Jane', 'Smith', 'Marketing', 75000),
  (3, 'Bob', 'Johnson', 'Sales', 68000);

-- UPDATE: Modify existing data
UPDATE employees
SET salary = salary * 1.10  -- 10% raise
WHERE department = 'Engineering';

-- DELETE: Remove specific rows
DELETE FROM employees
WHERE employee_id = 3;

3. JOINs - Connecting Related Data

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN: All rows from left table, matching rows from right
-- Returns NULL for right table columns when no match exists
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- FULL JOIN: All rows from both tables
-- Returns NULL where matches don't exist on either side
SELECT e.first_name, d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;

4. GROUP BY and Aggregate Functions

SELECT
  COUNT(*) as total_employees,
  SUM(salary) as total_payroll,
  AVG(salary) as average_salary,
  MIN(salary) as lowest_salary,
  MAX(salary) as highest_salary
FROM employees;

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

-- HAVING: Filter groups (use after GROUP BY)
-- 💡 Key difference: WHERE filters rows before grouping, HAVING filters after
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

5. Window Functions - Advanced Analytics

-- Window functions perform calculations across rows related to current row
-- Ranking employees by salary within their department
SELECT
  first_name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank
FROM employees;

-- Running total using window functions
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

6. DCL (Data Control Language) - Security & Permissions

-- GRANT: Give permissions to users
GRANT SELECT, INSERT ON employees TO user_john;

-- REVOKE: Remove permissions from users
REVOKE INSERT ON employees FROM user_john;

7. TCL (Transaction Control Language) - Data Integrity

-- BEGIN: Start a transaction
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

ROLLBACK;

7.1. Procedural Blocks (DO) - PostgreSQL Specific

PostgreSQL's DO statement allows you to execute anonymous code blocks with variables and control flow.

-- Example 1: Give performance-based raises with conditional logic
DO $$
DECLARE
  emp_record RECORD;
  raise_amount DECIMAL(5,2);
  total_updated INT := 0;
BEGIN
  -- Loop through high-performing employees
  FOR emp_record IN
    SELECT employee_id, salary, department
    FROM employees
    WHERE salary < 100000
  LOOP
    -- Different raise percentages by department
    IF emp_record.department = 'Engineering' THEN
      raise_amount := emp_record.salary * 0.15;  -- 15% raise
    ELSIF emp_record.department = 'Sales' THEN
      raise_amount := emp_record.salary * 0.12;  -- 12% raise
    ELSE
      raise_amount := emp_record.salary * 0.10;  -- 10% raise
    END IF;

    -- Apply the raise
    UPDATE employees
    SET salary = salary + raise_amount
    WHERE employee_id = emp_record.employee_id;

    total_updated := total_updated + 1;

    -- Log the action
    RAISE NOTICE 'Updated employee %: new salary = %',
      emp_record.employee_id,
      emp_record.salary + raise_amount;
  END LOOP;

  RAISE NOTICE 'Total employees updated: %', total_updated;
END $$;

-- Example 2: Data validation and cleanup with error handling
DO $$
DECLARE
  invalid_count INT;
  cleaned_count INT := 0;
BEGIN
  -- Count invalid records
  SELECT COUNT(*) INTO invalid_count
  FROM employees
  WHERE salary < 0 OR salary IS NULL;

  IF invalid_count > 0 THEN
    RAISE NOTICE 'Found % invalid salary records', invalid_count;

    -- Fix invalid salaries (set to minimum wage)
    UPDATE employees
    SET salary = 30000
    WHERE salary < 0 OR salary IS NULL;

    GET DIAGNOSTICS cleaned_count = ROW_COUNT;
    RAISE NOTICE 'Cleaned % records', cleaned_count;
  ELSE
    RAISE NOTICE 'No invalid records found';
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Error occurred: %', SQLERRM;
    ROLLBACK;
END $$;

-- Example 3: Create test data with generated values
DO $$
DECLARE
  i INT;
  dept_name TEXT;
  departments TEXT[] := ARRAY['Engineering', 'Sales', 'Marketing', 'HR'];
BEGIN
  FOR i IN 1..20 LOOP
    -- Randomly select department
    dept_name := departments[1 + floor(random() * 4)::INT];

    INSERT INTO employees (employee_id, first_name, last_name, department, salary)
    VALUES (
      1000 + i,
      'TestUser' || i,
      'LastName' || i,
      dept_name,
      50000 + (random() * 50000)::DECIMAL(10,2)
    );
  END LOOP;

  RAISE NOTICE 'Created 20 test employee records';
END $$;

8. Advanced Operators and Conditions

SELECT * FROM products
WHERE price BETWEEN 10 AND 50;

-- IN: Match any value in a list
SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');

-- LIKE: Pattern matching (% = any characters, _ = single character)
SELECT * FROM customers
WHERE last_name LIKE 'Smith%';  -- Starts with 'Smith'

-- Logical operators: Combine multiple conditions
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Sales')
  AND salary > 70000;

-- Subqueries: Use query results in another query
-- Find employees earning above average
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- EXISTS: Check if subquery returns any rows
SELECT * FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e
  WHERE e.department_id = d.id
);

9. Views and Aliases - Code Organization

-- CREATE VIEW: Save a query as a virtual table
CREATE VIEW high_earners AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 100000;

-- Use the view like a regular table
SELECT * FROM high_earners;

SELECT
  e.first_name AS "First Name",
  d.department_name AS "Department",
  e.salary AS "Annual Salary"
FROM employees e
JOIN departments d ON e.department_id = d.id;

Multiversion Concurrency Control

Multiversion concurrency control (MCC or MVCC), is a concurrency control method commonly used by database management systems to provide concurrent access to the database and in programming languages to implement transactional memorｂy

-- Wikipedia

References

• An Empirical Evaluation of In-Memory Multi-Version Concurrency Control
• Implementing Fine-Grained Postgres Permissions for Multi-Tenant Applications

---