Intro to SQL

https://slides.com/lizh/gdi-intro-to-sql

I'm LizTheDeveloper

17+ years in tech

I've taught over 3500 people to code in person

& nearly a million online

I've been a CTO for hot, VC-funded silicon valley startups twice,

I helped found Hackbright Academy and wrote the curriculum for Galvanize's Web Development Immersive

I wrote the fraud prevention system for 8 of the top 10 banks in the country

and yeah, people still ask me if I'm technical.

No one is perfect

Even though I'm awesome I have definitely:

Taken down production at least once at every job

Crashed a payments server during peak purchase hours

Dropped the users table and had to restore from a backup

Not had a backup

Faiza Khan

Professor and Data Scientist

Taught Statistics and Probability for nearly 3 years, to over 700 data scientists

Transitioned from Academia to Tech through Data Science

Welcome!

Girl Develop It is here to provide affordable and accessible programs to learn software through mentorship and hands-on instruction.

 

We have a Code of Conduct here

In Summary:

  • You're important and valued
  • Be excellent to each other
  • Help everyone out, it helps you learn!

Objectives

  1. Describe what a Relational Database is and what SQL is used for
  2. Create, Read, Update and Delete with SQL
  3. Aggregations with SQL
  4. Select related data in multiple tables 
  5. Create and Read a SQL Schema

You'll revisit these!

Relational Databases

What are they?

What makes them "relational"?

Relational Databases

Tables

ID Name Email Phone Zip
1 Liz liz@liz.com 555-2323 94110
2 Tyler tyler@teb.com 555-3232 94110
3 Matt matt@williams.com 555-4455 94110

Relational Databases

Relationships

ID Name Email Phone
1 Liz liz@liz.com 555-2323
2 Tyler tyler@teb.com 555-3232
3 Matt matt@williams.com 555-4455
ID User_ID Zip
1 1 94110
2 1 94102
3 2 94110
4 2 94114

Schemas

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email VARCHAR(256),
    phone VARCHAR(12)
);

CREATE TABLE zip_codes (
    id SERIAL PRIMARY KEY,
    user_id INT FOREIGN KEY,
    zip VARCHAR(10)
);

Defining A Table

Objectives

  1. Describe what a Relational Database is and what SQL is used for
  2. Create, Read, Update and Delete with SQL
  3. Aggregations with SQL
  4. Select related data in multiple tables 
  5. Create and Read a SQL Schema

You'll revisit these!

Structured Query Language

Create INSERT
Read SELECT
Update UPDATE
Delete DELETE

Structured Query Language

Structured Query Language 

SELECT id, name, email, phone FROM users;

SELECT [ a list of column names] FROM [a table name]

ID Name Email Phone Zip
1 Liz liz@liz.com 555-2323 94110
2 Faiza faiza@khan.com 555-3232 94110
3 Kelly kelly@tran.com 555-4455 94110

Getting Data Out

Structured Query Language 

SELECT id, name, email, phone FROM users
WHERE id = 1;

SELECT [ a list of column names] FROM [a table name]

WHERE [some condition is true]

 

ID Name Email Phone Zip
1 Liz liz@liz.com 555-2323 94110

Specific Data Out

Structured Query Language 

INSERT INTO users (id, name, email, phone)
VALUES (5, 'Aaron', 'aa@ron.com', '555-2156');

INSERT INTO [ a table name ] ([a list of column names])

VALUES ([comma-separated expressions in the same order as the column names])

Putting Data In

Demo time

http://sqlfiddle.com/#!9/974537

CRUD Queries

5-8 min

play along!

Practice Time

Let's Query Real Data

https://data.stackexchange.com/stackoverflow/query/new

The Stack Overflow Dataset

(dataset tutorial)

5 min

Find Out:

1. Find the post with id 2162271

2. Find posts that have a score higher than 25

3. Find posts created 3 years ago today

To limit queries:

SELECT TOP 10 * FROM posts;

Objectives

  1. Describe what a Relational Database is and what SQL is used for
  2. Create, Read, Update and Delete with SQL
  3. Aggregations with SQL
  4. Select related data in multiple tables 
  5. Create and Read a SQL Schema

You'll revisit these!

Aggregations

Count the number of rows

SELECT COUNT(*), zip_code FROM users
GROUP BY zip_code;

Aggregations

Many Functions

VAR(*) / OVER

number of rows that match a grouping

AVG(*)

average of values in a grouping

SUM(*)

sum of values

MAX(*) / MIN(*)
COUNT(*)

maximum / minimum value in values

STDEVP(*)

standard deviation of populations

statistical variance (OVER range)

Ordering

ORDER BY

Which column to order the query by

ASC

Lowest value first

DESC

Highest value first

ORDER BY
SELECT count(zip_code), zip_code from users
GROUP BY zip_code
ORDER BY count(zip_code) DESC;

What zip code has the most users?

Aggregations

WHERE is to rows returned without a group by statement

as HAVING is to rows returned with a GROUP BY statement

HAVING

 SELECT DeptID, SUM(SaleAmount)
 FROM Sales
 WHERE SaleDate = '01-Jan-2000'
 GROUP BY DeptID
 HAVING SUM(SaleAmount) > 1000

Demo

http://sqlfiddle.com/#!9/974537/9

Group By

5-8 min

play along!

Practice Time

Let's Query Real Data

https://data.stackexchange.com/stackoverflow/query/new

The Stack Overflow Dataset

(dataset tutorial)

5 min

Practice Time

5 min + 10 min stretch break!

Find Out:

1. 10 oldest posts / 10 newest posts

2. count of posts on this day 3 years ago

3. count of posts every day for the last 10 days

4. the count of all answers for posts in the last 10 days

When you're done, stand up and stretch up as high as you can!

🙆‍♀️

Objectives

  1. Describe what a Relational Database is and what SQL is used for
  2. Create, Read, Update and Delete with SQL
  3. Aggregations with SQL
  4. Select related data in multiple tables
  5. Create and Read a SQL Schema

You'll revisit these!

JOINs

id username
1 coolguy86
2 vampgurl
id user_id email
1 1 coolguy86@aol.com
2 2 vampgurl@aol.com
3 2 karen.smith@gmail.com 
SELECT users.username, emails.email FROM users
INNER JOIN emails ON users.id = emails.user_id;

JOINs

Source

INNER vs OUTER JOINs

Inner = a record is present in both tables

Outer = a record is present in either table

SELECT users.name, zip_codes.zip_code FROM users
INNER JOIN zip_codes ON users.id = zip_codes.user_id
SELECT users.name, zip_codes.zip_code FROM users
OUTER JOIN zip_codes ON users.id = zip_codes.user_id

All users that have a zip code

All users, and if they have a zip code, their zip code

Left vs Right JOINs

SELECT users.name, zip_codes.zip_code FROM users
LEFT JOIN zip_codes ON users.id = zip_codes.user_id
SELECT users.name, zip_codes.zip_code FROM users
RIGHT JOIN zip_codes ON users.id = zip_codes.user_id

All users and their zip codes

All zip codes and their users

FULL JOINs

Get rows that have no corresponding row in the other table

SELECT users.name, users.id, zip_codes.id, zip_codes.zip_code FROM users
FULL OUTER JOIN zip_codes ON users.id = zip_codes.user_id
WHERE zip_codes.user_id IS NULL;

Find users with no zip code and zip codes with no user

Demo

http://sqlfiddle.com/#!9/974537/10

JOINs

5-8 min

play along!

1 SELECT selects variables
2 FROM opens datasets
3 WHERE restricts observations
4 GROUP BY groups observations
5 HAVING restricts groups
6 ORDER BY sorts results

SQL clauses

required order

Practice / Homework

  • Practice:
    • https://sqlzoo.net/
    • https://pgexercises.com/
    • https://sqlbolt.com/lesson/select_queries_introduction
    • https://www.datacamp.com/courses/intro-to-sql-for-data-science
  • Create your own examples: https://sqlfiddle.com/
  • Connect to a real database: https://franchise.cloud
  • https://enki.com/ - an app I made with practice questions :D

Homework 2

Pros:

Ask for access to any SQL database you know about at work and try to answer questions using it

(social engineering tip: say it's for a class you're taking! 😉)

Thanks!

Thanks To:

GDI Philly's Class

Faiza Khan

Kelly Tran

Sarah Kwak

Carly Fujiyoshi

Everyone who came to class on time

Everyone who was late also I guess