Subquery with SQL

Navigation
0

I'm trying to solve an exercise in khanacademy in the SQL course and I'm having some difficulty.

The exercise asks for the following:

  

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

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

SELECT title FROM songs WHERE genre...

The system already accuses 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 in which I am doing the search does not have the same name? >

Below is an image of the exercise so you can understand me better.

    
asked by anonymous 18.06.2015 / 04:40

3 answers

1

Hello,

For this situation is very simple to do. You should see which columns between the two tables have a binding (FOREIGN KEY), in your case they are probably the "name" columns of the table artist and "artist" of the songs table. After having this information in hand is simple, create the query that will return the titles of the artists table, then create a closing WHERE where the column "name" of the artists table belongs (IN) the "artist" column of the songs table, for this , create a query that returns the artist of the songs table where genre is equal to "Pop".

Here's an example: 

SELECT title
  FROM artists
 WHERE name IN ( SELECT artist
                   FROM songs
                  WHERE genre = 'Pop' );
    
18.06.2015 / 19:26
0

In fact you select the title of the artist where it has the pop genre, name is equal to artist so I just did repeat: 

SELECT title FROM songs WHERE artist IN ( SELECT name FROM artists WHERE genre IN ("Pop"));
    
25.06.2015 / 12:05
-1
SELECT title 
FROM songs 
WHERE artist IN (SELECT name FROM artists WHERE genre = "Pop");
    
19.04.2018 / 19:30
Read memory CLP zelio How to create a site without reloading every click on a link?