Basics of MS SQL from Scratch
1. Introduction to MS SQL
- What is MS SQL Server?
- SQL vs. T-SQL (Differences and similarities)
- SQL Server Management Studio (SSMS)
- Database Concepts (Database, Tables, Schemas, and Relationships)
- Data Types in SQL (INT, VARCHAR, DATE, etc.)
2. SQL Basics (CRUD Operations)
- CREATE - Creating Databases, Tables, and Schemas
- INSERT - Inserting data into tables
- SELECT - Querying data from tables (DISTINCT, ORDER BY, WHERE)
- UPDATE - Modifying existing records
- DELETE - Deleting specific rows from a table
- TRUNCATE - Deleting all records from a table
3. SQL Constraints
- Primary Key, Foreign Key, Unique Constraint, Check Constraint, Default Constraint, Not Null
4. SQL Functions
- Aggregate Functions (COUNT, SUM, AVG, MAX, MIN)
- String Functions (LEFT, RIGHT, LEN, UPPER, LOWER, REPLACE, SUBSTRING)
- Date/Time Functions (GETDATE, DATEADD, DATEDIFF, CONVERT, FORMAT)
- Mathematical Functions (ROUND, CEILING, FLOOR)
5. Joins
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN, CROSS JOIN
6. Subqueries and Derived Tables
- Simple Subqueries, Correlated Subqueries, EXISTS, IN, Derived Tables
7. Set Operators
- UNION, UNION ALL, INTERSECT, EXCEPT
8. SQL Clauses
- WHERE, GROUP BY, HAVING, ORDER BY, TOP, LIMIT
9. Views, Stored Procedures, and Functions
- Views, Stored Procedures, User-Defined Functions (Scalar, Table-Valued, Inline, Multi-statement)
10. Triggers and Transactions
- Triggers (AFTER, INSTEAD OF), Transactions (BEGIN, ROLLBACK, COMMIT), ACID Properties
11. CTE (Common Table Expressions)
- CTEs and Recursive CTEs
12. Window Functions
- ROW_NUMBER, RANK, DENSE_RANK, NTILE, LEAD, LAG, PARTITION BY
13. Indexes
- Clustered, Non-Clustered, Unique, Filtered Indexes
14. SQL Optimization
- Query Execution Plan, Index Optimization, Avoiding SELECT *, Batch Processing, Query Hints
15. Data Export/Import
- Bulk Insert, SQL Import/Export Wizard, BCP, OPENROWSET
16. SQL Security and Permissions
- Authentication Modes, Users, Roles, Permissions, GRANT, REVOKE, DENY, Encryption, Data
Masking
17. Advanced SQL Concepts
- Dynamic SQL, Pivot/Unpivot, Row Locking, Isolation Levels, Error Handling (TRY...CATCH), Data
Warehousing Concepts
