Using Microsoft SQL Server Management Studio 2012 (Or Later) Or DataGrip

11 May Using Microsoft SQL Server Management Studio 2012 (Or Later) Or DataGrip

*/

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 to capitalize table names as they appear in the database; 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 ‘CIS 275, Lab Week 4, Question 1  [3pts possible]:

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 ‘CIS 275, Lab Week 4, Question 2  [3pts possible]:

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 ‘CIS 275, Lab Week 4, Question 3  [3pts possible]:

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 ‘CIS 275, Lab Week 4, Question 4 [3pts possible]:

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 ‘CIS 275, Lab Week 4, Question 5  [3pts possible]:

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 ‘CIS 275, Lab Week 4, Question 6  [3pts possible]:

Year-by-Year Popularity for JN