Views

Loading...

来自 University of California, Davis 的课程

SQL for Data Science

1347 个评分

University of California, Davis

SQL for Data Science

1347 个评分

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!

从本节课中

Modifying and Analyzing Data with SQL

In this module, you will be able to discuss how to modify strings by concatenating, trimming, changing the case, and using the substring function. You will be able to discuss the date and time strings specifically. You will be able to use case statements and finish this module by discussing data governance and profiling. You will also be able to apply fundamental principles when using SQL for data science. You'll be able to use tips and tricks to apply SQL in a data science context.

Module Introduction1:14

Working with Text Strings7:24

Working with Date and Time Strings5:25

Date and Time Strings Examples5:29

Case Statements7:14

Data Governance and Profiling5:43

Using SQL for Data Science, Part 15:48

Using SQL for Data Science, Part 26:14

与讲师见面

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

So far in this course, we've learned a lot of statements and functions, and have gone

over quite a few tips and tricks that kind of help you put everything together.

In this video,

we're going to talk about another useful feature you can keep in your back pocket.

We're going to talk about using views,

including what they are and how you can use them.

Sometimes, when you're in a sticky situation and don't know how to solve it,

views can really just help you simplify the queries you write.

After this lesson, you'll be able to discuss how and when to use views

with your queries, explain how to use the AS function with views,

and explain the benefits and limitations when using views.

So as we've talked about earlier in the course,

we're always combining data from multiple sources or transforming it in some way.

As you know,

sometimes things like the order of operations can get a little tricky.

Instead of creating a whole new table,

sometimes we can create the illusion of a table by using a view.

A view is essentially a stored query, and it helps us clean up our queries and

simplify when we have to write.

In a view, you can add or remove columns without changing the schema.

You're not actually writing the query to the database or

anything, what you're doing is you're kind of storing it for the time being.

This is really helpful and pays off when we use it to encapsulate queries.

The syntax for this is you're just going to CREATE and

you can either specify a temporary view or just create a view.

You can also add in IF NOT EXISTS.

So if it doesn't already exist, then you have the view name and

you state what the conditions you want to go into the view.

An important thing to remember with this,

again, is the kind of illusion that you're creating in the table.

It's only stored for the duration of a session.

So it's important that if you're using the view in your query,

you can save the query.

And then if you come back the next day and start a new session and you're having

errors in your queries, it's probably because you didn't create that view again.

So just keep this in mind.

It will save you some headaches and time in the long run.

But a view won't be a permanent fixture in your SQL code.

Let's take a look at an example.

Let's say I want to get a count of how many territories each employee has.

If you look at our Diagram,

this information is separated out from each other.

I'm going to create a view, so

that on that view, I can just run a simple count on the number of territories.

So here I will create my view.

Then for my view, I'm just going to call it as my_view.

Then, the AS is really the select statement.

Remember how before we've used AS as an alias to say what we want our column

name to be?

You can think of a view as a whole table, but

now you're saying what do you want in all of that table?

Your AS is going to be a select statement for what I want in that view.

I'm going to indicate all of the columns I want and where I want those columns from.

Here, even in my view, I'm going to join multiple tables together to create this.

You can then see that if you execute this query,

you should get a little status update that it was executed.

To actually view the data though, you'll need to use a select statement.

For this, I'll just add SELECT * from my_view.

And you can see the table output there.

If I didn't want this view anymore, when I hit in my session it will go away,

or I can just drop the view.

So to do this, I will just say Drop View and

then the name of my view which is my_view.

Now that I have my_view out there,

I can actually perform even more queries on top of that.

I can now take that view and I can select the counts in the territory descriptions.

For example, this will give me an idea of

the counts of how many territories that each employee has.

I can then group it by the employee's last name and first name.

Now I can see the total count for each territory of what each employee has.

This would've been a little bit more complex to do if I tried to do

it all at once.

But creating view just made things really simple.

The beauty of the view is that it can be used like a table.

But it's unlike a table in that you don't have to have ETL or

run ETL on any of the data.

This helps a lot by encapsulating complex queries or

complex calculations that you're trying to write.

It can really help simplify it.

It can also be used in pretty much any database, except for stored procedures.

Views are really most helpful if you need to join a set of tables and

you're having trouble getting calculations.

Particularly those complex ones dealing with the order of operations in the right

order to get the output you're looking for.

Another benefit of views includes different securities or

write capabilities.

We talked about not being able to write data to an environment or

to a particular database.

Views are helpful because you're creating a view of a table but

not actually writing data to that table.

This is a way to get around some of those database writing limitations.

Another thing that views are helpful for

is to create a stepping stone in multilevel queries.

For example, let's say you create a query that counts the number of cells

that each person has made.

You could then write a query that groups the salespeople into a particular group.

Then you can count the sales of that group as well.

It just creates this multilevel dimension that you wouldn't have been able to do

elsewhere.

And then, it also helps so that you're not transferring any data through and

ETL process.

So as you can see, views are really useful.

Just remember that views are temporary and are only going to be good for

that session.

If you're reusing a query,

you'll have to recreate that view in another session as well.

Views are definitely something that will come in handy, if you're in a particularly

sticky situation or having some trouble with your calculation.

It can definitely make things a lot easier for you.

So I encourage you to give them a try and put them in your queries.

探索我们的目录

免费加入并获得个性化推荐、更新和优惠。

Coursera 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

© 2019 Coursera Inc. 保留所有权利。

通过 App Store 下载

通过 Google Play 获取