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

Apply: Signature Assignment: Strategic Management Research Project - Speed fitness test examples - Sierra company incurs the following - Jbhi fi return policy - Nurse practitioner credentialing trends - Explain osmosis in potatoes - Building security policy examples - Sample soap note for abdominal pain - Great gatsby last line meaning - In what pursuit is solmization useful or important - Youtube calgary stampede chuckwagon race - Cell lab report in biology - Bus katoomba to echo point - Whitfield's ointment chemist warehouse - The odyssey book 5 questions and answers - Oliver cromwell relationship with parliament - Dikw continuum - Sonnet to an american painter departing for europe - Hofstra university transcript request - Shaping the Force - Dewi sant hospital gum clinic - A ___________ gantt chart is a simple and effective way to depict progress on a project. - Http billofrightsinstitute org educate educator resources landmark cases - Lord of the flies war quotes - Discuss case 9.2 hsbc combats fraud in split second decisions - Hooper printing inc has bonds outstanding - +27737189846,Al Dubai®=Safe Affordable Misoprostol 200 mcg Mifepack tablets For Sale in Dubai®Buy MTP KIT, - Extron 26 490 02 - 54/2 schofield place menai - First cousin twice removed - Sex linked traits biology worksheet - Difference between liquidity and solvency - Human communication in society 4rd ed alberts nakayama and martin - Qsr drive thru study 2017 - Sun travel path melbourne - Aca code of ethics apa citation - Compare and contrast pablo picasso and vincent van gogh - Leddy and pepper's professional nursing - Assignment on financial statement ratio analysis - Mr kinnear alias grace - Is troy a real place - Udsmr online credentialing exam answers - Cpt code for urolift procedure - Interactive operating system example - Accounting Paper - For ANN HARRIS - Dalton ga trade center - Dantes inferno worldly splendors - Outline for Definition Essay - Maintenance pro web login - Is capacity the same as volume - Www mh alabama gov pasrr - Apply porter's five forces framework to the specialty coffee retail industry - Use goal seek to perform a break even analysis - Higuchi ichiyo separate ways - +91-8890675453 love marriage problem solution IN Kakada - Nursing Statistics - Bleed bleed poor country - Christleton high school term dates - My fair lady songs - Ascl3 ionic or covalent - Research paper - Lectures on urban economics brueckner exercise solutions - International business mcgraw hill 10th edition - Continental io 550 engine performance chart - Mafs 912 g co 3.11 - Ion tape 2 pc software download - Convert oracle package to sql server - Precede proceed model ppt - Hero's journey outline template - Literature Review - Inch pound to kg cm conversion - Which of the following is true of advertising - Hpc high performance coatings - Intelligent hospital pavilion icu - South west of england lerwick club - Hal blackman net worth - Commedia dell arte scripts samples - Soap Note x 2 (20 Hours) - Contemporary nursing practice - William damon's stages of friendship - Is madagascar a disney movie - Equal opportunities policy template - Discussion-2 - Naming ionic compounds answers - Mid ulster healthcare dungannon repeat prescriptions - Solenoid with an iron core - Converting a linear program to canonical form - Maroubra bay public schoolmaroubra bay public school - Nursing concept map for copd - Words ending in tation - Applied statistics in business and economics 4th edition - International marketing case study - Discussion Board ASAP - Unit VIII Assignment - Robin hood case study swot analysis - Visual analysis essay outline - Discussion - Nab eftpos terminal fees - Lasalle claimed oklahoma for france