Explore
For Enterprise
Join for Free
Log In

Explorar
Buscar
For Enterprise
Inicia Sesión
Únete de forma gratuita

Subquery Best Practices and Considerations

Loading...

SQL for Data Science

Universidade da Califórnia, Davis

4.6 (2,572 calificaciones) | 110K estudiantes inscritos

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!

Destrezas que aprenderás

Data Science, Data Analysis, Sqlite, SQL

Revisiones

4.6 (2,572 calificaciones)

5 stars
1,789 ratings
4 stars
583 ratings
3 stars
117 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

De la lección

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

Impartido por:

Sadie St. Lawrence
Data Scientist at VSP Global

Transcripción

Welcome back. So in our last lesson, we started this discussion about subqueries and we're going to continue that discussion here by discussing a few notes on best practices with subqueries. After this particular video you should be able to discuss how to write a subquery within a subquery within a subquery. You get the idea? Discuss some performance limitations with the overuse of subqueries. Explain how to use subqueriess as calculation. Describe some great general best practices with using subqueries. Okay, to begin with one of the things you'll note is that there's no limit to the number of subqueries you can have in a statement. You can have a query inside a query, inside a query, inside a query. And just keep querying until your heart's content. However, you will start to notice that performance in obtaining your results does slow down as you get deeply nested and write long query statements with a bunch of subqueries. One thing to note is that subquery selects can only retrieve a single column. You can't say, hey, go select all these columns from this table and then bring them in my overall query. It's only going to select a single column at a time. Again, this is why they´re used in filtering a lot. As you can see, this is just an example here of subqueries and a subquery. We have three different queries here. This is one is nicely and dented, so it's a little bit easier to read. Which is a good practice, because if everything was just lined up, you could get lost very easily. And it's hard to see where you at and where are things are coming from and what's happening. One other things that's really important when you're working with subqueries, is to make sure you're properly indenting things. This is going to really help make it easy for you and others to read. Here is an example you can see when I removed it from the indention. And it's really hard to see what we're doing here, where things are starting and where things are ending. So if you're writing some queries, make sure that each SELECT statement is indenting and starting on its own. A helpful website for this is PoorSQL. If you're getting something from an application that generates SQL and they're just really difficult to read because they aren't automatically indented, I always just throw it into this website. And it pre-formats it, indenting things properly so I can easily interpret it. It helps makes it a lot easier to read. Then if you're writing them, just be really clear and consistent in your indenting with your queries. This will make it a lot easier for not only you, but for someone else to read. When you or they are troubleshooting a particularly complex query. Okay, just one more example of subqueries. Subqueries can also be used as calculations. In this example, we want to get the company name and the region, and we want a total number of orders for these customers. Before writing the subquery, what we could do is we could count the number of orders for a particular customer ID, but then we still wouldn't have the customer name and region information. Instead of doing something where we're trying to combine these two together, we're just going to write a subquery. And instead of using the subquery with the WHERE clause, this is actually one of the columns we're selecting. As you can see, we're selecting company name, we're selecting the region and then just as if we were going to select a different column name. We have a whole subquery in there. We have SELECT count as orders from orders, and then what we're saying is we want to count these based on the customer IDs. It's aggregating the orders based on the customer IDs. This is seen where the order customer_id = the customers, customer id. This is coming from the orders table and we're matching it up to our where query through the from of the customers table and the customer ID. Then we just close our subquery in brackets and finish our select statement. We're getting this from customers and we just want order by the company name. Now you can see we have our customer's company name, we have the region they're from and then we have the total orders for that. In this example, you can see it just treats it like another additional column. But really helpful when you're wanting to do some calculations and do a calculated fill on another table. Okay, so to sum up, remember some queries are very powerful. But consider their use very carefully, because it may not always be the best thing to use for performance reasons. There's a lot of other ways we can get the result and not have to use subqueries. For example, later in this course we're going to talk about joins. How we use them and what they're for, but it's important to know what subqueries are and how they fit into the grand scheme of things. This is just one of many techniques we can use to start to combine information together from multiple tables. We will continue on with some additional ways to combine data in the next few videos.

Explora nuestro catálogo

Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.

Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.

© 2019 Coursera Inc. Todos los derechos reservados.

Consíguelo en Google Play

Coursera
Acerca de
Liderazgo
Empleo
Catálogo
Certificados
Certificados MasterTrack™
Grados
For Enterprise
For Government
Para el campus

Comunidad
Learners
Partners
Developers
Beta Testers
Translators

Conectar
Blog
Facebook
LinkedIn
Twitter
Instagram
Youtube
Blog de Tecnología

Más
Términos
Privacidad
Ayuda
Accesibilidad
Prensa
Contacto
Directorio
Afiliados