Image for post
Image for post

The Best BigQuery SQL Cheat Sheet for Beginners

A more fun approach to learning SQL-commands. By Jadwiga Wilkens.

Being new to coding with SQL-language remembering all important SQL-commands is not easy. Therefore I created a fun SQL-Command cheat sheet for commands used in BigQuery. For making the dry coding lines more fun, Josephine, the character drawn in the cheat sheet, is showing either the results of the displayed query or is acting based on it. A link to download a high-resolution version of the cheat sheet is at the end of the article.

Image for post
Image for post

1. DISTINCT

Since there are only three types of fruits, the DISTINCT(type) command gives back three rows as melon, apple, and grape, depicted with only the outline of the fruits.

Image for post
Image for post

2. ORDER BY

First of all, to make code understandable and readable for other persons the

Image for post
Image for post

3. WHERE

Filters out rows that do not meet the requirements given in the WHERE statement. Here only the fruits are selected with ripeness date older than the current date, meaning they already went bad. So Josephine wants to throw them in the little trash bin drawn next to her.

Image for post
Image for post

4. ALIAS

Again, a good coding style requires to make people who did not write the code understand what the specific statements are doing. Using aliases for columns or table names that are not immediately clear or can be confusing helps a lot to comprehend statements. Keep in mind that the alias name cannot be used in the WHERE and JOIN statements, the original name has to be spelled out there. And for tables, once an alias is set up, only this alias can be used to refer to this table.

Image for post
Image for post

5. GROUP BY and HAVING

Running this query without the HAVING statement, only three rows will show up, like in the DISTINCT statement. But this time there is a second column, the COUNT(name) column which is named as amount where the number of items having the same type is counted. One could also write COUNT(ripeness) or COUNT(weight). When a condition on the amount column is set, a WHERE statement cannot be used. Instead, the HAVING statement is filtering out the rows where the condition is not fulfilled, here the alias amount could be used instead of COUNT(name). Now there is only one row with the amount being smaller than three, namely the grapes. Josephine has to reorder this type of fruit since there are only two left.

Image for post
Image for post

6. CASE WHEN and DECLARE

To make the CASE statement more readable, a DECLARE statement is used to define the variable today storing the current date. After declaring the name of the variable the type has to be given. A short list of other important types is written down in the Cheat Sheet. After the type definition, a default value can be given, otherwise, the variable has to be filled during the code which follows.

Image for post
Image for post

7. JOINs

Here a new table is introduced, the calories_table. Some fruits and their calories per gram is stored, but not all fruits. As can be seen in the Venn-diagram drawing, apple, grape, and banana is included but not melon when it comes to the fruits table. Meaning that by left joining the fruits table with calories_table, the two items in the overlapping of the Venn-diagram are assigned to a cal_per_g value but the melons entries for cal_per_g are staying empty, a null value is displayed. And there is no banana row appearing in the result. Apparently, a friend of Josephine does not know that null means empty and thinks the melon she is holding in her hand has null calories, which is, of course, wrong.

Image for post
Image for post
Image for post
Image for post

8. UNION and WITH … AS

If two tables have the exact same column structure, they can be appended to each other using a UNION statement. There are two types of UNIONs, one is UNION all where the whole two tables are put together. And there is UNION DISTINCT where the same rows appearing in both tables are not repeated in the result.

Image for post
Image for post

9. Easter eggs

Cross joining two tables can lead to a huge amount of rows, since a cross join puts every row from the first table together with every row from the second table, meaning that the number of rows from both tables is multiplied with each other.

Image for post
Image for post
Image for post
Image for post

Written by

Agency of Business Intelligence specialists, based in Coburg, DE. Building modern, customized and cloud-based data warehouse solutions. https://www.datadice.io/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store