SYNONYMS

Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.

You can refer to synonyms in the following DML statements: SELECT, INSERT, UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE.

You can refer to synonyms in the following DDL statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT.

To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege.

To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM system privilege.

To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

You can read the whole article at: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7001.htm

USING VIEWS

Before we are able to create views, we should know what view actually means. It is logical structure based on one or more tables, or another view.

We are treating views as any other table in the database. We can query them, perform insert, update and delete. Actually when we perform DMLs on a view, the operation will consider the table on which base the view is created.

There are many reason why we should use views. One of them is security. What does it means? It means that views give us opportunity to hide some critical information stored in the table.

Example:

Let take table employees from HR schema. It contains information about all employees in the company, names, emails, phones, salary…. Now we need to give access to the people from payroll department just to columns department_id, first_name, last_name, salary. To do this we have to create a view based on table employees.



Screen clipping taken: 4.2.2011 г.; 13:15 ч.

Now let take we should give the shipping department manager access to the information about all employees in his own department and hide from him the information about the rest of the employees in the company. To do this we are going to create a view called shipping_staff. This view will be based on 2 tables: employees and departments



Other reasons for creating and using views can be:

1. Better performance
2. Simplify SQL
3. Preventing errors...

   In our first example the view is based on just one table and it doesn't use any functions. It make it simple view.

   In our second example the view is based on two tables which make it automatically complex view

SIMPLE VIEW	COMPLEX VIEW
Select from one table	Select from one or more tables
Does not contain functions	Contains functions
Can perform DML trough simple views	Can't always perform DML trough complex views

REMOVING ROWS FROM A TABLE

We can remove rows from a table using different ways. First of them is using DELETE command. It will remove row from a table. Using WHERE clause we specify which rows to be deleted. If we omit WHERE clause all rows from the table will be deleted.

It is important to remember that DELETE command is a DML command and it can be rolled back. It means that if we want to remove rows permanently we need to COMMIT.

Examples:



The DELETE command is not COMMITED yet, so we are able to undo it:



If we want to remove rows permanently, we should COMMIT



We can see that after DELETE command is committed, rollback won't be able to undo the changes.

TRUNCATE TABLE

This command will remove ALL ROWS from a table. It is important to remember that it can not be rolled back.

Example:



If we want to remove whole table from a database ( rows, privileges, indexes), we need to use DROP command.



Rollback will again not undo changes made with DROP command.



Screen clipping taken: 4.2.2011 г.; 11:27 ч.

There is way in Oracle 10g to undo "dropping table" using FLASHBACK.

DELETE

Use the DELETE statement to remove rows from:

• An unpartitioned or partitioned table

• The unpartitioned or partitioned base table of a view

• The unpartitioned or partitioned container table of a writable materialized view

• The unpartitioned or partitioned master table of an updatable materialized view

For you to delete rows from a table, the table must be in your own schema or you must have the DELETE object privilege on the table.

For you to delete rows from an updatable materialized view, the materialized view must be in your own schema or you must have the DELETE object privilege on the materialized view.

For you to delete rows from the base table of a view, the owner of the schema containing the view must have the DELETE object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the DELETE object privilege on the view.

You can read the whole article here.

 

 

UPDATE TABLE

Use the UPDATE statement to change existing values in a table or in the base table of a view or the master table of a materialized view.

For you to update values in a table, the table must be in your own schema or you must have the UPDATE object privilege on the table.

For you to update values in the base table of a view:

• You must have the UPDATE object privilege on the view, and

• Whoever owns the schema containing the view must have the UPDATE object privilege on the base table.

The UPDATE ANY TABLE system privilege also allows you to update values in any table or in the base table of any view.

You can find the whole article at: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#SQLRF01708

SET OPERATORS

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character).

If component queries select character data, then the datatype of the return values are determined as follows:

• If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.

• If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.

If component queries select numeric data, then the datatype of the return values is determined by numeric precedence:

• If any query selects values of type BINARY_DOUBLE, then the returned values have datatype BINARY_DOUBLE.

• If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT.

• If all queries select values of type NUMBER, then the returned values have datatype NUMBER.

In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.

Examples The following query is valid:

SELECT 3 FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;




This is implicitly converted to the following compound query:

SELECT TO_BINARY_FLOAT(3) FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;




The following query returns an error:

SELECT '3' FROM DUAL
   INTERSECT
SELECT 3f FROM DUAL;




Restrictions on the Set Operators The set operators are subject to the following restrictions:




• 
  

  The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.

  
  
• 
  

  The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.

  
  
• 
  

  If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause.

  
  
• 
  

  You cannot also specify the for_update_clause with the set operators.

  
  
• 
  

  You cannot specify the order_by_clause in the subquery of these operators.

  
  
• 
  

  You cannot use these operators in SELECT statements containing TABLE collection expressions.




Note:


To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.


UNION Example The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:

SELECT location_id, department_name "Department", 
   TO_CHAR(NULL) "Warehouse"  FROM departments
   UNION
   SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name 
   FROM warehouses;

LOCATION_ID Department            Warehouse
----------- --------------------- --------------------------
       1400 IT
       1400                       Southlake, Texas
       1500 Shipping
       1500                       San Francisco
       1600                       New Jersey
       1700 Accounting
       1700 Administration
       1700 Benefits
       1700 Construction
...




UNION ALL Example The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:

SELECT product_id FROM order_items
UNION
SELECT product_id FROM inventories;

SELECT location_id  FROM locations 
UNION ALL 
SELECT location_id  FROM departments;




A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the UNION ALL operator.


INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT product_id FROM inventories
INTERSECT
SELECT product_id FROM order_items;




MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:

SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items;

ARTICLE IS TAKEN FROM :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm#i2054381

CROSS JOINS

Cross joins produce all possible combinations of rows from tables which are joining. If we have again table A and table B, and each table has 3 rows, cross joins will produce 3x3=9 rows.

Examples:

Pure natural join can produce also an cartesian product when the joining tables do not have identical named columns.

Example :

OUTER JOINS

LEFT OUTER JOINS

Let take we have two tables A and B. The left outer joins will return all the rows from the left table A in conjunction with the matching rows from the right table B. In case there are no columns matching in table B, it returns NULL values.

Example:





There will be 122 rows returned (there are some departments without employees to be assigned to so there are entries with NULL values in columns FIRST_NAME, LAST_NAME)

If we just change left and right table the result will be different:



There are 107 rows returned (number of rows present in table EMPLOYEES)



There are 107 rows returned (number of rows present in table EMPLOYEES)

RIGHT OUTER JOINS

I am taking again two tables A and B. The right outer joins will return all the rows from the left table B in conjunction with the matching rows from the right table A. In case there are no columns matching in table A, it returns NULL values. Example:





There will be 122 rows returned

If we change the table places will get different result



`There will be 107 rows returned (number of rows in EMPLOYEES table)

FULL OUTER JOINS

It combines both left outer and the right outer joins together.





There will be 123 rows returned (there are some departments without employees to be assigned to so there are entries with NULL values in columns LAST_NAME, SALARY )

Using Self-Joins to Access Related Records in the Same Table

You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table. The most common case where you'd use a self-join is when you have a table that references itself, such as the employees table shown below:

id first_name last_name manager
----------- --------------- --------------- -----------
1 Pat Crystal NULL
2 Dennis Miller 1
3 Jacob Smith 1

Suppose you're tasked with writing a SQL query to retrieve a list of employees and their managers. You can't write a basic SQL SELECT statement to retrieve this information, as you need to cross reference information contained in other records within the same table. Fortunately, you can use a self-join to solve this dilemma by joining the table to itself.

You can find the whole article at http://databases.about.com/od/sql/a/selfjoins.htm

NATURAL JOIN

NATURAL JOIN CLAUSE

To be able to join two tables using NATURAL JOIN they must have a common column. It compares those common columns with each other and returns the results. It automatically detects the join keys, based on the name of the matching column in both tables. For example in HR schema we can join EMPLOYEES with JOB_HISTORY table using the common columns EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID





The above statement returns only one row as result, because there is just one entry existing in both tables in same time.

Let take now ORDER and CUSTOMERS tables from OE schema. There is only one common column in both tables: customer_id





There is again just one entry presents in both tables in same time.

NATURAL JOIN USING CLAUSE

It allows to join tables on one or more common columns (not on ALL common columns). When we use JOIN USING clause we can specify the join_coulmn(s). In other words we use it when two tables have more than one column with same name but don't to use all of them. It has following syntax:

SELECT table1_column, table2_column…

FROM table1

JOIN table2 USING(join_column1, join_column2…);

Examples:



NATURAL JOIN ON CLAUSE

In some cases we need to join tables without using common column_name. This can be done with JOIN … ON clause.

Example:

It is important to remember that we NEVER can use all three keywords (NATURAL, USING and ON) in one JOIN clause.

HAVING CLAUSE

Having clause is used to restrict group-level rows. It can not be used without GROUP BY clause to be used.

Example:



We can use WHERE and HAVING clause in the same statement in case we want to do double restriction with the following logic:

1. WHERE clause will apply some restrict condition for each row in the table
2. GROUP BY clause will create groups only from the rows satisfying the where condition
3. HAVING clause will apply restrict condition on the groups produced by GROUP BY clause.

For example:





It is important to remember that HAVING clause must contain a group function or expression that uses such, otherwise we will get an error, for example:

GROUP BY

In my previous post i talked about group functions which are operating on groups of rows (as group of rows can be considered the whole table). In this post I am going to concentrate on the possibility of creating groups and using them in the SELECT statements.

To create group of rows we need first some common attribute. For example:

Considering orders, we can create groups using as attribute order_date, customer_id ect.

After we have chosen the common attribute we should put it into the select statement. It is done using the GROUP BY Clause. The GROUP BY Clause appears in the SELECT Statement after the WHERE clause.

Examples:

It is important that all columns listed in the SELECT clause that are not in a function must be included in the GROUP BY clause, otherwise we will get an ORA-00979: not a GROUP BY expression

Example:

The proper statement will be:

Now, let see how we can produce a report about the total amount of orders made by each customer:

GROUP FUNCTION

Group functions operate on group of rows and return one value for the ENTIRE GROUP. As a group is consdered the entire table or just some portions of the table grouped by some common atribute.

Group functions are:

COUNT, MAX, MIN, AVG, SUM, DISTINCT

COUNT - It returns the number of rows in the query. For example: we want to know number of employees wich have salary = 10000



What will happen if in the group are rows with NULL values? They will not be considered!!!

For example: We have in the entire table 107 rows, but only 35 from them are with non-null values in collumn commission_pct





DISTINCT - we can use it to retrieve only unique data entries.

Example:



There will be selected just 57 rows. (all duplicate values will be ignored)

AVG - it returns the average value of the column or expression. For example:





As we can see from the examples, the general syntax from the AVG functions is:

AVG([DISTINCT] EXPRESSION)

MAX Function - it will return the highest number (when operation on numeric values), latest date (when operating on date) and the world that appears at last alphabetically ( when operating on char data.

Examples:





MIN Function - it is exactly the opposite of the MAX function. For example:





SUM Function -it returns the sum of values of expression. For more information, pls check ORACLE DOCUMENTATION Examples:

More functions are available under http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions163.htm

Apply conditional expressions in a SELECT statement

NVL Function - If we want to test two or more  fields and get the value of the first non-null field, we have to use NVL function.  It has following syntax:
NVL( a, b) . It can be translated like that: If 'a' is not null will be returned 'a', else will be returned 'b':

Examples:
SQL> select nvl(commission_pct, 0)from employees;
NVL(COMMISSION_PCT,0)
---------------------
                    0
                    0
                    0
                   .4
                   .3
                   .3
 ...




NVL2 Function - it extends the functionallity from NVL function. Its syntax is NVL2(a,b,c) ---> if 'a' is not null then return 'b' else return 'c'.

SQL> select last_name, nvl2(commission_pct, 'has commission', 'has
n') from employees;
LAST_NAME                 NVL2(COMMISSION_PC
------------------------- ------------------
Matos                     has not commission
Vargas                    has not commission
Russell                   has commission
Partners                  has commission
Errazuriz                 has commission
Cambrault                 has commission
Zlotkey                   has commission
Sullivan                  has not commission
Geoni                     has not commission
Sarchand                  has not commission
Bull                      has not commission
Dellinger                 has not commission
Cabrio                    has not commission
.....

NULLIF - if we need to test two items for equility, we have to use NULLIF function. It has following syntax:
NULLIF(expression1, expression2).
It will return expression1 if two expressions are not equal. If they are equal, NULLIF will return NULL value.
Examples:

SQL> select nullif (mruhtel, mruhtel) from dual;
select nullif (mruhtel, mruhtel) from dual
                        *
ERROR at line 1:
ORA-00904: "MRUHTEL": invalid identifier

SQL> select nullif ('mruhtel', 'mruhtel') from dual;
NULLIF(
-------

SQL> select nullif ('mruhtel', 'mruhtels') from dual;
NULLIF(
-------
mruhtel
SQL> select nullif ('mruhtel', 'Mruhtel') from dual;   ----> it is case sensitive
NULLIF(
-------
mruhtel

COALESCE Function - It evaluates a list of expressions against NULL and it returns first non-null expression from the list. For example:
SQL> select product_id, list_price, min_price,
  2  coalesce(list_price, min_price, 0) "Price"
  3  from product_information
  4  where rownum <11;

PRODUCT_ID LIST_PRICE  MIN_PRICE      Price
---------- ---------- ---------- ----------
      1797        349        288        349
      2459        699        568        699
      3127        498        444        498
      2254        453        371        453
      3353        489        413        489
      3069        436        350        436
      2253        399        322        399
      3354        543        478        543
      3072        567        507        567
      3334        612        512        612
10 rows selected.
SQL> select product_id, list_price, min_price,
  2  coalesce(list_price, min_price, 'no_price') "Price"
  3  from product_information;
coalesce(list_price, min_price, 'no_price') "Price"
                                *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
To avoid that error, all not null parameters MUST have data type comptatible with the first not null parameter. In that case must be NUMBER.

If we compare NVL function and COALESCE function, we will see that they can be used equally. For example:
coalesce(a, b) = nvl(a, b)
SQL> select product_id, list_price, min_price,
  2  coalesce(list_price, min_price) "Price"
  3  from product_information
  4  where rownum <11;
PRODUCT_ID LIST_PRICE  MIN_PRICE      Price
---------- ---------- ---------- ----------
      1797        349        288        349
      2459        699        568        699
      3127        498        444        498
      2254        453        371        453
      3353        489        413        489
      3069        436        350        436
      2253        399        322        399
      3354        543        478        543
      3072        567        507        567
      3334        612        512        612
10 rows selected.
SQL> select product_id, list_price, min_price,
  2  nvl(list_price, min_price) "Price"
  3  from product_information
  4  where rownum <11;
PRODUCT_ID LIST_PRICE  MIN_PRICE      Price
---------- ---------- ---------- ----------
      1797        349        288        349
      2459        699        568        699
      3127        498        444        498
      2254        453        371        453
      3353        489        413        489
      3069        436        350        436
      2253        399        322        399
      3354        543        478        543
      3072        567        507        567
      3334        612        512        612
10 rows selected.
CASE Function - it let us to evaluate first two items for equility and returns the third one if they are equal and onother one if they are not equal. It implaments the IF-THEN-ELSE logic without involking procedures.
It has two formats: simple case expression and searched case expression.

Simple case expression -
case expression
 when comparrison_expression
 then result
else return_expression
end

SIMPLE CASE EXPRESSION IS ENCLOSED WITHIN CASE.... END BLOCK!!!
If expression =  comparisson_expression, it will return result
If expression != comparisson_expression, it will return return_expression

Example:
SQL> select last_name, salary,
  2  case salary when 2100 then 'low'
  3  when 21000 then 'high'
  4  else 'medium' end
  5  from employees;

LAST_NAME                     SALARY CASESA
------------------------- ---------- ------
Bissot                          3300 medium
Atkinson                        2800 medium
Marlow                          2500 medium
Olson                           2100 low
Mallin                          3300 medium
Dellinger                       3400 medium...

Search case expression
case
when condition then result
[else result_expression]
end

SEARCH CASE EXPRESSION IS ENCLOSED ALSO WITHIN CASE.... END BLOCK!!!
If condition is evaluated to true, it will return result
If expression is evaluated to fals, it will return null value unless an ELSE is defined
Examples:
SQL> select salary,
  2  case when salary < 5000 then 'low'
  3  else 'high' end
  4  from employees;

    SALARY CASE
---------- ----
      2600 low
      2600 low
      4400 low
     13000 high
      6000 high
      6500 high
     10000 high

Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

TO_CHAR Function - it  converts a date, number to a TEXT expression in a specified format. It means it returns an item of datatype VARCHAR2.
TO_CHAR function has following syntax:
TO_CHAR(number1, [format],[nls_parameter])
As we can see to_char has 1 mandatory parameter and 2 optional parameres.
Examples:
SQL> SELECT TO_CHAR(00001.1, 9) FROM DUAL;
TO
--
 1

SQL> SELECT TO_CHAR(00001.1, 9999.99) FROM DUAL;
TO_CHAR(
--------
    1.10

SQL> SELECT TO_CHAR(SYSDATE,'MONTH') FROM DUAL;
TO_CHAR(S
---------
JANUARY

TO_DATE - it will convert either a character string or an expression into a date value.
TO_DATE(string, [format],[nls_parameter]). The 'format' must be a valid DATE format: YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute
If no format is specified Oracle will assume the default date format has been supplied in char. Check here
Examples:"
SQL> SELECT TO_DATE(
  2      'January 15, 1989, 11:00 A.M.',
  3      'Month dd, YYYY, HH:MI A.M.',
  4       'NLS_DATE_LANGUAGE = American')
  5       FROM DUAL;

TO_NUMBER - it returns an item of type NUMBER. The syntax for the to_number function is:

to_number( string1, [ format_mask ], [ nls_language ] )

The 'format' must be a valid Number format. If we use shorter format mask, an error will be returned. If we use longer format mask, the original number will be returned.

SQL> select to_number('123.65', '9999.99') from dual;
TO_NUMBER('123.65',9999.99)
---------------------------
                     123.65

Using Single-Row Functions to Customize Output

Single-row functions return a single result row for every row of a queried table or view. For more information, check here.

1. Character Functions:

LOWER, UPPER, INITCAP – those functions are called character case convertion functions. They take only one parameter.  For example:
SQL> select lower('MY NAME IS MRUHTEL') from dual;
LOWER('MYNAMEISMRU
------------------
my name is mruhtel
SQL> select upper('my name is mruhtel') from dual;
UPPER('MYNAMEISMRU
------------------
MY NAME IS MRUHTEL
SQL> select initcap('my name is mruhtel') from dual;
INITCAP('MYNAMEISM
------------------
My Name Is Mruhtel
LENGTH – It returns the length of a string. For example:
SQL> select length('My name is Mruhtel') from dual;
LENGTH('MYNAMEISMRUHTEL')
-------------------------
                       18
SQL> select length(sysdate) from dual;
LENGTH(SYSDATE)
---------------
              9
SUBSTR – it extracts and returns a segment from a given source string. It takes 3 parameters. It has following syntax:
SUBSTR(source string, start position, [number of characters to extract]). If “number of characters to extract” is not specified, it takes as default value number of characters of source string. For example:
SQL> select substr ('my name is mruhtel', 3) from dual;
SUBSTR('MYNAMEIS
----------------
name is mruhtel –> it starts from position 3 and extracts the rest of the string
SQL> select substr ('my name is mruhtel',1) from dual;
SUBSTR('MYNAMEISMR
------------------
my name is mruhtel
SQL> select substr ('my name is mruhtel',3,5) from dual;
SUBST
-----
name –> it starts from position 3 and extracts 5 characters.

INSTR – It locates the position of a string in a source string. It has two mandatory and two optional arguments.
SQL> select instr('my name is mruhtel',3,2) from dual;
INSTR('MYNAMEISMRUHTEL',3,2)
----------------------------
                           0
SQL> select instr('my name is mruhtel',5,2) from dual;
INSTR('MYNAMEISMRUHTEL',5,2)
----------------------------
                           0
SQL> select instr('my name is mruhtel','my') from dual;
INSTR('MYNAMEISMRUHTEL','MY')
-----------------------------
                            1
SQL> select instr('my name is mruhtel','n',3) from dual;
INSTR('MYNAMEISMRUHTEL','N',3)
------------------------------
                             4
SQL> select instr('my name is mruhtel','n') from dual;
INSTR('MYNAMEISMRUHTEL','N')
----------------------------
                           4
SQL> select instr('my name is mruhtel',' ') from dual;
INSTR('MYNAMEISMRUHTEL','')
---------------------------
                          3
SQL> select instr('my name is mruhtel',' ',2,3) from dual
INSTR('MYNAMEISMRUHTEL','',2,3)
-------------------------------
                             11

REPLACE - It replace all occurrences of a search item in a source string with a replacement term. It has following syntax:
REPLACE (source string, search item, [replacement item]).

SQL> select replace ('my name is mruhtel','mruhtel','mira
REPLACE('MYNAME
---------------
my name is mira
SQL> select replace ('my name is mruhtel','mruhtel','john
REPLACE('MYNAME
---------------
my name is john

SQL> select replace ('my name is mruhtel', 'mruhtel') from dual;  ----> if [replacement item] is omitted, each occurrence of a search string will be removed

REPLACE('MY
-----------
my name is

ROUND - It rounds numeric value based on decimal precision scpecified. Its syntax is:
ROUND (source number, decimal precision).
For example:

SQL> select round (16359.69875,1) from dual; -------> if numeric value is greater than 5 we have "round up"occurence

ROUND(16359.69875,1)
--------------------
             16359.7

SQL> select round (16359.643332,3) from dual; ---------> if numeric value is less than 5 we have "round down" occurence

ROUND(16359.643332,3)
---------------------
            16359.643

SQL> select round (16359.643332,-3) from dual; -------> if decimal precision is negative, ti digit significant to the rounding is found n places to the LEFT

ROUND(16359.643332,-3)
----------------------
                 16000

SQL> select round (56987.236, -2) from dual;
ROUND(56987.236,-2)
-------------------
              57000

MONTHS_BETWEEN - computes the number of months between two given data parameters. It has following syntax:
MONTHS_BETWEEN(date1, date2)
For example:
SQL> SELECT MONTHS_BETWEEN('01-JAN-01','01-AUG-01') AS MONTHS_BETWEEN FROM Dual;

MONTHS_BETWEEN
--------------
            -7
SQL> SELECT MONTHS_BETWEEN('21-JAN-01','01-AUG-01') AS MONTHS_BETWEEN FROM Dual;

MONTHS_BETWEEN
--------------
    -6.3548387
LAST_DAY  - it returns the last day of the month in which a particular date falls. It has following syntax:
LAST_DAY(start_date)
 For example:

SQL> select last_day('12-DEC-2009') FROM DUAL;
LAST_DAY(
---------
31-DEC-09

For more details and functions check here

Substitution variables

I found veri nice article about substitution. Check hier.
Basically if we want to be prompt every time to put value in a script when ampersand ocure, we have to put single ampersan &. If we want to be prompt just once for a value, we need to use double ampersand like this &&.
For example:
select first_name, last_name from employees
where salary > &salary;
 Every time we execute that statemet it will promt for a value

select first_name, last_name from employees where salary > &&salary;
 It will promt us for a value just first time we execute the statement, because the variable is stored as a session variable.
Oracle provides also mechanism to undefine that session variable. It can be done using
UNDEFINE variable;
In our care will be UNDEFINE salary;

We have also posibilities to retrieve list with all defined variables by typing
DEFINE;

Second use of define command is to define variable during session.
DEFINE variable=value;

Sort the rows that are retrieved by a query

Oracle server does not store rows sequentially. It means there is no aphabetical order in a tables. In case we need to produce a report that has some order, we need to use ORDER BY clause in SELECT Statemen. The ORDER BY clause is aways last in a SELECT statement.
By default sorting when we use ORDER BY clause is the ascending sort. Have to remember that ascending order means:

• by character data - alphabetical
• by numbers - low to high
• data - earliest to latest

We can sort data also by descending order. For example:

select first_name, last_name
from employees
where salary > 1000
order by last_name desc;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Eleni                Zlotkey
Matthew              Weiss
Shanta               Vollman
Clara                Vishney
Jose Manuel          Urman
Oliver               Tuvault
Peter                Tucker
Jonathon             Taylor
Patrick              Sully
Lindsey              Smith
William              Smith
FIRST_NAME           LAST_NAME
-------------------- -------------------------
Sarath               Sewall
Ismael               Sciarra....

We can sort instead of scpecifying column name, specifying the position of column as it ocures in select statement. It means that positional sortig is possible only for columns that are listet in select statement. For example:
select first_name, last_name
from employees
where salary between 5000 and 10000
and job_id like 'SA%'
order by 2;
FIRST_NAME           LAST_NAME
-------------------- -------------------------
Sundar               Ande
Amit                 Banda
Elizabeth            Bates
David                Bernstein
Harrison             Bloom
Nanette              Cambrault
Louise               Doran
Tayler               Fox
Kimberely            Grant
Danielle             Greene...

Composite sorting - when result of a query are sorted by more than one column. Each column will be listed in order by clause from left to right in order of importance. To separate the columns we are using commas. We can also mix literally and positional sorting together. For example:

select last_name, first_name, salary from employees
where salary>5000
order by last_name, 3;

LAST_NAME                 FIRST_NAME               SALARY
------------------------- -------------------- ----------
Abel                      Ellen                     11000
Ande                      Sundar                     6400
Baer                      Hermann                   10000
Banda                     Amit                       6200
Bates                     Elizabeth                  7300
Bernstein                 David                      9500
Bloom                     Harrison                  10000
Cambrault                 Nanette                    7500
Cambrault                 Gerald                    11000
Chen                      John                       8200
De Haan                   Lex                       17000
Doran                     Louise                     7500
Ernst                     Bruce                      6000
Errazuriz                 Alberto                   12000
Faviet                    Daniel                     9000
Fay                       Pat                        6000
Fox                       Tayler                     9600
Fripp                     Adam                       8200
Gietz                     William                    8300
Grant                     Kimberely                  7000...

All rows will be sort by last_name alphabetically. The rows with same last_name value will be sorted by salary in ascending order.

Resctricting Rows

 Using SELECT statement we can retrieve data from a table, but if we need just few rows of a table returned based on one or more conditions we need to use WHERE clause.
Here are some important rules regarding WHERE clause:

• Where clause is optional;
• It always follows the FROM clause;
• We can apply one or more conditions to WHERE Clause.

Here are some exmples of using Where clause:

SQL> select first_name
from employees
where salary>15000;

FIRST_NAME
--------------------
Steven
Neena
Lex

It means salary should be 2000 or 5000 or 7000FIRST_NAME

SQL> select first_name
from employees
where salary=6000;

FIRST_NAME
--------------------
Pat
Bruce

 

SQL> select first_name from employees where last_name='Smith';

FIRST_NAME
--------------------
Lindsey
William

SQL> select first_name from employees where last_name like 'Sm_th'; ----> _ represents exactly one caracter

FIRST_NAME
--------------------
Lindsey
William

SQL> select first_name from employees where last_name like 'Smi%';  ----> % presents  0 or more caracters

FIRST_NAME
--------------------
Lindsey
WilliamWe can restrict rows by more than one condition. To do this we are using operators AND and OR.

• AND Operator - all conditions should be true

FIRST_NAME
--------------------
Lindsey
William

• OR Operator - one of the conditions should be true

SQL> select first_name from employees where last_name like 'Smi%' or salary > 10000;

--------------------
Michael
Shelley
Steven
Neena
Lex
Nancy
Den
John
Karen
Alberto....

BETWEEN Operator - comparison operator. It tests if some column value or expression falls within a scpecified range. For example:

WHERE salary BETWEEN 2000 and 5000;

It means : salary should be >=2000 and <=5000IN Operator - It testw if an item is member of specified set ot values. For example:

WHERE salary IN (2000,5000,7000);

NOT Operator - negates the comparison operator in condition. For example:

WHERE salary NOT IN (2000,5000,7000)



 SQL> select first_name from employees where last_name like 'Smi%' and  salary > 5000;

String concation

When we want to join expressions or columns together, we need to use concatenation operator which is ||. Here is some example:

select 'The name from employee with '|| employee_id || ' is '||last_name||' '||first_name from employees;

'THENAMEFROMEMPLOYEEWITH'||EMPLOYEE_ID||'IS'||LAST_NAME||''||FIRST_NAME
-------------------------------------------------------------------------
The name from employee with 198 is OConnell Donald
The name from employee with 199 is Grant Douglas
The name from employee with 200 is Whalen Jennifer
The name from employee with 201 is Hartstein Michael
The name from employee with 202 is Fay Pat
The name from employee with 203 is Mavris Susan
The name from employee with 204 is Baer Hermann
The name from employee with 205 is Higgins Shelley
The name from employee with 206 is Gietz William
...

What is happaning with the string that contains a quotation mark? Well, in that case we have 2 opportunities:

1. Adding an edditional quotation mark.

Example:

select 'My systers''s son is called Jemy' as Additional_quotation from dual;

ADDITIONAL_QUOTATION
-------------------------------
My systers's son is called Jemy


  2.   Using Q-quote operators

select q'(my sister's son is called Jamy)' from dual;

Q'(MYSISTER'SSONISCALLEDJAMY)'
------------------------------
my sister's son is called Jamy

Instead of brackets we can use can use any character we want. For example

select Q'K my syster's son is called Jamy K' from dual;
select Q'# my syster's son is called Jamy #' from dual;
select Q'% my syster's son is called Jamy %' from dual;

All those statements will produce the same result:
my sister's son is called Jamy