3 ms·

IMHO, I think the material here is too brief, and insufficiently progressive, to help a beginner move to confidence. To master concepts, people need repetition

IMHO, I think the material here is too brief, and insufficiently progressive, to help a beginner move to confidence. To master concepts, people need repetition, and application of concepts to problems of sufficient difference to see the fundamentals of the concept apart from any particular. Giving people a dozen tasks, each introducing some different concept, won't leave them with a basis for a confident understanding. I also think beginners need: - A strong grasp of DISTINCT. This prepares them for the division of tables into subsets by GROUP BY, which is vital to an understanding of how aggregation and window functions operate. ORDER BY can used to show beginners how they can first organize the data, and see how different values in different fields can be used to segregate records into groups. - WHERE is important but actually not that hard to communicate. The key things about WHERE are one, you can specify true / false expressions whose evaluation dictates inclusion or exclusion of a record (this is crucial to understanding how a JOIN works), and two, you can use IN to compare a record's value to a _list_ of values. Once someone grasps this, it becomes very easy for them to see how a subquery works. - A strong grasp of how subqueries work. One of the great strengths of SQL is the ability to encapsulate complexity into a subquery and pass that result on to another query for further use. - A thorough walk-through on "how" JOINs work. What does it mean to JOIN two records? To JOIN two tables? How do we "filter" that join of two tables? (All of this at a _conceptual_ level, taking care to note that how we might think through a complicated join is _not_ how the operation is implemented by the software.) What is the difference between an INNER JOIN and a LEFT JOIN, and how are these differences useful? - Some discussion of just what exactly SQL is, what it is used for, and what it isn't used for. The tool has to be placed into some context so that beginners are oriented to where they will encounter it and what they can use it for. In particular they need to understand its interface, and the textual / non-visual nature of that interface. Most people are accustomed to working with computers through GUIs. The textual nature of SQL makes it very different, in profound ways, from the vast majority of tools people use on computers.

buckbova 12y ago
If your code is littered with DISTINCT clauses either you are doing something wrong or the data design is poor. DISTINCT leads to expensive SORT operations and effectively poor performance.
- swyman 12y ago
  I'm genuinely curious. Would you mind elaborating or pointing me to another resource that goes into detail on why this is the case?
  - buckbova 12y ago
    The poor design part or the performance part? The use of DISTINCT everywhere smells of denormalized tables. Now if you are in data warehouse or reporting position, then this likely makes sense. I tend to work in transactional applications and keep the redundancy down to an absolute minimum. I abstract away some of the complicated queries with views, procs, and functions where allowable. As for performance, different rdbms implement this differently, but the general query plan category for GROUP BY and DISTINCT is SORT. If you are querying something where you have multiple subqueries with DISTINCT and you and ordering the final results, you are adding extra sort operations to the query plan, hence hurting performance. I tend to design everything to optimize read operations because there tends to be much more read than write in systems I work in. For me this means denormalized and heavily indexed where table/index scans are extremely rare. On smaller data sets, some rdbms always scan tables because it saves operations based on table statistics. I hope this helps.

buckbova 12y ago

If your code is littered with DISTINCT clauses either you are doing something wrong or the data design is poor. DISTINCT leads to expensive SORT operations and effectively poor performance.

swyman 12y ago
I'm genuinely curious. Would you mind elaborating or pointing me to another resource that goes into detail on why this is the case?
- buckbova 12y ago
  The poor design part or the performance part? The use of DISTINCT everywhere smells of denormalized tables. Now if you are in data warehouse or reporting position, then this likely makes sense. I tend to work in transactional applications and keep the redundancy down to an absolute minimum. I abstract away some of the complicated queries with views, procs, and functions where allowable. As for performance, different rdbms implement this differently, but the general query plan category for GROUP BY and DISTINCT is SORT. If you are querying something where you have multiple subqueries with DISTINCT and you and ordering the final results, you are adding extra sort operations to the query plan, hence hurting performance. I tend to design everything to optimize read operations because there tends to be much more read than write in systems I work in. For me this means denormalized and heavily indexed where table/index scans are extremely rare. On smaller data sets, some rdbms always scan tables because it saves operations based on table statistics. I hope this helps.