Nested and Correlated Nests in SQL, screen 6/8

Screen Link: Learn data science with Python and R projects

My Code:
Capture

DQ answer to this screen:

Chinook database schema: https://dq-content.s3.amazonaws.com/603/chinook-schema.svg

Hello, I don’t understand how that tr.quantity is used, I imagine that tr. is invoice (i), invoice_line (il) and track (t) combined, but I don’t really see what’s the point or why is it better to do so. Besides that, what’s the role in selecting il.*, t.milliseconds, t.genre_id, or why we selecting only those?
Also, I’d like an opinion if my code is well optimized, and my thinking it’s good, because I can’t really see all the rows. Thanks guys!

1 Like

Hey @zavatevlad26

This is an example of a nested and correlated query. So instead of applying multiple Joins, the author broke the problem into several nested sub-queries.

The solution to the problem statement requires three tables (as given in the content section) - Invoice, Genre and Track. However, we do not have common columns to join the invoice table with any of the tables. Hence, the Invoice_line table is introduced in the query.

As mentioned by the author as well, the same problem can be solved by different approaches/ solutions.

I may misinterpret this so can you elaborate on what exactly causes confusion here. My guess is we would need fields such as quantity, unit price and invoice_id from the invoice_line table, milliseconds would later be used for minute calculation and genre field will be used in filtering the Metal genre.

1 Like

Thanks for answering, the confusion was why we select these columns, because from what I know, we can select them in the outer query regardless if we select them in the inner one. Now I see that it’s not necessary and maybe they add it for a better understanding.

Hi @zavatevlad26

Have you tried this for this query? What was the result?

If not for this, can you show /share an example of the one you have tried?

Thanks.