馃攳
Learn Basic SQL in 15 Minutes | Business Intelligence For Beginners | SQL Tutorial For Beginners - YouTube
Channel: LearnBI.online (formerly Vitamin BI)
[0]
in this video you're going to learn the
[2]
basics of sql querying in just
[4]
15 minutes and by the time we're
[6]
finished you'll be wondering what all
[8]
the fuss was about
[9]
are you ready then let's get started
[19]
hello and welcome to vitamin bi bringing
[21]
you business intelligence for beginners
[24]
and beyond on this channel i help you do
[27]
more with data
[28]
so if you're new here consider
[30]
subscribing
[31]
right so as i said in my intro i'm going
[34]
to teach you the basics of
[35]
sql querying the essentials that you'll
[38]
need to know if you're considering
[40]
becoming a business intelligence analyst
[43]
obviously we can only go so far in 15
[45]
minutes
[46]
but i promise you that by the time we're
[48]
finished you'll be able to understand
[50]
quite a lot of what you might be called
[52]
upon to do
[54]
we'll be focusing specifically on
[56]
querying data
[57]
not creating or deleting databases
[60]
tables or data
[61]
just selecting data from tables that we
[64]
want to return
[65]
for our specific needs before we get
[68]
started let me give you a little
[70]
context you might be wondering why you
[73]
need to learn sql with bi tools becoming
[75]
more and more sophisticated
[77]
can't they write the sql queries for me
[80]
the answer to that
[81]
is yes and no yes when it comes to
[85]
requesting data for different charts
[87]
graphs and tables
[88]
that you might want to present in
[90]
dashboards in these cases the bi tool
[93]
will have some kind of graphical
[95]
interface
[95]
to be able to drag and drop fields to
[98]
return data
[99]
but no in that in a lot of cases you'll
[102]
need to write sql queries to
[104]
pre-aggregate filter and select only the
[107]
data necessary
[108]
for the project that you're working on
[110]
these are often called
[112]
views and you create views mainly
[114]
because it's more efficient than
[116]
connecting to
[117]
all data when you only need some of it
[121]
so you could have a table that contains
[122]
millions of rows of data
[124]
but by creating a view of it your bi
[127]
tool will only need to work with a
[129]
few thousand making analysis and
[132]
presenting results
[133]
much faster so you see knowing how to
[136]
query with sql is a useful skill to have
[140]
let's jump onto my computer and get
[142]
started so today we're working with a
[144]
sql lite database that i found
[146]
online containing data compiled from the
[149]
fifa video game by ea
[151]
sports so soccer stats i'll leave a link
[155]
to the database
[156]
in the description if you want to play
[157]
around with it too
[159]
in terms of the tool i'm using to query
[161]
the database it's called
[163]
navicat it's the premium version that
[165]
allows you to connect to and query
[167]
pretty much
[168]
any relational database but they also
[170]
have a version for specific
[172]
rdbms like mysql sql server etc
[176]
link also in the description here we're
[179]
in the navicat
[180]
interface on the left we can see that
[182]
i'm connected to the
[184]
soccerdb connection that i've created
[186]
and
[187]
in that connection is a database called
[189]
main
[190]
and in that database we have various
[193]
tables
[194]
country league match player etc
[197]
we're going to be working mainly with
[199]
the player table
[201]
if i double click it we'll see the data
[203]
contained within that table
[205]
we've got three different ids player
[208]
name
[209]
birthday height and weight what i want
[212]
to do is write sql queries and ask the
[214]
database to return
[216]
specific data from this table so i'll
[219]
open
[219]
a new query window now
[222]
when we're asking for data we're using
[225]
what's called a select
[226]
statement so that's what we need to
[228]
write first
[230]
and if we want to return the whole table
[232]
in the result we do so with a star
[236]
then we specify the table that we want
[238]
to select
[239]
everything from in this case the table
[242]
called
[242]
player select star from player
[246]
then we run the query and the database
[249]
returns
[250]
all data from that table you may notice
[253]
that i've written everything in
[254]
lowercase because
[255]
the sequel isn't case sensitive however
[259]
i have the option to what's called
[261]
beautify the sequel
[262]
and when i do you'll notice that select
[265]
and
[266]
from have been capitalized although it's
[268]
not obligatory this
[270]
does actually make your queries easier
[272]
to read
[273]
it's not so important with such a small
[275]
query like this one
[276]
but when you've got one with tens if not
[279]
hundreds of lines of code
[281]
it really does make life easier so we've
[284]
selected
[285]
all fields or columns and all rows from
[288]
the table
[289]
but what if we only want to select
[291]
specific fields
[293]
simple instead of the star we just write
[296]
out the field names we want separated by
[298]
commas
[299]
like this player underscore name
[302]
comma birthday i run this and those are
[306]
the fields returned
[307]
when we select specific fields we can
[309]
also rename them by creating aliases
[312]
to do this we use the as function
[315]
so player name as name
[319]
and we can see that update we could
[322]
specify an alias containing a
[324]
space but to do this we would need to
[326]
put the name in quotes
[328]
like this full name
[332]
let's go back to all data and look now
[335]
at how we can ask the database to
[337]
only return specific rows in the result
[340]
to do this we use a where clause with
[343]
different
[344]
operators for example we could select
[347]
only players
[348]
with a weight of 190 pounds
[351]
so we use the equals operator
[354]
we could also select any player with a
[356]
weight greater than 190 pounds
[359]
or greater than or equal to 190.
[362]
you see it's not that complicated and
[365]
we've gone from over
[366]
11 000 records to just under a thousand
[369]
being returned
[371]
we can also specify more than one
[374]
condition by using either
[375]
and or or so weight greater than 190
[380]
and height greater than 190.
[383]
so both conditions must be met
[386]
if i change it to or it means that
[389]
either condition must be met
[391]
in order for the row to be returned
[394]
so that's selecting rows based on the
[396]
value in integer fields
[399]
what about when it comes to text values
[402]
well we can also use the equals operator
[404]
to find any row where the text
[406]
matches exactly with what we specify
[409]
player name
[410]
equals in single quotes aaron
[414]
galindo
[417]
but we could also use the like operator
[420]
which achieves the same
[422]
goal but what if we want to select
[425]
just rows from all players called aaron
[428]
so basically the player name field
[430]
starts with aaron
[432]
here we can use a percent character
[435]
after the text to look for
[436]
like so and we get all aarons returned
[441]
if we wanted to find any player name
[443]
that ends with aaron
[445]
we put the percent character before
[448]
nothing that ends with aaron we could
[451]
put a percent before
[453]
and after which would basically mean any
[455]
player name that contains aaron
[458]
and we've got rolando aarons
[461]
finally here's an example which puts the
[464]
percent in the middle of text
[466]
so here we'll return all rows where the
[469]
player name starts with
[471]
a and ends with n
[475]
you can go even further with the like
[477]
operator
[478]
by using underscore to represent single
[481]
characters
[481]
an example would be like t
[485]
underscore m percent so the underscore
[489]
is asking for any rows where the player
[491]
name
[491]
starts with t then any character
[494]
between the t and the m followed by
[497]
anything
[498]
and run we've got tamas
[501]
tamir tim timmy etc
[505]
there are also things called wild cards
[507]
that allow you to go even
[508]
further when specifying the rows you
[511]
want to return
[512]
but we won't go into those for this
[514]
video we will however finish looking at
[517]
the where clause
[518]
by talking about a couple more operators
[520]
that you can use
[522]
for text fields we have in which allows
[525]
us to specify
[526]
multiple or statements more simply
[529]
however it can only be used for exact
[531]
matches
[532]
so we can't use percents or underscores
[534]
with it
[535]
so let's look for ronaldo and messi
[539]
open brackets cristiano ronaldo
[542]
comma lionel messi
[548]
integers we can use the between operator
[550]
so i could say
[551]
where weight between 180 and
[554]
190. finally we have the
[558]
is null and is not null null being empty
[562]
in this table we don't have any null
[564]
values but if i open up the
[566]
match table we can see that there are
[569]
lots in there
[570]
so select star from match
[573]
where home player 1 is null
[584]
or is not null
[591]
there we go let's go back to our
[595]
player table and see how we can sort
[598]
results
[599]
we can do this using the order by clause
[603]
the rows are currently sorted by the id
[605]
field in ascending order
[608]
but if we wanted to sort by weight we
[611]
can say
[612]
order by weight this sorts by weight in
[615]
ascending order
[616]
because that's the default sorting
[618]
method when not
[619]
explicitly specified but we can change
[622]
this to descending order by adding
[625]
d e s c now we can see who the
[629]
heaviest player is when it comes to
[632]
sorting data using order by
[634]
this isn't something that's overly
[636]
important for our use case
[638]
because when we're selecting data to
[641]
create views
[642]
we're usually then going to connect that
[644]
view to a bi tool
[646]
and use that for building individual
[648]
chart queries
[649]
and these tools will let you apply sorts
[652]
to the data
[654]
now we're going to look at how to join
[656]
data from different tables
[659]
to demonstrate this i'm going to use the
[661]
player attributes table
[664]
we can see that in this table we have
[666]
the player id
[667]
but not the player name to be able to
[670]
create a view containing the player name
[672]
plus the overall rating we would need to
[675]
join the data from the
[677]
player attributes and player tables
[680]
i'm going to start by specifying player
[683]
api
[684]
id comma date comma overall rating
[690]
now i want to get the player name from
[692]
the player table
[694]
how do i do that well because we're
[697]
going to need fields from two different
[699]
tables
[699]
we need to specify which field comes
[702]
from which
[702]
table we do this by writing the table
[705]
name
[706]
dot field name so player attributes
[710]
dot player api id
[713]
same for date and overall rating
[717]
now we can specify perhaps after the
[720]
player id
[721]
player dot player name
[724]
if i run this i'll get an error saying
[726]
that there's no such column as
[728]
player dot player name because we're
[730]
saying that we're looking for it
[732]
in the player attributes table this
[735]
is when we need to specify the joining
[737]
of the tables
[738]
using one of the four join types i'm not
[741]
going to go into them in this video but
[743]
i'll probably do a separate video that
[745]
explains them in more detail
[746]
so don't forget to subscribe so in this
[750]
case we'll use an
[751]
inner join and we'll say inner join
[754]
player the player table on
[758]
and now we specify what fields we want
[760]
to use to create the join
[762]
in this case we're going to use the
[764]
player api
[766]
id field which is contained in both
[768]
tables
[769]
and again we have to add the table name
[772]
first
[773]
dot field name equals player
[776]
dot player api id
[779]
and run now we have the player name
[783]
appended to the player attributes table
[785]
data
[786]
pretty clever right but let me show you
[789]
something even more clever
[791]
i showed you earlier how to use the as
[793]
function to create aliases for fields
[796]
well we can actually do this for tables
[799]
as well
[800]
which makes the query much cleaner
[803]
first i'm going to give the player
[804]
attributes table an alias of
[806]
a and the player table an alias of
[810]
b i just need to write these two letters
[813]
after where i've specified
[814]
each table so a after player attributes
[818]
here
[819]
and b after player here
[822]
once i do this i can now replace the
[825]
table name for
[826]
each field like this
[830]
you see much cleaner
[833]
so you may notice that there are
[835]
multiple rows for each player on
[837]
different
[838]
dates so how would we add up all of
[841]
these different
[842]
values for each player well to do this
[845]
we're going to want to aggregate the
[847]
data using the
[848]
sum aggregator i'll add this to the
[851]
overall rating field
[853]
and run the query ah that's
[856]
not what we were expecting but that's
[859]
because we haven't specified in the
[861]
query
[862]
how we want to group the data together
[865]
this is when we need to use the group by
[868]
clause
[869]
when we use the group by clause we need
[871]
to add into it
[872]
all of the fields that we need to group
[875]
so in this case essentially
[877]
all fields apart from the overall rating
[880]
a dot player api id comma
[884]
b dot player name comma a dot
[887]
date and run
[892]
this is correct but not exactly what we
[894]
want
[895]
we want to combine all of the ratings
[897]
for the different dates
[899]
so we actually want to remove the date
[901]
field from the specified fields
[903]
as well as the group by
[908]
and run that's better
[912]
but let's clean it up by aliasing the
[914]
sum
[915]
overall rating as rating
[919]
let's sort this descending order by
[922]
rating descending
[926]
strange that ronaldo and messi aren't in
[928]
there at the top
[930]
let's try and see why this is perhaps
[933]
there are just
[934]
more entries for some players when we
[936]
sum them up
[938]
to find this out we could add a count of
[941]
b dot player name
[946]
and yes in this case how about using an
[949]
average i'll replace sum with average
[952]
a avg and run and there we go
[956]
that makes more sense we're going to go
[960]
one step further and filter this result
[963]
to do that we can use the having clause
[967]
the having clause is only applied to the
[969]
result of the group by function
[971]
so isn't the same as the where keyword
[973]
that's applied before
[975]
and it's only applied to numeric values
[978]
so in this case i'm going to ask for
[980]
only ratings above 85
[983]
i need to write it directly after the
[985]
group by and
[986]
before the order by so having
[990]
rating greater than 85
[994]
and run only 26 records
[998]
and there we have our final query i'll
[1002]
beautify it and this
[1004]
is what it should look like you can see
[1006]
that all of the sql keywords
[1008]
are capitalized and you can see that
[1010]
we've come a
[1011]
fairly long way in a very short space of
[1014]
time
[1015]
we've only covered a very small part of
[1017]
sql as a whole
[1018]
so there's masses more to learn but what
[1021]
i've shown you today is a really good
[1023]
foundation
[1024]
for giving you the confidence to dive
[1026]
deeper
[1027]
did i not say that once we'd finished
[1028]
you'd wonder what all the fuss was about
[1031]
if you got value out of this video
[1033]
please do like
[1035]
share and subscribe for more videos like
[1037]
this one
[1038]
why not start with this playlist here
[1042]
as always thanks for watching i've been
[1044]
adam finer and until the next time
[1046]
stay be i curious
[1059]
you
Most Recent Videos:
You can go back to the homepage right here: Homepage