Conditional Selections, Operators

CONDITIONAL SELECTIONS AND OPERATORS

We have two clauses used in this

  1. Where
  2. 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
Ø =, !=, >, <, >=, <=, <>
1 between, not between
2 in, not in
null, not null
like
3 Logical Operators — lowest precedence
1 And
2 Or
3 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 200s
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

Related posts:

Leave a Reply