Databases and SQL: Issues with code snippets

Sokolan commented 2 months ago

Checks

[X] This is not a duplicate of an existing issue (please have a look through our open issues list to make sure)
[X] I have thoroughly read and understand The Odin Project Contributing Guide
[X] Would you like to work on this issue?

Describe your suggestion

The code snippet provided in the lesson:

  SELECT users.id, users.name, COUNT(posts.id) AS posts_written
  FROM users
  JOIN posts ON users.id = posts.user_id
  GROUP BY users.id;

Is problematic as users.name appear in the SELECT statement, but it doesn't appear in the GROUP BY statement, unless we go into specific SQL dialect and dependencies, this isn't allowed (as far as I know) by the SQL standard.

Then this snipped:

  SELECT users.id, users.name, COUNT(posts.id) AS posts_written
  FROM users
  JOIN posts ON users.id = posts.user_id
  GROUP BY users.id
  HAVING posts_written >= 10;

Using the aliased posts_written column in the HAVING clause, although it's initiated in the SELECT clause. And due to the fact that HAVING will be executed before the SELECT it won't "know" about the alias (hence won't work). It also suffers from the same mistake as the previews snippet.

There's also a minor mistake of calling HAVING a function instead of a clause.

note: Those issues appears in both Ruby and Node paths.

Path

Ruby / Rails, Node / JS

Lesson Url

https://www.theodinproject.com/lessons/node-path-databases-databases-and-sql

(Optional) Discord Name

Sokolan

(Optional) Additional Comments

No response

CodeMaverick2 commented 3 weeks ago

I would like to fix this issue

JoshDevHub commented 3 weeks ago

@Sokolan Hello and thank you for making this issue. Sorry it's taken a while to have it looked at.

If you're still interested in fixing both of these examples, I'd gladly accept a PR that does so :rocket:

TheOdinProject / curriculum