SQL can be confusing for beginners. Here are some recommendations to progress with SQL in a smooth way.
Figure 11.1 : Database schema
Practice !
As it’s often the case in programming, it’s important to put things into practice. Go go to studybyyourself.com and do exercises. There you can even redo the examples discussed in the present course.
Database schema
Before starting to do an exercise make sure you well understood the database schema. If necessary try to imagine what data the tables may contain. Pay particular attention to the linking tables (e.g. Involvement).
Table
Detect all the concerned tables before starting to solve an exercise.
Join
Make sure of having a good understanding of the join since it’s the most important SQL concept.
Subgroup
In principle do not make use of the DISTINCT clause which has not been discussed. Using it means either that your understanding of subgroup is not perfect, or that the database you deal with is not well designed.
WHERE vs. HAVING
WHERE sets conditions on records whereas HAVING sets conditions on subgroups as a whole.
Execution order
It is very important to keep in mind the order in which operations are performed by SQL. First, tables are joined by forming one single aggregated table. Only records part of that latter table meeting the stated conditions are selected (WHERE). Based on those remaining records, subgroups are created (GROUP BY). Finally, the conditions on those subgroups are checked out (HAVING).