SQL note2

Chap 2: select, where
---------------------
1. Character strings and date values are enclosed in single quotation marks.
Character values are case sensitive, and date values are format sensitive.
The default date format is DD-MON-RR.

2. Between A and B  <====> >= A and <= B

3. ESCAPE : You can use escape to filter the data contains '%' and '_'.
Here is a example:

SQL> select ename from emp where ename like 'Milo\_%' escape('\');

ENAME
----------
Milo_Luo

4. Operator Priority:
    1    Arithmetic operators
    2    Concatenation operator
    3    Comparison conditions
    4    IS [NOT] NULL, LIKE, [NOT] IN
    5    [NOT] BETWEEN
    6    NOT logical condition
    7    AND logical condition
    8    OR logical condition

You can use () to force a priority changing.

5. ROWNUM :

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;
Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees    WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

SQL> select * from emp where rownum > 1;

no rows selected

Order by must be place at the end of the statement.

SELECT     *|{[DISTINCT] column|expression [alias],...}
FROM       table
[WHERE     condition(s)]
[ORDER BY  {column, expr, alias} [ASC|DESC]];