Course Code: 24372

SQL Queries for Analysts Training - SQLQFA101

Class Dates:
10/7/2024
7/22/2024
Length:
4 Days
Cost:
$2600.00
Class Time:
Technology:
Server
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • Course Overview
  • 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.

  • Audience
  • Benefits

    Comprehensive and focused coverage: The course focuses on the Microsoft Transact-SQL language from writing a basic SELECT statement through complex JOINs with multiple tables, aggregations and CTEs.
    Practical examples: The course uses real-world examples and scenarios to help students understand how to write queries to solve problems. Rather than focusing on the AdventureWorks database, this class uses simplified databases to mimic what students may see in the banking and healthcare industries.
    Hands-on learning: Students will engage in hands-on practices throughout each chapter as well as independent labs to reinforce their learning and gain practical experience in writing queries. The class culminates with a final lab without the detailed directions to allow students to practice implementing what they have learned.

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
    Useful Prerequisites

    A general understanding of basic database concepts such as relational database design, normalization, and table structures is helpful
    Exposure to query writing or having experience with another programming background is helpful

Course Details

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