How many of you have asked yourself what SQL is all about
anyway? Join me as I begin a venture down the road of understanding SQL and how
to take advantage of this language.
SQL
The spelling is so easy and even rolls off our lips with
simplicity. The fact of the matter is this language has caused more agony than
one can imagine. I get dozens of emails and forum posts every week, wanting to
know how to extract or insert data from a RDBMS. Developers I have worked with
in the past, and have worked with databases for many years, still have troubles
constructing what seem to be simple statements. Couple the inability to
construct SQL with wanting any form of database performance you have a recipe
for failure. Therefore, I am beginning a series of articles that will take us
through SQL awareness. If during this series you have questions or special SQL,
send it my way. I will try to work it into this series.
We have already briefly touched on what SQL is but a
definition is appropriate here. SQL or Structured Query Language is nothing
more than a statement or line of code that is used to communicate with the RDBMS
(Relational Database Management System). SQL is the glue between database and
application. Now there are considered to be two types of SQL, DML and DDL. DDL
is the Data Definition Language that we use to create database objects and
their constructs. DML is the Data Manipulation Language that we use to extract
or alter data from within the RDBMS. This series of articles will deal only
with DML.
So where did SQL come from
Well, it came from a guy named E. F. Codd who is considered
the Father of RDBMS. I often wonder how these types of individuals were as
kids. Did Codd, when he went outside to play baseball say, I’m SELECTing the
bat and ball FROM the garage WHERE my Dad parks the car AND we can hit
baseballs. IBM then took Codds work and Structured English Query Language
(SEQUEL) was born. Funny, I just had a DBA last week send me an email with the
SEQUEL spelling instead of SQL. They were a bit old so we had a nice laugh
together on this one.
So where is SQL going
SQL is in constant flux. The American National Standards
Institute (ANSI) International Standards Organization (ISO), and the
International Electrotechnical Commission (IEC) are constantly working and
accepting new standards to the language. So you think you want to read up on
the standard. You can go to the ANSI web site at http://webstore.ansi.org/ansidocstore/default.asp
but do not expect to find anything free. The SQL standard is typically composed
of many parts and the ones I looked at were no less than $100USD and some are
over $200USD. The ISO web site at http://www.iso.ch/iso/en/prods-services/ISOstore/store.html
proved to be just as fatal. Now there are some older versions of the SQL
Standard floating on the web. I would encourage you to do a search for ‘SQL Standard’
and at least see how some of these are worded.
So give me something usable
Are all SQL engines created equal? I hope this is obvious but no, the SQL
engines in Oracle, SQL Server, or DB2 are all different and while they are SQL
compliant to a particular level of the standard they all have added extensions
to the SQL standard to take advantage of their own internal database feature
set. This is both good and bad. If you want to only use one database vendor
then by all means use the non-standard SQL they provide. Sometimes this is the
only way you can use that special feature you need to cut development costs.
But if you code for multiple database vendors you really want to stick with the
current SQL standard. To this note, there is an organization called The Federal
Information Processing Standard (FIPS) that requires the database vendors to
give us a method for identifying those SQL statements that do not comply with
the SQL92 standard. Oracle does this by providing a FIPS flagger.
You can turn on the FIPS flagger by
issuing the following SQL.
SQL > ALTER SESSION SET flagger=FULL;
Now when you run some SQL that is
not SQL92 compliant you will get an error explaining the reason. For instance I
created a table T1(c1 CHAR(1), n1 NUMBER) and had the following results when
turning on the FIPS flagger. As you can see, what looks like a normal SELECT
statement produces an error when trying to comply with the SQL92 standard.
Basically the standard does not allow for a character comparison with a data
type that is a character string. I then tried some simple equality in the
example and as you can see the ‘= 1’ works just fine. But when a NOT EQUAL
condition is needed the SQL92 standard does not like ‘!=’. If you read the
error SQL92 suggests using ‘<>’ but this also gives an error. Finally I give
up and, understanding that the SQL standard compares fine to a result set,
build some SQL I know will work.
SQL > select * from t1 where c1 = ‘1’;
select * from t1 where c1 = ‘1’
*
ERROR at line 1:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
ORA-06550: line 2, column 27:
PLS-01454: No operator may be used with values of data type CHARSQL > select * from t1 where n1 = 1;
C N1
– ———-
1 1SQL > select * from t1 where n1 != 1;
select * from t1 where n1 != 1
*
ERROR at line 1:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
ORA-06550: line 2, column 27:
PLS-01473: Use <> instead of != or ~=
ORA-06550: line 2, column 27:
PLS-01452: This function is not part of the ANSI standardSQL > select * from t1 where n1 <> 1;
select * from t1 where n1 <> 1
*
ERROR at line 1:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
ORA-06550: line 2, column 27:
PLS-01452: This function is not part of the ANSI standardSQL > select * from t1 where n1 <> (select 5 from dual);
C N1
– ———-
1 1
You can turn off the FIPS flagger
if you wish not to be SQL92 compliant.
SQL > ALTER SESSION SET flagger=OFF;
It should be noted that the FIPS
flagger is a session only parameter and thus you can not set it with an
initialization parameter. Also, beware that since this ALTER SESSION statement
itself is not FIPS compliant, a subsequent issuing of the ALTER SESSION command
will generate an error even though it will still alter your session.
SQL > ALTER SESSION SET flagger=FULL;
Session altered.SQL > ALTER SESSION SET flagger=OFF;
ERROR:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
Session altered.
Again, please note that the error explicitly states the use
of the "Oracle SQL feature not in SQL92." Oracle 10g is fully or
partially compliant on the SQL:2003 standard. If you care to explore Oracle’s
compliancy to the SQL:2003 standard, you should look at Appendix B of the
Oracle Database SQL Reference. Oracle is quite open with this information.
So I want to write some SQL
Me too! Actually I enjoy writing SQL. It posses a challenge
to effectively take a business problem and write it in such a way that not only
accomplishes the task of extracting data but is readable and maintainable at
the same time. We will be doing this in the following parts of this series. For
now be assured we will take it somewhat slow at times and introduce the
concepts in a fashion that will make you the guru. I will also present what I
feel are some of the more complex type problems, and solutions, to common SQL.
And if you are wondering, I will not stick to the SQL92 standard. I personally
see no reason here as Oracle has some great SQL extensions that should be made
use of. Please email me with any questions or SQL you have and I will try to
work it into the series.
