{"interactiveVideo":{"video":{"startScreenOptions":{"title":"Interactive Video","hideStartTitle":false},"textTracks":{"videoTrack":[{"label":"Subtitles","kind":"subtitles","srcLang":"en"}]},"files":[{"path":"videos\/files-5ee152a826958.mp4","mime":"video\/mp4","copyright":{"license":"CC BY","version":"4.0"},"metadata":{"qualityName":"720p"}}]},"assets":{"interactions":[{"x":17.46724890829694,"y":15.526443474041727,"width":32.75109170305677,"height":18.340611353711793,"duration":{"from":23.72,"to":25.72},"libraryTitle":"Fill in the Blanks","action":{"library":"H5P.Blanks 1.14","params":{"media":{"disableImageZooming":false},"text":"

Complete the two missing clauses in this query to retrieve all students whose average grade from all courses (i.e. average of all finalMark belonging to a student) is greater than 0:<\/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 studentNumber<\/p>\n\n

FROM CourseStudent<\/p>\n\n

*GROUP BY studentNumber*<\/p>\n\n

*HAVING AVG(finalMark) > 0*<\/p>\n\n

ORDER BY studentNumber;<\/p>\n"]},"subContentId":"e4f07648-328e-46a0-b85f-9cc20bb23c74","metadata":{"contentType":"Fill in the Blanks","license":"U","title":"Aggregation Fill in the Blanks"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":20.74235807860262,"y":23.28966521106259,"width":31.703056768558955,"height":16.84838973799127,"duration":{"from":73.099,"to":75.099},"libraryTitle":"Drag Text","action":{"library":"H5P.DragText 1.10","params":{"taskDescription":"

Complete the query to calculate the average of all non-zero averages, giving the result an appropriate column alias:<\/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 *AVG(finalMark)* *AS* *averageMark*\n FROM CourseStudent\n GROUP BY studentNumber \n HAVING AVG(finalMark) > 0;"},"subContentId":"41c105da-6b9b-4774-b818-53fddcd0f349","metadata":{"contentType":"Drag Text","license":"U","title":"Aggregation and Column Alias Drag Text"}},"pause":true,"displayType":"poster","buttonOnMobile":false,"adaptivity":{"correct":{"allowOptOut":false,"message":""},"wrong":{"allowOptOut":false,"message":""},"requireCompletion":false},"label":""},{"x":24.017467248908297,"y":25.1937984496124,"width":28.82096069868996,"height":16.85589519650655,"duration":{"from":165.93,"to":167.93},"libraryTitle":"Drag Text","action":{"library":"H5P.DragText 1.10","params":{"taskDescription":"

Remember our original statement which simply wanted all students with a non-zero average?<\/p>\n\n

SELECT studentNumber<\/p>\n\n

FROM CourseStudent<\/p>\n\n

GROUP BY studentNumber<\/p>\n\n

HAVING AVG(finalMark) > 0<\/p>\n\n

ORDER BY studentNumber;<\/p>\n\n

What should we do with this statement to instead get the list of all students that are above the global non-zero average we just computed?<\/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":"Replace *0* in the *HAVING* clause with the *subquery*."},"subContentId":"c3b72fed-7f8a-425e-9057-745205cc5bf9","metadata":{"contentType":"Drag Text","license":"U","title":"Subquery 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":"c9951da0-cf0f-4219-b061-af400146d8e1","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":"d936e311-9d54-43f9-a0bd-d27819e820e5","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."}}

Subqueries

Chris Lo