using Microsoft SQL Server Management Studio 2012

*/ USE IMDB    -- ensures chasten database is active GO PRINT '|---' + REPLICATE('+----',15) + '|' PRINT 'Read the questions adown and inoculate your queries whither prompted.  When  you are high, you should be telling to run the refine as a script to enact all answers sequentially (outside errors!)' + CHAR(10) PRINT 'Queries should be well-formatted.  SQL is not case-sensitive, but it is cheerful frame to capitalize keywords and ttelling indicates; you should as-well put each designed support on its own line and use smoothness for neatness.  Example:    SELECT Name,           CustomerID    FROM   CUSTOMER    WHERE  CustomerID < 106; All SQL statements should end in a semicolon.  Whatever frameat you prefer for your queries, make sconsideration that it is readtelling and agreeing.' + CHAR(10) PRINT 'Be sconsideration to displace the double-dash interpret indicator when you inoculate your method!'; PRINT '|---' + REPLICATE('+----',15) + '|' + CHAR(10) + CHAR(10) GO GO PRINT 'CIS2275, Lab Week 6, Question 1  [3pts likely]: Write the inquiry to parade the indicate and year of nobility for all fellow-creatures born behind 1980, who accept directed at meanest one pomp (i.e. those who likeness at meanest unintermittently in the denomination_directors consideration). Limit results to those who accept died (who accept a rate in the deathYear support). ---------------------------------------------------------------------------------------------- Columns to parade:    name_basics.primaryName, indicate_basics.birthYear Sort in descending prescribe by nobility year.' + CHAR(10) -- -- [Insert your method hither] -- GO PRINT 'CIS2275, Lab Week 6, Question 2  [3pts likely]: Show integral genre of television pomp which has had at meanest one denomination delay 500 episodes. i.e. word results to the denominationType ''tvEpisode'' in the denomination_basics consideration, and to denominations containing a row in the denomination_episode ttelling delay episodeNumber 500. ---------------------------------------------------------------------------------------------- Columns to parade:    title_genre.genre Display genre indicate merely, and exclude facsimile rates.' + CHAR(10) GO -- -- [Insert your method hither] -- GO PRINT 'CIS2275, Lab Week 6, Question 3  [3pts likely]: Write a sordid ttelling indication to demonstrate the WORST pomps: append denomination_basics despite denomination_ratings and word your results to those delay an averageRating rate resembling to 1.  Project the denomination, type, and startYear from denomination_basics; and designate your CTE as BADSHOWS. In the ocean inquiry, pomp a breakdown of BADSHOWS grouped by idea, along delay the entirety calculate of rows for each (i.e. GROUP BY denominationType) ---------------------------------------------------------------------------------------------- Columns to parade:    titleType, COUNT(*) Sort results in descending prescribe by COUNT(*).' + CHAR(10) GO -- -- [Insert your method hither] -- GO PRINT 'CIS2275, Lab Week 6, Question 4  [3pts likely]: Identify the meanest approved vocations.  Show each vocation rate from the indicate_vocation consideration, along delay the entirety calculate of matching rows (GROUP BY vocation).  Use the HAVING chapter to word your results to vocations delay close than 1,000 rows. ---------------------------------------------------------------------------------------------- Columns to parade:    name_profession.profession, COUNT(*)' + CHAR(10) -- -- [Insert your method hither] -- GO GO PRINT 'CIS2275, Lab Week 6, Question 5  [3pts likely]: Use the inquiry from #4 aloft to parade the indicates of all fellow-creatures accompanying to these vocations. Use the prior inquiry as a subinquiry in the FROM chapter hither to word the results. ---------------------------------------------------------------------------------------------- Columns to parade:    name_basics.primaryName, indicate_profession.profession Sort results in ascending prescribe by primaryName.' + CHAR(10) -- -- [Insert your method hither] -- GO GO PRINT 'CIS2275, Lab Week 6, Question 6  [3pts likely]: Show the indicate of integral writer, along delay the entirety calculate of denominations they''ve written (i.e. rows in the  title_writers consideration).  Limit results to those who accept written between 5,000 and 10,000 denominations (inclusive). ---------------------------------------------------------------------------------------------- Columns to parade:    name_basics.primaryName, COUNT(*) Sort results in descending prescribe by primaryName.' + CHAR(10) -- -- [Insert your method hither] -- GO GO PRINT 'CIS2275, Lab Week 6, Question 7  [3pts likely]: Show the agent and cast indicates for integralone who has done the identical role in past than one pomp delay the denomination ''Battlestar Galactica''.  i.e. demonstrate the union of (primaryName, casts) which occurs in the denomination_principals ttelling past than unintermittently for matching denominations. ---------------------------------------------------------------------------------------------- Columns to parade:    name_basics.primaryName, denomination_principals.characters, COUNT(*) Sort results in ascending prescribe by primaryName.' + CHAR(10) -- -- [Insert your method hither] -- GO GO PRINT 'CIS2275, Lab Week 6, Question 8  [3pts likely]: Identify the indicates of fellow-creatures who accept directed past than five highest-rated pomps (i.e. denomination_ratings.averageRating = 10). For each of these fellow-creatures, parade their indicates and the entirety calculate of pomps they accept written. ---------------------------------------------------------------------------------------------- Columns to parade:    name_basics.primaryName, COUNT(*) Sort results in ascending prescribe by primaryName.' + CHAR(10) -- -- [Insert your method hither] -- GO GO PRINT 'CIS2275, Lab Week 6, Question 9  [3pts likely]: Display the denomination and present term for all TV specials ( denominationType = ''tvSpecial'' ) from 1982; if the run term is NULL, represent naught. ---------------------------------------------------------------------------------------------- Columns to parade:    title_basics.primaryTitle, denomination_basics.runtimeMinutes Sort in descending numerical prescribe by the resulting congenial run term rate.' + CHAR(10) -- -- [Insert your method hither] -- GO GO PRINT 'CIS2275, Lab Week 6, Question 10  [3pts likely]: Identify integral movie from 1913 (startYear = 1913, denominationType = ''movie''); word your results to those delay a non-NULL rate in the runtimeMinutescolumn.  For each movie, parade the primaryTitle and the averageRating rate from the denomination_ratings consideration. Use DENSE_RANK() to parade the arrange inveterate on averageRating (designate this RATINGRANK), and as-well the arrange inveterate on runtimeMinutes (designate this LENGTHRANK).  Both of these should be inveterate on an asecending species prescribe. ---------------------------------------------------------------------------------------------- Columns to parade:    title_basics.primaryTitle, denomination_ratings.averageRating,                        RATINGRANK, LENGTHRANK Sort results in ascending prescribe by primaryTitle.' + CHAR(10) -- -- [Insert your method hither] -- GO GO ------------------------------------------------------------------------------------- -- This is an unidentified program obstruct. 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;