SQL Queries for Analysts Courseware (SQLQFA101)

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.

Publisher: Ann L Weber

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.
Engaging content: The course is designed to be engaging and easy to follow, ensuring that students remain interested and focused throughout their learning experience.
Experienced author: This courseware is authored by an experienced SQL Server trainer and developer, ensuring that the content is both accurate and up-to-date.

Outline

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
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
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
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
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)
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
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
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
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
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
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
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)
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
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
Final Lab
1. Final Lab Goals
2. Primary Goals RetailBankingSample Database
3. Primary Goals HealthCareSample Database

Required 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

License

Length: 4 days | $140.00 per copy

License Request More Information Request Trainer Evaluation Copy

What is Included?

Student Manual
Student Class Files