Variables — SQL Technical Interview Questions & Answers

Variables

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.

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

Code Examples for Variables

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.

How to Explain Variables to an Interviewer

What is @marks in MySQL?

A user/session variable SET @marks = 40;

Where is DECLARE used?

Inside stored programs Not in a normal SELECT tab