Joining Tables: An Introduction

Loading...

University of California, Davis 提供のコース

SQL for Data Science

1643件の評価

University of California, Davis

SQL for Data Science

1643件の評価

As data collection has increased exponentially, so has the need for people skilled at using and interacting with data; to be able to think critically, and provide insights to make better decisions and optimize their businesses. This is a data scientist, “part mathematician, part computer scientist, and part trend spotter” (SAS Institute, Inc.). According to Glassdoor, being a data scientist is the best job in America; with a median base salary of $110,000 and thousands of job openings at a time. The skills necessary to be a good data scientist include being able to retrieve and work with data, and to do that you need to be well versed in SQL, the standard language for communicating with database systems. This course is designed to give you a primer in the fundamentals of SQL and working with data so that you can begin analyzing it for data science purposes. You will begin to ask the right questions and come up with good answers to deliver valuable insights for your organization. This course starts with the basics and assumes you do not have any knowledge or skills in SQL. It will build on that foundation and gradually have you write both simple and complex queries to help you select data from tables. You'll start to work with different types of data like strings and numbers and discuss methods to filter and pare down your results. You will create new tables and be able to move data into them. You will learn common operators and how to combine the data. You will use case statements and concepts like data governance and profiling. You will discuss topics on data, and practice using real-world programming assignments. You will interpret the structure, meaning, and relationships in source data and use SQL as a professional to shape your data for targeted analysis purposes. Although we do not have any specific prerequisites or software requirements to take this course, a simple text editor is recommended for the final project. So what are you waiting for? This is your first step in landing a job in the best occupation in the US and soon the world!

レッスンから

Subqueries and Joins in SQL

In this module, you will be able to discuss subqueries, including their advantages and disadvantages, and when to use them. You will be able to recall the concept of a key field and discuss how these help us link data together with JOINs. You will be able to identify and define several types of JOINs, including the Cartesian join, an inner join, left and right joins, full outer joins, and a self join. You will be able to use aliases and pre-qualifiers to make your SQL code cleaner and efficient.

Module Introduction1:04

Using Subqueries6:55

Subquery Best Practices and Considerations5:44

Joining Tables: An Introduction5:17

Cartesian (Cross) Joins4:07

Inner Joins8:10

Aliases and Self Joins6:00

Advanced Joins: Left, Right, and Full Outer Joins7:35

コースの提供者

Sadie St. Lawrence
Data Scientist at VSP Global
Founder and CEO Women in Data (WID)

Hello, and welcome back.

In our previous couple of videos, we began to introduce some ways in which you can

combine information from multiple tables primarily by using subqueries.

As we learned though, due to some limitations,

subqueries aren't always the best way to combine information.

In this video, we're going to begin to go over joins,

another very popular method for combining data from multiple tables together.

After this lesson, you'll be able to discuss the benefits of relational

database systems, describe what a JOIN is and

how to use the JOIN function to combine information from multiple tables and

describe how a key field is used to link data together.

Before we start to talk about joins and why we use them,

let's do a quick recap on relational databases and why we use them.

Remember how we talked earlier in the course about how

understanding the database and how they relate to each other is

really important in terms of writing your queries?

Well, I think you're really going to start to see why

when we start joining tables together.

Until very recently, we've just been working off of a single table and

we've been able to form a lot of functions and get data.

But more often than not, the data we're after is stored in

multiple tables and so that poses a problem when we want to bring

all this data together to blend it into analysis that we need.

And then, there's some reasons why we store the data in multiple tables.

One of these is that it just breaks down the data for more efficient storage.

So, if we have an individual record of someone and

instead of breaking it up between maybe consumer demographics and

then the products that they ordered and had all the information in one table,

we would have duplicate information within a record.

So the orders that they make may change, but

something like their location may not change as frequently.

So when we split out the information into separate tables, it helps us to

more efficiently store the data that we have, so we're not duplicating records.

This also makes it a lot easier to manipulate the data, so

when we want to update a record or change something, when that data isn't

touching all different levels, it's a lot easier to manipulate.

This allows us the opportunity to scale the information.

So there's a lot of reasons why breaking down the data into

different tables is beneficial.

But, how we break the data down into tables

is often modeled after a business problem or business process.

So remember,

we talked about this in terms of data modeling in our relational databases.

A lot of times we'll take the tables and then reduce them in a way that has its own

kind of theme or process, and then to get the data to come together, we use keys.

This helps us so we don't have to duplicate the data in multiple tables.

We're able to join those tables together based on an individually unique key.

So, this key serves as a link between the tables and

this key is a critical piece to being able to join records and tables together.

So, the first thing we want to go over with joins is that these joins

are what associate the correct records from each table on the fly.

To help explain this, again, we'll go back to our example

of you have a table that list the different customer demographics but

you want to know all of the orders that the customer has made.

So, a join will allow you to associate the correct customer information

with the correct order, quickly and on the fly with your query.

This also allows you to retrieve the data from multiple tables in just one query.

So instead of what you've seen in the past, and the reasons why you do

subqueries instead of going and grabbing a portion from one table and

then a portion from another table and then trying to merge that information together.

Instead, this allows you to write it all in one query and

easily bring data from multiple sources together.

It's important to note that a join is not a physical entity.

In other words, it doesn't create anything permanent.

It only exists for the duration of the query execution,

to help you get that information you're trying to get.

Now there are several types of joins, and we're going to

go over some of the important ones beginning in the next video.

But, let's stop here and reflect what we've learned about joins so far.

We began with a review of relational database systems and

discussed how multiple tables are useful and efficient for storing data.

We introduced the concept of a join and

how it's used to combine information from multiple tables into one query.

And, we wrapped up by talking about how keys help us do this.

Don't worry, there's more to come.

コース一覧で検討

サインアップは無料です。今すぐサインアップして、パーソナライズされたお勧め、更新、サービスを利用しましょう。

コーセラは、世界トップクラスの教育を世界中に配信しています。最高峰の大学や組織とのパートナーシップに基づくオンラインコースが提供されています。

© 2019 Coursera (株)すべての権利予約します。

App Storeからダウンロード

Google Playで取得