SQL Help for Sql with subquery

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:
https://www.freecodecamp.org/challenges/say-hello-to-html-elements

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

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

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

Screenshot_513
Screenshot_5131569×666 59.9 KB
hope this helps explain it better

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.

12

Screenshot_514
Screenshot_5141277×657 40.3 KB
see orange line above window

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?

16

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

Screenshot_511
Screenshot_5111400×725 121 KB
!

17

Screenshot_512
Screenshot_5121574×741 59.3 KB

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

21

The exact answer is ;
SELECT title FROM songs WHERE artist IN (SELECT name FROM artists WHERE genre= “Pop”);

1 Like
23

CREATE TABLE users (
id INT GENERATED ALWAYS AS IDENTITY not null primary key,
first_name varchar DEFAULT NULL,
last_name varchar DEFAULT NULL,
username varchar DEFAULT NULL,
password varchar DEFAULT NULL,
PRIMARY KEY (id))
ENGINE = InnoDb DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

CREATE TABLE todos (
id bigint GENERATED ALWAYS AS IDENTITY not null primary key ,
description varchar DEFAULT NULL,
is_done bit(1) NOT NULL,
target_date datetime DEFAULT NULL,
username varchar DEFAULT NULL,
title varchar DEFAULT NULL,
PRIMARY KEY (id))
ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8_general_ci ;

on typing this this code its shows errors as following i am not able to rectify them

[Warning, Error code 1,064, SQLState 42000] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘IDENTITY not null primary key,
first_name varchar DEFAULT NULL,
last_name va’ at line 2

[Exception, Error code 1,064, SQLState 42000] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘IDENTITY not null primary key,
first_name varchar DEFAULT NULL,
last_name va’ at line 2
Line 1, column 1

[Warning, Error code 1,064, SQLState 42000] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘IDENTITY not null primary key ,
description varchar DEFAULT NULL,
is_done bi’ at line 2

[Exception, Error code 1,064, SQLState 42000] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘IDENTITY not null primary key ,
description varchar DEFAULT NULL,
is_done bi’ at line 2
Line 10, column 1

Execution finished after 0.095 s, 2 errors occurred.

24

can someone help me ?
Because of this issue I am not able to complete my project.

25

Hello~!

If you are having trouble with your code, I recommend creating your own forum topic to ask for assistance, rather than replying to someone else’s. This will help you get assistance more quickly. :slight_smile:

26

SELECT title
FROM songs
WHERE artist IN
(select name from artists where genre = ‘Pop’);