Skip to main content

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
 
 

Comments

Post a Comment

Popular posts from this blog

Create Custom ScreenTips for Shapes (MSOffice-word-2007)

r-onlineinformation.blogspot.com Create Custom ScreenTips for Shapes Shape ScreenTips in Microsoft Office Visio are the boxed text that appears when you point to a shape. Some Visio shapes have built-in ScreenTips—usually the name of the shape (such as the name of a workflow step). However, you can edit existing ScreenTip text or create new ScreenTip text for shapes that don’t have a built-in ScreenTip. To create or edit a ScreenTip: 1. Select the shape. 2. On the Insert menu, click either Shape ScreenTip or Edit Shape ScreenTip, as applicable. 3. In the Shape ScreenTip dialog box, type or edit the ScreenTip text. 4. Click OK.
1 comment
Read more

For Searching CSS Template (Free)

r-onlineinformation.blogspot.com   For Searching CSS Template  http://www.freecsstemplates.org/ http://www.bestfreetemplates.info/ http://www.freecsstemplates.com/ http://www.free-css-templates.com/ http://www.freecsstemplates.in/ http://www.free-css.com/free-css-templates/page1.php http://www.csstemplatesfree.org/ http://csstemplatesmarket.com/ http://csstemplatesfree.net/ http://www.templatemo.com/page/1 http://www.csstemplatesforfree.com/csstemplates.aspx http://all-free-download.com/free-website-templates/ http://www.template4all.com/css/ http://www.csstemplatesforfree.org/ http://www.mediavolo.com/?d=1  you may also search free css template for educational purpose from any famous search engine..  
Post a Comment
Read more

Some tips in creating a strong password

r-onlineinformation.blogspot.com * Avoid using any words that you can look up in a dictionary. These include common misspellings of words. * If you follow good security practices and try to change your password every say 6 months, do not just increment a digit or the digit in your current password * Avoid creating a password that begins or ends with a number because they can be easily guessed that passwords which have a number in the middle. * Avoid creating a password which someone can easily guess by knowing a little bit about you. This includes names of pets or family members or sports teams. * Avoid creating a password which includes words for popular culture. * Always create a password that requires the usage of both hands on the keyboard. Why is this important? So someone who may be looking over your shoulder don't need to worry about just looking on one side of the keyboard. Again much easier to guess. * Always have a password that uses uppercase and lowercase letters, has n...
Post a Comment
Read more