Introduction to Transact-SQL

SQL200 : 3 Day Instructor-Led Workshop

Learn how to use SQL for SQL Server

The goal of this course is to provide students with the technical skills required to write basic Transact-SQL queries and scripts for Microsoft SQL Server 2005/2008. During this three day course you will learn Transact-SQL as implemented in SQL Server 2005/2008.  The course starts by establishing a foundation understanding of database concepts and terminology.  The course then prepares the student to use the various tools provided by Microsoft to submit queries and view the results.  The course then takes the student through a step-by-step process for creating SQL queries based on business requirements.  This course reinforces the lectures with extensive demonstrations and hands-on labs.

Deliverables:
Attendees receive the following items when taking this course:

  • Interface exclusive curriculum
  • Interface exclusive student/lab manual and downloadable files
  • Interface Certificate of Achievements for
    • SQL200

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

Prerequisites:

  • Experience using a Microsoft Windows operating system
  • Familiarity with the role of the database administrator

Topics covered:

  • Introduction to Database Concepts
    • Definition of a database
    • Basics of Data Modeling
    • Three-schema architecture
    • Database Terminology
    • Understanding a database diagram
  • Using Transact-SQL Querying Tools
    • SQL Server 2005/2008 Management Studio
    • Using the Object Explorer
  • Retrieving Data
    • Retrieving Data by Using the SELECT Statement
    • Filtering Data
    • Formatting Result Sets
    • Using String and Datetime Functions
    • Retrieving Data and Manipulating Result Sets with the CASE expression
  • Working with Multiple Tables
    • Using Aliases for Table Names
    • Combining Data from Multiple Tables using the various JOIN operators
    • Using Relational Set operators (UNION, INTERSECT, EXCEPT)
  • Grouping and Summarizing Data
    • Listing the TOP n Values
    • Using Aggregate Functions
    • Group By Fundamentals
    • Generating Aggregate Values Within Result Sets
    • Generating Aggregates with Detail using Over Clause
  • Working with Subqueries
    • 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
  • Modifying Data
    • Using Transactions
    • Inserting Data
    • Deleting Data
    • Updating Data
    • Using the new OUTPUT clause when modifying data 

At course completion: Students will be able to: understand the basic concepts and terminology of a relational database system; describe the uses of and ways to execute the Transact-SQL language; use query tools available in SQL Server 2005/2008; 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 and modify data in tables.