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

You want more information about this curse? Contact us!

By sending this message you agree to our Privacy Notice

Intranet

Renewable Energy

What is it?

We offer specialized services of photovoltaic solar energy that is obtained directly from solar radiation through a solar panel that in turn transforms it into electric power.

Who is it for?

All those companies, businesses and homes that want to reduce costs in their day to day and want to have a sustainability plan to support the environment as well as their economy.

Why us?

1.- Support from a team committed to the environment.
2.- GSB will provide you with the best solution according to your needs
3.- Control and monitoring as well as operation reports, maintenance of preventive and corrective equipment.

Cybersecurity

What is it?

We offer specialized services in Cyber ​​​​Security in real time with the best tools, alliances and technology for the protection of your information and operation.

Who is it for?

All those companies that use technology on a daily basis and wish to have the guarantee of a team with vast experience, knowledge, kindness, efficiency and speed.

Why us?

1.- We identify your needs and proceed to define the required actions, through the use of analysis tools such as: diagnostic interviews, concept tests, simulators and questionnaires.

2.- We have the best specialists in the market, which allows us to provide you with a service that guarantees your protection against threats.

3.- We implement processes that allow timely feedback, through which we manage to mitigate risks and protect the computer security of your company.