Kursai

20761: Querying Data with Transact-SQL

Egzaminas įskaičiuotas

Trukmė Kalba Miestas Kaina Data ir registracija kursui
5 dienos lietuvių - 1500 EUR Užklausti


This course is designed to introduce students to Transact-SQL. It is designed in such a way that the first three days can be taught as a course to students requiring the knowledge for other courses in the SQL Server curriculum. Days 4 & 5 teach the remaining skills required to take exam 70-761.

Kursas skirtas

The main purpose of the course is to give students a good understanding of the Transact-SQL language which is used by all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.

Kurso nauda

After completing this course, students will be able to:

  • Describe key capabilities and components of SQL Server.
  • Describe T-SQL, sets, and predicate logic.
  • Write a single table SELECT statement.
  • Write a multi-table SELECT statement.
  • Write SELECT statements with filtering and sorting.
  • Describe how SQL Server uses data types.
  • Write DML statements.
  • Write queries that use built-in functions.
  • Write queries that aggregate data.
  • Write subqueries.
  • Create and implement views and table-valued functions.
  • Use set operators to combine query results.
  • Write queries that use window ranking, offset, and aggregate functions.
  • Transform data by implementing pivot, unpivot, rollup and cube.
  • Create and implement stored procedures.
  • Add programming constructs such as variables, conditions, and loops to T-SQL code.

Kurso turinys

  • Module 1: Introduction to Microsoft SQL
  • Module 2: Introduction to T-SQL Querying
  • Module 3: Writing SELECT Queries
  • Module 4: Querying Multiple Tables
  • Module 5: Sorting and Filtering Data
  • Module 6: Working with SQL Server Data Types
  • Module 7: Using DML to Modify Data
  • Module 8: Using Built-In Functions
  • Module 9: Grouping and Aggregating Data
  • Module 10: Using Subqueries
  • Module 11: Using Table Expressions
  • Module 12: Using Set Operators
  • Module 13: Using Windows Ranking, Offset, and Aggregate Functions
  • Module 14: Pivoting and Grouping Sets
  • Module 15: Executing Stored Procedures
  • Module 16: Programming with T-SQL
  • Module 17: Implementing Error Handling
  • Module 18: Implementing Transactions

  1. Module 1: Introduction to Microsoft SQL Server
    • The Basic Architecture of SQL Server
    • SQL Server Editions and Versions
    • Getting Started with SQL Server Management Studio
    • Lab : Working with SQL Server Tools
  2. Module 2: Introduction to T-SQL Querying
    • Introducing T-SQL
    • Understanding Sets
    • Understanding Predicate Logic
    • Understanding the Logical Order of Operations in SELECT statements
    • Lab : Introduction to T-SQL Querying
  3. Module 3: Writing SELECT Queries
    • Writing Simple SELECT Statements
    • Eliminating Duplicates with DISTINCT
    • Using Column and Table Aliases
    • Writing Simple CASE Expressions
    • Lab : Writing Basic SELECT Statements
  4. Module 4: Querying Multiple Tables
    • Understanding Joins
    • Querying with Inner Joins
    • Querying with Outer Joins
    • Querying with Cross Joins and Self Joins
    • Lab : Querying Multiple Tables
  5. Module 5: Sorting and Filtering Data
    • Sorting Data
    • Filtering Data with Predicates
    • Filtering Data with TOP and OFFSET-FETCH
    • Working with Unknown Values
    • Lab : Sorting and Filtering Data
  6. Module 6: Working with SQL Server Data Types
    • Introducing SQL Server Data Types
    • Working with Character Data
    • Working with Date and Time Data
    • Lab : Working with SQL Server Data Types
  7. Module 7: Using DML to Modify Data
    • Adding Data to Tables
    • Modifying and Removing Data
    • Generating automatic column values
    • Lab : Using DML to Modify Data
  8. Module 8: Using Built-In Functions
    • Writing Queries with Built-In Functions
    • Using Conversion Functions
    • Using Logical Functions
    • Using Functions to Work with NULL
    • Lab : Using Built-In Functions
  9. Module 9: Grouping and Aggregating Data
    • Using Aggregate Functions
    • Using the GROUP BY Clause
    • Filtering Groups with HAVING
    • Lab : Grouping and Aggregating Data
  10. Module 10: Using Subqueries
    • Writing Self-Contained Subqueries
    • Writing Correlated Subqueries
    • Using the EXISTS Predicate with Subqueries
    • Lab : Using Subqueries
  11. Module 11: Using Table Expressions
    • Using Views
    • Using Inline Table-Valued Functions
    • Using Derived Tables
    • Using Common Table Expressions
    • Lab : Using Table Expressions
  12. Module 12: Using Set Operators
    • Writing Queries with the UNION operator
    • Using EXCEPT and INTERSECT
    • Using APPLY
    • Lab : Using Set Operators
  13. Module 13: Using Windows Ranking, Offset, and Aggregate Functions
    • Creating Windows with OVER
    • Exploring Window Functions
    • Lab : Using Windows Ranking, Offset, and Aggregate Functions
  14. Module 14: Pivoting and Grouping Sets
    • Writing Queries with PIVOT and UNPIVOT
    • Working with Grouping Sets
    • Lab : Pivoting and Grouping Sets
  15. Module 15: Executing Stored Procedures
    • Querying Data with Stored Procedures
    • Passing Parameters to Stored procedures
    • Creating Simple Stored Procedures
    • Working with Dynamic SQL
    • Lab : Executing Stored Procedures
  16. Module 16: Programming with T-SQL
    • T-SQL Programming Elements
    • Controlling Program Flow
    • Lab : Programming with T-SQL
  17. Module 17: Implementing Error Handling
    • Implementing T-SQL error handling
    • Implementing structured exception handling
    • Lab : Implementing Error Handling
  18. Module 18: Implementing Transactions
    • Transactions and the database engines
    • Controlling transactions
    • Lab : Implementing Transactions

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of relational databases.