When you type some commands to run database query, you're hope to get an answer, but... suddenly something going wrong and you got an error message instead.
No need to worry! Making mistakes while coding, including when using SQL, is completely normal. In this article, we will discuss common errors that people often encounter when writing SQL code. We will explore various types of mistakes that can occur while working with SQL. By the end of this article, you will have a better understanding of these common mistakes and be able to avoid them. It is particularly important for SQL beginners to be aware of these mistakes and how to address them, as newcomers to coding often make more errors and spend additional time debugging their code. The article will provide further details on the common SQL mistakes to watch out for.
Preliminary steps
To illustrate common SQL errors we will create database with some data inside. Connect to your database server, create an empty database with testdb name and make this database active:
create database testdb;
use testdb;
Create two tables into the database:
CREATE TABLE boys (
ID int,
Name varchar(20),
Surname varchar(20),
FavoriteDiscipline varchar(20),
Age int);
CREATE TABLE girls (
ID int,
Name varchar(20),
Surname varchar(20),
FavoriteDiscipline varchar(20),
Age int);
Then fill this tables:
INSERT into boys (Id,Name,Surname,FavoriteDiscipline,Age)
VALUES
(1,'Vanya','Sidorov','Math',15),
(2,'Petya','Shmelev','Literature',14),
(3,'Seryozha','Petrov','Music',15),
(4,'Fedya','Kurbanov','Geography',15),
(5, 'Vanya','Korolyov','History',15);
INSERT into girls (Id,Name,Surname,FavoriteDiscipline,Age)
VALUES
(1,'Olya','Sukhova','History',14),
(2,'Irina','Vlasova','Music',14),
(3,'Vera','Kurbanova','Literature',15);
We will use this database in next steps. Lets describe common SQL errors!
Commands misstyping
Imagine if you said "Nice dukc" instead of "Nice duck". The person would be confused because they don't know what "dukc" means. It's the same with a computer database. If you tell it to find a "TABEL" instead of a "TABLE", it won't know what to do because it doesn't understand what "TABEL" means. One practical example - look at this simple sentence:
Can you see any mistakes in it?
If you want to see all the information about boys in the class, you need to write this code. But if you try to run it, you might see a message saying there is a mistake:
There is a mistake in this sentence. The word "FROM" is spelled wrong as "FORM". Sometimes, words are spelled wrong in important parts of computer instructions, like when we say which information we want to select, where we want to get it from, or what conditions it needs to meet.
The most common mistakes people make when writing SQL are usually because they spell things incorrectly.
To solve the problem, you can use any online SQL editor that helps you by highlighting the correct words, like "SELECT" and "WHERE", but it won't highlight the misspelled word "FORM".
So if we correct our statement we get:
Missing "delimiter" symbols
Imagine that you are looking for information about a person named Fedya. We're remember that his last name may be Kurbanov or Shmelev. Also, it seems he was really hate the mathematics but you not sure. So, lets run the query:
FROM boys
WHERE Name = 'Fedya' and NOT FavoriteDiscipline = 'Math' and (Surname = Kurbanov or Surname = Shmelev);
And... an error appears
Do you know the reason behind it? Forgetting to close brackets is a common mistake in SQL that often occurs. So, when we fix the command query will be completed:
FROM boys
WHERE Name = 'Fedya' and NOT FavoriteDiscipline = 'Math' and (Surname = 'Kurbanov' or Surname = 'Shmelev');
The same principle applies to single quotes (' ') or double quotes (" "). In SQL, it is always necessary to have a pair of quotes. There is no situation where a quote (single or double) should be left without its corresponding pair.
For text values in a column, if there is a need to include a quote within the value (e.g., Surname = "O'Henry"), we either use a combination of different types of quotes or utilize escape characters. In SQL, escape characters are used to deactivate the special meaning of a character, and they are commonly placed before the character that needs to be escaped, such as by adding another quote near it.
Aliases neglecting
When we combine information from different tables, it can sometimes confusing the machine because some columns have the same name. To avoid this confusion, we give each table a nickname called an alias. This helps the database know which column values to show us. It's not always necessary to use aliases when combining different tables, but it is necessary when combining a table which has things with the same names.
Imagine we are looking for all boys and girls who love the same discipline. To do this, we can use a special code called join and compare the content in both tables. So, lets construct the query according the "common rules":
FROM girls INNER JOIN boys
ON FavoriteDiscipline;
If you see the words ambiguous column name in the error message, it means you need to give a nickname to the table cause both tables. boys and girls, has a table with FavoriteDiscipline name. To fix this error is enough to add table aliases to the query:
FROM girls INNER JOIN boys
ON boys.FavoriteDiscipline = girls.FavoriteDiscipline;
Wrong order of statements
The order of statements is crucial in SQL. When writing SELECT statements, it's important to follow a predefined keyword order to ensure the proper execution of the statement. There is no flexibility or room for deviation in this regard, but no "rocket science" too. Correct order is "<action>, <place>, <search_filter>, <group_or_sort_options>. Example - we will find all boys who called "Vanya" and order output by surname:
It is "hardcoded" order, if we try to swap the words we will get an error:
Solution:
Don't feel sad or give up! Look, all the important words are placed correctly and all the quote marks and brackets are closed.
CAPITAL names
In most cases, SQL commands are case-insensitive, meaning that the letter case does not affect their execution.
However, if you create a table with the same name but with a different letter case, it can lead to further complications and potential problems. To illustrate lets create an "empty" table named GIRLS:
Now, if you accidetally change command case, it may be result of unexpected output. Imagine that we looking for surname of girl with name "Olya". And, if we turn caps lock on, we got an error:
Solution - avoid using CAPITALIZED names if it's possible.
These are the typical errors that individuals often make when writing SQL code. Making mistakes is a common occurrence while learning, and it's a natural part of the process of programming. Don't feel discouraged or give up. As you gain more experience, it's helpful to approach your code in an organized manner, allowing you to identify and rectify mistakes more efficiently.