Table Joins
- recap primary key foreign key joins
- recap inner versus outer joins
- non-equi joins
- self-joins
Set Operators
- union & union all
- intersect
- except
Summarising & Aggregating
- recap main summary functions
- recap Group By clause
- recap Having clause
- Rollup
- Cube
- Grouping
- Grouping_Id
Sub-Queries
- recap nested and correlated sub-queries in WHERE clause
- Common Table Expression
- sub-queries as columns
Analytical Functions
- Analytical versions of summary functions
- Ranking functions
- Lead & Lag
- Percentiles
Recursive queries
- Hierarchy from a single table
- Dynamic calendars & sequences
- Standardised reporting layouts
Stored Procedures & Functions
- Input & Output parameters
- If statements & Loops
Query Execution Plan
- Main principles
- Step detail
- Main retrieval methods
Indexes
- Clustered versus non-clustered
- Include
- Filter
Metadata
- Data Dictionary
Data Definition
- Create, Alter, Drop
- Mandatory columns
-Primary key
- Foreign key
- Unique key
-Check constraint
-Default values
Data Maintenance
- Insert Update Delete
- Transactions
- Rollback
Views
- Create Alter Drop