Serverspace Black Friday
VB
November 2 2023
Updated November 2 2023

How to fix common SQL errors

Databases

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:

mysql -u<username> -p<password>
create database testdb;
use testdb;

pic0

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);

Create two tables into the database

 

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);

Filling this tables

 

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:

SELECT * FORM boys;

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:

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".

pic2

So if we correct our statement we get:

Table with boys

 

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:

SELECT *
FROM boys
WHERE Name = 'Fedya' and NOT FavoriteDiscipline = 'Math' and (Surname = Kurbanov or Surname = Shmelev);

And... an error appears

 

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:

SELECT *
FROM boys
WHERE Name = 'Fedya' and NOT FavoriteDiscipline = 'Math' and (Surname = 'Kurbanov' or Surname = 'Shmelev');

fix the command query

 

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":

SELECT *
FROM girls INNER JOIN boys
ON FavoriteDiscipline;

6

 

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:

SELECT *
FROM girls INNER JOIN boys
ON boys.FavoriteDiscipline = girls.FavoriteDiscipline;

this error is enough to add table aliases to the query

 

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:

SELECT Name, Surname FROM boys WHERE Name = 'Vanya' ORDER BY Surname;

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:

SELECT Name, Surname FROM boys ORDER BY Surname WHERE Name = 'Vanya' ;

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.

SQL commands are case-insensitive

 

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:

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:

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.

 

Vote:
5 out of 5
Аverage rating : 5
Rated by: 1
33145 North Miami, FL 2520 Coral Way apt 2-135
+1 302 425-97-76
700 300
ITGLOBAL.COM CORP
700 300

You might also like...

We use cookies to make your experience on the Serverspace better. By continuing to browse our website, you agree to our
Use of Cookies and Privacy Policy.