Description

This program has as its main objective to give students a good understanding of the Transact-SQL language that is used in all disciplines related to SQL Server; namely, database administration, database development, and business intelligence.

Addressed to

  • Database administrators
  • Database developers
  • BI professionals.

Duration

35 hours

Modalities

  • Face-to-face

  • Virtual live through digital platforms

  • on site

Related Courses

  • 20764-C: Administering a SQL Database Infrastructure

  • 20762-C: Developing SQL Databases

It includes

  • 35 hours of training

  • Trained instructors

  • Participant manual in digital format

  • Proof of participation


Temary


Lesson 1: Introduction to Microsoft SQL Server

  • The basic architecture of SQL Server

  • SQL Server editions and versions

  • Introduction to SQL Server Management Studio

Lab : Working with SQL Server tools

  • Work with SQL Server Management Studio

  • Creating and organizing T-SQL scripts

  • Use books online

Lesson 2: Introduction to T-SQL Queries

  • Presentation of T-SQL

  • Set comprehension

  • Understanding Predicate Logic

  • Understanding the logical order of operations in SELECT statements

Lab: Introduction to T-SQL Queries

  • Execution of basic SELECT statements

  • Running queries that filter data using predicates

  • Running queries that order data using ORDER BY

Lesson 3: Writing SELECT Queries

  • Write simple SELECT statements

  • Removing duplicates with DISTINCT

  • Use column and table aliases

  • Write simple CASE expressions

Lab: Writing Basic SELECT Statements

  • Write simple SELECT statements

  • Eliminate duplicates with DISTINCT

  • Use column and table aliases

  • Use a simple CASE expression

Lesson 4: Querying Multiple Tables

  • Understanding joins

  • Query with inner joins

  • Query with outer joins

  • Consultation with Cross Joins and Self Joins

Lab : Query Multiple Tables

  • Writing Queries That Use Inner Joins

  • Writing queries that use inner joins of multiple tables

  • Writing queries that use self-joins

  • Writing queries that use outer joins

  • Writing queries that use cross joins

Lesson 5: Data Classification and Filtering

  • data classification

  • Filter data with predicates

  • Data filtering with TOP and OFFSET-FETCH

  • Work with unknown values

Lab: Data Classification and Filtering

  • Writing queries that filter data using a WHERE clause

  • Writing queries that order data using an ORDER BY clause

  • Writing queries that filter data with the TOP option

  • Writing queries that filter data using the OFFSET-FETCH clause

Lesson 6: Working with SQL Server Data Types

  • Introduction to SQL Server data types

  • Work with character data

  • Work with date and time data

Lab : Working with SQL Server Data Types

  • Write queries that return date and time data

  • Write queries that use date and time functions

  • Write queries that return character data

  • Write queries that return character functions

Lesson 7: Using DML to modify data

  • Add data to tables

  • Modify and delete data

  • Generating automatic column values

Lab : Using DML to Modify Data

  • Insert records with DML

  • Updating and deleting records using DML

Lesson 8: Using built-in functions

  • Write queries with built-in functions

  • Use conversion functions

  • Use logic functions

  • Using functions to work with NULL

Lab : Using Built-in Features

  • Write queries that use conversion functions

  • Write queries that use logical functions

  • Write queries that test nullability

Lesson 9: Data Grouping and Aggregation

  • Use added functions

  • Using the GROUP BY clause

  • Filter groups with HAVING

Lab: Data Grouping and Aggregation

  • Writing queries that use the GROUP BY clause

  • Write queries that use aggregate functions

  • Writing queries that use different aggregate functions

  • Writing queries that filter groups with the HAVING clause

Lesson 10: Using Subqueries

  • Write stand-alone subqueries

  • Write correlated subqueries

  • Using the EXISTS predicate with subqueries

Lab : Using Subqueries

  • Write queries that use separate subqueries

  • Writing queries that use scalar and multiple-result subqueries

  • Writing queries that use correlated subqueries and an EXISTS clause

Lesson 11: Using Table Expressions

  • use views

  • Using table-valued functions online

  • Use derived tables

  • Use common table expressions

Lab : Using Table Expressions

  • Write queries that use views

  • Write queries that use derived tables

  • Write queries that use common table expressions (CTEs)

  • Writing queries that use inline table-valued expressions (TVFs)

Lesson 12: Using Set Operators

  • Writing queries with the UNION operator

  • Using EXCEPT and INTERSECT

  • Using APPLY

Lab: Using Set Operators

  • Writing queries that use UNION and UNION ALL set operators

  • Writing queries that use CROSS APPLY and OUTER APPLY operators

  • Write queries that use the EXCEPT and INTERSECT operators

Lesson 13: Using the Windows Sorting, Compensation, and Aggregation Features

  • Creating Windows with OVER

  • Exploring Window Features

Lab : Using Windows Ranking, Compensation, and Aggregation Features

  • Write queries that use sort functions

  • Write queries that use compensation functions

  • Writing queries that use window aggregation functions

Lesson 14: Grouping and Pivoting Sets

  • Writing queries with PIVOT and UNPIVOT

  • Work with grouping sets

Lab: Pooling and Pivoting Sets

  • Write queries that use the PIVOT operator

  • Writing queries that use the UNPIVOT operator

  • Writing queries that use the GROUPING SETS CUBE and ROLLUP subclauses

Lesson 15: Executing Stored Procedures

  • Query data with stored procedures

  • Pass parameters to stored procedures

  • Creating simple stored procedures

  • Work with dynamic SQL

Lab : Running Stored Procedures

  • Using the EXECUTE statement to call stored procedures

  • Pass parameters to stored procedures

  • Execution of stored procedures in the system

Leave a Reply

Your email address will not be published.