SQL Glossary

SELECT COUNT(*) AS employees, AVG(salary) AS avg_salary, MAX(salary) AS top_salary
FROM Employees;

Common mistake

Mixing an aggregate with an ordinary column and no GROUP BY. SELECT first_name, MAX(salary) FROM Employees asks for one row and twelve rows at the same time. PostgreSQL, SQL Server, Oracle and MySQL in its default mode reject it; SQLite, which runs this playground, fills first_name from a single row, and the playground flags it with a warning. Either aggregate everything, or add GROUP BY for the plain column.

SELECT e.first_name, e.salary * 12 AS annual_salary
FROM Employees e
ORDER BY annual_salary DESC;

Common mistake

Using a column alias inside WHERE. WHERE is evaluated before the SELECT list, so WHERE annual_salary > 1000000 is an error in PostgreSQL, MySQL, SQL Server and Oracle. SQLite, which runs this playground, accepts it as an extension, so a query that works here can fail elsewhere; repeat the expression, or filter in an outer query.

SELECT name, unit_price
FROM Products
WHERE unit_price BETWEEN 100 AND 500;

Common mistake

Forgetting that BETWEEN includes both ends. BETWEEN 100 AND 500 keeps a price of exactly 500; to stop short of it, write unit_price >= 100 AND unit_price < 500.

SELECT name, unit_price,
       CASE WHEN unit_price >= 500 THEN 'premium' ELSE 'standard' END AS band
FROM Products;

Common mistake

Leaving out ELSE. A row that matches no WHEN branch becomes NULL, silently, and then drops out of counts and comparisons.

SELECT e.first_name, COALESCE(m.first_name, 'no manager') AS reports_to
FROM Employees e
LEFT JOIN Employees m ON m.emp_id = e.manager_id;

Common mistake

Expecting COALESCE to replace an empty string. It only steps past NULL; '' is a value, so it is returned as it is.

SELECT first_name, hire_date
FROM Employees;

Common mistake

Quoting a column name like text. 'first_name' in single quotes is a string, not a column, so every row shows the word first_name. Write the name bare.

SELECT e.first_name, e.salary
FROM Employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM Employees x WHERE x.dept_id = e.dept_id);

Common mistake

Dropping the condition that ties the inner query to the outer row. Without x.dept_id = e.dept_id the subquery averages the whole company once, and every employee is compared to the same number.

SELECT d.dept_name, p.category
FROM Departments d
CROSS JOIN Products p;

Common mistake

Producing one by accident. A JOIN with no ON clause, or two tables after FROM with no WHERE linking them, multiplies the tables together; if a query returns far more rows than either table has, look for a missing join condition.

WITH big_orders AS (
  SELECT customer_id, order_total FROM Orders WHERE order_total > 3000
)
SELECT c.company, b.order_total
FROM big_orders b
JOIN Customers c ON c.customer_id = b.customer_id;

Common mistake

Putting a semicolon after the CTE's closing parenthesis. The WITH block and the query that uses it are one statement; a semicolon between them ends the statement before the CTE is used.

CREATE TABLE Suppliers (supplier_id INT PRIMARY KEY, name VARCHAR(100));  -- DDL
INSERT INTO Suppliers VALUES (1, 'Aurora Parts');                         -- DML
SELECT * FROM Suppliers;

Common mistake

Assuming DDL can be rolled back like DML. MySQL and Oracle commit CREATE, ALTER and DROP immediately, so there a DROP TABLE inside a transaction is still gone after ROLLBACK. PostgreSQL, SQL Server and SQLite can roll most DDL back.

DELETE FROM Orders WHERE status = 'Cancelled';
SELECT COUNT(*) AS remaining FROM Orders;

Common mistake

A DELETE with no WHERE. It removes every row in the table, asks nothing, and has no undo outside a transaction. Run a SELECT with the same WHERE first and count what comes back.

SELECT DISTINCT category FROM Products;

Common mistake

Expecting DISTINCT to apply to one column. It de-duplicates whole rows, so SELECT DISTINCT country, city returns every unique country/city pair, not each country once.

CREATE TABLE Reviews (
  review_id  INT PRIMARY KEY,
  product_id INT,
  stars      INT,
  FOREIGN KEY (product_id) REFERENCES Products (product_id)
);
INSERT INTO Reviews VALUES (1, 201, 5);
SELECT * FROM Reviews;

Common mistake

Declaring the column but not the constraint. A plain product_id column accepts 999 even though no such product exists; only the FOREIGN KEY line makes the database refuse it. Try inserting a review for product 999 in the playground and read the error. One SQLite caveat: it enforces foreign keys only after PRAGMA foreign_keys = ON has been run on the connection, and that setting is off by default. The playground switches it on for you.

SELECT e.first_name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON d.dept_id = e.dept_id;

Common mistake

Reaching for FULL OUTER JOIN when a LEFT JOIN answers the question. It is the right tool only when unmatched rows on both sides matter - and MySQL does not support it, so there it is written as a LEFT JOIN, UNION, a RIGHT JOIN.

SELECT dept_id, COUNT(*) AS headcount
FROM Employees
GROUP BY dept_id;

Common mistake

Selecting a column that is neither grouped nor aggregated. SELECT dept_id, first_name, COUNT(*) ... GROUP BY dept_id has no single first_name to show per group. PostgreSQL, SQL Server, Oracle and MySQL in its default mode reject it; SQLite, which runs this playground, accepts it and shows the first_name of an arbitrary row in each group; the playground flags that with a warning. Group by the column or aggregate it.

SELECT dept_id, COUNT(*) AS headcount
FROM Employees
GROUP BY dept_id
HAVING COUNT(*) >= 3;

Common mistake

Using HAVING for a row-level condition. HAVING salary > 50000 runs after grouping, when there is no single salary left to test. PostgreSQL and SQL Server reject it; SQLite, which runs this playground, runs it without complaint and tests the salary of one arbitrary row in each group, which is worse than an error. Conditions on individual rows belong in WHERE, before the groups are formed.

SELECT first_name
FROM Employees
WHERE emp_id IN (SELECT manager_id FROM Employees)
ORDER BY emp_id;

Common mistake

Flipping that to NOT IN to find the employees who manage nobody. The five people at the top have a NULL manager_id, so the list holds NULLs, and emp_id NOT IN (SELECT manager_id FROM Employees) returns no rows at all instead of seven. Use NOT EXISTS, or add WHERE manager_id IS NOT NULL inside the subquery.

CREATE INDEX idx_employees_dept ON Employees (dept_id);
SELECT first_name FROM Employees WHERE dept_id = 10;

Common mistake

Indexing everything. Each index speeds up the reads it matches and slows down every INSERT, UPDATE and DELETE that has to maintain it. Index the columns you filter and join on, not every column.

SELECT e.first_name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON d.dept_id = e.dept_id;

Common mistake

Forgetting that unmatched rows vanish. An employee with no department, or a department with no employees, is simply not in the result. If that is a surprise, you wanted a LEFT JOIN.

INSERT INTO Departments (dept_id, dept_name, location, budget)
VALUES (60, 'Legal', 'Pune', 300000);
SELECT * FROM Departments;

Common mistake

Omitting the column list. INSERT INTO t VALUES (...) depends on the table's column order and breaks the day a column is added; naming the columns keeps the statement correct.

SELECT first_name FROM Employees WHERE manager_id IS NULL;

Common mistake

Writing = NULL. Nothing equals NULL, not even NULL, so WHERE manager_id = NULL matches no rows at all. IS NULL and IS NOT NULL are the only tests that work.

SELECT o.order_id, c.company
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id;

Common mistake

Joining on a column that is not unique on either side, which multiplies rows. If the joined result has more rows than you expected, check which side is supposed to have one row per key.

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM Departments d
LEFT JOIN Employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

Common mistake

Filtering the right-hand table in WHERE. WHERE e.active = 1 throws away the NULL rows the LEFT JOIN kept, turning it back into an INNER JOIN; put that condition in the ON clause instead.

SELECT company FROM Customers WHERE company LIKE '%Systems%';

Common mistake

Forgetting the wildcards: LIKE 'Systems' with no % matches only that exact word. Case is the other surprise, because it depends on the database: PostgreSQL and Oracle compare case-sensitively, while MySQL and SQL Server with their default collations, and SQLite for ASCII letters, ignore case. So in this playground, which runs SQLite, LIKE '%systems%' still finds Baltic Systems, but = 'baltic systems' finds nothing.

SELECT first_name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 3;

Common mistake

LIMIT without ORDER BY. Without an order, "the first three rows" means whichever three the engine reached first, which can change between runs.

SELECT first_name, manager_id FROM Employees WHERE manager_id IS NULL;

Common mistake

Treating NULL as zero or as an empty string. salary + NULL is NULL, COUNT(manager_id) skips NULLs, and NULL = NULL is not true: every operation on an unknown value stays unknown.

SELECT first_name, salary
FROM Employees
ORDER BY salary DESC, first_name;

Common mistake

Assuming rows come back sorted without ORDER BY. They come back in whatever order was cheapest to produce, which may look sorted today and not tomorrow.

SELECT first_name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM Employees;

Common mistake

Confusing PARTITION BY with GROUP BY. GROUP BY collapses each group to one row; PARTITION BY keeps every row and adds the group's value alongside it.

CREATE TABLE Suppliers (
  supplier_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL
);
INSERT INTO Suppliers VALUES (1, 'Aurora Parts');
SELECT * FROM Suppliers;

Common mistake

Choosing a key that can change, such as an email address. When it changes, every row that referenced it must change too. An id that means nothing on its own is the safer choice.

SELECT first_name, last_name
FROM Employees
WHERE active = 1
ORDER BY last_name;

Common mistake

Writing the clauses out of order. SQL requires SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY in that sequence; a WHERE after ORDER BY is a syntax error, not a style choice.

SELECT e.first_name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON d.dept_id = e.dept_id;

Common mistake

Using RIGHT JOIN where swapping the tables and writing LEFT JOIN says the same thing more readably. Nearly every RIGHT JOIN in the wild is a LEFT JOIN written backwards.

SELECT first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_by_pay
FROM Employees
ORDER BY rank_by_pay;

Common mistake

Filtering on the number in the same query. Window functions are computed after WHERE, so WHERE rank_by_pay <= 3 is not allowed; wrap the query in a CTE or subquery and filter there.

CREATE TABLE Suppliers (
  supplier_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  country     VARCHAR(60)
);
SELECT * FROM Suppliers;

Common mistake

Guessing column names and types instead of reading the schema. Most "column does not exist" errors are a schema nobody looked at. This playground shows it beside the editor.

SELECT first_name, last_name, salary FROM Employees;

Common mistake

SELECT * in code that other code depends on. It returns whatever columns exist today, in today's order, so adding a column to the table can silently change what the query produces.

SELECT e.first_name AS employee, m.first_name AS manager
FROM Employees e
JOIN Employees m ON m.emp_id = e.manager_id;

Common mistake

Leaving out the aliases. Both copies of the table are called Employees, so without e and m every column reference is ambiguous and the query cannot run.

SELECT first_name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

Common mistake

Using = with a subquery that can return more than one row. PostgreSQL, MySQL, SQL Server and Oracle stop with an error when it does; SQLite, which runs this playground, quietly compares against the first row instead, so the mistake can go unnoticed here. If the inner query may produce several values, the outer comparison needs IN or EXISTS, not =.

SELECT * FROM Products;

Common mistake

Thinking of a table as having a natural row order. It does not; rows come back in an arbitrary order unless the query says ORDER BY.

BEGIN TRANSACTION;
UPDATE Products SET unit_price = unit_price * 1.1 WHERE category = 'Accessories';
-- look at the result, then either keep it ...
COMMIT;
-- ... or undo it with ROLLBACK;

Common mistake

Leaving a transaction open. Until COMMIT or ROLLBACK, the rows it touched can stay locked for everyone else - and a connection that drops mid-transaction rolls it back, taking the work with it.

-- MySQL, PostgreSQL, SQL Server and Oracle (SQLite has no TRUNCATE)
TRUNCATE TABLE Orders;

SQLite, which runs this playground, has no TRUNCATE statement. Its equivalent is a DELETE with no WHERE, which SQLite can carry out as one fast wipe of the table rather than row by row:

DELETE FROM Orders;
SELECT COUNT(*) AS remaining FROM Orders;

Common mistake

Expecting TRUNCATE to take a WHERE. It cannot; it empties the whole table in one step. To remove some rows, DELETE with a WHERE is the only option.

SELECT city AS place FROM Customers
UNION
SELECT location FROM Departments;

Common mistake

Reaching for UNION by habit. UNION has to find and remove duplicates, which costs time; when the two sides cannot overlap, or duplicates are wanted, UNION ALL is both faster and correct.

UPDATE Products
SET unit_price = unit_price * 1.1
WHERE category = 'Accessories';
SELECT name, unit_price FROM Products WHERE category = 'Accessories';

Common mistake

An UPDATE with no WHERE. Every row in the table changes, immediately. Run a SELECT with the intended WHERE first and count what comes back.

CREATE VIEW active_staff AS
  SELECT first_name, last_name, dept_id FROM Employees WHERE active = 1;
SELECT * FROM active_staff;

Common mistake

Expecting a view to make a query faster. An ordinary view stores nothing - it re-runs its SELECT every time it is used - so it organises code, not performance.

SELECT first_name, salary
FROM Employees
WHERE dept_id = 10 AND salary > 90000;

Common mistake

Mixing AND and OR without parentheses. AND binds tighter, so a OR b AND c means a OR (b AND c); write the parentheses you mean.

SELECT first_name FROM Employees WHERE first_name LIKE '_ri%';

Common mistake

Using * as a wildcard in LIKE. In SQL the wildcards are % (any run of characters) and _ (exactly one character); LIKE 'A*' looks for a literal asterisk.

SELECT first_name, salary,
       SUM(salary) OVER () AS total_payroll
FROM Employees;

Common mistake

Expecting a window function to reduce the number of rows. It never does; every input row comes back, with the window's value added as a column.