CIS 275, Lab Week 3, Question 1 [3pts possible]: What is on TV? --------------- This is an exact repeat of Question 7 f
Posted: Thu Jul 14, 2022 2:06 pm
CIS 275, Lab Week 3, Question 1 [3pts possible]:What is on TV?---------------This is an exact repeat of Question 7 from Lab Week 2. However,instead of using the all_data view, instead JOIN the CHANNEL, SHOW, and SCHEDULE tables together.Consult the ERD (if needed)to determine which columns to match in the ON parts of theJOINs.
Show the first 100 TV shows that start on 9/10/2017 at 8:00PM(StartTime is 20:00:00).
Display results in order by ChannelNumber.
Show ONLY the DisplayName, ChannelNumber, StartTime, EndTime,and Title.
Use CONVERT to format the StartTime and EndTime hh:mi:ss withoutthe day, month, or year.
Use CAST or CONVERT make DisplayName 10 characters wide, andTitle 30 characters wide.
Make sure all columns have appropriate names (using AS whereneeded).
Hint: A DATETIME column can be matched against a string like8/30/1962 13:00:00.
Hint 2: Correct results will have 100 rows, and look likethis:
Channel Name Channel Number Start Time End Time Title------------ -------------- ---------- --------------------------------------KATU 2 20:00:00 21:00:00 Celebrity Family Feud KRCW 3 20:00:00 20:30:00 Two and a Half Men KPXG 5 20:00:00 21:00:00 Law & Order:Criminal Intent KOIN 6 20:00:00 21:00:00 Big Brother DSCP 7 20:00:00 21:00:00 Alaska: The LastFrontier WGNAP 9 20:00:00 21:00:00 Blue Bloods KOPB 10 20:00:00 21:30:00 The Carpenters: Close to You(KPTV 12 20:00:00 21:00:00 The Orville KPDX 13 20:00:00 21:00:00 Rookie Blue TELEP 15 20:00:00 00:00:00 Ad Channel QVC 16 20:00:00 21:00:00 Today''s Top Tech ...MEXCAN 625 20:00:00 20:30:00 Liga Mexicana de Jaripeo ProfeMULTV 626 20:00:00 22:00:00 Poncho en Domingo TEFEI 629 20:00:00 22:30:00 La Peña de Morfi ' + CHAR(10)
GO
USE TV
---- [Insert your code here] SELECT TOP 100CONVERT(VARCHAR(10),CHANNEL.DisplayName) AS 'Channel Name', CHANNEL.ChannelNumber AS 'Channel Number', FORMAT(SCHEDULE.StartTime,'HH:mm:ss') AS 'StartNumber', FORMAT(SCHEDULE.EndTime, 'HH:mm:ss') AS 'EndTime', CONVERT(VARCHAR(30), SHOW.Title) AS'Title' FROM SHOW, SCHEDULE, CHANNEL WHERE SHOW.ShowID = SCHEDULE.FK_ShowID AND SCHEDULE.FK_ChannelID =CHANNEL.ChannelID AND SCHEDULE.StartTime = '2017-09-1020:00:00' ORDER BY CHANNEL.ChannelNumber
Show the first 100 TV shows that start on 9/10/2017 at 8:00PM(StartTime is 20:00:00).
Display results in order by ChannelNumber.
Show ONLY the DisplayName, ChannelNumber, StartTime, EndTime,and Title.
Use CONVERT to format the StartTime and EndTime hh:mi:ss withoutthe day, month, or year.
Use CAST or CONVERT make DisplayName 10 characters wide, andTitle 30 characters wide.
Make sure all columns have appropriate names (using AS whereneeded).
Hint: A DATETIME column can be matched against a string like8/30/1962 13:00:00.
Hint 2: Correct results will have 100 rows, and look likethis:
Channel Name Channel Number Start Time End Time Title------------ -------------- ---------- --------------------------------------KATU 2 20:00:00 21:00:00 Celebrity Family Feud KRCW 3 20:00:00 20:30:00 Two and a Half Men KPXG 5 20:00:00 21:00:00 Law & Order:Criminal Intent KOIN 6 20:00:00 21:00:00 Big Brother DSCP 7 20:00:00 21:00:00 Alaska: The LastFrontier WGNAP 9 20:00:00 21:00:00 Blue Bloods KOPB 10 20:00:00 21:30:00 The Carpenters: Close to You(KPTV 12 20:00:00 21:00:00 The Orville KPDX 13 20:00:00 21:00:00 Rookie Blue TELEP 15 20:00:00 00:00:00 Ad Channel QVC 16 20:00:00 21:00:00 Today''s Top Tech ...MEXCAN 625 20:00:00 20:30:00 Liga Mexicana de Jaripeo ProfeMULTV 626 20:00:00 22:00:00 Poncho en Domingo TEFEI 629 20:00:00 22:30:00 La Peña de Morfi ' + CHAR(10)
GO
USE TV
---- [Insert your code here] SELECT TOP 100CONVERT(VARCHAR(10),CHANNEL.DisplayName) AS 'Channel Name', CHANNEL.ChannelNumber AS 'Channel Number', FORMAT(SCHEDULE.StartTime,'HH:mm:ss') AS 'StartNumber', FORMAT(SCHEDULE.EndTime, 'HH:mm:ss') AS 'EndTime', CONVERT(VARCHAR(30), SHOW.Title) AS'Title' FROM SHOW, SCHEDULE, CHANNEL WHERE SHOW.ShowID = SCHEDULE.FK_ShowID AND SCHEDULE.FK_ChannelID =CHANNEL.ChannelID AND SCHEDULE.StartTime = '2017-09-1020:00:00' ORDER BY CHANNEL.ChannelNumber