Publicly available SQL DBMS for teaching, workshops, etc


#1

What public SQL DBMS' do people use for classes or workshops? I found this in a posting by Jim Hester on GitHub, but I'm not clear whether it's legit to use it widely:

con <- dbConnect(odbc::odbc(),
driver = "libtdsodbc.so",
database = "AdventureWorks2012",
uid = "sqlfamily",
pwd = "sqlf@m1ly",
Server = "mhknbn2kdz.database.windows.net",
port = 1433)

I know SQLite would be a possibility, but are there others?


#2

How about PostgreSQL?


#3

Are you suggesting that a class or workshop include the installation of PostgressSQL? Or is there a publicly accessible database with sample data that can be used in exercises?


#4

I've never seen that connection before, so I can't comment on how available it is.

The source data in the AdventureWorks database are available here, though.

A question from me - how familiar are your workshop participants with setting up things like DSNs, especially on Mac/Linux (where it can be fiddly)?


#5

It might be worth setting up a database on AWS or Google Cloud or Azure or somewhere (I am partial to PostgreSQL). It is fairly straightforward to do the setup (lots of good docs out there) and then you are in control. Further, as long as your data size is reasonable, you will probably land well within the free tier and enjoy your own class-focused database for free!

There are some open datasets out there, on AWS for instance: https://registry.opendata.aws/

They vary in how they are accessed, though.


#6

I might have misunderstood your question, I was just suggesting PostgreSQL as a publicly available DBMS.
But in answer to these questions, I guess it depends on the workshop. What I can say is that:

  1. I've always found installing PostgreSQL pretty easy. If you want an easy interface for querying a PostgreSQL database I can recommend Postico for Mac.
  2. Uploading a dataset and creating a database, tables and schema is again pretty easy in PostgreSQL (there is loads of documentation out there), should you have a specific open dataset that you want to use in your workshop.
  3. You can give people access to a database by providing them with details of host, password, port, username and database name. This is quite a common method for testing SQL proficiency in job interviews/assessments.
  4. I've never done it myself, but a quick Google suggests there are sample databases for PostgreSQL.

#7

you can set up your own server pretty easily with MS Hosted Databases:

If you're open to Postgres, Amazon Redshift is a very good option:
https://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html

Keep in mind that not all postgres functions are available in Redshift, but it's a very functional subset.