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 the JOINs.

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 ------------ -------------- ---------- -------- ------------------------------ 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 Pea 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 HD channel. For this problem, only retrieve channels where the DisplayName of the standard definition channel matches the DisplayName of the HD channel with an HD added to the end (ignore the case where, for example, SYFYP matches SYFYHDP). Produce results as shown below, using 10 characters 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 like this:

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 a WHERE clause that matches display names that end in P with display names that end in HDP.

Note: There are ways to do this that don''t require a CROSS JOIN, 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 function LEFT(Animal, 5) will return the value "fruit". The function RIGHT(Animal, 3) will return 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 into a 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 currently scheduled. Include only results that have an episode name and a description.

Use DISTINCT. To match my results, title and episode should be 20 characters wide and description should be 50 characters wide.

Hint: Use an outer join with the SCHEDULE table along with IS NULL 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 wants to be the team lea A Different World Dr. War Is Hell Dr. War Is Hell: Jaleesa and Whitley have new room A Different World Dream Lover Dream Lover: Whitley is upset by the romantic drea A Different World Some Enchanted Late Some Enchanted Late Afternoon: 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 ''Didoro A CECYT 15 ''Didoro Antnez Echegaray'' vs. CECYT 3 Accessorize Your Fal Clarks Clarks: Adrenaline Preparation Preparation: The crew preps and plans for the midw Alaska: The Last Fro Episode 13 Episode 13: ... Wings Legacy Legacy: Brian returns to Nantucket when a mysterio Women''s Volleybal FIVB World Grand Cha FIVB World Grand Champions Cup: Japan vs. USA: Fro You Can Do Better Afterhours Afterhours: ' + CHAR(10)

GO

-- -- [Insert your code here]

-- GO