Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Lab 6 CIS 275

27/10/2020 Client: ericsermon1000 Deadline: 3 days

USE IMDB    -- 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 table names; 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 'CIS2275, Lab Week 6, Question 1  [3pts possible]:


Write the query to display the name and year of birth for all people born after 1980, who have


directed at least one show (i.e. those who appear at least once in the title_directors table).


Limit results to those who have died (who have a value in the deathYear column).


----------------------------------------------------------------------------------------------


Columns to display:    name_basics.primaryName, name_basics.birthYear


Sort in descending order by birth year.' + CHAR(10)


--


-- [Insert your code here]


--






GO


PRINT 'CIS2275, Lab Week 6, Question 2  [3pts possible]:


Show every genre of television show which has had at least one title with 500 episodes.


i.e. limit results to the titleType ''tvEpisode'' in the title_basics table, and to titles


containing a row in the title_episode table with episodeNumber 500.


----------------------------------------------------------------------------------------------


Columns to display:    title_genre.genre


Display genre name only, and eliminate duplicate values.' + CHAR(10)




GO




--


-- [Insert your code here]


--






GO


PRINT 'CIS2275, Lab Week 6, Question 3  [3pts possible]:


Write a common table expression to identify the WORST shows: join title_basics against title_ratings


and limit your results to those with an averageRating value equal to 1.  Project the title,


type, and startYear from title_basics; and label your CTE as BADSHOWS.


In the main query, show a breakdown of BADSHOWS grouped by type, along with the total number of


rows for each (i.e. GROUP BY titleType)


----------------------------------------------------------------------------------------------


Columns to display:    titleType, COUNT(*)


Sort results in descending order by COUNT(*).' + CHAR(10)




GO




--


-- [Insert your code here]


--






GO


PRINT 'CIS2275, Lab Week 6, Question 4  [3pts possible]:


Identify the least popular professions.  Show each profession value from the name_profession table,


along with the total number of matching rows (GROUP BY profession).  Use the HAVING clause to limit


your results to professions with less than 1,000 rows.


----------------------------------------------------------------------------------------------


Columns to display:    name_profession.profession, COUNT(*)' + CHAR(10)


--


-- [Insert your code here]


--


GO






GO


PRINT 'CIS2275, Lab Week 6, Question 5  [3pts possible]:


Use the query from #4 above to display the names of all people belonging to these professions.


Use the previous query as a subquery in the FROM clause here to limit the results.


----------------------------------------------------------------------------------------------


Columns to display:    name_basics.primaryName, name_profession.profession


Sort results in ascending order by primaryName.' + CHAR(10)


--


-- [Insert your code here]


--


GO






GO


PRINT 'CIS2275, Lab Week 6, Question 6  [3pts possible]:


Show the name of every writer, along with the total number of titles they''ve written (i.e. rows in the 


title_writers table).  Limit results to those who have written between 5,000 and 10,000 titles (inclusive).


----------------------------------------------------------------------------------------------


Columns to display:    name_basics.primaryName, COUNT(*)


Sort results in descending order by primaryName.' + CHAR(10)


--


-- [Insert your code here]


--


GO






GO


PRINT 'CIS2275, Lab Week 6, Question 7  [3pts possible]:


Show the actor and character names for everyone who has performed the same role in more than one


show with the title ''Battlestar Galactica''.  i.e. identify the combination of (primaryName, characters)


which occurs in the title_principals table more than once for matching titles.


----------------------------------------------------------------------------------------------


Columns to display:    name_basics.primaryName, title_principals.characters, COUNT(*)


Sort results in ascending order by primaryName.' + CHAR(10)


--


-- [Insert your code here]


--


GO






GO


PRINT 'CIS2275, Lab Week 6, Question 8  [3pts possible]:


Identify the names of people who have directed more than five highest-rated shows (i.e. title_ratings.averageRating = 10).


For each of these people, display their names and the total number of shows they have written.


----------------------------------------------------------------------------------------------


Columns to display:    name_basics.primaryName, COUNT(*)


Sort results in ascending order by primaryName.' + CHAR(10)


--


-- [Insert your code here]


--


GO






GO


PRINT 'CIS2275, Lab Week 6, Question 9  [3pts possible]:


Display the title and running time for all TV specials ( titleType = ''tvSpecial'' ) from 1982; if the run time is


NULL, substitute zero.


----------------------------------------------------------------------------------------------


Columns to display:    title_basics.primaryTitle, title_basics.runtimeMinutes


Sort in descending numerical order by the resulting calculated run time value.' + CHAR(10)


--


-- [Insert your code here]


--


GO






GO


PRINT 'CIS2275, Lab Week 6, Question 10  [3pts possible]:


Identify every movie from 1913 (startYear = 1913, titleType = ''movie''); limit your results to those with a non-NULL value


in the runtimeMinutescolumn.  For each movie, display the primaryTitle and the averageRating value from the title_ratings table.


Use DENSE_RANK() to display the rank based on averageRating (label this RATINGRANK), and also the rank based on runtimeMinutes


(label this LENGTHRANK).  Both of these should be based on an asecending sort order.


----------------------------------------------------------------------------------------------


Columns to display:    title_basics.primaryTitle, title_ratings.averageRating,


                       RATINGRANK, LENGTHRANK


Sort results in ascending order by primaryTitle.' + CHAR(10)


--


-- [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 6' + REPLICATE(' ',50) + CONVERT(CHAR(12),GETDATE(),101);


    PRINT '|---' + REPLICATE('+----',15) + '|';


END;

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Writer Writer Name Offer Chat

Writers are writing their proposals. Just wait here to get the offers for your project...

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

K species and r species - Focused cough assessment shadow health - Marxism in a doll's house - Determination of ka for a weak acid lab answers - Iso 9001 version 2000 ppt - Fibula with orientalizing lions - Balboa gs501z circuit board - Power drive corporation designs and produces - Aluminium oxide reacts with hydrochloric acid - Bedding parts of a bed - Psychosocial assessment in nursing - Cultural Perspectives - Managerial Finance_Problem Set 1 - The diagram shows a zn mno2 koh alkaline dry cell - Human services in the criminal justice system trends evaluation - Client consultation form template - Oxygen poem mary oliver analysis - The leadership grid used the two leader behaviors which were called: - A separate peace phineas last name - Edu 540 week 8 discussion - Alcohol + acidified potassium dichromate - Econ DISCUSSION/ESSAY - Colin turner risale i nur - Discussion - Exaquantum excel add in - Dolan company's accounting records reflect - Cambridge secondary checkpoint past papers 2015 science - Ma yu ching's bucket chicken house - Percent yield problems worksheet - Econ 312 week 2 quiz - Biozone ib biology second edition answers - What was hoover's solution to the great depression - How many syllables in snail - What is data converted into a meaningful and useful context - Transition learning and development statement examples - Coll 148 week 4 quiz - Bose einstein condensate properties - Poems about the four elements - Researching job - Tax Question - Costco case study crafting and executing strategy - Abc v lenah game meats - Which type of muscle tissue has cells that branch - Ann radcliffe the italian - Buying proactive at a kiosk - Variables Into Knowledge - What is a marketing channel system and value network - Borderlands 2 caustic caverns undiscovered challenge - Arguments for cell phones in school - Camp cooking total fire ban - Discussion - Beats by dre swot analysis - Jetblue and the veterans administration case study - Https www biography com people charles dickens 9274087 - Political science - Kedron brook wetlands reserve - Francine rivers net worth - Solahart booster not working - The author to her book tone - Kate woods occupational therapist - Mcdonalds no call no show policy - Csec english sba written report - Sylvia mathews burwell net worth - Experiment 6 freezing points graphing of data - Swot analysis of coca cola 2016 - NEED IN 10 HOURS or LESS POWERPOINT - Writing human resource Essay - Compare and contrast the lottery and young goodman brown - Need Research paper in APA format of 600 words with citations and references. Will need at least three journal references and the textbook - Naming aldehydes worksheet with answers - Barbie's influence on body image - Investor presentation - P2#2 - 10 lessons in clarity and grace - The ones who walk away from omelas essay ideas - Biology classification packet answer key - Franklin equipment ltd case study answers - Asg2 - What did the missouri compromise of 1820 accomplish - Party list system advantages and disadvantages - Facts about biotic and abiotic factors - In the graph above, the point d represents values resulting in ____________. - ENG 225 Introduction to Film NO PLAGIARISM PLZ - Intelligent reading by helen keller theme - Me talk pretty one day author purpose - Hius 221 mindtap answers - Aerospace engineering unsw handbook - Chase bank bissonnet hwy 6 - Husky injection molding buffalo ny - Sensory System Assignment - Tvb anniversary awards 2012 - Schoolwide enrichment model reading - Thought Paper - The fashion shoe company operates a chain - Python program to calculate bill amount - What was walmart's early global expansion strategy - Tim hortons case study analysis - University of new caledonia - Helium gas can change voice - Is symbolism a language feature