Introduction

1. Introduction

Hi. Welcome. My name is Dean Smith. I am the founder of Atamai Analytics, and I'll be your instructor for this course. When writing an SQL query, there are often many ways to achieve the same results. In this course, we'll look at good coding practices and different ways we can improve query performance to achieve the same outcome.

2. Earthquakes database

We'll be using three simple databases. The first is the Earthquakes database which consists of three tables. Earthquakes: lists all recorded earthquakes between 1900 and 2018 with a magnitude of 7 or more. Cities: lists world cities with associated data and statistics. And Nations: which contains all the world's nations and related data and statistics. The Earthquakes table is related to Cities, via a country code and city name, and Cities is related to Nations via a country code.

3. NBA Season 2017-2018 database

The second is the NBA Season 2017-2018 database, which consists of three related tables: Teams, Players and PlayerStats. Each table contains data from the 2017-2018 season of the US National Basketball Association league, or NBA, for short.

4. Customer Orders database

And finally the Customer Orders database, which consists of two related tables. The Customers table contains sales customer IDs and associated data. And the Orders table contains sales orders, and associated data, for each customer. This database is a subset of the freely available Northwind sample database from Microsoft.

5. Is this easy to read?

In this first lesson, we'll apply formatting and commenting to make the code easy to read and understand. Unlike many other programming languages, SQL syntax is case-insensitive, which means it doesn't matter if the case of the syntax is upper, lower or a mixture, it will still work the same. Check this query out. Is it easy to read? When writing queries, it is good practice to stick to a consistent format. Consistent formatting makes queries easier to read, easier to understand, and easier to debug.

6. Suggestions

The main thing is to be consistent with our formatting across queries. Here are some suggestions, all of which we will use in this course. Use UPPER CASE for all SQL syntax. Create a new line for each major processing syntax, such as SELECT, FROM, and WHERE. Apply an indent to sub-queries, ON statements, AND/OR conditions, and to avoid long single lines of code, for example, many column names. Complete the query with a semi-colon. And alias where required, using AS.

7. Much better...

Here is the same query from two slides back. This query looks much better. It's easier to read and understand.

8. Commenting blocks

So, what is the purpose of this query? Adding comments is an excellent way to convey what the query is about or information about certain parts of the query. Code or text that is within a comment, or commented out, will not be executed when the query is run. To comment a block, or multiple lines, enclose the code or text in a forward slash plus star and star plus forward slash.

9. Commenting blocks

Here, a block comment, above the query, is used to describe what this query should return.

10. Commenting lines

To comment on a single line, instead of an entire block, place two dashes in front of the code or text. Common uses for commenting single lines include:

11. Commenting lines

conveying information about a particular line of code,

12. Commenting lines

marking a section break or comment in a query,

13. Commenting lines

and commenting out a line of code that will not be executed at run-time but may be reused later on, for example, when debugging a query.

14. Let's practice

Let's practice some good coding practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.