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

No comments:

Post a Comment