Want to learn SQL?

HIFIS is stored in a database called Microsoft SQL Server, which is a database management system. Its “native language,” if you want to think about it that way, is called SQL. SQL (pronounced either “ess queue ell” or “sequel,” both are used) is a programming language used to communicate with databases. (It actually stands for Structured Query Language, but no one calls it that.)

For example, I might say SELECT * FROM HIFIS_Clients - that’s a SQL statement or “query,” and it’s basically asking the database to give me all the data from the HIFIS table HIFIS_Clients.

Now, you don’t need to actually know SQL to be able to pull data out of HIFIS. If you’re using a reporting tool like Crystal Reports, or Power BI, or Tableau, the interface tends to be point-and-click or drag-and-drop. Click on the tables you want, then drag the fields you want, then click the filter button to get only the data you want, and voila, the data appears! Because these reporting tools exist, you don’t need to know SQL.

Fun fact: did you know that even if you don’t think you’re using SQL, you probably are? Next time you’re working in Crystal Reports, go to Database > Show SQL Query… and you’ll see the SQL code that Crystal Reports is using to communicate with the HIFIS database. As I mentioned, SQL is HIFIS’ native language, so anything that wants to communicate with it needs to use SQL to do so.

That also means that it’s more efficient for you to communicate with the database using SQL. Knowing SQL means you have more finesse, more ability to communicate the nuances of what you need, and avoid pulling in unnecessary data that would bog down your query.

Limitations of Reporting Tools

Sometimes, you may have come across some kind of limitation. You’re an expert in the reporting tool you’re using, but it just won’t let you manipulate the data in quite the way you need to.

In Crystal Reports, one such limitation is getting a summary of a summary, which Crystal is really not happy about letting you do. What do I mean by “a summary of a summary”? Here’s a question I might get asked: on average, how long do people spend in my shelter over the course of their lives? It doesn’t seem that complicated. But here’s what we need to do.

• The data is stored with a new record for every Stay. So for every Stay, we need to calculate the difference between the Date Start and the Date End. Let’s call this Stay Duration for future reference.

• Next, we need to do a SUM for each Client. So for example, you could group by Client and do a SUM summary of Stay Duration in the Group Footer.

• But now we want an overall AVERAGE. So we need an AVERAGE of the SUM per Client of Stay Duration.

It’s possible but very clunky to get that value once in your report, in Crystal Reports, but if you wanted the average broken down by Service Provider or by Gender or by Program or by Indigenous Identity or anything else… good luck. Crystal Reports simply does not have the native tools to let you do that.

SQL to the Rescue

As mentioned above, you need to be able to provide more nuance than your reporting tool allows, and Crystal Reports allows you to do that.

With SQL, you’d manipulate the data before you loaded into a report. One way to approach the above problem is to use a SQL query that calculates the SUM of Stay Duration per client and gives you a dataset with a record for each client (as opposed to a record for each stay). Then you can bring that data into Crystal Reports and there’s now a database field that gives you the summary (SUM of Stay Duration) that you want to summarize (AVERAGE).

You do have the option to add SQL queries inside either Power BI dashboards or Crystal Reports files - I don’t have as much experience with Tableau, unfortunately (if you know the answer, please let me know!). Then once you’ve imported that query, it behaves essentially the same as any other table or view.

It should be pretty clear by now, if you’ve been following along, that SQL is not a tool for beginners. If you are just dabbling in pulling data out of HIFIS, SQL is not for you. Use one of the reporting tools - Crystal Reports, Power BI, or Tableau - and practice with it until you get really good. And once you’re very proficient with your chosen tool, you may start to notice the limitations. That’s when you might be interested in learning SQL and adding it as a tool to your toolkit.

So how do I learn SQL?

Good question. Some people have a degree in computer science or programming, and they learn SQL through their university or college studies. But if that doesn’t describe you, it can be difficult to know where to start!

When I decided to learn SQL, I called around the three different post-secondary institutions in Kingston and they all basically told me I was welcome to enroll as a full-time student but if I didn’t want to do that, they weren’t interested in offering courses to the general public.

Ivory tower, amiright?

You could, of course, get a book like SQL For Dummies, and slog through it on your own. But who has time for that? And it’s not hands on by any stretch of the imagination.

And then, my mother (yes, my mother of all people! She is in the middle of doing her PhD) told me about DataCamp. DataCamp is a very interesting software platform that’s designed to teach coding skills with interactive assignments and immediate feedback.

DataCamp offers a variety of different courses for different computer skills, but here’s the relevant one for right now:

SQL Fundamentals Learning Track

The above is an affiliate link, but I wouldn’t bother posting it if I didn’t genuinely think that this is the best way to learn SQL that I know of. So if you are interested in learning SQL but don’t know where to begin, why not check out DataCamp?