 So, as Nihal has introduced, we are going to cover some basic syntaxes which are used while using MySQL. So, I am going to start with MySQL. So, basically what is MySQL? You have heard of as many of the CS people over here have heard of SQL, MS SQL, MS access Oracle means there are n number of databases. So, MySQL is also one type of open source database system, but it differs in traditional databases like Oracle and MS access. And it has, so in this presentation we are going to cover what is MySQL and how is it different from SQL and other things and as it is a programming language course and I need to introduce you on the programming language used in SQL that is a short version of SQL and in that we are going to cover some DDS statements like create table, alter table, create database, alter database right and the statements which are used to manipulate that particular data which is stored into the table is called as data manipulation statements and it is type of, it is a subset of language called as data manipulation language. So, in that we are going to cover insert, select, update, delete means it is pretty much like loading data and trying to update data as per the requirements and again we are covering some dangerous DDS statements like drop and truncate like I said they are dangerous because once you drop particular table or database I will cover it later on in detail you cannot recover it means you cannot roll back to original state. So, after that means these are only syntax part like which you will usually use and another part is joins means if there are like I explained those statements they work and initially you will work with single table but when the scenario goes to multiple tables then you need to join those particular tables using some syntaxes like there are different types of join like cross join, natural join, inner join, quick join and there are sub types of other joins like outer join means I will explain them in detail. So, can I, can somebody know what is difference between MySQL and SQL, somebody what is SQL and what is MySQL, only vague difference is ok. SQL is a language, is a programming language it is defined by some programmers and it is being developed. So, it is a standard set of commands which are, which any developers can take and modify in their way and deliver a system which uses a SQL. So, MySQL is basically open source RDBMS means relational database management system. So, what is database everybody knows I hope everybody know what is a database like it is a structure in which we store the data into our computer like it is understandable by a computer and it is stored in a very standardized way. So, it is called as a database and it database contains tables, records and we used to query particular thing to database and database will return the response to the query. So, database is like this structure. So, any web application is consist of these three layers nowadays means there can be two tire or three tire architecture. So, I am explaining this is a three tire architecture. So, this is the presentation layer which user usually see like it can be JSP, HTML and there are more type of many type of presentation layers available like Jumla also can be fitted over here. So, this is a business layer means your overall logic any business any logic of application will be stored here means how what things we are going to display to the user, how we are going to display to the user, what we have to response respond to the user on particular actions. So, these things are stored over here and this is pretty much the storage like database it can be MySQL and it can be MySQL, Oracle or any other thing MS access. So, you guys may be developing some of your projects like in your BTEC. So, you can experience this type of structure over there and when you go to the company you will also similar type of structures in your project. So, MySQL stays over here. So, I am pretty much concerned about how to operate in this particular zone. So, we are not concerning about this particular tires. So, MySQL as I said it is a relational database. So means there are types of database like a database which are stored there is no relation between the tables stored on the database and there is a relation between tables which are stored in the database. So, like there is a database called as company. So, in MySQL it is pretty much understood that you are going to store the tables which are related to the company like tables of employee, tables of departments, tables of payroll like only the tables related to the company are being stored in the company database. So, it is a relational database and there is a relation between the row and the fields of the database. So, and basically the My in MySQL is nothing but the name of a girl child of the co-founder of company called as MySQL AB. So, MySQL has been started by these guys of company called as MySQL AB it is a Swedish company and it has been overtaken by Oracle Corporation now. So, it is not a property of MySQL AB now. So, his co-founder was Michael Vardinis and his daughter's name was My. So, as he developed this language he developed this system. So, he put that his child's name over there. So, there is a that is a what you can say secret about MySQL. And you can you do you know how have you only developed HTML software using Dream Ur or any other application. So, MySQL is like a Dream Ur which uses HTML to develop and along with HTML it uses JS and other things to develop the application. So, SQL is a language and MySQL is a system which uses SQL. So, and we can pronounce it as a MySQL or MySQL means it depends upon you how to pronounce it. Yes you and so I will go with the queries which are let which are used in MySQL. So, is it clear that what is different between SQL and MySQL? Any queries? Not clear, okay. So, SQL is a language like Java, HTML and other things. SQL is a language. It is a set of means it is only set of commands like you have to use these commands and to do some particular operation you need to use this command it is given in SQL, but SQL cannot be installed as it is on your system like there is a Java you can install Java, but for SQL you need any other system along which SQL comes. So, MySQL is a system. So, you can consider as a MySQL this container. This is SQL and this is MySQL. So, along with SQL there are many other things like as it is a database. So, many people it is assumed that many users will access that particular database simultaneously. So, there are multiple sessions. So, you need to monitor the session. You need to give the graphical user interface to the user. So, these kind of things are also there like here can be GUI component, here can be session monitoring tool. So, other tools are there. If you consider something like a system like a data. Suppose, you are sitting somewhere in a lab, you have server somewhere and you have say DB server. Now, you are sitting in a lab and you are accessing some university database wherein you have data of students. So, you will open some interface in your browser, there are some I and you will enter some roll number over here. That request will be forwarded to the server with PHP or something like that or JSP something like that. So, these guys will process this information over here. Now, the information is like they want some name of the student whose number is roll number one. So, now they are going to fire a query in a system wherein you have already stored some information. So, now this system is called database server. Now, what is this database wherein it is called relational database. Relational is like wherein relations are called tables. So, that's why it is relational database. So, in a table you will store say roll number and you have name of the student. Say roll number one, his name is say A or 2 is like B. So, these guys, I mean server is going to ask, okay, give me the name of a person whose roll number is 1. So, fire a query over here. Now, the information is stored, is in MySQL. So, this system makes sure that it stores some information and retrieves it efficiently. So, I will fire a query and those statements you are going to learn, they will fire a query and it is going to return you A and this information is being displayed and then sent back to the browser. So, now, Snehalish has explained what is the role of DB server over here. So, as a explanation purpose, we are going to give a running demo along with explaining the statement in which we are going to create a scenario where there is a company, there are employees and there is a department. So, we are only considering these two tables only, there's two relations and we are going to play with those tables, okay. So, as I said, now we have to define the data, like we have to define the database, we have to define the tables, we have to define, we have to insert the data and we have to play with them. So, we have to, for defining the data, we are using statements called as create database. It is pretty much straightforward, like we have to create the database, so we are using create database statement, okay. So, you can see the create database company DB will eventually create a company, database called as company DB, means it will allocate a space on the storage. It is called as a company DB database. So, everything and on using it, whichever tables you are going to create, whatever data you are going to insert will be stored in this particular area. So, this DB server, if a query comes for that company DB, any table in company DB database, so this DB server will look into that company DB database and go to that particular table, like here will be some particular table called as employee and it will satisfy users' requirement, like give me that particular employee with this particular employee ID or give me a particular employee which belongs to this particular department, like HR or any other department. So, this is the whole scenario where we are going to create using MySQL queries. So, it is many guys who are familiar with SQL will find it is very similar to SQL. So, this will be kind of revision for them guys and you can ask any question which you find some different between this SQL and your original PL SQL. So, here we have created create database company DB and we are going to create two tables, like create table department and create table employee. So, this employee table, as you can see over there, is containing two fields. One is the last name and department ID and this department table which contains two fields or two columns called as department ID and department name. So, I am going to show you how to create these tables using your SQL command. So, I will initially tell you some commands and you can easily find them on internet how to install SQL on your particular Ubuntu system as I am expecting everybody is using Ubuntu. You can also you install it on Windows but some different set of instructions will be needed. So, for installing MySQL server, is it visible or should I need to increase the font? Visible or not? Okay. So, you have to install like MySQL server using aggregate install. So, it will install the latest version or whichever is available on the repository and then after that it will automatically start the service of MySQL server. So, you can check it by using sudo netstat, netstat will, netstat is the command which show the open connections to the current system. And there we are checking that is MySQL server has been started or not. So, in result you can get a single line like localhost, it is open a TCP port and it is in mode listen. So, it is listening to the any queries coming from the client. And if not then you can do like your usual business, you can restart the MySQL server. You can see that the service has been started or not. So, it is these are only simple steps to install. Only first step is essential and second step is second step or not you can verify that particular installation has been done or not. So, on MySQL I have installed that particular MySQL. So, I am going to check is that particular service is running or not. So, it is showing that yes it is running and the TCP port has been opened and it is in mode listens. So, it has come on second line because the font is greater. And the process ID is 1244 and the service, the process administrating that MySQL is MySQL D. So, it is a data administrator of MySQL. In Windows it is MySQL D.exe and here it is MySQL D.sh or any script language script. So, here as you have seen that service has been started. So, I can I am ready to. So, any doubt in this like how to install and how to check that service has been started or not or any doubt in that particular command next step tap grape MySQL. So, we are tapping the connections which are open opened in this particular system and we are grape we are pattern matching MySQL into that. So, here as we were creating the company database. So, I am going to create that particular database like and for starting that MySQL for starting using that MySQL you need to type the command like MySQL. And I have defined user called as a root and I am asking user to enter the password using MySQL U root P. So, when I enter it is asking me to enter password. This password you like to give while you install. So, while you install make sure that you remember your the password. Use a simple passwords only because it will make a problem later on because you need to install reinstall it. So, as you can see the MySQL prompt has been came over there. So, here onwards you can go easily with whatever commands you need to type, whatever operation you need to do. So, any doubt in this? This is small question to you guys. I mean, can I tell why when we started this MySQL server? Why was it listening? Why the statement listen was coming? I hope all of you know what is like server client kind of applications. I mean, we are just concerned about the guys who are from EC. CS guys must be knowing it, but I mean our aim is to like reach to the people who doesn't know. You can see which version of MySQL has been installed by checking that there are variables in MySQL. Very much there are multiple engines which are used to run the query. And so, there are very much engines like MyISM or I know DB. I'm not going to cover because it is too much deep. So, these engines and other things are stored as a variable names. And you can see which version of MySQL has been installed. But having a simple command like show variables, like is a pattern matching. And I'm telling that I need to see versions. So, it can have MySQL version, MySQL DB version. Anything can be prepended to that particular version thing. So, I'm using that particular pattern, variables and it. That particular I know DB and the protocols version are other things are coming over here. So, version is coming over there and it is 5.5, 37. And it is compatible with my system. So, that system name is appended over there. So, 5.5, 37. So, there are like I think the version has gone to 5.5, 5.6, 0.18 or 0.17. You can check it on Wikipedia, like which is current stable version or which is current developer version has been released. And you can install that version. So, you need to modify that particular command little bit because here it is taking default version and you will install your specific version by modifying it. So, here I'm going to create that particular database. Create database. So, here you can see that query, okay. One row has been affected. Like, one row affected is only the standard response, like when you add particular row. So, you can see and you can see that particular database has been added or not by using show databases command. So, you can see these many database are in the system like company A, company, company DB, myDB or anything. So, MySQL, information underscore schema, performance underscore schema are three default tables in MySQL system. And these tables has been created by user, company A, company, company DB. So, I'm going to use that particular company DB. So, you need to tell the MySQL system that use particular DB. So, you need to use command use company underscore DB. So, that default database has been changed to database company DB. So, now any route in this, it is pretty much simple. Now, you have to create these tables like department, then employee in that particular company DB. And the columns like department ID, department name and last name and employee, department ID in the employee table. So, here create table, employee, last name. So, the name of that particular column is last name. And the type of that column means values we are going to store in that column are of string types. So, there are types care, where care, where care too. In Oracle, you can see multiple types. But here that the MySQL doesn't support all SQL commands or all SQL facilities fully. So, it is a shorter version of SQL. So, you will see some differences. Like the data, the Oracle also supports the num type. But here it doesn't support num. You have to use int explicitly. And the last name and I need to go department ID. So, it is type of int. So, the worker, the 20 in that will store 20 bytes for that particular name. So, you cannot extend the name of the last name more than 20 bytes. So, AC people is getting, are getting that what does worker 20 mean? Yes or no? So, and the department ID int query. Any question? So, and the department ID type is int. So, here it will store, it will store that department ID in the standard size allocated to int. Like it can be 16 byte or 8 byte, whatever according system. So, I am going to create that particular table. So, you can see that particular table has been created using your show command. Show, show me the table. So, you can see that tables in company DB the employee has been created. And you can check that your table structure is what you are expected and what is being created by using the description command like desk employee, describe employee given the description of employee. And you can see the last name having type worker 20 and department ID has been allocated default size of 11. You can modify that int 20 over there, but I am not bothered about that. And is null values are null values. So, null value concept is that you can leave that particular field blank while inserting the data. So, by default I am allowing yes, but in your actual project it should not be null. So, you have to give the particular constraint like not null in front of that particular create query. And is it a key like there are keys like primary key, unique key, candidate key. So, primary key is a special type of unique key which it does not allow null. So, I think I hope CS guys know what is unique key what is primary key. But problem with easy people. So, primary key is a that particular key which identifies unique row into your table. So, the employee name will not be unique, but the employee ID which has been allocated or roll number allocated to student is unique. So, I am not going to consider anything as a primary key. So, for the sake of simplicity, I am using the I am not using those primary keys and a unique key constraint. So, now I have to create that particular another table called as department. So, I am using same command similar command department and the types where the department ID and department name. It can be work R 30. So, here you can see like these tables have been created department and employee. So, we are we need to insert. So, this is the use of create table like we have we have created the database using create command. We have created tables. So, we have defined the data type of data in which we are going to insert our information. So, this is about create statement. So, any doubt in this? So, what happens usually we create something and later on the requirement changes and we need to insert some columns into our existing structure. So, we basically need to alter the existing structure. So, for that the MySQL is using the SQL command like alter table. So, alter table here alter table is altering the table employee and here I am adding a column called as first name and which has a size of 20 bytes of a type of wire cap. So, it is it is a string of 20 bytes and later on in demo I have not used the first name. So, I have I am also removing that particular column. So, you can I can show you over here. So, here initially there is no any first name and over here. So, I am adding that first name column over here. So, on checking you can see that the first name is added first name column has been added. So, MySQL also allows you to set the position of first name column which the new column which is being inserted by by allowing the keywords like after first last. So, you can specify the position of first name column. So, I am showing I just need to delete it. So, the syntax was alter table employee drop column first name. So, initially when you when you are giving that particular addition command addition of a column. So, you did not need to specify that it was a column because here you are specifying that particular type like wire cap 20. So, it understand the MySQL engine understand that yes it is a column and I need to add to add it to particular structure and while dropping you need to specify that is it a column or not. Means there are any other there are many other objects like triggers and other things. So, it can have the same name like first name. So, you need to specify that I need to drop a column called as first name. So, as by default it was adding at the last of that rows. So, I am by specifying first at the end I am expecting that the first name should be added to the first column in the database. So, you can see the first name is added at the first and also if you want if there is a tradition like last name is the first then you can also specify like after that last name I want to add. So, the first name has come over after last name. So, any problem in that it was about positioning that particular column. So, I am dropping that column. And now our table has been table structure has been defined. So, here we need to insert particular things into that particular table. So, the command is insert. So, it is a SQL command which helps us to insert that particular data in particular table by specifying the command is insert into then you have to specify the table name. So, I am adding the data into department then into employee. So, I am giving the table name over there and I have to use that particular clause called as values and in between that I can specify the values in order of the columns of a table. So, in the department it was department id, department name. So, 31 is a department id and department name. So, if you are not entered in this particular error then particular order then it will show error because the worker requires that sales in that particular codes and the int is entered without codes. So, you need to care about the order if you are not specifying the order in front of department. So, and in employee also you need to insert the same values. So, I have to enter the values in the last name and department id order. So, the one row has been inserted and you can check that particular data has been inserted by using the select command. So, the what value what data we have inserted can be shown by using select star from employee. So, it is about viewing the data. It is type of query which has a syntax called as select then table name select then column name which you want to view from particular table. So, after form from you can give the names of multiple tables but here we are considering only single table environment. So, we have given the single table name and the star is a pattern matcher which gives which displays all the columns which are present in the database and if you are worry about specific column then you can fire select department name from department. So, and also you can limit like what happens when the database is long. So, at that time you need to see only first five rows or ten rows which are ordered in a default sequence which are sorted in default order it can be ascending or descending. So, for that there is a keyword called as a limit. So, I am inserting another value like raffially and here you are seeing two rows. So, if you want to limit it to one rows then you can specify the keyword limit at the end of command and I can specify limit one. So, it need it should show one line. So, you can see that raffle 31 has been displayed. So, there are it means you can use limit in means your difficult queries to give to get the particular output. So, after selection sometimes the data inserted in the table need to be updated. So, the update department the update query is like update is a query and after that you need to provide the table for which you need to update and the set there is a set clause and after that you need to give the column name which you need to update and the new value which you are going to update for that particular column. So, initially suppose the 31 department id was associated with the sales department name and you need to update that particular department id with the department which having id 31 to the logistic to the name logistic. So, you can use update department set department name is equal to logistic where department id is equal to 31. So, here we have seen the introduction of new clause called as a where. So, where is there where clause is used for specifying the particular condition or a predicate on which based on which we filter the particular rows from the table and modify those rows or modify view those rows. So, after where you need to give the condition it is pretty much simple. So, and also I am updating like employee update employee say department id 31 where last name was Jones. So, in your table. So, there was the department last name was Raphael and department id was 31. So, I am updating the I should not update last name. So, here you can see that Rapens department has been changed to 33 where it was initially 31. So, you can use this update query over there. So, any doubt in this like updation etc guys. So, when there is a need to delete particular data like some employee has left the company and you need to delete his or her record. So, you need you can use delete query and the syntax is like delete from table name I am deleting employee. So, I am using employee table where last name was Jones. So, Jones has left the company. So, I need to delete it his name. So, I am delete from employee where last name was Jones and if some department has been removed or some department has been combined to another department. So, I am deleting that particular department using delete from department where department name was logistic or for the sake of correctness of query if I do not know exactly the department I am also providing department id like I have to delete department id with which was 31. So, I am not deleting anything in a demo. So, any doubt in this delete from So, delete is it is a data manipulation technique it does not go to your particular data structure it only deletes the particular data from your table. So, if you have commit there are concepts in transaction called as commit rollback or so if you have committed your data in company you will fill face a scenario when you insert something as a database you need to commit or if the usually auto commit is not set auto commit is like whenever you run something then the database is committed. So, when you fire commit command then data is stored and after that if you are trying delete command and you have said that I wrongly deleted that particular record you can go back to particular previous version of a table using rollback command. So, delete allows you to rollback to previous state where it was no auto commit was set but there are other commands which allow you to delete particular record but does not allow it to rollback. So, I am explaining I will explain those that command later. So, any doubt in this delete command this insert select update and delete where data manipulation commands they were playing with the data but not with the structure and the create alter drop and truncate are the commands which play with the particular data structure like they have to do some business with the storage actual storage on the drive. So, if you know if you need to drop some table or database you can use drop and particular object name it can be a table it can be a trigger it can be a sequence whatever you can drop it by simply firing drop employees but be careful before dropping anything because once you drop anything you cannot rollback or means there are some complex methods by using you can rollback but by simply typing rollback you cannot restore that particular table. So, trigger is like the scenario you want to you can consider like you are adding some employee to particular employee table and by default you cannot specify the usually system administration does not specify the employee to the particular employee. So, when you insert a record like insert into the employee last name is for Jones and department is 31. So, the employee the trigger will be something which will be curious to see that particular event like insert on employee is happening and on that event is when that event is happen then that trigger will add something like employee ID to that particular table. So, trigger is like some what you can say trigger is you can take a literal meaning when happen something on the database another thing happens in a background so that is the trigger. Consider scenario where in you have to pilot tax pilot tax on some amount and on employee suppose say you pilot tax when you cross amount like say one leg usually in a company suppose say your monthly income is around 90,000. If somebody crosses that amount per month you would like to have a trigger. So, once somebody in your database insert that query and that amounts particularly exit I mean exceeds one leg that it was procedure will be written which will I mean once that amount exceeds that one leg trigger will invoke a method which will take particular actions which will like calculate some tax on it. Yes or no like any another doubt so means there are means many different objects you can see in the reference we are giving and because it is not possible to cover all the objects in this particular one hour or one and half hour session so it was a drop. So, there is another command called as a truncate. So, the truncate employees will essentially do the same work as a delete like unconditional delete. So, when you do delete star from employees or delete the delete star from employees will delete all the rows from employees. So, whatever we have insert Raphael, Raphaely and the particular department IDs will be deleted. So, the same thing will happen when you do truncate. But there are some different scenario happen in a truncate. As I said it is a data definition language and it has to do something with the database or table structure it will do this action like it will first drop that particular table and it will again recreate that particular table you are getting. So, it is much faster like delete is deleting that particular thing row by row. If there is a where there is no where condition but delete will take particular row and if there is not a where then that delete that row or if there is a where then take is it satisfying that condition and delete that particular row. So, but truncate drops that particular table at once and recreate the particular employee table. So, it is much faster and so for the performance you can use truncate but the drawback of truncate is like it does not allow it to roll back because the structure has been deleted from the database structure and it is it has been registered as a new empty structure to that particular database schema. So, you cannot roll back to the original table filled with your rows. So, that is the difference between truncate and delete. So, any doubt in truncate and delete schema up. Table structure is preserved from table structure is as I have shown that show tables command. So, it was essentially reading some data from table. So, it is not visible to normal user it is it is it has been taken care by my skill. So, when you drop that particular thing that particular definition from the meta table like a table over table is deleted and reinsert it into that table. So, after that the information like these many rows were there or these type of rows the pointers were there. So, those pointers have been vanished and empty pointers has been void pointers has been replaced over there. So, I does not know like exact scenario happening over there. So, you can consider as the particular record has been deleted from the meta table and a new record has been inserted into the meta table. So, you cannot roll back to that particular table with your field rows. No, no, no. Delete does not make a new table. Delete only deletes that particular rows row by row. So, that is why it is a performance it is giving good performance. So, we were here only with only one table. So, what if there are multiple tables. So, you need to join those tables using there is a structure called there is a query called as a join. So, join is basically for joining tables and taking data from both the tables like suppose there is a situation like you have to select some particular department and showing and you need to show the employees which are work in that department. So, it will use like select department ID select star from department comma employee where department ID is 31. So, it will display the employees which have department ID 31 and it will in front of that employee name it will also show the department name which is combined output of that employee table and department table which I will show in our demo. So, join is combining the data from two tables and displaying to the user as a one particular schema. So, that it is not two tables more than two tables. So, so the examples I think it is not visible properly. So, you can just see that on left side there is employee table on right side the department table and the rows like Raphael Jones, Heisenberg are employees of that particular company departments like sales engineering clear clerical marketing are there in the company. So, I am going to join these tables and in various manner and show you the output. So, the department ID column of the department table is a primary key like it should be unique you cannot you to same ID to different departments. So, and the department ID used in that particular employee table is a foreign key. So, you are assigning particular employee to the existing departments in the company you cannot assign or if there is a CEO of the company, he need not be assigned to any department. So, I have we have given the John as a employee and we have not specified the department of that particular employee. So, that is the referential integrity which which you can learn more about in reference we have given referential integrity references one particular column from another table one particular primary key from another table and uses the values from that domain. So, in reference department ID you can repeat the particular key like 3333 has been repeated but you cannot repeat that 33 into the primary key column of another table. So, any doubts in primary key foreign key integrity. So, it just for reference. So, there are types of joints like called as a cross join. So, cross join is a simply like when in mathematics you do you get a cartesian product of a sets by using A cross B. So, you can consider tables as a sets like employee and that particular department and when you will do employee cross that department then you will get all the records like all possible permutation and combination of those employee and department ID in front of as a user as a result. So, here you can see that employee last name or employee department and department dot department name and department dot department ID. Some systems provide that table name dot column name specification but some systems like Ubuntu does not provide. It will only show last name department ID department ID department name. So, it will join it will take one row from particular employee and it will match with all the rows possible in that particular another table. So, it is a simply cartesian product when you do like you get multiple sets by cross cross joining some two sets or two or more sets the same scenarios over here. So, it is the largest joint possible the output will show the largest possible matches in your result. So, cross join will show the largest output. So, there is another type called as inner join. So, inner join here inner join allows you to provide some particular condition on which join is being happened. So, inner join will take one particular column from a table and try to match with another particular column from another table. So, the records which are satisfying both the condition specified in the where where clause will only be shown in the result. So, you can see that query over here. So, there are two ways to do inner join. So, I am select star from employee inner join department. So, it is a syntax like you can you do not bother our syntax like you can find it on internet while typing inner join department on on will specify the particular condition where usually we specify the condition using where clause but for in joins we are using on keyword on employee dot department id equals to department dot department id. So, return me those return me that data which return me a data like where the department of employee is equivalent to department id specified in the department table. So, here you will see this kind of output like employee last name department id then the department id and department name from the department table. So, this the data from two tables have been joined on this particular condition. It is not like Cartesian product it will you all the permutation and combination we are filtering that and there is another way by using our usual syntax like select star from employee comma department as I said we can specify multiple tables we are specifying where employee dot department id and department dot department id is same. So, one thing to be noted here like the department id column was the name of that column was repeating in those both the tables. So, I need to specify which department id and which table department id. So, if I use depth id in the department. So, I would I would use department id is equal to depth id. So, that MySQL engine is pretty much intelligent that it will select the department id from employee and depth id from department if you are not specifying that table dot column over here. So, we need out in this inner join inner join means we are joining the columns which are inside those tables like we the condition is satisfied by those columns. So, when I consider filtering applied for Cartesian product. Yeah in simple words you can say that any doubt last row there is a special condition like inner join and null. So, when you are comparing two columns like department id here we are considering department id of employee and department id of department table. So, there was scenario of John is equal to null. So, null is a null has null is been given special treatment in the MySQL any SQL environment. So, you cannot anything we you cannot compare anything with a null. So, in expression which uses a null will reproduce will produce a result called as a null. So, you cannot compare null equal to one is you cannot compare or is you cannot say that null is greater than zero or null is less than zero. So, be careful by using those columns which are not taking any null value in that. So, you can you will expect some orbit result if you are using null if you are using a department if you are using a column which contains a null value. So, it is a good it is a good thing to try inner join on the tables on columns which are not containing any null value. What is condition there are other types of joints which consider that which are null you can say null aware like they know that particular null value is in that particular department id and I have to treat it specially like I have to keep it aside and I do not have to match with another other things from another table. So, I will show in the outer join concept. So, there is another type of joint called as equi-join. So, it is like I said in the condition if you are using equal to we have used that equal to comparator. So, if you are using equal to comparator then it is called as equi-join. So, if there was some greater than less than thing greater than 4000 greater than 5000 then it was not it is not equi-join. So, equi-join is deals with one equal equality comparison it is a simple like employed or department id is equal to department or department id and it is a special case of inner join here you does not need to specify that inner join department on employee. So, here you can simply specify that select start from employee join department and the condition which is using equality comparator. So, that is a natural join means if you are not specifying any condition not specifying any where clause then how the tables will join. So, the MySQL engine will find the column that column names from different table and if he finds any column with the same name like department id was there then it will show it will show the output equi-joined on equi-joined on that particular column. So, be careful while using that particular same name into different tables be careful about the type you need to give the same type or else you need to change that particular column name. So, the natural join will give only correct output if the types are same. So, here the department id was same. So, you can see that another column which was appearing in the output of that inner join department id was twice coming twice. So, here it has been reduced to only one because it understand that the column is common in both the tables. So, it does not need to represent it twice. So, that particular inner join output can be shown over here. So, now I said that when the null condition was there. So, you need to treat it specifically. So, I am using outer join. So, outer join is like that you need to go out of this particular join condition and you need to display all the records from particular table. Like in the inner join condition or other equation condition you have to the records have to satisfy that particular condition. But sometimes the situation comes like you need to show all the records using join. So, for that you need to use this outer join whether or not the matching record exists in another table you have to display all the records. So, there are types like left outer join and right outer join. So, here my left table is employee and right table is department. So, if I want to display all the records flow employee whether or not is it have matching entry from that particular department ID. So, here it was like the employee called as John was having department ID null and it was not having as null is not being compared to anything in the department ID column. So, it has to be shown as a separate entry in the result and from department ID department table that thing should be blank over here because we cannot compare null to anything. So, those kind of scenarios has been handled by outer join. So, of which table you have if you are going to display the all columns of all data from the left table in the join condition then you can use left outer join as a particular syntax then it is called as left outer join. If you are worried about the right table in the right side of that particular join keyword then it is called as right outer join and if you are worried about showing the table records from all the tables from left and right then you can use the particular thing is called as full outer join. So, the syntax of left outer join is select employee left outer join department. So, here you can expect that all the records from employee are being displayed like that particular last record which was not displayed in the previous case and I am using the same equation condition like department id equal to department dot department id. So, the output will be like this the john null and null null which is shown in the gray background is being displayed. So, initially the john null was not coming in the inner join condition. So, here the john null was not coming and that particular row has been inserted in this left outer join and the other output which has matching column in that particular department table is same as it is. So, if the condition is changed like you have to display all the things from department whether or not any employee is working for the department. So, that condition can be satisfied by using right outer join. So, only the keyword is changed like from left to right. So, the engine will pick the table which we have to which he need to display all the records. Here it will pick department and in case of left outer join it was picking employee. So, here that particular john null is not matching to anything. So, that has been dropped by a null null and there was no employee which was working for the department called as marketing and having department id 30 pi. So, marketing 30 pi is preceded with the null null values from that employee table. So, any doubt in this left, right. So, as I said if you want to display the output combined of left and right. So, that is called as full outer join. So, in sql normal pale sql there is a keyword called as full outer join, but my sql does not support that full outer join. So, here you have to do the union of those particular things. So, as I previously said you can consider these tables as a sets and the output is shown as a table it can be also set. So, the normal union operation which is being operated which is being done on set can be done over here left outer join union right outer join. So, so the output will be like the John null null null and null null marketing 30 pi these rows will be added as a to the inner join queries result. So, any doubt in this full right, left. So, can anybody tell me like which out when you consider the scenario which output will be larger of full outer join or a cross join. Why? So, if I add any number of conditions to the full outer join will output ever grow than cross join. It is childish question, but you need to consider like because the space matters while showing the result. And you can limit that particular output by using the limit keyword when also you can limit the output of that cross join by using limit keyword. If you want to display only 30 combination then limit 30, limit 21 you can limit that particular output. Here also in the cross joins output was not fitting on this particular slide. So, we have cropped that. So, you can avoid these kind of scenarios by using that particular limit keyword. I mean you can find all this information on W3 So, the references means which the developers are using like the MySQL wikipage of MySQL is pretty much famous. Here you can find the which is the current stable version of MySQL and the references given in Deo.MySQL and the MySQL.com is a site where the MySQL downloadable can be found and you can type and test your MySQL queries on w3schools.sql and you do not need I have given the book references over there but you do not need to buy any book for that SQL because you cannot like it will be pretty much hard to read from book and type into computer it will be good to read it online and execute it simultaneously. So, that was about MySQL. So, share this slide with you guys. I have been prepared like based on the permission given and from the request to set some different kind of queries and some kind of complex query but this year the query has been very simple. So, you only need to So, only just see what is current updation in the MySQL and go through the references if it is possible. Just execute those commands and play with it. So, it will be helpful for your queries and other future endeavors. So, you can go further by using these set of sequence and do much better in MySQL. So, any doubt in MySQL? So, that was all. Thank you.