The function is similar to

CONCAT

where

WS

means - with separator. The first parameter is the separator between each one of the additional fields, that we pass as next parameters

SELECT 
    CONCAT_WS(' - ', first_name, 'joins', 'strings', 'and', 'columns', 'with', 'separator') 
FROM students

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

REPLACE INTO

is the similar to

INSERT INTO

statement. The difference is that it updates the record if it already exists. It's required one of the listed columns to be a private key of the table

REPLACE INTO students (id, first_name, points)
VALUES (41, 'ილიკო', 147)

Note 1

: If primary key is not listed, it will insert the record, but if the primary key is one of the listed columns, it will update the specified row that matches the primary key.

Note 2

: If the primary key exists, all the other omitted columns will get the default values after updating the record. Similar to the

REPLACE INTO

statement is

ON DUPLICATE KEY

. The only difference is that on duplicate key it updates only the listed columns but omitted values stays the same

INSERT INTO students (id, first_name, points) 
VALUES (41, 'გიორგი', 149) 

ON DUPLICATE KEY 

UPDATE first_name = 'გიორგი', points = 123

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

sub-query

used in

SELECT

clause. Calculates student's point in percent based on max points

SELECT
    id,
    first_name,
    points,
    (
        points * 100 /
        (
            SELECT
                MAX(points)
            FROM
                students
        )
    ) AS percent
FROM
    students

sub-query

used in

IF

conditional statement. Highlights the student with title

Highest

which has highest points

SELECT *, 
    IF(points>=90, IF(points=(SELECT MAX(points) FROM students), "Highest", "Brilliant"), "Lazy") AS class
FROM `students`
ORDER BY points DESC

sub-query

used in

FROM

clause. Select liked notes with likes counts and authors

SELECT 
    students.first_name, 
    note_id, 
    notes.note,
    liked_notes.likes_count
FROM (  
    	SELECT 
            note_likes.note_id AS note_id,
            COUNT(note_likes.id) AS likes_count
        FROM note_likes
        GROUP BY note_likes.note_id
    ) AS `liked_notes`
JOIN notes ON liked_notes.note_id = notes.id
JOIN students ON notes.student_id = students.id
ORDER BY likes_count DESC

sub-query

used in

WHERE

clause. Selects all students that have max points

SELECT
    id,
    first_name,
    points
FROM
    students
WHERE
    points = (
        SELECT
            MAX(points)
        FROM
            students
    )

sub-query

used in

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')
)

sub-query

used in

WHERE

clause in

UPDATE

statement. Updates students table based on notes table column

notes.id

UPDATE 
  students 
SET 
  points = points + 1 
WHERE 
  student_id = (
    SELECT 
      student_id 
    FROM 
      notes 
    WHERE 
      notes.id = 1
  )