Best strategy to initially explore tables with SQL

[estebanalfaroorozco]

Hi,

I actually was confronted with this same issue, and thought that maybe I was missing some rows for track names by using the track table as my FROM clause.

So, I wanted to ask for examples on good practices to divert from this issue in the first place.

From the get go, ¿How should anyone compare the three tables to determine which one would be the best option to act as your FROM clause?, ¿What strategy should I use to achieve that when comparing tables with SQL?

I’ve been having a hard time not getting access to similar methods like .info, .describe and .value_counts, that I used to apply when working with tables on Pandas. ¿Which would be a comparable set of tools when using SQL?

With my best regards for you all,

Esteban.

Screen Link: https://app.dataquest.io/m/190/building-and-organizing-complex-queries/3/the-with-clause

Reviewed Code: Wrong answer with changing order of tables in JOIN

[Sahil]

Hi @estebanalfaroorozco,

@monorienaghogho already mentioned the strategy in the linked post.

@monorienaghogho

Put the table you want the most important information from as the main table. Personally, I think you should put the table you intend to use its column with GROUP BY as main table.

In the below instructions:

Create a query that shows summary data for every playlist in the Chinook database:

• Use a WITH clause to create a named subquery with the following info:
  • The unique ID for the playlist.
  • The name of the playlist.
  • The name of each track from the playlist.
  • The length of each track in seconds.
• Your final table should have the following columns, in order:
  • playlist_id - the unique ID for the playlist.
  • playlist_name - The name of the playlist.
  • number_of_tracks - A count of the number of tracks in the playlist.
  • length_seconds - The sum of the length of the playlist in seconds.
• The results should be sorted by playlist_id in ascending order.

We can observe that the importance is given to the playlist data. So in the from clause, we will use playlist table. Then we have to find information about tracks. However, the only link between the playlist table and track table is the playlist_track table. So we have to join it first and later join it with the track table.

Best,
Sahil

[estebanalfaroorozco]

Great! Thank you Sahil for pointing out this specific instructions, I see now that the WITH clause could actually serve as tool to determine a proxy base repository to resource from with the main data to work with on different queries about a certain topic from the database, not just as some random pre-selection of data. Will be applying this strategy, many thanks!