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
  )