Writing Queries Using Microsoft SQL Server 2008 Transact-SQL
Course # 2778
Course Starts On: January 29, 2014 - May 28, 2014 -
Length of the Course: 3 Days
Price: $1,575 – Click Here to Pre Register
Course Description
This 3-day instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2008.
Course Objectives:
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.
Course Audience:
This course is intended for SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries.
Course 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 2008Lessons
- 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
Module 2: Querying and Filtering DataLessons
- 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
Module 3: Grouping and Summarizing DataLessons
- 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
Module 4: Joining Data from Multiple TablesLessons
- 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
Module 5: Working with SubqueriesLessons
- 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
Module 6: Modifying Data in TablesLessons
-
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
Module 7: Querying Metadata, XML, and Full-Text IndexesLessons
- 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
Module 8: Using Programming Objects for Data RetrievalLessons
- 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
Module 9: Using Advanced Querying TechniquesLessons
- 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
- 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.
- 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.
- Query metadata.
- Describe the functionality of XML.
- Query XML data.
- Describe the functionality of full-text indexes.
- Query full-text indexes.
- Insert data into tables.
- Delete data from tables.
- Update data in tables.
- Describe transactions.
- Write basic subqueries.
- Write correlated subqueries.
- Compare subqueries with joins and temporary tables.
- Use common table expressions in queries.
- Query multiple tables by using joins.
- Apply joins for typical reporting needs.
- Combine and limit result sets.
- 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.
- 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.
- 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.