SQL sub querry question

[mrchow19910319]

CREATE TABLE artists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    country TEXT,
    genre TEXT);

INSERT INTO artists (name, country, genre)
    VALUES ("Taylor Swift", "US", "Pop");
INSERT INTO artists (name, country, genre)
    VALUES ("Led Zeppelin", "US", "Hard rock");
INSERT INTO artists (name, country, genre)
    VALUES ("ABBA", "Sweden", "Disco");
INSERT INTO artists (name, country, genre)
    VALUES ("Queen", "UK", "Rock");
INSERT INTO artists (name, country, genre)
    VALUES ("Celine Dion", "Canada", "Pop");
INSERT INTO artists (name, country, genre)
    VALUES ("Meatloaf", "US", "Hard rock");
INSERT INTO artists (name, country, genre)
    VALUES ("Garth Brooks", "US", "Country");
INSERT INTO artists (name, country, genre)
    VALUES ("Shania Twain", "Canada", "Country");
INSERT INTO artists (name, country, genre)
    VALUES ("Rihanna", "US", "Pop");
INSERT INTO artists (name, country, genre)
    VALUES ("Guns N' Roses", "US", "Hard rock");
INSERT INTO artists (name, country, genre)
    VALUES ("Gloria Estefan", "US", "Pop");
INSERT INTO artists (name, country, genre)
    VALUES ("Bob Marley", "Jamaica", "Reggae");

CREATE TABLE songs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    artist TEXT,
    title TEXT);

INSERT INTO songs (artist, title)
    VALUES ("Taylor Swift", "Shake it off");
INSERT INTO songs (artist, title)
    VALUES ("Rihanna", "Stay");
INSERT INTO songs (artist, title)
    VALUES ("Celine Dion", "My heart will go on");
INSERT INTO songs (artist, title)
    VALUES ("Celine Dion", "A new day has come");
INSERT INTO songs (artist, title)
    VALUES ("Shania Twain", "Party for two");
INSERT INTO songs (artist, title)
    VALUES ("Gloria Estefan", "Conga");
INSERT INTO songs (artist, title)
    VALUES ("Led Zeppelin", "Stairway to heaven");
INSERT INTO songs (artist, title)
    VALUES ("ABBA", "Mamma mia");
INSERT INTO songs (artist, title)
    VALUES ("Queen", "Bicycle Race");
INSERT INTO songs (artist, title)
    VALUES ("Queen", "Bohemian Rhapsody");
INSERT INTO songs (artist, title)
    VALUES ("Guns N' Roses", "Don't cry");
    
SELECT title FROM songs WHERE artist = "Queen";
SELECT name FROM artists WHERE genre = "Pop";



/*add another query that will select the title of all the songs from the 'Pop' artists. It should use IN on a nested subquery that's based on your previous query.*/
/*select title from songs where genre in artists = "Pop"*/
SELECT title FROM songs WHERE name IN (SELECT name FROM artists WHERE genre = "Pop");

I have no idea how to do this. Someone help/?? 

[ZeouLs]

@mrchow19910319 what exactly do you need help with`? o.ô

[mrchow19910319]

4 minutes ago, ZeouLs said:

@mrchow19910319 what exactly do you need help with`? o.ô

/*add another query that will select the title of all the songs from the 'Pop' artists. It should use IN on a nested subquery that's based on your previous query.*/

[ZeouLs]

12 minutes ago, mrchow19910319 said:

/*add another query that will select the title of all the songs from the 'Pop' artists. It should use IN on a nested subquery that's based on your previous query.*/

SELECT title

FROM songs s

INNER JOIN artists a ON (s.id = a.id)

WHERE genre = 'Pop'

 

let me know if you need help for understanding it

 

EDIT: you could also change the INNER JOIN with a LEFT OUTER JOIN which in many cases yields more results

[mrchow19910319]

6 minutes ago, ZeouLs said:

SELECT title

FROM songs s

INNER JOIN artists a ON (s.id = a.id)

WHERE genre = 'Pop'

okay.. I only know: 

WHERE

IN

SELECT

INSERT 

syntax...

 

care to explain???

[ZeouLs]

SELECT title --> takes alles the data from the column title

FROM songs s --> choose in which table our selected column is in. in this case it's songs and I give it the alias s to make it less to type if I need it again (plot twist, we do)

INNER JOIN artists --> inner join is a way to combine two tables with each other (at the bottom is a picture with all the methods for a join), so if you type inner join artists it will combine songs with artists. in order for it to work you need to match the id's which leads us to

ON (s.id = a.id) --> first of all s.id is the column id from the table songs and a.id is the column id from the table artists. remember when I said I gave them an alias cause it's less to type? the s.id stands for songs.id and a.id for artists.id. What happens here is I take the id from one table and combine the other table for values where both of the ID's match.

WHERE a.genre = 'Pop' --> I actually forgot to put 'a.' infront of genre, this might cause issues when tables have the same name for columns. What happens here is I filter all values for their genre. Everything that isnt the genre Pop wont be listed

 

 

check out https://www.w3schools.com/sql/default.asp

They have very good examples and tutorials you can do

[mrchow19910319]

3 minutes ago, ZeouLs said:

 

I will take a look at this tmr... too tired just got off from work and learned a bit about SQL for 2 hours. I will give you a reply tomorrow. 

 

also I am following this tutorial, what do you tthink about it? 

do you think that it is good for a beginner? 

https://www.khanacademy.org/computing/computer-programming/sql

[ZeouLs]

15 hours ago, mrchow19910319 said:

I will take a look at this tmr... too tired just got off from work and learned a bit about SQL for 2 hours. I will give you a reply tomorrow. 

 

also I am following this tutorial, what do you tthink about it? 

do you think that it is good for a beginner? 

https://www.khanacademy.org/computing/computer-programming/sql

Never used but seems like a good source to learn

[zhick]

On 7/19/2018 at 3:49 PM, ZeouLs said:

SELECT title

FROM songs s

INNER JOIN artists a ON (s.id = a.id)

WHERE genre = 'Pop'

 

let me know if you need help for understanding it

 

EDIT: you could also change the INNER JOIN with a LEFT OUTER JOIN which in many cases yields more results

The assignment is to use a subquery, not a join (which I personally would prefer as well).

However, with the schema the OP has given, this can't be solved. There is no association between songs and artists as it stands.

But assuming song.artist is a foreign key referencing artist.name (which I would strongly discourage, as you usually really only should create foreign keys referencing primary keys):

select	title
from 	song
where 	artist in (
  			select name 
  			from artist 
  			where genre = 'Pop'
		);