Explore
For Enterprise
Join for Free
Log In

University of Michigan
Gain new skills in construction management in under 7 months
Earn the Construction Engineering & Management MasterTrack™ Certificate from a top-ranked school.
University of Illinois at Urbana–Champaign
Take your seat in the global classroom with an iMBA
Apply by February 1 to waive your application fee and connect early with an iMBA student mentor.

浏览 Coursera 的全部课程

浏览
顶级课程
企业版
登录
免费加入

Views

Loading...

SQL for Data Science

加州大学戴维斯分校

4.6（3,116 个评分） | 130K 名学生已注册

课程 1（共 4 门，Learn SQL Basics for Data Science 专项课程）

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!

查看授课大纲

您将学习的技能

Data Science, Data Analysis, Sqlite, SQL

审阅

4.6（3,116 个评分）

5 stars
2,191 ratings
4 stars
692 ratings
3 stars
145 ratings
2 stars
53 ratings
1 star
35 ratings

MS

Mar 11, 2019

Great course i had a very good learning experience from this course and what i was expecting from this course i got that knowledge and i am very happy to take this course. thank you so much.

JP

Jan 15, 2020

This class has already paid for itself. I have used what I learned to write SQL reports to pull reports from our test database. I look forward to taking more classes in SQL Data Science.

从本节课中

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
AI Strategy Consultant for Accenture Applied Intelligence

脚本

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 sales 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 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

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

通过 Google Play 获取

Coursera
关于
管理团队
工作机会
目录
证书
MasterTrack™ 证书
学位
企业版
政府版
面向校园

社区
Learners
Partners
Developers
Beta Testers
Translators

连接
博客
Facebook
领英
Twitter
Instagram
YouTube
技术博客

更多
条款
隐私
帮助
内容访问
媒体
联系我们
目录
附属公司