Common Mistakes

In this section, we have collected some common SQL mistakes and their resolutions to speed up your learning curve.

Example 1: Don’t narrow down the select scope via WHERE

As some tables are really big and include billions of rows, it’s better to narrow down the select scope with the WHERE clause. A common WHERE condition will be specifying the search dates.

Wrong SQL Example:

This SQL query without the data_creation_date condition will take a much longer processing time than it supposes to.

SELECT column_1, column_2, COUNT(1) AS COUNT FROM database.table GROUP BY 1, 2 ORDER BY 1 DESC LIMIT 100

Correct SQL Example:

SELECT column_1, column_2, COUNT(1) AS COUNT FROM database.table WHERE data_creation_date = '2022-7-14' GROUP BY 1, 2 ORDER BY 1 DESC LIMIT 100

Example 2: Wrong date format

The correct date format should be ‘yyyy-mm-dd’ (instead of ‘yyyy-m-d’).

Cautions: Unlike other data, the Solana data is currently using the date format of ‘yyyy-m-d’, and it will be changed to ‘yyyy-mm-dd’ soon.

Wrong SQL Example:

This query will return 0 rows because data_creation_date’s format is wrongly specified as ‘2022-07-04 instead of ‘2022-7-4’.

SELECT column_1, column_2 FROM database.table WHERE data_creation_date = '2022-07-04' LIMIT 100

Correct SQL Example:

SELECT column_1, column_2 FROM database.table WHERE data_creation_date = '2022-7-4' LIMIT 100

Example 3: Missing DATABASE name

Please always include the DATABASE name with the TABLE name. If you want to see a detailed explanation of the difference among DATABASE, and TABLE, please go to the Query Builder.

Wrong SQL Example:

SELECT column_1, column_2, FROM TABLE

Correct SQL Example:

SELECT column_1, column_2 FROM database.table

Example 4: Incorrect wrapping of DATABASE and TABLE

You do not need to wrap the name of DATABASE and TABLE.

Wrong SQL Example:

SELECT column_1, column_2 FROM `database`.`table`

Correct SQL Example:

SELECT column_1, column_2 FROM database.table

Example 5: Select TABLE name as COLUMN name

Only column names are selectable.

Wrong SQL Example:

SELECT table_name FROM database.table

If you want to see all the info from the COLUMNS, please use ‘*’ instead of the TABLE name.

Correct SQL Example:

SELECT COLUMN FROM database.table

Correct SQL Example:

SELECT * FROM database.table

Example 6: Incorrect Wrapping the Value

For a column value, you should wrap it with a single quote Don’t wrap it with a double quote or not wrap it at all.

Mistake: Use a double quote to wrap the value:

Wrong SQL Example:

SELECT column FROM database.table WHERE column = "some_value"

Mistake: Not wrap the value at all:

Wrong SQL Example:

SELECT column FROM database.table WHERE column = some_value

Correct SQL Example:

SELECT COLUMN FROM database.table WHERE COLUMN = 'some_value'

Example 7: Missing GROUP BY

If you want to find the COUNT for each category, please add a GROUP BY at the end.

Wrong SQL Example:

SELECT category, COUNT(*) AS count_for_each_category FROM database.table

This SQL will bring an error because you cannot use aggregate functions in the WHERE clause.
What you should do is use the HAVING clause.

Wrong SQL Example:

SELECT category, COUNT(*) AS count_for_each_category FROM database.table WHERE COUNT(*) > 10 GROUP BY category;

Correct SQL Example:

SELECT category, COUNT(*) AS count_for_each_category FROM database.table GROUP BY category HAVING COUNT (*) > 10

These examples showcase the most common mistakes that people often make. By practicing writing SQL, you will quickly learn from your own mistakes!


Did this page help you?