Oracle sql tutorial :Restricting the data set



So far in earlier oracle sql tutorial, we have learned about select from the table where all the rows are being returned.

Now in this Oracle sql tutorial we learn about restricting the date set (number of rows selected) from the table.
This is achieved through where clause in the sql statement

Basic sql statment with where clause is of the form

select [Distinct] { *,col alias…..} from table [where conditions ] ;

We have already explained the other parts in the earlier chapter ,the things which need to be explained is the where conditions

1) Where signifies to restrict the number of rows returned from the table based on the conditions given
2) Conditions can be column names, comparison operator, constant
3) column names can be compared to literal,column name using comparison operator

Lets us understand by some examples

select ename ,salary from dept where salary =5000

Here in this example
Column salary is compared to constant 5000 with the comparison operator =

select ename ,salary from dept where salary > 2000
Here in this example
Column salary is compared to constant 2000 with the comparison operator >

select ename ,salary from dept where salary = salary1
Here in this example
Column salary is compared to column name salary1 with the comparison operator =

So in general we can say about this where clause
It consists of three parts
1) Column name
2) Comparison operator
3) column name ,constant or list of values

More things on where clause conditions

Character and dates:We used single quotes for specifying

character and dates.

It is case sensitive.

The default display for date is DD-MON-YY

select ename ,salary from dept where ename=’NATHAN’
select ename ,salary from dept where ename=’john’
select ename ,salary from dept where hire_date=’01-OCT-14′;
Common Comparison Operator: They are >(greater than) ,

<(less than),

<=(less than or equal to >,>=(not equal to)

select ename ,salary from dept where salary =5000;
Between .. and ( Between the value) select ename ,salary from dept where salary between 2000 and 3000;
IN Operator: This is use to find the value of column

in a list of values

select ename ,salary from dept where ename in (‘JOHN’,’NATHAN’);
Like Operator: This is use to find the value of column

like a particular value.

% is used specially in that ,it denotes zero to any

character.

We can do wildcard searches of any string using like

select ename ,salary from dept where ename like ‘%ATH%’;
Is NULL Operator: Null means no values or unassigned values. We cannot test that with = operator. select ename ,salary from dept where surname is null ;

Leave a Reply