Advanced selecting

To play around with select in Oracle, there is nothing better than using the preset hr schema. There is a number of way we can access it through our test user. We could connect to the database using the hr user, or we could change the current_schema used by the current session, using this command:

alter session set current_schema = hr;

Or maybe we could use the qualified name for the table we want to access:

select * from hr.countries;

The american countries in the the countries database have region_id 2, so to select all of them we write this query:

select * from countries where region_id = 2;

The star (*) means "all the columns".

The american countries in the the countries database have region_id 2, so to select all of them we write this query:

select * from countries where region_id = 2;

The star (*) means "all the columns".

If we are not interested in seeing all the columns we could specify just the ones we want to see:

select country_name from countries where region_id = 2;

We can have a better selection ANDing more conditions:

select * from employees where department_id = 90 and salary > 20000;

And what if we'd like to select all the employees having first name starting with S? We could write something like that:

select * from employees where first_name > 'S' and first_name < 'T';

Actually, there is another way to get the same result, using the "like" keyword:

select * from employees where first_name like 'S%';

The percent means any number of any character. If we want just one 'jolly' character we use the underscore:

select * from countries where country_id like 'C_';

Another way to select a range of values is achieved using the between keyword:

select * from employees where first_name between 'S' and 'T';

We can also ORring different clauses:

select * from employees where department_id = 40 or department_id = 50;

A different approach is required to find the rows having a specific field set to NULL. If we are looking for the boss, we can get it knowing he has no manager, meaning his manager_id is NULL:

select * from employees where manager_id is null;

If we know the values we are looking for, instead of ORring all the conditions referring to them we can use the IN clause:

select * from employees where first_name in ('Steven', 'John');

Or we can select all the rows that are not in a set of values:

select * from employees where department_id not in (40, 50);

Post written while reading Head First SQL