WGUl D427l Pre-l Assessmentl V2l (Latestl 2025/l 2026l Update)l Datal Managementl –l Applications|l Questionsl &l Answers|l Gradel A|l 100%l Correctl (Verifiedl Solutions)
Q:l l Thel givenl SQLl createsl al Songl tablel andl insertsl somel songs.l Thel SELECTl statementl selectsl thel genrel andl rowl countl forl eachl genrel group.Addl al newl columnl tol thel SELECTl statementl thatl usesl MAX()l tol findl thel mostl recentl releasel yearl forl eachl genre.l Thenl addl al HAVINGl clausel thatl selectsl onlyl genrel groupsl thatl havel morel thanl onel rowl count.Runl yourl solutionl andl verifyl countryl pop,l R&B,l andl grungel genres,l rowl counts,l andl mostl recentl releasel yearsl appearl inl thel resultl table.
Answer:
CREATEl TABLEl Songl ( l IDl INT, l Titlel VARCHAR(60), l Artistl VARCHAR(60), l ReleaseYearl INT, l Genrel VARCHAR(20), l PRIMARYl KEYl (ID) ); INSERTl INTOl Songl VALUES l (100,l 'Heyl Jude',l 'Beatles',l 1968,l 'popl rock'), l (200,l 'Youl Belongl Withl Me',l 'Taylorl Swift',l 2008,l 'countryl pop'), l (300,l 'You\'rel Stilll thel One',l 'Shanial Twain',l 1998,l 'countryl pop'), l (400,l 'Needl Youl Now',l 'Ladyl Antebellum',l 2011,l 'countryl pop'), l (500,l 'You\'vel Lostl Thatl Lovin\'l Feeling',l 'Thel Righteousl Brothers',l 1964,l 'R&B'), l (600,l 'That\'sl Thel Wayl Lovel Goes',l 'Janetl Jackson',l 1993,l 'R&B'), l (700,l 'Smellsl Likel Teenl Spirit',l 'Nirvana',l 1991,l 'grunge'), l (800,l 'Evenl Flow',l 'Pearll Jam',l 1992,l 'grunge'), l (900,l 'Blackl Holel Sun',l 'Soundgarden',l 1994,l 'grunge');
--l Modifyl thel SELECTl statement SELECTl Genre,l COUNT(*),l MAX(ReleaseYear) FROMl Song 1 / 3
GROUPl BYl Genre HAVINGl COUNTl (*)l >l 1;
Q:l Thel SQLl belowl createsl Genrel andl Songl tables,l insertsl somel genresl andl songs,l andl performsl anl innerl join.Runl thel SQL.l Verifyl thel resultl tablel doesl notl includel songsl withl NULLl genrel orl genresl thatl arel notl associatedl withl songs.
Makel thel followingl changes:
Inl thel CREATEl TABLEl statementl forl Song,l renamel GenreCodel tol Code.l Modifyl thel SELECTl statementl tol workl withl thel newl name.l Runl thel SQLl andl verifyl thel resultl tablel isl unchanged.Modifyl thel SELECTl statementl tol performl al leftl join.l Runl thel SQLl andl verifyl thel resultl tablel includesl songsl withl NULLl genre.Modifyl thel SELECTl statementl tol performl al rightl join.l Runl thel SQLl andl verifyl thel resultl tablel includesl genresl thatl arel notl associatedl withl anyl songs.Combinel thel leftl andl rightl joinsl intol onel statementl thatl performsl al fulll join.l Runl thel SQLl andl verifyl thel resultl tablel includesl alll songsl andl genres.Hints:l Inl stepsl 2l andl 3,l usel keywordsl LEFTl andl RIGHT.l Inl stepl 4,l usel key
Answer:
SELECTl * FROMl Song INNERl JOINl Genre ONl GenreCodel =l Code;
Q:l Thel givenl SQLl createsl al Songl tablel andl insertsl somel songs.l Thel firstl SELECTl statementl selectsl songsl releasedl afterl 1992.l Thel secondl SELECTl statementl selectsl thel releasel yearl forl songl withl IDl 800.Createl al thirdl queryl thatl combinesl thel twol existingl queries.l Thel firstl SELECTl shouldl bel thel outerl query,l andl thel secondl SELECTl shouldl bel thel subquery.l Thel ORDERl BYl clausel shouldl appearl afterl thel subquery.Runl yourl solutionl andl verifyl thel newl queryl returnsl al resultl tablel withl fivel rows,l alll withl releasel yearsl afterl 1992.
Answer:
CREATEl TABLEl Songl ( l IDl INT, l Titlel VARCHAR(60), l Artistl VARCHAR(60), l ReleaseYearl INT, 2 / 3
l Genrel VARCHAR(20), l PRIMARYl KEYl (ID) ); INSERTl INTOl Songl VALUES l (100,l 'Heyl Jude',l 'Beatles',l 1968,l 'popl rock'), l (200,l 'Youl Belongl Withl Me',l 'Taylorl Swift',l 2008,l 'countryl pop'), l (300,l 'You\'rel Stilll thel One',l 'Shanial Twain',l 1998,l 'countryl pop'), l (400,l 'Needl Youl Now',l 'Ladyl Antebellum',l 2011,l 'countryl pop'), l (500,l 'You\'vel Lostl Thatl Lovin\'l Feeling',l 'Thel Righteousl Brothers',l 1964,l 'R&B'), l (600,l 'That\'sl Thel Wayl Lovel Goes',l 'Janetl Jackson',l 1993,l 'R&B'), l (700,l 'Smellsl Likel Teenl Spirit',l 'Nirvana',l 1991,l 'grunge'), l (800,l 'Evenl Flow',l 'Pearll Jam',l 1992,l 'grunge'), l (900,l 'Blackl Holel Sun',l 'Soundgarden',l 1994,l 'grunge'); SELECTl * FROMl Song WHEREl ReleaseYearl >l 1992 ORDERl BYl ReleaseYear; SELECTl ReleaseYear FROMl Songl WHEREl IDl =l 800;
Q:l Thel Moviel tablel hasl thel followingl columns: IDl -l integer,l primaryl key Titlel -l variable-lengthl string Genrel -l variable-lengthl string RatingCodel -l variable-lengthl string Yearl -l integer Writel al SELECTl statementl tol selectl thel yearl andl thel totall numberl ofl moviesl forl thatl year.
Hint:l Usel thel COUNT()l functionl andl GROUPl BYl clause.
Answer:
SELECTl YEAR,l COUNT(*)l ASl TNMYear FROMl MOVIE GROUPl BYl YEAR;
Q:l Thel Moviel tablel hasl thel followingl columns: IDl -l integer,l primaryl key Titlel -l variable-lengthl string Genrel -l variable-lengthl string
- / 3