Homework Helpers

Custom Academic Papers

100% Original, Plagiarism Free, Customized to your instructions!

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

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

******************************************************************************************* SQL SERVER 2012 and various databases*******************************************************************************************                                   CERTIFICATION:   By typing my name below I certify that the enclosed is original coding written by myselfwithout unauthorized assistance.  I agree to abide by class restrictions and understand thatif I have violated them, I may receive reduced credit (or none) for this assignment.                CONSENT:   [your name here]                DATE:      [date]********************************************************************************************/GOPRINT ‘|—‘ + 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 tocapitalize 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, makesure 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)GOGOPRINT '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 ProfeMULTV        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)GOUSE TV---- [Insert your code here]--GOPRINT '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 theDisplayName 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 displaynames, 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]--GOPRINT '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 WHEREclause that matches display names that end in P with display names that end inHDP.Note: There are ways to do this that don''t require a CROSS JOIN, but use a CROSSJOIN anyway.Hint: The function LEFT will return the left part of a string. For example, givena 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]--GOPRINT '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. Besure 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]--GOGOPRINT '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 havean episode name and a description.Use DISTINCT. To match my results, title and episode should be 20 characters wide and description shouldbe 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 leaA Different World    Dr. War Is Hell      Dr. War Is Hell: Jaleesa and Whitley have new roomA Different World    Dream Lover          Dream Lover: Whitley is upset by the romantic dreaA 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 friendA la Cachi Cachi Por CECYT 15 ''Diódoro A CECYT 15 ''Diódoro Antúnez Echegaray'' vs. CECYT 3Accessorize Your Fal Clarks               Clarks:                                           Adrenaline           Preparation          Preparation: The crew preps and plans for the midwAlaska: The Last Fro Episode 13           Episode 13:                                       ...Wings                Legacy               Legacy: Brian returns to Nantucket when a mysterioWomen''s Volleybal   FIVB World Grand Cha FIVB World Grand Champions Cup: Japan vs. USA: FroYou Can Do Better    Afterhours           Afterhours:                                       ' + CHAR(10)GO---- [Insert your code here]--GOGOPRINT ' 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 actressesplayed (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 theperson 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)GOUSE IMDB---- [Insert your code here]--GOGOPRINT 'CIS 275, Lab Week 3, Question 7  [3pts possible]:Quentin Tarantino-----------------What genres has Quentin Tarantino directed? Produce the title, type, year and genrefor shows where Quentin Tarantino was the director. Format Title as 30 characters wideand genre as 20 characters wide. Order by Year.Hint: title_directors has information about which people directed which shows. title_genreshas 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]--GOGOPRINT '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 characterswide. 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.9Kill Bill: The Whole Bloody Af movie        2011        8.8Django Unchained               movie        2012        8.4Inglourious Basterds           movie        2009        8.3Reservoir Dogs                 movie        1992        8.3Kill Bill: Vol. 1              movie        2003        8.1Kill Bill: Vol. 2              movie        2004        8.0Sin City                       movie        2005        8.0The Hateful Eight              movie        2015        7.8Grindhouse                     movie        2007        7.6Jackie Brown                   movie        1997        7.5Death Proof                    movie        2007        7.1Four Rooms                     movie        1995        6.7My Best Friend''s Birthday     movie        1987        5.7' + CHAR(10)GO---- [Insert your code here]--GOGOPRINT '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 correctvalues to use to match SF TV series.Correct results will look like this:Title                          Rating                                  # of Votes------------------------------ --------------------------------------- -----------Firefly                        9.1                                     210460Westworld                      8.9                                     254053Black Mirror                   8.9                                     202575The X-Files                    8.7                                     170522Mystery Science Theater 3000   8.6                                     20614The Prisoner                   8.6                                     10202The Venture Bros.              8.6                                     20478The Handmaid''s Tale           8.6                                     55338Altered Carbon                 8.5                                     35421Utopia                         8.5                                     32097' + CHAR(10)GO---- [Insert your code here]--GOGOPRINT '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 andAngelo 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 Piecestt0100935 Wild at Heart                 ' + CHAR(10)GO---- [Insert your code here]--GOGO--------------------------------------------------------------------------------------- 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; Students are often burdened with the task of writing essays. They need to master various academic topics, understand how to integrate facts and arguments, and organize all of this information in a clear and logical way. This is not an easy task for many students. But fear not! We’ve got your back. Here at MoreEssays.com, we offer professional academic help that can take on all these tasks on your behalf. All you need to do is fill out the Order form with your paper’s details, and we will get started on it right away!

Click Here to get this paper done within your deadline 

Can't find what you are looking for?

Post your question on MoreEssays and get help from one of our expert tutors in topics ranging from mathematics to rocket science!