Thoughts on Learning Oracle SQL: 2 – Knowledge and Normalization

I return again to my APEX SQL command box, break open my books and guides, and prepare once more to do battle. In the time I spent away from it, I had many concepts and ideas floating in my head, and rather than do the smart thing and look them up in the book, I first try them out in my own example system. I experiment with customizing column display headings, and data formatting in the retrieved data. Glancing only at the Oracle guide to find exact format syntax, I manage to muddle through making a more customized display, retrieving and combining string data, as well as performing some time math against other data. Once I make sure I save the query for later use, I return to my books. Today it’s all about expanding the usability of the WHERE and building better, leaner, more intricate filters.

Increasing Knowledge

SQL gives us many things other languages don’t. While you still have to write them out, and provide the logical and precedential constructs to them, I find it easier to construct a multi-layered criteria model for testing data than with other languages. It is still however a good idea I find to actually write out the conditions for the test manually, then convert them into code. Hmm, sounds like design, must be a fluke. Keeping a running log of all the queries that have worked, and the various ways to test data coming back out, be it through a simple value comparison ( equals, greater than, less than) or delving into string math and string comparisons, or looking at date differentials, you can begin to see how versatile you can get. Then they spring the kicker on me. You could use a query inside the WHERE clause to further the filtering. Very nice. Now I could use another query to generate a list of good, or bad values to test against. Oh the power.

After a short power trip of making queries that were insanely ugly to test this new found skill, I returned to my more mundane ones, and sought out the next great answer. And the next page held the answer, how to re-order your results how you want. So now, I could use a normal sorting routine to sort my data, and by multiple rows as well, in differing directions if needed, instead of the default sort of the order the data was read from the table. Now my data was growing more readable and organized with each iteration and application of knowledge. I suppose this is how learning is supposed to be. The book then takes a break from the WHERE and ORDER and FROM of daily life to discuss a concept I had heard about, mainly normalization.

Normal Forms

Basically at it’s core, normalization is merely a set of guidelines to better help you design (there’s that word again) your database tables and how they will interact. A simple set of guidelines at that, as the book stops at third normal form, while the grizzled old veterans I know speak of the evils of fifth normal form and the battles to the death with the data demons of sixth form. Someday maybe I’ll get to that stage. The basic premise I took away from it was that it was a very good idea to actually pause and think about the design BEFORE making it, it’s easier to measure 3 times and cut one, than it is to try and be inside the database 2 years after it went live applying electronic nails and bailing wire to make it work better due to jumping in with no planning.

I go over the guidelines and apply them to various test ideas I had, and other databases I work with, and can see where the programmer did some design work initially, and where they didn’t. Making a few notes, I dive back into my SQL pool and press on to subqueries. A great little tool when used right, a horrid resource drain if not. Subqueries allow you to place a query inside another query, extending the scope of the overall statement, also allowing you to read from multiple data tables at the same time. I apply this new insight to my own work, and manage to create a set of nested subqueries inside of a main query and it seems to work ok. It appears to run fairly well, of course, since I’m the only one who can use it, I won’t see any major difference in the performance, at least not yet. I take a look at my examples, and try to add another subquery, and I hit a block. I double check all my work, and it seems fine, the subquery will work as a stand alone query, but not as a sub query. It is perplexing.

Roadblock

I enjoy problems. Part of the learning process is working through them. Figuring out what the true problem is, and fixing it, or if it’s out of your league, finding the help you need to fix it. I spend many many hours trying to fix the problem, but without results, I have a feeling where the problem is, and it is my thought that the problem lies in a misunderstanding of a somewhat poorly worded explanation in the book. I am reluctant to, but I go to an outside source for help, and with their help, I get a much easier, and simpler explanation of the problem, and the concept I was working on. Applying what I was told, I now can get the whole example to work. Not only that, but the statement is smaller, and runs faster now as well, prompting me to examine the differences between the original query, and the one I received help with.

By now I had touched on most of the basic fundamental aspects of SQL. Creation of items, retrieval of data, filtering of data, updating data, even removing data. There are things I still don’t understand fully (Joins…you are a complex beast) but I also know that poking around with sample data in a learning environment only will get you so far. There are still more things I need to cover, and concepts that are a part of SQL, and they are looming on the horizon.

Leave a Reply

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