6 Common Syntax Mistakes All SQL Learners Make

Common Syntax Mistakes

We all make mistakes when learning a new language – especially at the beginning. New words, complicated grammar… Everyone needs time to master a language. But when we get immediate feedback, we can progress more quickly. The same goes for learning SQL.

Mastering SQL Queries

Interactive SQL courses are one way to get immediate feedback on your queries. These courses, like the ones offered in Vertabelo Academy, usually introduce new concepts and then ask you to have a go at them right away. If you make a mistake, you’ll instantly get a message so you can correct it (and improve your SQL skills).

6 Common Beginning SQL Mistakes

Below is a list of SQL syntax mistakes that are especially easy for beginners to make. I bet you have made at least one of them a few times in your life! As I explain each mistake, I’ll use examples based on the car table exercises from Vertabelo Academy’s SQL Queries course. If you’ve done any of this course, they will be familiar to you; if not, the course is free to try.

1. Misspelling Table Names

When you start working with databases, you should get to know the table names right away. Let’s say we want all the rows and columns from the car table. Will this statement work?

wrong
SELECT * FROM cars;

Oops! It’s not working! Fortunately, the error message gives us a hint about the problem. In this case, there is no “cars” table in the database, which means we probably misspelled the table name.

Console: 1. Misspelling Table Names

The correct statement is:

correct
SELECT * FROM car;

Now everything should work!

Many errors are simply misspellings: for example, typing “commodiy” instead of “commodity” or forgetting the underscore in “postal_codes”. In these cases, the “Table X not found…” message would mean there is a typo in the query and we should correct it.

2. Using Incorrect or Non-Existent Column Names

When you start working with a new database (or with any database that has many columns), always double-check variable names. Unfortunately, SQL is not that smart. If you misspell a word, it will not correct it. Each letter in the word should be correct (and in the correct place).

Let’s find the production years for our cars:

wrong
SELECT year FROM car;

Again, something is wrong. Let’s see the console.

Console: Using Incorrect or Non-Existent Column Names

The “year” column does not exist. So we have to check the table once again (for example, by using SELECT * FROM car to see all the column names) and find the correct name for that column. Instead of “year”, it is “production_year”! So we type:

correct
SELECT production_year FROM car;

Now it is working!

The same “Column “x” not found…” message will also occur when you refer to a column from a table you intended (but forgot!) to JOIN.

Suppose there is an additional table called customer that has columns called “customer_id”, “name”, and “car_id”. If we JOIN the car and customer tables, we could match cars and their prices with customer data (using the “car_id” column).

But if we execute the query below…

wrong
SELECT price, name FROM car;

… we’ll get an error. There is no “name” column in the table car, and unless we tell it otherwise SQL will look for it there. This is why forgetting to join two tables will also generate a “column not found” error. The solution is to do the following:

correct
SELECT price, name FROM car JOIN customer 
ON car.id=customer.car_id;

3. Forgetting SELECT List Commas

Now suppose we want to select the “brand” and “model” columns from car. We write the following query:

wrong
SELECT brand model FROM car;

Unfortunately, it doesn’t work. In SQL, you have to separate column names with commas (as we usually do when making lists in English). All we need to do is add a comma:

correct
SELECT brand, model FROM car;

Mistake fixed! The query will work exactly as we want.

4. Leaving Out Quotation Marks

Another thing we need to remember is how to filter text values. In SQL, text values (like a brand of car or a city) are stored in text columns. What if we want to see all Toyotas in our car database? We type:

wrong
SELECT * FROM car WHERE brand = Toyota;

We get an error in the console, as shown below:

Console: Leaving Out Quotation Marks

It says that there is no “Toyota” column in the table. When we do not use quotation marks around a text value, SQL treats that value as a column name.

This query will not work because we did not put the value (‘Toyota’) in single quotation marks. Let’s correct the mistake:

correct
SELECT * FROM car WHERE brand = ‘Toyota’;

This is the proper way to filter text values. Note that SQL uses single quotation marks (‘text’) instead of double ones (“text”) as some other programming languages do. Anyone with a programming background should be especially cautious about the kind of quotes they use here!

5. Not Specifying Table Names after SELECTs

Some beginners forget to specify which table they want to select columns from. They write:

wrong
SELECT brand, price;

SQL is just a programming language and it has its rules. If you list columns, you need to specify the table from which the columns should be extracted. This is done with the FROM clause:

correct
SELECT brand, price FROM car;

6. Ordering Statements Incorrectly

Learning SQL grammar is not that complicated. The order in which statements appear is one of its components. Let’s try to select cars with a price under $10,000:

wrong
SELECT * WHERE price < 10000 FROM car;

We get this error in the console:

Console: Ordering Statements Incorrectly

It simply says there is a syntax error in the statement. We should look at the statement and see if everything is in the correct order. The order should always be:

  1. SELECT
  2. FROM
  3. WHERE

Now our code should read:

correct
SELECT * FROM car WHERE price < 10000;

All other SQL statements should be written in the correct order. For example:

  • SELECT brand, avg(price)
  • FROM car
  • WHERE price < 100000
  • GROUP BY brand
  • ORDER BY brand

Keep Learning SQL!

Beginning mistakes can usually be corrected quickly. Don’t get discouraged if you keep making the same simple mistakes – that’s part of the learning process. An interactive SQL course like Vertabelo Academy will help you find, fix, and outgrow your coding mistakes.

What would you add to this list?

What mistakes did you make as a beginning SQLer? What errors do you find yourself making when you’re not careful? Share your thoughts and observations in the comments section below. Other SQL students will appreciate it!

comments powered by Disqus

GET ACCESS TO EXPERT CONTENT!

Over 85.000 happy students
and counting!