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 )