CIS 275, Lab Week 2, Question 1 [3pts possible]:What channel is SYFY?- XXXXXXXXXXWrite a SELECT statement that finds the channels named SYFYP and SYFYHDP in the CHANNEL table of the TVdatabase. Use a WHERE clause with an OR statement to return only these two channels.
Display only the ChannelNumber and DisplayName columns.
Hint: Correct results will look like this (note that DisplayName is 200 characters wide, so I've edited a bit):
ChannelNumber DisplayName----- XXXXXXXXXX XXXXXXXXXXSYFYP XXXXXXXXXXSYFYHDP
CIS 275, Lab Week 2, Question 2 [3pts possible]:What channel is OPB?- XXXXXXXXXXWrite a SELECT statement that finds all the channels with OPB in the display name.Use a WHERE clause with LIKE and wildcards to find all matching channels.
Display only the ChannelNumber and DisplayName columns.
This time, use CONVERT to make the DisplayName be 10 characters wide. Use AS to give your column a name.
Display results in ascending order by ChannelNumber.
Hint: Correct results will look like this:
ChannelNumber ChannelName----- XXXXXXXXXX XXXXXXXXXXKOPB XXXXXXXXXXKOPBDT XXXXXXXXXXKOPBDT XXXXXXXXXXKOPBDT3
CIS 275, Lab Week 2, Question 3 [3pts possible]:What are the genres? XXXXXXXXXXProduce a list of the distinct genres, sorted in alphabetical order by genre.
Display the genre with no more than 20 characters wide. Make sure your genre column has a column name.
Hint: Genre is a column in the SHOW table.
Hint 2: Use the DISTINCT keyword to get the distinct list.
Hint 3: Correct results will have 114 rows and will look like this:
Genre---------- XXXXXXXXXXAction Action sports Adults only Adventure Agriculture Animals Anthology Art XXXXXXXXXXArts/crafts Auction Auto Auto racing Aviation Baseball Basketball ...Weather Western Wrestling
CIS 275, Lab Week 2, Question 4 [3pts possible]:Best SF? XXXXXXXXXXFind the top rated family safe sci fi shows in the SHOW table. Include all shows where the Genre is sci fi, theStarRating is 8 or over, and the Classification is either G, PG, or PG-13. Use IN with a set of values to matchthe Classification.
Produce the following columns: Title, Description, StarRating, Classification. Title should be no more than 20characters wide and Description should be no more than 50 characters wide. Classification should be no morethan 5 characters wide. Don't forget to give all columns names!
Order in descending order by star rating. For shows with the same star rating, order by title alphabetically (A-Z).
Hint: Use the results in the previous query to determine the correct spelling for the sci fi genre.
Hint 2: Correct results will have 24 rows and will look like this (I did a bit of minor editing becauseof apostrophes):
Title XXXXXXXXXXDescription XXXXXXXXXXStarRating Classification-------------------- -------------------------------------------------- ------ XXXXXXXXXXAvatar On an alien planet, a former Marine (Sam Worthingt XXXXXXXXXXPG-13Gravity The destruction of their shuttle leaves two astron XXXXXXXXXXPG-13Minority Report A policeman (Tom Cruise) tries to establish his in XXXXXXXXXXPG-13Star Wars: The Force Thirty years after the defeat of the Galactic Empi XXXXXXXXXXPG-13The Day the Earth St Klaatu (Michael Rennie) and his guardian robot, Go XXXXXXXXXXG Dawn of the Planet o Human survivors of a plague threaten Caesar's gro 8 PG-13Fantastic Planet The 39-foot-tall pastel Draags keep leashes on the 8 PG Gattaca An outcast (Ethan Hawke) takes part in a complicat 8 PG-13Guardians of the Gal A space adventurer (Chris Pratt) becomes the quarr 8 PG-13I Am Legend After a man-made plague transforms Earth's popula 8 PG-13Independence Day A fighter pilot (Will Smith), a computer whiz (Jef 8 PG-13Interstellar As mankind's time on Earth comes to an end, a gro 8 PG-13Invasion of the Body San Francisco health inspectors (Donald Sutherland 8 PG Midnight Special The government and a group of religious extremists 8 PG-13Pacific Rim A washed-up ex-pilot (Charlie Hunnam) and an untes 8 PG-13Rise of the Planet o A scientist's (James Franco) quest to find a cur 8 PG-13Serenity Crew members (Nathan Fillion, Gina Torres, Alan Tu 8 PG-13Solaris A widowed psychologist (George Clooney) arrives at 8 PG-13The Abyss Oil-platform workers, including an estranged coupl 8 PG-13The Fifth Element A New York City cabdriver (Bruce Willis) tries to 8 PG-13The Hunger Games A resourceful teen (Jennifer Lawrence) takes her y 8 PG-13The Hunger Games: Ca After their unprecedented victory in the 74th Hung 8 PG-13Twilight Zone: The M Four tales include a bigot (Vic Morrow), oldsters 8 PG Westworld Androids go haywire with guests (Richard Benjamin, 8 PG
CIS 275, Lab Week 2, Question 5 [3pts possible]:HD Stations XXXXXXXXXXSelect the DisplayName and SortOrder from the CHANNEL table for all TV channels where the SortOrder isbetween 700 and 799 inclusive. Use the BETWEEN ... AND ... operator. Exclude all rows where the ExternalIDis NULL. Use "Channel Name" as the header for the name column and "Sort Order" for the sort order column.Display results in ascending order by SortOrder.
Hint: The correct answer will have 93 rows and will look like this:
Channel Name Sort Order---- XXXXXXXXXXKATUDT 702KRCWDT 703KPXGDT 705KOINDT 706DSCHDP 707KGWDT 708WGNAPHD 709KOPBDT 710VEL 711KPTVDT 712KPDXDT 713FUSEHD XXXXXXXXXXUPHD 797AXSTV 798NFLNRZD 799
CIS 275, Lab Week 2, Question 6 [3pts possible]:Daily Listing for SYFYHDP----- XXXXXXXXXXUsing the all_data view in the TV database, find all listings for shows on SYFYHDP that wereon any time on 9/12/2017. Do NOT include shows that end at 00:00:00 on 9/12/2017, or beginat 00:00:00 on 9/13/2017. DO include shows that start before midnight on 9/12 but end aftermidnight, or start before midnight on 9/12 and end after midnight on 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 Time Styles:https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
Hint 2: To get the Length, subtract the start time from the end time and convert theresult using the appropriate date format.
Hint 3: You can concatenate string values in the SELECT clause by adding them together with +.
Hint 4: Correct results will have 19 rows and will look like this:
Time XXXXXXXXXXTitle XXXXXXXXXXLength Description------------------- -------------------- -------- ------------------------------ XXXXXXXXXX23:00:00 - 01:00:00 Lake Placid 02:00:00 A New York paleontologist (Bridget Fonda) goes to01:00:00 - 03:00:00 Jeepers Creepers 02:00:00 A cloaked 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 Paid programming. (HDTV XXXXXXXXXX:30:00 - 05:00:00 Paid Programming 00:30:00 Paid programming. (HDTV XXXXXXXXXX:00:00 - 05:30:00 Paid Programming 00:30:00 Paid programming. (HDTV XXXXXXXXXX:30:00 - 06:00:00 Paid Programming 00:30:00 Paid programming. (HDTV XXXXXXXXXX:00:00 - 07:00:00 CSI: Crime Scene Inv 01:00:00 19 Down...: When the 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 the09: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 a11:00:00 - 12:00:00 CSI: Crime Scene Inv 01:00:00 Miscarriage of Justice: As Langston testifies at t12:00:00 - 14:00:00 Seventh Son 02:00:00 A supernatural champion (Jeff Bridges) has little14:00:00 - 17:00:00 Need for Speed 03:00:00 Determined to take down his treacherous rival (Dom17:00:00 - 18:57:00 Lake Placid 01:57:00 A New York paleontologist (Bridget Fonda) goes to18:57:00 - 21:00:00 Lake Placid XXXXXXXXXX:03:00 A sheriff, a big-game hunter and a wildlife office21:00:00 - 22:00:00 Face Off: Game Face 01:00:00 Stone Cold Superheroes: The artists create plant-h22:00:00 - 00:30: XXXXXXXXXX:30:00 Sparta's King Leonidas (Gerard Butler) and his ba
CIS 275, Lab Week 2, Question 7 [3pts possible]:What is on TV? XXXXXXXXXXUsing the all_data view in the TV database, 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 without the day, month, or year.
Use CAST or CONVERT make DisplayName 10 characters wide, and Title 30 characters wide.
Make sure all columns have appropriate names (using AS where needed).
Hint: A DATETIME column can be matched against a string like 8/30/1962 13:00:00.
Hint 2: Correct results will have 100 rows, and look like this:
Channel Name Channel Number Start Time End Time Title------------ -------------- ---------- -------- ---------- XXXXXXXXXXKATU XXXXXXXXXX:00:00 21:00:00 Celebrity Family Feud KRCW XXXXXXXXXX:00:00 20:30:00 Two and a Half Men KPXG XXXXXXXXXX:00:00 21:00:00 Law & Order: Criminal IntentKOIN XXXXXXXXXX:00:00 21:00:00 Big Brother XXXXXXXXXXDSCP XXXXXXXXXX:00:00 21:00:00 Alaska: The Last Frontier WGNAP XXXXXXXXXX:00:00 21:00:00 Blue Bloods XXXXXXXXXXKOPB XXXXXXXXXX:00:00 21:30:00 The Carpenters: Close to You (KPTV XXXXXXXXXX:00:00 21:00:00 The Orville XXXXXXXXXXKPDX XXXXXXXXXX:00:00 21:00:00 Rookie Blue XXXXXXXXXXTELEP XXXXXXXXXX:00:00 00:00:00 Ad Channel XXXXXXXXXXQVC XXXXXXXXXX:00:00 21:00:00 Today's Top Tech XXXXXXXXXXMEXCAN XXXXXXXXXX:00:00 20:30:00 Liga Mexicana de Jaripeo ProfeMULTV XXXXXXXXXX:00:00 22:00:00 Poncho en Domingo TEFEI XXXXXXXXXX:00:00 22:30:00 La Peña de Morfi
CIS 275, Lab Week 2, Question 8 [3pts possible]:Rick and Morty XXXXXXXXXXShow a distinct list of all Rick and Morty episodes in the SHOW table. Include the following columns:
Title - 20 wide.Episode - 20 wide.Series # - 10 wide.Episode # - 3 wide.Original Air Date - 12 wide, formatted as, for example, "Apr 01, 2017".
Display results in order by episode number.
Correct results will look like this:
Title XXXXXXXXXXEpisode Series # Episode # Original Air Date-------------------- -------------------- ---------- ------- XXXXXXXXXXRick and Morty The Rickshank Rickde EP XXXXXXXXXXApr 01, 2017Rick and Morty Rickmancing the Ston EP XXXXXXXXXXJul 30, 2017Rick and Morty Pickle Rick EP XXXXXXXXXXAug 06, 2017Rick and Morty Vindicators 3: The R EP XXXXXXXXXXAug 13, 2017Rick and Morty The Whirly Dirly Con EP XXXXXXXXXXAug 20, 2017Rick and Morty Rest and Ricklaxatio EP XXXXXXXXXXAug 27, 2017Rick and Morty The Ricklantis Mixup EP XXXXXXXXXXSep 10, 2017
CIS 275, Lab Week 2, Question 9 [3pts possible]:Couch Potato Achievement---- XXXXXXXXXXProduce a list of the 10 longest shows. Exclude sports-related genres and shows where the titleis "To Be Announced" or "SIGN OFF". Format your results to look like this:
Channel Name Title XXXXXXXXXXEpisode Date Time XXXXXXXXXXLength------------ -------------------- -------------------- ------------ -------- XXXXXXXXXXBBCAPH Doctor Who Deep Breath Sep 08, XXXXXXXXXX:30:00 - 12:30:00 08:00:00BBCAPH Doctor Who Deep Breath Sep 08, XXXXXXXXXX:30:00 - 20:30:00 08:00:00BBCAPH Doctor Who Deep Breath Sep 08, XXXXXXXXXX:30:00 - 04:30:00 08:00:00CSPAN2 Public Affairs Event N/A XXXXXXXXXXSep 08, XXXXXXXXXX:00:00 - 05:00:00 08:00:00CSPAN2 Book TV N/A XXXXXXXXXXSep 10, XXXXXXXXXX:00:00 - 18:00:00 08:00:00BBCAP Doctor Who Deep Breath Sep 08, XXXXXXXXXX:30:00 - 12:30:00 08:00:00BBCAP Doctor Who Deep Breath Sep 08, XXXXXXXXXX:30:00 - 20:30:00 08:00:00BBCAP Doctor Who Deep Breath Sep 08, XXXXXXXXXX:30:00 - 04:30:00 08:00:00TVMRT Beyond Today N/A XXXXXXXXXXSep 03, XXXXXXXXXX:30:00 - 03:30:00 08:00:00TVMRT Beyond Today N/A XXXXXXXXXXSep 04, XXXXXXXXXX:30:00 - 11:30:00 08:00:00
Hint: Use ISNULL to display NULL values in Episode as N/A, and restrict them to no more than 20characters wide.
CIS 275, Lab Week 2, Question 10 [3pts possible]:It's a Good Life XXXXXXXXXXFind all showings of The Twilight Zone episode "It's a Good Life." Format your results exactlyas they appear below.
Hint: You can convert the same DATETIME twice, using two different format codes, and concatenate theresults together into a single string.
Correct output will look like this (only two columns):
Channel Name Time------------ ------------- XXXXXXXXXXSYFYP Sep 15, 2017: 06:00:00 - 06:30:00SYFYHDP Sep 15, 2017: 06:00:00 - 06:30:00SYFYP Sep 16, 2017: 05:00:00 - 05:30:00SYFYHDP Sep 16, 2017: 05:00:00 - 05:30:00