Course Code: mssqlbspk
Duration: 28 hours
Prerequisites:
  • No previous SQL or database experience is required

Audience

  • Data analysts
  • Business Intelligence professionals
  • Business managers
  • Excel experts who wish to expand their analysis skill set
Overview:

Microsoft SQL Server is a relational database management system (RDBMS) for storing and retrieving data. In this instructor-led, live training (onsite or remote), participants will learn the essentials of the SQL language needed to query a Microsoft SQL Server database.

By the end of this training, participants will be able to:

  • Install and configure Microsoft SQL Server
  • Create and modify database objects with Transact-SQL statements
  • Query a Microsoft SQL Server database using Microsoft's Transact-SQL (T-SQL) language
  • Understand the essentials of database design
  • Optimize a database through normalization
  • Write stored procedures and user-defined functions

Format of the Course

  • Interactive lecture and discussion
  • Lots of exercises and practice
  • Hands-on implementation in a live-lab environment
Course Outline:

Relational Database Systems: An Introduction

  • Database Systems: An Overview
  • Relational Database Systems
  • Database Design
  • Syntax Conventions

Planning the Installation and Installing Sql Server

  • SQL Server Editions and Management Tools
  • Planning Phase: General Recommendations
  • Installation of SQL Server on Windows or Ubuntu

Front-End Tools for the Database Engine

  • SQL Server Management Studio
  • Using SQL Server Management Studio with the Database Engine
  • Authoring Activities Using SQL Server Management Studio
  • Azure Data Studio
  • SQL Server Management Studio vs. Azure Data Studio

Sql Components

  • SQL’s Basic Objects
  • Data Types
  • Transact-SQL Functions
  • Scalar Operators
  • NULL Values

Data Definition Language

  • Creating Database Objects
  • Modifying Database Objects
  • Removing Database Objects

Queries

  • SELECT Statement: Its Clauses and Functions
  • Subqueries
  • Temporary Tables
  • Join Operator
  • Correlated Subqueries
  • Table Expressions

Modification of a Table’s Contents

  • INSERT Statement
  • UPDATE Statement
  • DELETE Statement
  • Other T-SQL Modification Statements and Clauses

Stored Procedures and User-Defined Functions

  • Procedural Extensions
  • Stored Procedures
  • User-Defined Functions

System Catalog

  • Introduction to the System Catalog
  • General Interfaces
  • Proprietary Interfaces

Indices

  • Transact-SQL and Indices
  • Guidelines for Creating and Using Indices
  • Special Types of Indices

Views

  • DDL Statements and Views
  • DML Statements and Views

Security System of The Database Engine

  • Encrypting Data
  • Authentication
  • Schemas
  • Database Security
  • Roles
  • Authorization
  • Change Tracking
  • Data Security and Views

Concurrency Control

  • Concurrency Models
  • Transactions
  • Locking
  • Isolation Levels
  • Row Versioning

Triggers

  • Introduction to Triggers
  • Application Areas for DML Triggers
  • DDL Triggers and Their Application Areas

Troubleshooting

Summary and Conclusion