1. Can I use a column alias in the WHERE clause?

No. The database runs FROM and WHERE before it evaluates SELECT, so the alias does not exist yet when WHERE runs. Repeat the expression in WHERE, or wrap the query in a subquery or CTE and filter outside it. ORDER BY runs after SELECT, so it can use the alias. Key terms used in this answer - Alias: A temporary name given to a column or a table in a query. It makes the output or the query easier to read, and it exists only while that query runs. The real name in the database does not change. - Clause: One part of a SQL statement that does a specific job. A keyword such as SELECT, FROM, or WHERE, together with what follows it, forms a clause.

2. What does SELECT first_name last_name FROM users return?

One column, not two. The comma is missing, so the database reads lastname as an alias for firstname. The result shows only first names, with the column header lastname. Nothing is reported as an error because AS is optional for column aliases. Always write AS to make aliases obvious and to catch this mistake early.

3. Does DISTINCT apply to only the first column, or to all selected columns?

All selected columns together. DISTINCT removes rows where the whole combination of values is duplicated, not just the first column. Writing DISTINCT(department), title does not change this: the parentheses are only grouping, and DISTINCT is not a function. To get one row per department, use DISTINCT ON (department) in PostgreSQL or GROUP BY department in any database.

4. How do NULLs behave with DISTINCT and COUNT?

DISTINCT treats all NULLs as equal, so many NULLs collapse into one row. The COUNT variants behave differently: - COUNT() counts all rows, including rows with NULL. - COUNT(col) counts only rows where col is not NULL. - COUNT(DISTINCT col) counts unique non-NULL values only. For a column with values A, A, B, NULL, NULL, these return 5, 3, and 2.

5. Why is SELECT * discouraged in production code?

There are four main reasons: - It returns columns you do not need, which wastes network and memory. - It can stop the database from using a covering index, so it reads the full row from the table instead. - Code that relies on column order or count breaks when a column is added, removed, or reordered. - In a join, columns with the same name (like id) appear twice, which can silently overwrite values in your application. SELECT is fine for quick exploration, and inside EXISTS (SELECT ...) where the selected columns are never read.

Query Basics

SQL Cheat Sheet

A quick reference of SQL commands grouped by category. Each row shows what the command does and its syntax.

1. Data Query (Reading Data)

Command	What it does	Syntax
`SELECT`	Gets columns from a table	`SELECT col1, col2 FROM table;`
`SELECT *`	Gets all columns	`SELECT * FROM table;`
`SELECT DISTINCT`	Gets only unique values	`SELECT DISTINCT col FROM table;`
`AS` (alias)	Gives a temporary name	`SELECT col AS new_name FROM table AS t;`
`FROM`	Says which table to read	`SELECT col FROM table;`

2. Filtering (Choosing Rows)

Command	What it does	Syntax
`WHERE`	Filters rows by a condition	`SELECT * FROM table WHERE condition;`
`AND`	All conditions must be true	`WHERE cond1 AND cond2;`
`OR`	Any one condition can be true	`WHERE cond1 OR cond2;`
`NOT`	Reverses a condition	`WHERE NOT condition;`
`IN`	Matches any value in a list	`WHERE col IN (val1, val2);`
`NOT IN`	Matches none of the values	`WHERE col NOT IN (val1, val2);`
`BETWEEN`	Matches a range (inclusive)	`WHERE col BETWEEN val1 AND val2;`
`LIKE`	Searches for a text pattern	`WHERE col LIKE 'pattern';`
`ILIKE`	Case-insensitive LIKE (PostgreSQL)	`WHERE col ILIKE 'pattern';`
`IS NULL`	Finds empty values	`WHERE col IS NULL;`
`IS NOT NULL`	Finds non-empty values	`WHERE col IS NOT NULL;`
`EXISTS`	True if a subquery returns rows	`WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);`

3. Comparison Operators

Operator	Meaning	Syntax
`=`	Equal to	`WHERE col = 10;`
`<>` or `!=`	Not equal to	`WHERE col <> 10;`
`>`	Greater than	`WHERE col > 10;`
`<`	Less than	`WHERE col < 10;`
`>=`	Greater than or equal	`WHERE col >= 10;`
`<=`	Less than or equal	`WHERE col <= 10;`

LIKE wildcards

Wildcard	Meaning	Syntax
`%`	Zero or more characters	`WHERE name LIKE 'A%';`
`_`	Exactly one character	`WHERE name LIKE '_a%';`

4. Sorting and Limiting

Command	What it does	Syntax
`ORDER BY`	Sorts the result	`SELECT * FROM table ORDER BY col;`
`ASC`	Sorts small to large (default)	`ORDER BY col ASC;`
`DESC`	Sorts large to small	`ORDER BY col DESC;`
`NULLS FIRST`	Puts NULLs at the top	`ORDER BY col NULLS FIRST;`
`NULLS LAST`	Puts NULLs at the bottom	`ORDER BY col NULLS LAST;`
`LIMIT`	Limits number of rows	`SELECT * FROM table LIMIT 10;`
`OFFSET`	Skips rows before returning	`SELECT * FROM table LIMIT 10 OFFSET 20;`
`TOP`	Limits rows (SQL Server)	`SELECT TOP 10 * FROM table;`
`FETCH FIRST`	Limits rows (Oracle / standard)	`SELECT * FROM table FETCH FIRST 10 ROWS ONLY;`

5. Aggregate Functions

Aggregate functions work on many rows and return one value. They ignore NULL values (except COUNT(*)).

Function	What it does	Syntax
`COUNT(*)`	Counts all rows	`SELECT COUNT(*) FROM table;`
`COUNT(col)`	Counts non-NULL values	`SELECT COUNT(col) FROM table;`
`COUNT(DISTINCT)`	Counts unique values	`SELECT COUNT(DISTINCT col) FROM table;`
`SUM()`	Adds up values	`SELECT SUM(col) FROM table;`
`AVG()`	Finds the average	`SELECT AVG(col) FROM table;`
`MIN()`	Finds the smallest value	`SELECT MIN(col) FROM table;`
`MAX()`	Finds the largest value	`SELECT MAX(col) FROM table;`

6. Grouping

Command	What it does	Syntax
`GROUP BY`	Groups rows with the same value	`SELECT col, COUNT(*) FROM table GROUP BY col;`
`HAVING`	Filters groups (after GROUP BY)	`SELECT col, COUNT() FROM table GROUP BY col HAVING COUNT() > 1;`

WHERE vs HAVING: WHERE filters rows before grouping. HAVING filters groups after grouping.

7. Data Modification (DML)

Command	What it does	Syntax
`INSERT INTO`	Adds a new row	`INSERT INTO table (col1, col2) VALUES (val1, val2);`
`INSERT INTO` (many)	Adds many rows at once	`INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4);`
`INSERT INTO ... SELECT`	Copies rows from another table	`INSERT INTO table1 (col) SELECT col FROM table2;`
`UPDATE`	Changes existing rows	`UPDATE table SET col = value WHERE condition;`
`DELETE`	Removes rows	`DELETE FROM table WHERE condition;`

Warning: Always use WHERE with UPDATE and DELETE. Without it, all rows are changed or deleted.

8. Table Structure (DDL)

Command	What it does	Syntax
`CREATE DATABASE`	Creates a database	`CREATE DATABASE db_name;`
`CREATE TABLE`	Creates a table	`CREATE TABLE table (col1 datatype, col2 datatype);`
`ALTER TABLE ADD`	Adds a column	`ALTER TABLE table ADD col datatype;`
`ALTER TABLE DROP COLUMN`	Removes a column	`ALTER TABLE table DROP COLUMN col;`
`ALTER TABLE RENAME`	Renames a table (PostgreSQL / MySQL)	`ALTER TABLE old_name RENAME TO new_name;`
`DROP TABLE`	Deletes a table completely	`DROP TABLE table;`
`TRUNCATE TABLE`	Deletes all rows, keeps the table	`TRUNCATE TABLE table;`
`CREATE INDEX`	Speeds up searches on a column	`CREATE INDEX index_name ON table (col);`
`CREATE VIEW`	Saves a query as a virtual table	`CREATE VIEW view_name AS SELECT col FROM table;`

9. Constraints

Constraint	What it does	Syntax
`PRIMARY KEY`	Unique ID for each row (not NULL)	`id INT PRIMARY KEY`
`FOREIGN KEY`	Links to another table	`FOREIGN KEY (col) REFERENCES other_table(col)`
`UNIQUE`	No duplicate values allowed	`col datatype UNIQUE`
`NOT NULL`	Value cannot be empty	`col datatype NOT NULL`
`CHECK`	Value must meet a condition	`col INT CHECK (col > 0)`
`DEFAULT`	Sets a value when none is given	`col datatype DEFAULT value`
`AUTO_INCREMENT`	Auto-increasing number (MySQL)	`id INT AUTO_INCREMENT`

10. Joins (Combining Tables)

Command	What it does	Syntax
`INNER JOIN`	Rows that match in both tables	`SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;`
`LEFT JOIN`	All rows from left table + matches from right	`SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;`
`RIGHT JOIN`	All rows from right table + matches from left	`SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;`
`FULL JOIN`	All rows from both tables	`SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id;`
`CROSS JOIN`	Every row combined with every row	`SELECT * FROM t1 CROSS JOIN t2;`
`SELF JOIN`	Joins a table with itself	`SELECT * FROM table a JOIN table b ON a.col = b.col;`

11. Set Operations (Combining Results)

Command	What it does	Syntax
`UNION`	Combines results, removes duplicates	`SELECT col FROM t1 UNION SELECT col FROM t2;`
`UNION ALL`	Combines results, keeps duplicates	`SELECT col FROM t1 UNION ALL SELECT col FROM t2;`
`INTERSECT`	Only rows found in both results	`SELECT col FROM t1 INTERSECT SELECT col FROM t2;`
`EXCEPT`	Rows in first result but not in second	`SELECT col FROM t1 EXCEPT SELECT col FROM t2;`

Note: Oracle uses MINUS instead of EXCEPT.

12. Subqueries

Type	What it does	Syntax
Subquery in WHERE	Uses one query's result in another	`SELECT * FROM t1 WHERE col IN (SELECT col FROM t2);`
Subquery in FROM	Uses a query as a temporary table	`SELECT * FROM (SELECT col FROM t1) AS sub;`
Subquery in SELECT	Returns a single value per row	`SELECT col, (SELECT MAX(col) FROM t2) FROM t1;`
`WITH` (CTE)	Names a temporary result to reuse	`WITH name AS (SELECT * FROM t1) SELECT * FROM name;`

13. Conditional Logic and NULL Handling

Command	What it does	Syntax
`CASE`	If-then-else inside a query	`CASE WHEN cond THEN val1 ELSE val2 END`
`COALESCE()`	Returns the first non-NULL value	`COALESCE(col, 'default')`
`NULLIF()`	Returns NULL if two values are equal	`NULLIF(col1, col2)`
`IFNULL()`	Replaces NULL with a value (MySQL)	`IFNULL(col, 'default')`

14. String Functions

Function	What it does	Syntax
`UPPER()`	Converts to uppercase	`UPPER(col)`
`LOWER()`	Converts to lowercase	`LOWER(col)`
`LENGTH()`	Returns text length	`LENGTH(col)`
`TRIM()`	Removes spaces from both ends	`TRIM(col)`
`SUBSTRING()`	Extracts part of a text	`SUBSTRING(col, start, length)`
`CONCAT()`	Joins texts together	`CONCAT(col1, ' ', col2)`
`REPLACE()`	Replaces text with other text	`REPLACE(col, 'old', 'new')`

15. Numeric Functions

Function	What it does	Syntax
`ROUND()`	Rounds a number	`ROUND(col, 2)`
`CEIL()`	Rounds up	`CEIL(col)`
`FLOOR()`	Rounds down	`FLOOR(col)`
`ABS()`	Returns the absolute value	`ABS(col)`
`MOD()`	Returns the remainder	`MOD(col, 3)`
`POWER()`	Raises to a power	`POWER(col, 2)`

16. Date Functions

Function	What it does	Syntax
`CURRENT_DATE`	Returns today's date	`SELECT CURRENT_DATE;`
`NOW()`	Returns current date and time	`SELECT NOW();`
`EXTRACT()`	Gets a part of a date	`EXTRACT(YEAR FROM col)`
`DATE_PART()`	Gets a part of a date (PostgreSQL)	`DATE_PART('month', col)`
`DATEDIFF()`	Difference between two dates (MySQL)	`DATEDIFF(date1, date2)`

17. Window Functions

Function	What it does	Syntax
`ROW_NUMBER()`	Gives a unique number to each row	`ROW_NUMBER() OVER (ORDER BY col)`
`RANK()`	Ranks rows, leaves gaps for ties	`RANK() OVER (ORDER BY col DESC)`
`DENSE_RANK()`	Ranks rows, no gaps for ties	`DENSE_RANK() OVER (ORDER BY col DESC)`
`PARTITION BY`	Restarts the calculation per group	`ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2)`
`LAG()`	Gets value from the previous row	`LAG(col) OVER (ORDER BY col2)`
`LEAD()`	Gets value from the next row	`LEAD(col) OVER (ORDER BY col2)`

18. Transactions (Data Safety)

Command	What it does	Syntax
`BEGIN`	Starts a transaction	`BEGIN;`
`COMMIT`	Saves all changes	`COMMIT;`
`ROLLBACK`	Cancels all changes	`ROLLBACK;`
`SAVEPOINT`	Sets a point to roll back to	`SAVEPOINT name;`

19. Permissions (DCL)

Command	What it does	Syntax
`GRANT`	Gives permission to a user	`GRANT SELECT ON table TO user;`
`REVOKE`	Takes back a permission	`REVOKE SELECT ON table FROM user;`

20. Command Categories at a Glance

Category	Full Name	Commands
DQL	Data Query Language	`SELECT`
DML	Data Manipulation Language	`INSERT`, `UPDATE`, `DELETE`
DDL	Data Definition Language	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`
DCL	Data Control Language	`GRANT`, `REVOKE`
TCL	Transaction Control	`BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`

21. Order of Writing a Query

sql

1SELECT   columns
2FROM     table
3JOIN     other_table ON condition
4WHERE    row_condition
5GROUP BY columns
6HAVING   group_condition
7ORDER BY columns
8LIMIT    number;

Order the database runs it: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Next TopicAggregation