using Microsoft SQL Server Management Studio 2012 (or later) or DataGrip
Main ass 7
January 18, 2019
Officer Jones asked the neighborhood’s regular trash collector to put the content of the defendant’s garbage that was left on the curb in plastic bags and to turn over the bags to him at the end of the day. The trash collector did as the officer asked in
January 18, 2019

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

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

USE TV    — ensures correct database is active

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 2, Question 1  [3pts possible]:
What channel is SYFY?
———————
Write a SELECT statement that finds the channels named SYFYP and SYFYHDP in the CHANNEL table of the TV
database. 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
————- ———–
59            SYFYP
1411          SYFYHDP
‘ + CHAR(10)

GO


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 2  [3pts possible]:
What channel is OPB?
———————
Write 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
————- ———–
10            KOPB
1010          KOPBDT
1165          KOPBDT2
1166          KOPBDT3
‘ + CHAR(10)

GO


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 3  [3pts possible]:
What are the genres?
——————–
Produce 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
——————–

Action
Action sports
Adults only
Adventure
Agriculture
Animals
Anthology
Art
Arts/crafts
Auction
Auto
Auto racing
Aviation
Baseball
Basketball

Weather
Western
Wrestling
‘ + CHAR(10)

GO


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 4  [3pts possible]:
Best SF?
——–
Find the top rated family safe sci fi shows in the SHOW table. Include all shows where the Genre is sci fi, the
StarRating is 8 or over, and the Classification is either G, PG, or PG-13. Use IN with a set of values to match
the Classification.

Produce the following columns: Title, Description, StarRating, Classification. Title should be no more than 20
characters wide and Description should be no more than 50 characters wide. Classification should be no more
than 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 because
of apostrophes):

Title                Description                                        StarRating  Classification
——————– ————————————————– ———– ————–
Avatar               On an alien planet, a former Marine (Sam Worthingt 10          PG-13
Gravity              The destruction of their shuttle leaves two astron 10          PG-13
Minority Report      A policeman (Tom Cruise) tries to establish his in 10          PG-13
Star Wars: The Force Thirty years after the defeat of the Galactic Empi 10          PG-13
The Day the Earth St Klaatu (Michael Rennie) and his guardian robot, Go 10          G
Dawn of the Planet o Human survivors of a plague threaten Caesar”s gro 8           PG-13
Fantastic 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-13
Guardians of the Gal A space adventurer (Chris Pratt) becomes the quarr 8           PG-13
I Am Legend          After a man-made plague transforms Earth”s popula 8           PG-13
Independence Day     A fighter pilot (Will Smith), a computer whiz (Jef 8           PG-13
Interstellar         As mankind”s time on Earth comes to an end, a gro 8           PG-13
Invasion of the Body San Francisco health inspectors (Donald Sutherland 8           PG
Midnight Special     The government and a group of religious extremists 8           PG-13
Pacific Rim          A washed-up ex-pilot (Charlie Hunnam) and an untes 8           PG-13
Rise of the Planet o A scientist”s (James Franco) quest to find a cur  8           PG-13
Serenity             Crew members (Nathan Fillion, Gina Torres, Alan Tu 8           PG-13
Solaris              A widowed psychologist (George Clooney) arrives at 8           PG-13
The Abyss            Oil-platform workers, including an estranged coupl 8           PG-13
The Fifth Element    A New York City cabdriver (Bruce Willis) tries to  8           PG-13
The Hunger Games     A resourceful teen (Jennifer Lawrence) takes her y 8           PG-13
The Hunger Games: Ca After their unprecedented victory in the 74th Hung 8           PG-13
Twilight Zone: The M Four tales include a bigot (Vic Morrow), oldsters  8           PG
Westworld            Androids go haywire with guests (Richard Benjamin, 8           PG
‘ + CHAR(10)

GO


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 5  [3pts possible]:
HD Stations
———–
Select the DisplayName and SortOrder from the CHANNEL table for all TV channels where the SortOrder is
between 700 and 799 inclusive. Use the BETWEEN … AND … operator. Exclude all rows where the ExternalID
is 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
———— ———–
KATUDT       702
KRCWDT       703
KPXGDT       705
KOINDT       706
DSCHDP       707
KGWDT        708
WGNAPHD      709
KOPBDT       710
VEL          711
KPTVDT       712
KPDXDT       713
FUSEHD       714

UPHD         797
AXSTV        798
NFLNRZD      799
‘ + CHAR(10)

GO


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 6  [3pts possible]:
Daily Listing for SYFYHDP
————————-
Using the all_data view in the TV database, find all listings for shows on SYFYHDP that were
on any time on 9/12/2017. Do NOT include shows that end at 00:00:00 on 9/12/2017, or begin
at 00:00:00 on 9/13/2017. DO include shows that start before midnight on 9/12 but end after
midnight, or start before midnight on 9/12 and end after midnight on 9/13 (see results below
if 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 the
result 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                Title                Length   Description
——————- ——————– ——– ————————————————–
23:00:00 – 01:00:00 Lake Placid          02:00:00 A New York paleontologist (Bridget Fonda) goes to
01:00:00 – 03:00:00 Jeepers Creepers     02:00:00 A cloaked figure (Jonathan Breck) terrorizes two s
03:00:00 – 04:00:00 The Twilight Zone    01:00:00 Mute: Parents raise their daughter (Ann Jillian) w
04:00:00 – 04:30:00 Paid Programming     00:30:00 Paid programming. (HDTV)
04:30:00 – 05:00:00 Paid Programming     00:30:00 Paid programming. (HDTV)
05:00:00 – 05:30:00 Paid Programming     00:30:00 Paid programming. (HDTV)
05:30:00 – 06:00:00 Paid Programming     00:30:00 Paid programming. (HDTV)
06:00:00 – 07:00:00 CSI: Crime Scene Inv 01:00:00 19 Down…: When the team discovers a connection b
07:00:00 – 08:00:00 CSI: Crime Scene Inv 01:00:00 One to Go: Grissom announces that he is leaving, w
08:00:00 – 09:00:00 CSI: Crime Scene Inv 01:00:00 The Grave Shift: Dr. Langston”s first day on the
09:00:00 – 10:00:00 CSI: Crime Scene Inv 01:00:00 Disarmed & Dangerous: A specialized team of forens
10:00:00 – 11:00:00 CSI: Crime Scene Inv 01:00:00 Deep Fried & Minty Fresh: The team investigates a
11:00:00 – 12:00:00 CSI: Crime Scene Inv 01:00:00 Miscarriage of Justice: As Langston testifies at t
12:00:00 – 14:00:00 Seventh Son          02:00:00 A supernatural champion (Jeff Bridges) has little
14:00:00 – 17:00:00 Need for Speed       03:00:00 Determined to take down his treacherous rival (Dom
17:00:00 – 18:57:00 Lake Placid          01:57:00 A New York paleontologist (Bridget Fonda) goes to
18:57:00 – 21:00:00 Lake Placid 2        02:03:00 A sheriff, a big-game hunter and a wildlife office
21:00:00 – 22:00:00 Face Off: Game Face  01:00:00 Stone Cold Superheroes: The artists create plant-h
22:00:00 – 00:30:00 300                  02:30:00 Sparta”s King Leonidas (Gerard Butler) and his ba
‘ + CHAR(10)

GO


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 7  [3pts possible]:
What is on TV?
—————
Using 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
———— ————– ———- ——– ——————————
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


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 8  [3pts possible]:
Rick and Morty
————–
Show 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                Episode              Series #   Episode # Original Air Date
——————– ——————– ———- ——— —————–
Rick and Morty       The Rickshank Rickde EP01833673 301       Apr 01, 2017
Rick and Morty       Rickmancing the Ston EP01833673 302       Jul 30, 2017
Rick and Morty       Pickle Rick          EP01833673 303       Aug 06, 2017
Rick and Morty       Vindicators 3: The R EP01833673 304       Aug 13, 2017
Rick and Morty       The Whirly Dirly Con EP01833673 305       Aug 20, 2017
Rick and Morty       Rest and Ricklaxatio EP01833673 306       Aug 27, 2017
Rick and Morty       The Ricklantis Mixup EP01833673 307       Sep 10, 2017
‘ + CHAR(10)

GO


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 9  [3pts possible]:
Couch Potato Achievement
————————
Produce a list of the 10 longest shows. Exclude sports-related genres and shows where the title
is “To Be Announced” or “SIGN OFF”. Format your results to look like this:

Channel Name Title                Episode              Date         Time                Length
———— ——————– ——————– ———— ——————- ——–
BBCAPH       Doctor Who           Deep Breath          Sep 08, 2017 04:30:00 – 12:30:00 08:00:00
BBCAPH       Doctor Who           Deep Breath          Sep 08, 2017 12:30:00 – 20:30:00 08:00:00
BBCAPH       Doctor Who           Deep Breath          Sep 08, 2017 20:30:00 – 04:30:00 08:00:00
CSPAN2       Public Affairs Event N/A                  Sep 08, 2017 21:00:00 – 05:00:00 08:00:00
CSPAN2       Book TV              N/A                  Sep 10, 2017 10:00:00 – 18:00:00 08:00:00
BBCAP        Doctor Who           Deep Breath          Sep 08, 2017 04:30:00 – 12:30:00 08:00:00
BBCAP        Doctor Who           Deep Breath          Sep 08, 2017 12:30:00 – 20:30:00 08:00:00
BBCAP        Doctor Who           Deep Breath          Sep 08, 2017 20:30:00 – 04:30:00 08:00:00
TVMRT        Beyond Today         N/A                  Sep 03, 2017 19:30:00 – 03:30:00 08:00:00
TVMRT        Beyond Today         N/A                  Sep 04, 2017 03: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 20
characters wide.
‘ + CHAR(10)

GO


— [Insert your code here]

GO
PRINT ‘CIS 275, Lab Week 2, Question 10  [3pts possible]:
It”s a Good Life
—————–
Find all showings of The Twilight Zone episode “It”s a Good Life.” Format your results exactly
as they appear below.

Hint: You can convert the same DATETIME twice, using two different format codes, and concatenate the
results together into a single string.

Correct output will look like this (only two columns):

Channel Name Time
———— ———————————
SYFYP        Sep 15, 2017: 06:00:00 – 06:30:00
SYFYHDP      Sep 15, 2017: 06:00:00 – 06:30:00
SYFYP        Sep 16, 2017: 05:00:00 – 05:30:00
SYFYHDP      Sep 16, 2017: 05:00:00 – 05:30:00
‘ + CHAR(10)

GO


— [Insert your code here]

GO
————————————————————————————-
— This is an anonymous program block. DO NOT CHANGE OR DELETE.
————————————————————————————-
BEGIN
   PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’;
   PRINT ‘ End of CIS275 Lab Week 6’ + REPLICATE(‘ ‘,50) + CONVERT(CHAR(12),GETDATE(),101);
   PRINT ‘|—‘ + REPLICATE(‘+—-‘,15) + ‘|’;
END;

 

Looking for a Similar Assignment? Order now and Get 20% Discount!
Use Code; GET20

WhatsApp Chat with us on Whatsapp