Querying Microsoft SQL Server 2000 with Transact-SQL by etr47810

VIEWS: 8 PAGES: 2

									                                                 Querying Microsoft SQL Server 2000 with
                                                 Transact-SQL
                                                 Course length: 3 days                   Course number: M2071




Course Description                                                • The course materials, lectures, and lab exercises are
                                                                    in English. To benefit fully from our instruction,
Introduction                                                        students need an understanding of the English
                                                                    language and completion of the prerequisites.
The goal of this course is to provide students with the
technical skills required to write basic Transact-SQL
queries for Microsoft SQL Server™ 2000.                         Course Outline
Audience                                                        Module 1: Introduction to Transact-SQL
This course is intended for SQL Server database                 The following topics are covered in this module:
administrators, implementers, system engineers, and               • The Transact-SQL Programming Language
developers who are responsible for writing queries.               • Types of Transact-SQL Statements
                                                                  • Transact-SQL Syntax Elements
At Course Completion                                              • Using SQL Server Books Online
After completing the course, students will be able to:
  • Describe the uses of and ways to execute the                After completing this module, you will be able to:
    Transact-SQL language.                                        • Differentiate between Transact-SQL and ANSI-SQL.
  • Use querying tools.                                           • Describe the basic types of Transact-SQL.
  • Write SELECT queries to retrieve data.                        • Describe the syntax elements of Transact-SQL.
  • Group and summarize data by using Transact-SQL.
  • Join data from multiple tables.                             Module 2: Using Transact-SQL Querying Tools
  • Write queries that retrieve and modify data by using        The following topics are covered in this module:
    subqueries.                                                   • SQL Query Analyzer
  • Modify data in tables.                                        • Using the Object Browser Tool in SQL Query Analyzer
  • Query text fields with full-text search.                      • Using the osql Utility
  • Describe how to create programming objects.                   • Executing Transact-SQL Statements
                                                                  • Creating and Executing Transact-SQL Scripts
Microsoft Certified Professional Exams
This course will help the student prepare for the following     After completing this module, you will be able to:
Microsoft Certified Professional exam:                            • Describe the basic functions of SQL Query Analyzer.
  • Exam 70-229, Designing and Implementing                       • Describe how to use the Object Browser tool in SQL
    Databases with Microsoft SQL Server 2000 Enterprise             Query Analyzer.
    Edition                                                       • Describe how to use the templates in SQL Query
                                                                    Analyzer.
Prerequisites                                                     • Describe how to use the osql command-line utility.
Before attending this course, students must have:                 • Execute Transact-SQL statements in various ways.
  • Experience using a Microsoft Windows® operating
    system.                                                     Module 3: Retrieving Data
  • An understanding of basic relational database               The following topics are covered in this module:
    concepts, including:                                          • Retrieving Data by Using the SELECT Statement
  • Logical and physical database design.                         • Filtering Data
  • Data integrity concepts.                                      • Formatting Result Sets
  • Relationships between tables and columns (primary             • How Queries Are Processed
    key and foreign key, one-to-one, one-to-many, and             • Performance Considerations
    many-to-many).                                                • Retrieving Data and Manipulating Result Sets
  • How data is stored in tables (rows and columns).
  • Familiarity with the role of the database administrator     After completing this module, you will be able to:
                            Inacom Education Center ♦ 3001 West Beltline Hwy ♦ Madison, WI 53713
                              Tel: 608 661-7788 ♦ Fax: 608 661-7701 ♦ Web: www.inacom-msn.com
M2071.doc                                                                                                          Page 1 of 2
  • Retrieve data from tables by using the SELECT                 • Describe when and how to use a subquery.
    statement.                                                    • Use subqueries to break down and perform complex
  • Filter data by using different search conditions to use         queries.
    with the WHERE clause.
  • Format result sets.                                         Module 7: Modifying Data
  • Describe how queries are processed.                         The following topics are covered in this module:
  • Describe performance considerations that affect               • Using Transactions
    retrieving data.                                              • Inserting Data
                                                                  • Deleting Data
Module 4: Grouping and Summarizing Data                           • Updating Data
The following topics are covered in this module:                  • Performance Considerations
  • Listing the TOP n Values                                      • Modifying Data
  • Using Aggregate Functions
  • GROUP BY Fundamentals                                       After completing this module, you will be able to:
  • Generating Aggregate Values Within Result Sets                • Describe how transactions work.
  • Using the COMPUTE and COMPUTE BY Clauses                      • Write INSERT, DELETE, and UPDATE statements to
  • Grouping and Summarizing Data                                   modify data in tables.
                                                                  • Describe performance considerations related to
After completing this module, you will be able to:                  modifying data.
  • Use the TOP n keyword to retrieve a list of the
    specified top values in a table.                            Module 8: Querying Full-Text Indexes
  • Generate a single summary value by using aggregate          The following topics are covered in this module:
    functions.                                                    • Introduction to Microsoft Search Service
  • Organize summary data for a column by using                   • Microsoft Search Service Components
    aggregate functions with the GROUP BY and HAVING              • Getting Information About Full-Text Indexes
    clauses.                                                      • Writing Full-Text Queries
  • Generate summary data for a table by using aggregate          • Querying Full-Text Indexes
    functions with the GROUP BY clause and the
    ROLLUP or CUBE operator.                                    After completing this module, you will be able to:
  • Generate control-break reports by using the                   • Describe Microsoft Search service function and
    COMPUTE and COMPUTE BY clauses.                                 components.
                                                                  • Write full-text queries.
Module 5: Joining Multiple Tables                                 • Get information about full-text indexes.
The following topics are covered in this module:
  • Using Aliases for Table Names                               Module 9: Introduction to Programming Objects
  • Combining Data from Multiple Tables                         The following topics are covered in this module:
  • Combining Multiple Result Sets                                • Displaying the Text of a Programming Object
  • Querying Multiple Tables                                      • Introduction to Views
                                                                  • Advantages of Views
After completing this module, you will be able to:
                                                                  • Creating Views
  • Use aliases for table names.
                                                                  • Introduction to Stored Procedures
  • Combine data from two or more tables by using joins.
                                                                  • Introduction to Triggers
  • Combine multiple result sets into one result set by
                                                                  • Introduction to User-defined Functions
    using the UNION operator.
                                                                  • Working with Views
Module 6: Working with Subqueries
                                                                After completing this module, you will be able to:
The following topics are covered in this module:                  • Display the text of a programming object.
  • Introduction to Subqueries                                    • Describe the concepts of views.
  • Using a Subquery as a Derived Table                           • List the advantages of views.
  • Using a Subquery as an Expression                             • Describe stored procedures.
  • Using a Subquery to Correlate Data                            • Describe triggers.
  • Using the EXISTS and NOT EXISTS Clauses                       • Describe user defined functions.
  • Working with Subqueries

After completing this module, you will be able to:

                            Inacom Education Center ♦ 3001 West Beltline Hwy ♦ Madison, WI 53713
                              Tel: 608 661-7788 ♦ Fax: 608 661-7701 ♦ Web: www.inacom-msn.com
M2071.doc                                                                                                          Page 2 of 2

								
To top