Detailed Notes
1. using aliases 2. select from select as source 3. create table from another table
select tutorial_parent.* from tutorial_parent
select tp.t_parent_id as pid,tp.t_one_id as t1id,tp.username as name,tp.email as mail,tp.start_yr as start from tutorial_parent as tp;
select tp.username,tp.email,tp.userLogin,t1.address_name,t1.city, t1.state,t1.zip, t1.type_id from tutorial_parent as tp,tutorial_one t1, lkp_type lt where tp.t_one_id=t1.t_one_id and lt.type_id = t1.type_id;
select * from stats s1 inner join (select * from stats where stats.value2 in (select max(value2) as value2 from stats group by value1) ) s2 on s1.stat_id = s2.stat_id;
select * from stats where stats.value2 in (select max(value2) as value2 from stats group by value1)
Transcript Text
[Music] hello and welcome back we are continuing our season where we're looking at our i guess our series where we're looking at sql particularly mysql and mariadb and how we can improve our sql skills this episode we're going to continue digging into some of the insert type things creation and just sort of managing our tables and such the first thing we want to do is use like an alien the best way to do this i'm going to have to show you is we're going to use an alias and so if you look at some of our prior queries like select star from tutorial parent we get a record set but we can also do let's give it an alias and we can either do it with the space and then the name and then we can do tp.star whoops and we're going to get the same set or we can do as to give ourselves an alias we get the same thing now let's look at if you look at the selection here you'll see that we've got some default header things and we can actually change those around so instead of that we can use select let's do t parent id comma t1 id comma username comma email comma start year from tutorial parent and whoops we're going to keep him as we did use that alias now since we don't have anything we don't have any conflicts here we're okay we don't need to do a tp dot in front of each of those but for clarity that would be something we'd want to do and i'm about to change this over because this is a little slow on the editing so let's play with this in our editor whoops come here and do that okay so now you can see that now well our parent our names are the same but now what i can do is i can say this is gonna be uh i'll do aspid as t1 id as name as mail as start whoops and now we will see that here we just reset our columns now this becomes very important when ever we're doing uh whenever we're dealing with a programming language and pulling stuff out because inevitably we're going to have this is our result set this thing right here and when we pull values out it often is going to be by name by column name so we would want to pull you know mail that column name we'd have to get that value in order to bring it across if it's something that's you know very complicated for example t parent id then there can be a problem with that and that becomes a little bit of an issue even with some complex queries so let's take a look at one of those where we do one of our prior joins and let's dig around a little bit and take i think from let's see if we've got one nope and grab one just to save some time he doesn't have one here we go oh here we go okay and so we ran into this before where we were pulling that we were specifically stating values that we wanted out of each of these now if we have one that is unique let's go back and pull this query real quick so if we look at this query then it comes up it gives us a username email login address name c state zip but now if we have unfortunately uh oh here we go so for example we have this type id exists twice so we could do so if we said i just want the type id then we're going to get whoops oh i did we're going to get an error and it's going to say that column's ambiguous because it says hey that exists in both the lt and the t1 table but now if i come in and i do t1 then i can say that this whoops this is the one i wanted so now it sees it says that type id and it doesn't tell me what the table is note that even though i'm directing it from various tables it's just going to give me it the column name is without the table prefix and technically we could always do this when we do this like select star whoops if we do a select star from tutorial parent we could also do select tutorial parent dot star sometimes that's easier to read oops i should use a semicolon sometimes it's easier to read and sometimes not particularly if you have a long name now i think i'll start the next one jump into is creating a table from a table and in so doing we're going to let's see show that shows show tables so i'm going to go ahead because we've done this for a while i'm going to change these names i want to change the name of tutorial 1 which looks like this and basically what i want to do is say you know what this is going to be an address so i'm going to do create table address as select star from tutorial 1. and when i do so i can do describe address oh let me do show tables real quick first i should probably learn how to type better so i do show tables and i don't have a typo we can see now i have this address now if you look at it let's look at tutorial 1 where i created it and then let's look at address and you can see here that the big difference is we don't have an auto increment we don't have that constraint those don't necessarily come across and here we go same thing with so we have that primary key it's not set and the um the foreign key relationship that's not set so we would have to actually go in and do an alter table address alter column t1 id and i want him to be let me just steal some text from like probably here uh nope uh and let's just do him as like this and i think i can ultra channel one let's try that nope oh i wonder if it needs may need this oh it's not altar cow is it i'm gonna play for a second nope okay so it is a calm all right so let's not mess with that right now simply because uh but it is easier to do uh alter column actually uh oh sorry it should be column that's probably it nope still not it but i do want to use um i'm going to go in and set as i did probably somewhere do i have an example of it i do not oh here we go here we go so i can do a constraint and then he's gonna be this will be uh address pk it's gonna be i'm gonna call it primary key let me go steal that little thing from probably day four well day three oh i just have to do this t1 id see if that'll work yes it will so now if i describe address so now i can see where i have that primary key i could probably go but i think then i can go back and alternate and make alter it make it that but i'm not going to mess with that right now because what i can do whoops what i could do then is i can do tables and so if i do drop table tutorial one oh it's because of the parent so i have to deal with parent first now i'm going to do create a table [Music] call it app user as select star from tutorial parent cronkite tutorial actually oh truncate tutorial one up helps to do that now if i try to drop table tutorial one up can't let's go ahead and empty both of those uh let's see tutorial one it's tutorial parent oh now there we go it takes a few minutes to walk through all of those that's why the constraints can be a bit of a pain but they are also useful to us stop saying shows i keep using s's all over the place and so now we have slightly different looking uh tables and we've sort of cleaned up our names a little bit and we can come back later and add in uh adjust all of our constraints that i will deal with at a later point because right now i want to jump into so now we've got a couple tables we can work from and now i want to do a a way to get a little more complicated in our queries so this one let's start with select star from stats and so whoops so start with something like that but what i can also do is i can interjoin with other tables or with other queries now i can do it with itself so i could do stats i'm going to do s1 enter join stats s2 on oh was that s1 dot stat id equals s2 dot stat id and so if i do this i'm just going to see the table twice or the fields twice so i can see here stat id so this is table one this is table two now this becomes a little more interesting you know this is not terribly interesting but what we can do is we can say instead of stats 2 being that now we can change it let's say we're going to select star from stats and let's do uh let's do this actually we don't need to yeah so let's do this we're going to do select star from stats can i do that no i don't want to do that i want to do this i want to do let's do stat id comma max uh let's pick max that we want to do uh let's see max value one as mx value from stats and then what i want to do is group by value ah let's see oh wait i want to group by value one and let's do max value 2. so let's look at that first so when we do this what we're doing is we're going to say and we've seen this before so max value 495 here for a value one where it's value two it's gonna be 595 which we see and here it's 7985. now uh for three but if you look over here that stat id is just picking one it's that idea of one it's not the max value stat id 2 is not the max value so what we would have to do if we want to get the id where the values the max we actually have to come in here and actually we can do it from here is we can do select star from stats where uh was that value two equals or we're value two let's say n we're gonna do this we're gonna do a little intersect here and we're just going to call them as value 2. uh let's see and how is that going to look uh see it's select stats start from stats for value to and select max value as value two from stats group by value one all right let's go look at that guy real quick oh let's do this okay so that's that guy so now what i want to do let's do this oh where stat step value 2 and that let's see if we can do that this may not like it because i'm going a little bit stats where stats stop value too oh let's try this my mistake and there we go so now we're seeing that we have the actual stat id so what we can do here is we'll take that and we'll place it up here well i guess we can and so we did it this way instead of an inner join but we can also take that same one which is a complex query as we can do here and now we get this oh and now we're getting both of them we get our s1 and we get our s2 we get our duplicate again so we can do some pretty complicated inner selects and that's what we're going to continue talking about next time i don't want to run too late this time around i got a little sidetracked on a couple of things but we'll come back and we're going to continue looking into these but as always go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
hello and welcome back we are continuing
our season where we're looking at our i
guess our series where we're looking at
sql particularly mysql and mariadb
and how we can improve our sql skills
this episode
we're going to continue digging into
some of the
insert type things creation and just
sort of managing our tables and such
the first thing we want to do
is
use like an alien the best way to do
this i'm going to have to show you is
we're going to use an alias and so if
you look at some of our prior
queries like select star from tutorial
parent
we get a record set but we can also do
let's give it an alias and we can either
do it with the space and then the name
and then we can do tp.star
whoops
and we're going to get the same set or
we can do as
to give ourselves an alias
we get the same thing now let's look at
if you look at the
selection here
you'll see
that we've got some default header
things and we can actually change those
around so instead of that we can use
select
let's do t parent
id comma t1
id comma
username comma email
comma
start year
from tutorial parent
and
whoops we're going to keep him as we did
use that alias now since we don't have
anything we don't have any conflicts
here we're okay we don't need to do a tp
dot in front of each of those
but for
clarity
that would be something we'd want to do
and i'm about to change this over
because this is a little slow on the
editing
so
let's play with this in our editor
whoops
come here
and do that
okay
so now you can see
that now
well our parent our names are the same
but now what i can do is i can say this
is gonna be uh
i'll do aspid
as t1 id
as name
as mail
as start
whoops
and now we will see
that here
we just reset our columns now this
becomes very important
when
ever we're doing uh whenever we're
dealing with a programming language and
pulling stuff out because inevitably
we're going to have this is our result
set
this thing right here
and when we pull values out it often is
going to be by name by column name
so we would want to pull you know mail
that column name we'd have to get that
value in order to bring it across if
it's something that's you know very
complicated for example
t parent id then there can be a problem
with that
and that becomes
a little bit of an issue
even with some complex queries so let's
take a look at one of those where we do
one of our prior
joins
and let's dig around a little bit and
take i think from let's see if we've got
one nope
and grab one just to save some time
he doesn't have one
here we go
oh here we go okay
and so we ran into this before
where we were pulling that we were
specifically stating values that we
wanted out of each of these
now if we have one that is unique let's
go back and pull this query real quick
so if we look at this query
then it comes up it gives us a username
email login address name c state zip but
now if we have
unfortunately
uh oh here we go
so for example we have
this type id exists twice
so we could do so if we said
i just want the type id
then we're going to get
whoops
oh i did we're going to get an error and
it's going to say that column's
ambiguous because it says hey that
exists in both
the
lt
and the t1 table but now if i come in
and i do t1
then i can say
that this
whoops this is the one i wanted so now
it sees it says that type id and it
doesn't tell me what the table is note
that even though i'm directing it from
various tables
it's just going to give me it the column
name is without the table prefix and
technically we could always do this
when we do this like select star
whoops if we do a select star from
tutorial parent
we could also do
select tutorial parent dot star
sometimes that's easier to read oops i
should use a semicolon sometimes it's
easier to read and sometimes not
particularly if you have a long
name now
i think i'll start the next one jump
into is creating a table from a table
and in so doing we're going to let's see
show
that shows show tables so i'm going to
go ahead because we've done this for a
while i'm going to change these names
i want to change the name of tutorial 1
which looks like this
and basically what i want to do is say
you know what this is going to be an
address
so i'm going to do create
table
address
as select star
from tutorial 1.
and when i do so
i can do describe address oh let me do
show tables real quick first
i should probably learn how to type
better
so i do show tables
and i don't have a typo we can see now i
have this address now if you look at it
let's look at
tutorial 1 where i created it
and then let's look at address
and you can see here
that the big difference is we don't have
an auto increment we don't have that
constraint those don't necessarily come
across and here we go
same thing with so we have that primary
key it's not set
and the
um the foreign key relationship that's
not set so we would have to actually go
in and do an alter table
address
alter column
t1
id and i want him to be let me just
steal some text from like probably here
uh nope
uh and let's just do him as
like this and i think i can ultra
channel one
let's try that
nope
oh
i wonder if it needs
may need this
oh it's not altar cow
is it
i'm gonna play for a second nope okay so
it is a calm
all right so let's not mess with that
right now simply because uh but it is
easier to do
uh alter column
actually uh oh
sorry it should be column
that's probably it
nope still not it
but
i do want to use
um
i'm going to go in and set as i did
probably somewhere
do i have an example of it
i do not
oh here we go
here we go so i can do
a constraint
and then he's gonna be
this will be uh
address pk
it's gonna be
i'm gonna call it
primary key
let me go steal that little thing from
probably day four
well day three
oh
i just have to do this
t1 id
see if that'll work yes it will so now
if i describe address
so now i can see where i have that
primary key i could probably go but i
think then i can go back and alternate
and make alter it make it that but i'm
not going to mess with that right now
because
what i can do whoops
what i could do then is i can do
tables and so if i do drop table
tutorial
one
oh it's because of the parent so i have
to deal with parent first now i'm going
to do create
a
table
[Music]
call it app user
as select
star from
tutorial parent
cronkite tutorial actually
oh
truncate tutorial one
up
helps to do that
now if i try to drop table tutorial one
up can't
let's go ahead and empty both of those
uh let's see tutorial one
it's tutorial parent
oh
now
there we go it takes a few minutes
to walk through all of those that's why
the constraints can be a bit of a pain
but they are also
useful to us stop saying shows i keep
using s's all over the
place and so now we have slightly
different looking
uh tables and we've sort of cleaned up
our names a little bit
and we can come back later and
add in uh adjust all of our constraints
that i will deal with at a later point
because
right now i want to jump into
so now we've got a couple tables we can
work from
and now i want to do a
a way to
get a little more complicated in our
queries so this one
let's start with
select star from stats
and so
whoops
so start with something like that but
what i can also do is i can interjoin
with other
tables or with other queries now i can
do it with itself
so i could do
stats i'm going to do s1
enter join
stats
s2 on
oh was that
s1 dot stat id
equals
s2 dot stat id
and
so if i do this i'm just going to see
the table twice
or the fields twice so i can see here
stat id so this is table one
this is table two
now this becomes a little more
interesting you know this is not
terribly
interesting but what we can do
is we can say instead of
stats
2 being that
now we can change it let's say we're
going to
select star from stats
and let's do uh
let's do this
actually we don't need to
yeah so let's do this we're going to do
select star from stats
can i do that
no i don't want to do that i want to do
this i want to do
let's do stat id
comma
max
uh let's pick max that we want to do
uh let's see max value
one
as
mx value
from stats
and then what i want to do is
group by
value
ah
let's see
oh wait i want to group by value one
and let's do max value 2.
so let's look at that first
so when we do this what we're doing is
we're going to say and we've seen this
before so
max value 495
here
for a value one
where it's value two it's gonna be 595
which we see
and here
it's 7985. now
uh for three but if you look over here
that stat id is just picking one it's
that idea of one it's not the max value
stat id 2 is not the max value
so what we would have to do
if we want to get the id
where the values the max we actually
have to come in here
and actually we can do it from here is
we can do select
star from
stats
where
uh was that value two
equals
or we're value two let's say n
we're gonna do this we're gonna do a
little intersect here
and we're just going to call them as
value 2.
uh let's see
and how is that going to look
uh see it's select stats start from
stats for value to and select max value
as value two from stats
group by value one
all right let's go look at that guy real
quick
oh
let's do this
okay so that's
that guy
so now what i want to do
let's do this
oh
where
stat step value 2
and that let's see if we can do that
this may not like it because i'm going a
little bit
stats where stats stop value too
oh
let's try this
my mistake
and there we go so now we're seeing that
we have the actual stat id so what we
can do here
is we'll take that and we'll place it up
here
well i guess we can and so we did it
this way instead of an inner join but we
can also take that same one
which is a complex query
as we can do here
and now we get this oh and now we're
getting both of them
we get our s1 and we get our s2 we get
our duplicate again
so we can do some pretty complicated
inner selects
and that's what we're going to continue
talking about next time i don't want to
run too late this time around
i got a little sidetracked on a couple
of things but we'll come back and we're
going to continue looking into these
but as always go out there and have
yourself a great day a great week and we
will talk to you
next time
you