Use IMDb Ensures Correct Database Is Active ✓ Solved

Use Imdb Ensures Correct Database Is Activegoprint Repl

Use Imdb Ensures Correct Database Is Activegoprint Repl

Sample Paper For Above instruction

This assignment involves creating a series of SQL queries based on a provided database schema, specifically using the IMDB database. The tasks include retrieving specific data about people, shows, genres, ratings, professions, and roles within the IMDB dataset. The goal is to practice writing well-formatted, efficient, and readable SQL queries to analyze various aspects of the database, such as identifying actors, directors, genres, ratings, and other attributes of movies and TV shows.

Question 1: People born after 1980 who have directed at least one show and have died

Write a query to display the name and year of birth for all people born after 1980, who have directed at least one show (appear at least once in the title_directors table), and who have a value in the deathYear column. The results should be sorted in descending order by birth year. The columns to display are name_basics.primaryName and name_basics.birthYear.

Question 2: Genres with shows of 500 episodes

Show every genre of television show which has had at least one title with 500 episodes. Limit your results to titles with titleType 'tvEpisode' and containing a row in title_episode with episodeNumber 500. Display only unique genre values (title_genre.genre).

Question 3: Worst shows with rating 1

Create a Common Table Expression (CTE) titled BADSHOWS that joins title_basics with title_ratings to find shows with an averageRating of 1. Display the titleType, startYear, and primaryTitle. In the main query, group the results by titleType and count the number of such shows, sorting the results in descending order of the count.

Question 4: Least popular professions

Identify the least popular professions by listing each profession from name_profession along with the total number of occurrences (COUNT(*)), grouping by profession. Limit to those with less than 1,000 rows.

Question 5: People with less popular professions

Use the previous query as a subquery to list the names (name_basics.primaryName) and their corresponding professions (name_profession.profession) for all people whose professions are among the least popular. Results should be ordered ascending by name.

Question 6: Writers with between 5,000 and 10,000 titles

Show the name (name_basics.primaryName) of writers and the total number of titles they have written (via title_writers), only including those with written between 5,000 and 10,000 titles. Sort results by primaryName in descending order.

Question 7: Actors playing the same role in 'Battlestar Galactica'

Identify actor names (name_basics.primaryName) and character names (title_principals.characters) where the same actor played the same role in multiple episodes of 'Battlestar Galactica'. Show combinations occurring more than once, sorted ascending by actor name.

Question 8: Directors of highly rated shows

Find individuals (name_basics.primaryName) who directed more than five shows with averageRating = 10. Display their names and total number of such shows, ordered ascending by name.

Question 9: TV specials from 1982

List all TV specials (titleType = 'tvSpecial') from 1982. If runtimeMinutes is NULL, substitute zero. Show primaryTitle and runtimeMinutes, ordered descending by runtime.

Question 10: Movies from 1913 with ratings and rankings

Identify movies (titleType = 'movie') from 1913 with non-NULL runtimeMinutes. Display primaryTitle, averageRating, and assign RATINGRANK using DENSE_RANK() based on ascending averageRating, and LENGTHRANK based on ascending runtimeMinutes. Results ordered ascending by primaryTitle.

Note:

This is an anonymous block containing only sample questions for creating SQL queries. Do not modify or delete the block.

References

  • Houssain, S. (2020). SQL Practice Problems: 57 Beginning, Intermediate, and Advanced Challenges. SQL School.
  • Beaulieu, A. (2017). Learning SQL. O'Reilly Media.
  • Groff, K., & Weinberg, D. (2018). SQL in a Nutshell. O'Reilly Media.
  • Fowler, M. (2003). Patterns of Enterprise Application Architecture. Addison-Wesley.
  • Rob, P., & Coronel, C. (2009). Database Systems: Design, Implementation, and Management. Cengage Learning.
  • Ullman, J. (1988). Foundations of Databases. Addison-Wesley.
  • Elmasri, R., & Navathe, S. (2015). Fundamentals of Database Systems. Pearson.
  • Connolly, T., & Begg, C. (2014). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
  • Nixon, R. (2014). SQL: The Complete Reference. McGraw-Hill Education.

Related Assignments