Need some help I am stuck at the WHERE clause. I do not know what to put there. The rest of the code should be correct.
Posted: Tue Jul 12, 2022 8:20 am
Need some help I am stuck at the WHERE clause. I do not knowwhat to put there. The rest of the code should be correct. This isa beginner level SQL course for my college
Daily Listing for SYFYHDP-------------------------Using the all_data view in the TV database, find all listings forshows on SYFYHDP that wereon any time on 9/12/2017. Do NOT include shows that end at 00:00:00on 9/12/2017, or beginat 00:00:00 on 9/13/2017. DO include shows that start beforemidnight on 9/12 but end aftermidnight, or start before midnight on 9/12 and end after midnighton 9/13 (see results belowif that isn''t clear).
Include the following columns:Time - formatted exactly as HH:MM:SS - HH:MM:SS for start time -end time.Title - Limit to 20 characters wide.Length - formatted exactly as HH:MM:SS.Description - Limit to 50 characters wide.
Display results in order by start time.
Hint: Visit this page for the section on Date and TimeStyles:https://docs.microsoft.com/en-us/sql/t- ... erver-2017
Hint 2: To get the Length, subtract the start time from the endtime and convert theresult using the appropriate date format.
Hint 3: You can concatenate string values in the SELECT clauseby adding them together with +.
Hint 4: Correct results will have 19 rows and will look likethis:
Time Title Length Description------------------- -------------------- ----------------------------------------------------------23:00:00 - 01:00:00 Lake Placid 02:00:00 A New York paleontologist (Bridget Fonda) goesto 01:00:00 - 03:00:00 Jeepers Creepers 02:00:00 Acloaked figure (Jonathan Breck) terrorizes two s03:00:00 - 04:00:00 The Twilight Zone 01:00:00 Mute:Parents raise their daughter (Ann Jillian) w04:00:00 - 04:30:00 Paid Programming 00:30:00 Paidprogramming. (HDTV) 04:30:00 - 05:00:00 Paid Programming 00:30:00 Paidprogramming. (HDTV) 05:00:00 - 05:30:00 Paid Programming 00:30:00 Paidprogramming. (HDTV) 05:30:00 - 06:00:00 Paid Programming 00:30:00 Paidprogramming. (HDTV) 06:00:00 - 07:00:00 CSI: Crime Scene Inv 01:00:00 19 Down...: Whenthe team discovers a connection b07:00:00 - 08:00:00 CSI: Crime Scene Inv 01:00:00 One to Go:Grissom announces that he is leaving, w08:00:00 - 09:00:00 CSI: Crime Scene Inv 01:00:00 The Grave Shift:Dr. Langston''s first day on the 09:00:00 - 10:00:00 CSI: Crime Scene Inv 01:00:00 Disarmed &Dangerous: A specialized team of forens10:00:00 - 11:00:00 CSI: Crime Scene Inv 01:00:00 Deep Fried &Minty Fresh: The team investigates a 11:00:00 - 12:00:00 CSI: Crime Scene Inv 01:00:00 Miscarriage ofJustice: As Langston testifies at t12:00:00 - 14:00:00 Seventh Son 02:00:00 A supernatural champion (Jeff Bridges) haslittle 14:00:00 - 17:00:00 Need for Speed 03:00:00Determined to take down his treacherous rival (Dom17:00:00 - 18:57:00 Lake Placid 01:57:00 A New York paleontologist (Bridget Fonda) goesto 18:57:00 - 21:00:00 Lake Placid 2 02:03:00 A sheriff, a big-game hunter and a wildlifeoffice21:00:00 - 22:00:00 Face Off: Game Face 01:00:00 Stone ColdSuperheroes: The artists create plant-h22:00:00 - 00:30:00 300 02:30:00 Sparta''s King Leonidas (GerardButler) and his ba' + CHAR(10)
GO
---- [Insert your code here] NEEDS WORK!
SELECT FORMAT(SCHEDULE.StartTime,'HH:mm:ss') AS'Start Number', FORMAT(SCHEDULE.EndTime, 'HH:mm:ss') As 'EndTime', CONVERT(VARCHAR(20), SHOW.Title) AS'Title', CONVERT(VARCHAR(50), SHOW.Description) AS'Description' FROM all_data WHERE
Daily Listing for SYFYHDP-------------------------Using the all_data view in the TV database, find all listings forshows on SYFYHDP that wereon any time on 9/12/2017. Do NOT include shows that end at 00:00:00on 9/12/2017, or beginat 00:00:00 on 9/13/2017. DO include shows that start beforemidnight on 9/12 but end aftermidnight, or start before midnight on 9/12 and end after midnighton 9/13 (see results belowif that isn''t clear).
Include the following columns:Time - formatted exactly as HH:MM:SS - HH:MM:SS for start time -end time.Title - Limit to 20 characters wide.Length - formatted exactly as HH:MM:SS.Description - Limit to 50 characters wide.
Display results in order by start time.
Hint: Visit this page for the section on Date and TimeStyles:https://docs.microsoft.com/en-us/sql/t- ... erver-2017
Hint 2: To get the Length, subtract the start time from the endtime and convert theresult using the appropriate date format.
Hint 3: You can concatenate string values in the SELECT clauseby adding them together with +.
Hint 4: Correct results will have 19 rows and will look likethis:
Time Title Length Description------------------- -------------------- ----------------------------------------------------------23:00:00 - 01:00:00 Lake Placid 02:00:00 A New York paleontologist (Bridget Fonda) goesto 01:00:00 - 03:00:00 Jeepers Creepers 02:00:00 Acloaked figure (Jonathan Breck) terrorizes two s03:00:00 - 04:00:00 The Twilight Zone 01:00:00 Mute:Parents raise their daughter (Ann Jillian) w04:00:00 - 04:30:00 Paid Programming 00:30:00 Paidprogramming. (HDTV) 04:30:00 - 05:00:00 Paid Programming 00:30:00 Paidprogramming. (HDTV) 05:00:00 - 05:30:00 Paid Programming 00:30:00 Paidprogramming. (HDTV) 05:30:00 - 06:00:00 Paid Programming 00:30:00 Paidprogramming. (HDTV) 06:00:00 - 07:00:00 CSI: Crime Scene Inv 01:00:00 19 Down...: Whenthe team discovers a connection b07:00:00 - 08:00:00 CSI: Crime Scene Inv 01:00:00 One to Go:Grissom announces that he is leaving, w08:00:00 - 09:00:00 CSI: Crime Scene Inv 01:00:00 The Grave Shift:Dr. Langston''s first day on the 09:00:00 - 10:00:00 CSI: Crime Scene Inv 01:00:00 Disarmed &Dangerous: A specialized team of forens10:00:00 - 11:00:00 CSI: Crime Scene Inv 01:00:00 Deep Fried &Minty Fresh: The team investigates a 11:00:00 - 12:00:00 CSI: Crime Scene Inv 01:00:00 Miscarriage ofJustice: As Langston testifies at t12:00:00 - 14:00:00 Seventh Son 02:00:00 A supernatural champion (Jeff Bridges) haslittle 14:00:00 - 17:00:00 Need for Speed 03:00:00Determined to take down his treacherous rival (Dom17:00:00 - 18:57:00 Lake Placid 01:57:00 A New York paleontologist (Bridget Fonda) goesto 18:57:00 - 21:00:00 Lake Placid 2 02:03:00 A sheriff, a big-game hunter and a wildlifeoffice21:00:00 - 22:00:00 Face Off: Game Face 01:00:00 Stone ColdSuperheroes: The artists create plant-h22:00:00 - 00:30:00 300 02:30:00 Sparta''s King Leonidas (GerardButler) and his ba' + CHAR(10)
GO
---- [Insert your code here] NEEDS WORK!
SELECT FORMAT(SCHEDULE.StartTime,'HH:mm:ss') AS'Start Number', FORMAT(SCHEDULE.EndTime, 'HH:mm:ss') As 'EndTime', CONVERT(VARCHAR(20), SHOW.Title) AS'Title', CONVERT(VARCHAR(50), SHOW.Description) AS'Description' FROM all_data WHERE