Querying Data with Transact-SQL Training

3 days (10:00 AM - 5:00 PM Eastern)

$1,295.00

Register for a live online class.

Details

Subjects Covered

Prerequisites

Setup Requirements

Details

Course Details

This intensive course will start with a quick overview of the SQL Server architecture, then dive right into using T-SQL. You will learn about batches and scripts, how to declare and use variables—including the inevitable data type conversions—and the rich supply of operators available. You will learn about some of the many built-in T-SQL functions for doing things like working with numbers and string manipulation, as well as global functions for getting state information. You will also learn about how to rank results using special functions and the OVER clause.

Subjects Covered

Querying Data with Transact-SQL

  • Introduction to SQL Server
    • SQL Server Architecture
    • Editions and Versions
    • Overview of Relational Database Organization
    • SQL Server Tools
    • Management Studio
  • T-SQL Querying
    • Components of T-SQL
      • Data Manipulation Language
      • Data Definition Language
      • Data Control Language
      • Transaction Control Language
    • Basic Syntax Rules
    • Specifying Objects
    • Query Batches
    • Generating Queries
    • Sets and Predicate Logic
    • Order of Operations in SELECT Statements
  • Writing Select Queries
    • Simple SELECT Statements
    • Discovering Database Structures
    • Irregular Identifiers
    • Computations
    • Column and Table Aliases
    • CASE Expressions
    • Using DISTINCT to Eliminate Duplicates
  • Querying Multiples Tables
    • Table Joins
    • Joines as a Filter
    • Table Aliases
    • Join Types
    • Self Joins
  • Sorting and Filtering Data
    • Sorting Data
    • ORDER BY Details
    • Filtering Data with Predicates
    • Testing Equality and Inequality
    • Ranges of Values
    • Logical Operators
    • Work with Unknown Values
  • SQL Server Data Types
    • Available Data Types
    • Approximate and Exact Numerics
    • Date and Time Data
    • String Data Types
    • Type Coercion
    • Character Data Type
    • Collations and Overriding Collation
    • Concatenation
    • Substrings
    • The LIKE Predicate
    • Date/Time Functions
    • Extracting Date Parts
    • Calculating Dates
  • Using DML to Modify Data
    • Inserting Data
    • Modifying Data
    • Deleting Data
    • Update and Delete Strategies
  • Using Built-in Functions
    • Using Built-in Functions in Queries
    • Scalar and Aggregate Functions
    • Conversion Functions
    • Logical Functions
  • Grouping and Aggregating Data
    • Aggregate Functions
    • The GROUP BY Clause
    • Grouping and Aggregation Rules
    • Filtering Groups with HAVING
    • Handling NULL Values
  • Using Subqueries
    • Selecting from Subqueries
    • Scalar Subqueries
    • Multi-Value Subqueries
    • Correlated Subqueries
    • Using EXISTS with Subqueries
  • Using Table Expressions
    • Database Views
    • Derived Tables
    • Inline Table-Valued Functions
    • Common Table Expressions
  • Using Set Operators
    • UNION Operator
    • INTERSECT and EXCEPT Operators
    • APPLY Operator
  • Ranking and Aggregating Functions
    • Creating Windows with OVER
    • ROW_NUMBER Function
    • RANK and DENSE_RANK
    • NTILE Function
    • Offset Functions
  • Pivoting and Grouping Sets
    • What is Pivoting?
    • Using PIVOT
    • Using UNPIVOT
  • Executing Stored Procedures
    • Querying Data with Stored Procedures
    • Passing Parameters to Stored Procedures
    • Creating Simple Stored Procedures
    • Working with Dynamic SQL
  • Programming with T-SQL
    • T-SQL Programming Elements
    • Using Variables
    • Conditionally Executing Statements
    • Looping over Statements
  • Error Handling
    • Errors
    • Structured Exception Handling
    • Exceptional Execution Flow
  • Transactions
    • How Transactions Work
    • Controlling Transactions
    • Nesting Transactions
    • Working with Grouping Sets

Prerequisites

Before Taking this Class

This course assumes that you have at least a basic familiarity with the concept of relational databases and a basic understanding of what SQL Server is and the high-level tools in it, as well as how to create and manage objects using Management Studio. You should also have a basic understanding of how SQL Server implements security, including its authentication and authorization schemes, and how to assign permissions on securable objects to principals.

Setup Requirements

Software/Setup For this Class

SQL Server 2012 or higher.

Onsite Training

Do you have five (5) or more people needing this class and want us to deliver it at your location?