1
00:00:00,150 --> 00:00:00,810
All righty.

2
00:00:00,810 --> 00:00:01,720
Okey dokey.

3
00:00:01,740 --> 00:00:04,710
Let's try writing some queries using our three tables.

4
00:00:04,950 --> 00:00:07,560
So in this video, we're going to start simple.

5
00:00:07,590 --> 00:00:11,670
I would like for us to write a query that results in this data.

6
00:00:11,700 --> 00:00:17,760
Now, all of these that you see, all these screenshots only show a truncated version of the results.

7
00:00:17,760 --> 00:00:24,660
I limited this, I think to 15 or so because if we had all of the results, it would just be the world's

8
00:00:24,660 --> 00:00:25,410
tiniest text.

9
00:00:25,410 --> 00:00:26,430
You wouldn't be able to see it.

10
00:00:26,430 --> 00:00:27,540
So this is a subset.

11
00:00:27,540 --> 00:00:33,000
I don't want you to limit it necessarily, but what I do want is to recreate the same columns, the

12
00:00:33,000 --> 00:00:36,990
same relationship and information, but you don't need to limit it to 15 rows.

13
00:00:37,170 --> 00:00:39,930
Okay, So on the left hand side, we have title.

14
00:00:39,990 --> 00:00:42,960
On the right hand side, we have a rating.

15
00:00:43,110 --> 00:00:46,560
So this is every review left for every title.

16
00:00:46,830 --> 00:00:48,720
What is the title and what is the rating?

17
00:00:48,720 --> 00:00:52,620
We don't have anything about who left it, what is their name, anything like that.

18
00:00:52,620 --> 00:00:55,440
It's just title and rating side by side.

19
00:00:55,440 --> 00:00:59,250
So somebody gave Archer an eight and then someone else did a 7.5.

20
00:00:59,250 --> 00:01:02,640
Someone else had an 8.5, seven seven and an eight nine.

21
00:01:03,900 --> 00:01:05,610
So how would we go about doing this?

22
00:01:05,610 --> 00:01:09,960
We know that we need to do a join because we don't have these two pieces of information in the same

23
00:01:09,960 --> 00:01:10,530
table.

24
00:01:10,530 --> 00:01:13,860
So we have to join them and we'll start by just getting title.

25
00:01:14,370 --> 00:01:15,450
We'll keep it simple.

26
00:01:15,900 --> 00:01:20,070
We could do something like select title from series.

27
00:01:20,070 --> 00:01:23,490
We know that we want that and that will just give us the titles.

28
00:01:23,490 --> 00:01:24,360
Simple enough.

29
00:01:24,450 --> 00:01:28,020
Okay, the next thing we need is rating.

30
00:01:28,020 --> 00:01:29,970
But that's coming from a different table.

31
00:01:30,000 --> 00:01:37,950
Of course, that is going to be coming from select rating from and then reviews.

32
00:01:38,640 --> 00:01:40,230
There's all of the ratings.

33
00:01:40,990 --> 00:01:48,160
But what we want to do is take let's just select staff from reviews, everything from reviews, and

34
00:01:48,160 --> 00:01:56,800
we want to use the rating and match that row up to a series where the series ID matches the actual ID

35
00:01:56,800 --> 00:01:57,920
from the series table.

36
00:01:57,940 --> 00:02:03,070
In other words, take this 8.0 and we have a series idea of one.

37
00:02:03,070 --> 00:02:08,229
We want to put that alongside the series that actually has the idea of one, and that is going to require

38
00:02:08,259 --> 00:02:08,979
a join.

39
00:02:08,979 --> 00:02:11,080
And we don't need to worry about a left or right join.

40
00:02:11,080 --> 00:02:12,430
We want the overlap.

41
00:02:12,520 --> 00:02:20,200
So it's as simple as select title from series and then join reviews on where we're doing that.

42
00:02:20,200 --> 00:02:30,190
We're doing it where the Let's do series ID is the same as the reviews dot series ID.

43
00:02:31,100 --> 00:02:31,410
Okay.

44
00:02:31,460 --> 00:02:36,980
And instead of just selecting title, let's just select everything so you see what we're working with.

45
00:02:38,270 --> 00:02:40,490
Looks good to every review that's been left.

46
00:02:40,490 --> 00:02:47,770
We now have the rating the series ID in this case, one matches along with the series that has that

47
00:02:47,780 --> 00:02:54,080
ID and we get the title and the release year in the genre, but all we want is title and rating.

48
00:02:54,080 --> 00:02:57,800
So let's slim that down just like that.

49
00:02:58,040 --> 00:02:59,270
Try running this.

50
00:03:00,110 --> 00:03:00,950
And there we are.

51
00:03:00,950 --> 00:03:05,570
We see every rating that's been made for every show that we have.

52
00:03:05,570 --> 00:03:09,170
So every title alongside every rating, we're not doing an average.

53
00:03:09,170 --> 00:03:12,740
We're not doing a some we're not involving users or reviewers.

54
00:03:12,740 --> 00:03:16,880
It's just the score with the title and that's it.

55
00:03:17,000 --> 00:03:19,520
That is a solution to this first challenge.

56
00:03:20,030 --> 00:03:21,860
You could have written in or join.

57
00:03:22,160 --> 00:03:27,470
You could have done a different order, started with reviews and then joined on series.

58
00:03:27,470 --> 00:03:32,900
But at the end of the day, we want series and reviews joined where the series ID is the same as the

59
00:03:32,900 --> 00:03:34,940
reviews series ID.