Conditional Selections and Operations:
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
a. =, !=, >, <, >=, <=, <>
b. between, not between
c. in, not in
d. null, not null
e. like
v Logical operators
a. And
b. Or -- Lowest precedence
c. not
USING COMPARISION OPERATORS: With WHERE Clause
a) =, !=, >, <, >=, <=, <>
EX: SELECT *FROM STUDENT WHERE NO=5;
Output: only the number equal to 5 will give as result
|
NO
|
NAME
|
MARKS
|
|
5
|
MOHAN
|
500
|
EX: SELECT *FROM STUDENT WHERE NO>5;
Output: The numbers which are greater than 5 will give in the result.
|
NO
|
NAME
|
MARKS
|
|
6
|
Manvi
|
600
|
|
7
|
Namu
|
500
|
|
8
|
Susu
|
300
|
|
9
|
Thanu
|
200
|
|
10
|
AJ
|
100
|
|
11
|
MK
|
(null)
|
|
12
|
MD
|
(null)
|
EX: SELECT *FROM STUDENT WHERE NO<5;
Output: The numbers which are less than 5 will give in the result.
|
NO
|
NAME
|
MARKS
|
|
1
|
Visu
|
100
|
|
2
|
Vasu
|
200
|
|
3
|
Manoj
|
300
|
|
4
|
Aisu
|
400
|
EX: SELECT *FROM STUDENT WHERE NO >=5;
Output: The numbers which are greater than equal to 5 will give in the result.
|
NO
|
NAME
|
MARKS
|
|
5
|
Mohan
|
500
|
|
6
|
Manvi
|
600
|
|
7
|
Namu
|
500
|
|
8
|
Susu
|
300
|
|
9
|
Thanu
|
200
|
|
10
|
AJ
|
100
|
|
11
|
MK
|
(null)
|
|
12
|
MD
|
(null)
|
EX: SELECT *FROM STUDENT WHERE NO >=5;
Output: The numbers which are less than equal to 5 will give in the result.
|
NO
|
NAME
|
MARKS
|
|
1
|
Visu
|
100
|
|
2
|
Vasu
|
200
|
|
3
|
Manoj
|
300
|
|
4
|
Aisu
|
400
|
|
5
|
Mohan
|
500
|
EX: SELECT *FROM STUDENT WHERE NO <>5;
Output: with this statement except no 5, remaining all the rows will be displayed in the result.
|
NO
|
NAME
|
MARKS
|
|
1
|
Visu
|
100
|
|
2
|
Vasu
|
200
|
|
3
|
Manoj
|
300
|
|
4
|
Aisu
|
400
|
|
6
|
Manvi
|
600
|
|
7
|
Namu
|
500
|
|
8
|
Susu
|
300
|
|
9
|
Thanu
|
200
|
|
10
|
AJ
|
100
|
|
11
|
MK
|
(null)
|
|
12
|
MD
|
(null)
|
b) Between, Not Between
Using Between:
SYNTAX: select * from <table_name> where <col> between <lower bound> and <upper bound>;
Ex: select * from student where marks between 200 and 400;
Output: With the above statement we are going to see the result of marks between 200 and 400.
|
NO
|
NAME
|
MARKS
|
|
2
|
VASU
|
200
|
|
3
|
MANOJ
|
300
|
|
4
|
AISU
|
400
|
|
8
|
SUSU
|
300
|
|
9
|
THANU
|
200
|
Using Not Between:
SYNTAX: select * from <table_name> where <col> not between <lower bound> and <upper bound>;
Ex: select * from student where marks not between 200 and 400;
Output: With the above statement, we are going to see result of marks between 200 and 400.
|
NO
|
NAME
|
MARKS
|
|
1
|
VISU
|
100
|
|
5
|
MOHAN
|
500
|
|
6
|
MANVI
|
600
|
|
7
|
NAMU
|
500
|
|
10
|
AJ
|
100
|
c) in, not in:
Using IN: This will give 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: select * from student where no in (1, 2, 3);
Output:
|
NO
|
NAME
|
MRKS
|
|
1
|
VISU
|
100
|
|
2
|
VASU
|
200
|
|
3
|
MANOJ
|
300
|
Using Not In: This will give 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: select * from student where no not in (1, 2, 3,7,8,9,10);
Output:
|
NO
|
NAME
|
MARKS
|
|
4
|
Aisu
|
400
|
|
5
|
Mohan
|
500
|
|
6
|
Manvi
|
600
|
|
11
|
MK
|
(null)
|
|
12
|
MD
|
(null)
|
d) null, not null:
Using Null: This will give the output based on the null values in the specified column.
Syntax: select * from <table_name> where <col> is null;
Ex: select * from student where marks is null;
Output:
|
NO
|
NAME
|
MARKS
|
|
11
|
MK
|
(null)
|
|
12
|
MD
|
(null)
|
Using Not Null: This will give the output based on the not null values in the specified column.
Syntax: select * from <table_name> where <col> is not null;
Ex: select * from student where marks is not null;
Output:
|
NO
|
NAME
|
MARKS
|
|
1
|
Visu
|
100
|
|
2
|
Vasu
|
200
|
|
3
|
Manoj
|
300
|
|
4
|
Aisu
|
400
|
|
5
|
Mohan
|
500
|
|
6
|
Manvi
|
600
|
|
7
|
Namu
|
500
|
|
8
|
Susu
|
300
|
|
9
|
Thanu
|
200
|
|
10
|
AJ
|
100
|
e) Like:
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>;
1. Ex: select * from student where marks like 100;
Output: This will give the rows whose marks are 100.
|
NO
|
NAME
|
MARKS
|
|
1
|
VISU
|
100
|
|
10
|
AJ
|
100
|
2. EX: select * from student where name like 'V%';
Output: This will give the rows whose name start with ‘V’.
|
NO
|
NAME
|
MARKS
|
|
1
|
VISU
|
100
|
|
2
|
VASU
|
200
|
3. EX: select * from student where name like '%J';
Output: This will give the rows whose name ends with ‘J’.
|
NO
|
NAME
|
MARKS
|
|
3
|
MANOJ
|
300
|
|
10
|
AJ
|
100
|
4. EX: select * from student where name like '_A%';
Output: This will give the rows whose name’s the second letter start with ‘A’.
|
NO
|
NAME
|
MARKS
|
|
2
|
VASU
|
200
|
|
3
|
MANOJ
|
300
|
|
6
|
MANVI
|
600
|
|
7
|
NAMU
|
500
|
5. EX: select * from student where name like '__N%';
Output: This will give the rows whose name’s the third letter start with ‘N’.
|
NO
|
NAME
|
MARKS
|
|
3
|
MANOJ
|
300
|
|
6
|
MANVI
|
600
|
6. EX: select * from student where name like '%_S%';
Output: This will give the rows whose name’s the second letter start with ‘S’ from ending.
|
NO
|
NAME
|
MARKS
|
|
1
|
VISU
|
100
|
|
2
|
VASU
|
200
|
|
4
|
AISU
|
400
|
|
8
|
SUSU
|
300
|
7. EX: select * from student where name like '%I__%';
Output: This will give the rows whose name’s the third letter start with ‘I’ from ending.
|
NO
|
NAME
|
MARKS
|
|
1
|
VISU
|
100
|
|
4
|
AISU
|
400
|
8. EX: select * from student where name like '%S% S %';
Output: This will give the rows whose name contains 2 S’s.
|
NO
|
NAME
|
MARKS
|
|
8
|
SUSU
|
300
|
LOGICAL OPERATORS:
a) Using And:
Syntax: select * from <table_name> where <condition1> and <condition2> and ..<conditionn>;
Ex: select * from student where no = 2 and marks = 200;
Output: This will give the output when all the conditions become true.
|
NO
|
NAME
|
MARKS
|
|
2
|
VASU
|
200
|
b) Using Or: This will give the output when either of the conditions become true.
Syntax: select * from <table_name> where <condition1> and <condition2> or .. <conditionn>;
Ex: select * from student where no = 2 or marks >= 200;
Output:
|
NO
|
NAME
|
MARKS
|
|
2
|
Vasu
|
200
|
|
3
|
Manoj
|
300
|
|
4
|
Aisu
|
400
|
|
5
|
Mohan
|
500
|
|
6
|
Manvi
|
600
|
|
7
|
Namu
|
500
|
|
8
|
Susu
|
300
|
|
9
|
Thanu
|
200
|
USING ORDER BY: This will be used to ordering the column's data (ascending or descending).
Syntax: Select * from <table_name> order by <col> desc;
By default, oracle will use ascending order, If you want the output in descending order you have to use desc keyword after the column.
Ex: select * from student order by no;
Output:
|
NO
|
NAME
|
MARKS
|
|
1
|
Visu
|
100
|
|
2
|
Vasu
|
200
|
|
3
|
Manoj
|
300
|
|
4
|
Aisu
|
400
|
|
5
|
Mohan
|
500
|
|
6
|
Manvi
|
600
|
|
7
|
Namu
|
500
|
|
8
|
Susu
|
300
|
|
9
|
Thanu
|
200
|
|
10
|
AJ
|
100
|
|
11
|
MK
|
(null)
|
|
12
|
MD
|
(null)
|
EX: select * from student order by no desc;
|
NO
|
NAME
|
MARKS
|
|
12
|
MD
|
(null)
|
|
11
|
MK
|
(null)
|
|
10
|
AJ
|
100
|
|
9
|
Thanu
|
200
|
|
8
|
Susu
|
300
|
|
7
|
Namu
|
500
|
|
6
|
Manvi
|
600
|
|
5
|
Mohan
|
500
|
|
4
|
Aisu
|
400
|
|
3
|
Manoj
|
300
|
|
2
|
Vasu
|
200
|
|
1
|
Visu
|
100
|
No comments:
Post a Comment