1
00:00:01,689 --> 00:00:04,099
hello i'm ok taxes 2013 shoot or ooze

2
00:00:04,099 --> 00:00:07,189
this video gonna be talking you through queries

3
00:00:07,190 --> 00:00:10,630
such gonna play along for the link below the video tattooed on a 6-2 website and

4
00:00:10,630 --> 00:00:12,530
download access 2013

5
00:00:12,529 --> 00:00:15,829
TV 06 miss opener but boyer

6
00:00:15,830 --> 00:00:21,198
who Kate know if you are intending on doing the course

7
00:00:21,199 --> 00:00:25,330
I would def ni download this databases this contains

8
00:00:25,329 --> 00:00:29,639
all the tables and data from the database I showed you

9
00:00:29,640 --> 00:00:32,980
in the first video but minus school reforms

10
00:00:32,979 --> 00:00:36,599
reports queries macro so this is the

11
00:00:36,600 --> 00:00:40,329
two bases which we're going to create our database

12
00:00:40,329 --> 00:00:45,179
a right side let's begin okay this lessons on queries

13
00:00:45,179 --> 00:00:48,219
I now what our queries well

14
00:00:48,219 --> 00:00:51,429
we basically use queries to

15
00:00:51,429 --> 00:00:54,878
of data Australia ask questions if the data

16
00:00:54,878 --> 00:00:58,789
I'm in the database and if we take a look

17
00:00:58,789 --> 00:01:03,198
table rooms you can see that

18
00:01:03,198 --> 00:01:06,859
as with most tables wise to vote I was very much

19
00:01:06,859 --> 00:01:10,129
information guess entered has created

20
00:01:10,129 --> 00:01:14,438
which means is it was entered in

21
00:01:14,438 --> 00:01:18,399
gorda and so for example please don't tell me why

22
00:01:18,400 --> 00:01:22,189
the room number 41 was into here

23
00:01:22,188 --> 00:01:25,429
and room number 2004 wasn't

24
00:01:25,430 --> 00:01:30,790
he this record and its pretty normal so what are things we can use queries for

25
00:01:30,790 --> 00:01:33,900
is to ask a question such a show me all day to

26
00:01:33,900 --> 00:01:37,909
in Table rooms sorted by room number so

27
00:01:37,909 --> 00:01:41,299
room number would appear in order a

28
00:01:41,299 --> 00:01:44,930
safe week clothes are said to do that go up to create

29
00:01:44,930 --> 00:01:48,729
and click on query design when you get this

30
00:01:48,728 --> 00:01:52,109
this books he

31
00:01:52,109 --> 00:01:57,509
is where you can choose tables to base your queries awful for you can actually

32
00:01:57,509 --> 00:01:58,399
also

33
00:01:58,399 --> 00:02:01,968
we don't have any based queries of other queries

34
00:02:01,968 --> 00:02:05,849
which is actually quite useful feature we just interested in

35
00:02:05,849 --> 00:02:09,579
single table queries at the moment so interested table room cyclical table

36
00:02:09,580 --> 00:02:10,280
with the click

37
00:02:10,280 --> 00:02:13,729
had I'm and

38
00:02:13,729 --> 00:02:17,780
the information we see in the query is what we've

39
00:02:17,780 --> 00:02:21,430
here so what we do is double click on room I T

40
00:02:21,430 --> 00:02:24,530
so it appears here

41
00:02:24,530 --> 00:02:28,050
telecom room number tie

42
00:02:28,050 --> 00:02:32,239
cost for night notes and active or exceed

43
00:02:32,239 --> 00:02:36,120
at the moment is recreating the table could what we said now is

44
00:02:36,120 --> 00:02:40,879
City show me remind the room number type cost per night notes an active

45
00:02:40,878 --> 00:02:44,018
from Table herbs and if we take run

46
00:02:44,019 --> 00:02:48,610
you get this which is basically the same as the

47
00:02:48,610 --> 00:02:55,610
rooms table no different but what we want is you want to sort by room number

48
00:02:56,729 --> 00:03:00,789
so go back to disarm you

49
00:03:00,789 --> 00:03:05,479
I'm getting from number my sis saw change it to sending

50
00:03:05,479 --> 00:03:11,018
and then click Run now you can see the sexy sorted

51
00:03:11,019 --> 00:03:17,870
the room number by the sending character so he goes to run through to a 5-3 want

52
00:03:17,870 --> 00:03:22,120
for 235 40 and 45 51 55

53
00:03:22,120 --> 00:03:25,780
so that's and it hasn't just saw it obviously room numbers taken the whole

54
00:03:25,780 --> 00:03:27,840
record a move the whole record into the

55
00:03:27,840 --> 00:03:31,598
for free location and everything you can do so

56
00:03:31,598 --> 00:03:36,229
descending which is the same I to

57
00:03:36,229 --> 00:03:40,318
backwards you can also be a bit more sophisticated your queries

58
00:03:40,318 --> 00:03:45,048
for example we've lost foo with information firm the

59
00:03:45,049 --> 00:03:50,219
I'm table rooms sort by room number but what about if we just want to see

60
00:03:50,219 --> 00:03:53,969
the and I the same double beds

61
00:03:53,969 --> 00:03:57,568
we're not interested in singles twins and

62
00:03:57,568 --> 00:04:02,079
Sweex how do we do that but we have to use tackle criteria

63
00:04:02,080 --> 00:04:05,620
so no what we're saying to the databases show me

64
00:04:05,620 --> 00:04:09,069
with information from table room so it by room number

65
00:04:09,068 --> 00:04:12,098
where the type equals

66
00:04:12,098 --> 00:04:15,419
double so we do clicking criteria

67
00:04:15,419 --> 00:04:18,449
and

68
00:04:18,449 --> 00:04:21,579
and variation eat even really use any calls

69
00:04:21,579 --> 00:04:27,478
open/close quotation marks because it was searching on a string

70
00:04:27,478 --> 00:04:31,939
destroyed double in Africa run you get

71
00:04:31,939 --> 00:04:35,040
sort by room number where type is double

72
00:04:35,040 --> 00:04:38,530
and you can change that to single

73
00:04:38,529 --> 00:04:45,529
and you can change it

74
00:04:45,569 --> 00:04:52,569
tweet

75
00:04:52,930 --> 00:04:56,319
Emperor exit to protect design view

76
00:04:56,319 --> 00:05:00,579
know what a pal

77
00:05:00,579 --> 00:05:04,159
if we want to ask a financial question were interested

78
00:05:04,160 --> 00:05:07,689
in every room the

79
00:05:07,689 --> 00:05:11,629
has a cost per night is greater the 230-pound

80
00:05:11,629 --> 00:05:15,579
tonight can we do to easy we have to use

81
00:05:15,579 --> 00:05:20,129
am bassoon criteria for us to use a symbol too little

82
00:05:20,129 --> 00:05:24,029
her which points to the right icon say don't know the exact name for their

83
00:05:24,029 --> 00:05:27,679
I'm so if you do night please put in the comments section video

84
00:05:27,680 --> 00:05:31,329
you should be rewarded if gold and silver just not mine

85
00:05:31,329 --> 00:05:35,019
so point where to the right which means greater than

86
00:05:35,019 --> 00:05:39,250
I'm and it up in 139 press run

87
00:05:39,250 --> 00:05:42,439
we're now telling this

88
00:05:42,439 --> 00:05:47,598
Creek show me who love the rooms or so it by room number

89
00:05:47,598 --> 00:05:52,709
where the cost per night is greater than 130 so you think about the way you

90
00:05:52,709 --> 00:05:54,538
actually phrase these questions

91
00:05:54,538 --> 00:05:59,279
an English and he say then that can help you to think about this kinda queries

92
00:05:59,279 --> 00:05:59,908
you create

93
00:05:59,908 --> 00:06:03,180
and you can

94
00:06:03,180 --> 00:06:07,319
now if you want to say okay 131 great 1930 by one

95
00:06:07,319 --> 00:06:12,329
less than and less than or equal to 150

96
00:06:12,329 --> 00:06:16,779
how we do that well he do

97
00:06:16,779 --> 00:06:21,750
politic indian-american builder I don't need to do this part is going to Iraq so

98
00:06:21,750 --> 00:06:22,949
we say we want less

99
00:06:22,949 --> 00:06:27,189
the greater than 130 and less than

100
00:06:27,189 --> 00:06:31,139
which is there are reporting to the left and equal to

101
00:06:31,139 --> 00:06:34,960
150 okay

102
00:06:34,959 --> 00:06:38,870
purse from now we have

103
00:06:38,870 --> 00:06:42,090
rooms sort by remember

104
00:06:42,089 --> 00:06:46,750
read a cost is Grayson and in 30 or less than or equal to

105
00:06:46,750 --> 00:06:51,699
150 K yes mother where you can use criteria

106
00:06:51,699 --> 00:06:55,329
shift kinda get used to the symbols the

107
00:06:55,329 --> 00:06:58,519
area to the right means greater than ours it left me less than

108
00:06:58,519 --> 00:07:02,629
and the crew guess what that means i dont okay

109
00:07:02,629 --> 00:07:06,098
so what about remember what about if we wanted to

110
00:07:06,098 --> 00:07:09,379
I'm see everything

111
00:07:09,379 --> 00:07:12,728
in or on the

112
00:07:12,728 --> 00:07:15,949
third floor well this is a hotel

113
00:07:15,949 --> 00:07:19,468
database I'm telling you right now everything on the third floor

114
00:07:19,468 --> 00:07:23,389
is that which is

115
00:07:23,389 --> 00:07:27,579
who love the room numbers that begin whiffed on three

116
00:07:27,579 --> 00:07:32,028
you know before we do anything this get a table rooms

117
00:07:32,028 --> 00:07:35,538
for to find a rich icon right there good as I'm few

118
00:07:35,538 --> 00:07:39,718
covering them with short text okay

119
00:07:39,718 --> 00:07:44,478
so reason I've done this is to find out what they taught real number is a short

120
00:07:44,478 --> 00:07:46,348
text so we're gonna have to do

121
00:07:46,348 --> 00:07:49,728
is what we're gonna send to the OSC database now is

122
00:07:49,728 --> 00:07:53,658
bit more complicated were gonna say please show me every

123
00:07:53,658 --> 00:07:56,788
recalled from Table rooms a.m.

124
00:07:56,788 --> 00:08:00,490
so driver number where the first

125
00:08:00,490 --> 00:08:03,879
character covering number the

126
00:08:03,879 --> 00:08:08,990
these three and we do that we're in order to do that we have to use

127
00:08:08,990 --> 00:08:13,689
the like operator so much looking criteria here for room number

128
00:08:13,689 --> 00:08:16,689
to build a and the like operator

129
00:08:16,689 --> 00:08:20,278
is literally yet show me rooms like this

130
00:08:20,278 --> 00:08:25,459
so for in like then what we do to

131
00:08:25,459 --> 00:08:28,908
double quotation mark and

132
00:08:28,908 --> 00:08:32,759
three and then Asterix

133
00:08:32,759 --> 00:08:35,948
kanin double quotation mark again

134
00:08:35,948 --> 00:08:39,198
going to show you this explain

135
00:08:39,198 --> 00:08:42,948
what this means press run and you get everything

136
00:08:42,948 --> 00:08:46,359
in 43 which is essentially room numbers that begin with

137
00:08:46,360 --> 00:08:51,310
number three so or did I just do K

138
00:08:51,309 --> 00:08:55,419
wife said is I want everything

139
00:08:55,419 --> 00:08:58,549
from room number I

140
00:08:58,549 --> 00:09:02,028
like cannon of pips 3 fantastic to keep

141
00:09:02,028 --> 00:09:06,370
actual thing here is assessed eric's s3x means I want everything for room number

142
00:09:06,370 --> 00:09:07,250
like

143
00:09:07,250 --> 00:09:10,809
where where the room number begins with

144
00:09:10,809 --> 00:09:15,349
the character three and as any amount of characters after it

145
00:09:15,350 --> 00:09:18,620
and that's what the s3x making just like a wild card character

146
00:09:18,620 --> 00:09:22,250
mean any amount of characters for move to

147
00:09:22,250 --> 00:09:25,429
what this actually says show me

148
00:09:25,429 --> 00:09:29,438
I'm who rooms a.m.

149
00:09:29,438 --> 00:09:32,958
firm table rooms where the room number is

150
00:09:32,958 --> 00:09:38,219
equal to 3 which we don't have

151
00:09:38,220 --> 00:09:41,319
I'm now all the real numbers

152
00:09:41,318 --> 00:09:47,250
are three digits long so yes tricks is not the only character can use here

153
00:09:47,250 --> 00:09:52,458
I can also use question mark and your question mark does the same thing is

154
00:09:52,458 --> 00:09:57,438
is well call it character be only represents one wild card character

155
00:09:57,438 --> 00:10:03,509
so 3 character character which should also produce the same results

156
00:10:03,509 --> 00:10:07,500
301 302 303 but if you not sure mean if

157
00:10:07,500 --> 00:10:13,120
for example I'm this moment such a case we might have firm numbers with like 30

158
00:10:13,120 --> 00:10:15,870
33 03 04 for whatever

159
00:10:15,870 --> 00:10:19,370
if they're different link the characters you definitely need to use P

160
00:10:19,370 --> 00:10:25,209
Esther so you know

161
00:10:25,208 --> 00:10:28,849
so we can do

162
00:10:28,850 --> 00:10:32,110
we get that result okay so that's like

163
00:10:32,110 --> 00:10:35,120
I'm isola save this query

164
00:10:35,120 --> 00:10:39,129
what do I in a minute I'll save in a minute discover that and one last thing

165
00:10:39,129 --> 00:10:40,889
on a show you

166
00:10:40,889 --> 00:10:46,409
if you look over active see the only forms are active instead of twin

167
00:10:46,409 --> 00:10:50,159
if you want to search true or false

168
00:10:50,159 --> 00:10:54,039
am says active just type in true

169
00:10:54,039 --> 00:10:57,169
press Return an irony

170
00:10:57,169 --> 00:11:00,438
give us apartments that are active and true

171
00:11:00,438 --> 00:11:03,879
okay so let's go back to the grind your that my main

172
00:11:03,879 --> 00:11:07,169
thank for this query was actually sort sending

173
00:11:07,169 --> 00:11:10,818
I'm which have done and save it

174
00:11:10,818 --> 00:11:14,519
directorship saying cool it Creary

175
00:11:14,519 --> 00:11:17,938
a rooms

176
00:11:17,938 --> 00:11:22,649
sorted a a I'm

177
00:11:22,649 --> 00:11:26,578
PSC name inquiries is just a nightmare

178
00:11:26,578 --> 00:11:30,159
telling you right now there's no other found any really good

179
00:11:30,159 --> 00:11:33,578
solid naming convention and

180
00:11:33,578 --> 00:11:38,088
inevitably you name query them because if a couple careers like you realize AXI

181
00:11:38,089 --> 00:11:41,060
the name you gave the first query doesn't is a really consistent with the

182
00:11:41,059 --> 00:11:42,708
other ones he change it

183
00:11:42,708 --> 00:11:45,409
when you make a few more queries and realize actually should change it back

184
00:11:45,409 --> 00:11:46,688
to the original

185
00:11:46,688 --> 00:11:51,750
I did so wherever very discreet every room sort ascending

186
00:11:51,750 --> 00:11:55,429
be a mere eight ish week rewritten so it by room number sending but then you end

187
00:11:55,429 --> 00:11:56,219
up with

188
00:11:56,220 --> 00:12:00,199
stupidly long name so yeah one or the other really any

189
00:12:00,198 --> 00:12:04,769
thats queries a single tail queries and index we're gonna be looking at most two

190
00:12:04,769 --> 00:12:06,838
total queries: thanks watching to see them