Welcome back to the Database for Software Developers series. In this post, we’re diving into data transformation, grouping, and aggregation—some of the most powerful tools you can use to manipulate and make sense of your data.

Whether you're building dashboards, cleaning up records, or generating insightful reports, these SQL functions will be your best friends. Let's go step-by

step and see how they work in real-world scenarios.

📌 Real-World Scenarios

Here are a few examples of what we’ll achieve in this guide:

  • ✅ Concatenate multiple fields to show them together
  • 🗑️ Delete abandoned carts older than 3 days
  • 📧 Filter users by email domain (e.g., @mycompany.com)
  • 🕒 Calculate how long applications took to get approved
  • 🔢 Format large numbers with commas for readability

Let’s explore how to implement these using built-in SQL functions.

🧰 What Built-In SQL Functions Can Do

SQL is more than just SELECT and WHERE! Here's what built-in functions are capable of:

  • 🎨 Data formatting
  • ✂️ String manipulation
  • 🧮 Numeric calculations
  • 📆 Date and time processing
  • 🔒 Encryption/decryption
  • 🔍 Comparisons and control flows
  • 🔄 Type conversions
  • 📦 Aggregations

🔤 String Manipulation Functions

These are super handy when cleaning or formatting text data:

Function Purpose
CONCAT() Combines multiple strings
SUBSTRING() / SUBSTR() Extracts part of a string
INSTR() Finds the position of a substring
LENGTH() / CHAR_LENGTH() Finds string length
REPLACE() Replaces part of a string
LOWER() / UPPER() Changes case
LEFT() / RIGHT() Gets a substring from the left or right
LPAD() / RPAD() Pads strings
TRIM(), LTRIM(), RTRIM() Removes whitespace

✨ Examples:

1. Extract email domain:

SELECT id, name, SUBSTRING(email, INSTR(email, '@') + 1) AS email_domain 
FROM users;

2. Generate SEO-friendly product slugs:

SELECT title, 
       LEFT(description, 30) AS excerpt, 
       LOWER(REPLACE(title, ' ', '-')) AS url_slug 
FROM products;

🔢 Numeric Calculations

These functions are useful for rounding numbers, calculating totals, and more.

Function Purpose
ROUND(), FLOOR(), CEIL() Rounding strategies
MOD() Returns remainder
COUNT(), SUM(), AVG() Aggregates
MAX(), MIN() Finds limits
POWER(), SQRT() Math ops

✨ Examples:

1. Count total admins:

SELECT COUNT(*) FROM users WHERE role = 'admin';

2. Update every 3rd user to 'teacher':

UPDATE users
SET role = 'teacher'
WHERE MOD(id, 3) = 0;

🗓️ Date and Time Functions

Perfect for filtering or calculating time differences.

Function Description
CURDATE() / NOW() Current date/time
DATEDIFF() Days between two dates
DATE_ADD() / DATE_SUB() Add/subtract time
DATE_FORMAT() Format date strings

✨ Examples:

1. How many days since a given date?

SELECT DATEDIFF(CURDATE(), '2014-12-16');

2. Find upcoming payments:

SELECT check_number, amount 
FROM payments 
WHERE payment_date > CURDATE();

🔁 Control Flow Functions

Dynamic logic based on conditions—great for labels or categorizations.

Function Description
IF() Simple if/else logic
CASE Multiple conditions
COALESCE() First non-null value
IFNULL() Fallback value if NULL

✨ Examples:

1. Categorize customers:

SELECT id, name,
  CASE
    WHEN credit_limit > 1000 THEN 'Premium'
    WHEN credit_limit > 500 THEN 'Standard'
    ELSE 'New'
  END AS customer_type
FROM customers;

2. Discount eligibility:

SELECT product_id, 
       IF(stock_qty > 50, 'Eligible', 'Not Eligible') AS discount
FROM products;

🔐 Cryptography & Hashing

Security is key when handling sensitive data.

Function Use
MD5(), SHA1(), SHA2() Hashing
AES_ENCRYPT(), AES_DECRYPT() Encryption

✨ Examples:

1. Storing hashed password:

INSERT INTO users(username, password)
VALUES('goodboy', MD5('the-secret-password'));

2. Signing document version:

UPDATE documents
SET content_hash = SHA1(CONCAT(content, username, salt));

3. Encrypt/decrypt card info:

UPDATE customers 
SET credit_card_info = AES_ENCRYPT('the-card-number', 'encryptionKey');

SELECT customer_id, AES_DECRYPT(credit_card_info, 'encryptionKey') AS card_info 
FROM customers;