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'

Updates students table based on notes table column -

notes.id

using

explicit JOIN

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

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
  )

We can get the same result using explicit join

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

We can clone a table using

CREATE & SELECT

statement

CREATE TABLE students_archived AS
SELECT * FROM students

We can

SELECT

from one table and

INSERT

the selected data to another table using

INSERT & SELECT

statement

INSERT INTO `students` (`first_name`, `last_name`, `points`) 
SELECT first_name, last_name, points * 1.2 FROM students WHERE id = 3

The only thing we must consider is column positions (position must be the same)

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

SELECT *
FROM students
WHERE points BETWEEN 80 AND 90

We can get the same result by using

AND

logical operator

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

Updates all rows. Increases every student's points by one

UPDATE `students` SET `points` = points+1;

Updates several columns in one row using

private key

UPDATE `students` SET `first_name` = 'თენგიზ', `last_name` = 'ბოჭორიშვილი', mail = 'tengiz@gmail.com' WHERE `students`.`id` = 38;

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'

Updates students table based on notes table column -

notes.id

using

explicit JOIN

UPDATE notes
INNER JOIN students ON notes.student_id = students.id
SET students.points = students.points + 1
WHERE notes.id = 1

Updates students table based on notes table column -

notes.id

using sub-query

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

Alternative of the above three updates:

SELECT student_id FROM `notes` WHERE id = 5;
UPDATE students SET points = points + 1 WHERE id = 3;

Categorize students based on their points using

CASE WHEN

conditional statement

SELECT *, 
    CASE 
      	WHEN points>90 THEN "Brilliant"
      	WHEN points>80 THEN "Gold"
      	WHEN points>60 THEN "Silver"
      	ELSE "Lazy"
    END as 'class'
FROM `students`
ORDER BY points DESC

Conditionally checking columns Using

CASE

SELECT * 
FROM `students` 
WHERE 
    (CASE
        WHEN LENGTH(mail) THEN mail
        ELSE mail2
    END) LIKE '%gmail.com%'
ORDER BY points

CASE

in

ORDER BY

clause (if the current day of month is odd, orders by

last_name

, otherwise orders by

first_name

SELECT *
FROM students
ORDER BY (
    CASE DAY(CURDATE())%2
      	WHEN 0 THEN first_name
      	WHEN 1 THEN last_name
    END
) DESC

Creates

index

for

guid_field

column

CREATE INDEX guid_field ON students123 (guid_field);

Removes

guid_field

column

index

ALTER TABLE `students123` DROP INDEX `guid_field`;