Sql · Basic SQL Queries
Alias
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Introduction to Aliases
In SQL, an alias is a temporary name given to a table or column for the duration of a query. It's like giving a nickname to make your queries more readable or to simplify complex table names.
We use the
AS
keyword to create aliases. Here's the basic syntax:
SELECT column_name AS alias_name
FROM table_name;
Let's try a simple example with our Codémon database:
SELECT name AS codemon_name
FROM codemon;
Notice how the column header in the result is now "codemon_name" instead of "name".
Exercise 2 — Multiple Column Aliases
You can use aliases for multiple columns in the same query. This is especially useful when you want to give more descriptive names to your columns or when working with calculated fields.
SELECT name AS codemon_name,
type AS element_type,
power AS battle_power
FROM codemon;
In this example, we've renamed all three columns with more descriptive names for our query results.
Alias Names with Spaces
If you want to use spaces or special characters in your alias names, you need to enclose them in quotes: SELECT name AS "Codémon Name" FROM codemon;
Exercise 3 — Alias Syntax
Which of the following queries correctly uses an alias for the 'power' column?
Options
• ```SELECT power AS strength FROM codemon;```
• ```SELECT AS strength power FROM codemon;```
• ```SELECT strength power FROM codemon;```
• ```SELECT power = strength FROM codemon;```
Hint : The correct syntax for column aliases is: SELECT column_name AS alias_name FROM table_name;
Exercise 4 — The AS Keyword is Optional
Interestingly, the
AS
keyword is actually optional in SQL. You can create aliases by simply putting the alias name after the column name with a space.
SELECT name AS codemon_name FROM codemon;
SELECT name codemon_name FROM codemon;
Both queries above produce the same result! However, using the
AS
keyword makes your queries more readable and is generally considered a best practice.
Readability Matters
While the AS keyword is optional, including it makes your SQL more self-documenting and easier for others (or your future self) to understand.
Exercise 5 — Complete the Aliases
Complete the query to select the name column as 'Codémon Name' and the type column as 'Element Type'.
SELECT name ____ "Codémon Name",
type ____ "Element Type"
FROM codemon;
Options: AS = ALIAS RENAME AS = ALIAS RENAME
Hint : When creating aliases, especially with spaces or special characters, use the AS keyword followed by the alias in quotes.
Exercise 6 — Table Aliases
Just like columns, tables can also have aliases. Table aliases are particularly useful when:
- The table name is long or complex
- You're joining multiple tables
- You need to reference the same table more than once in a query
Here's the syntax for table aliases:
SELECT column_name
FROM table_name AS alias_name;
SELECT c.name, c.type
FROM codemon AS c;
In this example, we've aliased the table name 'codemon' as simply 'c', and then used that alias to refer to the columns. This makes the query shorter and clearer, especially when dealing with multiple tables.
Exercise 7 — Alias Concepts
Match each alias concept with its correct description.
Items to match
• Column Alias
• Table Alias
• SELECT col AS new_name
• t.column_name
Definitions
• Temporary name for a column in query results
• Shorthand reference for a table name
• Standard alias syntax with AS keyword
• Column reference using a table alias
Exercise 8 — Using Table Aliases
Which of the following queries correctly uses a table alias?
Options
• ```SELECT c.name, c.type FROM codemon AS c;```
• ```SELECT codemon.name, codemon.type FROM c AS codemon;```
• ```SELECT name AS c.name FROM codemon;```
• ```SELECT c.* FROM c AS codemon;```
Hint : Table aliases are defined in the FROM clause, and then used to reference columns from that table.
Exercise 9 — Combining Table and Column Aliases
Complete the query to select the name column as 'Codémon Name' and the type column as 'Element' using a table alias 'c' for the codemon table.
SELECT ____ ____ "Codémon Name",
____ ____ "Element"
FROM codemon ____ c;
Options: c.name c.type name type AS AS AS RENAME ALIAS
Hint : When using both table and column aliases, reference columns with the table alias, then use AS to create column aliases.
Exercise 10 — Practical Uses of Aliases
Aliases are particularly useful in these scenarios:
1. When working with calculated columns
2. To make query results more readable
3. When you need to reference the same table multiple times
4. To simplify complex table or column names
Here's an example of using aliases with a calculated column:
SELECT name,
power,
power * 1.5 AS boosted_power
FROM codemon;
In this query, we're creating a new calculated column that shows what each Codémon's power would be if boosted by 50%. The alias 'boosted_power' gives this calculated column a meaningful name in the results.