SQL · Theory

Variables

Theory

5/439

Variables

MySQL variables are named boxes that hold a value for this session. User variables start with @. SET @pass = 40; then SELECT name FROM students WHERE marks >= @pass; That is useful in the client when you repeat the same cutoff.

DECLARE is for stored programs (procedures, functions, triggers). You cannot DECLARE @x in a normal query tab the way you do in a procedure. Session vs procedure is the viva line.

System variables (@@sql_mode, @@autocommit) change server behaviour. Don’t SET GLOBAL on a shared lab PC unless the teacher said so.

Trap: thinking MySQL variables are like Java local variables in every SELECT. They are not. Most student queries need no variables at all — just WHERE marks < 40.

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

Exam tip

Show SET @pass = 40 and a SELECT that uses it. Say DECLARE is for procedures.

Variables — sample query

-- Variables
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;

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

Short notes

Def@name is a session variable. SET @pass = 40;
RuleDECLARE only inside procedures. @@ is a system variable.
TrapUsing variables when a simple WHERE is enough.

SQL · Theory

Variables

← All stacks

Theory

5/439

Variables

DECLARE is for stored programs (procedures, functions, triggers). You cannot DECLARE @x in a normal query tab the way you do in a procedure. Session vs procedure is the viva line.

System variables (@@sql_mode, @@autocommit) change server behaviour. Don’t SET GLOBAL on a shared lab PC unless the teacher said so.

Trap: thinking MySQL variables are like Java local variables in every SELECT. They are not. Most student queries need no variables at all — just WHERE marks < 40.

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

Exam tip

Show SET @pass = 40 and a SELECT that uses it. Say DECLARE is for procedures.

Variables — sample query

-- Variables
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;

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

Short notes

Def@name is a session variable. SET @pass = 40;
RuleDECLARE only inside procedures. @@ is a system variable.
TrapUsing variables when a simple WHERE is enough.

Variables

Variables — sample query

Short notes

Questions

Variables

Variables — sample query

Short notes

Questions