Learning Relational Databases? Install Linux SQL or Bootcamp/VM with MS Access?

I’m looking for a slight career change where I’d become a business intelligence report writer responsible for writing SQL queries for my corporation’s proprietary database. I understand the basic concepts of relational databases but I lack hands on experience writing queries to do things like joining tables to get the desired output.

The main two tools used in my industry are Crystal Reports and Tableau. I’ve been turned down for job transfers in my company due to lack of a background in relational databases. I know I’d be really good at it if they gave me a chance! So I want to take initiative and install some software on my home Mac, create my own databases that are similar to what we use at work, and play around with creating my own SQL queries.

Friends I talk to that worked in his job space suggest Microsoft Access. It seems counterintuitive to me because no one actually uses Microsoft Access in my field. But it’s the advice I’ve received. I was told if I can get good at Access, the skillset will transfer forward to SQL. I asked why I shouldn’t just install a MySQL and was told I’d have to spend a lot of time figuring out how to install the software and administer the database, whereas Access would be plug and play.

Of course, my friends who gave me this advice all use Windows. So now I’m stuck trying to figure out how to get Access on my Mac. My two options seem to be bootcamp or a VM such as Virtual Box, Parallels, or VM Ware and then buy the PC-version only of MS Access. I do not like software subscriptions and already own standalone Microsoft Office (except for Access). MS sells Access for $129 on their website. I dislike that it’s “Access 2016” and perhaps I’m paying full price for 3 year old software that’s being updated soon, but that’s just the price of doing business as someone who refuses a subscription model.

I’m open to just going a Linux SQL install even though my friends suggested Access. The “simplicity” of using Access over MySQL seems lost in the need to spend $500+ total on a Windows 10 license plus Access plus a VM and the time to install Windows, figure out privacy settings, updates, etc. I could probably just install a SQL server on my machine in less time for free, but I’ve never done it and am not sure where to start.

Regarding Tableau, I’d like to learn how to use, but it seems like the only option is a $70/month license, which I cannot afford to buy just for the purpose of learning how to use it. I can probably find some videos online, although that doesn’t seem to be enough to tell my current or future prospective employer that I know how it works because I watched videos.

Regarding Crystal Reports, I think they are just a form of SQL so if I get experience writing queries, then I can probably handle crystal reports.

What advice can Mac-specific folks on here provide to help me? I understand the potential folly in expecting to get someone to give me a job because I taught myself applications at home. But it’s something I am genuinely interested in for the sake of learning it.

If you want to learn to work with a relational database, maybe consider installing PostgreSQL. It’s a very mature, widely used, highly regarded system and available via Homebrew and MacPorts.

For basic SQL, I think that SQLite comes with macOS and would let you get started without having to install anything.

2 Likes

Wow I do not agree with that advice at all. I’ve been in the business intelligence (BI) field for a couple of decades and currently focus on Tableau but I’ve spent years with SQL, Crystal and other tools that power our industry.

SQL

So glad to hear you’re focused on this first. It is fundamental and a lot of folks try to skip it. You can do that but you’ll be so much better off in the long run, no matter what analysis/reporting/viz tools you use. Since SQL’s syntax is mostly standard across relational dbs, you don’t really need to worry about the particular db you start out with for learning it. (Keep focused on learning SQL for data analysis, not database management. You can learn to be a DBA if you want but most analyst positions do not require that skillset. You can always add later. Also, it IS fairly db-specific so it pays to hold off committing.)

On macOS SQLite is easy to get started with because its file-based, not server-based (like MySQL or Postgres). And unlike Access, which is also file-based, its open source/free. There are many free client options you can use to query against a SQLite db/file:

SQLite Official Tools (cmd-line)
SQLite Studio
SQLite DB Browser
SQLite VS Code Extension - VS Code is a great IDE on macOS

Setapp also has several SQLite clients (e.g. Base) if you already pay for a subscription.

Tableau

It is awesome that Tableau has a macOS client (and iOS/iPadOS too). One of the only BI vendors with a native content authoring experience on the Mac. As you probably know, you can download the commercial version of Tableau Desktop for a 14-day trial, but you can also download the Public version of Tableau Desktop which is free to use for learning.

Let me know if you need more info or if this brings up other questions. Happy to help.

5 Likes

If you download MAMP (the free version is fine) then with about 3 clicks you can be in the phpMyAdmin page and work with MySQL.

I’d also recommend MySQL Workbench, it’s useful for DB design.

1 Like

In addition to the good advice already given, I suggest looking for online courses such as from Udemy or Lynda.
A course will give you a structured way of learning the material. Many provide certificates of completion that would be something tangible to take to your employer.

Go to the w3schools website for a good beginners tutorial https://www.w3schools.com/sql/

I also use MAMP as a development environment, it’s an excellent way to get up and running in no time.

I find Postico to be a beautiful editor/viewer for Postgres dbs. If you use it with Postgres App you also don’t have to manage however Postgres works, just that it is on.

I second @ACautionaryTale and say you can get a long way just using SQLite. I would skip Access and if you need to go beyond what SQLite can tach you go for MySQL or or PostgreSQL.

To give you some info on what tools use SQLIte, Quicken stores all transactions in an SQLite database. LightRoom is also a massive SQLite database. So while it’s got “Lite” in the name it can actually be a very powerful system.

As for learning, when I started LambTracker I had never used any relational database system at all, I came from a hierarchical DB world. I found the following books most useful to me:

Beginning Database Design
and
Beginning SQL Queries

Both books were easy to read and follow. In my case the examples are also biology and farming related so they made more sense to me.

If you want to take a look at some rather complex SQLite Queries I use in LambTracker you might look at my GitLab repository

LambTracker Queries

The one Overall list new database is a rather complex one that can teach you a lot about how to write the queries.

Tools I use include an old rev of Firefox and the old legacy SQLite Manager extension. It’s a quick way to edit and manipulate the SQLite database. The stand alone program SQLite Manager is also good but I find myself going back to the simple Firefox extension all the time.

If you want a complex database to play with and take a look at I’d be glad to send you a copy of my LambTracker database with current info in it. Right now I have 1538 sheep in it and 580 people. I’m in the process of adding another 12K or so sheep records so it will be growing a lot over the next few days/weeks but you are welcome to getting it at any stage if you want. Right now the database size is only about 9 MB so not that big even though it is complex. My largest table has over 17K records in it. Currently there are 85 separate database tables in it but some are Android specific. 77 of them are my real database tables that contain the actual sheep data.

For me, learning is a lot easier with a working example of something complex. I get bored with most tutorials that start with the equivalent of the “hello world” program. I prefer to have a problem I am trying to solve and then jump in even if it is off the deep end. I may take a while to surface that way but I find I retain what I learn much better too.

Feel free to PM me if you want more info. Heck, if you want some real life examples to play with I can send you my not yet working queries to edit or some that I haven’t even started working on yet :wink:

edited to fix the book links

There are a number of SQL servers you could install on your Mac. I use MySQL for a production application (in use for a decade or so). Recommended. Also, as mentioned by others, MySQL Workbench to manage the server, databases, tables, and practice with Queries on those tables. All free. And Linux not needed. Versions of these products work on Linux, OSX, and Windows. They also provide command-line tools which you can run in a terminal.

Also mentioned below is PostgreSQL which is also free of cost and they have an equivalent tool as MySQL Workbench (but I forget what it’s called).

Doing MySQL and/or PostgreSQL will be free for you and give you some learnings which are applicable for use on Oracle and Microsoft’s servers. Very similar, including the command line tools.

While I also have been a long-time Microsoft Access user (part of the mentioned production system), I don’t think that is a good idea based on what you say. (I run Access on Windows 10 which runs inside of Parallels and connect to the database runnining in OSX via ODBC in Windows. It’s a legacy app which I don’t want to spend $ upgrading to something else. My app relies on Access for user-interface forms only. All queries, reports, automation is in Python/Django/Pandas accessing the MySQL database on the Mac.

Also, if you aren’t aware, https://stackexchange.com/ is a great resource for finding answers to questions that might come up.

Access to learn SQL? Just…no.

You can install:

  • mySQL
  • PostgreSQL
  • mariaDB

No need for VMs or Windows. I have a Setapp subscription, which includes SQLPro Studio and TablePlus.

2 Likes

Someone mentioned Lynda as a source for training videos. Check with your local library. I get access to Lynda.com via my library at no additional cost to me. Just checked and they have a number of SQL courses.

1 Like