How to setup SQL Practice Problems database on any platform - works on Mac, Windows, Linux

Credits: SQL Practice Problems.

Based on the following publication:

Title: SQL Practice Problems: 57 beginning, intermediate, and advanced challenges for you to solve using a “learn-by-doing” approach
Edition: Kindle
Publication date: November 16, 2016
ASIN: B01N41VQFO

Why did I create this?

The original scripts shipped with the Kindle edition of the book I bought off Amazon was compatible with only Windows/SQL Server. I needed a way to run the scripts on my Mac. I came up with two solutions:

• Option 1: Port the scripts to work on MySQL.
• Option 2: Run Microsoft SQL Server as a Docker container.

Both the options are cross-platform!

Option 1: Setting up on Mac and MySQL

1. Login to MySQL

$ mysql -h localhost -P 3306 --protocol=tcp -u root -p

2. Re-create database

mysql> drop database if exists northwind;

mysql> create database northwind;

3. Import northwind-mysql.sql

mysql> source [/path/to/]northwind-mysql.sql;

You'll notice the tables and column names are in snake_case per MySQL convention.

Option 2: Setting up on Mac and Microsoft SQL Server

1. Install Docker Desktop for Mac

2. Start MS SQL Server as a Docker container

$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=@dmin!234' -p 1433:1433 --name mssql -h mssql -d mcr.microsoft.com/mssql/server

3. Note the credentials. User ID is SA. Password is @dmin!234.

4. Install Azure Data Studio for Mac

5. Set up the database in MS SQL Server:

   • Login to Azure Data Studeo
   • Launch 'New query' and run the following command:

     CREATE DATABASE Northwind

   • Refresh the tree on the left to reveal Northwind.
   • Launch 'New query' and run the contents of the file northwind-ms-sql-server.sql.
   • Refresh the tree again to reveal the tables created.