Transcript for:
Introduction to Structured Query Language

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