Microsoft SQL Server 2016 provides a robust architecture for enterprise level data management, developer’s productivity and business intelligence. This hands-on course is designed to provide necessary knowledge and hands-on experience for installation and configuration and administration of SQL Server 2016; designing and implementing SQL Server databases, querying and manipulating data from SQL Server and implementing a Data Warehouse with SQL Server 2016.
Brief Contents
- Introduction to SQL Server, Its Installation & Configuration
- Introduction to Database Designing & Entity Relationship Diagram (ERD)
- Database Design Theory Concepts & Rules for Normalization
- Working with Tables, Schemas, Columns & Built-In Data Types
- Working with Primary Keys, Foreign Keys & Identity Column
- Working with SQL Server Management Studio
- Ensuring Data Integrity through Constraints
- Introduction to T-SQL & Writing Basic Select Statements
- Eliminating Duplicates with DISTINCT & Using Column and Table Aliases
- Writing Queries that Filter Data using a WHERE Clause
- Writing Queries that Sort Data Using an ORDER BY Clause
- Writing Queries that Filter Data Using the TOP Option
- Write Queries that filter data using the OFFSET-FETCH
- Querying Multiple Tables & Understanding Joins
- Inner Joins, Self Joins, Outer Joins & Cross Joins
- Indexing & Designing Optimized Indexing Strategies
- Using Conversion Functions, Logical Functions & Function to work with NULL
- Writing Queries using GROUP BY Clause & Aggregate Functions
- Writing Queries that Filter Groups with the HAVING Clause
- Using Self-Contained Subqueries, Scalar Subqueries & Multi-Result Subqueries
- Writing Queries That Use Correlated Subqueries and EXISTS Clause
- Writing Queries with UNION, EXCEPT, INTERSECT & APPLY
- Designing & Implementing Views & Performance Consideration for Views
- Inserting, Updating & Deleting Data using Data Manipulation Language (DML)
- Designing and Implementing Stored Procedures
- Creating & using user defined Table-Valued Functions (TVFs)
- Creating & using user defined Common Table Expressions (CTEs)
- Programming with T-SQL: T-SQL Programming Elements
- Programming with T-SQL: Controlling Program Flow
- Programming with T-SQL: Implementing T-SQL error handling
- Programming with T-SQL: Implementing structured exception handling
- Programming with T-SQL: Controlling database transactions
- Responding to Data Manipulation via Triggers
- Storing and Querying XML & Spatial Data in SQL Server
- Storing and Querying BLOBs and Text Documents in SQL Server
- Performance Optimization, Monitoring & Concurrency in SQL Server
Training Methodology
Audience
Course Project
Participants of the course will complete hands-on labs and project assignments with help of their trainers.
Certificate
EVS Certified SQL Server Developer
MCP: Querying Data with Transact-SQL
MCP: Developing SQL Database
Duration & Frequency
2 Month (8 weeks; with 3 training sessions of 1.5 hours a week)