SQL · Theory

Versions

Theory

3/439

Versions

MySQL versions are like class years. 5.7 is the older one still in many companies. 8.0 is the current main line (window functions, better JSON, caching_sha2_password). 8.4 is a newer LTS. You do not need to mug every point release.

What changes for a fresher: syntax of SELECT is the same. New things in 8: WITH (CTE), ROW_NUMBER(), better JSON functions. If a tutorial uses ONLY_FULL_GROUP_BY errors, that is 5.7/8 being strict — fix the GROUP BY, don’t switch off the mode blindly.

Check your version: SELECT VERSION(); or mysql --version. In interviews, ‘I practised on 8.0’ is enough. Don’t invent version numbers.

Trap — mixing MariaDB version numbers with MySQL. MariaDB started from MySQL but they are not the same product now.

On the example next to this theory: Versions: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

Say 5.7 vs 8.0. Check with SELECT VERSION(). Mention CTE in 8 if asked.

Versions — sample query

-- Versions
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Versions: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

Def5.7 old, 8.0 current line. SELECT VERSION() to check.
RuleSELECT is the same. 8 adds CTE / window functions.
TrapMixing MariaDB and MySQL version numbers.

SQL · Theory

Versions

← All stacks

Theory

3/439

Versions

Check your version: SELECT VERSION(); or mysql --version. In interviews, ‘I practised on 8.0’ is enough. Don’t invent version numbers.

Trap — mixing MariaDB version numbers with MySQL. MariaDB started from MySQL but they are not the same product now.

On the example next to this theory: Versions: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

Say 5.7 vs 8.0. Check with SELECT VERSION(). Mention CTE in 8 if asked.

Versions — sample query

-- Versions
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Versions: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

Def5.7 old, 8.0 current line. SELECT VERSION() to check.
RuleSELECT is the same. 8 adds CTE / window functions.
TrapMixing MariaDB and MySQL version numbers.

Versions

Versions — sample query

Short notes

Questions

Versions

Versions — sample query

Short notes

Questions