Coding Time

.

Washington Post Graphics

Small Business Career Fair

SQL Is Fun

Really.

What does SQL stand for?
What is SQL and Why Do I Need It? Relational database. “SQL is a standard language for storing, manipulating and retrieving data in databases.”
How Does It Work?
When Can We Get Started? I Can’t Wait! https://www.w3schools.com/sql/default.asp

This week:

–Orientation with the queries and logic of basic SQL commands

–Import data and create a database

Part 1:Basic Commands.

We will be using this sample database called customers for these drills. Take a look at it.

This data is in our SQL tutorial. Here’s how we extract and filter the records to get what we want.

Major Commands:

SELECT

FROM

WHERE

GROUP BY

ORDER BY

—

SELECT: You tell the database what columns you want.

If you want everything, SELECT *

Example: SELECT * FROM Customers;

–Retrieves EVERYTHING (*) from the database called Customers

SELECT CustomerName FROM Customers;

–Retrieves CustmerName from the database called Customers

FROM: The basic command that just identifies which table you’re pulling data from.

Example: SELECT * FROM Customers;

–Retrieves EVERYTHING (*) from the database called Customers

WHERE: A filter. Allows you to grab only certain records do numerical and text string, or a horizontal slice of your data; you set criteria that has to be true in order for rows to be returned

SELECT * FROM Customers WHERE ContactName “Maria Anders”;

–retrieves all records with Maria Anders as contact name

SELECT * FROM Customers WHERE ContactName LIKE “Maria%”;

–retrieves all records with Maria in ContactName. Note the addition of LIke and the wildcard %

Work on the tutorial:

TIP: Print our the table names and the first 2-3 rows of data as a reference

GROUP BY – this groups rows together by a certain field or fields so that you can do math on those groups, such as counting the number of records in each group or summing up the numbers in a field.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

–Here we are counting the number of countries in the database

Remember this command is paired with putting an aggregate function in your SELECT statement, such as COUNT(*) or SUM(noncitizen)

ORDER BY – this sorts your results by a field or fields, ascending (default) or descending (DESC)

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

–Produces a list ordered by country, alphabetized

List of countries in descending order:

SELECT * FROM Customers

ORDER BY Country DESC;

Exercises:

Find the total number of cities in the database, sort by descending order
List all of the business in the United States, sorting alphabetically by city
Produce a list of all that counts all of the customers by countries in the database, sorted by descending order
Switzerland has how many customers?
United States?
Argentina?

Quick Reference Sheet for these commands:

https://www.w3schools.com/sql/sql_quickref.asp

More Exercises

UPDATE

https://www.w3schools.com/sql/sql_update.asp

–Update the first record contact name to Larry Foley

How to Totally Screw Up A Database with an Update Function – Eliminate WHERE

UPDATE Customers

SET ContactName=‘Fat Fingered Idiot’;

(Reset the database after this)

https://www.w3schools.com/sql/sql_delete.asp

MIN MAX

https://www.w3schools.com/sql/sql_min_max.asp

How do we identify the product?

How do we identify the product with highest price?

https://www.w3schools.com/sql/sql_count_avg_sum.asp

SQL – Importing Data, Creating a Database

For this exercise, we will be using an open source database manager called SQLite that runs as an extension to Firefox. It basically turns your browser into a database program. Firefox Add-On: https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/

This will suffice for our purposes this semester, but if you are going to deal with Big Data, you will need Big Boy and Big Girl data software. A discussion for another day.

Firefox: Install add-on, relaunch browser. Find Add-on in Menu Bar: Tools

Create a database

1) Create a database. Call it anything, Test, Donkey, Hog. Put it in the same folder as your data: FL_nativity

2) Import Data FL_nativity into SQL.

This can be tedious, and here’s why:

We have to tell SQL about each column, whether it is text, numbers or some variation in between.

There are multiple data types:

https://www.sqlite.org/datatype3.html

For this, we are just going to define the columns as either “text” or “numeric” — more on the other data types later. So you need to separately define 15 different columns as text or numeric. (When you buy Big Boy or Big Girl software, the import sequence is less tedious)

As you import FL_nativity — do yourself a favor and open it separately in a spreadsheet so you can see the various columns and what they contain. Check the number of columns in the spreadsheet versus the number of imported columns to ensure they match. You should have 15 imported columns and it should import 69 rows.

Headings

IMPORTANT NOTE: Eliminate Drudgery of renaming file names – CHECK the box import top row with file names

Id Id2 Geography Estimate_Total MOE_Total Native Native_MOE Puerto_Rico Puerto_Rico_MOE BornAbroad BornAbroad_MOE Naturalized Naturalized_MOE Noncitizen Noncitizen_MOE

Here’s what your finished import sequence should look like:

This is what you did in SQL language

CREATE TABLE “FL_nativity” (“Id” NUMERIC,”Id2″ NUMERIC,”Geography” TEXT,”Total” NUMERIC DEFAULT (null) ,”MOE_Total” NUMERIC,”Native” NUMERIC,”Native_MOE” NUMERIC,”Puerto_Rico” NUMERIC,”Puerto_Rico_MOE” NUMERIC,”BornAbroad” NUMERIC,”BornAbroad_MOE” NUMERIC,”Naturalized” NUMERIC,”Naturalized_MOE” NUMERIC,”Noncitizen” NUMERIC,”Noncitizen_MOE” NUMERIC)

If you messed up, here is how you edit the headings

1) Structure

2) See columns, click on row and rename

Get Stuff From The Database

SQL exercises

https://www.w3schools.com/sql/sql_update.asp

–Update the first record contact name to Larry Foley

How to Totally Screw Up A Database with an Update Function – Eliminate WHERE

UPDATE Customers

SET ContactName=‘Fat Fingered Idiot’;

(Reset the database after this)

https://www.w3schools.com/sql/sql_delete.asp

https://www.w3schools.com/sql/sql_min_max.asp

How do we identify the product?

How do we identify the product with highest price?

https://www.w3schools.com/sql/sql_count_avg_sum.asp

Homework

Do not turn in any work for Tuesday. Use the Firefox and the SQL Lite Manager https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ read the first section on importing data in this tipsheet: MySQL_Tipsheet-rsw using this data FL_nativity

Try to create a new database (see instructions on this page) and perform some basic queries.

We return to our SQL commands to see what is in the database. Remember to have a list of your column headings handy so you spell the queries correctly.

SELECT * FROM FL_nativity

–Brings up all columns from FL_nativity

SELECT geography, total, native, naturalized, noncitizen FROM FL_nativity

–brings up these columns

Wildcard query

WHERE georgraphy LIKE ‘a%’ Finds any values that starts with “a”

Add a column

ALTER TABLE “main”.”FL_nativity” ADD COLUMN “Immigrant” NUMERIC

Fill it with stuff

UPDATE FL_nativity

SET immigrant = (naturalized + BornAbroad)

Follow this cheat sheet for additional exercises:

MySQL_Tipsheet-rsw

Data Journalism Backup Site

March 28- 30 Class