New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Consideration to move questions into a new section: nested queries #194

Open
joshkyh opened this Issue Oct 13, 2017 · 1 comment

Comments

Projects
None yet
3 participants
@joshkyh

joshkyh commented Oct 13, 2017

In https://swcarpentry.github.io/sql-novice-survey/06-agg/

[What does this query do?] In order to find the differences of individual readings with the individual's average reading, I think we'd require nesting the query in the following manner:

SELECT S.* , AV.avg_reading , S.reading - AV.avg_reading AS diff_reading FROM Survey S INNER JOIN (SELECT person, quant, AVG(reading) AS avg_reading FROM Survey GROUP BY person, quant) AV ON S.person = AV.person AND S.quant = AV.quant ;

At this stage, nesting is not introduced, and I think nesting deserves a new section by itself given its usefulness.

I think the next question [Ordering When Concatenating] also has the same issue (requires nesting).

@pgmccann

This comment has been minimized.

Contributor

pgmccann commented Oct 14, 2017

I think the point with that question is not to get the result one might naïvely expect (which the query above is designed to produce) but rather to get the learner to reflect on how aggregation works and why the query doesn't produce what might be expected. I would suggest that at this point it is sufficient to make clear that a more sophisticated query is needed to get the desired result, and that it is out of the scope of the lesson.

An episode on nesting would need to come after the one on joins, and would require the introduction of aliases too. I suspect there just isn't the space in this lesson.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment