5.00
(1 Rating)

90T20761: Introduction to Querying Data with Transact-SQL

By Dr. Garcia Categories: Microsoft, SQL Server
Wishlist Share
Share Course
Page Link
Share On Social Media

About Course

NOTE: This course is an updated version of the Microsoft Retired course:

MOC 20761-C: Querying Data with Transact-SQL

This course is based on the Microsoft Retired MOC 20761 and it follows the same outline and context.  In some cases the Content has change to allow for new security updates and better course structure.  However, this course is design to introduced  learners of all ages how to work and create solutions using DBMS Database Management Systems and the essentials of working with Transact-SQL.

It has been updated to be delivered in a five days instructor-led section or self-paced with digital instructor-lectures, as your trainer provider for the self-paced access the digital courseware. This course would allow students to pass examinations which follow the same pattern as the Microsoft exam 70-761.

Show More

Course Content

Module 1: Understanding SQL Server Technologies
Chapter Overview This module provides an overview of Microsoft® SQL Server®, the data management software that stores data securely. Before you start, it is helpful to understand the basic architecture of SQL Server, the different editions that are available, and a little about SQL Server Management Studio (SSMS). SSMS is one of the tools you use to connect to instances of SQL Server, write queries, and view data returned by your queries. After completing this module, you will be able to: Describe the architecture of SQL Server. Describe the different editions of SQL Server. Work with SSMS.

  • Understanding SQL Server basic Architecture.
  • Understanding the SQL Schemas and Relational Databases
  • History of SQL Server Editions and Versions
    13:05
  • Azure SQL Database
  • Getting Started with SQL Server Management Studio
  • Knowledge Check

Module 2: Understanding Query Writing with T-SQL
Transact-SQL, or T-SQL, is the language you will use to interact with Microsoft® SQL Server®. In this module, you will learn that T-SQL has many elements in common with other computer languages, such as commands, variables, loops, functions, and operators. You will also learn that designing your queries to take sets into account means SQL Server will perform at its best. To make the most of your effort in writing T-SQL, you will also learn the process and order by which SQL Server evaluates your queries. Understanding the logical order for operations of SELECT statements is vital to learning how to write effective queries. Objectives After completing this module, you will be able to describe: • The elements of T-SQL and their role in writing queries. • The use of sets in SQL Server. • The use of predicate logic in SQL Server. • The logical order of operations in SELECT statements.

Module 3: Writing Queries using T-SQL Statements
You can use the SELECT statement to query tables and views. It is likely that you will use the SELECT statement more than any other single statement in T-SQL. You can manipulate the data with SELECT to customize how SQL Server returns the results. This module introduces you to the fundamentals of the SELECT statement, focusing on queries against a single table. Objectives After completing this module, you will be able to: • Write simple SELECT statements. • Eliminate duplicates using the DISTINCT clause. • Use table and column aliases. • Write simple CASE expressions.

Module 4: Introduction to Joins

Module 5: Introduction T-SQL Clauses

Module 6: Introduction to Subqueries

Module 7: Understanding T-SQL Commands
Module Overview Transact-SQL (T-SQL) data manipulation language (DML) is the subset of the SQL Language that contains commands to add and modify data column values, within rows, within tables. In this module, you will learn the basics of using INSERT to add column values to rows within tables, using UPDATE to make changes to column values to rows within tables, and using DELETE to remove complete rows from tables. You can also use the TRUNCATE command to delete all rows within a table quickly, without incurring an overhead that protects accidental deletion of rows when using the DELETE statement. You will also learn how to generate sequences of numbers using the IDENTITY property of a column, in addition to the sequence object, which is a stand-alone object that can be applied to many columns—in the same or different tables—to gain consistency between identities within different tables. You can use the MERGE command to change existing columns within rows of a destination table, based on the values stored within a source table, and comparisons between the source and destination table contents. Objectives After completing this module, you will be able to: • Write T-SQL statements that insert column values into rows within the tables. • Write T-SQL statements that modify values in columns, within rows, within tables. • Write T-SQL statements that remove existing rows from tables. • Appreciate the importance of the WHERE clause when using data modification language (DML). • Appreciate T-SQL statements that automatically generate values for columns and see how this affects you when using DML. • Understand the use of the MERGE statement to compare and contrast two tables and direct different DML statements, based on their content comparisons.

Module 8: Using Built-In Functions
Module Overview In addition to retrieving data as it is stored in columns, you may have to compare or further manipulate values in your T-SQL queries. In this module, you will: • Learn about the many built-in functions in Microsoft® SQL Server® that provide data type conversion, comparison, and NULL handling. • Learn about the various types of functions in SQL Server and how they are categorized. • Work with scalar functions and see where they may be used in your queries. • Learn conversion functions for changing data between different data types, and how to write logical tests. • Learn how to work with NULLs, and use built-in functions to select non-NULL values, in addition to replacing certain values with NULL when applicable.

Module 9: Introduction to Groups and Aggregates
Module Overview In addition to row-at-a-time queries, you may need to summarize data to analyze it. Microsoft® SQL Server® provides built-in functions that can aggregate, or summarize, information across multiple rows. In this module, you will learn how to use aggregate functions. You will also learn how to use the GROUP BY and HAVING clauses to break up the data into groups for summarizing, and to filter the resulting groups. Objectives After completing this lesson, you will be able to: • List the built-in aggregate functions provided by SQL Server. • Write queries that use aggregate functions in a SELECT list to summarize all the rows in an input set. • Describe the use of the DISTINCT option in aggregate functions. • Write queries using aggregate functions that handle the presence of NULLs in source data.

Module 10: Using Subqueries
Module Overview At this point in the course, you have learned many aspects of the T-SQL SELECT statement, but each query you have written has been a single, self-contained statement. You can also use Microsoft® SQL Server® to nest one query within another—in other words, to form subqueries. In a subquery, the results of the inner query (subquery) are returned to the outer query. This can provide a great deal of flexibility for your query logic. In this module, you will learn to write several types of subqueries. Objectives After completing this module, you will be able to: • Describe the uses for queries that are nested within other queries. • Write self-contained subqueries that return scalar or multi-valued results. • Write correlated subqueries that return scalar or multi-valued results. • Use the EXISTS predicate to efficiently check for the existence of rows in a subquery.

Module 11: Introduction to Expressions and Operators
Module Overview Previously in this course, you learned about using subqueries as an expression that returned results to an outer calling query. Like subqueries, table expressions are query expressions, but table expressions extend this idea by allowing you to name them and work with the results as you would with data in any valid relational table. Microsoft® SQL Server® 2016 supports four types of table expressions: derived tables, common table expressions (CTEs), views, and inline table-valued functions (TVFs). In this module, you will learn to work with these forms of table expressions and how to use them to help create a modular approach to writing queries. Objectives After completing this module, you will be able to: • Create simple views and write queries against them. • Create simple inline TVFs and write queries against them. • Write queries that use derived tables. • Write queries that use CTEs.

Module 12: Introduction to Database Programming

Module 13: Implementing Transactions
As you continue to move past SELECT statements and into data modification operations with T-SQL, you should consider how to structure batches containing multiple modification statements, and those that might encounter errors. In this module, you will learn how to define transactions to control the behavior of batches of T-SQL statements submitted to Microsoft® SQL Server®. You will also learn how to determine whether a runtime error has occurred after work has begun, and whether the work needs to be undone. Objectives After completing this module, you will be able to: • Describe transactions and the differences between batches and transactions. • Describe batches and how they are handled by SQL Server. • Create and manage transactions with transaction control language (TCL) statements. • Use SET XACT_ABORT to define SQL Server's handling of transactions outside TRY/CATCH blocks.

Student Ratings & Reviews

5.0
Total 1 Rating
5
1 Rating
4
0 Rating
3
0 Rating
2
0 Rating
1
0 Rating
Dr. Garcia
Dr. Garcia
3 years ago
Dr. Garcia is a great Instructor