Saturday, October 18, 2014

SQL Conditional Operators



                                                     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.

v  Arithmetic operators         
v  Comparison operators
v  Logical operators

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

a) USING =, >, <, >=, <=, !=, <>
   
     Ex:
        SQL> select * from student where no = 2;


        NO NAME            MARKS
        ---  -------           ---------
         2   Prasad            200
         2   Lucky             400
        
        SQL> select * from student where no < 2;

        NO NAME            MARKS
        ---  -------           ----------
         1   Rama             100
         1   RESHWANTH             300

        SQL> select * from student where no > 2;

         NO NAME            MARKS
         ---  -------           ----------
         3   Ramesh
         4   Madhu
         5   Mahesh
         6   Ravi

         SQL> select * from student where no <= 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Rama             100
         2   Prasad            200
         1   RESHWANTH             300
         2   Lucky             400
      
         SQL> select * from student where no >= 2;

         NO NAME            MARKS
         ---  -------           ---------
         2   Prasad            200
         2   Lucky             400
         3   Ramesh
         4   Madhu
         5   Mahesh
         6   Ravi

         SQL> select * from student where no != 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Rama             100
         1   RESHWANTH             300
         3   Ramesh
         4   Madhu
         5   Mahesh
         6   Ravi

         SQL> select * from student where no <> 2;

         NO NAME            MARKS
         ---  -------           ----------
         1   Rama             100
         1   RESHWANTH             300
         3   Ramesh
         4   Madhu
         5   Mahesh
         6   Ravi

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   Prasad            200
         2   Lucky             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   Prasad            200
         1   RESHWANTH             300
         2   Lucky             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   Prasad            200
         1   RESHWANTH              300
         2   Lucky              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   Rama             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   Rama             100
         2   Prasad            200
         1   RESHWANTH             300
         2   Lucky             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   Mahesh
         6   Ravi

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   Mahesh
         6   Ravi

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   Rama             100
         2   Prasad            200
         1   RESHWANTH             300
         2   Lucky             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   Rama             100
       
        ii) This will give the rows whose name start with ‘S’.

             SQL> select * from student where name like 'S%';

         NO NAME            MARKS
         ---  -------           ---------
         1   Rama             100
         2   Prasad            200

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

              SQL> select * from student where name like '%h';
         
         NO NAME            MARKS
         ---  -------           ---------
         2   Prasad            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   Prasad            200
          1   RESHWANTH             300
          2   Lucky             400
          3   Ramesh
          4   Madhu
          6   Ravi
 
        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   Rama             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   Prasad            200
         6   Ravi
        
        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   Prasad            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   RESHWANTH             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   Rama             100
         1   RESHWANTH              300
         2   Prasad            200
         2   Lucky             400
         3   Ramesh
         4   Madhu
         5   Mahesh
         6   Ravi

        SQL> select * from student order by no desc;

        NO NAME            MARKS
        ---  -------           ---------
         6 Ravi
         5 Mahesh
         4 Madhu
         3 Ramesh
         2 Prasad            200
         2 Lucky             400
         1 Rama             100
         1 RESHWANTH             300

 

SQL Introductioin


                      INTRODUCTION


SQL is divided into the following


Ø  Data Definition Language (DDL)
Ø  Data Manipulation Language (DML)
Ø  Data Retrieval Language (DRL)
Ø  Transaction Control Language (TCL)
Ø  Data Control Language (DCL)

DDL -- create, alter, drop, truncate, rename
DML -- insert, update, delete
DRL -- select
TCL -- commit, rollback, savepoint
DCL -- grant, revoke

CREATE TABLE SYNTAX

Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);
Ex:
     SQL> create table student (no number (2), name varchar (10), marks number (3));

INSERT

This will be used to insert the records into table.
We have two methods to insert.
Ø  By value method
Ø  By address method

a) USING VALUE METHOD
    
     Syntax:
          insert into <table_name) values (value1, value2, value3 …. Valuen);

    
     Ex:
            SQL> insert into student values (1, ’Rama’, 100);
            SQL> insert into student values (2, ’Prasad’, 200);
           
     To insert a new record again you have to type entire insert command, if there are lot of  
     records this will be difficult.
     This will be avoided by using address method.

b) USING ADDRESS METHOD
    
      Syntax:
          insert into <table_name) values (&col1, &col2, &col3 …. &coln);
     This will prompt you for the values but for every insert you have to use forward slash.
     
      Ex:
            SQL> insert into student values (&no, '&name', &marks);

Enter value for no: 1
Enter value for name: RESHWANTH
Enter value for marks: 300
old   1: insert into student values(&no, '&name', &marks)
new   1: insert into student values(1, 'RESHWANTH', 300)

SQL> /
Enter value for no: 2
Enter value for name: Lucky
Enter value for marks: 400
old   1: insert into student values(&no, '&name', &marks)
new   1: insert into student values(2, 'Lucky', 400)

c) INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD
    
     Syntax:
           insert into <table_name)(col1, col2, col3 … Coln) values (value1, value2, value3 ….
                                                              Valuen);
     Ex:
            SQL> insert into student (no, name) values (3, ’Ramesh’);
            SQL> insert into student (no, name) values (4, ’Madhu’);

d) INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD
    
     Syntax:
          insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2, &col3 …. &coln);
     This will prompt you for the values but for every insert you have to use forward slash.
     
     Ex:
            SQL> insert into student (no, name) values (&no, '&name');
Enter value for no: 5
Enter value for name: Mahesh
old   1:  insert into student (no, name) values(&no, '&name')
new   1:  insert into student (no, name) values(5, 'Mahesh')

SQL> /
Enter value for no: 6
Enter value for name: Ravi
old   1:  insert into student (no, name) values(&no, '&name')
new   1:  insert into student (no, name) values(6, 'Ravi')

SELECTING DATA

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   Rama             100
         2   Prasad            200
         1   RESHWANTH             300
         2   Lucky             400
         3   Ramesh
         4   Madhu
         5   Mahesh
         6   Ravi

    SQL> select no, name, marks from student;

        NO NAME            MARKS
        ---  ------             --------
         1   Rama             100
         2   Prasad            200
         1   RESHWANTH             300
         2   Lucky             400
         3   Ramesh
         4   Madhu
         5   Mahesh
         6   Ravi

    SQL> select no, name from student;

        NO NAME
        ---  -------
         1   Rama
         2   Prasad
         1   RESHWANTH
         2   Lucky
         3   Ramesh
         4   Madhu
         5   Mahesh
         6   Ravi