SQL Complete Notes

This SQL Notes package is a comprehensive, structured, and interview-oriented learning resource.

What This SQL Notes Covers

SQL Fundamentals & Database Concepts

1. What is SQL and why it is used
2. SQL vs MySQL vs PostgreSQL vs Oracle
3. Database vs DBMS vs RDBMS
4. Tables, rows, columns, keys
5. Primary key, foreign key & candidate key concepts
6. SQL syntax rules & comments

SQL Command Types (Core Foundation)

1. DDL: CREATE, ALTER, DROP, TRUNCATE
2. DML: INSERT, UPDATE, DELETE
3. DQL: SELECT
4. DCL: GRANT, REVOKE
5. TCL: COMMIT, ROLLBACK, SAVEPOINT
6. ACID properties explained with real-world examples

Data Types & Constraints

1. Numeric, string, date & time data types
2. PRIMARY KEY, FOREIGN KEY
3. NOT NULL, UNIQUE, DEFAULT, CHECK
4. AUTO_INCREMENT rules & limitations
5. Data integrity (entity, referential, domain)

SELECT Queries & Filtering

1. SELECT * vs selecting specific columns
2. WHERE clause with operators
3. BETWEEN, IN, LIKE, NOT
4. Handling NULL values correctly
5. Execution order (FROM → WHERE → SELECT)

GROUP BY, HAVING & Aggregations

1. GROUP BY with SUM, AVG, COUNT, MIN, MAX
2. WHERE vs HAVING

Sorting, Limiting & Pagination

1. ORDER BY (ASC / DESC)
2. Sorting behavior & ASCII order
3. LIMIT & OFFSET for Top N and pagination

SQL Joins

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL OUTER JOIN (MySQL workaround)
5. CROSS JOIN
6. SELF JOIN
7. NATURAL JOIN
8. Join behavior, duplicates & NULL handling

Subqueries

1. Scalar, single-row & multi-row subqueries
2. Subqueries in WHERE, FROM, HAVING
3. Correlated subqueries
4. EXISTS vs IN
5. Performance best practices

SQL Functions

1. Aggregate functions
2. String functions
3. Date & time functions
4. Math & numeric functions
5. NULL handling functions

Window (Analytic) Functions – Advanced

1. ROW_NUMBER, RANK, DENSE_RANK
2. Running totals & analytics
3. OVER(), PARTITION BY, ORDER BY
4. Window frames (ROWS / RANGE)
5. GROUP BY vs Window functions
6. LAG, LEAD, FIRST_VALUE(), LAST_VALUE()

Common Table Expressions (CTE)

1. WITH clause & CTE syntax
2. CTE vs Subquery (interview comparison)
3. Readability & performance benefits

Indexes & Performance Optimization

1. Index concepts & purpose
2. Clustered vs non-clustered indexes
3. When to create indexes
4. When NOT to use indexes
5. Impact on SELECT, INSERT, UPDATE

Views

1. View concept & use cases
2. Creating & querying views
3. Advantages & limitations
4. Views vs Tables

Stored Procedures & Functions

1. Stored procedures vs functions
2. Parameters: IN, OUT, INOUT
3. Advantages of stored procedures

Triggers

1. Trigger concepts
2. BEFORE vs AFTER triggers
3. INSERT, UPDATE, DELETE triggers
4. Use cases & risks