Gain the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2008 in this course that incorporates material form the Official Microsoft Learning Product (OMLP) 2778: Writing Queries Using Microsoft SQL Server 2008 Transact-SQL.
What You'll Learn
Uses of and ways to execute the Transact-SQL language
Use querying tools
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
Create programming objects
Use various techniques when working with complex queries
Course Outline
-
1. Getting Started with Databases and Transact-SQL in SQL Server 2008
- 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
2. Querying and Filtering Data
- Using the SELECT Statement
- Filtering Data
- Working with NULL Values
- Formatting Result Sets
- Performance Considerations for Writing Queries
3. Grouping and Summarizing Data
- Summarizing Data by Using Aggregate Functions
- Summarizing Grouped Data
- Ranking Grouped Data
- Creating Crosstab Queries
4. Joining Data from Multiple Tables
- Querying Multiple Tables by Using Joins
- Applying Joins for Typical Reporting Needs
- Combining and Limiting Result Set
5. Working with Subqueries
- Writing Basic Subqueries
- Writing Correlated Subqueries
- Comparing Subqueries with Joins and Temporary Tables
- Using Common Table Expressions
6. Modifying Data in Tables
- Inserting Data into Tables
- Deleting Data from Tables
- Updating Data in Tables
- Overview of Transactions
7. Querying Metadata, XML, and Full-Text Indexes
- Querying Metadata
- Overview of XML
- Querying XML Data
- Overview of Full-Text Indexes
- Querying Full-Text Indexes
8. Using Programming Objects for Data Retrieval
- Overview of Views
- Overview of User-Defined Functions
- Overview of Stored Procedures
- Overview of Triggers
- Writing Distributed Queries
9. Using Advanced Querying Techniques
- Considerations for Querying Complex Data
- Working with Data Types
- Cursors and Set-Based Queries
- Dynamic SQL
- Maintaining Query Files
Labs
Lab 1: Getting Started with Databases and Transact-SQL in SQL Server 2008
- 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
Lab 2: Querying and Filtering Data
- Retrieving Data by Using the SELECT Statement
- Filtering Data by Using Search Conditions
- Using Functions to Work with NULL Values
- Formatting Result Sets
Lab 3: Grouping and Summarizing Data
- Summarizing Data by Using Aggregate Functions
- Summarizing Grouped Data
- Ranking Grouped Data
- Creating Crosstab Queries
Lab 4: Joining Data from Multiple Tables
- Querying Multiple Tables by Using Joins
- Applying Joins for Typical Reporting Needs
- Combining and Limiting Result Sets
Lab 5: Working with Subqueries
- Writing Basic Subqueries
- Writing Correlated Subqueries
- Comparing Subqueries with Joins and Temporary Tables
- Using Common Table Expressions
Lab 6: Modifying Data in Tables
- Inserting Data into Tables
- Deleting Data from Tables
- Updating Data in Tables
- Working with Transactions
Lab 7: Querying Metadata, XML, and Full-Text Indexes
- Querying Metadata
- Querying XML Data
- Creating and Querying Full-Text Indexes
Lab 8: Using Programming Objects for Data Retrieval
- Creating Views
- Creating User-Defined Functions
- Creating Stored Procedures
- Writing Distributed Queries
Lab 9: Using Advanced Querying Techniques
- Using Execution Plans
- Converting Data Types
- Implementing a Hierarchy
- Using Cursors and Set-Based Queries
