1
00:00:01,030 --> 00:00:03,540
- [Instructor] Now, you can
customize your query results

2
00:00:03,540 --> 00:00:07,710
by looking for records or rows in tables

3
00:00:07,710 --> 00:00:12,470
that meet certain search
criteria or selection criteria.

4
00:00:12,470 --> 00:00:17,070
So here, we have an example
with a multi-line Python string

5
00:00:17,070 --> 00:00:19,310
representing a SQL query.

6
00:00:19,310 --> 00:00:22,140
The SQL query is going
to select the title,

7
00:00:22,140 --> 00:00:26,310
edition and copyright columns
from our titles table,

8
00:00:26,310 --> 00:00:30,650
and we're only going to
select them for the rows

9
00:00:30,650 --> 00:00:35,650
that match copyright
greater than the value 2016.

10
00:00:35,870 --> 00:00:40,870
So we want basically any
books published 2017 or later.

11
00:00:41,790 --> 00:00:44,090
We did this as a triple quoted string

12
00:00:44,090 --> 00:00:45,960
because we're breaking it apart

13
00:00:45,960 --> 00:00:47,870
in multiple lines for readability,

14
00:00:47,870 --> 00:00:50,510
and again we supply the
same connection object

15
00:00:50,510 --> 00:00:52,840
we created a couple of videos ago.

16
00:00:52,840 --> 00:00:55,450
So as you can see here,
what we are getting

17
00:00:55,450 --> 00:00:57,630
as a result are only the books

18
00:00:57,630 --> 00:01:01,523
that have copyright 2017 and higher.

19
00:01:02,830 --> 00:01:07,510
Now, we can also do pattern
matching in our where clause.

20
00:01:07,510 --> 00:01:11,500
So in this next query, we're
going to select the id,

21
00:01:11,500 --> 00:01:14,520
first name and last name
from the authors table

22
00:01:14,520 --> 00:01:17,600
but we only want to select a given row

23
00:01:17,600 --> 00:01:22,600
if the last name looks like
the string that you see here,

24
00:01:23,040 --> 00:01:25,180
which starts with the letter D,

25
00:01:25,180 --> 00:01:28,910
followed by the %*, which means zero

26
00:01:28,910 --> 00:01:30,940
or more additional characters.

27
00:01:30,940 --> 00:01:34,050
So basically, any last name that starts

28
00:01:34,050 --> 00:01:36,570
with the letter D will be a match

29
00:01:36,570 --> 00:01:38,530
and in this case we're going to use

30
00:01:38,530 --> 00:01:41,640
the same connection
object and we chose to use

31
00:01:41,640 --> 00:01:45,420
as our index on the
left side of the display

32
00:01:45,420 --> 00:01:47,170
of the pandas data frame that we're going

33
00:01:47,170 --> 00:01:50,240
to get here the id column once again.

34
00:01:50,240 --> 00:01:51,980
So if I go ahead and execute that

35
00:01:51,980 --> 00:01:54,900
we can see that the ids
are one, two and three

36
00:01:54,900 --> 00:01:58,050
for Paul, Harvey and Abbey Deitel

37
00:01:58,050 --> 00:02:00,380
in this particular database.

38
00:02:00,380 --> 00:02:03,600
Now, that was to match
zero or more characters.

39
00:02:03,600 --> 00:02:08,600
You can match any specific
character using the underscore

40
00:02:08,630 --> 00:02:10,880
in your where clause for pattern matching

41
00:02:10,880 --> 00:02:13,080
with that same like operator.

42
00:02:13,080 --> 00:02:15,410
So here once again, we're going to select

43
00:02:15,410 --> 00:02:19,650
the same id, first and last
columns from the authors table

44
00:02:19,650 --> 00:02:21,670
and this time we only want to select them

45
00:02:21,670 --> 00:02:24,530
if the first name is like something

46
00:02:24,530 --> 00:02:27,290
that starts with any character followed by

47
00:02:27,290 --> 00:02:30,380
the letter B and then any number

48
00:02:30,380 --> 00:02:32,640
of additional characters after that.

49
00:02:32,640 --> 00:02:35,700
So basically, the second letter is B

50
00:02:35,700 --> 00:02:39,660
and everything else can be
pretty much anything you want.

51
00:02:39,660 --> 00:02:41,650
So if I execute this, you can see

52
00:02:41,650 --> 00:02:43,270
that there's only one author

53
00:02:43,270 --> 00:02:47,150
whose first name has the second letter B.

54
00:02:47,150 --> 00:02:49,420
We have one letter before that which is A

55
00:02:49,420 --> 00:02:51,110
that matters the underscore,

56
00:02:51,110 --> 00:02:54,660
and everything else after
that matches the percent,

57
00:02:54,660 --> 00:02:59,000
so it could of just been
Ab or it could be Ab

58
00:02:59,000 --> 00:03:02,153
and several additional
letters in that case.