SQL Alias Syntax

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Introduction

In this chapter, we will learn how to use aliases in SQL. Aliases are used to give a temporary name to a table or a column in a SQL query. This chapter will cover the syntax, usage, and provide examples to help you understand how to use aliases effectively.

What is an Alias?

An alias is a temporary name given to a table or a column for the purpose of a particular SQL query. Aliases are often used to make column names more readable, to shorten lengthy table names, or to rename columns in the result set.

Syntax for Aliases

Column Alias Syntax

SELECT column_name AS alias_name
FROM table_name;

column_name: The name of the column you want to alias.
alias_name: The temporary name for the column.

Table Alias Syntax

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

table_name: The name of the table you want to alias.
alias_name: The temporary name for the table.

Example

Assume we have a table named employees:

SELECT first_name AS fname, last_name AS lname
FROM employees;

This command retrieves the first_name and last_name columns from the employees table and renames them to fname and lname in the result set.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables named employees and departments.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

3. Using Column Aliases

To retrieve first_name and last_name with aliases:

SELECT first_name AS fname, last_name AS lname
FROM employees;

Output

fname	lname
Ramesh	Kumar
Sita	Patel
Arjun	Singh
Priya	Sharma
Ramesh	Kumar

4. Using Table Aliases

To retrieve data from employees and departments using table aliases:

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;

Output

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance
Ramesh	Kumar	IT

5. Using Aliases in Calculations

To calculate and alias the total annual salary for employees:

SELECT first_name, last_name, salary * 12 AS annual_salary
FROM employees;

Output

first_name	last_name	annual_salary
Ramesh	Kumar	600000
Sita	Patel	720000
Arjun	Singh	660000
Priya	Sharma	744000
Ramesh	Kumar	600000

6. Using Aliases with Aggregate Functions

To calculate the total salary for each department and use an alias for the result:

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

Output

department_name	total_salary
HR	105000
Finance	122000
IT	50000

Conclusion

Aliases are used for making SQL queries more readable and manageable. This chapter covered the basic syntax for column and table aliases, using aliases in calculations, and using aliases with aggregate functions. Understanding how to use aliases effectively will enhance your ability to write and understand SQL queries.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

SQL Alias Syntax

Introduction

What is an Alias?

Syntax for Aliases

Column Alias Syntax

Table Alias Syntax

Example

Step-by-Step Example

1. Create Sample Tables

2. Insert Sample Data

3. Using Column Aliases

Output

4. Using Table Aliases

Output

5. Using Aliases in Calculations

Output

6. Using Aliases with Aggregate Functions

Output

Conclusion

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

ChatGPT + Generative AI + Prompt Engineering for Beginners

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

Building Real-Time REST APIs with Spring Boot - Blog App

Building Microservices with Spring Boot and Spring Cloud

Java Full-Stack Developer Course with Spring Boot and React JS

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

Testing Spring Boot Application with JUnit and Mockito

Spring Boot Thymeleaf Real-Time Web Application - Blog App

Master Spring Data JPA with Hibernate

Spring Boot + Apache Kafka Course - The Practical Guide

Java Testing: Mastering JUnit 5 Framework

Reactive Programming in Java: Spring WebFlux and Testing

Spring Boot + RabbitMQ Course - The Practical Guide

Functional Programming in Java (Includes Java Collections)

ChatGPT for Java Developers: Boost Your Productivity with AI

Comments

Post a Comment

Spring Boot 3 Paid Course Published for Free on my Java Guides YouTube Channel

Spring Boot 3 Paid Course Published for Free
on my Java Guides YouTube Channel