Working with SELECT statement ...

In my previous post I have discussed about different operators available in SQL. In this post I want discuss about some important concepts related to writing the SELECT statement.

WHERE clause :


      Where clause can be used with all DML and DRL statements to limit  the effect of those statements to desired rows except INSERT statement. It means, we can have restrictions on rows to be displayed when using SELECT statement or we can specify which rows to be modified when doing UPDATE or we can specify which rows to be deleted using DELETE. So, WHERE clause is very useful while working with the SELECT statement.

     We can use any of the operators which we have discussed in my previous post in the conditions of WHERE clause.

   EX : SELECT * FROM Emp WHERE deptno = 10 AND sal BETWEEN 1800 AND 3000;

DISTINCT Key word :


      DISTINCT key word can be used to eliminate duplicates in the SELECT list. If we want to check set of distinct values in the column of a table then we can use DISTINCT key word. This will give us the distinct values present in particular column.

Ex : Let us say we want to find out different jobs present in a company by checking Emp table. But if we write SELECT statement on job column of Emp table then we will get lot of values because of the duplicity. This is because many employees will be there under single job. So, it is difficult to identify the unique jobs in the company. In this scenario DISTINCT will be very useful. This will display only unique or distinct values present in job column.

   SELECT DISTINCT job FROM Emp;

 We can have only one DISTINCT key word in a SELECT statement. We can list any number of columns after the DISTINCT key word.

Ex : SELECT DISTINCT job, deptno FROM Emp;

   This query will give us all distinct combinations of job, deptno from the employee table.

 Similarly we can use * also instead of column list as shown below.

     SELECT DISTINCT * FROM Emp;

  This will give us all the distinct rows from Emp table. If any row is present multiple times then it will show it only once in SELECT list.

  So, in this way we can eliminate the duplicates in SELECT list.

Column Aliasing :


     Column aliasing is very useful while writing the SELECT statements involving the expressions. Generally column aliases are used for display purpose in the SELECT statements. We can provide some meaningful name as alias for an expression in the SELECT list, to make it understandable for the users who are seeing the result.

    Aliasing is nothing but giving some other name to a column or expression. The scope of this new name will be only for that particular SELECT statement where we have used aliasing.

 Ex : Let us say we want to display the employee names and their total salaries from employee table. For this  we need to add sal and bonus columns of the employee table. The query can be written as,

   SELECT ename, sal + bonus FROM Emp;

But If we run this query then we will get total salary under column with name as sal+bonus . This will not be a proper column name to display as end users cannot understand it. So, here column aliasing will be very useful. Same query can be written using aliasing as shown below.

   SELECT ename , sal+bonus total_sal FROM Emp;

So, here I gave alias as total_sal to the expression sal+bonus. So, result of this expression will be displayed under a column with name total_sal which is a meaningful name understandable to any users.

If we are using the above syntax we cannot have spaces in the name of column alias. If we want spaces also in the name of column alias we have to use following syntax.

   SELECT ename, sal+bonus "Total Sal" FROM Emp;

  As, shown alias name should be enclosed in double quotes if we want to have spaces in it.

This is how we can use column aliases.

ORDER BY Clause :


     ORDER BY clause will be used for sorting the rows retrieved by SELECT statement. Using this clause we can sort the result of a SELECT statement in either ascending or descending order of single column or multiple columns. To sort the results in ascending order we will use ASC  keyword and to sort the results in descending order we will use DESC keyword.

     The default ordering using ORDER BY clause is ascending order. It means if we have not specified any thing about ordering after ORDER BY clause, then by default rows will be ordered in ascending order. So, ASC key word is not required to use as it is defaulting order. If we want to sort the result in descending order then we have to use DESC keyword.

  EX : Let us say we want to display details of all the employees in the ascending order of their salaries. Then,

     SELECT * FROM Emp ORDER BY sal ;

   If we want to sort in the descending order then,

     SELECT * FROM Emp ORDER BY sal desc;

  We can use any number of columns in the ORDER BY clause.

     SELECT * FROM Emp ORDER BY deptno, sal desc;

  If we have used more than one column in ORDER BY clause then the sorting will be done by using columns from lest to right . Let us consider above query for example.

   In this, first result will sorted in the ascending order of deptno and then in each department rows will be sorted in the descending order of the salary. So, like this we can sort the result using any number of columns.

In any SELECT statement the ORDER BY clause must be last clause. It means it should be placed at the end of the SELECT statement after all the conditions.

  The only place where we can use the column aliases which we have specified is ORDER BY clause.

    EX : SELECT empno, sal+bonus total_sal FROM Emp ORDER BY total_sal;

This is how we can sort the results using the ORDER BY clause.