Language differences between Oracle, MS-SQL and Access

[FloridaSteve]

Am a beginner in developing in Access 97/2000. Want to learn ORACLE SQL and
wondering how much difference between the languages. I am aware that there
are some extensions to the ANSI code by the different vendors. Have yet
to learn how to secure and password protect applications or create setup
files for exportation, but this is near. Would appreciate any and all discussion.
Thank you.

FlSteve

[Tore]

Invest in Martin Gruber's "SQL Instant Reference" (SYBEX). It is small (normal
pocketbook size) and a tremenduous reference based on the SQL-92 standard.

Most databases have some restrictions and additions. Some of the key differences
between MS Access SQL and Oracle PL/SQL (I use Oracle version 8, newer versions
may differ some):

- LIKE statements use different wildcards (Oracle uses the SQL-92 standard
% for any string and _ for any single character, while MS Access uses the
MS proprietary * and ? respectively).

- MS Access supports the SQL-92 standard OUTER JOIN, Oracle does not. Instead,
Oracle uses an old style join with a (+) appended to a column name in the
WHERE clause.

- Many function names tend to be different on different database engines.
Many/most of the SQL-92 standard Aggregate functions are implemented with
their standard names, and a handful of basic string functions. Beyond that,
expect the functions available, their names and parameters to differ between
systems.

- Optimizing your queries on different systems require different techniques.
Or maybe I should say that Oracle (at least version 8) does not do a very
good job of optimizing reasonably complex queries. You have to read up on
Oracle's quirks (job security, I guess).

- The syntax for specifying Aliases can differ (Oracle often doesn't like
the keyword AS).

- MS Access uses double quotes as a string delimiter and [square brackets]
as identifier delimiters. Oracle follows the SQL-92 standard using single
quotes for the string delimiter and double quotes as identifier delimiters.

Besides these differences, you'll find that the development tools make a
big difference in how you do things in the two environments. Oracle's tools
(again, version 8) leave a lot to be desired (or so I think), so you may
want to look for alternatives when the time comes. Also, help is not as
easy to come by for Oracle.

This should get you started,
Tore.


"FloridaSteve" <steve.wilson@cnet.navy.mil> wrote:
>
>Am a beginner in developing in Access 97/2000. Want to learn ORACLE SQL
and
>wondering how much difference between the languages. I am aware that there
>are some extensions to the ANSI code by the different vendors. Have yet
>to learn how to secure and password protect applications or create setup
>files for exportation, but this is near. Would appreciate any and all discussion.
> Thank you.
>
>FlSteve