The Ultimate MySQL : Go from SQL Beginner to Advanced

1. Course introduction: Hello. Welcome to MySQL Bootcam Course. My name is John Shortar and I'm professionally Full Stack Web developer and online instructor. My SQuil sometimes we pronounce MySQL. It is the most popular open source database in the world. It is used by various tech giants like Google, Apple, Amazon, et cetera. Over the couple of years, people said everyone should learn to code. But now everyone should learn to SQL. Whether you are IS developer, Android developer, game developer or a web developer. And if you are in sales and marketing, otherwise, run your own company, learning SQL would be great addition for your portfolio. So before we jump into this course, let's see what we are going to learn from these course. In our first tutorial, I'm going to give you the idea what is MasquL and what is relational database. And then we will see how to install MSQuel in our compute room. In our next tutorial, we are getting familiar with MySQL W wedge application. It is a graphical interface that is used to run our SQL query. Next, we are going to learn how we can create user and database. With that, we are going to learn how can we create tables. Next, we will learn how can we insert multiple data in our table? And the next part, we start constraint, select command with her clause or type of SQL operators. We are going to learn wild cards, regular expressions, water Y and distinct clause, is null and not null operators, and aggregate functions. Also, we are going to learn update command, delete command, comet, and rollback. And then we are going jump into the most important part of my skill, which is relational database. In this section, we are going to learn primary key and foreign key concepts. Next, we are going to work with joins, lip join, right join, cross join, inner join, et cetera. Also we are going to learn how can we join multiple table at once? Next, we are going to learn group by and having clause. With that, also, we are going to working with sub queries like exist, not exist, Union union A, and in the next part, we are going to learn conditions. E and K statement, arithmetic function, string function, det function, time function. Also you're going to learn alter command, what a distinct drop and truncate, view, index, and import and export database using Mcqule W wedge application, et cetera. After that, we're going to start our projects. This course is created for completely beginner, otherwise intermediate students. If you don't have any knowledge about database, otherwise, any programming language, also you can join this course. This course is completely beginner friendly. After end of this course, you are able to create complete library management system, database management systems, student management system, et cetera. This course would be great addition for your portfolio. So what are you waiting for? Let's get started. 2. What is my sql: First of all, what is mySQL? MySQL is a database management system. Basically, it is a softire which can manage data. Now you might have question what is database? A database is a collection of data stored in a format that can easily accessed. Let me clear the concept, how it's actually work. What is database? Here you can see tiMLFm in your screen. You can see this kind of from many websites. Maybe you've seen it in a login page, register page, flight booking website, hotel booking website, college application, et cetera. The information we get from this TML from we stored in a database. Basically, MySQL store our data in a they will format, something like that. As you can see in our form, from here, we take name edge and Zender. Suppose total four people fill up this from Adan, Smith, Rabbi, and Roma, and they fill up this form with their edge and Zender. All type of data we store in our database, and we call it collection of data. The advantage of saving data in this way is that we can read it easily. It looked like a Excel sheet. We can easily read this table. We can read all the name their age, their gender, where do we store data in organized way, we call it database. Now, let's talk about what is database management system. Otherwise, we can call it DVM. MySQL is a one of database management system. It's a software, which helping us to manage data and to manage the data, we use SQL query. Now let's see how it work with database. Suppose there is a client with computer and he tried to fill some from and set the data in a database. The client cannot set the data directly to this database. They need a software, software called DBMS, which stands for database Magmin system. At first, the information from the client side, go to the DBMS. Then DBMS send the data to the database, and whenever we try to access, otherwise, extract the data from the database. Once again, DBMS play the mediator role. And there are many popular DBMS software in the world. Oracle is in top of all of them, and thence come MSQL. MCCL is the world's second most popular DBMS system. Without it, there are other popular DBMS system, MSQOL server posts ESQL Mongo DV. Now it's clear for you, what is DBMS? Now, let's talk about Typop database. There are two type of database. First one is relational database and second one is NoSQL database. If I talk about relational database, this kind of database use table format to store the data. Here you can see a database structure, which is based on movie ratings, and it is a relational database. In this database, you can see total fourth table, rating table, user table, movie table, and tax table, and both of the table connected with each other. That's why we call it relational database, RD Bs. In this tutorial series, we are going to learn relational database because mySQL is a relational database. As I told you, our relational database use AQL language to communicate each other and AQL stands for structure query language. Now, let's talk about our second type of database, which is no SEQUL database. In this database, we do not use stable structure to save the data. It's save our data in a document based, and we have to popular NoSQL database, which is MongoDB and radius. These databases do not use SQL query to store data. As you know, in this tutorial, we are going to learn about mySQL database. Now, let's learn about the advantages of MCIGuL database. MySQL database is a cross platform RDBMS. It's mean we can use it most of the operating systems like Windows, Linux, Mac. And the next most advantage is, we can use it multiple programming languages like PHP, js, Python, C shar, et cetera. The third and most advantage is, it is open source software. It's mean, we don't need to pay anything for that. Next advantage is mysql is a RDS database. That's why we can relate multiple table in a single line of command. And the fifth advantage is MySQL database server is very fast, reliable, scalable, and easy to use. You already know the benefits they using of MCQL database. Now, let's find out what most popular websites that can use MCQL to store their data. First is Facebook. Second is Twitter, then Google, Wikipedia, and YouTube. And this website do not need any introduction. Also, most popular CMS system using MySQL, like What Press, Zuma, DRPal, et cetera. CMS stands for Content Management System. This is the introduction video of MySQL T. I hope now you understand why we need to learn MySQL. Thanks for watching this video, stay tuned for our next tutorial. 3. MySQL Installation & Workbench Tutorial : Hello, friends. Nice to see you back. It is our second video. In this video, we are going to see how we install MySQL and how we can run SQL command key on it. Let's see how we can install MySQL. Your first step is you need to download one of these three servers on your computer, Zam WM, or MAM. In this video, we are working with ZAP application. Let's see how we can download or install in our machine. Just search Download Zam in your browser. Then click on the official link. This link will redirect you to download page, and here you can see the download options for three type of OS. If you are Windows user, then go for Windows and if you are Linux user, then go for Linux. It is also available in Magersion. I am Windows user, so I'm going to download Windows version. I'm going to click this link and you can see our download has started. My Internet connection is not very fast, so I'm going to pause this video. If we install Zev application, then my SQL will be automatically installed with it. And it also install another web application named PHP my admin. Not only that, we are going to use third party application MySQuL workbench. This is MSEQLs Corporation's own graphical tool. Here, too, we can run the command of MSQuL and we use this tool in this course. Finally, our download is complete. Let's install the Zap application in this computer. It takes little time to install. First, I'm going to run this application with administration. You can see an alert on your screen. Just click Okay. Then you can see the setup wizard. Just click on next. First of all, check if Mosqule and Apache are selected or not. I selected, then click on next, then you need to choose a folder. Why you want to install them and I am want to go with Default Path. Then click Next, select your language, and next now your installation process will start. Your setup is ready for install. Just click on next and wait for some time. Don't worry. I take little time. After complete the installation process, you can see a alert on your screen. You need to allow your Apache server communicate with private or public network. Click on Allow XIs, and then just click on finish. And also I'm going to run the ZEM server. You can see the ZAM control panel on your screen. First, you need to start Apache and Misqull. I'm going to click on Start button. You have to wait a little until its color is green. Once again, it asks for network permission. Just click on Allow xs. Now your MSQuel is ready. Then I back to Browser. For now, I don't need this tab, so I'm going to close it and just type Local host on your URL bar, and it is redirect to Dashboard. As we say, we will use another application to run MySQL. Just open a new tab and type mysqul.com. Before I download this application, let me introduce what is PHP my admin. Just back to the local host and click on PHP my admin. You can see here that the control panel of our PHP Madmin is open. It's basically a web based tool, and here you can practice your MSQel. You can see this on the left side. These are our database. If we click on any of these we can see the table inside it. If you want to create a new database, then just click on database. Then a from will open in front of you create database, and you can put here whatever name you want. I'm going to create a new database name Taste two and then just click on Create. You can see on your lip a new database has been created, name Taste two, but we have not made any tables yet. There is another way to create a database. For this, you need to click on Home icon. Then you need to click on SQL Link. Here you can create a database using the SQL command. Just type, create database, and then type your database name. Our database name is Test three and use semicolon to end this line. Remember, SQL commands are not case sensitive, so you can use small letter or capital letter. It's totally on you and then just click on Go button. It's execute the EQL command and create a new database, Test three. This is the first way where we can practice McQuL. If you do not want to use any third party application, then it would be the great option for you. But in this tutorial series, we are going to work with the third party application, which is MySQL Workbench. Let me show you how we can install it. But you need to remember one thing. Before you start MySQL workbench application, you need to start your local host server. In our case, Zam. Let's back to our second tab. Then click on second link, download. Then scroll your page a little bit. Then you can see link, MSQuL community downloads. Then a page will open in front of you. Here you can see all the software of MSQuL. But here we need to install MySQL W Bench only. If I click on this link, this software works just like PHP Madmin, but its user interface is very easy to operate. Then just click on the do Down link and select your operating system. I am Windows user, so I select Windows and then just click the Download button. Then you ask for login or sign up. But you don't need to sign up for download this application. Just click on no thanks, start my download, and it will start your download. Then just open the Download folder and just double click on the application then you can see a setup wizard. Then click on next. If you want to change your file directory, you can, but I would like to go with default option. Then I click Next button once again. Then you can see to option, complete or custom, I would like to go with complete. Then click Next. Then your installation process will start. It's take little time. Finally, our installation process is complete. First, you need to create a connection. Creating a new connection you need to click this plus icon, and this will open a new window, something like this. Set up new connection. First, you need to create a connection name, and our connection name is demo. Yes, you can choose your own connection name. Then come connection method. You don't need to change it. Just keep it as it is. Similarly, you don't need to change your host name and your prod name also. Just keep it as it is and you don't need to change your username. Then if you want, you can set your password. By default, Zem server came with no password. If you want, you can use password, and I don't want to use any password. I'm going to leave it blank and then just leap the default schema and press Okay. Then you can see it's create a new correction name Demo, and then just Double click on your Cnection. If this is your first time, you will receive a warning message. Just right click on, don't show this message again, and then press continue anyway. Then an editor will open in front of you. This is your SQL editor. Here you can practice your mySQL commands. And if you want to see what your old databases are, just click on Schema. Now you can see my previous databases, and this is your SQL editor. Here you can type your SQL command. Just type your SQL command and click on Thunder icon, and it will run your command. If you want to delete your old database, then just select your database, right click on it, and just click on drop Scrima. Then it ask for review Qull and drop now. You can see it delete our Test three database. Similarly, we can delete Test two database. Let's create a new database name student using SQL command. So I'm going to type, create database, student, and use semicolon to end this line. Then I'm going to click this Thunder icon. If the green sign came here, then you think your code has worked properly, and if the color is red, then think that your command was not successful. As you know, our database is created, but we can't still see here because we just refresh our schema. Then you can see your new database name. If I drop down this student database, you can see some options like tables, views, functions, et cetera. From the next video, we will start learning equal command. Thanks for watching this video. 4. MySQL Create Table Tutorial : Hello, guys, good to see you back. Once again, I'm back with another tutorial related mySQL, and in this tutorial, we are going to create a table. In our previous tutorial, we already learn how we can create database. And now we need to create a table using SQL command. This is the example of a table. As you can see in my table, we have to tell three column name, age and gender, and here we say, multiple record. Now we need to create this table in our database. To create a table, we need three things. First, they will name. We can create multiple table in a database, we need to take different name for those tables. Next, to create table, we need to define the columns name. How many columns we want, and we need to pass the columns name. According to this example, in this table, we have to tell three column name, g and Zinder the third most important thing that we need to create in table, which is column datatypes. In our column, we need to define the type of data. Suppose for name, we need to use string data type. For ge, here we need to use numeric data type, and for gender, again, we need to use string data type. So to creating a table in a database, these three things are very important. Table name column name, and column datatype. Now, let's talk about what SQL command we need to type to create tab in our database. This is the SEQL command that we need to type to create a table. In this command, first, you need to type create table. Then you need to provide the table name. And one thing you need to remember in your table name, you cannot provide any space. For that, you can use under Sco sine. Then inside the round recess, you need to take the column name and you can take multiple columns name at once. As I told you, with the column name, we need to use data type, column data type. For name, we need to use string, for numeric value, we need to use numeric datatype, et cetera. Then you need to use comma to take another column name. This is how you can take multiple column name. Now you know how we can create table using this command. But now let's talk about data type. How many data type we have in SQL. Now let's talk about data type in MsiGal. In MCiGuL there are mainly three type of data types. String, numeric, and data type. Now let's check out how many string data types we have. We have total 14 different string data types. Char, Berger, binary. This all data type is used to store string el, they are different by their length. Our first string datatype is a stands for character. This datatype can store between 022 55 characters. Not only that, also we can mention the limit of this datatype. Suppose if you want to say only 100 character. In that case, inside the round verses, you need to mention the signs. Our next datatype is gear. This data type can store data 0-65535 character. Suppose if you want to store a big paragraph, in that case, you need to use C data type, and if you want to save name, in that case, you need to use this data type. Next, we have binary and binary. Binary and where binary is similar to care and we care. But the difference is if you use this data type, it's going to use binary format to store the data. This means it's going to set the data as zero and one format. Then we have TNI text and it's come with fixed character. It can support up to 255 characters. If you use TNEtext then we do not mention this size. Our next datatype is text. In text, we set data in a bytes form. And these data type support up to 65,535 bytes data. Our next datatype is medium text. This datatype can support huge amount of characters. Basically, it supports 16 million plus characters. Next we have long text. This datatype can support 4 billion plus characters. Then we have tiny blob, all type of blob store data ina bytes format and it's pretty similar to text. Just the different is text sb data in a character format and blob Sb data in Bytes format. We have to tell four blob data type, Tiny blob blob, medium blob, and long blob, our last two remaining datatype is EM and sit. Basically, both the data type do the same job, but their size is different. Basically, here we pass set of values. Then in our column, we can choose any of it. If we use um, then we can set value up to 65,535 values. But if we use set, then we can set up to 64 values. Suppose here we pass only three colors, red, green, and blue, and to separate the value, we need to use comma. Then in the column, I want to print only one value from this list. In that case, we can use this type of data types, um and just the different is, um can store lot of values and set can store limited amount of values. These are all data types related stream. Now, let's talk about our next datatype, which is numeric datatype, and we have total 13 numeric datatype in McQual. Our first datatype is B. Our B datatype provide a range, number 1-64. Similarly, we have PN. It can store data between -128 to 127. Then we have in data type, and it is used to store good amount of data. Intisar and I both are same data type. It is short end of incisor. Then we have small in, medium int, and big E, and these are their value range. Then we have bool and Bolan. Both are same. BL is the short end of boolean. Both the datatype can save only one value at the same time. Zero either one. Zero stands for forms, one stands for two. And our last remaining datatype is used for decimal float double decimal DEC. Float and double, these two datatype is pretty similar. Also, we can mention the size and DC in decimal point. These are all datatypes related numeric values. Now let's talk about data time datatypes. We have to tell the five data type data types in my Segal, date, dt times time, time and year. If we use date data type, then we can store data up to 1002 9,999. We can save any date between these years. Just to use this format. First, we need to pass year, then we need to pass month and then we need to pass date. Our next datatype is date and time. If you want to save date and data time advance, in that case, you can use this data type. This is the format to store data in date and time here, month, day, hour met and sec in. Our third datatype is timestam. Timestam means the time that has passed. If you want to store any old data, then you can use this one or fourth dative is time. If you want to store only the time, in that case, you can use this one. Just you need to follow this format, our minute and sec in our last datatype is here, this data type support four disi format. Example, 901. These are all datatypes in mysq. Let's start the practical and see how we can use this data type to create theme. Hello, I am back to my MySQL warns application. But first, we need to start our Zam server. I already open my Zam control pan. First, I'm going to start Apache and then I'm going to start Muskill server. So first, we need to create our connection. I open my cursor to the connection name and double click on it. It's trying to connect with server. We learned about its interface in the previous video. First of all, you have to choose the database that you want to work with. Click on your database and click on set as default schema. Now you are able to create tables in this database. You can use another method to choose your database. Just you need to type a simple a SQL command. Just type use, then type your database name. Student semicolon to end this line and click on the flash icon to execute this code. Now your database is selected and ready to use. Now we can create table in this database. You can see in our schema section, there is no table in our database. Now we are going to create a table. Let's type our command to create a table. Create Table. SQL is not case sensitive. We can use both type of litter, capital or small and our table name is personal. Then we start our parenthesis and obviously we need to use semicolon to end this line. Inside this round races, we need to take our columns name. Our first column is ID and its datatype is indser, so I type INT in. I use comma and I'm going to type our second column name, which is name and its datatype is ircare. Not only that, we can fix our character limit inside the round basis. For name, I would like to give 50 character and our next column is birth date and our datatype is date and our next column is phone number, and it's data die is arcare I would like to give 14 character for our phone number, and our last column is Zender which is also wearcaerFGender, I would like to give one character. M for male, F for female. Our command is complete. We take five different columns for our table and our table name is personal. Now I'm going to click the thunder icon to execute my code. You can see in my action output section, it execute our code perfectly. Then I back to schema section. And click on refresh icon and you can see a drop down sign in table section. If I drop down it, you can see our table, which was parsonal. If I select our table, you can see the columns name and their tie. In the same way, if you click on the table link, you can see some options, columns, index, foreign key. If I click this columns link, you can see our columns name. We can create multiple tables in our database. Let's create another table name products. Our first column is Product ID. I'm going to type PID and its Dadative is ItserO second column is product name and its datative is irkiir we don't need the data burth column. For company, I'm going to type P company means product company for product price, I'm going to type price and its data type is indser. Let's execute the code. You can see in my output section, our commands run perfectly. If I depress our schema, you can see now we have two tables in our database. First one is personal and second one is product. Unfortunately, there is no data in our table. In the next video, I will show you how can we insert data in our table? If you want to see the table in graphical format, just hover on your table and now you can see three icons. Just click on Table icon. Now you will see a table structure. When I click the table icon, it's run this SQL command. Select start from student dot product. There is no information in our table, so you can't see anything. In the similar way, you can see another table. Just mouse over the tablaM and click Table icon. And you can see our columns name, ID names, but they for gender. I hope you can understand how we can create tables using a SQuL command. In the next video, we are going to insert data in our table using a Squilommand. Thanks for watching this video. 5. MySQL Insert Tutorial : Hello, guys. Good to see you back. Once again, I'm back with a new tutorial related mySQL, and in this tutorial, we are going to learn how we can insert data in our table. To insert data in our table, we are going to use insert SQL command. In our previous tutorial, we learn how we can create table using SQL command. But in this tutorial, we are going to learn how we can insert data in this table, something like that. Let me show you the insert command. This is the insert command. First, we need to type insert into. Then we need to provide the table name. According to our previous tutorial, it may be personal otherwise product. Then insert the round presses, we need to provide the columns name. In which column we want to set the data and we need to use comma between our columns name and then we need to provide another keyword called values. Then inside the rounder says, we need to provide the values, the actual values, name, edge, Zender, et cetera. But here you need to remember one thing. You need to arrange the values the same way as you arrange the columns name. Suppose in your first column, you pass he and then Zender. Then your values should follow the same path. First, you need to provide the edge value, then you need to provide the Zender value. It is up to you how you want to short the columns name. So without wasting your time, let's start the practical, and then I'm going to show you how we can insert data in our tables. Before you start MySQL WWench application, you need to start Zamserver and a SQL and Apache. Then you need to go to the MySQL WWench application. And here you can see your connection name Devo, then Double lik on it. After Dale lick on it, as you can see, He created database name study. In this database, we have total two tables, personal and product. In this tutorial, we are going to set data in our personal table. If I open the personal table, as you can see, we have total five columns, ID, name, birth date, phone, and Zinder. At first, I'm going to remove the old codes. This is our previous query. We're going to start with insert query. Soon types, insert into our table name. Our table name is personal. Then inside the round ress we need to pass the columns name. In which column we want to set the data. Our first column name is ID. Then we need to use. Then our second column name is name. Our third column name is birth date, under school date. Our fourth column name is form and our fifth column name is ender. These are all color names of our table. Then we need to use value keyword values. Values inside the round dresses we need to pass the values. And also we need to use semigron outside the round resses to complete the query. At first, inside the round dresses, we need to pass the ID number, which is one. After comma, then we need to pass student name. For that, we need to use double codes for name, here we use ga data type. Inside the tool codes, I'm going to type add one Mij. Our third data div is date. Also we need to take inverted. Also you need to use dual codes. Inside the dual codes, I'm going to type the date, his birth date, 1990 605 month and date is 03. Then after Coma, I'm going to take the phone number. For phone number we use Verge data type. Again, I'm going to pass inside the double codes and the phone number is 000-99-9888 is a Dai phone number. Next, I'm going to take Zender value. Inside the double codes, I'm going to take for male. Now, let's execute the code and see is it work properly or not. For that, we need to praise this thunder icon. After press this thunder icon, as you can see, it execute our School query successfully. Now I want to see the personal table data. For that, we need to ho on this table name and you can see a icon, we need to click this last icon. If I click this last icon, as you can see, it return the table. Basically, it's run this query. Select star from our database name and our table name ParsNw as you can see, it return the table data. ID one name AdvantMing date of birth, phone number, and Zinder. Basically, in this tutorial, we learn how we can insert data in our table. In the next tutorial, we are going to learn how we can insert multiple data in our table. This is it for this tutorial. Thanks for watching this video. Stay tuned for our next tutorial. 6. MySQL INSERT Multiple Rows Tutorial : Hello, friends. Nice to see you back. It is our fifth video in our tutorial series. In this video, you are going to learn. How can we insert multiple row using SQL command? In the previous video, I explain to you how can we insert multiple data in our table using SQL command. But the problem is, we need to add data one by one. Suppose we need to insert five row in our table. In that case, we need to run this SeQuL command for five time. First, we need to execute for Advan, then we need to execute for Smith and similarly, we need to execute for Sophia and Emma. So we have to run the insert command. Every time when you want to add a row? It's a very lengthy process, but we can cover it in a single step. Using this insert command, we can add multiple row at once. Let's see the syntax, how it works. You can see on your screen, first, we need to use insert command key, insert into our Tavin name, and then we need to define the columns name, and we need to use another keyword, values. Then you need to take a round recess for the information you want to add in your columns, and we need to follow the same order, and then you need to use comma. And similarly, you can take another row of data and you can add a new record. In this process, you can take multiples row data, and at last, you need to use semicolon to end this command. Let's see the practical, how can we use insert command to add multiple robs data? So I am back to my MySQL War Brench application, and I also open ZEM Control Panel. Before I run the MSc War Brench application, we need to start Zem Lukavos server. First, I'm going to start Apache server, and then I'm going to start MisquL then I am waiting for green signal. Now it's ready for connection. Let's connect it. I'm going to hover on my connection name, Demo and DowlkO it. And you can see on your screen, we can connect our connection perfectly. In our schema section, you can see our database names. And also you can see my table them, personal and product. Let's see what we have in our personal table. I'm going to Howard on table icon and Tk on. And you can see there is one row data, which we insert in our previous video, name Advanan date of birth 1995, and gender is Min. Now I'm going to add three recode advance in this table using insert command. I am back to my query one section. First, I'm going to type insert Keyword, insert into our twnmPersonal, and then I'm going to use round braces. Inside the round braces, we need to type our columns name. Our first column is ID. Our second column is name and our third column is birth underscored date, our fourth column is phone and our fifth column is Zender then I type another keyword values. In the next line, I'm going to take another round recess and we will insert the data the way we took the column name. First, I'm going to insert our ID value two, then name Smith date of birth, 1991, October 6. And for phone number, I'm going to type some random numbers, and for gender, I'm going to type, we need to use coma? Because we are going to add another row. So I duplicate this line, and now I'm going to change the values. ID three, name Nna date of birth 1997, October 9. For phone number, once again, I'm going to type some random number, and for gender, I'm going to type I. Once again, I duplicate our previous line and I'm going to replace value. For ID, I type four. For name, I'm going to type RV and birth year is 1990. But for phone number, I'm going to use the similar phone number. I'm going to explain you in the next video why I choose similar phone number. For gender, I type. At the end, we need to use semicolon to end this command. Our command is complete. Now it's ready for execute. Let's execute the command. I'm going to hit Thunder icon. You can see in my action output section, it's written green signal, its main, it's execute perfectly. If I back to my personal table and run this command once again, you can see the new data. You see how we insert a lot of data together. Thanks for watching this video. See you on the next Tatal. 7. MySQL Constraints Tutorial : Hello friends. Nice to see you back. This is our sixth video related to MiSequL. In this video, we are going to learn what is constraints in MiSequL and how we use it. You can see we have a list of mySQL constraints and here are the green constraints that we will learn in this video. There are two constraints late name foreign key and primary key that we will cover in our upcoming videos. Now the question is, what is constraints? It's mains restrictions on the columns of our data table. This determines what kind of data we will insert into our data table. We already know how to make a table, but we can have a problem if we make the table that way. Let me show you the problem. You can see a table on your screen. I do not use any constraint in our table. You can see in our table that the ID was not entered correctly for Sophia. Similarly, Emma forgot to insert her gender in gender column because we do not use any restrictions. You can see Adwan is 16-year-old. But the minimum requirement for the college is 18 years. In that case, he will not able to admission in college and we know each student's phone number is different. Phone number should be unique. If you notice, Sophia and Advan have the same phone number. At the end, suppose our college is in Delhi, most of these students will came from Delhi. Every time you need to insert Delli as city. So you want to do something like that, if we leave the name of the city blank, then it automatically insert D as city. For all the problem, we have different constraints. So for ID and ender, we can use not now constraints. Similarly, we need to add restriction for H column. For restriction, we have another constraint, named chick. First, we need to type check Q word, then inside the round resist, we need to set our condition. In our case, age getter than equal to 18. This means if student age is below than 18, he's not able to join this college. Similarly, we want to use unique phone number for every student. In that case, we can use unique constraint. Similarly, we need to use unique constraint for our ID column. For city, we can use default. We can set our default value. If we skip this column, it automatically insert our default value and also we can insert different city name. Let's see how we can use these constraints in real life. We basically use it while we create the table. To create a new table, we need to type create table, then our table name. Then inside the round reress when you go to make a column for ID, first, we need to declare data type, and then we use not now constraint because we don't want to leave it blank. Then we use our second constraint unique. If we set unique, then we do not duplicate our constraint value and for name or datatype is wearcare. Similarly, I don't want to leave it blank, so I use not null constraints for age, I'm going to use multiple constraints or datatype is int. First, I use not null constraints, and then I use check constraints. Inside the round brass, we can set our condition. For gender, we use WCA data type, and I don't want to leave it blank. And for phone number, our databe is re care and I use two constraints, not null and Quniq. We do not copy student phone number in our database. The same phone number cannot be used repeatedly. For our last column city, we use two constraints, not null and default. I student leave their city name, it will automatically insert the as city. Then I close our table using round bases and semicolon. We use it this way. Let's see the practical how we can use it in our queries. So finally, I am back to my Misquel War Bench application, and I also open my Zam control panel. First, I'm going to start my Apache server, and then I'm going to start Misquel and just wait for green signal. Now our Local vos server is ready. Let's create the connection. First, I'm going to hover on my connection name and then double click on it. You can see our previous code in our query section. In the previous video, we insert multiple row at once. So let's add another student in this directory. So first, I'm going to remove these lines. For our next student, our ID is five and I do not change my student name, and I forgot to insert ender value. Let's run this command. I'm going to press this Thunder icon and you can see the green signal in our action output section. Let's back to the table. If I run this query, you can see in my table, our ender is blank, and I have used the phone number twice because we do not use any restriction in our table. To resolve this problem, I'm going to create another new table and I'm going to delete this one. To delete table, just hold on your Taviname and click on it. Now you can see an option, drop table. Now you can see a confirm box on your screen and just click on drop now. I back to my query section and I'm going to create the same table once again. But this time I'm going to use constraints. To create a new table, we need to type create table and our table name is personal. Then inside the round verses, our first columnim is ID and its datatype is indser and I don't want to leave it blank. I use our first constraint, not null and I do not want to repeat this ID in our table. I use Kunqu constraint. And our next column name is name, and it is datatype is ker I set limit for 50 characters, and I don't want to leave this column blank. I use not nun constraint for age or datatype is int, and I don't want to leave it blank, so I use not noun constrain and I want to set limit for student age. I use check constraint. If age is below than 18, then he is not able to join this college. For gender, our datatype is Werker. And I use not null constraint and for phone number or datata is we Care. I use two constraints, not null and unique for city or data Dave is we Care, and I don't want to leave it blank, so I use not null. If any student forgot to provide his city, they need to use default constraints and our default city is Billy so our query is complete. Let's run the query. I'm going to click on the flash icon. You can see the green signal, our query perfectly. If I refresh our schema section, you can see our Twinem personal. If I click on our Tevin LM, you can see the columns name and its datatype below on your screen. If I open our personal table and open our columns, also you can see the columns name. ID, name, age, Zender phone city. Let's see the table. I over on my Tevilm and click on Tewicon and I want to insert a student in our table. I'm going to type the query. Insert into our Tewme is personal. Then inside the round basis, we need to provide our columns name, ID, name, comma age, ZenderPhone and city. Then we need to type another keyword, values. Then inside the round basis, our student ID is one. Our student name is Edwan and he's 19-years-old. He's rendered his male. So I type capital AM. And for phone number, I'm going to type some random numbers and he is from Di. I use semicolon to end this line, our query is complete. Let's run the query and you can see the green signal in our output section. Let's see the table. Just ho out your curds on your table Name and click on Tavil icon and you can see the first data in our table. Let's add another student in this table. This time, you forgot to enter Zender value. So I remove Zender from our columns name. And also, I'm going to remove this one from our value section. And also, you forgot to change student phone number, and our student ID is two, and our student is 18-year-old, our student name is Anna. Let's try to insert this value in our table. So I'm going to praise flash icon. And you can see in my output section, there is red signal. We set our phone number Kuni and it shows the error for duplicate entry. First, we need to resolve our phone number problem. I'm going to change the phone number. And once again, I'm going to run this query. This time, you can see a warning sign. It's worked perfectly, but it shows some warning. It warns Zender doesn't have default values. If I to my table and show you my table, you can see our Zender section is empty. Empty means null value. Let's to our query section. Once again, I'm going to use our Zender column. He Zinder. In our value, I'm going to provide for ZinderT time our student name is Susmita and her ID is three, but she forgot to provide her city name. This time, I'm going to remove city from our column section. Also, I remove city name from our value section. But if I run this query, it's not going to work. Let's run the query. Because once again, we do not change student phone number. Student ID and student phone number should be Qiu. We do not dblgate their virus. So I'm going to change the phone number, and then I'm going to run the query. And this time you can see it worked perfectly. There is no warning. But if you notice, we do not provide citName because we use default constraints for this column. Let me show you our table. So I'm back to my table and run this code. You can see by default, the CityName is delay. Let's add another student and our student ID is four and student name is Rahul and he is just 16-year-old and his gender is. He male, so I type capital M. I also change student phone number. If I try to insert this student in our database, it's not work or query failed it because we set limit for student age. If student below than 18, he is not able to join this college. I'm going to change his age. Now he is 20 and also I want to use City Column. Phone city. This time, I want to enter different city name and Raul is from Kokata. If I done this query, you can see, now it's worked perfectly. If I show you my table, you can see we can insert different city name. If city is not available, then our default constraints put D as his city name. I hope now you understand how we can use constraints in our columns. It is very important to use. I hope you understand the us case of unique, not null, default and hake. Thanks for watching this video. See you on the next tto. 8. MySQL SELECT With WHERE Clause Tutorial : Hello, friends. Nice to see you back. In this video, we are going to learn how we can use select command in our queries. In our previous video, we learn how we can create tables in our database. And we also learn how we can insert data in our database. Now I want to see the data from my table. We can do this by using the Silt command. Let me show you the syntax. You can see first we need to type Salt command. After SLT command, you need to type your columns name. In our case, column, column two. Which columns you want to see data is up to you. Then you need to use another keyword from, and you need to type your table name. By doing this, you will get all your data. You can specify your columns them to see the data. But if you want to see all the data at once, then you need to use star sign. Stars mean every column in your table. Let's start the practical and see how sed command work. You can see I open my MySQL Word Winch application and I also open my ZEM Control panel and I'm going to start Apache and MSEQuL and I wait for green signal. Now our Local voice server is ready for work, and I'm going to create my connection. I just hover on my connection name and Dule click on it. And you can see our old query in our editor, and you can see my table name in our schema section. If I want to show my table data, then I need to type select command. First, I'm going to remove my old queries, select star. Star means every column in our table from our table name, personal. Then I use semicolum to end this line, and I'm going to click Run button. You can see it provides all the data from our personal table. Basically we have four record in our table and you can see all the columns. If you want to see all the data from your table, select star from your table name. If you want to see particular columns from your table, yes, you can. Let me show you. I want to see student ID, their name and their gender. I type ID, name, ender. If I execute the query, you can see our student ID, student name and their gender. After Slett command, you need to type your columns name. Then you can see the information in that column in your table. Let me show you one thing. You can see our ID name and gender is in small letter, but I want to make it capital letter. In that case, we can use Ali's name for our columns name. After ID, I'm going to type Sqword as capital ID. Similarly, for name as I'm going to type capital student and for gender, I'm going to type capital gender. These are Ali's name. Using Allis name, you can take different name for your columns. Let's execute this scope. You can see it replaces our columns name with their Ai's name. If you want to show two words name for your column, I just trying to say student name as column name. Yes, you can. In that case, you need to use double codes. Inside the double codes, you can type two different orbs. Student name. If you want to show space in your column name, then you need to use inverted codes. Let's execute this code and you can see the result. Now our column name is student name. After this, let's see how we can retrieve conditional based data from our table. When we try to retrieve conditional waste data, then we need to use higher class. You can see a table on your screen. From this table, I want to retrieve some conditional waste data. I want to show all the male student from our table. As a result, it returned this table. You can see we have only two male student in our table. And now I want to run another condition and I want to show the student data who are more than 30-years-old. In that case, it's written this table. You can see we have only one student who is over 30-years-old. Whenever we try to retrieve conditional se data from our table, then we need to use select command with her gloss. Let me show you the syntax. First, we have a select statement and I want to show all of this column from my table, so I use star. Then I use a keyword, name from, and our tab llene. Then we don't need to close this statement. Just using a space and type higher class. Then we need to define our condition. Let's see what comparison operator we have to make the condition. You can see our first comparison operator is equal and our next comparison operator is also equal. It is set to compare null values and we have two, not equal sine. Next, you can see greater than sine. Then come greater than or equal sine, less than sine, less than or equal sine. Then we have in operator, not operator, between operator, is null operator, is not null operator, operator, and exist operator. We explore all of this operator in our upcoming videos. For now, I'm going to show you how we can use some con operators in this video. Right now, I'm showing you the practical. Once again, I'm back to my Moskile arwnch application. First, I'm going to show all of the column from our table. You can see all of the column from our table, and now I'm going to set my condition and I want to show all the male student from our table. First, I'm going to use higher class. Then you need to type our column names, Zener ender equal to M for male. If I execute the code, you can see it written all the male student data. Similarly, if you want to show all the female student, you can just replace A with A. Let's execute the code. You can see it print one row from our table. Now I decide I want to show all the students who are below than 20-years-old. First, I'm going to type our column name age and then I'm going to use less than operator. For age, I'm going to type 20 because our He com data type is int datatype, so we don't need to use quotation. If I execute the code, you can see it print all the student name who are less than 20-year-old. If you want to show people under the age of 20 or equal to 20, then we need to use less than equal to operator. If I execute the code, you can see, now it's written students who are 20-years-old or below than 20-years-old. Similarly, we can use Gata then operator. P student age greater than 18. If I execute this code, you can see it return to students, Adan and Rahun. Let's talk about another operator, not equal to operator, now I want to return all the students who are not living in Kolkata city. I'm going to type City column, not equal to Kolkata. If I run this code, it return all the students who are not lived in Kolkata. Similarly, if I want to show all the student who lived in Kolkata, then just remove the not equal to sine and we need to use equal to sine. If I execute the code, you can see only round libbed Kolkata. As I showed you earlier, we have another way to use not equal to sine. If we use less than and get than Sun together, its main is not equal to. If I execute this code, you can see all the students who are not live in Kolkata, now I want to show only student ID and name who are not live in Kolkata. Select ID name. If I execute the code, you can see it print student ID and their name, who are not live in Kolkata. I hope now it's clear for you how we use higher clause with select command. Thanks for watching this video. See you on the next Tutorial 9. MySQL AND, OR, NOT Operators: Hello friends, welcome back. In this tutorial, we are going to learn why we need to use and or not operator. In our previous video, we learn how we use select command with higher clause, and we can use only one condition in our Wire clause. But if you want to use multiple operator at once, then you need to use and or not operator. You can see a table on your screen. From this table, I want to retrieve age 21-30, maybe 21 or greater than 21 between less than equal to 30. And if we retrieve, then we got this table. We got two students and their age 21-30. We need to say two condition with our her class, something like that. He age greater than equal to 21 and age less than equal to 30. If the both conditions are true, they need to return our table. You can see Sophia is 21. So her age met with our condition. If age is 21 or greater than 21, and if age is 30 or below than 30, then it show student details. You can see Smith is 32-year-old. I meet our first condition, but it doesn't meet our second condition, but our two condition must be correct. Let me show you another example. And now I retrieve those studen whose age is 20 and 32. In that case, it written Smith and Emma. In that case, we need to use operator. Also, we can use it with Ayer clause our first condition or keyword, then our second condition. Means one of the two condition must be correct. If any condition is true, it retrieve the table. This is our two operator, let me show you the syntax. First, we need to type select keyword and then we need to type columns name. Which column we want to retrieve. If you want to see all the columns, then you can use star sign. From, you need to type your table name. Then we need to type recross, and then we need to declare our first condition. We can use N Keyword then we declare another condition. We can declare multiple condition at once. If all the condition are true, then we can see the result. Even if one of the condition goes wrong, you will not able to see the result. If you're talking about operator, everything is same, we need to use or keyword between conditions. Let's back to the Meskill gwench application and see how it so. You can see I open our application and I also open Zam control panel and I already start Apache and Mesqule. Our local host server is ready for connection. I'm going to create the connection, just double click on it, and you can see our SQL editor, and you can see the old command from our previous tutorial. But first, I'm going to show you my current table. I'm going to click on tbcon you can see we have four student in our table. For now, I don't need these multiple panels, so I'm going to close it. First, I'm going to use end operator, and I want to retrieve students who are greater than equal to 19-years-old and less than equal to 20-years-old. I'm going to type, select, start from or tain Name, personal, and then I'm going to use Hier QR, our column name age greater than equal to 19 and age less than equal to 20. If both the conditions are true, show me the result. Let's run the code. And you can see it written two students Adan and Rahul. Advan is 19-years-old and Rahul is 20-years-old. Not only that, we can use different type of columns name with and operator. With our first condition, I want to retrieve those students who lived in Kokata City. I want to remove our second condition and type city equal to Kolkata. If I execute this code, you can see it written one result. Rahul, only Rahul fulfill our two condition at once. First, he is more than 19-years-old and he live in Kolkata. Not only that, we can use three condition at once, and now our city is deli. I also want to add another condition, Zender and Zenda equal to male. In this case, the three conditions must be correct. If I run this code, you can see we found one student who match our of the conditions. He is 19-year-old. He lives in D and also he is a male. Let's take a look at our second operator. First, I'm going to use two conditions and we already make our conditions. I'm going to remove City column and between our first and second condition, I'm going to use our operator. Now the condition is if student age is better than 19 or 19 and I student age is male, then return these students. If any condition is true from our column, it returned these two names. If I run this code, you can see it returned to result, Advin and Rahul. Now I'm going to use or operator using similar columns name. Hire age equal to 20 or age equal to 18. If one of the condition is true, then it return need to return our student names. If I run this code, it return three student, Anna, Susmita and Raul. Now I'm going to use these two operator at once in our query. I want to use operator between H column. I'm going to move this condition inside the round braces then I use operator and Zender equal to male. I combine this H column. I know if we use operator, then the two condition must be correct. And defined on this command, you can see it written one student name Raul because A is 20-years-old and his gender is male, so it fulfill both the condition. We use or operator inside the round braces, so our operator act like one operator. In this way, we can search complex data from our table. I'm going to remove this code. We, I'm going to use not Qword. City equal to Delhi or city equal to Kolkata. This command, going to re trip students who are not living in Delhi because we use not operator for Deli. With not operator, we also use operator and say, city equal to Kolkata. This query going to re trip all these students who lives in Kolkata, not in Delhi. And if I run this score, you can see it written on the one student, Raul because Raul lived in Kolkata. He not lived in Delhi. If I replace the city name and now I don't want to show the student who are not living Kolkata, so I naught city equal to Kolkata or city equal to deli. If I run this code, you can see it return three resin and all the students lives in Delhi. We use naught operator for our first condition and between our condition, we use or operator. But we can make our or condition a single condition using round races. Now it's act like one condition. Let's run the query. If I run this query, it going to return nothing because our three student lives in Delhi and one student lives in Kokata. If we had the name of another city, we would have seen that result. Let's run another condition for not operator. So I'm going to remove this code. Age greater than equal to 20, then show me those students who are not 20-years-old or greater than 20-years-old. If I do this command, you can see it written three students, Advan Anna, and Susmta because they are below than 20-years-old. I hope now it's clear for you why you use and and not operator. We can retrieve complex data with that. Thanks for watching this video. See you on the next tutorial. 10. MySQL In Operators : Hello, friends, welcome back. In this tutorial, we are going to learn in operator. Suppose we have a table in our database, and I want to retrieve those students who is 21-years-old and 24-years-old and it's written this table. You can see we have only two students who is 24-year-old and 21-year-old, Adwan and Sophia. Use our operator to get the same result in our previous video using higher class, first, we need to declare our condition, then we need to use or keyword. Once again, we need to declare condition, and it's a very lengthy process. To solve this kind of problem, we have another operator in operator. We can retrieve same result using in operator. Just we need to type where our column name age, then our in operator, and inside the undress, we need to type our query. Let me show you the syntax how we can use in operator in our SQL command. First, you need to type your select command, then you need to type your columns name. If you want to retrip all the students, you can use star sign. Then you need to use Prom keyword. After that, you need to declare your table name and then come hire class. After higher class, you need to mention the column name from where you want to retrieve data. Then you need to use in operator. Inside the down recess, you need to provide value which you want to retrieve from this column. You can type multiple values at once. Just you need to use comma between them and we can use our inoperator with not operator. When we use not operator with in operator, it returns all the columns name without this value. Let's start the practical how we can use in operator in our SQL query. So finally, I'm back to my MySQL Wbdge application, and I also open my Zem control panel. First, I'm going to start Apache, then I'm going to start MySQL. We need to wait for green signal. Now our Local Vos server is ready for connection. So first, I'm going to double click on my connection and I wait for connection. And you can see my SQL editor and my database name student. And inside my database, I create a table name personal. And now I'm going to show you all the record from my table. And you can see we have four record in our table, and we have six column in our table. First, I'm going to search according to student age, and I'm going to use in operator for that. So after from, I'm going to use my higher clause. So I'm going to remove it. Where age in age in inside the round recess, I'm going to source those students who are 19-year-old and 18-year-old. Inside the round ss, I'm going to type 18 and 19. If you notice I did some silly mistake. I don't mention my Tavilname in my query, so we need to mention devil name and our Tavin name is personal. Select start from personal were aging 18 and 19. Let's execute the code and see what happened. I'm going to hit Thunder icon. You can see we got three results from our table, add one, Anna and Susmta. Add one is 19-years-old. Anna is 18-years-old and Susmita also 18-years-old. I can put many condition here as I want. I'm going to add another condition 20 and if I done this code, you can see it's written another student named Dan who is 20-years-old. Not only that, we can use not apator with that. It's mean, show me those students who are not 18-years-old, 19-years-old and 20-years-old. If we run this code, it's going to return empty table. Let's run the code. You can see it's written an empty table because four of our students are between the age of 18 and 20. I'm going to remove two condition 19 and 20. And now I'm going to run this score, you can see it returned to student Edwan and Rahul. I returned those students who are not 18-years-old and now I want to use our in operator with City column I'm going to remove. And I'm going to type city and I want to show those students who are lived in Dali. Inside the own verses, first, we need to use Dole codes because our data type is re gear. Inside the double codes, we need to type our cityme D. If I execute this code, it written those students who are lived in Deli and we also use not operator with that. Now it's written those students who are not living deli. If I execute the code, you can see it's written one student, Rahul because Raul lived in Kolkata, not in Delhi. Using in operator, we can search in our ID. So I'm going to do not operator, and also I'm going to do City column. Then hire ID in, and I want to show multiple student data using ID. So I'm going to remove this city Name and type one and four. Here I plus two ID, one and four. If I run this code, you can see it's written to student from our table, Add one and Rahul. We use in operator, where we have to set multiple records. I hope now it's clear for you why and how we use in operator. Thanks for watching this video and stay tuned for our next Tutu do. 11. MySQL BETWEEN & NOT BETWEEN Operator Tutorial : Hello, friends. Welcome back. In this tutorial, we are going to learn between operator. Here you can see a student table and inside this table, we insert some dummy data, and now I want to find something in this record. I want to search students 18-21-years-old, then it's written this kind table. Only three students are 18-21-years-old, Sophia, Emma, and Olivia. Extract this record, we need to use between operator. You can see up to where we need to type our column name, g. Then we need to use between keyword and then I need to provide two values, 18 and 21 between these two value, we need to use and keyword. I set a range 18-21. You can see in my table, there is another column named DoV means date of birth. Now I want to extract students who were born 2001-2004, then it's return this table because Emma Olivia and James are born 2001-2004. If you want to extract data between two date range, we can use between keyword. You can see when two type Wire DOB 2001-2004, then show me the table of the students. Let's see how we can use between keyword with select command. We need to type select our column names. Which column you want to extract from your table. Then we need to use from keyword and we need to provide table name. We column name between value one and value two. If you want to extract all the column, then you can use star sine. We can use this between operator with not operator. Just we need to use not keyword before the between. If we use not keyword, then it's going to return all the value without the range between value one and value two. Let's start the practical and see how it work. You can see I open my Muscle brdge application and I already open my Zem Control panel. First, I'm going to start Apache, and then I'm going to start MisquilO local host server is ready for our connection. I'm going to create the connection, and I'm going to Dollik on my connection name. And you can see previous code in our quit section. For now, I don't need this tab, so I'm going to remove it. Let's see what is in our table. I'm going to click this icon. You can see we have four records in our table. From this table, I would like to find students who are between the age of 19 and 20. For this, I'm going to type select star from tblame and our tableame is personal. Then I'm going to use higher close. Then I need to type the column name, age, then we need to use between operator, between, and then we need to take two values. Our first value is 18 and our second value is 20. This query going to return those students, those between the age of 18 and 20. Let's execute the code. I'm going to click the thunder icon. And you can see it written for student because all these students are 18-20. If I use Notebor between the C N naught, now it will show those students who are not between the age of 18 and 20. If I execute the code, you can see it's written nothing and why it's written nothing, you know the reason. Let's run the code for student ID. I'm going to remove this column name and type ID. Also, I'm going to remove this not keyword. And I want to show those students who are 2-4. If I run this code, you can see it's written three student, two, three, four, we have three records. If I use not keyword with it and run this code, you can see it's written only one result because we have only four result in our table. If we do not show student 2-4, then only one student left and he is at one. It is not mandatory that operators always be number. We also use this between keyword with our string data type. I'm going to use it with name. The name, not between. I'm going to remove this not QR and between, I'm going to type A and is. It's a string. I need to move it inside the double codes. A, inside the double code. If I execute the code, you can see it's written at one Ana Rahul. It showed those two ends, those names start between A and S. We can use this between operator with numeric value and string value. Let me show you another example with date column. But you can see there is no column name date. So I'm going to create another table. The table will have a date column in it. Now you can see, I create another table named persons, and we already learn how to make tb. If I show you this twil you can see it have four result. You can see there are four row and three columns. We have saved the databth of the student along with the name of the student. I'm going to use between operator with that. I want to see those students who are born 1996-1999. First, we need to type select star from our table name person. Then we need to use reclose our column birth date, date of birth between the quotation, I'm going to type our first date, 1996 month January date 01, and now I'm going to use ACWord the age between 1998, December month and last December. So it's going to return those students who are born 1996-1998. If I execute the code, you can see it returned three student Advanced Myth and Sussma. This is our between operator. We use it to find value between two numbers. Thanks for watching this video. 12. MySQL LIKE Operator & Wildcards : Nice to see you guys. In this tutorial, we are going to learn operator in SQL. Here you can see a dummy table on your screen. Now I want to extract those students whose names start with is. You can see then it's written this table. It's written Smith and Sophia because their names start with is. If you want to extract result like this, then you need to use operator. Why name like inside the double quotes is module sign. It's mean our word, start with is and we don't know how many characters will come after that. It may be zero, maybe one or maybe multiple characters. You can see we use modular sign. Similarly, we have more characters and we called it Wildcard character. Basically, we use two characters, percentis and underscore. Percentse mean it represents zero character, one character or multiple character, and underscore represents a single character. I'm going to show you some examples of our pattern. If I type like a person, then it's going to find any value that start with A. Similarly, if I type percentage A, it's find any value that ends with A. Then if I type percentage OR percentage, it's find any value that have OR in any position. If I type underscore R percents, its main finds any value that have R in second position. If we type A underscore percents, it finds any values that start with A and are at last two characters in lay. But if I use A unco percentis, it finds any values that start with A and are at last three character in length. Just one thing, remember, ndscoe mean one character and percentis mean any character could be one character or multiple character. If we dive A percents O, find any values that start with A and end with O. And if we type scooY it's going to find those O in the second and Y in the third position. Let's see how we can use like operator with our syntax. First, we need to type silt Qard, then our columns name from table name. Then we need to use Wire class. Column after hire, we need to create a condition, column like operator, and then we can create any pattern as we want to search in our column. Also, we can use this operator with not operator. Something like that. We need to use not keyword before the keyword. It's going to return all the answers opposite of this button. Let's start the practical and try to understand how we can use operator with different patterns. Hey, I'm back to my Maskill War wrench application, and I also open Zam Control Panel. First, I'm going to start Apache server, and then I'm going to start MySQL and we need to wait for green signal. First, we need to create the connection. I'm going to hover over the connection name and Doble Glick on. Here our software is open and you can see my SQL editor, and we already create two tables, personal and person. If I show you my personality wheel, you can see we have four record in our table. If I show you another table person, you can see we also have four record. From my personality wheel, I want to find those tudens whose name start with A. Or that we need to type select star from personal means our Tevin name, then we need to use higher class and we need to provide column name and then I'm going to use operator, and I'm going to use quotation because our name column data is were here. I'm going to find those toudens whose names start with A, percentage sine. This means a should be in the beginning and what remains up that will not matter. If I execute this code, you can see we get two result. At one and Anna. Not only that, we can use multiple character at once. If I dive ADW, I want to see ADW out of all the student out there. If I execute this code, you can see we get only one record, add one. If I want to find such a record, it is not here, so I'm going to add another W I I execute this code, it's going to return blank. Now I'm going to use double percent design. Inside the double percent design, I'm going to type double N. This means if these two character comes in between the name, then show us that name. It doesn't matter if it stays in the starting or stays in the N or stays in the middle. If I execute this code, you can see it written one student, Anna, now I want to see those students whose name start with A N is. For these, first, I'm going to type A, percentis and then I'm going to use operator or name like inside the quotation is percentis. If I execute this code, you can see it's written three student, add one Anna and Susmta. We can use naught operator with so I'm going to remove this section and I type name not like our pattern. If I execute this code, you can see it's written all the students who are not matched with this partin. You can see it written SuspiaNRahul, but it don't written Edwan and Anna because their names start with A. Let me show you another operator, named binary. To execute this query, I'm going to open another table name persons, and I'm going to remove this not keyword. Wire, name like small. I want to show those students whose names starting with small is not capitals. In that case, we need to use binary keyword. Wire binary, name like smalls. If I execute this code, you can see it's written to student Smith and Susmta because their names start with smalls. Let me prove it with another way. You know, in our table, there is a student named Rahul and his name start with capital R, but now I'm going to use smaller. If I execute this code, you can see it's written blank because we use binari UWord it's going to return specify character type results. Once again, if I use, you can see it's written Susmita and Smith. But if I use capital R, now it's going to return Rahul because his name is start with CR. Let me show you another example. I'm going to remove binaric Word. And I'm going to use percentis sign before the character and I type N, it's mean I want to see those students whose name end with N. If I execute this code, you can see it's written one student name advance. Similarly, we can search two character at once. I'm going to type PA and execute the code and you can see it written Susmta. Now I'm going to show you another example. I'm going to type R percents. This line mean R will came beginning of the word and L will be in the last. There can be multiple characters between these two characters. If I execute this code, you can see it's written Rahul because his name start with R and end with. Now I'm going to show you how can we use undercodoperator? This example, I'm going to type undersco US percentage. It's mean the word starting with three character. U is our second character and S is our third character, but we don't know the first character. If I execute this code, you can see it written Sushmta now I'm going to use underscoeTT coco, H. I mean it start with three character and we don't know the first two character, and our third character should be H. It's going to return those students whose third character is H. If I execute this code, you can see it written Rahul. We can use underscore between two characters. Et me show you a underscore, I. If I execute this code, it's written Smith because our characters start with a and we don't know our second character and our third character is I. This pattern match with Smith. As a result, it's written Smith. I hope now it's clear for you how we can use operator with patterns. Thanks for watching this video. See you in the next tutorial. 13. MySQL Regular Expression : Hello, friends. Welcome back. In this video, we are going to learn what is regular expression in my skill. With that, we are going to learn how we can use regular expression with select Cavan. Here you can see a dammi student table and I want to search those students whose name end with WAN. With this, I want to search another pattern if there is a name and OPh written in that name, then show me the and if we try to find this kind of record from this table, then it's written these two results, Advan and Sofia because Ad one end with Wn and you can see the characters OPh in Sophia. For this kind searching, we use regular expression. You can see after where we need to provide columname and then we need to use a key name regular expression, RZEXP. It's a short name of regular expression. Then inside the codes, we need to type multiple expression and we call them patterns. As you can see, it's end with one. I type WAN and then I use dollar sign. It's mean the word end with one, and then I use Pi sign. It's mean I want to search another pattern using this command. Using regular expression, we can execute different type of searches in a single command. There are a lot of sign in regular expression. Let's try to understand it. First, you can see a upper arrow sign. It's main, if the string start with AB, then it's written that string and our next sign is dollar sign. Its main wire string end, if the string end with AV, then it's written that string. With that, we have squared res sign. I type any character inside the square resis, here you can see I type Rs. In that case, it's going to find every character differently. First, it's going to search for R, then going to search for and at last, it's going to search for S. Next, if we use upper arrow before the square resist and if I type any character inside the square resis, its mean is going to find those word B or start with A, otherwise E, otherwise R. Then we have another pattern where we can set range. Here we set a range A to Z. It's going to find all these word if the word contain any character between eight to Z, suppose you set range between A to F. Then it's going to return those results who have the character between A to F. If I use another character up the square sis it's going to create different type of pattern. Then it's going to search the character who end with AE, BE, CE, something like that. Last, you can see Pi sins. Here we can set our different patterns. You can see I type three name in our pattern Eva, Mia, and Nora. Between them, I use Pi sign. It's going to find all the result if the string contain this and we can use any of this sign to create this pattern. Don't worry about it. I'm going to show you the practical. Let's see this syntax how we can use regular expression with select command. First, we need to type select Keyword, and then we need to provide columns name. If you want to show all the columns, you can use star. Then we need to type from keyword and we need to provide table name. Then come Wire Ker, we need to provide the exact column name where I want to search, and then we can use our regular expression and we can set our own pattern. Without wasting your time, let's start the practical and see how we can use regular expression. You can see I already open MySQL Warwige application and I also open ZeM Control Panel. First, I'm going to start Apache and then I'm going to start MSQuL. Now our local host server is ready for connection. I'm going to hoard on my connection name and DoubleClick on it. This is our SQL editor. In our previous video, we work with person and personal table. If I show you the personal table, you can see we have only four records. And now I'm going to search record using regular expression. First, we need to select our table. Cyax star from personal. Then we need to use Hire closer then we need to select our column name, name, and we need to use regular expression keyword, now we need to provide exact pattern what I want to search in our table. I'm going to search I. If a name contains MI, then that name will show. Let's execute the code. So I'm going to click this Thunder icon. You can see it's written a result named Susmita because Am I available in this name. If I tie RA, then it's going to find those who went which have RA character. If I execute this code, you can see it's written Rahul because Rahul start with RA. We did the same thing with the help of operator in our previous video, but we need to use percentise sign for that. In our operator, we need to use two percentisSie to get the same result. But the regular expression is much more reliable than operator. Let's dive into our next example. In the name field, I want to check those name whose name I start with AD. So we need to use upper erosine for this. It's going to find those who start with 80. Let's run the code. If I execute the code, you can say it's written Adan because advance start with 80. If I die, S is going to find those who start with AU. Let's run the code. You can see it's written Susmta. If I use error operator beginning of the string, then it's going to check those words who start with this pattern. Similarly, we use dollar sign to check word from end. I'm going to type ANA, then I use Dollar sine. If I execute this code, you can see it's written Anna because Ana ends with NA now I'm going to show you how can we use P sine to search more than one pattern. So I'm going to type Rahul, then I use PiSine and I'm going to type another word AnnasN I use PiSIin and I'm going to type another word at one. Let's try to execute this code. If I execute this code, you can see, you can see it's written null. But the question is why it written null? Because we know these students names are available in our table. Then why it's written null? Because we use space. We need to remove this space. That's why it's written null. Regular expression count space also a character. If I execute this code, this time you can see it written or result at one NAN Rahul. If I remove AN and then execute the code, you can see it's once again written NA because AA match with this pattern NA and a end with NA. But if I use upper erosine is in it start with NA. If I execute this code, you can see it written only two student, Advance and Rahul and not start with AA. That's why it's written only two student, Advance and Rahul. But if I use dollar sign, end of our third pattern, and then execute this code, you can see it written Advance advance start with this pattern and also Advance end with this pattern. If I remove AD from Ad one and then execute the code, it's also written at one because also this time it's ended with WAN. This is our Pi sign. Using this sign, we can check multiple records at once. You can use this sign how much you want. There is no limit to use Pi sine. Now I'm going to show you another example where I'm going to use square sis. How many character we are going to provide inside our square basis, it is going to check it one by one. If I type Is, it's not going to search is, it's going to search Is. It's going to return all the name where INS is available. If I execute this code, you can see it's written only one student, US MITmia because INS is available in this world. But if I search I R and D, then I execute the code. You can see now it's written Advance Susmta and Rahul. Because D character available in I character available in Susmita and R Caracor available in ah. That's why it's written these students. We can do same thing using operator, but we need to use operator for this. Then it create our code very clamsy. That's why we prefer to use regular expression. Inside the square ses I'm going to type RN and outside the square ses I'm going to type A. Now the question is, what is the meaning of that? It's going to create combination. First, it's going to create RA combination, then it's going to create NA combination. I'm going to remove the unnecessary characters and I'm going to execute this code. You can see it written to student, Anna and Rahul. Because NA combination match with this pattern, also RA combination match with this pattern. We can use multiple character inside the square races. I'm going to enter T character. If I execute this code, you can see it's return Susma also because Susmia end with TA character. Not only that, we can use upper erosin with our square sis. I'm going to search those characters who start with R A and a. If I execute this code, it's written those students who start with RA and a. Similarly, we can use end operator with that. End of the square ss, we need to use taller sin. If I execute this code, it's written only to student Ana and Susmta because these two word end with A character. In our condition, A character is available. If I type DN inside the square resis, and I'm going to use A character beginning of the square resis, and if I execute this code, you can see it's written at one and ANA. Because it creates two combination, AD and AN, these two combination match with Aduan and NA. Not only that, we can use range inside the square ***. I'm going to type c2e. And I'm going to tell you how it work. It's going to find those characters who are between C to E. According to our pattern, it's going to select three character, C, D, and E. Our pattern start with A, and it's going to create three combination with A, AC, AD and AE. Let's execute this code. You can see it written at one because AD match with our pattern. We can use range operator. Regular expression is more efficient than operator. So we use regular expression. I hope now you know how we can search complex to complex pattern using regular expression. We can execute any complex search using regular expression. I hope now you understand what is regular expression. Thanks for watching this video. Stay tuned for our next tutorial. 14. MySQL ORDER BY & DISTINCT : Hello, friends. Nice to see you back. In this video, we are going to learn to new poverty from SQL command. W B and distinct. You can see a dummy student table and you can see the first column is name, which is not sorted by order, and I want to be sorted by order. As you can see, after A, student names started with. Smith and Sophia. I want the names, those who started with A, who came first, and then I want to see those students who started with B. Similarly, I want to order students by their name means ascending order, something like that. If I set ascending order for name column, according to the table, after adv it return Emma because E come first from. If you notice, Smith and Sophia, both of the names start with, but the second character of Smith, A come first, then O, it's going to print Smith before then Sophia. So here we have set our table in ascending order. Set ascending order or descending order, we need to use a special keyword, name derby. As you can see on your screen, first, you need to type der by keyword and then you need to provide the column name. In our case, name, and then you need to provide which order we want to set. We have two types of orders, ascending or descending. If you want to go with ascending, then you need to type ASC. But if you want to go with descending order, then you need to type DESC. If we set this column descending, then it will start working from the opposite direction. I want to say it's going to start from Z. Let's see how we can use water by command using Salt command. First, we need to type Salt command, then we need to provide columns name. If you want to see all the columns, you can type star then we need to use from keyword and we need to provide the table en and we need to use water by keyword and then we need to provide the columns name, which we want to water. We can order multiple columns in one table, we need to use comma between columns then and then we need to provide the order form it. Ascending otherwise descending. If you want to go with ascending, then you need to type ASC and for descending, you need to type DESC. Ascending order is ascending order is default order. If you use order by keyword, by default, it's run ascending order. Let's start the practical and see how it's so. You can see on your screen, I already opened my Zem Control Panel and I also open mySQL War Wrench application. First, I'm going to start Apache server, and then I'm going to start MySQL server. Now our server is ready for connection, so I'm going to double click on my connection name. Here you can see our SQL editor and you also can see our tables, and I'm going to show you the personal table, so I'm going to Dwigon it. You can see we have four student in our table, add one Anna Smitha Rahul, and here I want to order the column name. If you look closely, you will see that S come first, then come R. I'm going to order this column name and print student name alpha vertically. I'm going to type Clec star from ParslL then I'm going to use Water Viki. And then you need to provide column name, which is name. That's it. If I execute this code, you can see, and this time you can see character print first. Then the S character is being printed. First of all, it's going to print all the names who start with A, and then it's going to print those names who start with B. It's going to print name alpha vertically, means A to Z. If you want to show this data in a descending order, yes, we can. Just going to type. DESC. If I execute this code, you can see, now it's print all the name in a descending order. First it prints Susma, then you print Raul, then you print Anna. At last it print ad one. Here you can see, I do not use any condition. If you want to use a condition, yes, we can. Need to type Wir Qard. Here city equal to sat the quotation Di. If I execute this code, you can see those students who are from D and also it print their name in a descending order. First, it prints usmida, then some Anna at last add one. We can do ordering any up column here. I'm going to remove this line. And I'm going to order H column. I'm going to remove this column name and I'm going to type H. For now, I don't want to descending order, so I'm going to remove DSC. If I execute this code, you can see it order our H column, 18 18 1920. Not only that, we can order two columns at once. Name city. If I execute the code, you can see, first it's going to order name column. Then according to name column, it's going to order City column. So we can execute multiple column ordering at once. Let's talk about another method, listing. Here you can see a similar table, and now I want to extract all the city names from this table. If I type select City from Taven then it's written this table. If you notice, then you can see it prints Chicago City twice. This command written as it is city column. I want to consolidated report. I don't want to repeat it city name in my result, something like that. There is no duplicacy in this result. It prints Chicago once. If we want to extract result like this, in that case, we need to use distinct Keyword. Here you can see, select distinct columnim city from Devil. Let's say the proper syntax. First, you need to type set command, then you need to type distinct keyword and you need to provide column's name. Then you need to use From keyword and we need to provide Devil's name. Let's start the practical and see how we can use DistinYord. Once again, I back to my my skill Word wench application and you can see all these students cities. Most of the students are from Delhi. Only Raul came from Kolkata. First, I'm going to remove this code and then I'm going to type, select dist and then you need to provide column City from our table personal. Let's execute this code. If I execute this code, you can see it written Dili and cat. I do not repeat our cityme Dili now I want to extract all the age group from the personal table. I'm going to remove City and I'm going to type H. If I execute this code, you can see it's written three result, 1918 and 20. Not only that, also we can order this H column. Just want it to tie Water by and we need to provide column name H. If I execute this code, you can see it ordered our result. First, it prints 18, then it print 19, then it print 20. I shot our column in ascending order. We use distinct Qard to remove duplicacy and get the unique result. I hope it's clear for you why we use water B and distinct keyword in myscal command. Thanks for watching this video. Stay tuned for our next tutorial. 15. MySQL IS NULL & IS NOT NULL : Hello, friends. Nice to see you back. In this deterial we are going to learn is null operator and is not null operator. Here you can see a Damis student table, and by mistake, Sophia forgot to provide her He. So you can see g is blank. By default, it's stored in null value. If we do not use not null constant or H column, then we can leave this column blank. So now I want to extract those students who do not provide their age, something like that. In that case, we use Enulloperator. First, we need to type hire clause and then we need to provide the column name in our case age then we need to type our operator Inull. This query going to retrieve those students who forgot to provide Ege. Let's see how we can use it with Cletommand. Unit to type select, then we need to provide columns them from our tablin W columns them is null operator. This command going to re trip all the null values. If we want to retriep those value, which is not null, then similarly we can use is not null operator. Just unit to type is not null, it's going to retrip those results who don't have a null value to the particular column. Let's start the practical and try to understand how it's work with our SeQuL command. I already open my ASQLVwich application. With that, I also open ZM Control Panel. First, I'm going to start Apache and then I'm going to start my Squil. Now it's ready for connection, and I'm going to Dowlek on my connection name. Here you can see a table named person. If I double click on this icon, you can see we have five record in our table and we have four column on this table, ID, name, age, and city. If you notice, here you can see, there is no age value for Susmta and Kris we know that if there is no value, it pretends to be null. Now I want to see those value from the table who forgot to insert their H. For this, I'm going to type select star from our Tevin limb and our Tevin name is person. Then I'm going to type WrelasH and I want to check H column and then I'm going to use our isnull operator. This command going to return those students whose a is null. If I execute this code, you can see it's written to student show me the Krish. They forgot to provide their age. Similarly, we can use not null operator. If I use nonaloperator, it's going to return those students. Who provide their He value in H column. If I execute this code, you can see it's written at one, Amr and Rohith you also can see their H 24, 23 and 22. Just remember one thing, nulls mein empty, not nulls mein, not empty. We cannot use between not like operator to check null value. There is only solution to check null values. I null or is not null. I hope now it's clear for you. Thanks for watching this video. 16. LIMIT & OFFSET Tutorial: Hello, friends. Welcome back. In this tutorial, we are going to learn two new topics, limit and Osit. Here you can see a dummy student table. Suppose there are many records in this table, and I want to see all the record at once. For this, we need to type select star from student Mans our table name. If I execute this command, it's going to return all the student at once. But the problem is if we have 3,000 student in our table, but I don't want to see all the student at once. I want to see the result in a limited number and I want to see two student at once. Something like that means our first two result and one and Smith. To extract this result, we need to use limit clause. First, we need to type limit, then you need to set the number. We need to set the exact number, how many result I want to extract from the student table. Let's talk about the syntax. How we can write limit clause in our query. First, you need to type sealed command, and then you need to provide columns name. Otherwise, you can use star sign from table name. Then come hire clause. If you want, you can set any condition. Otherwise, you can skip this hire section. It's a optional. You can use it without condition, and then you need to use Limit clause, and then you need to provide the number, how many result you want to see. Let's start the practical and try to explore how we can use limit clause with a squall command. So I open my MSEQL winch application and I also open EM Control Panel. So first, we need to start Apache, and then we need to start MySQL and our server is ready for connection. I'm going to double glig on my connection name, Demo. And here you can see my SQL editor. And you can see we have one Tavl in our student database, named person. And if I show you the table, here you can see we have five students in our table, and this is our SEQL editor. And now I don't want to extract all the result at once. I want to see this result in a limited number. So I'm going to type Celec star from our Tevin limb person. And then you need to use limit clause, and I want to see to record at once, so I'm going to type two. If I execute this code, you can see it's written first two result at one and Susmita. Similarly, if I type four and then execute the code, it's written for student at once. Not only that, we can use higher condition with that. I'm going to type hire city equal to Pune and I want to see one result at the time. If I execute this code, you can see it's written one result, Rohit and he's from Pune. Now I want to ordering our name column. I want to change the limit. I'm going to type four. As I'm going to remove this where it was. I'm going to type der Y name. If I execute this code, you can see it written four result and also it print their name in ascending order, AdV Amer, Krish and Ruid. As you can see, we can use everything with our limit clause, wire clause, water by, null operator, et cetera. As you can see, from this travel, we can extract our first two student. But if I want to see the two student, then what? Now I want to see Sopia and Emma and I want to skip our first two student. To extract this result, we need to use another optional keyword with limit clause, which is opposite. Here you can see a black color number. This is our opposite. First, we need to provide the opposite and we need to provide it before the limit number. So Oset work as starting number means to start. Here you can see our osite is two means it start from Sofia because it's going to scape two student, Adwan and Smith. Let's see the syntax of opposite, how we can use it with Caltommand. Everything will remain the same. Just we need to use Oste number before the limit number. After the limit keyword, we need to provide Oste number. Then using comma, we need to provide our limit number. That's it. Let's start the practical and see how it works. So for now, I'm going to remove this line and I'm going to set limit and ositeTT. Our osite is two. It's mean, it's going to skip two student, number one and number two, and our limit is also two. It means going to return to student, number three and number four. If I execute this code, here you can see it's written to student Amor and Rohit and it's skip two student Advan and Susmta. Here I set my Opsete two. It's mean it's going to start from three, and I also set our limit two, it's going to return to student. As you can see, our ID end with four. If I set Oset limit four and then execute the code, you can see it's written only one student. This is our last student, Kris. If I set our Oset zero, then execute the code, it's going to return our first two student ID one and ID two. Whenever we need to start, always we need to provide zero. I hope now it's clear for you what is offset and limit in my Squill. Thanks for watching this video. 17. MySQL Count Sum Min Max Avg Tutorial: Hello, friends. Nice to see you back. In this tutorial, we're going to learn aggregate functions like count sum minimum max average, et cetera. Here you can see a employee table, and there is four column in this table, name, age, center and salary. Suppose now we want to know how many employees we have in our company. I just want to count the total employee. For this, we need to use a aggregate function which is count. After typing count inside the parenthesis, we need to provide column name. Which column we want to count? Suppose I provide name column in this parenthesis, then it's going to count all the name from this table, and now I want to see who get the highest salary among the employees. For this, we can use another function which is max. Is also aggregate function. It is going to return higher salary among the salary column. According to table it's going to return at one because his salary is $1,200. Similarly, if you want to see the minimum salary from this employee table, we have another function named Min. Similarly, we need to provide the columnum salary inside the parenthesis. If you want to see the total LATI we have to pay to our employees. We can use some function inside the parenthesis, we need to pass salary column and then it's going to return total salary from the salary table. If we want to see the average of the salary, we can use AVG function. AVG means average. Now let's see how we can use it with salt command. First, we need to type Salt command, and then we need to use the aggregate function. Here we use count function. Then inside the round press, we need to provide the exact column name. Then we need to type from and our tab name. If you want to set condition, you can. It's optional, not mandatory. In a similar way, we can use other functions like sum, minimum average, et cetera. We need to follow the same procedure. After Salt command, we need to type our function name. That's it, and everything remains same. Let's start the practical and see how it's work. Hey, I'm back to my McCleVarwnch application, and I already start my Zem server, and I'm going to create the connection. Here you can see a new table name employee. If I show you the table, you can see, I already insert some dammi data in this tip, and also we have four columns in this table, name, age, ender and salary. If you notice, you can see, we do not use ID column to store student unic value, and now I want to count all the student from the employee table. I'm going to type, select count. Inside the parenthesis, we need to provide the column name, which is name from employee. If I execute this code, you can see, it's written six. It's mean our total number of student is six. Not only that, we can replace our column name with star sine. It's going to count all the student from our table. If I execute this code, it's also written six. We know there are usually two enders in the world, but if I count Zender column, and then execute this code, it's written six because it's going to return total number of Zender. But I don't want this sum. I just want to see how many Zenders there are, so I want only unique values. For these, we can use distinct Q word. If I execute this code, you can see, now it's written to because our employee may be male or female. That's why it's written to. If you notice you can see it do not return a proper column name, so we can take Allis them for this. Want to tie as as means Aisname you can take any name, and I'm going to take total. And if I execute the code, you can see now our column name is total because we have taken A's name for this column now I'm going to use another function, Max. Here you can see in my twin, there is a new column name Celari. Everyone's alary is different here, but I want to see whose alary is the highest here. For this, I'm going to use a new function named Max. I'm going to replace count with max I'm going to provide our column name, which is salary, and I'm going to use the same allies name salary. If I execute this code, you can see it's written 1,200. Let's see who is the person is. He's at one. The rest of get paid less than him. In the same way, we can see the lowest paid salary, only to type Min. If I execute this code, you can see, it's written 540. Let's see who is the person. He is Jacob. Now the problem is every time we need to back to the table to see their name, I want to see students salary with their name. I just want to return name column with LAI. We can use for this, we need to type comma and we need to type our column name, which is name. Also I like to return employee H. I'm going to type He. If I run the square, here you can see it's written employee salary, their name and their age. Using Coma, you can take any column name. Now let's see how much the company has to pay the total salary of employees. I'm going to type sum as allies name, I'm going to take total and I don't need this column name and H. If I execute the score, you can see the total salary is $5,190. The company have to pay a total of $5,190, if I want to see the average salary of the employee, we need to use app function, just to replace some with AVG also I want to change the Ai's name average. If I execute the score, the average salary of every employee is $865. These are our five different aggregate function and we can use it for arithmetic calculations. I hope you like this video. Thanks for watching this video. 18. MySQL UPDATE: Hello, friends. Nice to see you back. In this tutorial, we are going to learn about Udt command. Here you can see a employee table, and I want to change value from this table. As you can see, here is an employee named Smith, and he get paid $800 as salary. But he got a promotion. That is why his salary has increased. I need to update this column, 800 to $900. We have another employee named Emily and her age is not 22. She's just 21-years-old. I mistakenly wrote his ages 22, so we need to update her age. Whenever we need to update existing data, then we need to use a special command, name update. Let's see this syntax, how we can use update command. First, you need to type update keyword and then you need to provide the table name. And then you need to type set keyword. We use set Keyword to set a value in a column, and then you need to provide the exit column name. Using equal to sign, you need to provide value. You can set multiple columns value at once. Just you need to use comma between them, and then you need to use where it was, and you need to set a condition. Remember, if you run update command without Wire clause, otherwise, this change will happen in all the table. That's why we need to use hire condition. So let's start the practical and try to explore how we can use update command. Hey, here you can see, I'm back to my mySQL Ognch application, and I already start mySQL and Apache server. I'm going to create the connection. Here you can see my secure editor, and also you can see our tables, employee in person. Here I'm going to practice our update command. We already use a table name employee. If I show you the table, you can see, we have six record in this table. Now I want to update a record. Here you can see a employee named Smith. I want to update her salary, 802 900. I'm going to use Update command. I'm going to remove these lines, and I'm going to type update. Then you need to provide the tavame and our tab Name is employee. Then we need to use set keyword, ACT. It's mean which column you want to set. I want to update salary column. I'm going to type salary. We don't need to use quotation because Lady's datatype is in. I'm going to remove this simply I'm going to type 900 and now I need to use condition. Otherwise, it's going to change all the records, and I want to change Salad only for Smith, not for everyone. I'm going to use condition, Herename equal to Smith. And I use semicolon to end this line. If I click on the employee table, you can see columns data type, name care, H int, ender Care, and salary Int. Let's execute the code and see what happened. I'm going to click on the Tanda icon. Here you can see it's run successfully. If I show you my table and reload this table, you can see we update the employee salary, 800 to 900. Let me show you another example. Now I'm going to show you how we can change multiple columns below at once. If I show you my table, here you can see a employee named Emily. I'm going to change her age and also I'm going to change her salary at once. So first, I'm going to select the employee name, Emily. While her name is Emily, I want to change her salary. Salary, and her salary is hundred dollar. I also going to update her age. I'm going to use coma and I'm going to type age. Here you can see our He column datatype is int datatype. I'm going to type equal to 25. Here you can see, I'm going to update two columns value at a time. Let's execute the code and see is it work or not. I'm going to press this thunder icon. You can see our don properly. If I show you my table and reload this tab, here you can see, now Emily age is 25. And her salary is $1,000. Not only that, we can update multiple rose value at once. Just we need to use in operator for this. Let me show you. Someone to remove this equal to sign, and I'm going to type I. Then I use parenthesis. Inside the parenthesis, we need to provide the employee names. Which employee I want to update? Someone to change add one. Using Coma, we need to provide another employee name, and our employee name is Smith. If I execute this code, it's going to update salary and age, both of the employees. Let's execute the code and see what happened. Here you can see, it's executes successfully. If I back to my table and reload this table, here you can see, advance is 25-years-old. Also Smith is 25-years-old. Advanced AD is 1,000 and Smith's Sari also 1,000. Here you can see how we can update multiple columns and multiple rows in one line of code. For this, we need to use inoperator then we can select multiple rows. At last, I'm going to show you another example. I'm going to remove this wire class. If I do not use Wire class, and I want to update only one column, only Salary column. I'm going to remove H column. If I execute this code, it's going to update all the employee salaries. Let's execute it. If I back to my employee table and repress this table, here you can see, now all the employee salaries are $1,000. Definitely, hire gloss is very important. You need to remember it. If you use update command, you need to use ire gloss. Otherwise, it's going to update all the record. I hope now it's clear rodeo how updates common work. Thanks for watching this video. 19. MySQL COMMIT & ROLLBACK : Hello, friends. Nice to see you back. In this tutorial, we are going to learn two new command, commit and roll back. Here you can see a dummy employee tab. Here you can see a employee name, Sophia. I want to change her salary. 750 $800. If you want to update a table data, we need to run Update command and we learn about it in our previous video. For this, we need to type update Tevin name, and then you need to use set command. Our column name and we need to provide the new value, at last, we need to use Fire clause to identify the exact value. After running update command, I observe it run by mistake. I want to update this data for another employee, so we need to revert this table. Now we have two solution. We can run the same update command to fix this problem, otherwise, we can run rollback command. Suppose you by mistake, run insert command and we need to resolve this problem, so we can run rollback command. Just simply, you need to type roll back and semicroon to end this line. It's going to revert our execution. If we use insert, update, and delete command, it's going to revert this command, and I want to tell you one thing, all the SQL command will be reverted before the rollback. Let me show you another example. Before this update command, I run another update command and similarly we update employee salary. But if I don't roll back command, it going to roll back all the update commands. But I want to roll back only for Sophia, and you can see her ID is three and I don't want to roll back Jacob SLariUdate. To solve this problem, we use another command, named Commit. Basically, Commit save all the previous line of code. Let me show you how it's work. First update command, execute for Jacub and then you run Commit command means it's going to save this record permanently. Then mistakenly, I update Sophia's Salary and now I want to roll back this command, so I need to execute the rollback command. That's it. Now the question is, does Rollback command work on every SQL command? No, it's not. It's just only work on three command. These three commands are insert, update, and delete, and we'll learn about insert and update command in our previous videos. And in our upcoming video, I'm going to teach you delete command. So let's start the practical and see how it work. 20. MySQL COMMIT & ROLLBACK PART II: Here you can see on your screen, I'm back to my Mascule arwnch application and I already start and I already start Apache and Mascuil on my Zem Control panel. Now I'm going to create the connection, and I'm going to hover on my connection name and Double Glick on. Here you can see a table name employee. If I show you the table, you can see there are six Damidata in this table. But if you work on Muscle mag application, then you need to go Edit section. Here you can see a option, name preference. You need to open this option and then you need to go SQL execution. Here you can see a option, new connection use autocommit mode. Just you need to uncheck it. If we do not uncheck it, it automatically run Commit command. Then we cannot roll back our execution. That's why we need to uncheck it, and then you need to click Okay, that's it. Then you need to restart your Moskill War Wrench application. Once again, I'm going to create the connection. I'm going to show you my table. Now I'm going to use a command, which is commit. I'm going to type commit. Commit mean all the changes that happened before it going to save our code. I use it because when I insert a new data or update a new data, I want to roll back to previous state here I'm going to use Update command. I'm going to type update, and I'm going to update Smith Salary thousand 21500, I'm going to run the same command which I run in my previous video. Update Table M employee, set, and then you need to provide the column name, which is salary equal to 1,500, and now I need to run Wire pass Wire name equal to Smith, then semicron to the line. But now I want to tell you one thing you need to click on the commit command. After click this command, you need to execute this icon. It's means it's going to execute only this command, not whole command. Just you need to click on the Ford button, not execution. So I'm going to click on it. It's main, it's going to commit our whole command, and then I'm going to click our next command, which is update. Similarly, I'm going to click on it. And similarly, I'm going to click on this icon. Let's click. Here you can see it update our data. Similarly, similarly, if I execute our first late command only, here you can see the table, you can see Smith's salary increased by $500, but I did it by mistake, and now I want to roll back our table. I'm going to use Rollback command. I'm going to tie rollback and semicolon to end the line. I'm going to click on the rollback command, and I'm going to execute only rollback command. I'm going to click on the fourth icon. That's it. Now our data is rollback. If I show you my table, here you can see Smith's salary has returned to its former place. You can see we made a change inside our database, but we roll back it again the helopRollb command. Let's show you another example. I'm going to select the code and duplicate these lines, and now I want to update employee age, Emily age, S age and her age is 29. Her name is Emily. First, I'm going to run Commit command and then I'm going to update Emily age. Here you can see it update Emily age 29, and also I'm going to change Smith's salary to 1,900. A I'm going to run this command. I'm going to execute it. Here we successfully run our two update commands. If I show you my table, here you can see Emily's age is 29 and Smith's Salary is 1,900. Now I want to roll back it. I select this command and execute this command. If I show you my table once again, how you can see, you can see it's back to its former position. Let's understand how rollback work. First, it's going to work on this command. It's going to revert it. Then it's work for this command and also it's going to revert it Rollback command is not able to revert commit command. The commands above the commit rolleck won't work on them. Let me show you the similar example once again. I'm going to move this code. Above the commit command. First, I'm going to update Emily H. I'm going to execute this command and then I'm going to run Commit command. Now I want to update Smith salary so I execute this command. If I show you my table, here you can see it update our data. Smith's salary is 1,900 and Emily age is 29. But if I roll back this command, it's going to revert only this update, but it not work on this command. Let's roll back it. Let's roll back. If I show you my table, once again, here you can see it roll back Smith's salary, Smith's salary back to his own state. But you can see our rollback command is not work on Emily's update. Rollback work upwards. If it get Comite command, then it's not going to execute all the code above the commit. Commit means permanent sap. You need to remember one thing. Rollbeck works on only three SEQL command, insert, update, and delete. I hope now it's clear for you what is omit and rollback. Thanks for watching this video. 21. MySQL DELETE : Hello friends. Nice to see you back. In this tutorial, we are going to learn about delete command. Here you can see a dummy employee table. From this table, one employee left the company, and he is Smith. I want to delete this employee from this table. Whenever we need to delete existing data from our table, we need to use delete command. Let's see the syntax, how we type delete command. Plus you need to type, delete. Then you need to type. After that, you need to provide the table name. From which table, you want to delete the data, and then you need to use hire condition. Specify the exact data. You can type delete command without hire condition, something like that. If I use delete command without Wire clause, it's going to delete all the data from the table. You should careful with this command. Remember, if you want to delete specified data, then you need to use hire condition. Let's start the practical and see how it's work. Finally, I'm back to my Miskill W Wrench application, and I also open Zam Control panel. First, I'm going to start Apache and then I'm going to start MSQL. Now a server is ready for connection. I'm going to over on my connection name, which is Demo and Dow week on it. Here you can see a table name employee. If I show you my table, you can see there are six employees on this table, and I'm going to close these tabs. I don't need these tabs, so I want to close it. I'm going to practice delete command in this table. First, I'm going to remove this line and I'm going to type delete, delete, then we need to type from and then we need to provide the table name and our tab name is employee, and now we need to use Wires to delete exact value. I'm going to type Wire Wire and I want to delete Smith from this table. I'm going to type Wire name equal to such the quotation, Smith. Then semigroon to end this line. If I execute this command, it's going to delete Smith records means row number two. But before I run this command, I'm going to use rollback. Otherwise, it's going to delete our record permanently and we learn about rollback in our previous video. A I'm going to type commit before the delete command. Commit. Now first, I'm going to execute commit command and then I'm going to execute our delete command. Let's execute it. It's execute perfectly. If I show you my table and reload this tbl, here you can see there is no employee name Smith. You can see how delete command work, and now I'm going to roll back this data. I'm going to execute rollback. If I refresh my table, here you can see Smith back to his old place. Let me show you another example of delete command. Now I want to delete all the male employee from this table. Let's do it. For this, just want to change the column name, Zender Wire Zender equal to M for male. That's it. Let's execute the command. If I show you my table and reload this table, here you can see it delete all the male employee from this table. Here you can see, we can delete multiple row at once using our condition. So let's back to the table. Now I want to delete those employee whose age more than 20-years-old according to T Sophia and Emily. I'm going to set a condition to delete those employee. Just I'm going to type Wire age greater than 20. That's it. If I execute this code, let's execute this code. Now if I show you my table, here you can see it delete those employees who are more than 20-years-old. Here you can see hire condition is very important for delete command. I'm going to roll back this table, and I'm going to reload this table once again. Now I'm going to show you if we do not use hire clause, then what's happened. I'm going to remove this wire clause, and now I'm going to run this delete command. And if I refresh my table, here you can see there is no data on this table because I do not use fire class and I need my data back, so I'm going to roll back it. Just execute the rollback command. You can see if you do not use Wire command, it's a very risky for our table. I hope you understand Dalit command. Thanks for watching this video. 22. MySQL PRIMARY KEY & FOREIGN KEY : Hello, friends. Welcome back. In this tutorial, we are going to learn two constant primary key and foreign key. Here, you can see a list of mySQL constant and we complete our food constant in our previous videos. In this video, we are going to cover primary key and foreign key. As I told you earlier, constant mean restriction. Restrictions to our tables column. Our primary key and foreign key also work as a restriction. First, let's try to understand what is primary key. Why do we set a primary key in a column, we need to store always unit data? There will be no duplication in it. Now you can think we can do the same job using unique constant, but there is a difference between unique key constant and primary key constant. We can store null value when we use unique constant, but we cannot store null value in primary key. Remember, we can set primary key only once in our table. We can use it just for one column, but we know we can use unique constant in multiple columns. We cannot use primary key like this. We can use it only for one column. Here you can see a student table, and you can see we have four columns in this table, ID, name, age and city. There can be many students with the same name and the same situation goes for Ag and city. Here you can see Smith is from London and also Emma is from London. We cannot use primary key constant or city column. And you can see a column name ID, here we can set primary key, because all of the three condition satisfied with this column. All the data in these columns are unique, and our second condition is we cannot store null value in this column because when we add a new student in this table, we always give a new ID. Let's see how we can set primary key in a new table. Let's see this syntax. First, we need to type creative command. Then you need to type the table name, and then I'm going to create a column name ID and its data type is int and I don't want to leave it blank, so I use not null constant and I use another constant auto increment. Smein whenever we add a new data in this table, we don't need to provide the ID every time. It automatically incriminate itself. First time when we set a value, it automatically take one as an ID. Then I'm going to take another column, which is name and its databas care. I set character limit for this column. We can use only 50 characters, and I don't want to leave this column blank, so I use NautNll then I'm going to create our third column, city. But here you can see, we use in data type for city. I'm going to explain you later why I use in datatype for city. Now I'm going to use primary key. First, we need to type this keyword, primary key. Then inside the parenthesis, we need to provide the column, which I want to set primary key, and I want to set ID as primary key. In this way, we can set primary key when we create new table. Now I'm going to show you the process if we already have a table and I want to set primary key in a column. For this, we need to type alter table keyword. Then we need to provide the table name and we need to use Add keyword and then just type primary key and inside the parenthesis, we need to provide the column name. He and I want to add primary key. Using altered Table keyword, we can modify our table, and I'm going to explain this keyword in our upcoming videos. Let's talk about foreign key. What is foreign key? The main reason to use it if I want to link two tables together. The most important part is, it contains only that value, which is the primary key value of another table. We do this because we want to join both the tables. We want to create link between them. Let's make it more simple. Here you can see a table named city. We just use two column for this table, CID for city ID and city name. We have another table named student and we have four column in this table, ID name age and city. In my city table, I want to make CID column primary key. Similarly, in my shooting table, I want to make ID column primary key. As you can see a column name city, here you can see city names, but we already created table name city. As you can see, I save all the city names here and now I don't want to type city name. I just want to provide City ID from City table, something like that. We can set this column foreign key. We use CID column Value in city column. CID is our primary key, and we use this value in other foreign key table. Advin was from Dili, his city ID is one. Similarly, Smith Ipam Londo. So his city ID is two. We can link our table using foreign key and primary key. That's why we use this Toki. If we set data like this, it creates our table more lighter, more faster. We don't need to provide repeated name every time, we need to pass their ID. That's it. Let's talk about foreign key syntax. We need to follow the same process. Up to set primary key. Next, I'm going to set foreign key. First, we need to type foreign key, and we need to type it capital later. And then you need to provide the column name where you want to set foreign key, and I want to set city column as a foreign key. That's why I use in data type for city because here I am going to save ID, not city name, and then we need to provide references. I type references. It's mean which other ts primary key is referring. I provide the tv Name city and inside the parenthesis, we need to provide the primary key column, which is CID. Then we need to close our command and just execute it. When we create new table and we want to set primary key and foreign key, we need to follow this process. But now I'm going to show you if we have existing table and I want to set foreign key in this table. For this, similarly, we need to use alter table keyword and then we need to provide tbl name. After that, we need to type at keyword and just type foreign key, and we need to provide the column name. As a reference, we need to provide the table name, which is city inside the parenthesis, we need to provide primary key column name from CD table. Let's start the practical and see how it's over. Hello, friends. Finally, I'm back to my Moskal War Wrench application, and I also open Zem Control Panel. First, I'm going to start Apache and then I'm going to start MySQL and we need to wait for green signal. Now our server is ready for connection, so I'm going to over my connection name Demo and DoV Cate. Here you can see a database name student and there is no table on this database. I drop all the tables from this database. First, I'm going to create a table name city. I'm going to type in a query section, create table, and our tb name is city. Then inside the parenthesis, our first column name is city ID, means CID, and it is datatype is in and I'm going to use not now constraint. Also I'm going to use Autogrement. That's it. This is our first column. Our next column name is city name, and it is Data is here. I'm going to set a limit. Limit four, we can only use 50 characters. And also, I'm going to use not now constant. That's it. Now I'm going to set a primary key to this table. I'm going to type primary key, insert the parenthesis and I want to primary CID column. I'm going to type CID. If I execute this command and reload this grima section, here you can see the city. If I show you my table, here you can see, it's totally empty. Now I want to add value to this table, and I'm going to insert four CityName in this table. I'm going to type insert into our Tevin name city. Then inside the parenthesis, I'm going to pass on the city name column, city name, our values are inside the parenthesis, our first city name is Dilma our second city name is Kolkata, and our third city name is New York, and our last city name is London. Semicon to end this line. If I execute this code, it's going to add all the data in our table. Let's execute it. It's executes successfully. If I show you my table, here you can see all the city names. If you notice here you can see, I use only one column name which is CityName. I do not use City ID to insert data because when we create the table, we use auto increment for CID colum. That's why we donate to provide the value for CID column. It's automatically increase. Now I'm going to create our second DvlPersonal. I'm going to type personal. And our first column name is ID. I datatype is also int and I use not now constant, and I don't want to auto increment it. I'm going to remove this one. And our second column is name. Also, I'm going to use Werker datatype for this. I use not now constant for this and the third columname is percentage, and her datatype is int. And its data dy is It also, I'm going to use not null constant Our fourth column name is age, and I'm going to use in datatype for this. I don't want to leave this column empty, so I use Nonull. Our next column name is Zener, and it is deatives care. And I want to pass only one character, and also I don't want to leave it blank, so I use not null. Our last column name is student city, mean a city. But here I'm going to use in data type and you know the reason. Also, I'm going to use not null constant. Now I'm going to set primary key for this table, and I want to set primary key ID column, and now I want to create the foreign key, I'm going to tie foreign key. Then inside the parenthesis, we need to provide the column name, which I want to make foreign key, and I want to make foreign key is city column. I want to type a city. And now we need to provide the reference, which table I want to use. With that, we need to provide which column we want to use. I'm going to type reference, and our table name is city. If I click on the City table, here you can see a column name CID, which is our primary key, and I'm going to use this column. Inside the parenthesis, I'm going to type CID means City ID. That's it. If I execute this command and rephrase this Kema section or new table, personal, our code executes successfully. Now I want to add data to this table. For these, I already create some dummidata. I'm going to paste this Dami data here. Here you can see my data and you already learn about it, how we can insert multiple data in our table. I'm going to execute this command only. So I click here and I'm going to execute this icon. If I execute this icon, here you can see a error, duplicate entry four for key primary. Here you can see, I use same ID for Neha and Lucas. I'm going to change four to five, and then I'm going to run this code. Here you can see, now it's executes successfully. If I show you my table, here you can see all the data. You can see it create our table and store our multiple values here you can see a column name a city. We store multiple ID in our city column because this is our foreign key column. So this is the way we can set primary key and foreign key in a table. Now you can think we can see only ID, not the city name. If you want to show the city name, why we select the city. For this, we need to use Joy and Clause and we are going to learn about it in our next video. How we can combine our table using Joy and Clause. Thanks for watching this video. See who soon. 23. MySQL INNER JOIN Tutorial : Hey, guys, good to see you back. In this tutorial, we are going to learn inner join class. Here you can see we have four different joins in misqul inner join, left join, right join, and cross join. In this video, we are going to cover inner join, and I will cover the remaining three in our upcoming videos. Let's see what is inner join. Here you can see a table one, and we have another table, table two. If I combine this table, you can see a black stroke area. This is our common data, which is available in table one and also table two. So if you want to extract the common data from both of the tables, we can use inner join. So the inner join CX records that have matching values in both tables. Let's see an example. Here you can see a student table and city table. In our city table, we set our CID column primary key, and the similar type of column available in student table, which is city, where I store city ID. This is our foreign key column. Now I want to extract the common data between those table. For this, we need to use inerjoin. Here I'm going to match city column with CID column. If I add another city to our city table, Agra, you can see in the student table, no one have ID four in their city column. I do not exist in this city column. When we match this table, it never ever going to return Agra anyhow. It will show only related data. Let's see the syntax, how we can type in a join. First, only to type select command. Then we need to provide columns name. If you want to see all the columns, you can use star sign. Then we type from table one, and then we need to use inner joint command. And I want to join table two with Table one, so I type table two, and then we need to match between two tables. For these, we need to type on keyword. Table one dot column name, and we need to provide the foreign key column equal to table two dot column name, which I said primary key in other table. If there is common data between this table, it's going to return this data. Let's start the practical and see how we can use inner join. Hello, friends. Good to see you back. As you can see, I opened my Maski wwnch application, and I also open Zem Control Panel. First, I'm going to start Apache, and then I'm going to start MCQuo. Now our server is ready for connection. I'm going to Hard on my connection named Demo, and I'm going to double click on. Here you can see, we have two table in our database, city and personal. If I show you my personal table, you can see we have six columns in this table. If I show you my city table, here you can see we have two columns in this table, CID and city name. At first, I'm going to insert some damaged student data in our personal table. I already create Cavan for this. Here I'm going to pase this command. Insert into our table limit is personal. I execute this command. Here you can see is written in error. We cannot use duplicate entry for ID, so I'm going to type five and then I'm going to execute the command. Now it execute properly. If I show you my table, here you can see, we successfully insert our dummy data in our table. Let's back to C table. Here you can see the City IDs. We stored the City IDs in our personal table, and this is our foreign key column. As you can see, we cannot see the city names. We just see some numbers. Whence they are ID. And we do not know from the ID that the student is from which city. For this, we need to use inner join. First, we need to type silt command, and then we need to use star because I want to see all the column from both the tables. Then I'm going to type and our first table name is personal. Then I'm going to use IerjoN. And then I'm going to use our second table name city. After that, we need to use a keyword name on. It means we will match both the table, and then we need to provide both the tables column name. Why do we want to match. From personal table, I'm going to use a city column. So to type personal city. I want to mesh these columns with our second table name city city dot CID column. That's it. I type, select start from personal. This is our first table, Iajoin and this is our second table. Then I use on keyword and I want to match SCD column with CID column. This is our primary key, and this is our foreign key. If I execute this code, here you can see all the column from both the tables. Here you can see, CID and CityName. These two column came from CD table. Also you can see act value and CID value is identical. Now I'm going to use Allis Name for this table. Otherwise, every time to type whole table personal as P, city as C. P for personal, C for city. For now, they are temporary names, and now I'm going to repress personal with P and repress City with C. If I execute the command, once again, you can see the same result. Our command becomes smaller after using Ali's name. If you notice here you can see two columns, a city and CID. For now, they are unnecessary columns. And I don't want to show it in my table. Utter Ender, I just want to see student city name. I don't want to see a city or city ID. For this, I'm going to remove star sign. Utter select, I want to show ID column. I'm going to type P dot P means our allies name, ID. Then I want to show student name. P dot name. After name, I want to show their city. I'm going to use City allies name, C dot CityName. This column is from city table. We use C dot city name. I type all the columns which I want to show in my table. Now I'm going to execute this command. Here you can see the three columns, IT name and city name. So here you can see how can we use inner join to see data from both the tables? Now I'm going to show you we can use Wire clause with this command with this select command. So to type, wire, and I want to see those students who are from London. Wire city name equal to London. Here I use a condition. If I execute this code, you can see two result, Smith and Emma because they are from London. Not only that, we can use Water by clause. So to type Water B I want to order this column by their name. I'm going to type p dot N. That's it. As you know, our name column is from personal table, so I use P. If I execute this command, here you can see, Emma comes first, and then come Mth. Our name is printed from ascending order. At last, I want to told you one thing. We can remove inner Keyword from Ierjoin keyword. By default, it's going to run inner join. If I execute this code, you can see we can see the same result this track only work for Inerjoin. I hope now Inergoin concept is clear for you. Thanks for watching this video. 24. MySQL LEFT JOIN : Hello, friends. Good to see you back. In this tutorial, we are going to learn two new clause, Lapoin and we join. In our previous video, I told you, basically we have four join in my SQL, inner Join, lab join, right join and cross join, and we cover inner Join in our previous video. In this video, we are going to cover lab join and join. First, let's try to understand what is lab Join and why we should use this lab join. Here you can see a table, table one, and you can see another table, table two. Just assume that table one is our lap table and table two is our we table. Now I want to extract those data, which is common both of this table. With that, I want to see all the data from table one. For these, we need to use lap Join. The lab join returns all the records from the lap table. Also match record from the right table. Just remember one thing, if the data do not match between Table one and Table two, it not going to return all the data from table two. It is going to return all the data from table one. Let me show you an example. Here you can see, we have two table, student table and CD table. From CD table, CID column is primary key. Similarly, from our student table, City column is our foreign key. Suppose Smith forgot to provide his city name, but if I use Inarjoan then it's not going to return Smith because it's only written matching data. If city column value match with CID column value, then it's return the data. In case we need to see all the data from student table, no matter if student forgot to provide his city or not. In that case, we need to use LebJoan. This is going to return all the student from student table and if student provide their city, then it's written city name. Otherwise, it's written null in their city column. Let's talk about syntax. First, you need to type salt command. If you want to see all the columns, you can use star sign. Otherwise, you can provide the column names. Then you need to type from Qard Table one. Table one means our lap table. After that, we need to use lap Join command two. Table two is our right table, if you want to create join between them, for this, we need to use on QWord table one dot column name. Here we need to pass the foreign key column from table one, equal to t two dot column name, and here we need to pass primary key column. From two. Let's try to explore it how we can use LebjoN. Here you can see on my screen, I open MScill Word winch application and I also open Zem Control Panel. First, I'm going to start Apache and there I'm going to start MySQL. Now our server is ready for connection. I'm going to over on my connection name demo and double lick on it. And here you can see, we have two table in our database, personal and city. If I show you my personal table, here you can see, we have six cola in this table, ID name percentage age, ender, and city and we set foreign key to our city column. Similarly, we have another table named city and I made CID column primaty key. Now I want to extract lp Join record from both this table. For these, we need to type select star from personal and I'm going to type p join. And our second table name is city. To match both of the tables, I'm going to use on clause. But before I'm going to use Ali's name for this table, personal as P, city as C, on P dot, our foreign table is city and I want to match with C dot our primary gi column CID, that's it. If I execute this code, how can see all the data from both of the tables. Now I want to remove City Racode from personal table, but I can't do it because I use Nounal constant for this column. Also I made this column for k. That's why we cannot remove data from this column. We need to change it. But for this, we need to use Alter command. But for now, I don't going to use order command. I'm going to use IbelTol in this software. For this, we need to go to this table and we need to click on this setting icon. And here you can see all the column from the personal table. As you can see the data type from this table, and here you can see all the constant name that we can use in the table and NNS vein nonal. For every column, I use natal constant because I don't want to leave it blank those columns. Now I'm going to uncheck this SCD column. I don't going to use nonal constant from this column. That's it. Now we can store null value in this column. Also I made this column foreign key and we need to remove the foreign key. Here you can see option named foreign key. If I click on this tab, you can see I already create a foreign key in this table city and I want to delete this foreign key, I click on it and I click this option, Delete Selected. Now you need to click on this apply option to apply these changes. I want to click on it. Behind the scene, it's going to run Alter command and Here you can see, change command, a city for this column, we are going to use null value and also it's going to drop our foreign key. I'm going to execute this command. I click Apply and it's apply successfully. And now we can store Null value in this column. To store Null Valu, we need to use update command. But for now, I don't want to use Update command. Suppose I want to remove City from Emma. I click on this column, and I'm going to click Edit icon. Now I'm going to change EA Valu blank. Now I need to click on this apply option. Apply changes to record set, and I'm going to apply it, it is going to run this command, Updatestudent dot personal set a City blank ire ID three, and I'm going to apply this command. Finish. After applying, it says zero value in this column. Zero means null value, and I'm going to do same thing for Lucas. Just I'm going to remove his city value, and now I'm going to apply it once again. And once again, you see it print zero, zero means null value. I null two records from this table. But if I run this lab John command once again, even then it will show us all the data. Let's execute it. Here you can see it return all the data from both of the table. Here you can see it written Emma and Lucas data, but the data of these two student is not matching with any city name. So as I say you earlier, pjon going to show all the records from lab table meant personal table. Either it match with city table or not. It going to return matching record, also it going to return null record from the lap table. But if we use inner join for this table, so mtotype inner join, and I execute this code, Hegacy it do not return Emma and Lucas because they do not provide city ID in their St column. There so I inner join do not return those students. And if we want to return all the student from the personal table, in that case, we need to use LebJon. Once again, I'm going to type Lab Join. If I execute this code, you can see it's written all the student from personal table. Now it's clear for you what is the difference between Lebjoin and inner Join. Now I don't want to see this column. After gender, I just want to see city name. For this, we need to remove the star and we need to provide exact column name. First, I want to see name column and the name column came from personal table. I type P dot name. With that, I want to see their P H, and now I want to see their city name. I want to use C CityName. Cityme column came from City table. That's why I use C because C is the Allisoname of city table. If I execute this code, here you can see here you can see only those column which you want to see, name, age, and their city name. Not only, we can use higher clause with that, someone to type hire age greater than 20. If I execute this command, you can see only those students whose age is greater than 20-years-old. If I want to run order by command, yes, we can. Just to type, just we need to type derby. I want to order by name column. So I type name. If I execute this code, you can see all the names are print in ascending order. It starts from A, then E, N. This is our lip joint. We mainly used to extract all the data from our lip table, and also it's going to return the matching data from both of the tables. Let's talk about another clause, right join. 25. MySQL RIGHT JOIN : To understand right join, we have two table, table one and table two. Table one is our left table and Table two is our right table. Now I want to see all the common data between these two table. With that, I want to see all the data from table two, no matter if it match with table one or not. For this, we need to use join. The right Jen returns all the records from the right and match record from the p table. Let me show you an example. Here you can see our previous tables, Student table and City table. You can see there is no student who came from Agra, means ID four. No matter how, I just want to see all the city name from city table according to city name, want to see all the student from student table. If there is no student who doesn't match with any city, then you're going to provide null value in their name column. For these, we need to use write join. If I show you the syntax, everything is remains same. Just we need to type t join. That's it. Let's explore it practically how it's work. Once again, I'm back to my Mo school wage application. Now just on to type right join. That's it. If I execute this command, Harry can see all the records from City table. As you know, our table is city. Basically, join going to return all the data from Right table. It's return all the city name from City table. As you can see, we have no student from personal Tavi who came from New York City. That's why you can see null Valu in their name column. It's going to play the opposite role of p join. It's going to return all the matching data with that and also it can return all the null values. It's going to return all the cinnym from city table, no matter if we use it or not. I hope now it's clear for you what is join and abjoan what is the basic difference between join and Lip Joan. Thanks for watching this video. See you in the next tutorial. 26. MySQL CROSS JOIN Tutorial : Hello, friends. Good to see you back. In this tutorial, we are going to learn cross join. Here you can see the list of joins inner Join lab join, join and cross join, and we learn inner Join, Lab John and join in our previous videos. In this video, I'm going to cover cross join. Let's try to understand what is cross join meaning. Here you can see two tables, student table and city table. If we want to join these two table without primary key and foreign key, then we can use cross join. Here you can see, our first student name is AON. So Cross john going to join all the cities with Ad van. It's going to return for different Raw. Next, it's going to join Smith with every city name, and it's going to follow the similar step for Sophia and also Emma. It's going to make a combination. Here you can see, we have total four student. Our student table. As you can see we have four city name in our city table. And if we join this table using cross join, it's going to return 16 different results. Means 16 different combination, and it's going to return result, something like that. Here you can see for Advance, it creates four combination. First one is Deli then London, New York, and Agra. Similarly, it's work with Smith, and this is the UCage of cross Join. It's not a very important join that we can use in our project. Basically, we use inner join epjoin and right join. Let's say the syntax. First, you need to type select command, and then you need to provide columns. Otherwise, you can use star sine then from table one, and then we need to type cross join, and we need to provide another table, table two. If we execute this command, it's going to return combined result of two tables. Let's see how we can use it practically. Here you can see, I'm back to my mySQL War Wrench application. As you can see, I already create two tables city and personal. Here you can see, we have four record in our city table. If I show you my personal table, you can see we have five record. Now I want to extract Cross Join from both of the tables. For this, I'm going to use cross join. I want to tie Select star from our table name and our table name is personal. And I'm going to use cross join. Then we need to provide our second city. If I execute this command, here you can see here you can see, we get a lot of combination from both of the tables. The total result is 20 because we have four city in our city table and we have five student in our personal table. That's why it creates 20 combination. We can get the same result without using cross join. For this, I'm going to copy this command and move to another tab I'm going to replace this command. Now I want to see the same result without using cross join. First, I'm going to remove this cross join keyword and I use just a comma. That's it. If I execute this command, here you can see the same result. It's written 20 combination. I hope now you can understand what is cross join. It's basically create two tables combination. It's not a very important join. Its use is very rare. Thanks for watching this video. 27. MySQL JOIN Multiple Tables : Hello, guys. Good to see you back. In this tutorial, we are going to learn how we can join multiple tables together. Here you can see a table one. With that, we have another table, table two, and now I want to extract common data between this table, and we can use inner join to extract this data and we'll learn about Inergoin in our previous videos. But there I join only two tables. Suppose we have another table, table three, and now I want to extract this kind of data, which is most common in Table three, table one and table two. For this, we can use Inergoin between T three and table one. As you can see, here I join three tables together. Following the similar procedure, we can join four table five table as much we want. There is no limit for this join. Let's see the real example why we need this joins. Here you can see three tables, student table, programming language table, and city table. As you can see, we have five column in our student table. You can see we set some numeric values in city column and PL column. PLs mean programming language. These numeric values are primary primarily from Language table and CD table. PID is our primary key column. Similarly, CID is our primary key column from Cit table. We set this primary key because they are unique IDs. But the problem is when we extract all the data from student table, it's going to return City ID and programming language IT, not the name. We cannot find out from which city this student has gained. Similarly, we cannot find out the programming language name, for these, we need to use multiple joins. We need to join PL column with PID column. Similarly, we need to join City column with CID column. Let's see the syntax how we can join multiple tables. First, we need to use select command. Then you can selet columns. Otherwise, you can use star sign from T one inner join table two, and then we need to use on keyword to join the tables columns, t dot foreign key Column. Equal to from t two, we need to bt primary key column. You already learned this syntax from our inner joint tutorial. Now I want to join our third table with t one. Once again, we need to use inner join command. Ierjoin T three. Similarly we need to use on Kar, T one, foreign key column name equal to t three primary key column. Just one thing you need to remember, t dot column name and t dot column name must be unique. They should be primary key. It should contain unique values. Otherwise, it's going to return wrong result. Following the similar procedure, we can add more tables. Also, you can use abjoin or right join to join multiple tables. Just you need to replace inner join with lab joint and everything remains same. Let's see practically how it worked. Here you can see, finally, I'm back to my Moscu Word range application and I also open Zem Control Panel. First, I'm going to start Apache and then I'm going to start MSQL. Now our server is ready for connection. I'm going to over on my connection name Demo and double Glick on it. Here you can see, we have three table in our database, city, language, and personal. You can see in our personal table, I added two column, SCT and language. These are our foreign geys I save some IDs in this column. These IDs came from City table and Language table. Here you can see, we have four different programming languages and their IDs and ID is our primary key column. Similarly, in our city table, CID is our primary key column. Also you can see the city names. Now I want to join all the three table records. I want to see all the student record using inner join. Let's type the command. Select start from personal in our first table, and then I'm going to use IRjoin I want to join with City table, and I'm going to take Ali's theme for our table. For personal table, I'm going to use P and for city, I'm going to use C and then I'm going to use on command on. As you know, using on command, we can join our tables. Personal dot a city column, and I'm going to join this column with C dot CID column. If I execute this command, here you can see it's join our two tables, personal and city because here you can see CID column and City Name column. Now I want to add our third table. For this, I'm going to use inner join command. Inner join and our third table name is language. Also, I'm going to take Ali's name for language. For joining these two table, I'm going to use on keyword on P, mean personal table, I want to join with Language table. Language. Equal to, and I want to join this column with this ID, ID means language ID. I back to my personal table and I'm going to use Allis them ID. We can join our three tables successfully. This is our first join with Ct table and this is our second join with Language table. Let's execute the command and see what happened. Here you can see, we successfully join our three tables. Here you can see, these two columns from city table and these two columns from Language table. As you can see, now these columns are unnecessary. I don't want to see these columns. You know, to resolve this problem, we need to remove star. And also we need to provide the exact columns name. From personal table, I want to see ID and name student ID and student name. I'm going to type select PP means lie name of personal table, ID, pt Name. After that, I want to see city from city table, Ct, CityName. Then I want to see programming language name from Language table tm Tb that's it. If I execute this command, here you can see the exit column name, which we want to see, their ID, their name, and their city name and their languages. Now it's good for table readability. Otherwise, it look very clumsy. Not only that, we can use higher condition with that, and now I want to see those students who select JavaScript language. I want to type hire dot m equal to inside the quotation, JavaScript. And semicolon to end this line. If I execute this aman, now you can see only two students, Smith and Lucas because they select JavaScript language. I hope now it's clear for you, how can we join multiple tables? Following the similar way, you can add multiple tables together no matter how many table we have, and if you don't want to use inner join, you can use Joan otherwise lab Joan. That's totally on you. Thanks for watching this video. See you in the next tutorial. 28. MySQL GROUP BY & HAVING Clause Tutorial : Hello, friends. Nice to see you back. In this tutor here, we are going to learn group by and having clause. Here you can see on your screen, we have to at two tables, Student table and City table. In our student table, we have five column ID name Agent City, and we have another table, city and He we store city names, and we save city ID in our student table. Now I want to find out how many students gain from which city, something like that. According to the personal table, from D, we have one student. From London, we have two student and from New York we have one student. Here you can see London ID is two and this ID is exist two time in student table city column. That's why it returned to student two and here you can see ID four Agora, and it doesn't exist in student table. That's why in our result, it don't provide the city name. When we need to extract this breakout using select command, we need to use group by clause. It's going to group cities. The group by statement groups row that have the same values into Summar E row. Find number of students in each city and Group by statement is often used with aggregate functions. Count, max, mini, sum, average, et cetera. When we use Group B class, then also need to use aggregate function and we learn about aggregate function in our previous videos. Aggregate function is compulsory with group B. I told you one thing, when we use group B, joins are not mandatory. You can use it only one table. Let's see the syntax. First, you need to type salt command, then you need to provide columns name. Otherwise, you can use star sign. Then you need to type from table name, and then you need to provide the wire class and it's not mandatory to use her class, you can use Group B without re class and last you need to provide Group B, and then you need to provide the column name where you want to group. If you want to use Group B with joins, we can. Let's see the syntax. Select columns name from table one, and if you want to use inner Join, you can inner join two, then on keyword on t dot column name, means foreign key, equal to t dot column name, means primary key, and if you want to use condition, it's up to you. Just you need to type group B, and you need to provide the column name. Let's start the practical and see how it's worked. So finally, I am back to my Meskel Wargne application, and I already created a table named personal. As you can see, we have total five record in this table. Here you can see two column is city and language. Here you can see the IDs that we are fetching from other table, and now I want to count student by their city. I just want to extract how many students came from each city. If I show you my city table, here you can see, we have total four city Dili Kulkat New York, and London. Soto type, select city, comma. Now I'm going to use aggregate function, cow. In such a parenthesis, we need to pass the column name from personal table. Where we save cities ID. The columname is a city, and then I'm going to type from and our twname is personal. I just want to extract how many students came from each city. Soto type group B, and then we need to provide the column name which is city. If I execute this code, it's going to return two columns, city and cow. Let's execute the code. Hey, there is a syllabistic in our code. Our columnme is a city not city. If I execute this code, now you can see the result. From ID one means city one, we have one student. From ID two, means City two, we have one student. From ID three, we have one student. But from ID four, we have two student. If I show you my Cit table, here you can see our IDFd is London. If I show you my personal table, as you can see, Smith and Emma came from London, means ID four. That's why it's written ID four count two. Now the problem is we cannot see the city names, so we need to use Jones to see the city names. I'm going to join this city table with personal table. First, I'm going to take Allis name for this personal table, P, and then I'm going to use inner Joan. Now I need to provide our second table name, which is city and I'm going to take Allis theme for city, which is C. Next, we need to use on QWord. Which means to join our table. I want to join personal dot is city column with city CID column. This is our foreign key column and this is our primary key column. Now we need to provide the exact column name, which I want to see. If I show you a city table, you can see columname is city name. Here we need to provide city name. C dot city name, and also we need to change little bit in our count section. Just need to provide pt City. I just assign this a city column from personal table. It's time to execute the code. If I execute this code, here you can see, now we can understand this result properly from Dili one student, from KukataO student, from New York one student, from London to student. As you can see, it's not a proper column name. I'm going to take Ali's name for this column as total. If I execute this code once again, now you can see our columnim is total. Now we can understand how many students came from each city. Now I'm going to show you how can we use iclos with group B, we need to use recross before the group B. I'm going to type fire p dot Cinder equal to male. I use a condition and I want to see all the student who are male. It's going to return total student from each city, but there is a condition. Students should be male. I don't going to count female student. If I execute this code, here you can see from Kolkata, one male student, from New York, one male student, and from London, one male student. As you know, we have two students for New York, but according to the higher condition, it's written one student means one male student. I hope now it's clear for you, how can we use higher close with group B? You need to remember, we need to use or clause before the group B. If you want to ordering this table, yes, you can. After group B, just to type Water B and I want to order by their cityme. Copy this line. If I execute this code, here you can see it sprint our CityName ascending order. We can use Watery with group B. Just you need to remember after group B, you need to pass Water By. This is our group B clause. Let's talk about our having clause. We already learn how to extract total number of students using group by glass. If we want to set a condition in this result and try to extract a new different result, I want to extract those city names where we have more than one student. In that case, we need to use having clause. We cannot use higher condition in this result. Most of the time we use having clause with group B. We cannot use this having clause anywhere else. Let's see this index. First, we need to type sealed command and then we need to provide columns name. Table name, group B columns name. After group B, then you need to provide the having condition. Just you need to remember one thing. If you use hire condition, then you need to use it before the group B. If you use having condition, then you need to use at the group B and this having condition based on this group result, not the table columns. Let's see it practically how it's or. First, I'm going to remove the condition. I don't need this re condition for this example. Then I'm going to run this code. Now you can see from London we have two student and now I want to see those city names where we have more than one student. For these, I'm going to use having clause having and now I'm going to count the city. I'm going to copy this count section and paste it here, greater than one. This is our condition. If there isn't more than one student in a city, it's going to return this city name. If I execute this code, here you can see it's written only one city name, London. This is the only city that we have more than one student. I set this condition using having loss, not higher clause. If I want to set condition to group by result, then we need to use having class. We cannot set higher class in the group by result. Last thing I want to say, we can use any aggregate function like sum average, et cetera. I hope now it's clear for you what is group B and having clause. Thanks for watching this video. 29. MySQL SubQuery with EXISTS & NOT EXISTS : Hello, guys. Good to see you back. In this tutorial, we are going to learn about sub queries and also known as nested query. With that, we are going to exist and not exist clause. Here you can see on your screen, we have two tables, student table and City table. In student table, we have four columns, ID name Ag and City. And in our City table, we have only two columns, CID and City. And now I want to see those students name who are from New York City. As you can see, New York City ID is three and according to the student table, we have two students who are from New York. I just want to see student name who are from New York, not their ID, not their age, something like this. It's mean, Joan is not necessary for this command. But the question is, if I don't use Joan, then how can I search city names? By their ID. In actual life, we are going to search with their names, not their ID. In that case, we use sub queries. Actually, we set query in our student table. First, we need to define the columns name from student table. In our case, I want to see only student names. With that, we need to use her class inside the hire clause, we need to provide another query for another table, and we called it nested query. Let me show you the syntax. Then it's clear for you. First, we need to type select, then we need to provide the columns name, then from table one. Table one means student table, and then we need to use Wire class, then we need to use the columns name which is exist in our student table. In our case, city. Equal to inside the parenthesis, we need to provide another query, and this query going to search result from table two. Inside the condition section, we need to provide the condition means which result I want to search. I told you one thing, this query not only run with salt command, it's also work with insert, update, and delete command. It's work with every main command. Let's start the practical and see how we can use this sub query. So finally, I'm back to my McCle Wrench application, and I already start my am server. Here you can see a database name study. Inside this database, we have total three tables city, language, and person. Here you can see, I open my city table and we have total four record. Since we have total four city name, Delhi Kolkata, New York, and London. And if I show you my personal tv, here you can see we have total five records, here you can see a column name is City. Here I save cities ID from CD table. Now I want to print those students name who are from London, but without using joining. For this, we need to use select command with sub queries. First, I'm going to type select name from our Tavin name, personal. And now I'm going to use HR heard and I want to search in SCD column. I'm going to type St, Sit, and I want to search city by their city names. But if you notice, here you can see, we don't store CityName in SCD column. We just store their IDs from CD table. For this, we need to use subquery, equal to inside the parenthesis and inside the parenthesis, I'm going to type select CID column from CD table, CID, from our Tim city. We need to use another re condition to select this city. I'm going to type hire our city ame lam, someone to type city name, equal to, and I want to search those students who are from London, someone to type London. That's it. First, this query going to provide the London City ID from City table. Basically, this query going to return ID four because London's ID is four. Let me prove you that. If I copy this code and paste it here and semicoron to end this line, and if I execute only this code, not the whole code, so I'm going to click on this Thunder icon. Here you can see it written ID four means London city. Now I want to search this ID four in our SCIt column from our personal table. For now, I don't need this line, so I'm going to delete. So if I execute this command, here you can see, it's written to student Smith and Emma. And if I show you my personal tv, ecc Smith and Emma, both are from London. So you can see here without using joining, we can search result using course name, not their ID. First, I execute this command to get the London City ID and up to get the ID is going to run this command. If we want to search students for multiple city, in that case, we cannot use this equal to sign. In that case, we need to use in operator. So we're going to use in. Then inside the parenthesis, I want to move this code. So with London, I want to search student name from D. I use comma and inside the double quotation, D. If we execute this command, it going to return to ID and we cannot search to value using equal to operator. In that case, we need to use in operator. If I execute this command, here you can see, it's written to the three student Smith, Emma and Neha. If I show you my personal table, here you can see Smith and Emma is from London and is from Delhi. It's possible because just to use nested query here, otherwise, we need to use joins to do this work. Let's talk about our two new clause exist and not exist. Here you can see the command of exist clause. For this, similarly, we need to create this sub query inside the parent query. After her condition, we don't need to provide any columns name. We just need to type exist. Similarly, we need to use another command. Now the question is how its work. If any single record exist in child command, then parent gammand going to execute. Means if our child command returns any record, then parent gmmand show the result. Otherwise, it show nothing. Let's talk about not exist clause. It is totally opposite of exist clause. If child command do not provide any single record, then it's going to execute the parent command. If there is a record in child command, then it's not going to run the parent command and show nothing as a result. Let's start the practical and see how it's work. Once again, I'm back to the Mccle Warrench application. First, I'm going to show you the exist clause. For now, I want to search only one city. I'm going to delete the leave from the squaring and I'm going to type exist command, and now I'm going to use exist clause. After hire condition, we don't need any columns name. We directly type just exist. Now the question is, what is the meaning of the squaring? First, it's going to run this command, and this command going to return ID four because London City ID is four. If London City ID exists in city table and then it's going to execute this command, otherwise, it's not going to execute this command, and this command going to return all the student name from the personal table. Let me show you that. If I execute this code, here you can see, it's written all the student from personal table. If I search city and if I search city which is not exist in our city table, in that case, it's going to return nothing. Let me show you that. I'm going to type Paris. And if I show you my City table, here you can see, we don't have parties in our city name column. And if I execute this command, here you can see, it's written nothing because this subquery do not provide any ID as a result, and this query execute if only the ID exists as a result. But if I use not exist, in that case, it's going to return all the student names. It's play the opposite role of exist command. If I run this command, here we can see all the student names from personal table. According to the query, if city name doesn't exist in city table, in that case, it's going to run this command. That's why it's written all the student names. If not exist, it's going to run the parent command. That's it. I hope now it's clear for you what is exist and not exist. Thanks for watching this video. 30. MySQL UNION & UNION ALL : Hello, friends. Good to see you back. In this tutorial, we're going to learn two new operators, Union and Union. Here you can see, we have two tables, Student table and teachers table. Suppose we are working with school database, and inside the database, we have two tables, student and teacher. In student table, we have three column, ID name and He. Similarly, in teachers table, we have three column, ID name and H. I want to extract a result using select command. Which is written combined result from the table, something like this. Here you can see, our first three result came from student table and our last three result came from teachers table. This kind of result is possible using Union all command. Basically, Union all going to join two different table commands and return a combined result. Let me show you one thing. Suppose we have a student named Sophia and also we have a teacher named Sophia. Then it's going to ignore the duplicate name. If we use only union, then you don't return the duplicate record. But if we use union, then it's going to return the duplicate record. Let's see the syntax. First, we need two types, let command, then we need to provide the columns name which we want to extract from our tables. Then we need to type form and we need to provide the table name. In our case, table. And then we need to use the operator, union, otherwise union all. Then once again, we need to type the command for our second table. Select columns name from table two. Union all going to return a combined result from both the table. But when we need to use union or union all, then we need to maintain some rules. Our first rule is each statement within union must have the same number of columns. Here you can see, we have two columns for our first table. Similarly, we need to use same number of columns for our second table, our second rule, the column must also have similar data types. Suppose our first column is ID, so its data div is in, and our second column is name, so it's datatype is were care. We need to follow the same datatype for both the table columns, and our third rule is the column in each select statement must also be the same order. If we select ID column in our first table, similarly, we need to select the ID column for our second table. We cannot use the ID column after name column. It's mean we need to follow the same order. Let's start the practical and see how we can use Union and Union A. Finally, I am back to my mySQL wage application. Here you can see, we have two new tables, student and teacher. If I show you my student table, here you can see, we have total three student in our table, Advanced Smith and Sophia. If I show you my teachers table, Here you can see, we have also three teacher, William James and Lucas, there are many similarities between two tables. If I show you my student table, here can see, we have four column ID name age and City, and we use same column for teachers table, ID name Agenct and their data debe is same. Now I want to extract results from both the tables using Union. I'm going to type select star from student. Then I use Union keyword. On on select star from feature table. You need to remember one thing. If their columns name is different and their datatype is different, otherwise, if their order is different, then we cannot use star. If the number of column is same and the datatype is same, then we can use this star. If I execute this code, here we can see all the data from both the tables. The first three result came from student table and the last three result came from teachers table. Now I want to see name column from both the table. I rep star and type name. If I execute this score, Hero can say all the student and teacher's name together. Now I want to add another student in our student table, and I want to use a name that is already existing teacher's table. So I back to my student table and here I'm going to paste the code, which can add a new student in our student table, our student name is Lucas. If I execute this code and show you my student table, here you can see a new student named Lucas. Now you expect to see seven record in name column. Lucas from student table and Lucas from teachers table. Let's execute the code and see what happened. Here you can see it don't show the duplicate record, I show only one record, Lucas from student table. Our union command do not show duplicate entry, and if you want to see the duplicate entry, you need to use Union A. Let me show you. If I run this command, now you can see duplicate records. Lucas from student table, Lucas from teachers table. With name, I want to show their age. I want to select age. He. If I execute this code, here you can see student name and teacher's name with their age. Now I'm going to show you how can we use hire class with Union and Union A. Now I want to see those students who are greater than 21-years-old. I'm going to type hire age greater than 21. Similarly, I'm going to use this hire close with teachers table, age greater than 32, that's it. If I execute this command, now you can see those students and teachers who match with our conditions, and now I want to search result according to their city name. If I show you my city table, here you can see, we have four city, Dili Colgata, New York, and London. Now I want to search those students and teachers who are from Kolkata. For this, we can search with their IDs. Otherwise, we can use sub queries, and I want to search result with sub query. Let me show you. Here, g equal to. This is the parenthesis, I'm going to type select command. Select CID from City table. CID is our primary column from our CD. Here you can see the column. Then we need to use hire condition to select the city. I want to type Wire city name equal to Kolkata. That's it. This command, going to return the ID of City Kolkata. According to our city table, it's going to return ID too. Hey, I did some ille mistake. I forgot to change the column name, which is a city. Hire a city. We are searching students from their city, not their age. I want to set the similar condition for our teachers table. I'm going to copy this command. And I'm going to paste it here. This command going to return those students and teachers who are from Kokata City. Yes, we can search student from their city's ID. After all, this subquery going to provide the same ID. That's why we use this subquery. If I execute this code, here you can see only one student and one teacher who are from Kokata City, Adw and William. If you want to citinm after H, we can do it using subqueries. For this, we need to use Inarjoins. Let me show you how can we use inarjoin with this command. First, I'm going to take Ali's name for our student table, a inner join, and I want to join with City table. I'm going to type city. I'm going to take As them for City table, which is C. Now I need to use on keyword to join these columns. I'm going to type on student a city column. It's mean from student table, I'm going to use a city column equal to from city table, I'm going to use CID column. This is our foreign key from student table, and this is our primary key from city table. We also need to change the condition here. W C dot CityName equal to Kolkata. With name and H column, I want to see student city names. For these, I'm going to use C dot CityName because our city Name column is from City tab. That's why I use C. Name and H came from student Tavin. I need to use dot name and dot H. This code, going to show this result from student table, and we need to write similar code for teachers table. I'm going to copy the code. And paste it here. Now I want to run this hare condition for our teachers table. I'm going to change the table name teachers. I take T as Ali's name and I use TC for City table Ai's name. As you know, name and he came from teachers table. I want to replace with T, T dot name and T dot H. Our city name column came from City table. I'm going to use the Ai's name TC, that's it. And now I want to join a city column from teachers table. I type T dot a city and CID column from City table. I use the allies them TC where CityName from City table is Kolkata. That's it, and semicolon to end this line. This command going to return result from Student table, and this command going to return result from teachers table. Let's execute the command and see what happened. Here you can see, it's returned to result. Our first result came from student table at one, our second result came from teachers table and also we can print their city names. And now I want to search student from Dili and teachers from Kolkata. I want to change student hire condition query, Dilly. If I execute this score, He can see, we have two students who are from D, Sophia and Lucas, and we have one teacher, William, who is from Kolkata. I hope now it's clear for you, what is Union and Union A. Thanks for watching this video. See you on the next tutorial. 31. MySQL IF Statement : Hello, guys. Good to see you back. In this tutorial, we are going to learn two new clause related MySQL, I and gas clause. Here you can see a table name student, and we have three column in this table, ID, name and percentage. According to the percentage, I want to extract those results that told me which student is pass and which student is fail, something like that. Here you can see Advance is not pass the exam. That's why in result column, it print, fail, and those students who pass the exam, it print pass in their result column. So here you can see a custom column name result. We create the custom column based on the condition, and this is our condition. If student percent is greater than equal to 33%, then print pass in result column. Otherwise, written fail. If I want to create a conditional base custom column, for these, we need to use p clause. Let's see the syntax, how we can use I clause in a SQuL query. First, we need to type select command. Then you need to select the columns. Which you want to see in. For your custom column, you need to type IP condition. First, you need to type p clause, then inside the parenthesis, you need to provide three parameter. In your first parameter, you need to provide the condition, and according to your condition, it's going to return to result, either true, either false. If your condition is true, you can pass your message, whatever you need to show in the result column. I written false, similarly, you can show the false message, and then you need to assign a name for this column. You need to type as means Ai's name, and you can type your own Ai's name for that. In our case, result. Which is a temporary name, and then you need to provide the table name from tab and you can use higher Clause with that. Let's see the practical and try to understand how we can use the condition. Here you can see, I open my Zem control panel and I also open McCL W wedge application. First, you need to start Apache, and then you need to start MCQL now your server is ready for connection. I'm going to over on my connection name and double lick on it. Here you can see a database name student, and we have to two tables in this database, city and student. If I show you my student table, here you can see, we have to five student in our table, Advance meets Sophia Nia and Mia. Here you can see a column named percents. Using this percentis column, I want to create a custom column and try to see who student is pass and which student is fail. For this, after select command, we need to provide the columns then. I want to see ID, name, and percents. ID name and percentage, and I don't want to say student age. That's why I don't call this column. Now I want to create the custom column, where I want to see the results? I'm going to use a comma and then I'm going to use IP condition. I then inside the parenthesis and then we need to use this columns name. Why do we want to set a condition? Our column name is percentage. I percent is greater than equal to 33. This condition going to return to result, true or false, me pass or fail. I pass, then it is going to return pass in the result column. I condition is fail, then it is going to return fail. In results column. Then after round basis, we need to take a name for this column. I'm going to use Ali's name as and our column name is results. After that, I'm going to use from clause. From, we need to provide the columnme which is student. And semicolon to D line. First, I type the three columns name, ID name and percentage, and then I create a custom column name results, and I set a condition in this column. If student percent is greater than 33 or equal to 33, then it's going to return pass. Otherwise, it's going to return fail. Let's execute this code and see what happened. Here you can see, without Smith, everyone pass their exam. He just get 29% in his exam because 29 is less than 33, that's why it's written F. This is our IP statement. Using this, we can set any condition in a particular column and based on the result, we can create another custom column. Let's see another clause, which is Ks. 32. MySQL CASE Statement : Here you can see the same table on your screen. Here I say student ID and their name and their percentage. According to the percentage column, I want to create another custom column, which is going to tell me the grade of their result. Basically, I want to see their grade score, something like that. You can see, according to different percents, it's written different result in grade column. Advance is fail, Smith grade is B, Sophia's grade is B plus, and Ma's grade is A. Here we need to use multiple condition, something like that. I student percentage 80-100, then it's going to return A in his grade in the grade column. Then between 60 and less than 80, it's going to return B plus. Next, between 33 and less than 60 is going to return B and less than 33 is going to return fail. So here you can see we use four different conditions. Whenever you need to use multiple condition, and with these conditions, you want to create a custom column for this, we need to use case clause. Let's see the syntax how we can type case clause with select command. First, you need to type select, and then you need to provide columns name, which you want to see then create a custom column, you need to use case command. First, you need to type case, and for all your multiple condition, you need to use Keyword. When your condition is true, then it's written the result. In our case, A, similarly, we need to create all the conditions and at last, we need to use s. If all the conditions are fail, then it automatically execute the s condition, and here you can put your own customer result. Also you need to provide the name for that. Here you can use multiple condition as much you want to end this case statement, you need to use end command, and also you need to provide a column allys name. In our case, grade, then you need to provide the Taviam from Taviam and semigu to this line. Let's start the practical and try to understand how we can use case command with sealed command. Once again, I'm back to my McQuL Wrench application. Here I'm going to use case clause. I'm going to remove this line and I'm going to type case. But before I start my condition, I want to show you something. Here you can see a student named Mia and his percentage is 110. I know it's not correct, and I'm going to handle this situation later. So after case, I also want to end this case statement. I want to type, as and I want to take a temporary columnim for this case condition, and our columnim is grade. So between case and end, I'm going to set my conditions according to student percentis. For this, we need to use when statement, when, and then we need to provide the columnam where we want to set the condition, which is percentis. When student percent is greater or equal to e t, and percent is less than equal to 100. If both the condition is true, then it's going to print A. If student percent is 80-100, it means he achieve A grade. For our next condition, I'm going to copy this line. Similarly, when our percent is greater than equal to 60 and our percent is less than 80, in that case, is going to print B plus. Now I'm going to create our third condition. When our percent is greater than equal to 40, and percent is less than 60, in that case, is going to print B. Similarly, in our next condition, if percent is greater than equal to 33 and less than 40, then it's going to print C. In our last condition, when percent is less than 33, there is going to return fail. Basically, we create total five condition and you can create as much you want. At last, we need to create a very important condition, which is s. Else, I want to print wrong input. Suppose someone type wrong input like 110 or any string input, in that case, it's going to print wrong input. So our case clause is complete. If I execute this code, here you can see the results. Here you can see adv achieve B grade, Smith achieve fail. Basically, Smith do not achieve anything. Sophia did great. She achieved a grade, and achieve B plus grade. For Mia, our data into operator enter wrong input. So in that way, we can use multiple conditions and also we can create a custom column to show this result. Not only that, also we can use this case clause with update command. Let me show you. Suppose we need to update multiple records at once. Suppose I want to change Smith result. I also want to correct me as result. For this, I'm going to use Update command with case clause. First of all, I'm going to remove all these statements from my editor. First, I'm going to type update and I want to update student table, then set command. After that, I'm going to set my condition, and I want to update in my percents column, so I'm going to type percents. Equal to inside the parenthesis, I'm going to use case clause to set the multiple columns, and we need to update the percents according to their ID. First, I'm going to type case. Then I'm going to use when statement when but before we need to provide the column name, and I want to choose ID. ID is two, then percentage is 48. Similarly, when ID five then 43%. This condition is for Smith because Smith ID is two and I want to update his percentage 29 to 48. This condition is for Mia. Her ID is five and her updated percentage is 43. As you know, if we use Ks we need to type N. And then I'm going to use hire clause because we need to use hire clause with every update command. Otherwise, it's going to change all the columns. I'm going to type hire hire ID, and we need to check in multiple columns. I'm going to use in operator. I inside the parenthesis, ID two and ID five and Semgron to this line. First, it's going to check ID two and ID five exist or not. And then it's going to set the condition according to their ID. For ID two, is going to set 48 and for ID five is going to set 43, and it's going to update this value in percentage column. Let's execute the code and see what happened. I execute this course successfully. If I show you my student table, here you can see the result. It update our columns. Now Smith's percents is 48 and Myer's percentess 43. It's possible at once because we use case clause with update command. This is very important use case of case clause. Thanks for watching this video. Stay tuned for our next tutorial. 33. MySQL Arithmetic Functions Intro : Good to see you guys. In this tutorial, we are going to cover arithmetic functions. Here you can see a list. These are all arithmetic functions. But in this tutorial, I'm going to cover these green functions, and I don't going to covert this red function in this tutorial because these are core mathematic functions like sine theta, stita, tantita, et cetera. Let's try to understand the green functions. Our first function is Avis. Avis mean absolute value. Here you can see a negative value and absolute value. If we pass one of this value in our evos function, then it's always going to return positive value. If you send negative value or a positive value, it's always going to return the positive result. It's going to remove the minus sine and our next function is flow. Here you can see, we have to at two types of value. First one is 7.3 and second one is 7.8. Now the question is, what is the meaning of flow? Floor means land means below. If you pass 7.3, otherwise, if you pass 7.8, it's always going to return seven. Similarly, we have another function named sale. Sale means roof. Roop means what? A or high. If I pass the similar value to the function, then it always return eight. Our next function is round. It's always written in rounded value. Let me show you. Suppose we have two value, 7.3 and 7.8. If your value is below than 0.5, then it is going to return seven. If your value is above 0.5, then it is going to return eight. Basically, I want to say if below than 0.5, then it's going to return floor value, and I above 0.5, then it's going to return cell value. 7.3 is below than 7.5. That's y is written seven and 7.8 is above the 7.5. That's why it's written eight, and our next function is square root. If I want to extract square root of nine, the answer would be three. So in my sequel, we have function name square root. Inside the parenthesis, we need to pass the number means nine, and then it's going to return three, and our next function power function. Here we need to provide two value, base value and exponential value. As you can see in my example, two is our base value and three is our exponential value. So two multiply by two equal to four, four multiply by two equal to eight, and you know the process, how it's work. Our next function is sine. If I pass any number to this function, maybe it's zero, maybe it's one or any positive number, if the number is greater than zero, in that case, it's going to return one, if the number value equal to zero, then it's going to return zero. If I send any negative value which is below the zero, in that case, it's going to return minus one and our last function is rand. Rands mean random value. It's always written a random floating point value. Suppose I want a number 1-10, and if I use this function, it can return any number 1-10. It may be seven, maybe eight, anything. Let's start the practical and try to understand how all the arithmetic functions are work. Thanks for watching this video. 34. MySQL Arithmetic Functions : Here you can see, finally, I'm back to my computer screen. First, I'm going to start Apache and then I'm going to start McCL. Now our server is ready for connection, so I'm going to over on my connection named Demo and Doogie Cone. So first, I'm going to show you the example how we can do normal arithmetic calculations. For these, you need to type select. For calculation, I'm going to type a number seven, and I'm going to addition this number with two. So I'm going to use plus sign, then I'm type two and semig to end this line. If I execute this code, here you can see, it's written nine, seven plus two equal to nine. Not only that, you can set a column name for this. For this, you need to use Ali's name. Let me show you as, and our column name is total. If I run this code, here you can see the columnim total, and it also calculate our number nine. Here you can use any sign. If I use substruction sign and then run the code, here you can see the result. Five, we can use multiplication. If I run this code, you can see the result. Also, we can use division sine. If I execute this code, here you can see the value, three, four and five, and also we can use modulus reminder. If I execute this code, you can see the reminder is one, and also you can type MOD for modulus sine. If I execute this code, you can see the same result. Similarly, we can use division DIV. If I run this code, here you can see is written three. Because it do not return floating point. That's why it's written three, and now I'm going to use all the arithmetic operator with a table. Let me show you. Here you can see a table name student. You can see we have total five student in our table. We set their percents and their age, city, name, and their ID, and now I want to addition ten with every one percents. I'm going to type, select. I want to see student ID and their name and their percente. From a T student. If I execute this code, here you can see it is written only three columns, ID name and percentis now I want to arithmetic calculation with percentis column. I want to at ten with everyone percents. I'm going to use parenthesis. Inside the parenthesis, percentis plus ten. If I execute this code, here you can see, it's at ten with everyone percents. Not only that, we can use Ali's name for this column. So I'm going to type as total. If I execute this code, you can see the column name. So using this method, we can use any operator with that. I'm going to use multiply CI, multiply Y. Two. If I execute this code, you can see the result. In MisquL we have a constant vil, which is Pi. It's a inval function in MSQL. Let's use this function. For this, I'm going to type another salt command. Select Pi is a function, so I need to use parenthesis. If I execute only this command, here you can see it's written 3.14 1593 and you know the exact value of Pi and it's written a constant value. Next, I'm going to show you another function which is round So type round. As I told you earlier, round function going to return a rounded in. I value is greater than 0.5, then it's going to return the seal Val and I value lower than 15, then it's going to return floor in. Let me show you. If our value is 5.52 means is greater than 15. If I execute this code, urcc is return six. Similarly, if I use 5.49 and then execute the code, rec C is written five. If I use negative value and then execute the code, He can C is written the negative five. Now, suppose we have three value after point. If I execute this code, we know it's going to return five, and now I want to return two digit value after decimal point. For this, we need to declare the dCTs. How is digit we want? I want two digit. That's why I pass two, and now it's going to create the rounded value a decimal point. These two values. Let me show you. If I execute this code, now you can see it's written 5.50. And if I change the numbers, I want to pass 97 and then execute the code here you can see it's written the rounded value of two digits 97, which is 98. Just we need to pass the parameter, and we need to to the function how much digit we want up to decibal point. Now I'm going to show you our next function which is sale. Inside the round ress I'm going to provide a value. 2.19. Here you can see, up demil, this value is lower than 0.5. No matter what, if I execute this code is going to return three. If the number greater than 0.5, and then I execute this code is also return three. Cell function always return the higher value. This function is not work like rounded function. Similarly, we have another function, flow, which can play the opposite role of the cell function. Let me show you. If I execute this code, here you can see is returned two. But if you notice up decimal point, the number is greater than 0.5. So no matter what, it's going to return the lowest value. Let's talk about our next function, which is power of function. For this, we need to type POW. Basically, we need to pass two parameter in this function. Our base parameter is two, and our next parameter is three. It's mean two to the power three. If I execute this code, roc C is written eight. Suppose our base number is five and 52 power three, it's going to multiply 35 at once. If I execute this code, herro can C is written 125. This is our base parameter, and this is our exponential parameter. And now I'm going to show you another function, which is square root. For these, we need to type SQRT. Inside the round *****, we need to pass the parameter, and our parameter is 16. If I execute this code, here you can see the answer is four. If I try to execute the square root of 15, if I run the sode, here you can see it is written floating by and well now I want to rounded value of this result. For this, we need to type the function name rounded. Round and we need to pass this square root function inside the round braces. If I execute the score, here you can see it and four. In that way, we can use functions inside another function, and our next function is random function. For these, just we need to type RAND. If I run only this function, it's going to return result 0-1. Let me show you. If I run this code, here you can see, it's written a random number 0-1, so it's written a decimal number, 0.95 something something. But if I were to number between 1200 for this, we need to use multiple hundred. If I execute this code, now it's written a number between 1200. It's written 88. If I run this code once again, now it's 1058. It's randomly written any number. If you don't want to this decimal value, you can use round function. Let me show you. We need to book this Run function inside the round sis. If I execute this code, here you can see it's written in random number without decimal point. If I run this query once again, now it's written 46 without decimal point, now I want to return a number 5-15. In that case, we need to type five plus rand and five plus ten equal to 15. If I execute this code, here you can see, it's written in number 5-15. If I run this code, once again, it's written nine. If you don't want to see the decimal value, we can use four function with that. Let me show you flow. I need to book this ran function inside the round races. If I execute this code, here you can see is written in number 5-15, it's written 12. If I run this code once again, it's written six, and now I'm going to show you the most use case of random function. Let me show you. If I execute this code, here you can see our student table, and here you can see is print our ID in ascending water one, two, three, four, five, now I want to print our student ID random for this. After student, I'm going to type Water bin now I'm going to use Run function. If I execute only this query, here you can see it prints our student ID randomly two, five, one, four, three. Similarly, if I re execute this code, here you can see it start from two, one, three, four, five, so we cannot predict which ID cames fast. Most of the time, we use Run function with waterme. Our next function is AVs. Let me show you Abs. Inside the round says if I pass any value may be negative, maybe positive. It's always going to return the absolute val. Suppose I tie -77. If I execute this code, here you can see is returned only 77 if I pass any decimal value, with minus sine, it's also going to return the absolute value 77.9. Let's talk about our last function, which is sine. I'm going to type sine. Basically, sine can return three type of values. Suppose I pass nine. If I execute this code, it's return one because this number is greater than zero. If the number is greater than zero, then it's going to return one. If I pass zero, in that case, it's going to return zero. If I pass any value which is less than zero, something -99. If I execute this score, then it's return minus one. If the value is lower than zero, it's always return minus one, no matter if the value is decimal or not, it's always going to return minus one because we pass negative. This is our sine function. I hope now it's clear for you. These are our arithmetic functions. Thanks for watching. See you in the next tutorial. 35. MySQL String Functions part 1 : Hello, guys. Good to see you back. In this tutorial, we are going to learn mysquL string functions. Here you can see all the string functions. And in this tutorial, we are going to cover these green color functions. And in our upcoming videos, I'm going to complete the other functions. Let's start the practical and try to understand how we can use these functions with our slit command. So finally, I'm back to my mySQL owing application. I already start my Zem server and create the connection. And here you can see, I open a table named students, and we have total five record in this table and also we have total five column, ID, name, H, percentis and SCT. I'm going to apply our string functions in this table. So our first function is upper. If I pass any string to the upper function, it's going to convert to the string into uppercase. Let me show you select ID, comma and now I'm going to use upper function. Inside the parenthesis, I'm going to provide our column name, which is name. I want to see all the name in uppercase. That's why I use this column. This function going to return a new column and we need to use Ali's name for that Sow type as name. Comma, and our last column is student H. Then we need to provide the Taviname from student and semiclum to ND line. If I execute this code, here you can see, it's create a new column name which is uppercase name, and you can see all the names in capital letter, Advance meets Sophia Na and Mia. We have another similar function, which is also do the same thing, UKs. Let me show you sumar type U, CAs. If I execute this code, you can see the same result. All the names are uppercase. Our next function is lower function. It's going to convert all the characters in lower case. Here I'm going to type lower. If I execute this code, here you can see it converts student names in lowercase. Similarly, we have another function which can do the same thing case, let me show you. If I execute this code, you can see the same result. Our next function is character line. We can count characters using this function. Suppose I want to count all the characters of student names. For this, I'm going to use this function, character, character in. Inside the parenthesis, we need to provide the column name, which is name. Now I don't want to see student age, so I'm going to remove this column name. As I'm going to change the Ai's name of this column, which is total. With that, I want to see name column, so I'm going to type name, character in. If I execute this command, here you can see student name and it's count total character of this name. Add 15 character, Smith five character, Sophia six character, Neha, four character, Ma three character. Not only that, if I use space between name and their surname, it's also count the space. Not only that, we have short form of this function. If I pass char length only and then execute this code, you can see the same result. It's also count the name character. This is the shorter version of this function. Not only that, we have another function which can count the characters, which is length. The length function, not count the characters, I calculate the byte length. It's going to return how many bytes the name takes. Let me show you. I'm going to remove this old function and I'm going to type length. If I execute this code, hey, it's not work because I did some sllista. The length spelling is wrong, GTH. If I execute this code, now you can see nearly the same result. I know it's similar to character length function, but it written bytes, not the characters. Let's jump into our next function, which is concat. Basically, it's going to join to string value or string value with numeric value. Suppose I want to print student name with their edge in a single string. In that case, we can use concatenate. Let me show you. For now, I don't want to show student name. I'm going to remove this column, and I'm going to use concat function. And we can pass multiple columns name inside the round braces. With name, I want to concat student age. Also I'm going to change the allies name of this column, which is result. If I execute this code, here you can see the result at 1:24 Smith 22 Sophia 21. It prints student name with their age, and it creates a single string. If you want to give space between name and age, you need to use third column comma comma, between them, I'm going to use then codes and a space. If I execute this code this time, you can see it give a space between name and He. Not only that, we can use any character between them. Suppose I want to use slash if I execute this time, here you can see a slash between name and edge. There is no limitation with this congt function. We can use multiple columns to concat each other. Suppose after age I want to concat student ID. Inside the double code, I'm going to pass dash, then coma, I'm going to pass ID column ID. If I execute this code, this time you can see student name, student age dash student ID. Not only that, we can use custom string with that. If I type student age, and if I execute this code, here you can see it print at one student age 24 dash ID one. Similarly, we have another function which is Concat undersco Ws. I'm going to use underscoews. There is a minor difference between concat and concat Ws. In this function, we can pass third parameter, which is separator and it count our first parameter as separator. I'm going to pass inside the double course underscoe in our second parameter, I'm going to pass, I'm going to pass name column. Then he column and then our ID. If I execute this code, here you can see the result. I print name, then their edge and then their ID. We don't need to use this separator multiple time. In our previous method, it looked very clamsy, but in this way, it look very organized. This is the basic difference between concat Ws and concat function. In concat Ws, it's stick our first parameter as a separator. Our next function is related to cream. Related trim, we have to do three function, trim art trim and trim. Let's see what is the difference between these three function. First, I'm going to type trim Inside the round verses, I'm going to pass a stre which is student name. And before Utter student name, I'm going to provide a lot of space. But if I execute this se, here you can see there is no space, Utter and before student name. Let me prove you that thing. So I'm going to type another slid command. Select inside the quotation, I'm going to provide a lot of space. Then I'm going to type student name, student name. Up to student name, I'm also going to provide a lot of space as name. If I execute this query only, Hicc student name, but it also print lot of space. But if I use trim function, let me show you. We need to move this string inside the round braces. And then execute the code. Here you can see in lap side, there is no spaces. Similarly, if I use art trim and then execute the code, now you can see in right side, there is no spaces, and you can see this space in lap side, trim going to remove all the space from the lap site, and art trim going to remove all the space from the right side. I I use simply trim and then execute the code, here you can see, let's remove all the space from both of the side, and then come our next function, which is position. I'm going to use this function position. This function can find the position which you want to find. Suppose you just want to find name and then we need to use in Q word I and in, and then you need to provide this stream. Then inside the double course, your name is add one. And now I want to find this name word in this string and this function going to return exact position of this word. Let's execute the command and see what happened. If I execute this command, here you can see it's written six. The name word start from the sixth character of this string. Let me prove it. Our first character, second, third, fourth, then a space, but it is also a character. So this is our fifth character, and thence come our sixth character name, and that's why it's written six. But if I use the same word multiple time, then what happened? If I execute this code, it's always going to return the first word position. Similarly, we have another function and we called it INS TR. I'm going to use this function, Ns TR. It's similarly worked like position function. Just a little difference. We don't need to type in keyword, first we need to provide this string where we want to search, then we need to provide the keyword. What do we want to search? This time, I want to search is keyword. If I execute this code, here you can see he's written 11 because you start from 11 index. This is it. In our upcoming video, I'm going to cover all the remning functions. Thanks for watching this video. Stay tuned for our next tutorio. 36. MySQL String Functions part 2 : Hello, guys. Good to see you back. In this tutorial, we are going to learn string functions. This is our second video related string function. Here you can see all the string function from a squall command. But in this tutorial, we are going to cover these green ones. Without wasting your time, let's start the practical. So finally, I'm back to my Mccar Wrench application and I already start my Zem server and create the connection. Here you can see a table named student. As you can see, there are a total five students, and we have also total five columns, ID, name, H, percentage and SCT. I'm going to start this tutorial with Lockett function. Let me show you. The Locket function also work like position, but we can send third parameter to this function. So let me show you select, locate. At first, we need to provide this such query and the word is M, and then we need to provide the second parameter means our string and our string is I am a student. Last, you need to provide another parameter which is completely optional. Then I'm going to take a Allis name for this column as result, if I execute this code, here you can see, it's written three. It's written a index position, which is three. It's work like position, and now I want to search only a character, not AM. If I execute this code, here you can see the same result. It's stent three because the A character start from the third index. But as you can see, there are another A character in this string. I want to search, so I want to search the next a character position. For these, we need to pass our third parameter, comma, we need to pass the index number. I mean how much character you want to skip. I want to pass to pass three. I want to search result after three character, one, two, three. It's going to skip this three character, then it's going to search the result. Means it's going to search a character. If I execute this code, here you can see, p sorry, we don't need to use Dual codes. Also I'm going to change the parameter, fourth. I'm going to search this result from the fourth index, one, two, three, four. If I execute this code, here you can see it's written six because up to fourth index, our Acarctor start from six index, one, two, three, four, five, six. This is the basic different between locate and position function. In locate function, we can use third parameter, and we can define the position from where we want to search. If you want to search result up to ten character, yes, you can. Just you need to provide the parameter here. Let's jump into our next function, which is substring. It's means you can remove a part from a big string. I'm going to use this function. So to remove this function Locate and I'm going to type substring. First, we need to provide this tree. In our case, I'm going to pass the damage tree. Hey, this string is to be, so to remove this part. LoramHpsum is simply and in our next parameter, we need to provide from where we want to start, mean position. We need to provide the index number. So to use fourth index means E. So if I execute this code, here you can see it's written this string without this three character because here we mentioned that our string start from this character, EM. That's why it's c this three character and return this string from this position. If I pass seven and then execute this code, here you can see it's written EPAM is simply. Ipsum is simply. But wait, there is another parameter. Suppose I want to print only Ipsum is from this string, we need to pass third parameter to specify the exact position. Let me show you comma 14. His query is going to return this string from seven index to 14 index. If I execute this query, here you can see, it's simply print, Ipsum is simple. And if you want to print, only Ipsum is, and then you need to count after your starting index. This is your starting index, one, two, three, four, five, six, seven, eight. I I pass eight, and execute this code, now it's print Ipsum is. From the starting index, we need to count serially, like one, two, three, four. It's work that way. Not only that, we can use minus value in this parameter. Let me show you. This is our minus one position, minus two minus three minus four minus five, and minus six. I'm going to type minus six. F minus six, I want to print six character. It's mean this simply word. I'm going to type sixL. If I execute this code, here you can say it's print simply word. Here starting position is minus six, means this position. Then after minus six, I want to print of six character, means this over. There is a shorthand version of this function, which is substr. Let me show you. Just wotype substR. If I execute this code, we can see the same result. Similarly, we have another function named me. Let me show you. If I execute this code, it's written, same result. Substring, substr and met, it's written, same result. Let's talk about our next function, substring index. Let me show you. I'm going to simply type the function name, substring underscoe index. In this function, simply, we just pass a string with that, we need to provide a delmeter. It's mean from where you want to break this string, and it's going to return result before the breaking point. Suppose here I'm going to pass a um.google.com. In our second parameter, we need to pass the deliminated name. I mean from where I want to break this string. Suppose I want to break this string from this point. Inside the inverted course, I'm going to pass dot. Now I need to mention the exact, here you can see two dots. We need to provide the index number of this character, and I'm going to pass one. Mens our first dot. If I use two, I going to recognize this one. If I execute this code, simply is going to return all the characters before our first dot. Let me show you if I execute this code, here you can see is print WWW if I pass two index and then execute this code, it's going to return www dot Google. It print all the characters before this dot. Similarly, if I pass Z character and then execute this code, here you can see, www dot ZW. It's print all the character before the second Z. This is our substring index function. Our next function is PT. I'm going to type P basically, it's going to return all the string, lip side of the index number. Here we can pass only two parameter. Suppose I want to print only www dot, so we need to pass fourth index. From fourth index, it's going to print all the string from the lip side. If I execute this se, here you can see it print WWW dot. Basically, it's going to print four character from the lip side. Similarly, we have opposite function which is right. Let me show you. So this function going to return last four character means.com. If I execute this code, here you can see it print.com. If we use left function, then we can print our first four character, and if we use function, then we can print our last four character. Our next function is RPAd Sun type R PAD. Arpad means first, it's going to count all the character from the string. If you want to increase the character length in right side, if you count this character, we have total 14 character in the string. And now I want to increase this string size 14-20, so to pass 20 character. In our second parameter, it's going to padding our text from the right side. After 14 character, I'm going to fill this area with a new character, which is slash. If I execute this code, here you can see, after prenww.google.com, it filled the area with slash. Here you can see twotsixslash. Because we have already 14 character in this stream and we mentioned 20 character for this stream. That's why it print total six additional slash to cover this area. Now the total character length is 20. Not only that, we can use any string in this position. Suppose O E, if I execute the code, it's fill the area with OE character. For now, I'm going to use slash. Similarly, PAD. If I use Pad, and then execute this code. Here you can see it fill the area in the left side with slash, and it's also written 20 characters. It's totally opposite of PET function. Similarly, we have another function named space. Let me show you Summertype space. We use this function rarely. Basically, we do not use this function. Suppose I want to use 50 space. So to type 50. If I execute this code, here you can see, it's provide 50 space. This is the use case of this function. I don't know why we use this function. Let's jump into the next function, which is reverse. Someone to type reverse. Basically, it's going to print a string in opposite direction. Here I'm going to type a string at one Ming. If I execute this code, there you can see the result. It's print our string from the opposite direction. This is the use case of reverse function. Our last and next function is repeat. I'm going to type the function name, which is repeat. In this function, we need to pass total two parameter. In our first parameter, we need to pass string, and in our second parameter, we need to pass the number how many time we want to repeat this string. I want to repeat this string for three time, so I pass three. If I execute this code, here you can see it print at one Manch three time. If I give a space and then run the code, now it's clear for you. At one Manch at one Minch at one Mang. This is it for this tutorial. In our upcoming tutorial, I'm going to cover the remaining functions. Thanks for watching this video. Stay tuned for our next tutorial. 37. MySQL String Functions part 3 : Hello, friends. Nice to see you back. This is our third tutorial related string functions. In our previous video, we cover 22 string function. But in this tutorial, I'm going to cover the remaining functions, the green ones. Let's jump into the mysquL Wgwench application. Finally, I am back to my mysocel Wgwench application. Here you can see a function name, repeat. This is our previous function. Let's start with a new function name replace. I'm going to type the function name, replace. Using this function, we can replace any character or a word with our new character or word. Let me show you. In our second parameter, we need to provide which character you want to find. I want to find Minh. Then in our third parameter, we need to provide the new string, which is going to replace the old string. I want to replace Ming with Smith. Smith. So first, it's going to find this keyword in our string and then it's going to replace this string with our new keyword. Smith, if I execute this code, Hegasy it print add one Smith. Not only that, if I use this Min word multiple time, add one Mine Mine. Let me show you. And then execute this code. Here you can see it replaces Minchkeyword multiple time. This is our replace function. In that way, it's work. Our next function is string compare. Let me show you. I'm going to type string compare STR CMP. Basically, we use this function to compare strings. Let me show you. Suppose our student name is Adan Ming now I'm going to pass another parameter. In this parameter, I'm going to use same student name, but I'm going to use small A, add one Ming if the compare is successful, then it's written zero. Let me show you. If I execute the code, her you can see it's written zero. Both the string are massed. And now I'm going to remove Minch from our second parameter. Here you can see our lip set string is greater than right such string. If I execute this code, it's going to return one. Here you can see it's written one because our lip such string is greater than right such string. Similarly, if I remove Minchkeword from the let side and type Minchkeword in right side, and then execute the scode, here we can see it's written minus one. If our right such string greater than lip set string, in that case, it is going to return minus one. So this is the main usage of string compare function. Let's jump into our next function, which is filled. So I'm going to type field. Basically, here we provide multiple string list. And if you want to find a particular string from this list, we can do using field function. So here I'm going to pass multiple string. Ming Smith, Roy, inserted do codes, Smith once again. From this list, I want to search Ming. If I execute this code, here you can see it's written zero because in our first parameter, we need to provide the search query and our search query is Ming remaining values are our list items. So if I type Ming once again, and then execute this code, here you can see it's written four because it's written the index number of this word. This is our first index. Smith, Roy is our second index, and Smith is our third index, and Minji is our fourth index. That's why it's written four. Just you need to remember in our first parameter, we need to provide the search query, and our remaining parameters are our list items. Similarly, we can use this function for intesers. We can search inteser value also. Similarly, we have another function which is related to this function find in set, similar type, find in set. Similarly, it's going to find the query in a list, but we need to convert this list into a set. I'm going to convert this list into a set. So I'm going to remove the double codes and convert it into a set. That's it. As you can see, now it's a complete string. Inside this string, I want to search MincKord. So if I execute the code, Harrigacs also written four. Similarly, if I search Roy and then execute the s code, Higasy is written to index. It's a set. Just you need to remember between coma, you don't need to provide any space because string function counts space as a character. This is our fine inset function. Let's jump into our next function, which is formate. Let me show you some w type, formate. We use this function basically with numeric values. Here I'm going to take a numeric Val. I'm going to t12 3456. Here you can see up to decimal point, we have total three value, but I want to return only two value up to this decimal point. For this, we need to pass a second parameter and mention that how many value you want to show up to decimal point. I'm going to show two value. If I execute this code, here you can say it's written only 46 because it's written in rounded value. It's always written rounded value. In our arithmetic function tutorials, I told you how round value works. So I don't going to explain it here. This is the usage of format function. If I want to show one character and then execute the se, here you can see, it's written 0.5 because 0.456 is greater thanzo 0.450. That's why it returned the rounded value and its print five. Let's talk about our last function, which is x. I'm going to type x. Basically, it's going to return hexa decimal from any string. If I pass N string, Suppose I want to pass at one. If I execute this code, here you can see a hexa code. It always return hexa from of the string. Basically, we don't use this function, and if you want to use this function, you can use it in password column. These are our all function related to string. I hope now it's clear for you. Thanks for watching this video. Stay tuned for our next tutorial. 38. MySQL Date Functions Part 1 : Hello, friends. Good to see you back. In this tutorial, we are going to learn mySQL date function. Here you can see a list of date function, and in this tutorial, we are going to cover the green ones, and I'm going to complete the remaining functions in our next videos. Let's start the practical and try to understand how we can use this function with our select command. Here you can see I'm back to my McycleVwng application. I already start Zem server and I already create my connection. At first, I'm going to use current date function, which is going to return current date means today. First, I'm going to remove the old code, and I'm going to type, select current date. If I execute this code, here you can see, it's written current date. Whatever it's written, it is not our local time. It came from the server. It's written server date. For now, we use Localhost server. That's why it's written our local system time date. Similarly, we have the short form version of this function, which is card date. Let me show you. If I use card date and then execute the code, here you can see it's written same result. This function also written current date. Our next function is Seize date. Let me show you. It's basically written our local system date. There is no difference between current date and system date. If I execute this code, here you can see it's written date with date, it's written current time. Similarly, we have another function related to Cs date, which is now. Let me show you. If I execute this se, here you can see is written same result. It's written server date and time. Our next function is date. Let me show you. If I pass data time to this function, it's going to return date. First, I'm going to take a Allis name for this column as date. Then I'm going to pass date and time as parameter. First, I'm going to provide year 2021. Month is, and I'm going to pass June month and then our date is 21. With that, I'm going to pass at ten colon 32 minute, 20 1 second. If I execute this code, here you can see it's written date only, not the time. Similarly, our next function is month. Physically, it's going to return the month of this date. If I execute this code, here you can see it's written six only. If I pass any date and time to this function, it's going to return the month. Our next function is month name. Here you can see a numeric field, but I want to return the month name. For that, we need to use month name. If I execute this code, here you can see it's written the month name, June. Our next function is day function. If I use this function, let me show you. And then execute the code. There you can see it written the date date is 21. This function basically return the date of this month. Similarly, we have another function related to this function, which is day of month. If I execute this code, here you can see the same result. Our next function is day name. Let me show you. It's going to return the weekday name according to this date. If I execute this code, you can see, it's written Monday. 21 June 2021 is was Monday. If I change the month and I type seventh and then execute the code, here can see Wednesday. If I want to see the index number of this week, for these, we need to type day of week. If I execute this code, here you can see it's written fourth day. As you know, they start from Sunday. That's why Wednesday is the fourth day of any week. Similarly, we have another function which is related to this function day of year to type day of year. As you know, we have total 365 day in a year. According to this date, it's going to return the day number. If I execute the score, here you can see, it's written 202. Our next function is last day function. Let me show you. If I tie underscore day, and I'm going to pass February month means if I execute the score, it's going to return last date of this month, which is 28 February. If I use any different year, something 2020 and then run the scode here you can see the last date of this month is 29 because we know 2020 is leper year. That's why it's 1029. For now, I complete all these functions, and now I'm going to use these functions with real database. And now I'm going to use all this query without table. Here you can see a table name student. If I show you my table, here you can see, we have total five students in this table, Advance meth Sofia Neha and Mia. With that, you can see another column name DOP means date of birth. I set their date of birth her month and day format. Now I'm going to use all the query one by one. First, I'm going to type, select ID, name, DOB means date of birth from student. And now I want to extract D from this date of birth. For these, I'm going to use a function, comma and our function name is D function. Then inside the parenthesis, we need to provide the column name, which is DOB. Also, I'm going to take allies them for this column as day. If I execute the code, here you can see, it's written day from their date of birth. Ad tenth day Smith, 23rd, Sophia ninth, et cetera. Now I want to see their day name to type NAM day name, if I execute the code, here you can see, Advin born in Saturday, Smith born in Tuesday, Sophia Sunday, et cetera. So similarly, you can use all this function one by one. In the next tutorial, I'm going to go these functions. Thanks for watching this video, stay tuned for our next exercise. 39. MySQL Date Functions Part 2 : Hello, guys. Good to see you B. This is the second video related mySQL data functions. Here you can see all the data functions related McQuil. And in this tutorial, we are going to cover the green ones. So without wasting your time, let's start the practical. Here you can see I'm back to my McCL workbench application, and I already start my EM server. So I'm going to start this tutorial with Wik function. So I'm going to type, select, Wik Is that the undress is, I'm going to pass the date 2021, and our month is August and the date is 21. Space. As I'm going to provide time 09, colon, 35 minutes, colon, 40 1 second, and also I'm going to take Ai's them to this column as week. If I execute this code, hierogc is written. This is the 33 week in this year. As you know, we have totaled 52 week in a year. It basically return week number according to this date. Similarly, if I change the month and I'm going to pass January month and then execute this code, here you can see, it's written three week. Similarly, we have another function which is week day. I'm going to type DAY. According to this date, it's going to return day number of this week. Let me show you. If I execute this code, here you can see, it's written three, let's change the month eight. And once again, I'm going to run this score. Hierogcy is written five. As you know, our week start from Sunday. According to this result, this is Thursday. Our next function is year week. I'm going to type year, week. It's going to return to result and week. According to this date. If I execute the scope, hierochcyF it written year 2021, and then it's written weeknumber which is 33. Our next function is week of year. I'm going to type week of year. This function also going to return week number of the given date. If I execute this code, Hec similarly, it's going to return week number 33. Our next function is function. We already learn about it in our previous tutorial. Let me show you once again. If I remove this week off and then execute the code, rocacy is written the year according to this date 2021. Our next function is extract, which is very important function. Using this function, we can extract year, date, week, time, et cetera, as we want. I'm going to type extract. Using this extract function, you can retrieve any value from the given date. Suppose you want to extract month to this date for this oritotype month here. Month from this date. Also, I'm going to change the allys name, Week two result. If I execute this code, hirogcy is written eight. There are a lot of parameters you can use it here. Let me show you. You can extract microsecond, week, quarter, hour minute, et cetera. Suppose now I want to extract week from this date. I'm going to type week. If I execute this code, iogcy is written 33. If you want to extract hour from this date, let me show you. And execute the score, it's written nine. In a similar way, you can extract minute, second, millisecond, et cetera. In that way, you can use all the parameters. You can try it one by one yourself. Let's talk about our next function. Our next function is add date. First, we need to take a existing date. Then using interval, we return a new date. So I'm going to type, select, add date. For now, I'm going to use this date only, not the time. I'm going to remove the time and now I want to add 20 days to this date. For this, first, I'm going to use a coma, then I'm going to type a text, which is interval. After interval, 20 day. Obviously, I don't need this from an hour keyword. It's means I want to add 20 day with this existing date. If I execute this code, it's going to return a new date. Let me show you. Hurricac is written a new date. 2021, our month is 09 and date is ten. We can add how much day we want. Let me show you. Suppose I want to add 321 day. If I execute this code, urrocc is written a new year, 2022, seven month, and date is eight. Not only that, we can add months. Let me show you. Suppose I want to add three month and we need to pass month parameter. If I execute this code, here you can see it add three month with existing date. We can add anything, week, month, year, et cetera. Here you can see the list what kind of parameter we can use this function. Similarly, we have another function, which is do the same job, which is date, underscore, at if I execute this command, you can see the same result. Suppose I want to add five month. If I execute this command, here you can see, it's jump into the new year 2022. There is no difference between at date function and date underscored at function. In this function, we can add value with existing date. But if we want to create a new date, then we have another function. Let me show you. I want to use make date obviously we need to type sealed command. Select, make date, instead the round recess, we need to pass two parameter. First, we need to pass the new date year, suppose 2023, and then we can pass a day. Suppose our day is five. If I execute in this code, Hurccy is written a date, here is 2023 and month is 01 means January month. If we pass year en date, by default, it's always written January month. It's going to return first month of this year. This function can only create January month date. We cannot pass any month value in this function. Let's jump into the next function and our last function, which is dt underscores sub. It's play the opposite role of debt underscored at function. Let me show you date underscored SUB sub. If I execute this code, it's going to minus from this existing date. Let me show you. If I execute this code, here you can see, it's written here you can see it's written March month, 2021, 03 month, and date is 21. Using this function, we can subtract month, hour, day week, et cetera. Suppose I want to extract 21 day from this existing date, so you need to pass day parameter. If I execute this code, here you can see the result. For now, this is the end of this tutorial. In our upcoming tutorial, I'm going to cover the remaining ones, sudate two undersco days from under scoe days, et cetera. So thanks for watching this video, stay tuned for our next tutorial. 40. MySQL Date Functions Part 3 : Hello, guys. Good to see you back. In this tutorial, we are going to learn my school date functions. In our previous tutorials, I cober this 20 functions related date. In this tutorial, I'm going to cover with the green ones. Without wasting your time, let's start the practical. Finally, I am back to my Mccle War wrench application. As you can see, I already create my connection, and I'm going to start this tutorial with subdtFunction. Dead subfunction and subdtfunction do the same thing and we need to follow the similar process to run this function. Let me show you. Clea sub date, inside the parenthesis, first, we need to assign a date. Inside the double codes, I'm going to pass 2021 and our month is seven and our day is 21. As you know, then we need to pass the interval, sum to type interval, interval, and I want to -100 day from this date, 100 day. Also I'm going to take is theme for this column as they so if I execute this code, here you can see, it's written a new date. I subtract 100 days from this date and written a new date. Similarly, we can use month parameter, year parameter, quarter parameter, et cetera. Let me show you. Now I want to subtract month, 100 month. If I execute this code, it's written 2013, three month, 21 day. I minus nearly eight year from this date. Let's jump into our next function, which is dead deep. Deep mean difference. Let me show you. I'm going to tie dead deeps DAT, DIF. If you want to see the different between two dates, in that case, we use dead Deep function. Oops, there is a spelling mistakes. A last corrector should be A. Now it's correct. In this function, we need to pass two parameter, let me show you. We need to pass another date. In this parameter, I'm going to pass another date. 2013, February month and our date is 21. If I execute this code, it's going to return result in dates. Let me show you. So the difference between two deity is 3,072 days. Related to this function, we have another function, which is two days. Let me show you. So to remove this function and I'm going to type two days, two underscore days. In this function, we need to pass only one parameter. I'm going to remove one parameter from this space. This function also going to return difference between two dates. But the difference is, it's going to compare the date from the first year. It's going to show result between from one year to 2021 year. Let me show you. If I execute this code, here you can see, it's written 738357. We just use this function for education purpose. This is not a very important function. We have another function which is play the opposite role of this function and our function name is from days. So I'm going to type from days. In this function as a parameter, we don't need to pass any date. We need to pass number of days. Suppose I want to pass same number of days. I'm going to pass the days number here 738357. If I execute this sce, you can see it's written the same date. It's also provide the difference between from zero days and two days dates. This function always written date. Our next function is period ad, let me show you. So type, period, underscore, add. As you can see, we already pass a period. With that, I want to add five days, comma five. If I execute the score, here you can see with our old period, it add five and it's written a new period. Similarly, we have another function related period, which is period D. Let me show you. It's going to return difference between two periods. I'm going to pass another period here. Is that all codes, I'm going to pass seven, three, eight, one, two, three. If I execute this code, here you can see it's written 58. These are not very important functions, but our next function is very important function, which is date format. I'm going to type dead format here, date ace format. In date format function, basically we pass two parameters. In our first parameter, we need to pass a date, so on to pass a date, 2021, 09 month and 20 day. Our date is 20 September 2021. But in Asian countries, first we use date, then we use month, and then we use year. We don't use this format. So for this, we need to use date format function. Suppose I want to see the year from this date. In our second parameter, I'm going to pass percentage Y, Y. If I execute this code, it's going to return four digit year 2021. We call this parameter date format. We have a lot of dead formats in mySQL, let me show you the list of these date formats. Here you can see a date format. According to this date, 26 February 2021. Most of Asian countries use this date format, and our debt formats divided in four parts. Day, month, year, and week. Every birds come with different parameter, and we called it date format. Suppose you want to show dates, then we have four different formats. Let me show you. Suppose if you want to show date 01-31, in that case, you need to use percentage design, and if you don't want to show zero before the date, in that case, you need to use percentage ESIn if you want to show day like first day, second, day, third, fourth, in that case, you need to use percentise capital design. If you want to show the day number 1-360 s then you need to use percents small J character for months, similarly, we have four different formats. Let me show you. If you want to show the full name of this month, then you need to use percentage capital M. Similarly, if you want to show short form of this month, in that case, you need to use percentage B and if you want to show the month in digit format for this, you need to use percentage. For January, it's going to show 01, for February, it's going to show 02, something like that. If you don't want to show zero, before the month number, in that case, you need to use percentage C for year, we have two different formats. Let me show you. If you want to show the four digit year, in that case, you need to use percentage Y, and if you want to show last two digit, in that case, you need to use small Y, and for E, we have three different formats. If you want short from name of this week, in that case, you need to use dollar A, and if you want complete name, in that case, you need to use dollar W. If you want number of this week, in that case, you need to use small W. So similar to the date format, we have time format. Let me show you. Here you can see a time, five hour, 32 minute, 28 second, and 00 microsecond, our time permit divided in five parts, hour minutes seconds meldium and microseconds. For hour, we have four different formats. If you want to show 12 hour clock, in that case, you need to use percents small H, and if you want to show 24 hour time format, in that case, you need to use percentisH if you don't want to show zero before the 12 hour format, in that case, you need to use small G. And if you don't want to show zero before the 24 hour format, in that case, you need to use G for minutes we have one format. If you want to show zero to 68, then you need to use IF seconds, we have one format, which is, and then come meridian. If you want to show AMPM in that case, you need to use percents B and then come microsecond. If you want to show microsecond, in that case, you need to use percents A. Let's jump into the practical and try to understand how we can use these formats. Now I want to show this date in Asian format. For this, we need to create a format. For date, I'm going to use percents percents small D. Slash per month, I'm going to use percentis small B, slash, and for, I'm going to use percents, small Y. You don't need to remember all these parameters, you need to remember the process how it's work. If I execute this code, here you can see it's written a date, 20 September 2021. If you notice, you can see it's written two digit year format. If you want to show four digit year format, in that case, you need to use Y. If I execute the code, now you can see the four digit year format. And now I want to show the full month name of this date. For this, I'm going to use. If I execute this code, now you can see the full month name, September and now I want to show the week according to this date. For this, I'm going to use the underscore and I'm going to use a parameter, and I'm going to type percents W. So if I execute this code, here you can see, it's Monday. If you want to show time with this date, you need to pass time here. I'm going to pass time. First, I'm going to pass hour, four hour, 30 minutes, 21 seconds, and 30 microseconds. With this date, I want to show the time. I'm going to remove this week name, and I'm going to use percents, Smoleg for hour, colon, percents, I for Minute. So if I execute this code, here you can see here you can see with date it's written time four our 30 minutes. I hope now you understand the date formats, and then come our last function, which is SDR to date. First, I'm going to type the function name, which is STR underscoe two underscoe date. In our first parameter, I'm going to pass a string format update. Let me show you. First, I'm going to type month name, which is March, our date is 23 and here is 2021. Basically, I pass a string permit of a date. But the problem is my sequel do not upset this format. So in our second parameter, we need to assign the term of this date. So here we need to pass the date format. I'm going to so I'm going to type percent for month for date, I'm going to use percent D for four digit year, we need to pass percents Y. After this string, we declare the date format. For month, I use for date, I use small D and for her, I use capital Y. If I execute this code, here you can see it's written a date. Here you can see it's written a date which my sequel support properly. We basically use this function. When we get data from the user using t forms. When we need to set this date in our database, then we convert this data in SQL readable format. I hope all the date related functions are clear for you. In our next tutorial, we are going to learn about time functions. Thanks for watching this video. Stay tuned for our next tutorial. 41. MySQL Time Functions Tutorial : Hello, guys. Good to see you back. In this tutorial, we are going to learn about mysequel time functions. In our previous tutorials, we'll learn about mysquel date function. But in this tutorial, we are going to work with time functions. Here, you can see all the time functions related mysquel. And in this tutorial, I'm going to cover up to microsecond function. So without wasting your time, let's start the practical. Here you can see, I already start my Zem server, and I also establish my connection, and I'm going to start this tutorial with current function. So I'm going to type, select current time underscore time. Basically, this function going to return server current time. For now, we use Locals server. That's why it's going to return my local computer time. As I'm going to take Ali's name for this column as time. Let's execute the code and see and see what it return. I'm going to click on the Thunder icon. And here you can see the result. It's written current time. It's written 10:00 A.M. 43 mite, 43 second. This is my system time. But whenever we upload our code into the server, then it's going to return the server time. Similarly, related to this function, we have another function, which is short end of this function and the function name is card time. If I execute this code, here you can see it's written current time once again. There is no difference between two function. Similarly, we have another function related card time, which is current timestamp. Let me show you current underscore timestamp. We need to remove one R from this current. If I execute this code, here you can see it's return time with current date, and we have another function similar to this function, which is local time. Let me show you. I'm going to type local time. This function also return local current date and time. If I execute this code, here you can see the result. Our next function is local timestam. Time, I'm going to type SAM. It also return current date and time. If I execute this code, here you can see the result. Our next function is time function. Let me show you. This function going to return a time from particular date, so we need to pass a date as a parameter. Inside the parenthesis, I'm going to take a date with time. First, I'm going to type a date 2021, seven month. Date is 21. With that, I'm going to take a time 14 hour, 12 minute, 32nd and 008 millisecond. If I execute this code, it's going to return time only. Here you can see it. It's written only three format time. That's why first it return hour, then minute, and then second. If you want to see the hour from this data and time, you need to type hour here. If I execute this code, you can see the result. It's written 14. Similarly, if you want to see the minute, just type minute function. If I execute this code, you can see the result. It return 12 minute. Similarly, if you want to see the second, just you need to type second here. If I execute this code, you can see the result. It returned 32nd. Similarly, you can extract microsecond from this time. So I'm going to jump into the next function, which is time Div. Let me show you Sumo type, time, D. In this function, we pass two time as a parameter. This function going to return difference between two time. I'm going to pass two time as parameter. Our first time is 15 hour, 54 minute and 32 second. And our second time is 13 hour, 44 minute, 20 1 second. As we need to move this time inside the double gore. So if I execute this code, here you can see the difference between two time is two hour ten minute and 11 seconds. We basically use this function to extract the time difference between two times. This is it for the tutorial. In our upcoming tutorial, I'm going to cover these remaining functions at time, sbt make time, time format, second to time, time to second, et cetera. So thanks for watching this video, stay tuned for our next tutorial. 42. MySQL Time Functions Tutorial Part2 : Hello, guys. Good to see you back. This is the second part, relatate MSQuLT function. In this tutorial, I'm going to covert the remaining functions. I'm going to start out tutorial from att function. Without wasting your time, let's start the practical. Finally, I'm back to my mysquL ogench application, first, I'm going to use att function. So I'm going to remove time dip function and I'm going to tip at time. Basically, using at time function, we can at times with existing time. With existing time, we need to pass a interval. Let me show you. As parameter, I'm going to pass a date and a time. 2021, August month and 23rd day. With date, also, I'm going to pass a time 13 hour, 32 minute, 22nd and 0.1 microsecond. With this time, I want to add 1 hour, zero, zero minute and 00 second 0.5 millisecond. If I execute this code, here you can see the result. I add 1 hour with our existing time. Now our new time is 14 hour, 32 minute, 22nd, and 0.6 millisecond. With that, if you want to add with this data and time, yes, you can. Just unit to pass two then a space. If I execute this code, here you can see our new dt is 25 August. Using at time function, you can add new data time with our existing time. Similarly, we have another function which is play the opposite role of this function, which is subti. Let me show you. I'm going to type subti here. Subti means subtract time. If you want to minus some times for the existing time, in that case, you need to use subti function. If I execute this code, here you can see our new date is 21st August because it subtract two days from our date and also is subtract 1 hour 5 microseconds from this time. That's why it's written 12 hour, 32 minute 19 second 9999 and 6 microseconds. Our next function is make time. So to type, Mt. Using mati function, we can create new time. For this, we need to pass three parameter. I'm going to pass this three parameter, and our first parameter is hour. I want to pass 14 hour, 20 1 minute and 32 second. If I execute this code, it's going to return a new time. As you can see, our time is 14 hour, 20 1 minute and 32 second. Remember, you need to follow this sequence. First, you need to pass hour, then you need to pass minute and then you need to pass seconds. Our next function is time format. Let me show you. Time underscoe format. In this function, basically we pass a format which can misqu understand. Let me show you 14 hour, 32 minutes and 33 second. Our mysqal understand this kind of time format. And now you want to see the hours from this time. For this, you need to pass a second parameter. Inside the second parameter, we need to pass a format, and for hour, we need to pass percents H. If I execute this code, here you can see it's written 14. Using time format function, we can extract exact time you want. You can extract hours, you can extract minutes, you can extract seconds and millisecond also. MySQL provide a lot of time formats like hour. Let me show you the list. Here you can see a time five hour, 32 minutes, 28 second, and 00 mini second. It is a evening time, that's why I use PM value. Basically, we divide our time in four format, hour minutes, seconds and microseconds. Mildium is not a format, but we can declare Am PM using it. Let's start with hours. To show hours, we have total four different formats, small H H, small G and G. If we use small H time formats, is going to return 12 hour time format, but it's going to add zero before the single digit. Suppose for 1 hour is going to print zero, one, for 3 hours going to print 03 hour. If you want to show the 24 hour time format, you need to use percentage H. It's going to return time 00-23 and also it's going to return zero before the single digit. And if you use small G, it's going to return 12 hour time format. But it don't going to print zero before the single digit time. It's written one, two, three, four, something like that. Similarly, if you don't want to show zero in 24 hour time format, you can use percents Z. And for minute, we have only one time format, percentisis going to return result 00-59 for seconds, you need to use smalls. It's going to return result 00-59 once again. Then come microsecond. For that, you need to use percentis small A. It's going to return result between 00000 from 999999. It's always return six digit value. Let's see how we can use this format practically. First, I'm going to change the hour in our parameter. I'm going to use nine hour. And then I'm going to execute this code. Here you can see it's written 09 hour, but there is no zero before the nine. If you want to return result without zero, in that case, you need to use Z. If I execute this code, no with R, I want to show seconds, not the minutes. So I'm going to use percentis smalls. If I execute this code, you can see the result. First, it return nine hour, then it return 33 second. Using time format function, you can return any kind of time format result. If you want to show minute first, yes, you can. Just you need to pass percentis I. If I execute this code, you can see the result. First, it return 32 minutes, nine hour, 33 second. If you want to print any separator, yes, you can, you need to pass hyphen between these formats. If I execute this code, you can see the result. You can use dot here slash, et cetera. Once again, I'm going to change our value, and I'm going to pass 14 here. Now I want to show Midiam with this time, I want to type percents P. If I execute this code, you can see the result. As you know, ater 12 hour, it's written pm. That's why for 14 hour, it's written Pm. Basically, we use this function to improve our readibility. Our next function is time to s s to type, time to second. Here we need to pass a time and it's going to convert this time into a second. If I pass this time and then run the sd, here you can see it calculate and return the seconds of this time. Similarly, we have opposite function related to this function, which is sec to time. Let me show you. Say two time. As a parameter, we need to pass second and it's going to return the time. I'm going to pass 47,234. If I execute this code, here you can see it's returned 13 hours, 7 minutes and 14 seconds. Using this function, we can convert seconds into a time and also we can convert time into a second. I hope now the time function concept is clear for you. Thanks for watching this video, stay tuned for our next tudio. 43. MySQL ALTER Tutorial : Hello, guys. Good to see you back. In this tutorial, we are going to learn mysquel alter command. Here you can see a tbl named student. And in this table, we have total for student. And also we have to do three column in this table name, age, and city. Now I want to do some modify in this table. Suppose I want to add a new column in the existing table. Suppose we want to add ender column in this table. Otherwise, I want to change the datatype of H column. Now our H column datatype is wegear, but I want to change the data type, gear to in. This is one kind of modification. Otherwise, you want to reorder your column name. You want to move City column first of the order and name column last of the order, and name column last of the order, something like that. The reordering is also one kind of modification. Also if you want to change any column name, otherwise, you want to change TewameF all type of modification, we have a command. We call this command alter command. Using alter command, we can change anything in our table. Let's see what can we do using alter command. At first, we can add a new column in a table. If you want to add a new column in existing table, in that case, you need to use alter command. Also you can change datatype of a column. If you want to convert in datatype to reca datatype, in that case, you can use alter command. Not only that, you can change column name also. Suppose our existing column name is just name, and I want to replace this name with student name. In that case, you can use this command. If you want to add any restriction in your existing column, in that case, also you can use this command. So you can add constraint to a column, and also you can change the column position. You can delete the column and at last using this alter command, you can change tblname. But for all type of modification, we have different syntax, and we need to use it with alter command. Let me show you. Suppose you want to add a new column in a table. In that case, you need to type alter table, table name, and then you need to type a keyword, which is add, then your column name and your column data type. And also you can add some constraint with that. For a new column in a table, you need to use at Keyword with alter command. And if you want to modify the column, you need to type alter TaviTwname. Then you need to type modify keyword. Similarly, column name and data type. If you want to delete the column, just you need to type alter TaviTwname drop column. You need to type drop column keyword, and then you need to provide the column name. Similarly, if you want to rename the column, you need to type alter Tevin name. You need to use change keyword. At first, you need to pass your existing column name and then you need to pass your new columname also you need to pass the data type of this column. If you want to rename your Tevin name, in that case, you need to type altered Taviname and you need to use Rename Ker, and then you need to type your new Taviname. This is the introduction video of alter Keyword. From the next tutorial, we are going to start the practical. So thanks for watching this video, stay tuned for our next tutorial. 44. MySQL ALTER Tutorial part 2 : Hello, guys. Good to see you back. As you can see, I already open Zam Control Panel and also I start Apache and MySQL. As you can see, I already established my connection. You can see in your left side there is a database named students, and inside this database, we have to two tables, city and student. As you can see, inside this student table, we have total five students Advanced meth, Sophia Neha, and Mia. With that, we have total three column ID, name, and DOB means date of birth. And now I want to add another column in this table. For that, I'm going to use alter command. Let me show you how. So first, I'm going to type alter table, AL PR table. And then you need to provide the tblame and our tb name is student, and then you need to use a keyword, which is add a DLD. Why we need to create a new column in a table? In that case, we need to use Adkeword and now we need to provide the new column name, and our column name is email. And for email, I'm going to take recat data type, and I'm going to take total 200 character for email. Let's execute the code and see is it worked properly or not. As you can see, it executes successfully. If I show you my table and rewrote my table, as you can see, there is a new column named email. You can create new column using alter command. Not only that, you can reorder this column. Not only that, you can reorder this column also. Suppose you want to show the column after name and before the DOB. In that case, you can use alter command also. Let me show you how. It's mean I want to modify my email column. In that case, we need to type alter table name. Here we need to use modify keyword. Modify. After modify, we need to provide the column name. Means which column we want to modify. In our case, I want to modify email. Also, I'm going to use same data type for this column. Then we need to use another keyword, which is after. Here I'm going to tie Utter. Next, we need to provide the column name. After which column, you want to put this email column. As you can see, our column name is name. I want to type name here. Let's execute the code and see is it worked properly or not. If I execute this code and show you my table and reload my table, here you can see the email column after name column. Not only that, also we can change the datatype of any column. Suppose I want to change the datatype of email column. In that case, you can use alter command also. If I show you the datatype of this column, as you can see, Email column datatype is Werker. I'm going to change the datatype of this column. Now I want to convert this Ca datatype into It data type. For that, we need to type alter table student, modify our column name email. For now, we don't need these lines. And here we need to type the data type, which is Int. Then we need to pass it to that character, how much character we want for our column. I'm going to pass 20 character. So if I execute this code and show you my table structure, here you can see our email data dive is int. So as you can see, we successfully convert our column data dive using alter command. Now I want to add some constant in this column. I want to say, for this column, I want unique data. When I create the column, we forgot to declare the constant. So we can add constant after creating the column using alter command. For that, we need to type alter our name student. Then we need to use Atord AD. Now we need to pass the constant name. I want to use unique constants. I want to type unique. Unique in the parenthesis, we need to provide the columnim and our columnm is email. Let's execute the code and see is it worked properly or not. As you can see, our query runs successfully. If I show you my t properties once again, let me show you. As you can see, there is a tick mark on Kunqu column. It's mean every time we need to pass unique value for this column. We cannot use duplicate value for this column. Using similar process, you can make this column primary key, you need to type primary key here. Now I want to change the column name, as you can see, our column name is email and I want to rename this column and I want to make it email ID. For that, we need to use change keyword. Let me show you alter table student, we need to use change keyword. Change our old column, which is email, and then we need to pass our new column, which is email ID, email underscore ID, and also need to pass the data type, which is ire. Sum root type, ire. For email ID, I'm going to use 200 character. If I execute this code and show you my table as you can see, we successfully change the column name and you can see our new column name is email ID. Not only that, also we change the datatype of this column. Let me show you. As you can see, now the datatype is we care, and it take 200 character. Not only that using altered command, we can delete any column. Let me show you how. For this, we need to type alter tawl our Tavime. As you can see, our Tbilim is student, and then we need to type drop column command, drop column. And then we need to pass the column name and our column name is email ID. Under sce I. If I execute this code, it's going to remove the column from this table. Let's execute the code. If I show you my table and rewrote my table, as you can see, there is no column named email ID. This is it for this tutorial. In the next tutorial, we are going to learn how can we modify tables, remove tabs or change Taviname using alter command. Thanks for watching this video, Stay tuned for our next tutorial. 45. MySQL DROP & TRUNCATE Table Tutorial : Hello guys. Welcome back. Once again, I'm back with a new tutorial related mySQL, and in this tutorial, you are going to learn to new command, drop and truncate. We use this two command for same job. They are mainly used to delete data from the table. Suppose we have a table named student Also you can see we have multiple data in our table. If you use drop command to delete data, in that case, it remove all the data with the table. I want to say it's going to delete the table from the database. It remove all the data, all the columns and the table also. But if I talk about truncate command, then it's not going to remove complete tbl from this database. This command going to remove all the data from the table, not the complete table. But as you can see, Columns name and their database still remain. This is the basic difference between these two command, drop and truncate. Let's talk about the syntax. If you want to use drop command, then you need to type drop table and the table name. If you want to use truncate command, in that case, you need to type truncate table, table name. So without wasting your time, let's start the practical and see how it's worked. So as you can see, I already opened my Zem control panel and I start Apache and Mccule. As you can see I already established my connection. As you can see in your left side, we have a database named students. And in this database, we have to two tables, city and student. You can see in our city table we have total four Reco in our student table, we have total five lacO. So first, I'm going to apply truncate command in our city table. I'm going to tie truncate truncate table, and our table is city. I'm going to type city. Then semiconm to the line. As you can see in our city table, we have total four reco. If I execute this code, and show you the city table. As you can see, there is no record. It's delete all the record from the table. But if you notice you can see it do not remove our table completely. You can see the columns name CID city names. Our truncate command delete only the data from this table. This is the usage of truncate command. Now let's talk about our next command, which is Brom. I'm going to run this command in our student table. Here I'm going to type drop table, our table name. As you can see, our table name is student sum type student. Then semicoron two this line. This command going to remove everything from this table. Also the tablem from the database. Here you can see the table lem in our database. But if I execute this core, as you can see, it will our table from our database. This is the us Kaj of drop table command. It's going to remove all the data and the table from our database. But if we use the truncate command, it remove only the data from the table, but it remains the tbl structure as it is. This is it for this tutorial. Thanks for watching this video, Stu for our next tutorial. 46. MySQL VIEW Tutorial: Hello, guys. Good to see you back. Once again, I'm back with a new tutorial related M Sequel. In this tutorial, we are going to learn what is view in my sequel. Here you can see, we have to two table, Student table and city table. Now I want to join this two table and extract some combined data, something like that. Here you can see the city names. For that, I use inner Join. We already learned about inner join, left join, right join in our previous tutorials. When we use inner joins, it made our query very heavier. According to this example, if I type this equal query, let me show you, it looks something like that. Clet Column strong student, inner Join city on student dot city equal to city dot CID. This is our first table name student, and I want to join with City Table. I use Ina Join City. And then we need to join with primary key with foreign key. I know you already know this process. Suppose now I want to add more than one table using Join. In that case, it's going to make our query very complex, and maybe we had to use this query again and again. And I don't think it would be a great option to type our query again and again. It's just waste your time, nothing else. For solution, you can set this command in your notepD file. And whenever we need the command, we need to copy the command from the notepP file and we can use it as a query. But there is another solution, which is provided by Msignal. We can set this query in our database. And whenever we need to use this query, we can use it using view command. If we use this process, we don't need to type this query again and again. This is the usage of view command. We can save and use our complex command using view. Now, let's talk about the syntax of view command. First, we need to type create view, and then you need to put your view name. At first, you need to use this keyword, create view, and then you need to take a view name, next, we need to use Aqword after SQWord, you need to put your exact query whom you want to set as view. Now using this view Name, whenever we want to run this command, we can run and Dt save our time. If you want to modify your view command, yes, you can, you need to use alter command and we learn about alter command in our previous tutorials. If we don't need this command in future, then also we can delete it. Using drop command, we can delete it. Let's moving forward and start the practical. So finally, we are in MySQL Varage application. And here you can see, we have to dual three table in our database, city, course and student. In our city table, we have to dual four city. And in our course section, we have to dull four course. And in our student table, we have to dual five student. Here you can see a columnm courses, and here you can see the course IDs, two, three, one, four, one. These are all came from course table. Now I want to join course table and student table and return a new table, and I want to extract their ID, name, and their course name. For that, we need to type SeqL inner join command. I create a new tab and here I'm going to type, so I'm going to type, select ID. With ID, I'm going to take NaN. With name, I want to show the course name, as you can see, our column name is course name. So here I'm going to type course underscoe name. And then we need to use from keyword, from our TV and our TV name is to date. And also, I'm going to take a Allis name, which is, and I'm going to use inner join for that. And I want to join with course table in. Also, I'm going to take Age them for that C. After that, we need to use OKed on. Now we have to select from here we have to join these two tables. If I show you my student table, as you can see, here you can see a columnam Curse means student course. Here I'm going to type our Ais them as dot course. Equal to, and now we need to attach it Cable means cours table. If I go back to the course table, as you can see, there is a column name course ID, CO ID. Here, we need to tie c.co ID. Here you can see we create our JOIN. If I execute this code, here you can see the result. First, it written ID, meme and course name. Here you can see it's written our result, ID, meme and course ename. Our joN works perfectly. I want to create a view of this code. For that, we need to use create view command. Here I'm going to type create view. Now we need to put a viewin and our view name is student data. Underscore data. After that, we need to use another keyword which is as as after SQ word, just need to type the command, nothing else. To create a view, first, we need to type, create view command, then we need to take a view Name. Next, we need to use SQWord after SQWord. We need to put our complex command. Let's execute the command. I want to click this icon. As you can see, it execute our Cav one. And now we need to refresh our schema section. If you click on this view dot down, icon, as you can see, there is a view named student data. Whenever we need to call the view, we don't need to write this much of code. For that, we need to type select star from our un. Select star from, and our view name is student data. So if I execute only this command, as you can see, it's written the result. It's written the ID and name from student table and course them from courses table. I hope now it's clear for you, what is the usage of view? You don't need to type multiple lineup code every time. Just create a view and you can use it again and again and it permanently our view section. This is it for this tutorial. In the next tutorial, we are going to learn how we can change this view. So thanks for watching this video, stay tuned for our next tutorial. 47. MySQL VIEW EDIT Tutorial : Hello, guys. Good to see you back. Once again, we are in my War wedge application. In this tutorial, we are going to learn how we can edit view. If you want to edit the view, you need to use Alter command. Here I'm going to type Alter. Alter view after alter view, we need to pass our view in, which is student underscoe data. Now you are able to change anything in your command. As you can see in my student table, there is another column named Sit means student sitting. Also you can see the tab lem city. We use the primary ID of CDD V in our student table. Now I want to create another join with a city column and CID column. For that, I'm going to create another inner join. Let me show. Here I'm going to type inner join inner join our city. Also, I'm going to take Ai's Name for our CD table, which is CitY. As you know, then we need to use QordO after all, we need to call our City column from our student table, which is t. Our student Ai name is H t. I'm going to assign with City table primary ID, which is CID. Here I'm going to type City dot CID. So basically we join two tape, one from City column from student table and another is CID column from CD tape. And as you know, this is our existing view, we just made changes using alter view command. Let's execute the code. To execute this code, I'm going to click this button. As you can see, execute code perfectly, and now I want to call this view table again. For that, we need to execute select star from our uname. And now I want to call our view again. For that, we need to execute this command. So here, I'm going to execute this command. As you can see, it's written our table, but it do not return the cityym because we do not select our cityym. As you can see, our column name is cityym. Here, we need to type city Name. Now before execute this let command, we need to execute alter view command again because we need to set this view. I want to execute this command, and now I'm going to execute this let command. If I execute this let command, as you can see, with OsnM is sit and CDM also. In that way, you can change your view command. Just you need to use alter view command. But there is another command that you can use for modifying purpose, which is create or place. Let me show you. You can use this command. Create or place. Create a re plus view your view and you can use this command in a similar way. But I would like to go with alter command. This is your choice. It's up to you which command you are going to use. This is it for this tutorial. In our next Tutorial, we are going to learn how we can rename our existing view. Thanks for watching this tutorial. Stay tuned for our next. 48. MySQL INDEX Tutorial intro : Hello, guys. Good to see you back. Once again, I'm back with a new tutorial related my sequel. In this tutorial, we are going to learn index, my sequel index. As you can see, we have a index page. Maybe you seeing this kind of index page in your book. Using Index page, we can navigate the chapters. Basically index page come with chapter name, and chapter page number. Suppose you want to dip dive into Chapter three, here you can see the chapter name, in our case, EFG. If you want to open this chapter, then you need to open page number 23. The conclusion is basically index page, increase the search speed. So the same way, when we need to increase the searching speed in our database, we use index. Here you can see a table which contain name, age and ZenderT is a student table. Most of the time we need to search student by their name. So every time we need to pass the name to search the result. But to resolve it, we can use index. For that, we need to use special command, which is index command. Let's see the syntax how we can use index command in McQuil. This is the syntax of index command. First, we need to type, create index. Then we need to provide the index name. After that we need to use on Gord, then we need to pass the tabame. Then inside the round recess you need to pass those column name. Which column you want to put in this index. And most of the time we put those columns, which is used for searching. So this is how we can create this index to this particular column, and it's going to increase our searching speed. And this indexing remain permanently safe in our server. Just you need to create the index one time for this column. And when someone search in this column using index, it's become so fast. And if you want to delete this index in future, for that you need to use drop command. Drop index, you need to pass the index name, s you need to pass the Daviname. This is how you can delete this index. Now let me show you some guidelines. Whenever we try to create this index, we need to follow those guidelines. The first guideline is automatically creates the index for primary key and unique columns. It's mean whenever you try to create table and make one column primary key and make another column unique, I want to say, if you use unique constant, then don't put this column in index. Because those who have primary key and unique columns, you can search those results very fast. Then you don't need index for these columns, by default, it's automated key included in index, and our second rule is index columns that you frequently use to retrieve the data. Always use those columns where you need to perform most of these searching. Third rules index column that are used for joins to improve joint performance. Basically, we create index when we join three or four table at once because whenever we joins multiple table, it's become so heavier. That's why we need first searching method and that index can provide us in our fourth coin, we need to avoid those column which contain null values. And our last rule is small tables do not require indexes. If you have 300 400 data in your table, then you don't need to use indexes. You need to use indexes when you have thousands of data because small tables are useless because it will provide you quick results already. Without wasting your time, let's study practical and see how we can use index.