Richard Cotton shows how to use aliases to rename tables, columns, and subqueries in SQL. He demonstrates with an Earthquakes database, aliasing the Nations and Cities tables to select the right columns, renaming output columns for clarity, and naming calculated columns so they can be referenced later in the query.
Chapters
In this lesson, we'll discuss how to use aliasing to avoid some kinds of errors and how it can make code easier to read and understand. Aliasing refers to providing a short name code or simple identity to tables, columns, and subqueries in a query. Aliases are temporary and only apply during the running of the query. They do not alter the table or column names in the database. They can be handy for making code in a query easier to read and in many cases are required, for example, a new column or subquery that will be reused in another part of the query. Let's take a look at some reasons why we need to use aliasing. We use aliasing to avoid repetitive use of long table or column names. Easily identify joined tables and associated columns, identify new columns, identify subqueries, avoid ambiguity when columns from joined tables share the same name and rename columns in this example, we want to know the 2017 population of each nation capital. We can get this by joining the nations and Cities tables from the Earthquakes database. When run, it returns an error because both tables have a column named POP2017. These refer to the 2017 census populations. In the nations table, it's the population of each nation and in the Cities table it's the population of each city. This query needs to know which is which. In this case we want the city population. Aliasing tables can correct this. Nations is N. Cities is c. Now if we look at the select statement, we see aliases used to indicate the source table of each selected column with the column name prefixed with the alias of the table name. The Cities table has a C alias and therefore 2017 population column is identified as C pop2017 Note there are no rules to what aliases should be. Just make sure they're intuitive and consistent. To make the output a little easier to read, we can choose to rename columns by aliasing the source columns in the select statement. In this example, we renamed country Name to country code 2 to country code and pop2017 to population. Anytime a new column is created, it should be given an alias. By doing this indicates what information is in the column and is also required if the column is referenced or used elsewhere in the query. In this example we are creating a new column which is the maximum magnitude earthquake by country and nearest population. We alias the new column as max magnitude. When using subqueries, we need to use an alias to refer to it in other parts of the query. In this example, we run a query that returns the biggest earthquake magnitude where country capital is the nearest population to the epicenter. We join the nations table, aliased as N to a subquery aliased as E. Let's now print.
