Introduction to Data Science and Analytics Stephan Sorger

Introduction to Data Science and Analytics Stephan Sorger www. stephansorger. com Unit 6 B. SQL: Overview and Coding SQL Coding Session in My. SQL Disclaimer: • All images such as logos, photos, etc. used in this presentation are the property of their respective copyright owners and are used here for educational purposes only • Some material adapted from: Sorger, Stephan. “Marketing Analytics: Strategic Models and Metrics. Admiral Press. 2013. © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

Outline/ Learning Objectives Category Description Hosting Obtaining hosting server for My. SQL (free or paid) Setup Setting up the My. SQL Server on hosting platforms Importing databases into My. SQL Server Structure Data structure; Showing tables within databases SHOW, SELECT Commands to display data and choose data Sorting, Filtering Commands to sort and filter data Creating, Dropping, and Altering databases and tables © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Hosting Go to 000 webhost. com (or other similar Web hosting service) and get an account © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL Server: Setup Creating My. SQL Databases for hosting accounts on Go. Daddy. com © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL Server: Setup For Go. Daddy, launch hosting account to see c. Panel (shown). Click on My. SQL Database Wizard © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL Server: Setup Enter name for database © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL Server: Setup Enter username and password © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL Server: Setup Select privileges for user © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL Server: Setup Select php. My. Admin © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Importing Databases Select your Database (Database 1) © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Importing Databases Select Import © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Importing Databases My. SQL Sample Databases: dev. mysql. com/doc/index-other. html For our example: world database © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Importing Databases Choose File: Dataset: world. sql Click “Go” (bottom of page) © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Importing Databases Imported OK; Click “Database 1” © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Data Structure Check “City” Table within World database Click on SQL tab © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Data Structure In SQL tab, enter “SHOW DATABASES” and hit “Go” SQL commands also called “queries” are in ALL CAPS Table entries are in lower case or Mixed Case © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Data Structure Shows databases: information_schema (already in SQL server) Database 1 (we created) © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Data Structure In SQL tab, enter “SHOW TABLES” and hit “Go” © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Data Structure Tables: City Country Language © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SHOW Command View columns of table Click on SQL tab Syntax: SHOW COLUMNS FROM (table) Here: SHOW COLUMNS FROM City Then click Go © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SHOW Command Results: See columns from table Note PRI (primary) key Here, PRI = ID field © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SHOW Command Click on file structure list in left column to see table Here, click on “City” © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT Command SELECT Name FROM City and click Go Note column names on right Note commands on bottom © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT Command Result: Names of cities © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT Command Select multiple columns from tables using comma (, ) between column labels Note semicolon at end of line (most compilers will run command without semicolon if command is only 1 line) SELECT Name, Country. Code FROM City; © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT Command Result: Names of cities AND Names of Country Codes © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT Command Insert wildcard character (*) to retrieve all information SELECT * FROM City © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT Command Result: Entire dataset © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT with DISTINCT and LIMIT DISTINCT: To show only one instance of a particular value SELECT DISTINCT Country. Code FROM City © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT with DISTINCT and LIMIT Result: Distinct country codes © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT with DISTINCT and LIMIT Show only first 5 using LIMIT command SELECT Name, Country. Code FROM City LIMIT 5 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT with DISTINCT and LIMIT Result: First 5 entries © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT with DISTINCT and LIMIT Select only limited set of entries From start point (say, 5) To a certain number of data points (say, 10) (Computers start counting at 0, not at 1, so results will show “ 6”, not “ 5” SELECT Name, Country. Code FROM City LIMIT 5, 10 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT with DISTINCT and LIMIT Result: Results 5 - 15 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT with DISTINCT and LIMIT Fully Qualified Names: “dataset. label” Handy if working on multiple databases with the same column SELECT City. Name FROM City © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: SELECT with DISTINCT and LIMIT Results: Same as before Note names in order as found in the original table; Can be confusing. Alternative: Sort. © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Sort results using ORDER BY command SELECT Name FROM City ORDER BY Name © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Results are now in alphabetic order © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting multiple columns, specifying order parameter SELECT Name, Country. Code, District FROM City ORDER BY Name © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Results: Multiple columns, sorted by Name © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Sequential Sort: Sort by x, then by y SELECT Name, Country. Code, District from City ORDER BY name, Country. Code © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Result: Sort by Name, then by Country Code (not very interesting in this case, because data is unique for each area) © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting in reverse direction; from high to low DESC = Descending ASC = Ascending SELECT Name, Country. Code FROM City ORDER BY Name DESC © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Result: Names from Z – A BUT: Algorithm lists special characters, such as umlauted vowels, AFTER Z © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Select highest value (or lowest value) SELECT Name, Population FROM City ORDER BY Population LIMIT 1 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Results: Adamstown has the smallest population, with only 42 people © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Select highest value (or lowest value) SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 1 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Sorting Results: Mumbai has the largest population, with 10, 500, 000 people © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Filtering: Want only areas within country code ‘AFG’ SELECT Name, Country. Code FROM City WHERE Country. Code=‘AFG’ © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Filtering Results © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Filtering: Want to list “small cities”, i. e. areas with population under 10, 000 SELECT Name, Population FROM City WHERE Population <= 10000 (could also show “large cities”) : SELECT Name, Population FROM City WHERE Population >= 10000 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Filtering Result: Listing of “small” cities in ID order (could also sort if desired) © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Filtering: Want to list “medium cities” i. e. areas with population between 10, 000 and 50, 000: SELECT Name, Population FROM City WHERE Population BETWEEN 10000 AND 50000 OR SELECT Name, Population FROM City WHERE Population >= 10000 && Population <= 50000 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Filtering Result: “Sweet spot” cities between 10, 000 and 50, 000 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Logical Operators Find name of city within AFG with a population greater than 200, 000 SELECT Name, Country. Code FROM City WHERE Country. Code=‘AFG’ AND Population>200000 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Logical Operators Result: Two cities met both criteria © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Logical Operators Find name of city within NLD OR with a population greater than 400, 000 SELECT Name, Country. Code, Population FROM City WHERE Country. Code=‘NLD’ AND Population>400000 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Logical Operators Result: Three cities met at least one of the criteria © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Searching Use REGEXP (regular expression) to search for item. Here, we want to search for any district with the word “Holland” in it. SELECT Name, District FROM City WHERE District REGEXP 'Holland' © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Searching Results: Many districts have the word “Holland” in them © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Special Columns Create special columns using math: +, -, *, / For example, what if population were to grow by 10%? SELECT Name, Population*1. 1 AS Population_Growth FROM City © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Special Columns Result: Special Column temporarily inserted in database: Population Growth Shows population + 10% SELECT Name, Population*1. 1 AS Population_Growth FROM City © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: CREATE Database Create new database called “database 2” CREATE DATABASE database 2 To delete database: DROP DATABASE database 2 © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: CREATE Table Create new table through php. My. Admin By clicking on Database © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: CREATE Table Within php. My. Admin: Create table: Enter Name Enter number of columns © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: CREATE Table Within My. SQL: Create new table: CREATE Drop or rename table: DROP TABLE signup RENAME TABLE signup TO users Create new table for website where people can sign up for emails © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: CREATE Table CREATE TABLE signup ( id int NOT NULL AUTO_INCREMENT name varchar (30) NOT NULL email varchar (30) NOT NULL PRIMARY KEY (id) ) Open bracket; Create new lines First variable: id; data type: Integer; Automatically add 1 Second variable: name; alphanumeric text; Required Third variable: email; max length: 30 char. ; Required Declare primary key; in this case, “id” Close bracket © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: CREATE Table Click on Structure to see table structure id underlined PRIMARY KEY © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: CREATE Table Manually enter data by clicking Insert tab, then click Go © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Alter Table Results: -Data inserted -ID incremented To Delete a Row: DELETE FROM signup WHERE name = ‘Kathy Kar’ © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Alter Table Add column: phone (phone number) To add a column: ALTER TABLE signup ADD phone varchar(10) NOT NULL To delete a column: ALTER TABLE signup DROP COLUMN phone © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Alter Table Results: New column inserted: “phone” © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: Reference Manuals dev. mysql. com/doc/index. html © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

My. SQL: © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

Outline/ Learning Objectives © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

Outline/ Learning Objectives © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

Outline/ Learning Objectives © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

Outline/ Learning Objectives © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

Outline/ Learning Objectives © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1

Outline/ Learning Objectives © Stephan Sorger 2016; www. stephansorger. com; Data Science: SQL: My. SQL 1