SQL Fundamentals - Assessment - Multi row and multi column subqueries assessment

Q&A DQ Courses
#1

Question 2: https://app.dataquest.io/m/2000602/multi-row-and-multi-column-subqueries-assessment/2/question-2

My Code: 

SELECT name
  FROM track
 WHERE media_type_id LIKE (SELECT media_type_id
                             FROM media_type
                            WHERE name LIKE '%AAC%')
                      AND genre_id LIKE (SELECT genre_id
                                           FROM genre
                                          WHERE name LIKE '%Rock%'
                                             OR name LIKE '%Metal%');

Test says that it didn't find the expected column.

Logic seems there to me, what am I missing?

Question 5: Learn data science with Python and R projects

My code:

SELECT ct.country, ROUND((i.invoice_tally / ct.customer_tally), 2) AS sale_avg
  FROM (SELECT billing_country, SUM(total) AS invoice_tally
          FROM invoice
         GROUP BY billing_country) AS i
  JOIN (SELECT country, COUNT(*) AS customer_tally
          FROM customer
         GROUP BY country) AS ct
    ON i.billing_country = ct.country
 LIMIT 5;

Same thing as above, the test cannot find the expected column with the expected result.

Thanks a ton!

#2

Write a query to find Metal or Rock AAC Audio tracks that have a composer.

This isn’t a helpful feedback message. The issue is that because the columns in the result are not what we expect, we’re saying the column (as in the right values) isn’t there.

As to why your answer is wrong, that is because the questions asks (or at least wants to ask) for the top five countries with the highest average sale. Your answer does not provide that.

1 Like
#3

Hi @Bruno , thanks for the reply.

For answer 5 I have added:

 ORDER BY sale_avg DESC
 LIMIT 5;

And it worked perfectly. I simply missed the last thing the exercise asked for, silly me.

Hower for question 2 I tried to filter only tracks with a composer by adding:

AND composer IS NOT NULL;

But it still says they can’t find the column with the right values. I guess I am doing this wrong in some way.

Thanks!

1 Like
#4

Probably not that specifically, but rather something else that was there from the start, namely media_type_id LIKE and genre_id LIKE. Are you sure you want to use LIKE there?

Note that the subquery SELECT media_type_id FROM media_type WHERE name LIKE '%AAC%' results in a column that looks like this:

2
4
5
#5

Hey Tarmicle,

My code for Q3 is:

SELECT invoice_date
FROM invoice
GROUP by invoice_date
HAVING MAX(total) > (SELECT SUM(total)/COUNT(invoice_id)
FROM invoice);
But i keep pulling the wrong data. Do you mind sharing your code?