GROUP BY

groups the rows that have the same values in some columns. In this example, we group all students with the same last name and count them for individual unique last names

SELECT last_name, COUNT(*)
FROM students 
GROUP BY last_name

Calculates average points for each unique last name

SELECT last_name, AVG(points)
FROM students 
GROUP BY last_name

Returns unique last name and first name, meaning, if there are two rows with the exact same first names and last names, it will be appeared once

SELECT 
    first_name, 
    last_name
FROM students
GROUP BY first_name, last_name

The query returns the same result using

DISTINCT

keyword as the above query

SELECT DISTINCT
    first_name, 
    last_name
FROM students

If we want to filter the aggregated data, it's not possible to use

WHERE

clause, because

WHERE

runs before running

GROUP BY

clause

SELECT
    last_name,
    AVG(points) average_points
FROM students
WHERE average_points > 70
GROUP BY last_name

The query gives the following error:

Unknown column 'average_points' in 'where clause'

Instead, we need to use

HAVING

because it's specifically designed to filter data after running

GROUP BY

clause:

SELECT
    last_name,
    AVG(points) average_points
FROM students
GROUP BY last_name
HAVING average_points > 70

Converts a string or column value to

UPPER-CASE

SELECT 
    UPPER(first_name), 
    UPPER('Converts a string or column value to UPPER-CASE') 
FROM students

Converts a string or column value to

lower-case

SELECT 
    LOWER(first_name), 
    LOWER('Converts a string or column value to lower-case') 
FROM students

Joins strings and columns together

SELECT 
    CONCAT(first_name, ' ', 'joins', ' ', 'strings', ' ', 'and', ' ', 'columns') 
FROM students

Note: If the function parameter is not surrounded by

quotation marks

, then the parameter is going to be interpreted as a column value

The function

LENGTH

Returns the length (in bytes)

SELECT 
    id, 
    first_name,
    LENGTH(first_name) first_name_length 
FROM students

Note: Each Georgian letter takes

three bytes

The function

TRIM

removes leading and trailing spaces

SELECT 
    LENGTH(' text ') length,
    LENGTH(TRIM(' text ')) length_with_trim

The function

RTRIM

removes trailing spaces (removes spaces from the end)

SELECT 
    LENGTH(' text ') length,
    LENGTH(RTRIM(' text ')) length_with_right_trim

The function

LTRIM

remove leading spaces (removes spaces from the beginning)

SELECT 
    LENGTH(' text ') length,
    LENGTH(LTRIM(' text ')) length_with_left_trim

The function

LEFT

returns leftmost characters. In this case 5 characters because we pass 5 as second parameter

SELECT 
    LEFT(first_name, 5) AS 'five leftmost characters'
FROM students

The function

RPAD

appends string (third parameter) the specified number of times (second parameter) to the first parameter. In this example each one of the student's last name that is less than 10 characters long, is filled with

-

SELECT 
    RPAD(first_name, 10, '-') AS 'student name'
FROM students