0:02
[Music]
0:06
thank you
0:09
[Music]
0:11
Hello friends welcome back to our
0:13
channel so in the previous session we
0:15
have discussed about different commands
0:18
in SQL that is a data definition
0:19
language commands data manipulation
0:21
language commands right so we have taken
0:24
only one table and we have seen all
0:26
these comments how these commands will
0:27
be executed now we'll see some more
0:31
queries by executing multiple tables
0:34
that means we have discussed about the
0:35
join operation right so natural join so
0:39
we'll see the queries related to
0:41
multiple tables so how we can get the
0:44
data from multiple Tables by using this
0:46
join operation and also in the previous
0:48
session we have seen in order to relate
0:51
two different relations we have to use a
0:54
concept called a foreign key so an
0:57
attribute which is the primary key of
1:00
one table will be acting as a foreign
1:02
key in another table so we'll see those
1:05
type of queries right so how we can
1:08
relate one table with another table by
1:10
creating the foreign key and we how we
1:12
can get the details from the multiple
1:15
tables right so for this I would like to
1:18
take an example of sailors
1:24
Sailors table
1:29
both table
1:33
Reserve stable so usually most of the
1:36
examples you will be receiving these
1:38
tables right so we'll also see the
1:40
queries related to these tables sellers
1:42
table board table results table so the
1:44
concept here is the sailors will reserve
1:48
a boat
1:49
right assailers will reserve a boat so
1:53
that concept is represented in three
1:57
different tables three different tables
1:59
so in the silence table what are the
2:02
attributes what are the attributes so
2:05
one is a silent ID seller ID next seller
2:09
name
2:10
sailor name next what is the rating
2:14
given for this particular silent and the
2:17
age age of the cellular so these are the
2:20
attributes we are going to take for this
2:22
sellers right so we'll create the tables
2:25
and we we have to insert some values to
2:27
the tables and then we will find out the
2:29
queries and coming to the both table
2:32
the boat ID is one attribute
2:35
both name
2:38
and color
2:41
color color of the boat okay and Reserve
2:44
stable coming to the reserves table
2:47
so we we have to relate these things so
2:49
we will have as ID
2:52
board ID
2:55
and the next one is reserve date
3:00
Reserve day so the day which we have
3:03
reserved The Silo reserved that
3:05
particular boot right so here we can say
3:08
S ID and B ID both are a primary key
3:13
here in this reserves One S ID and bid
3:17
both comes under the primary key so here
3:20
it is a primary key and here it is a
3:22
primary key
3:23
and in this results table we are going
3:25
to take Sid and boot ID as a primary key
3:29
as well as these are the foreign keys
3:33
these are the foreign keys so just now
3:35
we have discussed what is a foreign key
3:36
so the foreign key is a attribute which
3:40
is a primary key in another attribute so
3:42
here you can see primary key Sid is a
3:44
primary key of silence and vid is a
3:46
primary key of both so these two
3:49
attributes will come say under foreign
3:51
key in this Reserves
3:54
Reserves
3:56
right so this is a concept and in our
3:59
further sessions we are going to discuss
4:01
about the queries related to these
4:03
tables so in this session I would like
4:06
to use the two commands one is create
4:10
and insert create and insert so in this
4:15
session let us create the tables silos
4:18
with the given attributes both with
4:19
given attributes and results with the
4:21
given attributes making the constraints
4:24
a primary key and a foreign key after
4:26
that we'll insert a few rows in Sailors
4:29
boards and reserves so in the next
4:32
session we'll discuss about a few
4:33
queries related to this one
4:36
right so let's move on to the MySQL so
4:39
that let us create the table for all
4:42
these three things and also we'll insert
4:44
some data for all these three tables so
4:46
let's move on to the MySQL
4:49
Hello friends so just know we have seen
4:52
the tables and the attributes that we
4:55
are supposed to create so first let us
4:57
create and then we'll insert the data so
4:59
I'm entering into the MySQL command line
5:02
interface so I'll go with the CLS so we
5:05
have already created one database called
5:07
the YouTube so we are we just enter into
5:09
this YouTube and now we are supposed to
5:11
create a table in this particular
5:13
database the first table is assignments
5:15
so the syntax is create table table name
5:19
Silas so and here we need to give the
5:22
attributes first one is Sid which is of
5:24
a integer comma the second one is a s
5:27
name which is of a where care with a 30
5:30
characters comma and the rating so
5:34
rating it will also be taken as an
5:36
integer comma and age H can also be
5:39
taken as an integer right and primary
5:43
key we have to set the primary key the
5:45
primary key will be Sid
5:50
so you can observe query okay zero rows
5:52
affected that means the size table has
5:54
been created so you can observe here
5:57
show tables
5:59
so in that there is a silos table now
6:01
let us create a boards table
6:04
so create
6:08
table table name table name it's both
6:13
boats the first attribute is boat ID
6:18
which is of integer comma both name
6:23
which is of where care
6:26
with the 20 characters and the third one
6:30
color with a
6:33
data type where cat this is also we can
6:36
take it one t so these three
6:40
and here we can consider the primary key
6:45
as vote ID
6:51
vote ID right
6:54
so the table boards is also been created
6:57
so you can see show tables boards right
7:01
now we have to create a reserves table
7:04
where
7:08
the Sid and bid
7:11
so
7:12
create
7:14
table
7:16
reserves Reserves
7:20
the first one S ID
7:23
of integer
7:26
sorry comma B ID of integer
7:32
comma
7:35
day Reserve day right as a date data
7:40
type
7:41
then
7:43
primary key
7:45
is a S ID comma b i d so both will be
7:50
considered as a primary key
7:53
foreign
7:55
key
7:59
the foreign key
8:02
of Sid which is a references
8:09
to Sailors of Sid so Sid is a primary
8:14
key of sales table so we have to write
8:16
like this comma again
8:19
foreign key
8:22
of boat ID which references
8:26
which references
8:28
Boards of boat ID
8:32
so just execute this one so zero is
8:35
affected so you can observe short tables
8:38
now we are having the three tables
8:40
boards results and silence boards
8:42
reserves and sellers now we are supposed
8:45
to take inside the values we are
8:49
supposed to inside the values so first
8:51
let us insert the values into sellers so
8:54
uh the command insert
8:57
so let us clear the screen system CLS
9:02
right so
9:05
insert
9:07
into
9:09
sailors
9:11
values
9:15
so give the values 1 0 1 is a seller ID
9:21
let us take some Sandeep as a sailor
9:23
name
9:24
and the rating as a mine
9:27
and age is some 25.
9:31
comma go with the second attribute I
9:34
mean second values one zero two
9:37
with the saradi
9:40
rating with 10
9:42
and age with a 27.
9:46
next third one one zero three
9:49
name is
9:51
Rajesh
9:54
and six rating
9:57
with a 26 age
10:00
and go to the 104
10:03
ramu
10:06
sorry here it is a wire care so we have
10:08
to place in the quotations ramu and here
10:11
it is some eight
10:14
and twenty five
10:17
comma 105
10:20
some hurry
10:24
and some six
10:27
with a 28
10:30
106
10:32
with Shiva
10:37
and with a 10
10:39
and with the age
10:41
30.
10:43
right so let us execute this one
10:47
oh
10:58
so let us once again take it this one
11:01
into
11:03
Sailors values
11:09
101
11:12
Sandeep
11:13
nine
11:15
and 25.
11:18
yes
11:22
values
11:25
one zero two
11:28
sarathi
11:30
with a 10 and 27
11:33
so how close one more parenthesis here
11:36
so that's why we got an error so uh I'm
11:39
sorry for the delay so I'll make it fast
11:42
so Rajesh and uh six with a 26 age
11:49
104
11:51
with ramu
11:54
and 8 and 25
12:00
comma 105 with Harry
12:06
and 6 comma 28 so let us stop here
12:10
so query side now you can observe select
12:13
star from
12:14
Sailors so we got some five tables that
12:18
means a five sales name
12:20
now let us go with the boots
12:23
inside the value for boots right so
12:26
insert into table name values so give
12:31
the name vote ID that's one
12:34
and board name
12:37
board one
12:38
and the color
12:41
green
12:43
comma go to the second one go to
12:47
go to and the color
12:51
red
12:53
next third one third boot
12:56
with a
12:57
board 3
12:59
and color
13:01
blue
13:02
so here we have taken the color is also
13:04
as
13:05
that cancer we are supposed to give in
13:08
quotations
13:09
so
13:10
board four
13:15
with a color
13:17
black
13:19
right so for us executed so you can
13:22
select uh star from
13:24
boards so we got some
13:27
green the four four rows right
13:31
now we'll insert the data into
13:34
reserves table so insert
13:38
into table name Reserves
13:43
values
13:47
so give the seller's ID result so first
13:49
board on the date
13:52
2022 so we need to give the four digit
13:54
year with a two digit month and two
13:58
digit day so let us execute this one see
14:00
you can observe here query okay one row
14:03
affected yes so let us insert one more
14:06
thing so instead
14:09
Reserves
14:11
values
14:13
1 0 2 sorry 1 0 2 and make sure that
14:17
this both Eddies and cellular so seller
14:19
ready should be available in both stable
14:21
and selectable because we are
14:22
referencing the boat ID and sellers ID
14:26
as a foreign key right so if you are
14:28
trying to give the sales idea or boat ID
14:30
which is not available in those tables
14:32
automatically will get the error right
14:35
let me show you that one so we have
14:37
created only one zero one two one zero
14:39
five so I am trying to give 106 as a
14:41
silent side which is not available in
14:42
sales table right so here I will give
14:45
some reserves the second board 2022 uh
14:48
one and ten or one right so if you
14:53
execute we'll get a error cannot add or
14:55
update a change row a foreign
15:02
and here 106 is the ah foreign key right
15:06
which referencing the seller's ID from
15:08
the sales table so in the sales table we
15:11
are not having this one zero six so
15:12
that's why we got an error otherwise C
15:16
so we need to give the
15:18
values which are available in sales ID
15:20
and boot ID so 102
15:23
and reserves the first board on the date
15:26
2022 sum 0 2 and 12. right I am giving
15:29
some random dates
15:31
so one one more uh
15:33
value has been inserted row has been
15:36
inserted 103 and
15:39
to the fourth boat and give the
15:44
2022 and 7 and 11.
15:49
so one more uh row has been inserted so
15:52
one zero four reserves the third board
15:56
and on the date
15:59
two zero two one
16:00
zero to twenty one so randomly I'm
16:03
giving the dates so once again I'll give
16:05
the fifth insertion so one zero five
16:09
and let us take it as a two second boat
16:14
second boat with the 2022 0 4 and 25.
16:20
so this will only be accepted so because
16:25
so we have given the uh ID right this
16:29
one uh seller's ID and the both ID as a
16:33
primary keys so let us try to insert one
16:36
more
16:37
okay
16:39
row
16:41
a Reserves
16:45
values let us take one zero 5 with a one
16:50
first board and
16:52
2022 0 9 10.
16:56
so
16:57
yes so one zero five reserves second
17:01
board as well as first boat similarly we
17:04
will insert one more table I mean one
17:06
more row insert into
17:09
results
17:11
values
17:13
1 0 3 reserves so previously it was
17:18
resource fourth one so now it reserves
17:20
the second second board on the date 2021
17:24
8 and 20.
17:28
yes so we have inserted the
17:30
rows in all the three tables now you can
17:34
observe here so
17:36
select star from
17:39
results
17:42
see this is the information
17:44
reserved
17:46
the Silas and boat IDs reserve dates and
17:50
this is the
17:56
silos information
17:58
and
18:00
this is the
18:03
board's information
18:06
and Boots information right so in
18:10
today's session we have successfully
18:11
created the three tables with the
18:14
different attributes and with the
18:17
primary key and foreign key now in the
18:20
next session we will discuss about a few
18:22
queries which relate one table with
18:24
another table because we have created
18:26
the tables with the primary key and
18:27
foreign key right so in the previous
18:29
sessions we have created only one table
18:31
so we have not
18:33
executed the queries related to The
18:35
Forum key right only one table we have
18:38
seen and from the table we have executed
18:40
the queries now we have created the
18:42
three tables and we are supposed to
18:44
execute the queries which gives the data
18:47
from all the three tables right so let's
18:50
stop here so hope you understood the
18:52
once again the repetition of creating a
18:55
table and insertion of rows but here we
18:58
have given a foreign key and the prime
19:00
primary key constraints right so let's
19:03
stop here and then the next session
19:04
we'll see that some more queries related
19:06
to this sales boats and reserves in
19:09
so if uh if you are having any doubts
19:12
regarding this one feel free to post
19:13
your notes in the comment section
19:14
definitely I will try to clarify all
19:15
your thoughts if you really enjoyed my
19:17
session like my session share my session
19:18
with your friends and don't forget to
19:19
subscribe to our Channel thanks for
19:21
watching thank you very much