0:02
[Music]
0:06
thank you
0:09
[Music]
0:12
Hello friends welcome back to our
0:13
channel so in today's session we'll
0:16
discuss about a few queries related to
0:18
the Silas table board stable and
0:20
resource table so in the previous
0:22
session already we have created these
0:24
three tables and also we have inserted a
0:27
few rows in these tables so already in
0:30
the previous videos we have executed a
0:33
different queries related to a single
0:35
table but here we are writing the three
0:37
tables okay so we need to relate one
0:40
table with another table that that will
0:41
be done with the help of a foreign key
0:43
right so seller stable which is having
0:46
the attributes Sid s name rating and age
0:50
and a board stable there are attributes
0:53
called vote ID both name and a color and
0:56
there's a stable is having the three
0:58
attributes Sid bid and day so
1:05
as a primary key and in the results
1:08
table where we have taken Sid and did as
1:11
a primary key and they will be acting as
1:14
a foreign key right so by using this one
1:17
we are relating with the remaining two
1:19
tables now we will see the queries
1:22
finding the names of the Silas who
1:24
reserved the boot number three so start
1:26
writing the query here so
1:29
says we have to find out the names so
1:32
write on select
1:35
yes name
1:38
from
1:40
Silas
1:42
from Silas and what we have to get get
1:46
the details of sailors who reserved the
1:48
port number three so the reserved board
1:51
members I mean the the reservation
1:54
details will be available in reserves
1:56
table so we have to include that table
1:58
also
2:00
Reserves
2:02
all the results I mean uh the seller who
2:05
reserves the boats will be available in
2:08
this particular reserves table so we
2:11
have to include that one so select s
2:13
name from cellular comma Reserves and
2:16
then then
2:18
we need to relate two tables we need to
2:21
relate these two tables so the two
2:23
tables will be regulated with the common
2:25
attribute what is the common attribute
2:27
among these two tables Sid so where
2:30
sailors
2:32
dot Sid is equal to Reserves
2:38
dot s i b so this will relate these two
2:41
tables but what we have to get who
2:44
results about number three so here we
2:46
need to write The Logical operator and
2:49
so both ID is equal to 3 so this will
2:53
give the all the seller's name who
2:56
reserve the board number three so once
2:59
more than one sailor can resolve the
3:01
boat number three right so the name of
3:03
the scientists who reserve the boat
3:05
number three will be getting like this
3:07
and coming to the second one here we
3:09
have to find the names of our sellers
3:11
who reserve the red Port so here we are
3:13
getting the seller's name based upon the
3:15
color of the boot right so start writing
3:18
the query so SQL prompt so select
3:24
s name
3:26
from where the s name will be the SM
3:30
will be in silence so from Silence
3:34
Sailors comma so reserved reserved means
3:38
the reserve details will be in resource
3:40
table so results
3:44
and also you can observe you need to get
3:46
the details who reserve the red board
3:48
red means the color of the board so the
3:50
color of the board will not be available
3:52
in results the color will be available
3:53
in Boots so we have to include this name
3:56
also both
3:58
where we need to link these things first
4:01
result Sailors reserved so we need to
4:04
link these two tables Sellers and
4:07
Reserve with the common attribute the
4:08
common attribute is Sid and Sid so right
4:11
on the same thing
4:13
sailors
4:15
dot sib is equal to
4:18
results
4:21
dot sib so this is nothing but our
4:23
natural join without any condition based
4:25
upon the common attributes we are
4:27
joining the tables
4:29
right uh is this enough no because we
4:32
need to get the details who reserves the
4:34
red color boot so we need to find out
4:37
the color so color is not available in
4:39
this reserves table so color is
4:41
available in both stable so we need to
4:44
relate these two tables the results in
4:47
both with a common attribute bid so you
4:50
can write it as n after this one
4:52
silos.sid and resource dot Sid and
4:55
results
4:58
dot bot IDE is equal to
5:02
words dot both ID so now we have related
5:06
all these three tables okay we have
5:09
related all these three tables is this
5:11
enough no so we need to check for the
5:13
red board
5:15
and
5:16
and
5:18
boards dot color
5:21
is equal to
5:25
boards.color is equal to Red so we are
5:28
relating the three tables and finding
5:30
the condition both dot color is equal to
5:32
Red so that will get the complete seller
5:35
names who reserves the red color Port
5:37
now so hope you understood the second
5:40
one
5:41
so first let me write down the queries
5:43
here after completion of this one I will
5:45
execute each and every query in the
5:47
MySQL command line prompt right so
5:51
so next one finding the colors of a
5:53
boards reserved by Sandeep so write down
5:56
the query here so first we need to
5:58
select
6:00
color
6:08
from the color is available in boards
6:11
from boards
6:13
so we need to find the boards color
6:16
boards result so result means results
6:19
table so we need to include that one
6:21
Reserves comma and here we have to
6:26
search based upon the main okay given
6:29
name so this name is available in
6:31
failures so you can observe Sailors so
6:33
we need to include that one also Sellers
6:35
and now start uh relate on table with
6:39
another table so where where class now
6:42
relate one table with another table
6:44
first so the board ID
6:47
I mean the board's table is related to
6:50
results so boards
6:53
dot board ID is equal to
6:56
results
6:58
dot board ID and this is not enough
7:02
because we are we are searching the
7:05
details of board with respect to the
7:07
name name is in Sailors so we need to
7:10
relate the results with the silos with
7:12
the common attribute that is Sid so here
7:14
we are relating one table with another
7:16
table with respect to the common
7:18
attribute here the common attribute
7:20
between sales and resources FID so sales
7:22
dot so here I write results
7:27
dot Sid is equal to
7:31
sine s dot s i d so is this enough no so
7:36
we need to search with respect to the
7:37
name so and
7:40
silence
7:42
dot yes name
7:45
is equal to give the name so which we
7:49
need to compare right so name so this is
7:52
the query to execute I mean to get the
7:55
colors of a boards who reserved the
7:57
results by the given name okay
8:00
and the last one finding the names of
8:03
the sailors who have at least one boat
8:05
who have at least one boat so that means
8:09
simply relate all these three things so
8:11
that we'll get the details of sailors
8:14
who Reserves at least one vote right so
8:17
here the query will be SQL
8:20
so select
8:21
we need to get the names so s name
8:25
from
8:27
s name will be in silos first right on
8:29
the sellers so who have at least one
8:31
more the result will be in resource
8:34
table so comma
8:36
results and we need to check whether it
8:40
was reserved with one board or not so
8:42
that relation will be with the help of
8:45
both ID so boards
8:48
boards where relate each terms like
8:52
silence
8:54
dot Sid is equal to
8:57
results
8:59
dot Sid
9:02
and there is the reserves table with the
9:05
board stable
9:07
results
9:08
dot board ID is equal to
9:12
boards dot board ID so this will give
9:16
the complete details of assailers who
9:19
reserved at least one vote at least one
9:21
vote right so this is the query we need
9:24
to execute this one so here we are
9:26
relating one table with another table
9:29
with the help of a common attributes
9:31
that we call it as a natural join
9:34
right so
9:36
this is how you need to write the
9:38
queries now I will implement the same
9:41
queries
9:42
in the MySQL I will I will explain You
9:44
by showing by executing these queries in
9:47
MySQL so let's move on to the MySQL
9:49
command line
9:50
Hello friends so just now we have seen
9:53
the queries some queries related to The
9:56
Silence reserves and the boards table
9:58
now I will show you the execution of
10:00
those queries so we have seen the
10:02
queries for the following questions
10:04
because finding the names of Silence who
10:06
reserve the board number three finding
10:07
names of signers who reserve the red
10:09
board finding colors of boards reserved
10:11
by Sandeep and finding names of Silas
10:13
who reserved the at least one book right
10:16
now let us open the MySQL command line
10:23
so give the password and clear the
10:26
screen now use the database because we
10:29
have created the table siler's reserves
10:32
and the boards in the YouTube database
10:35
so use the YouTube database
10:37
now let us check with the tables first
10:39
show tables
10:41
yes boards results and signers let us
10:44
check whether the rows are available
10:45
select uh star so that we'll get all the
10:48
detail so from boards yes so select star
10:54
from
10:56
sailors yes select star from Reserves
11:04
yes so we had a few rows available in
11:07
all these three tables now let's let's
11:09
start executing this one so finding the
11:11
names of sellers who reserved the board
11:14
number three so for that what we have to
11:16
do so we need to use the select command
11:18
to need to retrieve the data so select
11:21
instead of using the star we need to get
11:23
the names of the Siler so s name from so
11:26
s name will be available in sales table
11:28
so I'll write here
11:30
Sailors comma what we have to do Sailors
11:33
who reserved board members so reserved
11:35
means it will be available in the
11:37
results table so Reserves
11:40
okay and what we get boat number three
11:42
so Board number will be available in
11:44
results cellular name will be available
11:45
in seller so we need not uh include the
11:48
boards table also so write down the
11:50
condition where we need to relate these
11:52
two tables with the help of a common
11:53
attribute so the common attribute among
11:55
these two tables is Sid so Silas dot Sid
11:59
is equal to
12:04
results Dot
12:07
Sid
12:08
and that's not enough so here we need to
12:11
check with the board number three and
12:14
B ID is equal to 3. so here we can write
12:18
it B ID is equal to 3.
12:22
right
12:24
C
12:26
so the seller name who reserved the boat
12:29
ID3 is run let us check here so first
12:33
let us check here
12:35
boat ID3 so white ID 3 in the reserves
12:38
table you can check because the the link
12:41
will be in the resource table byte so in
12:44
the results table the board the board ID
12:46
is reserved by the 104 Sid so what's the
12:49
name of one zero four Sid it's a rub
12:52
right so hope you understood this one so
12:54
we are just relating these two tables
12:56
with the help of a common attribute so
12:58
aside is a common attribute so sales dot
13:00
side is equal to reverse.id and bid is
13:03
equal to 3. so in the resource table bid
13:05
is equal to 3 is reserved on 21st to
13:09
February 2021 by seller id104 so now we
13:13
can get the name from this particular
13:15
Siler ID so 104 Silo already name is ROM
13:18
so the name the seller who reserves the
13:22
board number three is from so I hope you
13:24
understood this one right next the
13:28
finding names of the sellers who reserve
13:29
the red Port the same thing so select s
13:33
name from Sailors and here we we need to
13:38
get the results who reserves the red
13:40
board so also include the reserves
13:43
comma and here we are checking with the
13:46
help of a color so color will be in the
13:47
board so we need to include this boards
13:49
also boards where link the thing with
13:53
the common attributes so Sailors dot Sid
13:56
is equal to
13:58
reserves dot Sid
14:01
and also we need to relate the results
14:05
table with the boards table so reserves
14:08
dot bid is equal to
14:13
boards.bid and see both dot color is
14:18
equal to
14:19
red
14:21
so we got two names who reserves the red
14:25
color ports
14:27
okay let us check whether it is right or
14:29
wrong
14:33
all right
14:36
see color boards color red okay so red
14:40
color
14:42
a boat ID is two red color is red color
14:46
both ID is two so we need to find the
14:49
sailors who reserves the second boot so
14:52
one zero three and one zero five so we
14:56
need to get the names of one zero three
14:57
and one zero five because both have
14:59
reserved the same number Port which is
15:02
of red color so one zero three and one
15:04
zero five so we can get one zero three
15:06
as Rajesh and one zero five as Hari so
15:09
we got the result as Rajesh and Hari
15:13
so once again I'm saying so we need to
15:15
get the color red who reserves the color
15:16
red so the color red color both ID is 2.
15:21
okay red color both ID is two now we
15:24
need to find in the results table who
15:25
which signers have reserved this board
15:27
too so one zero three results about two
15:30
and one zero five results the both two
15:32
so one zero three name is Rajesh and one
15:34
zero five name is so we got these two
15:36
names
15:37
so hope you understood this query
15:40
right next one so finding the color of
15:42
boards reserved by Sandeep now we need
15:45
to find out the color with respect to
15:48
the name cell as name now select color
15:53
from boards okay comma Reserves
16:02
reserves comma sellers because we are
16:05
having up all the three things okay
16:07
color is in boards reserves that means a
16:10
relation will be in results and name
16:12
will be in the silence
16:14
where relate the things so
16:17
boards.bid is equal to reserves Dot
16:21
bid and
16:23
Reserves
16:27
dot Sid is equal to Sailors dot Sid and
16:32
sailors dot s name so we need to check
16:36
this once s name is equal to
16:41
Sunday
16:43
so there is a green color both so the
16:47
Sailor Sandeep have reserved green color
16:49
boot let us check so go to the sun leap
16:52
so Sandeep Sid is one zero one so let us
16:56
check 101 both I mean which which boot
16:59
ID reserved by the one zero one so one
17:01
so one means both one is in color green
17:06
hope you understood see so we need to
17:09
check the color of boards who reserved
17:12
by the name Sandeep first go to the
17:14
seller's name Sandeep Sandeep is the
17:17
name of a Seiler who is having the
17:19
sid101 now go to the results table and
17:22
find out the corresponding boat idea of
17:25
1 0 1 so 1 0 1 Resource of boat ID 1 so
17:29
go to the board table boat id1 is having
17:32
the color green so we got the green
17:35
color
17:36
so hope you understood this one
17:40
right
17:41
yes so next the last one the finding
17:44
names of sellers who have reserved at
17:47
least one boot
17:48
finding names of sellers who have
17:50
reserved at least one board
17:51
so simply we can relate the tables so
17:54
that we'll get the details so select so
17:57
what we have to get names s name
18:00
from
18:02
sellers
18:05
so relation Reserves
18:08
boards where
18:11
Sailors dot Sid is equal to reserves dot
18:15
Sid and
18:17
Reserves
18:20
dot bid is equal to
18:23
gods.bid so simply we'll get all the
18:26
details of an sellers who reserved at
18:29
least one vote now you can observe
18:32
Sandeep one two three four five six and
18:35
seven are there
18:41
yes see Rajesh having a two boats and
18:46
Harry is having I mean the same person
18:48
reserved the two different ports so at
18:51
least one you can observe here so in the
18:54
sailors table in the sales table uh we
18:57
are having five names Sandeep Rajesh
19:00
ramu and Hari right one zero one one
19:03
zero two one zero three one zero four
19:04
and one zero five now you can observe so
19:08
reserves table so one zero one so that
19:11
means already suddenly reserve one boot
19:13
one zero two results one more one zero
19:16
three it is also two boards right one
19:19
zero four one more one zero five two
19:20
boats
19:21
so that means you can observe all the
19:25
sellers reserved at least one book all
19:28
right now let us say we'll remove the
19:30
one zero four okay we'll remove the one
19:33
zero four so delete
19:36
from results
19:39
where
19:42
where
19:44
[Music]
19:44
um
19:45
what is this one one zero four no sorry
19:48
where Sid is equal to one zero four
19:53
so delete it now you can check select
19:55
star from Reserves
19:58
so you can observe here there is Row one
20:00
zero four okay one zero four one zero
20:03
four means you can observe one zero four
20:05
is nothing but rub so ramu have not
20:08
reserved any boat here now now execute
20:10
the same query so
20:13
getting all the seller's name who have
20:15
reserved at least one boot now you can
20:17
see ramu name will not be available here
20:19
because ramu doesn't Reserve I mean is
20:23
have not resolved any board so Sandeep
20:26
Rajesh and Hare
20:30
let us
20:34
so we can use a distinct
20:38
we can use a word
20:41
distinct so that will not get any
20:44
repetition distinct means it will remove
20:46
the duplications so these are the four
20:49
four names who have reserved at least
20:52
one boat at least one boat
20:55
right so hope you understood these four
20:57
queries
20:59
right so I have explained you about the
21:01
theory and we have I have shown you
21:03
about executing these queries also
21:06
right so let's stop here in the next
21:08
session we'll go with a few more queries
21:09
related to these designers results and a
21:12
boards table so if you are having any
21:14
doubts regarding this one feel free to
21:15
post your doubts in the comment section
21:16
definitely try to clarify all your
21:18
doubts if you really enjoyed my session
21:20
like my session
21:22
share my session with your friends and
21:24
don't forget to subscribe to our Channel
21:25
thanks for watching thank you very much