WELP project (task 8): Guidance for using WITH and JOIN in SQL

Language Help SQL
1

So I am working on the bonus practice for joining multiple tables. I have come to the question about the WELP reviews and I have been staring at it forever I feel like, I’m fairly new to Codecademy so was hesitant to ask for help but seems like my best option. I’m sure it’s something small I’m missing so please no judgement :).
So the following is the question and following that is the query I’m trying to use. So I’ve studied the tables multiple times but when I run the basic query with the WITH and STRFTIME but it returns no results. Like I said, I’m sure it’s something minor that I’m overlooking but I could really use some advice. Thanks so much .

Write a query using the WITH clause to select all the reviews that happened in 2020. JOIN the places to your WITH query to see a log of all reviews from 2020.

(This will use the WITH clause as well as the strftime() function. See if you can use Google to dig up some information about the function before take a look at the hint.)

WITH reviews_2020 AS (
SELECT *
FROM reviews
WHERE strftime("%Y", review_date) = ‘2020’
);

2

What language is this and can I have a link to the lesson

Edit also format you code correctly learn more here: [How to] Format code in posts

1 Like
3

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-welp
SQL
thank you for the formatting link!!

4

Ok but unfortunately I wont be able to help you because I don’t know SQL

5

OK . Thanks anyway though for showing me how format the codes in my posts. I appreciate it.

6

Using WITH is one way of creating a temporary table to work with but I can’t see what you actually do with it, you seem to be missing some code. You might want to quickly review how to use WITH. The instructions mention joining it to something else, have you tried that bit (is this all the code for this query?).

7

Hello @ajbeck20 ,

Welcome to the forums! Yes, please ask for help after giving it a try. :slight_smile: We are all learning.

The only thing you were missing is a select from the temporary table you created.

WITH reviews_2020 AS (
SELECT *
  FROM   reviews
  WHERE  strftime("%Y", review_date) = '2020'
)
SELECT *
FROM reviews_2020;
5 Likes
8

Thank you so much for all your help!! I knew it was something small I was overlooking.

2 Likes
9

I came here to search for a fix to my errors, and I was missing the apostrophes for the date.

But I believe this is missing the JOIN clause to complete the full requirement. Still unsure if there is a difference between using INNER JOIN or simply JOIN, but seems to give equal results.

WITH reviews_2020 AS (
SELECT *
  FROM   reviews
  WHERE  strftime("%Y", review_date) = '2020'
)
SELECT *
FROM reviews_2020
JOIN places
  ON places.id = reviews_2020.place_id;
5 Likes
10

Hello @rente1496058297 ,

There is no difference between INNER JOIN and JOIN. It’s the same.

:+1: good catch, I missed the JOIN to the places table.

1 Like
11

Is the JOIN clause necessary for any reason other than the prompt asked us to include it?

If not, isn’t the most efficient way to write this code the following? If the below code is different than the code you provided, can you elaborate on what is different?

SELECT *
FROM reviews
WHERE strftime('%Y', review_date) = '2020'
12