SQL for Data Analysis: Weekender Crash Course for Beginners
Buff up your resume/CV and become interview-ready by learning real-world SQL in this course.
This SQL course has been taken by fine marketing folks at Google, Facebook, Amazon, Lyft, and Udemy.
Bill was looking to move into a more analytical role and saw SQL as a requirement in the job listings he saw. He wanted to add “SQL” as a skill to his resume/CV with a clean conscience and back it up if any questions arose in the interview. But getting there would take forever. Better to just “fake it til’ you make it”… right?
Joe was working in a marketing position at a small company. He had a bunch of creative ideas but sometimes felt like he was shooting in the dark and guessing at what customers were doing. If only he had some insights about user behavior so he could be a more data-driven marketer. But data analysis is only for technical folks… right?
David and Pete joined Udemy with little to no technical experience. But after a lot of trial and error, headaches, and help from their friends, they got good enough to uncover unique insights for themselves, their team, and their company using SQL. They were able to discover interesting things about user behavior, create dashboards to track and measure progress on team goals, pull data for the exec team to use in investor pitch decks, and get data driven about decisions they made. They’ve since helped many team members buff up their data analysis skills and helped students land jobs!
What You’ll Learn:
If you have no technical background, don’t be afraid! We’ve distilled our knowledge and experience using SQL into a short course so that by the end, you’ll have the raw skills to do some real data analysis for your company using SQL – a language virtually EVERY company uses. Note: this courses teaches you real-world SQL – not just the theory in abstract, but real skills you can use to get more data-driven in your current job.
How This Course is Structured:
- In this course we’ll be pretending we’re a real business (i.e. Blockbuster) so the stuff you learn will be easy to apply to your own situation/company. No abstract/theoretical mumbo jumbo.
- We’ll go through queries a real business would run while also teaching you the raw skills undergirding those queries so you can adapt those skills to create custom queries for your own specific purposes
- To reinforce learning, we have exercises and quizzes scattered throughout the course so you can learn by doing
- We’ll have a bonus section where new lectures will be added occasionally (including student-requested lectures, more advanced topics, strategies for getting unstuck, etc.)
- We’ll be actively involved in the discussion board answering any questions you might have! Don’t be afraid to ask!
A Note About Pedagogy:
We know what it’s like to buy a book, feel good about yourself, never finish it and have nothing to show for it. We don’t want that to happen with this course. We want this to be a course you’ll actually finish. We believe half of learning is motivation and engagement, so we’ve tried extra hard to make this course fun, relevant, entertaining, and punchy – no frills, no dragging things out, just the good stuff. Heck, you might even find yourself skipping a party to spend time with your new best friends (i.e. us)!
- Do I need to purchase any software to take this course? Nope! Everything we use to do data analysis with MySQL is completely free. We’ll walk you through the installation and set-up of any software we’ll be using.
- Can I take this course with Linux? The set-up and installation lectures we’ve created are for Windows and Mac, and we don’t currently have specific installation lectures for Linux.
Welcome to the course!
We really want you to finish this course. But instead of setting that as your goal, instead, we challenge you (!) to watch just 30 seconds a day for the next two weeks. That's it. Can you spare 30 seconds a day? I think you can.
Note: 2 Links below:
1.) Link to download Udemy's mobile app so you can learn on the go
2.) Link to Stanford Professor BJ Fogg's Tiny Habits method (the inspiration for this 30 second challenge)
In this lecture we will take the first step to getting started!
We will be downloading an AWESOME (and free) text editor called Sublime Text.
Intro to MySQL
This is just a short quiz to reinforce a couple of the points that we made in the last lecture.
In this lecture we go over the basic outline of a SQL query.
A SQL query is made up of three main sections and phrases. They are SELECT, FROM, WHERE
- Write a query to find all customer names (first and last) and email addresses for customers of store number 2
We will be showing you the query and answer for that brainbuster in the next lecture. Good luck!
Here are a couple questions about the structure of a SQL query.
So in the last lecture we showed you a way to cheat and see the number of results that you were getting from a query. For example we wanted to know how many actors/actresses we had in our database so we ran a query and looked at the number of rows the result had. However, there is a faster, better and cleaner way to do that. That is to use the COUNT() function.
The count function will simply tell you how many items meet the requirements that you set forth in the query.
COUNT() and GROUP BY are very commonly used in conjunction in queries. This will allow you to break the COUNT up by another dimension. For example if we wanted to see the number of movies in each store, we would GROUP BY store_id and then COUNT(film_id).
Now let's have you practice!
- Which rating do we have the most films in? Write a query that will tell us the number of films that we have in each film rating.
- ADVANCED (NOTE: We've changed the Advanced Brainbuster question from the one in the video so as not to introduce a new concept you would have to use to figure out the one we present in the video): Which rating is most prevalent in each price (use only 1 query)?
This quiz will check your answers from the brainbusters and information from the previous lectures.
NOTE: You may have noticed that we changed the "Advanced Brainbuster" question to ask about ratings per price instead of ratings per store. The reason for this is because to get ratings per store, you'll need to learn a new concept (one we cover in a future lecture). To not overwhelm you, we changed it to ratings per price which you should be able to do with the knowledge you've attained thus far!
Buuuut, for those of you who worked hard to figure out the original question, the query would have been as follows for "ratings per store":
i.store_id, f.rating, count(f.film_id)
film f, inventory i
f.film_id = i.film_id
GROUP BY 1,2
- Film, Category Name, and Language Name (connect 3 tables)
- ADVANCED: (combining 3 tables, group by, and count)
- How many times has each movie been rented out?
[Figure out for every film, how many actors in each in film, and how many of that movie we have in inventory]
Make sure to take this quiz before you try solving the brainbuster!
- Run an analysis to see what store has historically brought in the most revenue.
- Give me every customer’s last rental date
- Give us revenue by each month
- Find the number of distinct films that are rented each month. It is important for your business to know what percent of their movie library is actually getting rented and earning money for the company.
- Okay, your micromanaging boss is back at it again. He now wants to know how much revenue that store 1 has made from movies that are rented R or PG-13 between
We learned how to connect tables, this lecture goes into more advanced ways to do that.
See the link below from Kissmetrics for further reading on Cohort Analyses.
Appendix A: Alternative Installation
Steps to Install/Setup MySQL on Mac (if on Windows/PC see next lecture):
1. Install Homebrew through the Terminal
--> Open terminal
--> visit http://brew.sh/
--> PASTE INTO TERMINAL: ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
2. Install MySQL using Homebrew
--> PASTE INTO TERMINAL: brew install mysql
3. Set your password
PASTE THE FOLLOWING COMMANDS INTO TERMINAL
--> PASTE INTO TERMINAL: mysql.server start
--> PASTE INTO TERMINAL: mysql.server stop
--> PASTE INTO TERMINAL: mysqld_safe --skip-grant-tables
OPEN NEW TAB IN TERMINAL
--> PASTE INTO TERMINAL: mysql -u root
REPLACE 'password' WITH YOUR DESIRED PASSWORD WITHIN THE SINGLE QUOTES i.e. 'Pass1234!'
THIS WILL BE WHAT YOU USE TO LOGIN TO MYSQL AND SEQUEL PRO MOVING FORWARD
--> PASTE INTO TERMINAL (including semicolon): UPDATE mysql.user SET authentication_string=PASSWORD('password') WHERE User='root';
--> PASTE INTO TERMINAL: FLUSH PRIVILEGES;
CLOSE THIS TAB OF THE TERMINAL AND OPEN A NEW ONE
--> PASTE INTO TERMINAL: mysql -u root -p
You will be prompted to enter the password you set above: Enter password you set above without single quotes
REPLACE 'password' WITH YOUR DESIRED PASSWORD WITHIN THE SINGLE QUOTES i.e. 'Pass1234!'
--> PASTE INTO TERMINAL: ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
YOU ARE DONE WITH THE TERMINAL!!!!
4. Download Sequel Pro
--> Download and open Application
5. Set Login in Sequel Pro
--> Choose the 'Socket' option
--> NAME: SQLforNEWBS
--> USERNAME: root
--> PASSWORD: enter password you set above without single quotes
--> Before clicking 'Connect', click 'Save to Favorites'
6. Download and Install the Sakila DB
--> Download the DB file from the 'Resources' Section of this lecture!
--> Open the .zip file
--> Drag and Drop the file sakila-schema.sql into the 'Query' tab in Sequel Pro
--> Select All
--> Click 'Run'
--> Drag and Drop the file sakila-data.sql into the 'Query' tab in Sequel Pro
--> Select All
--> Click 'Run'
Enjoy the course!!!
This the most tedious part of this course but you'll only have to do it once so let's knock it out in the next 10 minutes and we'll be home free from here on out!
Steps for getting set up:
- Install a local MySQL server: http://dev.mysql.com/downloads/installer
NOTE (December 11, 2014):It seems some people have been having trouble installing MySQL with the new installer they released. To use the old installer go here and download version 5.6.21: http://downloads.mysql.com/archives/installer/
- During installation choose the "server only" setup type
- Keep default configurations
- Choose a root password (you'll be using this to connect to the SQL server whenever you want to run queries)
- Recommended: Keep 'Start the MySQL Server at System Startup' checked so the server automatically fires up when you start your computer. Otherwise you'll have to remember to manually start up the server yourself before you start querying.
- Download the sample database we'll be working with (files at bottom)
- Unzip files
- Install the sample database (you'll only need to do this once):
- Open up the MySQL Command Line Client
- Enter the root password you created during step 1
- Type in: "SOURCE "
- Drag the sakila-schema.sql file onto the Command Line Client
- Delete the " "s around the path
- Press Enter
- Repeat steps 3-6 for the sakila-data.sql file.
- Download HeidiSQL (this will be the interface you'll be using to explore tables and run queries): http://www.heidisql.com/
- Connect to the Server and explore the installed Sakila database (you'll need to do this every time you want to run queries)
- Open up HeidiSQL
- Click 'New'
- Type in your root user password created in step 1
- Click 'Save'
- Rename your session name to "Sakila"
- Click 'Open'
Done! If you have any issues - leave a question in the discussion section of this lecture (right-hand column) and we'll usually get back to you within 24 hours.