SQL for Newbs Masterclass: Beginner Data Analysis

For non-technical folks and marketers. Mainly using MySQL but applicable for PostgreSQL / Postgres, SQL Server, etc.
4.4 (2,246 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
20,071 students enrolled
$150
Take This Course
  • Lectures 35
  • Contents Video: 3.5 hours
    Other: 2 mins
  • Skill Level All Levels
  • Languages English, captions
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 11/2014 English Closed captions available

Course Description

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. 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 and in abstract, but real skills you can talk about during an interview and help you 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.  If you can 


Course last updated April 6, 2016 - new installation lecture

What are the requirements?

  • No previous technical knowledge required

What am I going to get from this course?

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

What is the target audience?

  • Marketers
  • Startup folks
  • Non-technical folks
  • Aspiring Data Analysts
  • Recent College Grads
  • Job-seekers

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Welcome!
Introduction to the Course and Your Instructors
Preview
03:59
02:59

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)

A Note from Your Humble Leaders
Article
02:20

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/

Installation Guide
Article
16:47

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

13:14

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.

Section 2: Intro to MySQL
What the heck is a relational database?
Preview
05:56
1 question

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

10:23

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!

3 questions

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

[Brainbuster] Build Your First Query!
05:32
Section 3: Basic Queries
06:23

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)?
Brainbuster TWEAK
Article
3 questions

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

04:17

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

;

06:40

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]

2 questions

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

[Brainbuster] Connect Tables
05:32
[Brainbuster] Finding your best selling products (Rental Count by Movie)
05:23
Having trouble understanding "connecting tables"?
Article
11:37

BRAINBUSTER

  • Run an analysis to see what store has historically brought in the most revenue.
[Brainbuster] Finding Your Top-Performing Store
04:14
11:04

BRAINBUSTER

  • Give me every customer’s last rental date
  • Give us revenue by each month
[Brainbuster] Finding Your Active Users - Last Rental Time by Customer
00:36
[Brainbuster] Finding Month over Month Revenue Growth
04:53
06:28

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.
[Brainbuster] Find Distinct Films Rented Each Month
04:09
IN()
06:29
05:26

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
[Brainbuster] Breaking Down Revenue by Store and Rating
04:57
Section 4: Advanced Querying
09:12

BRAINBUSTER

  • BREAK IT DOWN: Revenue by actor/actress
[Brainbuster] Find the Revenue by Actor/Actress + Conclusion (and Final Tips)!
08:02
Add your new skills to LinkedIn!
Article
Section 5: BONUS Lectures
19:19

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

[Brainbuster] BONUS: JOINs
05:02
06:49

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

Cohort Analysis Query - Time to get FANCY
19:18
A Request from Your Humble Leaders
Article

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

This series is built upon the conviction that half of learning (ok, maybe 40%) is motivation and engagement.

This series aims to make each course engaging, not gargantuan in length, and just plain fun/ny (we’ll try at least). Plus, there will be quick wins throughout the course so you can visibly see yourself learning new skills able to applied in the real world.

In addition, we’ll hold contests every now and then (ooh~ prizes!) to motivate you to finish your course.

This is gonna be a course you’ll actually finish.

Instructor Biography

David Kim, Product Manager at Udemy

Hey! I'm David and I work at Udemy doing Engagement Marketing for the Instructor Team.

I graduated from UC Berkeley as a Rhetoric major (read: no technical skills) but then joined Udemy in 2012 when the company was about 20 employees. I initially joined Udemy's course quality and review team and about a year later became Udemy's first email marketer on the instructor team.

At Udemy, I was introduced to the basics of SQL and soon started using it everyday. After a lot of messing around, StackOverflow-ing, and working with my co-instructor and colleague Peter on some mind-numbing queries, we've started helping others at our company learn the ropes.

Instructor Biography

Peter Sefton, Data Analyst at Udemy

Hi there! I'm Peter and work here at Udemy on the Growth team. I am focused on data analytics for the growth marketing team.

I graduated from Stanford University and started working at Udemy in the Summer of 2013. I initially came in as a support team member for the Instructor team. I helped instructors as they went through all stages of thinking about, creating and managing their courses. Since the Udemy team was quite small (~35 people) everyone was responsible for taking ownership of their part of the business and making sure that things were running smoothly.

A very important quality of the company is being data driven and empowering all employees to parse and use the companies database to get and track the information that he/she needs. This is why and how I learned SQL. I did not know anything about databases or querying languages before starting at Udemy, but was able to learn quickly by being curious and working with my colleague and co-instructor, David Kim.

I am hoping to share the knowledge and skills that I have learned on the front line here at Udemy with anyone interested!

Ready to start learning?
Take This Course