Selects all the fields from

students

table (

*

means - all fields)

SELECT * 
FROM students

Selects only

first_name

and

last_name

from

students

table (fields are separated by comma

,

)

SELECT first_name, last_name 
FROM students

WHERE

clause in

SELECT

statement

SELECT first_name, last_name 
FROM students
WHERE id = 5

WHERE

clause (combining several conditions with logical AND)

SELECT *
FROM students
WHERE points > 75 AND last_name = 'გელაშვილი'

WHERE

clause (combining several conditions with logical OR)

SELECT *
FROM students
WHERE last_name = 'გელაშვილი' OR last_name = 'გოგუაძე'

Logical OR with logical AND

SELECT *
FROM students
WHERE points > 75 AND (last_name = 'გელაშვილი' OR last_name = 'გოგუაძე')

ORDER BY

clause in

SELECT

statement

SELECT first_name, last_name 
FROM students
ORDER BY id DESC

ORDER BY

last_name

ascending

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC

ASC

is optional because it's default value

ORDER BY

with several fields

SELECT first_name, last_name 
FROM students
ORDER BY last_name ASC, first_name ASC

ASC

SELECT

with some primitive values

SELECT first_name, last_name, 20 AS age 
FROM students

SELECT

with only primitive values

SELECT 20, 40 AS age 
FROM students

Math operations in

SELECT

statement

SELECT 
    first_name, 
    last_name, 
    points * 1.1 /* + - * / % */ AS points
FROM students

Math operations in

WHERE

clause

SELECT *
FROM students
WHERE points * 1.5 >= 80

Order of the Math operations matters

SELECT 
    first_name, 
    last_name, 
    (points + 10) * 2 AS points
FROM students

Advantages of quotes in alias

SELECT 
    first_name, 
    last_name, 
    (points + 10) * 2 AS 'points calculated'
FROM students

DISTINCT

keyword in SELECT statement

SELECT DISTINCT
    first_name, 
    last_name
FROM students

GROUP BY

clause with several fields (results of the two queries are identical)

SELECT 
    first_name, 
    last_name
FROM students
GROUP BY first_name, last_name

GROUP BY

clause with one field and

COUNT()

function

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

GROUP BY

and

ORDER BY

clauses in one select

SELECT 
    last_name,
    COUNT(last_name) last_name_counter
FROM students
GROUP BY last_name
ORDER BY last_name_counter DESC

LIMIT

clause

SELECT 
    last_name,
    COUNT(last_name) last_name_counter
FROM students
GROUP BY last_name
ORDER BY last_name_counter DESC
LIMIT 2

LIMIT

clause for pagination

SELECT 
    first_name
    last_name
FROM students
ORDER BY points DESC
LIMIT 4, 2

Student with minimum points

SELECT *
FROM students
ORDER BY points
LIMIT 1

Student with minimum points using

MIN()

function

SELECT *
FROM students
WHERE points = (SELECT MIN(points) FROM students)

Student with maximum points

SELECT *
FROM students
ORDER BY points DESC
LIMIT 1

Student with maximum points using

MAX()

function

SELECT *
FROM students
WHERE points = (SELECT MAX(points) FROM students)

Average points grouped by lastname

SELECT 
    last_name,
    AVG(points) average_points
FROM students
GROUP BY last_name
ORDER BY average_points DESC

Select all students with points greater than 70 (comparison operators:

>

>=

<

<=

=

!=

<>

)

SELECT *
FROM students
WHERE points > 70

Select all students with points less than or equal to 70 (using NOT logical operator)

SELECT *
FROM students
WHERE NOT points > 70

Select all students with points between 80 and 90

SELECT *
FROM students
WHERE points >= 80
    AND points <= 90

Select all students with points between 80 and 90 (using BETWEEN AND operator)

SELECT *
FROM students
WHERE points BETWEEN 80 AND 90

Select all students with points more than average

SELECT *
FROM students
WHERE points > (SELECT AVG(points) FROM students)

Select all students where lastname is either 'გელაშვილი' or 'გოგუაძე' with

IN

operator

SELECT *
FROM students
WHERE last_name IN ('გელაშვილი', 'გოგუაძე')

Select all students which lastname is not any of the listed lastnames ('გელაშვილი', 'გოგუაძე') with

NOT IN

operator

SELECT *
FROM students
WHERE last_name NOT IN ('გელაშვილი', 'გოგუაძე')

Select all students which lastname contains 'უა' with

LIKE

operator

SELECT *
FROM students
WHERE last_name LIKE '%უა%'

Select all students which

lastname

ends with 'უა' using

LIKE

operator

SELECT *
FROM students
WHERE last_name LIKE '%უა'

Select all students which

lastname

does not contain 'უა' with

NOT LIKE

operator

SELECT *
FROM students
WHERE last_name NOT LIKE '%უა%'

Select all students where

lastname

starts with any two character followed with

უა

and we don't care what the other following symbols are (with

_

)

SELECT *
FROM students
WHERE last_name LIKE '__უა%'

Select all students where

lastname

is exactly 5 characters long and the second symbol is

უ

SELECT *
FROM students
WHERE last_name LIKE '_უ___'

Select all students where

lastname

is exactly 5 characters, starts with

a

and ends with

y

and has exacly any three letters between them

SELECT *
FROM students
WHERE last_name LIKE 'a___y'

Select all students where

lastname

ends with either

ძე

or

შვილი

SELECT *
FROM students
WHERE last_name LIKE '%ძე' OR last_name LIKE '%შვილი'

Select all students where

last_name

contains

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა'

Select all students where

last_name

starts with

გე

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP '^გე'

Select all students where

last_name

ends with

უა

with

REGEXP

operator

SELECT *
FROM students
WHERE last_name REGEXP 'უა$'

Select all students where

last_name

contains

უა

or

ია

SELECT *
FROM students
WHERE last_name REGEXP 'უა|ია'

Select all students where

last_name

contains

უა

or

ია

using square brackets

SELECT *
FROM students
WHERE last_name REGEXP '[იუ]ა'

Select all students where

last_name

ends with any one letter from the range

[ა-უ]

followed by

ა

using square brackets with range

SELECT *
FROM students
WHERE last_name REGEXP '[ა-უ]ა$'

Select all students where

last_name

contains

ვა

or ends with

ია

or starts with

ცი

SELECT *
FROM students
WHERE last_name REGEXP '^ცი|ია$|ვა'

Select all students which has no mail with

IS NULL

operator

SELECT *
FROM students
WHERE mail IS NULL 
    OR mail = ''

Select all students which has no mail using

IFNULL()

function

SELECT *
FROM students
WHERE IFNULL(mail, '') = ''

Select all students which has mail using

IS NOT NULL

operator

SELECT *
FROM students
WHERE mail IS NOT NULL
    AND mail != ''

Select all students which has mail using

NULLIF()

function

SELECT *
FROM students
WHERE NULLIF(mail, '') IS NOT NULL

Select notes with author names (using INNER JOIN)

SELECT students.first_name, notes.*
FROM `students` 
JOIN notes ON notes.student_id = students.id

Select notes with author names (from students using LEFT JOIN)

SELECT students.first_name, notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id

Select notes with author names (from students using RIGHT JOIN)

SELECT 
    students.first_name, 
    notes.*
FROM `notes` 
RIGHT JOIN students ON notes.student_id = students.id

Select notes with author names (from students using LEFT JOIN)

SELECT students.first_name, notes.*
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
WHERE notes.id > 0

Select notes with author names (from notes)

SELECT students.first_name, notes.*
FROM `notes` 
LEFT JOIN students ON notes.student_id = students.id
WHERE notes.id > 0

Select only students with notes (using INNER JOIN)

SELECT students.*, COUNT(students.id) notes_count 
FROM `students` 
JOIN notes ON notes.student_id = students.id
GROUP BY students.id
ORDER BY notes_count DESC

Selects only students with notes (using LEFT JOIN and HAVING)

SELECT students.*, COUNT(notes.id) notes_count 
FROM `students` 
LEFT JOIN notes ON notes.student_id = students.id
GROUP BY students.id
HAVING notes_count > 0
ORDER BY notes_count DESC
LIMIT 2

Select popular notes with its authors and likes counts

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

Select only liked notes

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

Select only liked notes (without JOIN keywords)

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

Select liked notes with likes counts and authors (using sub-query)

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