Saturday, December 1, 2012

Report Building Part 2: The Basics of a SQL Query

Since I'm bored in my hotel room I thought that I would get this blog series off the ground by covering the basics of a SQL query.  To keep the information relevant I will use the query that I included in the last post as a template:

SELECT Manufacturer0, Model0, Count(Model0) AS 'Count'
FROM dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Manufacturer0,Model0
ORDER BY Model0

SELECT

A SQL query always starts with SELECT, commonly referred too as a SELECT Statement.  There are many commands that you can use in SQL, but SELECT is what you need to start with when you want to retrieve data from your Database.  The other commands allow you to add and remove content to your DB so is not part of the scope of this series.

After SELECT you provide the details of which tables you wish to obtain information from.  You can use a * wildcard here if you wish to select all tables but its best to specify tables so you can keep the query results manageable.  In the example above, I'm selecting the tables called Manufacturer0, and Model0.

Another part of the SELECT statement is the Count function.  Basically, this function will count the number of entries in a column and provide a total.  To explain that based on the Query: Count the number of Models in the Model0 column and place the total in a column called Count.

FROM

The next part of a SQL query is choosing where the tables are located.  In this case the tables are located in dbo.v_GS_COMPUTER_SYSTEM.  This is where you need to know the layout, or schema, of the database you are trying to query.  Microsoft released the schema for SCCM 2007 some time ago, I've linked to it on the previous post.  I haven't seen a database schema for SCCM 2012 yet so I'm assuming that it is the same.

GROUP BY

This instructs SQL to display the query results in like-groups rather than individual rows.  As specified above, it will first group via Manufacturer, then by Model. Additional entries for each Manufacturer will be created as Models are discovered, and vice versa.

ORDER BY

This tells SQL how to order the data in the report, otherwise there will be no order to the data output.  Depending on the complexity of the query, this could make the results unusable.

To give you an idea of what the output from this query looks like, here is what my results look like:




For all you hard core SQL designers and report builders, you will notice that I have left out A LOT in this first post.  Missing are some very important syntax items such as JOIN, INNER JOIN, OUTER JOIN, and many others.  I hope to cover these in future posts as we add to this query to include the additional data required as well as how to incorporate Parameters into the query so we can add the ability to choose Collections to limit our results.

Please let me know in the comments if I made any errors, if I'm not clear in my description, or to provide any other feed back!


Table of Contents

Introduction to Series
Part 1: The Goal
Part 2: SQL Query Basics
Part 3: Lets all JOIN hands

No comments: