Review and Practice

Name: Review and Practice
Uploaded: 2016-09-12T17:08:30Z
Duration: 126 s
Description: The final video in this stage will recap what we learned about subqueries and then back to the SQL playground for some more practice.

2:06 with Andrew Chalkley and Kevin Nahm

The final video in this stage will recap what we learned about subqueries and then back to the SQL playground for some more practice.

Teacher's Notes
Video Transcript
Downloads

Subqueries are one of the most advanced concepts to learn in SQL query writing. Seeing lots of examples and getting lots of practice will help solidify the concept.

Advanced Subqueries

As you begin using subqueries, be advised that a poorly structured subquery written against a large table or tables can impact overall query performance. Be careful if you are writing subqueries against data sets that consume tables with row counts in the million-plus row range.

Correlated Subqueries

This course does not cover the concept called Correlated Subqueries, which are slightly different in how they tie in with the outer query. Most of the time a regular subquery will do what you need, but correlated subqueries can be handy, too. These are especially heavy weight on the database engine, as they run the subquery many times; once per row in the outer query.

Information on correlated subqueries

Cheat Sheet

See all of the SQL used in Querying Relational Databases in the Querying Relational Databases Cheat Sheet.

We've just learnt some great tools that make querying with SQL so powerful. 0:00

In most environments subqueries probably aren't used every day. 0:05

But when you're faced with the task of putting together a tricky dataset, 0:09

subqueries come in very handy. 0:13

Let's recap what we've learnt. 0:16

There are a couple of different reasons why you might need to use a subquery. 0:18

You can use a subquery when you don't necessarily know 0:22

all the values you want to include in a WHERE clause. 0:25

Or when you need to create a temporary data view, that 0:29

you can join to other data elsewhere in your database. 0:32

When using a subquery as part of an in statement you can put 0:36

any valid statement in parenthesis and the query puzzle will limit the results of 0:40

the outer query to the values that are returned by the subquery. 0:44

The key to this approach is that the subquery can only return 0:49

one column via the SELECT clause. 0:52

The number of ways that you construct to derive tables is 0:55

only limited by your imagination. 0:58

They sample syntax here only represents one possible way to use a derived 1:00

table but the things that apply every time you create a template table include, 1:05

you have to use parentheses just like any other subquery. 1:10

You need to alias the derived table so that the outer query can reference it. 1:14

The subquery is part of the FROM of the outer query and 1:18

you can join to it just like any other table. 1:23

It's time for another practice session. 1:26

Open up the accompanying SQL Playground with this video. We're 1:29

still using the library database with two locations in the North and 1:33

South. In the coding areas in the top left. 1:36

I have given you a number of reports that I'd like you to generate. 1:40

Try using subqueries where you can. 1:43

This concludes our course on Querying Relational Databases. 1:46

You have been presented with a lot of information. 1:50

Make sure you practice your new skills in the SQL Playground and 1:53

feel free to keep coming back to brush up on your skills and solidify them. 1:56

Once you're done practicing, it's time for your final assessment. 2:01

Good luck, and I'll see you next time. 2:04

You need to sign up for Treehouse in order to download course files.