PL/SQL – mixed basic stuff

This is place where I put general or basic info about PL/SQL. There are short pieces of information on various topics.

SQL queries can be delimited in two ways:
1. using semicolon “;”
2. using new line and forward slash “/” after new line sign

Using double pipe “||” you can concatenate values:

select 'Code for ' || country_name || ' is: ' || country_id from hr.countries;

Writing select statement you can use aliasing. You can alias columns:
1. without quotes (only single word is allowed, it will be upper cased in result)
2. with double quotes (multiple words are allowed, case is preserved)

In oracle we can use two different not equal operators: “” and “!=”.

There are two wildchars in like statement (‘%’ – zero or many chars, ‘_’ – one char). When you need to search for ‘%’ or ‘_’ in a string you must set escape character:

select * from employees where code like '123\%XX%' escape '\'; -- will find codes like '123%XX' or '123%XXed' as it means that any character after \ is not a wildchar.

You can sort rows deciding whether nulls should be first or last in this order:

select * from employees order by commission_pct nulls first;
select * from employees order by commission_pct nulls last;

There is something like ‘positional sorting’ you can use number of column which should be used while sorting. Both queries return the same results:

select first_name, last_name from employees order by last_name;
select first_name, last_name from employees order by 2;

Assign ordering number to each row. It can be used to limit query to for example a few rows (when you need i.e. 5 top results).

select rownum, region_name from
(
select * from hr.regions
order by region_name
)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s