Course number HG794S

Delivery method
Remotely assisted instructional learning (RAIL)
Instructor-led training (ILT)
Onsite dedicated training (OST)

Special notes
Elements of this syllabus are subject to change.

Prerequisites
Before attending this course, students must have:

• Logical database design
• Physical database design
• How data is stored in tables (rows and columns)
• Data integrity concepts
• Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, and many-to-many)
• Basic knowledge of the Microsoft Windows operating system and its core functionality. For example, how to use Windows Explorer, open and save files, and what a client/server application interaction means

Audience
This course is intended for SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries

Course objectives
After completing this 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
• Use various techniques when working with complex queries

Course outline

Module 1: Getting Started with Databases and Transact-SQL in SQL Server 2008

Lessons:

• Overview of SQL Server 2008
• Overview of SQL Server Databases
• Overview of the SQL Language
• Syntax Elements of T-SQL
• Working with T-SQL Scripts
• Using T-SQL Querying Tools

Lab: Getting Started with Databases and Transact-SQL in SQL Server 2008

• (Level 200) Exploring SQL Server Management Studio
• (Level 200) Executing Queries in SQL Server Management Studio
• (Level 200) Examining a Database Diagram in SQL Server Management Studio
• (Level 200) Using Excel to Generate a Report from a SQL Server Database

Module 2: Querying and Filtering Data

Lessons:

• Using the SELECT Statement
• Filtering Data
• Working with NULL Values
• Formatting Result Sets
• Performance Considerations for Writing Queries

Lab: Querying and Filtering Data

• (Level 200): Retrieving Data by Using the SELECT Statement
• (Level 200): Filtering Data by Using Search Conditions
• (Level 200) Using Functions to Work with NULL Values
• (Level 200) Formatting Result Sets
• (Level 200) Rewriting Queries for Performance

Module 3: Grouping and Summarizing Data

Lessons:

• Summarizing Data by Using Aggregate Function
• Summarizing Grouped Data
• Ranking Grouped Data
• Creating Crosstab Queries

Lab: Grouping and Summarizing Data

• (Level 200) Summarizing Data by Using Aggregate Functions
• (Level 200) Summarizing Grouped Data
• (Level 200) Ranking Grouped Data
• (Level 200) Creating Crosstab Queries

Module 4: Joining Data from Multiple Tables

Lessons:

• Querying Multiple Tables by Using Joins
• Applying Joins for Typical Reporting Needs
• Combining and Limiting Result Set

Lab: Joining Data from Multiple Tables

• (Level 200) Querying Multiple Tables by Using Joins
• (Level 200) Applying Joins for Typical Reporting Needs
• (Level 200) Combining and Limiting Result Sets

Module 5: Working with Subqueries

Lessons:

• Writing Basic Subqueries
• Writing Correlated Subqueries
• Comparing Subqueries with Joins and Temporary Tables
• Using Common Table Expressions

Lab: Working with Subqueries

• (Level 200) Writing Basic Subqueries
• (Level 200) Writing Correlated Subqueries
• (Level 200) Comparing Subqueries with Joins and Temporary Tables
• (Level 200) Using Common Table Expressions

Module 6: Modifying Data in Tables

Lessons:

• Overview of Transaction
• Inserting Data into Tables
• Deleting Data from Tables
• Updating Data in Tables

Lab: Modifying Data in Tables

• (Level 200) Inserting Data into Tables
• (Level 200) Deleting Data from Tables
• (Level 200) Updating Data in Tables
• (Level 200) Working with Transactions

Module 7: Querying Metadata, XML, and Full-Text Indexes

Lessons:

• Querying Metadata
• Overview of XML
• Querying XML Data
• Overview of Full-Text Indexes
• Querying Full-Text Indexes

Lab: Querying Metadata, XML, and Full-Text Indexes

• (Level 200) Querying Metadata
• (Level 200) Querying XML Data
• (Level 200) Creating and Querying Full-Text Indexes

Module 8: Using Programming Objects for Data Retrieval

Lessons:

• Encapsulating Expressions by Using User-Defined Functions
• Encapsulating Queries by Using Views
• Overview of Stored Procedures
• Writing Distributed Queries

Lab: Using Programming Objects for Data Retrieval

• (Level 300) Creating User-Defined Functions
• (Level 200) Creating Views
• (Level 300) Writing Distributed Queries

Module 9: Using Advanced Querying Techniques

Lessons:

• Considerations for Querying Complex Data
• Querying Complex Table Structures
• Writing Efficient Queries
• Using Different Techniques for Complex Queries
• Maintaining Query Files

Lab: Using Advanced Querying Techniques

• (Level 300) Breaking up a Complex Business Reporting Requirement
• (Level 300) Writing Complex Queries
• (Level 300) Rewriting Complex Queries

• Upis u radnu knjižicu: ne
• Certifikat: ne
• Uvjerenje: ne
• In-house: ne
• Svjedodžba: ne
• Diploma: ne