Sub-search with SQL

Question

I’m trying to solve a khanacademy exercise in the SQL course and I’m having a little trouble.

The exercise calls for the following:

To finish creating the "Pop" playlist, add another search that will select the title, title, of all the songs of the "Pop" genre artists. It should use IN in a nested sub-search based on its previous search.

I tried several ways and could not, I noticed that if I use;

SELECT title FROM songs WHERE genre...

The system already states that there is no Genre column in the Songs table (which is correct) so how could I search for a column in another table if the table I am doing the search does not have the same name?

below follows an image of the exercise to better understand me.

Answer 1

Hello,

For this situation it is very simple to do. You should see which columns between the two tables have a link (FOREIGN KEY), in your case they are probably the columns "name" of the Artist table and "Artist" of the Songs table. After having this information in hand is simple, create the query that will return the titles of table Artists, then create a WHERE enclosure where the column "name" of table Artists belongs (IN) the column "Artist" of table Songs, for this purpose, create a query that returns the Artist from the Songs table where Genre equals "Pop".

Following example:

SELECT title
  FROM artists
 WHERE name IN ( SELECT artist
                   FROM songs
                  WHERE genre = 'Pop' );

Answer 2

SELECT title 
FROM songs 
WHERE artist IN (SELECT name FROM artists WHERE genre = "Pop");

Answer 3

explicación: es una consulta que implica consultar datos de dos Tablas por Ende, es necesario hacer un Join..

Respuesta:

select title from songs where artist=(select  artist from songs
    inner join artists on songs.id=artists.id
        where artists.genre IN ("Pop"));

This query can be found in Khan Academy: https://es.khanacademy.org/computing/computer-programming/sql/more-advanced-sql-queries/p/challenge-playlist-maker.

Answer 4

Actually you select the artist’s title where you have the pop genre, name equals Artist so I just did repeat:

SELECT title FROM songs WHERE artist IN ( SELECT name FROM artists WHERE genre IN ("Pop"));