Let's start simple but do it properly. Here's our table:
employees
+----+----------+------------+--------+
| id | name | department | salary |
+----+----------+------------+--------+
| 1 | Alice | Engineering| 95000 |
| 2 | Bob | Sales | 62000 |
| 3 | Charlie | Engineering| 105000 |
| 4 | Diana | Marketing | 70000 |
+----+----------+------------+--------+
Question: Find all employees in Engineering earning more than 100,000.
sql
SELECT name, salary
FROM employees
WHERE department = 'Engineering'
AND salary > 100000;
Simple, right? But here's the interview trap:
⚠️ Common pitfall: People forget
WHERE runs before SELECT logically, so you can't reference a column alias defined in SELECT inside your WHERE clause.sql
-- This will ERROR in most databases:
SELECT salary * 1.1 AS new_salary
FROM employees
WHERE new_salary > 100000; -- ❌ new_salary doesn't exist yet here
Fix it by repeating the expression or using a subquery/CTE. We'll get into CTEs in a few weeks 👀
What's a SQL mistake that tripped you up early on? 😅