Microsoft SQL Advanced

Learn via : Virtual Classroom / Online
Duration : 3 Days
Download PDF
  1. Home
  3. Microsoft SQL Advanced

Description

    This Advanced SQL Database Query course is a training program that aims to gain in-depth knowledge and skills in extracting data from databases, filtering, analyzing and reporting using the SQL (Structured Query Language) language. This training aims to equip participants in creating more complex database queries, optimizing performance, performing data manipulation more effectively, and performing data analysis.

Outline

Window Functions

Conversion Functions

Logical Functions

  • Usage of Logical Functions.
  • Performing Pivot with CASE WHEN.

System Functions

SQL Server Data Types.

  • Working with Character Data Types.
  • String Concatenation.
  • Numeric Data Types.
  • Monetary Data Types.
  • Binary Data Types.
  • Date and Time Data Types.
  • Data Type Precedence.

DML (Data Manipulation Language) Concepts

  • Inserting Records into a Database. (INSERT Statement – Using SELECT and INSERT Together.)
  • Updating Records in a Database. (UPDATE Statement – Usage of WHERE in Updates.)
  • Deleting Records from a Database. (DELETE Statement.)

DDL (Data Definition Language) Concepts

  • CREATE Statement.
  • ALTER Statement.
  • DROP Statement.
  • TRUNCATE Statement.
  • Comparison between TRUNCATE and DELETE.

TCL (Transaction Control Language) Concepts

  • Transaction Concept.
  • COMMIT Statement.
  • ROLLBACK Statement.
  • SAVEPOINT Statement.

Table and View Creation and Usage

  • Designing and Creating a Table.
  • CONSTRAINTS (Table Constraints)
  • PK (Primary Key)
  • FK (Foreign Key)
  • NOT NULL
  • UNIQUE
  • CHECK
  • DEFAULT
  • IDENTITY
  • Creating and Using Views.
  • View Constraints.
  • Temporary Tables.

Introduction to SQL Programming

  • Declaring Variables.
  • Assigning Values to Variables.
  • Using Variables in Queries.
  • Creating Tables of Variable Data Types.
  • Creating and Executing Stored Procedures. Working with Stored Procedure Parameters.
  • User-Defined Function Types.
  • Creating and Executing User-Defined Functions.
  • Flow Controls.
  • BEGIN END Statement.
  • IF ELSE.
  • BREAK and CONTINUE.
  • Error Handling in T-SQL.
  • TRY-CATCH Usage.
  • THROW Statement.

Introduction to Query Performance

  • Server Architecture.
  • Performance Enhancing Factors.
  • Execution Plan.

Prerequisites

Having taken Microsoft SQL Fundamentals training or mastering the topics explained in the Microsoft SQL Fundamentals training.