SQL Help for Sql with subquery

SQL Help for Sql with subquery
0

#1

Tell us what’s happening:

Your code so far

<h1>Hello</h1>

Your browser information:

Your Browser User Agent is: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:52.0) Gecko/20100101 Firefox/52.0.

Link to the challenge:


#2

The title of your topic does not have anything to do with the challenge you linked from.


#3
SELECT title
FROM songs
WHERE title IN
    (SELECT genre
     FROM artists
     WHERE genre LIKE "pop");

Kind of new to all this programming stuff. I am taking an online class at Kahn Academy and a challenge wants this:To finish creating the ‘Pop’ playlist, 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.

Here is there table info:

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 * FROM artists;*/   
SELECT title FROM songs Where artist LIKE "Queen";
SELECT name FROM artists Where genre LIKE "pop";

the top select with subquery is my third part of challenge which I wrote, online code checkers say it is good but the lesson won’t let me advance. Just need help


#4

What kind of database is this for (mySQL, MSSQL, Oracle, other)?


#5

It is for practice at SQL subquery. I am 59 years old trying to learn on my own but I’m stumped. Also I study on lynda.com and I am having a problem where teacher has a variables field in sql pane of xampp phpmyadmin and I don’t. He has 4.4.12 and I have 4.7.4 I can’t get help anywhere. Mysqlite


#6

I would try:

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

Since artists contains the names of the artists, you just want to select the title from songs where artists is in the subquery (which selects the applicable artists).


#7

Thank you but they want a query with a sub query and an IN statement like at the top of my post.

To finish creating the ‘Pop’ playlist, 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.

It has an avatar that smiles when the codes is good (which it is smiling) but it won’t let me advance. can I upload a pic?


#8

hope this helps explain it better


#9

Did you see my updated post above?


#10

yes sir but it did not satisfy the code demon on the site. lol there are only four pop song titles and that code brought in all titles. I really would like to thank you for trying to help me as I am at my wits end.


#11

The screenshot you provided shows your old code and not what I gave you.


#12

see orange line above window


#13

I made a mistake the first. Try the one below:

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

My mistake was that I used artist instead of name in the sub query.


#14

you nailed it that time but I’m confused on how that works, would you explain this for me, also thanks you rock!
Okay I’m thinking that the subQ is reading all the artist that are ‘pop’ and the other is reading their songs, is that correct?


#15

The first part of the query (seen below):

SELECT title
FROM songs
WHERE artist IN
is going to return the title from the songs table where artist IN the sub query. Because you are looking for names of artists to be in the sub query, the sub query must select the artist name (name field) where genre = “Pop”.


#16

Again, thanks. Did you see my question about phpMyAdmin and missing variables field?

!


#17


#18

I would contact the teacher and ask for help.


#19

He is not on lynda.com any more and I have been waiting for four days for help from lynda support. I’m thinking the phpmyadmin people took it out then nothing I can do. Just wondered if you have that and does it have the variable field. xampp 4.7.4


#20

I am not actually understanding what the issue is. I have not taken a course on lynda.com, so I do not know what variable field you are referring to. The screen shot you provided does not show a variable field nor do I understand what the purpose of it is. Maybe some one else who has taken the course before can assist you. I don’t have time to watch a lynda.com course, plus this is really a question for lynda.com and not FCC. We focus on teaching programming via JavaScript here and do not offer courses on using phpMyAdmin or xampp.

If you can not get your question resolved here or over at lynda.com, then it might be time to switch courses and find one that has better support.