1
00:00:00,440 --> 00:00:05,080
We've just learnt some great tools that
make querying with SQL so powerful.

2
00:00:05,080 --> 00:00:09,770
In most environments subqueries
probably aren't used every day.

3
00:00:09,770 --> 00:00:13,225
But when you're faced with the task
of putting together a tricky dataset,

4
00:00:13,225 --> 00:00:16,250
subqueries come in very handy.

5
00:00:16,250 --> 00:00:18,520
Let's recap what we've learnt.

6
00:00:18,520 --> 00:00:22,640
There are a couple of different reasons
why you might need to use a subquery.

7
00:00:22,640 --> 00:00:25,670
You can use a subquery when
you don't necessarily know

8
00:00:25,670 --> 00:00:28,130
all the values you want to
include in a WHERE clause.

9
00:00:29,200 --> 00:00:32,260
Or when you need to create
a temporary data view, that

10
00:00:32,260 --> 00:00:36,140
you can join to other data
elsewhere in your database.

11
00:00:36,140 --> 00:00:40,340
When using a subquery as part
of an in statement you can put

12
00:00:40,340 --> 00:00:44,990
any valid statement in parenthesis and
the query puzzle will limit the results of

13
00:00:44,990 --> 00:00:49,100
the outer query to the values that
are returned by the subquery.

14
00:00:49,100 --> 00:00:52,670
The key to this approach is that
the subquery can only return

15
00:00:52,670 --> 00:00:55,350
one column via the SELECT clause.

16
00:00:55,350 --> 00:00:58,600
The number of ways that you
construct to derive tables is

17
00:00:58,600 --> 00:01:00,770
only limited by your imagination.

18
00:01:00,770 --> 00:01:05,550
They sample syntax here only represents
one possible way to use a derived

19
00:01:05,550 --> 00:01:10,200
table but the things that apply every
time you create a template table include,

20
00:01:10,200 --> 00:01:14,030
you have to use parentheses
just like any other subquery.

21
00:01:14,030 --> 00:01:18,776
You need to alias the derived table so
that the outer query can reference it.

22
00:01:18,776 --> 00:01:23,390
The subquery is part of
the FROM of the outer query and

23
00:01:23,390 --> 00:01:26,890
you can join to it just
like any other table.

24
00:01:26,890 --> 00:01:29,680
It's time for another practice session.

25
00:01:29,680 --> 00:01:33,410
Open up the accompanying
SQL Playground with this video. We're

26
00:01:33,410 --> 00:01:36,930
still using the library database
with two locations in the North and

27
00:01:36,930 --> 00:01:40,000
South. In the coding
areas in the top left.

28
00:01:40,000 --> 00:01:43,620
I have given you a number of reports
that I'd like you to generate.

29
00:01:43,620 --> 00:01:46,420
Try using subqueries where you can.

30
00:01:46,420 --> 00:01:50,520
This concludes our course on
Querying Relational Databases.

31
00:01:50,520 --> 00:01:53,190
You have been presented
with a lot of information.

32
00:01:53,190 --> 00:01:56,520
Make sure you practice your new
skills in the SQL Playground and

33
00:01:56,520 --> 00:02:01,540
feel free to keep coming back to brush
up on your skills and solidify them.

34
00:02:01,540 --> 00:02:04,600
Once you're done practicing,
it's time for your final assessment.

35
00:02:04,600 --> 00:02:06,090
Good luck, and I'll see you next time.