Let us start from the very basics and first of all try to understand what is a need behind studying MySQL or what exactly is MySQL and what kind of solution does it offers to me. So if you just see the first point, I have written that MySQL is the most popular open source relational SQL database management system. So what does this mean? If you just take the first line, then I have said that MySQL is a database. So my first thing is that MySQL is what? MySQL is a kind of database. And then so database is primarily intended for saving some kind of data that is associated. So it can be any kind of data like data for an organization, suppose data for an online departmental store, suppose data for a mobile vendor, all these kinds of data you can use a database for storing all these kind of data. But I would like to ask you a question that why cannot we use files for storing data. So and even before that, let me first of all tell you suppose you are writing one program, suppose you must be aware of C programming language and if you are aware of C programming language, then you declare the main method and inside the main method you declare some variables. Suppose you declare a name, suppose int, suppose, suppose ID, and then suppose you declare the name, suppose I say string name, these variables you declare. But these variables, what is the lifetime of these variables that you are created in your program, they are just created till the program runs. So for example, if this is your RAM, then these variables will be allocated inside your RAM. But once the program finished its execution, these variables, they will be, you can no longer access these variables. So that means I need some kind of permanent storage in which I can save my data. And for that purpose, I need a secondary storage. So always remember that data, you always save data in some kind of secondary storage. So RAM, this is your RAM. And this is the primary memory, we do not use RAM for storing any kind of data that we frequently require by because RAM data is not permanent, it is data just comes and goes, it lives for the lifetime of the program till the program is executing. But if you want to actually save data and retrieve that data for some later use, then you use, then you use your secondary storage. So your secondary storage means your hard drive. So in your hard drive, you can save some data. All right, so your hard disk or, or probably you call this as a secondary storage. So this is my secondary storage in which I want to save some data. Now what I can, I can do, I suppose I write, I clear all this. So we have seen that RAM is not, RAM is not a solution for saving data because it is temporary. But we can have, we can have our hard disk and hard disk can be used for saving some kind of data. Now I can do something like this. I can save data in my secondary storage in the form of files. So files can also save some kind of data. But why do not we use files and why, why we use database management system? Can you tell me the answer? Why file is not a very efficient storage efficient solution for saving data? See, you can store some amount of data in files. For example, you can store your contacts, you can store your friend's birthday list. But if the data becomes very large, then file handling the data through files, it becomes very difficult and tedious job. And data also suffers from some kind of inconsistency. For example, if there are two files and two files, suppose contain the entry for the same employee, suppose there is one employee, and both of these files contain the entry for the same employee. Suppose there is one employee even and his age is 21. And here also suppose the employee is even and his age is 21. And now what happens is that suppose one year passes away and employee age has to increase by one year. So what it may happen that I can increase the salary of, I can increase the age of this employee in this file, but I do not increase the age of employee in this file. So in this way, both of these files can become what both of these files can become inconsistent. That is why files storing data in files, it is not very, it is not. So that means there is a problem that is associated with multiple updates in files when we store data in files. Like, if the similar kind of data is present at multiple locations, and if one of the location gets updated, then it may happen that the other locations may not get updated. And that may result in data inconsistency. Plus also files you cannot produce data in files from from files in very representable format. If you want some aggregate data, if you want to produce some kind of reports from your data, which you have saved in your files, then it is very difficult to produce all these kinds of report aggregated reports from files, you'll have to write very complex programming logic for that. And that is generally not very efficient. And if you delete accidentally delete some data, and like if you want to search for some kind of data, like if file is very big, and it contains many of the records and you want to search for some kind of data, then you'll have to traverse through the whole data and then you can then you can find that data. So all these all these things indicate that files are not very efficient mechanism for storing data. And so we need a database and we do not need a database, we need a whole database management system. That means a whole software that can offer a solution for our data related problems. So for that purpose, we have databases and there are many databases that are in the market, many popular relational databases that are present in the market. For example, you have Oracle database, you have SQL Server, that is also meant for storing data. And then of course, we'll be studying MySQL. So MySQL is also there. And then PostgreSQL, all these are, all these are the different kinds of databases that you use. Plus, there are also some NoSQL solution like support, say for example, MongoDB and Redis. And these are all your NoSQL database. So NoSQL means doesn't mean that they lack SQL statement. NoSQL means that not only SQL. So of course, our motive over here is not to study NoSQL databases. Our motive here is to study relational databases. And what is the prime identification of relational database? The prime identification of a relational database is that data is always stored in the form of tables. So here, for example, suppose you have a server and inside the server, suppose you have a database. So inside the database, data will be always stored in form of what? In form of a table. So what exactly is a table? A table is an intersection of rows and columns. So how can we say that tables can be used to represent any kind of data? See, for example, suppose I have, I have one real word entity. And I want suppose the real word entity is what I suppose the real one entity is student. So you can identify the fields that are associated with student. For example, every student will have ID, every student will have a name, every student will have marks. And suppose every student will have what? Every student will have a subject. So then what I can do is that I can design a table for that. And how will I design tables? Suppose I say one student is one, his name is Amit. His marks are suppose 97. And his subject is say, for example, Java. Then I can have one more student suppose two, and his name is suppose, and his marks are say suppose 85. And his subject is say, for example, C++. Then I can have one more student, for example, three, and his name is say suppose, Nathan, and his marks are 91. And his subject is say, suppose, no chase. So no chase is a very popular server side tool, written in JavaScript. Of course, we are not going to see this in this video. But still just seeing this. So this is what this becomes my see this becomes my table where data is stored in the intersection of rows and columns. Okay, so this is what is the table. So each row is representing what? Each row here is representing one instance of students see this row is representing Amit, this is representing web and this is representing what Nathan. And each column is representing one other properties of students. For example, this is representing ID, this is representing the name, this is representing subject, this column and this column is representing what? This column is representing the subject of the student. So that is where if you have intersection of rows and columns, then you can model any kind of real-world entities in that table. So always remember that a relational database means a kind of database in which data is stored in the form of tables. Clear? So let us continue our discussion and I will just clear all these fields. And so we discussed that we are discussing about relational database and there are a lot many of relational database systems that are available in the market, for example Oracle is one of your database. Then there is one database SQL server. This is also a relational database. Relational database means in all these databases, data will be stored in the form of tables. Then you have what? Then you have mySQL. So these are all relational databases. Then what is SQL? So SQL, what can you tell me? What does SQL mean? Many people carry this confusion that SQL is also a database. But SQL stands for structured query language. So structured query language is a language that you can use to communicate with these databases. So you can use SQL to communicate with Oracle. You can use SQL to communicate with SQL server and you can use SQL to communicate with bot. You can use SQL to communicate with MySQL as well. So remember that SQL is a structured query language. It is a language that is used to communicate with databases. So it is a 4th generation language, very user friendly language and it is almost written in the way we generally communicate. Like if we go on to fetch some data. So naturally we will say select this from this and get me this out from this. So it is almost very user friendly language that is your SQL language. So always remember SQL stands for structured query language. So it is a language that is used for communicating with the database or it is a language that most of the relational databases understand. So with this I will just end this video. In the next video we will of course continue our discussion of MySQL and we will see in the next video how can we connect to the MySQL database, what is MySQL server and what is MySQL client. So what I will do is that I will just open my database. It is asking for password and password is I am not given any password. So what are the current options of suppose I say what are the databases that are available. So I will I just you know the names of all the databases. It is not giving any. What I will do first of all I will select a database. Suppose it is a publisher database. Then if I say select database then what is the current database. It is the publisher database. So inside my publisher database I have one table. This is the employee table. This is the employees table. And if I say select star from employees. If I say select star from the employees table then you get this data. Now there is another table. I will just show you the structure of these table. Department stable. This is the department stable. And if I say select star from departments then you get this data. So let us first of all study the employee table. This is my employee table. So what all do you have in the employee table. You have the employee ID. You have the employee name. You have the department ID. You have the salary commission designation the manager ID and the gender of the employee. For example if I say select star from employees. Since this is the data. Suppose there is one employee. His name is suppose Sonali. And Sonali is having the employee ID as nine. Her name is Sonali. She works in department number 20. Her salary is 40,000. She gets the commission per month as 3500. Her designation in the organization is as a developer. And her manager ID is two. What does this mean? Her manager ID is two. That means her manager is an employee with the ID two. That means her manager is within. And her gender is female. Similarly this is the data about all the employees. So this is my employee table. And then when I say select star from the department stable. So you have one department ID. Suppose department ID 10 and the department name is sales. And suppose the city in which this department is located is the Mumbai. So department number 20 is in Bangalore. Department number 30 is in Chennai. So this is my suppose this is my table. And these are the two tables which I will be mainly working on. And so you can consider that these two tables belong to the same. These two tables represent one single organization unit. For example I just want to say that if for suppose say suppose the department ID of Nikhil is 10. Then that means Nikhil is working. If you go to the department stable and follow my mouse. And Nikhil is working in the sales department. I will just explain it here. I will just take a pen. And suppose if you say about Nikhil. Suppose this is Nikhil. And so Nikhil is working in department number. And this is so this is the case. Now let us study about the select statement. So select statement is used to know it is used to select some data from the table. So the basic you know basic format of a select statement is as follows. Suppose I will open this notepad. And if you want to write a select statement. Then you write something like this. Column name 1 comma suppose column name 2. And so on from suppose your table from the table. For example here in the employees table I have the following columns. Suppose employee name department ID. Suppose I just want to get the employee ID name and department ID of all the employees. Then what query will I like? I will write something like this. E ID E name comma dpt ID from push table from the employees table. So this is my data. I just got the employee ID name and department ID. So I just fetched a part of the table. See the table is having the following you know. It is having if you just count the number of columns there are exactly eight columns. But I just fetched out of them you know the you know the name this the ID the name and the department ID of every employee. Suppose I want the salary and name of every employee. Then what query will I like? I will write something like this. Suppose salary from which table it is the employee table. So this is what I get. Now so if you are studying the select statement the very first thing that has to be kept in mind. The select statement works row by row. Okay what does this mean? The select statement works row by row. See this is you know it is not you know any kind of formal thing or you know this kind of things definitions you will not find in books. Standard books but this is what I've learned you know working in my SQL. This is all what I've learned. So select statement works row by row. If you want to understand what is what does this mean. Then let me go to paint and I know just let me explain it. For example suppose I have employee stable and this is my suppose this is my employee stable okay. Please ignore the font and everything. And suppose okay then this is you know some kind of your employee stable. And I'll like something over here also. Suppose there is one employee and I will give random names. Suppose ABC there is two. Third employee suppose XYZ and LNN. Suppose he is working in department number 10. He is working in department number 10. He is working in department number 20. And then I say suppose his sally suppose 2000. His sally suppose XYZ sally suppose 3000. And suppose elements sally say about about 1000. So this is suppose this is my table. Now if I say I am suppose select this is my employee stable and select suppose so these are your columns. Suppose employee ID then this is your name and then this is your department ID and this is your sally. So if I say select sally suppose select sally from which table. From your employee stable okay. This is your table. So what it will do it will go from one row to another. First it will go to this row. It will get the sally. Then it will go to this row and it will get the sally. And then it will go to this row and it will get the sally okay. So if I say suppose select sally suppose sally from the employee stable. Then it will go to each row and get the sally you know of every employee from that row. But if I write suppose something like this select it from employees. Then can you guess what will be the output of this query if I say select 8 from employees. See if you want to know about the output of this query you just remember this thing. Select statement works row by row okay. Select statement works row by row. So if I write something like this then what you will get this output. See 10 rows are being selected but from each row it is going to select what it is selecting 8. And this is what I precisely said my dear friends if you go over here this is what I said. The select statement so if I write select 8 from employees. If I say select 8 from employees. Then it will go to first this row and select what 8. It will go to this row and then you know although 8 is not a column of this you know table. But still it is going to select that thing. Suppose I write select suppose Krishna from employees. If I say something like then it will go to each row and it will get what it will get employees. But if I say something like this these queries do not make sense. But if I say ename comma salary from which table from the employees table then what it will mean. It will mean that it will get go to each row and it will get the id name and the corresponding salary. And now this is the case with you know the department table also select star from and so for what is star. So star is you know it's a kind of wild card operator that is used to select all the you know all the columns of your table okay. So if I say select star from the employees table if I say something like this then the whole details of the employees table will be selected. So this is you know this is the select statement and always remember that select statement works row by row okay. It is going to go it is going to going to each row and then fetching the you know desired column. For example if you see select star from the department table this is my department table and suppose I say select dpt id comma dename from which table department table okay. Suppose this then it will go to each row and fetch the department id and the department name. So this is the way you know the select clause. In this video we will study about dml commands in my SQL. So you can just go and open it. So dml stands for data manipulation language and to you always remember keep this thing in mind that whenever you are asking about order you know whenever dml commands are used they always work on rows okay.  So without wasting any time I have one table and this is the student table. So student table has student id student name age and subject. And now my task is to insert a row inside the student table. So insert is a dml command. Why it is a dml command? Because it is inserting one row inside the student table and this I have already told you that dml commands act on rows. So what will I write insert into and then you write the name of the table. So in my case the name of the table is student and then you give the name you know and then you give the column names.  These are the columns and now what values I want to insert? I want to insert one suppose amit and suppose age is 21 and subject is suppose his subject is Java. So I have inserted one row and now once when you say select a star from student table then you will get this one row. It is also possible to change the order of columns. For example I do not write like this. First I say something like this for example age and then I say sname and then subject followed by the subject all right. So how now you are going to insert it? So now you are going you the the way you have given columns the order in which you have you know you have given the list of columns in that same particular order you have to perform insertion. So here first you have to mention what first you have to mention the age and now you have to mention what and now you have to mention the name. Suppose age is 22 and then suppose name is Vishal and subject is suppose compilers and now if you do select star from student. So you will get two rows. So it is also possible to change you know the order of columns and the way you change the order of columns inside the insert query you have to supply the columns in you know the values in those fashion in that particular order. And there is a default ordering you know if I say B, S, C, S, Q, D and D. So default ordering is that first first you have to you know default by in default way first SID has to be entered then sname has to be entered and then age and then subject. So if you perform you know if you perform insertion operation in default in default ordering way then you do not have to give the you know the column list. So I can directly insert how I can directly insert I do not have to if if I do like this. See I am performing a default you know default ordering of insert first SID is coming then name is coming then age is coming and then subject is coming and that is what is exactly over here. First SID then name and then age and then subject. In this case you do not need to give the column list. So I say three and suppose the the name of the student is worth it. Suppose our age is 24 and subject is say suppose add up analysis and design of algorithms. So it will be successfully inserted but if I change the default ordering and then try to insert it for example if I if I change the age if I change first of all I give the age and then I give you know then I give the name suppose Shah Rukh suppose I give this name and suppose I set the age to 25 and suppose suppose id to be 5 and subject to be suppose TOC that is theory of computation. If I write like this it will not it will not insert because column count doesn't match the value of count at row one. So in that case now you will have to supply you know the order in which you are performing the insert. So how you are performing the insert first you are giving the SID and then you are giving what then you are giving age and then you are giving what name and then finally you are giving what subject. So if you break this default ordering then you have to then you have to give your column list. SID, age, name and subject should get inserted SID age then I am giving the age and then I am giving the name and then I am giving the subject okay. I think there is a insert into student values SID then I am giving the age then I am giving the name okay. So I forgot to add a comma here so all right. So now it will get inserted okay. So this is how you work with insert query and now we will talk about updates. So this is my table okay suppose I want to do one thing I want to update this employee his name is Rithik and his age is 24 I want to set his age equal to 42 suppose I do I want to do like this then what where will I issue I will say update the table name then what is the name of the table student and then I say set set what age age equal to new updated age is say suppose 42 and where you know where what where SID equal to what SID equal to 3. See this employee Rithik has an SID of what it has an SID it has a student ID of 3. So that is why when I say SID equal to 3 this row will be selected then age will be updated to what age will be updated to 42. So if you say again say select start from student in the new updated age will come become what it will become equal to 42 okay. Suppose I can do suppose I want to this employee Shahrukh suppose I want to change his age to 26 his age his age is 25 I want to change him to 26 and I want to set the subject to be say suppose operating system then what will I do I will say update update which table update the student table update student set what age equal to 26 comma all right. So if you have if you are forming multiple updates if you are performing updates on many columns then you have to give a comma separated you know you have to separate every column by a comma and then age equal to 26 and then I will say subject equal to suppose operating system OS where I can write S name equal to what Shahrukh okay if I try to run this okay so it is working fine and you are now you are getting you know Shahrukh age has become 26 and subject has become what it has become OS but I can also write the previous query you know like writing like this for example yeah over here I do not write I write SID equal to what SID equal to five I can also write this in both of the cases this row will be selected see this row will be selected all right but it is always a better approach to catch a row by the by the primary key or you know the id column in this case the id column is sid so it is always better to to you know when you are performing you know when you are writing where then it is always better to write the id why because there can be two employees whose name can be Shahrukh but they will have different id so it this is always better approach to you know whenever you are performing updates in the where class you you have to perform on specific rows and you you perform update you know you catch those rows by their id's all right so this is you know how you work with update and then finally delete so delete also works the same way suppose I want to delete this row that is Shahrukh so what will I say delete from which table delete from student table where what SID equal to what SID equal to five see in place of SID equal to five I can also say s name equal to Shahrukh why because this is because I want to select this row I want to select this row but it is always you know as I told it is always advisable to write the you know to catch hold of the row by the primary keys so here I do it so this row will be deleted and now once when I say select star from student this row has me deleted all right so this is how you work with delete and now if you want to delete the suppose I write delete from student all right a quiz time for you suppose I write delete from student they are one equal to two okay what will happen why you know okay this is a you know this is a kind of specific query that works in oracle it's not working in my sequel all right suppose I only write delete from my delete from student then what will happen can you guess see this is a very dangerous query if you write if you perform delete operation without using the way without any without giving any wear condition the whole records of the table will be deleted so if I write delete from student and I just give an enter then what will happen you know if I write select star from the student table if I write select star from student table then all the rows of the table have been deleted so you have to be very cautious when you when you write delete queries why because if you if you just delete then if you just delete by giving without giving any wear condition then the whole table will be deleted okay so this way we perform insert update and delete operations on my sequel in this video we will study about dml commands in my sequel so you can just go and open it so dml stands for data manipulation language and to you always remember keep this thing in mind that whenever you are asking about order you know whenever dml commands are used they always work on rows okay they they perform some kind of operation on rows of a table and insert update and delete are the examples of dml commands so without wasting any time I have one table and this is the student table so student table has student ID student name age and subject and now my task is to insert a row inside the student table so insert is a dml command why it is a dml command because it is inserting one row inside the student table and this I have already told you that dml commands act on rows so what will I write insert into and then you write the name of the table so in my case the name of the table is student and then you give the name you know and then you give the column names so I will say s ID and then I will say s name then I will say age then I will say subject these are the columns and now what values I want to insert I want to insert one suppose amit and suppose age is 21 and subject is say suppose his subject is java all right so I have inserted one row and now once when you say select a star from student table then you will get this one row it is also possible to change the order of columns for example I do not write like this first I say something like this for example age and then I say s name and then subject followed by the subject all right so how now you are going to insert it so now you are going you the way you have given columns the order in which you have you know you have given the list of columns in that same particular order you have to perform insertion so here first you have to mention what first you have to mention the age and now you have to mention what and now you have to mention the name suppose age is 22 and then suppose name is Vishal and subject is suppose come compilers and now if you do select star from student so you will get two rows so it is also possible to change you know the order of columns and the way you change the order of columns inside the insert query you have to supply the columns in you know the values in those fashion in that particular order and there is a default ordering you know if I say b s c s q d n d so default ordering is that first first you have to you know default by default where first s id has to be entered then s name has to be entered and then age and then subject so if you perform you know if you perform insertion operation in default in default ordering way then you do not have to give the you know the column list so I can directly insert how I can directly insert I do not have to if if I do like this see I am performing a default you know default ordering of insert first s id is coming then name is coming then age is coming and then subject is coming and that is what is exactly over here first s id then name and then age and then subject in this case you do not need to give the column list so I say three and suppose the the name of the student is worth it suppose our age is 24 and subject is say suppose add up analysis and design of algorithms so it will be successfully inserted but if I change the default ordering and then try to insert it for example if I if I change the age if I change first of all I give the age and then I give you know then I give the name suppose Shah Rukh suppose I give this name and suppose I set the age to 25 and suppose suppose id to be 5 and subject to be suppose TOC that is theory of computation if I write like this it will not it will not insert because column count doesn't match the value of count at row 1 so in that case now you will have to supply you know the order in which you are performing the insert so how you are performing the insert because you are giving the s id and then you are giving what then you are giving age and then you are giving what name and then finally you are giving what subject so if you break this default ordering then you have to then you have to give your column list visible okay s id age name and subject it should get inserted s id age then I'm giving the age and then I'm giving the name and then I'm giving the subject okay I think there is a insert into student values s id then I'm doing the age then I'm doing the name okay so I forgot to add a comma over here so all right so now it will get inserted okay so this is how you work with insert query and now we will talk about updates so this is my table okay suppose I want to do one thing I want to update this employee his name is with it can his age is 24 I want to set his age equal to 42 suppose I do I want to do like this then what where will I issue I will say update the table name then what is the name of the table student and then I say set set what age age equal to new updated age is say suppose 42 and where you know where what where s id equal to what s id equal to 3 see this employer ethic has has an s id of what it hasn't seen it has a student id of 3 so that is why when I say s id equal to 3 this row will be selected then age will be updated to what age will be updated to 42 so if you say again say select start from student the new updated age will come become what it will become equal to 42 okay suppose I can do suppose I want to this employee Shahrukh suppose I want to change his age to 26 his age his age is 25 I want to change into 26 and I want to set the subject to be say suppose operating system then what will I do I will say update update which table update the student table update student set what age equal to 26 comma all right so if you have if you are performing multiple updates if you are performing updates on many columns then you have to give a comma separated you know you have to separate every column by a comma and then age equal to 26 and then I will say subject equal to suppose operating system OS where I can write s name equal to what Shahrukh okay if I try to run this okay so it is working fine and you are now you are getting you know Shahrukh age has become 26 and subject has become what it has become OS but I can also write the previous query you know like writing like this for example over here I do not write I write s i d equal to what s i d equal to 5 I can also write this in both of the cases this row will be selected see this row will be selected all right but it is always a better approach to catch a row by the by the primary key or you know the id column in this case the id column is s i d so it is always better to to you know when you are performing you know when you are writing where then it is always better to write the id why because there can be two employees whose name can be Shahrukh but they will have different id so it this is always better approach to you know whenever you are performing updates in the where class you you have to perform on specific rows and you you perform update the you know you catch those rows by their id all right so this is you know how you work with update and then finally delete so delete also works the same way suppose I want to delete this row that is Shahrukh so what will I say delete from which table delete from student table where what s i d equal to what s i d equal to 5 see in place of s i d equal to 5 I can also say s name equal to Shahrukh why because this is because I want to select this row I want to select this row but it is always you know as I told it is always advisable to write the you know to catch hold of the row by the primary key so here I do it so this row will be deleted and now once when I say select star from student this row has been deleted all right so this is how you work with delete and now if you want to delete then I suppose I write delete from student all right a quiz time for you suppose I write delete from student we are 1 equal to 2 okay what will happen why you know okay this is a you know this is a kind of specific query that works in oracle it's not working in my sequel all right suppose I only write delete from my delete from student then what will happen can you guess see this is a very dangerous query if you write if you perform delete operation without using the way without any without giving any where condition the whole records of the table will be deleted so if I write delete from student and I just give an enter then what will happen you know if I write select star from the student table if I write select star from student table then all the rows of the table have been deleted so you have to be very cautious when you when you write delete queries why because if you if you just delete then if you just delete by give without giving any where condition then the whole table will be deleted okay so this way we perform insert update delete operations on my sequel and in this video we will study about DDL commands in my sequel so DDL stands for data definition language so data definition language commands are those command in which you modify your change the structure of your data so this is actually different from inserting updating or deleting a data this is basically used when you are creating a table or when you are altering the structure of a table then you issue DDL commands so if you just read it over here DDL statements or commands are used to define and modify the database structure of table or schema so they are either used to create table or they are used to create that you know alter the structure of a table or there can be some other database structures database objects which we create or alter and for that purpose we use DDL commands so some of the DDL commands are create all to drop truncate rename so let us try to explore and learn all these DDL commands okay so I'll just go to my database and all right I will select the database okay all right so database selected by say select database then you will get this database now my task is to create table and suppose first of all let me say something let me ensure that all right let me create a student table so how will I create a table so if you have to create a table then you use something like this you know I'll just minimize it I think now I will go over here and so if you have to create a table then you use the following command create table and then you give the table name and then inside the brackets you give a column name one and column one data type okay so all right I don't I don't have a comma over here you give the column name and then you give the column data type so you can have many numbers of columns inside your table for example I can then you have a comma and suppose again then you have new columns suppose column two and then you mentioned the column two data type and then you know this way you keep on you know adding columns to the table this way you create tables in my SQL so let us create a table suppose the name of the table is student table suppose I say the student serial number and let us so this is my this is the name of my column serial number and I am having this I am telling it that the data type of serial number is int and then I have a column suppose student name and then suppose it is of var car 20 and suppose age suppose student age is also an int and upon one more column I have suppose date of birth and I say it equal to be of type date so I have created the table and what is the name of the table the name of the table is the student table so once when I go and you know you know I say student then you are getting this table so you are getting these columns serial number name age and date of birth so this is this command see this command which you have you have issued over here that is you know create table command so this is a DDL command why it is a DDL command because you are actually kind of you are creating a structure in your table okay you are not creating new data you are creating a new structure in the table see if I say insert into student values suppose student name is one suppose name is Amit and suppose age is 22 and then I say suppose date of birth is I can say anything suppose he is recently born okay I cannot you know because age is 22 so I cannot set to current date but let us again do it doesn't make any difference just for trial and testing purpose select start from student if I say like this see here by using this command I have created a new data inside this table I have not changed the structure of the table I have just added a new data so this command insert command this is a kind of dml command it is a dml command why because it is manipulating the data it is a data manipulation language but this command which is actually changing the structure of you know structure of table or creating some new structure so these type of commands are called your DDL commands so we have created the table that is called the student table so now let us go and you know you know if I want to drop this table then what command will you suppose you want to drop a table then I will say drop table and then you give the table name so if you want to drop the table then what will you say you can say drop sorry drop table what is the name of the table drop table so the table will be drop and now if you say select start from stu student table you will not get it why because this does not this table does not exist because we have already dropped this table so drop is also a kind of it's a kind of ddl command data definition language command so let us go and again create this table so I have already you know let me create this table again so this table is the student table this is my student table this is my student table sorry type of mistakes all right so this is my student table suppose now I want to do something I want to add a marks to the student so I have not given a column so if I want to add a new column to a table so what command will I what command will I use so if you want to add a column to a table then you use this command alt table then you give the table name and then you give you write this add and then you give the name of the column which you want to add to the table column name and then you give the data type so all right like this alt table table name add column and then the data type associated with the column so here I can write alt table what is the name of my table student suppose I say add marks and the marks is of type int so I have added one column and now if you say dsc student then you are getting one more column in this table that is a marks column so this is the command that you use to add some column to the to existing table so this alt a command alt a table command is also a kind of ddl command why it is a ddl command because we are not adding some new data to the student table we are actually changing the structure of a of the student table see what this is what we have studied over here is for example if you just I mean if you you see this ddl statements are used to modify you know the database structure you they are used to define or then they are used to modify so alter command is you you are using this alter command to modify the structure of the table now if you you have created this marks column suppose you want to now if you want to drop a column from a table drop a existing column from a table suppose you want to drop the date of birth column from the student tables and what command will you use to drop a column you use this command alter table and then you give the table name and then drop and then you give the column name this way you drop a column from a table so now if I go to my database for example this is my database and now if you want to drop the date of birth column then what command will I say alter table which table the student table and drop what date of birth so I've dropped one column and now if I say dsc student I will only get serial number name age and marks so this way you can drop a column now you can also do something very interesting suppose you can also suppose now let me add one more column to the student table suppose this is the student subject and suppose alter table suppose student add subject and suppose I write like this then when I say dsc dsc this is my student table so I've serial number name age mark and the subject of the student so let me insert one data inside this table suppose I say insert into sorry insert into which table student table and then I say values suppose there is one student 2 his name is Neha her name is Neha and age is suppose 21 and suppose marks are suppose 98 and suppose I say the subject is max will it work out let us try and see okay if I say select start from student suppose I write like this then you are getting the subject to be what then you are getting the subject to be zero this is happening because you are setting the subject you see the data type of subject is integer so because it is a subject it has to be var care type but I have you know by mistake I made that made it an integer so now I want to modify the structure of the table and I want to change the data type of subject which is initially it isn't and now I want it I wanted to change it to var care see here I am inserting math as a subject for this Neha student but the subject is coming out to be zero why because it is not getting you know properly inserted so now I want to change the structure of the student table and now I want to modify I want to modify this column that is the subject column and I want to change it to var cat type so what command will I use I will just write the command and then we will you know so yeah I just I will just create this command is will be used for creating a table and this this command we use to drop a table this command we use to add a column to a table and this command we use to drop a column from a table okay so now what I want to I want to modify a column so I will say modify a column so what command will I use to modify a column in a table I will use this command alter table alter table and then you will give the name of the table so what is the name of the table table whatever be the name of the table alter table table name modify and then you give the column name that you want to modify and then you give the new data type so okay like this you modify all right so alter table table name modify column name whichever column you want to modify and new data type new data type okay the new data type which you want to this which you want this column to have for example in this case our subject column I want the subject column to be of type var cat so what will I say I will say alter table what is the name of the table student table and then will I say modify modify what subject and I want the subject to be of type what I want the subject to be of type var cat I say var cat hundred okay so here if I say again BSC student then what will I get see now the student is subject is of type what it is of type var cat so now let us if you try to insert one more row suppose the student is Amisha and a row number is say suppose three and now this then this will be properly inserted if you say select star from student then this will be properly inserted why because you have changed the data data type of subject all right and you can also do something you know very interesting you can also set a null value you know you can for example if you just observe this column this column has a default type of null I can change this also and I can you know I can you see this can this column can be set to null value and it has default value of what it has default value of null so I can change this also for example I can write what I can write modify var care subject and then I can say not null and I can give a default value associated with the column also suppose I say default value is suppose the default value of every student subject is any subject you know I think let us make it history all right and then once when you say BSC is the student table if you describe it then see this column where subject is now it is no more you know it is no more you know it cannot be set to null and the default value of this column is what the default value of this column is equal to history so you can also do this all right let us move on and now suppose the default value of this column is history but now I want to change this default value of this column and I want it to set it to suppose Sanskrit Sanskrit is a very popular language as far as where Indian medic civilization is concerned Sanskrit is a is a language of of of the gods so let us try to modify this so how will I do it alter table sorry I am making a lot of typos in this video all right what table I want to modify alter table student and alter what subject and I will say default to be Sanskrit there is some problem with this let me know alter table student alter subject default Sanskrit okay there is some problem with this I think I need to add one more you know thing I alter table student name alter table table name alter column name and then set subject default is equal to what Sanskrit so now if you go and student suppose you write like this then you know the default value so this also I can do now I can change the default value of some column okay besides changing the data type of the column I can also change the default value associated with that particular column and then in the end I can also rename a table so what command will I use to rename a table alter suppose suppose these students are all engineering students and I want to and I'm and I'm engineer and I want students of type engineer so I don't want this table to be named student I want this table to be named as e-student that will stand for engineering students so what will I say alter table student and rename to suppose e students so I hope there will be many engineers will be watching this video so all right if I do like this then if I say the e-s-c what e-s-t-u-d-e-n-t e student then you will get this table so I this command also you can use you know you can use this command to rename a table so what will I use what command will I use to rename a table rename a table so alter alter table table name and then I say rename to what rename to a new table name new table underscore name so these are some of the dvl commands and yes one more thing I'm missing that is a truncate so this is a very popular interview question for example I say select star from e-s-t-u-d-n-t then you will get this if you want to if you want to remove all the records from a table then you will say delete from what e-s-t-u-d-n-t all right if you write this command then all the records from your or from your table will be deleted but this delete command is a kind of dml command see it is a data manipulation language command but I can also issue one more command that is a truncate command so if I say truncate table s-t-u-d-n-t if I say like this truncate table student command then this will also drop all the rows from the table but this truncate command is not a dml command like the delete command it is a ddl command why because because the operation which it is performing it cannot be reverted back it cannot be put into some kind of transactions and it cannot be you know it cannot be reverted or rolled back and all the space that has been allocated for the rows of this table that is a e-student table that will be deallocated so always remember this is the difference between truncate and delete okay the stable does not exist actually e-student table exist so if I say now if I say select star from sorry select star from e-student so I will not get any rows why because I have used this command truncate command okay so this command is going to delete all the rows from a table but this will be a ddl operation so always remember this is the difference between delete and truncate and they in interviews many frequently they ask what is the difference between dml between the truncate delete and drop so drop is you know this table structure is remaining you know if you say s-t-u-d-n-t this table structure is always here but the rows have been deleted and truncate is a ddl command but if I say drop table e-st-u-d-n-t then the whole table will be dropped and if we say dsc-st the structure is being dropped so this table will not exist okay so truncate is a ddl command always remember this all right in this video we will study about few clauses in mysql programming and you will use them use these clauses frequently when you write your sql queries so I will just so when I say show data bases so it will list out the list you know the all the database in my server and I will out of you out of it I will use the publisher database so this is okay sorry use publisher so I use a publisher database and inside this database there is one table the employees table so this is my employees table and there is one more table and the department's table select star department so this is my department's table okay so now let us continue and over here suppose first of all let me tell you what I am going to explain in this video so I'll just open the notepad and I'm going to discuss few clauses in sql okay that's okay so I will talk about in clause then I will talk about you know the between clause and then I will talk about the like clause so when you say the in clause so the in clause is used for then you can say exact matching okay between clause is is used for sorry range matching okay and like clause like clause this is used for your pattern matching okay so like clause is used for pattern matching so all these clause you will study one by one but before that let us move to the database and let me tell you one important thing that whenever you deal with string literals in my sql you always include strings in single inverted commas for example if I want to find out the salary of nickel so I want name and I want the salary from which table from the and the employee's table okay where ename equal to what is equal to is equal to suppose nickel because I want to find out the salary of nickel so nickel has a salary of 1220,000 per month but you know you have to keep this thing in mind for example I'll just use a pen tool over here to just you know highlight things here you know I have used these you know if you just observe I have used this single inverted commas over here so always remember that whenever you are dealing with strings in the database you always use single inverted commas okay so this is very simple but now I will just minimize it and then suppose now I want to find out all the employees who are either managers and who are either developers okay so what I will do I will write down the queries one by one and okay and you can just you know so you can refer them okay so like this so my first query is find all the employees who are you know either managers or developers okay they are either managers or developers so what query will I write select ename ename comma designation designation from which table from the employee's table where I say that the designation can be manager or it can be a developer so what I can write where designation equal to what it can be either equal to a manager or designation can be equal to what it can be equal to or designation equal to what it can be equal to a developer so I can write something like this okay so if I write like this then I will guess I'll get a list of employees who are either managers or developers so here also if you observe I have because you know designation is also of type varcats if you suppose if you write dsc the employee's table if you describe the employee's table then you find designation is of type varcats so whenever you and you know you operate on you know varcats you write there you know will you perform some search operation in in your very class using some varcats then you enclose them in your in single inverted commas for example I have enclosed manager and developer over here in single inverted commas okay so just keep this thing in mind okay now now let us move forward and first of all let us study the in clause so all right I will study first of all the in clause so in clause is used for exact matching how is in clause used for exact matching just take the previous query this query is returning me all the employees who are either manager or developer okay now I can write this query in some other form using the in operator how I will use select ename comma designation from the employee's table where designate designation in I can say something like this either the designation can be a manager or the designation can be a developer if I write something like this okay okay now it is correct so here I have what what operator I have used I have used the in operator over here if you just observe I have used the in operator over here and why do I say that in operator if you just look at this in operator you use for exact matching why I have said like this because it is going to exactly match either manager or exactly match what either developer and carefully note the word either I am using either so either means it can be either manager or developer so that is why you can also write this query using or you know or operator but over here I have used in so you can either use you know either you can use in or either you can use or for example suppose I say I want the list of all employees who work either in department number 10 or in department number 20 okay I like the query suppose get the list get the list of all employees who work either in department either in a department 10 or 20 okay I want the list of all these employees so what query will I like select ename comma dpt id dpt id from which table from the employees table where dpt id can be equal to 10 or dpt id can be equal to what it can be equal to 20 also but the same query I can write in this form also select ename comma dpt id where dpt id in what it can be either in 10 or it can be in what 20 okay I can write this okay select ename comma dpt id from which table from the employees table okay from employees where dpt id in 10 or 20 so I will get the same record so over here if you observe also I'm writing what where department either in 10 or either in 20 and it is exactly matching 10 and 20 okay like this now we'll study the so this is my employees table if I write select star from the employees table this is my employees table okay now suppose I say list all the employees who work either in department number 10 or in department number 20 but their salary is greater than suppose 60,000 or say suppose 40,000 okay like this I'll just you know copy it and this is my third query so what is the third query I'll just paste it get list of all employees who work either in department id 10 or 20 and whose salary is greater than suppose 30,000 okay 30,000 then what query will I write I will you know I this I have already written their department id in 10 in 20 and what and salary is greater than what it is greater than 30,000 if I write this query then I will get this these employees suppose if you want you know if you want to justify also then you can write salary over here also salary from employees where department id in 10 comma 20 and salary is greater than what it is greater than 30,000 so this is very simple in clause using the in clause now we will use the between clause so why do we use between clause I have written over here between clause is used for some kind of range matching okay so what kind of range matching let us see suppose this is my employee statement so let's start from the employee statement this is the employee statement now I want to list of all employees who get a salary greater than say suppose 40,000 and less than 90,000 okay I want to list of all these employees whose salary is greater than 40,000 less than 90,000 so I would like the query so whenever I say I mean whenever I write the query you just pause the video and try to write the query on your own you cannot able to write then we will try to solve it on you know you can just watch the video to solve the query but here I want the list all the employees whose salary is greater than or equals 40,000 and less than or equal to say suppose 90,000 okay I want this query then what query will I write I will write something like this you know we you you have operated that you have you have used a greater than or equal to less than equal to operator so I can write something like this select ename comma salary from which table from the employees table where salary is greater than or equal to 30,000 and salary is less than equal to what it is less than equal to 90 sorry yeah less than equal to 90,000 okay if I write this then I will get the list of all employees whose salary is between 30,000 and 90,000 what 30,000 okay I have said 40 40,000 okay so you know just I'll just change this 3 to 4 okay now it is correct so you are getting these list of employees whose salary is between 40,000 to 30,000 but now I can write the same query using the between operator also how I can write like this select ename comma salary from which table from the employees table where salary between between what 40,000 and 90,000 okay if you write this query then also you will get the same result that so so you can observe this between operator is you know it is performing a range matching between 40,000 and 90,000 okay so one thing to always keep in mind when using this between operator is that you know it is inclusive that means it is searching for salary greater than or equal to 40,000 you observe that there is employee level and whose salary is 40,000 and that this employee is also getting selected why it is getting selected because between means you know it is it also perform you know in the you know the matching set endpoints what are my endpoints over here 40,000 and 90,000 that is why it is equal to greater than or equal to 40,000 less than or equal to 90,000 okay so like this so always remember this you know it is inclusive between operator is inclusive of the endpoints like 40,000 and 90,000 over here for example some employee is getting 90,000 that that employee will also get selected by this query okay so what is my employees table this is my again my employees table okay now good suppose now I ask you to list all employees whose commission is between suppose 5,000 to 16,000 okay whose commission is between 5,000 to 16,000 so what query will what query will you write I'll just copy it and list all employees whose suppose I will say commission commission is what did I say commission is between suppose between I say 5,000 and 16,000 okay send what query will I write I think it is very simple again you can use a between operator how you can use select ename comma what commission from which table from the employees table where what commission between what what I have said 5,000 and 16,000 okay now I'll get the list of all employees whose commission is between 5,000 and 16,000 okay so this query is also you know you can write it through the use of between operator okay so now suppose I want the list of all employees whose commission is between 5,000 and 16,000 and who work in department number 10 okay so what query I want I'll just copy it and okay paste it and who work in department 10 okay this is what I want I will say this is my fifth query okay this is not the fifth query this one is the this one is the fifth query and then again this one is the sixth query okay now correct okay sorry yeah cancel so can you do it I think it is very simple just give it a try I'll just pause okay if you're not able to get it then I have to add one and condition over here also and what and department id equal to what equal to 10 okay then all I will get only one employee I can also write this query like this using the between operator and in operator both you know I can write where department id in what it can be in only in what then then it is going to perform exact matching for the value okay is it correct yeah okay again suppose this is my employees table select star from the employees table this is the employees table now suppose okay now it is our turn to study the like operator so like operator is used for pattern matching okay so pattern matching you can you know you can think about you know pattern matching is done through wildcard operators so you can think of pattern matching in terms of you know some regular expression matching you perform if you have worked on some programming languages you must have worked on some the regular expressions so it is same like that here you will study two wildcard operators one is the m percentage operator and the other is the underscore operator so m percentage operator is used to match anything you know it can match any string and this underscore is used for matching a single character at a time okay remember this all right see percentage is used for matching any string and underscore is used to match a single character at a time now let us move to this employees table and what are the employees in what are the names of the employees table select ename from what from the employees table this is my employees table now i want the list of all employees whose names you know whose names start with n then what query will i write give it a try i'll just pause i think it is very simple if i'm not able to get it then i have to get the name from which table from the employees table where ename is like what it is it must start with n and then it can have anything okay that is why i have given the wildcard operator in your percentage that means anything okay first it will start with n then it can have any string okay now i want the name of all those employees whose names ends in a okay okay i'll just write the queries so that there are no confusions seventh list all the employees whose name ends in a okay how will you write the query just give it a try okay if you are not able to get it then it is very simple i can write select ename from which table select ename from the employees table where ename sorry where ename like what it will always end in singular character but before that it can contain anything that will that means i will write this query so you will get these employees neha rashmita nanshita are three employees whose name ends in a okay now again these are my select ename from employees these is the this is the list of all employees the names of all employees now i want the names of all those employees whose name contain at least two a first of all let us find the names of all those employees whose name contains at least one a okay at least one a what query will i write okay list all employees whose name contain at least what one a it must contain at least one a okay then what query will i write give it a try i'll just pause i think it is simple select if you are not able to get it select ename from which table from the employees table where ename is like like what it is like something like this okay yeah now it is correct how come how come i have written this wildcard operator you know what let us go and test it out against you names for example i'll you know i just open the pen tool and i'll just take this i'll just take the pen and suppose it has to match sonali then you know for the first percentage it will match son and then it will match a and the next percentage it will match what against ally okay if it has to match anchita then the first percentage over here you see this first this first percentage in case of anchita will be replaced by nil then again this a will be matched and the next percentage will be replaced by what nsh ita okay so similarly it will be true for all the names so this is the you know this is the query which you can use to list all employees whose name contain at least one okay clear now i want to list all employees whose name contain at least two a's how will you write the query first of all just let me power sorry controls it copy it paste it at least not one but okay at least two a's what query will you write i'll just give it a try i can just pause the video so always you know it's always good you know that you try to solve queries on your own i'm giving some queries you first try on them on their own on their on your own and then if you're not able to get then eventually i will solve it okay so if you're not able to do it you just pause the video and then you try to do it on your own okay if you're not able to do it then it is very simple what i just need to select the name from which table from the employees table where ename like what it has to be like something like this okay a and then again a percentage is it correct okay all right okay now it is correct all right these are the names of all the employees whose name contain at least two a's okay then again you can you i mean you can see for example suppose i again use a pen tool and i think it is easy to understand but still you know if you're not able to get it then for example if you want to match anshita then you know this first percentage will be replaced by null then this a will be matched against the first day of anshita then NS it will be replaced by this percentage and then again this A will match against this a okay this a and then you know this percentage that is remaining will be matched by what if you will be matched by a null similarly if you want you can match giant also the first percentage will be replaced by J then A will match and then the second percentage will be replaced by y and then again A will match and the third percentage will be replaced by what? Np. So, it is getting matched. So, this will give you a list of all employees whose name contain at least two ways. Again, I would give you a query just try on your own. List all employees whose name is at least say suppose five characters long 10. List all employees whose name is exactly five characters long. Exactly five characters long. Give it a try. A hint for you. You have to use this wild card. We have used this percentage. Now you have to use this underscore and it is used for matching a single character. A hint for you. Try it. Pause the video. Okay, if you are not able to get it, then select ename from this table from the employees table. Where ename like what? Sorry, ename like what? Ename will be something like this. One, two, three, four, five. It will have five underscore. Okay, the only name that is being selected is nothing. You know, this means one underscore will replace it can be a substitute for one single character and that character can be anything. Okay, that is why when I write five underscores, you know, in one sequence and you know, it will give me a list of all employees whose name is five characters. Okay, again, one more query for you. Okay, I have, okay, 10. Sorry. Okay, I will just move these dots.  Now, suppose my 11th query is at just five on your list, all employees whose name, whose second character in name, in name is I. Okay, so it can be any name, but the second character has to be what? It has to be I. Okay, all the employees in whose name the second character is I. Okay, try it, try it. Okay, try on your own. I'll wait. Okay, if you're not able to get it, then this is also very simple. I think you can do it with all the tools which are, you know, I've provided you from this table from the employees table where you name like what it has to be something like this. See the first character can be anything that I can replace it with underscore the second character has to be what it has to be I sorry, it has to be I and after that if anything can come so I can write something like this and then you know, if I do something like this and Nikhil and Nitin are two employees whose second character in the name is I. Okay, so this is the, you know, this is the use of like operators. So like operator is what I've said it is used for pattern matching. Okay, if you go over here, I have given you know, like operator is used for pattern matching. See, I have performed some kind of pattern matching in these names. Okay, all right. Now, all right, this is my employees table. Again, one more query will write and then I will just know I'll end the video. Select start from employees. This is my employees table. And suppose I want you to find all the managers and developers whose name can either start with the, you know, in N or S. Okay, I'll write the query 12 list all this time managers, sorry, managers or developers whose name can either start with N or S. What will be the query which I will write? Okay, just give it a try. Okay, if you're not able to get it, then I think I will write it select email. Okay, and designate. Okay, so I am performing operation on name and then on designation from I think the first part is simple, you know, you want the, you know, all the employees were either managers or can I will write and list all managers or developer. Okay, whose name can either start with N or S. Okay, so the first part is simple, you know, listing all managers or developers, select the name permit designation from employees, where designation in what it can be it can be either a manager, or it can be what it can either be a developer. And what the name has to be either it has to start with N, or it has to start with what it has to start with S. I can write the and name, like, say, suppose, N percentage. Okay, if I write this, then what you will get these two employees are managers, but I'm getting no developers, so I can write something like this. And, or even like, I don't know if it will work or not, let us try it out. Okay, S percentage. And I will just, you know, just also close it. So you're having these two conditions, even can be like, you know, N or S starting with N or S, and your designation can be what it can be either a manager or either it can be what it can be a developer. Okay, so then you are getting these three employees, you know, Nikhil Nithil and Sonalis, they are managers or developers and their name are either starting with N or they are starting with S. Okay, so you have to group these two conditions, you know, you have to group these two conditions using the and, you know, because they can be either manager or developer and what and so you can, you know, when I state the query, then you can, you know, understand you have you have to use OR or AND over here. And what you can use ename like N percentage or ename like what S percentage. In this video, we will try to understand what does null mean.  So what does null means? So if you go to the first line, what I've written is that value of null at any cell means an undetermined value and arithmetic operation on any null value also returns in null value. So what does all this mean? Let us try to understand this with the help of examples. So I'll go to the database. For example, so I'll use the published database and if I say, so I select the database, then you know, it is a published database. And then then there is my employees table. See, this is my employees table. Now you find that the commission for some employees is null. For example, for the commission is null, for the commission is what? The commission is null. So what does this null value mean? The null value means over here is that this value is undetermined. This value is undetermined. Or it means that the organization has not yet decided what commission will it give to Vasim or what commission it will give to Anchita. So the organization is not clear with it. So that is why the value has been said to what? The value has been said to null. So this is what we saw. So the value of null at any cell means an undetermined value. And null values in table is not the same as zero. See over here, for example, the commission for Rajneesh is zero. That means it is a determined value. How come a determined value? Because the organization has decided to give no commission to Rajneesh. That is why his commission is zero. But for Vasim, the organization is not yet sure what commission will it give to this employee, this Vasim employee. See his commission is coming out to be null. Vasim commission is null. So the value of null means an undetermined value. Now let us try to do something. For example, I will remove this query to find the effective salary of all employees. You have to write this query. And what is the effective salary? The effective salary is equal to salary plus commission. Effective salary is equal to salary plus commission. So I can write something like this. Select ename comma salary comma what? Commission from this table, from the employees table. So you will get the ename, salient commission for all employees. Now if I want the effective salary also, then what will I do? I will write salary plus what? Commission. Suppose I say as effective salary from what? From which table? From the employees table. Now for example, the salary of Nikhil was 120,000 and his commission was 30,000. So the effective salary is coming out to be what? 150,000. Similarly in the case of Nitin, the salary was 80,000 and the commission was 15,000. So effective salary was 95,000 per month. But now you observe a very important difference and I will take the pen tool to make you understand with this. For example, you take Anshita. Anshita's salary was what? Anshita's salary was 60,000 but her commission was what? Null. So her effective salary is also coming out to be null. Why the effective salary of Anshita is also coming out to be null? It is because of this reason. See, if you just see the third line, you know, here you see the second line. Arithmetic operation on any null values also result in what? It also results in a null value. So why it results in the null value? For example, you say, suppose you say infinity. So if you add a number, suppose you add 10 to infinity, the result will also, will always be what? It will always be infinity. If you subtract 10 from infinity, the result will be what? Result will be infinity. This is the basics of mathematics. So here, you know, suppose, you know, the salary of Anshita is determined. It is 60,000 but the commission is undetermined and if you add an undetermined value to a determined value, the result will also be what? The result will always be what? Undetermined. That is why arithmetic operation on any null value results in a null value. So here, I think I have written this. Arithmetic operation on any null value also results in a null value. Why it results in a null value? Because null means an undetermined value and you add anything, subtract anything, divide, multiply anything from an undetermined value. The result will be what? It will always be undetermined. So arithmetic operations on any null value also results in what? A null value. So I think this much is clear. Now, I want you to find the name of all the employees whose commission is null. So what query will you write? I will say query to find the names of all the employees whose commission is null. I want to find the name of all employees whose commission is null. So what query will I write? Select suppose name, select the name from which table, from the employees table. Where what will I say? Where commission is equal to what? Is equal to null. Will it work? It will not work. See, so here write, you can, you know, I will just write it. I will write it. Control Z, we can never equate null values. Null values can never be equated. See, here you are saying that the commission is equal to null. You cannot equate an undetermined value. This is never allowed in SQL. See, you can say something like this, where commission is equal to what? Equal to say, suppose 30,000. You can say something like this and Nikhil's commission is what? 30,000. But you cannot equate an undetermined value. For a null is an undetermined value. You cannot equate an undetermined value. So for this, we use the is clause. So whenever you want to perform matching with nulls, you use what you use is. So how do you use this? Something like this. Where not commission is null. We will not write commission is equal to null. Instead, we will write what? Where commission is null. Clear? So here, you know, you are getting Anchita, Bevav and Basim. Three employees are there whose commission is what? Whose commission is null.  Suppose I say commission is not null, then what query will you write? So you will write something like this. Where commission is not what? Where commission is not null. So you will get all these employees. For example, if you select the commission of these employees, you will get commission. So all these employees, the commission is not null. C and Rajneesh is also getting selected. Rajneesh is having a commission of zero, but this is not equal to null.  Null values in a table are not the same as zero. So Rajneesh commission is zero means the organization has decided that it will give only zero commission to which employee to the employee whose name is Rajneesh. So this is my employees table. You can see the employees table. Okay. So this is the way, you know, you work with null values. For example, I give you a query, write a query to find the how many employees in your organization. Suppose how many employees in department number 30 have null commission. Okay, you have to write this query. Write a query. Okay, I will formally state the query. Okay, I will remove it. Query to find the count of all employees in DPT department ID 30 whose what? Commission is say suppose null. So you have to write a query for this. Okay, try out on your own. Okay, I want the count of all employees in department number 30 whose commission is null. Write this query on your own. I'll pause the video. Okay, if you're not able to get it, then I think it is very simple. I just want the count. So I will use the select count star from which table from the employees table where what commission is null and DPT department ID equal to what it is equal to 30. So you are getting two employees in department number 30 whose commission is null and see these two employees are what one employee is you know, this was seen his commission is done and the other employee is what other employee is where above he belongs to department number 30 and his commission is also what his commission is also null.  This video we will study the order by keyword. So what does it mean by order by? So order by is you know it is basically meant for sorting of grows and I have given you the you know the general form in which you can use order by new query. So let us try to understand it with some practical examples. So here, this is my employees table, which we generally use employees table. This is employees table, sorry, select star from the employees. This is my employees table. Now, I want to suppose the name and salary of all employees and what query we are at select ename comma salary from which table from the employees table. So I have got the name and salary of every employer. But now suppose I write something like this, I select the name, select ename and I also select the salary from the employees table and I write order by also order by what order by suppose salary. Then what will happen? So you observe that now the salaries are being ordered.  Okay, so the rows are they are sorted on the basis of salary. Okay, if you observe the rows are sorted on the basis of salary. So this is the way in which we use order by clause. We can also do something like this. Suppose I say order. So what is the default ordering of salaries in MySQL if you observe? The default ordering is ascending order. It has arranged it in ascending order. If I want it in descending order, then I will use the dsc keyword. So suppose if I say like this, then you will have the salary order by salary descending order means from highest to the lowest. So Nikhil is getting the highest salary 120,000. So it is coming first followed by Nitin and then you know Abhinav and all, you know, then in the end you have Rashmita because she is getting the lowest salary. Okay, so this is the way you use order by in your SQL queries. Okay, again, I will go to my employees table. Select star from the employees table. Okay, and you can also, you know, apply order by on multiple columns. How you can apply order by on multiple columns? Let us have a look. Suppose I say something like this. Select ename. I want the name. I want the salary also and I want the department ID also from employees. Then you will get a list of all the names, salary and department ID. For example, Nitin is getting a salary of 80,000 and he is in department number 10. But if I say something like this, order by department ID comma salary. Okay, then what you will you get if you just observe? See, okay, I will just, you know, I will just change. I will not write salary first. I will write department ID first. That is, you know, it doesn't it's not any kind of error, you know, you know, it will help you to better visualize the result. Okay, I will write salary second. Basically, these both queries are same. Okay, now, suppose you so what it is doing when I am using order by on multiple columns, then it is, you know, first of all, it is sorting by the first column. So the results are sorted by the first column.  So 10 will come first 20 will come second. And then you know, the in the end what will come 30 will come. And now inside department number 10, the salaries are will be sorted. For example, Nitin is having a salary of 80,000. And then Nikhil will have a salary of 1 lakh 20,000. For example, now if you go to department number 20, then all the employees in department number 20 will be sorted on the basis of their salary. For example, Rashmita is giving getting the lowest salary.   And then, you know, there was department ID 30. All right, these three department IDs were there. Now it has first sorted on the basis of what it has first sorted on the basis of these department IDs. Now in this, for example, now in this department ID 20, it is going to sort on the basis of salary from the highest to the lowest. Okay. So I think it is clear. Okay. Now, suppose I say something like this, I just have the same previous query, I just make some modifications. So I write order by dbtid, dsc, and then I write salary, then what will be the result? Can you guess what will be the result? See what is the result. So you have ordered, you know, department ID have ordered in a descending way. So first of all, it is going to order by in what by which order by the department ID in descending order. So first of all, department 30 will come, then department 20 will come and then department 10 will come. And then in department 30, you know, Sally will be in ascending order. For example, if you see department number 20, Sally is always in which order it is in ascending order. Okay. But now if I write something like this, Sally, and I also write dsc after Sally also, then, you know, I think results are obvious, you know, now if you go to department number 20, then the employee that is having this highest Sally is coming first, because, you know, Sally is also ordered in a descending fashion. Okay. So this is the use of this order by clause in my SQL. So and you can also use order by clause in conjunction with their clause. For example, I want the, you know, ordering of Sally in department number 20. Okay. How suppose I want Sally from highest to lowest in department number 20. Okay. I'll just, you know, I'll just write the queries. List, list all employees in department 20 arranged in descending order of their Sally. Okay. This is the first query. So just write this query. Okay. I think it is very simple. You just have to use order by clause in conjunction with the whereby. Okay. So what query will you write? Give it a try. I'll just pause. If you're not able to get it, then I have to write just something like this. What I want Enem comma Sally comma DBT ID from, from this table from employees table, where department ID equal to what 20 order by what Sally, order by Sally. Okay. So this is the, you know, so you are now getting. Okay. Sorry. I wanted in arranged in descending order. So if I want in descending order, then I have used to use the descending keyword also. Okay. So ascending order is, you know, by default, they are arranged in ascending order. So for ascending, you have to use, you know, ASC for ascending order. But you don't have to, you know, every time use it. See, now the Sally's are arranged in ascending order. So say the lowest Sally was coming first and the highest Sally is coming in the last. Okay. So you don't have to, you know, by default, it is all automatically taken.  Like this. I think this much is clear. I would just like to, you know, I would just like to make, to make things clear. How will you process this query? This is my impulse table. And then this is my, this is the query, which, you know, I wanted you to, I wanted you to work on. Okay.  So I'll just, you know, I'll just take the pen tool. Okay. So always remember my dear friends, then select clause is used for select clause will go from row by row and where clause will you will then filter those records. For example, select clause will move from every row. And then where clause will filter those records. So what is my where clause department ID is equal to 20 is department ID 20 in this row? No. And is it 20 in this row? No. Over here, it is 20, it will be selected over here, it is, it is 20, and then it will be selected and what will be selected names alien department ID. Okay. And then this is not, this is not true. And then this is true. This is not true. Again, this and this are true. So in these four row, first off, now these, you have got all these four rows. So first, the where clause will execute first, which clause will execute. This where clause will be executed. And now if you have got these results, they will be sorted, they will be sorted on the basis of the Sally in a descending faction. Okay. Like this. So this is the way, you know, you can better understand, you can better have a feel of this query. First, where clause is executing, it is, you know, fetching the results and then the results are being sorted in descending fashion. Okay. Like this. So it is executing in this fashion. So, you know, you, if you remember like this, I mean, this looks out to be very simple, but once when you go to formulate, you know, complex queries, then if you have a feel of all these things, then you can, you can have better intuition in solving complex queries. Okay. Thank you. And so this is the way we work with, you know, with the order by clause. And there is one more clause that is the limit, one more limit keyword is there. So limit keyword is used for limiting of records. How does it limit records? Select, suppose I say, select ename, suppose I say eid, eid, comma ename, comma salary, so Sally from the employees table. So you have got the id, the name and the salary, but suppose I say something like this, select ename comma, sorry, first the id, then the name, then the salary from employees limit, suppose I say 3, suppose I say limit 3, then what does it mean? So it will fetch me the first three records. It will fetch me the first three records. Okay. So all the, it is getting all the records and then, you know, it is limiting the number of records which have to be, you know, finally given to 3. Okay. Then if I write something like this limit, if I write, so I am also not sure, let us see how it runs. If I write limit 1 comma 3, if I write something like this, what will be the result? Yeah, you are, I mean, it is better, you know, you can understand it, how you can understand it, you know. So you can, you know, index every position, how you can index every position, look like this. Okay. Suppose here again, I suppose again, if I, you know, I take this pen tool to better explain it. So, you know, you can think, I am thinking in these terms at least, suppose, you know, it is having every column, you know, index, this column has index zero and then, you can have this, this column has index two. Now, if I write limit 1 comma 3, what I am writing, limit 1 comma 3, so it is going to this one first row. Okay. And then, from the first row, it is pulling out which all rows, how many rows, three rows, so we are getting Nitin, so Nali and Anshita. Okay. Suppose I write, I do not write limit 1 comma 3, I write something like this, suppose, I just minimize it, I am sorry. Suppose I, you know, I, okay, I will just, you know, I will just, now suppose I write this query, limit say, suppose not 1 comma 3, I say limit 0 comma, suppose 4. Okay. So, it is, it is, you know, this is, this row has an index zero, first row has an index zero and from this position, it is getting how many rows, it is getting four rows. So, Nikhil, Nitin, Sonali and Anshita are being selected. Okay. So, this is the way you, you know, you work with limit clause. Now, limit clause, you know, with in use, when in conjunction with order by keyword, then, you know, it is, it is, it can be very interesting. You know, you can have some queries through some good queries, you can formulate through with the help of order by a limit. For example, suppose I say something like this and then, I say, select the name of the employee getting the highest Sali. Select the name, select the name of the, suppose employee who gets the highest Sali. Okay. If I want the name of the employee who gets the highest Sali, then how will you, how will you write this query? Suppose I say, then I can write something like a select ename comma Sali from which table, from the employee's table. Then you will get all the names and Sali. Now, if I say order by salary, sorry, order by Sali. Okay. Then you will get the results, you know, ordered by what? Order by the Sali. But I want the highest Sali, then I will use, you know, I will use first DSC clause over here. Then, you know, the Sali's will be arranged in descending fashion. So, Nikhil is getting, you know, I have got this Nikhil is getting in the highest Sali. But now, my query is saying, I want, just want the name of the employee who gets the highest Sali, then what will I write? I will just write like this. Limit what? Limit one. If I say something like this, then, you know, out of this, you know, when you say it, when you say it, limit one, then it, what it means that out of the whole records, out of the all records which is getting, which it is getting over here, it is just, you know, you have, because you have said limit one. So, what it is doing? It is just selecting this record. Which record? This, this record. If you go over here, this first record, it is only this record has been selected. Nikhil one. Okay. Now, sorry, now I will keep, give you one or two queries for you to solve. I will just escape it. Okay. Now, suppose list the first three employees who get the lowest Sali. Suppose list, I would not say first, I would list the three, list three employees who get the lowest Sali. Okay.  So, what will I write? What query will I write? If I just go to my SQL console, what should I write? Just try it. Okay. I will just pause the video. Okay. So, I think it is, I think this can also be done. Select ename from a salary from employees. Order by what? Order by Sali. If I write something like this, then you are getting the Sali in ascending fashion. Okay. Rashmita is having the lowest Sali. Then after this, you are getting Jaya. Then after that, you are getting what? You are getting Vibhav. Now, I want all these Rashmita, Jaya and Vibhav. Then what will I write? I will write the limit 3. Okay. Then you will get these three Sali. The lowest Sali in the, you know, organization, the second lowest Sali in the organization and the third lowest Sali in the organization. So, if you want to better, you know, if you want to better have understanding of this query, then first of all, what is happening? First of all, this order by is executing and then in the order by what the results, the sorted results that you have got. Then on that, the limit operator is being applied. Okay. For example, now suppose a good verify, I give you just try to work on this query. Find the employee getting the highest salary in department 20. I want the employee in department number 20. Okay. I want only the employee in department number 20 who gets the highest salary. What query will I write? Just try it. I'll pause. Okay. So, please, when I say I pause and just try.  Many times, you may not be able to do it. But still, if you just try, keep on trying, you know, you will get, you know, a better intuition when you'll be able to solve complex queries. You know, life is also like that.   Okay. So, this way things work. So, if you just keep on trying, so ultimately success will come. So, here, what is my query? My query is to find the employee getting the highest salary in department number 20. So, I will first of all write, select ename comma salary comma dpt department ID from which table from the employees table. This is my employees table. Now, I only want results for which department for department number 20. So, I will use that where class where department ID equal to what where department ID equal to 20. I'll get only employees of department number what department number 20, but are these results sorted? They are not sorted. Then I will apply what I will apply the order by clause. So, what will I say? Order by what order by salary? I'll get this. But now I have got the lowest salary first, I want the highest salaries and what will I what will I say? See, I want I want the highest salary. Then what will I say? Order by salary dsc. Okay. Then first of all, I will get Abhinav that is getting the highest salary. But now I only want Abhinav. Then what query will I write? I will write something like this. So, limit one. It is going to fetch only the first row. And so, Abhinav is the employee in department number 20 that is getting the highest salary and his salary is 70,000. Okay. And so, first of all, also, this may look out to be very simple, but still, I would like to, you know, explain this query how this query is executing sequentially. First of all, first of all, select clause and the where clause are running in conjunction. Okay. So, how select clause and where clause are running in conjunction? Look, I will just explain it. I will use a pen tool and okay. So, if you go over here, okay, I will just, you know, pull it, sorry, it is not coming down. Okay, escape. I will just pull it down. And just again, I will, you know, I will use a pen tool. There must be some shortcut for this. I think I don't have to, every time, tool, pen. Okay. I think, all right. Now, first of all, select statement and where clause will run. Is this row in department number 20? No. Is this row in department number 20? No. This is in department number 20. This is in department number 20.  This row is in department number 20. And these are rows in department number 20. So, first of all, select and where are executing? Now, these rows are getting sorted by the ordered clause, order by clause. So, when they are sorted by the order by clause, and then, you know, the result that has been obtained by the order by clause, then limit is being applied on them. So, first, where is running, then order by is running, and then what is running limit? Okay. Remember this. Always remember this. I'll just escape it. For example, if I am saying something over here, you know, I just applied the order by didn't apply the limit. So, here you see where, where, where clause has been written and the order by has been written. So, these after where order by is running and then, and then after that, what you are writing, then after that, you are writing limit one. So, you are getting the lowest value. Okay. So, this is very simple. Now, I'll give you one more query. And after that, we'll end the video. Find the employee getting the second lowest sally in department 20. I want the employee getting the second lowest sally in what? In department number 20. See, I don't want the lowest sally. I want the second lowest. Okay. That means one level above the lowest salary. Okay. How will you write this query? I'll just pause the video. Try on your own. I've given you all the tools. So, you can write this query. Please try it on your own. Okay. Let us try. Let us see how I do, how I approach this query. So, this part is common, you know, I think where department 90 20. So, you'll get all the employees from department number 20. And then, I will order by them on basis of what? Order by what? Order by sally. But now, I want the lowest sally because, you know, order by is defined default in the sending order. I want the lowest sally. So, I will not use the DSC over here. Okay. Then I want the second lowest sally. So, I don't want, you know, in this case, I don't want Rashmita. I want Sonali. Okay. Because she is getting the second lowest sally. So, how will I write this query? I can write, let us see, I can write 1 comma 1. Will it be correct? Yes, it is correct. Why it is correct? This is correct. Because this row has an index what this row, this row has an index, I told you this row will have the index one. And from this row, how many rows will be selected? Only one row will be selected. So, you will get what you will get Sonali. Okay. So, Sonali is getting the second lowest sally in department number 20. And if you want to have the second highest sally in department number 20, what is, which is I am by getting the second highest sally in department number 20, she is Neha. So, I think you have just have to make this one change in this query to get the second highest sally. Case statements are very important when you design SQL queries. If you want to design complex SQL queries, then you have to understand how you can properly work with case statements. So, understanding case statements is the backbone of writing effective SQL queries. So, this is the basic format of writing any case statements. So, when you, so case is almost like, it is like the, it can be said about, you have done casing in C++ programming also. In C, you have done it. So, you match it against a certain value. And then, you know, you perform the actions accordingly. For example, in this slide, what we have written, if you just observe this case expression will be matched against case expression one, and then it will be matched against case expression two. If it matches against case expression one, then these commands, you know, this command will be executed. If it matches against case expression two, then this command will be executed.    So, this way, you write case statements in MySQL. So, if you don't understand it, then let us try to understand it with the help of examples. So, whatever I will do, I will just go to my MySQL prompt. And I think, you know, I've just opened one window. Okay. Yeah. Okay. And I will use the published database. This is my published database. And so, case statements you generally use when, you know, there are some conditions which are involved. For example, I want to find out whether a number is even or odd. So, select, suppose I set a number and suppose I set the number to be 10. Okay. Suppose the number is 10. Now, I want out. So, I have set it to certain value. Now, I want to find out whether the number is even or odd. So, what effectively, what query can I write? Okay. So, I'll just open the notepad. And, you know, I'll, you know, formally, I'll state the query. The query is write a query to find whether a number is even or odd. Okay. So, we have to do this. So, we have to write a query to find whether a number is even or odd. So, writing a query for this. So, there are two, you know, there are two conditions involved, you know, if it is even or if it is odd. So, what can I write? I can write something like this. I can write, suppose I can write select. First of all, I will write select. And then what I will write, suppose case. And then the number, what is the number it is denoted by add the right number. So, you have to, you can declare the variables in my SQL like this, you know, set number equal to 10. So, that is setting this variable that is, you know, to number variable to 10. Okay. Then I will, you know, I will perform the modular operation, modulus operation by 2. Okay. So, when it is 0, then, you know, the number is what the number is. Okay. Even and when it is 1, then, you know, then the number is what it is odd. Okay. And then I will, you know, and so always you have to end the case, you know, similarly, you see, I have ended this case using this end. Okay. And suppose I can give an allies name to this column also, I will write down, not give any allies name to this column. So, you know, you can get what is happening behind the scenes. And then, you know, I will just, I will just copy it. And it is very simple, you know, I will just paste it. Let us see what is out. Okay. So, you get that number is what number is, number is even because 10 is even. Suppose I set the number to piece. Suppose I say, set at the rate number equal to, say, suppose 11. I set the number to be 11. Now, again, I will copy.  So, I will analyze this column.  Suppose even or suppose even or odd. Okay. Okay. I write like this. And then, suppose now I, you know, I copy it. And then, suppose now over here, I just paste it. So, I think it is not get copied. I will copy it. And then, again, I will paste it. All right. All right. So, now you get the value is even or odd to be odd because, you know, 11 was an odd number. Okay. So, I have given, you know, some allies name for the column. So, suppose EC double OD. This, you know, you can take it to be anything you can do it, you know, whatever you like, you can end this column. I have named it to EC double OD. Okay. So, this much is clear. Now, I have this employees table. I will write the query, select start from the employees. This is my employees table. And now, the organization has decided something for the benefit of the employees. Suppose, say, I will, you know, I will just remove this query from here. And then I will formally state the second query. Write a query to conditionally increment the salary of employees. Okay. And what is the condition? If the employee is a manager, then the salary is incremented by, say, suppose, by 2000. Okay. And then I will copy it. If the employee is, say, suppose, if the employee is a developer. Okay. If the employee is a developer. If he is a developer, then Sally's incremented by, say, suppose 1000. Else, I will write Sally will remain the same. Okay. So, here also, you can observe these conditions. If the if some employee is a manager, then his salary has to be incremented by 2000. If the employee is a developer, then his salary has to be incremented by what? It has to be incremented by 1000. And if it is not a manager or a developer, then the salary will remain the same. So, what, you know, how will you write this query using case statements? Go for it. I will just pause the video. We just try out how will you write this query? Okay. I am not able to get it. Then let me write it. How will I write it? I will write it in the nodepad. Okay. Suppose I say select ename, select ename comma salary comma case, I will say for the, okay, I will select designation also from here. Designation, designation comma case designation. Okay. And what is the cases for designation when the employee is a manager? When the designation is a manager, then what will I select? Then I will select Sally plus 2000. This is what I said. And when, you know, when the employee is a developer, then what will I select? Then I will select Sally plus 1000. Else, you know, in every other employee who are not managers or developers, the Sally is going to, you know, remain the same. And then, you know, I will just end this case statement and I am going to analyze this as suppose say net Sally. Okay. And from which table I have to select it? From the employee's table I have to select it. Okay. So, I have written this query. Let us see if it works out or not. I have just copied it and I will just paste it. Okay. So, now you see this Nikhil is a manager. See Nikhil over here is a manager.  Okay. And Nitin's Sally, Nitin is also a manager. His Sally is also getting incremented by what? By 2000. Okay. And then you see there is one employee Sonali and Sonali is a developer and she is getting 50,000. But her net Sally is now 51,000 by because she is a developer. Okay. And for developers, the Sally has to be incremented by what? 1000. So, you see, I have not performed any updations over here and let us try to see. I have not, you know, first of all, let me be very clear that I have not performed any updations here. For example, when I say select star from employee's table, when I say like this, you see this Nikhil Sally is still 120,000, you know, but I have not performed any kind of updations over here. This is just for display purpose. Okay. But now let us try to understand this query. So, I will, you know, I will just take the pen tool and, you know, I'll try to, you know, make you understand the query. So I will take this tool and this is the pen tool. Okay, I've got it. Now you have the select statement. Select statement. I already told whenever you work with select statement, select statement works from row by row. Okay. So it is going to go from one row to the next row. First of all, it will go to the first row. It will select what? It will select the name. It has selected the name. It has selected the Sally. It has selected Sally. You said designation, it selected the designation. And now what it is saying case designation. So in the case of Nikhil, what is the designation? It is manager. That is why it is getting matched against manager. And what is being getting selected? It is salary plus 2000. Who's Sally? The Sally of the current row, that is Nikhil Sally. And Nikhil Sally was one like, you know, one like 20,000. So the new Sally of Nikhil is one like 22,000. Then it will go to the next row. Again, it will select ename. That is Nathan. It will select Sally. Sorry, that is 80,000. It will select the designation that is manager. And then it will go for case designation, the current designation for, you know, it is in the second row. That is mean the current designation. It is about Nathan. So the designation of Nathan is again manager. Then again, it is going to be matched against this and it is going to select what? It is going to select the Sally plus 2000. And who's Sally? Sally of Nathan. Sally of Nathan is 80,000 and now it is showing it to be 82,000. Okay. Now, for example, it is going over here. Suppose it is going to Anshita and Anshita is an analyst. It is selecting Anshita. It is selecting, suppose what? It is selecting this Anshita. Okay. It is selecting Anshita, 6,000 analysts. And then it is again going to this case statement. Okay. And the, you know, in designation, I have written what? Designation will be now analyst in case of Anshita. So it will not be matched against this, against manager. It will not be matched against developer. It will not be matched. What will actually run? This L's part will actually run when it is going to Anshita's rows. Okay. And what is being selected? It is just being selected as a Sally. And what is the Sally of Anshita? It is 60,000. So you see, in this case, net Sally is getting, you know, net Sally, the Sally is also 60,000. And the net Sally is also coming out to be what? It is coming out to be 60,000. Okay.  Okay. Clear. So, all right. So I have written this query. Okay. Now, one more interesting query for you. I hope you try this query on your own. So, you know, I'll just move it. Third query. Prefix every name, prefix every name in the employees table with Mr. or Mrs. Depending on the gender of the employee. Okay. I want to write this query. I want, you know, for example, over here, if you just, I just, you know, I just do a select star from employees table. For example, this is Nikhil. And the gender is male. That is why I have to prefix the name of Nikhil by what? By Mr. Okay. Suppose Sonali is, she is a female. So I have to prefix her name by what? I have to prefix her name because, you know, in gender, it is what it is what it is written as F. So she is a female. So I have to prefix her name with what? With what? I have to prefix her name with Mrs. Okay. So you just write this query. See, this is the query. Just write the query. Prefix every name in employees table with Mr. and Mrs. Okay. Depending upon what? Depending upon the gender on the employee. Okay. She is either a male or female. So try, try this query. Okay. I'll just wait. I'll just pause the video. Okay. If you're not able to get it, then I think this is also, you know, pretty, you know, I mean, I'll use one singular function for this. You'll get it. So we'll eventually cover this function and you'll know when we will cover singular functions change singular functions in my SQL. We will cover it. But so let us, you know, let us do it. Suppose I have to get the, you know, suppose I say gender also ename comma gender comma case what I have to write case on what I have to write case on gender. Okay. Because through gender, I'm deciding whether I have to prefix a name by with male or I have to prefix the name by no by Mrs. Okay. If he's a, if he's a male, then I have to have to prefix it by Mr. I'm she's a female, then I have to prefix it by what by Mrs. Okay. Case gender, when, when the gender is what it is male, then what I have to write, then I will write something like this concat. So concat function is a function that is used for what, you know, concatenation of strings. So I will like Mr comma name. Okay. And then when it is what it is female, then I will again use the concat function.  I will write Mrs. Mrs comma what? Okay. And then I will end, you know, end because there are only two conditions male and female. Okay. So we are not considering considering the third gender. Okay. Mr. and Mrs. And then I will end and then from this table, I'm, you know, I'm selecting all these records from the employees table. Okay. And okay.  So I'll, you know, I like the column also I'll copy it. Um, sorry, sorry, sorry, sorry, I'll just, I'm going to copy it. It's not getting, I'm just going to copy it. Then I will go to the notepad. I'll just paste it over here. And I will remove this. Okay. This I'm going to remove. Okay. So it is properly invented also.  Okay. And now suppose I, you know, I copied and then I run this query, let us see what is the output. Okay. All right. You got. Now, for example, Nikhil is what Nikhil is a male, that is why it is printing Mr. Nikhil. Suppose, Anchita is a female, though it is what the new name is becoming Mrs. Anchita. Why? So now again, you know, you can, if you want to understand this query, let me explain it once again. Although I've explained, you know, but still, you know, if you get any kind of confusions, then I'll explain this query also. I'll take the pen to, for example, how this query will run. So select statement is here, you see select statement. And then I say select statement, select statement will run how select statement will run from row by row. Okay. It will run from row by row. Okay. So first of all, it will go to the first row, this it will go to Nikhil. And then it will select what it will select the ename, it will select Nikhil, then it will select gender that is m. And then it is selecting performing case against what against gender. So gender in this row, current row is what it is male. So it is getting mashed over here. And then what I am printing concatenation of Mr. and ename. So the current ename is what it is Nikhil. If it is going to this, suppose Sonali's row. So Sonali here, the case gender is what it is female, it is getting mashed over here. And then it is what printing what concat Mrs. plus what ename and what is the ename of the employee, this employee, it is Sonali, that is why it is printing out Mrs. Sonali. Okay. So in this way, this query is working. Okay. Clear. This video we will study about string functions in MySQL. So all the operations that you know, we generally perform on all the strings that we will study. All right. So let us begin with the understanding of what are string functions. And before starting string functions, let me tell the time only dealing with single row functions. Okay. Single row functions. So what is single row functions? It means that they operate on one row at a time. Okay. So the first thing function that we will be covering is the length function. So how does length function work? Let us see. Suppose I write one query and I write this query like this. Select length of suppose Amit, then what will be the output? You will get the output as follows. Suppose I say length of Vishal, then you are getting length. So this is giving the length of every name. For example, I say select ename from employees table. Okay. Then you are getting the name of every employee. But now if I say select ename from a length of what length of ename from which table from the employees table, then you will get the length of every employee. So this is the length function. Okay. Then there is one more function that is the upper function. So how does the upper function work? Suppose I say select upper Amit from employees, then this will give you what? Every name in what? Sorry. Select upper, select upper. Okay. Select only upper Amit. Then this will give you what? This will give you Amit. Okay. And if I say select upper of ename from the employees table. Okay. Select upper from which table, from the employees table. Okay. Then this will give you every name in what? In which case, in the upper case. So you can easily observe that these functions, you know, these functions, this upper function is also a single row function. Why a single row function? Because it is operating on row by row basis. Because it is going to the first row and then you know converting it into upper case, then going to the second row, converting it into the upper case. Then there is one function that is a sub string function. Suppose I write the sub, sorry, sub string. And I write, suppose Nikhil and Nikhil and I say, from the second position extract four characters. All right. Then you are getting I k h i. Okay. From the second position, I have extracted how many characters? Four characters. So this is sub string function. Then there is one more function that is the INSTR function. Okay. Then what is the INSTR function doing? It is, you know, it is finding the index position of a substring within a string. For example, I say Nikhil and I say, suppose I, then what will it give me? It will give me the first index position of occurrence of I that is second position. Then if I say suppose INSTR of suppose I say k, then it will give me what? It will give me three. Okay. Like this. Then this is your INSTR function. And yeah, there is one more function. Okay. We will see all these, some more functions we will cover later. Let us try to work with these functions. Okay. So let me, you know, formally state the first task that is in that you have to do. And you have to do, you have to write this query. Okay. And there is one more function. Let me tell that's most important. That is the CONCAT function. Okay. The CONCAT net CONCAT function. Suppose this is used to CONCAT net two strings. Suppose I say Nikhil and the second argument I give to be supposed to us, then it will do what it will do? It will CONCAT net these, these two strings. So it is used to merge or combine two strings. Okay. This is the CONCAT net function or CONCAT function. All right. Now you have to do this query. Okay. You have to do this query. Generate the full name from first name and last name with space between first and last name. So this is your test table select star. Okay. I'll, you know, I'll, I'll write it. This is my test root table. Insert into test two values. Suppose Amit and the second argument is Shah. And suppose Agarwal and the first argument is Webhav. Okay. Webhav Agarwal. Let us just take two names. Select star from which table, from test to root table. Now you have to, you know, you have to generate the full name and full name with space between the first and the last name. Go for it. This is your first task. I'll pause the video. Okay. If you're not able to get it, then I think it can be done. Select first comma what last comma what we have already studied the CONCAT function. So I will use CONCAT and arguments that I will give first comma what comma last from which table from the test to table. So you're getting concatenated name. But I first of all, let me, you know, analyze this column as, as say, suppose a full name column, let me analyze it. Okay. So you'll get this. Now I don't want I want there should be a space between the first name and the last name. Okay. A space between the first name and the last name. So what will I do? Tell me. I think you can approach it. Again, I can perform a more, you know, one more level of nesting of CONCAT.  And then now if I write this, then let us see what will be the output. Now you're getting proper full output. Why? Because, you know, you are having two level of nesting of CONCAT function. So the inner CONCAT net function will, you know, prefix the last name with a space, and then you are concatenating what you are concatenating concatenating the first name with this. So you'll get, you know, space between the first name and the last name by using this. So this problem has been solved. Okay. Now let us see what is the second problem query to display name in INET cap. Okay, you have to display the name in INET cap. So what does name in INET cap mean? INET cap name in INET cap means this. If the name is say, suppose Nikhil, then I have to write Nikhil. Okay. For example, for example, Nikhil becomes what, you know, the first alphabet has to become caps and the rest alphabets have to be what they have to be in small case. The first alphabet has to be in what, in which case? It has to be in upper case. Okay, try this. Okay, I'll pause the video. Okay, if you're not able to get it, then let us try to solve it. So this is the list of names from the employees table. This is, these are all the names. Now I have to take, now what I have to do, I have to take the first alphabet of every name. How will I take the first alphabet of every name? Select ename comma substring, substring of what of ename. From first position, take how many characters, one character, from which table, from the employees table. If you do like this, you will get the first character of every name. Now what I do, I convert it into upper case. So upper, upper of this string. Alright, now if I write them like this, then what will be the output? Let us see what is output, you know, now you have taken the first alphabet of every name and converted it into which case, converted it into upper case. Okay, I will write, you know, I will, I will, I will analyze this column. Okay, I'll analyze this column also. Now you have get it. Okay, I need cap.  How will you concatenate it with the rest of the string? I'll take, you know, I'll take a new paint. And I don't want to save it. Suppose the name is what, suppose the name is Nick Hill. Okay, and then you have taken this first, you have taken this first, first alphabet and converted into upper case. Now this remaining IKHIL string is what it is remaining. So you have to take this, how will you take this? This will be done with the help of substring position from which position you have to take the substring from the second position. And how many characters I can say the length of the name? Because you know, suppose length of the name is six from the second position, you will you take six alphabets. So you'll get the all the remaining string out of it. Okay, so what can I write? I can write something like this. Okay, I'll take this. Okay, here, here I have to concatenate concatenate what? Concatenate this, you know, upper with what? With a substring and substring of what substring of ename from which position with us from the second position. Sorry, till which position till the length of ename. And then I will end this concat function. Let us see if it is giving the required output. Yes, it is giving me the required output. So you have converted a name into Init cap. Okay, so we have solved this problem also. Again, now there is one more problem for you. And let us see what is the problem. Display name in form like Nikhil Srivastava has to be converted in, you know, n dot Srivastava.  Anyway, this is also a sizable amount of very complex query. Okay, how will you approach this problem? Let me tell you, how can you approach this problem also? Let me go to paint. Okay, suppose I'll take a new paint document new. I don't want to save it. I want suppose the name is Amit Shah, A M I D S H A H. Okay, you index every position. This is the first index position. This is the second, third, fourth, this is the fifth space is coming at it position, fifth position, then shy is coming at six position. Okay, and so on. Okay, now what I have to do, you have to first extract this string, this A, and you have to convert it into what uppercase, so you will get capital A, then you have to concatenate it with what you have to concatenate it with a dot, and then you have to concatenate it with what shot this you have to do. But remember, but remember one thing, how will you get this substance? How will you get this substance, this substance, this substance for that for getting substance, you'll have to definitely use which function, the substring function, substring of what substring of name and from which position, the six position to the length of the whatever the name. Okay, but here how you will be getting this six, see this six position is equal to five plus one. That means for come you have to compute first of all this at which position this five is occurring. So you have to compute the index position of this space and then you have to add one to it and then you will get the rest of the name. Okay, this way this logic we are going to apply. See if you are not able to get it, then let me you know go to the, see now I have explained this query, now you can write this query on your own, at least you can try it. If you are not able to do it, then let us solve it. Select ename, ename from which table, from the employees table. See this is the ename, then you know this part I think I have already covered substring of what ename, from which position, from one, one. Okay, and then you know you have to convert it into uppercase. So I convert it into which case the uppercase, so what will I get. As suppose I will analyze this column as s name, so that you know. So we, okay, sorry, selects the, I will not use this table. Selects are from test two, this is my, sorry, select, sorry, just one minute. Selects start from test one, suppose this is my test one table, it is empty. And now I say insert into test one values, suppose Amit Shah, sorry Amit Shah, and then suppose Anshita Mata, okay. And suppose one more name I will insert, suppose Kiran Tamkala, okay, like this. Then now if I say select star from which table, from the test one table, if you do that, sorry, then you are getting these names. Now if I write, if I write something like this, test one, and this is not ename, this is name, similarly here this is not ename, this is name, okay. Now if I write this query, let us see what is output. So we are getting the first, you know, the first character in upper case of every name. Now I have to concatenate it with what? I have to concatenate it with a dot. So how will I do it? I will do something like this. I will write a dot over here, and then, okay, if I close it, then you will get something like this, okay. Now you have to do what? You have to add, for in case of Amit Shah, you have to add what? You have to add Shah in the end. For getting Shah, you have to