Explore
For Enterprise
Join for Free
Log In

검색
포 엔터프라이즈 (For Enterprise)
로그인
무료 회원 가입

Using Subqueries

Loading...

SQL for Data Science

캘리포니아 대학교 데이비스 캠퍼스

4.6(2,739개의 평가) | 120K명의 학생이 등록함

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(2,739개의 평가)

5 stars
1,914 ratings
4 stars
614 ratings
3 stars
128 ratings
2 stars
52 ratings
1 star
31 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.

NP

Jan 07, 2018

A nice course to get introduced to writing SQL queries in Data science. Provides hands on exercises that boost confidence. Genuinely appreciate the ease with which SQL topics are covered

수업에서

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

스크립트

Things are starting to really heat up for us now with SQL because we're finally getting to a point where we're going to be able to use multiple tables together. Up until this point we've just been working with a single table, and we've learned how to do some cool things. We can get lots of data returned. We can perform some calculations on it, but we're still really limited in terms of we can do because we're only using one table. And where we get a lot of value in relational databases is when we combine different facts, dimensions and sources together to obtain new meaning. One of the easiest ways to do that is with something called subqueries, which is what we're going to focus on in this lesson. After this video, you should be able to define subqueries, discuss advantages and disadvantages to using subqueries. Explain how using subqueries can help us merge data from two or more tables together and write efficient subqueries to make your data analysis smoother. So to begin let's define subqueries. In essence, subqueries are queries embedded into other queries. It's literally just a query inside another query. So far we've only worked with a single select statement and a single table, but when you're working with a relational database, since data is stored in multiple tables, you may need to merge these together or get information from multiple sources. Data scientists often use subqueries to select specific records or columns and then use that criteria as a filtering criteria for the next thing they want to select. Not only are subqueries helpful when it comes to getting information from multiple tables, but they're often used for adding additional criteria like filtering criteria that's not in your current table from another table into your query. Let's look at another example of this. Let's say we want to know the region each customer is from, who has had an order with freight over 100? Maybe we're wanting to look at a shipping cost and say, who's really had heavy freight and where are these coming from? If we did this with the normal method we have right now, since this information is in two different tables, the customer table and the orders table, we would have to first retrieve all of the customer IDs for orders with freight over 100. But then we wouldn't have what region the customers from because that's in a different table. The orders tables in this case. Then I would have to go and retrieve the customer information to get the regions from that separate table and then I would need to combine that so now I can select the IDs for the ones that had freight over 100. Clearly this is quite a few steps and we always want to limit these and make things as simple as possible in SQL. In this example again you can see we pulled our customer IDs WHERE Freight > 100. To do another query, to get my CompanyName from my Customers region And then now, combine the list of those customer IDs into my other query which had my customer information. This is also taxing right now because I just had a few customer IDs. So I could just say where a customer ID is this. Because I've already prequalified this as being over a hundred in freight. If that was a large list, this would be very taxing, when either trying to find a way to manually copy and paste in, or also computationally for it go through each one and match it. So to avoid this missing SQL, we write what are called subqueries. We can take the three queries we had to write to do the job. And instead combine all three queries into one larger query in order to make things more efficient. What I usually do when writing a subquery is start with the innermost query. How we're using this query is to filter for customers, that we're getting back. My filtering criteria in this example is to find records where the freight is over 100. I'm first going to take that first query I wrote, which is to select the customer IDs from orders where freight is over 100. And that's going to be my main base. I'm going to filter down to that and then I'm going to go and expand and add to it. This is your innermost query. From there, I'm going to write the query to select my customer id's. My company name, my region, to get my region information. I'm going to get that from inside the customer table. Where now I can connect the subquery and my customer information query together is using the WHERE clause. When I say is where the customer ID is in the SELECT statement, remember we're using this kind as a filtering criteria. It's saying only to return the region CompanyName and CustomerID for those CustomerID's that are in this other SELECT statement. A good way to think about this is that the query statement is always going to go perform the innermost SELECT portion first. It's going to say okay, I've got the customer IDs where the freight's over 100. And then it's going to sit there with them. Then it's going to go and go through to bring in the CustomerID, CompanyName and Region. But it's going to match to the customer IDs up against the list it pulled earlier to see if the customer IDs are actually in there. Then it's only going to return those customer IDs that were actually in that list of the select statement. You can see this helps reduce the number of queries we have to write, and it also helps keep everything concise in one query. Two things to remember about this in the innermost query is it's always what is performed first. If I'm looking at somebody's query and trying to troubleshoot it I always start with the innermost query to see what's going on and then slowly from there build and work backwards. In this example, remember the database is really performing two operations. One, it's getting our customer IDs for the criteria freight over 100. Then it's going and pulling the other customer information and matching it up against the customer IDs we've already pre-selected. Okay, this is a good place to stop for now. You know all of the basics with subqueries. What they are? When they're advantageous? How they help us when combining data from multiple tables and how to write them effectively. Keep all of this in mind because we're going to be using subqueries a whole lot more from here on out.

Coursera 카탈로그 살펴보기

무료로 참여해 맞춤화된 추천, 업데이트 및 제안을 받아보세요.

Coursera는 세계 최고의 교육에 대한 보편적인 접근 기회를 제공하며, 최고의 대학 및 기관들과 제휴하여 온라인 강좌를 제공합니다.

© 2019 Coursera Inc. All rights reserved.

Coursera
소개
리더십
직업
카탈로그
수료증
MasterTrack™ 수료증
학위
포 엔터프라이즈 (For Enterprise)
정부용
캠퍼스용

커뮤니티
Learners
Partners
Developers
Beta Testers
Translators

연결
블로그
Facebook
Linkedin
Twitter
Instagram
YouTube
기술 블로그

기타
조건
개인정보 보호
도움말
접근성
보도 자료
문의하기
디렉토리
계열사