Realdolmen Education

Details

Basic Querying Microsoft SQL Server 2012

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

3 day(s)

Audience

This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals. The course will very likely be well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, report writers, business analysts and client application developers.

Prerequisites

Before attending this course, students must have a working knowledge of relational databases and a basic knowledge of the Microsoft Windows operating system and its core functionality.

Objectives

This course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. This course helps people prepare for exam 70-461.

Methods

Instructor led training alternated with demos and exercises. Each student disposes of a workstation. All the labs for this course can be performed using the provided virtual machines.

Description

After completing this course, students will be able to: Write SELECT queries, Query multiple tables, Use built-in functions, Use subqueries, Use set operators, Implement error handling, Implement transactions, Sort and filter data, Program with T-SQL.

Contents

  • Module 1: Introduction to Microsoft SQL Server 2012
    • Introducing Microsoft SQL Server 2012
    • Getting Started with SQL Server Management Studio
    • Creating and Organizing T-SQL scripts
    • Using SQL Server Books Online
  • Module 2: Introduction to T-SQL Querying
    • Executing Basic SELECT Statements
    • Executing queries which filter data using predicates
    • Executing queries which sort data using ORDER BY
  • Module 3: Writing SELECT Queries
    • Writing Simple SELECT Statements
    • Eliminating Duplicates with DISTINCT
    • Using Column and Table Aliases
    • Writing Simple CASE Expressions
  • Module 4: Querying Multiple Tables
    • Understanding Joins
    • Querying with Inner Joins
    • Querying with Outer Joins
    • Querying with Cross Joins and Self Joins
  • Module 5: Sorting and Filtering Data
    • Sorting Data
    • Combining Data from Multiple Tables
    • Filtering with the TOP and OFFSET-FETCH Options
    • Working with Unknown and Missing Values
  • Module 6: Using Built-In Functions
    • Writing Queries with Built-In Functions
    • Using Conversion Functions
    • Using Logical Functions
    • Using Functions to Work with NULL
  • Module 7: Grouping and Aggregating Data
    • Using Aggregate Functions
    • Using the GROUP BY Clause
    • Filtering Groups with HAVING
  • Module 8: Using Subqueries
    • Writing Self-Contained Subqueries
    • Writing Correlated Subqueries
    • Using the EXISTS Predicate with Subqueries
  • Module 9: Using Set Operators
    • Writing Queries with the UNION Operator
    • Using EXCEPT and INTERSECT
    • Using APPLY
  • Module 10: Programming with T-SQL
    • Declaring Variables and Delimiting Batches
    • Using Control-of-Flow Elements
    • Generating Dynamic SQL
    • Using Synonyms
  • Module 11: Implementing Error Handling
    • Redirecting Errors with TRY / CATCH
    • Using THROW to Pass an Error Message Back to a Client
  • Module 12: Implementing Transactions
    • Controlling transactions with BEGIN, COMMIT, and ROLLBACK
    • Adding error handling to a CATCH block