SQL Functions (Aggregate & Scalar)

Aggregate functions perform operations on multiple rows and return a single value. Examples: SUM(), COUNT(), AVG(), MAX(), MIN().

• COUNT(*) counts all rows, including NULLs.
• COUNT(column_name) counts only non-NULL values in that column.

SELECT COUNT(*) FROM employees;  -- Includes NULLs
SELECT COUNT(salary) FROM employees;  -- Excludes NULLs

It calculates the total sum of a numeric column.

SELECT SUM(salary) FROM employees;

AVG() ignores NULLs while calculating the average.

SELECT AVG(salary) FROM employees WHERE department = 'HR';

SELECT MAX(salary) FROM employees;

SELECT MIN(price) FROM products;

GROUP BY is used to group rows with the same values and apply aggregate functions to each group.

SELECT department, COUNT(*) FROM employees GROUP BY department;

SELECT department, SUM(salary) FROM employees GROUP BY department;

• WHERE filters before grouping.
• HAVING filters after aggregation.

SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000;

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

SELECT job_title, MAX(salary) 
FROM employees 
GROUP BY job_title;

SELECT UPPER('hello world');  -- Output: HELLO WORLD

SELECT LOWER('HELLO');  -- Output: hello

SELECT LENGTH('SQL Functions');  -- Output: 13

SELECT SUBSTRING('HelloWorld', 1, 5);  -- Output: Hello

SELECT CONCAT('Hello', ' ', 'World');  -- Output: Hello World

SELECT REPLACE('I love SQL', 'SQL', 'Database');  
-- Output: I love Database

SELECT POSITION('SQL' IN 'I love SQL');  -- Output: 8

SELECT TRIM('   Hello World   ');  -- Output: 'Hello World'

SELECT CURRENT_DATE;  -- Output: 2025-02-12

SELECT YEAR('2024-02-12');  -- Output: 2024

SELECT MONTH('2024-02-12');  -- Output: 2

SELECT DATEDIFF('2024-02-12', '2024-01-01');  -- Output: 42

SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2024-02-01');  

SELECT DATE_ADD('2024-02-01', INTERVAL 10 DAY);  

SELECT DAYNAME('2024-02-12');  -- Output: Monday

SELECT ROUND(123.4567, 2);  -- Output: 123.46

SELECT ABS(-50);  -- Output: 50

SELECT SQRT(16);  -- Output: 4

SELECT POWER(2, 3);  -- Output: 8

SELECT FLOOR(4.9);  -- Output: 4

SELECT CEIL(4.1);  -- Output: 5

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT salary 
FROM employees 
ORDER BY salary 
LIMIT 1 OFFSET (SELECT COUNT(*)/2 FROM employees);

SELECT YEAR(order_date), SUM(amount) 
FROM sales 
GROUP BY YEAR(order_date);

SELECT * FROM employees WHERE name LIKE 'A%';

SELECT * FROM employees WHERE join_date >= CURDATE() - INTERVAL 30 DAY;

SELECT department, employee_name, salary 
FROM employees e1 
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department = e2.department);

SELECT employee_id, employee_name, salary, 
       SUM(salary) OVER (ORDER BY employee_id) AS running_total 
FROM employees;

SELECT department, ROUND(AVG(salary), 2) AS avg_salary 
FROM employees 
GROUP BY department;

SELECT employee_id, employee_name, department, salary 
FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

SELECT employee_name, 
       MONTHNAME(hire_date) AS hire_month, 
       YEAR(hire_date) AS hire_year 
FROM employees;

SELECT employee_id, employee_name, salary, 
       (salary * 100.0 / (SELECT SUM(salary) FROM employees)) AS salary_percentage 
FROM employees;

SELECT employee_name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 3;

SELECT * FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

SELECT employee_name 
FROM employees 
ORDER BY LENGTH(employee_name) DESC 
LIMIT 1;

SELECT d1.department, d2.department, 
       ABS(AVG(d1.salary) - AVG(d2.salary)) AS salary_difference 
FROM employees d1, employees d2 
WHERE d1.department > d2.department 
GROUP BY d1.department, d2.department;

SELECT e1.employee_name, e1.salary 
FROM employees e1 
JOIN employees e2 ON e1.salary = e2.salary AND e1.employee_id <> e2.employee_id;

SELECT * FROM employees 
ORDER BY hire_date DESC 
LIMIT 1;