The goal of this course is to provide students
with the technical skills required to write basic
Transact-SQL queries for Microsoft SQL Server
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.
| Microsoft Certified Professional Exams
This course will help the student prepare for the following Microsoft Certified Professional exam:
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
- The course materials, lectures, and lab exercises are in English. To benefit fully from our instruction, students need an understanding of the English language and completion of the prerequisites.
Module 1: Introduction to Transact-SQL
Module 2: Using Transact-SQL Querying Tools
- The Transact-SQL Programming Language
- Types of Transact-SQL Statements
- Transact-SQL Syntax Elements
- Using SQL Server Books Online
Module 3: Retrieving Data
- 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
Module 4: Grouping and Summarizing Data
- Retrieving Data by Using the SELECT Statement
- Filtering Data
- Formatting Result Sets
- How Queries Are Processed
- Performance Considerations
- Retrieving Data and Manipulating Result Sets
Module 5: Joining Multiple Tables
- 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
Module 6: Working with Subqueries
- Using Aliases for Table Names
- Combining Data from Multiple Tables
- Combining Multiple Result Sets
- Querying Multiple Tables
Module 7: Modifying Data
- 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
Module 8: Querying Full-Text Indexes
- Using Transactions
- Inserting Data
- Deleting Data
- Updating Data
- Performance Considerations
- Modifying Data
Module 9: Introduction to Programming Objects
- Introduction to Microsoft Search Service
- Microsoft Search Service Components
- Getting Information About Full-Text Indexes
- Writing Full-Text Queries
- Querying Full-Text Indexes
- 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