The reserved word SELECT in Oracle, is used to retrieve rows from tables.

Consider below table. You can download the table DDL/DML here.

Let’s write and understand simple SELECT queries.

The order of columns specified in the SELECT statement, may not be in same order as that of table columns. Example: You can specify: SELECT deptno, ename, job FROM emp; This is absolutely right though EMP table columns are in different order.

Look at some examples now…

SELECT ename, deptno, sal, comm 
  FROM emp;

You can add a virtual column (column not existing in table) to a SELECT statement by specifying a literal or through an expression.

SELECT 'EMP' AS table_name, empno, job, deptno, sal 
FROM emp;

Q3 : Write a query to get the table name, employee name, designation, department and total salary for all employees.

Ans:

SELECT 'EMP' AS table_name, ename, job, deptno, sal + nvl(Comm, 0) AS Total_Sal
  FROM emp;

How to specify column alias or table alias?

Aliases are temporary names for a table or a column, available only within the query. Aliases are specified using the alias name after the column name separated through a space. You can also use AS keyword to specify a column alias but that is optional. Use of AS keyword to specify table alias is not accepted.

column_name AS alias_name

column_name alias_name

table_name alias_name

Q4 : Write a query to get the employee number, designation and salary for all employees but columns should be displayed as below:

empno – EmployeeID

job – Designation

sal – BaseSalary

Ans:

SELECT empno AS EmployeeID, job AS Designation, sal AS BaseSalary
FROM emp;

Declaring column alias without AS keyword.

SELECT empno EmployeeID, job Designation, sal BaseSalary
FROM emp;

Importance of column alias and table alias in SQL

1. Column alias increases readability. By giving proper alias names for columns increases data understandability.

SELECT
      empno AS "Employee ID", 
      job   AS "Designation",
      sal   AS Salary
  FROM emp;

2. Table alias makes column qualifying easier. When you have bigger table names, giving a smaller table alias makes easy to refer everywhere in the code.

--Table name is used to qualify table

SELECT
      emp.empno AS "Employee ID", 
      emp.job   AS "Designation",
      emp.sal   AS Salary
  FROM emp;

--Small table alias makes column qualifying easy

SELECT
      e.empno AS "Employee ID", 
      e.job   AS "Designation",
      e.sal   AS Salary
  FROM emp e;

Below is one more example illustrating the use of table alias in a Join query.

--Table name is used to qualify table and looks untidy

SELECT
      employee.empno              AS "Employee ID", 
      employee.job                AS "Designation",
      employee.sal                AS Salary,
      department.department_name  AS Department
  FROM employee, department
 WHERE employee.department_id = department.department_id;

--Small table alias makes column qualifying easy

SELECT
      e.empno            AS "Employee ID", 
      e.job              AS "Designation",
      e.sal              AS Salary,
      d.department_name  AS Department
  FROM employee e, department d
 WHERE e.department_id = d.department_id;

3. Column alias play a very important role in SQL queries. If not mentioned carefully, they can change the complete meaning of a query result set. Also, comma if missed between columns they act as column alias. Watch the below query and see the misleading behaviour though the query gives no error.

SELECT empno ename, job Salary, sal AS designation
FROM emp;

Following observation in the above code.

- empno ename: No comma is specified between columns. Here ename acts like alias to empno.
- job is aliased with Salary which is not a realistic name.
- Similarly, sal column is aliased with designation.

All these definitely make the result erroneous. These types of errors can not be raised by Oracle database. These errors are called as Semantic error.