CYBER WEEK - EXTRA SAVINGS EVENT
TRY A FREE LESSON

SQL Joins and other clauses Assessment Question #4

Screen Link: Learn data science with Python and R projects

My Code:

SELECT art.name, 
       COUNT(t.genre_id = '1') AS num_rock_songs
  FROM track AS t
  JOIN album AS al
    ON t.album_id = al.album_id
  JOIN artist AS art
    ON al.artist_id = art.artist_id
 GROUP BY art.name
HAVING num_rock_songs >= 5
 LIMIT 10;

What I expected to happen: I expected to get the total number of rock songs by band (organized aphabetically)

What actually happened: I thought I got my desired result but maybe I was just supposed to have the list and not have the count column. I’m not sure how to do that. I’ve spent a long time trying to figure out this problem.

[10 rows x 2 columns]

name                                        num_rock_songs

AC/DC                                      18
Aerosmith                              15
Alanis Morissette                 13
Alice In Chains                      12
Amy Winehouse                   23
Antônio Carlos Jobim       31
Apocalyptica                         8
Audioslave                            40
BackBeat                               12
Battlestar Galactica           20

After I submit the problem it says “• We could not find a column we expected in the query result. Make sure that the query calculates every column specified by the instructions. * The query didn’t join the correct tables. Review how to specify which tables you want to join., and * The query didn’t use the correct join conditions. Review how to join tables on a column.” Please help me understand what I’m doing wrong.

This doesn’t work the way you think it does. Check how many rows the track tables has and then run. . .

SELECT COUNT(genre_id = 1)
  FROM track;

. . . and see what you get.

This is probably on us. You apparently are trying to avoid using the genre table by using the Rock genre id directly in the query, but we’re expecting you to use the genre table to look for rock songs.

I am also having a problem interpreting this question. I’ve tried it 2 ways with the same error that I’m not joining the correct tables.
Method 1:

SELECT track.composer AS composer, COUNT(track.name) AS total_tracks
  FROM track
  JOIN genre
    ON track.genre_id = genre.genre_id
 WHERE genre.name = 'Rock' AND track.composer NOT NULL
 GROUP BY track.composer
HAVING COUNT(track.name) >= 5
 LIMIT 10;

Method 2:

SELECT art.name AS artist_name, COUNT(t.track_id) AS total_tracks
  FROM track AS t
  JOIN album AS al
    ON al.album_id = t.album_id
  JOIN genre AS g
    ON g.genre_id = t.genre_id
  JOIN artist AS art
    ON art.artist_id = al.artist_id
 WHERE g.name = 'Rock' AND t.composer = art.name
 GROUP BY art.name
HAVING COUNT(t.track_id) >= 5
 LIMIT 10;

which tables are supposed to be joined and how do we interpret if a song is “written” by an artist?

Your second solution is close. Just forget about composer.

This is given by the connection track.album_id → album.album_id → artist.artist_id. For the sake of the answer, as the instructions suggest, we should end up providing not artisti_id, but rather their name.