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.
|Get all columns from table EMP||This type of queries are used to get all columns and all rows from the table.|
|Get selected columns from table EMP||This type of queries are used to get all columns and all rows from the table.|
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…
Q1 : Write a query to get the employee name, department number, salary and commission for all employees.
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.
Q2 : Write a query to get the table name, employee number, designation, department number and salary for all employees.
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.
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 alias Syntax:
column_name AS alias_name
Table alias Syntax:
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
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.