Find the Error
“The following query has an error. Identify it and rewrite the correct query.” Two marks, and they are among the easiest on the paper once you have seen each mistake once. Every message below is what MySQL actually printed.
1Start with the one that gives no error
The most dangerous mistake in SQL is not the one that fails loudly. It is this one, which runs perfectly and answers nothing:
select name from emp where bonus = null; to list employees with no bonus, and gets nothing back although three such employees exist. Why?▶Show the answer
No error, no rows. Nothing is ever equal to NULL — not even another NULL — so the condition is never true. The fix is the dedicated operator:
2Text without quotes
select name from emp where city = Gangtok;▶Show the answer
Read the message closely: MySQL is not confused about the value, it is looking for a column named Gangtok. Without quotes, a bare word is a column name. The fix is where city = 'Gangtok'. Numbers, by contrast, never take quotes.
select ename from emp;▶Show the answer
The same error number as question 2, but in 'field list' rather than in 'where clause' — the message tells you which part of the query to look at. The column is name, not ename.
3Clauses in the wrong order
select name from emp order by salary where salary > 30000;▶Show the answer
ERROR 1064 is the general syntax error, and the useful part is near 'where salary > 30000' — it names the first thing it could not accept. order by must be the last clause, so the correct query is select name from emp where salary > 30000 order by salary;
select name from emp where salary between 25000 or 50000;▶Show the answer
between is always followed by and — it is one fixed phrase, and or cannot stand in for it. The fix is where salary between 25000 and 50000.
4Aggregates in the wrong clause
select deptno, count(*) from emp where count(*) > 2 group by deptno;▶Show the answer
where is applied before any grouping happens, so at that moment no group exists to count. A condition on an aggregate belongs in having: select deptno, count(*) from emp group by deptno having count(*) > 2;
select deptno, name, count(*) from emp group by deptno;▶Show the answer
A long message for a simple problem. Department 10 has four employees, so there are four possible names and the query has not said which one it wants. Anything in the select list must either be grouped by or wrapped in an aggregate. This is also why select name, max(salary) from emp; cannot give you the name of the highest earner.
select job, count(*) from emp having count(*) > 2;▶Show the answer
having without group by treats the whole table as one single group, and then job has no single value. Add the missing clause: select job, count(*) from emp group by job having count(*) > 2;
5Something simply missing
select name from;▶Show the answer
Note near '' — two empty quotes. MySQL ran out of query while still expecting a table name. When the “near” part is empty, something is missing from the end rather than wrong in the middle.
6Reading an error message
near. 1054 — unknown column: a name is wrong, or a text value has lost its quotes. 1111 — an aggregate somewhere it is not allowed. 1055 and 1140 — a group by problem: a column in the select list is neither grouped nor aggregated.In the exam you are not asked for the error number, only for the mistake and the corrected query. But the number is how you find the mistake quickly when you are at a real prompt.
Why does 'where bonus = null' produce no error and no rows?
Which clause should hold the condition count(*) > 2?
'ERROR 1054: Unknown column 'Gangtok' in 'where clause'' most likely means what?