Transact-SQL – Querying Data

Home » Database » Transact-SQL – Querying Data

Course Skill Level:

Foundational

Course Duration:

3 day/s

Course Delivery Format:

Live, instructor-led.
Course Category:

Database
Course Code:

TSQLQDN22J02

Who should attend & recommended skills:

BI professionals, database developers, & database administrators with basic relational database experience

Who should attend & recommended skills

BI professionals, Database Developers, Database Administrators.
Relational Databases: Basic (1-2 years’ experience).

About this course

This class provides students with knowledge in writing Transact-SQL queries and an understanding of the Transact-SQL language.

Skills acquired & topics covered

Describe key capabilities and components of SQL Server.
T-SQL or Transact SQL is the query language specific to the Microsoft SQL Server product.
Write a single table SELECT statement, multi-table SELECT statement, use filtering and sorting.
Write queries that use built-in functions, write queries that aggregate data, and write subqueries.
Use window ranking, offset, and aggregate functions, transform data with pivot, unpivot, rollup and cube, create and implement stored procedures, working with snippets

Course breakdown / modules

Basic Architecture of SQL Server
SQL Server Management Studio
Example Queries and Syntax
Review and Lab Exercises

Predicate Logic
Query Syntax
SELECT, FROM, WHERE
GROUP BY, HAVING
ORDER BY
TOP OR OFFSET-FETCH filter
UNION, EXCEPT, INTERSECT
Date and time data types
Date and time functions
Review and Lab Exercises

Column and Table Aliases
Eliminating Duplicates with DISTINCT
Writing simple CASE expressions
Review and Lab Exercises

Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins
Subqueries
Self-contained subqueries
Correlated subqueries
The EXISTS predicate
Misbehaving subqueries
Table Expressions
Derived tables
CTEs
Views
Inline table-valued functions
Generation numbers
The APPLY operator
The CROSS APPLY operators
The OUTER APPLY operator
Implicit APPLY
Self Joins
Review and Lab Exercises

Sorting Data
Filtering Data with Predicates
Filtering with the TOP and OFFSET-FETCH Options
Working with unknown values
Review and Lab Exercises

Introducing Data Types
Working with Character Data
Working with Date and Time Data
Review and Lab Exercises

INSERTING Data
SELECT INTO
Bulk Import
Logging issues
BULK Rowset Provider
Modifying and Deleting Data
TRUNCATE TABLE
DELETING DUPLICATES
UPDATE Data
Using table expressions
Using variables
MERGE Data
Examples of MERGE
Conflicts to prevent
DELETING Data
DELETING DUPLICATES
TRUNCATE TABLE
Review and Lab Exercises

Writing Queries with Built-In Functions
Using Conversion Functions
Using Logical Functions
Using Functions to Work with NULL
Review and Lab Exercises

Using Aggregate Functions
CUBE and ROLLUP Clause
Using the GROUP BY Clause
Filtering Groups with HAVING
Review and Lab Exercises

Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Review and Lab Exercises

Using EXCEPT and INTERSECT / Labs
Writing Queries with UNION Operator/ Labs

Windows Ranking, Offset, and Aggregate
Windows with OVER
Exploring Window Functions
Review and Lab Exercises

Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets
Review and Lab Exercises

Querying Data with Stored Procedures
Passing Parameters to Stored Procedures
Creating Simple Stored Procedures
Working with Dynamic SQL
Review and Lab Exercises

T-SQL Programming Elements
Controlling Program Flow
Review and Lab Exercises

Implementing T-SQL Error Handling
Implementing Structured Exception Handling
Review and Lab Exercises

Transactions and the Database Engines
Controlling Transactions
Review and Lab Exercises

Insert Transact-SQL Snippets
Insert Surround-with Transact-SQL Snippets
Complete Transact-SQL Snippets
Add Transact-SQL Snippets
Review and Lab Exercises
Class Summary