SQL can be confusing for beginners. Here are some recommendations to progress with SQL in a smooth way.
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.
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).
Detect all the concerned tables before starting to solve an exercise.
Make sure of having a good understanding of the join since it’s the most important SQL concept.
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.
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 (