SQL tips
Get the basics right
If you do not understand the essential SQL clauses (select, from, where) you will not be able to handle the more complex constructions in the language.
Do things in the right order
The beginning of an SQL statement is not the most logical place to start. You should first decide which rows to include in the results, and then decide which columns you want to see. This will help to avoid a range of problems. When constructing an SQL statement with even a small degree of complexity, follow this sequence:
- from (including any joins)
- where
- group by
- select
- order by
Set your code out neatly
Lay out your code so that each clause appears on a different line. This will make it much more readable, and will help you to track down errors much more easily. There are many examples of good layout for you to follow in the notes.
Do subqueries independently
If you are constructing an SQL query that contains a subquery, make sure that the subquery works independently before embedding it into the outer query. Once you know the subquery works, you should not have to change it. Any errors you see later on must come from the outer query.
Use sensible table aliases
In SQLzoo, some exercises use x and y for table aliases. It is much more sensible in practice to use aliases that are abbreviations of the table names. This helps with readability.