Writing
Queries Using Microsoft SQL Server 2008 Transact-SQL
Course 2778: Three days;
instructor led
|
About this Course This 3-day instructor led course provides students with the technical
skills required to write basic Transact-SQL queries for Microsoft SQL Server
2008. |
|
Audience Profile This course is intended for SQL Server database administrators,
implementers, system engineers, and developers who are responsible for
writing queries. |
|
At Course Completion After completing this course, students will be able to: ·
Describe
the uses of and ways to execute the Transact-SQL language. ·
Use
querying tool. ·
Write
SELECT queries to retrieve data. ·
Group and
summarize data by using Transact-SQL. ·
Join data
from multiple tables. ·
Write
queries that retrieve and modify data by using subqueries. ·
Modify
data in tables. ·
Query
text fields with full-text search. ·
Describe
how to create programming objects. ·
Use
various techniques when working with complex queries. Prerequisites Before attending this course, students must have: ·
Knowledge
of data integrity concepts. ·
Core
Windows Server skills. ·
Relational
database design skills. ·
Programming
skills. Course Outline Module 1: Getting Started with Databases and Transact-SQL in SQL
Server 2008 The student will be introduced to how client/server architecture
works, and examine the various database and business tasks that can be
performed by using the components of SQL Server 2008. The student will also
be introduced to SQL Server database concepts such as relational databases,
normalization, and database objects. In addition, the student will learn how
to use T-SQL to query databases and generate reports. Lessons ·
Overview of SQL Server 2008 ·
Overview of SQL Server Databases ·
Overview and Syntax Elements of T-SQL ·
Working with T-SQL Scripts ·
Using T-SQL Querying Tools Lab : Using SQL Server Management Studio and SQLCMD ·
Exploring the Components and Executing Queries in SQL Server
Management Studio ·
Starting and Using SQLCMD ·
Generating a Report from a SQL Server Database Using Microsoft
Office Excel After completing this module, students will be able to: ·
Describe
the architecture and components of SQL Server 2008. ·
Describe
the structure of a SQL Server database. ·
Explain
the basics of the SQL language. ·
Describe
the syntax elements of T-SQL. ·
Explain
how to manage T-SQL scripts. ·
Use T-SQL
querying tools to query SQL Server 2008 databases. Module 2: Querying and Filtering Data The students will be introduced to the basic Transact-SQL (T-SQL)
statements that are used for writing queries, filtering data, and formatting
result sets. Lessons ·
Using the SELECT Statement ·
Filtering Data ·
Working with NULL Values ·
Formatting Result Sets ·
Performance Considerations for Writing Queries Lab : Querying and Filtering Data ·
Retrieving Data by Using the SELECT Statement ·
Filtering Data by Using Different Search Conditions ·
Using Functions to Work with NULL Values ·
Formatting Result Sets After completing this module, students will be able to: ·
Retrieve
data by using the SELECT statement. ·
Filter
data by using different search conditions. ·
Explain
how to work with NULL values. ·
Format
result sets. ·
Describe
the performance considerations that affect data retrieval. Module 3: Grouping and Summarizing Data The students will learn to group and summarize data when generating
reports in Microsoft SQL Server 2008 by using aggregate functions and the
COMPUTE clause. Lessons ·
Summarizing Data by Using Aggregate Functions ·
Summarizing Grouped Data ·
Ranking Grouped Data ·
Creating Crosstab Queries Lab : Grouping and Summarizing Data ·
Summarizing Data by Using Aggregate Functions ·
Summarizing Grouped Data ·
Ranking Grouped Data ·
Creating Crosstab Queries After completing this module, students will be able to: ·
Summarize
data by using aggregate functions. ·
Summarize
grouped data by using the GROUP BY and COMPUTE clauses. ·
Rank
grouped data. ·
Create
cross-tabulation queries by using the PIVOT and UNPIVOT clauses. Module 4: Joining Data from Multiple Tables The students will learn to write joins to query multiple tables, as
well as limiting and combining result sets. Lessons ·
Querying Multiple Tables by Using Joins ·
Applying Joins for Typical Reporting Needs ·
Combining and Limiting Result Set Lab : Joining Data from Multiple Tables ·
Querying Multiple Tables by Using Joins ·
Applying Joins for Typical Reporting Needs ·
Combining and Limiting Result Sets After completing this module, students will be able to: ·
Query
multiple tables by using joins. ·
Apply
joins for typical reporting needs. ·
Combine
and limit result sets. Module 5: Working with Subqueries The students will be introduced to basic and correlated subqueries and
how these compare with joins and temporary tables. The students will also be
introduced to using common table expressions in queries. Lessons ·
Writing Basic Subqueries ·
Writing Correlated Subqueries ·
Comparing Subqueries with Joins and Temporary Tables ·
Using Common Table Expressions Lab : Working with Subqueries ·
Writing Basic Subqueries ·
Writing Correlated Subqueries ·
Comparing Subqueries with Joins and Temporary Tables ·
Using Common Table Expressions After completing this module, students will be able to: ·
Write
basic subqueries. ·
Write
correlated subqueries. ·
Compare
subqueries with joins and temporary tables. ·
Use
common table expressions in queries. Module 6: Modifying Data in Tables The students will be able to modify the data in tables by using the
INSERT, DELETE, and UPDATE statements. In addition, students will examine how
transactions work in a database, the importance of transaction isolation
levels, and how to manage transactions. Lessons ·
Inserting Data into Tables ·
Deleting Data from Tables ·
Updating Data in Tables ·
Overview of Transactions Lab : Modifying Data in Tables ·
Inserting Data into Tables ·
Deleting Data from Tables ·
Updating Data in Tables ·
Working with Transactions After completing this module, students will be able to: ·
Insert
data into tables. ·
Delete
data from tables. ·
Update
data in tables. ·
Describe
transactions. Module 7: Querying Metadata, XML, and Full-Text Indexes The students will learn to query semi-structured and unstructured
data. The students will also learn how SQL Server 2008 handles XML data and
will query XML data. The students will also be introduced to full-text
indexing in SQL Server 2008. Lessons ·
Querying Metadata ·
Overview of XML ·
Querying XML Data ·
Overview of Full-Text Indexes ·
Querying Full-Text Indexes Lab : Querying Metadata, XML, and Full-Text Indexes ·
Querying Metadata ·
Querying XML Data ·
Creating and Querying Full-Text Indexes After completing this module, students will be able to: ·
Query
metadata. ·
Describe
the functionality of XML. ·
Query XML
data. ·
Describe
the functionality of full-text indexes. ·
Query
full-text indexes. Module 8: Using Programming Objects for Data Retrieval The students will be introduced to user-defined functions and
executing various kinds of queries by using user-defined functions. The
students will be introduced to SQL Server views that encapsulate data and
present users with limited and relevant information. In addition, the
students will be introduced to SQL Server stored procedures and the
functionalities of the various programming objects. The students will learn
how to perform distributed queries and how SQL Server works with
heterogeneous data such as databases, spreadsheets, and other servers. Lessons ·
Overview of Views ·
Overview of User-Defined Functions ·
Overview of Stored Procedures ·
Overview of Triggers ·
Writing Distributed Queries Lab : Using Programming Objects for Data Retrieval ·
Creating Views ·
Creating User-Defined Functions ·
Creating Stored Procedures ·
Writing Distributed Queries After completing this module, students will be able to: ·
Encapsulate
queries by using views. ·
Encapsulate
expressions by using user-defined functions. ·
Explain
how stored procedures encapsulate T-SQL logic. ·
Define
triggers, types of triggers, create a trigger. ·
Write
distributed queries. Module 9: Using Advanced Querying Techniques The students will be introduced to best practices for querying complex
data. The students will also examine how to query complex table structures
such as data stored in hierarchies and self-referencing tables. The students
will analyze the recommended guidelines for executing queries and how to
optimize query performance. Lessons ·
Considerations for Querying Data ·
Working with Data Types ·
Cursors and Set-Based Queries ·
Dynamic SQL ·
Maintaining Query Files Lab : Using Advanced Querying Techniques ·
Using Execution Plans ·
Converting Data Types ·
Implementing a Hierarchy ·
Using Cursors and Set-Based Queries After completing this module, students will be able to: ·
Explain
the recommendations for querying complex data. ·
Query
complex table structures. ·
Write
efficient queries. ·
Use
various techniques when working with complex queries. ·
Maintain
query files. Solutient
Corporation of Ohio 6133
Rockside Road, Suite 100 – Cleveland, OH
44131 FOR MORE
INFORMATION CALL 216-654-0025 |