Thoughts on Learning Oracle SQL: 1 – The Prelude

kevin.landonTechnical TipsLeave a Comment

So you want to learn SQL. Not a problem. You want to learn Oracle SQL, not a problem. You have no knowledge of SQL, not a real problem. All you need is some patience, access to a computer and the internet, and a little bit a familiarity with how to use a computer. I was asked to begin reviewing SQL for work, and to focus on the fine points of the beast that is Oracle SQL. I have done some SQL before, many moons ago, shortly after dinosaurs roamed the earth and a 720K 3 and a half inch floppy was a neat thing to have. After going over my old mundane SQL work that was written into an old Visual Basic program against an Access database, I found myself floundering in the immensity of my new challenge. SQL has come a long way since the beginning, and is more powerful and robust than ever. So I go to Oracle themselves and humbly acquire the Oracle 11g SQL Reference guide, all 1500 pages of it in electronic format. I also get the PL/SQL (anticipating the need in the future to learn this as well) guide. Now don’t get me wrong, I consider myself fairly intelligent and logical in my process of learning. But a few pages into the guide and it is obvious the Oracle guide is meant for the more advanced users of SQL. So, I retrace my steps, and believe it or not, find SQL for Dummies that makes much more sense for the new user.

As most know, SQL is accepted as a de-facto standard for working with relational databases, with several major sub-flavors. Among these are IBM-DB2, SQL Server, MySQL, Access, and Oracle. Thankfully between Oracle and the books, the major flavors are covered quite well, as are the differences and quirks about some of them. But onto the work at hand. As a beginner in this world of Structured Query Language, I knew a little about the basic SELECT, INSERT, and UPDATE structures. But I wasn’t fully aware of how they worked and the other aspects of SQL that were out there. Given the non-procedural nature of SQL, it’s difficult to learn in the traditional sense. Many of the more complicated things you can do are combinations of simpler concepts. Also, since it’s designed to work with data, you either have to have examples that generate the data tables for you to use (and explain the ‘don’t worry, we’ll cover how this works later’ system) or you need a preset environment with data already there.

The Basics

Thankfully for me, and my own learning style, I had such a platform. My APEX project was still installed, and had the sample data in it from when I was learning how that worked. APEX is a wonderful tool for learning SQL I found out, as it has a built in SQL command box that you can use to test your examples, write your own SQL statements, as well as look at the table structure and layout, and the SQL that would generate those very tables, letting you test yourself in the age old practice of reading code and figuring out what it does.

So we start at basic table creation. Learning how to make a table, add a column, modify a column, and drop a column or a table. What we create we can also destroy. A good thing to know. A bit of conceptual theory behind what makes a good database design, tips for better data structures, and a lot of ideas that get formulated and churn in my mind as I read the books. Occasionally I look up from my books and try the concept being read about against the test data in my APEX install. For the faint of heart, I would say to do exactly what the books say, they show you very simply how it is done. My own method says take the concept and apply it, and hope it DOESN’T work. That way, I have to work at it, and figure out why it didn’t and where I goofed, leaving me with a better understanding of what I’m doing. Works for me quite well, and at the same time, since it’s on non-critical data, not much is lost if I enter the wrong command. Were I working on live (or a copy of live) data for a client, the safe road becomes the method of choice.

After we create a few tables, and test the waters of our basic understanding, we start building a set of example data to be used throughout the rest of the text, entering it and updating it. We learn the basic structure of the SELECT FROM and how it works, what it does, and it’s basic syntax. From there we branch out, beginning to limit the fields we bring back, applying conditions and tests via a WHERE clause, learning how to limit and restrict the data coming back. And then problems crop up.

Differences

Although each major flavor of SQL supports most of the ISO standard for it, there are variances. In my learning, the example text was written against a previous version of Oracle SQL (the example text has different code for the major flavors where needed) and some of the examples they gave no longer matched with the Oracle guide and recommended practices. Pausing in the process of the siege, I take a few hours and track down the items that aren’t matching and essentially rewrite the SQL script files that came with the 3rd party books so they match the rules and guidelines of the Oracle reference guide. This also impresses upon me the importance of cross-platform support, making sure the code I write will work in another SQL environment without any, or only minimal, modification.

Some of these problems are addressed in the datatypes used by the various flavors. The books covers the basic datatypes, and what they are meant to cover and store, and how they can be used. But I find this at odds with the Oracle guide. The two sources don’t match. While there is legacy support in Oracle for some of the standards, they recommend different datatypes than the book, and offer differences in how they are structured. For example, the book loves to use the Integer datatype. Oracle says instead to use the NUMBER type and give it parameters. They accomplish the same thing, but go about it differently. There were have a dozen datatypes listed in the book depending on if you wanted shorts, longs, floats, double floats, root beer floats, wait, not that last one. In Oracle land, they use a single NUMBER datatype primarily, and let you define it’s scale and precision to get the various data you need. At first a bit confusing, but pretty simple when you step back a bit and look at it. The book also loves to use the VARCHAR type, while Oracle makes a rather large point to use VARCHAR2, citing future evolutions of the VARCHAR type that will most like cause issues with the systems if you try and use them interchangeably. In the process of going between my sources to figure out the data types I find a very convoluted thing that Oracle calls a syntax flowchart….

I find out rather quickly to not look at the syntax flowcharts in the Oracle guide for the commands. A fine product, but the charts are geared for highly trained individuals who are well versed in the ways of convoluted lines and tiny arrow marks. I decide to take a break for a bit, and resume later when the dust has settled and the current reading and learning has had a chance to sink in and make connections to itself I can’t see at the present.

Leave a Reply

Your email address will not be published. Required fields are marked *