Querying Data with Transact-SQL

5 days
20761C - Version:1
Description
This course is designed to introduce students to Transact-SQL.
Intended audience
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.
▼Expand All

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • Module 9: Grouping and Aggregating Data
    • Using Aggregate Functions
    • Using the GROUP BY Clause
    • Filtering Groups with HAVING
    • Lab : Grouping and Aggregating Data

  • Module 10: Using Subqueries
    • Writing Self-Contained Subqueries
    • Writing Correlated Subqueries
    • Using the EXISTS Predicate with Subqueries
    • Lab : Using Subqueries

  • Module 11: Using Table Expressions
    • Using Views
    • Using Inline Table-Valued Functions
    • Using Derived Tables
    • Using Common Table Expressions
    • Lab : Using Table Expressions

  • Module 12: Using Set Operators
    • Writing Queries with the UNION operator
    • Using EXCEPT and INTERSECT
    • Using APPLY
    • Lab : Using Set Operators

  • Module 13: Using Windows Ranking, Offset, and Aggregate Functions
    • Creating Windows with OVER
    • Exploring Window Functions
    • Lab : Using Windows Ranking, Offset, and Aggregate Functions

  • Module 14: Pivoting and Grouping Sets
    • Writing Queries with PIVOT and UNPIVOT
    • Working with Grouping Sets
    • Lab : Pivoting and Grouping Sets

  • 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

  • Module 16: Programming with T-SQL
    • T-SQL Programming Elements
    • Controlling Program Flow
    • Lab : Programming with T-SQL

  • Module 17: Implementing Error Handling
    • Implementing T-SQL error handling
    • Implementing structured exception handling
    • Lab : Implementing Error Handling

  • 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.
  • 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.
Contact Us
03-6176666
03-6176677 info@sela.co.il

SEND