Applying Functions in SQL Queries:

How to Know Which Function to Use:

You can use these functions to transform data, calculate values, or extract specific information in your queries.

Let’s go through some examples that clarify how to use different functions in SQL.

Example 1: String Functions - Formatting Names

Let’s say you want to format employee names, ensuring the first letter of their first and last names is 

SELECT 

    CONCAT(UPPER(SUBSTRING(e.first_name, 1, 1)), LOWER(SUBSTRING(e.first_name, 2))) AS formatted_first_name,

    CONCAT(UPPER(SUBSTRING(e.last_name, 1, 1)), LOWER(SUBSTRING(e.last_name, 2))) AS formatted_last_name

FROM Employees e;

Explanation:

• UPPER() and LOWER() are string functions used to make the first letter uppercase and the rest of the name lowercase.
• CONCAT() is used to join the modified first and last names together.

Example 2: Date Functions - Calculating Years of Employment

If you want to calculate how long an employee has been working based on their hire_date, you can use the DATEDIFF() function:

SELECT 

    e.first_name,

    e.last_name,

    DATEDIFF(CURDATE(), e.hire_date) AS days_employed,

    DATEDIFF(CURDATE(), e.hire_date) / 365 AS years_employed

FROM Employees e;

Explanation:

• DATEDIFF(CURDATE(), e.hire_date) calculates the number of days between today (CURDATE()) and the employee’s hire_date.
• Dividing the number of days by 365 gives an approximate number of years the employee has been with the company.

Example 3: Aggregate Functions - Average Salary by Department

If you want to calculate the average salary by department, you can use the AVG() function:

SELECT 

    d.department_name,

    AVG(e.salary) AS avg_salary

FROM 

    Employees e

JOIN 

    Departments d ON e.department_id = d.department_id

GROUP BY 

    d.department_name;

Explanation:

• AVG(e.salary) calculates the average salary of employees in each department.
• GROUP BY groups the employees by department, so the aggregation happens for each department separately.

Example 4: Mathematical Functions - Rounding Salaries

Suppose you want to round employee salaries to the nearest thousand for a report:

SELECT 
    e.first_name,
    e.last_name,
    ROUND(e.salary, -3) AS rounded_salary
FROM Employees e;

Explanation:
ROUND(e.salary, -3) rounds the salary to the nearest thousand 
(the negative value indicates rounding to a place before the decimal point,
 in this case, 
to the thousands).
Example 5: Using CASE Statements - Conditional Logic in Queries
You can use the CASE function to apply conditional logic in SQL.
For example, you want to display a bonus eligibility column based on the employee's salary:
SELECT 
    e.first_name,
    e.last_name,
    e.salary,
    CASE 
        WHEN e.salary > 80000 THEN 'Eligible'
        ELSE 'Not Eligible'
    END AS bonus_eligibility
FROM Employees e;


Explanation:
The CASE function evaluates the salary: if it’s greater than 80,000, the employee is
 marked as "Eligible" for a bonus, otherwise "Not Eligible".
 
Example 6: Conversion Functions - Converting Data Types
To convert a salary to a string format:

SELECT 
    e.first_name,
    e.last_name,
    CAST(e.salary AS CHAR) AS salary_as_string
FROM Employees e;


Explanation:
CAST(e.salary AS CHAR) converts the numerical salary to a string (CHAR).