CUNY

Installing SQL Server Express in macOS #2

Francisco Olvera

This is an alternative to the previous post — Installing SQL Server Express in macOS.

INSTRUCTIONS:

  1. Open or sign in to your Docker ID (https://id.docker.com/login/).
  2. Install Docker Toolbox (https://docs.docker.com/toolbox/toolbox_install_mac/).
  3. Install Kitematic (https://kitematic.com/).
  4. Read the documentation (https://docs.docker.com/kitematic/userguide/).
  5. Download and run the SQL Server image from the Docker Hub using Kitematic (https://hub.docker.com/_/microsoft-mssql-server).

Installing SQL Server Express in macOS

Francisco Olvera

Instructions:

  1. Open `Terminal`. 
    Application/Utilities/Terminal.app
  2. Install the Homebrew package manager system. 
    /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
  3. Test Homebrew installation. 
    brew doctor
  4. Install Docker using Homebrew. 
    brew install docker
  5. Run Docker and download the latest package of SQL Server. 
    sudo docker pull mcr.microsoft.com/mssql/server:2017-latest
  6. Accept the EULA (End-User Licensing Agreement) and enter a `sa` (system administrator) password (position shown below as `<YourStrong!Passw0rd>`). This is the account that will run SQL Server using port 1433 to access container `sql1`. 
    sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' \
  -p 1433:1433 --name sql1 \
  -d mcr.microsoft.com/mssql/server:2017-latest
  7. View all containers created. You should see container `sql1`. 
    sudo docker ps -a
  8. Connect to SQL Server in container `sql1`. 
    sudo docker exec -it sql1 "bash"
  9. Connect to `sqlcmd` (SQL Command). 
    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourNewStrong!Passw0rd>'
  10. At this point, you can stop the execution of `sqlcmd`. 
    QUIT
  11. Install DBeaver Community Edition (CE).
    https://dbeaver.io/files/dbeaver-ce-latest-installer.pkg
  12. Connect to SQL Server using a client. In this tutorial, we will cover DBeaver.
  13. Run DBeaver.
  14. Select `Connect to database`.In the dialog box, select `SQL Server`.
    • Accept the default values.Host: `localhost`
    • Port: `1433`
    • Database/schema: `master`
    • Authentication: `SQL Server Authentication`
    • User name: `sa`
    • Password: `<YourStrong!Passw0rd>`
    • Save password locally: checked
    • Trust Server Certificate: checked
    • Show All Schemas: your choice, unchecked by default
    • Advanced Settings: `Network settings (SSH, SSL...)` & `Connection details (name, type...)`
    • Driver name: `MS SQL Server / Sql Server`
  15. At this point, you can test the connection or click the `Finish` button.

Resources

Database Administration Fundamentals (WS19SQL10004) – Day 4

Francisco Olvera

So far, we have covered how JOIN tables, built-in functions to manipulate strings, numbers and even convert numbers (currencies and dates) to strings, GROUP BY and ORDER BY clauses and the structure of a SELECT statement.

  SELECT table1.field1,
    table1.field2,
    ...
    table2.field1,
    table2.field2,
    ...
    table3.field1,
    table3.field2,
    ...

  FROM table1
  INNER|LEFT|RIGHT JOIN table2
    ON table1.shared_field1 = table2.shared_field1
    AND table1.shared_field2 = table2.shared_field2
  ...
  INNER|LEFT|RIGHT JOIN table3
    ON table1.shared_field1 = table3.shared_field1
    AND table1.shared_field2 = table3.shared_field2
  ...

  WHERE condition1
    AND|OR condition2
    AND|OR condition3
  ...

  GROUP BY table1.field1,
    table1.field2,
    ...
    table2.field1,
    table2.field2,
    ...
    table3.field1,
    table3.field2,
    ...

  ORDER BY
    table1.field1 ASC|DESC,
    table1.field2 ASC|DESC,
    ...
    table2.field1 ASC|DESC,
    table2.field2 ASC|DESC,
    ...
    table3.field1 ASC|DESC,
    table3.field2 ASC|DESC,
    ...

Download the class notes for day 4.