Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Tuesday, 11 April 2017

SQL Select Statement



It is used to retrieve the data for read only purpose

Syntax:

    Select * from <table_name>;              -- here * indicates all columns
     or
    Select col1, col2, … coln from <table_name>;

Examples:

    SQL> SELECT * FROM student;
  
        NO NAME            MARKS
        ---  ------             --------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

    SQL> SELECT no, name, marks FROM student;

        NO NAME            MARKS
        ---  ------             --------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

    SQL> SELECT no, name FROM student;

        NO NAME
        ---  -------
         1   Sudha
         2   Saketh
         1   Jagan
         2   Naren
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

We have two clauses used in this

  • Where
  • Order by


SELECT STATEMENT WITH WHERE CLAUSE


Syntax:
     select * from <table_name> where <condition>;

     The following are the different types of operators used in where clause.

Arithmetic operators
Relational operators
Logical operators
Special operators
+
=
And
In,Not In
-
! = or  <>
Or
Between, Not Between
*
> 
Not
Like, Not Like
/
< 

Is null,Is not null

>=



<=






SELECT STATEMENT WITH RELATIONAL OPERATORS

  
     Example:

        SQL> SELECTFROM student WHERE no = 2;


        NO NAME            MARKS
        ---  -------           ---------
         2   Saketh            200
         2   Naren             400
       
        SQL> SELECT * FROM student WHERE no < 2;

        NO NAME            MARKS
        ---  -------           ----------
         1   Sudha             100
         1   Jagan             300

        SQL> SELECT * FROM student WHERE no > 2;

         NO NAME            MARKS
         ---  -------           ----------
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

         SQL> SELECT * FROM student WHERE no <= 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400
     
         SQL> SELECT * FROM student WHERE no >= 2;

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         2   Naren             400
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

         SQL> SELECT * FROM student WHERE no != 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Sudha             100
         1   Jagan             300
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

         SQL> SELECT * FROM student WHERE no <> 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Sudha             100
         1   Jagan             300
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu


SELECT STATEMENT WITH AND

   
     This will gives the output when all the conditions become true.
   
     Syntax:
          select * from <table_name> where <condition1> and <condition2> and .. <conditionn>;
   
     Ex:

         SQL> SELECT * FROM student WHERE no = 2 AND marks >= 200;


                              NO NAME            MARKS
         ---  -------           --------
         2   Saketh            200
         2   Naren             400


SELECT STATEMENT WITH OR


     This will gives the output when either of the conditions become true.

     Syntax:
         select * from <table_name> where <condition1> and <condition2> or .. <conditionn>;

     Ex:
         SQL> SELECT * FROM student WHERE no = 2 OR marks >= 200;

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         1   Jagan             300
         2   Naren             400


SELECT STATEMENT WITH BETWEEN


     This will gives the output based on the column and its lower bound, upperbound.

     Syntax:
         select * from <table_name> where <col> between <lower bound> and <upper bound>;

     Ex:
         SQL> SELECT * FROM student WHERE marks BETWEEN 200 AND 400;

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         1   Jagan              300
         2   Naren              400

SELECT STATEMENT WITH NOT BETWEEN


     This will gives the output based on the column which values are not in its lower bound, upperbound.

     Syntax:
     select * from <table_name> where <col> not between <lower bound> and <upper bound>;

     Ex:
         SQL> SELECT * FROM student WHERE marks NOT BETWEEN 200 AND 400;

         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100

SELECT STATEMENT WITH IN



    This will gives the output based on the column and its list of values specified.

    Syntax:
         select * from <table_name> where <col> in ( value1, value2, value3 … valuen);

     Ex:
         SQL> SELECT * FROM student WHERE no IN (1, 2, 3);

         NO NAME            MARKS
         --- -------            ---------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400
         3   Ramesh


SELECT STATEMENT WITH NOT IN

     This will gives the output  based on the column which values are not in the list of values
     specified.

     Syntax:
         select * from <table_name> where <col> not in ( value1, value2, value3 … valuen);

     Ex:
         SQL> SELECT * FROM student WHERE no NOT IN (1, 2, 3);


         NO NAME            MARKS
         ---  -------           ---------
         4   Madhu
         5   Visu
         6   Rattu


SELECT STATEMENT WITH NULL

     This will gives the output based on the null values in the specified column.

     Syntax:
         select * from <table_name> where <col> is null;

     Ex:
         SQL> SELECT * FROM student WHERE marks IS NULL;

         NO NAME            MARKS
         ---  -------           ---------
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu


SELECT STATEMENT WITH NOT NULL


    This will gives the output based on the not null values in the specified column.

     Syntax:
         select * from <table_name> where <col> is not null;

     Ex:        
         SQL> SELECT * FROM student WHERE marks IS NOT NULL;
         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100
         2   Saketh            200
         1   Jagan             300
         2   Naren             400


SELECT STATEMENT WITH LIKE

    This will be used to search through the rows of database column based on the pattern you
     specify.

     Syntax:
        select * from <table_name> where <col> like <pattern>;
   
     Ex:        
        i) This will give the rows whose marks are 100.

            SQL> SELECT * FROM student WHERE marks LIKE 100;

         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100
      
        ii) This will give the rows whose name start with ‘S’.

             SQL> SELECT * FROM student WHERE name LIKE 'S%';

         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100
         2   Saketh            200

        iii) This will give the rows whose name ends with ‘h’.

              SQL> SELECT * FROM student WHERE name LIKE '%h';
       
         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         3   Ramesh

        iV) This will give the rows whose name’s second letter start with ‘a’.

               SQL> SELECT * FROM student WHERE name LIKE '_a%';

          NO NAME            MARKS
          ---  -------            --------
          2   Saketh            200
          1   Jagan             300
          2   Naren             400
          3   Ramesh
          4   Madhu
          6   Rattu

        V) This will give the rows whose name’s third letter start with ‘d’.

              SQL> SELECT * FROM student WHERE name LIKE '__d%';

         NO NAME            MARKS
         ---  -------           ---------
         1   Sudha             100
         4   Madhu

        Vi) This will give the rows whose name’s second letter start with ‘t’ from ending.

               SQL> SELECT * FROM student WHERE name LIKE '%_t%';

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         6   Rattu
       
        Vii) This will give the rows whose name’s third letter start with ‘e’ from ending.

                SQL> SELECT * FROM student WHERE name LIKE '%e__%';

         NO NAME            MARKS
         ---  -------           ---------
         2   Saketh            200
         3   Ramesh

        Viii) This will give the rows whose name  cotains 2 a’s.

                    SQL> SELECT * FROM student WHERE name LIKE '%a% a %';

         NO NAME            MARKS
          --- -------           ----------
         1   Jagan             300

* You have to specify the patterns in like using underscore ( _ ).


SELECT STATEMENT WITH ORDER BY


This will be used to ordering the columns data (ascending or descending).

Syntax:
        Select * from <table_name> order by <col> desc;

By default oracle will use ascending order.
If you want output in descending order you have to use desc keyword after the column.

Ex:
        SQL> SELECT * FROM student ORDER BY no

        NO NAME            MARKS
        ---  -------           ---------
         1   Sudha             100
         1   Jagan              300
         2   Saketh            200
         2   Naren             400
         3   Ramesh
         4   Madhu
         5   Visu
         6   Rattu

        SQL> SELECT * FROM student ORDER BY no DESC;

        NO NAME            MARKS
        ---  -------           ---------
         6 Rattu
         5 Visu
         4 Madhu
         3 Ramesh
         2 Saketh            200
         2 Naren             400
         1 Sudha             100
         1 Jagan             300
Share this article :

2 comments:

  1. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete
  2. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

    ReplyDelete