I like my customers to be as independent as possible. I hate consultants who pretend everything they do is brain surgery and beyond anyone else’s capability.
So, when GPUG asked for a volunteer to give a webinar on SQL, I proposed one on SQL for super users.
It’s not that I expect my customers to become programmers. But I believe educated consumers are the best customers. And a little knowledge of SQL can help data jockeys fix simple queries or at least better understand what IT has done to their system.
The feedback from the seminar was positive, in spite of a few challenges. (If you’ve ever given a webinar before, you’ll now how odd it is to talk to a screen with little immediate response, instead of an in-person audience.)
The audience consisted of all Dynamics user groups (GP, NAV, AX and CRM), which added another level of complexity.
I chose to use the generic Northwind database with some additions for my examples (so everyone would be equally confused).
A Series of Posts for SQL Super Users
Since then, I’ve turned the content of the webinar into a series of blog posts. Keep in mind that while I’ve written these posts for SQL newbies, I’m assuming you have data experience. Maybe you’re an Excel pivot table jockey. Or you’ve used a different kind of reporting tool (such as Crystal, Access or SSRS).
Over the course of the series, I’ll cover the following topics:
- Database terminology
- How to write a basic select statement
- How to add single and multiple columns with joins
- How to use the left outer join
- How to use aggregates, such as sum, min and max
- The difference between “having” and “where” clauses
- Common pitfalls of “date” vs. “date and time” columns.
By the time we’re done, you should have a firm grasp of the following terms:
- Primary key
- Foreign key
And a solid understanding of the following clauses:
- GROUP BY
- ORDER BY
Of course, it’s one thing to read these posts and another to play along with your own database.
Here’s what you’ll need to run the examples I use:
- SQL Server. All my examples work with SQL Server. (At Red Three, our focus is Microsoft.) If can’t use your company’s servers, you can download the free version.
- SQL Server Management Studio. You can use many tools to build queries. But SSMS is the standard for SQL people in the Microsoft world. Before, you used to have to install the database to get the management studio, but now there’s a standalone install.
- The Northwind database. The AdventureWorks database is more complex than Northwind, and I wanted to keep things simple for these examples. You can download Northwind here.
A Few Additional Notes
For some of my examples, I had to create a few new tables. I’m in the process of creating some sample documents that will include these new tables. Let us know if you’d like a copy. We’ll notify you when they’re ready.
I’m also planning to create examples using the sample data in NAV and GP. If you’d like a copy, let us know. We’ll notify you when they’re ready.
All the source code for the examples is also available. Contact us if you’d like the files.
Here are links to the parts of thttps://redthree.com/contact/he series published thus far:
SQL for Super Users Part 1: Basic Terminology
SQL for Super Users Part 2: SELECT Statements
SQL for Super Users Part 3: Adding Another Table Using JOINs
SQL for Super Users Part 4: The Multi-Column Join
SQL for Super Users Part 5: The Left Outer Join
SQL for Super Users Part 6: Aggregates and Having vs. Where