The function

RTRIM

removes trailing spaces (removes spaces from the end)

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

The function

TRIM

removes leading and trailing spaces

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

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

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

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

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

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

We can use

sub-query

for

INSERT

statement. Before inserting the record,

sub-query

gets

gender_id

based on the provided gender name

INSERT INTO students (
    first_name, 
    last_name, 
    points,
    gender_id
) 
VALUES (
    'ილია', 
    'დავითაშვილი', 
    '84',
    (SELECT id FROM genders WHERE name = 'Male')
)

Returns

COUNT

of all rows of the

students

table

SELECT COUNT(*) AS students_count
FROM students

Returns

minimum

points from

students

table

SELECT MIN(points) AS minimum_points
FROM students

Returns

maximum

points from

students

table

SELECT MAX(points) AS maximum_points
FROM students

Returns

average

points from

students

table

SELECT AVG(points) AS average_points
FROM students

Returns

SUM

of all students points

SELECT SUM(points) AS 'sum of all points'
FROM students

Aggregate functions behaves differently when there is any number of additional columns with any of the aggregate functions in

SELECT

clause.

GROUP BY

clause is needed when there is any additional column from the table. In this example rows are grouped by

last_name

and the value that

SUM

function returns is aggregated. We get all the unique last names with its aggregated points

SELECT last_name, SUM(points) AS 'sum of all points'
FROM students
GROUP BY last_name

We can do the same for all the other aggregated functions Complete list of aggregate functions: https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html

Updates students table based on notes table column -

notes.id

using

implicit JOIN

UPDATE notes, students
SET points = points+1
WHERE notes.student_id = students.id
	AND notes.note = 'My first note'