Multiple-tables, cross-join

https://www.codecademy.com/courses/learn-sql/lessons/multiple-tables/exercises/cross-join

Hello fellow SQL learners! So, in this exercise of Cross join, the result set of Task 2 and 3 seem to have no difference, at least to me. Can someone please help me with what I’m missing to see? The third task says, Scroll right to look at the month column – some months are gone now. I think all 12 months appear once for all subscribers.


Screenshot 2024-04-16 152153
Screenshot 2024-04-16 152214

Thanks a ton in advance!

what was your code for the exercise? Please format it using the “</>” button.

Hello! The following are the codes for the exercise:

TASK 1:

SELECT COUNT (*)
FROM newspaper
WHERE start_month <= 3
AND end_month >= 3;

TASK 2:

SELECT *
FROM newspaper
CROSS JOIN months;

TASK 3:

SELECT *
FROM newspaper
CROSS JOIN months
WHERE start_month <= month
AND end_month >= month;

TASK 4:

Don’t remove your previous queries.
Create a final query where you aggregate over each month to count the number of subscribers.
Fill in the blanks in the following query:

SELECT month,
  COUNT(*) AS 'subscribers'
FROM newspaper
CROSS JOIN months
WHERE start_month <= month
AND end_month >= month
GROUP BY month;

Hi,
The way the exercise is set out the results of tasks 2 and 3 are both displayed in the output. That means you have to scroll all the way through the table for 2 to see 3.
For instance, at the top, Vinnie Sagaser has 12 rows in table 2, one for each month.
If you scroll all the way down to table 3 (it starts with bold column headings) this time Vinnie has just 5 rows (those inside his start and end months, inclusively)

You could also delete the query for 2 to save scrolling. You might need to put it back to pass the lesson, but at least it’d be easier to see how it’s changed

Hope that helps

1 Like

Hello!

Thanks a ton! Wow, I think impatience got the better of me, that I didn’t really notice a second set of columns! Thank you soo much! :smiley:

1 Like