What's in the database?

Exploratory Data Analysis in SQL

Christina Maimone

Data Scientist

PostgreSQL

PostgreSQL Logo

Database client

Database client connects to database server

entity relationship diagram with each database table and their columns

Evanston 311 database table

Stackoverflow database table

Tables supporting stackoverflow database table

Select a few rows

SELECT * 
  FROM company 
LIMIT 5;

 id | exchange | ticker |         name          | parent_id 
<hr />-+----------+--------+-----------------------+-----------
  1 | nasdaq   | PYPL   | PayPal Holdings, Inc. |          
  2 | nasdaq   | AMZN   | Amazon.com, Inc.      |          
  3 | nasdaq   | MSFT   | Microsoft Corporation |          
  4 | nasdaq   | MDB    | MongoDB Inc.          |          
  5 | nasdaq   | DBX    | Dropbox, Inc.         |          
(5 rows)

A few reminders

Code	Note
`NULL`	missing

A few reminders

Code	Note
`NULL`	missing
`IS NULL`, `IS NOT NULL`	don't use `= NULL`

A few reminders

Code	Note
`NULL`	missing
`IS NULL`, `IS NOT NULL`	don't use `= NULL`
`count(*)`	number of rows

A few reminders

Code	Note
`NULL`	missing
`IS NULL`, `IS NOT NULL`	don't use `= NULL`
`count(*)`	number of rows
`count(column_name)`	number of non-`NULL` values

A few reminders

Code	Note
`NULL`	missing
`IS NULL`, `IS NOT NULL`	don't use `= NULL`
`count(*)`	number of rows
`count(column_name)`	number of non-`NULL` values
`count(DISTINCT column_name)`	number of different non-`NULL` values

A few reminders

Code	Note
`NULL`	missing
`IS NULL`, `IS NOT NULL`	don't use `= NULL`
`count(*)`	number of rows
`count(column_name)`	number of non-`NULL` values
`count(DISTINCT column_name)`	number of different non-`NULL` values
`SELECT DISTINCT column_name ...`	distinct values, including `NULL`

Let's start exploring

Exploratory Data Analysis in SQL