SQL AS Clause

SQL AS Clause

The SQL AS clause is used to rename a column or table with an alias. This command is essential for improving the readability and clarity of SQL queries, allowing for more meaningful and descriptive names in query results.

---

Syntax

-- To alias a column name
SELECT column_name AS alias_name
FROM table_name;

-- To alias a table name
SELECT column1, column2, ...
FROM table_name AS alias_name;

• SELECT column_name AS alias_name: This is the SQL syntax used to rename a column with an alias.
• column_name: This specifies the name of the column to alias.
• alias_name: This specifies the new name or alias for the column.
• FROM table_name AS alias_name: This is the SQL syntax used to rename a table with an alias.
• table_name: This specifies the name of the table to alias.
• column1, column2, ...: These specify the names of the columns to retrieve.

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the AS clause to alias columns and tables.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01');

Here, we insert four rows of data into the employees table.

Step 4: Using the AS Clause to Alias Columns

This step involves using the AS clause to alias the first_name and last_name columns.

SELECT first_name AS fname, last_name AS lname, email, hire_date
FROM employees;

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

Step 5: Using the AS Clause to Alias Tables

This step involves using the AS clause to alias the employees table.

SELECT e.first_name, e.last_name, e.email, e.hire_date
FROM employees AS e;

This command retrieves the first_name, last_name, email, and hire_date columns from the employees table, and renames the employees table to e in the query for simplicity and clarity.