MS SQL:
Question 1
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 Last Frontier  
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 Profe
MULTV 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] — GO
Question 2  
HD Channels
———–
Find channels in the CHANNEL table where there is a matching HDchannel. For this problem,
only retrieve channels where the DisplayName of the standarddefinition channel matches the
DisplayName of the HD channel with an HD added to the end (ignorethe case where, for example,
SYFYP matches SYFYHDP). Produce results as shown below, using 10characters for both display
names, and ordering by Standard Name. Hint: JOIN CHANNEL to itself and alias the tables using AS. Hint 2: The correct answer will have 58 rows and will look likethis: Standard Channel Standard Name HD Channel HD Name
—————- ————- ———– ———-
71 AMCP 1405 AMCPHD
129 BLOOM 1122 BLOOMHD   
412 CBSSN 1303 CBSSNHD   
46 CNBC 1121 CNBCHD
44 CNN 1111 CNNHD   
184 COOK 1485 COOKHD
24 CSPAN 1128 CSPANHD   
201 DEST 1487 DESTHD
121 DFC 1714 DFCHD   
70 EP 1466 EPHD
35 ESPN 1205 ESPNHD
36 ESPN2 1206 ESPN2HD   
609 ESPND 1231 ESPNDHD   
411 ESPNU 1301 ESPNUHD   
130 FBN 1123 FBNHD   
…
276 VICEP 1436 VICEPHD   
47 WEATH 1102 WEATHHD   
9 WGNAP 1420 WGNAPHD   
‘ + CHAR(10) GO —
— [Insert your code here] — GO
Question 3  
HD Channels Part 2
——————
Repeat the previous query, except this time use a CROSS JOIN. Add aWHERE
clause that matches display names that end in P with display namesthat end in
HDP. Note: There are ways to do this that don”t require a CROSSJOIN, but use a CROSS
JOIN anyway. Hint: The function LEFT will return the left part of a string.For example, given
a column like “Animal”, with a value like “fruitbat”, the functionLEFT(Animal, 5)
will return the value “fruit”. The function RIGHT(Animal, 3) willreturn the value
“bat”. The function LEN(Animal) will return 8. Hint 2: The correct answer will look like this: Standard Channel Standard Name HD Channel HD Name
—————- ————- ———– ———-
52 AETVP 1402 AETVHDP   
43 APLP 1471 APLHDP
56 BETP 1625 BETHDP
61 CMTVP 1608 CMTVHDP   
41 DISNP 1715 DISNHDP   
7 DSCP 1449 DSCHDP
122 DXDP 1716 DXDHDP
551 HBOP 1803 HBOHDP
561 MAXP 1821 MAXHDP
576 SHOWP 1841 SHOWHDP   
59 SYFYP 1411 SYFYHDP   
55 TBSP 1434 TBSHDP
591 TMCP 1861 TMCHDP
58 USAP 1403 USAHDP
‘ + CHAR(10) GO —
— [Insert your code here] — GO
Question 4  
HD Channels Part 3
——————
Use UNION to merge the results from Question 2 and Question 3 intoa single set of results. Be
sure that your final results are ordered by display name. Correct results will have 72 rows and will look like this: Standard Channel Standard Name HD Channel HD Name
—————- ————- ———– ———-
52 AETVP 1402 AETVHDP   
71 AMCP 1405 AMCPHD
43 APLP 1471 APLHDP
56 BETP 1625 BETHDP
129 BLOOM 1122 BLOOMHD   
412 CBSSN 1303 CBSSNHD   
61 CMTVP 1608 CMTVHDP   
46 CNBC 1121 CNBCHD
44 CNN 1111 CNNHD
…
276 VICEP 1436 VICEPHD   
47 WEATH 1102 WEATHHD   
9 WGNAP 1420 WGNAPHD   
‘ + CHAR(10) GO —
— [Insert your code here] —
GO GO
Question 5  
Too late!
———
Find TV episodes in the SHOW table that aren”t currentlyscheduled. Include only results that have
an episode name and a description. Use DISTINCT. To match my results, title and episode should be20 characters wide and description should
be 50 characters wide. Hint: Use an outer join with the SCHEDULE table along with ISNULL on the primary key to find the shows. Correct results will have 360 rows and will look like this: Title Episode Description
——————– ———————————————————————-
12 Corazones Normal Normal: (First aired 9/6/2014)  
90 in 30 Bolivia vs. Chile Bolivia vs. Chile:
A Different World A Stepping Stone A Stepping Stone: Whitley wantsto be the team lea
A Different World Dr. War Is Hell Dr. War Is Hell: Jaleesa andWhitley have new room
A Different World Dream Lover Dream Lover: Whitley is upset by theromantic drea
A Different World Some Enchanted Late Some Enchanted LateAfternoon: Walter and Jaleesa
A Different World Two Gentlemen of Hil Two Gentlemen of Hillman:Dwayne and Ron”s friend
A la Cachi Cachi Por CECYT 15 ”Diódoro A CECYT 15 ”DiódoroAntúnez Echegaray” vs. CECYT 3
Accessorize Your Fal Clarks Clarks:   
Adrenaline Preparation Preparation: The crew preps and plans forthe midw
Alaska: The Last Fro Episode 13 Episode 13:   
…
Wings Legacy Legacy: Brian returns to Nantucket when amysterio
Women”s Volleybal FIVB World Grand Cha FIVB World Grand ChampionsCup: Japan vs. USA: Fro
You Can Do Better Afterhours Afterhours:   
‘ + CHAR(10) GO —
— [Insert your code here] —
GO Attached