1
00:00:00,000 --> 00:00:04,980
Hello, and welcome to retrieving data with SELECT statements string patterns.

2
00:00:04,980 --> 00:00:07,080
In this video, we will learn about

3
00:00:07,080 --> 00:00:11,105
some advanced techniques in retrieving data from a relational database table.

4
00:00:11,105 --> 00:00:12,730
At the end of this lesson,

5
00:00:12,730 --> 00:00:18,040
you will be able to describe how to simplify a SELECT statement by using string patterns,

6
00:00:18,040 --> 00:00:20,570
ranges or sets of values.

7
00:00:20,570 --> 00:00:25,630
The main purpose of a database management system is not just to store the data,

8
00:00:25,630 --> 00:00:28,425
but also facilitate retrieval of the data.

9
00:00:28,425 --> 00:00:30,065
In its simplest form,

10
00:00:30,065 --> 00:00:34,260
a SELECT statement is select star from table name.

11
00:00:34,260 --> 00:00:38,525
Based on a simplified library database model and the table Book,

12
00:00:38,525 --> 00:00:42,470
SELECT star from Book gives a result set of four rows.

13
00:00:42,470 --> 00:00:45,330
All the data rows for all columns in the table Book are

14
00:00:45,330 --> 00:00:49,565
displayed or you can retrieve a subset of columns for example,

15
00:00:49,565 --> 00:00:54,965
just two columns from the table book such as Book_ID and Title.

16
00:00:54,965 --> 00:00:58,970
Or you can restrict the result set by using the WHERE clause.

17
00:00:58,970 --> 00:01:04,585
For example, you can select the title of the book whose Book_ID is B1.

18
00:01:04,585 --> 00:01:09,595
But what if we don't know exactly what value to specify in the WHERE clause?

19
00:01:09,595 --> 00:01:12,735
The WHERE clause always requires a predicate,

20
00:01:12,735 --> 00:01:16,990
which is a condition that evaluates to true, false or unknown.

21
00:01:16,990 --> 00:01:21,040
But what if we don't know exactly what value the predicate is?

22
00:01:21,040 --> 00:01:24,790
For example, what if we can't remember the name of the author,

23
00:01:24,790 --> 00:01:28,045
but we remember that their first name starts with R?

24
00:01:28,045 --> 00:01:29,830
In a relational database,

25
00:01:29,830 --> 00:01:33,880
we can use string patterns to search data rows that match this condition.

26
00:01:33,880 --> 00:01:37,455
Let's look at some examples of using string patterns.

27
00:01:37,455 --> 00:01:39,660
If we can't remember the name of the author,

28
00:01:39,660 --> 00:01:42,200
but we remember that their name starts with R,

29
00:01:42,200 --> 00:01:45,040
we use the WHERE clause with the like predicate.

30
00:01:45,040 --> 00:01:49,975
The like predicate is used in a WHERE clause to search for a pattern in a column.

31
00:01:49,975 --> 00:01:53,460
The percent sign is used to define missing letters.

32
00:01:53,460 --> 00:01:56,285
The percent sign can be placed before the pattern,

33
00:01:56,285 --> 00:02:00,400
after the pattern, or both before and after the pattern.

34
00:02:00,400 --> 00:02:03,910
In this example, we use the percent sign after the pattern,

35
00:02:03,910 --> 00:02:08,640
which is the letter R. The percent sign is called a wildcard character.

36
00:02:08,640 --> 00:02:12,695
A wildcard character is used to substitute other characters.

37
00:02:12,695 --> 00:02:15,590
So, if we can't remember the name of the author,

38
00:02:15,590 --> 00:02:19,050
but we can remember that their first name starts with the letter R,

39
00:02:19,050 --> 00:02:22,370
we add the like predicate to the WHERE clause.

40
00:02:22,370 --> 00:02:25,725
For example, select first name from author,

41
00:02:25,725 --> 00:02:28,585
where firstname like 'R%'.

42
00:02:28,585 --> 00:02:31,910
This will return all rows in the author table

43
00:02:31,910 --> 00:02:36,995
whose author's first name starts with the letter R. And here is the result set.

44
00:02:36,995 --> 00:02:40,710
Two rows a return for authors Raul and Rav.

45
00:02:40,710 --> 00:02:45,855
What if we wanted to retrieve the list of books whose number of pages is more than 290,

46
00:02:45,855 --> 00:02:47,490
but less than 300.

47
00:02:47,490 --> 00:02:50,040
We could write the SELECT statement like this,

48
00:02:50,040 --> 00:02:52,490
specifying the WHERE clause as,

49
00:02:52,490 --> 00:02:56,175
where pages is greater than or equal to 290,

50
00:02:56,175 --> 00:02:59,460
and pages is less than or equal to 300.

51
00:02:59,460 --> 00:03:01,570
But in a relational database,

52
00:03:01,570 --> 00:03:04,745
we can use a range of numbers to specify the same condition.

53
00:03:04,745 --> 00:03:08,635
Instead of using the comparison operators greater than or equal to,

54
00:03:08,635 --> 00:03:11,735
we use the comparison operator 'between and.'

55
00:03:11,735 --> 00:03:14,335
'Between and' compares two values.

56
00:03:14,335 --> 00:03:16,935
The values in the range are inclusive.

57
00:03:16,935 --> 00:03:20,655
In this case,we rewrite the query to specify the WHERE clause

58
00:03:20,655 --> 00:03:24,780
as where pages between 290 and 300.

59
00:03:24,780 --> 00:03:26,550
The result set is the same,

60
00:03:26,550 --> 00:03:29,775
but the SELECT statement is easier and quicker to write.

61
00:03:29,775 --> 00:03:33,855
In some cases, there are data values that cannot be grouped under ranges.

62
00:03:33,855 --> 00:03:38,375
For example, if we want to know which countries the authors are from.

63
00:03:38,375 --> 00:03:42,095
If we wanted to retrieve authors from Australia or Brazil,

64
00:03:42,095 --> 00:03:43,950
we could write the SELECT statement with

65
00:03:43,950 --> 00:03:46,865
the WHERE clause repeating the two country values.

66
00:03:46,865 --> 00:03:52,170
However, what if we want to retrieve authors from Canada, India, and China?

67
00:03:52,170 --> 00:03:54,570
The WHERE clause would become very long

68
00:03:54,570 --> 00:03:57,445
repeatedly listing the required country conditions.

69
00:03:57,445 --> 00:04:00,275
Instead, we can use the IN operator.

70
00:04:00,275 --> 00:04:05,090
The IN operator allows us to specify a set of values in a WHERE clause.

71
00:04:05,090 --> 00:04:08,880
This operator takes a list of expressions to compare against.

72
00:04:08,880 --> 00:04:12,190
In this case the countries Australia or Brazil.

73
00:04:12,190 --> 00:04:16,425
Now you can describe how to simplify a SELECT statement by using string patterns,

74
00:04:16,425 --> 00:04:19,260
ranges, or sets of values.

75
00:04:19,260 --> 00:04:22,400
Thanks for watching this video.

76
00:04:22,400 --> 00:04:27,000
(Music)