1. SQL Basics & Filtering
SELECT **
SELECT — SELECT name FROM users; → picks which columns to return.
**Example:
SELECT * FROM users;
→ returns all columns.
*DISTINCT *
SELECT DISTINCT city FROM users; → removes duplicate rows from results.
*FROM *
SELECT * FROM users;
→ names the table to query.
WHERE
SELECT * FROM users WHERE age > 18;
→ filters rows before grouping/output.
EXAMPLE:
*AND *
WHERE age > 18 AND city='Chennai';
→ all conditions must be true.
OR —
WHERE city='Chennai' OR city='Trichy';
→ at least one condition must be true.
*NOT *
WHERE NOT city='Chennai';
→ negates a condition.
*IN *
WHERE city IN ('Chennai','Trichy');
→ matches any value in a list.
*NOT IN *
WHERE city NOT IN ('Chennai');
→ excludes values in a list.
BETWEEN
WHERE age BETWEEN 18 AND 25;
→ inclusive range check.
LIKE **
WHERE name LIKE 'A%';
→ pattern match (%=any chars, _=one char).
*Like *
**case-insensitive
LIKE (Postgres only;
MySQL's LIKE is case-insensitive by default on most collations).
IS NULL
WHERE phone IS NULL;
→ checks for missing values.
*IS NOT NULL *
WHERE phone IS NOT NULL;
→ checks value exists.
ORDER BY **
ORDER BY age DESC;
→ sorts result rows.
**ASC
ascending sort order (default).
DESC **
descending sort order.
**LIMIT
LIMIT 10;
→ caps number of rows returned.
*OFFSET *
LIMIT 10 OFFSET 20;
→ skips rows before returning (pagination).
*AS *
SELECT name AS full_name;
→ renames a column/table (alias).
CASE WHEN
CASE WHEN age<18 THEN 'Minor' ELSE 'Adult' END → conditional logic in a query.
COALESCE()
*COALESCE(phone,'N/A') *
→ returns first non-null value from a list.
*NULLIF() *
NULLIF(a,b)
→ returns NULL if a=b, else returns a.
*CAST() *
CAST(salary AS DECIMAL(10,2))
→converts data type.
*ROUND() *
ROUND(3.14159,2)
→ rounds a number to given decimals.
UPPER()
converts text to uppercase.
*LOWER() *
converts text to lowercase.
*TRIM() *
removes leading/trailing spaces.
**CONCAT()
CONCAT(first,' ',last)
→ joins strings together.
2*. Aggregation & Functions*
*COUNT() *
COUNT(*)
counts rows; COUNT(col) counts non-null values.
COUNT() *
counts total rows including NULLs.
*COUNT(DISTINCT) *
counts unique non-null values.
*SUM() *
total of a numeric column.
**AVG()
average of a numeric column.
*MIN() *
smallest value.
*MAX() *
largest value.
*GROUP BY *
GROUP BY department
→ groups rows to apply aggregates per group.
*HAVING *
HAVING COUNT()>5
→ filters groups after aggregation (WHERE can't do this).
**GROUPING SETS *
lets you compute multiple GROUP BY combinations in one query.
ROLLUP
adds subtotal + grand total rows to grouped results.
CUBE
like ROLLUP but generates subtotals for every combination of grouped columns.
*DATE() * extracts the date part from a datetime value.
*EXTRACT() *
EXTRACT(YEAR FROM order_date)
→ pulls a specific part (year/month/day) from a date.
DATE_TRUNC()
rounds a timestamp down to a unit (day, month, year)
Postgres; MySQL uses DATE_FORMAT.
*CURRENT_DATE *
returns today's date.
*CURRENT_TIMESTAMP *
returns current date and time.
*INTERVAL *
date + INTERVAL 7 DAY
→ adds/subtracts a time span.
ABS()
absolute (non-negative) value.
CEIL() **
rounds a number up.
**FLOOR()
rounds a number down.
POWER()
POWER(2,3)
→ 2³ = 8.
*MOD() *
remainder of division.
*LENGTH() *
number of characters in a string.
*SUBSTRING() * SUBSTRING(name,1,3)
→ extracts part of a string.
*REPLACE() * REPLACE(str,'a','b')
→ replaces substring occurrences.
*POSITION() *
finds the index of a substring within a string.
STRING_AGG()
concatenates values from multiple rows into one string with a separator
(Postgres; MySQL equivalent is GROUP_CONCAT).
GREATEST() **
returns the largest of a list of values.
*LEAST() *
returns the smallest of a list of values.
**3. Joins & Subqueries
INNER JOIN
returns only rows matching in both tables.
LEFT JOIN
all rows from left table, matched rows from right (NULL if no match).
RIGHT JOIN
all rows from right table, matched rows from left.
FULL OUTER JOIN
all rows from both tables, matched where possible (not supported directly in MySQL — simulate with UNION of LEFT and RIGHT joins).
CROSS JOIN
every row of table A paired with every row of table B (Cartesian product).
SELF JOIN **
a table joined with itself
(e.g., comparing employees to their managers in the same table).
**JOIN ... ON
JOIN table2 ON t1.id=t2.id
→ join condition
using arbitrary columns.
JOIN ... USING
JOIN table 2 *USING(id)
→ **shorthand join when column names match exactly.
**UNION *
combines results of two queries, removing duplicates.
UNION ALL
combines results of two queries, keeping duplicates (faster).
INTERSECT
returns rows common to both queries.
EXCEPT
returns rows in the first query not present in the second
(MySQL: use NOT IN/NOT EXISTS instead).
EXISTS
WHERE EXISTS (subquery)
→ true if subquery returns any row.
NOT EXISTS
true if subquery returns no rows.
*ANY — *
compares a value to any result of a subquery
(e.g., > ANY(...)).
*ALL *
compares a value to all results of a subquery
(e.g., > ALL(...)).
IN (Subquery)
WHERE id IN (SELECT ...)
→ filters using a subquery result set.
Scalar Subquery
a subquery that returns exactly one value, used like a single value.
*Correlated Subquery *
a subquery that references the outer query's columns, run once per outer row.
*Nested Subquery *
a subquery inside another subquery.
WITH
starts a Common Table Expression (CTE), a named temporary result set.
CTE
WITH temp AS (SELECT ...)
SELECT * FROM temp;
→ improves readability of complex queries.
*Recursive CTE *
a CTE that references itself, used for hierarchical/tree data
(e.g., org charts).
CREATE VIEW
saves a query as a virtual, reusable table.
CREATE TABLE AS
creates a new table populated from a query's results.
INSERT INTO
adds new rows to a table.
UPDATE
modifies existing rows.
DELETE **
removes rows from a table.
*MERGE *— combines **INSERT/UPDATE/DELETE
logic based on a match condition
("upsert" in Oracle/SQL Server; MySQL uses INSERT ... ON DUPLICATE KEY UPDATE).
UPSERT — general term for "insert, or update if it already exists."
4. Window & Advanced SQL
*OVER() *
turns an aggregate/ranking function into a window function that doesn't collapse rows.
*PARTITION BY *
OVER(PARTITION BY dept)
→ resets the window calculation for each group.
ORDER BY (in window)
defines row order within each partition for ranking/running calcs.
*ROW_NUMBER() *
assigns a unique sequential number to each row within a partition.
RANK()
ranks rows, skipping numbers after ties (1,2,2,4).
*DENSE_RANK() *
ranks rows without skipping after ties (1,2,2,3).
NTILE()
splits rows into N roughly equal buckets (e.g., quartiles).
*LAG() *
accesses a value from the previous row in the partition.
*LEAD() *
accesses a value from the next row in the partition.
FIRST_VALUE()
returns the first value in the window frame.
*LAST_VALUE() *
returns the last value in the window frame.
NTH_VALUE()
returns the value at a specific position in the window frame.
SUM() OVER()
running/grouped total without collapsing rows.
AVG() OVER()
running/grouped average.
COUNT() OVER() running/grouped count.
MIN() OVER()
running/grouped minimum.
*MAX() OVER() *
running/grouped maximum.
*ROWS BETWEEN *
defines a physical row-based window frame
(e.g., ROWS BETWEEN)
2 PRECEDING AND CURRENT ROW).
RANGE BETWEEN
defines a value-based window frame instead of row-count based.
*UNBOUNDED PRECEDING *
window frame starts from the very first row of the partition.
*CURRENT ROW *
window frame boundary set at the current row.
*PERCENT_RANK() *
relative rank of a row as a percentage (0 to 1).
*CUME_DIST() *
cumulative distribution fraction of rows with value ≤ current row.
*PERCENTILE_CONT() *
interpolated percentile value (continuous).
PERCENTILE_DISC()
percentile value taken from actual data (discrete).
QUALIFY
filters rows based on a window function result
(Snowflake/BigQuery; in MySQL/Oracle, wrap in a subquery and filter with WHERE instead).
PIVOT
rotates row values into columns (native in Oracle/SQL Server; MySQL simulates with CASE WHEN + GROUP BY).
UNPIVOT
rotates columns into rows (opposite of PIVOT).
JSON_EXTRACT() **
pulls a value out of a JSON column by path.
**EXPLAIN
shows the database's query execution plan, used to analyze/optimize performance.
Top comments (0)