20761-C: Querying Data with Transact-SQL

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

Recent articles