Last week, DS52 and I tackled SQL for the first time. Whilst these sessions were definitely fun and interesting, the same silly errors kept popping up in our code whilst we were practising. Below are some of the most common mistakes that beginners (like us) face when learning SQL.
Clauses in the Wrong Sequence
One of the most common mistakes SQL beginners make is writing query clauses in the wrong order. It is important to remember at all times that SQL follows a strict sequence: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Mixing up this order will usually result in an error.
Example
This query fails because WHERE is called before FROM (which SQL does not allow!).
Here is what it should look like:
Tip #1
If you ever get a weird error, make sure you double-check the order of your clauses! A mnemonic that our cohort likes to use to remember this order is: Silly Frogs Wear Green Hats Often.
Quotation Marks
Strings in SQL must be enclosed in single quotes (‘ ‘). A common mistake is to use double quotes (“ “) which some databases reserve for identifiers like column or table names. Using the wrong type of quotes leads to syntax errors or incorrect results.
Example
This query fails because by using double quotes, SQL reads Line 4 as where rows from the Country column in the Circuits table equal the column USA. Because USA has not been defined as a column, this query is impossible.
Here is what it should look like:
Tip #2
Stick to single quotes for string values and check your database documentation if in doubt. On a similar note, it may also be useful to only give your columns aliases that only use underscores (Top_Score) instead of (“Top Score”) to avoid potential confusion later on when referring to them.
Using Keywords as Column Names
Using SQL’s reserved keywords (like max, select, or order) as column aliases is just asking for errors. Some databases allow this in certain situations but it’s usually better to avoid reserved words entirely (save yourself the headache of debugging it later on, trust me).
Tip #3
Check your database’s list of reserved words and avoid them in schema design. You can sometimes escape them by using backticks or double quotes but why give yourself the extra work? Sometimes you need to get creative with your column names and that’s okay!
Fun Learning Resources
To finish off, I would like to share some of my favourite (and most fun) resources for practising SQL:
- Summer of SQL: 10 weeks worth of challenges (+ a bonus!) covering key SQL skills with walkthroughs and solutions by our local SQL genius, Will Sutton.
- SQL Squid Game: You have been hired as a data scientist specialising in SQL by the Front Man. Complete these 9 challenges to survive the job (talk about a rough day at the office!).
- SQL Noir: You are a data detective who can solve criminal cases with the power of SQL. There are 6 unique cases for you to solve as of writing (more coming soon!).
- Lost at SQL: You are the captain of the SS.QL and must stop your submarine from sinking. Complete these 20 challenges (that gradually increase in difficulty) to find out how this story ends.