Querying Data with Transact-SQL Training

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

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.

Software/Setup For this Class

SQL Server 2012 or higher.