Basic SQL Language

• Setting up development environment

  • Introducing SQL server Management Studio

• Querying Data Using T-SQL Language

  • Basic Concepts of Databases

  • SQL Server Data Types

  • Retrieving Data: SELECT Statement

  • Filtering Data: WHERE, Logical Operators and Expressions

  • Organizing Retrieved Data: ORDER BY, GROUP BY, TOP

  • Using System Supplied Scalar Functions

  • Multiple Table Access: JOINs

  • Using Simple Sub-Queries

  • Updating Data Using DML Statements: UPDATE, INSERT, DELETE

• Database Design

  • Working with Data Types

  • Designing and Implementing Tables

  • Ensuring Data Integrity through Constraints

• Table Structures and Indexing

  • Planning for Indexing

  • Implementing Table Structures

  • Reading Execution Plans

  • Improving Performance through Non-Clustered Indexes

  • Execution Plans, Query Store, Optimizing Indexes

  • Advanced Table Designs, Partitioning Data, Compressing, Temporal Tables.

  • Columnstore Indexes, Using In-Memory Tables.

T-SQL Programming

• T-SQL Programming

  • Advanced Query and Code Writing

    • Basic T-SQL Programing

      • Variables, Loops, IF Function

      • Temp tables and table variables

      • Using Cursors

    • Working with Subqueries (Derived Tables, Correlated query, EXISTS, CTE)

    • Ranking Functions

    • MERGE Statement and OUTPUT Clause

• Code Objects and Transaction

  • Designing and Implementing Views

  • Designing and Implementing Stored Procedures

  • Merging Data and Passing Tables

  • Designing and Implementing User-Defined Functions

  • Creating Highly Concurrent Applications - Transactions

  • Error Handling

  • Responding to Data Manipulation via Triggers

• Data Integration

  • Working with semi-structured data (XML, JSON) and moving data (BCP, Export/Import etc)

Administering Microsoft SQL Server Databases

• Setup and Design

  • Setting up and Configuring SQL Server Infrastructure

  • Working with Databases

• Disaster Recovery

  • SQL Server Recovery Models

  • Backup and Restore

• Database Security

  • Authenticating and Authorizing Users

  • Server and Database Roles

  • Authorizing Users to Access Resources

  • Partially Contained Databases

  • Protecting Data with Encryption and Auditing

• Automating Maintenance

  • Automating Management using Jobs

  • Configuring Security for SQL Server Agent

  • Alerts and Notifications

  • Performing Ongoing Database Maintenance

• Monitoring and Troubleshooting

  • Tracing Access to SQL Server SQL Server Profiler and Extended Events

  • Management and Troubleshooting

  • Managing SQL Server Using PowerShell

  • Tracing Access to SQL Server with Extended Events

Advanced Extra Topics

• Performance Tuning Workshop - Simulating, Monitoring and Optimizing Activity

  • Reviewing Monitoring Tools

  • Setting up Simulating Environment

  • Configuring Monitoring Tools

  • Generating Database Activity

  • Viewing and Exploring Monitoring Findings

  • Discussing Performance Issues

  • Applying Optimizations

• Replication

  • Distributing and Synchronizing Data

  • Replication Components

  • Replication Mechanism

  • Implementing Replication

• High Availability Architectures

  • Database Mirroring

  • Log Shipping

  • SQL Server Failover Clustering

  • Always On

• Internship Project