4.3 out of 5
4.3
7632 reviews on Udemy

SQL for Data Analysis: Weekender Crash Course for Beginners

Using MySQL but applicable to Oracle SQL, Microsoft SQL Server, and PostgreSQL. Taught by a Data Scientist and PM.
Instructor:
A Course You'll Actually Finish
45,078 students enrolled
English More
Analyze user behavior
Find actionable customer/business insights
Make data-driven decisions
Measure and track marketing efforts
Discover sexy marketing stats (e.g. 1 in 4 people love toast!)

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.

Your Story:

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?

Our Story:

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)!

FAQs:

  1. 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.
  2. 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!

1
Introduction to the Course and Your Instructors
2
WATCH ME NOW!

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)

3
A Note from Your Humble Leaders
4
Getting Started (Downloading a Text Editor!) - All OS

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.

http://www.sublimetext.com/

5
Getting set up: No installation necessary!

Intro to MySQL

1
What the heck is a relational database?
2
[QUIZ] What the heck is a relational database?

This is just a short quiz to reinforce a couple of the points that we made in the last lecture.

3
Da SQL Basics: Skeleton of a Query

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

BRAINBUSTER:

  • 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!

4
SQL Query Skeleton

Here are a couple questions about the structure of a SQL query.

5
[Brainbuster] Build Your First Query!

Basic Queries

1
COUNT() and GROUP BY

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!

BRAINBUSTER

  • 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)?
2
Brainbuster TWEAK
3
COUNT() and GROUP BY

This quiz will check your answers from the brainbusters and information from the previous lectures.

4
[Brainbuster] Organize movies by rating!

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":

SELECT

i.store_id, f.rating, count(f.film_id)

FROM

film f, inventory i

WHERE

f.film_id = i.film_id

GROUP BY 1,2

;

5
Connecting Tables

BRAINBUSTER:

  • 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]

6
Connecting Tables (Warm-up for Brainbuster)

Make sure to take this quiz before you try solving the brainbuster!

7
[Brainbuster] Connect Tables
8
[Brainbuster] Finding your best selling products (Rental Count by Movie)
9
Having trouble understanding "connecting tables"?
10
Arithmetic & Order By

BRAINBUSTER

  • Run an analysis to see what store has historically brought in the most revenue.
11
[Brainbuster] Finding Your Top-Performing Store
12
LEFT(), MIN() AND MAX()

BRAINBUSTER

  • Give me every customer’s last rental date
  • Give us revenue by each month
13
[Brainbuster] Finding Your Active Users - Last Rental Time by Customer
14
[Brainbuster] Finding Month over Month Revenue Growth
15
DISTINCT

BRAINBUSTER

  • 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.
16
[Brainbuster] Find Distinct Films Rented Each Month
17
IN()
18
Comparison Operators and HAVING

BRAINBUSTER

  • 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
19
[Brainbuster] Breaking Down Revenue by Store and Rating
20
Nested Queries
21
Nested Queries vs. Temporary Tables
22
Add your new skills to LinkedIn!

BONUS Lectures

1
BONUS: JOINs

We learned how to connect tables, this lecture goes into more advanced ways to do that.

2
[Brainbuster] BONUS: JOINs
3
What is a Cohort Analysis?

See the link below from Kissmetrics for further reading on Cohort Analyses.

4
Cohort Analysis Preamble
5
Cohort Analysis Query - Time to get FANCY
6
A Request from Your Humble Leaders

Appendix A: Alternative Installation

1
Installation Guide
2
Getting Set Up: (Installation) - Mac Version

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

--> http://www.sequelpro.com/download

--> 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!!!

3
Getting Set Up (Installation) - Windows 32-bit Version

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:

  1. 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/
    1. During installation choose the "server only" setup type
    2. Keep default configurations
    3. Choose a root password (you'll be using this to connect to the SQL server whenever you want to run queries)
    4. 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.
  2. Download the sample database we'll be working with (files at bottom)
    1. Unzip files
  3. Install the sample database (you'll only need to do this once):
    1. Open up the MySQL Command Line Client
    2. Enter the root password you created during step 1
    3. Type in: "SOURCE "
    4. Drag the sakila-schema.sql file onto the Command Line Client
    5. Delete the " "s around the path
    6. Press Enter
    7. Repeat steps 3-6 for the sakila-data.sql file.
  4. Download HeidiSQL (this will be the interface you'll be using to explore tables and run queries): http://www.heidisql.com/
  5. Connect to the Server and explore the installed Sakila database (you'll need to do this every time you want to run queries)
    1. Open up HeidiSQL
    2. Click 'New'
    3. Type in your root user password created in step 1
    4. Click 'Save'
    5. Rename your session name to "Sakila"
    6. 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.

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.3
4.3 out of 5
7632 Ratings

Detailed Rating

Stars 5
3701
Stars 4
2645
Stars 3
905
Stars 2
221
Stars 1
160
b1598c9112dcf84c2509ad847fddb63f
30-Day Money-Back Guarantee

Includes

4 hours on-demand video
7 articles
Full lifetime access
Access on mobile and TV
Certificate of Completion