The SQL language works with sets. This is important to understand when working with multiple tables and nested queries. Usually these sets can be drawn as circles to understand what we need to get (to shade the required intersection areas). Tables can also be filtered and sorted.

SQL in detail is quite different in different databases. Things like getting the year from the date are usually very specific to each database and even column type.

Indexes are needed for fast queries. They speed up data extraction, but slow down recording and increase the size of the database, so a balance is needed.

Pseudocode of tables for example queries:

users: id, name, age
teams: id, name, create_at
users_in_groups: user_id, group_id

To withdraw all user groups with more than 5 adult users, by reducing the number of adult users:

SELECT
  t.name as team_name,
  COUNT(u.id) as amount
FROM
  teams t,
  users u,
  users_in_teams u2t
WHERE
  u2t.user_id = u.id
  AND u2t.team_id = t.id
  AND u.age > 18
GROUP BY t.name
HAVING COUNT(u.id) > 5
ORDER BY COUNT(u.id) DESC

Output groups in alphabetical order with the number of users (output even 0):

SELECT
  t.name as team_name,
  COUNT(u.id) as amount
FROM
  teams t
LEFT JOIN users_in_teams u2t ON u2t.team_id = t.id
LEFT JOIN users u ON u2t.user_id = u.id
GROUP BY t.name
ORDER BY t.name ASC

Output groups in alphabetical order with the number of adult users (output even 0):

SELECT
  t.name as team_name,
  IFNULL(team_with_amount.amount, 0) as amount
FROM teams t
LEFT JOIN (SELECT
    t2.id as team_id,
    t2.name as team_name,
    COUNT(u.id) as amount
  FROM
    teams t2,
    users u,
    users_in_teams u2t
  WHERE
    u2t.user_id = u.id
    AND u2t.team_id = t2.id
    AND u.age > 18
  GROUP BY t2.id, t2.name) as team_with_amount
ON t.id = team_with_amount.team_id
ORDER BY t.name ASC

If you can read and write such SQL queries, then usually this level of understanding is enough. If it’s not clear right now, then it’s better to learn a little bit about it – in interviews, they almost always ask something like that. And in practice it is necessary.

ACID properties of transactions:

  • Atomicity - applied or rolled back
  • Consistency - the transaction does not violate the consistency of the data
  • Isolation - as if the transactions were sequential
  • Durability - if applicable, it is impossible to lose data

Well, the levels of insulation:

  • Read uncommitted (reading uncommitted data) – everything is bad, but without locks
  • Read committed (read fixed data) – by default – row changes may appear inside a transaction if another transaction has ended
  • Repeatable read – but new lines may appear inside a transaction from other transactions
  • Serializable (orderability) – everything is fine, but slow