SQL AS Keyword Tutorial

Introduction

SQL (Structured Query Language) is used for managing and manipulating relational databases. The AS keyword in SQL is used to give a table or a column in a table a temporary name or alias. This alias exists only for the duration of the query.

Understanding the AS Keyword

The AS keyword is used for creating aliases. Aliases can make query results more readable and easier to understand. You can use AS for both columns and tables.

Column Aliases

Column aliases are used to rename a column heading in the result set. This is especially useful for making column names more readable or for combining columns.

Table Aliases

Table aliases are used to rename a table within a query. This can simplify queries, especially when joining multiple tables or when the table names are long or complicated.

Sample Scenarios and Codes

Scenario 1: Basic Column Alias

Problem

You have a table named employees with columns first_name, last_name, and salary. You want to display the full name of the employees in one column named Full Name.

Solution

Use the AS keyword to create an alias for the concatenated full name.

SELECT 
    first_name || ' ' || last_name AS "Full Name", 
    salary 
FROM 
    employees;

Step-by-Step Explanation

1. Concatenate Columns: first_name || ' ' || last_name concatenates the first_name and last_name with a space in between.
2. Alias: AS "Full Name" renames the concatenated result to Full Name.
3. Select: salary is selected as is, without any alias.
4. From: The employees table is the source of the data.

Scenario 2: Basic Table Alias

Problem

You have two tables, employees and departments. You want to join these tables and display the department name along with employee details.

Solution

Use table aliases to make the query more readable.

SELECT 
    e.first_name, 
    e.last_name, 
    d.department_name 
FROM 
    employees AS e
JOIN 
    departments AS d 
ON 
    e.department_id = d.department_id;

Step-by-Step Explanation

1. Table Aliases: employees AS e gives the alias e to the employees table, and departments AS d gives the alias d to the departments table.
2. Select Columns: e.first_name, e.last_name selects the first name and last name from the employees table.
3. Select Department: d.department_name selects the department name from the departments table.
4. Join: The JOIN operation is performed on the condition e.department_id = d.department_id, matching employees with their respective departments.

Scenario 3: Complex Column Aliases with Calculations

Problem

You want to display the annual salary of employees (assuming the salary column is monthly salary) and alias it as Annual Salary.

Solution

Calculate the annual salary and use the AS keyword to alias it.

SELECT 
    first_name, 
    last_name, 
    salary * 12 AS "Annual Salary" 
FROM 
    employees;

Step-by-Step Explanation

1. Select Columns: first_name, last_name selects the first name and last name from the employees table.
2. Calculate Annual Salary: salary * 12 calculates the annual salary by multiplying the monthly salary by 12.
3. Alias: AS "Annual Salary" renames the calculated column to Annual Salary.
4. From: The employees table is the source of the data.

Scenario 4: Using Aliases in Subqueries

Problem

You want to find the average salary of employees in each department and alias the result as Avg Salary.

Solution

Use a subquery with aliases to achieve this.

SELECT 
    department_name, 
    avg_salary 
FROM 
    (SELECT 
        d.department_name, 
        AVG(e.salary) AS avg_salary 
    FROM 
        employees e 
    JOIN 
        departments d 
    ON 
        e.department_id = d.department_id 
    GROUP BY 
        d.department_name) AS department_avg;

Step-by-Step Explanation

1. Subquery: The inner query calculates the average salary.
2. Select Columns in Subquery: d.department_name selects the department name, AVG(e.salary) AS avg_salary calculates the average salary and aliases it as avg_salary.
3. Join: The inner query joins employees and departments on department_id.
4. Group By: The inner query groups the results by department_name.
5. Alias for Subquery: The entire subquery is aliased as department_avg.
6. Select from Subquery: The outer query selects department_name and avg_salary from the subquery.

Conclusion

The AS keyword in SQL is a powerful tool for creating temporary names or aliases for columns and tables, making your queries more readable and easier to understand. By practicing these scenarios and understanding the step-by-step explanations, you can effectively use AS in your SQL queries to enhance clarity and maintainability.