Intro to SQL

Vicki Boykis; vickiboykis.com / @vboykis

Welcome!

Tell Us About Yourself

• Who are you?
• What's your experience level with SQL?
• What do you hope to get out of the class?
• What is your favorite movie, and how many times have you watched it?

We're going to make a table out of this info in part 2, and the data will live here: [Table Link]

About Me

• Data scientist
• 9+ years of experience with SQL
• Favorite movie is Beauty and the Beast, watched 25 times. (and that was just yesterday)

What to expect from the class

• This is a complement to the database design class
• We'll be using the same terminology and building on those concepts
• But we'll be hands-on

By the end of the class, you will be able to query and modify a SQL table to make sense of the data you have in a relational way.

Plan for the day: CRUD

What's a relational database?

A virtual organized file cabinet that makes data available to people and computers across the organization.

The language of relational databases (1/2)

The language of relational databases (2/2)

Databases, schemas, and tables

DB Schema→ Table → Row → Value

Excel File → Workbook → Row → Cell

What's a relational database?

What's SQL?

• It's the language that the database speaks to bring you back data


• Has a lot of words similar to English, but they have their own meaning as key words


• Like Siri or Alexa

Different Flavors of SQL

• How do you say that you want a can of Coca-Cola?
• Coke, soda or pop?


• How do you write SQL?

All different flavors of the same language

Databases, schemas, and tables

• Reading tables other people wrote


• Modifying those tables to add your own information/ creating your own tables to read from CSV files, other SQL tables, and other systems.

Databases and CRUD

What can you do with SQL?

The power is in the relationship between data sets.

• How many customers do we have in Los Angeles?


• What's that person's Facebook and Twitter handle?


• Did profits go up or down this quarter?


• How many users are clicking on our new site?

SQL clauses: required order

Let's develop it!

Let's get started reading tables

We'll use MySQL, a popular version of SQL.

If you've done anything online, chances are the transation went through MySQL (Walmart, Kayak, Facebook, Etsy, Verizon)

We'll log on to the front-end of MySQL, PHPMYAdmin.

You should see a login screen:

How PHPMyAdmin Works

Two ways to execute in MySQL: PHPMyAdmin and command line

Focusing on phpMyAdmin for the first half of the class.

We have a test dataset of employee records we'll be using in the employee schema.

Employee data:
6 tables in the employees schema

Tables and Schemas

employees is the database and it has 6 tables.

Database/schema used interchangeably

Database → Schema ---> Table

Book ---> Chapter ---> Page

MySQL Database → Employees Schema → employees Table

How to name schemas and tables

• Schema/Databse: employees


• Tables:
• employees.employees,
• employees.departments,
• employees.salaries
• And so on.

Usually it's bad practice to name tables the same thing as the schema (confusing), but we'll be ok with the small test data set.

Let's develop it!

Let's run our first query!

Go to the SQL tab and type in:

SELECT * FROM employees.employees; 

Type out all the SQL you write. It'll be easier to learn.

SELECT * from different tables

Try a couple of these:

SELECT * FROM employees.titles;
SELECT * FROM employees.dept_emp;
SELECT * FROM employees.salaries; 

and check out the tables and info.

Stretch break!

Selecting specific columns

Now let's try selecting only certain columns

SELECT title, emp_no FROM employees.titles; 

A note about the semi-colon: you don't really need it, depending on which flavor of SQL you're using, but it's good practice in general to include it. Like a period at the end of an English sentence.

More specific columns. Let's get to know our data.

Selecting one column:

SELECT dept_name FROM employees.departments;

Selecting multiple columns:

SELECT emp_no, dept_no FROM employees.dept_emp;

Distinct: selecting only unique instatnces of that column.

SELECT DISTINCT  title FROM employees.titles;

Difference between distinct and *

SELECT COUNT(DISTINCT title) FROM employees.titles;
7
                                

SELECT COUNT(title) FROM employees.titles;
443,308
                                        

Difference between distinct and *

Distinct: Selecting only unique instances

Which result set is bigger?

What is each one telling us?

SELECT COUNT(title) FROM employees.titles;
433,308
                                

SELECT COUNT(*) FROM employees.salaries;
2,844,047
                                        

Which table is the largest?
(most records)

SELECT COUNT(*) from employees.departments;
9
                                

SELECT COUNT(*) FROM employees.dept_emp;
331,603
                                        

SELECT COUNT(*) FROM employees.dept_manager;
24
                                                                

SELECT COUNT(*) FROM employees.employees;
300,024
                                                                        

SELECT COUNT(*) FROM employees.salaries;
2,844,047
                                                                                

SELECT COUNT(*) FROM employees.titles;
443,308
                                                                                        

Three parts of SQL

SQL clauses: required order

Now let's group stuff!

                        SELECT type_of_dog,COUNT(*)
                        FROM animals.total_dogs
                        GROUP BY type_of_dog;
                                

Group statements corral data together into categories when we want to count things by groups of things

i.e. How many total dogs vs puppies vs parents.

Counting titles and salaries

SELECT title, COUNT(*) FROM employees.titles
GROUP by title;
                                

More group by examples

SELECT dept_name, COUNT(*) FROM departments
GROUP BY dept_name;
                                                

How many instances of that department are there in that table?

Group by more than one item: making smaller groups

Let's try some exercises that combine COUNT(*), DISTINCT and GROUP BY

How many unique salaries does each employee have? (how many times have they been given raises?)

SELECT emp_no, COUNT(DISTINCT salary)
FROM salaries
GROUP BY emp_no;

How many managers has each department had?

SELECT dept_no, COUNT(emp_no) FROM dept_manager
GROUP BY dept_no;

How many people that were born on the same day, have the same first name?

SELECT birth_date,first_name, COUNT(*) FROM employees.employees
GROUP BY birth_date, first_name;

Another kind of group-by:
maximum and minimum

When was your youngest employee born? (i.e. are you following labor laws?)

 SELECT MAX(birth_date) FROM employees;

Which title has been around the longest?(Smallest employee number)

SELECT title, MIN(emp_no) from titles
GROUP BY title;

Order by: Sorts by the terms you want

Will sort by first name ascending, unless you specify desc.

SELECT first_name, gender, COUNT(*)
FROM employees.employees
GROUP BY first_name, gender
ORDER BY first_name;

SELECT first_name, gender, COUNT(*)
FROM employees.employees
GROUP BY first_name, gender
ORDER by first_name DESC;

Ordering by more than one column, will sort by the first, then by the second, ascending

SELECT gender, birth_date FROM employees.employees
ORDER BY gender, birth_date;

Order by exercises

Order by a number ascending

SELECT emp_no, salary FROM employees.salaries
ORDER BY salary ASC;

Order by a number descending

SELECT emp_no, salary FROM employees.salaries
ORDER BY salary DESC;

SQL clauses: required order

Where Clause: Like a Filter

You only want to select certain things.

SELECT first_name, last_name, COUNT(*)
FROM employees.employees
WHERE first_name='Adam'
GROUP BY first_name, last_name;

Like statements are really useful in where clauses

"Like" will pick up anything similar to what you entered.

SELECT last_name, COUNT( * )
FROM employees
WHERE last_name LIKE 'S%'
GROUP BY last_name;

Where clause exercises

Only all people named Mary

SELECT * FROM employees
WHERE first_name='Mary';

Only all of the engineers

SELECT * FROM employees.titles
WHERE title LIKE '%Engineer%';

A couple of things to note about greater/less than/equal to

 WHERE number >='5'; --returns (5,6,7,8...)

 WHERE number > '5'; --returns (6,7,8...)

 WHERE day <= '2017-01-01'; --returns (Jan 1, Dec. 31, Dec. 30…)

 WHERE day < '2017-01-01'; --returns (Dec. 31, Dec. 30…)

 WHERE day != '2017-01-01'; --returns anything other than Jan 1

Cooking with the good suff: joins

One table is great. But the power of SQL comes from being able to join related tables to get information.

Plan for the day: CRUD

Let's connect multiple tables

emp_no is the column that's the primary key for all three tables that allows us to join them

• We join by primary keys: A field (or set of fields) that uniquely determine a row.
• Values in PK must be unique in table (no duplicates allowed); Only one PK per table allowed
• To foreign keys: A field (or set of fields) that is a PK in some other table.

How all the tables join in our schema

In SQL, a join looks like this:

Often when we join tables, we make them easier to reference later on by aliasing them, but at this point it's a style choice.

You can now select columns that, before, only existed in either table

Find out salary history:

SELECT first_name, last_name, hire_date,salary, from_date, to_date
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no

Another join example

When you have two columns from different tables with the same name, you have to reference them specifically.

Different types of joins

Let's develop it!

Left join example

Left join: All the fields in the first table, plus the ones that match in the second table

SELECT first_name, last_name, hire_date,title, c.from_date, c.to_date
FROM employees.employees a
LEFT JOIN titles c ON a.emp_no = c.emp_no;

What if you'd done a right join instead?

Using everything we've learned

What is this query telling us?

SELECT first_name, last_name,a.emp_no, hire_date, 
COUNT(DISTINCT title) , COUNT(DISTINCT salary)
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no
JOIN employees.titles c ON a.emp_no = c.emp_no
where first_name like 'A%'
GROUP BY first_name, last_name, hire_date,a.emp_no;

Going all in again:

What is this query bring back?

SELECT first_name, last_name,hire_date,a.emp_no, salary, from_date as
salary_start_date, to_date AS salary_end_date
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no
WHERE salary >60000
AND from_date >'2000-01-01'
ORDER BY last_name

Anyone whose salary is greater than $60,000 and who was hired after 1/1/2000

Lunch!

Now the power of subqueries

Using nested logic datasets to query other data sets

Subqueries

A subquery or inner query or nested query is a query within a query. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but not the exact value

Subqueries: How much money did only the engineers make?

What kind of business questions can we answer now? SQL Power!

Exercises:

• How many employees do we have?

SELECT COUNT(DISTINCT emp_no)
FROM employees.employees;

• How many people got raises in 1999?

 SELECT * from (
        SELECT emp_no, COUNT(*) AS number_of_salaries 
        FROM salaries
        WHERE from_date>'1998-01-01'
        AND from_date<'2000-01-01'
        GROUP BY emp_no) a
        WHERE number_of_salaries >1

• What's the most common job title we have?

SELECT title, COUNT(DISTINCT a.emp_no) 
FROM titles a
JOIN employees b
ON a.emp_no=b.emp_no
GROUP BY title

What kind of business questions can we answer now? SQL Power!

Exercises:

• What's the top salary by department?
• How much money did non-engineers make?
• Extra-hard question:
• How many women engineers got raises in 2000 versus male engineer, and who were their managers?

Rehash

What did we do so far?

• What a database is
• What a table is
• Why relationships are good
• How to query a table or database with SQL
• How to read a SQL query
• How to ask business questions with SQL

We learned cRud; now we learn CrUD

Now that you see how powerful SQL is, let's make some tables of our own. What if we have tons of data we want to query?

Or we want to join our Excel tables to other data?

With great power comes great responsibility

You have the ability to create tables. But also to delete them by accident, to create duplicate data,or to confuse people with data.

You have the power to make people hate you. Use it wisely :)

Databases, schemas, and tables

DB Schema→ Table → Row → Value

Excel File → Workbook → Row → Cell

First we create our schema

Name it with your first name or something easy you can remember; You'll have to keep typing it, so under 10 characters is optimal

And you should see it show up:

Now we create a table to put in the schema

Let's do favorite movies that we did at the beginning of the class.
Let's have: First Name, Favorite Movie, Number of times watched

First, we need to design it a bit in our head. Which columns do we want? Which one will be the unique primary key? How to we want to name the columns?

Things to think about when designing relational tables

1. How will you join to other tables?
2. What kind of data are you storing?
3. Who else will use your tables?

Quick Overview of Database Design(from Sondra's class)

Relational Database Model

Storing the data as relations

• eliminates redundancy
• saves space
• reduces mistakes (ties in to consistency)

Quick Overview of Database Design(from Sondra's class)

Tables are joined on special fields called keys

• Primary Key (PK)
• A field (or set of fields) that uniquely identify a row
• In math speak, the minimal set of fields that the row is functionally dependent upon

• Foreign Reference Key (FK)
• A field (or set of fields) that is a PK in some other table
• IThere can be multiple FK in a table
• Points to a row in another table

Data Types

Data usually comes in two types: numbers and charactes

• Numbers
• Boolean: True or False [0,1]
• Int/TinyInt: whole numbers, negatives [5, -7]
• Decimal: [.5555, 2.40404040]
• Strings
• Varchar(can't add/subtract):[Belle, five,5]
• Have to specify length
• Blob: [9999349234023403204300000]
• Dates/Times
• Date: 2013-01-01, 9/1/2012]
• Timestamp(UTC) [1385496316]

*Accepted mySQL formats: https://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Let's look at the data types in the tables we have

By going to STRUCTURE

Our movie database (with real data)

• First Name as the Primary Key and Foreign Key
• We can join by the name, as long as it's unique
• But our names aren't unique, so we'll create an incrementer
• First Name Should be a Character Field.
• Favorite Movie should be a character Field.
• Times Watched should be an Integer. We want to be able to add it.

Two ways to execute in MySQL: PHPMyAdmin and command line

For the second half of the class we'll be working with commands more than the UI, but there is usually more than 1 way to do everything in MySQL.

How to create a table with SQL

Run in the sample place you did your select statements:

Some Data types in MySQL(Part 2)

Here's the create statement for one of the tables we've been working with

CREATE TABLE employees (
emp_no      INT             NOT NULL,
first_name  VARCHAR(14)     NOT NULL,
last_name   VARCHAR(16)     NOT NULL,
gender      ENUM ('M','F')  NOT NULL,
hire_date   DATE            NOT NULL,
PRIMARY KEY (emp_no);

Which field is the primary key?
Which fields are numbers?
What's enum*?

Let's get to it

In PHPMYADMIN: STEP 1

In PHPMYADMIN: STEP 2

In PHPMYADMIN: STEP 3

Congratulations, it's a table!

Now let's do some quick analysis

SELECT * FROM vicki.moviesample;
SELECT first_name FROM vicki.moviesample;
SELECT COUNT(*) FROM vicki.moviesample;

What do we get?

Nothing! because our table has no data. :(...yet

To the SQL: Inserting data into a SQL table

Now that we have the table set up, we can insert the values. Let's load a row of data.

INSERT INTO
yourname.tablename(first_name, favorite_movie,
times_watched) -- column names
VALUES ('Jasmine', 'Aladdin', '456'); -- column values 

Adding data to tables

INSERT INTO vicki.moviesample(first_name, favorite_movie, times_watched)
VALUES ('Alice','Alice in Wonderland', 5);

Now let's check it..wahoo! We have rows!

How do we add the rest of the data?

INSERT INTO vicki.moviesample
(first_name, favorite_movie,times_watched)
VALUES ('Vicki', 'Beauty and the Beast', '5');

INSERT INTO vicki.movie_sample
(first_name, favorite_movie,times_watched)
VALUES ('Jasmine', 'Aladdin','456'); 

Break to enter the rest of the data into the table

What kind of issues do you see arising from wrong data entry?

There are much easier ways of getting data into tables

• Bulk-loading ETL
• Writing a script in Perl, Ruby, Python, or any other scripting language
• Manipulating Excel to write your insert/update statements

Our beautiful movie table!

Let's answer some exercises with SQL:

SELECT favorite_movie,
COUNT(DISTINCT first_name)
FROM yourname.moviesample
GROUP BY favorite_movie
ORDER BY COUNT(DISTINCT first_name) DESC;

• What's the most popular name this Saturday?
• How many times was the most someone watched a movie?
• How many movies have 'The' in the title?
• How many people watched a favorite movie more than 3 times?

Now, let's make another table

We want to see which decade was more popular among GDI members. This one has the data for the movie name and the year it was made.

How do we add the rest of the data?

SELECT * FROM vicki.moviesample a
JOIN vicki.movie_year b
ON a.movie_title=b. movie_title

• What happens when you have two fields with the same name in your select statement?
• What is movie title called in year_released (special role?)
• What would be a better field to join on?
• What's the year with the most popular movies?
• And, what's the decade with the most popular movies? Hint: Take the first three digits of the year
• select left(year_released,3), count(*) from..

Deleting SQL Values

Dropping Rows Example

Easy way in UI: The delete button. Be careful!

• SQL...more accurate.
DELETE FROM user1.moviesample WHERE first_name='Shrek';

You can delete with any type of where statement

DELETE FROM moviesample
WHERE first_name like '%S';

DELETE FROM moviesample
WHERE times_watched >='500'; 

Updating the table

• SELECT is to count things
• CREATEis to create a table
• DELETE is to erase
• UPDATE is to change specific entries



UPDATE moviesample
SET first_name = 'Chihiro', times_watched= 3
WHERE movie = 'Spirited Away';

Try it now

Altering the Table

• SELECT is to count things
• CREATEis to create a table
• DELETE is to erase
• ALTER is to fundamentally change a table:



ALTER TABLE icecream DROP COLUMN flavor;
ALTER TABLE icecream ADD unique (quantity);
ALTER TABLE icecream MODIFY flavor VARCHAR(35);

Try it now:

ALTER TABLE vicki.moviesample ADD COLUMN release_yr
varchar(4) after times_watched;

Troubleshooting SQL Errors

• You misspelled something
• Selection from table you don't have rights to
• Your group by statements don't have enough columns
• Query's running too long
• Format for date/time doesn't match
• You're selecting from the wrong table in the schema
• Date type is the wrong type for a join
• You haven't aliased your table

Best Practices

• Limit your queries by timerange
• Only do joins on primary keys
• Profile your data beforehand
• Save your SQL queries for future use
• Be friends with your database people
• Write explain plans!

A word about non-relational "big data"

The end!

Here's what we've learned:

• Reading tables (cRud)
• Basic SQL Logic
• Writing tables (CrUD)
• Basic deletes and updates
• Best practices for writing SQL and writing tables

More SQL Resources

• Head First SQL Good visual book
• Learn SQL The Hard Way Highly recommended, and not hard at all
• Visualizing SQL Joins Great visual intro
Good visual book
• Popular SQL Questions Highly recommended, and not hard at all

Practice, practice, practice! Upload your Excel spreadsheets.

• SamplePractice Data Set that we used in class

Get Involved

1. Join Slack community
2. Teaching assist
3. Teach something
4. Mentor or be mentored

All forms here

More SQL: Local Groups

• Philly DB http://www.meetup.com/PhillyDB/
• Data Science Philly http://www.meetup.com/DataPhilly/
• Philly Open Data http://www.meetup.com/Philly-Open-Data/
• Philly PostrgreSQL UG http://www.meetup.com/phlpug/

Survey

We value your feedback and are always trying to improve.

Vicki Boykis vicki.boykis@gmail.com

vickiboykis.com | @vboykis

http://bit.ly/gdi-sql-2

Thank you to the following people:

Sarah Canfield - converting slides to Reveal A.E.Lavender - Sanity and syntax checks