DRL

Agenda : SELECTING DATA

Arithmetic operators
Comparison operators
Logical operators

Oracle Introduction

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

WHERE

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

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

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

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

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
```
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
```
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
```
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
```

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

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
```
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
```

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

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:

This will give the rows whose marks are 100.
SQL> select * from student where marks like 100;

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

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

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

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

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

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

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

This will give the rows whose name contains 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 ( _ ).

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

BACK