{"interactiveVideo":{"video":{"startScreenOptions":{"title":"Interactive Video","hideStartTitle":false},"textTracks":{"videoTrack":[{"label":"Subtitles","kind":"subtitles","srcLang":"en"}]},"files":[{"path":"videos\/files-5ee153ebad795.mp4","mime":"video\/mp4","copyright":{"license":"CC BY","version":"4.0"},"metadata":{"qualityName":"720p"}}]},"assets":{"interactions":[{"x":60.043668122270745,"y":73.64341085271317,"width":10,"height":10,"duration":{"from":30.268,"to":40.268},"libraryTitle":"Link","action":{"library":"H5P.Link 1.3","params":{"linkWidget":{"protocol":"https:\/\/","url":"docs.microsoft.com\/en-us\/sql\/t-sql\/queries\/from-transact-sql?view=sql-server-ver15"},"title":"FROM clause documentation"},"subContentId":"42d52b8c-0e79-4b2a-b34c-3470c4405787","metadata":{"contentType":"Link","license":"U"}},"displayType":"poster","visuals":{"backgroundColor":"rgba(0,0,0,0.5)","boxShadow":true},"pause":false,"buttonOnMobile":false},{"x":29.47598253275109,"y":23.28966521106259,"width":17.336244541484717,"height":15.093477074235809,"duration":{"from":61.409,"to":63.409},"libraryTitle":"Fill in the Blanks","action":{"library":"H5P.Blanks 1.14","params":{"media":{"disableImageZooming":false},"text":"

What keyword is used to give tables a correlation name or alias?<\/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":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":["

SELECT \u2605 FROM TableA *AS*a, TableB *AS* b <\/p>\n\n

WHERE a.ColumnA = b.ColumnA;<\/p>\n"]},"subContentId":"4c99785e-d612-4093-99be-0fd0e0120b45","metadata":{"contentType":"Fill in the Blanks","license":"U","title":"Table Correlation Names Fill in the Blanks"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":17.03056768558952,"y":23.253275109170307,"width":33.18777292576419,"height":16.593886462882097,"duration":{"from":170.591,"to":172.591},"libraryTitle":"Drag Text","action":{"library":"H5P.DragText 1.10","params":{"taskDescription":"

How do we filter the records to only those with ColumnC values ending in \"2\"?<\/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 \u2605\n FROM TableA AS a \n INNER JOIN TableB As b ON a.ColumnA = b.ColumnA\n WHERE *b*.*ColumnC* *LIKE* *\u2018%2\u2019*;"},"subContentId":"8db38092-f248-4043-8ae7-99308a3bcd68","metadata":{"contentType":"Drag Text","license":"U","title":"String Mask Drag Text"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":48.03493449781659,"y":19.37984496124031,"width":19.08296943231441,"height":12.473389737991267,"duration":{"from":399.103,"to":401.103},"libraryTitle":"True\/False Question","action":{"library":"H5P.TrueFalse 1.8","params":{"media":{"disableImageZooming":false},"correct":"true","behaviour":{"enableRetry":true,"enableSolutionsButton":true,"enableCheckButton":true,"confirmCheckDialog":false,"confirmRetryDialog":false,"autoCheck":false,"feedbackOnCorrect":"This is an inner join because only rows joined due to matching data are shown. This is also a self join because the two tables being joined are the same.","feedbackOnWrong":"This is an inner join because only rows joined due to matching data are shown. This is also a self join because the two tables being joined are the same."},"l10n":{"trueText":"True","falseText":"False","score":"You got @score of @total points","checkAnswer":"Check","showSolutionButton":"Show solution","tryAgain":"Retry","wrongAnswerMessage":"Wrong answer","correctAnswerMessage":"Correct answer","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"},"question":"

This is an INNER JOIN that is also a SELF JOIN.<\/p>\n"},"subContentId":"827cb566-06c8-4d20-a951-206325e34040","metadata":{"contentType":"True\/False Question","license":"U","title":"Self join True\/False Question"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":53.493449781659386,"y":15.503875968992247,"width":17.77292576419214,"height":13.783433406113538,"duration":{"from":549.775,"to":551.775},"libraryTitle":"Multiple Choice","action":{"library":"H5P.MultiChoice 1.16","params":{"media":{"disableImageZooming":false},"answers":[{"correct":true,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"

INNER JOIN<\/div>\n"},{"correct":false,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"

LEFT OUTER JOIN<\/div>\n"},{"correct":false,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"

FULL OUTER JOIN<\/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":"

What type of join is demonstrated here? <\/p>\n"},"subContentId":"377ccafb-df7b-40d1-85f0-beb8bdabb80f","metadata":{"contentType":"Multiple Choice","license":"U","title":"Join type Multiple Choice"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":51.31004366812227,"y":15.503875968992247,"width":19.213973799126638,"height":15.617494541484717,"duration":{"from":584.255,"to":586.255},"libraryTitle":"Multiple Choice","action":{"library":"H5P.MultiChoice 1.16","params":{"media":{"disableImageZooming":false},"answers":[{"correct":true,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"

LEFT OUTER JOIN<\/div>\n"},{"correct":false,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"

FULL OUTER JOIN<\/div>\n"},{"correct":false,"tipsAndFeedback":{"tip":"","chosenFeedback":"","notChosenFeedback":""},"text":"

INNER JOIN<\/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":"

What type of join is demonstrated here?<\/p>\n"},"subContentId":"f90cef10-ee20-4187-8d1f-f389820e8a5d","metadata":{"contentType":"Multiple Choice","license":"U","title":"Join type Multiple Choice 2"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":49.45414847161572,"y":0,"width":20.218340611353714,"height":20.333651746724893,"duration":{"from":668.376,"to":670.376},"libraryTitle":"Multiple Choice","action":{"library":"H5P.MultiChoice 1.16","params":{"media":{"disableImageZooming":false},"answers":[{"correct":false,"tipsAndFeedback":{"chosenFeedback":"

We want a LEFT OUTER JOIN so that we still keep all rows with schools and employees (hence not INNER), but we aren\u2019t interested in keeping rows with names that have no associated employee (hence not FULL).<\/div>\n","notChosenFeedback":"

INNER JOIN<\/div>\n"},{"correct":true,"tipsAndFeedback":{"chosenFeedback":"

LEFT OUTER JOIN<\/div>\n"},{"correct":false,"tipsAndFeedback":{"chosenFeedback":"

What type of join should we use on Person to append the name of the employee, if the row contains an employee?<\/p>\n"},"subContentId":"bc5194a0-cd2d-44cb-adaf-22fabee13911","metadata":{"contentType":"Multiple Choice","license":"U","title":"Join type Multiple Choice 3"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":20.524017467248907,"y":0,"width":31.790393013100438,"height":22.53206877729258,"duration":{"from":671.504,"to":673.504},"libraryTitle":"Drag Text","action":{"library":"H5P.DragText 1.10","params":{"taskDescription":"

Complete the rest of the query to left join the Person table and show the full name in the results:<\/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 code, school.name, emp.number, emp.extension, \n *per.firstName* + ' ' + *per.lastName* AS Name\n FROM School AS school \n FULL JOIN Employee AS emp ON school.code = emp.schoolCode\n LEFT *JOIN* *Person* *AS* *per* *ON* *per.number* = emp.number\n ORDER BY school.code;"},"subContentId":"f225f60d-3dc3-4b9c-9c28-ad212363390f","metadata":{"contentType":"Drag Text","license":"U","title":"Complete the FROM clause with JOIN Drag Text"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""}]},"summary":{"task":{"library":"H5P.Summary 1.10","params":{"intro":"Choose the correct statement.","summaries":[{"subContentId":"aa54bc56-5d6d-48fc-8e4d-c7f7f4b9a908","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":"2faf28a4-a6e1-43c9-ae5c-1755738f1b36","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."}}

Explicit Inner, Outer and Self Joins

Chris Lo