 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. 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 sid and then I will say sname and then I will say age and 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 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. I mean, you will get the, you know, you are intelligent enough to, you know, get it what I want to, what exactly is the query which I want to ask. 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 many times, you know, some, you know, at in your tables in your relational database, many times the values are null. 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 copy it and you know, again, you know, a very simple query. 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. And this is, you know, a common confusion. 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. So this way you know you work with null values. 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. For example, Rashmita was having the lowest salary, you know, then you know, it is coming first and then giant was having the next highest the second lowest salary, then it is coming like this. 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 if you sort by, you know, so department IDs are, you know, 10 20 and 30. 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. Okay, if you are not able to, you know, get it, then you know, you can better visualize this, you know, with this, I'll just open the pen tool. So if you observe, then you know, first of all, it is having this, you know, when I say order by department ID, you know, then, you know, you form these, you know, department ID, there was department ID 20. 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. So you don't have to put ASC, but still if you want, you know, you want to, you know, still better describe your query than you can use explicitly use ASC also. Okay. 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. Now, first of all, I, you know, first of all, how this query will execute, you know, this query looks out to be very simple, but I will just take one, you know, few seconds, you know, to explain how this query will execute. 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, I want all the employees, so that I want three, I want employees who are getting the, I want the employee who get the lowest Sali, the second lowest Sali and the third lowest Sali. 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. So, please, you first try out on your own. 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. You have to learn by, you know, you have to learn from the go, you know, when there are some challenges, then you, you know, you try to work on them, you try and then you many times you fail also. But failing also, you know, you're failing, but still you are learning new ways to success. 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. And then, you know, you know, this row, this, sorry, okay. 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. Else, you know, you can also, you know, give a default route. If nothing matches, then, you know, you can write else and, you know, this thing is going to execute. And then, when you, you know, when you terminate a case, then you write end. Okay. 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. Now, what I will do is that because, you know, so this big name is, you know, this column name is getting this much big. So, I will analyze this column. So, how will I analyze this column using, you know, as I will get, I will give an allies name. 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. So, Nikhil net Sally has become 12,000, you know, 1,22,000 by because he is a manager and he is getting an increment by 2000. 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. So in this way, you know, it is going to every row and, you know, perform these case operations. 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. And this time I will write what I will write not Mr. 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 you get, you know, suppose one employee is in the kill, then, you know, okay, I'll just, you know, okay, suppose I'll just take the pen to, you know, I'll just explain this query before explaining, I'll just let me, you know, do some modifications I forgot, you know, to, you know, I like this column. 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. And here I will give the, you know, as, you know, suppose I call this, you know, this column, then I can write something like this new name from which table from the employees table. 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. Suppose the first I give this argument, and then I write something like this over here, I give a space. 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. Now, what you have to do, now you have to, you know, you have to concatenate it with the rest of the string, you have to concatenate it with the rest of the string. 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. So let for, you know, for simplicity purpose, let us keep this, you know, in small case, because otherwise, you know, unnecessarily will have to do some more things and the query will become more complex. 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 get the index position of what? This space, and for that you will have to use the INSTIR function. So how will you get it? Let me explain it. For example, I will just, you know, I will, I will say substring, substring of what? Substring of a name, and at what position suppose, sorry, name, comma, space, and the third argument which I pass is suppose the length of name, okay, then what you are getting output. See, you are getting output Shah, Mota and Tangala, because you are getting the last name out of it. How you have get it? How you have got it? You are doing the substring function and substring of what? Substring of name. From which position? From the, from the position where, you know, okay, there is one issue with it, you know, I will add plus one to it. Why? I will add one to it, because you know, this, okay, now, you know, there were spaces, you know, some, some leading spaces were there. I have removed these leading spaces by, because it was taking substring from the space itself. So if you know, if you go to this paint, you know, you were getting this, you were getting, here you were getting it at this space, it was there at this, with this fifth position, you have to add one to the fifth position. So, okay, so this is what I have done. I have one, I have added one to the position of occurrence of space and then the rest of the characters. So that can be specified by length of the name, okay. In any case, it will, it will be bigger than that, okay. So, you will get this output. Now, I have to do what? I have to concatenate both of these. So, how will I do it? I will again use what? I will again use the concatenate function. Sorry, the concat function. And then I will put one more bracket over here. And then I will what? I will write, suppose this name from, so you are getting the output. Because many times, you know, it is a requirement that you have to write your names, you know, in, in such format on some on your board in your home and anything like that, okay. Clear. Now, let us move forward and yeah, there is one more query. This is the query to find the mid alphabet of a name, okay. So, what do I mean when I want to say the mid alphabet of a name? Suppose I say the name is Nikhil, okay. Or, okay, yeah, Nikhil, N-I-K-H-I-L. So, what will be the middle alphabet? See, if you find the middle alphabet, this is of length six. So, it is not possible. So, in this case, the middle alphabet will be K-H-Y because this is of even length. So, if the string is of even length, then the middle two characters will be of, you know, the middle alphabet. But if the string is, say, suppose Krishna. So, how, what is the length? See, it is of length seven. So, in case of Krishna, the middle alphabet will be what? S, okay. So, now your task is to find the middle alphabet in this employees table. For example, I will go to the company, you know, and select the name from which table, from the employees table. See, this is my employees table. And now you have to get the middle alphabet. For example, in the case of Nikhil, the middle alphabet will be K-H-Y, but in the case of N-I-K-H-I-L, it will be what? It will be P. So, I will give you a hint. You have to use case in this case. Why you have to use case? Because there are two cases. For the even length string and for what? And for the odd length string, you have to form two cases. How will you go for it? I will pause the video. Okay. If you are not able to get it, then let me solve it. But first of all, you should have tried it on your own. I am assuming that you have, you know, tried it on your own. What will I write? Select ename comma what? Case, how will I find this, you know, this string is even length or odd length? I will do, I will say length of ename divided by length of ename modulus two. Okay. So, if the length of the ename modulus two is what? If it is zero, when it is zero, then it is what? It is even length. Then for even length, I have to get a substring of what ename? From which position I have to get? I have to get it from the length of ename divided by two. And how many characters? Two characters. And when it is one, that means the, you know, it is, that means it is odd length. So, for odd length, I have to, you know, from the middle of the string. So, middle of the string, you will get by length of ename divided by two. And from that position, you have to get how many characters? One character. I have just forgot to write n over here. So, I will write it again. And then I will end as suppose, from which table? From the employees table. Now, if I just copy it and then over here, if I go and just paste it, let us see what is out. I will just not copy it. I will copy it. Here, if I go and just paste it, let us see what is out. But you know, okay, for nickel, you are getting k h, for nothing, you are getting t. So, are getting the middle alphabet of every. So, how it is working? Select statement will always work from row by row. And for every row, it will test this condition. Is the length of ename divided by two is equal to it is zero? Or is it equal to one? For example, in nickel, it is of length six. So, the modulus will be what? It will be zero. So, it will go to this first condition. And it will take the substring of which ename? That is nickel in this case. And then it will divide it by two. Length of ename will be what? Six divided by two. What it will be? It will be three. And from the third position, it will take how many characters? Two characters. For example, N i k. K is at third position. And from this position, take how many characters? Two characters, okay? Like this, okay? Similarly, in the case of Nathan also, it is an odd length string. So, it will go to this position. Okay? Clear? Like this. All right. So, we have done this also. Again, let us see what are, yeah. Let us have, let us try to solve this for you. So, query to pad, lift, spaces and name that star, okay? But for that, I will have to, you know, I will tell you some more functions. There is one function that is the L trim function. L trim function is used to know, remove the left side, you know, leading spaces from a string. Suppose I say, I don't say, I say Krishna, select L trim Krishna, then it will give you what? It will give you Krishna. If you just select Krishna, for example, if you don't say, you know, if you don't, then you Krishna is having some spaces over here. You can see it is having some spaces. But what L trim is doing, it is removing all the leading spaces from the left side. It is removing all the leading spaces from the string. And, you know, you can verify this also. For example, I say, select length of what? L trim Krishna. If I write this, then you will get 7. Why? Because Krishna is of 7 length and it has, you know, it has removed the, you know, left side spaces from Krishna. So, it is coming out of what? It is coming out to be of length 7. I think it should be clear. And there is one function, the repeat function. It is used to repeat some strings, some specified number of times. For example, star 5. If I write this, you know, it is repeating star, how many times? It is repeating star 5 times. Now, there is one table, select star from, I think the test 2 table is 0. No, select star from test 1 table is 0. This is one test 1 table. I will say delete from test 1. Okay, I delete it. And then I will insert into test 1 values. Suppose I will insert some names Krishna. Suppose the next name is with more spaces. Suppose the name is Govinda. And suppose the next name is Madhava. And suppose, suppose there is a name and the suppose the name is Gopal. Okay, these are the names. And then when I say select star from which table from the test 1 table, then what will you get? Okay, you are having spaces. But now my task is to do something like this. Okay, I have to, this is the task. Query to pad left spaces in name with what in star. What does this mean? This means something like this. For example, there is name, one, there is one name Amit. And before Amit, there are three spaces. And then Amit is coming. Okay, so I have to replace all these spaces with what I have to replace them with star. Okay, clear? I have to do this. So try to do it on your own. I'll pause the video. How will you go for it? See, I'll give you one hint. Okay, if you're not able to get it, select name, comma, if I write something like this, length of name minus length of, suppose L trim of name. If I write from which table from the test 1 table if I write what you're getting, see, you're getting the number of spaces. See, in this case, for example, if you just observe, here, Krishna is having five spaces, Govinda is having how many spaces, it is having 13 spaces how the length of whole name, for example, if you go over here, they know the length of the whole string we bought, it will be equal to three plus four, that is seven. And so length of name in this case will give you seven and then you subtract what length of L trim name. So if you have L trim name, then you will you'll be left with only what the summit and this will be equal to what four. So seven minus four equal to what seven minus four equal to three. This is exactly the number of spaces that are present in the name. Okay, clear? So I have counted the number of spaces that are present in the name. Now can you approach this problem? I think you can do it with the help of repeat function. For example, now if I say, okay, try it on your own. Pause. Okay, not able to get it. Watch me do it. Repeat what? Repeat star. How many times these many number of times? Okay, these many number of times from which table from the test 1 table. See, for Krishna, I'm getting five stars for Govinda, I'm getting, you know, 13 stars for Madhava, I'm getting five stars and for Gopal, I'm getting 14 stars. But now this is only half of the task. The remaining task is to concatenate it with what? It with Krishna, Govinda, Madhava, and Gopal. So what will I do? I will just use the concat function. How will I use the concat function? Concat. Okay, concat this with what? I have to concat this with L trim of what? L trim of name. If I write like this, then I think I will get out. Yes. Okay. Yeah. And suppose I will, you know, I will, I will analyze it also. Suppose I say it is new name. Okay, clear. Okay. So let us now return. I think there is one more function that we you can use to, you know, solve this problem in there is one function replace select replace and what does a replace function does? Suppose the name is Amit and I replace A with say suppose B. Okay, then what will it give every occurrence of V A will be removed but replaced by what it will be replaced by B. So I can do something over there also select I suppose select name comma replace replace what name and in name replace what a space and replace a space with what a star. So this is a, you know, a cute method of, okay, from which table from the test one table. If you do like this, sorry, I think there is some problems. Select name comma replace replace name. Okay, there are two problems. Sorry. All right, now I think it will be correct. So you have got the same output using the replace function also. So let us, you know, move to our last task. And that task is, you know, performing a credit card masking. Okay, you have to perform a credit card masking. So what is credit card masking? I'll tell you what is credit card masking and what you have to do. I'll take a new file. And I don't want to save it. So many times when you you do some kind of transactions, suppose your credit card number is 21265212 and 7410. So it can be a privately of any length. I'm not I'm not concerned about the length. But it's it masked is how it masks it. Because you know, you only get to see the last four digits, you know, rest are all masked with what x. So here 222126 will be replaced by four x and five 212 will be replaced by four x's. And then you will get what 7410, you'll not get the whole number. So I am you know, I am having some credit card numbers and I want to mask them. How will I mask them using string functions? Okay, I'll just you know, I'll just go to here. And I'll write, sorry, delete from which table from the test one table, I'll delete everything. And then I will, you know, insert into test one values. Okay, and I'll insert some random numbers. Okay, and I'm not concerned about the length. Only thing is that the last four character last four digits should be you know, should be visible. And the rest of all of them should be replaced by what should be replaced by x. Let us insert one more row. And then some random values. So suppose these are the credit card numbers. So I'm not, you know, in this case, I'm not worried about the length of the credit card numbers. For example, you know, in my I work in India. So in India, you know, credit cards of 1616 digits. But here I'm not worried about the length. Okay, clear. Now how will I approach this problem? Can you can you think how will I approach this problem? See, I'll give you a hint. I use a formula to strategy strategy for doing it. For example, see this number, it is of which length, it is four plus 48, then it is of length 12. Okay, so out of 12, if you subtract four, you will get what eight. So you have to replicate x eight how many x how many times you have to replicate x eight times. And then when you replicate x eight times, then you have to concatenate it with what 7410. That means the last four, last four digits. Okay, so I've given you a hint, you can try the problem on your own. I'll pause. Okay, if you're not able to get it, then let us try to solve it. I'll go to mine. Okay, here, select name, select name from which table from the test one table, you have got it, all the names. Now I will say select name, comma, length of name minus four, if I write, then what will I get? Okay, I'm getting something like this. Now, if I write, replicate, replicate what? Sorry, capital X, how many times these many number of times length of name minus four, then you will get what? You'll get, sorry, oh, really, it is not really get it is replicate. Oh, it is not replicate. I am sorry, it is repeat. I'm sorry. Replicate is in my SQL server. In my SQL, it is repeat. Okay, so you're doing getting something like this. Now, if you want to get the last four alphabets, what will you what will you write? Necessarily, you will use a which function the sub string function and sub string of what? Sub string of name from which position, from the length of name minus fourth position. Okay, and till what I can safely say till sorry, length of name. Okay, if I write to this, then what will be the output? Okay, so you are getting the last four digits, I think length, I think it'd be plus one. Now, I think it should be good. Yeah, I have got the last four digits. Okay, and now my task is to what is to punk up on catnate both of them. How will I concatenate both of them? I will use the concat function on cat and concatenate this with this as masked name. Okay, masked name. Okay, now it is correct. Okay, so you have masked every credit card number. Okay, clear. Let us continue our discussion with some more, some of more date functions. And let us, you know, try to see how we can apply more date functions in some kind of real scenarios. So this is my employees table. I'll just, you know, I'll just show you my employees table. Select star from employees table. Okay, this is my employees table. Now, you are getting the high date also. Okay. Now, let us see some more, some more date related functions. There is one so you I'll not cover all the functions. I'll just cover a few I'll just tell you how to apply them. There is a there is a there is a date related function time set the timestamp diff function. And that is taking a unit and you know, that is subtracting, you know, finding the difference of two dates based on that unit. For example, if I say, if, you know, they have written this query, for example, timestamp diff month, and they have given these, these three days, these two dates, then it is, you know, finding the difference in months between these two dates. Okay, clear. It is something like this. See, I can write like this, select times stamp. Okay. And I want to say, suppose month. Okay. And I this, I just passed, suppose 2014. Suppose the year is 04. And the day is 13. And I pass the second day, suppose 2014. And the month is, say, suppose 09. And the date is suppose 23. Okay. If I do like this, then you, okay, you're not getting, all right, timestamp diff month. I have given 19. Okay, it is, should be 09. All right. Let us see. So you're getting a difference of five months between these two dates. Okay, clear. Now, if I want to define the difference between these two dates in, in terms of day, then what will I write? I will write day. Okay. So how many days difference is between 163 days? But if I write year, okay, then what is the difference in year? Because, you know, this, they belong to the same year. So that is why there is no, you know, the, there is no year difference between these two dates. So now you have to write a query. Suppose that the organization has decided to give some increments to the employees. So suppose 10% increment to all the employees who have worked for more than one year. Okay. Write a query to compute the new salary of all employees, which is, which is what, which is 10% of current salary. If the, if the employee has the work for more than one year. Okay. If the employee has worked for more than one year, then he will be getting what then will he be getting an increment of what a 10% okay. So he'll sadly suppose if, suppose if some employee has worked for say, suppose more than one year, and his salary say about 20,000, then he will be getting a 10% increment. That means he will be getting 22,000 as a new salary. Okay. So here is my employee's table. Okay. Select start on the employee's table. This is my employee's table. Now you have to find out the, and the, you know, what is, what will be the new salary of each employee. So how will you write the query? I'll just pause the video. Okay. Let me see. Suppose this is a name, select ename comma say suppose this is a salary comma this is the higher date. Okay. Now if I write times stamp and I give, I want a difference in year of the higher date and what and the current date. Okay. So if I write like this, then what will I get? Okay. Okay. I have to write something like this also from which table from the employee's table. Okay. If I write like this, then you will get a listing. Suppose I will, you know, I will analyze this column as suppose year work. Okay. These employees have worked for this, these many of years. Okay. So for example, you know, current, you know, when you say select, you know, current date, if you are selecting the current date, then the current year is 2017. And this, you know, this employee was, Nikhil was hired in 2015. So he has worked for, you know, two years. Okay. He has worked for two years and this employee has worked for what? He has worked for three years. Now I have calculated the, how many years every employee has worked. And now I have to compute from on this column, I have to compute what I have to compute, what will be the new salaries. So how will you do it? Okay. So there is function that is the if function. So we can use it, you know, how we can use it, I can use something like this. If, okay, let it be like this and is year work and I'll, you know, I'll go to a new line. How I'll write it. I'll just, okay. Let me do something like this. Let me copy it. Okay. Let me write the whole query in a notepad. Okay. I'll just, I'll just go to the notepad and let me write the whole query over here. Okay. Year work and then, you know, I'll select this. I'm going to select this. So these are not, you know, very simple queries. And then I, I write something like this. If, if this value is greater than what, if it is greater than or equal to one, then what I write, then I write salary plus salary into 10%, 0.1. Else I write salary. Okay. Suppose as new, as new salary from which table from the employee's table. If I write this query, then what will be the output? Suppose I copy it and then I go to over here and I just paste it. Let us see what is output. Okay. I'll just minimize it. Suppose now I paste it and if I run it, okay, you're getting some errors at line two time. Okay. What is error? Let us go over here. As new salary from employees it is getting. Okay. Let us see what is the error as new salary. Select if time step. I think there is a problem of one bracket. You know, I think I'll just add one bracket over here. Now it will be correct. Okay. If I again copy it, and then if I go and just paste it over here, then let us see what is the output. Again, I'm getting some errors. I end two. Okay. I have, you know, I have not added the comma over here. Now I think it will work. Okay. I'll just copy it and then again, I think now it will work. It should work. Okay. Let us paste this way. So I'm getting pasted. Let me copy it and then let me paste it over here. I think now it should work. Yeah. It's working. So now you can see you, for example, this employee, Nikhil has worked for two years. So his new salary is worth 12, 1,20,000 plus what? You know, it is getting 1,32, he's getting 1,30,000. For example, he has also worked for three years. So he's also getting what? He is also getting 10% increment. So his salary was 80,000. Now his salary is what? New salary is 88,000. But you take this employee, for example, Anshita, she has not worked for for how many years? For one year minimum period. So her salary is remaining to be what? It is remaining to be what? It is remaining to be 60,000. Okay. Clear. All right. Let us try and explore some more date functions and then we will practice some more stuff. Okay. Here you are getting, there is one function that is a quarter function. Okay. Let us try to work on it. So what is quarter function? So quarter function is giving you the quarter of a specific date. Suppose I will write U, U, A quarter and I pass the argument as a current date. Then it is giving me the Q, U, A, R. Okay. Then you are getting the quarter of the current, you know, the current date. For example, this belongs to the second quarter. Okay. So January, February, March belong to the first quarter, belong to the first quarter and April, May, June belong to the second quarter. Okay. So now you have this employee stable. Select star from employees. This is the employees. This is my employee stable. And now I want to find out in which quarter every employee was hired. For example, if I write select E name, suppose, sorry, I am writing hired date, hired date from which table, from the employee stable. If I say like this, now I want to find out in which quarter every employee was hired. For example, you say Nikhil was hired and you know, because he is employed in second month, that is February, he is employed in the first quarter. But you know, if you say Abhinav then Abhinav is employed in April. So that is why he was employed in the second quarter. Okay. So now I want to find out in which quarter every employee was hired. How will you approach this query? You have to approach this query with the help of case. Okay. How will you do it? Try it. Okay. If you are not able to get it, then let me solve this problem. How will I approach this problem? I will say something like this. Select E name comma what? Hire date comma, comma case. Okay. Suppose I write case over here, case. And when suppose I write quarter hired date, okay. When one, then I say suppose first quarter, okay. And then I just, okay, if I write like this, if I just copy it, control it. If I copy it and then I paste it, then I paste it again and then I paste it again. So here I will write second, here I will write third, here I will write fourth and here I will write fourth. Four, three and again. And here I will, you know, in this case, as suppose I say hire quarter from the employee's table. Okay. I write something like this. So I have, let us try to run this query. If it is running or not, then I will explain this query, okay. I will explain this query also. I will just, you know, I will just go to my SQL prompt. I have copied it, I think. Let me copy it and then I will paste it over here. Let us see if it is working. Yeah, it is working. See, Nikhil is being hired in the first quarter and Nitin is also, Nitin is being hired in what, in which quarter is the second quarter. But if you go to web of, he is being hired in which quarter in the third quarter, okay. So you are getting, you know, in which quarter every employee has been hired. So how this query is working, this is very interesting. You know, you can see, suppose I go to screen drawer and I take the pen tool, okay. If I take the pen tool, then you can observe. I think it is very simple to see, I think, because if you just go to this query, suppose you, the select statement will always work in which way. It will work from row by row basis. Okay. It will work on row by row. It will go to the first row. It will select the ename. It will select the hide it. And then it will work on what? It will work on the case statement. So it is taking case of what? It is taking the case on the quarter of hide it. Suppose the hired it for Nikhil is 2015, 2005. So his quarter is equal to 1. It is equal to 1. So that is why in case of Nikhil, first quarter is being, is getting selected. But if you go to Nitin, then in the Nitin, the hired it will become 2013, 06, 21. So the quarter will become, quarter value will come what? It will become 2. So in that case of Nitin, second, okay, okay. I have to, you know, I have to change this query. Little I have to change this query. Some, you know, some cosmetic modifications I have to make this, make to this query. I will escape this. You know, I am not properly formatted it. Let us write something like this, you know. It is not first quarter. It is fourth quarter. And then I will write third quarter and I will write, you know, what second quarter, okay. Now it is, you know, it is proper. It is properly looking query, okay. Copy it and then suppose if I go and paste it, yeah, you are getting, you know, proper, okay. So now you have got an, you have got an understanding how this query is working. Select statement is working row by row. And on each row case statement is also operating, okay. So you are getting the quarter in which, in which quarter every employee was hired, okay. Clear? All right. Now again one query using case statement also, I will move this. This is, so what was the previous query? Find the quarter in which each employee was hired, okay. And now suppose you have to write one more query, find whether the current day is weekday or weekend, okay. So I have to find whether the current day is weekday or weekend. So you have to do this query. Try to do this query. How will you do it? I will just pause the video. Just try this query. Try to do this query on your, for example, on my system, if I say the current date, you know, it is, you know, 23rd and it is Sunday. So it should give me weekend. It should not give me weekday, you know. But if it, if it is, you know, something, you know, suppose when not 23rd Apple, it would, if it would have been set as 25th Apple, then it would have been given me, you know, weekday, okay, not a weekend. So the current, if I, so if I say select current date, you know, so it is Sunday in my case. How we approach this query? Again, this also you have to approach by what? You will have to approach this by case sequence. How will you do it? See, I will give you a hint. Select a day name, okay. Select day name, current date. If I write, then I am getting Sunday. So you have to work on it. I have given you a hint. Try on this basis. Okay. If you are not able to get it, then let me solve it. How will I do it? Select, select what? Select day name. And the argument what I will give? Current date, okay. And when, when this is equal to what? When this is Sunday. If it is Sunday, then what I am going to write? Then it is a weekend. Okay. I will just copy it. And if it is Saturday also, so in most of you know, the IT companies which you work, it is off on Saturday also. So if I write, if it is Saturday, then also it is a week, what it is a weekend. And else it is what? Else it is a week, day. And then I will end the query as day of W. Suppose I say day of week, okay. D or W means day of week, okay. It is a short form. And then I will just copy it and I will paste it over. Let us see if it is working or not. Okay. It is giving some error. When Saturday, then weekend, okay. I have not, I have not given the case. All right. I think now it is correct. Yeah. All right. I will copy it and then I will just go and paste it over here. Let us see if it is working or not. Yeah. So today is a, it's a weekend, okay. Clear? All right. I will just ask you to write one more query and then I will pause this video. Select star from and not cover all the, you know, all the date functions. It is left upon you as an exercise to cover all the date functions, okay. Clear? Now, for example, now I want to have, suppose all the employees who were hired in the same month in which Jainth was hired, okay. I want to have a list of all employees who were employed in the same month in which Jainth was hired. So how will I write the query? Okay. I will just state the query. Write a, you know, query to find all the employees who were hired in the same month in which Jainth was hired, okay. How will you write this query? Write on your own. So if you have to write this query, you have to, first of all, find the month in which Jainth was hired. So how will you approach this? You can write something like this. We have already covered the extract function. What? Extract month from what? So you are getting four, okay. So if you want to find the month in which Jainth was hired, how will you write the query? You will say something like this. Select extract month from hired it from which table from the employees table where ename equal to what is equal to Jainth, okay. Then you are getting the hire date of what Jainth. And now if I want to find a list of all employees who were hired in the same day in which Jainth was hired, then what will I write? Then I will write something like this. See this query is giving me three. And now if I write something like this, select ename from which table from the employees table where extract month from hired it equal to this if I write like this, okay. If you run this query, what will you get? You will get Sonali Jainth and Neha, okay. So you are getting this. You can verify this also. Sonali is also, you know, Sonali she is getting on hired. And if you say about Anjita also, okay. Sonali Neha and then you talk about Neha, then she is also getting hired on which month the third month. So you are getting this query also, okay. So we have solved this query also, all right. Write a query to find all the employees who were hired in the same month in which Jainth was hired. So now you can, you know, there is one assignment for you. Here Jainth is also getting selected. So you manipulate the query in such a way so that Jainth doesn't get selected, okay. Manipulate this query in such a way so that Jainth doesn't get selected. How will you approach it? You just try on your own. This is left as an assignment for you. Again, let me see, select star from the employees table. This is my employees table, okay. Let me see if there are some more date functions which we can see, okay. Seconds and only str to date. Yeah, there is one more very important function str to date function. I think this is, you know, this function is used for converting some, you know, some, you know, some given string into a date format. So you have to specify the format in which you have specified the date, okay. For example, here if you just copy it, see, I'll run it. You know, it is getting selected was 2004, 431, okay. So it is, you know, converting into date. This string format you are converting into date, but you are also providing, you know, the format in which this string was given. For example, I could say something like this. If I were to write like this, okay, and then I have to specify the format like this, okay. All right. Suppose, you know, this year was not given in, you know, in four digits. It was given in two digits. For example, I say one, seven, okay. This was given in two digits. Then I have to, I think I have to specify small by over here, okay. Yeah, it is giving, okay. All right. So you have to do, this is also very important for, you know, like this. If you say, suppose you are not, you are first specifying the month, then you are specifying the day, then you are specifying what? Then you are specifying year. If I say you first specify the day, then what will you write? You first specify the day, then you specify the month, and then you specify what year. So here also, you will have to first specify what the day, then you have to specify the month, for example, zero, four, and then you specify what the year. So you are getting it. 31st, you know, 31st April, 2017, okay. Clear. This way it is working. All right. I think now I have covered all, I mean all of the, you know, most of the important functions to in MySQL date functions. In this video, we will study about some null-related functions in MySQL. So what is my database? This is the publisher database. Now again, you know, this is our own table. This is the employees table, okay. This is my employees table. And for some employees, the commission is null. So if I write select ename comma sadly comma commission from influence, then you will observe that for Anshita, for example, over here for Anshita, the commission is what? The commission for Anshita is null. And in the previous video, we saw something about null-related null values. So null values means an undetermined values. Any arithmetic operation on a null value also results in null value and null value are not the same as zero. So this we saw in the previous video. So now to just revise, you know, for a division, I want the name of all employees whose commission is null. So what variable I write? Select ename from which table? From the employees table, where commission is equal to null. Will it work? It will not work because you can never equate null values. You have to use the is operator if you want to find some, if you want to, you know, perform some comparison related operations on null value. So you have to use is, okay. So this is a brief revision of null values. Now in this video, we will study about some null related functions. So first is the if null function, okay. So I will just, you know, remove it. And this function is the if null function. If null function is taking two arguments expr1 and expr2. And what it is doing? It is doing something like this. If expr1 is null, then it returns expr2. Else it returns expr1, okay. So it is taking two arguments, this function, this null function. It is a singular function and it is taking two arguments. It is a singular function. That means it executes on each row and it is taking two arguments. If the first argument is null, then it will return the second argument. Else it will return the first argument. This is very simple. For example, I say select if null, suppose I say 3 comma 4. So it will return what? It will return 3. Why 3 is the first argument? See, what is the first argument? The first argument is 3. What is the second argument? The second argument is 4. Is the first argument null? No, it is not null. That is why it will return which argument? The first argument. But now if you say the first argument will be what? Null. Then what? It will return. It will return the second argument. And this is effectively what I have written over here. You see, if the first argument null, then it returns the second argument. Else it returns the first argument. So this is the if null function and it is a singular function. Now let us move on to our original problem. And I want to compute the effective sally of all employees. So what is a query? Query to find the effective sally of all and this is equal to salary plus commission. It is equal to like this. So what will I say? Select ename. I have written this. So I can you know. I think yeah. Select ename comma sally comma commission comma what? Salary plus what? Commission as suppose I will say effective sally from which table? From the employee's table. So now there is one problem with this. Suppose for Nick Hill, the sally is one like 20,000. The commission is 30,000. So the effective sally is coming out to be 150,000. But for Anshita if you observe the sally is 60,000. But the commission is what? Null. So effective sally is coming out to be what? It is again coming out to be null. Why null? Because any arithmetic operation on a null value also results in what? A null value. See here I have added sally to commission. The arithmetic operation plus was applied and commission for Anshita was null. That is why her effective sally is also coming out to be what? It is coming out to be null. We don't want this to happen. So how can you control this? You can do it by using the if null. If suppose you do something like this. If null commission and you know comma 0 as effective sally from employees. Now if you run this query, you see for Anshita the sally is 60,000 and commission is what? Commission is null. But the effective sally is coming out to be what? 60,000. So in this way I use the if null function. But if you are not able to understand it, then let me try to make you understand this. I will take the pen tool. I will first of all, for example, I will move to the first row. So there is a select statement. Which statement is used? Select statement is used. Select statement will work row by row. First of all it will go to the first row. For example this Nikhil. It will go to this employer and it will find the sally. The sally is 120,000. The commission is 30,000. Effective sally, you know it is adding sally plus what? If you go where it is sally, it is adding sally plus what? If null commission comma 0. Does the commission for Nikhil is 0? No. What is the commission for Nikhil? The commission for Nikhil is 30,000. So this is taking two arguments 30,000 comma 0. Is the first argument null? No. It is not null. That is why it will return what? The first argument. First argument is not null. That is why the first argument will be written and you know the effective sally will become what? 1 lakh 20,000 plus 30,000. So that will come out to be 1 lakh 50,000. In case of Nitin also, you know the commission will be replaced by what? See this 30,000. In case of Nitin, when it will go to Nitin, it will be replaced by what? 15,000 comma 0. So the first argument is null? No. Therefore 15,000 will be written and effective sally will come out to be what? 95,000. But once when it will go to Anchita, Anchita sally is what? Anchita sally is coming out to be here. I have write it. Anchita sally is coming out to be 60,000. And I am writing if null. And her commission is what? Her commission is actually null. And the second argument is what? Zero. The first argument is null. Therefore in case of Anchita, it will return the second argument. That is zero. So her effective sally will be equal to what? 60,000 plus zero. That is again you know it will be equal to 60,000. So this is what you know. This result you are getting. Similarly in the case of Vibhav and similarly in the case of what? In the case of Vasilev. See there for example, when you take Vibhav, the sally was 40,000, commission was null. But effective sally is coming out to be 40,000. Vasim also. The sally is 34,000 but commission is null. So the effective sally is coming out to be what? 34,000. But there is one problem and let me you know tell you the problem. I am not able to decide. What I am not able to decide? Again I will just you know I will again let me you know again take the pen tool because you know it got out to be little clumsy. So here I am with this employee. Suppose let me first of all state the problem. This Anchita. Anchita's sally is 60,000. Her commission is null. So her effective sally is also coming out to be what? 60,000. That is equal to the sally. Similarly in the case of Vasim also you know the commission is null. That is why you know the effective sally is coming out to be 34,000. But if you take Rajneesh, if you take Rajneesh, her salary, his salary is 40,000 and his commission is what? Zero. That is why his effective sally is coming out to be 40,000. But you know what is the difference between for example I say Anchita and Rajneesh. What is the difference between effective sally of Anchita and Rajneesh? I write it over here. For Anchita and one employee is Anchita and other employee is Rajneesh. Both of their you know for Anchita the sally is 60,000. Effective sally. Effective sally is also coming out to be 60,000. But her commission is what? Her commission is null. And in case of Rajneesh if you observe the sally of Rajneesh is 40,000 and commission is what? Null. Commission is zero. So her effective sally is also coming out to be what? It is also coming out to be 40,000. It is also coming out to be 40,000. So here the problem is because you know for both of them the effective sally is coming out to be equal to what? It is coming out to be equal to be sally. But for Anchita you know in this sally only which part is there? The salary part is there. Only for Anchita the sally part is there. But for Rajneesh you know the salary part is also there and which part is there? The commission part is also there. Although Rajneesh is getting zero commission but it is actually decided you know. For example if you take Nikhil. Nikhil also you know his effective sally is equal to what? It is equal to the effective sally is comprised of the sally part also and which part? The commission part also. But for example for Anchita because the commission is null the effective sally is only comprised of which component? It is only comprised of the salary component. So now what my task is that I want to find out the salary component of every employee in the organization. So let us try to do this. So I will you know I will briefly you know give you an overview of the if condition. For example I say select if suppose I say 2 is greater than 1. If this condition will true it will select a, a else it will select what? Suppose BPP. So you know this is you know working of it. If 2 is greater than 1 then it will select the first argument else it will select the second argument. So this is you know this is the function of if. So let us try to solve this using. So now I will return to the problem and what is my what is my problem? The problem is like this. Query to compute the salary component of effective salary. I want to compute the salary component of effective effective salary. And this salary component can be equal to what? Sally plus commission or only what? Or only salary. So this I want to try to do it on your own. I have given you a hint. You have to use it over here. You can also use case but for sake of simplicity I will just use it over here. Just try to do it. How you approach this problem? I will just pause the video. If you are not able to get it then I will explain it. As effective salary comma what will I say? I will say if, if the commission is what? Commission is null. If the commission is null then you know the effective salary will only be comprised of which part? Commission is null. If the commission is null then the effective salary will be comprised of only the salary part. But if the commission is not null then the effective salary will be comprised of what? Sally plus commission. So here if you go and I just you know I have done this query. So here you get for example in the case of Nikhil the effective salary is comprised of the salary part also and commission part also. But if you observe Anchita, her salary is 60,000 but commission is null. So you know the effective salary is comprised of which part? As only the salary part. But for Rajneesh you know because the commission is not null it is equal to zero but it is not null. So her salary component is what? It is salary plus commission because organization I have you know I analyze this column also as sal comp. Sally component. It is not getting. From employees as sal comp. So it is coming out and now you can differentiate between you know Vasim and Rajneesh. See you can now differentiate between Vasim and Rajneesh. How? Because both of their effective salary is equal to salary but for Rajneesh the salary you know the effective salary is comprised of both part the salary part and commission part but for Vasim it is only comprised of which part? It is only comprised of the salary part. So this video we will study about group functions. So what are group functions and how do they work? All this we will study in this video. So in order to understand group functions first of all you have a look at this line what I have written over here. Group functions act on multiple rows at a time like some min, max, average and count. So some min, max, average count these are all what kind of functions? These are all group functions and how do they operate? They operate on many multiple rows at a time. Okay how do they act on multiple rows at a time? Let us have a look. Let us also see what have we been doing till now. So this is my database and there is my employee's table and then there is my department's table. Select a database. So this is my publisher database and inside this there is employees and the department's table. Now how do group functions work and how do they are different from single group functions? So this we have already seen. For example if I say select upper of ename from which table? Say suppose employee's table if I write like this then you know this upper function is working on row by row basis. Why it is called a single row function? Because it acts on one row at a time. See here I am writing suppose this query. Select upper ename comma upper ename from employees. So what it is doing? It is doing something like this. This I have already told but still for a kind of provision I am telling. First it is going to this row and then you know applying an upper function to which applying an upper function to the name and then you know converting it into upper case. Then it is going to the second row then it is going to the third row. So it is how it is acting? These are single row functions and they are acting on one row at a time. They always act on one row at a time. There were many examples of single row functions which we saw for example there was one function suppose the length function is also a single row function for example suppose I say like this length of ename from employees. So this was also a single row function because it acted on one row at a time but these now functions are different from group functions and how do group functions work? They work on multiple rows at a time. For example suppose I write when I write select star from employees. This is select star from employees. How many rows are there in this table? There are 10 rows in this table. See there are 10 rows in this table. Now I want to have a count of all employees in the table. Then what will I write? I will select count star from c. It is giving a count of what? It is giving a count of 10. So there are total 10 employees in my organization. So how count is a group function? So count is used for counting. So it will count all the distinct occurrences of all the rows. For example how this count is a kind of group function because you see if I take this pen marker. So you will observe that this count function is working on how many rows at a time. It is working on all these rows. This count function it is acting on these many rows at a time and it is counting having a count of all these occurrences of all these rows. So this is where this count function is working. Now there are many more group functions. So we will take all these group functions one by one. Let us say there is one function that is a sum function. So suppose I want to have a query to find the sum of Sally of the organization. So what query will I write? Sorry I have not. Select sum of what? Sum of Sally from which table from the employees table. Then it will give a sum of the Sally of all the employees. See if you add all these Sally's, if you add all these Sally's you will get this 5,72,000. Suppose I want to want the minimum Sally of the organization. Then what query will I write? Select min Sally from which table from the employees table. So minimum Sally of the employees table is what it is 15,000. That is being earned by Rashmita. And suppose I want the maximum Sally then what will I write? I will write max Sally from employees. So this is my max Sally. So in this way suppose now again you are working with suppose min. So how it is min? How min is working? So if I take this min tool again and you know how suppose when I am writing this query. Select suppose when I am writing suppose select min Sally from EMP. Then how it is working? It is taking all these rows into account. See all these rows, all these rows are being taken into account and then the minimum Sally amongst them is being found. This is where you know this minimum is working. Now similarly there is maximum it is acting on all these row and finding out what? Finding out the maximum Sally. I also have an option to find the average Sally. How will I find the average Sally? For that I have to use the group function that is the AVG function. How will I use it like this? For example when I say select AVG average, average of Sally from vegetable from the So you are getting an average of 57200 and that is validated also because what were the total number of employees? The total number of employees were 10 and what was the sum of all these Sallys? The sum of all these Sallys was 572000. Therefore you know the average Sally is what it is 57200. Clear? So this is where you work on. This is the way of working of group functions. Now let us try to solve few examples and work on them. So the first query that you have to write is to write a query to display Sally statistics report of the organization as maximum minimum and average Sally. So what do I want out of it? I want the maximum minimum and average Sally of the organization. So how will you go for it? Try it. Okay it is I think it is very simple. Select max salary suppose I say as max cell comma min of what? Min of also salary as suppose min cell comma average of what? Sally as AVG from vegetable from the employees table. So you are getting the maximum Sally of the employees of all the employees the minimum Sally that is being earned by all the employees and the average Sally that is being earned by all the employees. Clear? So this is you know this is the way you work with which kind of function this is the way you work with group functions. Okay it is so group functions and then okay I think here here it is you know it is very clear. Now let us try to work on one more query and this is your this query query to find the highest Sally for managers. Okay so I want the you know what is the maximum Sally that is being earned by managers for example when I say select star from the employees table if this is my employees table and then you go there are two managers first manager is Nikhil and then the second manager is Nitin. I want to find the highest Sally that is being earned by what that is being earned by managers. Okay so how will I go for it give it a try okay let me you know let me tell it if you are not able to get it the query is will be like that select max of what max of Sally from employees if I write like this then it will give what it will give the maximum Sally of all the employees okay but I have one thing okay let me do it okay where if I write like this where designation equal to what where designation equal to manager then it will give the max okay where designation equal to what it is equal to manager so it will give the maximum Sally of which employees of the managers I am not sure if you are able to get it but let us you know try to understand this query before and try to understand this query you take you take what I have written on this line group functions are evaluated after the where clause what does this line mean this means this line means that group functions are always evaluated after you know after the filtering of records clear group functions again I would repeat group functions are always evaluated after the filtering of rows which is done by the where clause clear let us try how it is working let us see how it is working for example I won't say so this way you can find the you know highest Sally of managers also but I won't say for managers suppose I say for I want to find the highest Sally for developers okay what query will I write see select max of what I've written where designation equal to what it is equal to a developer so you are getting the maximum Sally for a developer that is 50,000 see in this table there are two developers how many developers are there two developers are there one is Webhub and one is Sonali and whether Sally is 40,000 Sonali Sally 50,000 and maximum Sally is what it is 40,000 if I say suppose the minimum Sally from employees where designation equal to developer so it will find the minimum Sally for all the developers in the organization so how this query is working particularly selects are selects staff of employees okay this is my employees table so you have a look suppose now I'll take you know to better you know understand make it explain let me you know take this point okay now like now if you observe this query okay if you observe this query select the min Sally from employees where designation equal to developer okay this Sally this this query if you observe okay now here what I have written if you go here so I have written that group functions are evaluated after the filtering of rows so what does this mean this where class we already studied that select class select when you write select statement it works on row by row and then where class performs what it performs the filtering of rows so here also suppose I write select means Sally from employees by designation equal to developer so this will go to all the rows of the organization all the rows and will select only which rows it will select only the rows that are where the designation is developer so here designation is manager it will not be selected this will also not be selected this row will be selected this will also not be selected this will also not be selected this and again webhub will be selected and Rashmita Neha and you know the niche will not be selected so effectively how many rows will be selected by the query see this you know this webhub will be selected and then this row this Sonali will be selected and now in these two rows the group functions will be applied okay now in these two rows group functions will be applied when I say the minimum Sally then out of these two rows which is the with what is the minimum Sally when I say maximum Sally then out of these two rows what is the maximum Sally clear so in this way you see group functions are operating how do they work after the you know after the execution of filtering of rows that is being done by the via cross let us try and take another example suppose here is one more query for you find the count of all employees whose Sally is between 40,000 and 90,000 okay you have to write this query try it on your own I'll pause the video okay if you are not able to do it then I think again this query is also you know not very difficult to understand what will I write select what I have to find the count therefore I will use a count group function select count star from this table from the employees where what will I say Sally between Sally between what I have said 40 that is 40,000 yeah it is 40,000 and 90,000 so where Sally between 40,000 and 90,000 see it is giving a total count of what 7 when I say select count star from employees when I say like this total number of employees are what total number of employees are 10 but when I write like this then the count is coming out to be what count is coming out to be 7 why it is coming out to be 7 because where clause over here is performing filtering of rows and then after the execution of where clause you know the select clause you know this the select clause is working I'll just if I take this pen now if you observe this query this query then here it is here the where clause is performing filtering of rows this row is being filtered because you know no no this row is not getting filtered okay this row is getting filtered this row is getting filtered this row is getting filtered this is not getting filtered again this row is getting you know selected this row is getting selected this is not getting selected and this row is getting selected and the niche is putting selected so how many rows have got selected 1 2 3 4 5 6 7 you know 7 ticks are there this is a cross okay so 7 ticks are there okay So, in this way, you know, first this their class has formed the filtering of rows, these rows have been filtered and in these filtered rows, the group functions are being executed. This is the way group functions work, clear, all right. Now, again, let us have a look at a very important point. Group functions do not operate on null values. What does this mean? This means that group functions will always omit the, you know, the rows that are null, okay. Let us see how this is working. Suppose, for example, I say, select, I don't say, when I say select count ename from the employees table, then it is, you know, it is giving, it is giving the count of all the enames, you know, it is taking all these enames and giving a count of all of them. Now, if I say, select count of salary from employees, if I say like this, then what will be the output? See, again, the output will be 10 because, you know, it is having a count of all the, you know, all the salaries. This is my employees table. This is my employees table. Now, if I say something like this, select count of commission from employees, select count of commission from employees, then what will be the output? Then you see the output is not coming out to be 8. 8 is coming out to be 10. But still, if you observe, here commission, if you go to this commission column, this commission, here will you observe that the total number of rows are what? These total number of rows are 10, but why it is giving the output of 8? It is giving an output of 8 because there are two null, you know, there are two nulls in the commission column. That is why here you observe, there are two nulls. This is the first null, okay, sorry. This is the first null and this is the second null. And what I have written group functions never operate on null values. So it will omit this null rows, okay, wherever the commission is null, it will omit these rows and then it will perform a count, okay. So always remember these fact that group functions never act on null values, okay, clear. For example, suppose when I say, sorry, I write this query, select max of what? Max of say commission, max of commission from which table, from the employee's table. When I write like this, it is, sorry, maximum commission, it is giving to be 30,000. When I write sum of commission, suppose sum of all commission from employee's table, then it is giving what? It is giving 74,000. See, 30,000 plus 45,000, 50,000, 52,000, 56,000, 56,000 plus, you know, 18,000 equal to what? 74,000. It is 74,000. But it is not adding these null values, okay. And this is also, you know, it is verified. It cannot add null values because we already saw, if you add null to any value, the result will also be what? It will be null, clear, because null is an undetermined value. You add an undetermined value to a determined value, the result will be undetermined. So this, that is why this sum is not taking these nulls into account, you know, this null and this null into account. Because if you add these null, you perform a sum of it, then the result will also be what? It will also be null. So for the sake of security, you know, for the sake of, you know, security, these group functions never act on null values, clear. I think this much must be clear. Okay. Now, let us have a look at one more query, okay. Get count of all employees whose commission is null, okay. Count of all employees whose commission is null. How will you write this query? I want to find out all the employees whose commission is null. Try out, try this, okay. Try this query. I will pause the video. Okay. If you are not able to get it, then again, this is very simple. Select, I have, I want to count and then I will say select count star from employees where commission is equal to what? Commission is equal to null. My dear friends, will this query work, will this query work? It will not work. See, okay. It is giving a count, you know, count of zero. It is not giving the result. I mean, two employees are having commission as null. So it should exactly give the count of what? Count of two, okay. So, you know, you can never equate null. For having, for matching null, you always use the is operator, okay. So where commission is null, if I write like this, then you will, then you know, it will, it is giving a count of all employees where commission is null. So here, you know, it, I have written star. So star means a row, okay. It is, you know, so. So here, if you, I mean, I don't want you to get confused with this query. Let me explain this, select star from the employees table. This is my employees table, you know. If this is my employees table, then, you know, this where clause again, you know, it is performing filtering of roads. So two rows are being filtered, you know, these row and this row. And then you are performing counts of star. So star, it is, you know, it's a kind of wildcard operator that is, you know, that will take into account the distinct occurrence of rows. So this row is there, you know, this row is also there. That is why it is, you know, it is giving a count of what it is giving a count of two, okay, clear. So in this way, this query is working. This video, we will further continue our discussion with the group functions. So we will take a look at few queries, few practice queries, and we will try to better understand how group functions work, okay. And how can we apply a working of group functions, okay. So this is my employees table, and you know, if this is my database, for example, if I go over here, okay, select data base, okay, sorry, the publish database. Now, okay, okay, have a look at this. I want you to, first of all, I want you to write this query, okay. I want you to find the number of employees who are hired in the year 2017, okay. So find the number of employees who were hired in the year 2017, all right. How will you go for it? So these two employees, you know, these, these employees, this, this is one employee, this is one employee, and this again, you know, all the employees who were hired in the year 2017. For example, when I say, select, you know, select ename, hire date, hire date from which table, from the employees table. And I write like this, you are getting this. So for example, Nikhil is getting in the year, hired in the year 2015. But Sonali is getting hired in the year, in which year, in the year 2017. So I want you to find the count of all employees who were hired in the year 2017. Go for it. Okay. If you're not, if you, if you don't get it, then I would like, first of all, you know, first of all revise your, you know, when I, when I told you about the, you know, date functions. So there was one date function that was extract function. And when I write extract year from which table, from the, from, sorry, hired date. As suppose year hired, if I write like this, then what will be the output, you know? You will get, you know, the year in which every employee was hired. For example, this Nikhil was hired in the year, in which year 2015. So extracting year from which, from the hired date. So you're extracting a part of hired date, that is the year part. So this is, this extract is, you know, it is a single row function. This extract, it's a single row function. Clear? It is, see, it is working on row by row. It is acting on every row and finding, you know, the year in which every employee was hired. So now I want a count of all the employees who were hired in the year 2017. So what query will I write? I think it is very, very simple to write this query. I just want what? I just want a count. So I will say count star from which table. From the employee's table. Where extract what year from which, from hire date. Hire date. Is equal to what? Is equal to 2017. So you'll get the count of all employees who were hired in which year? Who were hired in the year 2017. So I've solved this query. Let us now look at one more query. And suppose this is the query. All right. Organization has decided to give commission to only those employees who have worked for more than one years. Find the total commission that has to be given by the organization. So what do I mean when I say this query? Let us go to this database. And this is my employee's table. Select star from, suppose I say select ename comma sadly. Select ename comma salary comma commission from which table, from the employees table. Now what the organization has decided? It has decided to give the commission to only the employees who have worked for more than one year. So now for example, if you take Nikhil, this row, if you take this first row, this first row Nikhil. Nikhil was hired in which year? He was hired in 2015. And if I write on my system, if you see, select current date. If I write, then the current date is what? It is 2017. So that means Nikhil has worked for more than how many years? He has worked for more than one year. That is why he will be given commission. But in case of Sonali, she's just started to work on 2017. So she will not get any commission because she has not completed minimum one year duration. So although her commission is 5,000, but she will not get any commission. So I want the total commission that has to be given by the organization. I mean, so total commission means the sum of all, sum of commission that has to be given by the organization. Write this query. How will you write this query? To write this query, I will again give you one more hint. And that is the timestamp diff. So if I write the select ename comma higher date comma timestamp diff. If I write timestamp diff, I give two arguments. Here, higher date. And then the third argument is, say, suppose the current date. If I give from which table? From my employees table. Then you will get a count of how many years this. So this timestamp diff function is doing what? You just observe this timestamp diff function. Timestamp diff is finding the difference in years of these two dates, the higher date and the current date. The higher date and the current date difference in year this timestamp diff function is finding. So the current date is 2017. The current date is 2017. And in case of Nikhil, the higher date is 2015. So it has found out the difference to be two years. So how will you go for this query? How will you write this query? I'll pause the video. I think I have given you a big hint. If you are not able to get it, then I will write the select count of what? Select count star from the, oh, sorry. I have to find the sum of commissions. So how will I write? Select sum of commission from which table? From the employees table. Where what condition will hold true? Where this condition will hold true? Where timestamp diff of year from the higher date and what? Current date is greater than or equal to what? It's greater than or equal to? So this has to be the total commission that has to be given by the organization, according to my condition. So for all the employees, we have worked for more than one years. See, if I write like this, see if I write the ename comma commission, select ename comma what? Select ename comma commission from employees where timestamp diff is equal to 1. So you will get all these rows. You will get all these rows. And now when you write sum of commission, when you write sum of commission, then it is performing the sum of the commission of all these filtered rows. Whereas what is the filtering condition? All those employees who have worked for more than one year. So if you perform the sum, you will get this sum to be 67,000. Clear? All right. Let us move to again a very, write a query for this. Get count of, not for this, count of distinct jobs per department. Count of distinct jobs per department. So what do I mean when I say this query? For example, when I write select dptid comma designation from the employees table, when I mean like this, you see 10 rows are getting selected. 10 rows are getting selected. In department 10, there is one job manager. In 20, there is a job that is developer. In 20, there is a job that is analyst. Then in department 20, there is a job that is a trainer. And then there is HR head. And then there again, there is again a trainer. And then there is again a technician. But I want only the distinct jobs. So what query will I want a count of distinct job? Here you are getting the count what? Here you are getting the count of 10. So if I write select count, select count. If I write like this from employees, I'm sorry. If I write select count distinct department id comma designation from employees, then you will get a count of what? You will get a count of 8. Because the total number of distinct jobs are what? The total number of distinct jobs are only 8. Because 10 manager and 10 manager, it is getting repeated. Similarly, 20 trainer and again this 20 trainer is getting repeated. So it is counting the number of distinct occurrences of this. Clear? All right. Now again, one more very challenging query. Find the total males and females in the organization. Total males and females in the organization. So I want this query in a definite format. I don't want it in a specific format. In what format do I need this query? I want this query in this format. I'll just take the pen tool and then I'll try to explain. So I want the output in this format. How I want the output? I want suppose number of males and number of females. These will be the two columns and say suppose the number of males are what? Suppose the number of males are seven and number of females are three. Whatever may be the account. I just found, you know, this space. So here you take this male M and F are two columns or I can say, I can analyze this column as this also for example, I can say you don't write M over here. You can write males and you have females. Account of all males and females in the organization. How will you go for this query? So this is query, you know, it is a kind of challenging query. So I will not ask you to do it, but let us see how we can approach this query. If you can understand the working of this query, you know, it will be very good. So to find all this, suppose this is my employees table, select star from which table from the employees table. This is my employees table. Now what is the total number of males in the organization? Total number of males in the organization is like this. Select, I want the count. So I will just say count star from which table, from the employees table, where, sorry, where gender equal to male. Total number of males are six and total number of females are what? Total number of females are four. But now I want, you know, I don't want this result to be like this. I don't want to write one separate query for males and one separate query to be of females. I wanted the result in, you know, what I told exactly, what I wanted the query in which specific format, okay? This was a format in which I asked you. So I want, you know, both count of males and count of females in a single query. And how will I, you know, this way, count of, suppose males is six and count of females is, oh, sorry, it is not 10, it is four. I want the result in this format. How will I go for it? So let me write this query. You try it, better try to understand this query. How am I writing this quick, okay? So for this, writing this, I will say select. I will use a group function. And group function, inside group function, I will use a case, okay? And inside the case, I will pass this gender. When m, then one, if I write, else I say zero, sorry, zero, as males. And then I copy it and I put a semicolon in this term. And I forgot, you know, I just, you know, need to end the, you know, and case also, okay? So end, as suppose, when gender, suppose if I write f, then females, from which table? Employee's table. If you write this query, if you write this query, then you can see what will be the output. Let us try to work on this query. If I have written this query, I'll just explain this query, how this query is. See, the number of males are six and the number of females are what? Number of females are four. How this query is working? If you can understand the working of this query, I think you have, you know, you have a good understanding of, you can solve complex SQL queries. Just try to understand this query. I'll try to explain it. And I'll take this point to look at here. So select clause is there. You are using the select clause. The select clause will always work from in which basis. It will work in row by row basis, okay? So here you see there is no where clause, okay? So now first of all, it will find, you know, it will go to this group function. In some case, so it will go to the first row and what is the gender? Suppose the gender here, the gender is male. So, you know, the gender is male. So it will give, you know, it will produce what? It will produce one, okay? Then it will go to this row also. It will go to this row also. And then gender is male. So again, the count will be what? Again, the count, you know, in this case will give the output of what? It will give the output of one. But for Sonali, it will give the output of zeroes. So it, you know, for all males, it will, you know, it is giving out, you know, giving output of what? this case, if you observe this case, for all the, for all the when it goes through all the rows who are males, then it will give one as the output. And we have, I have written some outside. So it will count all the ones. So it will give the count of all the males. And similarly, this will give a count of all the females. Clear? So in this way, I'm writing this query. So it is very popular format of writing these queries. Now I want you to write one query on your own. On the basis, I have, on the basis, how I have solved this query, I want you to solve one more query. So I want you to write this query. I'll just remove it. Find the count of employees hired in year 2017, and 2015, 16, and 13. 2016, and 2015, and 2013. So I want all the employees who were hired in the year 2017. I want a count of all, separate count of all the employees who were hired in the year 2017, 16, 15, and 13. This is the query I want you to write. So how will you approach this query? See, I want this output in this form. So for example, if you just take the main tool here. So here, what format do I want? I want this format. Say 2017, then 2016 will be one more column, 2015 will be one more column, and 2013 will be one more column. And these four will be the columns. And then I want the count. For example, in the year 2017, say suppose three employees were hired. In 2016, say two employees are hired. And then say in 2015, four, and then maybe three, whatever may be the count, I want output in this format. Write on your own. This query, I think you can write on the basis of what I've written over here. Try it on your own. If you're not able to get it, then of course we will solve. I'll pause the video. So this query is also, I think, not very difficult if you are able to understand the previous query. How will I write it? Select what? Select some of what? Select some of what? Case. Case of what? Extract year from hired it, extract year from hired it. When it is 2017, then I will give the output one. Else I will give the output zero. And then I will end the case. And then I will say as 2017. If I write like this, is it correct? See, I think it is correct. And then similarly, I will write it for what? I'll copy. I'll just copy it, and then I will write it for what? 2016, 2015, and then 2013. So here I will write 2016, 2015, and then here I will write 2013. And then if you go here, also similarly I'll write this. 2016, 15, and then 13. And I'll remove the comma from here. From which table? From the employees table. I think it should work. Let us see if this query is working or not. Copy and just paste it. Oh, there is some problem. Just giving problem where it will just let me work out with this problem. I will just set it. I think there is some problem with the brackets. Let me see. Case of extract year from hydrate. What may be the possible error with this? Select case extract year from hydrate in 2017, then 1L0N. And as 2017, I think it is correct. I'll put the commas also from employees. Select. OK, let's let me, you know, I will just, maybe I'll just again try to run it on employees. Let me try to break this very. Select case of extract year from hydrate when 2017, then 1L0N from employees. Just let me take a part. Is this working? Yeah, it is working. So I think then extract year from hydrate, yeah, when 2017 case, OK, all right. I think if you just go over here, this sum function is 19. I'm not able to track there. I think you can just try this query on your own. Maybe there will be some kind of syntactical error with this query, why this is not working from employees. In some case, just what is the error, right, syntax to use near 2017, OK, I'll just try to do it like this. Suppose it should work. Yeah, OK, great. See, it was, you know, it was considering these allies column as numbers. So, you know, I have to, you know, I have to enclose them in single inverted commas to make them back as OK. So this is working. So you are getting the count of all employees, you know, who worked in 2017, who were hired in 2016, 15, and similarly in the year 2013, OK. We will study about the group by clause. So we have already studied about the group functions. What were the group functions? Like the sum, average, count, min, max, all these were the group functions. But now we will study something different, and that is a group by clause. So always take note of this that group by clause is different from group functions. So what we have already studied, we have already studied that group functions never operate on null values, and group functions are executed. And now we will study, you know, the later part of it, you know, group functions are executed after the by clause, and group functions can never be written inside the by clause. All these things we will study when we try to see the group by clause, OK. So let us first of all go to my database, and here is my, I'll again write the query. Suppose this is, again, I'll work with employee stable. This is a standard table with which we work, OK. So this is my employee stable, and now let us try to work on it. Suppose I want to find a count of all the employees who work in department number 10, OK. So this is not, you know, we have already done this query, so I'm not going to, you know, this is not a query that you need to attend. We have already done this, but still, if I need to find a count of all the employees who work in department number 10, just for your revision, then what query will I write? I will write this query. Select what counts from which table, from the employees table. Where what? DPT ID equal to 10, department ID equal to 10. So you get two employees who are working in department number 10, OK. And just to revise your concept, you know, we already saw in the previous video that, you know, first of all, the where clause will execute, and then, you know, these group functions will run, OK. So how it is working, it is working like this. Again, you know, to make the concepts clear, it is working something like this. If I, you know, I just take the pen tool, OK, here. So you have written that where department ID equal to 10. So how many rows will be selected by the where clause, you know, these two rows will be selected by the group by clause, OK, by the where clause, you know, these will be filtered by the where clause. And when these two rows have been filtered by the where clause, now the group functions will be applied. And what is a group function? The count star function is there. This is our group function. So count star will produce the result what? It will produce the result to be 2. If I wanted, suppose, you know, the sum of salary of all the employees in department number 10, then what query will I write? I think, again, you can write this query. Instead of using counts, I will use what? I'll use sum of salary, OK. Sum of salary, OK, but the sum of salary is, you know, 2 lakhs. So Nikhil and Nitin are two employees who work in department number 10. And their sum of salary is what? Their sum of salary is here, you see. These two employees are working in department number 10. Department number 10. And their sum of salary is 2 lakhs. So here, the same thing is happening. What? The same thing that is the first of where clause is, you know, filtering the rows. How many rows are being filtered? Two rows have been filtered. And now in these two filtered rows, you know, this group function has been applied, OK, clear? So always keep this execution in mind, OK? So let me, you know, write this very important point for, you know, OK, we'll just, you know, see all that. Now let me minimize it. And now suppose I want to, you know, I'll just return. Suppose the count of all employees who work in department number 10. So this was the query, OK? This was the query. Suppose now I wanted to find for department number 20, then what query will I write? All the employees who work in department number 20. So they are six employees in department number 20. And how many employees work in department number 30? See, two employees are working in department number 30, OK? And this is my employees table. I'll just, I'll write select star employees. OK, this is my employees table. And you can verify this from this table also, you know, two employees are in department number 30. This Jayant and Vaibhav are in department number 30. Two employees Nikhil and Nathan are in department number 10. And the rest of the employees are in which department? They are in department number 20. Now, I don't want the result in this manner. I want the result in different manner. And let me tell you in what way I want the results. So I want the result in this way. Suppose if I take this, OK, if I just take the pen, and how do I want the result? I want the result in this way, you know? Suppose the department 10, 20, and 30. And here, this is department ID, OK? And here is the count, you know? For example, two employees in department number 10, six employees in department number 20, and then again, two employees in department number 30. I want the result in this way, OK? For that purpose, I am going to use the group by clause, OK? For this purpose, I will use a group by clause. How I will use a group by clause? Let us try to see this, OK? So first of all, just write, let me write a query. And then I'll just explain what I have written. Department ID, from which table? From the employees table. And then I write group by dptid, OK? If I write like this, OK? You get the correct result, you know? In department number 10, there are two employees. In department number 20, there are six employees. And in department number 30, there are how many employees? There are two employees. Two better, you know, if I first write the department ID and then I write the count star, and I analyze this column also. Count star, as I suppose, num employees, OK? From which table? From the employees table. From the employees table. And group by which column? Group by department ID, clear? So in department number 10, two employees, 26 employees, and 32 employees. So now we want to study how this query is working, OK? Let us try to understand how this query is working. And in this query, you see what I have used. I have used the group by clause. I have used which clause? I've used this clause. This is the group by clause, OK? This is the group by clause. And this is my group function. Count star, min, max, sum, average, these are all group functions, but this is the group by clause. So what effectively this group by clause is doing? It is grouping by which? It is grouping by department ID, OK? It is grouping by what? It is grouping by department ID. So how many groups, how many, you know, distinct department IDs are there if you just, you know, if you write this, if you write a query, how many department IDs are there? Then suppose I write select distinct DPT ID from the employees table, you know, sorry, select distinct DPT ID from the employees table. There are three distinct departments. Now let me, you know, select star from employees group by department ID, sorry, group by department ID. Select star from employees. And then I write order by, order by what? Order by department ID, if I write like this, OK? Then what effectively if you let me, you know, just, sorry, I'm just, that query, you know, it's, let me, I like it, OK? Select DPT ID comma count star as num employee from this table from the employees table. And then I was saying this, this query I've already grouped by what? Group by DPT ID, OK? DPT ID. All right. Now how this query is working, you know, how it is working, let us try to understand this. I'll take the pen tool and here let us group. So what, what am I doing? I am doing, I am doing, I'm using the group by clause, OK? Group by, and group by what? I am saying group by department ID. So how many distinct departments were there? You see previously what we saw, how many distinct departments were there, 10, 20 and 30? So for department number 10, a group will be formed, OK? This is the group for department number 10. For department number 20, again one more group will be found and this is the group for what? This is the group for department number 20. And for department number 30, again a group will be formed and what will be this group? This is the group of department number 30, comprising of jayant and webhub and department number 10 group comprising of what nick hill and nithin so you have group Why what you have group why department id's? Okay, so you have made three groups. Okay, how many groups you have made three groups All right, just you know try to be patient and try to understand this Okay, this is very important if you have to write complex queries try to understand this now once you have grouped How many groups have been found three groups have been formed once these groups have been formed the group functions will be applied individually on these groups. For example one for example the you which group function You are applying this group function count star. So here in this group of department. ID ten Countstar will be applied and how many count will come count will come out to be true Again for this group this group that you know This group function will be applied and what will be the count for this The count will come out to be six and again for this group the count set function will be applied and the count will come out to be what? It will come out to be 2, clear? So in this way group functions are working, they are grouping, first they are grouping and then they are applying the group functions. So let me write like this, let it be what it is, I will take one note and whatever important deductions we have we will keep on writing them. First grouping is performed and then on those groups, group functions are applied. Are you able to get this line? What I have written? First the grouping is performed and then on those groups, group functions are applied. So what does this mean? This means what we have seen over here, what we have seen my dear friends, we have seen that first the grouping was applied, we have written group by what? We have written that group by department ID, the first the grouping was performed or how many groups were formed, three groups were formed or department number 10, 20, 30 and then on those groups the group functions were applied, clear? This way it is working. So let us again try to solve one more query. For example I want to find the maximum salary in every department. So for example if you say department number 10, the maximum salary it is what? It is 120,000 and similarly for every other department I want to find the maximum salary. So what query will I write? Select department ID comma what? Then I will use the group function what? I will use the group function as I want the maximum salary then I will use max. Max of what? Max of salary from which table? From the employee's table and group by what? Group by department ID because I want the result per department. So you are getting for department number 10 maximum salary one like 20,000 for 20, 70,000 and for 30 it is what? It is 40,000 and one thing to keep in mind over here is that again if you observe first the group functions, first grouping will be formed, how many groups will be formed of three groups will be formed of department number 10, 20 and 30 and in those group the group function will be applied and what is the group function? This group function maximum of salary will be found. So in this way the group by clause is working. I am little slow because I want to explain each and every concept. Please bear with me if you have already studied it then you can skip this video. But I want to target those students who are just trying to understand what is equal. Let us write one more query. So we have already seen this query. You have to now write this query. Query to find the maximum, minimum and average salary per department. So write this query. I will just pause the video. You write this query. How will you go for this query? I want to find the maximum, minimum and average salary per department. What query will I write? I think select department id, I want the maximum salary per department then I will write max salary. Suppose I say I will analyze this column as max salary, if I want the minimum salary then I will use the group function with what min. And then again I will analyze this column as min. And if I want the average salary then I will use the AVG. AVG salary as suppose AVG salary from the employee's stable and group by what? Group by department id because I want the result per department. So you are getting for department number 10 the maximum salary is 120,000 minimum salary is 80,000 and average salary is this okay. So you are getting the average minimum and maximum salary per department. Let us just observe this query. So here the same thing is again happening you know the same thing is happening. What is happening? You are forming the groups by the group by clause and on those groups three group functions have been applied. What three group functions? The max salary, the minimum salary and what the average salary group functions have been applied okay. Let us try to work with again one more query. Forming the average salary group by what? Group by designation. So now I want the average salary by designation. Can you write this query? So here is my employee's table. So I want the average salary of each and every designation. Select start from employees. So how many designations are there? Managers, developer, analyst, trainer, technician. I want the average salary per designation. So what query will I write? I will write like this. Select what designation and what do I want? I want the average salary. Therefore I will use the group function which group function AVG salary from which table from the employee's table and group by now group by what? Now group by what? Designate. If I now group by designation you know sorry select okay. Select designation, my average salary from which table from the employee's table and group by what? Group by designation. So you are getting the average salary per designation. So here also you know if you observe we have group by what? We have group by designation. So if I write select start from employees and order by designation if I write like this I just start this just for making things clear and then you know so here again if you go you know if you again go over here okay so again you know if I you know just take the pen tool. So what is effectively happening? The thing that is effectively happening is now you are grouping by what? Now you are grouping by designation. So how many groups will be formed? This analyst group will be formed. This developer group will be formed. HR head group will be formed. Manager group will be formed. This technician group will only contain how many employees, one employee and this trainer group will contain how many employees, two employees. Now how many groups have been formed? One, two, three, four, five, six and six groups have been formed. And now in each group what group function will be applied? This group function which we have seen okay. If you go over here okay sorry yeah this average salary you know this group function this group function. This average salary group function will be applied. For example if you go to suppose analyst suppose you grow for technician then what is the you know what is the average salary for technician it is 40,000 okay. So it is coming out okay. This much is clear. This much is clear. I'll you know I'll just escape it okay. And then I will make one more important suppose let us try to write one more query query to find the number of employees hired per year hired per year. So for example you know if you take this again you take this employees table and now I say order by hired it order by hired it. So this is your this is your table and now I want to find out the employees number of employees who are hired in every year. For example when I say that in 2013 3 employees were hired for in 15 2 employees were hired and 16 2 and then 17 how many employees 3 employees were hired. So what query will I write okay. You just try this query I'll pause the video try this query on your own okay. I think this query is also very simple what I have to write. So what do I want I want to count and which column do I want to group them about I want to group them by year so what will I write yeah if you know if you have not studied date function then first of all try to study the date functions okay extract what extract year from which from hired it extract year from hired it comma I want the number of employees. So I will use the count cell group function from which table from the employees table and what will I write I will again you know group by extract year from hired it if I write like this you know you are getting in 2013 3 employees were hired 2015 2 employees were hired 16 2 employees and 17 how many employees were hired 3 employees were hired and you know you can also group you can also write this query like this. For example if I write select and as year it suppose I analyze this column I analyze this column as year h or year hired and now I write from employees and then I write the group by I don't write this you know extract year from hired it I don't write extract year from hired it I just use what what was the allies I use for it this column I used year h then suppose if I write year h is will it work I'm not sure just let us try okay it is working okay so you can you know also use allies, allies names of columns when you are grouping them okay allies names for example you have we have allies this column as what year h and we are using the same in group by cross. So here what is happening again the same thing is happening you are forming groups of the years and then in each group you are performing the group function that is the count star function okay clear so this query we already seen okay okay so I think all right so we will have a continue a discussion of group by group by clause in the next video also we will study that group by you know how group by is using conjunction with having so we will study this all this in the next video okay I'll just pause it this video for now we will continue our discussion of group by clause so what we started in the previous video we saw that group by clause is used to group together rows and then you know group functions are applied on those group rows so this we already saw in the previous video now let us continue our discussion after this so if you just have a look at the second point group functions are executed after the bear cross so what does this line means okay so you can always you know keep this thing as you know it you can keep it as a rule that you can always remember that group functions will always be executed once the wear clause has finished its execution okay so let us try to practically understand it so this is my database and you know I'll just know although I've selected but still again you know this is my employees table okay now this you know what we what we have already seen till now is that suppose I want you know I want you to find the count of all employees per department okay for example how many departments how many employees in department number 10 how many in department number 20 and how many in department number 30 so this very is very simple you have already we have already you know already seen this query from us suppose so I want you know from which table from the employees table and group by department ID if I write like this I will get you know department number 10 20 and 30 and what exactly is happening over here if I you know just I revise your concept for just your revision and I'll just draw it so that you know things are clear so what exactly is happening is that you know it is grouping together rows you know this for example this department number 10 is forming one group this department number 20 you know it is forming one more group you know this this is these all rows will come under department number 20 these these rows will come under department number 20 this group will belong to department number 20 this will group will also belong to department number 20 and this group will also belong to which department department number 20 and once you know these groups have been formed the group functions will be applied and what is a group function we have used over here we have used a count function so it it will count the number of rows ok. So, this way it is working it looks out to be fine, but now I have some more requirement and I you know what I want I want to omit out the department number 30 and I only want the count of employees working either in department number 10 or in department number what or in department number 20. So, what query will you write what will you do to solve this query? Give it a try I will pause I will wait I think it is very simple. So, what I am going to do select department id comma count of what let us first of all you know write select star from you know so that everything and let me order it by suppose department id first of all let us do this then write our query. So, what query will I write I will write because I want to group by department id I will select department id and then I will write select what select count star from the table my name of my table is the employees table and I will write group by department id if I write like this sorry I only want for department number 10 and 20. So, what will I write I will use a where clause where department id in and what it should mean either in department number 10 or in department number what 20 and then I have to group by what group by department id. So, now if you just see if I take the pen tool you know I just take this pen tool and you know this query is almost you know same to this query what we have done over here is almost same what we did over here, but here you know we have omitted this what we have omitted we have omitted department number 30 how we have omitted department number 30 because we have used the group because we have used a where clause where clause we have used and if you are able to understand it what is what I have written over here suppose here what I have written group functions are executed after the where clause. So, what does this mean this means that first this select will run in you know in conjunction of where and first the rows will be filtered and then once the rows are filtered then they will be grouped and then group functions will be applied. Let me you know write the flow so that it is clear. So, let me write you know it is very important to understand this you know first in first place what is running here where clause is taken second what is you know group by is running and you know in the third position what is running the group functions are being run group functions. So, here what is exactly happening this is see this is happening. So, you here you have used up where clause you have used up where clause. So, it will filter rows and it will only filter rows where department id is either equal to what either equal to 10 or either it is equal to what it is equal to 20. So, these many number of rows will be what they will be filtered all these rows will be filtered. So, these rows are filtered now these rows have been filtered now what will happen is that now the grouping of these will grouping of these rows will happen according to what according to department id why because I have written group by what I have written with group by department id that is why grouping of rows there will be performed by their department id. So, taking their respective department id is there only 2 distinct department id one is the department id 10 and the other is the department ID what 20 so two groups will be formed in this case why because these rows have been you know these rows have not been selected because they have been filtered out they have you know they have not been selected they have been filtered out by the where clause only their clauses first selecting you know all the employees who are in department number 10 or 20 and then grouping of those rows have been performed and then once the grouping has been performed the group functions are being applied is it clear I think it should be clear this is very important so if you are able to get it then I want you to write this query query to find the average SLE of managers developers analysts see in my employees table if you go to the employees table these are all distinct designations managers trainers HR head analysts developer technician developers all these are different designations but I only want what I only want the average SLE of managers developers and analysts okay write a query for this right on your own okay if you have been able to do it it is very good if you're not able to do it then just see what I do okay so what I have to select select designation comma I want the average SLE so which group function will I use average and average of what average of SLE from which table from the employees table and then if I write group by designation if I group by designation then you you are getting everything but according to my query I only want managers developers and what analysts what query will I write I will write something like this from which table from the employees table and then I will write where you know desec designation in what designation in first one is your manager the second one is developer and the third one is what it is analyst analyst okay and then if I group by what group by designation if I write it then you will only get the average SLE of analyst developers and managers I think it should be clear and here also what will be the order of execution the order of execution will be like this first the where clause will be executed then group by and then finally group functions will be applied so how how it is happening if you observe I write this okay select star from employees order by designation you know I have ordered them by designation so now if you observe if you write again I know because I think it is you know I take this time because you know I just want to explain these concepts to you so you know some may find it to be very long explanation but this is the way I do it so that things are clear so first of all what is going to happen the where clause is going to run and what does where clause do it does filtering of rows select statement works row by row and where clause will perform filtering of rows so which rose will be for filtered this row will be for filtered by because its designation is manager this row will also be filtered by because its designation is developer but this sorry yeah these two rows will be filtered because their designation is analyst these two rows will also be filtered because their designation is developer and this Rashmi ta will it be filtered no because its designation it's what HR here now again one in two these rows will be filtered because designation is manager and then you know 10 9 and 6 will these rows be filtered no they will not be filtered by because their designation is not either and manager, developer, analyst. Okay, now what, so we have VailClaus, VailClaus has finished it's execution, it has finished performed the filtering of rows. Now, what is the second thing that is gonna happen? The second thing that is gonna happen is, you know, grouping. So, group by what? You have written group by designation. So, according to the designation, one group will be the group of managers. One group will be the group of what? One group will be the group of developers. And one group will be the group of what? will be the group of analyst and then now grouping has also been performed and once the grouping has been performed what is going to run the group functions will run and what is the group functions over here that I have used over here the group function is a AVG function. So, average of salary of every group for example there are two analysts and if you see there are two analysts over here there one is at least 60,000 the other is at least 30,000 the sum is 90,000 and divided by 2 it is 45,000 that is why you know average of analyst is 45,000 for managers if you go one salary is 120,000 other salary is 80,000 and you know sum is 2 lakhs so the average salary will be what it will come out to be 1 lakh okay now on these groups the group functions are running. So, in this way you are getting this output. So, now I think you must be clear with what I have written over here I have written first the where then I have written the group by and then I have written the you know you know this group by and then in the third position the group functions are going up and so you keep this thing in mind when you design SQL queries alright. So, I think it is clear now if you go to my PowerPoint presentation and what I have written in the third one group functions can never be written inside where class what does this mean can you what all I have told you till now if I if I am going to write good function inside where class what is going to happen. For example, suppose I say I want the count of again the same query I want the count of every employee in each department according to their department. So, I will use the you know I will use the department ID and then I will use the count because I want the count I will use the count functions from the employees table and then group by what group by sorry department ID here you are getting like this now I only want for example my query is that I only want those departments where the number of employees working is greater than 2 here this is a query query to display only those department where the number of employees working is more than 2. So, can I write something like this? So, you first of all try this query on your own and can for solving it can I write something like this can I write something like this suppose I say select and then from employees table and then I write the where clause and inside the where clause I write where count star is greater than what is greater than 2 and then I write group by clause group by what department ID you see invalid use of group function this query will never run why this query will never run this will never run because of the second point which I have mentioned that group functions are group functions can never be written inside where clause why do you think that I can never write group function inside where clause what is the exact reason for it see I have already told you if you just observe the order of execution first the where clause is running then grouping is being performed then the group by function is being run and then you know the group functions are running. So, when the where clause is running when the where when the bear clause is performed filtering of rows is there any you know is happy calculated any of the group functions have any of the group functions being executed no that is why always keep this thing in your mind that you can never use group function inside where clause why you can never use it because once the where clause run the group functions the grouping has not been yet performed and group functions have also not been you know calculated so how can you use group functions inside a where clause first of that purpose we will be using separate clause that is the having clause so we will study it you know little later maybe in this or maybe in the next video we will study the having clause for solving this problem but for now you just keep this thing in mind that you can never use group functions inside their class okay now let us move on to the third point and what is the third point the third point is sorry the fourth point only the columns mentioned in the group by clause can be selected using the select clause so I am you know there is you know my sequel this the fourth point which I mentioned over here you know it is not the true in the case of my sequel but for most of the other relational database for example or Apple SQL server and very popular you know standard databases this point is always valid but for my sequel it is not you know it although you can never do it it is you know it is it it is it doesn't make sense to do it but still you know my sequel allows this and it does you to allow it it allows you to do it by because it makes this assumption that the you know the end user is clear of what point of query he's writing and I also you know maybe in some you know some later versions of my sequel this is this is valid okay so let us see what is what this point is exactly saying let us try to understand this so I'll you know just if I just you know write this query select department ID comma count star from employees group by department ID so you know this is very simple this is very simple this we already seen okay group by department ready I think you are getting you know this but now let me do something different what I will do suppose I select I will select the department ID and I will select salary also salary comma count star from which table from the employees table and I say group by department ID if I write this query do you think that this is there any problem is there any issue with this if I write this place is there any issue with this query there is a serious problem with this query if you can make it out first of all let me tell the in my sequel you know this very will run it will not give any errors but for most of the other relation database this very will not work I will just try to show you how it is not working with some other very popular databases but first of all just see that it is working in case of my sequel and now if I write select star okay from employees and then I write order by department ID okay to make you know things clear I'll just now I'll just minimize it now if you see you know these three groups have been formed this we already saw you know okay and I need to take a pen tool over here so this we have already seen enough okay okay so how many groups will be formed I think it is very simple to answer it these 10 these groups will be found okay this group of department number 10 then group of department number 20 and then the other group of department number 30 because I have used group by department ID now what I am selecting I am selecting department ID and I am selecting count to count you know it is giving the count of you know in number of employees in every department but I am selecting Sally also okay so Sally also for example you consider this group you consider this group if I select department ID from for this group for this group only if I select department ID then for every employee belonging to this group the department ID will always be 20 but what but if I select Sally then who's Sally because if you see in department number 20 you know there are six employees which are working in department number 20 and if I am talking of Sally then who's Sally I want to select I have not specified it that is why this query is never valid okay that is why this query is never never valid you can always select department ID because you have group by department ID so all the members in that group will have the same department ID that is why you can select department ID but you can never select salary but in my sequel you know what is happening is you know it is giving some some one one Sally of you know for example it is giving Sally in department number 10 it is giving Sally of Nikhil and for department 20 it is giving Sally of you know Sonali okay that is you know mainly it is taking giving you know selecting the rows on the basis of how it has stored it but this query is now you know it is not valid and it will not work in most of the popular databases for for your I mean if you try to run it on some other database let us try to see what is the problem okay and you know I will just take this I think I minimize it okay so what I've done is that this is my oracle okay oracle library skill yeah alright so what I've done you just observe that if you're I'll just minimize it and now this is your wrackle database this is my difference keep in mind that this is not my skill database this is wrackle database and I have created a table that is employees table I'm not you know added all the columns I've added only you know four columns and I've inserted some dummy data inside this table and if you do select star from employees select I say select star from employees then I'll get this okay you know I'm getting for four employees Nikhil, Nitin, Neha and Nithi these are the four employees and now if I do something like this what do I do I do select department ID comma salary comma say suppose count star from which table from the here also the name of the table is employees and then I write group by department ID if I'm gonna run this query on oracle database not on my SQL my dear friends just keep in mind this is oracle I SQL if I run it then what will you get you know not a group by expression this will query will not work why this query will not work because of this reason okay if I you know if you go to this presentation then what I've written only the columns mentioned in the group by clause can be selected using the select clause so here if you observe okay over here if you observe this salary is this salary this column which I've selected in the select clause is it mentioned in the group by clause no it is not mentioned that is why it can never be selected but this can always be done for example if I write something like this group by salary and no for example okay not to make you confused if I write if I don't select this earlier if I don't select the cell then this query is always gonna run then this query is always going to run for example yeah it is going to run by in department number 20 there to implies in department 10 there are two employees but if I write you know if you write to suppose and then if you write suppose you write you select salary and then you group by what then you go by salary only okay you go by salary only then it is possible to select Charlie okay you it is possible to select you know you will get okay you are getting you know for you know one employee is getting 12,000 one employee is getting 10,000 you know and you know one employee is getting 80,000 one employee is getting 1,000,000 but it is not possible to do something like this suppose I select department ID also over here is it with this query then this query will not run why because in the group by clause I have only use only that so in the select clause also you can only select was you can only select what cell you cannot select what you cannot select department ID so here if I write if I write this and you just see it it is not going to work but again this thing can also be done suppose I write department ID okay now I have in my group by clause which two columns are being according to which two columns looking is being performed it is being performed by salary and department so each group will have you know the same salary and the same department that is why you will be able to select what the salary and the department ID using this query and this query will run fine okay this query will run fine so this is what exactly I have written over here if you observe so this I know this this was you know I just showed you in you know other database that is you know your Oracle database so the only columns mentioned in the group by clause can be selected using the select clause for this is not true in case of my sequel but it is true in case in in most of the other relation databases like Oracle or my sequel or any any of the other relation database okay so how does the having clause works and how does you know what it is used for so let us try to understand this and before even trying to understand this the need for having clause was discussed in the previous video so what was the need the need was that because you can never use group functions inside the bare clause what was so this was the need so let us try to understand this with the help of one query I want query to display only those department where the number of employees working is more than two so let us before you know like to to approach this query let us first of all find out how many employees are working for departments so this is you know very a lot many of time I have written this query okay select department ID comma what counts are from which table employees table and then sorry group by department department ID you are getting this now I only want those departments where the number of employees working is greater than two so if you observe I want so which rows much must be idly selected only these rows will be selected you know only this one row will be selected department number 20 why because here only the number of employees working are six okay which is greater than two for department number two and 10 and 32 employees are working so I don't want to select those department my requirement is something like this so one solution is that I can do I can write to this from the employees table and then I can write what where count star is greater than two and what will I write group by department ID if I write then you will got invalid user group functions and we also saw in the previous video why it is invalid user group functions because of this reason why this reason because first the where clause is run then you know the group by and then the group functions are done so once your where where clause is running these group functions have not been yet calculated so there's no point in putting you know group functions inside you know your where clause so where do you exactly put this condition I want this to happen so for that purpose I will use the having clause so what will I write I will write like this select from employees okay and then group by department sorry group by department ID all right all right okay you know select from employees and then group by department ID and then I will write having having what count star greater than two not if I write you know then only then you will get you know which employee you know you will get the employee that is having you know account of number of employees are working in this department is greater than two okay clear so let me you know let me state one more query I have I have designed one more query for you so you have to write this query okay query to find the designations where uh then query to find the designations sorry where only single employee okay query to find the designation where only single employee work what do I mean by this suppose I write select star from employees and then I write group by sorry then I write group by design designation all right then sorry not group by designation I like order by designation if I write order by designation you are getting you know these employees and they are ordered by designation so now I only want those designations where one employee is working okay single employee is working okay that designation that is held by only a single employee for example this analyst post it has it is held by two employees this developer post is held by two employees but this HR head post is held by only one employee similarly this technician post is held by only one employee so only I want those designations where you know there is only one employee that is working in the whole organization so I want this query okay so try this query on your own I'll pause the video okay if you're not able to get it then what what has to happen first of all if you write this query okay select designation comma what count because I want the number then I will use the count star from which table from the employee employee stable and then I will write group by designation see you will get you know number of employees working in every you know by their designation for example two are analysts two are developers but I only want the count star which counts are only one there those employees that you know only those designations were only single employees working so how will I approach this again I will use a having clause for it by because I cannot write something like this where count star is equal to one so what will I write I will write from which table so what will I do you know I will just you know copy it to notepad and because you know again I will not take the headache to you know write this query again and again so just let me copy this to the notepad so that it is okay from employees and then go by designation so you know you are getting and now what I have to write I have to write like this having what having having what count star is equal to what count star is equal to one if I just copy it and I go and paste it over here and just now if I go and paste it and then you will get you know HR hidden technician have two designations where only one employee is working okay you got it and you can do something like this also you know can use those aliases in the having clause also for example suppose as EMP CNT you you write this as employee count in VMP CNT so you can write something like this also having EMP CNT is equal to one so okay so it is also valid to use some aliases inside the having clause if I copy it and then if I go and just paste it you will get the result also okay all right so you have got this also you have understood the working of having clause so now you got an idea that what is happening exactly is something like this fourth after the group functions have executed which clause is executing you know the having clause is executing okay so first we are clause in group by the group functions and then after group functions have been evaluated then we you know we can use those evaluated group functions inside the having clause so this is the order in which you know your query is executing all right so this is this looks out to be very good now for example I want to have suppose suppose the average salary of all the departments so what will I write I think is very simple you know this we have already seen the average salary of I will remove this because you know we have already done this so I want you know department ID comma what average of salary from the table in price table and then grouping by what department ID I am writing this query then just let us run this query first and then you know perform some other operations I will use the having clause of course in it here I have got you know this okay okay now let us I do not do not do it for you know for this let us do it for suppose designation okay so that I will you know I want more than you know three four rows so that you know I can I can work easily you know I can show you some good results okay let us write this query I think this query is very simple understand you are getting this now I want the the average salary so now what I am going to do I want the average salary of only analyst developer and managers so what this is this also we already saw from employees where designation in what designation in first the manager sorry okay manager and then then your what then the developer and then analyst analyst now if I write this query then I copy it I just paste it again it is very simple to understand I only want the average salary of manager developer analyst and this is very simple but now again I have one more condition what is the condition the condition is that I only want the average salary of those designations where you know the average salary is less than 90,000 okay the average salary is less than 90,000 so what query will I write I can write some now I will have to use the the having clause suppose I I you know given a nice name to this suppose I give the nice name as AVG cell then what will I write having what AVG cell is less than what 90,000 now if you go in on this query copy and then you write it so now you will get only analyst and developer see you will not get manager why because for managers the average salary is greater than 90,000 for analysts and developers the average salary is less than 90,000 okay this much is also fine and I can do something like this also for example now I have got this result I can now apply order by order by suppose designation and I write the descending okay copy and then now if I go and I run this let us see your firstly you are getting first you are getting the analyst then the developer but now I have ordered it by first ordered it by designation and then in descending fashion so firstly developers will come and then you know the analyst row will come so now you get an idea for example here if you see having and then in the fifth position after having you know what will be running order by will be running okay so this is you know this is the you know the your sequential break of when you write group by clause this is the sequential way of execution okay first the where clause run then the group by the group functions having and finally the order by is going to run okay so this is you know I have you know designed a query in which you are using everything you are using where group by group functions having and then order by clause and their order of execution is also mentioned in serial number so you keep always these things in your mind okay so I think I am done with the with having clause just let me see if there is some other thing that I can also take okay suppose I give you one query find suppose I you know this one assignment query for you quickly we will solve it query on the same line query to find a number of employees hired in 2017 2016 and 2015 and the number of employees uh suppose uh you know this style you know let me write one query select star from employees or so that I can just you know I can see it whatever I had it okay in 13 15 16 17 all right so all right well and and the number of employees hired in a year are greater than say suppose to okay for example I want to find the number of employees hired in 2017 16 and 15 but what else condition is that then else condition is that that I only was out of 2017 16 and 15 I want only those years where the number of employees hired are more than what where are more than two so what query will I write for this try it on your own this is the last query for as far as having clauses since I try on your own okay if you're not able to get it then what do I want I want first of all I will say the I will say extract what extract here from uh hide it then I will find the count okay let me analyze this column now just uh okay suppose I analyze this column as num hired okay num hired all right from this table from the employees table but and then but there is one more condition what will I write where because I only want for years for which years I only want for 2017 16 and 15 where extract year from hired it in what it will be in I I'm just uh sorry my god I'll just you know I'll just I'll copy it I'll write it in the notepad you know because many times if there's some errors then I'll be able to you know easily resolve it as far as you know in notepad it is very easy to do it let me remove it let me paste it over here just this from here also remove this from here also where extract year and hired it in what it is either in 2017 either in what 2016 and either in what 2015 and then you are performing group by what you are coping by extract year from hired it year from hired it let me go and first of all and this query and see if it is working or not okay um all right I'm not sure whether it will run or not okay it is not it's working or maybe all right let us see okay so we're getting you know in 2015 16 17 how many employees you know uh so you are getting it now I only want those years where you know the number of employees hired were more than that see this was what I wanted you know and the number of employees hired in a year are greater than what are greater than two so what I had to write in this case also first of all let us do one more thing also suppose you realize this column you realize this column suppose uh uh you realize it it has a year so you can write it in you know year and group by say year okay here okay so I'm analyzing this column let us see if it is working or not I'm not sure if it will work but hopefully it should column year and year plus okay I think yeah this is a problem uh where extract year from uh hired it you know but this I this will this will always work you know I just remove it oh my god this we don't want group by year this will always work I think now if it will work let us paste it yeah it is working so now so what I've done you know what I've done you know I have realized this column which column I have realized this extract year from hired it I have realized it at it has was it have I have realized it as year so I can use you know the allies column in the group by clause and similarly for the hiring clause also I can use I can you know I can say something like this where num hired is greater than uh two okay where num hired is greater than two so what is num hired this is this column is your num hired column okay this is your num hired column so I have you know so I can write something like this and then you know I'll just copy it and now if I go and paste it it's gonna run I think it should come okay yeah so only the year 2017 the number of employees out of 2017 16 and 15 only the year 2017 is the only year in which the number of employees hired are greater than okay so if you have ever uh studied the relational databases uh if you have ever worked with any relational database then you must be well conversed with what are primary keys so primary key constraint is used to uniquely identify each row in a table so what is exactly a primary key how it is you know it is uniquely identified each row in table say for example that you have bank you have some account in certain bank and you want to you go to the bank to make some withdrawal then you won't say that this is my name and you give me such and such amount of money you will always say that this is my account number and on the basis of this account number you pay me this money or you pay me that money why because your account number is uniquely identifying you okay so in this case the account number is a primary key then a primary key cannot be unique and it cannot be not null why a primary key cannot be unique see suppose you go to the bank so it is not possible that two customers of the same bank have the same account number it is never possible they will always be unique that is why you know every customer will have a different account number so a primary key is always unique and primary key can never be null why a primary key can never be null because if you make primary key null then what is the purpose you know for example suppose you go suppose you have a bank account you have everything you registered in your bank account details for example your name your mail your mobile number your age your sex everything is registered the only thing is that your account number is set to null is it possible it is never possible so so in this case account number is a primary key and primary key can never be equal to null so that by you know account number can never be null that is why it is said that primary key constraint is equal to what it is equal to unique plus not null always remember that okay primary key constraint is equal to unique plus not null so how can we create primary key so one approach of creating primary key is that whenever you create a table you mention the column that is you that is a primary key column how will you do it let us try to understand this okay so i'm going to my database and i'm just going to so i have logged on to the database and suppose i create a table okay and i create a table suppose the name of the table is movie and suppose i suppose say the movie name and suppose a movie name back at 20 and then i say suppose director okay and director is also suppose it is also a string okay then i when i'm creating this table so one approach of creating primary key there are many approaches of you know there are two approaches of creating a primary key but one of the approach is that whenever you are creating a table inside the you know when you are when you are mentioning mentioning the column for example i'm saying over here that this column is denoting the movie name this is the data type of the movie name for example it is var cat 20 it is a string type and then i would say that this is also what this is also a primary key so now what will happen just try to see this i created this table the movie table has been created and when i say dsc movie then what will happen you see this m name is column is coming out to be what it is primary key okay and the you know null if you just observe this column see this column under null m name has said to be no why because movie name can never be equal to what it can never be equal to a null value all right so this way i have created a table movie and movie name is a primary key so let us try to insert some values in the you know this this table suppose i say insert into movie values suppose one movie was okay you can take any movies for example one of my favorite movie was there was a movie that was that was based on world war two i don't remember the exact name of the movie for example let us say matrix okay it was one of one of my favorite movies and matrix movie i don't know the exact director suppose i would say that james was the director of the matrix movie so this is interest is inserted you know then i would say that there was another movies suppose suppose ddl it's one of the favorite it's one of the very popular bollywood movies and suppose the director was current johar you know i'll say johar okay so two movies have been interested inserted and then will i say select star from which from the movie then you will get get these two movies that have been inserted now let us try to do one thing let us try to you know for example i would say that matrix and again i let let me go and insert this rule is it possible let me try to insert this rule will it will this be inserted suppose i insert i change the director to james not i change the director to canes it can be some arbitrary name but i have said the movie name again to what matrix and you can observe sorry all right all right what did i said canes but i've said you know the movie the movie name to be equal to what matrix and you can see that this movie name is already been inserted so once you try to do it it will never allow why because duplicate entry for matrix is never possible by because the movie name is what it is a primary key and duplicate values in a primary key column are never possible primary key is always unique and if you try to insert null value suppose you try to do something like this you make the movie name to be what null and then you try to do it column m name cannot be null by it cannot be null because it is a primary key so this way you add you create to you you know you create a primary key on a column and then there are also very special things suppose you want to drop a primary key see the last point if you want to drop a primary key use all the table table name drop primary key okay so what is my table my table is you know this is this this table okay if i say dsc movie this is my table now if i want to drop the primary key then what will i do i will say alter table then what is the name of the table movie and then what will i say drop primary if i say like this then the primary key will be dropped so i've dropped the primary and now again when i say dsc movie you know see this primary key constraint that was occurring over here is not now it is now it is not occurring over here now okay so now it is the primary key constraint has been removed and then once when i say select start from movie then you you are getting the same records but the table has no primary keys okay and then what you can of course do is that suppose now you try to insert the same you try to insert this value matrix comma k and c matrix has already been you know movie name has been once one movie name of type matrix has been already inserted you know one movie of name matrix has been inserted then if you try to insert another movie of the name matrix is it possible now yeah it is possible why it is possible because we have already removed the which kind of constraint we have already removed the primary key constraint so if you again say select start from movie you will get two movie of what type two of of type matrix okay i will say delete from i will delete all the rows from the movie table okay so now again if you say dsc movie you will get describe the details of the movie table now what i want to do is that i want to this you see the fourth point for example one's third fourth point and to add a primary key to an existing column so now you see this does the same name is a primary key no it is not a primary key one way we you know we created the primary key was like something like this you know when we created the table for example what we did you see we one way of creating the primary key was by using at the create table when you were creating the table then you were mentioning that such and such column is a primary key but now i don't want this for the table has already been created and now i want to add a primary key constraint to a specific column in a table so what will i do so i will use the fourth fourth point you just observe to add a primary key to an existing column use what all the table table name add constraint constraint name primary key column name okay so wherever i have you know these uh uh these uh between these two you know you have to you have to keep on substituting values so you have to substitute table name you have to substitute constraint name and you have to substitute substitute what the column name so how will i do it so i will like alter table okay and then i need to substitute the table name so what is the name of the table movie and then i have to say add constraint sorry add constraint i can give any name see now what i have to give i have to give the constraint name so i can give any name of the constraint for example this is also a valid constraint name but generally it is very advisable that you give a descriptive constraint name so how will you give the descriptive constraint name you can do it do it something like this what is the name of the column m name what is the name of the table and what is the type of the constraint pk so it is now describing that M name column of the movie table has been given the primary key constraint So, it is a descriptive name, constraint name. Then what I have to say it is a primary key and on what column the primary key constraint has been added, it has been added to the M name column and then once when I say now again you have see you see I have inserted the primary key constraint on the table. If I try to insert if I try to say DSC movie then again now previously you are not given under the key column you know you are not getting the primary key, but now you are getting what it is a primary key. Okay, clear so this is the working of primary keys. I think okay all right let us continue. So, so this is the way we work with primary key, but again there is one problem. What is the problem let us try to understand this for example if you are if you are if you watch Bollywood movies then there was a you know many times it happens that movie names are repeated. For example there was a movie that was called Agnipath and that was released in 1990 and that belonged to Amitabh Bachchan and there was also a movie that was there was a movie that was also called Agnipath and that was released in the year 2012 and that belonged to Ritik. So, you know just to give you I mean I can like you know kind of I would say IMDB Agnipath. So, you know two Agnipath movies are there one was released in the year 2012 and the other was released in which year it was released in the year 1990. You see this movie 1990 Agnipath it belonged to Mr. Amitabh Bachchan and then you say for example Agnipath 2012 that it was released in the year 2012 and it belonged to Ritik. So, I want to keep these two you know tables in my these two movies in my movie tables so what will I say insert into movie values okay and then I will give the movie name and suppose Agnipath and suppose you know the previous I am sorry IMDB Agnipath and you know the 1990 movie Agnipath it was you know directed by whom it was directed by Mukul Anand you see so I will give the name of the director Mukul okay he was the director of the movie. I have inserted this role now again I want to insert the one more role for Agnipath that was released in the year 2012 and the director was what director was Karan okay director was Karan you can just check it also you know for example if you go over here Agnipath 2012 movie director was Karan Malhotra okay so this way it goes I'll just I'll just close these tabs again if I go to my database will it be allowed see will it be allowed it will never be allowed because why you are making because why you are making duplicate entries for what you are making duplicate entries for the movie name and movie name has been said to what movie name in the movie table is a primary key so now this is a problem so how can I remove this problem I can say said that I will say that movie name is not a primary key the movie name so now I will introduce you with the concept of what the concept of the concept of composite primary keys so it is also possible that two or you know more than more than one columns in a table can be can be primary keys combined so primary key can also be combination of many columns so you know on what it is called it is called a composite primary key a composite primary key can have multiple columns whose combination must always be unique okay so what does it mean that in a composite primary key you can have multiple columns but there you know their combination will always be unique so I can say for example over here for example in my in my application the movie application that I will say that the movie name will not be a primary key the movie name plus the release year will be a primary key for example you know only unique movies per year are released so I would at least go on to say that movie name plus the year in which it was released will be the primary key so now what I need to do I need to create a composite primary key so before creating a composite primary key let me do one thing that you know because this movie table already has a primary key so I need to remove that primary key so how will I remove this primary key is how is it possible I will say you see this PowerPoint presentation you know if you want to remove you know I'll just minimize it if you want to drop a primary key alter table table name drop primary key so I will use this okay alter sorry right this stuff alter table and then what is the name of the table movie and then what I say drop primary okay and then once when you say the primary key will be dropped so now I why I drop this primary key because now I want to add a composite primary key to this table so before adding a composite primary key I am saying that movie name plus the release year is unique identification for all the movies so because you know I have not added the release year column to this table so let me do that also so what will I say alter table which table movie add what add a column and I will say it is a just a release year column and it is of type so I've added a release year column so if you say DSC DSC movie describe the movie table then you are getting what the movie name and the release year column so now I want to do what I want to add a composite primary key so how will I add a composite primary key composite primary key composing of what it is being composed of the movie name movie name also and the release year also so now if you want to do it then what will you do again you will add what you will say alter table okay which table the movie table add the see alter table table name add constraint constraint name primary key followed by the column names so what will I do add constraint and suppose I will say that M name are you are here release year and what is the name of the table the name of the table is movie and what is the type of constraint it is a primary key constraint so I have given this name constraint this name the primary key constraint I've given this name alter table table name add constraint constraint name what kind of constraint a primary key constraint and on what columns I'll just minimize it and on what columns you can just see there are two columns one column is a name column the other column is what it is a REF that is a release year column if you go on to it again a primary key constraint has been added but now if you go you know if you say suppose DSC what is the name of the table it is a movie so if you go on to do it see the movie name and the are here are you know they are primary key so so so always keep this in mind I'll I'll make this very important statement you know it is very important that is why always I'll take a note pad to write it down because it is very important I would go on to say that a table can have only one you know table can have only one primary key so here you see that M name and are here are they both you know different primary keys suppose this is this table movie having two primary key see M name and area no it is not their combination is one unique primary key see this is the difference M name and area are not to separate movie are not the separate primary keys for the movie table why because a table can have only one primary key but M name and area combination is what it is what it is a primary key so if you want to just see it for example I say now I can do it insert into what insert into movie table movie table and what what I want to suppose the name of the movie I will say Agnipath and I will say what is the name of the director suppose this is the director that has you know that suppose Mukul and it was 1990s Agnipath and it was released in the year 1990 then when I go and insert it okay all right suppose I will say insert into movie and values okay let us go and insert it so this is inserted again I would say there was one movie Agnipath that came in the year 2012 and that was directed by Karan Malhotra and it was released in year which year you know 2012 you know now it is possible see if I say select star from which table select star from the movie table if you if you do it then you see this movie name is movie name is getting what movie name is getting repeated but the movie combination of movie name you know sorry I know I'm just there was one low that was already inserted so I will you know I will say to lead from movie there are year equal to zero okay I'll just remove it all right now if you say select star from movie then you will get two movies you know their movie name are same but the combination of movie name and the release year is what it is different okay so movie name and release year are together they are forming a primary key so if I try to do something like this suppose I insert into suck suppose I change the name of the director you know suppose I change the name of the director to like this I give any name okay and then you know the combination of the movie name and the release year is remains the same for example Agnipath 1990 you know it will never allow see duplicate entry what what not Agnipath or not 1990 but a combination of what Agnipath 1990 so a primary key can constrain can span multiple columns also okay so in this way so again you know one more thing one more last thing about primary key constraint if you want to get some details about the primary key constraint then you can use this there is one metadata view that is the information underscore schema dot what I think it is key column usage yeah okay so if I you know if you can press the you know what will I say select suppose I will install okay I will say select then I will say enter column name comma suppose I will say constraint name all right and then I will say suppose table name okay I think table name and then I will say no table schema from from which table from this table from information where will I then I'll just add a where clause I will give the where for example sorry where table underscore name is equal to what is the name of the table it is equal to movie movie and what is the name of the schema then I will say table underscore schema equal to the name of my you know the current database that is the publisher database so if I go on to you know make a query okay so and I just all right and I just need to add a and so you'll get you know this move this table has you know it has a primary key constraint that is imposed on which column two columns and name and area suppose you do something like this okay there is one table that is a MP table suppose you do and then that that this table we have frequently dealt with what is the employees table and employees does it has a primary just check it out yeah it has a primary key constraint on which column it is a primary key constraint on the EID column so this way you can you know you can pitch the metadata information that is associated with primary key we'll be also using this metadata view for you when we will fetch information about the you know foreign keys so we will use this also so what is a not null constraint this constraint forces the values in a column to be not null so what does this mean that whatever value stored in a table if you mention that that the particular column is not null then you can never insert a null value in that column so let us try to understand this with a practical example so this is my database and I say select a database then you know you'll get the database that's a publisher database and then I will create a table and suppose the tape name of the table is say suppose the products table and I have two columns product ID int and product name okay that is suppose that care 20 and against product name I add a constraint that is the not null constraint so I created its product table and now let us try to do something suppose I say insert into products and then I may give the name of the column suppose a product ID and the product name and then I insert values suppose I say that first the product ID is one and the product name is suppose a soap then this will be inserted and if I say select star from which table from the products table then you will get this sorry select star from products you'll get it but now if you insert another row and you give the second value that is a name value to null so will it accept it let us try to see this suppose a product ID is true and then I try to insert it see it is not inserted why because product name cannot be null so why because we have inserted a null constraint inside the not null constraint inside the product name so product name column this column can never be null so this is what I've written over here the syntax of a not all constraint is something like this column name data type and then not null so this is what we have done over here see when we created the table how we created the table we created the table something like this suppose the product name column that was made not null so the column name and then what was the data type the data divers work back at 20 and then I gave the not null constraint so this column product name is a not null column okay clear so I can never insert a null values inside this so now if I say if I describe my table products then what will you get you will see that this product name and there is one you know in the describe there is one column that is a null so this product name you know the value for product name against null is no so that means product name can never be what it can never be null but product ID can always be null how it can be null just see for example I say insert into product suppose I just give the product name if I just give the product name then what will be the product ID it will be it will be null suppose and suppose the name of the product is shampoo and then if you say select star from this table product stable then you will get a value of ID ID is null value ID can be set to null by because here you have seen the PID can be null but can product name be null no it can never be null so this is what is null constraint in many cases it may be required that you have you need to create a null not null constraint on an existing column in an existing column sorry so how will you add it see to do this you will use a change alter table change so how will you do it to add a not null constraint to an existing column so let us try to see this also for example let me create another table create table suppose I will say products one suppose okay and there is one column PID and that is it and there is one more column product name and that is vatcat 20 so I have created this table that is the products one table and if you say dsc describe the products one table then you will you know both of these column that is a product ID and then the product name both of them can be what they can be null so I try to do something like this insert into sorry into which table products one table suppose values 1 comma so so we have created the products one table and then I insert a value suppose 2 comma shampoo and then I insert suppose third and you know for against the third ID I insert a null value so how will I insert a null value I will just write null over here so now you can see if I say select star from which table from the products one table then you will get the product name as null so now what I want to do is that I want to add a null constraint to the product name column of the products one table see what I want to do I want to add a null constraint to the product name column okay so what command will will I use I will use this command see to add a not null const sorry I want to add a not null constraint I don't want to add a null constraint I want to add a not null constraint the value is nullable but I want to add a not null constraint to the product name column so to add a not constant to an existing column what command will I use I will use this command alter table alter table table name change the old column name to the new column name and then the new column definition so how will I go for it I will say something like this alter table what what is the name of the table products one and then will what will I say see change sorry change what is the old column name P name what is the new column name I would say it is P name and then what is the new column definition so I have to give the whole column definition see what the data type and then followed by the you know the natural constraint so what was the data what is the data type that I want I want the data type to be also what it has to be a bar care but I will add a new constraint to it what is what is that constraint I will and I will add a natural constraint to the product name column if I try to do this let us see okay yeah it is done so now if I say select start from products one then what it has done see it has replaced all these null value the null value that was there in the product name column it has replaced it with a blind string okay so now I can never insert is a null value in the in the product name column of the products one table for if I try to do it for example here if I try to do this suppose I say the PID to be 4 and then if I again if I try to do this it will never happen by because product name column can now it can never be null and if you see products one table see this column product name it can never be set to what it can never be now set to null all right so yeah there is one more thing that I just want to tell that whenever you add the null constraint sorry a not null constraint I always you know confuse it with the null I would say a not null constraint to a column then you it is a it is it is a generally a good practice that you also give a default value how will you give a default value for example I just create a test table suppose I say create table test 11 suppose the name of the table is test 11 and there is just one column serial number of time in but I will add a not null constraint to this serial number column and I will also give what I will give a default value suppose default value is 0 okay so if now I say insert into okay what will I do I'll just add one more column I clear add one more column to this table all the table what was the name of the table test 11 I suppose add suppose name and add I will say column okay or add column name and then suppose the data type is of back at 20 then when you say DC test 11 what values will you get the serial number and the name see serial number I have added the not null constraints so serial number can never be null so let me try to do one thing I will say insert into test 11 and I will not give the serial number value I'll just give what I'll just give the name so I will say name sorry name and suppose values in the name I just give one field what Krishna okay then what will happen see select star from test 11 if you do this then the serial number will be set to its what it will be set to its default value and this is exactly what we did while creating the table we said that serial number default value for serial number has to be what it has to be 0 so if you don't give the value of serial number then it will be set to its default value of 0 and this is exactly what happened over here I just inserted the name into this test one table I didn't inserted the serial number the serial number was set to 0 and serial number can never be null see if you describe test one then what is happening serial number can never be set to null so it because we have added a normal constraint to the serial number column okay so what is a foreign key constraint so it's a kind of referential integrity constraint so what is exactly this referential integrity constraint see whenever I ask many students what is foreign key they just say that foreign key is a key that points to you know some other table so they just have don't have any clarity in mind so but foreign key is used impose you know a kind of parent child relationship between two tables so what is a parent child relationship a parent child relationship states that suppose can you create a child without a parent it is never possible first you have to create the parent and then you can create the child so this is what I've written a foreign key constraint denotes a parent child relationship a child cannot be inserted unless what exists a parent exist and suppose you delete a parent and the child for the parent exists so can you delete the parent you cannot delete the parent because why you cannot delete the parent because the child will become orphan so this kind of constraint is imposed by a foreign key relationship between two tables you will also see you know after sometime we will see that foreign key constraint can be imposed within the same table so you know so this is just a kind of extension to foreign key but right now just just consider foreign key is like it points to the primary key of another table and it maintains referential integrity between two tables so let us first of all understand the need for foreign key and see how you know between these two tables between two tables there is no referential integrity for example I have one table the EMP table and I have one table the dpt table you know these tables kind of tables we use frequently and I say select star from EMP and I will say select star from dpt so let me first of all state that these two tables belong to the same organization so for the organization there are two employees Nikhil and Nithin and working in the sales and marketing department so Nikhil is working in sales because his department ID is 10 and department ID 10 is sales department Nithin is working in the marketing department so both of these tables belong to the same organization and now let me do something I will insert a record into which table insert a recording EMP table and say values suppose there is employee third employee and name of the employee is Nihar and her department ID is 40 if I try to insert this record it will be inserted and I will say select star from EMP and select star from DMP can you see what is the flaw with this this kind of this these two tables what is the flaw with these two tables there is a serious flaw with these two tables for example just what is the problem with these two tables see if you because these tables you know they belong to the same organization here these two belong to the same organization here I've inserted a employee which implies he nikhil is working in the sales department that I know you know Nikhil is working in the sales department but here I've inserted a employee which is working in department ID 40 but is department ID 40 existing department ID 40 is not existing that is why this if you try to insert this if you insert this imply this employee should have never have got inserted see because department ID 40 is it is not existing then how can I imply work in department number 40 you know department ID 40 so ideally this record should have never got inserted okay so can you just make out which will be the child table and which will be the parent table this employees table will be what table it will be the child table and this departments table will be what this will be the parent table why because the child cannot exist unless the parent exist see here this Neha cannot exist until until a department with the ID 40 exist but because we have not inserted the foreign key constraint it is you know it is allowing us to do so for now suppose I try to do one more thing I try to delete depart the marketing department so what will I say I'll just delete from which table delete from DPT where the name equal to what it is equal to marketing if I try to delete the marketing department it will get deleted and I will say select star from EMP and then I will say select star select star from DPT but again now there is a serious problem I deleted the marketing department I deleted the marketing department but you see this employee Nitin has become orphan record because why because its parent you know its corresponding department in the department stable is not existing the DPT table it is not existing so this record has become an orphan record so ideally when we try to delete the marketing department it should not have allowed to delete the marketing department so if you want to impose these kind of relationship between these two tables so that what kind of relationship that a parent a child cannot exist up until unless a parent exist and a parent cannot be deleted until and unless it and it has some dependent records child records so this is the kind of relationship between two tables that is imposed by a foreign key so how will we add a foreign key I will just clear these two tables delete from EMP and I will say delete from DPT first of all I'll clear these two tables and you know these two tables are now empty if I say select star from DPT it is empty and then I say select star from EMPT is empty and now if you want to add a foreign key then you will use this command what command to add a foreign key constraint on existing column use alter table table name add constraint constraint name then foreign key then on the column on the child table that you are imposing the foreign key and which table it references so I'll just you know I'll just want to make a point