Advanced SQL for Data Analysis

Presenter: Adolfo De Unanue

Level: Advanced (previous SQL experience required)

Are you familiar with using SQL and databasesn and want to learn advanced SQL? You can do selects, joins, group bys but are envious of SQL ninjas who can write stored procedures, windowing functions, do text analysis, and deal with json inside sql?  Then this session is for you! We’ll cover advanced SQL for data analysis inside a database (CTEs, Windowing functions, datawarehousing functions, text analysis, json).

This workshop will require the use of a computer so you should bring one.  We recommend installing some software before the conference (or if you have psql, that should be enough):

Windows:

Mac:

  • Java 8
  • If you use homebrew (recommended), install with `brew install Caskroom/cask/java`
  • You can also download the software at http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
  • dBeaver Community Edition
    •  If you use homebrew, install with `brew cask install dbeaver`
    • Or you can also download the software here: https://dbeaver.jkiss.org/download/

You can test your setup before the conference:
1. Open dBeaver
2. Choose “new connection” (a button in the top-left corner or an option under “database” at the top)
3. Choose “PostgreSQL”
4. Here’s the connection information:

  1. host: dssg-conference.dssg.io
  2. database: dssg_conference
  3. user: attendee
  4. password: dssg_conference_2017
  5. Choose “test connection.” It might prompt you to download Postgres drivers. Please do so. If you successfully connected, choose “next” and then “finish.”