Querying Microsoft SQL Server 2000 with Transact-SQL

Course contentModule 1: Introduction to Transact-SQLThe following topics are covered in this module:

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

After completing this module, you will be able to:

• Differentiate between Transact-SQL andANSI-SQL
• Describe the basic types of Transact-SQL
• Describe the syntax elements of Transact-SQL

Module 2: Using Transact-SQL Querying ToolsThe following topics are covered in this module:

• SQLQuery Analyzer
• Using the Object Browser Tool inSQLQuery 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 ofSQLQuery Analyzer
• Describe how to use the Object Browser tool inSQLQuery Analyzer
• Describe how to use the templates inSQLQuery Analyzer
• Describe how to use the osql command-line utility
• Execute Transact-SQL statements in various ways

Module 3: Retrieving DataThe following topics are covered in this module:

• Retrieving Data by Using theSELECTStatement
• 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 theSELECTstatement
• Filter data by using different search conditions to use with theWHEREclause
• Format result sets
• Describe how queries are processed
• Describe performance considerations that affect retrieving data

Module 4: Grouping and Summarizing DataThe following topics are covered in this module:

• Listing theTOPn Values
• Using Aggregate Functions
• GROUPBY Fundamentals
• Generating Aggregate Values Within Result Sets
• Using theCOMPUTEandCOMPUTEBY Clauses
• Grouping and Summarizing Data

After completing this module, you will be able to:

• Use theTOPn 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 theGROUPBY andHAVINGclauses
• Generate summary data for a table by using aggregate functions with theGROUPBY clause and theROLLUPorCUBEoperator
• Generate control-break reports by using theCOMPUTEandCOMPUTEBY clauses

Module 5: Joining Multiple TablesThe 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 theUNIONoperator

Module 6: Working with SubqueriesThe 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 theEXISTSandNOTEXISTSClauses
• 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 DataThe 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
• WriteINSERT,DELETE, andUPDATEstatements to modify data in tables
• Describe performance considerations related to modifying data

Module 8: Querying Full-Text IndexesThe 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 ObjectsThe 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