Dean Smith introduces SQL best practices for faster queries, using three sample databases: Earthquakes, NBA 2017, and Northwind. He covers formatting rules like uppercase keywords and new lines for each clause, plus commenting strategies to make code readable and maintainable.
Chapters
Hi, welcome. My name is Dean Smith and I'm 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, we'll be using three simple databases. The first is the Earthquakes database, which consists of 3 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. Earthquakes Table is related to cities via a country code and city name and Cities is related to nations via a country code. The second is The NBA Season 2017 database, which consists of three related teams, players and player stats. Each table contains Data from the 20172018 season of the U.S. national Basketball Association League or NBA for short. And finally, the Customers 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 North Twin Sample database from Microsoft. In this first lesson, we'll apply formatting and commenting to make the code easier 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'll still work the same. Check this query out Is it easy to read when writing queries? It's good practice to stick to a consistent format. Consistent formatting makes queries easier to read, easy to understand, and easier to debug. The main thing is to be consistent with our formatting across queries. Here are some suggestions, all of which we'll use in this course. Use uppercase for all SQL syntax. Create a new line for each major processing syntax. For example, select from and where. Apply an indent to subqueries on statements and or conditions, and to avoid long single lines of code. For example, many column names. Complete the query with a semicolon and alias where required, using as here's the same query from two slides back, this query looks much better. It's easier to read and understand. 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 running. To comment a block or multiple lines enclose the code or text in a forward slash plus star and here a block comment above the query is used to describe what this query should return. 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 conveying information about a particular line of code, marking a section break, or comment in a query. In commenting out a line of code that will not be executed at runtime but may be reused later on. For example, when debugging a query, let's practice some good.
