SQL and Me (A BIG subject)

I don't plan on writing the full history and scope of SQL, just the history of me and SQL so that you have an understanding of my experience and perhaps learn something along the way. Believe me, there is enough information on the net about SQL or any form of a database that can be consider to be related to SQL and a Relational Database. Look up NoSQL and you'll start diving into the world of XML files and more.

What is SQL

SQL stands for Structured Query Language and is a programming language used to manage data in a relational database management system (RDBMS). These are systems used for the storage of data. Using SQL the data can be added, altered, manipulated and removed from a database. The database consists of system tables and user tables created to store relevant data including libraries, system dictionaries and system tables such as those containing user data. SQL uses a sublanguages in the form of DDL, DCL, and DML commands. Data manipulation is DML and use to the commands Update, Insert and Delete to change, add or delete data from the database tables. DML being the primary one in daily use.

    Relational Databases in use include:

  • Oracle
  • Microsoft SQL Server
  • MS Azure SQL Database
  • MySQL
  • NUODB

How I came to use SQL back in the mid 80's'

I learned SQL back in 1983 when I was using a language called APL which simply meant a programming language. Using APL was, once learnt, was a fairly simple means to create reports from a terminal connected to an mainframe computer such as the IBM3270 which was in used by United Biscuits at the time. Don't misunderstand me when I say APL was fairly easy, it wasn't, it was basically a programming language with complicated operators which was way over the top for the information we were extracting in those days. Then came the IBM RDBMS which offered a much more creative interrogation of the data that was available but unused unless you were a COBOL programmer. Instead, COBOL was used to extract the data from tape and load it into a relational database that was really being designed on the fly at the time.

In the early days of my use of the RDBMS to build reports I used both APL and SQL to compare data to ensure that the data being extracted from the database was in fact the same as the data being used when programming reports using APL. So I also learned a query language in the simplest form of a query language called QBE (Query By Example), QBE in fact was a graphical interface for users but which in my mind was far too long winded for my needs. I was after all asked to create multiple reports using the new RDBMS so QBE was never going to do. So I set about finding out what was behind QBE, that's when I discovered SQL. This looked more my type of thing, by now I was into programming and looking at COBOL. The opportunities for me and SQL at that time within the business were exciting to say the least, I had to leave COBOL on the shelf for quite some time. Ideas kept on coming through for several years after that and helped UB move to Central Warehousing and make significant cost saving as well as efficiencies.

While I'm here and as I go I'll be talking about Business Intelligence and my experience with Business Objects as an administrator and designer.

ETL and Datawarehouse

I'm going throw something else in here as I've mentioned BI and that is ETL. ETL is a process that extracts, transforms and insert data in to a central warehouse Database. Back in 2001 I started a Data Warehouse project using Kalido Datawarehouse and taking the data from various sources including Oracle Databases and feeding it to a DW for use with our chose BI called Business Objects.

The phrase ETL wasn't in use at the time, it's just what we didn't do a lot of the time since we were just taking data from one source and feeding it to another. But going back to my early days with SQL and the data we held and which could only be accessed by COBOL programmer, we actually did use ETL as part of the process of creating the database. The data from each table wasn't used in it's pure form, I would join tables and take the data I needed in the way I needed it and load it in to another table, a temprary table and run the reports I wanted from those temprary tables. This meant that a lot of the data processing could be done over night and assembled ready for use the next day. The computer had a better loading and we had improved delivery of our reports. I even set up menus and queries for users to run their own reports before BI came along. I left DiverseyLever before Kalido was implemented, the company was sold to Johnson Brothers and I think Kalido was shelved on the basis of expertise and costs.

ETL is of course a process Extract Transform Load, I could have coined that title myself after all the data used by Business Objects BI was a the data stored in those temporary tables. So I was technically working with SQL before ETL and even before we had a Data Architects, in fact, when I joined DL back in 2000 I didn't know they even existed. Ask a Data Architect I said! ETL is an important part of today's business intelligence (bi). It is the IT process from which data from disparate sources can be put in one place to programmatically analyze and discover business insights. etltool.com See this website for more information, stats, trends and ETL selection.

RDBMS

There's a lot of information on the web about SQL and relational databases, when I started with SQL there wasn't a web except in the corner of my office.

To come, SSRS, more on BI, Oracle and NoSQL.

This isn't a page for actual SQL code although I can always create some but I may have bored you already, I shall add that type of thing to Blog. But I'm sure we all know what happens when we run this "Select * from emp where empname like='B%' Order by lastname"

Phillip Donnelly