Advanced search

M20761 Querying Data with Transact-SQL Print

This 4-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server.

Accredited course for Continuing Education of Pedagogical Staff

Course length: 4 days

Dates

DatePlaceLanguagePrice (without VAT)Availability
03/25/2019 - 03/28/2019 Prague cs 25 000 CZK Free date
05/27/2019 - 05/30/2019 Prague cs 25 000 CZK Free date
PDF to download Expand allCollapse all
  • Students will be able to

    • Write SELECT queries
    • Query multiple tables
    • Sort and filter data
    • Describe the use of data types in SQL Server
    • Modify data using Transact-SQL
    • Use built-in functions
    • Group and aggregate data
    • Use subqueries
    • Use set operators
    • Create and implement views and table-valued functions
    • 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
  • Course requirements

    • Basic knowledge of the Microsoft Windows operating system.
  • This course is intended for

    • This course is intended for end users, Database Administrators, Database Developers, and BI professionals.
  • Literature

    All participants will get original Microsoft student materials.

  • Hardware

    Classrooms are equipped with high-performance computers with Internet access and the possibility of wireless connection.

  • Syllabus

    Module 1: Introduction to Microsoft SQL Server 2016

    • Lesson 1: The Basic Architecture of SQL Server
    • Lesson 2: SQL Server Editions and Versions
    • Lesson 3: Getting Started with SQL Server Management Studio
    • Lab: Working with SQL Server 2016 Tools

    Module 2: Introduction to T-SQL Querying

    • Lesson 1: Introducing T-SQL
    • Lesson 2: Understanding Sets
    • Lesson 3: Understanding Predicate Logic
    • Lesson 4: Understanding the Logical Order of Operations in SELECT
    • Lab: Introduction to T-SQL Querying

    Module 3: Writing SELECT Queries

    • Lesson 1: Writing Simple SELECT Statements
    • Lesson 2: Eliminating Duplicates with DISTINCT
    • Lesson 3: Using Column and Table Aliases
    • Lesson 4: Writing Simple CASE Expressions
    • Lab: Writing Basic SELECT Statements

    Module 4: Querying Multiple Tables

    • Lesson 1: Understanding Joins
    • Lesson 2: Querying with Inner Joins
    • Lesson 3: Querying with Outer Joins
    • Lesson 4: Querying with Cross Joins and Self Joins
    • Lab: Querying Multiple Tables

    Module 5: Sorting and Filtering Data

    • Lesson 1: Sorting Data
    • Lesson 2: Filtering Data with Predicates
    • Lesson 3: Filtering Data with TOP and OFFSET-FETCH
    • Lesson 4: Working with Unknown Values
    • Lab: Sorting and Filtering Data

    Module 6: Working with SQL Server 2016 Data Types

    • Lesson 1: Introducing SQL Server 2016 Data Types
    • Lesson 2: Working with Character Data
    •  Lesson 3: Working with Date and Time Data
    • Lab: Working with SQL Server 2016 Data Types

    Module 7: Using DML to Modify Data

    • Lesson 1: Adding Data to Tables
    • Lesson 2: Modifying and Removing Data
    • Lesson 3: Generating Automatic Column Values
    • Lab: Using DML to Modify Data

    Module 8: Using Built-In Functions

    • Lesson 1: Writing Queries with Built-In Functions
    • Lesson 2: Using Conversion Functions
    • Lesson 3: Using Logical Functions
    • Lesson 4: Using Functions to Work with NULL
    • Lab: Using Built-in Functions

    Module 9: Grouping and Aggregating Data

    • Lesson 1: Using Aggregate Functions
    • Lesson 2: Using the GROUP BY Clause
    • Lesson 3: Filtering Groups with HAVING
    • Lab: Grouping and Aggregating Data

    Module 10: Using Subqueries

    • Lesson 1: Writing Self-Contained Subqueries
    • Lesson 2: Writing Correlated Subqueries
    • Lesson 3: Using the EXISTS Predicate with Subqueries
    • Lab: Using Subqueries

    Module 11: Using Set Operators

    • Lesson 1: Writing Queries with the UNION Operator
    • Lesson 2: Using EXCEPT and INTERSECT
    • Lesson 3: Using APPLY
    • Lab: Using Set Operators

    Module 12: Using Table Expressions

    • Lesson 1: Using Derived Tables
    • Lesson 2: Using Common Table Expressions
    • Lesson 3: Using Views
    • Lesson 4: Using Inline Table-Valued Functions
    • Lab: Using Table Expressions

    Module 13: Using Window Ranking, Offset, and Aggregate Functions

    • Lesson 1: Creating Windows with OVER
    • Lesson 2: Exploring Window Functions
    • Lab: Using Window Ranking, Offset and Aggregate Functions

    Module 14: Pivoting and Grouping Sets

    • Lesson 1: Writing Queries with PIVOT and UNPIVOT
    • Lesson 2: Working with Grouping Sets
    • Lab: Pivoting and Grouping Sets

    Module 15: Executing Stored Procedures

    • Lesson 1: Querying Data with Stored Procedures
    • Lesson 2: Passing Parameters to Stored Procedures
    • Lesson 3: Creating Simple Stored Procedures
    • Lesson 4: Working with Dynamic SQL
    • Lab: Executing Stored Procedures

    Module 16: Programming with T-SQL

    • Lesson 1: T-SQL Programming Elements
    • Lesson 2: Controlling Program Flow
    • Lab: Programming with T-SQL
  • Dependencies

    Business Intelligence

    Business Intelligence

    SQL Server

    SQL Server

    CertificationMS

    CertificationMS
OKsystem a.s.
We use cookies to optimize site functionality and deliver best results based on your interests.