🧠 Database for Software Developers – Data Transformation, Grouping & Aggregation (Part 1)

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:

✨ 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.

✨ 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.

✨ 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.

✨ 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.

✨ 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;