What is a Database?
• Database Architectures
• SQL Server 2016 Architecture
• Editions of SQL Server 2016
• Tools and utilities in SQL Server 2016

SQL-THE STRUCTURED QUERY LANGUAGE

• Types of SQL Commands

SQL SERVER MANAGEMENT STUDIO

• A Quick Overview of SSMS
• Query Editor

DATABASE DESIGN AND CREATION

• Defining a Database
• System Databases And User Databases
o master
o tempdb
o model
o msdb
• Creating User-defined Databases
• Working with Databases
• Working with FileGroups

WORKING WITH TABLES

• Types of Tables
• Data Types
• Creating tables using navigation
• Creating tables using Command
• Altering table structure
• Inserting, Updating and Deleting Data
• Difference between delete and truncate
• Creating table with schemas and File groups

• What is Data Integrity?
• Types of Integrity Constraints
• Using Check, Unique, Primary Key and Foreign Key Constraints
• Enabling and Disabling Constraints
• Rules and Defaults

Day 2

RETRIEVING DATA

• SELECT Statement
• FROM and WHERE Clauses
• Using GROUP BY Clause
• Using HAVING Clause
• ORDER BY Clause
• Creating a Copy Table

JOINS

What is a Join?
Types of Joins
• Inner Join
• Outer Join
• Cross Join
• Self Join
• Joining more than 2 tables

RETRIEVING DATA – ADVANCED

• What is a sub query?
• Types of Sub Queries
• Working with CORRELATED SUBQUERIES
• Working with Common Table Expressions

BUILT-IN FUNCTIONS

• Aggregate Functions
• Numeric Functions
• Date and Time Functions
• String Functions
• Program Flow Functions
• Coalesce
• IIF
OPERATORS
• LIKE
• IN
• ANY
• BETWEEN
• UNION, UNION ALL
• EXISTS
• INTERSECT
• EXCEPT

VIEWS

• What is a View?
• Creating and using Views
• Using With Check Option
• Using Schema Binding
• Materialized Views

• Fundamentals
• Declaring and initializing variables

Flow Elements

• The IF ELSE Flow Element
• The WHILE Flow Element
• Case Statement
TRANSACTIONS
• ACID properties
• Types of Transactions
• Using Commit, Rollback and Save Commands

STORED PROCEDURES

• Definition
• Types of Stored Procedures
• Creating a Stored Procedure
• Input and Output Parameters
• Executing a Stored Procedure
• Error Handling in Stored Procedures
• Debugging a Stored Procedure

TRIGGERS

• Definition
• Types of Triggers
• DDL Triggers
• DML Triggers
• CREATE TRIGGER Syntax for DML Triggers
• Deleted and Inserted Logical Tables
• Creating DDL Triggers
• DDL Database-Level Events
• Dropping a DDL Trigger
• INSTEAD OF Triggers
• Setting Triggering Order

Day 4

USER-DEFINED FUNCTIONS

• What is a UDF?
• Creating and using UDF’s
• UDF’s returning a table

CURSORS

• What is a Cursor?
• Types of Cursors
• Statements in the usage of a Cursor
• @@FETCH_STATUS

XML INTEGRATION

• XML Datatype
• Using XML Schema collections
• Retrieve Related Data as XML
• Transform XML data into Relational Data

WORKING WITH INDEXES

• Index Uses
• Types of Indexes
• Creating Indexes
• Index Fragmentation
• Tuning Indexes

CONCURRENCY

• Isolation Levels
• The READ UNCOMMITTED Isolation Level
• The READ COMMITTED Isolation Level
• The REPEATABLE READ Isolation Level
• The SERIALIZABLE Isolation Level

SECURITY

• Security Basics
• Authentication Modes
• Creating a login
• Providing permissions on a database
• Providing permissions on database objects

BACKUP & RESTORE

• Types Of Backup
o Full database backup
o Differential backup
o Transaction log backup
o Restoring Database
MISCELLANEOUS TOPICS
o Partitioned Tables
o CTE’s