0:02
[Music] thank you
0:10
Hello friends welcome back to our channel so in today's session we'll discuss about the introduction to SQL
0:25
introduction to SQL so first let us discuss about what is an SQL and what is
0:32
the importance of SQL and then we'll move on with the SQL what is what exactly we have to learn in the SQL
0:38
so SQL stands for
0:45
structured query language
0:52
structured query language and this SQL is used to work with the databases
1:01
the main use of SQL is to work with
1:07
database so we know that database means the
1:13
collection of tables if it is a relational database right there are different types of database so we have
1:18
already recorded about the different types of databases in our channel so please go through that so the link will
1:24
be in the description section so if it is a relational database so the data will be stored in terms of a table so
1:30
the database will be having lot of tables so table one similarly table two
1:37
right table 3 Etc so we'll be having a different tables in a single database
1:44
then if you want to so what exactly there will be in table so a table will
1:49
be having data right data
1:56
data so the data will be in terms of uh rows and columns right right rows and
2:03
columns so if you want to access this data okay if you want to access this data that means accessing
2:14
or manipulating
2:22
on data so we need a language called structured query language
2:29
so this query language is used to access or manipulate the data which is
2:36
available in the database right so accessing means read
2:44
manipulation means adding all removing
2:50
both both we cannot call it as a manipulations right so everything will be done with the help of this query so
2:57
without using the SQL you can't access or manipulate the data available in the
3:04
database then so in order to execute this SQL we are
3:10
going to use a different dbms tools different dbms tools right so those dbms
3:19
tools so MySQL
3:26
SQL Server Oracle so etc
3:33
etc or all the different dbms tools so whatever the tool we are using
3:38
the SQL is a common language which is used to access or manipulate the data
3:44
from the tables which are available in the database right so here the query means
3:53
a query means commands the query means commands so with the
3:59
help of commands we are going to access the data accessing means reading or
4:04
writing so usually we call them as current operations we'll call it as a
4:11
current operations so
4:16
all the third operations can be done with the help of this query so curd operations means
4:22
create so creating the database creating the tables
4:30
read so accessing the data
4:36
update so we can update the data
4:44
so we can delete the data from the table so usually these we call it as a current
4:49
operations so create read update and date now what can we do with the help of
4:55
this SQL so we are saying that accessing and manipulation so a little bit elaborate
5:02
this term I mean little bit elaborate this term so we can create a database
5:12
so everything will be done with the help of a commands Okay SQL command we call them as a SQL commands
5:18
next create table
5:23
so inside the database we can create the tables
5:29
alter database so if you want to change the structure
5:35
of a database then also we can use the author
5:40
alter table altering the table that means updating
5:45
the table
5:56
and reading data So reading the data from the tables
6:06
update the data so we can update the tables I mean the data tables data
6:15
delete database so we can also delete the database
6:21
we can also delete the tables
6:27
we can also delete the data inside the table data
6:34
inside the table right so all these operations all these operations can be
6:40
done can be done using
6:47
SQL commands SQL comments
6:53
so for every operation we have to write some query squaring is a command
7:00
okay so here the commands are divided into different categories so that we have already discussed in the previous
7:07
session like a dbms languages now once again I'll reverse that one so now these
7:13
SQL commands are categorized into different ways so let us see that commands what are the different commands
7:19
and from the next sessions I will elaborate each and every command I will explain each and every comment by
7:26
executing in the dbms tool we'll use a SQL Server
7:31
so which is a free source so we'll execute each and every command in the SQL Server MySQL right so here
7:42
SQL commands are divided into different categories
7:49
so let us take the categories so one is
8:00
data definition language
8:05
so usually we call it as a ddl ddl commands ddl commands
8:19
data manipulation
8:25
language so usually we call it as a DML commands DML commands
8:38
data control language so usually we call it as a DCL
8:45
commands
9:00
so the next one is transaction control
9:08
language which we call it as a TCL so all the SQL commands are categorized
9:14
into four waves one is a data definition language data manipulation data control and that transaction control
9:21
right so usually we call all these as a different dbms languages we call as a
9:26
dbms languages so here data definition means which updates a table okay which
9:33
observes the table structure so the commands will be
9:39
create alter
9:45
drop create alter drop so these are very few commands which changes the table
9:52
structure and these are the data manipulation so as the name indicates here the commands which are used to
9:59
change the data inside the table so selection in order to read the data
10:06
attention we can update the existing data insertion we can insert the data
10:13
deletion we can also delete the data and also here there is a one more command called truncate
10:19
truncate right and these are the few currents which comes under the data manipulation language
10:25
data control language so Grant
10:32
and rebook that means so the database administrator will give
10:38
the permissions or privileges to the users right so granting the services to
10:43
the user and taking back the permissions from the user is a revoke so this comes under the
10:50
data control right and transaction control
10:56
commit
11:04
rule that commit and roll back so which will
11:09
confirm the transaction and which will undo the recent transaction
11:14
so these are the very few comments okay very few comments so comes under this
11:20
SQL so data definition data manipulation data control and the transaction control
11:26
right so this is all about our SQL SQL is a structured query language which is
11:32
used to access the data from the database so whatever the database
11:37
management tool you are using either Oracle or MySQL or SQL Server so the
11:42
data can be accessed from the database by using only the structured query language where query is nothing but a
11:48
command Command right so these comments are divided in these categories so let
11:54
us stop here and in the next session we'll see all these commands one by one
11:59
so what is the syntax and how we can create the database how we can create the tables how we can read the data from
12:06
the tables how can we update the tables so each and every command I will demonstrate in the
12:12
MySQL right so let's shop here and this is all about the introduction to
12:17
structured query language
12:23
data types in s q yeah so in the previous session we
12:29
have started the SQL so that is a structured query language so there we have seen the introduction so here uh
12:37
first of all let us see what is the data type and now then we'll see what are the different data types supported in this
12:44
particular SQL so many of you might know about these data types because if you
12:50
are aware about any one of the programming language definitely you will be knowing about these data types so
12:55
don't worry if you don't know about the data types so let me revise this concept once again what is the data type
13:01
so here the type of value given is called as a data type
13:11
so what is this type what is this type so in our computer terminology the complete data is divided
13:21
into different types different types one is
13:26
numeric strings
13:33
okay and some images etc etc different the data can be
13:39
divided in different categories different categories right in the numeric we can get it as
13:45
integers that means a whole numbers so we can simply call it as a whole numbers
13:52
whole numbers real numbers
13:59
complex numbers etc etc and similarly the strings we can call it as a characters
14:06
and strings right so similarly the whole numbers
14:11
here the whole numbers are represented as e Els integers and real numbers are
14:20
floats float values the complex is complex and the characters are a characters and this is
14:28
a usually we call it as a so
14:33
we have seen the basic concepts of rdbms in that we have seen the concepts like
14:39
entity or we can simply call it as a row or we
14:44
can simply call as a tuple similarly columns so columns are attributes
14:53
attributes so what we call these attributes these attributes are the
14:58
characteristics of any entity okay the characteristics of any entity we call it as attributes so definitely these
15:06
attributes holds some values holds some values and we have to define the
15:13
attributes with this particular data types okay so every value will be having
15:18
the type which type of data which type of data for example if you want to store
15:24
the student table student table so we need to give the attributes like a student ID
15:31
student name student percentage so let us take only
15:37
these three so if you take the student ID so it is just like one zero one one
15:43
zero two etc etc names some ABC or def
15:49
etc etc and percentage 75.3 85.9
15:55
etcetera etcetera so you can see all these are whole numbers so this should be
16:01
implemented by using integer so we have to represent it as an integer that means
16:07
Sid is an attribute which of course the numbers what is this numbers five it's
16:14
an integer so integer similarly yes name so this is a group of characters we call
16:19
it as a strings we call it as a strings and percentage so these are float values
16:26
real numbers right so this will be represented as float
16:31
right so the data type is a type of a value which is stored in an attribute
16:38
attribute or variable whatever it may be so now what are the different data types
16:44
that are supported in SQL let us
16:50
so hope you got an idea about what is the data type what is the data type right
16:55
so similar to our programming language Concepts so here also we are going to
17:01
use the attributes and definitely while creating the table itself we have to define the attribute and we have to
17:07
declare that attribute with the particular data type corresponding data type right so corresponding data type means
17:13
the type of value which is being stored in the attribute now
17:19
now what are the different data types so
17:24
data types in SQL
17:33
so the first one is
17:46
binary data type okay I will let you know when we will we're going to use this binary
17:53
binary yes numeric
18:04
strings
18:11
date and time binary numeric strings and date and time
18:17
and this binary is used to store the images in a database so similar to the
18:22
values we can also store the images right so in order to store the images the data type is a binary okay and
18:30
coming to the numeric again it is divided into two types one is
18:39
approximate another one is
18:45
exact that means approximate numeric data type exact numeric data approximate
18:52
means again it was divided into real data types and
18:59
flow through data types that means which are having some decimal points okay the attribute which will hold the
19:06
value which consists of some decimal points then those attributes should be declared by using real or a flow so
19:13
what's the difference between real and a float so one difference major difference is the range of values so real will
19:19
support up to one range and Float will support up to one range right so exact
19:25
again coming to this exact the first one is bit either 0 or 1.
19:33
next one tiny in similar to our real and slow there are different data types related
19:40
to this exact which are differentiated with the range of values that can
19:45
support right so timing it small int
19:52
it big int
20:00
decimal right so all these that comes under the exact exact
20:06
numerate right so common integers all are whole numbers but what is the
20:12
difference means the range so bits are nothing but a zero or one tiny N means
20:17
to store very less number of values that means the range is very less and small
20:23
it small end is also to store the normal variable values that means the small
20:28
values which are greater than tiny heat but less than integer which can store more number of values
20:36
decimal right so the main difference among all these things is the range that
20:42
can support and strings again divided into character
20:51
where can text so here's the difference a character
20:57
where care and text here here also the difference is a range of characters but one difference is character means it
21:04
will store the fixed size of variable that means once you fixed the variable size it can't be modified okay but if
21:12
you are using this where care so we can modify the size of the data type also so
21:18
we have to mention the size that means how many characters that particular attribute can hold should be mentioned
21:24
here so that can be modified in the wear cap which cannot be done in the character
21:29
right so for example if you want to store the student name so if you use a character data type it
21:37
can hold a fixed size value so we can't change okay fixer size
21:42
but if you are using some webcam you can mention the size so that we can use only that particular size for example where
21:48
care of 30 so that implies so this particular attribute can hold a maximum of 30
21:57
characters as his student name so if you if the user wants to give the name which
22:05
is greater than 30 that will not be accepted okay so that's the major difference
22:10
between the character and the worker so text is also similar to wire care and here the character means both the alpha
22:17
numeric Alpha numerator okay for example if you want to store some pan number
22:24
per number so we know that a pan number will be having the alphanumeric variables alpha numeric characters okay
22:31
it is a combination of digits and Alpha Alpha so if you want to use a fund as an attribute so you have
22:40
to declare this fund as where can so that
22:45
it will accept both the alphabets and the numerical values right and the last one is a date and a
22:53
time so in order to store the date as well as the time as well as a date and time so here we again it is classified
23:01
into three categories so one is a date another one is a Time
23:07
another one is a date
23:13
so date means it will accept the four digit year four digit a month
23:19
to sorry two digit month and two digit day okay and the time will be
23:26
hours minutes seconds and coming to this date time it
23:32
will accept the four digit year two digit month two digit day
23:38
seconds
23:44
so this is the data date time and date time so these are all the different data
23:50
types available in SQL okay don't get confused this is an SQL so here
23:57
where we are using this SQL the question is where we are using this SQL so this SQL will be used in any one of the dbms
24:06
tool okay so hope you are understood so SQL
24:11
will be used in any one of the dbms tool what is the dbms tool so we say that
24:17
bbms tool means already SQL Server
24:26
MySQL so all these are the different database tools so we are going to use this SQL in one among the dbms tool so
24:36
this dbms tools doesn't support all the data types
24:41
okay so these data types will be different in different dbms tools now
24:47
let me say the common data types which can be used in all the daily messages
24:54
so the common data types are float so if you want to store the
25:01
attribute value as a real number that means a number with a decimal value so use a float so this is accepted in all
25:08
the dbms tools next integer can be used as integer
25:15
number so this is a common data type which can be supported in all the dbms tools
25:23
where can their care is also backer on the characters both are also the common
25:29
data types which are used in all the dbms tools and similarly this date
25:36
time date time so all these are also the common data types which are used in our
25:41
dbms tools any dbms tool so I'll write here
25:47
float integer or a number
25:55
so here also we can restrict the number of digits okay next character
26:04
of science so here we have to mention the size
26:10
date time
26:16
data so these are the common data types which can be supported
26:22
by any dbms tools right but here you can observe the exact
26:27
numeric data type will be having a lot of variations so this is just because of
26:33
size of the variables I mean the size it supports okay similarly the float and real also
26:41
right so hope you understood about the data types I think you got an idea of what is the data type and what are the
26:47
different data types supported in the SQL and what are the common data types which are being supported by all the
26:54
dbms tools the common data types so float integer variable characters date
26:59
time and date time so in our further sessions we are going to use these data types
27:05
so while creating a table we are supposed to you give the attributes and those attributes do should be defined
27:12
with the help of a corresponding data types so there we are going to use these data
27:17
types while creating the table So based upon this one we have to fill the data we have to give the value to
27:23
the attribute so if any value which violates this data type automatically it will not be accepted to the attribute
27:32
right so hope you understood the data type supported in xsql and the common
27:37
data types which are supported by all the dbms tools so
27:44
so create command in SQL so first of all we'll start with the DM ddl command so d
27:52
d l command so ddl comments we have discussed about these things in the
27:57
previous sessions which is nothing but a definition it gives the definition right so one is
28:04
a create drop
28:09
truncate etc etc all these will be the data definition language commands so in that
28:16
we will see the create command so how what is the Syntax for create command so
28:21
in order to use this SQL commands SQL commands we require one dbms tool
28:30
one dbms 2. so we will select MySQL as a dbms tool to execute the
28:40
queries so why because I mean why to select this MySQL means it's a open
28:45
source right so we can also select the oracle or SQL Server
28:53
Etc right so I will choose this MySQL as a dbms tool to execute the SQL queries
29:02
so how to download this MySQL and how to install this MySQL in our system I have
29:08
recorded the session and I will post the link in the description section you just follow that and you can install download
29:14
and install the MySQL and then you can execute these queries now
29:20
so by if you want to execute this SQL command so first of all we have to create the database the first step we
29:28
need to create a database so inside the database we have to create
29:35
tables have to create tables right so all these
29:41
tables will be stored in this particular database okay and this
29:47
dbms tool this MySQL will be having a lot of databases
29:54
and inside this table databases will be having a lot of tables inside the
29:59
databases we'll be having a lot of tables so first we have to create a database and then we have to create the table into this particular database now
30:07
first of all let us see the Syntax for creating the database okay so here uh MySQL we have chosen the
30:15
dbms tool as a MySQL it will comes under the two different variations one is a
30:20
GUI graphical user interface in the command line interface so what's the
30:25
difference what is the difference so here everything all the queries you need not write the queries you can simply use
30:32
a selections okay Mouse selections and mouse moves to execute the queries so
30:37
the query will be automatically generated right but here in the command line you have to write down a query so
30:43
we will use this command line because we have to practice the SQL commands by writing the queries so that's why we'll
30:50
go with the command line I will show everything in the system don't worry about that so first of all let us see
30:56
how to create a database how to create a database so if you log
31:02
into the MySQL command line you will get a SQL prompt you will get SQL prompt and
31:07
here you need to write down the queries so in order to create the database create database
31:17
so the syntax is create
31:22
database followed by
31:28
database mean so whatever the name you you are going to give so that will be
31:34
given here right database name so one database will be created in my in in
31:39
this particular MySQL with a given name with a given name and one thing you just
31:44
remember every query will be terminated with a semicolon so that's the most important thing
31:51
every query so which we have you are going to write
31:56
in this SQL prompt will be terminated
32:05
using semicolon using semicolon so after end
32:11
of the query you have to place the semicolon so if you are not placing the semicolon if you press enter
32:17
automatically the query will be continued with the next line so here the query can be written in a multiple lines
32:23
so the query is not restricted to write in a single line so the query can be written in a multiple lines so unless
32:29
the user gives the semicolon that query will be keep on going with the new lines
32:35
so once you give the semicolon that shows the end of the query so then the
32:40
query will be executed so I will show everything in the command line of MySQL okay so the Syntax for
32:48
creating the database is create a database database name and semicolon so if you want to check how many databases
32:55
and what are the databases available in this particular MySQL the command
33:02
so the next one is displaying
33:08
databases in
33:13
MySQL so that is the command is
33:19
show databases
33:26
show databases so automatically it will give a table of databases
33:33
so it will give a table of databases so once you execute this one it will
33:39
give that this one okay now we have created a database and
33:45
we have seen whether the database has been created in the MySQL or not so if you if the database was created here so
33:51
that will be available in this particular list right so once it was done our next step
33:57
is we have to create a table so in order to create a table that table should be created in a particular
34:03
database so first we have to enter into the database and in that particular database we have to write down the
34:09
tables I mean we have to create the tables and we can execute the queries so how to enter into the particular
34:17
database how to enter into particular database so
34:22
I think I'm sure so it will show the result here with a list of databases so
34:31
entering into database
34:37
sorry entering into the database the query is
34:43
SQL use database name
34:52
use database name so automatically we will be
34:57
entering into the particular database now after this one we'll get a command that database changed we'll get the
35:03
statement simply here the database
35:09
changed so if you've got this statement that implies we are entering into that particular database and we are going to
35:15
use this particular database so in that particular database we are going to write the tables so whatever the tables
35:21
we are creating that will be saved in this particular database okay whatever the database given here
35:27
so once you enter into this particular database now we have to create a table
35:33
so whatever the tables we are going to create everything will be in this particular database name so next step
35:40
how to create a table how to create a table
35:52
creating table
35:58
so as I have said that the query can be written in a multiple rows okay multiple
36:04
lines so SQL the
36:09
and here give the command create table
36:16
give the table name table name and open the branches and in the table
36:22
we know that there will be a columns and rows so we have to give the columns that
36:28
we call it as an attributes so we have to define the attributes which should be available in this particular table with
36:34
the corresponding data types So based upon the value we are storing into the attribute we have to Define that
36:40
particular attribute with the corresponding data types so in the previous session we have seen the different data types right so here see
36:48
if you press enter automatically you will get one more line
36:53
okay and because here we have not given semicolon if you have given semicolon that will be treated as a end of the
36:59
query so if you are not giving any semicolon and press enter automatically it will go to the new line and here you
37:06
can define attribute attribute 1 and corresponding data type
37:14
data type comma and again if you press enter again it will move with the next
37:19
line attribute 2 and data type
37:28
and if you press enter third line so it will be keep on moving unless that
37:34
user enters the semicolon data type okay and so on so on and
37:43
finally if you use a closer parenthesis and you use a semicolon then the table
37:50
will be created with the following attributes a table will be created with the following attribution right
37:58
so hope you have understood this one so I will explain this table creation also in the MySQL command line okay we'll
38:06
create one table with the different attributes and here one more thing so we can create a table without a constraints
38:12
and with constraints so we can give the constraints for this particular attributes okay in order to avoid the redundancy if
38:20
you uh in order to link one table with another table in order to give the
38:25
default values okay so there are many constraints so those constraints should be given for these attributes while
38:32
creating a table so we have not discussed about these constraints so far so in this session I am just showing how
38:39
to create a table without any constraints here we are not giving any constraints this is a simple creation of
38:45
a table okay in the next sessions I will explain you about the constraints what
38:50
are the different constraints we can give for the attributes and then we'll see one more time how to create a table
38:55
with a particular constraints okay I will demonstrate everything with an example right so this is how we
39:04
can create the table in inside the database okay inside the database now
39:10
if you want to look at what are the tables available in the database
39:16
if you want to look at what are the tables available in the database so I'll execute the same query in the MySQL
39:21
command line don't worry so I will show everything in a practical so displaying
39:31
yeah tables in database
39:39
in database so the query is similar to our databases here also we can use
39:45
show tables so short tables
39:51
show tables okay show tables will give a list of tables which are available in
39:58
that particular database in that particular database it will give a complete list okay the queries index
40:05
will be small difference there will be a light difference with the different dbms tools so if you are using some Oracle if
40:12
you are using some Oracle the syntax will be select star from
40:19
tab so this is the syntax to list out all the tables of particular database
40:25
okay the syntax would be a light difference slight difference so we are using a MySQL in this MySQL so
40:34
this is a syntax to create or to list out all the tables available in the database
40:41
so let us see everything the execution of uh each and every syntax that means
40:48
how to create a table how to create a database how to use the database how to show the database table databases and
40:55
how to show the tables in the MySQL command line prompt so let us move on to the system and let us see the execution
41:02
of all these queries so let's move on to the S MySQL Hello friends so just now we have seen
41:10
the create command in SQL right so now we'll see the implementation part so how
41:16
we can execute the query create so in this session we have seen how to create
41:22
a database and how to create a table without constraints okay so after the session from the next session we'll see
41:30
how to create a table with constraint right so let us see first here I'm using
41:37
the dbms tool MySQL to execute the queries right so in the previous
41:42
sessions already have recorded how to download and install the MySQL so I will
41:49
post the link in the description section so you can just follow that and you can install the MySQL so I have chosen this
41:55
MySQL you can use a Oracle SQL Server etc etc so I why I have preferred this
42:01
MySQL means it's a open source so you did not pay anything so it will be available in the internet so I you can
42:07
download and install and you can execute all the credits and MySQL will be coming in two interfaces I mean two different
42:14
interfaces one is a graphical user in the command line so we will execute the queries in the command line because if
42:20
you use the uh user interface graphical user interface so you did not execute
42:26
the query directly by using the most moves and clicks you can directly create a table and you can execute the queries
42:31
but you have if you want to learn the queries SQL queries so it my suggestion
42:38
is work with the command line interface so you will be getting these two icons you can see MySQL workbench and MySQL
42:43
command line so here you will be getting MySQL folder so
42:50
I will show you see MySQL folder in this MySQL you will be finding all these
42:55
options so one is the command line and MySQL workbench so use a MySQL command
43:01
line so I have already taken so it will prompt for the password just give the password so here we'll get
43:08
the prompt okay so the first one if you want to clear the screen use the command
43:14
system CLS so it will clear the state now as we have discussed it just now
43:20
before creating the tables first we have to create a database so if you want to
43:26
create a database in MySQL simply use create a database name
43:33
okay create database database name so I'll give the database
43:39
name as YouTube okay semicolon it will end the query so execute you can get the you will getting
43:46
the comment like a query okay one row affected now if you want to
43:51
uh look at the databases which are available in our MySQL use a command use
43:57
let's say show database databases
44:03
so these are all the databases available in MySQL okay these are all the database
44:09
available in MySQL so here we have created the database YouTube so you can
44:15
find the name here here itself okay and also
44:21
now we have to create a table inside this database so first of all we have to
44:26
select the database in which we want to create a table and then we have to enter into that particular database and then
44:33
there we have to create a table if you want to enter into a particular database which we want to select user command use
44:40
the database name so so you can you will get the statement database changes now
44:46
we are in a YouTube database now whatever the tables we are going to create everything will be saved in this
44:52
particular YouTube so in order to get the details of the tables available in this particular database use the
44:59
command show tables so you can see empty set that means there are no tables till now there are
45:05
no tables right so first of all create a table student okay without any constraints so actually we have to
45:12
create a table with a multiple constraints so we'll see what are the constraints we can see in the next session and again we will see the same
45:18
comment that means how to create a table using those constraints but now in this session we'll see how to create a table
45:26
without the constraints so that syntax is create a table student and you can
45:34
give the query in a multiple lines we can write the query in a multiple lines and here the semicolon is a termination
45:40
of a query and until the user gives the semicolon the query will be continuously
45:45
keeps on moving so just give enter give the attribute so what is the first attribute student ID and give the
45:53
corresponding data type what is the student ID data type that means the value which holds the student ID is an
45:59
integer so declare this one with integer comma the second attribute is the
46:05
student name the student name will be the character so the data type can be where care and
46:10
do the maximum size of the name it should accept so the student name can have only the characters 30 characters
46:17
okay not more than 30 characters and the third one student Branch so which branch
46:22
he or she belongs to the student branch is also a character so where can of some
46:29
20 that length of the branch should be not more than 20 and the last one is a student percentage which is of a real
46:36
number that is a float value so float value and close the parenthesis and use
46:41
a semicolon so that the table will be created so you can see the query okay zero rows affected so a table has been
46:49
created now now use the command show tables to play all the tables of this YouTube
46:55
database so tables same there is a table in a YouTube
47:01
database that is a student so what are the records of a student so
47:06
this is an empty table okay empty table so in order to use in order to list out
47:12
all the records of a student table so you have to use a command select star from table name so it will display all
47:18
the sorry select star from student okay
47:23
table name so it's an empty set because we have not inserted any rows or records inside the table just we have created a
47:30
table now if you want to know about the structure of this particular table which
47:35
we call it as a schema schema of the table right so what are the attributes and what is the data types given for
47:41
those attributes use the command called the description DSC describe describe
47:47
table name so that it will give all the field field names okay all the attribute
47:52
names those types data types and the constraints what are the different constraints given for those attributes
47:59
so actually here we have not given any constraints so null value is by default so everything it will accept an all
48:05
value and the default value is a null value for all the attributes so we can set all these things while creating a
48:13
table okay we can set all the constraints given for the attributes while creating of a table so uh we have
48:21
created a table without any constraints without any constraints we have created a table right so this is how we can create a
48:29
table and a database the create command the data definition language command is
48:35
a create the first ddl command is a create so for that create we have seen the syntax how to create a database and
48:42
how to create that table okay and once again I'm repeating creating a table
48:47
without constraints without giving the constraints but we have to create the table by using this constraints so that
48:53
the redundancy will be avoided so that we'll discuss in the further sessions right so hope you understood this simple
49:00
create command
49:07
insert command Okay so insert command is a DML command data manipulation language okay so this comes under
49:16
DML command so this insert method or insert command
49:23
is used to insert
49:29
data into tables so already we have seen how to create a
49:36
table in the previous session so after creating the table we have to insert the values so here uh while insert while
49:43
creating a table we have we have derived the attributes with the corresponding data types
49:49
so here we have to pass the values for all the attributes or limited attributes
49:57
so we'll see the Syntax for all the three all the things so how can we insert a single Row for all the
50:04
attributes and how can we insert a single row with a limited number of attributes and how can we insert a
50:11
multiple rows okay with more number of attributes with all the attribute values and how can we insert multiple rows with
50:19
a limited number of attributes so first one so let us see the syntax so SQL it's a
50:28
prompt okay after the prompt user command called insert
50:34
into table name
50:40
table name okay and here you need to give
50:48
attribute one attribute
50:54
2 and so on and so on
51:01
values the value for first attitude
51:10
one underscore a value so the value corresponds to this particular attribute
51:15
one similarly attribute 2 value
51:24
and so on so this is a command which we will use
51:29
to insert a single row with with a specific attributes that means see for
51:36
example so we have created a student table in the previous session with the four attributes
51:42
four attributes that is student ID student name
51:48
student branch student percentage so here we need to give the data for
51:57
all the values I mean all the attributes okay see
52:02
so Sid student ID student name
52:10
student branch student percentage so if these are the
52:18
four fields or four attributes we have created so one thing you just remember if the data type
52:25
if the data type decrease of a string is of a string so
52:31
the value should be enclosed in quotations the value should be enclosed in quotations that means if any of the
52:38
attributes is derived with a string data type that means either a character or a worker or a text so that should be
52:45
enclosed in single quotations so that you have to remember so where we have going to Value so we are giving the
52:51
value here itself so so here we have not given any
52:56
constraints while creating a table because we have not discussed about the constraints so far so in the further sessions we will discuss about the
53:02
constraints and again we will see the insert command so we we can give
53:08
a partial values that means a partial attributes values for partial attributes not for all the attributes that means if
53:15
you want to give you can give the values for only these three you can skip this one or you can give the complete values that
53:22
means the values for all the four attributes so this is the syntax
53:27
okay this is a syntax to insert the values for okay a limited attributes not for all
53:34
the attributes so you can also give the all the attribute values okay but if you want to give only a few values for only
53:42
a few attributes you have to mention the attribute names and attribute values
53:47
my feet example I need to give this one so SQL insert
53:57
into student so what we have done we have to mention
54:03
the attribute so S ID yes name I just want to pass only this
54:08
one so values values 1 0 1 and Sunday
54:15
so this is the thing so we have to enclose in
54:22
quotation single condition so after executing this one we'll get 1 0 1 Sunday and all these values will be null
54:30
by default it will be a null okay by default it will be a null without using any constraint okay without using any
54:36
constraint the default value will be null so we have not passed any value for these two attributes branch and
54:42
percentage so there will be pretty so if you want to fill all the four values so you need not mention this one
54:50
we need not mention this one inside into table name values give the value for all
54:56
the attributes in the table that means insert into student insert into student
55:02
okay values values so 1 0 2
55:11
samsara some CAC 95
55:18
so here you can see the table consists of four attributes and we are giving all the four values here
55:25
okay we will not get any error so if you are want to give only the three values
55:30
with this syntax this will return an error because the student is having a
55:35
four columns or four attributes but we are passing only the three values so if you don't want to get this type of
55:42
error you have to mention the attribute names here so for which we are passing the values okay if you are not
55:48
specifying any attribute names here so you need to pass the value for all the
55:53
attributes so 95 so this will enter the values 1 0 2
56:00
sarathi and Branch CSE and percentage 95. so this is the syntax to insert the
56:09
elements all the elements into your table okay all the values for all the
56:15
attributes and what is the Syntax for inserting a multiple rows multiple rows at a time so
56:22
this will insert only one row at a time so how to insert a multiple rows at data so simply you can see this one and
56:30
again without executing the query without terminating the query use a comma and
56:36
again give the second row add values attribute one value
56:46
attribute to value thanks on okay and comma use the third
56:54
one so that implies the two rows will be inserted into the table so let us see
57:00
instead of terminating this query comma so let us change this one one zero
57:06
three
57:11
comma again 1 0 4 hurry
57:18
ECE 96 per month 105
57:26
Suri Tripoli 97 come cork column that implies we are
57:36
inserting three rows into a table with a one insert command okay we are passing
57:42
three we are trying to insert three rows at a time so all the three rows will be inserted into this table
57:50
okay only one thing if you want to give the values for all the attributes you need
57:55
not mention the attribute names here and if you want to insert the values for a specific attributes you have to mention
58:02
the attribute names for which we are giving the values so if it is a single
58:07
row insertion or a multiple rows insertion whatever it may be right so hope you understood this insert
58:14
comment so don't worry so I'll execute each and every concept each and every uh
58:19
case that means how to insert a specific attributes on the how to insert the
58:24
values for all the attributes single row insertion multiple rows insertion everything I'll execute in the MySQL so
58:31
let's stop here so let us move on to the MySQL
58:37
Hello friends so just now we have seen the Syntax for inserting the data into a
58:42
table so now we'll see the implementation of that insert command so first let us open
58:49
the MySQL in a command line so give the password
58:54
so first let us clear the screen system CLS now let us use the database
59:02
YouTube because in the previous session we have already created a database let us check for that
59:09
databases and here you can see there is a database called YouTube so
59:14
let us use that YouTube and enter into that particular YouTube database so use YouTube now let us check with the tables
59:22
which are available in that particular thing so show tables so student table is there so now we'll
59:30
check whether the data is available in the student or not so here the syntax is select a star from
59:38
student so that it will display all the records so it is an empty set but that means that's an empty table that means
59:45
there are no records available in the student table now we will insert this data into the student so before in
59:52
session we have to know what are the attributes that are available in that popular student table so in order to
59:57
know the complete structure of that student table we have to go with the desc describe student
1:00:05
so there are four attributes one is a student ID which is of integer a student name which is of where care student
1:00:11
branch which is of where care and student percentage which is a float and here we have not given any constraint
1:00:18
any constraint okay so we'll discuss about these constraints after this one because uh in the next session after
1:00:24
completion of the constraints we will check all the constraints by using this
1:00:30
insertion command so before that we have to know about the syntax of insertion so
1:00:35
let us insert the data into this particular table right so for that syntax insert
1:00:41
into table name that is a student okay and give the attribute names so if
1:00:47
you want to give the values for only a limited attributes you have to give the attribute names so if I want to give
1:00:55
only the three values that means the student ID okay STD ID comma STD name
1:01:03
comma STD Branch okay I just want to enter the
1:01:09
values for these three attributes only so values
1:01:15
give the values here 101 student ID student name so if it is a their care or
1:01:21
a character or a text data type the data should be given in quotations itself so
1:01:26
give the quotations so I'll give Sandeep comma and we have to give the branch so
1:01:33
I'll give the branch CSE right so you can see query okay one row
1:01:39
affected that means already the data has been inserted into that particular table so let us check so in order to get the
1:01:45
details go with the query select star from table name so you'll get one record
1:01:51
so we have not given any value for the student percentage so by default it will be null because the default is null we
1:01:58
have not given any constraint if you have given any constraint that particular value will be done okay so
1:02:04
we'll discuss about this constraints in the next session so if you are not giving any value here it will take a
1:02:09
default value right next if you want to give the complete values
1:02:17
if you want to give the complete value that means all the four attributes the values for all the four attributes you
1:02:22
need not give the attribute names here insert into student and here you need
1:02:29
not give the attribute names directly student into student values so here you
1:02:34
need to give 1 0 2 sarathi
1:02:40
CSE so it's a back at CSC some 99
1:02:47
so now see query okay one row affected also we got
1:02:53
the data has been inserted into table we can select here student see two tables
1:02:58
have been selected and if you are trying to give this one without one attribute
1:03:04
see here I'll give one zero three
1:03:12
ramu easy and I just want I don't want to
1:03:17
give the percentage so you will get an error because here we are not giving any column names
1:03:24
here we are not giving any column names so if you are not giving any columns names you have to pass the values for
1:03:30
all the columns here we are having four columns so if you are not specifying the column names so we have to specify all
1:03:38
the four values for these four columns so if you want to give only the three values here you have to mention three
1:03:44
columns in this way so here you can see we have given only three columns
1:03:51
right then if how what is the procedure to
1:03:57
so select sorry inset into student
1:04:05
okay values here pass on the values so 1 0 3
1:04:13
ramu ECE
1:04:19
96 okay and comma give one more value so 1 0 4
1:04:28
some Suresh Tripoli
1:04:35
93. [Music]
1:04:40
right if you want to give a one more value use a comma
1:04:46
give one more value all right one zero five
1:04:53
comma hurry make
1:04:59
92 use a semicolon execute so you can observe here query okay
1:05:06
three rows has been affected that means all the three rows have been
1:05:11
inserted so you can select here so select star from student
1:05:17
so you can see one zero three one zero one zero five so all the three columns has been I mean all the three rows has
1:05:24
been inserted so if you want to give this single row if you want to insert the single row so this is a syntax and
1:05:30
two two types so that means if you want to give a same value or if you want to give I mean all the values are if you
1:05:37
want to specify a limited number of attributes values for limited number of attributes and if you want to give a
1:05:43
multiple rows simply use the cam use the command insert and give the details of
1:05:49
complete row you need to pull right in one parenthesis so you can give a multiple values in the same
1:05:56
this statement in the same query so if you want to give the multiple values for
1:06:02
The Limited attributes here you need to specify the attribute so this is how we can insert the values
1:06:09
into a table right so hope you understood this insert
1:06:15
command so insert command is used to insert the data into the table so for
1:06:20
that we have to know what are the attributes and what are the data types for the corresponding
1:06:26
attributes and if it is if the attribute data type is is character or where care
1:06:33
or text all right if it is a string data type simply we have to specify it in a
1:06:39
quotations so just remember these things right so hope you understood constraints
1:06:45
our topic is
1:06:51
constraints so what are these constraints right so generally these constraints are given for the attributes
1:06:58
while creating a table right so constraints are nothing but some rules
1:07:04
rules while creating a table we have to give some rules for the attributes okay
1:07:09
while inserting the data while accepting the data so what are those constraints the the common constraints the common
1:07:16
constraints so let us discuss about the primary key one is the primary key
1:07:25
not null
1:07:33
check default
1:07:42
unit
1:07:48
foreignty so these are the common constraints we are going to use while
1:07:54
creating a table so previously we have seen the command for creating a table and we have seen the command for
1:07:59
inserting the values into a table but there we have not given any constraint for the attributes but here we can give
1:08:07
a constraint so let me explain you about the theoretical part that means what is this constraint if you give this
1:08:13
constraint for the attribute what happens I'll explain here and after this we'll move on to the SQL command line
1:08:19
and I will execute each and every constant okay I will demonstrate each and every constant by writing the query
1:08:25
that means we'll create a table with each and every constraint I will explain everything okay so first one is a
1:08:31
primary key if if we have given a primary key
1:08:36
attribute I mean any attribute as a primary key it will be having the two characteristics one is by default not
1:08:44
null that means the user should not leave it as a blank so while inserting the data if the user
1:08:51
left the blank that means if user doesn't give any value for that particular attribute so by default the
1:08:59
attribute value will be null so there is a one more constant not null if you give this not null it should not
1:09:05
be accepted okay it should not be left blank so compulsory the value should be given by the user while inserting the
1:09:11
data so here if you give a constraint as a primary key for any attribute so by default the not null
1:09:19
will be applied and also
1:09:26
no duplicates so duplicates were also not not allowed okay if it is a primary key if any
1:09:33
attribute is given as a primary key duplicate should not be allowed so let me explain here so create
1:09:40
table student
1:09:46
okay so we can give a multiple multiple line we can write the query in a multiple line unless you give the
1:09:51
semicolon because the semicolon is the termination of the query so here I'll write some s i d
1:09:58
some integer comma some s name
1:10:04
somewhere care which is of account of some 30
1:10:09
okay and a branch of a wire cap
1:10:15
of 20 percentage as a float
1:10:21
and here you need to write the primary key that means a constraint after declaring all the attributes then we can
1:10:27
write the constant a primary key so which one we want to give which attribute we need to give the primary
1:10:33
key in a parenthesis so S ID so this will create a table student with
1:10:40
a four attributes Sid name branch and percentage and this Sid will be treated
1:10:46
as a primary key which should not be kept null and which should not accept the duplicates
1:10:51
so I'll execute this query and I will show you okay so after creating a table I will execute that one and also I will
1:10:58
try to insert the values with the duplicates as well as with the null values I will show everything okay all
1:11:05
the cases we'll see so this is how we can give the constraint primary key and
1:11:11
coming to the not null so if you specify any attribute with an ordinal constraint
1:11:16
that implies so it should not be kept blank so while inserting the data we
1:11:22
have to give a value for that particular attribute and while creating a table here so for which attribute we want to
1:11:29
give an opt null that should be given after the after declaring the variable so here Sid integer SMS Branch worker
1:11:37
percentage float here I give a not null
1:11:42
here I give an ordinal so if I give an ordinal that implies this normal constraint is given for the percentage
1:11:48
so percentage should not be kept kept blank for all the students any student
1:11:54
okay we have to give a value for the percentage for the student it should not be null it should not be the default
1:12:01
value is not null okay we have to pass the value we have to pass the value we have to give the value we have to insert
1:12:07
the value right so that is the normal constraint and check then name itself indicates check means
1:12:14
before taking the data from the insert command before inserting the data from the insert command it will
1:12:21
check the condition which we are giving and if the condition is true then only it will be accepted the data will be
1:12:27
accepted into the table for example let us take one more
1:12:33
attribute that is the age H which is of a integer
1:12:38
okay after that we need to write the conditions so constraint so it is not condition constraints so I'll write the
1:12:45
constraint a primary key which is of a S ID and then we can use a check constraint
1:12:52
check h greater than 18.
1:13:00
so if I execute this one so it will create a table with the attributes student ID student name
1:13:07
Branch percentage age all the five attributes will be
1:13:12
created okay and in that the primary key is set for the Sid and not null is set
1:13:20
for the percentage and check age greater than 18. so while inserting the data if
1:13:27
you give the value for the h Which is less than 18 it will not be accepted
1:13:32
okay while inserting the data so we have to insert the age value which is a
1:13:39
greater than 18 then only it will be accepted and that that data only will be inserted into the table if the age is
1:13:46
less than 18 the data will not be inserted into the table that means before insertion it will check this
1:13:51
condition okay so this is also we'll execute the query and I will explain everything
1:13:57
so that is a check and the next one is a default default so we can give we can set the
1:14:04
default value okay we can set the default value so let us take the
1:14:11
I will remove the branch okay I will take the college
1:14:19
where can of 20 and here I can set the default College Okay so
1:14:27
here only we can write the default quotations let us write some college
1:14:32
name here right while declaring the attribute
1:14:37
itself we can write the default value so default value will be this one and
1:14:43
unless I mean if you are not giving any value for the college by default it will
1:14:48
be taking as a BC if you are giving any value for the college that value will be taken as a
1:14:54
consideration that will be I mean this BAC will be replaced with the given value
1:14:59
okay while insertion we have to pass all the values or we can give
1:15:06
I mean required values okay if you want to insert a required values required attribute values for the required
1:15:12
attributes so we need to give specify the attribute names in such case if you are not giving any value for the college
1:15:19
by default it will be considered as is default value okay whatever the value we are giving as a default that will be
1:15:25
taken all right so if you give any value for the college this default value will be
1:15:32
replaced with a given value right so I'll explain you I will demonstrate by executing the query
1:15:38
and the next one is the unit which is similar to our primary key which is similar to our primary key so the same
1:15:45
thing same thing okay but the only difference is
1:15:53
it can accept the null value but it doesn't accept the duplicates it doesn't accept the duplicates but it can accept
1:15:59
the null value right for example we have seen the unit unique as a contact okay
1:16:04
or an email so it is not mandatory that every student should have the contact number or every student can have the
1:16:11
email so if we can left blank okay we can left blank or you can simply yes we
1:16:17
can left blank right so such cases we can give the unit so here instead of
1:16:22
giving the age okay here itself I will give uh contact
1:16:31
as integer followed by the constraint unit and the
1:16:39
default that is the primary key of yes ID
1:16:45
so automatically the contact number will be unit that means if you give the same
1:16:50
contact number for two students it will not be accepted because it doesn't accept the duplicates it doesn't accept
1:16:56
the duplicates but you can left blank here okay that's the main difference between
1:17:02
the unique key and the primary key so primary key doesn't accept the null values unikey can accept the null values
1:17:08
but it doesn't support the duplicates similarly primary key is also doesn't support the duplicates so that is a
1:17:15
unique constraint so for this also we'll execute the query and coming to the foreign key
1:17:21
we have discussed about this foreign key the mainly foreign key is used to relate two different tables okay we relate to
1:17:28
different tables for example so I am having a two tables student
1:17:39
with Sid yes name and a cosine cosine and there is a one
1:17:47
more table called course so which is of C ID course ID and course
1:17:53
name and you know that course ID is the primary key yes ID is a primary key okay
1:17:59
and course ID in the student will be the foreign key so this is a primary key
1:18:05
this is a primary key and one the primary key of one table will be the foreign
1:18:12
so this will relate the two tables this will relate the two tables so for this
1:18:18
one let us create table
1:18:24
course CID integer
1:18:32
C name where can of
1:18:39
30. and
1:18:44
primary key of CID so there will be some space Prime
1:18:50
primary key there is a some space so
1:18:58
primary key and this will create a course ID and then while creating the
1:19:04
primary element while creating the student ID so create table student
1:19:11
so how many fields we have to create three Fields so S ID with the integer
1:19:19
yes name as a where can of some 30
1:19:26
and then CID as a integer
1:19:31
primary key what's the primary key for the student
1:19:37
table Sid so S ID and we have to give the foreign foreign key so here we we
1:19:42
have to write foreign key of CID
1:19:49
foreign key of CID which references the primary key of course CA so here you
1:19:56
need to write references
1:20:03
COS of CID course official so
1:20:09
the course of CID that means C course ID will be the primary key of course table which references the foreign key in this
1:20:16
student table so this is the syntax to create the foreign key while creating a
1:20:21
table right so this is how we will create a foreign key for two different
1:20:28
tables and so I'm not I I will execute the same thing in the SQL so I will show
1:20:34
you the queries I will show you that I will demonstrate each and every constraint so how the primary key can be
1:20:40
set as a constraint and how the not null check default unique in the foreign key
1:20:45
I will execute each and every query in the SQL so let's move on to the SQL prompt
1:20:52
Hello friends so just now we have seen the Syntax for the common constraints
1:20:57
which can be applied while creating a table in MySQL now let us see the
1:21:03
implementation part of each and every constraint so first let me open the command line MySQL
1:21:09
so I will give the password so just clear the screen
1:21:15
now let us enter into the database so let us check the databases which are available in this MySQL so we have
1:21:22
created one database that is a YouTube so let's let us insert I mean enter into
1:21:28
that particular database so use YouTube sorry
1:21:33
use YouTube now we are in the YouTube now let us
1:21:40
check what are the tables available here so we have created a student table with a four Fields like a student ID student
1:21:47
name branch and percentage so we have not given any type of constraints here
1:21:53
okay so we will delete this table and we'll create one more table the same
1:22:00
student table with the same Fields but by giving the constraints so let us write on the constraints first
1:22:07
let me write down the constraint first so the first one is a not null
1:22:13
so if you give this constraint for any attribute so then null value will not be
1:22:19
accepted that means the user have to give the value for that particular attribute so the user need is not I mean
1:22:25
is not able to live that attribute blank and the next one
1:22:31
is the primary key primary key means it will avoid the
1:22:37
redundancy so the duplicates will not be allowed and by default uh it should not be kept as a null okay
1:22:45
by default it should not be kept as enough and then check so
1:22:50
while giving the value we can check some condition and if that condition is true then only the value will be accepted so
1:22:57
that will we'll discuss uh by executing the query right so then default so we
1:23:03
can set some default value so if the user doesn't give the value for that
1:23:09
particular attribute this particular default value will be taken and then unique
1:23:15
unique so this is also similar to our primary key but only one difference is it will avoid the redundancy similar to
1:23:22
our primary key but what the difference is it can have the null okay the the
1:23:27
value of this particular attribute which we which we have given as a unit can accept the null value so primary key it
1:23:34
doesn't accept the null value and then foreign key
1:23:39
foreign key that means to relate one table with another table okay so these are the common constraints
1:23:46
we have seen just now so now we'll create a table by following each and every constraint so let us check with
1:23:52
the each and every constraint now so first let us check with the primary key let us check with the
1:23:58
primary key okay so let us drop this one so drop table student so that the student table
1:24:05
will be dropped so now you can observe short tables so it will return empty set
1:24:10
because the complete student table has been removed from the YouTube database right now
1:24:18
let us create a table okay so we know the Syntax for creation create table
1:24:25
table name and give the attributes so give the attributes the first one is
1:24:30
a student ID which will be of
1:24:36
number okay
1:24:42
then let us take an integer by default
1:24:48
next second parameter s name okay student name which is of
1:24:55
where cat of some 30 characters it will accept next branch
1:25:03
which is of a wire cap again it will accept some 20 characters
1:25:08
percentage which is of a float value right after that we have to give the
1:25:13
constraints now I will give the constraint primary key primary key off here we need
1:25:20
to give the primary key which will which we have to give the primary key so I will give Sid as a primary key that
1:25:27
implies if you give the same Sid for the two student details it will not be accepted
1:25:32
and if you left this Sid while inserting inserting the values it will not be
1:25:38
accepted because the characteristics of the primary keys by default it should be not null
1:25:45
and also it should not be repeated so just we are giving the primary Keynote okay
1:25:53
so query okay zero rows affected that means we have created a table now you can see short tables
1:25:59
so there is a student table you can observe describe student who will give the structure of a student table see
1:26:05
student ID integer no null is no so that means null is I mean we can't left this
1:26:12
Sid because of our primary key because of a primary key okay now we'll
1:26:18
insert some values and we'll check so insert into table name values
1:26:26
and here we need to give all the details okay otherwise if you want to give some
1:26:32
particular attributes not all the four you have to mention the attribute names so here I am giving all the values so 1
1:26:39
0 1 and S name Sandeep and branch
1:26:45
CSE and percentage 98.
1:26:52
right see one row affected you can select you can check with the student
1:26:57
database say one row is there now insert into
1:27:04
student values again I'll give one or one one or one
1:27:12
some other name sarathi CSC
1:27:17
95. so we should get an error because here
1:27:22
the S ID is a primary key it should not accept the
1:27:28
redundancy that means the duplicate values here the first row the student ID is one or one and the second row we are
1:27:34
just inserting the second row and we are giving the same one zero one as a student ID
1:27:40
so if you execute duplicate entry one or one for key student dot primary that
1:27:46
means the primary key we are given we are we have given Sid as a primary key so it should not accept the duplicate
1:27:53
values for that particular attribute okay it should have only the unique values so while insertion itself we are
1:28:01
getting an error otherwise so let us insert into student and give the
1:28:08
attribute names like Sid s name Branch values
1:28:16
so 1 0 2 samsarathi and the branch is some Easy
1:28:25
execute see query okay one row affected so we have given only the three attributes so in the previous class we have seen
1:28:31
the Syntax for the insert command so there you can insert all the values or
1:28:37
only a few attributes values for a few attributes if you want to give a values for few attributes that attributes name
1:28:44
should be mentioned here so here I have mentioned only three attributes and I am giving only the three values so we have
1:28:50
skipped the attribute percentage so by default it will be null let us check so
1:28:56
select star from student C by default it will be null now again
1:29:03
I'll execute the same thing but here now I just want to
1:29:10
skip the Sid okay so I just want to skip the Sid s name branch and I'll give the percentage
1:29:18
percentage so I'll give the take the
1:29:23
so first I'll give the Branch name branch and percentage Let It
1:29:29
Be 99 execute see field Sid doesn't have a default value
1:29:36
it doesn't have the default value you can observe here so not no null no null
1:29:42
so it should not be kept null okay it should not be kept null
1:29:47
because Sid is declared as a primary key so primary key characteristics are one
1:29:54
is redundancy it should not accept the duplicate value second one is it should not be kept null so for every row there
1:30:03
should be Sid okay not null not null right so hope you understood the
1:30:08
constraint primary key constraint primary key
1:30:14
I will change the order here right primary key so accept the primary key we
1:30:20
can give a null for all the things see once again insert
1:30:25
I will tell you so I I'll just insert the Sid okay I am not giving any value for all
1:30:32
the remaining attributes so one zero three execute you can observe carry query okay
1:30:38
one row affected and you can see select the star from student so that the third
1:30:44
one is one zero three and all the remaining values kept null because we didn't give any constraint for these
1:30:50
particular uh attributes see Sid integer where care where care percentage and
1:30:57
primary key we have not given any constraint as in not tunnel not null so it will accept a null value
1:31:04
right so this is the primary key now let us drop the table
1:31:10
and we'll check with the not enough okay not now so
1:31:16
drop table student so actually we need not drop the table we can use the alter
1:31:22
command to give the constraints but there will be a little bit confusion so I'll First Let Me Explain you about all
1:31:29
the constraints and then we'll see the alter commands okay so we can add the constraints by using the altar so that
1:31:35
we'll discuss in the next one right for better understanding and I'm just a prop I'm deleting the table and I'm I'm
1:31:42
creating a table with the multiple constraints so drop to table student so it was dropped now you can observe short
1:31:48
tables so there will be no tables empty set now again we'll create a table create
1:31:55
table student so first one Sid which is of a integer
1:32:03
yes name which is of a where Cap of a 30 maximum and branch
1:32:12
okay a branch and where care
1:32:19
of a 20 percentage which is of
1:32:25
float and I I just want to give the percentage I don't want to keep a null
1:32:31
value for this percentage so give the constraint here itself so not null that implies this percentage value should be
1:32:38
given by the user so it should not be make blank while inserting the values so
1:32:45
I'll give the primary key primary key will be the S ID right so you can observe
1:32:54
so we have we are creating the table student with a sid integer s name wear
1:32:59
care Branch worker percentage float and we are giving a primary key as Sid so it
1:33:05
will be not kept blank and percentage I am giving a one more constraint not null for the attribute percentage so the user
1:33:12
have to give the percentage okay how to give the percentage the user
1:33:18
is not able to left blank for this percentage while inserting the values so we'll check so now you can observe query
1:33:26
okay zero rows affected so if you apply the show tables it will display the
1:33:32
student you can see describe student C
1:33:37
float null value no null it should not keep blank okay and we can leave the s
1:33:46
name and Branch but we should not leave the Sid and percentage let us check so
1:33:51
insert into student okay values
1:33:58
let us take 1.01 Sandeep
1:34:06
CSE and 98
1:34:13
so query okay okay now select star from
1:34:18
student you can get a company details now let us check insert
1:34:24
into student Give the names Sid
1:34:30
s name values 102 and s name sarathi
1:34:39
so it will not be accepted because the field percentage doesn't have a default value so you can observe here so we are
1:34:45
giving a constraint not null for a percentage that implies the user have to
1:34:51
give a value it should not be kept blank for example if you modify the same query
1:34:56
so we are giving only the Sid as well as percentage so it will be
1:35:03
accepted because the remaining two that means the name and Branch we are not
1:35:09
giving any constraint so it can be left blank so 97 see query okay
1:35:16
select start from student you can see and null will be given for s name and Branch because we are not
1:35:22
giving any constraint for s name in a branch we are giving constant for percentage as a not null and primary key
1:35:29
as a Sid so these two Fields should not be kept blank we have to pass the value we
1:35:36
have to give the value for these two Fields because of the constraints right
1:35:41
so hope you understood this not null not null and the next one check so
1:35:48
before inserting the value we can check the value and if the condition is okay
1:35:54
then we can add the value so let us check drop a table
1:35:59
student and just deleting the table you can see show tables there were no tables
1:36:04
empty set so I'll create a table student
1:36:09
with the different attributes Sid with the integer yes name with the where care of
1:36:17
authority branch as a bearcat
1:36:24
of 20 h integer
1:36:32
okay percentage or leave the percentage let
1:36:37
us stop here so primary key of Sid
1:36:43
and then check age greater than 18.
1:36:50
check age greater than 18 and so so if you are passing the value for H
1:36:57
the if the age is less than 17 the row will not be the value will not be accepted that means the row will not be
1:37:03
inserted so let us check
1:37:20
create a table student Sid
1:37:26
integer s name where care of 20 characters
1:37:34
branch of where care of 30 characters age of integer
1:37:43
primary sorry primary key offer Sid
1:37:50
check H greater than 18.
1:37:56
right so now we have given this particular
1:38:01
check constraint so while inserting the data into this particular student details the if the age is less than 18
1:38:10
if the user gives the value of age as less than 18 the data will not be get
1:38:15
inserted into the table let us check see describe student so it will give the complete
1:38:22
structure of the student now let us check insert
1:38:27
into student values so 1 0 1
1:38:33
Sandeep what's the next one branch so CSC here
1:38:39
what's the age so let it be 20. so one draw if I affect it so let us check the details select star from
1:38:48
student so you'll get the details now we'll try to insert the data
1:38:53
okay so whose age is less than 18 so 102
1:38:58
so let us take some sarathi and ECE
1:39:04
and I'll give some 16. you can check so the data will not be
1:39:10
get inserted into the table because we have given this check constraint and we are
1:39:15
checking whether age is greater than 18 or not so if the condition is false automatically the data will not be get
1:39:21
inserted so you can observe here here we are giving the age as 16 so we got an
1:39:26
error check a constraint student check is violated so if you update this value
1:39:33
as 19 it will be accepted so you can select you can see select
1:39:39
star from student you'll get the details so like that we can also give the
1:39:45
constraint check we can give the constraint check
1:39:50
okay so we have to give the some condition based upon that condition if
1:39:56
the condition is true then only the value will be inserted into the table
1:40:02
there's a check constraint Now default constraint so I will drop the table once again top
1:40:09
table student so system CLS
1:40:17
so here the default constraint means we need not pass if the user doesn't doesn't give the value to the attribute
1:40:24
the default value will be taken as a consideration so we can set the default
1:40:29
value okay we can set the default value for any attribute so let us create a table
1:40:35
now so now we'll create the table by giving
1:40:43
the default constraint so create tables student and take the Sid as an integer
1:40:51
and S name as a where care of 30 and Branch with aware care of 20
1:41:00
and let us take one more uh field that is a college which is also a where care
1:41:06
of 20 and let us set the default so default
1:41:12
give the college name I'll give the college name as a BEC so if if the value is not given by the user to the College
1:41:18
field so by default the college will be the Bec so let us set the primary key as
1:41:25
S ID you can check the query is okay so 0 is
1:41:31
affected so let us check select solve show
1:41:36
tables so student so describe student so it will give the structure of a student see
1:41:43
the default value is a b c okay now let us insert the data so
1:41:49
insert into student values
1:41:55
values so here we need to pass all the values here okay you here you
1:42:01
are not specifying the attribute so we need to pass the values for all the feeds so let us take 1 0 1
1:42:10
Sandeep CSC and college some gec
1:42:17
so one row is affected okay one row is affected that means we we have to pass a value here and if see select star from
1:42:27
student one row is inserted now let us take insert into student
1:42:35
right here if you want to pass Sid s name and Branch I am not giving any value for
1:42:43
the college okay any value for the college so values so 102 s name sarathi
1:42:52
and Branch easy so we are not passing any value for the college so still
1:42:59
it was executed so previously okay previously if you give this one the
1:43:07
default value for the college will be null but here while creating a table we are giving a constraint for the college
1:43:13
attribute that is a default BC that means if you if any value is not passed here so automatically the default value
1:43:20
for the college will be filled with be easy you can observe here see
1:43:27
we have not given any any college name for one zero two roll number one zero two but the college name is BC
1:43:35
okay so if you are not mentioning here if you are not using any attributes here you you are supposed to pass the values
1:43:42
see for example let us take here insert into student so
1:43:50
I am not giving any value here I am passing only the three parameters so
1:43:55
I'll give some ramu and one zero three so it will give an error because the
1:44:01
column count doesn't match value at count at Row one so because four attributes are there here we are giving
1:44:06
only the three attribute values so we have to pass the attribute I mean value
1:44:11
for the attribute College also even though it was given a default BGC so here we can skip that one so if you
1:44:19
want to give only the values for a few attributes then you have to follow the syntax itself so automatically the the
1:44:25
default value given for this particular attribute will be set as a BC so you should not use this one okay for
1:44:33
the default so if you are using this one you are supposed to give all values for all the
1:44:39
attributes even though there is a default value right so hope you understood this one
1:44:45
so this is all about our default and then the unique unique is similar to our
1:44:50
primary key but but the it can be accepted in null value it
1:44:57
can be accepted in null value so let us check so let us drop the student detail so drop a table student so yes
1:45:06
system CLS so show tables you can observe the empty set so create table
1:45:14
student so with the Sid integer s name where care of 30 and some
1:45:24
percentage right uh sorry let us take some roll number
1:45:32
okay roll number of integer which is of a unique let us give the constraint unit
1:45:38
so roll number will also be unique but or else we can give the contact number
1:45:44
why don't we give the contact contact which is of a integer let us take the
1:45:51
integer itself and give the unit so it's not a mandatory that every student will
1:45:56
be having a contact number right so unique and let us check primary
1:46:03
key with a S ID execute so query okay zero rows affected
1:46:09
so you can use show tables to check the details so desc student so which is a
1:46:17
student okay you can see unit key is a primary and this is a unique key now let
1:46:23
us try to insert the values insert into a student let us T take all the values student
1:46:30
values so 1 0 1 so Sandeep and the third one is a contact number yes the contact
1:46:37
number will be some one two three four five six seven so query okay so you can check select
1:46:44
star from student so we got this one contact yes so let us
1:46:50
take one more time so insert into a student values and if
1:46:56
if you are not able to give only the yes name if you just want to give the s name
1:47:02
so if you don't want to give the s name I mean Sid and the contact number
1:47:08
definitely will get an error because Sid is a primary key it should be not null
1:47:14
it should be B naught none so let us take here yes ID
1:47:20
and s name or or let us check with the multiple values so 102 and let us take
1:47:28
this one sarathi and what is the contact number which we have given one two three four
1:47:34
five six and seven see so it will also be not not accepted
1:47:39
because the contact number is a duplicate value that means there is a one more contact number here okay so
1:47:47
here the contact is a unique the contact attribute is a unique so it it should also not accept the duplicate values
1:47:55
so that's why it was not accepted and one more thing rather than that it can accept the null value
1:48:01
it can accept the null value student uh Sid yes name so with user 102
1:48:10
and sarathi so OneDrive affected so you can see so select a star from student
1:48:15
so it can be it can be null so that's the only difference between the primary key and the unit key a primary key and
1:48:23
unique key okay so uh that means a primary key and
1:48:30
unique key both are equal so it will it doesn't accept the duplicate values but
1:48:36
apart from the primary key unique key will accept the null value that's the only difference okay so this
1:48:43
is a syntax how we can give the unique constraint
1:48:49
for any attribute right so hope you understood this one right the next one
1:48:55
a foreign key foreign
1:49:02
key we know that the foreign key means the primary key of one table will be the
1:49:07
foreign key of another table let us take the two tables so I will show you the
1:49:12
syntax how we can give the foreign key okay with the help of a foreign key
1:49:18
itself we are going to relate the tables
1:49:23
right so let us create this one
1:49:30
so student table will create the student table with Sid yes name
1:49:37
branch and course ID and also
1:49:45
course table with course ID and course name
1:49:51
and duration right so you can observe here course ID is a primary key in course table which
1:49:58
is a foreign key in a student table which is a foreign key in student table so let us create that one let us create
1:50:06
this one so create so let us clear the screen system
1:50:12
CLS create table
1:50:18
let us create the course table first course CID of a integer
1:50:25
and C name of where care of 30
1:50:31
right only two two attributes we are giving only two attributes and let us give the primary constraint primary key
1:50:40
as CID so query okay you can check show tables
1:50:47
so we are having a course table and a student table okay so let us drop here the student
1:50:52
table and let us create one more student table
1:50:58
now let us check show tables will give only the course table now we'll
1:51:04
create a student table with a course ID as a foreign key so create
1:51:11
table student okay what are the fields student ID student name branch and CID
1:51:16
student Sid with the integer
1:51:23
s name with the where care of 30 characters
1:51:28
Branch with a wear cap of 20 characters and CID with the integer
1:51:37
integer right so also we have to give the primary key so primary key is of Sid
1:51:45
and again we need to give the foreign key what's the foreign key
1:51:50
foreign key of the foreign key which is a foreign key
1:51:55
here CID is a foreign key which references
1:52:00
which references the course of CID
1:52:06
so the foreign key is a Cid in this table which references to the primary key CID
1:52:13
of course table right so this is the syntax oh sorry references okay
1:52:20
yes is uh we have to give the references not a reference right sorry so let us
1:52:27
click create a table student Sid integer
1:52:34
yes name where care of for 30.
1:52:39
Branch worker offer 20
1:52:44
CID of integer primary key of Sid
1:52:53
foreign key offer CID references so here we have not given yes
1:53:01
okay so references CID of course that means which is of a
1:53:08
primary key so you can see query okay jio cross affected now you can see
1:53:13
show tables so two tables course and student so you
1:53:21
can observe describe a student you can see
1:53:26
primary key and mul means nothing but it's a foreign
1:53:32
okay it's a foreign key right so describe course so here also we'll get the structure which is a
1:53:38
primary key this is a primary key right so this is how we can create a
1:53:44
foreign key okay so once again I'm explaining so this is the only creation of a foreign key and a primary key
1:53:50
constraints I am not inserting any values so we'll we'll discuss about the foreign key while executing the queries
1:53:57
relating with the different tables okay so but this is a syntax how we can give
1:54:03
the foreign key for any uh table okay any any attribute for any
1:54:08
attribute right so hope you understood this uh constraints so alter command in the
1:54:16
previous session we have seen a few comments so now we'll see alter command
1:54:22
in SQL so what we can do with this alter so mainly this alter command is used to
1:54:30
change user to change the schema of a database
1:54:36
schema of database right or a structure of a database that includes so one we
1:54:43
can add any column new column any new column next we can delete
1:54:51
delete an existing column or if you want to modify
1:54:57
the data type data type okay of existing
1:55:04
column and if you want to
1:55:11
set constraints constraints like not null
1:55:20
primary key Unity
1:55:28
Etc right so we can set the constraints also right so on
1:55:35
we can remove the constraints we can remove the constraints right so
1:55:41
all these things will be done with the alter command okay and this alter
1:55:46
command is that comes under the ddl command ddl
1:55:52
command ddl means a data definition language so that these commands will change the schema of a database schema
1:55:59
or a structure so we can call anything schema of or S structure
1:56:07
right so now let us see uh one by one I will execute each and everything and in
1:56:15
the MySQL and I will show how we can add a column or how we can delete a column how we can modify the data types or we
1:56:22
can set the constraints and remove the constraints so let me uh demonstrate that by
1:56:27
executing in the MySQL so let us open the MySQL so clear screen
1:56:39
in our demonstrations we are using this YouTube database so show tables so here
1:56:45
we are having only one table let me drop that table okay
1:56:51
so now there are no tables so you can see empty set so let us create one table
1:56:57
without any constraints and anything right so create table student
1:57:07
so let it take Sid which is of a integer
1:57:13
yes name which is of a wire cap for 30. okay percentage which is of a float
1:57:21
which is of a float data type or let me take its integer integer
1:57:27
right so you can see query okay zero rows affected so show tables you can see only
1:57:34
one table is there and there are no data there is no data in it okay now the
1:57:41
first thing the first thing is how to add one more
1:57:46
column so if you want to add a one more column that means age if you want to add
1:57:52
age so add column so what is the syntax here so SQL
1:57:58
so what we have to do so alter table
1:58:05
name table name
1:58:10
add column name and the data type
1:58:16
so simply by using this one it will add a column let us check so alter table
1:58:22
student add let us add age H which is of integer
1:58:29
data type simply use this one so query okay zero rows affected you can describe the student so that we will be finding
1:58:36
the age attribute one more attribute has been added and if you want to drop so
1:58:43
the next one is drop column so hope you understood alter means we are just changing the structure
1:58:50
of the database so are the table table name and if you want
1:58:57
to delete a column so drop column
1:59:04
column name so it will automatically drop that particular column if you want to delete
1:59:11
some percentage or is some age so you can use alter
1:59:17
table student drop
1:59:23
column what's the column name Let It Be it's a percentage or age
1:59:28
see query okay zero rows affected and if You observe that this I mean describe this student the structure of the
1:59:36
student you can find only the three rows that means the three columns are available Sid s name in percentage so in
1:59:43
order to delete any column so you can delete that one and the next one modify
1:59:49
the data type now we know that percentage we have declared this percentage with an integer but the value
1:59:54
of a percentage will be always a fruit right so if you want to change the data type
2:00:00
right see modify
2:00:06
data type so what's the syntax SQL alter table
2:00:12
name so instead of add one drop here you need to use a modify modified class so column
2:00:21
column name column name and the data type what what
2:00:27
data type we need to give so alter table student
2:00:33
modify percentage so column column name and data type so
2:00:40
modify column percentage with the float
2:00:46
yeah see query okay zero rows affected that implies the percentage data type
2:00:52
has been changed so let us check with that one describe student database so here you can see percentage with a float
2:00:58
and previously the percentages of integer so this is how we can modify the data type and one more thing how we can
2:01:06
also add a notional constraint add not null
2:01:13
how can we add the normal so for that also use a alter
2:01:18
table table name so this is a common for all the things right alter table table
2:01:23
name and after that simply we can we have to use a ad constrained add constraint
2:01:31
okay sorry uh modify okay modify similar to our modified data type modify the
2:01:39
column name modify the column name
2:01:45
data type and not null constraint
2:01:50
or null constraint see for example here null is yes for percentage if I want to
2:01:57
change this uh not null here so let us take alter table student
2:02:04
so modify percentage with the float with a normal
2:02:12
so query okay zero rows affected you can describe the student so that see float
2:02:18
is no okay here you can observe right not a null not a null constraint
2:02:24
and then adding a const
2:02:30
unique constraint uni so this is also a simple
2:02:36
so give the SQL alter table table name
2:02:43
right so give the ad constraint unique
2:02:50
unique of column name
2:02:57
column name so automatically the given column will be given as a unit so let us add first one so alter
2:03:05
table student okay so we'll add one more field that is
2:03:11
a email with a bearcat of 50.
2:03:18
so now you can observe describe the student so there is a one more field there is a email now we'll we'll give a
2:03:26
email as a unique okay so alter table
2:03:32
student alter table student add constraint add
2:03:38
constraint okay unique of
2:03:44
email so automatically the email will be set as a
2:03:50
unique constraint so which which will which will be unique that means no duplicates but it can be left blank that
2:03:57
is a null see I mean and a UK is a unique K is a
2:04:03
unit similarly add primary key constraint
2:04:10
primary key constraint similar to our unique key constraint okay similar to our unit key constraint so for this also
2:04:17
we have to use the alter a table table name
2:04:23
add constraint primary key
2:04:30
of column name so if it is a single column
2:04:36
we can give a single column otherwise we can give a multiple columns with the comma separated right so let us set this
2:04:42
Sid as a primary key so alter table student
2:04:48
at constraint primary key of Sid
2:04:55
so you can see query okay zero rows affected that means you can observe describe a student so you can see Sid
2:05:03
int no I mean primary Keys the key the key is a primary key
2:05:09
okay and how to add a constraint check add
2:05:15
constraint check so if you want to add the constant check
2:05:21
so this is also alter table
2:05:27
table name add constraint
2:05:33
check followed by the condition so whatever the condition we are giving that that we can give so for example we
2:05:39
can uh add one more field alter table student
2:05:45
add age with the integer
2:05:50
so now we can check the condition we can set the condition
2:05:55
so describe student
2:06:00
right so here you got the age age field so we give the checked constraint for this H
2:06:07
alter table student
2:06:12
add constraint check give the condition H greater than
2:06:18
18. so you can now check describe
2:06:27
student okay here we'll be having the age the check constraint has been set
2:06:33
and if you want to drop any constraint if you want to drop
2:06:38
constraint right so for that also we have to use a
2:06:44
alter command so alter table name
2:06:51
drop constrained
2:06:59
constraint constraint name so whatever the constraint we need
2:07:06
to drop that should be deleted so here you can see alter table
2:07:13
student drop
2:07:19
constraint unique
2:07:26
so let us drop sorry so let's take alter
2:07:31
table student drop index so what is a unique email
2:07:42
so automatically it will be dropped so if you want to delete the unique key the syntax so delete the unique key
2:07:53
I will write once again okay
2:07:58
drop constraint unique so for this only it will be
2:08:05
different so SQL alter table
2:08:11
name drop
2:08:16
index we need to drop index so what is the index email right
2:08:23
so index and column name right so here alter table student drop
2:08:32
index so for which we are giving the unique key that particular column we have to give so automatically it will be
2:08:39
deleted now let's set the ESC a student so whether the unique has been deleted
2:08:45
yes the unit has been deleted and if you want to delete the primary key also so use the same thing alter table student
2:08:54
okay drop constraint
2:09:02
give the primary key simply you can use a primary key so this is a syntax drop constraint and constraint mean so drop
2:09:08
constraint the primary key so it will sorry
2:09:17
alter table student drop
2:09:23
primary key so automatically it will be deleted right so sorry the syntax is slight
2:09:29
difference you can remove this constraint so this is a primary key
2:09:36
primary okay so simply you can
2:09:43
use the Syntax for deleting the primary key constraint and the unit constraint okay so you can observe here after
2:09:50
deleting the primary key see there is no primary key
2:09:55
okay so this is how we can use the alter command so alt command is used to change
2:10:01
the schema of the database or a structure of the database so in order to change the structure we can add a column
2:10:07
we can drop a column we can modify the data type we can add a different constraints
2:10:14
right we'll discuss about two more commands in SQL that is a drop and
2:10:21
truncate so a drop command is used to delete the
2:10:26
complete table from the database whereas truncate command will delete the data
2:10:33
from the table not the table the data from the table so let us see the syntax and then we'll
2:10:40
execute the same in the SQL so the first one is a drop command and
2:10:47
both are both comes under the data definition language commands Okay so drop command which is a ddl command
2:10:55
and the syntax is drop table followed by table name so it will
2:11:04
delete the complete table okay see here it deletes
2:11:13
complete table from the database from the database so
2:11:20
we know that the database consists of a lot of tables so in order to delete the table the syntax is drop table table
2:11:27
name and coming to the truncate truncate
2:11:32
command which is also comes under the ddl command and it deletes
2:11:41
complete data from the table so the table will not be
2:11:46
deleted but it will delete the data from the table so the Syntax for this one is truncate
2:11:53
table name table name right so let us execute these
2:12:00
two commands in the MySQL so let us open the MySQL command line so it will prompt for the password so
2:12:07
use the password and clear the screen now use the database YouTube because we
2:12:16
are going we are using this YouTube and in this YouTube we are having the tables let us check
2:12:22
tables so we are having only one table that is a student right so if you want
2:12:27
to delete the data so let us check whether this one having the data or not
2:12:32
so select start from student there is an empty table so let us insert some data so insert
2:12:40
let us check the description
2:12:46
student so that we'll get the complete structure so we need to give all these values so I'll insert
2:12:56
or or first let us see the drop table so drop table student so it will completely
2:13:05
delete the table from the database see query okay zero rows affected and now you can see show databases or show
2:13:12
tables so it will be an empty set because previously we are having a student table so in the YouTube database
2:13:19
if you execute this show tables command you will get the table student
2:13:25
but we have deleted the table so if the table consists of data then also the
2:13:31
table will be deleted the drop table table name will completely deletes the table from the database now once again
2:13:39
we'll create a table and we'll check with the truncate function so create table
2:13:46
student let us uh take some three attributes Sid which is
2:13:52
of integer s name which is of where care
2:13:58
of authority and some percentage which is of a float
2:14:04
and use a primary key it's not required but we'll use this one primary key as ID
2:14:11
so a table has been created now if You observe short tables so there is a one
2:14:17
table with a student so let us insert some values insert into student
2:14:23
values so let us take 1 0 1 Sandeep
2:14:30
99 so one row affected
2:14:37
and see one zero two
2:14:42
sarathi 96 so again
2:14:49
one zero three
2:14:54
Rambo sorry one zero three
2:15:03
ramu with 93 right so now you can select uh start
2:15:11
from student you can get the data so there are three rows now use a truncate
2:15:16
method truncate table table name student so it will delete all the data from the
2:15:25
table so you can observe the tables so the table exists okay the
2:15:30
table will not be deleted so if you use a truncate command it will delete the data inside the table but not the table
2:15:37
right so you can observe show tables still the student table is available here but what about the rows inserted in
2:15:44
the student tables so select start from student it will be the empty set because all these through
2:15:50
three rows will be get deleted for by using this truncate command
2:15:57
right so hope you understood this one a simple commands drop to delete the table truncate to delete the data
2:16:04
[Music] so in the previous sessions we have seen
2:16:11
a create command and insert command so create command is used to create a table
2:16:16
and insert command is used to insert the data into a table now the select command
2:16:21
is a one more DML command that is a data manipulation language command which will
2:16:27
retrieve the data
2:16:34
we will retrieve the data from the table right so in order to get the data from the table we need to use this select
2:16:40
command so here the Syntax for this select command is so
2:16:45
select star
2:16:53
table name
2:16:59
table name and here you can write a where class where
2:17:06
a condition so here you can write the condition
2:17:13
order by order by the column name
2:17:23
and also having so we can write a having
2:17:29
and a group by right so all these classes we can write
2:17:35
but uh in this session we'll see this three and after we will see this having
2:17:41
a group by okay so first we will see this one so here this is a normal syntax and here
2:17:48
the star the star is nothing but our projection so we have
2:17:54
discussed this projection Concept in relational algebra right so projection means restricting the column names
2:18:00
so here we need to specify whatever the columns we require to display that
2:18:06
particular columns we have to specify here so if you give a star it will display
2:18:14
all the columns all the columns right and if you want to
2:18:20
restrict a few columns here you need to specify it as specific columns right so
2:18:26
select some column name one column name 2
2:18:31
Etc from table table name right so only these
2:18:39
particular columns will be displayed on the result resultant table right and here this is not mandatory where where
2:18:46
class is not mandatory so if you want to get the details based upon the condition then you have to write this where class
2:18:52
and this condition can be this condition can be a simple condition
2:19:00
or a compound condition so we know what is exactly the condition so so if our
2:19:06
condition means if our expression is having some is equal to
2:19:11
not equal to greater than less than greater than or equal to less than or
2:19:16
equal to so if you use any field with these operators relational operators so
2:19:22
these are called as a relational operators relational operators so if our condition
2:19:29
is having these relational operators this expression is having these relational operators so that we call it
2:19:35
as a condition so here we can use either simple condition or a compound condition so if you are using the compound
2:19:41
condition then if you are using the compound condition that comparison between two different
2:19:48
simple conditions so you can use all and end and if not so you can use these
2:19:56
operations these operators that means a logical operators we call them as a
2:20:01
logical operator so these logical operators are used to compare two different conditions So based upon the
2:20:07
result of the condition the complete results will be displayed in the resultant table right so don't worry I
2:20:14
will execute all the queries so I will execute a query with a simple condition I will execute a query with the Comfort
2:20:20
condition and I'll execute with the query without the conditions I will execute a query with some columns and I
2:20:26
will execute the query to display all the columns every case each and every case I am going to execute in the MySQL
2:20:33
right right so this is only the introductory part I am showing the syntax and apart from these things we
2:20:40
can also have like
2:20:45
so we can use a like here okay where condition or some this condition in the
2:20:51
place of condition we can use this light so this is to search
2:20:56
for a pattern for a pattern so that means if you want to get the details of an employees or a
2:21:02
student whose name starts with some character so that is a pattern match so
2:21:08
for that purpose we are going to use this light and in so in means will give
2:21:14
a multiple values and if the condition satisfies with those values the result will be displayed right so search
2:21:22
in multiple values multiple values and also between
2:21:32
so between means the name itself indicates will give such inner range
2:21:49
excuse me so here we are supposed to give a range of values So based upon
2:21:55
that particular range the result will be written okay so here the next one is an order by
2:22:01
order by 2 arrange
2:22:06
arrange in ascending order
2:22:13
or descending order So based upon the given column the
2:22:19
complete data will be arranged either in ascending order or a descending order right so all these are the not mandatory
2:22:26
so this is a normal syntax one retrieving the data so select start so
2:22:31
star can be a single column I mean if a particular columns or a multiple columns
2:22:37
all the columns so from a table name so that from this particular table the details will be displayed in the
2:22:43
resultant table and if you want to filter the changes filter the values so
2:22:48
for example if you consider any student table if you want to get the student details whose percentage is greater than
2:22:55
90 so there we are supposed to use a condition so there we have to use this where class
2:23:02
okay so apart from this uh order by that means if you want to arrange the student
2:23:07
details based upon the percentages so either ascending order or a descending order you can use this one
2:23:13
right and also we can search the student details based upon the pattern given
2:23:19
pattern we can search the student detail based upon in the multiple values giving the multiple values as I input from that
2:23:26
multiple values we can get the data and also we can give a range of values so the I mean the match or the search which
2:23:34
satisfies this particular range will be displayed on the screen so I'll execute
2:23:40
each and every query of this select statement so accept having end Group by
2:23:46
so we'll see the having group by and the nested queries in our further sessions so this is all about our select command
2:23:54
it's a very very important because if you want to get the data from the table this is most important but through the
2:24:00
select command itself we are getting the data from the table so we which is com which comes under the data manipulation
2:24:07
language right so let's let's move on to the SQL I'll demonstrate each and every case of
2:24:13
this select statement right so let's move on to the MySQL
2:24:19
Hello friends just now we have seen the Syntax for select command in SQL
2:24:25
and this select command is used to retrieve the data from the tables so in
2:24:30
the previous session we have seen the command how to create the table and how to insert the data into the table now
2:24:36
we'll see how can we retrieve the data from the table right so one command is Select command which comes under the DML
2:24:44
commands data manipulation language so just now we have seen the syntax so how many ways we can get the data so let
2:24:52
us see in the implementation by executing the queries in MySQL so let me open the msql command line
2:24:59
so give the password I'll clear the screen
2:25:07
and here we need to use a database so we have created one database called a YouTube database so from this YouTube
2:25:14
database we have created one table by having that is a student table and we
2:25:19
have inserted some sort of data into the student table let us check with that one so show tables
2:25:28
so there is one table that is a student right in order to get the data
2:25:34
in order to get the data from the table so if you use the star if you use the
2:25:41
star that implies it will get all the columns the data from all the columns
2:25:47
see let us check so select command
2:25:52
start from the table name is a student if you execute this one it will display
2:25:57
all The Columns of a student table see so if you describe the student
2:26:04
foreign fields that means the three attributes
2:26:09
we got all the three data right three the data of all three Fields And if you want to restrict the number of fields
2:26:16
that means in the relational algebra we have studied about the operations like projection right so that means we can
2:26:22
restrict The Columns so we can get only the specified columns then you have to
2:26:28
use this query so select the required column names from the table name so
2:26:34
select if I just want to get only the roll number and the percentage so select
2:26:39
Sid comma percentage from student so that it will display
2:26:46
only the required columns right only student ID and a percentage only the student ID and percentage so if you want
2:26:54
to specify the data for a particular columns then you have to use this query
2:26:59
select column name one column name two column name three Etc etcetera what are the columns you need to be displayed so
2:27:05
that column names should be represented here from the table name and there is a one more case to get the data that is a
2:27:12
distinct distinct so that means If You observe the data so select a star from
2:27:19
student you will get okay the complete data you can observe here so total uh
2:27:25
some eight student details are there eight student details among these eight
2:27:30
student details see what are the distinct values that means without any
2:27:36
repetition for example if you go with the Sid there is no repetition because it's a primary key so we'll get all the
2:27:42
eight and coming to the s name you can see there is a one one name which is
2:27:49
repeated Harry so among this total eight data we will get only seven because if
2:27:56
you use this distinct okay without repetition what are the values right see
2:28:01
select distinct s name
2:28:07
from student so you will get only eight see one two three four five six seven because total
2:28:15
eight among these eight the name Hari has been repeated two times so it will
2:28:20
not be reflected here only seven rows of insert similarly coming to the percentage
2:28:28
select distinct percentage from student so here also we'll get only
2:28:36
six because there are repetitions I think see 99 is one repetition 88 is
2:28:42
another repetition so two repetition State repeated values will be deleted so that will get only the unique values so
2:28:48
for that purpose for to getting that unique count we can go with this distinct
2:28:55
distinct right then the general Syntax for retrieving
2:29:02
the data so select the column names from the table and where we have to use a where class and do the condition So
2:29:09
based upon this condition the data will be retrieved and here the condition can be a single condition or a compound
2:29:14
condition and single condition means using the relational operators right so all these are the relational operator
2:29:20
and apart from these relational operators we can also use a different cases that is a between so that we can
2:29:27
give a certain range and like which can search the pattern search for the given pattern and in to select from the
2:29:36
multiple set of values right so I'll show you the syntax and I will execute
2:29:41
the query don't worry about that say first let us see the simple condition
2:29:48
so select star from student where simple condition that means if I need to get
2:29:55
the details of a student whose percentage is greater than 85 so
2:30:01
that's a percentage so select star from student C
2:30:08
display student details whose
2:30:14
percentage greater than 85
2:30:20
right so let's let first one so what you have to do select star from
2:30:26
student where percentage greater than 85. so you'll get the complete details
2:30:32
of a student whose percentage is greater than 85 so you can cross check before so select star from student so you will get
2:30:40
this one so we are checking for percentage greater than 85 so one zero one
2:30:47
zero three one zero seven and one zero eight see one no zero one three seven and eight
2:30:54
total four rows are there right so this is how we have to write
2:31:00
this this condition so here we are checking only one condition so that's why we are giving with a single
2:31:05
condition single condition right next so display
2:31:13
student details whose percentage
2:31:19
is greater than 70 and
2:31:27
s name is equal to
2:31:34
Hari okay s name is so we are checking this one percentage greater than 70 and
2:31:39
S name so it should satisfy both the conditions so the name should be Hari
2:31:44
and the percentage should be 70. okay or simply you can see uh we'll check Sid
2:31:51
greater than one zero three okay so we'll check with the student ID
2:31:56
whose student ID is ID is greater than 103 and whose percentage is 70. see
2:32:02
select star from student so if you need to get only a few things we can simply use the
2:32:08
columns I mean here you need to give the column and giving star so that it will display all the three columns so where
2:32:16
where percentage greater than 70 and Sid
2:32:23
greater than one zero three sorry
2:32:29
sorry here the percentage is p r c right see we got the details so 1 0 3
2:32:38
and percentage is greater than 70. so 103 greater than 70 is 1 2 and 3. so the
2:32:45
total three rows have been retrieved okay so end end means both the
2:32:52
conditions should be satisfied and if you give the same query
2:32:57
with r or means so a the percentage is greater
2:33:02
than 70 or SID greater than one zero three we'll get everything so if any one of the condition is true
2:33:09
the the same result will be given here so that means we will get the details of students whose percentage is greater
2:33:16
than 70 and whose per whose Sid is one zero three that means a union operation
2:33:22
so you can simply for better understanding you can see the percentage greater than 70 the result will be there
2:33:28
Sid greater than one zero three is results will be there so you just you apply the union operation in between
2:33:34
these two operations so it will automatically avoid the duplicate so we'll get the complete details
2:33:41
right so I hope you understood this one and then
2:33:47
see this is the third query display the student so this is this we
2:33:54
call it as a compound condition because we are applying the two different conditions so we can simply uh say this
2:33:59
as a compound condition right so similarly less than or equal to
2:34:05
greater than or equal to not equal to everything you can write it see condition one and condition two and
2:34:11
condition three and so on condition one or condition two or condition three and so on right and not not that means
2:34:20
if it is true then it will be false if it is false it will be true so you can see
2:34:26
so display student details whose percentage
2:34:33
is not greater than okay not greater than
2:34:42
uh 90. okay so for this we can simply write so
2:34:48
select star from student where
2:34:55
see what is the syntax here simply you can see where not a condition
2:35:00
where not percentage greater than 70. or 90.
2:35:09
see so simply we will get the student details whose percentage is not greater
2:35:15
than 90. right so we are getting only six rows because so there are two rows with a
2:35:22
percentage 99 which is greater than 90. right so hope you're understood so first
2:35:28
it will check for the condition percentage greater than 90 and not we are applying not that means other than
2:35:34
the percentage greater than 90 will be retrieved so this is one case
2:35:39
okay and the next one is order by order by means simply that's an uh arranging
2:35:46
the data in ascending order or additioning order by default it will be the ascending order so see select
2:35:55
star from student so let let us arrange this percentage in
2:36:02
ascending and descending order so if you want to arrange the percentage in ascending or descending order you can use a order by
2:36:10
so our next query so display
2:36:16
student details in ascending or
2:36:24
descending order right based on percentage So based upon percentage we
2:36:32
need to display the student details so for that you can simply use a select star from student
2:36:38
so where sorry order by
2:36:43
percentage so by default it will be the ascending order sorry
2:36:48
PRC okay so always I am giving the wrong field name so perc right so you can
2:36:57
observe here it is an ascending order that means lower to higher lower to higher so it will display all the
2:37:04
student details with the assigning order percentage with ascending model and if you want to get the internet descending
2:37:10
order okay if you want to get it in a descending order you need to specify this particular DSC
2:37:17
desc let us say so select
2:37:23
star from student order by
2:37:29
percentage Des here you need to specify dese so that will get the details in a
2:37:34
decent border designing model similarly you can also use a select a star from
2:37:40
student order by yes name so yes name means based upon
2:37:47
the alphabetical order details the student detail should be arranged g h r s v
2:37:53
if you want to give in the descending order simply you can use so desc so that we'll get the complete
2:37:59
details in a descending order with respect to the name with respect to the name
2:38:04
so this is how we can arrange the data in ascending or a descending order and the next one
2:38:11
limit the number that means so based upon the condition we may get a number
2:38:16
of values okay we may get the number of values but if you want to limit the number of data right so if you want to
2:38:21
get only the few data some uh top two data right so we can
2:38:27
limit that data to be retrieved by using this limit class for example
2:38:35
so select star from student so there are a lot of data so you can
2:38:40
get see select star from student where percentage greater than
2:38:49
80. so we are getting the complete details right from the starting of the Sid we are getting
2:38:55
the complete result whose percentage is greater than 80. now you can limit this
2:39:01
one so I just want to get only the three details the first three details so then
2:39:06
so select star from student
2:39:12
student where percentage greater than 80 limit
2:39:17
to 3 that means I need to get the first three values sorry
2:39:24
yes just a second
2:39:30
yes
2:39:39
from student right see we'll get only three we'll get only the three because we are
2:39:46
limiting the result to 3. right so the query will be display
2:39:53
foreign details okay whose percentage
2:40:01
greater than 80 so this is also display
2:40:07
first three student details first three student details whose percentage is
2:40:12
greater than 80. So based upon the Sid okay based upon the Sid that means a student ID will get the details if you
2:40:19
Google here so greater than 16. limit to 3 so we'll get only limited to
2:40:25
3. so if you go with the limit to 5
2:40:32
we'll get a 5 top 5. so here you can observe one zero five is missing
2:40:38
select star from
2:40:43
student okay where PSI d is equal to one zero five let us
2:40:50
check only the percentage okay why this percentage I mean why this one zero five is not getting right so select
2:40:58
percentage student from Sid is equal to one zero five
2:41:04
so the percentage of 105 is 40 which is not greater than 60 so that's why we
2:41:10
didn't get this value 105 results in the result right
2:41:15
okay I hope you understood we can limit the number of results so
2:41:22
in a class there might be some 100 students so we need to get only a few students so that's why we got this one
2:41:30
okay so hope you got this one
2:42:15
see so this is how we can get the data that means the top three
2:42:23
rankers in the class okay so let us write down the query here
2:42:28
first so that you can understand so display first
2:42:35
three ranks based on percentage okay
2:42:41
from the student table right from the student table display first
2:42:47
three that means the top three ranks top three ranks based on percentage from the student
2:42:58
right so for that what we have done so simply we have created one query select star
2:43:04
from student order by percentage descending so this will automatically display all the details in the
2:43:11
descending order based upon the percentage in addition order here we are limiting the three limit to 3 so that we'll get the top
2:43:18
three rankets here right so hope you understood this one why we are using this limit
2:43:24
okay so if you want to get a least last three ranks so simply you can use a
2:43:30
select star from student order by percentage so it will be in
2:43:36
ascending order so limit three so you'll get
2:43:42
the least three ranks in the table you can observe here select star from table
2:43:48
student so the last ranks are 40 65 40 and 77
2:43:56
only these three so see 40 65 and 77 so this is the one use of using this
2:44:03
limit that means we can limit the result so we may get a multiple values multiple
2:44:09
rows but we can limit these multiple rows and then
2:44:15
like it's a pattern pattern that means search pattern if you want to search a
2:44:22
particular pattern we can use this like okay so like pattern here we'll write
2:44:28
the query first so if you want to display the
2:44:33
display student details whose name
2:44:41
starts with a or some alphabet yes
2:44:46
okay so if you want to search this particular pattern you can you have to use use dislike okay so select a star
2:44:54
from student where s name like and here the
2:45:01
percentage symbol gives a multiple values okay percentage
2:45:06
symbols give them multiple values so like pattern so what what is the query here so starts with the S so like yes
2:45:17
percentage that means it will it should starts with yes and followed by there can be any number of characters
2:45:25
see any number of characters it should starts with yes and followed by any number of characters
2:45:33
right so this is a pattern search pattern search here we are giving a pattern
2:45:38
here we are giving a pattern so if you want to give this double e if you want to get the details of a students where
2:45:44
there are consecutive ease so display
2:45:51
student details
2:45:56
whose name how e e
2:46:06
anywhere anywhere whose name have ee anywhere so
2:46:12
for that purpose we can select this one so select the query where you can simply
2:46:17
use a percentage that means multiple elements or give here
2:46:22
e e percentage e and a percentage
2:46:29
okay and also EE so that implies sorry so here you can
2:46:35
see so it can have multiple values and ee
2:46:42
consecutive ease and multiple values so you'll get the result you'll get the result here
2:46:49
okay so this is how we can use this pattern search pattern search
2:46:55
if you want to get the data of a student details whose names ends with some particular I
2:47:01
so you can use percentage I so that you'll get the
2:47:07
complete student details whose name ends with I right so hope you understood this part
2:47:13
that is a like like to search for the pattern and
2:47:19
in column name in you can use multiple values if the column name is having
2:47:24
these values it will execute
2:47:30
see so display student details who's
2:47:37
percentage is either 90 99
2:47:42
88 75 okay so we have to display the
2:47:48
student details whose percentage is either 1999 88 and 75 so you can simply
2:47:53
use a simple condition okay compound condition by checking the percentage is equal to 90 or percentage is equal to 99
2:48:00
or percentage is equal to 88 or percentage is equal to 75 but by using this in also we can execute the query so
2:48:08
let us check this one so select star from student where
2:48:15
percentage in so give the values here what are the values we have given here so 90
2:48:22
99 and 75. so you'll get the complete
2:48:28
details whose percentage is satisfies with these particular values so for example you can also give the 40
2:48:35
here see so you get the details 9988 40 all
2:48:40
the three has been there right so there are no student details whose percentage is 90. okay
2:48:48
this is exact match it's not a between okay we are not giving any range we are
2:48:53
checking the percentage with exact matches either 90 or 99 or 88 of what
2:48:59
right so the next one is a between that means a range
2:49:05
range so the query will be like this so display
2:49:11
student details who's percentage
2:49:17
is between 85 and 90.
2:49:23
or 95 85 and 95. so here we have to give the range
2:49:30
right so in order to give the range you can use this BTV between okay see simply will execute the
2:49:37
query so select
2:49:42
star from student where percentage
2:49:50
between 85 and 95 so it will display all the
2:49:57
student details so whose percentage is in between 85 and 95 and if you change this 95 to
2:50:04
100 you will get the details see in between 85 and 100.
2:50:11
right so this this is how we can execute the query to retrieve the data that
2:50:19
means there are a lot of cases so still there are a lot of cases so there are we
2:50:24
are having a two more cases that is by using uh have have by okay Group by and
2:50:32
having group by and having classes so we can also retrieve the data by using
2:50:37
these two classes so that means a ha having an Group by and also we can use a
2:50:42
sub queries okay sub queries nested queries so we'll
2:50:47
see all these things in the further classes right so hope you understood this uh select statement how many ways
2:50:53
we can get the data from the table we can use a simple condition we can use a
2:50:58
compound condition or we can use a between we can use a like we can use in
2:51:03
we can use the order by that means we can change
2:51:09
the order right so based upon ascending order or the descending order and also we can limit the number of results we
2:51:15
can limit the number of results so hope you understood this one
2:51:21
update and [Music]
2:51:27
so coming to the first one that is an update command and it is used to
2:51:38
the existing data the existing data so it is a slightly different with
2:51:46
the alter command so alter will be the ddl command and this update is a DML command
2:51:52
so this one is in DML command so DML command means here this
2:52:00
command will be used to change the data of a table if it is a ddl it will change
2:52:05
the structure of a table right so the syntax were update
2:52:13
is update
2:52:19
table name set
2:52:26
column 1 is equal to the new value comma
2:52:33
column 2 is equal to a new value
2:52:38
comma Etc where the condition satisfies
2:52:45
where the condition satisfies so first it will check for the condition so for
2:52:50
all the resultant rows the column value will be set okay whatever the value
2:52:57
existing that will be replaced with the new value okay the given value so this is the
2:53:04
Syntax for update and this is the same will be used for updating either single record or multiple records okay so based
2:53:11
upon the condition based upon the condition and based upon the resultant table so it may change the single row or
2:53:19
multiple rows so that's the update so I'll demonstrate this variable execute this query in the
2:53:24
SQL MySQL okay so first let me complete the syntax the theoretical process and
2:53:30
then we'll go to the Practical so I'll execute the query in the MySQL and I will show you then coming to the delete
2:53:37
so delete is used to delete the rows a
2:53:42
specific rows of a table so delete
2:53:50
use it to delete
2:53:59
specific rows from a table
2:54:06
so some based upon the condition here also we will give some conditions So based upon that condition the particular
2:54:12
rows will be deleted either it can be a single row or a multiple row and similar to our update function update command
2:54:18
this is also a DML command DML command so this command is used to
2:54:24
change the data that means delete the data and it is completely different from drop and truncate drop and truncate
2:54:31
where drop and truncate are the ddl command data definition language so drop
2:54:36
comment is used to remove the complete structure of the database and truncate is used to remove all the rows of a
2:54:42
database but here the delete command is used to delete a specific rows based upon the condition
2:54:48
so the Syntax for delete the Syntax for delete is
2:54:55
delete from
2:55:01
table name where
2:55:07
condition so here we can write the condition so from this condition based upon this condition the resultant rows
2:55:14
will be get deleted or this is one sentence and the second syntax
2:55:20
is a delete from table name
2:55:27
so this syntax will delete all the rows from the table similar to our truncate
2:55:33
function okay so this one deletes
2:55:41
specific rows or records and this function
2:55:47
deletes all the records all the records are all
2:55:53
the rules from the table right so this delete command is a DML
2:55:59
command and update is also DML command update is to reassign the values okay
2:56:05
change the value of a attribute and delete is to delete the required rows
2:56:11
so hope you understood this syntax of update and delete commands in SQL now
2:56:17
let's move on to the MySQL and I will execute the two statements okay two
2:56:22
statements or two comments that is update and delete so that if you still have any doubts those routes will be get
2:56:29
clarified so let's move on to the MySQL Hello friends so just now we have seen
2:56:35
the Syntax for two different comments in our SQL that is the update and delete which are DML commands
2:56:42
so so these are the syntaxes we have seen just now so let us execute these queries in MySQL so let me open the
2:56:50
MySQL so give the password and let us clear the screen first
2:56:55
yes so we have created one database to execute the queries that is a YouTube database let us move on to the YouTube
2:57:02
database and here we have created one table that is a student so there are
2:57:07
some sort of Records in student table so let us check for that for those records select start from student we'll get the
2:57:13
complete details of a student details so there are some seven records seven still records of a student now we'll update
2:57:21
update a query or update a command is used to change the value of n
2:57:27
attribute right so it can be anything but the primary key cannot be updated we
2:57:33
can't update the primary key value right so only other than the primary key we can update any attribute
2:57:39
for example if you just want to change the percentage of student Sandeep
2:57:46
what's the syntax so give that one update stable name update Sandeep say
2:57:51
the column name percentage is equal to some 95 okay condition where s name is equal to
2:58:01
let us take Sunday execute
2:58:08
sorry here we have given table also sell it right table student no so student set
2:58:15
percentage is equal to 95 where yes name
2:58:22
is equal to something so first this condition will be executed and it will get all the rows whose name is Sandeep
2:58:30
and those percentages will be get updated with the 95 so previously it was 99 so once you executed this one so you
2:58:38
can see select start from students now it was 95 so that means we can modify the attribute value by using the update
2:58:46
not only the single value so here it is a single single value now I just want to
2:58:52
update multiple values okay in order to update the multiple values how we can so
2:58:57
for example for that let me uh add one more column here so alter in order to
2:59:03
add one more column alter table student
2:59:11
and I'll give a grade with a where care of some 40.
2:59:19
execute one row is affected right so select a star from student you can
2:59:24
observe here so we had a grade column initially every value of this grid is
2:59:30
null now we need to update this one so how can we update we have to give a
2:59:35
grade such that the 40 is a pass and in between 40 to 60
2:59:43
okay in between 40 to 60 it's passed and 60 to 70 it's a first class and above 70
2:59:51
first class with extension right so we need to update this one so for that we
2:59:57
can see update student set grade is equal to
3:00:04
pass give the condition here where percentage is greater than
3:00:14
35 and percentage less than 60.
3:00:21
okay percentage less than 60. simply update here you can get the details see
3:00:26
the student who got the percentage in between 35 and 60
3:00:32
so we'll get a pass right now again change the data see
3:00:41
now change the grade to first class I'll give the first class
3:00:46
first class where percentage is greater than
3:00:52
60 and percentage less than 70.
3:00:59
so two rows matched you can observe here what are those two rows so 68 percent
3:01:05
which is a first layer 65 percent which is a first class now again you can update the thing so
3:01:11
above 70 those are nothing but restrictions
3:01:17
distinctions distinction right where above we can simply use a 70
3:01:27
execute okay and here needs C
3:01:32
95 percent destination I mean sorry the 95 percent the grade is distinction at 84 percent
3:01:39
which is great is distinction 68 for square 77 distinction so we can update
3:01:44
is either single row or a multiple rows So based upon the result from the
3:01:49
condition so the number of rows will be get affected with the given value so
3:01:55
that is the update function that's simply update function similarly
3:02:01
delete function delete function so here delete function the syntax let us see
3:02:07
this index so there are two functions okay two queries so delete from table
3:02:12
name where condition and delete from table so we can delete a single row or a multiple row or all the rows so in order
3:02:19
to delete a single row or a multiple row we can use this delete option delete from table name where condition so you
3:02:26
can write here delete from student where where grade is equal to
3:02:34
pass so one row is affected you can see whatever the student one zero five the
3:02:40
student details of one zero five who got a great pass will be get deleted here
3:02:46
see one zero five has been deleted because we have we are deleting the student details whose grade is equal to
3:02:53
pass now let us check here
3:02:59
so I'll delete the first class I will delete the student details whose
3:03:04
grade is the first class now you can observe two rows affected and see only
3:03:09
the distinction will be remained here because in our table we are having the first class two first class and one pass
3:03:16
in among the seven rows so we have deleted pass and first class so three
3:03:22
rows has been deleted so totally four rows has remaining whose grade will be the only distinction
3:03:27
only the distinction so this is how we can delete either single row or a
3:03:34
multiple rows either single row or multiple rows and if you want to delete all the all the rows simply you can use
3:03:41
a command delete from student which will delete all the rows
3:03:47
you can see we will get empty set we'll get a empty set
3:03:52
right so this is the option these are the DML commands two commands
3:03:58
update and delete so which will use it to update the value or delete the
3:04:04
records update the value or delete the records update can be done for single row or multiple rows and delete can be
3:04:11
done for a single row or multiple rows or all that all rows right so hope you understood these two
3:04:17
commands
3:04:26
aggregate functions in SQL and these are the functions which will be evaluated on
3:04:34
a different values of a column right so the first one is
3:04:40
count function which will give the
3:04:46
count of values of an
3:04:52
attribute attribute so these functions these
3:04:58
aggregate functions will be applied on a columns okay individual columns so if
3:05:03
you use this count function it will give the number of values available for that particular field okay
3:05:10
then some function so this will be applied
3:05:17
for a numerical values okay it will be applied for numerical
3:05:23
values and it will apply the sum of
3:05:30
all values
3:05:37
of an attribute so if you consider some attribute like subjects or a percentage
3:05:45
so it will give the complete sum of all the percentages okay then similarly if someone would function
3:05:52
called average so this will also be applied for the
3:05:57
numerical values and if you give the average
3:06:03
of all values right similarly
3:06:10
minimum it will give the minimum
3:06:17
value of n
3:06:25
so here the attribute means a column right here the attribute means a column so in that particular column there will
3:06:32
be a different values that means a different rules right so among those values it will return the minimum value
3:06:38
similarly among all those values it will return the average value among all those
3:06:43
values it will give the sum okay and this will give the count the number of values available for that particular
3:06:49
column and and similar to our in there is one more function called max amount which will
3:06:57
give the maximum value maximum value
3:07:04
of phonetic maximum value of an atom so these are
3:07:09
the aggregate functions of in SQL right so in the previous sessions we have
3:07:15
discussed about the select statement and in that select statements we left two concepts that is grouped by and having
3:07:22
so in order to know about the group by is having first we have to know about these aggregate functions so in this
3:07:30
session we'll see the aggregate functions and in the next session we'll see the group by end having class in
3:07:39
select command okay so these are the different aggregate functions now let us
3:07:46
execute each and every function in a MySQL so already we have created one database and in the database we have
3:07:53
already created a table right so we'll apply these aggregate functions on the
3:07:58
attribute of that particular table right so let's move on to the MySQL command line interface
3:08:05
Hello friends so just now we have seen the aggregate functions which are
3:08:10
available in SQL so let me demonstrate each and every aggregate function this MySQL so let me open this one so giving
3:08:19
the password so just and clear the screen now we have
3:08:25
created one database called YouTube so let us use that particular database and in that database we have created one
3:08:32
table I think yes there is a student so see first letter let's check what are
3:08:38
the fields available in that first students so whether the data is available in the student table or not so
3:08:44
in order to retrieve the data select star from student yes yes so we are having around
3:08:50
6 student details now so what is the first step
3:08:57
aggregate function it's a count right so count function
3:09:08
syntax so count of give the attribute
3:09:14
attribute name right so here
3:09:20
select count of yes name count off yes name
3:09:28
right from student so we'll get the count of that
3:09:35
particular destiny right so let us create one more uh I
3:09:41
mean let us check select count of Branch let's check this one
3:09:47
branch from student so we got a six count okay so among
3:09:53
these six you can observe three are from each CSE and one from ECE and civil and
3:09:59
Tripoli so what is the distinct count here so the distinct count is one two
3:10:06
three and four so we can also select this one select count of use the keyword distinct
3:10:13
distinct branch from student
3:10:18
see we got a distinct brand that means the unique count so it will avoid the
3:10:24
repetitions it will give a unique account and simply you can also give the select so count
3:10:31
star which will give you the complete count from student C
3:10:36
so there are six rows it it implies that there are six rows in the
3:10:42
table right so this is the count function this is a count function
3:10:47
similarly sum function some function so some function is also
3:10:53
similar so the Syntax for some function
3:11:03
sum of attribute
3:11:08
name so here select let us check select star
3:11:14
from student so this is a percentage
3:11:20
so that is only numerical thing so select sum of
3:11:26
percentage from student so you'll get the complete sum of all the values of an
3:11:33
attribute so here the attribute is percentage so whatever the value is available in this particular percentage
3:11:39
will be added and that will be returned as a sum okay then
3:11:46
what's the next one the next one is average
3:11:51
average the Syntax for this average is ABJ
3:11:57
followed by attributes name so select
3:12:03
average of percentage from student
3:12:09
so this is the average of a percentage so we have had the percentage of six students so among these six students
3:12:16
what's the average percentage the average percentage is some 91.333 and so on
3:12:23
so this is the average function then next one a minimum
3:12:31
minimum so in order to get the minimum value the syntax is min of attribute
3:12:40
name so here you can see select Min of
3:12:45
percentage from student so this is the minimum
3:12:50
percentage so you can see among all the percentages of students so this value is
3:12:56
the minimum so that's why we got 78 as a result the minimum percentage
3:13:03
and then the next one is maximum
3:13:09
maximum so the syntax is similar to our minimum function will give a maximum of
3:13:14
attribute name
3:13:20
so select Max of percentage
3:13:27
from student so we got a maximum percentage so among
3:13:33
all the percentages you can see the maximum value is 99 so we got this one maximum percentage
3:13:39
right so this is how we can execute the
3:13:45
aggregate functions so select count of attribute name
3:13:52
from table name so this is the syntax and here also you need to use a select
3:13:59
sum of attribute name from tablenet
3:14:06
and here also select average of attribute name
3:14:12
from table name
3:14:20
select minimum of attribute name from
3:14:25
table name right and here also you can give select Max of attribute name
3:14:32
from table name right so this is the aggregate functions all these are the
3:14:38
aggregate functions
3:14:44
SQL or we can simply call as SQL aggregate functions SQL aggregate
3:14:50
functions right so hope you understood the Syntax for all the aggregate functions and we
3:14:57
we are going to use these aggregate functions in the queries okay and uh in
3:15:02
the next class we will see about the group by class
3:15:07
as well as having class so in that we are going to use these aggregate functions so those class that means are
3:15:15
grouped by as well as having will be used in a select command there we are
3:15:21
going to use this aggregate functions so Group by
3:15:27
and so these clauses
3:15:35
so these clauses will be used in
3:15:42
select the statement or select command
3:15:51
select Command right and see this select the command is used to
3:15:59
retrieve the data retrieve the data from the table so in this select command we
3:16:05
have already seen the where class and the ordered by class so where class is
3:16:10
to get the data based upon the conditions and uh the next one order by
3:16:15
class is used to order I mean the getting the data in either ascending
3:16:21
modern or the descending mode similarly this group by
3:16:27
Group by class is used to group the class group the rows so
3:16:33
whatever the rows we are having so group the rows based upon the column
3:16:38
based upon the column value right so based upon the column value and
3:16:44
here we are supposed to use the aggregate functions so which we have covered in the previous session aggregate
3:16:50
functions like sum average minimum maximum count etcetera
3:16:57
etcetera we have seen these aggregate functions in the previous session so we are making use of those aggregate functions in this group by class
3:17:03
similarly having this having class here also we are going
3:17:09
to use the aggregate functions
3:17:14
and here will apply the conditions
3:17:20
conditions on a result
3:17:25
of Group by class
3:17:30
so after applying this group by class we can apply the conditions by using this
3:17:35
having clause having Clause right so this group by means grouping the rows so
3:17:42
the resultant rows will be retrieved based upon the column so that
3:17:47
means that group by column okay Group by column for example so in our uh
3:17:54
actually in the previous sections we have taken some database called a student right student database in the student
3:18:01
database we have taken the student ID student name for example let it be the
3:18:07
gender gender right so we can group I mean we can get the
3:18:15
details of particular rows grouped by this gender that means male and a female
3:18:21
so we can count okay how many number of students are male students and how many
3:18:27
students are a female so that can be done with the help of this group by so
3:18:33
we'll use account method so count means which is an aggregate function Group by
3:18:40
Group by gender so automatically we'll get the count of male and female
3:18:46
right so like that we are going to get the summary of rows based upon the
3:18:53
column that means Group by a column so that group can be either based upon a
3:18:59
single column or multiple columns or a multiple columns right so here also we are going to this
3:19:06
aggregate function whatever the summary we are getting here we can apply the conditions and get the result
3:19:12
okay we can apply the conditions and get the result right so hope you understood this one so
3:19:18
still if you are having any doubts so now let us execute these two things for a student table
3:19:24
okay in MySQL so I'll demonstrate these two classes by executing a small query
3:19:29
uh on the student database okay so that if you are still having any doubts your
3:19:36
doubts will be get clarified so let's move on to the MySQL Hello friends so just now we have seen
3:19:43
the introduction part of a group by and having classes now let us demonstrate
3:19:49
this group by in having classes of Select command so first let me open the MySQL
3:19:57
so go with the sorry password
3:20:02
root let's let us clear the screen now
3:20:08
see let us execute a few queries of that uh demonstrating these two things
3:20:21
yes Group by and the Syntax for this group by
3:20:26
is so we are going to use this group by and
3:20:33
having in a select command so select
3:20:39
star from
3:20:45
table name Group by
3:20:51
column one column two Etc so this is a small syntax
3:20:59
for group by and coming to this having class
3:21:04
the syntax
3:21:11
select start from student sorry from table name
3:21:21
table name Group by column one
3:21:28
column two Etc having
3:21:33
condition right so this is a Syntax for having class
3:21:38
so whatever the resultant table we are getting from the group by applying the conditions for that we'll get this
3:21:45
having class so whatever it may be we are going to use this group by class as well as the having class in a select
3:21:52
Command right so let us take an example so already we have we are using one
3:21:58
database called YouTube we have created the database sorry so in that YouTube
3:22:03
database we have already created one table called a student so first let us see the details of that student table so
3:22:11
let's start from student so we are having some rows
3:22:16
from 106 rows that means the details of 106 so we are taking the attributes Sid
3:22:24
s name percentage gender and a branch now coming to the group by
3:22:30
see first one finding number of students Branch wise so we have to group the
3:22:35
number of students Branch wise so for that we have to write so select we need
3:22:42
to get a branch from a count of Sid that means for each branch how many number of
3:22:48
students are there so from student Group by user group by give it as a
3:22:54
branch so obviously Group by Branch means based upon the branch it will
3:23:00
display the branch name as well as the count of the student details you can observe here see after executing this
3:23:06
one the CAC the count of Sid is three you can observe in the table CAC one two
3:23:12
and three so there are three students belongs to this CAC branch and ECE only one student
3:23:20
and civil one student and a triple one student so we got the details of
3:23:25
students based upon the branch so we are grouping the results grouping the rows based upon the branch
3:23:33
right next finding the number of male students and
3:23:38
the female students so how many number of male students and how many number of female students so for that we have to
3:23:46
group by gender we have to group by gender similar to our previous query so
3:23:52
use a gender count of Sid from student now we have to group by
3:24:00
gender So based upon the gender we'll get so male students three female students three now let us check with
3:24:06
this one star from student she made students three so one two and
3:24:13
three and a similarly female students one two and three so we are grouping the complete rows based upon the gender so
3:24:21
automatically we are having a distinct uh count of gender is true so we are
3:24:26
getting only two values based upon this one we are getting the count right so this is how we can use a group
3:24:34
by and the third one finding average percentage of male students and the female students we need to get the
3:24:41
average percentage of all among all the male students among all the female students here also we need to group by
3:24:48
the gender right because we are asking about the percentage of male and female
3:24:54
students now use a query select a gender comma use aggregate functions average of
3:25:03
percentage from student from student
3:25:08
grouped by gender so male students average is 94 and the
3:25:15
female students average is 88. so you can observe here let us
3:25:20
checkout so here the male students average 99 Plus
3:25:26
another mail 95 plus another 188
3:25:31
we are calculating the average so divided by 3. so we got a 94 for a male student yes correct and coming to the
3:25:39
female students let us take 9 78
3:25:44
plus another one 98. and here also we need to divide it by 3 average because
3:25:50
it's average 88.666 so we got a average percentage among all
3:25:56
the male students among all the female students next one maximum percentage from male
3:26:03
and female students here also we are grouping based upon the gender right so select
3:26:10
maximum of percentage okay uh
3:26:16
based upon gender no so gender comma maximum percentage
3:26:22
from student Group by gender So based upon the gender we are getting the maximum and a minimum
3:26:29
so male male students among male students the maximum percentages 99 and
3:26:35
among the female students so it's a 98 so 98 in male and 98 in a female
3:26:44
right so this is how we can write the query we can Group by gender
3:26:50
we can Group by gender and if you just want to get a branch wise
3:27:01
foreign so if you want to get the maximum percentage among the branch
3:27:08
so see CAC it's 99 and all the remaining only one student so we'll get 90 88 and
3:27:14
9 10. so coming to the CAC see 99 95 and 78 among these three 99 will be the
3:27:20
maximum and remaining branches only one student is there so it will be a maximum so we'll get this one so in this case we
3:27:26
are grouping by Branch we are grouping by branch right similarly the next one minimum
3:27:34
percentage a similar query so select uh similarly write down a branch and a
3:27:40
minimum of percentage from student Group by
3:27:45
Branch so in each individual Branch what is the minimum percentage so now you will getting the CAC as a 78 you can
3:27:51
observe here CSA 99 and 95 and 78 three students right among these three
3:27:58
percentage 78 is the least percentage so we are getting the 78 here so similarly based upon the gender you
3:28:04
can also use by gender so you can get
3:28:09
gender and a minimum of percentage now we are going to group by
3:28:16
gender so among the male students 88 is the least percentage and among the
3:28:23
female students the least percentage is the 78. see let us check
3:28:28
select star from student among male 98 95 and 88
3:28:36
so 88 will be the least female 90 78 and 98 so 78 will be the least
3:28:43
so like this we can apply the group by we can apply the rows the summary of the
3:28:48
rows grouping by a particular columns right and coming from coming to this one
3:28:56
so after getting the results of this group by applying the conditions we'll
3:29:01
be using with the having class so select start from table name Group by column one column two having a condition right
3:29:09
see finding the branches having only one student
3:29:14
finding the branches having only one student so we need to group by Branch
3:29:20
okay Group by Branch now use this one select
3:29:25
get a branch and a count of branches
3:29:30
from student Group by branch and what we have to get only one
3:29:38
student so count off Sid is equal to 1. so here also we need
3:29:45
to use the aggregate function sorry here Group by branch and here we
3:29:51
need to write the having class having count S ID is equal to one so actually we are getting the count of branches
3:29:57
cac3 ec1 civil one and triple one but here we are finding the branches who is
3:30:04
having exactly one student so we got only one stone in the branches who have
3:30:10
only one student so these are the three branches if you just change this one the second one see Finding branches having
3:30:16
more than one student so you can observe here greater than 1. so obviously we'll get
3:30:23
so only the CAC because except the CSA all the remaining branches are having only one student so let us count here
3:30:28
select star from student and here you can observe so CAC one two three students
3:30:36
belongs to CSE and ECE one civil one and equally one so here we are finding the
3:30:41
branches who are who are having who is having the students number of students greater than one so obviously we'll get
3:30:49
only one branch because only CS is having three students right so this is
3:30:54
how so whatever the result we are getting after applying the group by and
3:30:59
applying the conditions on that particular result we'll get the habit right next uh finding branches in which
3:31:06
students having a minimum percentage greater than 70 that means uh finding
3:31:12
the branches in which the student's minimum percentage is a greater than 70. okay in
3:31:18
which a student's minimum percentage so for this we can write so select
3:31:23
branch and we have to find the minimum percentage from a student
3:31:32
Group by branch having minimum of percentage
3:31:41
greater than 70. so you can see among the CAC
3:31:47
among the CSE the minimum percentage is 78 which is greater than
3:31:53
70. so among this ECE only one student 90 so it will be at the maximum and
3:31:59
minimum so let us insert a few more rows and we will check okay so you insert
3:32:07
into student values
3:32:14
107 so let us take the name
3:32:20
ramu and the percentage is some six
3:32:28
sixteen okay and gender May
3:32:33
[Music] and easy
3:32:39
right now select star from student we'll get a one more Row one or seven now we
3:32:45
can check greater than 70 you can observe here the
3:32:51
minimum percentage greater than 70 there is no EC because EC there are two EC
3:32:56
students right one with 90 percent and one with sixty percent so among with among these two percentages what's the
3:33:03
minimum percentage it says 60 Which is less than 70. so it will be not displayed so our query is our query is
3:33:11
finding the branches in which students is having the minimum percentage which
3:33:16
is greater than 70 so the minimum percentage of a student should be greater than 70. so obviously the easy
3:33:23
student will not be in the resultant right so hope you understood this one
3:33:29
similarly the last one finding branches in which student having a maximum percentage is greater than 90 so among
3:33:36
this one same maximum percentage the student's maximum
3:33:43
percentage should be greater than 90.
3:33:49
greater than 90 let us check foreign
3:34:01
let us check select star from student so among CAC among CSC
3:34:10
99 95 and 78 among this one maximum percentage is 99 which is greater than
3:34:16
90. so we are getting CH as a result coming to the ECE 90 and 16. so both are
3:34:24
not greater than 90. so here we are giving only greater greater than it's not a greater than or equality so EC
3:34:31
will not be displayed here coming to the Civil only one student which is 88 which is not greater than 90 and Tripoli which
3:34:38
is 98 which is greater than 90. so we got it to be as this one and if you check here
3:34:44
greater than
3:34:49
80. so we'll get all the branches because among CAC the maximum uh one
3:34:55
maximum percentage is 99 which is greater than 80 and among the EC 19 which is greater than 80 then civil
3:35:01
which is greater 88 which is greater than 80 80 and triple 98 which is greater than 80. so like this we can
3:35:09
apply the grouping and our further resultant we can check the conditions by
3:35:15
using the having class we can check the having class right so this is how we can
3:35:21
use this group by class and have in class in select a command
3:35:28
so in the previous session we have discussed about different commands in SQL that is a data definition language
3:35:34
commands data manipulation language commands right so we have taken only one table and we have seen all these
3:35:41
commands how these commands will be executed now we'll see some more queries
3:35:46
by executing multiple tables that means we have discussed about the join operation right so natural join so we'll
3:35:53
see the queries related to multiple tables so how we can get the data from
3:35:59
multiple Tables by using this join operation and also in the previous session we have seen in order to relate
3:36:06
two different relations we have to use a concept called a foreign key so an
3:36:11
attribute which is the primary key of one table will be acting as a foreign
3:36:16
key in another table so we'll see those type of queries right so how we can
3:36:23
relate one table with another table by creating the foreign key and we how we can get the details from the multiple
3:36:29
tables so for this I I would like to take an example of sailors
3:36:39
Sailors table both table
3:36:47
Reserve stable so usually most of the examples you will be receiving these
3:36:52
tables right so we'll also see the queries related to these tables sellers table board table results table so the
3:36:59
concept here is the sailors will reserve a boat
3:37:04
right assailers will reserve a boat so that concept is represented in three
3:37:11
different tables three different tables so in the silence table what are the
3:37:17
attributes what are the attributes so one is the sign and ID cellular ID next
3:37:23
seller name next what is the rating
3:37:28
given for this particular sailor and the age age of the seller so these are the
3:37:35
attributes we are going to take for this sellers right so we'll create the tables
3:37:40
and we we have to insert some values to the tables and then we will find out the queries and coming to the both table
3:37:47
the boat ID is one attribute both name
3:37:53
and color color of the both okay and Reserve
3:37:59
stable coming to the reserves table so we we have to relate these things so
3:38:04
we will have as ID both ID
3:38:09
and the next one is Reserve date
3:38:15
Reserve day so the day which we have reserved the Siler reserved that particular boat right so here we can say
3:38:23
S ID and B ID both are a primary key here in this results One S ID and bid
3:38:31
both comes under the primary key so here it is a primary key and here it is a primary key
3:38:37
and in this results table we are going to take Sid and Port ID as a primary key
3:38:44
as well as these are the foreign keys these are the foreign keys so just now
3:38:49
we have discussed what is a foreign key so the foreign key is the attribute which is a primary key in another
3:38:55
attribute so here you can see primary key Sid is the primary key of silence and bid is a primary key of both so
3:39:02
these two attributes will come say under foreign key in this Reserves
3:39:08
Reserves right so this is a concept and in our
3:39:13
further sessions we are going to discuss about the queries related to these tables so in this session I would like
3:39:20
to use the two commands one is create and insert create and insert so in this
3:39:30
session let us create the tables Silence with the given attributes both with the given attributes and results with the
3:39:36
given attributes making the constraints a primary key and a foreign key after that we'll insert a few rows in Sailors
3:39:44
boards and reserves so in the next session we'll discuss about a few queries related to this one
3:39:50
right so let's move on to the MySQL so that let us create the table for all
3:39:56
these three things and also we'll insert some data for all these three tables so let's move on to the MySQL
3:40:03
Hello friends so just now we have seen the tables and the attributes that we
3:40:09
are supposed to create so first let us create and then we will insert the data so I'm entering into the MySQL command
3:40:16
line interface so we'll go with the CLS so we have already created one database called the YouTube so we are we just
3:40:23
enter into this YouTube and now we are supposed to create a table in this particular database the first table is
3:40:29
assign us so the syntax is create table table name Silas so and here we need to
3:40:36
give the attributes first one is Sid which is of a integer comma the second one is a s name which is of a wire care
3:40:44
with a 30 characters comma and the rating so rating it will also be taken
3:40:50
as an integer comma and H H can also be taken as integer
3:40:55
integer right and primary key to set the primary key primary key will be Sid
3:41:04
so you can observe query okay zero was affected that means the silence table has been created
3:41:09
so you can observe here show tables so in that there is a silos table now
3:41:16
let us create a board stable so create
3:41:23
table table name table name it's booked boats
3:41:29
the first attribute is boat ID which is of integer comma board name
3:41:37
which is of where care with the 20 characters and the third one
3:41:45
color with a data type where Cap this is also we can
3:41:50
take it 20. so these three and here we can consider the primary key
3:42:00
as vote ID both ID right
3:42:08
so the table boards is also been created so you can see show tables
3:42:14
boards right now we have to create a reserves table where the
3:42:21
foreign key will be the Sid and bid so
3:42:26
create table Reserves
3:42:32
Reserves the first one S ID
3:42:37
of integer comma B ID of integer
3:42:47
comma day Reserve day right as a date data
3:42:54
type then primary key
3:43:00
it is a S ID comma B ID so both will be considered as a primary key
3:43:09
next foreign key the foreign key
3:43:16
of Sid which is a references
3:43:23
to Sailors of Sid so Sid is a primary key of sales table so we have to write
3:43:30
like this comma again foreign key
3:43:36
of a boat ID which references which references
3:43:42
Boards of boat ID so just execute this one so zero is
3:43:49
affected so you can observe short tables now we are having a three tables boards reserves and silence boards reserves and
3:43:58
sellers now we are supposed to take insert the values we are supposed to
3:44:03
inside the values so first let us insert the values into silos so uh the command
3:44:11
insert so let us clear the screen system CLS
3:44:17
right so insert into
3:44:24
sailors values
3:44:30
so give the values 1 0 1 is a seller ID let us take some Sandeep as a sailor
3:44:37
name and the rating as a mine and age is some 25
3:44:46
comma go with the second attribute I mean second values one zero two
3:44:51
with the saradi rating with 10
3:44:57
and age with a 27. next third one one zero three
3:45:04
name is Rajesh and
3:45:09
six rating with a 26 age
3:45:15
and go to the 104 ramu
3:45:20
sorry here it is a wire care so we have to place in the quotations ramu and here
3:45:26
it is some eight and twenty five
3:45:31
comma 105 some hurry
3:45:38
and some six with a 28
3:45:45
106 with Shiva
3:45:51
and with a 10 and with the h 30
3:45:58
right so let us execute this one oh
3:46:09
okay so let us once again take it this one
3:46:15
into sellers values
3:46:23
101 Sandeep nine
3:46:29
and 25. yes
3:46:36
values one zero two sarathi
3:46:45
with a 10 and 47 so I have closed one more parenthesis
3:46:50
here so that's why we got an error so uh I'm sorry for the delay so I'll make it
3:46:56
fast so Rajesh and uh six with a 26 age
3:47:04
104 with ramu and 8 and 25
3:47:14
comma one zero five uh with Harry
3:47:20
and 6 comma 28 so let us stop here so what is right now you can observe
3:47:27
select star from sailors so we got some five tables that means a
3:47:33
five sales name now let us go with the boots insert the value for boots right so
3:47:40
insert into table name values so give
3:47:46
the name boat ID there's a one and both name
3:47:51
both one and the color green
3:47:58
come on go to the second one go to go to and the color
3:48:05
red next third one third boot
3:48:10
with a board 3 and color blue
3:48:16
so here we have taken the color is also as that cancer we are supposed to give in
3:48:22
quotations so both four
3:48:29
with a color black right so for us executed so you can
3:48:36
select uh star from boards so we got some
3:48:41
green the four four rows right now we'll insert the data into
3:48:48
reserves table so insert into table name Reserves
3:48:57
values so give the seller's ID result so first
3:49:04
board on the date 2022 so we need to give the four digit year with a two digit month and two
3:49:12
digit day so let us execute this one see you can observe here query okay one row affected yes so let us insert one more
3:49:20
thing so instead Reserves
3:49:25
values 1 0 2 sorry 1 0 2 and make sure that
3:49:31
this vote Eddies and cellular so seller ready should be available in both stable and sellot table because we are
3:49:37
referencing the boot ID and sellers ID as a foreign key right so if you are
3:49:43
trying to give the sales ID or boat ID which is not available in those tables automatically will get the error right
3:49:49
let me show you that one so we have created only one zero one two one zero five so I am trying to give 106 as a
3:49:55
silent side which is not available in sales table right so here I will give some reserves the second boat 2022 uh
3:50:03
one and ten or one right so if you execute we'll get a error cannot add or
3:50:10
update a change row a foreign
3:50:16
and here 106 is that ah foreign key right which referencing the seller's ID
3:50:22
from the sales table so in the sales table we are not having this one zero six so that's why we got an error
3:50:28
otherwise C so we need to give the values which are available in sales ID
3:50:35
and boot ID so 1 0 2 and reserves the first board on the date
3:50:40
2022 some 0 2 and 12. right I am giving some random dates so one one more uh
3:50:48
value has been inserted row has been inserted 103 and
3:50:53
to the fourth boat and give the
3:50:58
2022 and 7 and 11. so one more uh row has been inserted so
3:51:07
one zero four reserves the third board and on the date
3:51:13
two zero two one zero to twenty one so randomly I am giving the dates so once again I'll give
3:51:20
the fifth insertion so one zero five and let us take it as a two second board
3:51:29
second board with the 2022 0 4 and 25.
3:51:35
so this will only be accepted so because so we have given the uh ID right this
3:51:44
one uh seller's ID and the vote ID as a primary keys so let us try to insert one
3:51:50
more row
3:51:55
a Reserves values let us take one zero five with a
3:52:03
one first board and 2022 0 9 10.
3:52:10
so yes so one zero five reserves second board as well as first boat similarly we
3:52:19
will insert one more table I mean one more row insert into
3:52:24
results values 1 0 3 reserves so previously it was just
3:52:33
fourth one so now it reserves the second second board on the date 2021 8 and 20.
3:52:42
yes so we have inserted the rows in all the three tables now you can
3:52:48
observe here so select star from
3:52:54
results see this is the information
3:52:59
reserved the Silas and boat IDs reserve dates and
3:53:05
this is the
3:53:10
silence information and this is the
3:53:18
board's information and Boots information right so in
3:53:24
today's session we have successfully created the three tables with the different attributes and with the
3:53:31
primary key and foreign key now in the next session we will discuss about a few queries which relate one table with
3:53:39
another table because we have created the tables with the primary key and foreignty right so in the previous
3:53:44
sessions we have created only one table so we have not executed the queries related to The
3:53:50
Forum key right only one table we have seen and from the table we have executed the queries now we have created the
3:53:57
three tables and we are supposed to execute the queries which gives the data from all the three tables right so let's
3:54:04
stop here so hope you understood the once again the repetition of creating a
3:54:09
table and insertion of rows but here we have given a foreign key and the prime primary key constraints so in today's
3:54:17
session we'll discuss about a few queries related to the Silas table board stable and resistable so in the previous
3:54:24
session already we have created these three tables and also we have inserted a
3:54:29
few rows in these tables so already in the previous uh videos we have executed
3:54:35
a different queries related to a single table but here we are writing the three tables okay so we need to relate one
3:54:42
table with another table that that will be done with the help of a foreign key right so Silas table which is having the
3:54:49
attributes Sid s name rating and age and about stable there are attributes called
3:54:55
vote ID board plane and a color and there's a stable is having the three attributes Sid bid and day
3:55:03
so in the sales table we have taken Sid as a primary key and board stable bid as a
3:55:09
primary key and in the results table where we have taken Sid and bid as a
3:55:14
primary key and they will be acting as a foreign key right so by using this one
3:55:19
we are relating with the remaining two tables now we will see the queries finding the names of a Silas who
3:55:26
reserved the boot number three so start writing the query here so
3:55:32
says we have to find out the names so write on select
3:55:37
yes name from
3:55:42
Silas from Silas and what we have to get get
3:55:48
the details of sailors who reserved the port number three so the reserved board
3:55:54
members I mean the the reservation details will be available in results table so we have to include that table
3:56:01
also results all the results I mean uh the seller who
3:56:07
reserves the boats will be available in this particular reserves table so so we
3:56:13
have to include that one so select s name from cellular comma Reserves and then then
3:56:20
we need to relate two tables we need to relate these two tables so that two
3:56:25
tables will be regulated with the common attribute what is the common attribute among these two tables Sid so where
3:56:32
sailors dot s i d is equal to Reserves
3:56:40
dot s i b so this will relate these two tables but what we have to get who
3:56:46
results about number three so here we need to write The Logical operator and so both ID is equal to 3 so this will
3:56:56
give the all the seller's name who reserve the board number three so once
3:57:01
more than one sailor can resolve the boat number three right so the name of the scientists who reserve the boat
3:57:07
number three will be getting like this and coming to the second one he found we have to find the names of our sellers
3:57:13
who reserve the red boat so here we are getting the seller's name based upon the color of the boot right so start writing
3:57:20
the query so SQL from so select
3:57:26
s name from where the s name will be
3:57:31
the SM will be in silence so from Silence Sailors comma so reserved reserved means
3:57:40
the reserve details will be in resource table so results
3:57:46
and also you can observe we need to get the details who reserve the red board red means the color of the board so the
3:57:53
color of the board will not be available in results the color will be available in Boots so we have to include this name
3:57:58
also both where we need to link these things first
3:58:03
Reserve sellers reserved so we need to link these two tables Sellers and
3:58:09
Reserve with the common attribute the common attribute is Sid and Sid so right on the same thing
3:58:15
sailors dot sib is equal to
3:58:21
results dot sib so this is nothing but our natural join without any condition based
3:58:28
upon the common attributes we are joining the tables right uh is this enough no because we
3:58:34
need to get the details who reserve the red color boot so we need to find out the color so color is not available in
3:58:41
this reserves table so color is available in both stable so we need to relate these two tables reserves in both
3:58:49
with a common attribute bid so you can write it as n after this one silos dot
3:58:55
Sid and resource dot Sid and results
3:59:00
dot bot IDE is equal to both dot bot ID so now we have related
3:59:09
all these three tables okay we have related all these three tables is this enough no so we need to check for the
3:59:16
red board and and
3:59:21
boards dot color is equal to
3:59:27
boards.color is equal to Red so we are relating the three tables and finding the condition both dot color is equal to
3:59:34
Red so that we'll get the complete seller names who reserves the red color
3:59:39
Port now so hope you understood the second one so first let me write down the queries
3:59:45
here after completion of this one I will execute each and every query in the MySQL command line prompt right so
3:59:53
so next one finding the colors of a boards reserved by Sandeep so write down the query here so first you need to
4:00:01
select color
4:00:10
from the color is available in Boots from boards
4:00:15
so we need to find the boards color boards result so result means results
4:00:21
table so we need to include that one Reserves comma and here we have to
4:00:28
search based upon the main okay given name so this name is available in
4:00:33
sellers so you can observe Sailors so we need to include that one also Sellers and now start uh relate one table with
4:00:41
another table so where where class now relate one table with another table first so the board ID and yeah
4:00:49
I mean the board's table is related to reserves so boards
4:00:55
dot board ID is equal to results
4:01:00
dot board ID and this is not enough because we are we are searching the
4:01:07
details of board with respect to the name name is in Sailors so we need to relate the results with the silos with
4:01:14
the common attribute that is Sid so here we are relating one table with another table with respect to the common
4:01:20
attribute here the common attribute between sales and resources FID so sales dot so here I write results
4:01:29
dot Sid is equal to sine s dot s i d so is this enough no so
4:01:38
we need to search with respect to the name so and silence
4:01:45
dot yes name is equal to give the name so which we
4:01:51
need to compare right so name so this is the query to execute I mean to get the
4:01:57
colors of a boards who reserve the reserves by the given name okay
4:02:03
and the last one finding the names of the sailors who have at least one boat who have at least one boat so that means
4:02:11
simply relate all these three things so that we'll get the details of sailors who Reserves at least one book right
4:02:19
here the query will be SQL so select we need to get the names so s name
4:02:27
from SM will be in silos first write on the sellers so who have at least one more
4:02:34
the result will be in resource table so comma results and we need to check whether it
4:02:42
was reserved with one boat or not so if that relation will be with the help of both ID so boards
4:02:50
boards where relate each terms like silence
4:02:56
dot Sid is equal to results
4:03:02
dot Sid and release the reserve stable with the
4:03:07
boards table results dot board ID is equal to
4:03:14
boards dot board ID so this will give the complete details of assailers who
4:03:21
reserved at least one vote at least one vote right so this is the query we need
4:03:26
to execute this one so here we are relating one table with another table with the help of a common attributes
4:03:33
that we call it as a natural join right so
4:03:38
this is how you need to write the queries now I will implement the same queries in the MySQL I will I will
4:03:46
explain You by showing by executing these queries in MySQL so let's move on to the MySQL command line
4:03:53
Hello friends so just now we have seen the queries some queries related to The
4:03:58
Silence reserves and the boards table now I will show you the execution of those queries so we have seen the
4:04:05
queries for the following questions because finding the names of Silence who reserve the board number three finding
4:04:10
names of signers who reserve the red mode finding colors of boards reserved by Sandeep and finding names of Silas
4:04:15
who reserved the at least one book right now let us open the MySQL command line
4:04:26
so give the password and clear the screen now use the database because we have created the
4:04:32
table siler's reserves and the boards in the YouTube database so use the YouTube
4:04:38
database now let us check with the tables first show tables
4:04:44
yes boards resource and signers let us check whether the rows are available select uh
4:04:49
start so that we'll get all the detail so from boards yes so select star from
4:04:59
sailors yes select star from Reserves
4:05:06
yes so we had a few rows available in all these three tables now let's let's
4:05:11
start executing this one so finding the names of sellers who reserved the boat
4:05:16
number three so for that what we have to do so we need to use the select command to need to retrieve the data so select
4:05:23
instead of using the star we need to get the names of the Siler so s name from so
4:05:29
s name will be available in sales table so I'll write here Sailors comma what we have to do Sailors
4:05:36
who reserved board members so reserved means it will be available in the results table so Reserves
4:05:42
okay and what we get boat number three so Board number will be available in results cellular name will be available
4:05:48
in seller so we need not uh include the boards table also so write down the condition where we need to relate these
4:05:54
two tables with the help of a common attribute so the common attribute among these two tables is Sid so Silas dot Sid
4:06:01
is equal to
4:06:06
results Dot Sid and that's not enough so here we need to
4:06:13
check with the board number three and B ID is equal to 3. so here we can write
4:06:20
it B ID is equal to 3. right
4:06:26
C so the seller name who reserved the boat
4:06:31
ID 3 is rum let us check here so first let us check here
4:06:37
boat ID3 so white ID 3 in the reserves table you can check because the the link
4:06:43
will be in the resource table byte so in the results table the board the boat ID is reserved by the 104 Sid so what's the
4:06:51
name of one zero four Sid it's a rub right so hope you understood this one so
4:06:57
we are just relating these two tables with the help of a common attribute so aside is a common attribute so sales dot
4:07:03
a side is equal to reverse.id and bid is equal to 3. so in the reserve table bid is equal to 3 is reserved on 21st to
4:07:11
February 2021 by seller id104 so now we can get the name from this particular
4:07:17
seller ID so 104 Silo already name is ROM so the name the seller who reserves
4:07:24
the board number three is the problem so I hope you understood this one right next the finding names of the sellers
4:07:31
who reserve the red Port the same thing so select s name from Sailors and here
4:07:39
we we need to get the results who reserves that boot so also include the
4:07:44
reserves comma and here we are checking with the help of a color so color will be in the
4:07:50
board so we need to include this boards also boards where link the thing with
4:07:56
the common attributes so Sailors dot Sid is equal to
4:08:01
reserves dot Sid and also we need to relate the results
4:08:07
table with boards table so resource dot bid is equal to
4:08:15
boards.bid and see both dot color is
4:08:20
equal to red see we got two names who reserves the
4:08:27
red color ports okay let us check whether it is right or wrong
4:08:35
all right see color boards color red okay so red
4:08:42
color both ID is two red color is red color
4:08:48
both ID is two so we need to find the sailors who reserves the second boot so
4:08:54
one zero three and one zero five so we need to get the names of one zero three
4:08:59
and one zero five because both have reserved the same number both which is of red color so one zero three and one
4:09:06
zero five so we can get one zero three as Rajesh and one zero five as Hari so we got the result as Rajesh and Hari
4:09:15
so once again I'm saying so we need to get the color red who reserves the color red so the color red color board ID is
4:09:22
2. okay red color both ID is two now we need to find in the reserves table who
4:09:28
which signers have reserved this board too so one zero three reserves about two and one zero five results the both two
4:09:34
so one zero three name is Rajesh and one zero five name is so we got these two names so hope you understood this query
4:09:42
right next one so finding the color of boards reserved by Sandeep now we need
4:09:48
to find out the color with respect to the name cell as name now select color
4:09:55
from boards okay comma results
4:10:04
reserves comma sellers because we are having up all the three things okay
4:10:09
color is in boards reserves that means a relation will be in results and name
4:10:15
will be in the silence where relate the things so boards.bid is equal to reserves Dot
4:10:24
bid and Reserves
4:10:29
dot Sid is equal to Sailors dot Sid and sailors dot s name
4:10:37
so we need to check this once s name is equal to
4:10:43
Sunday so there is a green color both so the
4:10:49
Sailor Sandeep have reserved green color booked let us check so go to the sun
4:10:54
leap so Sandeep Sid is one zero one so let us check 101 both I mean which which
4:11:01
boot ID reserved by the one zero one so one so one means both one is in color
4:11:07
green hope you understood see so we need to check the color of boards who reserved
4:11:14
by the name Sandeep first go to the seller's name Sandeep Sandeep is the name of a Seiler who is having the
4:11:21
sid101 now go to the results table and find out the corresponding boat ID of 1
4:11:27
0 1 so 1 0 1 Resource of boat ID one so go to the board table board ID 1 is
4:11:34
having the color green so we got the green color
4:11:39
so hope you understood this one right yes so next the last one the finding
4:11:46
names of sellers who have reserved at least one booth finding names of sellers who have
4:11:52
reserved at least one book so simply we can relate the tables so that we'll get the details so select so
4:11:59
what we have to get names s name from
4:12:04
sellers so relation Reserves
4:12:11
boards where Sailors dot Sid is equal to reserves dot
4:12:17
Sid and Reserves dot bid is equal to
4:12:25
gods.bid so simply we'll get all the details of an sellers who reserved at
4:12:32
least one book now you can observe Sandeep one two three four five six and
4:12:37
seven are there
4:12:43
yes see Rajesh having a two boats and Harry is having I mean the same person
4:12:51
reserve the two different ports so at least one you can observe here so in the
4:12:56
sellers table in the sales table uh we are having five names Sandeep Rajesh
4:13:03
ramu and Hari right one zero one one zero two one zero three one zero four and one zero five now you can observe so
4:13:10
reserves table so one zero one so that means already suddenly reserve one boot
4:13:16
one zero two results one more one zero three there's also two votes right one
4:13:21
zero four one more one zero five two boats so that means you can observe all the
4:13:27
sellers reserved at least one book right now let us say we'll remove the one zero
4:13:33
four okay we'll remove the one zero four so delete
4:13:38
from results where
4:13:44
where [Music] um what is this one one zero four no sorry
4:13:50
where Sid is equal to one zero four
4:13:55
so delete it now you can check select star from Reserves
4:14:00
so you can observe here there is Row one zero four okay one zero four one zero
4:14:06
four means you can observe one zero four is nothing but rub so ramu have not reserved any both here now now execute
4:14:12
the same query so getting all the sailors name would have reserved at least one vote now you can
4:14:19
see ramu name will not be available here because ramu doesn't Reserve I mean is
4:14:25
have not resolved any board so Sandeep Rajesh and Harry
4:14:32
let us so we can use a distinct
4:14:40
we can use a word distinct so that will not get any
4:14:46
repetition distinct means it will remove the duplications so these are the four
4:14:51
four names who have reserved at least one boat at least one boat
4:14:57
right so hope you understood these four queries right so I have explained you about the
4:15:03
theory and we have I have shown you about executing these queries also
4:15:10
so what is the view so here A View is a virtual table
4:15:17
it is a virtual table
4:15:22
constructed from existing tables
4:15:27
from existing table so this existing table can be from the single table or
4:15:36
from multiple tables so we can create a view we can create a view
4:15:42
from a single table or a multiple way tables with required attributes
4:15:50
required attributes attributes that means
4:15:57
it doesn't mean all the attributes should be there right so we can create a view with the
4:16:04
required attributes from a single table or a multiple tables right so
4:16:09
we if you modify any data in this view that will be reflected on the original
4:16:15
table and if you do any modification on the original table that will be reflected on this view so that means
4:16:22
these two are correlated right so changes
4:16:28
made in table
4:16:36
reflects on View and also
4:16:42
changes made
4:16:48
in a view reflex
4:16:53
on table the corresponding table right but the view is a
4:17:00
virtual table so what is the purpose of creating these views so we are having
4:17:06
some tables so we can access the data with from this particular table so why
4:17:12
we are going with the views see an example
4:17:18
so take the college database so College will be having
4:17:23
different departments let it be some CAC Department some ECE Department
4:17:30
some triply Department and mechanical Department
4:17:37
civil Department right so there will be a student details
4:17:42
which will be maintained by the college as well as in the CSE right so
4:17:49
this college will be this database will be having all the details of all the
4:17:55
branches okay all the branches details all the students details will be maintained in the main database called
4:18:02
College database and actually the CSC student should be maintained here
4:18:07
similarly easy student details should be maintained here triple students mechanical students and the Civil
4:18:13
students so if there are any modifications done on some CSE students
4:18:18
and automatically that should be reflected on the college dataway that means original database because here
4:18:24
also the college will also maintain the database of all the Departments right
4:18:30
and individually the department will maintain the student details so hope you understood this one so if
4:18:36
you maintain two different tables one is for college one is for CSE and one use for AC one is for Tripoli mechanical and
4:18:43
civil there are different tables right so College table
4:18:51
CAC table ECE table and so on so if if all these are the
4:18:57
individual tables if you made any change in the CSC table that should be
4:19:03
reflected on the college that that cannot be done automatically so we need to update the CSC student details in the
4:19:10
college database also that means we have doing the same work multiple times so
4:19:15
instead of doing that we'll create a database for college and we'll create a
4:19:21
view for CAC View
4:19:26
first ECE view for Tripoli and a view for
4:19:32
mechanical and view first scene so that these are the virtual tables distributed
4:19:38
to each and every department so that if there are any changes made in this particular Department that will
4:19:44
automatically reflect on the main table so this is the main advantage and the
4:19:50
purpose why we are creating these views so that's why we are extracting some
4:19:57
portion of data and representing that portion in a table so that is called a
4:20:02
view so that's why view is a virtual table constructed
4:20:07
from existing table that may be either or single or a multiple with the required attributes so it doesn't mean
4:20:14
that we have to get all the attributes right so so all the student details from the main
4:20:20
database will be maintained in the CSE view all the ESC students will be maintained in the EC view typically view
4:20:27
mechanical review and a civil view so that the reflex if there are any changes made in this original table that will
4:20:33
also be reflected on this individual tables because these are all the views
4:20:39
right so that is the main advantage why we are not creating multiple tables we
4:20:44
are creating simply a views right so changes made in table reflects on view
4:20:49
and changes made in viewer effects on the table right so this is the purpose of views
4:20:56
now we'll see the syntax now and then I will show you the execution part in the
4:21:02
MySQL right so we'll create a one table and we'll
4:21:09
create different views and also I will show you the updations if the updation
4:21:15
is done on the table whether they are reflecting on The View and if you are doing any updations in the view whether
4:21:20
they are reflecting on the table so that we will see practically in the MySQL now I will show you the
4:21:27
Syntax for creating The View
4:21:33
syntax so SQL prompt
4:21:39
so create
4:21:45
view give the view name similar to our table name we have to give the view name
4:21:51
as write down the query here select
4:21:58
column name one comma column name two
4:22:06
Etc from table 1 comma
4:22:14
table two so if it is only one table we can write here only one table if you
4:22:19
want to extract the portion from multiple tables we can get these multiple tables so let us take with the single table
4:22:27
from table 1 where tried on the condition
4:22:37
so this is the syntax to create a view from a single table so if you go with
4:22:43
the multiple tables so use the same syntax
4:22:49
create View
4:22:55
name as select
4:23:02
column name one column name two
4:23:11
Etc from table one
4:23:18
comma table 2 Etc where
4:23:23
so give the join operation right
4:23:29
so table one dot column name one is equal to
4:23:36
table 2 dot column name column name
4:23:43
and condition so whatever the condition we have to write that condition should be
4:23:50
written here so this is for creating a view of from the single table and this is a syntax to create a view from
4:23:58
multiple tables right so now let me stop here so let us move on to the MySQL I
4:24:05
will execute each and every query and I will explain you everything right so let's move on to the MySQL
4:24:11
Hello friends so just now we have seen the syntax and complete introduction about the views in dbms
4:24:18
now uh let me explain You by executing this views in MySQL so let me open the MySQL
4:24:25
command line so I'm getting the password so yes so we we are creating the tables
4:24:34
in a one database called a YouTube so let me go to YouTube so now we are in YouTube
4:24:41
database so let me check for the tables so here we are having a different tables
4:24:46
and here you can see there is a table called student right so
4:24:55
select star from student so here we are having a total seven
4:25:02
students with the different branches so CAC EC civil right now
4:25:08
uh let us create a view for individual departments okay so because so if if you
4:25:16
if individual departments maintain this different tables if one student details
4:25:21
has been changed that should be reflected in all the tables right so in order to avoid that thing so we'll we
4:25:27
are creating a view for this particular master table to require with the
4:25:33
required audio attributes right so let me create individual tables for
4:25:38
individual groups right so that means creating a view so the syntax create
4:25:48
View name view name let us take it as a CSC underscore
4:25:55
students students as as give the query so select star from
4:26:04
student where branch is equal to CSE
4:26:12
so query okay zero rows affected now let us check with the tables so show tables
4:26:18
here you got the name CAC underscore student now select a star from CSC
4:26:24
underscore students see there are three students
4:26:32
in the CSC Department so let us check whether there are only three or uh more than three see
4:26:40
so there are three one two and three three students
4:26:45
now create a one more View
4:26:51
as a ECE students ECE students as give the query select
4:26:59
so you can select a complete table or I mean all the attributes or required attributes I am taking all the
4:27:05
attributes select star from student so from this table I'm extracting
4:27:14
where branch is equal to easy I'm extracting only the EC students so it
4:27:20
was also created now let us check with that one show tables
4:27:26
so select star from ECE underscore students
4:27:33
so there are three students yes let us check with the EC civil also
4:27:39
okay let us create a civil also create a view as view name so civil students
4:27:50
as select star from student
4:27:55
where branch is equal to civil
4:28:07
I think wrong name yes let us check once again
4:28:14
so select star from student
4:28:21
so we yes we have given civil itself right once again we'll check so
4:28:28
as select star from
4:28:34
student where branch is equal to
4:28:42
7. once again I'm getting the error
4:28:54
sorry syntax wrong create View
4:28:59
view name should not give you as right view view name
4:29:05
as here we need to give you a query select star from student where branch is equal to
4:29:13
civil right now we'll get yes so you can observe show
4:29:19
tables see we are getting different so the master table is a student and from
4:29:26
this student the views are civil student CSC student easy student now let's check
4:29:33
so select star from
4:29:40
student right now check I just want to update the
4:29:46
percentage of a civil student here in the master table so which query so update
4:29:52
student set
4:29:57
percentage is equal to 90
4:30:04
where Branch or simply we can say Sid is equal
4:30:11
to one zero five so one row affected now you can observe
4:30:16
so select star from student see now we got a 90 for civil previously
4:30:23
it was 88 okay now we'll check with the Civil student so select
4:30:28
star from civil underscore students
4:30:35
so you got Knight here so when we are updating the attribute from the master
4:30:41
table it should be reflected on The View also so here civil student is the view
4:30:46
so we are changing the value in the master table it was reflected on The View now let us change the view
4:30:54
and we'll check whether it was reflected in the master table so we'll update the same thing so update
4:31:02
civil student so view name civil students
4:31:10
set percentage is equal to
4:31:15
95 where a side is equal to
4:31:22
one zero five so it was changed in the view now we will
4:31:29
see the L attributes of civil students star from
4:31:35
civil students see here 95 previously it was 90 now we
4:31:41
are updated here now we'll check in the master table right so
4:31:46
select star from student so here in the master table it was
4:31:53
reflected so if you do any changes in the uh view it will be reflected on the
4:31:58
master table and if you are doing any manipulations in the master table it
4:32:03
will be reflected on The View right so this is the
4:32:09
view which we have created from the single table now we'll check how to
4:32:14
create a view from multiple tables now let us check so
4:32:20
tables so here we are having some contact and we are having some student
4:32:25
so we know the details of a student table what are the available things so
4:32:32
we can also have this one see select star from contact
4:32:37
so this is a contact only three student contacts now we'll create a view by
4:32:43
combining these two things okay see what we have to do first we
4:32:48
need to create a view name so student contact student contact
4:32:58
as now we have to write the query so select
4:33:05
student dot Sid give the attributes which we required in
4:33:11
the tube so student dot s name and see contact dot email so I'll go
4:33:21
with only these things these three these four things contact Dot mobile
4:33:27
all right from right on the tables so one is the student table one is a
4:33:32
contact table where Q the condition so we have to join this one so the join will be with
4:33:39
respect to the common attribute so where student
4:33:46
Dot Sid is equal to
4:33:51
contact dot aside so this is the natural join so equal
4:33:57
attribute see query okay 0 0 is affected now you can observe the short tables
4:34:07
so here you can observe student contact so now you can go with
4:34:13
this one select star from student contact so we are having only three
4:34:20
terms one zero one one zero two one zero six with the email and mobile
4:34:25
so if you change name here automatically that will be reflected on the Master tab
4:34:32
right so hope you understood this one so how can we respond and the next one
4:34:38
is how can we delete the views we can also delete the views those are similar to our table so drop command is used to
4:34:44
delete the table so if you use a drop table
4:34:50
a drop table student contact so we'll get an error so if you use a
4:34:57
drop View student
4:35:02
contact see query okay zeros was affected If You observe the tables list the student
4:35:09
contact is not available because the Syntax for deleting the tab view is a
4:35:14
prop view instead of table we have to give the view that's the only difference
4:35:20
right so hope you understood this views concept how can what is the purpose of use and how can we create a view from
4:35:28
single table how can we create a view for from the multiple tables and how can we delete the views
4:35:34
so I'll stop here and if you are having any doubts regarding this one please post your doubts in the comment section
4:35:40
definitely I will try to clarify all your doubts and if you really enjoyed my session like my session share my session with your friends and don't forget to
4:35:46
subscribe to our Channel thanks for watching thank you very much