SQL Server syllabus for BI

Topics covered so far:
1. Data Manipulation Language
a) Insert
b) Update
c) Delete
d) Select

2. Data Definition Language
a. Create
b. Alter
c. Drop
d. Truncate (not sure if it’s really a DDL command)

3. Data Types
a. Integer (int())
b. Character (char())
c. Varchar()
d. NVarchar()
e. Float()
f. Decimal()
g. Date()
h. Time()
i. Datetime()
j. Datetime2()
4. Identity Function
5. Union and Union all

6. Functions in SQL

a. Aggregate Functions
 Sum()
 Count()
 Count(*)
 Max()
 Min()
 Avg ()
b. String Functions

 Left()
 Right()
 Ltrim()
 Rtrim()
 Len()
 Datalength()
 Reverse()
 Substring()
 Charindex()
 NullIf()
 Coalesce()
b. Date Functions

 Year()
 Month()
 Day()
 Datepart()
 Datename()
 Dateadd()
 Datediff()
c. Analytical Functions
 Row_Number()
 Rank()
 Dense_Rank()
7. Sequence of SQL query

8. Sub Queries and Correlated Queries

9. Constraints
a. Primary key
b. Foreign key
c. Unique
d. Default
e. Check
f. Not null

10. Joins
a. Inner join and Outer join
b. Left join
c. Right join
d. Cross join
e. Self join

11) Common Table Expressions

12) Derived table

13) Temporary table

a. Local temporary table

b. Global temporary table

14) Table Variables

15) Views

16) Cursors

17) Stored Procedures
a. Stored procedures using parameters.
b. Stored procedures with output parameters.

18) User Defined Functions
a. Scalar value functions
 Case when condition
b. Table value functions
 Inline function
 Multi statement table value function
c. Other functions

19) Triggers

20) Performance Tuning