Class Notes: SQL Clauses, Aggregate Functions, and MySQL Functions
1. Sorting Output (ORDER BY Clause)
By default, records appear in the order they were entered.
ORDER BY sorts records in ascending (ASC) or descending (DESC) order.
Examples:
SELECT * FROM emp ORDER BY name; -- Ascending (default)
SELECT * FROM emp ORDER BY salary DESC; -- Descending
SELECT * FROM emp ORDER BY dept ASC, salary DESC; -- Multiple columns
2. Aggregate Functions
Aggregate functions perform calculations on groups of rows and return a single summary value.
Available Functions:
SUM() – Total of values
AVG() – Average of values
COUNT() – Number of non-NULL values
MAX() – Highest value
MIN() – Lowest value
COUNT(*) – Total rows (including NULLs)
Example Table (emp):
Empno
Name
Dept
Salary
1
Ravi
Sales
24000
2
Sunny
Sales
35000
3
Shobit
IT
30000
4
Vikram
IT
27000
5
Nitin
HR
45000
6
Krish
HR
NULL
Examples:
SELECT SUM(salary) FROM emp; -- Output: 161000
SELECT SUM(salary) FROM emp WHERE dept='Sales'; -- Output: 59000
SELECT AVG(salary) FROM emp; -- Output: 32200
SELECT AVG(salary) FROM emp WHERE dept='Sales'; -- Output: 29500
SELECT COUNT(name) FROM emp; -- Output: 5
SELECT COUNT(salary) FROM emp WHERE dept='HR'; -- Output: 1
SELECT COUNT(DISTINCT dept) FROM emp; -- Output: 3
SELECT MAX(salary) FROM emp; -- Output: 45000
SELECT MAX(salary) FROM emp WHERE dept='Sales'; -- Output: 35000
SELECT MIN(salary) FROM emp; -- Output: 24000
SELECT MIN(salary) FROM emp WHERE dept='IT'; -- Output: 27000
SELECT COUNT(*) FROM emp; -- Output: 6 (includes NULL)
SELECT COUNT(salary) FROM emp; -- Output: 5 (excludes NULL)
Key Notes:
COUNT(*) counts all rows (including NULL).
COUNT(column) counts non-NULL values only.
All aggregate functions ignore NULL values.
3. GROUP BY Clause
Divides records into logical groups for aggregate calculations.
Only grouped columns or aggregate functions can be in SELECT.
Examples:
SELECT dept, SUM(salary) FROM emp GROUP BY dept;
SELECT dept, AVG(salary), MAX(salary), COUNT(*) FROM emp GROUP BY dept;
Error Example:
SELECT ename, job, SUM(sal) FROM emp GROUP BY job; -- Error: ename not in GROUP BY
4. HAVING Clause
Filters groups after GROUP BY (unlike WHERE, which filters before).
Can use aggregate functions in HAVING.
Examples:
SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVING dept IN ('HR', 'Sales');
SELECT dept, MAX(salary), MIN(salary) FROM emp WHERE salary >= 20000
GROUP BY dept HAVING COUNT(*) > 2;
WHERE vs. HAVING:
WHERE
HAVING
Filters rows before grouping
Filters groups after grouping
Cannot use aggregates
Can use aggregates
5. MySQL Functions
A. String Functions
Function
Description
Example
CHAR()
Returns ASCII character
SELECT CHAR(65); → ‘A’
CONCAT()
Concatenates strings
SELECT CONCAT(name, ' works in ', dept);
LOWER()/LCASE()
Converts to lowercase
SELECT LOWER('INDIA'); → ‘india’
SUBSTRING(S,P,N)
Extracts substring
SELECT SUBSTRING('LAPTOP',3,3); → ‘PTO’
UPPER()/UCASE()
Converts to uppercase
SELECT UPPER('india'); → ‘INDIA’
TRIM()
Removes leading/trailing spaces
SELECT TRIM(' Apple '); → ‘Apple’
INSTR()
Finds substring position
SELECT INSTR('COMPUTER','PUT'); → 4
LENGTH()
Returns string length
SELECT LENGTH('Python'); → 6
LEFT(S,N)
Returns left N characters
SELECT LEFT('KV NO1',2); → ‘KV’
RIGHT(S,N)
Returns right N characters
SELECT RIGHT('KV NO1',3); → ‘NO1’
B. Numeric Functions
Function
Description
Example
MOD(M,N)
Remainder of M/N
SELECT MOD(11,5); → 1
POWER(B,P)
B raised to power P
SELECT POWER(2,5); → 32
ROUND(N,D)
Rounds N to D decimal places
SELECT ROUND(11.589,2); → 11.59
SQRT(N)
Square root of N
SELECT SQRT(144); → 12
TRUNCATE(M,N)
Truncates without rounding
SELECT TRUNCATE(15.789,2); → 15.78
C. Date & Time Functions
Function
Description
Example
CURDATE()
Current date
SELECT CURDATE(); → 2023-11-20
MONTH()
Extracts month
SELECT MONTH('2023-08-15'); → 8
YEAR()
Extracts year
SELECT YEAR('2023-08-15'); → 2023
DAYNAME()
Weekday name
SELECT DAYNAME('2023-12-04'); → ‘Monday’
NOW()
Current date & time (fixed)
SELECT NOW(); → 2023-11-20 10:30:00
SYSDATE()
Current date & time (dynamic)
SELECT SYSDATE(); → 2023-11-20 10:30:05
Difference: NOW() vs. SYSDATE()
NOW() returns the same timestamp for all calls in a query.
SYSDATE() returns the current timestamp at execution time.
