Introduction
The goal of
this course is to provide students with the
technical skills required to write basic
Transact-SQL queries for Microsoft SQL Server.
Audience
This course is
intended for SQL Server business users, database administrators,
implementers, system engineers, and developers
who are responsible for writing queries.
At Course
Completion
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. |
Prerequisites
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. |
Student
Materials
The student kit
includes a comprehensive workbook and other
necessary materials for this class. The
following software is provided in the student
kit:
|
• |
Microsoft
SQL Server, Enterprise Edition
Evaluation Copy |
Course Outline
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. |
