Intro to Joins

Learning Objectives:

• Identify foreign keys in an ERD

• Apply INNER JOINS to obtain information from multiple tables

• Use aliasing with table names

What is a Join?

Joins combine columns from one or more tables into a single output table.

There are several types of joins that often can cause confusion especially when first starting out with SQL queries. Before exploring the different types of joins, it can be easiest to become familiar with the most common type: INNER JOIN. In fact, INNER JOIN is so common that simply typing the keyword JOIN will refer to an INNER JOIN.

It can be helpful to learn the applications of joins by working with an existing database and seeing which type of questions require a join. For this lesson, we will continue to explore the "world" database that you should have loaded into MySQL Workbench in an earlier lesson.

Summary:

This lesson introduced the concept of joins by practicing several inner joins. In order to make a join, there must be a common column in each table. A foreign key in one table references the primary key in another. We specify the table name with the column using table.column. It is common to alias table names with the first letter or two of the table name. The nature of databases makes joins an essential SQL command. You will get more comfortable with joins as you continue to practice.

More SQL Joins

Learning Objectives:

• Differentiate between different type of joins: Inner Join, left join, right join and full outer join

More SQL Joins

You have practiced Inner joins (JOIN). This lesson will continue to develop your conceptual understanding of different joins.

Summary

This lesson demonstrated the difference in results that you would get from applying inner, left, right, and full joins. When there are missing values in your database, it is especially important to consider what you want your results to include. MySQL does not have a FULL OUTER JOIN, but it can be achieved by using UNION with a left and right join. It is helpful to practice all of these joins on a simple database such as the one here so that you can recognize the difference in outputs.

SQL Subqueries

Learning Objectives:

• Describe what a subquery is

• Explore examples of subqueries in different places

Subqueries

Subqueries are also known as inner queries or nested queries. A subquery is used to perform the query in multiple steps. The subquery is placed in parenthesis and is completed first. The subquery must be able to run on its own. Subqueries can occur anywhere within the main query. To help you understand how subqueries are used, we will look at at example of a subquery occurring in three different places:

• WHERE

• SELECT

• FROM

Summary

This lesson introduces the concept of subqueries by providing examples of how a subquery might be used in different clauses of the main query. It is important to realize that there are usually multiple ways to achieve the output you are seeking, and subqueries are another tool to add to your growing SQL toolbox.

Full page PDF link

Querying MySQL with Python

Objectives:

• Use Python to query a MySQL database

You have already had some experience using SQLAlchemy to execute SQL queries in a sqlite file within Python. The ability to transition between SQL and Python is an important skill for data scientists. This lesson shows you how to connect to your local MySQL server with pymysql.

Do NOT use Colab!

You will need to work on your local machine to connect to your MySQL server.

Reminder: you should create a new GitHub repository and jupyter notebook for each assignment (except for your Project 3 Parts 1-4).

Summary

This lesson taught you how to connect your local python to your MySQL database to perform SQL queries.

Congratulations! You are now able to query your local MySQL databases with Python!

Introduction to Database Design

Objectives:

• What is a relational database

• What is important to remember about database design?

• Designing with primary and foreign keys

What is a database?

Databases are merely collections of organized information that can easily be accessed, managed, and updated. As a data scientist, you need to be familiar with how databases are built and understand the rules by which it stores data!

Summary

This lesson provided an overview for understanding relational database design. We use multiple tables that are connected by foreign keys. This ultimately saves storage space and allows us only to query what we need from a given database. As we have already seen, an ERD can help us understand an existing database. It is also a great place to start when designing a database! We will go into more details related to database design in the next few lessons.

Normalization

Objectives:

• Explain what is meant by database normalization and why it is important

• Recognize and apply 3 forms of normalization

What is Normalization?

Database normalization is simply a convention for splitting large tables of data into smaller separate tables with the primary goal being to not repeat data.

Why is this so important? Let's say that you wear a watch so you can check the time, because it's very important for you to know what the current time is. Would wearing eight watches make it easier? No way! Now we have eight conflicting accounts of what the proper time is. Worse yet, if we ever want to update the time, we'd have to do it for every watch independently. That's not very efficient!

Summary

This lesson introduced three forms of normalization that you should be aware of when designing a database. You should use these conventions as a guide for designing your ERDs. Always remember, however, that they are common conventions, and not absolute rules. It is possible to take normalization to an extreme. For example, a simple address field. One state can have many cities, one city can have many streets, one street can have many buildings, one building can have many apartments, one apartment can have many residents... and so on. Yikes, that can get really crazy really quickly! Normalization conventions help databases save storage space and eliminate data repetition. It also allows one piece of information to be updated without disrupting the rest of the data. Consider these conventions for normalization when you are using or designing a database.

Database Relationships

Objectives:

• What are different types of relationship in the context of databases?

• What is a join table and how is it used?

Relationships allow information to be stored in different tables, yet recombined when needed (by using joins). The two tables will be linked by a foreign key in one table matching the primary key of another.

In this lesson, we will explore three types of relationships:

• One to One

• One to many (or Many to One)

• Many to Many

Summary

You should be aware of the different types of relationships that are commonly found in a relationship database. It is best to "talk out" the relationship to confirm if it is one to one, one to many, or many to many. Understanding how the data is related will help you structure your database or will help you when querying it. One to one and one to many relationships rely on foreign keys, while a many to many relationship uses a separate join table as the "glue" between the two tables.

Objectives:

• Familiarity with database conventions

• Familiarity with the different types of data that can be stored in MySQL and when to use each

Conventions

We will be following a set of conventions to create our database. We don't have to follow these conventions, but we recommend our students to follow them when setting up a database in this course.  Note that we will be using many databases that do NOT follow these conventions. Still, it is good to be aware of some best practices.  

1. make the table name plural and ALL lowercase - make it plural (ex. users, leads, sites, clients, chapters, courses, modules)

2. use "id" as the primary key - name it id (also make it auto-incremented).

3. name foreign keys with singular_table_name_id when referencing to a primary key in another table name it [singular name of the table you're referring to]_id (ex. user_id, lead_id, site_id, client_id, chapter_id, course_id, module_id).

4. use created_at and updated_at as columns in your tables

While these are some best practices, most of the SQL assignments in this course will not follow these conventions.  Similarly, down the line, you may find yourself working with a company that has set up their database with different conventions. 

Summary

This lesson introduced some best practices for database conventions. It also listed many of the variable types that you will encounter when using MySQL. This information can serve as a reference when creating a database.

Simple Data Types

The following are the data types that you will be using 95% of the time. Although there are quite a few other data types that you can use, focus on these for now.

• VARCHAR(number of characters)

  • Used to store non-numeric values that can be up to 255 characters. It is called a VARCHAR because it can store a variable number of characters and will only use the space required for each record that is stored in the database. VARCHAR should be used for values with different character lengths like an email, first_name, or last_name.

• CHAR(number of characters)

  • Also used to store non-numeric values, however, it will use up all space for the set number of characters regardless of what value is added. For instance, if I set CHAR(15), and I try to store the value "Coding", it will use up the equivalent of 15 characters even though "Coding" is only 6 characters long. Char is good to use for things that will always be a given number of characters. Char would work well for something like a state_abbreviation.

• INT

  • Used to store integers.

  • The columns that you will find mostly using the INT are things like a unique identifier for each table. The majority of rows in a table will not exceed 2.1 billion records. INT is good to use for most normal number values like a phone_number or a zip_code.

  • unsigned (positive numbers only) - can store numerical values from 0 up to 4294967295

  • signed (positive and negative numbers) - can store numerical values from -2147483648 up to 2147483647

• BIGINT

  • BIGINT would be used for columns that would need to store huge numbers. In most cases, you wouldn't need BIGINT, but if you wanted to store something like a Facebook id when using Facebook's API, since they have over a billion users the id will need to be a data type of BIGINT.

  • unsigned (again positive numbers only) - can store numerical values from 0 up to 18446744073709551615

  • signed (positive and negative numbers) - can store numerical values from 9223372036854775807 to -9223372036854775808.

• TINYINT

  • TINYINT would be good to use for numbers that will be relatively small. A good example of something that would use a TINYINT is user level identifier (0 - inactive user, 1 - active user, 9 - admin).

  • unsigned - can store numerical values from 0 up to 255

  • signed - can store numerical values from -128 up to 127

• FLOAT

  • Used to store floating point numbers (numbers that need to have decimal places). An example column for this would be like an item_cost.

• TEXT

  • Used to store a large amount of text, like a description, message, or comment. Use this for any text that VARCHAR() is too small to handle.

• DATETIME

  • used to store a date and time in the format YYYY-MM-DD hh:mm:ss

Forward Engineering

Learning Objectives:

• Create a database by forward engineering an ERD in my SQL workbench

What is Forward Engineering?

The process of designing the ERD first and then creating the database is called "Forward Engineering." MySQL workbench provides us with an easy way to actually create a database easily from the ERD we created.

By using the "Forward Engineering" tool, MySQL workbench will create a SQL script that creates the tables from the information provided in the diagram.

Summary

This lesson showed you how to produce a database from an ERD that you created in MySQL workbench. This process is called "Forward Engineering" because typically you would create the ERD in the early stages of the database design process and then create the database based on the model. (Note that creating a model from an existing database is known as "reverse engineering"). MySQL Workbench allows for these models to be converted into actual databases without the designer needing to write the SQL syntax required to create the database.

Database Admin with Python

Objectives:

• Use Python to create a MySQL database

• Save a pandas data frame as a table in a MySQL database

You have already had some experience using SQLAlchemy to execute SQL queries within Python. The ability to transition between SQL and Python is an important skill for data scientists.  This lesson shows you how to create a MySQL database using python, and then how to add pandas data frames to the database. When you are finished, you will be able to open your database in MySQL workbench.