Oracle Alias Usage in SQL

**Title: Mastering Oracle Alias Usage in SQL: A Beginner's Guide**

### Introduction Welcome to the fascinating world of Oracle Alias Usage in SQL! If you're new to SQL or looking to enhance your skills, you're in the right place. This guide will demystify the concept of aliases and show you how to use them effectively in your SQL queries.


So, let's get started!

### What is an Alias in SQL? An alias is used as a temporary name or any meaning full name assigned to a table or column in a query for better understanding of sql or plsql code. It's like giving a nickname to your data, making your queries more readable and concise.


Think of it as a shortcut that simplifies complex SQL statements without compromising on functionality!


### Why Use Aliases in SQL? Aliases offer several advantages, including:


- **Readability**


Aliases make your SQL queries easier to understand by providing meaningful names to tables and columns.


SELECT e.employee_name AS "Employee Name", d.department_name AS "Department" FROM employees e, departments d WHERE e.department_id = d.department_id;


- **Simplification**


Aliases can shorten lengthy table or column names, making your SQL statements more concise.


SELECT e.employee_name AS ename, d.department_name AS dname FROM employees e, departments d WHERE e.department_id = d.department_id;


### How to Use Aliases in SQL? Using aliases in SQL is straightforward! Let's walk through some examples to understand the concept better.


1. **Table Alias**


- Assign an alias to a table for easier reference in your SQL query.


SELECT e.employee_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;


2. **Column Alias**


- Assign an alias to a column to provide a custom name in the query result.


SELECT employee_name AS "Name", salary AS "Monthly Salary" FROM employees;


### Real-Time Examples:-- Let's delve into some real-world scenarios to see aliases in action:


1. **Joining Multiple Tables**


- Use aliases to join more than one tables and make the sql plsql query simple.


SELECT e.em_name, d.de_name FROM emp e, dept d where e.dept_id = d.dept_id;


2. **Subqueries**


- Use aliases in subqueries to reference or pointing to the main query.


SELECT emp_name FROM emp WHERE dept_id IN (SELECT dept_id FROM dept WHERE dept_name = 'Agricultural');


3. **Group By**


- Use aliases with aggregate functions and GROUP BY to simplify result sets.


SELECT dept_id, sum(sal) AS "Sum Salary of Employee" FROM emp GROUP BY dept_id;


Certainly! Let's dive into more real-time examples to further illustrate the usage of aliases in SQL. 4. **Order By with Alias**


- Use aliases in the ORDER BY clause to sort the result set by an alias name.


SELECT employee_name AS ename, salary FROM employees ORDER BY ename;


5. **Self Join with Alias**


- Use aliases to perform a self-join on a table, especially useful for hierarchical data.


SELECT e1.emple_name AS manager, e2.emple_name AS reportee FROM empl e1 ,emp2 e2 where e1.emple_id = e2.manager_id;


6. **Subqueries with Alias**


- Use aliases in subqueries to simplify complex queries and improve readability.


SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = (SELECT location FROM locations WHERE city = 'New York'));


7. **Calculations with Alias**


- Use aliases to perform calculations and display the result with a custom name.


SELECT employee_name, (salary * 12) AS "Annual Salary" FROM employees;


8. **Multiple Aliases in a Single Query**


- Use multiple aliases in a single query to reference different tables and columns.


SELECT e.employee_name, d.department_name, p.product_name FROM employees e, departments d, products p WHERE e.department_id = d.department_id AND d.product_id = p.product_id;


9. **Alias in Join Conditions**


- Use aliases in join conditions to make complex joins more readable and manageable.


SELECT e1.emple_name AS manager, e2.emple_name AS reportee FROM empl e1 ,emp2 e2 where e1.emple_id = e2.manager_id;


10. **Alias with Aggregate Functions**


- Use aliases with aggregate functions to provide custom names to calculated values.


SELECT dept_id, sum(sal) AS "Sum Salary of Employee" FROM emp GROUP BY dept_id;


Important Notes: To know more about Oracle Queries, Please go through the below youtube video links:-------

Topics that are covered in Videos:--

Visit Oracle Database tutorial


Oracle SQL Query Part 1


Oracle SQL Query part 2


Oracle Dual table and Alias Column


Oracle fetch clause, Oracle in and not in operator


Oracle between, Oracle Like Operator


Oracle IS NULL operator, , Oracle Joins (inner, left, right and full outer join)


Oracle Cross Join, Oracle self join


Oracle UNION, Oracle intersect, Oracle minus


Oracle Subquery, Oracle corelated subquery with exists operator


Oracle exists update stmt, ,Oracle Any operator


Oracle All operator, view creation, Oracle Rollup


Oracle PIVOT clause, Oracle UNPIVOT


Oracle INSERT , Oracle INSERT INTO SELECT


Oracle Update, Oracle Delete


Oracle MERGE statement


UnConditional Oracle INSERT ALL statement, Conditional Oracle INSERT ALL statement


Oracle CASE Expression


### Conclusion With these additional real-time examples, you should now have a comprehensive understanding of how to effectively use aliases in SQL queries.


Remember, aliases are your friends in the SQL world, helping you write cleaner, more readable, and efficient queries. So, don't hesitate to use them in your next SQL adventure!