Subquery Best Practices and Considerations

Loading...

Do curso por University of California, Davis

SQL for Data Science

898 classificações

University of California, Davis

SQL for Data Science

898 classificações

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!

Na lição

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

Conheça os instrutores

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

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.

Explore nosso catálogo

Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.

O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.

© 2018 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play