MODULE 1: what the hell is sql?

THE THEFT OF BARAcK OBAMA's portrait

The Crime

On February 7th 2016 the famous painting of the the portrait of the 44th President of the United States was stolen from the National Portrait Gallery in Washington DC.  The artist, Kehinde Wiley unveiled the painting only a week before the the date of the theft.  The thieves have left no trace and the FBI needs your help to find who the criminals.  Your only lead is access to NYC data records, phone records like text messages and phone numbers, flight data and bank records.  You have your tenacity and SQL as your best tools to solve the case.  Let's solve this crime!

 

The Backdrop

OK, so we have the phone records, flight data and bank records, now what?  How do we start looking at it.  

A relational database (RDBMS)  is a type of software we will use to look at the data.  We can think of a RDBMS as the house for our database.  Inside this house our data is organized, much like an organized closet, in containers called "Tables".  A table is nothing more than columns and rows that hold pieces of our data.  Think about an Excel spreadsheet (columns and rows).  

Each table in a relational database contains rows (records) and columns (fields).  A table can be visualized as a matrix of rows and columns, where each intersection of a row and column contains a specific value. Think, Excel spreadsheet!  

Now that we know a bit more about where data is housed, how do we get it out of it's house?  The standard way to access data from a relational database is through an SQL (Structured Query Language) query. SQL is the language used to play with data, it can create, modify, and delete tables, as well as select, insert, and delete data from existing tables.  

 

What tha SQL?

SQL, is an acronym for Structured Query Language and can be pronounced as either "sequel" or "S-Q-L."  It is a query language used for accessing and modifying information in a database.  The language was first created by IBM in 1975 and was called SEQUEL (pronounced: see-quel) for "Structured English Query Language."

SQL is a language, when spoken, allows us to discover insight about any database of data.  We will use it as our main tool to to solve our crime.   

 

Microsoft SQL Server

Microsoft SQL Server is the RDBMS software we will be using to conduct our investigation.  It is where we will be composing our SQL statements to gain insight (query) about our data.  SQL Server is just one of the RDBMS that are available in.  SQLite, MySQL, PostgreSQL, Oracle are examples of some others.   We are choosing Microsoft SQL Server because it is on of the most popular database software being used today.  

Here are some fun facts about SQL Server:

  1. Microsoft has released 11 versions of SQL Server between 1995 and 2017.
  2. SQL Server is now an enterprise-class relational DBMS that competes with Oracle Database, DB2 and other rivals
  3. It includes various data management and data analytics tools for business intelligence (BI).
  4. For more than 20 years Microsoft SQL Server ran exclusively on Windows.
  5. Beginning with SQL Server 2016, is now available on Linux.  Moving it onto an open source operating system.