Tips for running queries on Oracle

r-onlineinformation.blogspot.com

Tips for running queries on Oracle

We encourage the use of PostgreSQL for the labs, to expose students to an excellent open source database.  However, if your syllabus forces you to use Oracle (and all participants at your remote centre are also from universities where they are forced to use Oracle), you can do assignments on Oracle.  Our model solutions will often give syntax errors on Oracle due to non-support for some SQL features in Oracle.  Below are some tips on how you can rewrite the queries to work on Oracle.
Oracle SQL Tips: non-standard features, and limitations
The following issues are for Oracle 10, and may or may not apply to later versions of Oracle.

1. Oracle does not support the as clause, except as part of the "with" statement. In all other cases, just use the same syntax without the "as" keyword, and things should work.
2. To use a single quote in a string, use two consecutive single quotes, e.g. 'D''Souza', the usual backslash escape (e.g. 'D'Souza') does not work.
3. Oracle supports the with clause, but there are some syntax differences: the query defining a temporary view should be enclosed in parenthesis, and if there are multiple views, separate them by commas, instead of repeating the with keyword. Also column renaming is not supported, you can only give a name for the temporary view. The following example illustrates the Oracle with clause syntax:
   
   with foo as (select * from bar),
               baz as (select * from zap, zit where P)
   select *
   from foo, baz
   where P
4. The with clause cannot be used in update queries
5. Oracle does not support tuple comparisons; thus, (r.A,r.B) < (s.A, s.B) will not work in Oracle; instead you have to use ((r.A < s.A) OR (r.A=s.A and r.B < s.B))
6. Oracle does not allow giving an alias to (renaming) a join expression such as (r natural join s), using FROM (r natural join s) T does not work. You can use from (select * from r natural join s) T, or even define T using a with clause.
7. If you use the expression "r join s using (A, B)", you cannot refer to r.A or r.B, you have to refer to them as just A and B; however, if you have another common attribute C, you can use r.C and s.C. For the case of "r natural join s", all attributes can be accessed using only their name, you cannot prefix them with "r." or "s."
8. Oracle allows correlation variables (table aliases) to be used in an immediate subquery, but not in a subquery of a subquery. This caused a lot of grief in some queries, where the obvious formulation required the variable to be used two levels down. 
9. Instead of the keyword lateral, use the keyword table
10. Oracle supports a weird non-standard SQL syntax which you should NOT use:
    select max(count(*)) from student group by dept_name

reference: iitb.ac.in/moodle