General SQL Class Notes
1. What is SQL?
SQL (Structured Query Language) is used to create, read, update, delete, and manage data in
relational databases.
2. Common Commands
DDL: CREATE, ALTER, DROP, TRUNCATE
DML: INSERT, UPDATE, DELETE, MERGE
DQL: SELECT
DCL: GRANT, REVOKE
TCL: COMMIT, ROLLBACK, SAVEPOINT
3. SELECT Syntax
SELECT column1, column2 FROM table_name WHERE condition ORDER BY column ASC/DESC;
4. Filtering
WHERE, AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL, IS NOT NULL.
5. Sorting & Limiting
ORDER BY, LIMIT / TOP depending on database.
6. Aggregate Functions
COUNT(), SUM(), AVG(), MIN(), MAX(). Use GROUP BY and HAVING.
7. Joins
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN.
8. Subqueries
A query inside another query. Can be scalar, multi-row, correlated, or nested.
9. Window Functions
ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM() OVER().
10. CTE
WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;
11. Indexes
Indexes improve query performance but slow INSERT/UPDATE slightly.
12. Transactions
Use COMMIT to save, ROLLBACK to undo changes.
13. Best Practices
Use meaningful aliases, avoid SELECT *, create indexes wisely, write readable queries.