Training Schedule Training Schedule

(4395) Querying Data with Transact-SQL
Code: 4395 v12/8/2018
Type: null
Tuition (USD): $2995
Length: 5 Days
Have a question?

Contact Us

character(s) remaining

Location Date Guaranteed to Run
Live Virtual, US 03/25/2019
Chicago-Schaumburg, IL 04/08/2019
Live Virtual, US 04/08/2019
Live Virtual, US 04/22/2019
Live Virtual, US 04/29/2019
Morristown, NJ 05/13/2019
Live Virtual, US 05/13/2019
Live Virtual, US 06/03/2019
Live Virtual, US 06/17/2019
Live Virtual, US 06/24/2019
Irving, TX 06/24/2019

Not finding any suitable dates? Contact us for additional availability: training@dunnsolutions.com

Skills Gained:
1. Describe key capabilities and components of SQL Server. 2. Describe T-SQL, sets, and predicate logic. 3. Write a single table SELECT statement. 4. Write a multi-table SELECT statement. 5. Write SELECT statements with filtering and sorting. 6. Describe how SQL Server uses data types. 7. Write DML statements. 8. Write queries that use built-in functions. 9. Write queries that aggregate data. 10. Write subqueries. 11. Create and implement views and table-valued functions. 12. Use set operators to combine query results. 13. Write queries that use window ranking, offset, and aggregate functions. 14. Transform data by implementing pivot, unpivot, rollup and cube. 15. Create and implement stored procedures. 16. Add programming constructs such as variables, conditions, and loops to T-SQL code
Who Should Attend?
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.
Prerequisites:
  • Working knowledge of relational databases.
  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
What You Will Learn:
  • Classroom Live Outline
  • 1. Introduction to Microsoft SQL
  • The Basic Architecture of SQL Server
  • SQL Server Editions and Versions
  • Getting Started with SQL Server Management Studio
  • 2. Introduction to T-SQL Querying
  • Introducing T-SQL
  • Sets
  • Predicate Logic
  • Logical Order of Operations in SELECT statements
  • 3. Writing SELECT Queries
  • Writing Simple SELECT Statements
  • Eliminating Duplicates with DISTINCT
  • Using Column and Table Aliases
  • Writing Simple CASE Expressions
  • 4. Querying Multiple Tables
  • Joins
  • Querying with Inner Joins
  • Querying with Outer Joins
  • Querying with Cross Joins and Self Joins
  • 5. Sorting and Filtering Data
  • Sorting Data
  • Filtering Data with Predicates
  • Filtering with the TOP and OFFSET-FETCH Options
  • Working with Unknown Values
  • 6. Working with SQL Server 2016 Data Types
  • Introducing SQL Server 2016 Data Types
  • Working with Character Data
  • Working with Date and Time Data
  • 7. Using DML to Modify Data
  • Inserting Data
  • Modifying and Deleting Data
  • 8. Using Built-In Functions
  • Writing Queries with Built-In Functions
  • Using Conversion Functions
  • Using Logical Functions
  • Using Functions to Work with NULL
  • 9. Grouping and Aggregating Data
  • Using Aggregate Functions
  • Using the GROUP BY Clause
  • Filtering Groups with HAVING
  • 10. Using Subqueries
  • Writing Self-Contained Subqueries
  • Writing Correlated Subqueries
  • Using the EXISTS Predicate with Subqueries
  • 11. Using Set Operators
  • 12. Using Set
  • Writing Queries with the UNION Operator
  • Using EXCEPT and INTERSECT
  • Using APPLY
  • 13. Using Windows Ranking, Offset, and Aggregate
  • Creating Windows with OVER
  • Exploring Window Functions
  • 14. Pivoting and Grouping Sets
  • Writing Queries with PIVOT and UNPIVOT
  • Working with Grouping Sets
  • 15. Executing Stored
  • Querying Data with Stored Procedures
  • Passing Parameters to Stored Procedures
  • Creating Simple Stored Procedures
  • Working with Dynamic SQL
  • 16. Programming with T-SQL
  • T-SQL Programming Elements
  • Controlling Program Flow
  • 17. Implementing Error Handling
  • Implementing T-SQL Error Handling
  • Implementing Structured Exception Handling
  • 18. Implementing Transactions
  • Transactions and the Database Engines
  • Controlling Transactions
  • Classroom Live Labs
  • Lab 1: Working with SQL Server 2016 Tools
  • Lab 2: Introduction to Transact-SQL Querying
  • Lab 3: Writing Basic SELECT Statements
  • Lab 4: Querying Multiple Tables
  • Lab 5: Sorting and Filtering Data
  • Lab 6: Working with SQL Server 2016 Data Types
  • Lab 7: Using DML to Modify Data
  • Lab 8: Using Built-In Functions
  • Lab 9: Grouping and Aggregating Data
  • Lab 10: Using Subqueries
  • Lab 11: Using Table Expressions
  • Lab 12: Using SET Operators
  • Lab 13: Using Windows Ranking, Offset, and Aggregate
  • Lab 14: Pivoting and Grouping Sets
  • Lab 15: Executing Stored
  • Lab 16: Programming with T-SQL
  • Lab 17: Implementing Error Handling
  • Lab 18: Implementing Transactions
Course Info:
This course is designed to introduce students to Transact-SQL. It is designed in such a way that the first part of the content can be taught as a course to students requiring the knowledge for other courses in the SQL Server curriculum. The remaining content focuses on the skills required to take exam 70-761.

This is MOC for course 20761.

This course is also available in the On-Demand delivery format with digital Microsoft Official Courseware (dMOC).

RELATED CERTIFICATIONS:
Earning an MCSA: SQL 2016 Database Development certification validates your skills in building and implementing databases across organizations and will qualify you for a position as a database developer.

MCSA: SQL 2016 Database Development

Follow-On Courses:
Administering a SQL Database Infrastructure (M20764)
Implementing a SQL Data Warehouse (M20767)
Developing SQL Databases (M20762)

Training Exclusives

This course comes with 12 months access to the following benefits:

Practice Labs
24x7 Mentoring
Indexed Class Recordings
Unlimited Retakes
Digital Courseware

* Terms and conditions may apply. Learn more about our Microsoft training exclusives by contacting training@dunnsolutions.com

Live Virtual:
Get engaging and impactful live, instructor-led training, regardless of your location.

Our Virtual Classroom Live online training format combines premium skills development technologies and our industry-leading instructors, content, exercises and peer collaboration to ensure that you get the highest quality professional development experience possible. Gain the skills and expertise that matter from the convenience of your home, work or wherever you have an Internet connection.

Engage with your instructor and fellow students via a learning platform and course material designed to ensure a stimulating and productive skills development experience.

Choose from sessions across a variety of time zones for training options that suit your schedule. Save time, money and effort without sacrificing learning quality by accessing our expert-led online training from the convenience of your home, office or anywhere with an Internet connection.

Attend from your PC, Mac or any iOS/Android tablet or smartphone. Connect with the class through your device audio or via toll-free phone number†, depending on available technologies and your interaction preferences.