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!




Test a byline

by Brian Wisnor

This is a test post to see how name would appear as byline if I just put it rignt into the post instead of messing around with all these silly plugins that want to pull my info from my Google+ profile.  I don’t want a Google+ profile – I simply want to insert a line like that which appears in any newspaper indicatin who wrote the article.  I can’t believe we make this so hard – why can’t there simply be a ‘byline’ field in settings?  Strange.  I would think a byline would be a pretty basic function for writing articles – you have a headline, a byline and an article.

Happy St. Patrick’s Day from Chicago

St Pats Parade Chicago 2013

Not really a day for a business article but a great day to celebrate being even a little Irish.  Celebrating St. Pat’s Chicago style with brother and wife who happen to be in town for a conference.  Sister and law and I made it out for a bit of the parade while brother attended conference.  Then back to Chicago Hilton where it appears, judging by crowd in lobby after parade, many of the parade participants are staying.  Like I said, great day to be Irish in Chicago and having family in town to celebrate the day was really nice.

Screenr Test Tour Take 4

Here’s another test of the Screenr screencasting tool. Once again, for this test, we are taking a tour of our own StreetSmartSOB™ web site.  For this test, I switched to recording with my Plantronics headset and boy what a difference in sound quality over the original headset I was using. And while I did write out a quick script for this screencast, it is obvious I need to work on my rehearsal and recording skills.

Screenr Test 3 using jsvideo embed

This is test of 3rd method to embed my test Screenr video into my post.
RESULT: Woohoo! Looks like we have a winner :) Video size is adapting to, and playing on, PC, iPad and Droid phone. (No sound on Droid phone but think I somehow muted it – not getting sound on anything).

Test 2 of Screenr Screencast

In our first test, we used the embed code from our screenr screen to embed the video into our post. This worked fine for PC, cropped far right hand side of video on ipad and video wouldn’t play at all on our droid bionic. Now we’ll try a different method of placing screencast into our post. RESULT: For this post we tried using the standard Add Media button – which just gave a link in our article to the video – not what I was looking for.


Test of Screenr Screencast

In this test of the Screenr screencasting tool, we take a tour of our own web site.  In this post, we’ll test embedding the video with the embed code from screenr.  One thing we’ll be looking for is to see if video is ‘responsive’, meaning the size auto-adjusts to the display, by viewing on PC, iPad and Droid Bionic phone.  If it does not appear to be responsive, then we’ll try using our videojs plugin to wrap our .mp4 video in HTML5. RESULTS: Looks great on PC. Video crops right hand side on iPad but is ok. Video did not play at all on Droid Bionic. Going to try and post a different way now.

Another YouTube Embed Test

Just another test of embedding a YouTube video.