Sorting and Built-in Functions

#Introduction

Now that you can create tables, run CRUD operations and filter data, it's time to sort data and use built-in functions to manipulate strings, numbers and dates. Almost every backend API uses ORDER BY or built-in functions.

#Sample Table

#ORDER BY

Ascending Order (Default)

SELECT *
FROM employees
ORDER BY salary;          -- same as ORDER BY salary ASC

Descending Order

SELECT *
FROM employees
ORDER BY salary DESC;     -- highest salary first

#Sorting by Multiple Columns

Department ascending, then salary descending:

SELECT *
FROM employees
ORDER BY department ASC,
         salary DESC;

The second column is used only when the first column has duplicate values.

#Sorting with NULL Values

In MySQL, NULLs sort first in ascending order and last in descending order. For consistent "NULLs last" behaviour:

SELECT *
FROM employees
ORDER BY salary IS NULL,   -- 0 for real values, 1 for NULL
         salary;

This pushes NULL values to the end in ascending order. (MySQL has no NULLS FIRST / NULLS LAST syntax.)

#CASE in ORDER BY

Real-world priority — IT first, then Finance, then HR — instead of alphabetical order:

SELECT *
FROM employees
ORDER BY CASE
             WHEN department = 'IT'      THEN 1
             WHEN department = 'Finance' THEN 2
             ELSE 3
         END;

Frequently used in dashboards and custom business sorting. (ORDER BY FIELD(department, 'IT', 'Finance', 'HR') is a MySQL shortcut for the same idea.)

#String Functions

LENGTH() and CHAR_LENGTH()

SELECT name, LENGTH(name)
FROM employees;

UPPER() and LOWER()

SELECT UPPER(name) FROM employees;   -- RAHUL, AMIT, NEHA, ...
SELECT LOWER(name) FROM employees;

CONCAT()

SELECT CONCAT(name, ' works in ', department)
FROM employees;                      -- Rahul works in IT

SUBSTRING()

SELECT SUBSTRING(name, 1, 3)
FROM employees;                      -- Rah, Ami, Neh, ...

Syntax: SUBSTRING(string, start_position, length) — positions start at 1.

REPLACE()

SELECT REPLACE(city, 'Bangalore', 'Bengaluru')
FROM employees;

TRIM()

Removes leading and trailing spaces:

SELECT TRIM('  Rahul  ');            -- 'Rahul'

LOCATE()

Finds the position of a substring (0 if not found):

SELECT LOCATE('hu', 'Rahul');        -- 3
SELECT LOCATE('xy', 'Rahul');        -- 0

#Numeric Functions

#Date & Time Functions

One of the most commonly used areas in backend applications.

SELECT NOW();        -- current date and time, e.g. 2026-07-01 09:15:30
SELECT CURDATE();    -- current date only,     e.g. 2026-07-01
SELECT CURTIME();    -- current time only

SELECT YEAR(joining_date)  FROM employees;
SELECT MONTH(joining_date) FROM employees;
SELECT DAY(joining_date)   FROM employees;

DATEDIFF()

SELECT DATEDIFF(CURDATE(), joining_date)
FROM employees;

Returns the number of days between two dates (first minus second).

DATE_ADD() and DATE_SUB()

SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY);   -- useful for expiry dates
SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY);    -- useful for "last 7 days" reports

LAST_DAY()

SELECT LAST_DAY(CURDATE());   -- last day of the current month

#Nesting Functions

Functions can be combined — the innermost runs first:

SELECT UPPER(TRIM(name))
FROM employees;

SELECT CONCAT(UPPER(name), ' - ', department)
FROM employees;

#Real Spring Boot Example

List<Employee> findAllByOrderBySalaryDesc();

Generated SQL:

SELECT *
FROM employees
ORDER BY salary DESC;

JPQL:

@Query("""
        SELECT e
        FROM Employee e
        ORDER BY e.joiningDate DESC
        """)

generates ... ORDER BY joining_date DESC.

A projection such as SELECT UPPER(e.name) FROM Employee e makes Hibernate generate SQL that uses the database function, reducing work in your Java application.

#Performance Tips

Avoid Functions on Indexed Columns in WHERE

-- ❌ Often prevents index usage
WHERE YEAR(joining_date) = 2026

-- ✅ The index can be used efficiently
WHERE joining_date >= '2026-01-01'
  AND joining_date <  '2027-01-01'

ORDER BY on Indexed Columns

Sorting by an indexed column can be much faster, because MySQL may read rows in index order instead of performing an extra sort (a "filesort").

Avoid Unnecessary Sorting

If the UI doesn't need sorted results, don't add ORDER BY out of habit — sorting large datasets is expensive.

#Hands-On Assignment

Using the employees table:

1. Display employees by highest salary.
2. Display employees by department, then salary descending.
3. Show all employee names in uppercase.
4. Show the first three letters of each employee's name.
5. Concatenate employee name and department.
6. Display employees who joined this year.
7. Display the number of days each employee has worked.
8. Display the last day of the current month.
9. Round the average salary to two decimal places.
10. Display each employee's name after trimming leading or trailing spaces.

Challenge Problems

1. Display employees ordered by salary descending, then name ascending.
2. Show names longer than 5 characters.
3. Display employees who joined within the last 365 days.
4. Replace "IT" with "Technology" in the output without changing the stored data.
5. Sort employees with IT first, then Finance, then everyone else.