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 CHAR


SQL > select * from t1 where n1 = 1;

C         N1
- ----------
1          1

SQL > 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 standard


SQL > 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 standard

SQL > 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.

Next

Back to DBAsupport.com