SQL Queries for Analysts Training - SQLQFA101

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