After completing the course, students will be able to:

• Describe the 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.
• Describe how to create programming objects.

Before attending this course, students must have:

• Experience using a Microsoft Windows^® operating system.
• An understanding of basic relational database concepts, including:
  • Logical and physical database design.
  • Data integrity concepts.
  • Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, and many-to-many).
  • How data is stored in tables (rows and columns).
• Familiarity with the role of the database administrator

Module 1: Introduction to Transact-SQL

The following topics are covered in this module:

• The Transact-SQL Programming Language
• Types of Transact-SQL Statements
• Transact-SQL Syntax Elements
• Using SQL Server Books Online

After completing this module, you will be able to:

• Differentiate between Transact-SQL and ANSI-SQL.
• Describe the basic types of Transact-SQL.
• Describe the syntax elements of Transact-SQL.

Module 2: Using Transact-SQL Querying Tools

The following topics are covered in this module:

• SQL Query Analyzer
• Using the Object Browser Tool in SQL Query Analyzer
• Using the osql Utility
• Executing Transact-SQL Statements
• Creating and Executing Transact-SQL Scripts

After completing this module, you will be able to:

• Describe the basic functions of SQL Query Analyzer.
• Describe how to use the Object Browser tool in SQL Query Analyzer.
• Describe how to use the templates in SQL Query Analyzer.
• Describe how to use the osql command-line utility.
• Execute Transact-SQL statements in various ways.

Module 3: Retrieving Data

The following topics are covered in this module:

• Retrieving Data by Using the SELECT Statement
• Filtering Data
• Formatting Result Sets
• How Queries Are Processed
• Performance Considerations
• Retrieving Data and Manipulating Result Sets

After completing this module, you will be able to:

• Retrieve data from tables by using the SELECT statement.
• Filter data by using different search conditions to use with the WHERE clause.
• Format result sets.
• Describe how queries are processed.
• Describe performance considerations that affect retrieving data.

Module 4: Grouping and Summarizing Data

The following topics are covered in this module:

• Listing the TOP n Values
• Using Aggregate Functions
• GROUP BY Fundamentals
• Generating Aggregate Values Within Result Sets
• Using the COMPUTE and COMPUTE BY Clauses
• Grouping and Summarizing Data

After completing this module, you will be able to:

• Use the TOP n keyword to retrieve a list of the specified top values in a table.
• Generate a single summary value by using aggregate functions.
• Organize summary data for a column by using aggregate functions with the GROUP BY and HAVING clauses.
• Generate summary data for a table by using aggregate functions with the GROUP BY clause and the ROLLUP or CUBE operator.
• Generate control-break reports by using the COMPUTE and COMPUTE BY clauses.

Module 5: Joining Multiple Tables

The following topics are covered in this module:

• Using Aliases for Table Names
• Combining Data from Multiple Tables
• Combining Multiple Result Sets
• Querying Multiple Tables

After completing this module, you will be able to:

• Use aliases for table names.
• Combine data from two or more tables by using joins.
• Combine multiple result sets into one result set by using the UNION operator.

Module 6: Working with Subqueries

The following topics are covered in this module:

• Introduction to Subqueries
• Using a Subquery as a Derived Table
• Using a Subquery as an Expression
• Using a Subquery to Correlate Data
• Using the EXISTS and NOT EXISTS Clauses
• Working with Subqueries

After completing this module, you will be able to:

• Describe when and how to use a subquery.
• Use subqueries to break down and perform complex queries.

Module 7: Modifying Data

The following topics are covered in this module:

• Using Transactions
• Inserting Data
• Deleting Data
• Updating Data
• Performance Considerations
• Modifying Data

After completing this module, you will be able to:

• Describe how transactions work.
• Write INSERT, DELETE, and UPDATE statements to modify data in tables.
• Describe performance considerations related to modifying data.

Module 8: Querying Full-Text Indexes

The following topics are covered in this module:

• Introduction to Microsoft Search Service
• Microsoft Search Service Components
• Getting Information About Full-Text Indexes
• Writing Full-Text Queries
• Querying Full-Text Indexes

After completing this module, you will be able to:

• Describe Microsoft Search service function and components.
• Write full-text queries.
• Get information about full-text indexes.

Module 9: Introduction to Programming Objects

The following topics are covered in this module:

• Displaying the Text of a Programming Object
• Introduction to Views
• Advantages of Views
• Creating Views
• Introduction to Stored Procedures
• Introduction to Triggers
• Introduction to User-defined Functions
• Working with Views

After completing this module, you will be able to:

• Display the text of a programming object.
• Describe the concepts of views.
• List the advantages of views.
• Describe stored procedures.
• Describe triggers.
• Describe user defined functions.