Computer Science Help

11 May Computer Science Help

GO

PRINT Question 1

Popular Genres

————–

We will start with the IMDB database.

For each genre, show the total number of shows that are listed in that genre. Format genre as

15 characters wide. Order in descending order of popularity.

Correct results will have 28 rows and will look like this:

Genre Count

————— ———–

Drama 1183422

Comedy 1049517

Short 670864

Documentary 499078

Talk-Show 452645

Romance 398779

Family 339665

News 338804

Animation 255212

Reality-TV 232633

…

Western  25128

War 20984

Film-Noir 852

‘ + CHAR(10)

GO

USE IMDB

—

— [Insert your code here]

—

GO

PRINT Question 2 M*A*S*H

——-

For each season of M*A*S*H, show the total number of episodes and total number of votes.

Display results ordered by season. Write your query to match the primaryTitle in title_basics

instead of hard-coding a tconst value.

Hint: title_episode.parentTconst is the series, title_episode.tconst is the episode.

Episodes have ratings in title_ratings, where the number of votes for the episode is also contained.

Correct results will look like this:

Season Number Number of Episodes Total Votes by Season

————- —————— ———————

1 24 8470

2 24 6724

3 24 6383

4 24 5859

5 24 5546

6 24 5092

7 25 5362

8 25 5340

9 20 4177

10 21 4049

11 16  5849

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT, Question 3

Again Popular Genres

——————–

Repeat the query from Question 1, but this time express the popularity as a percentage of the total number

of shows. Format the percentage to two decimal places and add a % sign to the end.

Hint: Start by adding the Total column to the SELECT clause. You”ll need to use a windowed function.

If you use … OVER () that will window over the entire contents of the table. Once you have that working,

the percent is 100 * the expression that gives you Count / the expression that gives you Total. Use STR

to convert that to 6 characters with two digits after the decimal point, then add a % to the end.

Hint 2: Percent is a reserved keyword in SQL, so you”ll need to quote it if you want to use it as a column name.

Correct results will have 28 rows and look like this:

Genre Count Total Percent

————— ———– ———– ——-

Drama 1183422 7297619 16.22%

Comedy 1049517 7297619 14.38%

Short 670864 7297619 9.19%

Documentary 499078 7297619 6.84%

Talk-Show 452645 7297619 6.20%

Romance 398779 7297619 5.46%

Family 339665 7297619 4.65%

News 338804 7297619 4.64%

Animation 255212 7297619 3.50%

Reality-TV 232633 7297619 3.19%

…

Western 25128 7297619 0.34%

War 20984 7297619 0.29%

Film-Noir 852 7297619 0.01%

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT Question 4

Metaphone with the most Variants

——————————–

We”re going to switch over to the NAMES database for the next few queries.

Produce a report that shows the most popular metaphones for baby names. Include two rows

for each metaphone, one for F babies and one for M babies. For each metaphone, show the

total number of names that match that metaphone, and the total number of babies that were

given those names. Order in descending order by total number of babies, and display the

top 10 results.

Format Metaphone as 10 characters wide.

For practice, do not use the all_data view. Write the correct JOIN instead.

Correct results will look like this:

Gender Metaphone Total Names Total Babies

—— ———- ———– —————————————

M JN 3565 5910583

M JMS 403 4859289

F MR 3499 4835115

M RBRT 410 4755683

M MXL 1520 4641296

M WLM 604 3746134

M TFT 606 3547986

F TN 10004 3276887

F JN 9945 3216270

M RXRT 435 2516670

‘ + CHAR(10)

GO

USE NAMES

—

— [Insert your code here]

—

GO

PRINT Question 5

What about John?

—————-

For the most popular combination of Metaphone with Gender (JN and M), show a list of the most popular names

that match the metaphone.

Name Total Babies

———- —————————————

John 4712974

Juan 331865

Johnny 305889

Jon 164821

Gene 124121

Johnnie 93336

Jonah 54315

Jean 22359

Jan 21534

Johnie 17265

Gino 11250

Gianni 8071

Joan 5594

…

Giani 227

Jony 216

Jauan 190

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT Question 6

Year-by-Year Popularity for JN

——————————

This time, show a breakdown of the popularity of JN names for M babies.

Your results should contain 100 rows that look like this:

Metaphone Year Total Babies

———- ————————————— —————————————

JN 1915 50983

JN 1916 53675

JN 1917 55877

JN 1918 61148

JN 1919 58457

JN 1920  62319

JN 1921 63521

JN 1922 62980

JN 1923 63395

JN 1924 65348

…

JN 2012 21925

JN 2013 21402

JN 2014 21125

‘ + CHAR(10)

GO

—

— [Insert your code here]

—

GO

PRINT Question 7

Corey in the House

——————

For all years where the total number of both M and F babies named “Corey” was 100 or more,

show the name (formatted as 10 characters wide) along with the year and the total number

of babies with that name. Display results in chronological order.

Correct results will have 62 rows formatted as:

Name Year Total Babies

———- ————————————— —————————————

Corey 1952 117

Corey 1954  161

Corey 1955 271

Corey 1956 313

Corey 1957 285

Corey 1958 336

Corey 1959  351

Corey 1960 347

Corey 1961 450

Corey 1962 485

Corey 1963 484

Corey  1964 501

Corey 1965 544

Corey 1966 513

Corey 1967 512

Corey 1968  1668

Corey 1969 5069

…

Corey 2012 895

Corey 2013 889

Corey 2014 818

‘ + CHAR(10)