<commit_before>---
layout: page
title: An Introduction to SQL for Academics
comments: true
---

During my Physics PhD program, I really never understood the purpose of SQL and
similar [relational database management systems 
(RDBMs)](http://en.wikipedia.org/wiki/Relational_database_management_system).
And so as I began to prepare to transition to a career as a data scientist, I was
confused why so much data science was done inside of databases.

After all, physicists (and scientists more broadly) love to reinvent the wheel. 
Working in astrophysics,
I was used to all sorts of file formats like
[ROOT Trees](http://en.wikipedia.org/wiki/ROOT), 
[Fits Files](http://en.wikipedia.org/wiki/FITS),
[HDF5 Files](http://en.wikipedia.org/wiki/Hierarchical_Data_Format),
and [pandas DataFrames](http://en.wikipedia.org/wiki/Pandas_(software)),

What was the point of SQL databases? everything had to be these flat tables, and the syntax
to deal with theme seemes really archane!

Now that I have been a data scientist for half a year, I have grown to really love databases.
What they are designed to do well they do expetionally better than anything else.
And now that I am on the other side of the table 
helping other academics transition to become become data scientists, I see that
most of them have very similar confusion about the purpose and benefit of databases.

# The Need for Relational Database

Common use cases in business:
* Data sets which down fit into

Denormalized database.

I was used to 

# A Simple Example Of a Database

As a simple example of a relational database, supposed 
we want to describe a schema for a recepie book.

The first thing we would like to do is describe a
list of all the recipies.

| recipe_id |    recipe_name |        recipe_description |
| --------- | -------------- | ------------------------- |
|         0 |          Tacos |        Mom's famous Tacos |
|         1 |   Tomatoe Soup |      Homemade Tomato soup |
|         2 | Grilled Cheese | Delicious Cheese Sandwich |

But each recipie is built out of ingredients.

| ingredient_id | ingredient_name | ingredient_price |
| ------------- | --------------- | ---------------- |
|             0 |            Beef |                5 |
|             1 |         Lettuce |                1 |
|             1 |        Tomatoes |                2 |
|             1 |      Taco Shell |                2 |
|             1 |          Cheese |                3 |
|             1 |            Milk |                1 |
|             1 |           Bread |                2 |

  
Findally, we chould create a table to describe all of the ingredients in all of the tables:

| recipe_id | ingredient_id |
| --------- | ------------- |
|         0 |             0 |
|         0 |             1 |
|         0 |             2 |
|         0 |             3 |

Comming from a python background, my natural tendency is to believe that this
is totally overengineering the problem. Instead, I would imagine using
a hashtable to define this data:

```python
recipies = {
  "Tacos": { ingredients: ["Beef", "Lettuce", "Tomatoes", 
                           "Cheese", "Taco Shell" ], 
             description: "Mom's famous Tacos" },
  "Tomato Soup": { ingredients: [ "Tomatoes", "Milk" ], 
                   description: "Homemade Tomato soup" },
  "Grilled Cheese": { ingredients: [ "Cheese", "Bread" ],
                      description: "Delicious Cheese Sandwich" }
```
But there are several downsides of this approach.

The way to read

Other things you might want:
* A list of users in the system.
* A mapping of what users have eaten what recipies...

To get a list of all ingredients for the taco recipie by ingredient name:

```sql
SELECT ingredient_name 
FROM 
```
 
Some SQL queries
* How 

# Advanced SQL: 

* Indicies
* Terradata/Vertica for distributed databases.
* HIVE/Pig to run parallel Map/Reduce queries.

If you liked this post you can follow me on Twitter [@joshualande](http://twitter.com/joshualande).

# Relevant links

* <commit_msg>Updating blog post<commit_after>---
layout: page
title: SQL Basics for Data Scientists
comments: true
---

During my Physics PhD program, I really never understood the purpose of SQL and
similar [relational database management systems 
(RDBMs)](http://en.wikipedia.org/wiki/Relational_database_management_system).
And so as I prepared to transition to a career as a data scientist, I was
confused why so much data science was done inside of databases.

After all, physicists (and scientists more broadly) love to reinvent the wheel. 
Working in astrophysics, I was used to all sorts of file formats like
[ROOT Trees](http://en.wikipedia.org/wiki/ROOT), 
[Fits Files](http://en.wikipedia.org/wiki/FITS),
[HDF5 Files](http://en.wikipedia.org/wiki/Hierarchical_Data_Format),
and [pandas DataFrames](http://en.wikipedia.org/wiki/Pandas_(software). 
I wans't sure why I needed to learn SQL.

And besides, SQL seemed so limited. Everything had to be these flat tables, and the syntax
to deal with theme seemes really archane. I didn't get it.

Now that I have been a data scientist for half a year, I have grown to really love databases.
What they are designed to do well they do expetionally better than anything else.
And the use case for them in business and data science applications is almost limitless.

Now that I am on the other side of the table 
helping other academics transition to become become data scientists, I see that
most of them have very similar confusion about the purpose and benefit of these
databases. So my hope in this post is do describe
the benefits of an SQL database, go over the basic synax of SQL,

# A Simple Example Of an SQL Database

Before generalizing, it would be good to begin with a simple example.
The most important takeaway about SQL databases
is that a database is a collection of two-dimensional tables.
Each table has a fixed number of rows and columns. This may
seem limited, but as you will see later this simplification 
becomes incredibly powerful.

As a simple example of a relational database, supposed 
we want to describe a schema for a database of recipes.
The first thing we might want to do is to describe a list of all
the recipes. All recipes have a name which we call recipe_name and
they all have a description which we will call recipe_description.
In addition, we will associated a unique id for each recipe which
we will call recipe_id.

| recipe_id |    recipe_name |        recipe_description |
| --------- | -------------- | ------------------------- |
|         0 |          Tacos |        Mom's famous Tacos |
|         1 |   Tomatoe Soup |      Homemade Tomato soup |
|         2 | Grilled Cheese | Delicious Cheese Sandwich |

But each recipe is built out of ingredients, so 
we might want a table describing all of the ingredients 
that we can build food out of. For this simplified example,
we assume that all ingredients have a price which is stored
in the ingredient_price column.

| ingredient_id | ingredient_name | ingredient_price |
| ------------- | --------------- | ---------------- |
|             0 |            Beef |                5 |
|             1 |         Lettuce |                1 |
|             1 |        Tomatoes |                2 |
|             1 |      Taco Shell |                2 |
|             1 |          Cheese |                3 |
|             1 |            Milk |                1 |
|             1 |           Bread |                2 |

  
Finally, we need some way of listing all of the ingredients in each recipe.
Although we might naturally want to put this information into the recipe table,
we will see that it is advantageous to use a third table to store this information.
For every recipe 

| recipe_id | ingredient_id | amount |
| --------- | ------------- | ------ |
|         0 |             0 |      . |
|         0 |             1 |      . |
|         0 |             2 |      . |
|         0 |             3 |      . |

Of course, we could imagine that a real database, like the one
curated by [yummly.com](http://yummly.com), would have lots more information in it.
You might want more accurate list of steps involved in preparing the recipie.
But this simplified exmaple should get the point across.

## Similar python Implementation

Comming from a python background, my natural tendency is to believe that this
is totally overengineering the problem. Instead, I would imagine using
a hashtable to define this data:

```python
recipies = {
  "Tacos": { ingredients: ["Beef", "Lettuce", "Tomatoes", 
                           "Cheese", "Taco Shell" ], 
             description: "Mom's famous Tacos" },
  "Tomato Soup": { ingredients: [ "Tomatoes", "Milk" ], 
                   description: "Homemade Tomato soup" },
  "Grilled Cheese": { ingredients: [ "Cheese", "Bread" ],
                      description: "Delicious Cheese Sandwich" }
```

Although this seems simple, there are several similtations of this approach.
The first is that by organizing our data in this particular,

FIT DATA INSIDE MEMORY.


Other things you might want:
* A list of users in the system.
* A mapping of what users have eaten what recipies...


## Example SQL Queries

Given our recipe schema above, there are many kind of calculations we could imagine doing
on this databse.

```sql
SELECT ingredient_name 
FROM 
```
 
To get a list of all ingredients for the taco recipe by ingredient name:
Some SQL queries
* How 

# Advanced SQL: 

* Indicies
* Terradata/Vertica for distributed databases.
* HIVE/Pig to run parallel Map/Reduce queries.

# Wrapup 

Thats about it. If you can get get you mind around the idea of table normalization, as well as
as the benefits of joining tables together to produce more complicated queries, everything
else should be easy.

If you liked this post you can follow me on Twitter [@joshualande](http://twitter.com/joshualande).

# Relevant links

* 
and finally to work out a concrete exmaple which should get at the purpose of

# The Need for Relational Database

Common use cases in business:
* Data sets which down fit into

Denormalized database.

I was used to 
{'fraction_non_alphanumeric': 0.08660098522167488, 'fraction_numerical': 0.0051231527093596055, 'mean_word_length': 2.3938482113005684, 'pattern_counts': {'":': 6, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5885960', 'n_tokens_mistral': 2727, 'n_tokens_neox': 2602, 'n_words': 1294}