Query and Developing SQL Server Data with Traction SQL (T-SQL)

I.       Overview:

The main purpose of this 6 day instructor led 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. The course will very likely be well attended by SQL power users who aren’t necessarily database-focused; namely, report writers, business analysts and client application developers.

II.    Duration: 06 days (48 hours)
III. Objectives:

After completing this course, students will be able to:

  • Describe the basic architecture and concepts of Microsoft SQL Server 2016
  • Understand the similarities and differences between Transact-SQL and other computer languages
  • 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 table expressions
  • Use set operators
  • Execute stored procedures.
  • Program with T-SQL
  • Implement error handling
  • Implement transactions.
  • Design and Implement Tables.
  • Describe advanced table designs.
  • Ensure Data Integrity through Constraints.
  • Design and Implement Views.
  • Design and Implement Stored Procedures.
  • Design and Implement User Defined Functions
IV. Intended Audience:

This course is intended for Database Administrators, Database Developers, The course will very likely be well attended by SQL power users who aren’t necessarily database-focused; namely, report writers, business analysts and client application developers.

V.    Prerequisites:

This course requires that you meet the following prerequisites:

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

Module 1: 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 2: 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 3: 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 4: 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 5: Working with SQL Server 2016 Data Types

  • Introducing SQL Server Data Types
  • Working with Character Data
  • Working with Date and Time Data
  • Lab: Working with SQL Server 2016 Data Types

Module 6: Using DML to Modify Data

  • Adding Data to Tables
  • Modifying and Removing Data
  • Generating Automatic Column Values
  • Using DML to Modify Data

Module 7: 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 8: Grouping and Aggregating Data

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

Module 9: Using Subqueries

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

Module 10: Using Table Expressions

  • Using Views
  • Using Inline TVFs
  • Using Derived Tables
  • Using CTEs
  • Lab: Using Table Expressions

Module 11: Using Set Operators

  • Writing Queries with the UNION Operator
  • Using APPLY
  • Lab: Using Set Operators

Module 12: 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 13: Programming with T-SQL

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

Module 14: Implementing Error Handling

  • Implementing T-SQL Error Handling
  • Implementing Structured Exception Handling
  • Lab: Implementing Error Handling

Module 15: Implementing Transactions

  • Transactions and the Database Engine
  • Controlling Transactions
  • Lab: Implementing Transactions

Module 16: Designing and Implementing Tables

Describes the design and implementation of tables. (Note: partitioned tables are not covered)

  • Designing Tables
  • Data Types
  • Working with Schemas
  • Creating and Altering Tables
  • Lab: Designing and Implementing Tables

Module 17: Advanced Table Designs

Describes more advanced table designs.

  • Partitioning Data
  • Compressing Data
  • Temporal Tables
  • Lab: Using Advanced Table Designs

Module 18: Ensuring Data Integrity through Constraints

Describes the design and implementation of constraints.

  • Enforcing Data Integrity
  • Implementing Data Domain Integrity
  • Implementing Entity and Referential Integrity
  • Lab: Using Data Integrity Through Constraints

Module 19: Introduction to Indexes

Describes the concept of an index and discusses selectivity, density and statistics. It covers appropriate data type choices and choices around composite index structures.

  • Core Indexing Concepts
  • Data Types and Indexes
  • Heaps, Clustered, and Nonclustered Indexes
  • Single Column and Composite Indexes
  • Lab: Implementing Indexes

Module 20: Designing and Implementing Views

Describes the design and implementation of views.

  • Introduction to Views
  • Creating and Managing Views
  • Performance Considerations for Views
  • Lab: Designing and Implementing Views

Module 21: Designing and Implementing Stored Procedures

Describes the design and implementation of stored procedures.

  • Introduction to Stored Procedures
  • Working with Stored Procedures
  • Implementing Parameterized Stored Procedures
  • Controlling Execution Context
  • Lab: Designing and Implementing Stored Procedures

Module 22: Designing and Implementing User-Defined Functions

Describes the design and implementation of functions, both scalar and table-valued. (Also discusses where they can lead to performance issues)

  • Overview of Functions
  • Designing and Implementing Scalar Functions
  • Designing and Implementing Table-Valued Functions
  • Considerations for Implementing Functions
  • Alternatives to Functions
  • Lab: Designing and Implementing User-Defined Functions
  • Học trực tuyến

  • Học tại Hồ Chí Minh

  • Học tại Hà Nội

Các khóa học khác