From the Blog

SQL Lesson 1. Intro to SQL Databases

We know that our enterprise systems store our enterprise data in databases. There are a lot of different types of databases out there but for this SQL Survival Skills series we are going to focus on Microsoft SQL databases, also known as MS SQL databases. Now SQL itself is not actually a type of database but rather it is a programming language for working with relational databases. Many of us have heard or read that SQL stands for Structured Query Language, but Wikipedia says that's not really true.

We'll leave it to the experts to debate the definition, but for our purposes we'll just go with the fact that SQL is a language and a tool we can use to query our MS SQL databases to retrieve information. We know that we enter data, unique pieces of information, into fields on our screens, either manually, through automation such as bar code scanners or by means of interfaces, such as EDI. And while the data may appear to be organized as it is laid out on the user interface screens, it is not typically stored in the same manner in which it appears to users on their screens. The data only appears to represent information to the user because of the way it is presented in the user interface.

Data vs Information

I'm sure most of us know the difference between data and information, but it's a point worth reviewing before we start writing our SQL queries. An individual piece of data is just that – an individual piece of data. For example if I told you I had 2 pieces of data – $200,000 and $500,000 – and I asked you what these numbers mean, what would you say? Ok, what if I told you one was labeled Sales and the other Cost of Sales – would you say the company is doing well or poorly? What, you still don't know? Why? Because if the $200,000 is our Sales, then we are doing poorly but if the $200,000 is our Cost of Sales then we may be doing well, huh? So, $200,000 and $500,000, just like Johnny, Edwards, and 90210 are all just pieces of data, not until they are labeled, arranged and presented in a logical manner do they become meaningful information. And, turning data into meaningful information, is what learning to write SQL queries is all about.


Caution: When you start working with data and you are trying to turn it into information, you must understand the context of the data in order to organize it into meaningful information. Otherwise, you run a great and grave risk of mis-representing the data as incorrect information and this can have very bad consequences for you and your reputation – especially if your boss or their boss makes a bad business decision based on your bad information.


To understand the context of the data, you must understand the business meaning of the data. It is not enough to be able to retrieve the data, but you must understand what it represents and the best way to do that is to first understand the business process which gathers or produces the data. You are not truly a business analyst if you do not understand this. For example, in one ERP system in which I worked, there were 4 different costs for a product – a PO cost, a Landed Cost (PO Cost + Shipping & Handling), a Loaded Cost (PO Cost + S&H + Warehousing Overhead) and an Average Cost (a weighted average cost based on the cost and quantity of all of that product in inventory – very common in food industry where price for same product can vary from day to day). So, when you're asked to get the 'profit' or 'margin' information for a product from the database, do you know which cost you will subtract from the selling price? And do you think your boss or business user will know enough to tell you or ask you which one should be used?


Ok, Ok, I get it – data is just data and it's up to me to turn that data into meaningful information by using my magical SQL skills and my business knowledge about what the data means – so just show me how to do that!


Databases, Tables and Fields

Ok, we're getting there, just one more thing, and you probably know this too, so just a quick review. To state what might seem obvious, when you go to get information from your database, you'll need to know the name of the server on which the database resides and you'll need to know the name of the database. For most enterprise systems, there are at least two databases, Production and Test, and as many as four or more, such as Sandbox, Development, QA and Production. Your DBA will know the names of the various databases and on which servers they reside. If you install the SQL Management Studio and sample AdventureWorks database locally on your machine, as we are doing for this lesson, we'll point these out in those articles.


Now, once you know your server and database, this will help you connect to your database from within the SQL Management Studio, assuming you have the proper permissions. Again, we'll cover that in upcoming articles.


The last thing we'll cover in this article is that, for the purposes of our query lessons, think of databases as being made up of tables and tables being made up of columns or fields. For example, we may have customer data stored in a table called CUSTOMER, and in that table we may have fields (columns) such as customer_id, customer_name, customer_url, address_id, etc. Then we may have another table called ADDRESS, and it might contain fields (columns) like address_id, address_line_1, address_line_2, city, state, country, zip_code. Ok, got the idea? Server > Database > Tables > Fields


So our database will reside on a server and once connected to our database we will be working with tables that are made up of fields (columns) that contain our data. And with our magical SQL skills, we will soon be able to query those tables in that database on that server to extract the information contained in those fields (columns).


Sound good? Ok, just let that sink in until our next lesson where we will explore the SQL Management Studio Query Analyzer window to see what all these tables and fields look like in the database.


Until next time, my StreetSmart SoB's, rock on!