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.


   

Operators in SQL....

In this post I want to discuss about different operators which are available in SQL. These operators are very useful while writing the SQL statements depending on the requirement.

  The different types of operators which are available in SQL are,

        1. Concatenation Operator
        2. Arithmetic Operators
        3. Logical Operators
        4. Comparison Operators

  Before discussing about these operators we need to know about a table named 'DUAL' which is very
  important in writing SELECT statements.

  Dual ::


       Dual is a pseudo table which is having single row and single column. This is system table of database.
This table will not contain any data on its own. If we select the data using select statement from dual
table then we will see data as 'x'. This table is used to evaluate expression on the data which is not stored
in the database tables. So, this table is used for evaluation of general expressions.

      As, dual table will not contain any data, always it will display the output of the expression which we
are evaluating using dual.

EX : Let us say we want to check the sum of two numbers 234567 and 876954. If we want to add these
two numbers we can use + operator. But these two are independent numbers which are not stored in the
database. So, we need to evaluate arbitrary expression here. For this purpose dual table will be used.

   SELECT 234567 + 876954 FROM Dual;

So, this statement will give us the result of the sum of those 2 numbers. Like this, If we want to evaluate the
expressions involving all arbitrary values where data base tables not involved, we can use Dual table to evaluate them.

Now Let us discuss about the Operators in SQL.

Concatenation Operator ::


   Concatenation operator is used to combine two different strings into a single string. Using this operator we can combine multiple strings into single string. ' || ' will be used as concatenation operator in SQL.

 SYNTAX :  string1 || string2 || string3 ....

 EX : SELECT 'Ram ' || 'Babu' || ' Kandimalla' FROM dual;

  If we execute this statement then we will get output as , Ram Babu Kandimalla. So, here we have concatenated 3 strings into a single string using this operator. Whenever we have a requirement of combining two or more strings we can use concatenation operator.

 Ex : Let us say ,We have Emp table having many columns like empno , ename, sal, hiredate ..... . From this table I want to display the output in the format  like 'Ramu is earning sal of 5000'. This I can achieve with following query using concatenation operator.

  SELECT ename||' is earning sal of '|| sal FROM Emp;

This query will give us the required result. So, like this wherever we have the requirement of combining two strings or values into single string or value we can use concatenation operator.

Arithmetic Operators ::


    Arithmetic Operators are used to perform arithmetic operations on numeric columns or numeric values. We have 4 arithmetic operators available.

   1. Addition Operator ( + )
   2. Subtraction Operator ( - )
   3. Multiplication Operator ( * )
   4. Division Operator ( / )

All these operators are used to perform arithmetic operations.

 Ex : SELECT 2345667 + 543276 FROM dual;
        SELECT sal + bonus FROM Emp;
        SELECT 2358976 / 4567 FROM dual;
        SELECT sal * 5 FROM Emp;

Precedence of Arithmetic Operators ::


   Precedence means, which arithmetic operator will be executed first, if we have all the 4 arithmetic operators in an expression as shown below.

   SELECT 12897654 + 234567 * 45321 / 567 - 2345 FROM dual;

  In this case first division operator (/) will be executed first . So, here 45321/567 will be executed first. after that multiplication operator will be executed. 234567 * (result of division) . Then either + or - will be executed. Like this the expression will be evaluated and result will be displayed.

 So, Order of precedence of the arithmetic operators evaluation is,

    /     *   +   -

 If we want to override this precedence then we have to enclose the operators in brackets. Then precedence will be override accordingly.

Ex : SELECT (1298677+23456) * 45678 / (567-234) FROM dual;

     Now even though we have all the 4 operators in this expression, we gave  + and - operators in brackets. So, first they will be executed. and after that / followed by * will be executed. Like this we can override the precedence.


  Comparison Operators ::


     Comparison Operators are used to perform comparison operations in SQL.  While writing SQL statements, comparison operations are very useful depending on the requirement. To, perform these comparison operations we will use these operators. We have many comparison operators in SQL.

    1 . Equal to operator ( = ) :: used to match single value
    2.  Not equal to Operator ( != or <> )
    3.  Less than Operator ( < )
    4. Greater than Operator ( > )
    5. Less than or equal to Operator ( <= )
    6. Greater than or equal to Operator ( >= )
    7. In operator
    8. Like Operator
    9. Between And Operator
    10. Is Null Operator

 All these Operators are used to perform Comparison operations in SQL. Operators from 1 to 6 are general comparison operators which we all know about what they are used for. So, I will just give examples for these operators.

While performing comparison operation with character data, we must remember that this operation is case sensitive. It meane 'RAMU' is not equal to 'ramu' as first is containing upper case letters and second one is containing lower case letters. So, we need to be careful while performing comparison operation with character data. We must use correct case for the letters. Similarly we must be careful while comparing date data . We must use default format of dates for comparison.

    EX : SELECT * FROM Emp WHERE ename = 'RAMU';

            The above query will give the row corresponding to employee with name 'RAMU' from Emp table.

            SELECT * FROM Emp WHERE ename <> 'RAMU';
            SELECT * FROM Emp WHERE hiredate != '13-JAN-2011';

            The above two queries are performing not equal to operation.,

            SELECT * FROM Emp WHERE sal < 15000;
            SELECT * FROM Emp WHERE sal > 1500;
            SELECT * FROM Emp WHERE sal <= 15000;
            SELECT * FROM Emp WHERE sal >= 15000;

    Now let us discuss about remaining comparison operators.

  In Operator ::

      In Operator is used for matching multiple values to single column. Using '=' operator we can match single value only. If you want to compare multiple values in single comparison then we can use IN operator.

  EX : If we want to find out all the employees from departments 10, 20 and 30 . Here we have to match deptno column with values 10,20 and 30 at a time. In this case we can use In operator.

    SELECT * FROM Emp WHERE deptno IN ( 10, 20, 30 );

 This is the syntax for using the IN operator. Like this using IN operator we can match multiple values in single comparison.

 IS NULL Operator ::


     This operator is used to check the presence of null values in a column. If we want to check whether null values are present in any column we can use this operator.

  EX : If we want to check if null values are present comm column of emp table in any of the rows we can use following statement.

   SELECT * FROM Emp WHERE comm IS NULL;

 So, this will give us all the rows of Emp table where comm column contains null values.

 Between - And Operator ::


      This operator is used to match range of values to a column in single comparison. Unlike In condition where we will match selective multiple values, we will match range of values using this operator.

 EX : If we want to find out all the employees from emp table who are earning sal in the range of 1500 to 2000. We cannot do this using In as we have to list 500 values in In condition. So, here Between And operator will be useful. We can write the select statement for this requirement as shown below.

      SELECT * FROM Emp WHERE sal BETWEEN 1500 AND 2000;

   This operation is inclusive of comparison values. It means employees with sal's 1500 and 2000 also will come in the result.
     
  Like Operator ::


    Like operator will be used to match single or multiple characters with character or numeric columns. If we want to match single character or multiple characters to get the result without matching entire words we can use Like operator . Using this Like Operator we can match entire words also as shown below.

     SELECT * FROM Emp WHERE ename LIKE 'RAMU';

  But main use of like operator comes with its ability to match partial values . This can be achieved using two special characters called as Wild cards. So, While discussing about Like operator in detail , we will discuss about Wild cards also.

  We will use two wild cards in SQL to work with the Like Operator. They are,

       %     and   _ (under score)

   % will be used for matching the partial characters. If, we place % after set of characters like 'ab%' then it means that we want all the strings starting with ab. If we place % before the set of characters like '%ab' then it means that we want all the string starting with ab. If we place % on both sides like '%ab%' then it will give us all the strings containing string 'ab' in any position. Like this we can use % for matching partial characters depending on the requirement.

Let me explain this with an example.

 EX : Let us say we want to find out all the employees from Emp table , whose names starts with letter 'R' then we can write the query as,,

       SELECT * FROM Emp WHERE ename LIKE 'R%';

  If we want to find out employees whose name ends with 'H' then we can write the query as,

      SELECT * FROM Emp WHERE ename LIKE '%H';

 If we want to find out employees whose name contains 'BA' in any position of their name we can write the query as,

      SELECT * FROM Emp WHERE ename LIKE '%BA%';

 Depending on the requirement we can use this % operator accordingly.

If we want to find out employees whose name starts with 'A' and ends with 'K' then we can write this query as,

     SELECT * FROM Emp WHERE ename LIKE 'A%K';

If we want to find out out employees whose names starts with 'S' and ends with 'R' and containing 'H' in any position of their names we can write the query as,

     SELECT * FROM Emp WHERE ename LIKE 'S%H%R';

So, position of % in comparison condition will decide  the position of searching characters in comparison strings. So, we must place % in appropriate position to get desired result.

 _ (under score) wild card will be used to skip a position in the string from the comparison. Let me explain this with examples. Generally this will be used in conjunction with % wild card.

 Ex : Let us say we want to find out all the employees whose names contain letter 'A' in second position. In this case we dont bother about the letter in the first place. So, we need to skip that from comparison. In this case _ will be very useful. We can write the query for this as shown below.

  SELECT * FROM Emp WHERE ename LIKE '_A%';

 So, this will give us all the employees whose names contain letter 'A' in second position. So, like this _ wild card will be used to skip certain positions of string from the comparison.

   So, we can use _ and % wild cards in conjunction along with Like operator depending on the requirements to get the desired results.

Logical Operators ::


     Logical operators are used for performing logical operations in SQL . These are very useful while writing the SQL statements with comparison operators. We have 3 logical operators available in SQL.

    1. AND operator
    2. OR operator
    3. NOT operator

 AND and OR logical operators are used to combine or evaluate multiple conditions together. we can combine two conditions using AND or OR operators. If we want to combine more, we have to use brackets.

If we have combined two conditions using AND operator, then if both the conditions are satisfied then only we will get the result.

EX :  SELECT * FROM Emp WHERE empno = 123 AND deptno=20;

      This statement will produce the result only if the table contains a row having empno as 123 and deptno as 20. Other wise this statement will not produce any result.

If we have combined two conditions using OR operator then we will get the result if any of the  two or both the conditions satisfied.

EX : SELECT * FROM Emp where empno=123 OR deptno=20;

This statement will give us all the employees from deptno = 20 along with employee with empno as 123.

NOT logical operator will be used to perform negation operation on the conditions mentioned. Let me explain this with examples. we can use this operator in conjunction with certain comparison operators to get the negative comparison results.

EX :  SELECT * FROM Emp WHERE deptno NOT IN (10,20);
           
          The above statement will give details of all the employees from emp table except from depts 10 and 20.
         SELECT * FROM Emp WHERE ename NOT LIKE 'A%';
 
          The above statement will give us all the employees from emp table except those employees  whose name starts with A.

         SELECT * FROM Emp WHERE comm IS NOT NULL;  

          The above statement will give us all the employees who are having comm as not null value.

Order of Precedence of all the operators ::


     If we have all the above mentioned operators in single statement then order of execution of these operators will be as shown below.

    ---> Arithmetic Operators
    ---> Concatenation Operator
    ---> 1 to 6 comparison operators
    ---> IS (NOT) NULL, LIKE , (NOT) IN operators
    ---> (NOT) Between operator
    ---> NOT Logical operator
    ---> AND Logical Operator
    ---> OR Logical Operator

We can override precedence of any of these operators by enclosing  them in brackets.

SQL Statements Part3 (DRL,TCL and DCL)....

In this post I want to discuss about DRL, TCL and DCL statements.

DRL ::

   DRL means Data Retrieval Language. This will be used for the retrieval of the data from the database.

In order to see the data present in the database, we will use DRL statement. We have only one DRL

statement.

             SELECT is the only DRL statement in SQL.

SELECT ::

   Select statement is used to see the data present in the database. Syntax for writing this statement is,

    SELECT col_list FROM tab_name;

 If we want to see the data present in all the columns of the table,then we can use following syntax,

    SELECT * FROM tab_name;

 Ex ::

 To check data present in all the columns of Emp table,

 SELECT * FROM Emp;

To check data present in specific columns of the table, we will use first syntax.

 Ex ::

  SELECT empno,ename FROM Emp;

In this I am just checking data present in empno and ename columns.

TCL ::

    TCL means Transaction Control Language. These statements are used to control the transactions made by the DML statements to the database. Any change which is made to the database by a DML statement will be called as a transaction. We need to control these transactions made by DML statements.

    Whatever change made to the database by a DML statement will not reside permanently in the database, unless auto commit option enabled in the session. Those changes will reside in temporary memory. So, we need to handle these changes.

    TCL statements will be used for this purpose. We have 3 TCL statements available.

                      1 COMMIT
                      2 ROLLBACK
                      3 SAVEPOINT

 1. COMMIT ::

     Commit will be used to make the changes made by DML statements permanent to the database. After performing DML operations , if we issue commit then those changes will be made permanent to the database. If Auto commit option for session is enabled, then this is not required as commit takes place automatically.

Note : DDL statements will carry implicit commit. So, if we issue DDL statement against the database, after doing some DML operations , then due to implicit commit of DDL, previous DML statements also will be commited as commit of DDL applies for the session till that point.

 COMMIT;

2. ROLLBACK ::


     Rollback will be used to discard the changes made by DML statements to the database. If auto commit option enabled in the session , then there is no use with Rollback statements as transactions will be commited automatically.

    So, If we give Rollback then any pending changes to the database which are made by DML statements will be discarded.

  ROLLBACK;

3. SAVEPOINT ::

     To Rollback the changes to a certain transaction point, we will use SAVEPOINT. I will discuss about this in future posts while discussing PL/SQL.

DCL ::


    DCL means Data Control Language. These statements are used for controlling the access to database objects for users. Generally these statements will be used by DBA people to control the access of users to the database.

   We have 2 DCL statements available in SQL.

         1. GRANT
         2. REVOKE

1. GRANT ::


      Grant will be used for giving permissions on the database to users. Using this we can give any type of permissions to the users on the database.

 For ex if we want to give Select permission on all the database tables to a user,

  GRANT SELECT ANY TABLE TO user_name;

If we want to give select permission on specific table then

  GRANT SELECT ON tab_name TO user_name;

If we want to give Create table permission then,

   GRANT CREATE TABLE TO user_name;

Like this we can give any privilege to the users on the database.

2. REVOKE ::

     Revoke will be used to remove the permissions which are granted previously to the users on the database.

   IF we want to remove permissions given in the above statements, then we can use following statements.

    REVOKE SELECT ANY TABLE FROM user_name;

    REVOKE SELECT ON tab_name FROM user_name;

    REVOKE CREATE TABLE FROM user_name.

Like this using DCL statements we can control the access of users to the database.


Difference Between DELETE and TRUNCATE ::


---> Delete is DML where as Truncate is DDL.
---> Delete requires transaction control as it is DML. Truncate does not require transaction control as it is
        DDL
---> If we remove data using DML, then we can get back it by giving Rollback (if commit not done). But
        we cannot get the data removed by Truncate (auto commit).
---> Using Delete we can remove specific rows (using where clause) or total rows also. But using Truncate
        we can remove entire rows only. (we cannot use where clause with truncate).
---> Truncate is faster than Delete.