Discover the Top 10 Essential SQL Functions

Beginner Functions

1. COUNT()

• What It Does: Counts the number of rows in a table or rows that meet a specific condition.

• Analogy: Think of a classroom with students, and you're counting how many students are wearing blue shirts.

• Example:

    SELECT COUNT(*) FROM employees;
  

  Explained: This counts all the rows in the employees table, telling you how many employees exist.

---

2. AVG()

• What It Does: Calculates the average of numeric values in a column.

• Analogy: It's like finding the average score of students in a test by adding all the scores and dividing by the total number of students.

• Example:

    SELECT AVG(salary) FROM employees;
  

  Explained: This computes the average salary of all employees in the table.

---

3. CONCAT()

• What It Does: Combines two or more strings into one.

• Analogy: Imagine merging someone's first and last names into a full name.

• Example:

    SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
  

  Explained: This creates a full name by joining the first_name and last_name columns with a space in between.

---

Advanced Functions

4. COALESCE()

• What It Does: Returns the first non-NULL value from a list.

• Analogy: If you ask for someone's email but they didn’t provide one, you look for their phone number instead.

• Example:

    SELECT COALESCE(phone_number, 'Not provided') AS contact FROM customers;
  

  Explained: This returns the phone number if it exists; otherwise, it displays "Not Provided."

---

5. ROW_NUMBER()

• What It Does: Assigns a unique number to each row based on a specified order.

• Analogy: Ranking players in a game based on their scores.

• Example:

    SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank, first_name FROM employees;
  

  Explained: Ranks employees by salary, starting with the highest-paid.

---

6. JSON_VALUE()

• What It Does: Extracts data from JSON-formatted columns.

• Analogy: Reading a book and picking a specific piece of information from a paragraph.

• Example:

    SELECT JSONB_EXTRACT_PATH_TEXT(metadata, 'customer', 'name') AS customer_name 
    FROM orders;
  

  Explained: Extracts the customer's name from the metadata JSON column.

---

7. STRING_AGG()

• What It Does: Concatenates values from multiple rows into a single string with a delimiter.

• Analogy: Making a shopping list by combining items from different sources into one list.

• Example:

    SELECT STRING_AGG(department, ', ') AS departments FROM employees;
  

  Explained: Combines all department names into a single string, separated by commas.

---

8. Window Functions (SUM() with OVER)

• What It Does: Performs aggregate calculations across a specific "window" of rows.

• Analogy: Finding a team's total score while still showing individual player scores.

• Example:

    SELECT department, SUM(salary) OVER (PARTITION BY department) AS total_salary
    FROM employees;
  

  Explained: Calculates the total salary for each department without collapsing the data.

---

9. LEAD()

• What It Does: Accesses the next row's value in a result set.

• Analogy: Peeking at the next player in line during a game.

• Example:

    SELECT first_name, salary, LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary
    FROM employees;
  

  Explained: Displays an employee's salary alongside the next highest salary.

---

10. CTEs

• What It Does: Breaks a complex query into manageable parts by creating temporary tables.

• Analogy: Solving a big math problem by breaking it into smaller steps.

• Example:

    WITH department_totals AS (
        SELECT department, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department
    )
    SELECT * FROM department_totals;
  

  Explained: First, calculate total salaries by department, then reuse this result in another query.

---

Sample Dataset

Here’s a script to create a mock PostgreSQL database and populate it with data that aligns with the SQL functions you want to showcase.

---

Step 1: Create Tables

-- Create a table for employees
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE
);

-- Create a table for customers
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone_number VARCHAR(15),
    email VARCHAR(100)
);

-- Create a table for orders with JSON data
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    order_date DATE,
    total_amount DECIMAL(10, 2),
    metadata JSONB
);

---

Step 2: Insert Mock Data

Insert Employees Data

INSERT INTO employees (first_name, last_name, department, salary, hire_date)
VALUES
    ('John', 'Doe', 'Sales', 50000, '2021-01-15'),
    ('Jane', 'Smith', 'Engineering', 75000, '2020-03-22'),
    ('Alice', 'Brown', 'Marketing', 45000, '2022-07-10'),
    ('Bob', 'Taylor', 'Engineering', 80000, '2019-11-05'),
    ('Charlie', 'Wilson', 'Sales', 52000, '2023-02-01');

Insert Customers Data

INSERT INTO customers (first_name, last_name, phone_number, email)
VALUES
    ('Emily', 'Johnson', '1234567890', 'emily.johnson@example.com'),
    ('Michael', 'Clark', NULL, 'michael.clark@example.com'),
    ('Sarah', 'Lee', '9876543210', 'sarah.lee@example.com'),
    ('Chris', 'Evans', NULL, NULL);

Insert Orders Data (with JSON)

INSERT INTO orders (customer_id, order_date, total_amount, metadata)
VALUES
    (1, '2023-01-10', 150.50, '{"customer": {"name": "Emily Johnson", "loyalty_level": "Gold"}, "items": [{"item": "Laptop", "price": 1000}]}'),
    (2, '2023-02-15', 200.00, '{"customer": {"name": "Michael Clark", "loyalty_level": "Silver"}, "items": [{"item": "Phone", "price": 800}]}'),
    (3, '2023-03-05', 50.00, '{"customer": {"name": "Sarah Lee", "loyalty_level": "Bronze"}, "items": [{"item": "Headphones", "price": 200}]}');