SQL assignment
GO
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 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 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 ''Diódoro A CECYT 15 ''Diódoro Antúnez 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
GO
Question 6
Eraserhead
----------
We''ll be switching over to the IMDB database for the remaining questions this week.
Show all principals listed for the movie "Eraserhead." For each principal, show the name of the movie,
the category, and the name of the principal. Also include the characters that actors and actresses
played (display N/A for NULL values). Format all columns to 20 characters wide.
Display in order by name.
Hint: title_basics has information about shows and name_basics has information about people.
title_principals links people to shows with information about the what kind of job the
person had on the show. Use the category column in title_principals here, rather than the job column.
Correct results will look like this:
Title Category Name Characters
-------------------- -------------------- -------------------- --------------------
Eraserhead actor Allen Joseph ["Mr. X"]
Eraserhead actress Charlotte Stewart ["Mary X"]
Eraserhead director David Lynch N/A
Eraserhead cinematographer Frederick Elmes N/A
Eraserhead cinematographer Herbert Cardwell N/A
Eraserhead actor Jack Nance ["Henry Spencer"]
Eraserhead actress Jeanne Bates ["Mrs. X"]
' + CHAR(10)
GO
USE IMDB
--
-- [Insert your code here]
--
GO
GO
Question 7
Quentin Tarantino
-----------------
What genres has Quentin Tarantino directed? Produce the title, type, year and genre
for shows where Quentin Tarantino was the director. Format Title as 30 characters wide
and genre as 20 characters wide. Order by Year.
Hint: title_directors has information about which people directed which shows. title_genres
has information about what genres (up to 3) a particular show belongs to.
Correct results will have 63 rows formatted as:
Title Type Year Genre
------------------------------ ------------ ----------- --------------------
Untitled Star Trek Project movie NULL Action
Untitled Star Trek Project movie NULL Adventure
Untitled Star Trek Project movie NULL Sci-Fi
Love Birds in Bondage short 1983 Comedy
Love Birds in Bondage short 1983 Drama
Love Birds in Bondage short 1983 Short
My Best Friend''s Birthday movie 1987 Comedy
Reservoir Dogs: Sundance Insti video 1991 Drama
Reservoir Dogs: Sundance Insti video 1991 Short
Reservoir Dogs movie 1992 Crime
...
Untitled Quentin Tarantino/196 movie 2019 Crime
Untitled Quentin Tarantino/196 movie 2019 Drama
Untitled Quentin Tarantino/196 movie 2019 Thriller
' + CHAR(10)
GO
--
-- [Insert your code here]
--
GO
GO
Question 8
Quentin Tarantino''s popular movies
-----------------------------------
Produce a list of all of Quentin Tarantino''s movies in descending order by rating. Format title as 30 characters
wide. Your results should appear as below (I escaped the apostrophe in the last result).
Hint: title_ratings contains the number of votes and average rating for each show.
Title Type Year Rating
------------------------------ ------------ ----------- ---------------------------------------
Pulp Fiction movie 1994 8.9
Kill Bill: The Whole Bloody Af movie 2011 8.8
Django Unchained movie 2012 8.4
Inglourious Basterds movie 2009 8.3
Reservoir Dogs movie 1992 8.3
Kill Bill: Vol. 1 movie 2003 8.1
Kill Bill: Vol. 2 movie 2004 8.0
Sin City movie 2005 8.0
The Hateful Eight movie 2015 7.8
Grindhouse movie 2007 7.6
Jackie Brown movie 1997 7.5
Death Proof movie 2007 7.1
Four Rooms movie 1995 6.7
My Best Friend''s Birthday movie 1987 5.7
' + CHAR(10)
GO
--
-- [Insert your code here]
--
GO
GO
Question 9
Top 10 SF Series
----------------
Find the 10 most highly rated SF TV series. Include only series with 10000 or more votes.
Format Title using 30 characters.
Hint: Use SELECT DISTINCT genre and SELECT DISTINCT titleType to determine the correct
values to use to match SF TV series.
Correct results will look like this:
Title Rating # of Votes
------------------------------ --------------------------------------- -----------
Firefly 9.1 210460
Westworld 8.9 254053
Black Mirror 8.9 202575
The X-Files 8.7 170522
Mystery Science Theater 3000 8.6 20614
The Prisoner 8.6 10202
The Venture Bros. 8.6 20478
The Handmaid''s Tale 8.6 55338
Altered Carbon 8.5 35421
Utopia 8.5 32097
' + CHAR(10)
GO
--
-- [Insert your code here]
--
GO
GO
David Lynch/Angelo Badalamenti
------------------------------
Use INTERSECT and the title_principals table to find all movies where David Lynch and
Angelo Badalamenti worked together. Include ID and Title, format Title as 20 characters wide, and
order alphabetically by Title. Don''t bother with title_directors or title_writers, just use
title_principals.
Correct results will look like this:
ID Title
--------- ------------------------------
tt0090756 Blue Velvet
tt0116922 Lost Highway
tt0166896 The Straight Story
tt0105665 Twin Peaks: Fire Walk with Me
tt5334704 Twin Peaks: The Missing Pieces
tt0100935 Wild at Heart
' + CHAR(10)
GO
--
-- [Insert your code here]
--
GO
GO
-------------------------------------------------------------------------------------
-- This is an anonymous program block. DO NOT CHANGE OR DELETE.
-------------------------------------------------------------------------------------
BEGIN
PRINT '|---' + REPLICATE('+----',15) + '|';
PRINT ' End of CIS275 Lab Week 3' + REPLICATE(' ',50) + CONVERT(CHAR(12),GETDATE(),101);
PRINT '|---' + REPLICATE('+----',15) + '|';
END;