1
00:00:00,150 --> 00:00:01,350
Okey dokey.

2
00:00:01,620 --> 00:00:02,820
Artichoke?

3
00:00:03,030 --> 00:00:03,660
Yep.

4
00:00:04,380 --> 00:00:05,910
That's how I'm starting this video off.

5
00:00:06,689 --> 00:00:07,580
It's your turn.

6
00:00:07,590 --> 00:00:10,130
So we've covered four different things here.

7
00:00:10,140 --> 00:00:12,930
Four new tools for this mysterious, magical toolbox.

8
00:00:12,930 --> 00:00:17,010
We've got distinct as well as order by and then limit.

9
00:00:17,010 --> 00:00:22,290
And then finally, like and you're going to put those to use along with some of the stuff you've learned

10
00:00:22,290 --> 00:00:27,510
up until this point with a couple of exercises and there are some clown emojis here to show you how

11
00:00:27,510 --> 00:00:28,710
excited you should be.

12
00:00:28,710 --> 00:00:30,120
And they're not at all creepy.

13
00:00:30,840 --> 00:00:33,600
So here's the first thing I need you to do or I'd like you to do.

14
00:00:33,600 --> 00:00:34,800
Please, please do it.

15
00:00:35,130 --> 00:00:37,380
Select all story collections.

16
00:00:37,470 --> 00:00:43,380
So some of these books have titles that contain the word stories, which tells you that they're a collection

17
00:00:43,380 --> 00:00:44,100
of stories.

18
00:00:44,400 --> 00:00:48,960
What we talk about when we talk about love stories, really good read, by the way, where I'm calling

19
00:00:48,960 --> 00:00:51,660
from selected stories, oblivion stories.

20
00:00:52,140 --> 00:00:54,270
So using what we just saw.

21
00:00:54,270 --> 00:00:59,250
And that's something I just want to point out for all of these exercises, there are ways that you could

22
00:00:59,850 --> 00:01:06,180
directly select things maybe a little bit easier, or you could cheat in a way, but make sure that

23
00:01:06,180 --> 00:01:10,440
you do it the way that I specify, please, using the things you just learned.

24
00:01:10,440 --> 00:01:18,780
So specifically titles that contain the word stories, not exactly stories, but something that contains

25
00:01:18,780 --> 00:01:19,710
stories in it.

26
00:01:19,710 --> 00:01:20,820
So that's the first one.

27
00:01:21,780 --> 00:01:22,170
All right.

28
00:01:22,170 --> 00:01:27,720
And then the next one, find the longest book in the entire database or in the entire books table.

29
00:01:27,720 --> 00:01:29,490
So this is what you should get back.

30
00:01:29,670 --> 00:01:32,130
Print out the title and the pages.

31
00:01:32,730 --> 00:01:36,330
The Amazing Adventures of Cavalier and Clay is 634 pages.

32
00:01:36,330 --> 00:01:42,900
Of course, don't just select based off of where pages is exactly 634 or where the title is The Amazing

33
00:01:42,900 --> 00:01:44,250
Adventures of Cavalier and Clay.

34
00:01:44,460 --> 00:01:51,330
Please do it the right way so that your query would always work, no matter how many books are in the

35
00:01:51,330 --> 00:01:57,360
table and what they're named and how many pages they have, it will always get the longest book.

36
00:01:58,750 --> 00:01:59,350
Okay.

37
00:01:59,380 --> 00:02:00,280
Just one.

38
00:02:00,670 --> 00:02:01,780
Then the next problem.

39
00:02:02,650 --> 00:02:04,180
Print a summary.

40
00:02:04,270 --> 00:02:14,170
In our case, a summary contains the title of the year and print three only three of the most recent

41
00:02:14,170 --> 00:02:14,800
books.

42
00:02:15,070 --> 00:02:18,100
So 2017, 2016 and 2014.

43
00:02:18,250 --> 00:02:22,480
So again, a summary and it says up here it says summary.

44
00:02:22,480 --> 00:02:29,980
So that remember how to do that the title dash the year it was released and then only print out the

45
00:02:29,980 --> 00:02:32,320
three most recently released books.

46
00:02:33,250 --> 00:02:33,700
All right.

47
00:02:33,700 --> 00:02:41,020
Next next up is find all the books whose authors last name contains a space character.

48
00:02:41,320 --> 00:02:43,210
And it's only David Foster Wallace.

49
00:02:43,210 --> 00:02:46,060
In our case, don't cheat and just say we're author.

50
00:02:46,060 --> 00:02:47,860
Our name is exactly foster space.

51
00:02:47,860 --> 00:02:52,840
WALLACE But how can you find any author last name that has a space in it?

52
00:02:53,870 --> 00:03:00,440
And then once you do that, find the three books with the lowest number in stock, the lowest stock

53
00:03:00,440 --> 00:03:01,190
quantity.

54
00:03:01,610 --> 00:03:05,330
So as you can see here, we've got American Gods is 12.

55
00:03:05,330 --> 00:03:07,030
Where I'm calling from has 12.

56
00:03:07,040 --> 00:03:09,530
What we talk about when we talk about love has 23.

57
00:03:10,290 --> 00:03:14,370
And I'd like you to print the title, the released year and the stock quantity.

58
00:03:14,460 --> 00:03:17,910
So just to reiterate, the three lowest stocks.

59
00:03:19,510 --> 00:03:24,310
All right, then we have this one print the title and the author last name.

60
00:03:24,490 --> 00:03:29,040
So you've got title author name sorted by the author's last name.

61
00:03:29,050 --> 00:03:31,030
So this is all of the books, by the way.

62
00:03:31,600 --> 00:03:34,990
So sort by the author last name and then sort by title.

63
00:03:35,770 --> 00:03:41,170
Because some of these authors, for example, Carver or Eggers or Gaiman, have multiple books.

64
00:03:41,170 --> 00:03:46,090
So if we look at Carver, for instance, Carver has what we talk about, blah, blah, blah, where

65
00:03:46,090 --> 00:03:51,160
I'm calling from, what we talk about comes before where I'm calling from alphabetically.

66
00:03:51,970 --> 00:03:52,630
If that makes sense.

67
00:03:52,630 --> 00:03:58,780
Same thing with Eggers a heart breaking, a hologram, the circle so sort first by the last name of

68
00:03:58,780 --> 00:04:01,660
the author, and then on top of that, the title.

69
00:04:03,220 --> 00:04:04,390
Then we've got this one.

70
00:04:04,390 --> 00:04:05,340
Just make this happen.

71
00:04:05,350 --> 00:04:08,470
It's kind of difficult to write out exactly what's happening.

72
00:04:08,980 --> 00:04:13,360
The main idea is that we're going to go through every book.

73
00:04:13,720 --> 00:04:15,780
You're going to print out this string here.

74
00:04:15,790 --> 00:04:19,600
My favorite author is and this is yelling in all caps, I won't wake my neighbors.

75
00:04:19,600 --> 00:04:25,450
It's kind of late, but my favorite author is and then the author's first name capitalized and the author's

76
00:04:25,450 --> 00:04:28,450
last name capitalized and an exclamation point at the end.

77
00:04:28,780 --> 00:04:33,910
And do that for every book sorted alphabetically by the author's last name.

78
00:04:33,910 --> 00:04:39,580
So you can see Carver comes first Eggers, Foster, Wallace, Gammon down to Steinbeck.

79
00:04:39,670 --> 00:04:42,220
And then make sure you call it yell up here.

80
00:04:42,340 --> 00:04:45,460
Yes, it's a bit of a complicated one, but fortunately it's last.

81
00:04:45,910 --> 00:04:47,980
Hopefully you can make this one work.

82
00:04:48,010 --> 00:04:49,120
It's a little bit long.

83
00:04:49,630 --> 00:04:50,320
Good luck.

84
00:04:50,440 --> 00:04:52,060
See you in The Solution video.