Sql For Newbs

What the heck is a relational database? Preview 05:56

[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.

Da SQL Basics: Skeleton of a Query Preview 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!

SQL Query Skeleton

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

[Brainbuster] Build Your First Query! Preview 05:32

COUNT() and GROUP BY Preview 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 Preview 00:15

COUNT() and GROUP BY

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

[Brainbuster] Organize movies by rating! Preview 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

;

Connecting Tables Preview 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]

Connecting Tables (Warm-up for Brainbuster)

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

[Brainbuster] Connect Tables Preview 05:32

[Brainbuster] Finding your best selling products (Rental Count by Movie) Preview 05:23

Having trouble understanding "connecting tables"? Preview 00:13

Arithmetic & Order By Preview 11:37

BRAINBUSTER

• Run an analysis to see what store has historically brought in the most revenue.

[Brainbuster] Finding Your Top-Performing Store Preview 04:14

LEFT(), MIN() AND MAX() Preview 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 Preview 00:36

[Brainbuster] Finding Month over Month Revenue Growth Preview 04:53

DISTINCT Preview 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 Preview 04:09

IN() Preview 06:29

Comparison Operators and HAVING Preview 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 Preview 04:57

Nested Queries Preview 06:00

PSA: Temporary Tables on SQLSnack Preview 00:27

Nested Queries vs. Temporary Tables Preview 02:50

Add your new skills to LinkedIn! Preview 00:07

BONUS: JOINs Preview 19:19

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

[Brainbuster] BONUS: JOINs Preview 05:02

What is a Cohort Analysis? Preview 06:49

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

Cohort Analysis Query: Setting the Stage Preview 06:49

Cohort Analysis Query: SQL Fanciness Preview 13:49

Cohort Analysis Query: Finishing Touches Preview 11:17

Installation Guide Preview 00:18

Getting Set Up: (Installation) - Mac Version Preview 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!!!

Getting Set Up (Installation) - Windows 32-bit Version Preview 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.