HARTMANN software group

SQL Queries for Analysts Training in Denver

Enroll in or hire us to teach our SQL Queries for Analysts class in Denver, Colorado by calling us @303.377.6176. Like all HSG classes, SQL Queries for Analysts may be offered either onsite or via instructor led virtual training. Consider looking at our public training schedule to see if it is scheduled: Public Training Classes
Provided there are enough attendees, SQL Queries for Analysts may be taught at one of our local training facilities.
We offer private customized training for groups of 3 or more attendees.
get pricing information

Course Description

  

This 4-day SQL Server course, Introduction to SQL for Analysts, is designed to teach attendees both beginning and intermediate Transact-SQL (T-SQL). The course covers writing simple and complex queries, working with data types, using built-in functions, and more. Students will also learn how to combine data from multiple tables and result sets using JOIN operations, subqueries, and temporary objects. The course is structured to provide a comprehensive understanding of the Microsoft SQL Server query language and its capabilities. By the end of the course, students will be able to write advanced queries and manipulate data using Data Manipulation Language. The course is ideal for report writers and analysts who want to learn how to use SQL Server to create reports and analyze data, including using data aggregations.

Course Length: 4 Days
Course Tuition: $1890 (US)

Prerequisites
The ability to easily open and save files in a variety of programs and the ability to launch new applications in Windows 10 or 11

Course Outline

  
  1. Introduction
    1. Course Overview
    2. Data Manipulation Verbs
      1. Data Analysis Verb List
      2. Example Usage
    3. Courseware Overview
      1. Class Files
    4. Datasets Overview
      1. Retail Banking Sample Data
      2. HealthCare Sample Data
      3. Phishing Detection Sample Data
  2. Overview of SSMS and Query Writing
    1. Working with SSMS
      1. Object Explorer
      2. Viewing Data
      3. Try It 1 – Opening SSMS and Connecting to Object Explorer
    2. Using the SQL Editor
      1. Query Tab Right-Click Menu
      2. Closing, Hiding, and Floating Windows
      3. Saving Queries
      4. Saving Results
      5. Try It 2 – The Query Editor
    3. Creating SSMS Script Projects
      1. Try It 3 – Creating and Using a Script Project
    4. Using IntelliSense to your advantage
    5. Tips and Tricks with SSMS
      1. Try It 4 – Working in SSMS
    6. Adding Comments to Queries
      1. Block Comments
    7. Understanding Batches and Scripts
      1. Try It 5 – Understanding Batch Directives
  3. Database Design Concepts
    1. What is SQL
    2. Database Diagrams
      1. Try It 1 – Exploring Database Diagrams
    3. Database Terminology Overview
      1. Tables
      2. Relationships
      3. Constraints
      4. Try It 2 – Exploring Tables and Primary Key Constraints
      5. Try It 3 – Exploring Foreign Key Constraints
      6. Views, Stored Procedures, and Functions
      7. Try It 4 – Exploring Views and Stored Procedures
    4. Lab Exercise 1: Reviewing Database Diagrams
  4. Basic SELECT Statements
    1. The SELECT Statement
      1. The SELECT and FROM Clauses
      2. Fully Qualified Object Names
      3. Using Column and Table Aliases
      4. Execution Order of SELECT Statements
      5. Try It 1 – Basic SELECT Statement
    2. Ordering Results
      1. Try It 2 – Sorting Result Sets
    3. Additional SELECT Options
      1. DISTINCT
      2. TOP
      3. Try It 3 – DISTINCT and TOP
    4. Lab Exercise 1: Using the SELECT statement to retrieve data from a table
  5. Working with Data Types
    1. Data Type Precedence
    2. String Related Data Types
      1. Try It 1 – Exploring String Data Types
    3. Numeric Data Types
      1. Try It 2 – Exploring Numeric Data Types
    4. Date and Time Data Types
      1. Try It 3 – Exploring Date and Time Data Types
    5. Converting Between Data Types
    6. How to Find Help on Functions
      1. Understanding Function Help
      2. Try It 4 – Finding help
    7. Understanding Data Type Conversion
      1. Try It 5 – Exploring Implicit Conversions
    8. The CONVERT Function
      1. Try It 6 – Explicit Conversions
    9. TRY_CONVERT
      1. Try It 7 – Using TRY_ CONVERT
    10. Lab Exercise 1: Explicit Data Conversions
    11. Lab Exercise 2: Explore Additional Data Types (Optional)
  6. Enhanced SELECT Statements
    1. Expressions
      1. String Literals
      2. Concatenation
      3. Try It 1 – Concatenation and Literals
      4. Arithmetic Expressions
      5. Try It 2 – Arithmetic Expressions
    2. Working with CASE Expressions
      1. Try It 3 – Simple CASE
    3. Filtering Rows
    4. Comparison Operators
      1. Try It 4 – Comparison Operators
    5. Logical Operators
      1. Try It 5 – Logical Operators
      2. BETWEEN
      3. Try It 6 – IN and BETWEEN
    6. LIKE
      1. Try It 7 – Using LIKE
    7. Lab Exercise 1: Adding Expressions to a SELECT Statement
  7. Handling NULL Data
    1. NULL vs Blank
      1. Try It 1 – Working with NULL Data
    2. = vs IS NULL
      1. Try It 2 – Searching for NULLs
    3. ISNULL function
    4. COALESCE
      1. Try It 3 – COALESCE
    5. Concatenating NULL Data
      1. Try It 4 – Concatenating with NULLs
    6. Lab Exercise 1: Working with NULLs
  8. Aggregating and Grouping Data
    1. Aggregate Functions
      1. COUNT and COUNT_BIG
      2. MIN, MAX, SUM, AVG
      3. Aggregating and Nulls
      4. Try It 1 – Aggregate Functions
    2. GROUP BY
      1. Try It 2 – GROUP BY
    3. HAVING
      1. Try It 3 – HAVING Clause
    4. HAVING vs WHERE
      1. Try It 4 – HAVING vs WHERE
    5. Lab Exercise 1: Working with Aggregates and GROUP BY
  9. Joining Multiple Tables
    1. JOINS
    2. INNER JOIN
      1. Try It 1 – INNER JOIN
    3. OUTER JOIN
      1. Left and Right Outer Joins
      2. FULL OUTER JOIN
      3. Try It 2 – Outer Joins
    4. CROSS JOIN
    5. Joining Three or More Tables
      1. Try It 3 – Joining Three or More Tables
    6. Self-join
      1. Try It 4 – Self Join
    7. Alternate Syntax, Implicit Joins
    8. Set Operations
      1. UNION
      2. Try It 5 - Union
      3. INTERSECT
      4. Try It 6 - Intersect
      5. EXCEPT
      6. Try It 7 - Except
    9. Working with Multiple Set Operators
    10. Viewing Graphical Execution Plans
      1. Try It 8 – Execution Plans
    11. Lab Exercise 1: Working with Join Operators
    12. Lab Exercise 2: Combining Result Sets
  10. Built-in Functions Overview
    1. Working with Functions
      1. Try It 1 – Reviewing Finding Help
    2. Mathematical Function Overview
      1. Mathematical Functions
      2. Try It 2 – Mathematical Functions
    3. String Function Overview
      1. String Functions
      2. Try It 3 – String Functions
    4. Date Time Function Overview
      1. Date Retrieval
      2. Manipulating Dates
      3. Try It 4 – DATEADD and DATEDIFF
      4. Retrieving Parts of Dates
      5. Try It 5 – Retrieving Date Parts
    5. Additional Date Functions
      1. SET DATEFORMAT
      2. Try it 6 – ISDATE and EOMONTH
    6. Nesting Functions
      1. Try It 7 - Nesting Functions
    7. Lab Exercise 1: Using Built-in Functions
  11. Subqueries
    1. Subqueries
      1. Nested vs Correlated Subqueries
    2. Subqueries in the SELECT Clause
      1. Try It 1 – Subqueries in the SELECT
    3. Subqueries in the WHERE Clause
      1. Try It 2 – Subqueries in WHERE
    4. EXISTS
    5. Subqueries in FROM Clause
      1. Try It 3 – Subqueries in FROM
    6. Alternatives to Subqueries
      1. Try It 4 – Reworking Subqueries
    7. Lab Exercise 1: Using Subqueries
  12. Advanced Queries
    1. Advanced Data Types
      1. Binary
      2. XML
      3. Spatial
      4. HierarchyID
      5. Try It 1 – Working with Advanced Data Types
    2. Data Conversions
      1. The CAST Function
      2. The PARSE Function
      3. The STR Function
      4. TRY_CAST and TRY_PARSE
      5. Try It 2 – Advanced Conversions
    3. The COLLATE Clause
      1. Try It 3 – COLLATE
    4. Advanced Grouping Features
      1. Overview ROLLUP and CUBE
      2. GROUPING SETS
      3. GROUPING Function
      4. GROUPING_ID
      5. Try It 4 – Advanced Grouping
    5. OVER with Aggregates
      1. Try It 5 – OVER with Aggregates
    6. OVER with Ranking Functions
      1. Try It 6 – RANK
    7. Using OFFSET / FETCH
      1. Try It 7 – OFFSET / FETCH
    8. Advanced Date Functions
      1. From Parts Functions
      2. TODATETIMEOFFSET
      3. SWITCHOFFSET
    9. Lab Exercise 1: Using the OVER clause
    10. Lab Exercise 2: Using RANK functions
    11. Lab Exercise 3: Explore Advanced Data Types (Optional)
  13. Data Manipulation Language
    1. Transaction Overview
      1. Using Transactions
      2. Try It 1 – Using Transactions
    2. INSERT
      1. INSERT SELECT
      2. INSERT SELECT vs SELECT INTO
      3. Try It 2 – INSERT
    3. UPDATE
      1. Try It 3 - UPDATE
    4. DELETE
      1. TRUNCATE TABLE
      2. Try It 4 – DELETE and TRUNCATE
    5. OUTPUT Clause
      1. Try It 5 – OUTPUT
    6. MERGE
      1. Try It 6 – MERGE
    7. Lab Exercise 1: Working with DML Statements
  14. Working with Temporary Objects
    1. Variables
      1. Declaring Variables
      2. Using Variables
      3. Try It 1 – Using Variables
    2. Importance of Using Correct Data Types
      1. Try It 2 – Variable Data Types
    3. Table Variables
      1. Try It 3 – Table Variables
    4. Temporary Tables
      1. Try It 4 - Temporary Table
    5. Common Table Expressions (CTEs)
    6. Lab Exercise 1: Working with Temporary Objects
  15. Final Lab
    1. Final Lab Goals
    2. Primary Goals RetailBankingSample Database
    3. Primary Goals HealthCareSample Database

Course Directory [training on all levels]

Technical Training Courses
Software engineer/architect, System Admin ... Welcome!
Business Training Courses
Project Managers, Business Analysts, Paralegals ... Welcome!
Upcoming Classes
Gain insight and ideas from students with different perspectives and experiences.

Interesting Reads Take a class with us and receive a book of your choosing for 50% off MSRP.