Advanced Querying Techniques in SQL Server

https://www.pluralsight.com/courses/advanced-querying-techniques-sql-server 

by Gerald Britton

Mod 1: Course Overview

  1. Course Overview

Mod 2: Navigating Data Hierarchies Using Recursive Queries

  1. Introduction to Recursive Queries
  2. Using the WITH Statement
  3. Demo: Iterative Queries Using the WITH Statement
  4. Writing Recursive Transact-SQL
  5. Demo: Simple Recursive Queries
    • you can use the MAXRECURSION clause to safeguard against an infinite loop cause by a recursive query
    • how to generate Fibonacci series using recursive t-sql
  6. T-SQL Restrictions for Recursive Queries
    • Cannot be used with recursive queries
      • SELECT DISTINCT
      • GROUP BY, HAVING
      • Scalar aggregation (SUM, AVG, MIN, MAX etc.)
      • PIVOT (SQL Server 2012 or later)
      • TOP
      • LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
      • Subqueries
  7. Demo: Navigating a Data Hierarchy Using Recursion in T-SQL
  8. Module Summary
    • SQL Server maximum recursion (32,767)

Mod 3: Aggregating Data Using the PIVOT and UNPIVOT Clauses
  1. Introduction to PIVOT and UNPIVOT
  2. Demo: Pivoting in a Spreadsheet
  3. Demo: Pivoting in Transact-SQL Using Cross-tabulation
  4. Understanding PIVOT Syntax
  5. Demo: Using PIVOT in Transact-SQL
  6. Demo: Pivoting on Multiple Columns
  7. Demo: Building a Dynamic PIVOT Query
  8. Understanding UNPIVOT Syntax
  9. Demo: Using UNPIVOT and CROSS APPLY
  10. Module Summary
Mod 4: Storing Data as Entities, Attributes, and Values (EAV)
  1. Introduction to the EAV Model
  2. Demo: Handling Sparse Data Using a Traditional Approach
    • sql server allows 1024 columns per table
  3. Demo: Refactoring into an EAV Model
    • demo of an unstructured sql table, an EAV. you can basically have a schema-less design in sql server, with unlimited columns. however, you sacrifice referential integrity and writing queries become very difficult  stackoverflow
  4. Demo: Factoring out Attributes Using Foreign Keys
  5. Recapping Progress and Issues
  6. Demo: Handling Types and Constraints
  7. Demo: Implementing Dynamic Constraints
  8. Demo: Exploring Sparse Columns
    • you can mark a column SPARSE
      • occupies no space when value is null or 0
  9. Demo: Using XML as an Alternative
    • demo of the XML data type in sql server
  10. Demo: From XML to JSON
    • sql server 2016 introduced JSON support (not as a data type)
      • you can use JSON_VALUE to query a value
  11. Module Summary
Mod 5: Sampling Data in SQL Server
  1. Introducing Data Sampling
  2. Understanding Data Sampling Terminology
  3. The Syntax of the TABLESAMPLE Option
  4. Demo: Using the TABLESAMPLE Option
  5. Demo: Stratified Sampling
  6. Demo: Cluster Sampling
  7. Module Summary


Comments

Post a Comment

Popular posts from this blog

Angular Routing and Navigation Playbook

Image
https://www.pluralsight.com/courses/angular-routing-navigation-playbook by Lara Newsom Course Overview Course Overview Course Introduction and Code Examples Course Introduction and Code Examples Basic Angular Routing Setup Introduction to Angular Routing Discussion: Angular RouterModule Exports Demo: Bootstrap Angular RouterModule Discussion: Declaring Routes angular has tow route path match strategies: prefix based or full match prefix is the default Demo: Declaring Default and Home Routes two options for declaring a default route: {path: ' ', componenet: DefaultComponent} {path: ' ', redirectTo: 'home', pathMatch: 'full'} Discussion: Child Routes and Route Order Demo: Declaring a Wildcard Route wild card routes is: path: '**' to enabled logging all navigation events to the console, enable tracing Demo: Hooking into Router Events Routing in Standalone Component Applications Introduction to Standalone Components and Routing Demo: Bootstrapping Ro...
Read more

Working with Files in C# 10

https://www.pluralsight.com/courses/c-sharp-10-working-files by Jason Roberts Course Overview Course Overview Sandbox Managing Files and Directories Introduction Creating a New Console App Checking if a File Exists you can use to check if file path is an absolute path: Path.IsPathFullyQualified Getting the Parent Directory of a Path Checking if a Directory Exists and Creating New Directories Copying a File Moving a File Getting the File Extension from a File Name Checkpoint 01- Get the Parent Directory for a Path Changing a Filename Extension Checkpoint 02 - Get the Extension Part of a Filename Deleting a Directory Enumerating All the Files in a Directory Summary Exercise 01- Copying and Deleting Files Monitoring the File System for Changes Introduction Introducing the FileSystemWatcher Class The IntemalBufferSize Property Configuring Notification Filters Additional Properties Refactoring to Use a FileSystemWatcher use FileSystemWatcher to watch for file changes in a folder Checkpo...
Read more

Mastering Git

https://app.pluralsight.com/library/courses/master-git/table-of-contents by Paolo Perrotta Module 1: Course Overview Course Overview Module 2: The Four Areas: Introduction Version Check About This Training A Note About the Command Line Introducing the Four Areas The Working Area The Repository The Index Module 3: The Four Areas: Basic Workflow Revisiting the Basics Moving Data to the Right Moving Data to the Left Removing Files Renaming Files A Quick Summary Module 4: The Four Areas: Git Reset Understanding Reset A Reset Example More Reset Examples Module 5: The Four Areas: Advanced Tools Stashing Data Solving Conflicts Working with Paths Committing Parts of a File Introducing Switch and Restore Git Is a Toolbox Module 6: History: Exploring the Past Becoming a History Surgeon Referencing Commits to show changes in a commit: git show HEAD to show changes in a commit parent: git show HEAD^ Tracking Changes in History Browsing the Log Module 7: History: Fixing Mistakes Reviewing the Golde...
Read more