SQL Workshop 5/20

Data Science at UCSB and FACTOR (Female Actuarial Organization)

Contributors

• Raul Eulogio
• Ashley Samudio
• Jun Seo Park

Relevant Links:

• Data Science at UCSB
• FACTOR at UCSB
• Inertia7

Downloading MySQL Workbench

• Macs
• Windows - Download MySQL Installer and run
  • Setup Type: select "Developer Default"
  • Check Requirements: Click "Execute" and run through all the installations (there will be a lot)
    • Once everything finishes, click "Next"; ignore the message that you need another Python installation, this is not necessary
  • Proceed with the rest of the installation
    • If major issues occur, cancel the installation, re-run MySQL Installer and remove all packages, then try to reinstaller beginning with "Developer Default"

Goal

For this workshop, we will focus on basic functionalities within a MySQL context. As well as some theory with respect to Data Warehousing to give context to attendees the usefulness of SQL data storage.

Process

This stucture will be similar to the Python Workshop, except instead of being handed a dataset you will be handed a database.

Presentation:

• Data Warehousing
• SQL Syntax
• Student Collaboration

Databases:

• Northwind Database
• World Database
• Iris Database

Step 1: Repository

For this step you will create a GitHub repository with the following requirements:

• README detailing
  • List of contributors
  • Description of data set
    • Along with any process taken to retrieve the data set
    • Link to the source of data set
  • The problem you are trying to solve (i.e. the instructions on the file for your given data set)

STEP 2: Do Analysis

Simple as that do analysis on the database you chose to use (Goes without saying that you should be able to successfully load the database into the MySQL Workbench). As soon as I'm done lecturing we will branch off into teams and you will work with a team to make some interesting observations.

• Make insightful comments about queries (practice exercises included in Github repository)
• Use R or Python to go beyond (I hope most people from DS will do this.)
• Upload all relevant files to GitHub and if time permits we can do a 2 minute showcase of people's results.

Download:

• Macs
• Windows

Resources

• Ravi's Data Warehousing Notes
• w3schools SQL Tutorial
• Tom Jewett's Database Design Tutorial
• TutorialsPoint's Data Warehousing Tutorial
• Concept Hierarchies Tutorial And Examples
• SearchDataManagement's Definition of OLAP
• Managing Database Objects in SQL