Course Overview

This 3-day SQL Server course, Introduction to SQL for Report Writers and Analysts, teaches attendees the basics of Transact-SQL, including writing simple and complex queries, working with data types, building functions, and more. Students will also learn how to combine data from multiple tables and result sets using JOIN operations and subqueries.

Note: This class can be customized to use SQL Server 2012 through 2022.

Key Learning Areas

  • Understand database design concepts
  • Query the database
  • Work with different data types
  • Join Multiple Tables
  • Work with functions
  • Perform subqueries

Course Outline

Introduction

  • Course Overview
  • Data Manipulation Verbs
  • Courseware Overview
  • Datasets Overview

Overview of SSMS and Query Writing

  • Working with SSMS
  • Using the SQL Editor
  • Creating SSMS Script Projects
  • Tips and Tricks with SSMS
  • Adding Comments to Queries
  • Understanding Batches and Scripts

Database Design Concepts

  • What is SQL
  • Database Diagrams
  • Database Terminology Overview

Basic SELECT Statements

  • The SELECT Statement
  • Execution Order of SELECT Statements
  • Ordering Results
  • Additional SELECT Options

Working with Data Types

  • Data Type Precedence
  • String Related Data Types
  • Numeric Data Types
  • Date and Time Data Types
  • Converting Between Data Types
  • How to Find Help on Functions
  • Understanding Data Type Conversion

Enhanced SELECT Statements

  • Expressions
  • Filtering Rows
  • Comparison Operators
  • Logical Operators

Handling NULL Data

  • NULL vs Blank
  • = vs IS NULL
  • ISNULL function
  • COALESCE
  • Concatenating NULL Data

Aggregating and Grouping Data

  • Aggregate Functions
  • GROUP BY
  • HAVING
  • HAVING vs WHERE

Joining Multiple Tables

  • JOINS
  • INNER JOIN
  • OUTER JOIN
  • CROSS JOIN
  • Joining Three or More Tables
  • Self-join
  • Alternate Syntax, Implicit Joins
  • Set Operations
  • Working with Multiple Set Operators
  • Viewing Graphical Execution Plans

Built-in Functions Overview

  • Working with Functions
  • Mathematical Function Overview
  • String Function Overview
  • Date Time Function Overview
  • Nesting Functions

Subqueries

  • Subqueries
  • Nested vs Correlated Subqueries
  • Subqueries in the SELECT Clause
  • Subqueries in the WHERE Clause
  • EXISTS
  • Subqueries in FROM Clause
  • Alternatives to Subqueries

Prerequisites

In addition to their professional experience, students who attend this training should already have the following technical knowledge:

  • Basic knowledge of the Microsoft Windows operating system including the ability to easily open and save files in a variety of programs and the ability to launch new applications
  • Working knowledge of basic database concepts such as relational database design, normalization, and table structure would be helpful
  • Exposure to query writing or having experience with another programming background would be helpful