using Microsoft SQL Server Management Studio 2012 (or later) or DataGrip

/*
*******************************************************************************************

 SQL SERVER 2012 and various databases
*******************************************************************************************

                                   CERTIFICATION:

   By typing my name below I certify that the enclosed is original coding written by myself
without unauthorized assistance.  I agree to abide by class restrictions and understand that
if I have violated them, I may receive reduced credit (or none) for this assignment.

                CONSENT:   [your name here]
                DATE:      [date]

*******************************************************************************************
*/

GO
PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’
PRINT ‘Read the questions below and insert your queries where prompted.  When  you are finished,
you should be able to run the file as a script to execute all answers sequentially (without errors!)’ + CHAR(10)
PRINT ‘Queries should be well-formatted.  SQL is not case-sensitive, but it is good form to
capitalize keywords and to capitalize table names as they appear in the database; you should also put 
each projected column on its own line and use indentation for neatness.  Example:

   SELECT Name,
          CustomerID
   FROM   CUSTOMER
   WHERE  CustomerID < 106;

All SQL statements should end in a semicolon.  Whatever format you choose for your queries, make
sure that it is readable and consistent.’ + CHAR(10)
PRINT ‘Be sure to remove the double-dash comment indicator when you insert your code!’;
PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’ + CHAR(10) + CHAR(10)
GO

GO
PRINT ‘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 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
PRINT ‘CIS 275, Lab Week 3, Question 2  [3pts possible]:
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
PRINT ‘CIS 275, Lab Week 3, Question 3  [3pts possible]:
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
PRINT ‘CIS 275, Lab Week 3, Question 4  [3pts possible]:
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
PRINT ‘CIS 275, Lab Week 3, Question 5  [3pts possible]:
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
PRINT ‘ Question 6  [3pts possible]:
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
PRINT ‘CIS 275, Lab Week 3, Question 7  [3pts possible]:
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
PRINT ‘CIS 275, Lab Week 3, Question 8  [3pts possible]:
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
PRINT ‘CIS 275, Lab Week 3, Question 9  [3pts possible]:
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
PRINT ‘CIS 275, Lab Week 3, Question 10  [3pts possible]:
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 ‘ + REPLICATE(‘ ‘,50) + CONVERT(CHAR(12),GETDATE(),101);
    PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’;
END;