SQL Concepts Summary Table
| Category | What to Learn / Use | Notes / Keywords |
|---|---|---|
| Basics | SELECT, FROM, WHERE, ORDER BY | Core query structure |
| Filtering | WHERE, BETWEEN, IN, LIKE, IS NULL | Row-level conditions |
| Sorting | ORDER BY ASC/DESC, multiple columns | Control display order |
| Aggregations | COUNT(), SUM(), AVG(), MIN(), MAX() | Use with GROUP BY |
| Grouping | GROUP BY, HAVING | HAVING filters aggregated results |
| Joins | INNER, LEFT, RIGHT, FULL, CROSS | Combine rows across tables |
| Subqueries | Scalar, Correlated, IN, EXISTS | Nest queries inside others |
| Window Functions | ROW_NUMBER(), RANK(), LEAD(), LAG() | Operate across row “windows” |
| CTEs | WITH clause (Common Table Expressions) | Temporary result sets |
| Unions | UNION, UNION ALL | Combine rows vertically |
| Set Operations | INTERSECT, EXCEPT | Compare datasets |
| String Functions | CONCAT, SUBSTRING, TRIM, LOWER | Clean and manipulate text |
| Date Functions | CURRENT_DATE, DATE_PART(), EXTRACT() | Handle time-based logic |
| Case Statements | CASE WHEN ... THEN ... ELSE ... END | Conditional logic in SQL |
| Pivoting | Static & Dynamic Pivoting | CASE WHEN, MAX(), string_agg(), etc. |
| Temporary Tables | TEMP TABLE, CTEs | Useful for staging intermediate data |
| Views | CREATE VIEW, ALTER VIEW | Virtual tables for reusable logic |
| Indexes | CREATE INDEX, EXPLAIN | Improve performance, especially in big joins |
| Transactions | BEGIN, COMMIT, ROLLBACK | For data integrity and rollback capabilities |
| Constraints | PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY | Maintain data rules |
| Data Types | VARCHAR, INT, DATE, BOOLEAN, etc. | Understanding helps avoid bugs |
| Normalization | 1NF, 2NF, 3NF concepts | Database design principle |
| Performance Tuning | EXPLAIN PLAN, ANALYZE | Find bottlenecks |
| Stored Procedures | CREATE PROCEDURE, EXECUTE | Reusable blocks of SQL logic |
| Triggers | Auto-action on INSERT, UPDATE, DELETE | Event-driven automation |
| Permissions | GRANT, REVOKE, ROLES | Manage access and security |
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment