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

Pages

Monday, 28 March 2016

SQL DRL Operations

USING SELECT

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

Ex:
    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

 CONDITIONAL SELECTIONS AND OPERATORS 

We have two clauses used in this

  • Where
  • Order by

USING WHERE


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

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

Arithmetic operators        
Comparison operators
Logical operators

Arithmetic operators          -- highest precedence
+, -, *, /
Comparison operators
 =, !=, >, <, >=, <=, <>
between, not between
in, not in
 null, not null
like ,not like
Logical operators
And
Or                                            -- lowest precedence
not

a) USING =, >, <, >=, <=, !=, <>
  
     Ex:
        SQL> select * from 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

b) USING 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

c) USING 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

d) USING 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

e) USING 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

f) USING 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

g) USING 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

h) USING 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

i) USING 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

j) USING 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 ( _ ).

USING 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 :

0 comments:

Post a Comment