SQL workshop: ALIAS

·

3 min read

In SQL, an alias is a temporary name assigned to a table or a column for the purpose of a particular SQL query. Aliasing can make queries more readable or allow you to perform operations like self-joins where you join a table with itself.

Why use Aliasing?

  1. Readability: It can help in making your SQL statements more readable.

  2. Space Saving: For large table or column names, it's easier to refer to them with shorter aliases.

  3. Necessity in Self Joins: In cases where you want to join a table with itself, aliasing is essential to differentiate between the two uses of the table.

Syntax:

💡
An alias only exists for the duration of that query. An alias is created with the AS keyword.

For tables:

SELECT column1, column2, ...
FROM table_name AS alias_name
WHERE condition;

For columns:

SELECT column_name AS alias_name
FROM table_name
WHERE condition;

Examples:

  1. Table Alias:
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Orders AS o, Customers AS c
WHERE o.CustomerID = c.CustomerID;
  1. Column Alias:
SELECT CustomerName AS Name, ContactName AS Contact
FROM Customers;

Workshop Exercise:

Objective: To understand the concept of aliasing in SQL.

Prerequisites: Basic understanding of SQL, access to a MySQL environment, a sample database, and tables.

Setup:

  1. Database & Table Creation:

    💡
    Don't forget to add access permissions to the database for the user to access the database via DBeaver. More info at Troubleshoot Access Denied Error: Connect DBeaver to a local MySQL database
     CREATE DATABASE aliasing_workshop;
     USE aliasing_workshop;
    
     GRANT ALL PRIVILEGES ON aliasing_workshop.* TO 'user'@'localhost';
    
     FLUSH PRIVILEGES;
    
     CREATE TABLE Students (
         ID INT PRIMARY KEY,
         FirstName VARCHAR(50),
         LastName VARCHAR(50),
         DateOfBirth DATE
     );
    
     INSERT INTO Students (ID, FirstName, LastName, DateOfBirth)
     VALUES (1, 'John', 'Doe', '2000-01-01'),
            (2, 'Jane', 'Smith', '2001-02-03'),
            (3, 'Bob', 'Johnson', '1999-04-05');
    

Exercises:

  1. Column Aliasing:

    • Task: Fetch the FirstName and LastName of all students, but rename the columns as First and Last respectively in the output.

    • Solution:

        SELECT FirstName AS First, LastName AS Last
        FROM Students;
      

  2. Table Aliasing with Join:

    • Task: Create a self-join to get pairs of students who have the same DateOfBirth.

      💡
      Before that insert additional two student's entries to the table

    • Solution:

        SELECT A.FirstName AS Student1, B.FirstName AS Student2, A.DateOfBirth
        FROM Students A, Students B
        WHERE A.ID <> B.ID AND A.DateOfBirth = B.DateOfBirth;
      
      💡
      Students A: This refers to the Students table, and it is temporarily given the alias A for the duration of the query. Students B: Similarly, the Students table is again referred to and given another alias, B.

  3. Combining Both:

    • Task: Using both table and column aliasing, fetch pairs of students with the same birth year and present their names as Name1 and Name2.

    • Solution:

        SELECT A.FirstName AS Name1, B.FirstName AS Name2
        FROM Students A, Students B
        WHERE YEAR(A.DateOfBirth) = YEAR(B.DateOfBirth) AND A.ID <> B.ID;
      

References

  1. SQL Aliases