Use IMDb To Ensure The Correct Database Is Active

Use Imdb Ensures Correct Database Is Active Go Print

Use Imdb Ensures Correct Database Is Active Go Print

Write SQL queries for the following questions based on the IMDB database. Each query should be well-formatted with keywords capitalized, each projected column on its own line with indentation, and ending with a semicolon. Remove comment indicators where necessary. After completing each query, replace the placeholder comment with your code. The questions are:

1. Display the name and year of birth for all people born after 1980, who have directed at least one show (appear in title_directors), and have a deathYear value. Sort by birthYear descending.

2. Show every genre of television show (titleType = 'tvEpisode') which has at least one title with 500 episodes, i.e., titles with episodeNumber 500 in title_episode. Eliminate duplicates; display only genre names.

3. Create a Common Table Expression (CTE) to identify the worst shows with an averageRating of 1. Join title_basics with title_ratings. In the main query, show counts of worst shows grouped by titleType, ordered by count descending.

4. Identify the least popular professions with fewer than 1,000 entries from name_profession, along with the count of each.

5. Using the previous query as a subquery, display the names of all people (name_basics.primaryName) with those professions, ordered ascending by name.

6. Show each writer's name (name_basics.primaryName) and total titles written (title_writers). Limit to writers with 5,000 to 10,000 titles, sorted by primaryName ascending.

7. Find actors and character names for those who performed the same character role more than once in "Battlestar Galactica". Show primaryName, characters, and count, ordered by primaryName ascending.

8. Identify people who directed more than five shows with an averageRating of 10. Show their names and total shows directed, ordered by name ascending.

9. List all TV specials (titleType='tvSpecial') from 1982 with their title and runtimeMinutes, replacing NULL runtimes with zero, ordered descending by runtime.

10. List all movies from 1913 with non-NULL runtimeMinutes. Show primaryTitle, averageRating; rank them with RATINGRANK (based on ascending averageRating) and LENGTHRANK (based on ascending runtimeMinutes). Ensure results are ordered by primaryTitle ascending.

Replace each placeholder comment with your SQL code accordingly.

Paper For Above instruction

Use Imdb Ensures Correct Database Is Active Go Print

This academic exercise involves crafting SQL queries to extract specific information from the IMDB database. The queries focus on various aspects such as demographics of directors, genres with high episode counts, identification of the worst shows, analysis of professions, and ranking movies based on ratings and runtime. All queries are intended to be well-formatted, clear, and efficient for execution in a SQL environment.

Question 1

Display the name and year of birth for all people born after 1980, who have directed at least one show (appear in the title_directors table), have a deathYear specified, sorted by birthYear descending.

SELECT
nb.primaryName,
nb.birthYear
FROM
name_basics nb
JOIN
title_directors td ON nb.nconst = td.nconst
WHERE
nb.birthYear > 1980
AND nb.deathYear IS NOT NULL
GROUP BY
nb.primaryName,
nb.birthYear
ORDER BY
nb.birthYear DESC;

Question 2

Show every genre associated with television shows (titleType = 'tvEpisode') which have at least one title with 500 episodes, i.e., titles with episodeNumber = 500 in title_episode. Eliminate duplicates and display genre names only.

SELECT DISTINCT
tg.genre
FROM
title_genres tg
JOIN
title_basics tb ON tg.tconst = tb.tconst
JOIN
title_episode te ON te.tconst = tb.tconst
WHERE
tb.titleType = 'tvEpisode'
AND te.episodeNumber = 500;

Question 3

Create a CTE named BADSHOWS to find shows with an averageRating of 1 by joining title_basics and title_ratings. In the main query, group by titleType and count the number of bad shows for each type, ordered by count descending.

WITH BADSHOWS AS (
SELECT
tb.titleType,
tb.primaryTitle,
tb.startYear
FROM
title_basics tb
JOIN
title_ratings tr ON tb.tconst = tr.tconst
WHERE
tr.averageRating = 1
)
SELECT
titleType,
COUNT(*) AS TotalBadShows
FROM
BADSHOWS
GROUP BY
titleType
ORDER BY
TotalBadShows DESC;

Question 4

Identify the least popular professions with fewer than 1,000 entries in name_profession, along with the count of each.

SELECT
profession,
COUNT(*) AS profession_count
FROM
name_profession
GROUP BY
profession
HAVING
COUNT(*) 

Question 5

Using the previous query as a subquery, display the primaryName of all people with those professions, ordered by primaryName ascending.

SELECT
nb.primaryName,
np.profession
FROM
name_basics nb
JOIN
(SELECT
profession
FROM
name_profession
GROUP BY
profession
HAVING
COUNT(*) 
) AS FilteredProfessions ON nb.nconst = np.nconst
JOIN
name_profession np ON nb.nconst = np.nconst
ORDER BY
nb.primaryName ASC;

Question 6

Show the name and total number of titles written for each writer (name_basics.primaryName), limited to those with between 5,000 and 10,000 titles written, ordered by primaryName ascending.

SELECT
nb.primaryName,
COUNT(*) AS titles_written
FROM
name_basics nb
JOIN
title_writers tw ON nb.nconst = tw.nconst
GROUP BY
nb.primaryName
HAVING
COUNT(*) BETWEEN 5000 AND 10000
ORDER BY
nb.primaryName ASC;

Question 7

Find actors and character names who performed the same role more than once in "Battlestar Galactica". Show their primaryName, characters, and count, ordered by primaryName ascending.

SELECT
nb.primaryName,
tp.characters,
COUNT(*) AS role_count
FROM
name_basics nb
JOIN
title_principals tp ON nb.nconst = tp.nconst
JOIN
title_basics tb ON tp.tconst = tb.tconst
WHERE
tb.primaryTitle = 'Battlestar Galactica'
GROUP BY
nb.primaryName,
tp.characters
HAVING
COUNT(*) > 1
ORDER BY
nb.primaryName ASC;

Question 8

Identify people who directed more than five shows with an averageRating of 10. Show their names and number of such shows, ordered by name ascending.

WITH TopRatedDirectors AS (
SELECT
n.nconst,
n.primaryName,
COUNT(*) AS highRatedShows
FROM
name_basics n
JOIN
title_principals tp ON n.nconst = tp.nconst
JOIN
title_basics tb ON tp.tconst = tb.tconst
JOIN
title_ratings tr ON tb.tconst = tr.tconst
WHERE
tr.averageRating = 10
AND tp.job = 'director'
GROUP BY
n.nconst,
n.primaryName
)
SELECT
primaryName,
highRatedShows
FROM
TopRatedDirectors
WHERE
highRatedShows > 5
ORDER BY
primaryName ASC;

Question 9

List all TV specials (titleType='tvSpecial') from 1982, showing title and runtimeMinutes; substitute zero when runtimeMinutes is NULL, ordered descending by runtime.

SELECT
primaryTitle,
COALESCE(runtimeMinutes, 0) AS runtimeMinutes
FROM
title_basics
WHERE
titleType = 'tvSpecial'
AND startYear = 1982
ORDER BY
runtimeMinutes DESC;

Question 10

From movies of year 1913 (startYear=1913) with non-NULL runtimeMinutes, display primaryTitle, averageRating, and rank by RATINGRANK (based on ascending averageRating) and LENGTHRANK (based on ascending runtimeMinutes). Results ordered by primaryTitle ascending.

WITH RankedMovies AS (
SELECT
tb.primaryTitle,
tr.averageRating,
DENSE_RANK() OVER (ORDER BY tr.averageRating ASC) AS RATINGRANK,
DENSE_RANK() OVER (ORDER BY COALESCE(tb.runtimeMinutes, 0) ASC) AS LENGTHRANK
FROM
title_basics tb
JOIN
title_ratings tr ON tb.tconst = tr.tconst
WHERE
tb.startYear = 1913
AND tb.titleType = 'movie'
AND tb.runtimeMinutes IS NOT NULL
)
SELECT
primaryTitle,
averageRating,
RATINGRANK,
LENGTHRANK
FROM
RankedMovies
ORDER BY
primaryTitle ASC;

End of queries.