Retrieving Data: SELECT, FROM, DISTINCT, AS
**SELECT**: Retrieves specific columns from a table.
Syntax: SELECT column1, column2 FROM table_name;
Example: SELECT title, author FROM books;
Tips: Avoid SELECT *. Use column names. Alias columns using AS.
LeetCode Links:
- https://leetcode.com/problems/combine-two-tables/
- https://leetcode.com/problems/big-countries/
- https://leetcode.com/problems/article-views-i/
Filtering Data: WHERE, AND, OR, IN, BETWEEN, IS NULL, NOT
**WHERE**: Filters rows matching a condition.
Syntax: SELECT column1 FROM table WHERE condition;
Example: SELECT title FROM books WHERE price > 10;
Tips: Use brackets when mixing AND/OR. Use IS NULL not = NULL.
LeetCode Links:
- https://leetcode.com/problems/combine-two-tables/
- https://leetcode.com/problems/customers-who-never-order/
- https://leetcode.com/problems/department-highest-salary/
Aggregations: COUNT, SUM, AVG, MIN, MAX, GROUP BY,
HAVING
**COUNT, SUM, AVG, MIN, MAX**: Aggregate values.
Syntax: SELECT COUNT(*) FROM books;
Example: SELECT author, AVG(price) FROM books GROUP BY author;
**HAVING**: Filters results after GROUP BY.
LeetCode Links:
- https://leetcode.com/problems/classes-more-than-5-students/
- https://leetcode.com/problems/user-activity-for-the-past-30-days-i/
- https://leetcode.com/problems/employee-bonus/
Sorting & Limiting: ORDER BY, LIMIT
**ORDER BY**: Sorts result ascending/descending.
Syntax: SELECT * FROM books ORDER BY price DESC;
**LIMIT**: Restricts number of rows returned.
Syntax: SELECT * FROM books LIMIT 3;
LeetCode Links:
- https://leetcode.com/problems/calculate-special-bonus/
- https://leetcode.com/problems/sales-person/
- https://leetcode.com/problems/number-of-unique-subjects-taught-by-each-teacher/
DML Commands: INSERT, UPDATE, DELETE
**INSERT INTO**: Adds rows.
Syntax: INSERT INTO table (col1) VALUES (val1);
**UPDATE**: Modifies rows. Use WHERE!
**DELETE**: Removes rows. Use WHERE to avoid wiping table.
LeetCode Links:
- https://leetcode.com/problems/not-boring-movies/
- https://leetcode.com/problems/delete-duplicate-emails/
- https://leetcode.com/problems/swap-salary/
Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL,
DEFAULT, CHECK
**PRIMARY KEY**: Unique ID per row.
**FOREIGN KEY**: Link to another table’s PK.
**UNIQUE**: Value must not duplicate.
**NOT NULL**: Value is required.
**DEFAULT**: Fallback value if not specified.
**CHECK**: Must meet condition (e.g., price > 0).
LeetCode Links:
- https://leetcode.com/problems/second-highest-salary/
- https://leetcode.com/problems/consecutive-numbers/
- https://leetcode.com/problems/duplicate-emails/
