Complete SQL Notes
1. Introduction to SQL
SQL (Structured Query Language) is used to communicate with relational databases. It is the standard language for
relational database management systems (RDBMS).
- Common RDBMS: MySQL, PostgreSQL, Oracle, SQL Server, SQLite.
2. SQL Data Types
- INT, FLOAT, DECIMAL
- VARCHAR(n), TEXT
- DATE, TIME, DATETIME, TIMESTAMP
- BOOLEAN
3. SQL Statements
- SELECT: Retrieve data from a database.
- INSERT: Add new data.
- UPDATE: Modify existing data.
- DELETE: Remove data.
- CREATE: Create new tables or databases.
- DROP: Delete tables or databases.
- ALTER: Modify existing table structures.
4. SELECT Queries
- SELECT column1, column2 FROM table;
- WHERE: SELECT * FROM table WHERE condition;
- AND, OR, NOT: Combine conditions.
- ORDER BY column ASC|DESC;
- LIMIT n;
- DISTINCT to remove duplicates.
5. Aggregate Functions
- COUNT(), SUM(), AVG(), MIN(), MAX()
- GROUP BY and HAVING
6. Joins
Complete SQL Notes
- INNER JOIN: Matches in both tables.
- LEFT JOIN: All from left, matching from right.
- RIGHT JOIN: All from right, matching from left.
- FULL JOIN: All records when there is a match.
7. Subqueries
- Can be used in SELECT, FROM, WHERE clauses.
- Correlated subqueries use values from outer query.
8. Set Operations
- UNION, UNION ALL
- INTERSECT, EXCEPT
9. Window Functions
- ROW_NUMBER(), RANK(), DENSE_RANK()
- LAG(), LEAD(), NTILE(), FIRST_VALUE(), LAST_VALUE()
- OVER(PARTITION BY ... ORDER BY ...)
10. Common Table Expressions (CTEs)
- WITH cte_name AS (SELECT ...)
- Useful for breaking complex queries into simpler parts.
11. Data Definition Language (DDL)
- CREATE TABLE table_name (...)
- ALTER TABLE table_name ADD/MODIFY/DROP column
- DROP TABLE table_name
12. Data Manipulation Language (DML)
- INSERT INTO table VALUES (...)
Complete SQL Notes
- UPDATE table SET column = value WHERE condition
- DELETE FROM table WHERE condition
13. Data Control and Transaction
- GRANT, REVOKE permissions
- BEGIN, COMMIT, ROLLBACK transactions
14. Indexes and Performance
- CREATE INDEX idx_name ON table(column);
- Speeds up SELECT queries, slows down INSERT/UPDATE.
15. Constraints
- PRIMARY KEY, FOREIGN KEY, UNIQUE
- NOT NULL, CHECK, DEFAULT