SQL Database

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


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