Preview
Video Player
00:00
00:00
Review and Practice
2:02Recap and review the SQL Set Operations we learned.
In this stage, we've learned about the SQL
set operations and their four variants.
0:01
Let's review.
0:06
[SOUND] Each set operation
has the same basic structure.
0:06
The first query, then the set operation,
then the second query.
0:10
You can do anything inside of your
queries that you'd normally do.
0:16
Including, joining multiple
tables together and
0:18
filtering using a WHERE clause.
0:22
You can select any number of columns,
so long as you select the same number of
0:25
columns in both your first and
second query.
0:29
[SOUND] You can also add an ORDER BY
clause, but since you're ordering
0:31
the final result set you only need to
include it once, right at the very end.
0:35
Unions combine all records from both
query result sets into a distinct list.
0:40
If there are any duplicates they
are eliminated from the final result set.
0:46
Like union, union all combines all
records from both query result sets.
0:50
However, duplicate rows are shown.
0:55
Intersect compares the result
sets from two queries and
0:58
returns only the records
that exist in both.
1:01
Except compares the result sets
from two queries and returns just
1:04
the rows from the first query except for
the rows that are also in the second.
1:08
Set operations are structured in a very
similar way and work by combining
1:13
different data sets in various ways, but
they perform very different functions.
1:18
These SQL set operations are very useful
tools to keep handy in your SQL tool belt.
1:24
Once again it's time for you to practice.
1:30
Open up the SQL Playground with this video
and go through the various challenges.
1:33
We're in the library database again but
1:38
now we have two locations,
North and South.
1:40
Each location has books and
loans associated with it.
1:43
For example, books_north has all books
in the library's northern location.
1:47
The loans_south table contains all loan
information for the southern location.
1:53
Good luck and
I'll see you in the next stage.
2:00