{"interactiveVideo":{"video":{"startScreenOptions":{"title":"Interactive Video","hideStartTitle":false},"textTracks":{"videoTrack":[{"label":"Subtitles","kind":"subtitles","srcLang":"en"}]},"files":[{"path":"videos\/files-5eea55eb3f7f0.mp4","mime":"video\/mp4","copyright":{"license":"U"},"metadata":{"qualityName":"720p"}}]},"assets":{"interactions":[{"x":28.38427947598253,"y":29.11208151382824,"width":25.32751091703057,"height":13.799126637554586,"duration":{"from":118.93,"to":120.93},"libraryTitle":"Fill in the Blanks","action":{"library":"H5P.Blanks 1.14","params":{"media":{"disableImageZooming":false},"text":"

How do we filter out all the rows with NULL SalesPersonID?<\/p>\n","overallFeedback":[{"from":0,"to":100}],"showSolutions":"Show solution","tryAgain":"Retry","checkAnswer":"Check","notFilledOut":"Please fill in all blanks to view solution","answerIsCorrect":"':ans' is correct","answerIsWrong":"':ans' is wrong","answeredCorrectly":"Answered correctly","answeredIncorrectly":"Answered incorrectly","solutionLabel":"Correct answer:","inputLabel":"Blank input @num of @total","inputHasTipLabel":"Tip available","tipLabel":"Tip","behaviour":{"enableRetry":true,"enableSolutionsButton":true,"enableCheckButton":true,"autoCheck":false,"caseSensitive":false,"showSolutionsRequiresInput":true,"separateLines":false,"confirmCheckDialog":false,"confirmRetryDialog":false,"acceptSpellingErrors":true},"scoreBarLabel":"You got :num out of :total points","confirmCheck":{"header":"Finish ?","body":"Are you sure you wish to finish ?","cancelLabel":"Cancel","confirmLabel":"Finish"},"confirmRetry":{"header":"Retry ?","body":"Are you sure you wish to retry ?","cancelLabel":"Cancel","confirmLabel":"Confirm"},"questions":["

SELECT SalesPersonID, YEAR(OrderDate) AS SalesYear
\n FROM Sales.SalesOrderHeader
\n WHERE *SalesPersonID IS NOT NULL*;
\n <\/p>\n"]},"subContentId":"febfe878-43b5-4f3b-a46e-b0a7a735c0fb","metadata":{"contentType":"Fill in the Blanks","license":"U","title":"IS NOT NULL in WHERE clause Fill in the Blanks"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":1.0917030567685588,"y":31.01043182920912,"width":37.991266375545855,"height":15.545851528384281,"duration":{"from":138.176,"to":140.176},"libraryTitle":"Drag Text","action":{"library":"H5P.DragText 1.10","params":{"taskDescription":"

Count the sales grouped by SalesPersonID and SalesYear.<\/p>\n","overallFeedback":[{"from":0,"to":100}],"checkAnswer":"Check","tryAgain":"Retry","showSolution":"Show solution","dropZoneIndex":"Drop Zone @index.","empty":"Drop Zone @index is empty.","contains":"Drop Zone @index contains draggable @draggable.","ariaDraggableIndex":"@index of @count draggables.","tipLabel":"Show tip","correctText":"Correct!","incorrectText":"Incorrect!","resetDropTitle":"Reset drop","resetDropDescription":"Are you sure you want to reset this drop zone?","grabbed":"Draggable is grabbed.","cancelledDragging":"Cancelled dragging.","correctAnswer":"Correct answer:","feedbackHeader":"Feedback","behaviour":{"enableRetry":true,"enableSolutionsButton":true,"enableCheckButton":true,"instantFeedback":false},"scoreBarLabel":"You got :num out of :total points","textField":"SELECT SalesPersonID, *COUNT(\u2605)* AS TotalSales, YEAR(OrderDate) AS SalesYear\n\tFROM Sales.SalesOrderHeader\n\tWHERE SalesPersonID IS NOT NULL\n\tGROUP BY SalesPersonID, *YEAR(OrderDate)*\n\tORDER BY SalesPersonID, SalesYear;"},"subContentId":"f10e788a-de32-4964-89d9-7f64d5fd5b30","metadata":{"contentType":"Drag Text","license":"U","title":"Aggregation Drag Text"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":0,"y":36.04651162790697,"width":39.737991266375545,"height":14.41048034934498,"duration":{"from":301.798,"to":303.798},"libraryTitle":"Fill in the Blanks","action":{"library":"H5P.Blanks 1.14","params":{"media":{"disableImageZooming":false},"text":"

Which common table should we now refer to in the FROM clause of our main SELECT statement?<\/p>\n","overallFeedback":[{"from":0,"to":100}],"showSolutions":"Show solution","tryAgain":"Retry","checkAnswer":"Check","notFilledOut":"Please fill in all blanks to view solution","answerIsCorrect":"':ans' is correct","answerIsWrong":"':ans' is wrong","answeredCorrectly":"Answered correctly","answeredIncorrectly":"Answered incorrectly","solutionLabel":"Correct answer:","inputLabel":"Blank input @num of @total","inputHasTipLabel":"Tip available","tipLabel":"Tip","behaviour":{"enableRetry":true,"enableSolutionsButton":true,"enableCheckButton":true,"autoCheck":false,"caseSensitive":true,"showSolutionsRequiresInput":true,"separateLines":false,"confirmCheckDialog":false,"confirmRetryDialog":false,"acceptSpellingErrors":false},"scoreBarLabel":"You got :num out of :total points","confirmCheck":{"header":"Finish ?","body":"Are you sure you wish to finish ?","cancelLabel":"Cancel","confirmLabel":"Finish"},"confirmRetry":{"header":"Retry ?","body":"Are you sure you wish to retry ?","cancelLabel":"Cancel","confirmLabel":"Confirm"},"questions":["

WITH Sales_CTE (SalesPersonID, SalesYear)
\n   AS (SELECT SalesPersonID, YEAR(OrderDate)
\n       FROM Sales.SalesOrderHeader
\n       WHERE SalesPersonID IS NOT NULL),
\n   TotalSalesByYear_CTE
\n   AS (SELECT SalesPersonID, COUNT(\u2605) AS TotalSales, SalesYear
\n       FROM Sales_CTE
\n       WHERE SalesPersonID IS NOT NULL
\n       GROUP BY SalesPersonID, SalesYear)
\nSELECT \u2605
\n   FROM *TotalSalesByYear_CTE*
\n   ORDER BY SalesPersonID, SalesYear;<\/p>\n"]},"subContentId":"82c0d47a-e8d3-498e-b5b3-0980c714639a","metadata":{"contentType":"Fill in the Blanks","license":"U","title":"Multiple CTE Fill in the Blanks"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":0,"y":0,"width":40,"height":22.53275109170306,"duration":{"from":400.074,"to":402.074},"libraryTitle":"Fill in the Blanks","action":{"library":"H5P.Blanks 1.14","params":{"media":{"disableImageZooming":false},"text":"

How should the GROUP BY clause look for TotalSalesLifetime_CTE?<\/p>\n","overallFeedback":[{"from":0,"to":100}],"showSolutions":"Show solution","tryAgain":"Retry","checkAnswer":"Check","notFilledOut":"Please fill in all blanks to view solution","answerIsCorrect":"':ans' is correct","answerIsWrong":"':ans' is wrong","answeredCorrectly":"Answered correctly","answeredIncorrectly":"Answered incorrectly","solutionLabel":"Correct answer:","inputLabel":"Blank input @num of @total","inputHasTipLabel":"Tip available","tipLabel":"Tip","behaviour":{"enableRetry":true,"enableSolutionsButton":true,"enableCheckButton":true,"autoCheck":false,"caseSensitive":true,"showSolutionsRequiresInput":true,"separateLines":false,"confirmCheckDialog":false,"confirmRetryDialog":false,"acceptSpellingErrors":false},"scoreBarLabel":"You got :num out of :total points","confirmCheck":{"header":"Finish ?","body":"Are you sure you wish to finish ?","cancelLabel":"Cancel","confirmLabel":"Finish"},"confirmRetry":{"header":"Retry ?","body":"Are you sure you wish to retry ?","cancelLabel":"Cancel","confirmLabel":"Confirm"},"questions":["

WITH Sales_CTE (SalesPersonID, SalesYear)
\n   AS (SELECT SalesPersonID, YEAR(OrderDate)
\n       FROM Sales.SalesOrderHeader
\n       WHERE SalesPersonID IS NOT NULL),
\n   TotalSalesByYear_CTE (SalesPersonID, TotalSales, SalesYear)
\n   AS (SELECT SalesPersonID, COUNT(\u2605), SalesYear
\n       FROM Sales_CTE
\n       GROUP BY SalesPersonID, SalesYear),
\n   TotalSalesLifetime_CTE (SalesPersonID, LifetimeSales)
\n   AS (SELECT SalesPersonID, COUNT(\u2605)
\n       FROM Sales_CTE
\n       GROUP BY *SalesPersonID*)
\nSELECT \u2605
\n   FROM TotalSalesLifetime_CTE
\n   ORDER BY SalesPersonID;<\/p>\n"]},"subContentId":"0b7b2421-fd1d-4610-93c1-e0ce83a146ce","metadata":{"contentType":"Fill in the Blanks","license":"U","title":"Aggregation in CTE Fill in the Blanks"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":"

Correct. We want the lifetime sales, so we want to aggregate over all <\/strong>of a sales person's sales, and no longer group by SalesYear like we had in our prior CTE.<\/p>\n"},"wrong":{"allowOptOut":false,"message":"

We want the lifetime sales, so we want to aggregate over all <\/strong>of a sales person's sales, and no longer group by SalesYear like we had in our prior CTE.<\/p>\n"},"requireCompletion":false},"label":""},{"x":19.650655021834062,"y":15.503875968992247,"width":22.663755458515286,"height":18.674263100436683,"duration":{"from":427.071,"to":429.071},"libraryTitle":"Multiple Choice","action":{"library":"H5P.MultiChoice 1.16","params":{"media":{"disableImageZooming":false},"answers":[{"correct":true,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"

Left, full or inner joins are all fine, as long as we match the SalesPersonID column.<\/div>\n"},{"correct":false,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"
Left outer join on SalesPersonID<\/div>\n"},{"correct":false,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"
Full outer join on SalesPersonID<\/div>\n"},{"correct":false,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"
Inner join on SalesPersonID<\/div>\n"}],"overallFeedback":[{"from":0,"to":100}],"behaviour":{"enableRetry":true,"enableSolutionsButton":true,"enableCheckButton":true,"type":"auto","singlePoint":false,"randomAnswers":true,"showSolutionsRequiresInput":true,"confirmCheckDialog":false,"confirmRetryDialog":false,"autoCheck":false,"passPercentage":100,"showScorePoints":true},"UI":{"checkAnswerButton":"Check","showSolutionButton":"Show solution","tryAgainButton":"Retry","tipsLabel":"Show tip","scoreBarLabel":"You got :num out of :total points","tipAvailable":"Tip available","feedbackAvailable":"Feedback available","readFeedback":"Read feedback","wrongAnswer":"Wrong answer","correctAnswer":"Correct answer","shouldCheck":"Should have been checked","shouldNotCheck":"Should not have been checked","noInput":"Please answer before viewing the solution"},"confirmCheck":{"header":"Finish ?","body":"Are you sure you wish to finish ?","cancelLabel":"Cancel","confirmLabel":"Finish"},"confirmRetry":{"header":"Retry ?","body":"Are you sure you wish to retry ?","cancelLabel":"Cancel","confirmLabel":"Confirm"},"question":"
How should we join our two temporary tables, TotalSalesByYear_CTE and TotalSalesLifetime_CTE?<\/p>\n"},"subContentId":"1594110d-85df-40e5-b3c2-bd639d50028f","metadata":{"contentType":"Multiple Choice","license":"U","title":"JOIN Multiple Choice"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""}],"endscreens":[{"time":527.488,"label":"8:47 Submit screen"}]},"summary":{"task":{"library":"H5P.Summary 1.10","params":{"intro":"Choose the correct statement.","summaries":[{"subContentId":"1002ffa5-3324-4062-aead-22a186488b17","tip":""}],"overallFeedback":[{"from":0,"to":100}],"solvedLabel":"Progress:","scoreLabel":"Wrong answers:","resultLabel":"Your result","labelCorrect":"Correct.","labelIncorrect":"Incorrect! Please try again.","alternativeIncorrectLabel":"Incorrect","labelCorrectAnswers":"Correct answers.","tipButtonLabel":"Show tip","scoreBarLabel":"You got :num out of :total points","progressText":"Progress :num of :total"},"subContentId":"81dc8286-b591-4e7e-8b3c-f00685237a66","metadata":{"contentType":"Summary","license":"U","title":"Untitled Summary"}},"displayAt":3}},"override":{"autoplay":false,"loop":false,"showBookmarksmenuOnLoad":false,"showRewind10":false,"deactivateSound":false,"preventSkippingMode":"none"},"l10n":{"interaction":"Interaction","play":"Play","pause":"Pause","mute":"Mute","unmute":"Unmute","quality":"Video Quality","captions":"Captions","close":"Close","fullscreen":"Fullscreen","exitFullscreen":"Exit Fullscreen","summary":"Open summary dialog","bookmarks":"Bookmarks","endscreen":"Submit screen","defaultAdaptivitySeekLabel":"Continue","continueWithVideo":"Continue with video","playbackRate":"Playback Rate","rewind10":"Rewind 10 Seconds","navDisabled":"Navigation is disabled","sndDisabled":"Sound is disabled","requiresCompletionWarning":"You need to answer all the questions correctly before continuing.","back":"Back","hours":"Hours","minutes":"Minutes","seconds":"Seconds","currentTime":"Current time:","totalTime":"Total time:","singleInteractionAnnouncement":"Interaction appeared:","multipleInteractionsAnnouncement":"Multiple interactions appeared.","videoPausedAnnouncement":"Video is paused","content":"Content","answered":"@answered answered","endcardTitle":"@answered Question(s) answered","endcardInformation":"You have answered @answered questions, click below to submit your answers.","endcardInformationNoAnswers":"You have not answered any questions.","endcardInformationMustHaveAnswer":"You have to answer at least one question before you can submit your answers.","endcardSubmitButton":"Submit Answers","endcardSubmitMessage":"Your answers have been submitted!","endcardTableRowAnswered":"Answered questions","endcardTableRowScore":"Score","endcardAnsweredScore":"answered","endCardTableRowSummaryWithScore":"You got @score out of @total points for the @question that appeared after @minutes minutes and @seconds seconds.","endCardTableRowSummaryWithoutScore":"You have answered the @question that appeared after @minutes minutes and @seconds seconds."}}

Common Table Expressions

Chris Lo