Databases and SQL

NIST Telework Lessons

April 2020

Instructors: Dilip Banerjee, Chandler Becker, Gretchen Greene, Josh Taillon

Helpers: TBD

General Information

Software Carpentry aims to help researchers get their work done in less time and with less pain by teaching them basic research computing skills. This hands-on workshop will cover basic concepts and tools, including program design, version control, data management, and task automation. Participants will be encouraged to help one another and to apply what they have learned to their own research problems.

For more information on what we teach and why, please see our paper "Best Practices for Scientific Computing".

Who: The course is aimed at graduate students and other researchers. You don't need to have any previous knowledge of the tools that will be presented at the workshop.

Where: Online (as indicated by group leader)

When: April 2020

How: Although specific implementations will vary by cohort and instructor, groups will generally meet virtually via a web conferencing tool, and the instructor will present with a shared screen to a group of no more than 10 learners. Learners will follow along, running commands on their own systems and can interact with the instructor and helpers throughout the process.

We appreciate your patience and understanding as moving these sessions to an online-only format is a first-time learning experience for us and you alike, and hiccups in the process are certain to arise. We will try our hardest to ensure a good learning experience regardless of the circumstances, but feel free to let us know if you have any suggestions for improvements.

Requirements: Participants must have a computer with a Mac, Linux, or Windows operating system (not a tablet, iPad, etc.) that they have administrative privileges on. They should have a few specific software packages installed (listed below). If this is not possible, we can also provide a remote virtual desktop that can be used for the session (contact your instructor for details), but we prefer that you install the software on your NIST machine as a learning exercise.

Due to limited screen "real estate", you may find it easier if you have multiple monitors: one for the instructor's display and webinar, and another on which to evaluate commands and practice in realtime. If this is not possible, another solution is to use a second device (such as a tablet or another perhaps personal laptop) to login to the web conference and view the instructor, while you follow along interactively on your "main" computer.

Accessibility: We are committed to making this workshop accessible to everybody. As the workshop will be conducted entirely online, you will be able to participate from the comfort of your teleworking location, but please let your instructor know in advance if we can make any accommodations to help make learning easier for you.

Contact: Please email dilip.banerjee@nist.gov , chandler.becker@nist.gov , gretchen.greene@nist.gov or joshua.taillon@nist.gov for more information.


Code of Conduct

Everyone who participates in Carpentries activities is required to conform to the Code of Conduct.This document also outlines how to report an incident if needed.


Surveys

Please be sure to complete these surveys before and after the workshop.

Pre-workshop Survey

Post-workshop Survey


Schedule

The schedule is planned for about 6 hours total of instruction for this topic. The detailed schedule will be determined by each instructor and the learners in their cohort, and communicated separately. We will be following the curriculum of the Databases and SQL carpentry course, adapted for online teaching.


Syllabus

This lesson assumes some familiarity with Bash Shell.

Managing Data with SQL

  • Reading and Sorting Data
  • Filtering with where
  • Calculating New Values on the Fly
  • Handling Missing Values
  • Combining Values Using Aggregation
  • Combining Information From Multiple Tables Using join
  • Creating, Modifying, and Deleting Data
  • Programming with Databases
  • Reference...

Setup

To participate in a Software Carpentry workshop, you will need access to the software described below. In addition, you will need an up-to-date web browser.

We maintain a list of common issues that occur during installation as a reference for instructors that may be useful on the Configuration Problems and Solutions wiki page.

The Bash Shell

Bash is a commonly-used shell that gives you the power to do simple tasks more quickly.

Video Tutorial
  1. Download the Git for Windows installer.
  2. Run the installer and follow the steps below:
    1. Click on "Next" four times (two times if you've previously installed Git). You don't need to change anything in the Information, location, components, and start menu screens.
    2. From the dropdown menu select "Use the nano editor by default" and click on "Next".
    3. Ensure that "Git from the command line and also from 3rd-party software" is selected and click on "Next". (If you don't do this Git Bash will not work properly, requiring you to remove the Git Bash installation, re-run the installer and to select the "Git from the command line and also from 3rd-party software" option.)
    4. Ensure that "Use the native Windows Secure Channel library" is selected and click on "Next".
    5. Ensure that "Checkout Windows-style, commit Unix-style line endings" is selected and click on "Next".
    6. Ensure that "Use Windows' default console window" is selected and click on "Next".
    7. Ensure that "Enable file system caching" and "Enable Git Credential Manager" are selected and click on "Next".
    8. Click on "Install".
    9. Click on "Finish".
  3. If your "HOME" environment variable is not set (or you don't know what this is):
    1. Open command prompt (Open Start Menu then type cmd and press [Enter])
    2. Type the following line into the command prompt window exactly as shown:

      setx HOME "%USERPROFILE%"

    3. Press [Enter], you should see SUCCESS: Specified value was saved.
    4. Quit command prompt by typing exit then pressing [Enter]

This will provide you with both Git and Bash in the Git Bash program.

The default shell in some versions of macOS is Bash, and Bash is available in all versions, so no need to install anything. You access Bash from the Terminal (found in /Applications/Utilities). See the Git installation video tutorial for an example on how to open the Terminal. You may want to keep Terminal in your dock for this workshop.

To see if your default shell is Bash type echo $SHELL in Terminal and press the enter/return key. If the message printed does not end with '/bash' then your default is something else and you can run Bash by typing bash.

The default shell is usually Bash and there is usually no need to install anything.

To see if your default shell is Bash type echo $SHELL in a terminal and press the enter/return key. If the message printed does not end with '/bash' then your default is something else and you can run Bash by typing bash.

Text Editor

When you're writing code, it's nice to have a text editor that is optimized for writing code, with features like automatic color-coding of key words. The default text editor on macOS and Linux is usually set to Vim, which is not famous for being intuitive. If you accidentally find yourself stuck in it, hit the Esc key, followed by :+Q+! (colon, lower-case 'q', exclamation mark), then hitting Return to return to the shell.

nano is a basic terminal-based editor and the default that instructors use in the workshop. It is installed along with Git.

Other free graphical editors that you can use are Visual Studio Code, Notepad++, or Sublime Text. Be aware that you must add its installation directory to your system path. Please ask your instructor to help you do this.

nano is a basic terminal-based editor and the default that instructors use in the workshop. See the Git installation video tutorial for an example on how to open nano. It should be pre-installed.

Other free graphical editors that you can use are Visual Studio Code, BBEdit, or Sublime Text.

nano is a basic terminal-based editor and the default that instructors use in the workshop. It should be pre-installed.

Other free graphical editors that you can use are Visual Studio Code, Kate, or Sublime Text.

SQLite

SQL is a specialized programming language used with databases. We use a simple database manager called SQLite in our lessons.

  • Run git-bash from the start menu
  • Copy the following curl https://pages.nist.gov/2020-04-10-nist-sql/getsql.sh | bash
  • Paste it into the window that git bash opened. If you're unsure, ask an instructor for help
  • You should see something like 3.27.2 2019-02-25 16:06:06 ...

If you want to do this manually, download sqlite3, make a bin directory in the user's home directory, unzip sqlite3, move it into the bin directory, and then add the bin directory to the path.

SQLite comes pre-installed on macOS.

SQLite comes pre-installed on Linux.

  • In case of problems: register for an account at Python Anywhere
  • Download survey.db
  • Click on files and upload survey.db
  • Click on dashboard and Choose new console $ bash

If you installed Anaconda, it also has a copy of SQLite without support to readline. Instructors will provide a workaround for it if needed.