Introduction to Databases with SQL

Valentina Staneva

eScience Institute

vms16@uw.edu

11/30/2016

Plan for Today:

• quickly overview functionality of databases and SQL
• learn fundamental SQL commands by exploring a Seattle Crime Dataset

Data source: http://www.seattle.gov/seattle-police-department/crime-data/spd-data-sets

Lesson source: https://github.com/valentina-s/SQL_tutorial/

Why Databases???

• data you need is in a public database
• you want to make your data easily accessible
• you can manage the security permissions of the data
• computational load is on the server side
• you cannot load data into RAM
• certain operations are optimized in a database
• you can implement efficient scalable algorithms in a database

What is a Database?

"A database system is basically a computerized recordkeeping system -- that is, a system whose overall purpose is to maintain information and to make that information available on demand." (Date 1986)

• conventional databases use the relational data model
• non-relational databases (noSQL, mongoDB)

Relational Data Model:

• data are structured into row/column format

• each record has a unique identifier (primary key)

What is SQL?

Structured Query Language

• Domain-Specific Programming Language
• not Turing Complete (cannot build a robot which can follow any instruction :()

But great for data manipulation!!!

And human-readable!!!

Creating a Table:

• populating the database records:

  INSERT INTO seattlecrimeincidents VALUES
  
    (1,'trespass', 5700,'2015-01-28 09:30:00','12XX Block of E Pike St'),
  
    (2,'larceny-theft',2300, '2015-02-21 08:24:21','15XX Block of Aurora St');
  

Data in each column must be of the same type

Some common data types:

NULL values

• missing data are a common feature of many datasets
• here the code for "tresspass" is not known so the data entry is "X"

NULL values

• conventionally, some value is used to represent missing data (e.g. "X" or -9999)
• relational databases introduced NULL values:
  • NULL is a state representing a lack of a value
  • NULL is not the same as zero!
  • NULL values are ignored in SELECT statements

Normalization

• minimize redundancy

Example: multiple offenses at the same time

INCORRECT: database will have problems searching these columns

Solution: create another row

Selecting Rows:

SELECT * 
   FROM seattlecrimeincidents 
   WHERE "Offense code" = 5700;

• use a "WHERE" clause to select specific rows

Selecting Columns:

SELECT "Offense type", "Date" 
   FROM seattlecrimeincidents;

• use a comma separated list to select specific columns

Elementwise Functions on Columns

Example:

• use a function to extract a subset of a date (e.g. year, hour) from a column with type = "timestamp"

Aggregate Functions on Columns

• examples: SUM(), MAX(), MIN(), AVG(), COUNT(), STDDEV()

Data Analysis:

• databases have powerful methods for analyzing data
• one of the most common tasks: applying statistics across groups
• to accomplish this we need to learn
  • how to GROUP sets of data
  • how to apply statistical functions to those groups

Q: What is the total damage that occurred for each offense type?

• data grouped by "Offense code":

• data grouped by "Offense code":

Column Aliasing:

• often we want to rename newly generated columns:

Joining Tables

• well designed databases distribute data across multiple tables, for efficiency
• then we can JOIN data between tables as needed

Database Implementation:

• there are many relational database software implementations:
  • commercial: Oracle, Microsoft SQL Server, IBM DB2
  • open source: MySQL, PostgreSQL

• Deployment
  • most databases are deployed on a server
  • can run locally for testing

Database Interface:

• databases are accessed via a connection string:
  • hostname, port, user, password
• one can connect through
  • command line
  • GUI apps (pg_admin, MySQL Workbench, DB Visualizer)
  • Python, R, etc.