SQL Aliases
In this lesson, you will learn how to give an alias name for the returned columns using SQL.
Aliases In SQL
An alias is a temporary name given to a column or a table during a query execution.
Usually, we use an alias to make column names more readable.
The AS keyword allow you to give an alias.
Syntax
If the alias name is one word only, you can use this syntax:
SELECT column_name AS alias_name FROM table_name;
If the alias name contains more than one word separated by a space, you should wrap it between quotes as follows:
SELECT column_name AS 'alias_name' FROM table_name;
Another available option is to wrap the alias name between square brackets as follows:
SELECT column_name AS [alias_name] FROM table_name;
Preparing A Database
Since you will learn how to give an alias name for a column, let's create a database table that aliases names are useful to apply on them. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(7,2)
);
-- Insert 10 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "Johnson", 800);
INSERT INTO employees VALUES (null, "Benjamin", "Anderson", 750);
INSERT INTO employees VALUES (null, "Olivia", "Smith", 900);
INSERT INTO employees VALUES (null, "William", "Davis", 845);
INSERT INTO employees VALUES (null, "Sophia", "Brown", 1000);
INSERT INTO employees VALUES (null, "Daniel", "Martinez", 700);
INSERT INTO employees VALUES (null, "Ava", "Taylor", 850);
INSERT INTO employees VALUES (null, "James", "Wilson", 920);
INSERT INTO employees VALUES (null, "Mia", "Garcia", 780);
INSERT INTO employees VALUES (null, "Ethan", "Jones", 880);
Rename Returned Columns Example
The following query selects all records in the employees table and rename the returned columns as follows:
idrenamed to ID.first_namerenamed to First Name.last_namerenamed to Last Name.salaryrenamed to Salary.
Example
SELECT id AS 'ID', first_name AS 'First Name', last_name AS 'Last Name', salary AS 'Salary'
FROM employees;
Output
| ID | First Name | Last Name | Salary |
|---|---|---|---|
| 1 | Emily | Johnson | 800.00 |
| 2 | Benjamin | Anderson | 750.00 |
| 3 | Olivia | Smith | 900.00 |
| 4 | William | Davis | 845.00 |
| 5 | Sophia | Brown | 1000.00 |
| 6 | Daniel | Martinez | 700.00 |
| 7 | Ava | Taylor | 850.00 |
| 8 | James | Wilson | 920.00 |
| 9 | Mia | Garcia | 780.00 |
| 10 | Ethan | Jones | 880.00 |
Give Alias Name Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "users" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTfirst_name VARCHAR(50)last_name VARCHAR(50)email VARCHAR(100)
3- Write a query that inserts 5 rows in the "users" table.
4- Write a query that selects all records from the "users" table.
Note: Columns names should be returned as ID, First Name, Last Name, Email.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO users (first_name, last_name, email) VALUES
('Jack', 'Reed', 'jackreed@example.com'),
('Emma', 'Clark', 'emmaclark@example.com'),
('Luke', 'Lee', 'lukelee@example.com'),
('Jim', 'Wilson', 'jimwilson@example.com'),
('Lily', 'Brooks', 'lilybrooks@example.com');
SELECT id AS 'ID', first_name AS 'First Name', last_name AS 'Last Name', email AS 'Email'
FROM users;
