Chapter 15: Advanced Data Manipulation
This is where we go beyond just reading and writing data and start transforming it like real data ninjas!
In real life, raw data from the database is often ugly or incomplete:
- Prices without currency symbols
- Names split into first/last
- Dates in wrong format
- Categories that need to be grouped as “Cheap / Medium / Expensive”
Today we’re going to learn the most useful advanced manipulation tools:
- CASE statement — like if-else logic inside SQL
- String functions — CONCAT, SUBSTRING, REPLACE, UPPER/LOWER, TRIM, etc.
- Date functions — DATE_FORMAT, DATEDIFF, DATE_ADD, NOW(), etc.
- Mathematical functions — ROUND, CEIL, FLOOR, ABS, POWER, etc.
We’ll use our bookshop database with the books and authors tables. Let’s first add a few useful columns to make examples more realistic:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
ALTER TABLE books ADD COLUMN first_name VARCHAR(100), ADD COLUMN last_name VARCHAR(100), ADD COLUMN full_name VARCHAR(200), ADD COLUMN discount_percentage DECIMAL(5,2) DEFAULT 0.00; |
(You can imagine we’ve filled these with data.)
1. CASE Statement – If-Then-Else Logic Inside SQL
CASE is SQL’s way of doing conditional logic — exactly like if-else in programming.
Basic Syntax:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END |
Example 1 – Categorize books by price
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT title, price, CASE WHEN price < 200.00 THEN 'Budget Friendly' WHEN price BETWEEN 200.00 AND 350.00 THEN 'Mid-Range' WHEN price > 350.00 THEN 'Premium' ELSE 'Unknown' END AS price_category FROM books ORDER BY price; |
Result example:
| title | price | price_category |
|---|---|---|
| The Blue Umbrella | 180.00 | Budget Friendly |
| Wise and Otherwise | 199.00 | Budget Friendly |
| The Room on the Roof | 250.00 | Mid-Range |
| The Immortals of Meluha | 349.00 | Mid-Range |
| The Palace of Illusions | 450.00 | Premium |
Example 2 – Give discount labels based on stock
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT title, stock, CASE WHEN stock > 100 THEN 'Super Popular – 20% OFF!' WHEN stock BETWEEN 50 AND 100 THEN 'Popular – 10% OFF' WHEN stock < 50 THEN 'Low Stock – No Discount' ELSE 'Out of Stock' END AS discount_message, ROUND(price * CASE WHEN stock > 100 THEN 0.80 WHEN stock BETWEEN 50 AND 100 THEN 0.90 ELSE 1.00 END, 2) AS discounted_price FROM books; |
Pro Tip: You can also use searched CASE (no expression before CASE):
SQL
|
0 1 2 3 4 5 6 7 8 9 |
CASE WHEN stock > 100 THEN 'High' WHEN stock <= 100 THEN 'Low' END |
2. String Functions – Manipulating Text Like Magic
These are super common when cleaning or formatting names, descriptions, etc.
| Function | What it does | Example |
|---|---|---|
| CONCAT() | Join strings | CONCAT(first_name, ‘ ‘, last_name) |
| CONCAT_WS() | Join with separator (ignores NULLs) | CONCAT_WS(‘ ‘, first_name, last_name) |
| UPPER() / LOWER() | Change case | UPPER(title) |
| TRIM() / LTRIM() / RTRIM() | Remove spaces | TRIM(‘ Hello ‘) → ‘Hello’ |
| SUBSTRING() / SUBSTR() | Extract part of string | SUBSTRING(title, 1, 10) |
| LENGTH() / CHAR_LENGTH() | Length of string | LENGTH(title) |
| REPLACE() | Replace substring | REPLACE(title, ‘The’, ‘A’) |
| LEFT() / RIGHT() | Take left/right characters | LEFT(title, 5) |
| LOCATE() / POSITION() | Find position of substring | LOCATE(‘The’, title) |
Example 1 – Create full name and clean title
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT CONCAT_WS(' ', first_name, last_name) AS author_full_name, UPPER(title) AS book_title_upper, TRIM(title) AS cleaned_title, SUBSTRING(title, 1, 20) AS short_title, REPLACE(title, 'The', 'A') AS title_without_the FROM books LIMIT 5; |
Example 2 – Check if title starts with ‘The’
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT title, CASE WHEN LEFT(title, 4) = 'The ' THEN 'Starts with The' ELSE 'Other' END AS title_type FROM books; |
3. Date Functions – Working with Dates and Times
Dates are tricky — but these functions make them easy.
| Function | What it does | Example |
|---|---|---|
| NOW() / CURRENT_TIMESTAMP | Current date + time | NOW() |
| CURDATE() / CURRENT_DATE | Current date only | CURDATE() |
| DATE_FORMAT() | Format date as string | DATE_FORMAT(published_date, ‘%d-%m-%Y’) |
| DATEDIFF() | Days between two dates | DATEDIFF(CURDATE(), published_date) |
| DATE_ADD() / DATE_SUB() | Add/subtract days/months/years | DATE_ADD(published_date, INTERVAL 1 YEAR) |
| YEAR() / MONTH() / DAY() | Extract part of date | YEAR(published_date) |
| DAYNAME() / MONTHNAME() | Day or month name | DAYNAME(published_date) |
Example 1 – Show how old each book is
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT title, published_year, DATEDIFF(CURDATE(), published_date) AS days_since_published, CONCAT( FLOOR(DATEDIFF(CURDATE(), published_date) / 365), ' years old' ) AS book_age FROM books; |
Example 2 – Format dates nicely + add one year
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT title, DATE_FORMAT(published_date, '%d %M %Y') AS formatted_date, DATE_FORMAT(DATE_ADD(published_date, INTERVAL 1 YEAR), '%d-%m-%Y') AS anniversary_date FROM books; |
4. Mathematical Functions – Number Crunching
| Function | What it does | Example |
|---|---|---|
| ROUND() | Round to n decimals | ROUND(price * 1.18, 2) |
| CEIL() / FLOOR() | Round up / down | CEIL(99.1) → 100, FLOOR(99.9) → 99 |
| ABS() | Absolute value | ABS(-50) → 50 |
| POWER() / POW() | Raise to power | POWER(2, 3) → 8 |
| SQRT() | Square root | SQRT(16) → 4 |
| MOD() | Remainder | MOD(10, 3) → 1 |
| RAND() | Random number (0–1) | RAND() |
Example – Apply GST (18%) and round prices
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT title, price AS original_price, ROUND(price * 1.18, 2) AS price_with_gst, CEIL(price * 1.18) AS rounded_up_gst_price FROM books; |
Full Realistic Report – Combining Everything
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- Fancy book catalog report SELECT CONCAT_WS(' by ', UPPER(title), CONCAT(first_name, ' ', last_name) ) AS book_info, CASE WHEN price < 200 THEN 'Budget Gem' WHEN price < 350 THEN 'Worth It' ELSE 'Premium Pick' END AS recommendation, ROUND(price * 1.18, 2) AS price_with_gst, CONCAT( DATEDIFF(CURDATE(), published_date) DIV 365, ' years, ', DATEDIFF(CURDATE(), published_date) MOD 365, ' days old' ) AS book_age, IF(stock > 50, 'In Stock', 'Low Stock') AS availability FROM books ORDER BY price DESC; |
