| # |
SQL Server Installation And Orientation |
What Is Data? What Is Database? File Store Limitations?
Why Microsoft SQL Server? Advantages (Technical/Usage)
What Is SQL? What Is T-SQL? Differences. Why T-SQL?
Versions And Editions Of SQL Server
How To Install SQL Server - Step By Step Guidelines
SQL Server Tools - SQL Server Management Studio (SSMS)
|
1 |
01 |
Chapter 1: SQL BASICS - DDL, DML, SELECT |
Tables
DDL And DML Statements - Creating & Using Databases
Table Creation (Basic Level) - Columns And Data Types
INSERT / Store Data Into SQL Server Tables
Single Row And Multiple Row Inserts With NULL Values
SELECT Queries And Basic Operators: IN, BETWEEN
UPDATE Statements With / Without Conditions. SET
DELETE Statements With Conditions.
TRUNCATE Statement - DELETE Comparisons
SYSTEM DATABASES - Purpose And Importance.
|
4 |
| 02 |
Chapter 2: TABLE DESIGN & QUERIES |
Table Design - Creation. Columns - Data Types, Length
Schemas - Purpose, Creation And Usage With Tables
Table Design Using User Interface - Usage Options
Data Types, Length, NULLs And Naming Conventions
BATCH And TRANSACTION Concepts - Insert Examples
UNION, UNION ALL Operators. Differences, Row Order
CREATE, ALTER, DROP -- INSERT, UPDATE, DELETE
SELECT Queries With Schema On Tables, Column Aliases
Computed Columns
Comparing DELETE And TRUNCATE Statements
T-SQL Operators: IN, BETWEEN, IS, AND, OR, EXISTS
Basic Sub Queries - SELECT, MIN/ MAX. Column Aliases
Temporary Tables: Purpose And Types. Local And Global
Synonyms: Purpose. Alternate Object Reference, Queries
Synonyms: Purpose. Alternate Object Reference, Queries
|
4 |
| 03 |
Chapter 3: CONSTRAINTS And KEYS |
Constraints And Keys - Ensuring Table Data Integrity
Different Between OLTP And OLAP Database Model
Normalization And De-Normalization
NULL, NOT NULL And Default Null Ability For Columns
UNIQUE KEY Constraints: Importance, Uniqueness, Nulls
PRIMARY KEY Constraint: Properties, Priority, Limitations
FOREIGN KEY Constraint: References, Relations & Usage
FOREIGN KEY Constraints: Relating Two Or More Tables
CASCADED Foreign Keys And Relations - UPDATE, DELETE
CHECK Constraints: Properties, Conditions And Usage
CHECK Constraints: Multi Column Checks & Operators Use
DEFAULT Constraints: Properties, Usage And Limitations
Composite Primary Keys & Practical Use. Recommendations
Adding / Modifying Constraints, Keys And Data Types
Normal Forms - Types, Purpose And Usage. With Examples
|
3 |
| 04 |
Chapter 4: JOINS, SUB QUERIES & TUNING |
JOINS - Purpose And Types, Use Case Scenarios
CROSS JOIN In Detail. Examples And Conditions @ WHERE
INNER JOIN In Detail. Examples With WHERE And ON
OUTER JOINS In Detail. LEFT, RIGHT And FULL Joins
SELF JOINS With INNER / OUTER Joins. Usage Scenarios
JOINS With More Than 2 Tables. Syntax, Precedence Order
Query Tuning And Optimization With Schema References
Basic Sub Queries And Joins. Alternate Syntax & Queries
|
5 |
| 05 |
Chapter 5: VIEWS - FUNCTIONS |
VIEWS - Benefits For Data Access
Views As Stored SELECT Statements, Data Access
SCHEMABINDING And ENCRYPTION Options - Advantages
Issues With Views For Data Validations - Solutions
Cascaded Views And WITH CHECK OPTION, Advantages
CREATING FUNCTION
Queries With GROUP BY, HAVING, ON & WHERE
|
4 |
| 06 |
Chapter 6: STORED PROCEDURES |
Stored Procedures - Purpose, Syntax, Properties And Types
Variables - Usage And Data Types In Stored Procedures
Parameters - Usage And Data Types In Stored Procedures
Stored Procedures For Data Reporting. Advantages, Tuning
IF... ELSE, IF… ELSE IF, IIF Conditions. PRINT Statements
Error Handling Techniques In T-SQL: TRY, CATCH, THROW
Dynamic Parameters And Variables. Examples With Views
Default Parameter Values, Data Types And NULL Values
Batch Executions With Stored Procedures. Variants
SQL Injection
USE COUNT (*) CONCEPT TO HANDLE DATA ACCURACY.
|
4 |
| 07 |
Chapter 7: TRIGGERS & TRANSACTIONS |
Concept Of Inserted And Updated.
Example
|
3 |
| 08 |
Chapter 8: INDEXES And QUERY TUNING OPTIONS |
Indexes: USE
Clustered Indexes - Architecture, Fragmentation Issues
Non-Clustered Index
Included Indexes
|
3 |
| 09 |
Chapter 9: SSIS And SSRS |
Control Flow And Data Flow
Transaction Error Handling
Bulk Load
Configuration And Deployment
Transformation
Advanced Data Warehousing And ETL
Why SSRS
Reporting And Report Manager
Different Reports
|
5 |
| 10 |
Chapter 10: Other Commonly Used In T-SQL |
Merge
SCD – Slowly Changing Dimension (TYPE 0, TYPE 1, TYPE 2)
Common Table Expressions (CTE)
Temp Table – With Index
|
2 |
| 11 |
Chapter 11: Error Handling, Merge, SCD |
|
2 |
| 12 |
Chapter 12: ETL |
Data Warehouse Design And ETL Process
Control Flow Tasks - Purpose And Usage
Data Flow Tasks - Purpose And Usage
Creating Basic SSIS & ETL Packages
USE Of Different Transformation
Kimball Method
Bulk Load
|
1 |
| TOTAL |
|
|
41 |