SELECT CAST(AVG(songs.year) as int) as avg_year FROM songs

-- | avg_year |
-- |----------|
-- | 934      |

SELECT songs.year as median_year
FROM songs 
ORDER BY songs.year 
LIMIT 1 
OFFSET (SELECT COUNT(*) FROM songs) / 2

-- | median_year |
-- |-------------|
-- | 0           |

SELECT 
    songs.year,
    COUNT(*) as count
FROM songs
GROUP BY songs.year
ORDER BY COUNT(*) DESC
LIMIT 1

-- | year | count |
-- |------|-------|
-- | 0    | 5320  |

SELECT 
    MIN(songs.year) as min_year,
    MAX(songs.year) as max_year
FROM
    songs

-- | min_year | max_year |
-- |----------|----------|
-- | 0        | 2010     |

SELECT 
    songs.year,
    COUNT(*) songs_count
FROM songs
GROUP BY songs.year
ORDER BY songs.year ASC

-- | year | song_count |
-- |------|------------|
-- | 0    | 5320       |
-- | 1926 | 2          |
-- | 1927 | 3          |
-- | ...  | ...        |
-- | 2009 | 250        |
-- | 2010 | 64         |

Structured Query Language (forever and always referred to henceforth as SQL, and usually pronounced "sequel") is the language for managing and querying data in relational database management systems (RDBMS).

So intertwined are the terms SQL and RDBMS that they are often conflated, sometimes by the uninitiated, but often simply out of convenience, and the term SQL is used adversarially to distignuish relational systems from non-relational database systems, which are often categorized by the umbrella term "NoSQL." Yet, SQL skills aren't wasted on non-RDBMS systems; the top data processing frameworks all have some implementation of SQL that sits atop their architecture, including Apache Big Data behemoths Spark andHadoop.

Together with R and Python, SQL forms the triumvirate of data science programming languages (liberal usage of the term "programming languages"); in fact, the most recent KDnuggets data science and analyticssoftware poll results indicate that these are also the 3 most-used tools by respondents, when all software is considered.

Clearly, SQL is important in data science. As such, this post aims to take a reader from SQL newbie to competent practitioner in a short time, using freely-available online resources. Lots of such resources exist on the internet, but mapping out a path from start to finish, using items which complement each other, is not always as straightforward as it may seem. Hopefully this post can be of assistance in this manner.

Step 1: Relational Database Basics

First off, since SQL is used to manage and query data in relational databases, having an understanding of what, exactly, a relational database is would be a good start.

Jennifer Widom is a renowned database researcher and professor at Stanford. She also put together one of the very first MOOCs on Coursera. Videos from this course are a good place to start:

• Relational Databases - Relational Model
• Relational Databases - Querying

A good introductory read on the relational database model is presented by UMass Boston's Dan Simovici here:

• Database Management Notes Chapter 1 - Introduction (pages 1-7)

Extra credit: For a better theoretical understanding of the foundations upon which relational database systems are built, have a look at Widom's videos on relational algebra:

• Relational Algebra - Select, Project, Join
• Relational Algebra - Set Operators, Renaming, Notation

Step 2: SQL Overview

Now that we're all familiar with the relational paradigm, we shift focus to SQL.

First, get a high-level overview, again from Widom:

• SQL- Introduction to SQL

Read the first few pages of Simovici's SQL notes, or until it stops making intuitive sense:

• Database Management Notes Chapter 5 - SQL

For a great extended introductory overview of basic SQL commands, watch the following video from Khan Academy:

• Basic SQL Commands

Once finished with the video, the following is a good treatment of SQL commands, with explanations, and can also serve as a useful reference moving forward, so keep it handy:

• SQL Examples

Finally, in preparation of the of next step, get an SQL environment up and running. You may not want to enter every SQL statement you encounter, but having an SQL interpreter up and running just makes sense. I suggest installing SQLite locally; it is a simple, but capable, SQL installation:

• Get SQLite
• SQLite Introductory Tutorial

Step 3: Selecting, Inserting, Updating

While SQL can be used to perform a number of varied data management tasks, querying databases with SELECT, inserting records with INSERT, and updating existing records with UPDATE are some of the most heavily-used commands, and are good places to get started in practical SQL. Read and go through the following preliminary exercises:

Select

• Selecting Data (from SQL Course)
• SELECT Basics (from SQL Zoo)
• SQL SELECT (from Mode Analytics)

Insert

• Inserting Data (from SQL Course)
• INSERT Values (from SQL Zoo)
• INSERT Select (from SQL Zoo)

Update

• Updating Records (from SQL Course)
• UPDATE (from SQL Zoo)

This tutorial covers SQL basics and beginner to intermediate queries, and is a solid review of what we have seen to this point, and then some. It is presented from a MySQL point of view, but nothing in here is exclusive to that platform's SQL implementation.

• MySQL Tutorial 1: Overview, Tables, Queries (from arachnoid.com)

• Step 4: Creating, Dropping, Deleting

  Our second set of commands include those used to CREATE and DROP tables, as well as to DELETE records. With an understanding of this growing collection of commands, suddenly much of what could be referred to asregular data management and query is attainable (with practice, of course).

  Create

  • Creating Tables (from SQL Course)
  • CREATE Table (from SQL Zoo)

  Drop

  • Drop a Table (from SQL Course)
  • DROP Table (from SQL Zoo)

  Delete

  • Deleting Records (from SQL Course)
  • DELETE (from SQL Zoo)

  Step 5: Views and Joins

  On to some slightly more advanced SQL topics. First, we have a look at views, which can be thoughts of as virtual tables populated by the results of queries, useful for a number of different scenarios including application development, data security, and eased data sharing.

  First, get the details on what views are:

  • Using Views (from Tutorials Point)

  For beginner SQL practitioners in the data science realm, I would call views a "nice to have." Since the focus is likely to be more on data exploration, I would say that the next topic, joins, is a "must have."

  Read about what joins are, and why they are important (and get some examples):

  • What Are Joins? (from sql-joins.com)
  • SQL JOINS (from Mode Analytics)

  Joins come in different flavors, and likely one of the more complex topics you will cover while learning SQL is getting them straight. That's really more of a testament to the ease of SQL than the actual difficulty of learning about joins.

  Watch a video explaining inner joins, then check out a video on outer and cross joins:

  • Inner Joins
  • Outer & Cross Joins

  
  

  See this visual representation of SQL joins:

  • Visual Representation of SQL Joins (from Code Project)

  Finally, this tutorial reviews both joins and views:

  • MySQL Tutorial 2: Views and Joins (from arachnoid.com)

  Step 6: SQL for Data Science

  OK, so you've made some headway with learning SQL. You can query some data, create and manage some tables, make a view if you had to, and even use joins in some more complex querying. But why are you learning all this again? For data science, right? Let's take a bit of a break from the technical to get an idea of this very topic.

  Here are a couple of discussions regarding what SQL can be used for in data science:

  • SQL For Data Science (from yhat)
  • How do data scientists use SQL? (from Quora)

  Step 7: SQL Integration with Python, R

  Often we will find that SQL is embedded in, or called from within, software written in other programming languages as part of a larger system. For example, in web development you may find PHP or Ruby or some other language making calls to a database via SQL to input, modify, or retrieve application-related data. In data science, you may see SQL being called as part of some application written in, perhaps, Python or R. To that end, having an understanding of how these languages play with SQL is not a bad idea.

  Python:

  To gain an understanding of how Python and SQL can work together, readSebastian Raschka's fantastic and detailed post on using SQLite in Python:

  • SQLite in Python Tutorial

  R:

  Here are a pair of resources for achieving R and SQL integration, which approach the topic from different directions:

  • SQL and R (from Simple Talk)
  • RSQLite

  Further

  If you feel like a relentless regimen of reading about an SQL topic and following up with exercises, I recommend going through the following (freely-available) book:

  • Learn SQL the Hard Way, by Zed A. Shaw