MySQL Bootcamp: Go from SQL Beginner to Expert (2022) | Jayanta Sarkar | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

MySQL Bootcamp: Go from SQL Beginner to Expert (2022)

teacher avatar Jayanta Sarkar

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

51 Lessons (5h 39m)
    • 1. Introduction

      3:01
    • 2. What Is My Sql

      7:14
    • 3. Mysql Installation & Workbench Tutorial

      10:40
    • 4. Mysql Create Table Tutorial

      10:25
    • 5. Mysql Insert Tutorial

      4:58
    • 6. Mysql Insert Multiple Rows

      5:27
    • 7. Mysql Constraints Tutorial

      13:14
    • 8. Mysql Select With Where Clause Tutorial

      9:19
    • 9. Mysql And, Or, Not Operators

      8:58
    • 10. Mysql In Operator

      5:48
    • 11. Mysql Between & Not Between Operator Intro

      1:53
    • 12. Mysql Between & Not Between Operator

      4:40
    • 13. Mysql Like Operator Intro

      3:08
    • 14. Mysql Like Operator & Wildcards Tutorial

      6:32
    • 15. MySQL Regular Expression Tutorial

      3:41
    • 16. Mysql Regular Expression Tutorial part 2

      8:20
    • 17. Mysql Order By Intro

      2:29
    • 18. Mysql Order By

      3:03
    • 19. Distinct Intro

      1:00
    • 20. Mysql Order By & Distinct

      1:30
    • 21. Mysql Is Null & Is Not Null

      3:12
    • 22. Limit & Offset Tutorial

      6:07
    • 23. Mysql Count Sum Min Max Avg Tutorial

      6:12
    • 24. Mysql Update

      6:40
    • 25. Mysql Commit & Rollback

      2:26
    • 26. Mysql Commit & Rollback Part ll

      5:58
    • 27. Mysql Delete

      4:30
    • 28. Mysql Primary Key & Foreign Key

      13:44
    • 29. Mysql Inner Join Tutorial

      8:58
    • 30. MySQL LEFT JOIN

      10:26
    • 31. MySQL RIGHT JOIN

      2:12
    • 32. Mysql Cross Join Tutorial

      3:48
    • 33. Mysql Join Multiple Tables

      8:11
    • 34. Mysql Group By & Having Clause Tutorial

      9:35
    • 35. Mysql Subquery With Exists & Not Exists

      9:02
    • 36. Mysql Union & Union All

      11:00
    • 37. Mysql If Statement

      5:18
    • 38. Mysql Case Statement

      8:40
    • 39. Mysql Arithmetic Functions Intro

      3:24
    • 40. Mysql Arithmetic Functions

      11:11
    • 41. Mysql String Functions Part 1

      10:20
    • 42. Mysql String Functions Part 2

      11:35
    • 43. Mysql String Functions Part 3

      6:46
    • 44. Mysql Date Functions Part 1

      7:13
    • 45. Mysql Date Functions Part 2

      7:48
    • 46. MySQL Date Functions Part 3

      12:54
    • 47. MySQL Time Functions Tutorial

      5:09
    • 48. MySQL Time Functions Tutorial Part 2

      7:56
    • 49. MySQL ALTER Tutorial

      3:37
    • 50. MySQL ALTER Tutorial part 2

      6:35
    • 51. MySQL DROP & TRUNCATE Table Tutorial

      3:24
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

25

Students

--

Projects

About This Class

If you want to learn how to gain insights from data but are too intimidated by databases to know where to start, then this course is for you. This course is a gentle but comprehensive introduction to MySQL, one of the most highly in-demand skills in the business sector today. 

Whether you work in sales or marketing, run your own company, or want to build your own apps or websites, mastering MySQL is crucial to answering complex business problems and questions using insights from data.

Why Learn MySQL

Consistently ranked the most in-demand skill in recent employer surveys, SQL is a fantastic way to increase your income and boost your professional development. So many companies today use MySQL, including Twitter, Uber, Airbnb, Dropbox, Github, Kickstarter, Udemy, Slack, and many others. Unsure about the difference between SQL and MySQL? MySQL is the most popular open-source SQL database out there, so it’s a great choice to begin your learning journey. We’ll talk a lot more about the difference between SQL and MySQL in the course, but 95% of what you learn about MySQL in this course will apply to other databases such as Postgres, Oracle, and Microsoft SQL Server.

What Can SQL do?

  • SQL can execute queries against a database

  • SQL can retrieve data from a database

  • SQL can insert records in a database

  • SQL can update records in a database

  • SQL can delete records from a database

  • SQL can create new databases

  • SQL can create new tables in a database

  • SQL can create stored procedures in a database

  • SQL can create views in a database

  • SQL can set permissions on tables, procedures, and views

Meet Your Teacher

Hello, I'm Jayanta.

After graduating and holding a degree in "information technology," my professional career began, starting web development and graphics design career in 2018, I never stopped learning new programming skills .

I  learn web development and graphic design skills in Arena Animation. But my most of the skills are self-taught skills. As a self-taught developer and graphics designer, I had the chance to work with meaning of local IT companies to build their websites and train their employee on web programming.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Welcome to MySQL bootcamp course. Hey there, my name is joint a short code. Professionally, I'm full-stack web developer and online instructor. I have been involved in this profession almost five years. I have been working as an employee and also financial MySQL. Sometimes we pronounce MySQL. It is the most popular open-source database in the world. It is used by various think tanks, Googles, Apple, Amazon, etc. Over the couple of year, people say everyone should learn to code. But nowadays, everyone should learn to secure it. Whether you are iOS developer, Android developer, game developer and a web developer. And if you are in sales and marketing, either run your own company. Learning's is a great addition for your portfolio. So hey everyone, before we start this course, deci, what we are going to learn from this course. In the Fosdick, I'll give you the idea what is relational database and then we will see how to install MySQL in our computer. Next, we're getting familiar with MySQL or which application, which is a graphical user interface that we use to run our SQL queries. And then I'm going to teach you how to get user and database. With that, I'm going to teach you how to create tables, and then I'm going to teach you how to insert multiple data, a table. In the next part, we are going to learn about constraints. Also, we are going to learn about the select command with her boss. And in the third part, we are going to learn about all the operators and operator or a burden NOT operator in operator, etc. With that, we are going to learn while camps in the fourth stage, we are going to learn regular expressions. Water by n, distinct loss. He is now and not null and many more. And then we're going to learn aggregate functions. And also you are going to learn about update command, Delete command, commit, and rollback. And then we are when jump into the most important part of our course, relational database. In this section we are going to learn primary key and foreign key concepts. And then we are going to work with joins, lead to n, right, Jen cross, join in object, etc. And we're also going to learn how can we try and more than a two tables at once. And then we're going to learn groupby and having clause. And also we are going to working with subqueries with exist and not exist. And then we are going to learn union and union all. In the next part, we are going to learn conditions. If n gets statement eight many functions, string function, det, function, time function as a drought. In the last part of this video, we are going to learn. Come on. How can we use some drop and truncate command as a drought. They know you work with views indexes, how we can export data and import detect using MySQL workbench application and many more. This class is basically create for completely beginner or intermediate students. If you don't have any knowledge about databases, then also you can join this course. After end of this course, you are able to create concrete library maintenance system, completes today's management system and many more. This course will be very good addition for your portfolio. So let's get started. 2. What Is My Sql: First of all, what is MySQL? Mysql is a database management system. Basically, it's a software which can manage data. Now the question is, what is database? A database is a collection of data stored in a format that can easily be accessed. Let me show you the real life example, how we use database in actual life. You can see a form on your screen. You have seen such firm on many websites like bank application, flight ticket applications, college or school applications. So the information we get from hair will be stored in a database. As you can see here. It's saved in a table form it, and we called it collection of data. The advantage of formatting in this way is that we can read it easily. It's look like an Excel format. So we can read it. Very computability. Named Edwin, age 23, gender main. It's easily readable. It's easily updateable. When we store information in an organized way, we call it a database. Let's know about MySQL. As I told you, MySQL is the database management system. It's a kind of software which helping us to manage data. Let's see. We work with database. Suppose we have a computer and so on feeler from, and try to save data in a database. So he cannot save it directly to the database. So he needs to stop there, which call DBMS, its mean database management system. The information from our client side goes to DBMS first. Then this information will be stored in the database. And whenever we want to see the information from the database to our computer. And once again, it split the ROLLUP mediator. Basically, I want to say, when do we need to communicate between client-side and server-side? We need DBMS. There are many popular DBMS software in the world. Oracle is on top of all of them, and then come MySQL. Mysql is Waltz again, most popular DBMS system. There are also many more, such as MySQL Server, postgres, SQL, MongoDB. So now it's clear for you what is DBMS? Let's talk about databases. We have two types of databases, relational and NoSQL. Let's talk about relational databases. Whatever is in the relational database is usually saved in table. Here you can see a database model that is based on movie ratings. Means if you want to create a movie rating website, then you need to create this kind of database. First you can see here a rating stable. Also you can see two more tables here. One is the user and other is the movie. And here you can see each table is attach each other. So these why we call it relational database, hair, I just want to explain you what is a relational database? Let's talk about our second topic. Our relational database. Use their language to communicate with the database. And we know it as SQL or SQL, whatever sequence mean Structured Query Language. Let's see how CQL works with our database. We first notice that whenever we need to save something from our client side 2D database, we need a database management system. And if our database is a relational database, then we need RDBMS sloped here. And when we work with RDBMS, and then we need to know a special language named sequel or SQL. Using these languages, we can add data, delete data, update data, etc. And now it's clear to you MySQL is RDBMS. Already. We know that there are two kinds of databases, relational and NoSQL. And let's talk about NoSQL databases. In this database is we do not use tables to set data. It save our data in a document basis. In the world, there are many NoSQL databases, mongodb, readies, etc. As you already know. In this course, we will just learn about MySQL. Let's talk about the advantages of MySQL. Our first advantage is it's a cross-platform RDBMS. It's mean we can use it Windows computer also, we can use it Linux computer or Mac computer. And our next advantage is we can use it with any popular languages like PHP, NodeJS, Python, C-Sharp, etc. And our third advantages. It's an open source software, so we don't need to pay anything. Fourth advantage is MySQL is relational database, so we can access multiple tables using online up command. And the advantages, it is very fast, reliable, scalable, and very easy to use its mean. We can use it very small websites. And also we can use it in a big websites like Facebook. So you already know the benefits of using MySQL. Let's find out, while most popular websites which use MySQL to store data, Facebook games first on our list. As you know, it's all biggest social networking website. And then scum tutor. This is the worse again, most so sudden networking website and ETUs MySQL to store data. And then scum, Google, the internet giant, Google uses MySQL in its various projects. Dense come Wikipedia and YouTube. And there are also many popular CMS systems that use MySQL, such as the World Number 1 Themis system, what Press, and there are many more like Joomla, good poll, Magento, etc. Finally, let's see what we can learn in this course. First, we are going to learn how we can create databases and how we can create tables in databases. And then we are going to learn how we can add data in our databases. And then we are going to learn how we can update data in our databases. And then we are going to learn how can we read data in our databases and delete. Also, not only that, we are going to clear primary key, foreign key concepts, and also we are going to clear inner join, left, join, cross join, and many more SQL commands. I hope my course will help you to learn MySQL. In the next video, we are going to learn how we can install MySQL in our computer. Thanks for watching this video. It's a completely beginner friendly quotes. 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. So your first IV is you need to download one of these three servers on your computer, XAMPP. When our ma'am, In this video, we are working with XAMPP application. Let's see how we can download R installed in our machine. Just search, downloads them in your browser, then click on the official link. This link will redirect you to download page. And Harry, you can see the download of transport three type of OS. If you are a Windows user, then go for Windows. And if you are a Linux user, then go for Linux. And it is also available in Mac version. I am a Windows user, so I'm going to download Windows version. So I'm going to click this link and you can see our downward has started. My Internet connection is not very fast. So I'm going to pause this video. If we install XAMPP application, then MySQL will be automatically installed with it, and it is also installed another web application name, PHP, my admin. Not only that, we are going to use third-party application, MySQL Workbench. This is MySQL's corporations own graphical tool. Here too, we can run the command of MySQL, and we use this tool in this course. Finally, our download is complete. Let's installed XAMPP application in this computer. It's take 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 MySQL and Apache are selected or not selected. Then click on Next. Then you need to choose the folder where you want to install them. And I want to go eat default block. 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, it's taking time up to complete the installation process. You can see a lot on your screen. You need to allow your Apache server communicate we private or public network. So just click on Allow x's and then just click on Finish. And also I'm going to run these M server. You can see the zoom control panel on your screen. First you need to start Apache and MySQL. So I'm going to click on Start button. You have to wait a little until its color is green. And once again, it asked for network permission. Just click on Allow x's. Now your MySQL 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 localhost on your URL bar. And you just redirect to Ende des bowl. As we say, we will use another application to run MySQL. Just open a new tab and type MySQL.com. So before I download this application, let me introduce what is PHP, my admin. So just back to the local host and click on PHP, my admin. You can see here that the control panel of our PHP my admin is open. It basically a web-based tool. And Harry, you can practice your MySQL. You can see these on the left side. These are our database. If we click on any of these, we can see that table inside it. And if you want to create a new database, then just click on Database. Uh, from will open in front of you, create database and you can put here whatever name you want. So I'm going to create a new database name taste too. And then just click on Create. You can see on your lived, a new database has been created named Test2, but we have not made any tables yet. There is another way to create a database. For these. You need to click on Home icon. Then you need to click on CQL link. And here you can create a database using the SQL command. Just type. Create database and then type your database name. And our database name is test three and use semicolon to end this line. Remember, is 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 SQL command and create a new database test 3. This is the first way where we can practice MySQL. If you do not want to use any third party application, then it would be a great option for you. But in this tutorial series, we are going to work with a third-party application, which is MySQL Workbench. So 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, zan. So let's back to our second tab. Then click on second link, Download. Then scroll your page a little bit. Then you can see an link MySQL community downloads. Then a page will open in front of you where you can see all the software, MySQL. But here we need to install MySQL Workbench only. So if I click on this link, disruptor or arcs are just like PHP, my admin, but its user interface is very easy to operate. Then just click on the drop-down link and select your operating system. I am Windows user, so I select Windows and then just click the Download button. Then it asks for login or sign up. But you don't need to sign up for download this application. Just click on. No, thanks. Just 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 in default option. So then I click Next button once again. Then you can see two option, complete or custom. And I would like to go eat complete. Then click Next. Then your installation process will start it stick little time. So finally, our installation process is complete. So first you need to create a connection. So creating a new connection. You need to click this plus icon. And this will open a new window, something like this. Setup new connection. First you need to create a connection name. And our connection name is demo. Yes, you can choose your own connection name. And then scum connection method. You don't need to change it. Just keep it as 80s. Similarly, you don't need to change your host name. And port 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. Xampp server came with no password. And if you want, you can use password. And I don't want to use any password. So I'm going to leave it blank and then just leave the default schema and press okay. Then you can see it's created a new connection name demo. And then just double-click on your connection. 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 any way. Then an editor will open in front of you. So this is your CQL 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 sequel editor. Here you can type your SQL command. Just type your SQL command, and click on tundra icon. And it will run your command. And if you want to delete your old database, then just select your database, right-click on it and just click on drop schema. They need ask for review SQL and drop now. And you can see it delete our tastes three database. Similarly, we can delete test to database. Let's create a new database name student using a SQL command. So I'm going to type create database student and use semicolon to end this line. And then I'm going to click this icon. If the green sign came here, then you think your code has worked properly. And if the color is rate, then thing that your command was not successful. As you know, our database is created, but we can still see her because we just refresh our schema. Then you can see your new database name. And if I drop down this student database, you can see some options like tables, views, functions, etc. From the next video, we will start learning SQL command. Thanks for watching this video. 4. Mysql Create Table Tutorial: Nice to see you back guys. In this course, we are going to learn how can we create table in our database using SQL command. In the previous video, we learned how can we create database using MySQL command. After creating the database, our first task is to create the table. And we save this kind of data in our table. You can see a table on your screen. And it has three column, name, age and gender. And you can see the multiple records in this table. So our first job is to create a table in our database. And we need three things to create a table. First, our table name. We can create multiple tables in one database and we need to take unique name for every table. And our second thing is we need to take columns name for our table. As you can see, I have three column in my table, name, age and gender. And our third thing is colon datatype. Its mean, which kind of data I want to save in my column. As you can see, for name or need to take string datatype. And for age, we need to take numeric datatype. And for gender, we need to use string datatype. Before creating a table, we need to use this thing. Dana, you create a table using a particular command. Let's see the command. It says the curl command, and you can use it any type of security database. First, we need to use two keyword create table. Then you need to type your table name and it's totally on you. Which name you one, just one thing. Remember, you cannot use pace for your table name. You can use camelCase or underscore. Dense come our round braces. Inside the braces, you can take your column how much you want. First we need to type our column name, and then we need to take column datatype. And then you need to use comma and you can create your second column. I hope you got the point how we can create table using a SQL command. And now I want to talk about some basic data types. In MySQL. There are three main data types. String, numeric, date, and time. No matter what kind of data we gave, it should be related to one of these. There are lots of datatype in his quill. And I'm going to talk about some basic data types. Let's start to eat string datatype. These are very basic datatypes which is related to string datatype. Our first data type is char, and need to have some limitation. We can use only 0 to 255 character, then scum their care. Most of the time we use their care. And we can use character between 0 to 65535. And our third data type is text datatype. It's calculate our data by bytes. And we can use only 65,535 bytes of data. And our nest data viz by 90 datatype. It stored binary byte stream. The size parameter specify the column length in bytes and its default value is one. Let's talk about numeric datatypes. Our first datatype is bit datatype. The datatype can hold a value from one to 64. And our next data type is integer. And it's defend on in size. And our third and most common data type is int datatype. Its range is from minus 214748364822147483647. And then scum date and time data types and each support one hundred, ten hundred year, 29,999 year. Especially it has five datatypes. Bit did time, timestamp, time and year. In did datatype, we can set data year, month, did form it dense come datetime datatype. And our LAS data type format is year. And we can see here from 1900 one to 2155. So these are the basic data types we used in our SQL command. There are also many data types that we do not use very often. Let's see in practical example that will clear your doubts. Hello, I'm back to my MySQL workbench application. But first, we need to start our Zim server. So I already opened my eyes them control panel. So first I'm going to start. Apache, and then I'm going to start MySQL server. So first we need to create our connection. So I hover 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. So right-click on your database and click on Set as Default schema. And now you are able to create tables in this database. And you can use another method to choose your database. Just unique to type as simple SQL command. Just type, use. Then type your database name student, semicolon to end this line and click on the flesh icon to execute this code. Now your database is selected and ready to use. Now we can create tables in this database. And you can see in our schema section, there is no table in our database. Now, we're going to create a table. Let's type our command to create a table. Create table. Sql is not case sensitive, so we can use both type of litter, capital or small. And our table limeys persona. Then we start our parenthesis. And obviously we need to use semicolon to in this line. So inside this round braces, we need to take our columns name. Our first column is ID, and it is datatype is InDesign. So I type INT. Then I use comma. And I'm going to type or second column name, which is name. And IVs where care. 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, the date, and our datatype is date. And our next column is phone number. And it's dated IVs, where care. And I would like to give 14 character for our phone number. And our last column is Zinder, which is also where care. And for gender, I would like to give one character, M for male, F for female. So our command is complete. We take five different coulombs for our table. And our table name is personal. And now I'm going to click the icon to execute my code. And you can see in my action outputs friction. It execute our code perfectly. I beg to Schemas section and click on Refresh icon. And you can see a drop-down sine integral section. And if I dropped down it, you can see our table, which was arsenal. If I select our table, you can see the columns name and their type. In the same way. If we click on the Table link, you can see some options. Columns, index foreign key. And if I click this columns lean, 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. So I'm going to type PID and its relative ease in teaser. And our second column is product name, and its derivative is worker. And we don't need the date of birth column. And for a company, I'm going to type P company means product company. And for product price, I'm going to type price and its dendrite IVs in dieser. Let's execute the code. You can see in my output section or commands run perfectly. And if our depress our schema, you can see now we have two tables in our database. First one is personal and second one is Porter. Unfortunately, there is no data in our table. In the next video, I will show you how can we insert data in our table. And if you want to see the table in graphical form, it 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 we click the table icon, it's run this SQL command, select star from student dot product. There is no information in our table. So you can't see anything. In the similar way. You can see the analyte table, just mouseover the taping limb and take a lichen. And you can see our columns name, ID names, but the phone gender. So I hope you can understand how we can create tables using a SQL command. So in the next video, we're going to insert data in our table using a SQL command. Thanks for watching this video. 5. Mysql Insert Tutorial: Hello and welcome back. In this video, we are going to learn how can we insert data in our table using insert SQL command. In the previous video, we learn how can we create these kinds of tables using CQL command. But in this video, we are going to learn how can we insert data in our table. When we insert data into a table, they know you need to use a SQL command. So first we need to type insert into, then we need to type our table name. According to the previous video. It may be personal or productive. Then inset the round rices. Then you need to provide the column names. Where do you want to save data? And we need to use comma between our column names. And then you need to use another keyword, values. Then inside the round versus who we need to provide our values. But you need to remember one thing. You have to arrange the values in the same way as you Aaron's the column names. I want to say if your column one is gender and column two is h, Then first need to provide Zinder value as V1. And then you need to provide edge view. It's completely up to you how you want to fork the column names. So without wasting your time, Let's get into the MySQL workbench application. And then I'm going to show you how we can insert data in our tables. But first, we need to start our XAMPP server. And first I'm going to start Apache, and then I'm going to start MySQL. And just wait for green signal. Now use server is ready for job. Then back to MySQL or Grinch application. And I'm going to start my connection, which is demo. So I'm going to double-click on it to host you see our old command with which we made our table. In the left side, you can see our database name, which is student. And inside the database, you can see our table's name. First one is personal, and second one is product. And now I want to save data in our personal table. And if I open our columns, you can see we have five different columns. Id, name, but they form and gender. And now I'm going to remove the old SQL command. And then I'm going to write our command insert into. Then we need to take our table name and our table name is personal. And then I'm going to take round braces. Inside the round braces, I'm going to take our column names. Our first column name is id, coma, or second column name is name, then birth, date. Or fourth column is phone, and our last column is Zinder. Then we need to insert value in our columns. So I'm going to use value skewer and I'm going to take around resist once again. Then I'm going to provide values sin water as column names for ID. I'm going to take one. It's saying deservedly. So I don't need this type, this value inside the quotation. Our next video is a string value for name. I'm going to type at one comma four birth date. I'm going to type. Similarly, I'm going to take inverted coats and our format is year, month and date. Year is 1995, month is 05 mins me and the deities three. And then we need to provide his phone number and our data viz, their care. And for number is triple 0, triple line AAA. Then we need to take value for Zinder. If you remember, we said limit for Zinder. For Zander, we can take only one character. So I'm going to pass capital M for main. So finally, our command is complete, and now I'm going to execute this command. So I'm going to press this standard icon. And you can see in my output section, the signal is green. It's mean it execute our command perfectly. Let me show you my tables. And I'm going to hover on my table's name. And once again you can see three dot icons. And I'm going to click on the Table icon. And you can see it's open out table. And also you can see the data ID1 named Edwin means phone number Zinder. Using this SQL command, you can add data one by one. But in the next video, we are going to learn how we can insert multiple row in our table. Thanks for watching this video. 6. Mysql Insert Multiple Rows: Hello friends, nice to see you back. It is our feed, the video in our tutorial series. In this video, you are going to learn how can we insert multiple rule using CQL command. In the previous video, I explained you, how can we insert multiple data in our table using CQL 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 SQL command for five time. First we need to execute for Edwin, 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 we want to add a row. It's a very lengthy process, but we can cover it in a single-step using these insert command. We can add multiple row at once. Let's see the syntax, how it's work. You can see on your screen, first we need to use insert command key, insert into our table name. And then we need to define the columns name. And we need to use another keyword values. Then you need to take around resistor for the information you want to add in your columns. And we need to follow the same order. And then you need to use coma. And similarly, you can take another row of data and you can add a new record. In this process, you can take multiples row of data. And at last, you need to use semicolon to end this command. So let's see the practical. How can we use insert command to add multiple ropes data? So I am back to my MySQL workbench application. And I also open them Control Panel. And before run the MySQL workbench application, we need to start XAMPP local host server. First I'm going to start a purchase Server, and then I'm going to start MySQL. And then I am waiting for green signal. Now it's ready for connection. Let's connect it. So I'm going to hover on my connection name demo and double-click on it. And you can see on your screen we can connect our connection perfectly. Inner schema section. You can see our database names. And also you can see my table, steam, personnel and product. Let's see what we have in our personality. So I'm going to hold on Table icon and click on it. And you can see that is one row of data, which we insert in our previous video. Name and date of birth, 1995. And gender is male. And now I'm going to add three records at once in this table using insert command. So I'm back to my query one section. First I'm going to type insert, cure, insert into our given limb, personal. And then I'm going to use round braces inside the dome verses. We need to type our columns name or first column is ID. Our second column is name, and our third column is, but underscore date, comma, or fourth column is for. And our fifth column is Zinder. And then I type another keyword values. In the next line, I'm going to take another round resists and we will insert the data the way we took the column name. So first I'm going to insert our ID value to the name Smith. Comma, date of birth 1991, October 6. And 4 phone number. I'm going to type some random numbers. And for gender, I'm going to type capital M. And do we need to use coma? Because we are going to add another rule. So I duplicate this line. And now I'm going to change the values. Id3, name and date of birth 1997, October 9. And 4 phone number. Once again, I'm going to type some random number. And for gender, I'm going to type capital. If. Once again I duplicate our previous line and I'm going to replace value for ID, I tie for, for name. I'm going to type ravine. And but there 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 for number and for gender. I type capital M. And at the end, we need to use semicolon to in this command. So our command is complete. Now it is ready for execute. Let's execute the command. So I'm going to hit calendar icon. You can see in my action output section, it's written green signal. Its mean, it's executed perfectly. And if I vector my personal table and run this command, once again, you can see the new data. So you see how we insert lot of data together. Thanks for watching this video. See you on the next day to deal. 7. Mysql Constraints Tutorial: Hello friends, nice to see you back. This is our sixth video related to MySQL. In this video, we are going to learn what is constraints in MySQL and how we use it. You can see we have a list of MySQL constraints and Herod, the green constraints that we will learn in this video. And there are two constraints lived named foreign key and primary key that we will cover in our upcoming videos. Now the question is, what is constraints, its mean, restrictions, 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, am I forgot to insert her gender in gender column because we do not use any restrictions. You can see Advani, 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 numbers would be unique. If you'll notice Sophia and add one, have the same phone number. At the end. Suppose our colleagues isn't really. So most of the students will came from Delhi. So every time you need to insert Delhi as city. So you want to do something like that. If we leave the name of the city blank, then it automatically insert Delhi as city. For all the problem, we have different constraints. So for ID and Zinder, we can use not null constraints. Similarly, we need to add the restriction for H column. For restriction, we have another constraint name CIC. First we need to type check fewer than inside the round resists. We need to set our condition. In our case, age greater than equal to 18. It's mean if student ages below then 18, he's not able to join 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 a unique constraint for our ID column. So for city, we can use default. We can set our default value. So 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. Where basically use it when we create the table. To create a new TV, we need to type create table, then our table name, then inside the round resists. And when you go to make a column for ID, first we need to declare a datatype. And then we use not null 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 all constant value. And for name or datatype is where care. Similarly, I don't want to leave it blank. So I use not null constraints. And for h, I'm going to use multiple constraints. Or data type is int post I use not null constraints, and then I use check constraints. And inside their own braces, we can set our condition. And for gender, we use varchar datatype. And I don't want to leave it blank. And for phone number, our Data Service Worker and I use two constraints, not null and unique. So do not copy student phone number in our database. So the same phone number cannot be used repeatedly. For our last column, city. We use two constraints, not null and default. So apes student leave their city name. It will automatically insert Billy as city. Then it grows out table using round braces and semicolon. So we use it this way. So let's see the practical how we can use it in our queries. So finally, I'm back to my MySQL workbench application. And they also open my Zim control panel. First I'm going to start my aperture server, and then I'm going to start MySQL and just wait for green signal. Now, our local host server is ready. So let's create the connection. First, I'm going to hover on my connection name and then double-click on it. And 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 two end. Our ID is five. And I do not change my student name and I forgot to insert Zinder will Let's run this command. So I'm going to press this standard icon and you can see the green signal in our x and outputs section. Lives back to the table. If I run this query, you can see my table, or Zinder is bank. 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 other new table. I'm going to delete this one. Delete table, just hover on your table name and right-click on it. And now you can see an option drop table. Now you can see a confirm box on your screen and just click on drop now. And I'm back to my query section and I'm going to create this empty will 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 limits personal. Then inside the round braces are first column name is ID, and it is detached IVs in deserve. And I don't want to leave it blank. So I use are first constraints NOT null. And I do not want to repeat this ID in our table. So I use unique constraint. And our next column name is name, and it is datatype is where care. And I said limit for 50 characters. And I don't want to leave this column blank. So I use not null constraint. And for age or data type is int. And I don't want to leave it blank. So I use not null constraint. And I wanted to set limit for student h. So I use check constraint. If age is below than 18, then he is not able to join this college. Coma. For gender. Our datatype is where care. And I use not null constraint. And for phone number or data viz, worker. And I use two constraints, not null and unique. And for city or datatype is where care. And I don't want to leave it blank, so I use not none. And if any student for good to provide his city, then you need to use default constraints. And our default cities, Delhi. So our query is complete. Let's run the query. So I'm going to click on the flesh icon. You can see the green signal. So our query run perfectly. And if I refresh our schemas section, you can see our table impersonal. And if I click on our table limb, you consider columns name and its datatype below on your screen. And if I open up personal table and open our columns. Also you can see the columns name, id, name, age, gender forms, CT. Let's see the table. So I hover on my TV NLEM and click on Table icon. And I want to insert a student in our table. So I'm going to type the query. Insert into table name is personal. Then inside the round buses will need to provide our columns name, id, name, comma age, Zinder, phone, and city. And then we need to type another cue, arg values. Then inside the down, this is a student IDs. One student name is Edwin and he's 19 years old, is Zinder is mean. So I type capital aim. And for phone number, I'm going to type some random numbers. And he's from the and I use semicolon to end this line. So our query is complete. Let's run the query and you can see the green signal in our output selection. Let's see the table. Just hover your cursor on your table name and click on Table icon. And you can see the first data in our table. Let's add another student in this table. And this time you forgot to enter Zinder will. So I removed Zinder from our columns name. And also I'm going to remove this one from our video section. And also you forgot to change student phone number. And our student ID is two, and our student is 18 year old, and our student name is Anna. Let's try to insert this value in our table. So I'm going to press flesh icon. And you can see in my output section that is rich signal. We set our phone number q unique and it showed the ER for boom, we get entry. So first we need to resolve our phone number problem. So I'm going to change the phone number. And once again, I'm going to run this query. And this time you can see a warning sign. It's worked perfectly, but it shows some warning. It earns vendor doesn't have default values. If I go back to my table and show you my table, you can see our Zinder section is empty. Empty means null will lead us back to our query section. And once again, I'm going to use our center column H comma Zinder in our value. I'm going to provide capital for Zinder. And this time our student name is Susan, and her ID is three, but she forgot to provide her city name. And this time I'm going to remove city from our column section. Also remove city name from our 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. Be unique. We do not duplicate their views. 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's all perfectly. There is no warning. But if you notice, we do not provide city name because we use the full 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 city name is delay. Let's add another student. And our student IDs for and student name is Rahul. And he is just 16 year-old and his gender is male. He's mill. So I type capital M. And I also change students phone number. And 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 then 18, he is not able to join this college. So I'm going to change his age. Now he's 20. And also I want to use city column, phone, coma, city. And this time I want to enter different city name. And Raul is from Kolkata. And if I run this query, you can see now it's work perfectly. And 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 Dilly as his city name. So I hope now you understand how we can use constraints in our columns. It is very important to use. I hope you understand the use case of unique NOT null default. And Jake, thanks for watching this video. See you on the next tutorial. 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 learned how we can create tables in our database. And do you 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 select command. Let me show you this in texts. You can see first only to type select command. After select command, you need to type your columns name. In our case, column one, column two, which columns you want to see. Data is up to you. Then you need to use another cure from and you need to type your table limb. 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. Starts mean every column in your table. So let's start the practical and see how yet come on work. You can see I opened my MySQL workbench application, and I also opened my Azim control panel. And I'm going to start Apache and MySQL. And I went for green signal. Now, our local host server is ready for work. And I'm going to create my connection. I just hover on my connection name and double-click on it. And you can see our old query in our editor. And you can see my table's name in our schema section. And 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, persona. Then I use semicolon to end this line. And I'm going to click Run button. You can see it provide all the data from our personal table. Basically, we have four record in our table. And you can see all the columns. So if you want to see all the data from your table, select star from your TV limb. And if you want to see particular columns from your table, yes, you can. Let me show you. I wonder C student ID, name and their gender. So I type ID common name, comma Zinder. And if I execute the query, you can see our student ID, student name, and their gender. After select 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 later, but I want to make it a capital letter. In that case, we can use allies name for our columns, name after ID. I'm going to type SQR as capital ID. Similarly, for name, as I'm going to type capitals student. And for gender, I'm going to type capital gender. These are allies name. Using allies name, you can take different name for your columns. Let's execute this code. You can see it replace our columns name with their allies anime. And if you want to show two awards 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 quotes. And inside the double quotes, you can type two different worms, 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 upper these, let's see how we can retrieve conditional base data from our table. When we try to retrieve conditional with data, they need to use IR gloss. You can see a table on your screen. From this table, I want to retrieve some conditional base data. I want to show all the males two then from our table. As a result, it returns this kind of 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 returns this kind of table. You can see we have only one student who is over 30 years old. Whenever we try to retrieve conditional risk data from our table, then we need to use select command with gloss. Let me show you this index. 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 Octavian limb. We don't need to close the statement just using the space and type where clause. 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 Melville's and we have to not equal sign. Next you can see greater than sign, then scum greater than or equal sign, less than, sign, less than or equal sign. Then we have in operator not operator between operator is null operator is NOT null operator like operator and the exists operator. We explore all of this operator in our upcoming videos. For now, I'm going to show you how we can use some common operators in this video and write down. I'm showing you the practical. Once again, I'm back to my MySQL workbench application. First, I'm going to show all of the column from our table. And you can see all of the column from our table. And now I'm going to sit my condition. And I want to show all the male student from our table. So first I'm going to use whereClause. You need to type our column names, Zinder, Zinder equal to capital aim for me. And if I execute the code, you can see it written all the male student data. And similarly, if you want to show all the female student, you can just replace him with capital a if let's execute the code. And you can see it print one row from our table. And now I decide, I want to show all the students who are below then 20 years old. So first I'm going to type our column name age. And then I'm going to use less than operator for h. I'm going to type 20 because our H column data type is int datatype. So we don't need to use quotation. And if I execute the code, you can see it print all the student name who are less than 20 year-old. And if you want to show people under the age of 20 are 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 were 20 years old are below than 20 years old. Similarly, we can use greater than operator, IEP student age greater than 18. And if I execute this code, you can see it returned to students at one and run. Let's talk about another operator, not equal to operator. And now I want to return all the students who are not living in Kolkata city. So I'm going to type cd column not equal to Kolkata. And if I run this code, it written all the students who are not lived in Kolkata. And similarly, if I want to show all the students who are leaving Kolkata, then just remove the nautical to sign. And we need to use equal to sign. And if I execute the code, you can see on the router lived in Golgotha. And as I showed you earlier, We have another way to use not equal to sign. If we use less than and greater than sign together, it's mean is not equal to. And if I execute this code, you can see all the students who are not leaving Kolkata. And now I want to show only student ID and name who are not leaving Kolkata. Select id comma name. And if I execute the code, you can see it print student ID and their name who are not living Kolkata. I hope. Now it's clear for you how we use a where clause which slipped 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're going to learn why we need to use AND OR NOT operator. In our previous video, we learn how we use CILIP command with the where clause and we can use only one condition in our where clause. But if you want to use multiple operator at ones, then you need to use AND OR NOT operator. You can see a table on your screen. From this table, I want to retrieve edge between 21 and 30, maybe 21 or greater than to a Dewan, between less than equal to 30. And if we retrieve, then we got this kind of table. We've got to students and their edge between 21 and 30. So we need to say to condition with our where clause, something like that. Where h greater than equal to 21 and age less than equal to 30. If the boat conditions are true, then it return our table. You can see Sophia is 21. So her age mate with our condition. If age is 21 or greater than 21, and if age is 30 or below then 30, then it shows two then details. And you can see Smith is 32-year-old. It meet our first condition, but it doesn't meet our second condition. But our two conditions must be correct. Let me show you another example. And now I retrieve those students whose ages 20 and 32. In that case, it's written Smith and Emma. In that case, we need to use OR operator. Also, we can use the where clause, our first condition or keyword. Then our second condition, R means one of the two condition must be correct. If any other condition is true, it retrieved the table. So this is our two operator. And let me show you the syntax. First one we need to type, select your and then you 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 where cross, and then you need to declare our first condition. We can use Anki word. Then we declare another condition. We can declare multiple condition at once. If all the conditions are true, then we can see the result. Even if on of this condition goes wrong, we will not able to see the result. And if we're talking about OR operator, everything is same, just we need to use our keyword between conditions. So leads back to the MySQL workbench application and see how it's sore. You can see I opened our application and I also open them control panel. And I already start Apache MySQL. So our local host server is ready for connection. And 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. So I'm going to click on Table icon. And you can see we have four students in our table. For now. I don't need this multiple panels, so I'm going to close it. First. I'm going to use AND operator. And I wonder retrieve students who are greater than equal to 19 years old and less than equal to 20 years old. So I'm going to type select star from table limb personal. And then I'm going to use hierarchy or where our column name, age greater than equal to 19 and age less than equal to 28. Both the conditions are to show me the result. Let's run the code. And you can see each return to students at B1 and Rahul at one is 19 years old and the hole 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 today who lived in Kolkata city. So I'm going to remove our second condition and type CT equal to Kolkata. And if I execute this code, you can see it return one result rather than only the whole fulfill our two condition at ones. First, he's more than 19 years old and he leaving Kolkata. Not only that, we can use three condition at once. And now our CTE is the LEA. And they also wanted to add another condition, Zinder and Zinder equal to mean. In this case, the three conditions must be correct. And if I run this code, you can see we've found one student who match our only conditions is 19-year-old ellipse in Delhi and also he is a Min. 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, second condition, I'm going to use our operator. Now the condition is if student age is greater than 19 and 19 and deep student ages male. Then written these two rems. If any condition is true from our column, it return this student names. If I run this code, you can see it's written to result at one. Rahul. And now I'm going to use all operator using similar columns name where age equal to 20 or equal to 18. If all of this condition is true, then it return. Then it written our student names. And if I run this code to it, D1 through student Anna Smith and run, and now I'm going to use this to operator at once in our query. I want to use OR operator between each column. So I'm going to move this condition inside the loan business. And then I use AND operator and Zinder equal to min. I combine this H column. We know if we use an operator, then the two condition must be correct and differ on this command. You can see it returned one student name down because he is 20 years old and his gender is male. So it fulfill our both the condition we use OR operator inside the round resists. So our OR operator act like one operator. So in this way, we can search complex data from our table. So I'm going to remove this code where I'm going to use not QR city equal to Dilly, our city equal to Kolkata. This command going to read tips, students who are not living in the because we use NOT operator for the width NOT operator, we also use OR operator and say CT equal to Kolkata. This query going to retrieve all these footings. Who leaps in Kolkata, not in Delhi. And if I run this code, you can see it written on the ones touring around. He goes, Wow, lived in Kolkata. He not lived in Delhi. And if I replace their city name, and now I don't want to show the student who are not leaving Kolkata. So they are not CT equal to Kolkata, our CTE equal to del. And if I run this code, you can see it written tourism and all the students leaps in Delhi. We use NOT operator for our first condition. And between our condition we use OR operator. But we can make our organization a single condition using noun resists. Now it's at like in 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 Kolkata. And if we had the name of another city, you would have seen that result. Let's run another condition for NOT operator. So I'm going to remove the school age greater than equal to 20 min. Show me those students who are not 20 years old or greater than 20 years old. And if I run this command, you can see it written three students and one Anna and truce Meta, because they are below than 20 years old. I hope. Now it's clear for you why you use AND, OR and NOT operator. We can retrieve complex data with that. Thanks for watching this video. See you in the next tutorial. 10. Mysql In Operator: 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 kind of table. And you can see we have only two students who is 24 year-old and 21-year-old. And one and Sophia, we use OR operator to get the same result in our previous video using a where clause. First, we need to declare our condition, then we need to use or keyword. And 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 an operator. Just don't need to type where our column name age, then our inner operator. And inside the boundaries is we need to tie our query. And let me show you the syntax, how we can use in operator in SQL command plus unit to type your Select command, then you need to type your columns name. And if you want to retrieve all the students, you can use star sign, then you need to use from cure updated that. You need to declare your table name. And then scum where clause after her clothes, you need to mention the column name from where you want to retrieve data. Then we need to use IN operator, and instead they don't resist. You need to provide value which you want to retrieve from this column. You can type multiple values at once, just unit to use comma between them. And we can use our inner operator with NOT operator. When we use NOT operator within operator, it returns all the columns name without this value. So let's start the practical how we can use in operator in our SQL query. So finally, I'm back to my MySQL workbench application. And I also opened my eyes them control panel post. I'm going to start Apache. Then I'm going to start MySQL. We need to wait for green signal. Now our local host 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 security editor and my database name student. And inside my database, I created an impersonal. 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 where clause. So I'm going to remove it. Where age in age in, inside the round resists. I'm going to sort those students who are 19-year-old, an 18 year old. So inside their own risk is I'm going to type 18 and 19. And if you'll notice, I did some silly mistake. I don't mention my table name in my query, so we need to mention table name and our table name is personal. Select star from personal where aging 18 and 19. So let's execute the code and see what happened. So I'm going to heat thunder icon. And you can see we've got three results from our table. At one, Anna Smith, one is 19 years old, and I was 18 years old and sues me, they're also 18 years old. I can put many condition hair as I want. So I'm going to add another condition, 20. And if I run this code, you can see it's written another student neighborhood who is 20 years old. Not only that, we can use, not avert or with that, its mean. Show me those students who are not 18 years old, 19 years old, and 20 years old. If I run this code, it going to return a empty table. So let's run the code. You can see it's just an empty table because 40, our students are between the age of 18 and 20. So I'm going to remove two condition, 19 and 20. And now I'm going to run the school. And you can see it written to student at one end, Rahul. So it return those students who are not 18 years old. And now I want to use our in operator which city column. So I'm going to remove it. And I'm going to type City. And I wanted to show those students who are leaped in Delhi. So inside the boundaries is only to use the whole quotes because our datatype is where care. So inside the door course, we'll need to type our city name dilly. And if I execute this code written, those students who are lived in the Lee, and we also use NOT operator with that. Now it's your turn go students who are not leaving their LEA. If I execute the code, you can see it's written on student Rahul because it's only been Kolkata. Not entirely. Using inner operator, we can search in our ID. So I'm going to do NOT operator. And also I'm going to do CD column. Then her ID in. And I wonder from multiple student data using ID. So I'm going to remove this city name and type 1 and 4 here at bus two, id 1 and 4, and differ on the score. You can see it's written to student from our table and one and Rahul, we use in operator where we have to say it multiple records. I hope notes here for you why and how we use in operator. Thanks for watching this video and stay tuned for our next tutorial. 11. Mysql Between & Not Between Operator Intro: Hello friends, welcome back. In this tutorial, we are going to learn between operator. Here you can see a student table. And instead this table insert some dummy data. And now I want to find something in this record. And I want to serve students between 18 to 21 years old. And then it's written this kind of table. Only three students are between 18 and 21 years old, Sophia, Emma, and Olivia. To extract these kind of record who need to use between operator, you can see up to where we need to type our column name, age. Then we need to use between keyword. And then I need to provide two values, 18 and 21. And between these two value, we need to use and keyword. So I set a range between 18 and 21. And you can see in my table there is another column named DOB, means ghetto birth. And now I want to extract students who are born between two thousand and two thousand and four and then return this kind of table because Emma, Olivia and gyms are born between 20012004. And if you want to extract data between two date range, we can use between keyword. You can see when you type where DOB between 2001 and 2004. Then show me the table of the students. Let's see how we can use between keyword. We'd select command first one into Type, select our column names, which column you want to extract from your table. Then we need to use from cure. And we need to provide table name where column name between V1 and V2. And if you want to extract all the columns, then you can use star sign. And we can use this between operator with NOT operator. Just we need to use NOT keyword. We put the between and if we use NOT keyword, then is going to return all the way without the range of between V1 and V2. So let's start the practical and see how it's or 12. Mysql Between & Not Between Operator: You can see I opened my MySQL workbench application and I already opened my eyes them control panel. First, I'm going to start Apache, and then I'm going to start MySQL. And our local host server is ready for our connection. So I'm going to create the connection. And I'm going to hold me on my connection name. And you can see previous code in our quality section. For now. I don't need this step, so I'm going to remove it. Let's see what is in our table. So 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 1920. For this, I'm going to type select star from table name and our table limb is personal. And then I'm going to use where clause. Then you need to type the column name H, and then we need to use between operator between. And then you need to take two values. Are first value is 18 and our second video is 20. So this query going to return those students, those between the age of 18 and 20. Let's execute the code. So I'm going to click the tender icon. And you can see it written for students, because all the students are between 18 and 20. And if I use not divertor, afford the between this C N, O T naught. Now it will show those students who are not between the age of 18 and 20. And 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. So I'm going to remove this column name and type IT. And also I'm going to remove this not cured. And I want to show those students who are between 24. And if I run this code, you can see it's written three Student 2, 3, 4. We have three records. And if I use NOT Q or to eat and run this code, you can see it is written only one result because we have only four result in our table. So if we do not show students between 24, then only wants to them lip. And he's, and one, it is not mentally ready. That's operators always be number will also use this between cured with a string datatype. So I'm going to use it with name. In a name not between. I'm going to remove this, not cure. And between I'm going to type a and S is a string, so I need to move it inside the double quotes. A inside the double quote is. And if I execute the code, you can see it's written. And one NLRA hold, it showed those students whose names start between a and S. So we can use this between operator with numeric value and string value. Let me show you another example. We did 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 a Create and the table name Parsons. And we already learned how to make table. And if I show you this table, you can see it has four result. You can see there are four rows and three columns. We have saved the date of birth of this student along with the name of the student. So I'm going to use between operator with that. I want to see those students who are born between 1996 and 1999. So fast only to type select star from our table nim person. Then we need to use whereClause are our column name. But the date where ditto bark between inside the quotation, I'm going to type our first date. 1996 month January did 01. And now I'm going to use n keyword and the edge between 1998, December month and the last December. So it's going to return those students who are born between 1996 and 1998. So if I execute the code, you can see it written three student at once, meat and Sue Smith. So this is all between operator. We use it to find value between two numbers. Thanks for watching this video. 13. Mysql Like Operator Intro: Nice to see you back guys. In this tutorial, we are going to learn like operator in CQL. Here you can see a dummy table on your screen. Now I want to extract those students whose names start with this. And you can see then it's returned this kind of table. It's written Smith and Sophia because their names start with this. And if you want to extract result like this, then you need to use like operator where name, like inside the double quotes is Module sign. It's mean our world start with this. And we don't know how many characters will come up that it may be 0, maybe one, or maybe multiple characters. You can see we use modular sign. Similarly, we have more characters and we called it wildcard character. And basically we use two characters, percentages and underscore. Person does mean it represents 0 character, one character or multiple character. And underscore represents a single character. And I'm going to show you some examples of our parenting. If I type like a person, then is going to find any value that start with a. Similarly, if I type percentage a, it's find any value that, that ends with a. Then if I type Parson T's OR percentages, It's find any value that have OR in any position. And if I type underscore our personalities, It's mean finds any value that have are in second position. And if we type a under scope are seventies, it finds any values that start with a and R At last two characters in Lynn. But if I use a underscore, underscore per seventies, it finds any values that start with a and r atlas three character inland. Just one thing. Remember, underscore mean one character and percentages mean any character could be one character or multiple character. And if we type a percentage o, find any values that start with a and end with o. And if we type and Dusko 0, y is going to find those or where 0 in the end and y in the third position. Let's see how we can use like operator with our syntax. First we need to type select QR, then our columns name from table name. Then we need to use a where clause, column upturn where we need to create a condition column like operator. And then we can create any person as you want to search in our column. Also, we can use these like operator with NOT operator, something like that. Just only to use NOT keyword before the like keyword. So it's going to return all the answers opposite of this button. So let's start the practical and try to understand how we can use like operator with different patterns. 14. Mysql Like Operator & Wildcards Tutorial: Hey, I'm back to my MySQL workbench application. And I also open them control panel. First I'm going to start a purchase Server, and then I'm going to start MySQL. And we need to wait for green signal. First. We need to create the connection. So I'm going to hover over the connection name and double-click on it. 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, when you can see we have four record in our table. And if I show you another table Parson, you can see we also have four record. So from my personality will, I want to find those students whose name starts to eat a. For that, we need to type select star from personal means are given limb. Then we need to use where clause and we need to provide column name. And then I'm going to use like operator. And I'm going to use quotations because our name column data viz where care. So I'm going to find those students whose name starts with a percentage sign. This means a should be in the beginning. And what remains up toward that will not matter. If I execute this code. You can see we get to result at one. And Anna, not only that, we can use multiple character at ones. If I type NEW, I want to see AD w out of all the students out there. If I execute this code, you can see we get only one record at one. And if we want to find such a record, it is not hair. So I'm going to add another W. And if I execute this code is going to return blank. And now I'm going to use the whole-person design incentive. The whole person decide, 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 end, or stays in the medium. If I execute this code, you can see it written one student, Anna. And now I want to see those students whose name starts with a and is for these first I'm going to type a personalities and then I'm going to use OR operator are named like INSEAD, the condition is percentages. If I execute this code, you can see it's written three student at one end. And so Smith now we can use NOT operator with that. So I'm going to remove this section and I type where name, not like our pectin. If I execute this code, you can see it's written all the students who are not match with this button. You can see it written, shush, be the NRA whole, but it don't return at one and Anna, because their names start with a. Let me show you another operator name binary. To execute this query, I'm going to open our another table name Parsons. And I'm going to remove this NOT keyword where name like smallest. I want to show those students whose names starting with smallest, not capitalists. In that case, we need to use by genetic keyword where binary name like smallest. If I execute this code, you can see it's written to student Smith and slows me down because their names start to eat smallest. Let me prove it with another way. You know, in our table there is a student name Rahul, and his names start with capital R. But now I'm going to use smaller. And if I execute this code, you can see it's written blank because we use magnetic you work. So it's going to return specified character type results. And once again, if I use is, you can see it's written and Smith. But if I use capital R, Now it is going to return home because his name is start with a capital R. Let me show you another example. So I'm going to remove by genetic URL, and I'm going to use parsing sign before the character and a type in its mean. I want to see those students whose name into it in. If I execute this code, you can see it's returned one student named Edwin. Similarly, we can search to character at once. So I'm going to type TA and execute the code. And you can see it return. So Sumita, and now I'm going to show you another example. I'm going to type our percentages in this line mean our Wilkin 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 individual. And now I'm going to show you how can we use underscore operator. For this example, I'm going to type underscore us percentage. It's mean the word starting with three character. You ease 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, shush me down. And now I'm going to use underscore. Underscore, underscore H. It's mean, it started with three character and we don't know the first two character and our third character should be H. So it's going to return those students. Who's third character is h. If I execute this code, you can see it return Rahul. We can use underscore between two characters. Let me show you is underscore I. And if I execute this code, it's written Smith, because our character start with is, and we don't know our second character and our third character design. So this baton match with Smith. So as a result, it's written Smith. I hope now it's clear for you how we can use like operator with pertains. Thanks for watching this video. See you in the next tutorial. 15. MySQL Regular Expression Tutorial: Hello friends, welcome back. In this video, we are going to learn what is regular expression in MySQL. With that, we are going to learn how we can use a regular expression which select command. Here you can see a dummy Student table. And I want to search those students whose name into it WAN. With this, I want to search and other pertain if there is a name and o pH written in that name, then show me the name. And if you try to fund these kind of record from this table, then it's written these two result, one and Sophia because at one end WAN and you can see the characters oh, pH in Sofia. For this kind of searching, we use regular expression. You can see after her, we need to provide column name and then you need to use a keyword name regular expression are easy. Exp, it's a short name of regular expression. Then insert the codes. We need to type multiple expression and we call them patterns. As you can see, it's in week 1, I type WAN and then I use dollar sign, its mean the word in with one. And then I use Pi sine its mean, I want to search and other parent in 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 its mean. If the string starts with a B, then it's written that string. And our next sign is dollar sign. It's mean where string in wheat. If the string in with AV, then it's written the upstream. With that, we have square resist sign. If I type any character inside the square braces here you can see I type RMS. In that case, it going to find every character differently. First it going to search for, are, then going to search for an atlas, is going to search for this. Next, if we use upper arrow before the square resists, and if I type any character inside the square braces, it's mean is going to find those Ward who are start with a, otherwise II, otherwise R. Then we have another pet in where we can sit range. Here we set a range a to Z it. So it's going to find all these ordered. If the word contain any character between a to Z, it suppose you sit range between eight to eight. Then it's going to return those results who have the character between eight to eight. And if I use another character after this square resists, it is going to create different type of purlin. Then it going to search the character who indeed a, B, C, something like that. And last you can see by signs. Here we can set our different patterns. You can see I type 3 name in our parent in EVA, Mia, and Nora. Between them, I use Pi sine, so it's going to find all the result if the string contains this name, 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 in text, how we can use regular expression, which Select command. First we need to type select keyword, and then we need to provide columns name. And if you want to show all the columns, you can use star, then we need to type from keyword and we need to provide the table name, then scum, where cure. And 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 parenting. So without wasting your time, Let's start the practical and see how we can use the regular expression. 16. Mysql Regular Expression Tutorial part 2: You can see I already opened MySQL Oracle application, and they also opens them control panel. First I'm going to start Apache, and then I'm going to start MySQL. Now our local host server is ready for connection. So I'm going to hover on my connection name and double-click on it. And this is our secure editor. In our previous video, we work with Parson and personal table. If I show you the personality will, you can see we have only four record. And now I'm going to search record using regular expression. So first we need to sit it out table, select star from personal. Then we need to use a where clause, where then need to select or column name, name. And we need to use a regular expression keyword. And now we need to provide exact Patton what I want to search in our table. And I'm going to search if a name contents AMI, then that name will show. Let's execute the code. So I'm going to click this little icon. You can see is return a result names Meta. Because a my available in this name. And if I tie array, then it is going to find those world which have RA character. If I execute this code, you can see it's written at a whole because a host start with RA. Oh, he did the same thing with the HIPHOP like operator in our previous video, but we need to use percentage sign for that in our lac operator, who need to use two percentage sign to get the same result. But the regular expression is much more reliable than like operator. Let's dive into our next example. In the name field, I want to check those name, whose name must start with AD. So we need to use upper arrow sign for this. So it's going to find those word who are start with 80. Let's run the code. If I execute the code, you can see it's written. And one, because at once start to eat AD and d Phi dy SU is going to find those word who are start with SU. Let's run the code. You can see it's written So it's meta. So if I use a Robert or beginning of the string, then it's going to check those world who are start with this burden. And similarly, we use dollar sign to check word from end. So I'm going to type in a, then I use dollar sign. So if I execute this code, you can see is written Anna, because Anna ends with any. And now I'm going to show you how can we use pi sine to search more than one person? So I'm going to type for a one. Then I use Pi sine and I'm going to type another word, Anna and on-site. And I use Pi sine. 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 return null. But the cushion is why it written null. Because we know the students names are available in our table. Then y is written null. Because we use space, we need to remove this space there. So I, it returns null regular expression, counts space also a character. If I execute this code, this time you can see is return our result and one NRA home. If I remove in and then execute the code, you can see it's on secondary than n because n i match with this burden in a, an NIN with any. But if I use upper arrow sign is when it start with any. And if I execute this code, you can see, you can see it written only to students at one and Rahul because and not start with any. That's why it written on it to student at one and run. But if I use dollar sign end of our third Patton and then execute this code, you can see it's written. And one, because Advil start with this pattern and also at one end with this pattern. And if I remove AD from Edwin and then execute the code, it's also written at one because also this time it's indeed WAN. So this is our Pi sine. Using this sign, we can take multiple records at once. And you can use this sign. How much you want. There is no limit to use Pi sine. And now I'm going to show you another example where I'm going to use square braces. How many characters we are going to provide insight, our square basis. It's going to take it one by one. If I type I is it's not going to search. Is it going to search? Is, is going to return all the name where INS is available. If I execute this code, you can see it's written only one student who is MIT is so Smith's 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 advanced to split the NRA whole. Because d character available in Edwin, I character available in Meta, and our character and we live in. That's why it's written. The students, we can do same thing using like operator, but we need to use orbiter forties. They need to create our code very clumsy. That's why we prefer to use regular expression. Inside the square braces. I'm going to type in. And outside this Kerberos is I'm going to type a. Now the question is, what is the meaning of that? Is going to create combination, frosted, going to create our recommendation, then is going to create any combination. I'm going to remove the unnecessary characters, and I'm going to execute this code. You can see it's written to student and I entered a whole because in a combination match this pattern also recombination between these Burton. We can use multiple characters inside the square braces. So I'm going to enter T character. If I execute this code, you can see is returned switch with also because the enjoyed TA character. Not only that, we can use upper arrow sign with R-square resists. And I'm going to search those characters who are start with our a and S. And if I execute this goal, it's written those students who were start with RA and a. Similarly, we can use in the operator with that. So end of this square recess who need to use color sine. And if I execute this code is written only to student. And so sweet, because these two word into it, a character. And in our condition, a character is available if I type D N inside the square recess. And, and I'm going to use a character beginning of the services. And if I execute this code, you can see is written at one and Anna, because it creates two combination AD and AS. And these two combination mess with Edwin and enter. Not only that we can use range inside the square braces. And I'm going to type C to E. And I'm going to tell you how it's work, is going to find those characters who are, who are between C to E according to our pet. And it's going to select three character C, D, and E. So our pet and start with a, and it's going to create three combination with a AC, AD and a list execute this code. You can see it written add one because AD match with our pattern. So we can use range operator. Regular expression is more efficient than like operator. So we use regular expression. I hope now you know how we can search complex to complex burden 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. 17. Mysql Order By Intro: Hello friends, nice to see you back in this video, we are going to learn two new poverty from SQL command. What are BY and distinct. You can see a dummy Student table. And you can see the first column is name, which is not sorted by water. And I want to be sorted by water. As you can see, after a student name started with is Smith and Sophia, I 1D names, those who started with a, two came first, and then I want to see those students who were started with B. Similarly, I want to order students by their name means ascending order, something like that. If I sit ascending order for name column according to the table after Edwin it returned Emma because he come first from is if you notice Smith and Sophia, both of the name start with this, but the second character of Smith, him come first, then 0. So it's going to print Smith before then Sofia. So here we have set our table in ascending order, set ascending order or descending order. We need to use a special keyword name orderBy, as you can see on your screen. First, you need to type whatever IQR and then you need to provide the column name in our case name. Then you need to provide which order we want to say. We have two types of orders, ascending or descending. If you want to go eat ascending, then you need to type ASC. But if you wanted to go in descending order, then you need to type DESC. If we said this column descending, then it will start working from the opposite direction. I wanted to say it going to start from SSID. Let's see how we can use quarter right command using slit command. First we need to type C LET command, then we need to provide columns name. And if you want to see all the columns, you can type star and then you need to use from keyword. And do we need to provide the table name and we need to use quarter by keyword. And then you need to provide the columns theme which we want to ordering. We can order multiple columns in one table. Just we need to use comma between columns name, and then we need to provide the water from it. Ascending, otherwise decent mean. If you want to go with S and D, then you need to type SC. And for descending, you need to type DESC. Ascending order is default. Ascending order is default order. If you use orderBy QR by default, it's an ascending order. So let's start the practical and see how it's sore. 18. Mysql Order By: You can see on your screen, I already opened my Zim control panel and I also open MySQL orange application. Must 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 double-click on it. And you can see we have four student in our table at one Ana sue Smith or Rahul. And here I want to order the column name. If you look closely, you will see that S come first. Then scum are. So I'm going to order this column name and print student name alphabetically. So I'm going to type select star from personnel. And then I'm going to use water Vikki. And then we 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 our character print first, then the S character is being printed. First of all, it's going to print all the names who are start with a, and then it's going to print those name who are start with B. It's going to print name alphabetically, means a crusade. And if you want to show this data in a descending order, yes, we can just going to type D, E, f, c. And if I execute this code, you can see now it's sprint. All the name in a descending order. Posted prince, whose meta there is. Print it out hold. Then it's been Anna Atlas. It print ad one. Here you can see, I do not use any condition. If we want to use a condition, yes, we can just need to type work. You are where city equals 2 inside the condition Dilly. If I execute this code, you can see those students who are from the LEA. And also it print their name in a descending order plus the principal, smoother than scum Anna Atlas, Edwin. We can do ordering any column here. So I'm going to remove this line. And I'm going to order each column. So 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 D is see, if I execute this code. You can see it water our H column, 18, 18, 19, 20. Not only that, we can order two columns at once, name comma city. If I execute the code, you can see. So first you're going to order name column there according to name column it when to order city column. So we can execute multiple column ordering at once. Let's talk about another method, listing. 19. Distinct Intro: 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 even, then it's written this kind of table. If you notice, then you can see it print chicago city twice. So this command return as it is city column. But I want to consolidate repo. I don't want to repeat a city name. In my reason, something like that. There is no duplicates in it. Print 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 column name, city from table. Let's say the proper syntax. First you need to type select command, then you need to type distinct world and you need to provide columns name. Then you need to use from keyword. And we need to provide David's name. So let's start the practical and see how we can use distinct your 20. Mysql Order By & Distinct: So once again, I'm back to my MySQL workbench application. And you can see all these students cities. Most of the students are from Delhi on the ground frame from Kolkata. So first I'm going to remove this code and then I'm going to type select distinct. And then we need to provide column name, city from our table personal. Let's execute this code. If I execute this code, you can see treat done dilly and Kolkata. It do not repeat our city name dilly. And now I want to extract all the age group from the personal table sermon to remove City. And I'm going to type H. If I execute this code. You can see if freedom three desert 191820. Not only that, also, we can order this H column just wanted to tie orderBy and you need to provide column name age. So if I execute this code, you can see it ordered our result. First it being 18, 19, 20. So it sought out column in ascending order. We'll use distinct you are to remove do legacy and get the unique result. I hope it's clear for you why we use water by and distinct keyword in MySQL command. Thanks for watching this video. Stay tuned for our next tutorial. 21. Mysql Is Null & Is Not Null: Hello friends, nice to see you back. In this tutorial we are going to learn is null operator AND IS NOT null operator. Here you can see a dummy Student table. And by mistake, Sophia forgot to provide her age. So you can see ages blank. By default, it's storing null value. If we do not use NOT null constant for each 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 is null operator. First we need to type where clause, and then we need to provide the column name in our case age. Then we need to type our operator is null. So this query going to retrieve those student who forgot to provide his age. Let's see how we can use it with select command. First, we need to type select, then you need to provide columns theme from our table name where columns them is null operator. So this command going to retrieve all the null values. And if we want to retrieve those kind of value which is not null, then similarly we can use IS NOT null operator, just unique to type IS NOT null. It's going to retrieve those results who don't have a null value to the particular column. So let's start the practical and try to understand how it's worked with our SQL command. So I already open MySQL orange application. With that, I also pin them control panel. So first I'm going to start Apache, and then I'm going to start MySQL. So now it's ready for connection. And I'm going to double-click on my connection name. Here you can see a table named person. And 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 edge will look for shamatha and Krish. And 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 age. For these, I'm going to type select star from our Thevenin and our table name is person. Then I'm going to type where clause, where, and I want to check each column and then I'm going to use r is null operator, this command going to return those students whose is is null. If I execute this code, you can see it's written to student, sues me the increase. They forgot to provide their age. Similarly, we can use NOT null operator. If I use NOT null operator, It's going to return those students who provide there is value in each column. So if I execute this code, you can see it's written at one, hammer and Rohit. And you also can see their age 24, 23, and 22. Just remember one thing, Nelson. Empty, not nulls mean not empty. We cannot use between not like operator to check null value. There is only solution to check null values. Is null or not null. I hope now it's clear for you. Thanks for watching this video. 22. Limit & Offset Tutorial: Hello friends, welcome back. In this tutorial, we are going to learn two new topics, limit and opposite. Here you can see a dummy Student table. And suppose there are many records in this table. And I want to see all the record at once. For these, we need to type select star from student means are given limb. If I execute this command, it going to return all the student at ones. But the problem is if we have 3000 student in our table, but I don't want to see all the student at once. I wanted to see the result in a limited number. And I want to see two student at one's. Something like that means our first tourism and one and Smith, to extract these kind of result, we need to use limit clause. First, we need to type limit. Then you need to set the number. We need to say the exact number. How many is that I want to extract from the student table? So let's talk about this index, how we can write limit clause in our query. First you need to type select command, and then you need to provide columns name. Otherwise you can use star sign from David Lynn. Then scum where clause. If you want, you can say it any condition. Otherwise, you can skip this where section is optional. You can use it without condition. Then you need to use limit clause. And then you need to provide the number, how many results you want to see. So let's start the practical and tried to explore how we can use limit close with a SQL command. So I opened my MySQL orange application and I also open them control panel. So first we need to start Apache, and then we need to start MySQL and our servers ready for connection. So I'm going to double-click on my connection demo. And here you can see my SQL editor. And you can see we have one table in our student database, name person. And if I show you the table here, you can see we have five students in our table. And this is our SQL editor. And now I don't want to extract all the results at once. And I want to see this result in a limited number. So I'm going to type select star from our TV limb person. And then you need to use limit clause. And I wanted to see to record at once. So I'm going to type 2. So if I execute this code, you can see it's written foster result at one and Sue Smith. Similarly, if I type four and then execute the code, it's written ports to rent at one's. Not only that, we can use wire condition with that. So I'm going to type where city equals to puny. And I wanted to see one result at a time. If I execute this code, you can see is written one result, Rohit, and he's from puny. And now I want to ordering our name column. So I'm going to change the limit. I'm going to type four. And also I'm going to remove this where clause. And I'm going to type whatever by name. If I execute this code, you can see it written for result and also it print their name in ascending order at one, amar, Krish, and Rohit. So as you can see, we can use everything. We don't limit clause where clause water by null operator, etc. As you can see from this table, we can extract our first two student. But if I wanted to see the adults to student, then hurt. Now I wanted to see Sophia and Emma, and I wanted to skip our first two student. So to extract these kind of 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. So first, we need to provide the opposite and we need to provide it before the limit number. So upset work as starting number means supplier to start. Here you can see our opposite is two means it start from Sophia because it's going to escape to student. At one end. Smith, Let's see the syntax of opposite, how we can use it with select command. Everything will remain the same. Just we need to use opposite number before the limit number, after the limit keyword to provide opposite number, then using comma, we need to provide our limit number. That's it. So let's start the practical and see how its work. So for now, I'm going to remove this line and I'm going to set limit and opposite two comma to our opposite is two. Its mean is going to skip to student number one and number two. And our limit is also to its mean is going to return to student number three and number four. So if I execute this code here, you can see it's written to Student Ambassador Andrew heat, and it's skipped two student Edwin and choose meta. Here I set my opposite to its mean. It's going to start from three. And I also said all limit two. So it's going to return CU student. As you can see, our ID and width for. So if I sit opposite limit four and then execute the code, you can see it's written only one student. And this is our last student, Krish. And if I set our opposite 0, then execute the code is going to return our first two student, id 1 and id 2. So whenever we need to start, always we need to provide a 0. So I hope now it's clear for you what is upset and limit in my school. Thanks for watching this video. 23. 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, some minimum, max, average, etc. Here you can see an employee table and there is four column in this table name, age, Zinder, and celery. Suppose now I want to move how many employee we have in our company. I just want to count the total employee. For these, we need to use the aggregate function, which is a prototyping count. Inside the parentheses, 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 gets the highest salary among the employees. For these, we can use another function which is max. It is also an aggregate function. It is going to read on higher salary among the salary column. So according to table, it's going to return add one because his salary is killed a $100. Similarly, if you want to see the minimum salary from employee, will we have another function, name mean? And similarly, we need to provide the column name salary inside the parentheses. If we want to see the total salary we have to pay to our employees, we can use some function and inside the parentheses we need to pass salary column and then it is going to return total 30 from the salary table. And if we want to see the average of the salary, we can use AVG function, AVG mean, average. Now let's see how we can use the select command. First, we need to type select command, and then we need to use the aggregate function, heroin use count function. Then inside the round brushes will need to provide the exact column name. Then we need to type from and our table name. And if you want to set condition, you can, it's an optional, not mandatory. In a similar way, we can use other functions like some minimum, average, etc. We need to follow the same person you after select command when need to type our function name that's leaked and everything remains same. So let's start the practical and see how it's work. Hey, I'm back to my MySQL workbench application. And I already started my SEM 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 dummy data in this table. And also we have four columns in this table, name, age, Zinder, and salary. If you'll notice, you can see we do not use ID column to store student unique value. And now I want to count all the students from the employee table. So I'm going to type select count inside the parentheses. We need to provide the column name, which is name from employee. If I execute this code, you can see it's written six. Its mean, our total number of student is six. Not only that, we can replace our column Nim, which star sign it, going to count all the student fraud table. If I execute this code is also written six. We know there are usually two senders in the world, but if I count Zinder column and then execute this code is written six because it's going to return total number of Zinder. But I don't want this result. I just want to see how many senders there are. So I want only unique values. For these. We can use distinct keyword. 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'll notice, you can see it does not return a proper column name. So we can take allies name for these. So just wanted to type is Elyse name. And 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 allies name for this column. And now I'm going to use our another function, max. Here you can see my table. There is the new column name, salary. Everyone seller is different here. But I wanted to see whose salary is the highest hair. For this, I'm going to use a new function named Max. So I'm going to replace count. We max. And I'm going to provide our column name, which is celery. And I'm going to use the same allies name, salary. If I execute this code, you can see it's written 1200. Let's see who is the person is, is Adwan the rest of get paid less than him? In the same way, we can see the lowest paid salary. Just need to type. Mean. If I execute this code, you can see it's written 514. Let's see who is the person? He's Jacob. Now the problem is, every time we need to back to the table to see their name, I wanted to see students salary with their name. I just wanted to return name column which salary. So we can use for these, we need to type comma and we need to type our column name, which is name. And also I like to return Endo H, So I'm going to type H. If I run this query here you can see it's written employee salary, their name and their age. So using coma, you can take any column name. Now let's see how much the company has to pay the total salary of employees. So I'm going to type some. And as a last name, I'm going to take total. And I don't need this column, name and age. If I execute the scope, hurricane, see that Sarah is $5,190. So the company had to pay a total of $5,190. And if I want to see the average salary of the employee, we need to use App function just only to replace sum with AVG. And also I want to change the name average. And if I execute this code, the average salary of every employee's 865 dollar. So 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. 24. Mysql Update: Hello friends, nice to see you back. In this tutorial, we are going to learn about update command. Here you can see an employee table and I want to change value from this table. As you can see, here is the employee name Smith. And he gets paid $800 as salary. But he got a promotion. That is why he salary has increased. So we need to update this column, 800 to $900. We have another employee name, Emily, and her age is not 22. She's just 21 years old. Mistakenly wrote his age is 22. So I need to update her age. So whenever we need to update existing data, then you need to use a special command, name update. So let's see this thing takes how we can use update command. First we need to type update keyword. And then you need to provide the table name. And then you need to type set your, were you sick? You are to set a value in a column. And then you need to provide the exit Columbia. And using coal to sign, you need to provide value. You can sit multiple columns at once, just unit to use comma between them. And then you need to use a where clause. And you need to set a condition. Just remember, if you run, if you run an update command without Where clause, otherwise this change will happen in all the table. That's why we need to use her condition. So let's start the practical and tried to explore how we can use update command. Hey, here you can see I'm back to my MySQL workbench application, and I already start MySQL and Apache server. So I'm going to create the connection. Here. You can see my security reader and also you can see our tables, employee and person. Hello, I'm going to practice our update command. We already use a table name employee. And if I show you the table, you can see we have six record in this table. And now I want to update a record. Here you can see an employee named Smith. I want to update her salary 800 to 900. So I'm going to use update command. So I'm going to remove these lines and I'm going to type update. And then we need to provide the table name and Octavian limeys employee. Then we need to use secure ACT. Its mean, which column you want to see it. So I want to update salary column. So I'm going to type salary. We don't need to use quotation because salaries data type is int. So I'm going to remove this and simply I'm going to type 900. And now we need to use condition. Otherwise it's going to change all the records. And I want to change salary only first meet, not for everyone. So I'm going to use condition where name equal to smith. And I use semicolon to end this line. And if I click on the employee table, you can see column's datatype name, int Zinder where care and salary int. Let's execute the code and see what happened. So I'm going to click on the calendar 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. And now I'm going to show you how we can change multiple columns blue at once. If I show you my table here, you can see a employee name Emily, I'm going to change your age. And also I'm going to change your salary at once. So first I'm going to select the employee name, AMI. So my name is Emily. I wanted to change your celery. Celery. And her salary is 1000 dollar. And I also going to update her age. So I'm going to use comma and I'm going to type H. Here you can see our H column datatype into datatype. So I'm going to type equal to 25. So here you can see I'm going to update two columns will at a time. So let's execute the code and see is it work or not? So I'm going to press the a 100 icon. You can see our coordinate them properly. If I show you my table and reload this table. Here, you can see now Emily age is 25 and her salary is $100. Not only that, we can update multiple Roseville at once, just we need to use in operator for these. Let me show you. So I'm going to remove this equal to sign, and I'm going to type in. Then I use parentheses. Inside the parentheses, we need to provide the employee names which employee I want to update. So I'm going to change at one. Using coma. We need to provide another employee union and our employee name is Smith. If I execute this code, is going to update salary and age, both of the employees. So let's execute the code and see what happened. Herge unsee, it executes successfully. So if i back to my table and reload this table, arrogance, see, and when he's 25 years old, also smith is 25 years old. And one salaries 1, 0, 0, 0, and the SME certainly also on 1000. And here you can see how we can update multiple columns and multiple rows in one line of code. For this, we need to use in operator. And then we can select multiple roles. At last, I'm going to show you another example. So I'm going to remove this where clause. If I do not use whereClause. And I want to update only one column, only salary column. So I'm going to remove H column. If I execute this code, it going to update all the employee salaries. Let's execute it. If I back to my employee table and represent stable hair, you can see now all the employees salaries are $1000. So definitely higher glosses, very important. So you need to remember it. If you use update command, you need to use paragraphs. Otherwise, it's going to update all the record. I hope now it's leveled you how abridged come on work. Thanks for watching this video. 25. Mysql Commit & Rollback: Hello friends, nice to see you back. In this tutorial, we are going to learn two new command, commit and rollback. Here you can see a dummy employee table. Here you can see a employee name Sophia, I want to change her salary 750 to 800 dollar. So if we want to update a TV data, we need to run an update command. And we learned about it in our previous video. For this, we need to type update table name, and then you need to use sitcom on our column name. And we need to provide the new value. At last, we need to use a where 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 I 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 rollback and semicolon to end this line. It going to revert our execution. If we use insert, update and delete command is going to revert this command. And I wanted to tell you one thing. All the SQL command will be revoked for 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 run rule that command, it going to roll back all the git 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 salary update. To solve this problem, we use another command, name commit. Basically commit, save all the previous line of code. Let me show you how it's work. First update command, execute for Jacob. And then each run commit command means it going to save this record permanently. Then mistakenly I update Sophia salary. Now I want to roll back this command, so we 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 learn about insert and update command in our previous videos and in our upcoming we do. I'm going to teach you delete command. So let's start the practical and see how it's work. 26. Mysql Commit & Rollback Part ll: Here you can see on your screen, I'm back to my MySQL workbench application. And they already start, and I already start Apache and MySQL on Muslim control panel. And now I'm going to create the connection. And I'm going to hover on my connection name and double-click on it. Here you can see a table name employee. If I show you the table, you can see there are six dummy data in this table. But if you work on MySQL workbench application, then you need to go in each section. Here you can see option Nim preference. You need to open this option and then you need to go CQL execution. Here you can see your option new connection, use auto commit more than just unit to uncheck it. If we do not uncheck it, it automatically run commit command, then it cannot rollback our execution. That's why we need to uncheck it. And then you need to click, Okay, that's it. And then you need to restart your MySQL workbench application. So once again, I'm going to create the connection. And I'm going to show you my table. And now I'm going to use a command which is coming. So 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 updating new data, I want to roll back to a previous state. And hair, I'm going to use update command. So I'm going to type update and I'm going to update Smith salary 1000 to 1500. And I'm going to run the same command which are run, which I run in my previous video. Update, table limb, employee, sit. And then you need to provide the column name, which is celery equal to 1500. And now we need to run where clause where name is equal to smith, then semicolon to end the line. But now I want to tell you on the first unit to click on the commit command. After click this command, you need to execute this icon. It's mean it's going to execute only this command, not whole command. Just you need to click on the fourth button, not execution. So I'm going to click on it. It's mean it going to commit our whole command. And then I'm going to click our next command, which is update. And 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. And similarly, similarly, if I execute or facilitate command only, hesitancy that table. And you can see Smith's salary increased by $500. But I did it by mistake. And now I want to roll back our table. So I'm going to use rollback command. So I'm going to type rollback semicolon to end the line. So I'm going to click on the rollback command. And I'm going to execute only role that command. So I'm going to click on the fourth icon. That state. Now our data is rollback. If I show you my table here you can see Smith's salary has returned to its former place. So you can see we made a change inside our database, but we rollback it again, the hilltop rollback command. Let's show you another example. So I'm going to select the code and do we get this lines? And now I want to update employee age, Emily H. Sate age, and her age is 29. Her name is Emily. So first I'm going to run commit command, and then I'm going to update MLA page. Here you can see it update Emily, age 29. And also I'm going to change Smith salary to 9800. And also I'm going to run this command. So I'm going to execute it. Harris successfully run out to update commands. And if I show you my table header can see Emily's edges 29 and Smith's salary is 1900. And now I want to roll back. So I select this command and execute this command. If I show you my table once again, here you can see, you can see it's back to its former position. So let's understand how rollback work. So first it's going to work on this command. So it's going to revert it. Then it's worth for this command. And also it's going to revert it. And rollback command is not able to revert commit command. The commands above the commit or rollback owned work on them. So let me show you the similar example once again. So I'm going to move this code above the commit command post. I'm going to update Emily H. So I'm going to execute this command. And then I'm going to run commit command. And now I want to update Smith salary. So I execute this command. So if I show you my table here, you can see it update our data. Smith seller is 9800 and Emily edge is 29. But if I roll back this command, it going to revert only this update, but it not work on this command. So let's roll decade. So let's roll back eat. And if I show you my table once again, here you can see it roll back Smith salary, Smith salary back to his own state. But you can see our rollback command is not work on Emily's update. So rollback org upwards. If it git commit command, then it's not going to execute. All the code above. The commit. Commit means permanent safe. And you need to remember one thing. Rollback works on only three SQL command, insert, update, and delete. I hope now it's clear for you what is commit and rollback. Thanks for watching this video. 27. Mysql Delete: Hello friends, nice to see you back in this tutorial, we are going to learn about Delete command. And you can see a dummy employee table. From this table, one employee left the company and he's Smith. So I want to delete this employee from this table. So 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 D. Then you need to type from a prep that you need to provide the table name from which table you want to delete the data. And then you need to use air condition to specify the exact data. You can type delete command Without where condition, something like that. If we use delete command without whereClause it going to delete all the data from the table. You should careful with this command. And remember, if you want to delete specified data, then you need to use where condition. So let's start the practical and see how it sort. So finally I'm back to my MySQL workbench application. And I also opened them control panel. First I'm going to start Apache, and then I'm going to start MySQL. Now server is ready for connection, so I'm going to hover over my connection him, which is DMO, and double-click on it. Here you can see a table name employee. If I show you my table, you can see there are six employee on this table. And I'm going to close these tabs. I don't need these tabs, so I'm going to close it. So I'm going to practice delete command in this table. So first I'm going to remove this line and I'm going to type, delete, delete. Then we need to type from. And then you need to provide the table limb. And our table name is employee. And now we need to use a where clause could delete exact value. So I'm going to type where, where, and I want to delete Smith from this table. So I'm going to tie wear name equal to inside the quotation. Smith, then semicolon to end this line. If I execute this command, it's going to delete Smith records means row number two. But before I end this command, I'm going to use rollback. Otherwise it's going to delete our record permanently and we learn ever roll back in our previous video. And also I'm going to type comment 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 executed perfectly. If I show you my table and reload this table. Here you can see there is no employee named Smith. So you can see how delete come into work. And now I'm going to roll back this data. So I'm going to execute rollback. If I refresh my table. Hesitancy, 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 these just only to change the column name where Zinder, where Zinder equal to capital M for male. That's it. So let's execute the command. If I show you my table and reload this table here we can see it did all the male employee from this table. So here you can see we can delete multiple row at once using our condition. So let's back to the table. And now I want to delete those employee whose age more than 20 years old, according to table, Sophia and Emily. So I'm going to set a condition to delete those employee just I'm going to tie her age greater than 20. That's it. If I execute the scope, let's execute this code. Now if I show you my table here, you can see it delete those employee who are more than 20 years old. So here you can see where condition is very important for delete command. And I'm going to roll back this table. And I'm going to reload this table once again. And now I'm going to show you if we do not use applause, then what's happened. So I'm going to remove this where 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 we do not use a where clause and I need my data back, so I'm going to roll back eat, just execute the rollback command. So you can see if we do not use her command, it's a very risky for our table. I hope you'll understand delete command. Thanks for watching this video. 28. Mysql Primary Key & Foreign Key: Hello friends, welcome back. In this tutorial, we are going to learn who Constraint, primary key and foreign key. Here you can see a list of MySQL constant. And we complete our foot constant in our previous videos. And 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. So first, let's try to understand what is primary. When he set a primary key in a column or need to store all is skewed data. There will be no publication in it. Now you can think we can do the same job using unique constant. But there is a difference between uniquely constant and primary key constraint. We can store null value when we use unique constant, but we cannot store null value in primary key. And remember, we can sit primary key only once in our table. We can use it just for one column. But we know we can use unit 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 column in this table, id, name, age and city. There can be many student with the same name. And the same situation goes for agents city. Here you can see Smith is from London, and also Emma is from London. So we cannot use primary key constant for city column. And you can see a column name ID where you can sit 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 Melville in this column. Because when we add a new student in this table, we always give a new ID. So let's see how we can set primary key in a new table. Let's see the syntax. First, I need to type create table 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 used not null constraint and I use another constant, auto increment. Its mean. Whenever we add a new data in this table, we don't need to provide the ID every time it automatically increment itself has time when he said a value, it automatically take one as an ID. Then I'm going to take another column which is name. And its derivative is where care. And I said character limit for this column, we can use only 50 characters. And I don't want to leave this column blank, so I use NOT null. And then I'm going to create our third column, city. But here you can see we use indebted I for city. I'm going to explain you later why I use indebted I for city. And now I'm going to use primary key. First, we need to type this keyword primary key. Then inside the parentheses, we need to provide the column which I want to sit primary key, and I want to set ID as primary key. So in this way, we can set primary key when we create new table. And now I'm going to show you the process. If we already have a table and I wonder set primary key in a column. For these, we need to type Alter Table keyword. Then you need to provide the table name. And you need to use at keyword. And then just type primary key. And inside the parentheses, we need to provide the column name where I want to add primary key. Using alter 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 we want to link tables together and the most important parties, it contains only that value, which is the primary key 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 name, city. We just use two column for this table, CID for city ID and city name. And we have another table, name student. And we have four column in this table, ID name, age and city. In my CGI table, I want to make the ID column primary key. Similarly, in my student table, I wanted to make ID column primary key. As you can see, a column name, city. Here you can see city names, but I already create a table limb city. As you can see, I save all these names here and now I don't want to type CT name. I just wanted to provide COVID from CT table, something like that. And we can set this column foreign key. So we use CID column value in CD column CID is our primary key. And you use this value in other foreign key table at one was from Dili. So he's COVID is one. Similarly, Smith is from London. So he see DID is two. We can link our tables using foreign key and primary key. That's why we use this cookie. If we save data like these, it create our table more lighter, more Foster. We don't need to provide repeated name every time, just only to pass their ID. Let's see it. Let's talk about foreign guessing ticks. We need to follow the same process, adapter set primary KEY. Next I'm going to say it foreign key. First we need to type foreign key. And we need to type a capital letter. And then you need to provide the column name where you want to say to foreign key. And I want to say it C, D column as a foreign key. That's why I use in data type for city. Because hair, I'm going to save ID, not city name. And then you need to provide references. So I type references. Its mean, which other table's primary key is referring. So I provide the table name, city, and inside the parentheses, we need to provide the primary key column, which is the ID. Then you need to close out, come on 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 we have existing table and I want to say foreign key in this table. For these, similarly, we need to use alternative LQR, and then we need to provide table name. After that, we're going to type at keyword and just type foreign key. And you need to provide the column name as a reference. We need to provide the table name, which is city. And inside the parentheses will need to provide primary key column name from CD table. So let's start the practical and see how it's 4k. Hello friends. Finally, I'm back to my MySQL workbench application. And they also opens them control panel. First I'm going to start Apache, and then I'm going to start MySQL. And we need to wait for gain signal. Now our server is ready for connection. So I'm going to hover on my connection him demo and Dolby conic, halogen see a database them student. And there is no table on this database. I drop all the tables from this database. So first I'm going to create a table name city. So I'm going to type in a query section, create table. And our TV name is city. Then inside the parentheses, our first column name is CID. Cid. And it is derivative is int. And I'm going to use not null constraint. And also I'm going to use auto-increment. That's it. This is our first column. Our next column name is Sydney. And it is derivative is worker. And I'm going to set a limit lead for we can only use 50 characters. And also I'm going to use not null constraint. That's it. And now I'm going to set the primary key to this table. So I'm going to type primary key instead the parenthesis. And I wonder primary key CID column. So I'm going to type CID. If I execute this command and reload the schema section, hesitancy the given name, city. And if I show you my table here, you can see it's totally empty. And now I wanted to add value to this table. And I'm going to insert for city name in this table. So I'm going to type insert into our table limb city. Then inside the parentheses, I'm going to pass only seating him column city, name. And our values are inside the pan. This is our first city name is Billy coma. Our density name is Kolkata. And the third city name is New York. And our last city name is London. And semicolon. To end this line, if I execute this code, it going to add all the data in our table. Let's execute. It. Executes successfully. If I show you my table, here, we can see all the city names. If you notice. Here you can see I use on the one column name which is seeking Him. Do not use COVID to insert data. Because when we create the table, we use auto-increment for CID column there. So I, we don't need to provide the value for CID column. It's automatically increase. And now I'm going to create our second dividend parcel. So I'm going to type personal. And our first column name is ID. And its derivative is also int. And I use not null constraint and I don't want to auto increment it. So I'm going to remove this one. And our second column is name. And also I'm going to use worker datatype for this. And I use NOT null constant for this. And the third column name is percentages. And use it at IVs, int and int. And also I'm going to use NOT null constant comma. Our fourth column name is age. And I'm going to use Indira day for this. And I don't know unbelief this column empty so I use not none. Or next column name is Zinder. And it is relative is worker. And I want to pass only one character. And also I don't want to leave it blank so I use NOT null. And our last column name is students city within a city. But here I'm going to use in datatype and you know the reason. And also I'm going to use not null constraint comma. And now I'm going to sit primary key for this table. And I want to set primary key ID colon comma. And now I want to create the foreign key. So I'm going to type foreign key. Then inside the parentheses, we need to provide the column name, which I want to make foreign key. And I want to make foreign key as city column. So I'm going to type a city. And now we need to provide a reference. Which table I want to use. With that, we need to provide which column we want to use. So I'm going to type reference and our Heavenly City. And if I click on the CGT, will hurricane see a column name CID, which is our primary key. And I'm going to use this column. So inside the parentheses, I'm going to type CID in COVID. That's it. If I execute this command and refers the Schemas section are mutable personal, so our code executes successfully. Now I want to add data to this table. For these, I already create some dummy data. So I'm going to paste this damage to her hair. You can see my data and you already learned about it, how we can insert multiple data in our table. So 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 for four key primary header. You can see I use same ID for nasa and Lucas. So I'm going to change four to five. And then I'm going to run this code. Here. You can see now it executes successfully. And if I show you my table, heritance see all the data. So you can see it create our table and store multiple values. And here you can see a column name is CT. We store multiple ID in our city column because this is our foreign key column. So this is the way we can sit primary key and foreign key in a table. So now you can think, we can see only ID, not the city name. If you want to show the city name, when we select the city. For these, we need to use join clause, and we are going to learn about it in our next video, how we can combine our table using join clause. Thanks for watching this video. See you soon. 29. Mysql Inner Join Tutorial: Hey guys, Good to see you back. In this tutorial, we are going to learn inner join clause. Here you can see we have four different joints in MySQL. Inner join, left join, right join and cross join. And in this video, we're going to cover inner join. And I will cover the remaining three in our upcoming videos. So let's see what is inner join. Here you can see a table, table 1, and we have another table, Table 2. If I combine this table, you can see a black stroke area. This is our common data which is available in table one and table two. So if you want to extract the common data, both of the tables, we can use inner join. So the inner join selects records that have matching values in both tables. Let's see an example. Here you can see a student table and CT table, inner 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. And this is our foreign key column. And now I want to extract the common data between those table. For this, we need to use inner join. Here I'm going to match city column with CID column. If I add another city to our CT table, Acura. And you can see in the student table, no one have I deform in the city column. It do not exist in the city column. So when we mess this table, it never ever going to return. Anyhow, it will show only related data. So let's see the syntax, how we can type in a joint cost only to type select command. Then you need to provide columns name. And 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 join command. And I undo join table to table 1. So I type table two. And then you need to match between two tables or these, you need to type on Tuareg Table 1 dot column name. And we need to provide the foreign key column equal to W2 dot column name, and which I said primary key in another table. If there isn't common data between these stable, it going to return this data. So 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 MySQL workbench application, and I also opened them control panel. First I'm going to start Apache, and then I'm going to start MySQL. Now servers ready for connection. So I'm going to hover on my connection named demo, and I'm going to double-click on it. Here you can see we have two table in our database, CT and personal. If I show you my personal table, you can see we have six column in this table. And if I show you my CD table here you can see we have two columns in this table, CID and city name. So fast I'm going to insert some dummy student data in our personality will. And they already create common for these. And here I'm going to paste this command. Insert into table name is personal. If I execute this command, here you can see is returned an error. We cannot use do we get entry for ID? So I'm going to type five and then I'm going to execute the command. Now it executes properly. If I show you my table, here we can see we successfully insert our dummy data in our table. Let's back to the table. Here you can see the city IDs. We stored the city IDs in our personality will. And this is our foreign key column. As you can see, we cannot see the city names or just see some numbers means their ID. And we do not know from the ID that the student is from which city. Or these, we need to use inner join. So first I need to type select command, and then we need to use star because I wanted to see all the column from both the tables. And then I'm going to type from and our first divinities, personal, personal. And then I'm going to use inner join. And then I'm going to use our second table name city. After that, we need to use a keyword name on. It means where do we will match both the table. And then you need to provide both the table's column name. Where do you want to match? So from personal table, I'm going to use SET column. So I'm gonna type personal dot FCT. And I want to mess this columns with our second table, name, city, city, taught, CID column. That's it. So I type select star from personal. This is our festival inner join, and this is our second table. And then I use on keyword. I want to match a city column with CID column. This is our primary key and this is our foreign key. So if I execute this code here, you can see all the columns from both the tables. Here you can see CID and city name. These two columns came from CD tape in. And also you can see a city value and CID value is identical. And now I'm going to use a nice name for this table. Otherwise, every time only to type whole table name personal as P, city, as C, p for personal, safer city. For now, they are temporary names. And now I'm going to replace personal with T and G with C. And if I execute the command, once again, you can see the same result. So our common becomes smaller after using allies name. If you notice here you can see two columns, is city and CID. For now they're unnecessary columns. And I don't want to show it in my table. After Zinder, I just wanted to see students city name. I don't undo see a CT or COVID for this. I'm going to remove star sign after select. I want to show ID column. So I'm going to type p dot p means our allies name, ID. And then I want to show student name, P dot name after name. I want to show their city. So I'm going to use cd ls theme. See dark city name. This column is from CT table. So we use C dot city name. So 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, ID, name and city name. So here you can see how can we use inner join to see data from both the tables. And now I'm going to show you we can use where clause with this command, with the select command. So I'm gonna type where. And I wonder see those students who are from London where city name equal to London. So Hara 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 whatever I gloss. So I'm going to type whereby I want to order this column by their name. So I'm 100 times p dot name. Thus it, as you know, our name column is from personnel table, so I use P. If I execute this command. Here you can see Emma comes first and dense comes meet. Our name is printed from ascending order. At last I wonder told you one thing. We can remove inner keyword from inner join keyword by default, it's going to run inner join. So if I execute this code, you can see, we can see the same result. And this trick only work for inner join. So I hope now in adjourn concept is clear for you. Thanks for watching this video. 30. MySQL LEFT JOIN: Hello friends, good to see you back. In this tutorial, we are going to learn two new clause, left join, and right join. In our previous video, I told you, basically we have fortune in MySQL. Inner join, left join, right join and cross join, and recover inner join in our previous video. And in this video we are going to cover left join and right join. So first, let's try to understand what is left join and why we should use this left join. Here you can see a table, table 1, and you can see another table. Table to just assume that table one is elliptical and they will do is overwrite table. And now I want to extract kind of data, which is common both of this table. With that, I want to see all the data from table 1. For this, we need to use late join. The lip join returns all the records from the lip table. Also match record from the right table. Just remember one thing. If the data do not match between table 1 and table 2, it not going to return all the data from table 2. It's going to return all the data from table one. Let me show you an example. Hesitancy. We have two table, Student table and Sydney David from city table, CID column is primary key. Similarly, from our student table city column, either foreign key. Suppose Smith forgot to provide a city name. But if I use inner join, 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 written the data. In case I need to see all the data from student table. No matter if student forgot to provide his city on, not in that case, we need to use lip join. This going to return all the students from student table. And if student for what their city, they need street and city name. Otherwise, it's detect null in their city column. Let's talk about syntax. First unit to type select command. And if you want to see all the columns, you can use star sign. Otherwise, you can provide the column names. Then into type from QR tab1. Tab1 means our elliptical upward that we need to use lip join command tab2. They went to ease our active in. And if we want to create joint between them for this, we need to use on QR, Table 1 dot column name. Here we need to pause the foreign key columns from table 1 equal to table two dot column name. And here we need to pass primary key column from table two. So let's try to explore it how we can use lip join. Here you can see on my screen I open MySQL workbench application, and I also opens them control panel. I'm going to start Apache, and then I'm going to start MySQL. And now our servers ready for connection. So I'm going to hover on my connection named demo and double-click on it. And here you can see we have two tables in our database, personal and city. If I show you my personal table, here you can see we have six columns in this table. Id name, percentage is Zinder and city. And we set foreign key to our ACT column. Similarly, we have analytical name city, and they made CID column primary key. And now I want to extract LEP gene record from both this table. For this, we need to type select star from personnel. And I'm going to type lip join. And our second table name is city. To match both of the tables I'm going to use ON clause on. But before I'm going to use allies name for this table, personnel as P, city, as C on p dot. Our foreign key will SET and I want to match, we'd see dot our primary key column, CID, their seat. If I execute this code, hurricane, see all the data from both of the tables. And now I want to remove Superior Court from personnel table. But I can't do it because I used NOT null constant for this column. And also I made this column foreign key. That's why we cannot remove data from this column. So we need to change it. But for these, we need to use Alter command. But for now I'm going to use order command. I'm going to use inbuilt tool in the software. For this. We need to go to this table and we need to click on this sitting icon. And here you can see all the column from the personal table. And also you can see the datatype from this table. And here you can see the, all the constant name that we can use in the table. And anions mean NOT null. For every column. I use not null constraint because I don't want to leave it blank those columns. So now I'm going to uncheck this acidic column. I'm going to use NOT null constant from this column. That's it. Now we can still null value in this column. And also I made this column foreign key. And we need to remove the foreign key. So. You can see option name, 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. So I right-click on it, and I click this option, delete selected. And now we need to click on this Play option to apply these changes. So I'm going to click on it behind the scene, it going to run Alter command. And here you can see change command, a city. And for this column, we're going to use null value. And also it going to drop our foreign key. So I'm going to execute this command. So I click apply AI and it's applied successfully. And now we can store Melville in this column to store Melville who need to use update command. But for now, I don't want to use update command. Suppose I want to remove city from Emma. So I click on this column and I'm going to click Edit icon. And now I'm going to change MR. blank. And now I need to click on this option. Apply changes to record sales. And I'm going to apply it. And it is going to run this command, just student dot personal, set a city blank where id 3. And I'm going to apply this command. Finish. After applying. It says 0 value in this column. 0 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 is 00 means Melville. So I know to record from this table. But if I run this left join command once again, even then, it will show us all the data. Let's execute it. Here you can see it written all the data from both of the table. Here you can see it written AMA and Lucas data. But the data of this two student is not matching with any city name. So as I say you earlier, lepton going to show all the records from liftable Min personnel table. Either it match with CDD will or not. It going to return matching record. Also it going to return null record from the left table. But if we use inner join for this table, so I'm going to type inner, inner join and I execute this code. Hesitancy. It do not return Emma and Lucas because they do not provide COVID in their acidic column there. So I Inner Join do not return those students. And if we want to return all the student from the personalty will, in that case, we need to use live join. And once again, I'm going to type lip join. And if I execute this code, you can see, you can see it's written all the student from personal table. So now it's clear for you what is the difference between live join, an inner join. And now I don't want to see this column upper gender. I just wanted to see city name. For these, we need to remove the star and we need to provide exact column name. First I wanted to see name column and the name column came from personal table. So I type p dot name. With that, I want to see their age, p dot age. And now I want to see the city name. So I'm going to use C dot city name. City name column came from CT table. That's why I use C, because C is the Allies name of CD table. And if I execute this code here, you can see Heracles he only those column which we want to see, name, age, and they're sitting in. Not only we can use a where clause with that. So I'm going to type where age greater than 20. If I execute this command, you can see only those students whose age is greater than 20 years old. And if we want to run a water right command, yes, we can just in wintertime, just going to type whatever by. And 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 start from a then e, il in is. So this is our left join. We mainly use to extract all the data from our lip table. And also you're going to return the matching data from both of the tables. Let's talk about are another clause, right? Join. 31. MySQL RIGHT JOIN: Who understand right, join, we have quotable TB1 and TB2. Tb1 is our elliptical and table to either the right table. And now I want to see all the common data between these two table. With that, I want to see all the data from table 2. No matter if it metric to build one on, not for these, we need to use, right join. The right join returns all the records from the right and match record from the lip table. Let me show you an example. Here you can see our previous tables. Student you will NCD table. And you can see that is no student who came from agora means ID four. No matter how, I just wanted to see all the city name from CD table. And according to city name, I 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 Melville in their name column. For this, we need to use right join. And if I show you this thing takes, everything is remains same. Just only to type, right, join. That's it. So let's explore it practically how it's 4k. Once again, I'm back to my MySQL, which application? Now just an intro type, right, join that seat. If I execute this command here, you can see all the records from city table. As you know, our reactively city basically digest going to return all the data from right table. So if street on all the city name from city table, as you can see, we have no student from personality will who are came from New York City. That's why you can see null value in their name column. It's going to play the opposite roll-up lip, join. It, going to return all the matching data with that. And also it can return all the alveolus. It going to return all the city name from CD table no matter if we use it or not. So I hope not clear for you what is right, Jon and Joanne. What is the basic difference between right, join and let Joanne, thanks for watching this video. See you in the next tutorial. 32. 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, left join, right join and cross join. And to learn inner join Libya and Regine in our previous videos. In this video, I'm going to cover cross join. Let's try to understand what is Christian meaning? Hair. 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 add one. So cross join, going to join all the cities with Edwin. It going to return four different row. Next it going to join Smith with every city name. And it is going to follow the similar state for Sophia. And also Emma is going to make a combination. Here you can see we have total four student in our student table. And also you can see we have for city name in our city table. And if we join this table using cross join is going to return 16 different result means 16 different combination. And it's going to return result, something like that. Here you can see for Adwan, it creates for combination. First one is delete the London, New York, and Agra. Similarly, it's orchids me. And this is the use case of cross join. It's not a very important join that we can use in our project. Basically we use inner join, left join, and right join. So let's see the syntax. Post. You'll need to type select command, and then you need to provide columns. Otherwise, you can use star sign then from table 1. And then we need to type cross join. And we need to provide our analog table, table 2. And if we execute this command is going to return combined result of two tables. So let's see how we can use it practically. Here you can see I'm back to my MySQL workbench application. And also you can see I already create two tables, city and personal. Here you can see we have four record in our city table. And if I show you my personal table, you can see we have five record. And now I want to extract cross join from both of the tables. For this, I'm going to use cross join. So under Type, select star from our table limb. And our table name is personal. And I'm going to use cross join. And then we need to provide our second table, City. If I execute this command here, you can see, here you can see we get lot of communication 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 personality when they're so i, it create 20 combination. We can get the same result without using cross join. For these, I'm going to copy this command and move to another tab. And 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 these 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, a hope. Now you can understand what is caused join. It's basically create two tables combination. It's not a very important join is, is, is very rare. Thanks for watching this video. 33. 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, table 1. With that, we have another table, table 2. And now we want to extract common data between this table. And we can use inner join to extract this data. And we learn about inner join in our previous videos. But there I joined only two tables. Suppose we have another table, Table 3. And now I want to extract this kind of data, which is most common in table three, TB1 and TB2. These we can use inner join between table three and table one. As you can see, her I join three tables together falling the similar procedure we can join for table five, as much we want. There is no limit for this join. So let's see the real example. Why do we need this kind of joints? Here you can see three tables, student table, programming language table, and city table. And as you can see, we have five column in our student table. And you can see we have some numeric values in Sydney column and PL column peels mean programming language, this mnemonic villus or primary key, primary key from language they will NCD table PID is our primary key column. Similarly, CID is our primary key column from CGT will we said this primary key because they are unique IDs. But the problem is when we extract all the data from student table is going to written city ID and programming language. It not the name. So we cannot find out from his city this student has gain. Similarly, we cannot find out the programming language name. So for this, we need to use some multiple joints. So we need to join PL column with PID column, and similarly, we need to join city column with CID column. So let's see the syntax, how we can join multiple tables. First we need to use select command, then you can sell it columns. Otherwise, you can use star sign from table one, Inner Join Table 2, and then you need to use on cure to join the tables columns, they will one dot foreign key column equal to from table two. We need to provide primary key column. And you already LAN this syntax from our inner join tutorial. And now I want to join our third table with Table 1. So once again, we need to use inner join command in R join table 3. Similarly, we need to use on cure Table 1 dot foreign key column name equal to Table 3 dot primary key column name. Just one thing you need to remember to V2 dot column name and Table 3.com name must be unique. This would be primary key. It should contain unique values, otherwise, it going to written wrong result following the similar process view, we can add more tables. Also, you can use leptin or right join to join multiple tables, just unit to replace inner join with lip joined, and everything remains same. So let's see practically how it's work. Here. You can see finally, I'm back to my MySQL workbench application. And they also open zoom control panel. First, I'm going to start Apache, and then I'm going to start MySQL. Now our servers ready for connection. So I'm going to hover on my connection name demo and double-click on it. Here you can see we have three tables in our database, sitting language and personal. You can see in our personal table I added two column is city and language. These are our foreign keys. And I save some IDs in this column. And this ID some came from CGT will 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 CD table, CID is our primary key column. And also you can see the city names. And now I want to join all the three table records. I wanted to see all the student record using inner join. So let's type the command, select star from personal minora first table. And then I'm going to use inner join. And I want to join me CG table. And I'm going to take allies name 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. And as you know, using on command, we can join our tables. So personal dot a city column. And I'm going to join this column with C dot CID column. And if I execute this command here, you can see it's joined output tables, personal and city. Because hell, you can see CID column and city name column. And now I want to add our third table for these, I'm going to use inner join command, inner join, and our third table in layman's language. And also I'm going to take a nice name for language ill. For joining this two table. I'm going to use on keyword on P, min personality will Dar, I want to join me Language Table, languages equal to. And I want to join this column with this ID. Lid means language ID. So back to my personal table. And I'm going to use Elyse name L dot LID. So we can join our three tables successfully. This is our first join with CGD will, and this is our second join with language table. So let's execute the command and see what happened. Hello can see we successfully joined our three tables. Here. You can see these two columns from CD table and these two columns from language table. As you can see, now this 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. So from personality will, I want to see ID and name, student ID and screen name. So I'm going to type select p dot p means alliance name of personality will id comma p dot name. After that, I wanted to see city name from CG table comma, C dot city name. And then I went to C programming language name from language table L dot illimitable. 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's looked very clumsy. Not only that, we can use where condition with that. And now I want to see those students who select JavaScript language. I'm a 100 times higher l dot l m equal to inside the quotation JavaScript and semicolon to end this line. If I execute this command. Now you can see only two students, Smith and Lucas. Because this slick 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 tables we have. And if you don't want to use inner join, you can use region otherwise leave join. That's totally on you. So thanks for watching this video. See you in the next tutorial. 34. Mysql Group By & Having Clause Tutorial: Hello friends, nice to see you back. In this tutorial, we are going to learn groupby and having clause here you can see on your screen we have put our two tables, student and city table. In our student they will have five columns, ID name, agency, and we have another table, city and heavy store city names. And we save COVID in our student table. And now I want to find out how many students came from which city, something like that. According to the personal table from Dili, we have on students, from London will have two student, and from New York we have one student. Here you can see London ID is two, and this IDEs exists to time in student table C, D column there. So I return total students WHO and hesitancy ID for agora. And it doesn't exist in Student table. And that's why in our result, it don't provide the city name. So when we need to extract this kind of record using select command, we need to use group BY clause is going to group cities, the group by statement groups row that has the same values into summary row, like find number of students in each city. And goodbye statement is often used with aggregate functions. Count, max, meaning some average, etc. When we use good break loss, then also need to use aggregate function. And we learned about aggregate function in our previous videos. So aggregate function is compulsory with groupBy. And it told you one thing, when we use groupby, joins are not mandatory, you can use it only one table. Let's see the syntax. First you need to type select 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 where clause. And it's not military to use dwells. And you can use group by either worthless unless you need to provide goodbye. And then you need to provide the column name where you onto group. And if we want to use GroupBy, which joints we can, Let's see this index, Select columns name from table one. And if you want to use inner join, you can inner join table 2. Then on keyword, on Table 1.com name means foreign key equal to table two.com name men's primary key. And if you want to use condition, It's up to you. You need to type goodbye and you need to provide the column name. So let's start the practical and see how it's org. So finally I am back to my MySQL orange application. And I already created a table and impersonal, as you can see, we have total five record in stipple. Hesitancy to column is city and language hair. You can see the IDs that we are fitting from other table. And now I want to count student by their city. I just want to extract how many student came from each city. And if I show you my CDD will, hello, can see we have total for city, Delhi, Kolkata, New York, and London. So I'm going to type select city, coma. Now I'm going to use aggregate function count. Inside the parentheses. We need to pass the column name from personal table, where we save cities ID. The column name is a city. And then I'm going to type from and are telling them is personal. I just want to extract how many students came from its city. So I'm going to type groupBy. And then we need to provide the column name, which is city. And if I execute this code, it going to return two columns, city and county. So let's execute the code. Hey, there is a silly mistake in our code, our column names as city, not CT. And if I execute this code, now you can see the desert from Id1, Minh City one, we have one student from ID to Minh City two, we have one student from ID3. We have wants to them. But from before we have two student. And if I show you my CDD, will hurricane see our ID photos London? And if I show you my personal table, as you can see, Smith and Emma came from London, means ID for there. So why it's written? I default count two. Now the problem is we cannot see the city names, so we need to use joins to see the city names. So I'm going to join the City table with personnel table. So first I'm going to take a allies name for this personal table P. And then I'm going to use inner join. And now we need to provide our second table limb, which is City. And I'm going to take a nice name for city, which is C. Next we need to use on keyword. It's been hard to join our table. And I want to join personnel dot is city column, we CT dot CID column. This is our foreign key column, and this is our primary key column. And now we need to provide the exact column name, which I want to see. If I show you my CD Will. You can see alumna, Ms. city name. So heroin you to provide sitting in C dot 16 and also a need to change you till week in our counts section, just wanted to provide p dot city. I just assign this acidic column from personal table. So such temporary execute the code. If I execute this code here you can see, now we can understand this result properly from Delhi, one student from Kolkata, one student from New York, one student from London to student. As you can see, it's not a proper column name. So I'm going to take a nice name for this column as total. And if I execute this code once again, now you can see our column name is total. So now we can understand how many student came from each city. And now I'm going to show you how can we use whereClause with groupBy and we need to use workflows before the group BY. So I'm going to type where p dot cinder equal to male. So I use a condition and I understood all the students who are male is going to return tos two then from a CT. But there is a condition students would be mean isn't going to count female student. And if I execute this code, hesitancy from Kolkata, one male student from New York, one mil Sudan, and from London, one male student. As you know, we have two student for New York. But according to the while condition is student one student means one male student. So I hope now it's clear for you, how can we use wear clothes with groupBy? Just you need to remember, we need to use whereClause before the group BY. And if you want to ordering this table, yes, you can. After groupby, just type what are buy. And I want to order by their city name. So I copy this line. And if I execute this code here, you can see is print out sitting in ascending order. So we can use orderBy, groupBy, just unit, remember, a prayer group by unit 2 plus water by. So this is our group by clause. Let's talk about our having gloss. We already learned how to extract total number of student using group BY clause. And if we want to set a condition in this result and tried to extract a new different result. I want to extract those seeking games 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 close with groupby. We cannot use this having clause anywhere else. So let's see this index. First we need to type that command and then we need to provide columns name from table name, group by columns name after groupby, then you need to provide the having condition. Just you need to remember one thing. If you use higher condition, then you need to use it before the group BY. And if you use having condition, then we need to use up toward the group by and having condition based on this group result, not the table columns. So let's see practically how it's 4k. So first, I want to remove the condition. I don't need this while condition for this example. And 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 synonyms 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. So I'm going to copy this counts section and paste it here greater than one. So this is our condition. If there isn't more than one student in a city is going to written this city name. And if I execute this code here, you can see it's written only one sitting in London. So this is the only city that we have more than one student. So I said this condition using having clause, not a where clause. If we want to set condition to group BY result, then we need to use having clause. We cannot set hair clothes in the group BY result. And last thing I wanted to say, we can use any aggregate functions like sum, average, etc. So I hope now it's clear for you what is groupby and having clause. Thanks for watching this video. 35. 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 learn exist and not exist clause. Here you can see on your screen we have two tables, student table and city table. Instrumental. We have four column ID, name, age and city, and inner city table. We have only two columns, CID and city. And now I want to see those chlorines 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 wanted to see student name who are from New York, not their ID, not their age. Something like this. It's mean join is not necessary for this command. But the quotient is, if I don't use join, then how can I search city names by their ID? In actual life, we're going to search with their names, not their ID. In that case, we use subqueries. Actually, we sit quietly 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 a where clause and instead the whereClause, we need to provide another query for another table and recalled 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 min student table. And then we need to use a where clause. And 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 the another query. And this query going to search result from tab2. And inside the conditions section, we need to provide the condition means which result I want to search. And I also told you one thing. This query not only run with select command, it's also work with insert, update and delete command. So it's work with every main command. So let's start the practical and see how we can use this subquery. So finally, I'm back to my MySQL workbench application. And they already start my them silver hair, you can see a database named student. And insert this database. We have total three tables, city, language and porcelain, hair. You can see I opened my CD table and we have total four record. And we have total 416, Delhi, Kolkata, New York, and London. And if I show you my personality will hair. You can see we have total five records. And here you can see a column name is CT, where I save CDs ID from CD table. And now I want to print those students name who are from London. But without using joining for this, who need to use select command with subqueries. So first I'm going to type select name from our table, nim personal. And now I'm going to use her gloss. Her. And I went to searching a city column. So I'm going to type a city, a city. And I want to serve city by their city names. If you notice here you can see we don't store seeking him in a city column, will just told their IDs from CD table. So for this, we need to use subquery equal to inside the parentheses. And inside the parentheses, I'm going to type select CID column from CD table, CID from our table limb city. And we need to use another her condition to select the city. So I'm going to type PR or sitting in column. So I'm going to type city name equal to. And I want to serve those students who are from London. So I'm going to type London. That's it. So first, this query going to provide the London city ID from city table basicaly, this query going to return ID for, because London's ID is for. Let me prove you that. If I copy this code and paste it here, and semicolon to end this line. And if I execute only this code, not the whole code. So I'm going to click on this icon. Here you can see is written ID for means London City. And now I want to search this ID for In our ac column from our personal table. For now, I don't need this line, so I'm going to delete it. So if I execute this command here, you can see it's student-to-student Smith and Emma. And if I show you my personal digital health agencies, 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. So first I execute this command to get the London city ID. And after get the ID is going to run this command. And if we want to serve students from multiple city, in that case, we cannot use this equal to sign. In that case, we need to use IN operator. So I'm going to use in the inside dependencies. I'm going to move the school. So with London, I wanted to search student name from. So 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, who need to use in operator. And if I execute this command, here you can see It's returned two to three student Smith, Emma, and neon. And if I show you my personal TV, hurricane sees meet an MI is from London and highs from Delhi. It's possible because just to use nested query here, otherwise, we need to use joins to do this work. So let's talk about or to new clause exist and not exist. Here you can see the command of Xist draws. For these. Similarly, we need to create the subquery inside the parent query after her condition. We don't need to provide the any columns name. We just need to type exist. And similarly, we need to use another command. Now the question is how it's work. If any single record exist in child command, then parent gamma1 going to execute means if our child come and returns any record, then parent come and show the result. Otherwise, it's shown nothing. Let's talk about NOT exists clause. It is totally opposite of Xist clause. If child command do not provide any single record, then is going to execute the parent command. And if there isn't recording child command, then it's not going to run the parent command and show nothing as a result. So let's start the practical and see how it's work. Once again, I'm back to the MySQL workbench application. And first I'm going to show you the Xist gloss. And for now I want to search only one city. So I'm going to read the leaf from this query. And I'm going to type Exist command. And now I'm going to use exist clause. So after her condition, who don't need any columns name would directly type just exist. Now the question is, what is the meaning of this query? First, it's going to run this command. And this command going to return i d4, because London city ID is for if London city ID exist in CT table. And then it's going to execute this command. Otherwise, it's not going to execute this command. And this command going to read and all the student name from the personal table. Let me show you that if I execute this code, here, you can see is written all the students from personal table. And if I search city, and if I search city who just not exist in our city table, in that case, it's going to return nothing. Let me show you that. So I'm going to type Paris. And if I show you my CD table, heroin seen, we don't have Paris in our city name column. And if I execute this command here, you can see is three to nothing. Because this subquery who don't provide any ID as a result, and this query execute if only the ID exist as a result. But if I use not exist. In that case, it's going to return all the student names. It split the opposite role of Exist command. If I run this command, here you can see all the student names from personal table. According to the query. Ip city name doesn't exist in city table. In that case, is going to run this command. That's why it's written all the student names. If not exist, it going to run the parent. Come on, that's it. So I hope that's clear for you what does exist and not exist. Thanks for watching this video. 36. Mysql Union & Union All: Hello friends, good to see you back. In this tutorial, we are going to learn two new operators, union and union. All hair you can see we have two tables, student and teacher stable. Suppose we are working with school database. And inside the database we have two tables, student and teacher. In student table, we have three columns, ID name and age. Similarly, in digestible, we have three columns, ID name and age. I want to extract a result using select command, which is returned combined result from both the tables, something like this. Here you can see our foster a result came from student table and our last three result came from peaches table. This kind of result is possible by using union all command. Basically union all going to join two different day will come on and return a combined result. Let me show you one thing. Suppose we have a screwed in Nim Sophia, and also you have a teacher named Sophia, then it's going to ignore the duplicate name. If we use on the union, then it doesn't return the duplicate record. But if we use union all, then it's going to return the lubricate record. Let's see their syntax. First we need to type select command. Then we need to provide the columns name which we want to extract from our tables. Then you need to type firm and we need to provide the table name, in our case Table 1. And they don't 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 2. So union all going to return a combined result from both the table. But when we need to use union, union all, then we need to maintain some rules. Our first release is statement within Union must have the same number of columns. Hurricane see, we have two columns for our first table. Similarly, we need to use same number of columns for our second table. And our second rule, the column must also have similar datatypes. Suppose our first column is ID, so its data type is int, and our second column is name. It's datatype is where care. So we need to follow the same data type 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 festival. Similarly, we need to select the ID column for our second table. We cannot use the ID column, upped our name column, its mean. We need to follow the same order. So let's start the practical and see how we can use union and union all. So finally, I'm back to my MySQL workbench application. Here you can see we have commute tables, student and teacher. If I show you my student table here you can see we have totaled three students in our table, Advanced me than Sophia. And if I show you my teacher, stable arrogance, we have also strict teacher, William, James, and Lucas. And there are many similarities between two tables. If I show you my student, it will help can see we have four columns, ID name, age and city. And we use Sim column for teacher stable ID name, agency and their data with same. And now I want to extract result from both the tables using union. So I'm going to type select star from student. Then I use the union keyword, union select star from teacher table. And you need to remember one thing. If there are columns, name is different, there are data type is different. Otherwise. If they're otherwise different, then it cannot use star. If the number of columns is same and that data type is same, then we can use this star. So if I execute this code, here, we can see all the data from both the tables. The first three results came from student table and the last three result came from Stitcher stable. And now I want to see Name column from both the table. So I remove star and type name. If I execute this code, hurricane, see all the student and just name together. Now I want to add another student in our student table. And I wanted to use the name that is already existing, teacher stable. So i back to my student table and hair. I'm going to paste the code which can add a new student in our student table. And a pseudo name is Lucas. If I execute this code and show you my student table, hurricane, see a new student named Lucas. And now you expect to see seven record in name column, Lucas from student table and Lucas from teacher stable. So let's execute the code and see what happened. Here. You can see it. Don't show the duplicate record. It so 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 all. 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. So I want to select H comma H. If I execute this code. Here, we can see student name and first name with their age. And now I'm going to show you how can we use whereClause with union and union all. Now I want to see those students who are greater than 21 years old. So I'm going to type hire. Age greater than 21. And similarly, I'm going to use this where clause with teacher stable, where h greater than 32. That's it. And if I execute this command, now we 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 CD table, here we can see we have four city, the legal, whether New York and London. And now I wanted to search those students and teachers who are from Kolkata. For this, we can search with their ids. Otherwise, we can use subqueries. And I want to search result with subquery. Let me show you that. Where h equal to. So inside the parentheses, I'm going to type select command. Select CID from CT table. Cid is our primary key column from our CD table. Here you can see the column. Then we need to use her condition to select the city. So I'm going to type where city name equal to Kolkata. Let's see. So this command going to return the ID of CD Kolkata. So according to our CDD will, it's going to return id2. Hey, I do some silly mistake. I forgot to change the column name, which is ACT. Act. We have searching student from their city, not their age. And I want to set this similar condition for our teachers table. So I'm going to copy this command and I'm going to paste it here. So this carbon going to return those students and teachers who are from Kolkata City? Yes, we can search from the city's ID. After all, the subquery going to provide the same ID. That's why we use this query. So if I execute this code, you can see only one student and one teacher who are from Kolkata city at one and William. And if you want to sit in him after age, we can't do it using subqueries. For this, we need to use inner joins. So let me show you how can we use inner join with this command post. I'm going to take a nice name for our student table is inner join. And I want to join with city table. So I'm going to type City. And I'm going to take LSTM for CDW, which is C. And now we need to use on keyword to join these columns. So I'm going to type on student dot acidic column. Its 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 CG table. And we also need to change the condition hair, where c dot city name equal to Kolkata with name and age column. I want to see students city names. For these. I want to use C dot city name because our city name column is from CD table there. So I, I, you see and naming H came from student table. So I need to use is.na name and is dot h. So this code, I'm going to show this result from student table. And we need to write similar code for teachers table. So I'm going to copy the code and paste it here. And now I want to run this where condition for our teachers table. So I'm going to change the table name. Teachers. And I take t as allies name. And I use TC for city they will allies name as you know, name and age came from t-test table. So I want to replace it with t, T dot name and T dot h. And our city name column came from CT table. So I'm going to use the LST name, PC. That's it. And now I want to join a city column from teacher stable. So I type p dot ACT and CID column from city table. So I use the LSTM TC per city name from CD table is Kolkata and semicolon to end this line. So this command going to return result from student table and this command going to return result from teaches table. So let's execute the command and see what happened. Here. You can see it's written to result. First result came from student table and one. And our second result came from you just dip in. And also we can print their city names. And now I want to sort student from Dili and teach us from Kolkata. So I want to change student her condition query. If I execute this code here, you can see we have those two then were from the Sophia and Lucas. And we have one teacher, William, who is from Kolkata. So I hope nurse clear for you what is union and union all pens for watching this video. See you on the next tutorial. 37. Mysql If Statement: Hello guys, Good to see you back. In this tutorial, we are going to learn two new clause related MySQL, IF AND case gloss. Here you can see a table named student. And we have three column in this table, ID, name and percentage. So according to the person t's, I want to extract those kind of result that told me student is pus and his student is fade. Something like that. Here you can see at one is not passed the exam there. So i in result column it print fail. And those students who pass the exam, it print buzz 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. Iep student percent is greater than equal to 33 percent, 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 IEP clause. So let's see the syntax, how we can use IF clause in SQL query. First only to type select command. Then you need to select the columns which you want to see. And for your custom column, you need to type ape condition has integer type IEP clause. Then inside the parentheses, you need to provide three parameter. In your first parameter, you need to provide the condition. And according to your condition, it is going to return to result either true, either false. And if your condition is true, you can pass your message whatever you need to show in the result column. It written false. Similarly, you can show the false message. And then you need to assign a name for this column. So you need to type as, as means a lysine. And you can take your own allies name for that in our case, result, which is a temporary name. And then you need to provide the table name from table. And you can use where clause with that. So let's see the practical and tried to understand how we can use if condition. Here you can see I opened my Zim control panel and I also opened MySQL workbench application. First unit to start Apache, and then into Start MySQL. And now your server is ready for connection. So I'm going to hover on my connection name and double-click on it. Hello, you can see a database named student. And we have proton in this database, city and student. If I show you my student table here you can see we have total fiber student in our table. Advanced meet Sophia and EMEA head. You can see a column name percentages. So using this percentage column, I want to create a custom column and try to see which student is pass and history is fail. For this after select command, who need to provide the columns name. I want to see id name and parsing these ID name and percentage. And I don't want to see student age there, so I, I don't call this column. And now I want to create the custom column where I want to see the results. So I'm going to use a comma and then I'm going to use IEP condition if. Then inside the parentheses. And then we need to use these columns name. Where do you want to set a condition? And our column name is percentage. A percentage is greater than equal to 33. So this condition going to return to result true or false When pass or fail it pass then is going to return bus in the result column. And if condition is failed, then it's going to return fail. In results column. Then a per round basis, we need to take a name for this column. So I'm going to use allies name as, and our column names results. After that, I'm going to use from clause from. We need to provide the column name, which is student and semicolon to end the line. So first I type the three columns, name, id, name, and percentage, and then create a custom column name results. And I sit a condition in this column. If student percentage is greater than 33 or equal to 33, then is going to return false, otherwise is going to written fade. So let's execute this code and see what happened. Here. You can see without smith, everyone pass their exam. He just gave 29% in his exam because continent is less than 33. That's why it's written fee. So this is our if statement. Using these, we can set any condition in a particular column. And based on the result, we can create another custom column. So let's see our animal clause, which is case. 38. Mysql Case Statement: Here you can see the same table on your screen where I say Student ID and their name and their percentage. So according to the percentage column, I want to create a, another custom column which gonna tell me the grid of their result. Basically, I want to see their grade school, something like that. Here you can see according to different percentages, it's written different result in grid column. At one is Fe Smith grade is B, Sophia's grade is B plus and it has radius a. So here we need to use multiple condition, something like that. Iep student percentage is between 80 and 100. Then it's going to return a in his grade, in the grade column. Then between 60 and 80, it going to written v plus next, between 33 and less than 60 is going to return b, and less than 33 is going to return fade. So here you can see we use for different condition. So 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 gloss. So let's see this intakes how we can type case close with select command. First you need to type slipped, and then you need to provide columns name, which you want to see. And then a custom column. You need to use guess command. First, you need to type case. And for all your multiple condition, you need to use when 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 else. If all the conditions are filled, then it automatically execute the else condition. And how you can put your own custom result and also need to provide the name for that header. You can use multiple condition as much as you want. And to end this case statement, you need to use, come on. And also, you need to provide a column Allies name in our case, grade. And then you need to provide the taping limb from limb and semicolon to end this line. So let's start the practical and tried to understand how we can use case command with select command. So once again, I'm back to my MySQL workbench application. So here I'm going to use case plus. So 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 percentages 110. So we know it's not correct. And I am going to handle this situation later. So after case, I also want to in this case statement. So I'm going to type in end as. And I want to take a temporary column name for this case condition. And our column limb is grade. So between case and end, I'm going to set my conditions according to student personalities. For these, we need to use when statement, when. And then we need to provide the column name where we want to set the condition, which is passing these. When student personalities greater or equal to 80 and percentage is less than equal to a 100. If both the condition is true, then it going to print a. So if student percent is between 80 and 100, is min, he achieved a grade. And for our next condition, I'm going to copy this line. Similarly, when our percentage is greater than equal to 60 and our percentage is less than 80, in that case, is going to print B plus. And now I'm going to create our third condition. When our percentage is greater than equal to 14 and percent is less than 60. In that case, is going to print B. Similarly, in our next condition, it percent is greater than equal to 33 and less than 40, then is going to print C. And in our last condition, when percentages less than 33 then 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. Is else. Else. I want to print wrong input. Suppose someone typed wrong input, like a 110 or any string input, in that case, it going to print wrong input. So our case close is complete. If I execute this code. Here you can see the results. Here you can see at one, achieved, be great, Smith, achieve fail. Basically, Smith do not achieve anything. Sofia did grade, she achieved a grade in Hetchy p plus grade. And for me, our data into operator, into 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 close with update command. Let me show you. Suppose we need to update multiple records at once. Suppose I want to change Smith result. And I also want to correct nice result. For this, I'm going to use update command with case clause. So first of all, I'm going to remove all the statements from my editor. First, I'm going to type update and I want to update student table. Then sit, come on. After that, I'm going to set my condition and I want to update in a percentage column. So I'm going to type person t is equal to inside the parenthesis. I'm going to use case clause to set the multiple columns. And we need to update the percentages according to their ID. So 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. So often when, when IDs to then, then person does is 48. Similarly, when I D5, then 43 percent. So this condition is first meet because Smith's ideas too, and I want to update his percentage is 29 to 48 and this condition is for Mia. Her ID is five and our updated percentage is 43. And as you know, if we use case, we need to type end. And then I'm going to use where clause because we need to use a where clause with every update command. Otherwise, it's going to change all the columns. So I'm going to type where, where ID, and we need to check in multiple columns. So I'm going to use in operator in inside the parentheses id2. And I defined and semicolon to end this line. So first it's going to take id 2 and id 5 is exist or not. And then it going to set the condition according to their ID. For id2 is going to set 48, and for id 5 is going to sit for D3. And it's going to update this value in percentage column. So 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. No, Smith's percentage is 48 and Meir's percent is, is 43. It's possible at ones because we use case close with update command. This is very important. Use case, uppercase clause, tensor are watching this video. Stay tuned for our next tutorial. 39. Mysql Arithmetic Functions Intro: Good to see you back guys. In this tutorial, we are going to cover added kinetic functions. Here you can see at least these are all arithmetic functions. But in this tutorial, I'm going to cover this green functions. And I'm Don't going to cover this red function in this tutorial because these are core mathematic functions like sine theta, cos theta, tan theta, etcetera. So let's try to understand the green functions. Our first function is AVS. Avs mean absolute value. Here you can see a negative value and absolute value. If we pass one of these value in our previous function, then it's always going to return a positive value. Use the negative value or a positive value, it's always going to return the positive result is going to remove the minus sign. And our next function is flow. Here you can see we have put on two type of value. First one is 753, and second one is 7.8. Now the question is, what is the meaning of flow? Floor mean, land means below. So if you pass 7.3, otherwise if you pass 7.8 is always going to return seven. Similarly, we have another function, namesake. Sale means rou, route means what have up 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 a rounded value. Let me show you. Suppose we have two value, 7.37.8. If your value is below than 0.5, then it is going to written seven. And if your value is above the 0.5, then it is going to return eight. Basically I want to say if V lower than 0.5, then it's going to return flow will. And if above the point 5, then it's going to return sale value 7 13 is beloved in 725. That's why it's written. 7.87 is above the 7.5. That's why it is it an eight? And our next function is square root. If I want to extract square root of nine, answer would be three. So in MySQL, we have function name, square root. Inside the parentheses. We need to pass the number means nine, and then it's going to return three. And our next function is power function header. We need to provide to value, base value and exponential value. As you can see in my example, 2 is our base value, and three is our exponential value. So to multiply by 2 equal to 4, 4 multiplied by 2 equal to eight. And you know the process, how its work. And our next function is sine. If I asked any number 2, this function, maybe it's true, maybe it's one, or any positive number. If the number is greater than 0, in that case, it's going to return one. And if the number value equal to 0, then is going to return 0. And if I send any negative value which is below than 0, in that case, it's going to return minus1. And our last function is rand. Rents mean random value. It's always return a random floating point value. Suppose I want a number between one and 10. And if I use this function, it can return any number between 1 and 10. It may be seven, maybe eight, anything. So let's start the practical and tried to understand how all the arithmetic functions our work. Thanks for watching this video. 40. Mysql Arithmetic Functions: Here you can see finally I'm back my computer screen. First I'm going to start Apache, and then I'm going to start MySQL. Now our server is ready for connection, so I'm going to hover on my connection name demo and double-click on it. 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 in number 7. And I'm going to addition this number with somebody who's plus sign, then I'm Type 2. And we're gonna do in this line. If I execute this code. Here, you can see it's written 97 plus two equal to 9. Not only that, you can sit a column name for these, for these you need to use allies name. Let me show you as and our column name is total. If an underscore. Here you can see the column name CTL. And it also calculate our number nine. Here you can use any sign if I use subtraction sign and then run the code. Here you can see there is a five. We can use multiplication. If an underscore, you can see the result. Also, we can use division sign. If I execute this code. Here you can see the width 375. And also we can use modulus. When reminder, if I execute this code, you can see the remainder is 1. And also you can type M OD for modulus sign. If I execute this code, you can see the same result. And similarly, we can use division DIV if an underscore hurricane season 3 because it's do not return floating point. That's why it's written three. And now I'm going to use all the arithmetic operator without table. Let me show you. Here. You can see a table named student. And you can see we have prototype student in our table. We said that percentage and their age, CT name and their ID. And now I want to addition tin with everyone percent is. So I'm going to type select. I want to see student ID and their name and their personal needs. From our table limb student. If I execute this code here, you can see is sitting only three columns, ID, name, and percentages. And now I went to arithmetic calculation with percentages column I want to obtain with everyone parsons. So I'm going to use parentheses inside the parentheses, plus 20s plus 10. If I execute this code here, you can see it's acting with everyone percent is not only that, we can use ls 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 side, multiply by two. If I execute this code, you can see the result. In MySQL, we have a constant value, which is Pi. It's an invalid function in MySQL. So let's use this function. These, I'm going to type another solid command. Select pi is a functions, so we need to use parenthesis. And if I execute this command here, you can see it's written 3.141593. And you know the exit below pipe and it'll return a constant value. And next I'm going to show you another function, which is around summertime, around, as I told you earlier, round function going to return a rounded value is greater than 0.5, then is going to return the seal when and if they do lower than 15, then is going to return floor been let me show you If our Willie's five-point 52 means is greater than 15. If I execute this code, here, you can see it's written six. Similarly, if I use pipeline for nine, and then he was a good score. Here you can see it's written five. And if I use negative value and then execute this code, as you can see, it's written the negative five. Now, suppose we have three blue upper point. If I execute this code, we know is going to return five. And now I want to return to does it blew up additional point for these, we need to declare the disease. How was G21? I want to DCD, That's why I plus two. And now it's going to create the rounded value after decimal point, these two values. Let me show you if I execute this code. Now you can see it's written 500 and 500. 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 up to disease 97, which is 98, just 20 to pass the parameter. And we need to call the function how much G21 after decimal point. And now I want to show you our next function, which is saying, and instead the roundness is, I'm going to provide a two-point 19 hurricane. See after decimal. This video is lower than 0.5. No matter what, if I execute this code, is going to return three if the number greater than 15 and then execute this code is also written three cell function, always written the higher value. This function is not work like around IT function. Similarly, we have another function, flow, which can play the opposite role of the sales function. Let me show you if I execute this code. Here, you can see it's student goo. But if you notice upper 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 these, we need to type POW. Basically, we need to pursue parameter in this function. Our base perimeter is two and our next parameter is three. It's mean two to the power 3. If I execute this code, here you can see is written eight. Suppose or base number is 55 to the power three. So it's going to multiply 35 at once. If I execute this code. Here you can see is to turn 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. What these who need to type SQRT and inside their own verses, we need to pass the parameter and our parameter is 16. And if I execute this code here you can see the answer is four. And if I tried to exclude the square root of 15, if an underscore here you can see it's written floating-point value. And now I want to round it value of this result. For these, when you type the function name rounded and only two plus the square root function inside the round braces. And if I execute this code here, you can see it's suit unfold. So in that way, we can use functions inside the end of the function. And our next function is random function, or these just only to type R-A-N-D. If I run only this function is going to return result between 0 to one. Let me show you. If I run this code here, you can see it sit on a random number between 0 to one. So it returned a decimal number, 0.95 something, something. But if I want to number between one to a 100 for these who need to use multiple a 100, if I execute this code, notice return a number between one to a 100. It's such an 88. If I run this code once again, now it's eaten. 58 is randomly return any number. And if you go to 12, this the single value, you can use round function. Let me shorten. And we need to move this rand function inside their own risk. If I execute this code, here, you can see it's returning a random number without decimal point. If I run this query once again, now street and 40 seats without decimal point. And now I want to return a number between five to 15. In that case, we need to type 5 plus rand and type plus 10 equal to 50. If I execute this code, hesitancy is return a number between five to 15. If I run this code once again, it's the term nine. And if you don't want to see the decimal value, we can use for function. With that, let me show you Flow. And I'm going to move this red function inside the round. If I execute this code here you can see is treated a number between five to 15 is Student 2. If I run this code once again, its citizens seeks. And now I'm going to show you that 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 out ID in ascending order 12345. And now I want to print our student ID random, what these up to student. I'm going to type what are white. And now I'm going to use rand function. If I execute only this query. Here, you can see it print out student ID randomly, 2, 5, 1, 4, 3. Similarly, if I re-execute the scope here, you can see it starts from 2, 1, 3, 4, 5. So we cannot predict which id games fast. Most of the time we use rand function with water via our next function is AVS. Let me show you a. B is inside the down resist. If I pass any kind of value may be negative, maybe positive. It's always going to return the absolute Min. Suppose I tie minus 77. If I execute this code here, you can see is suitable only 77. And if I pass any decimal value with minus sign, It's also going to return the absolute 77.9. So let's talk about our last function, which is sine. So I'm going to type sign. Basically, sign can written three type of values. Suppose I pass nine. If I execute this code is written one because this number is greater than 0. If the number is greater than 0, then it's going to return one. And if I pass 0, in that case, is going to return 0. And if I pass any value which is less than 0, something minus 99. And if I execute this code, then it's return minus one. If the video is lower than 0, it's always written minus1. No matter if the video is discipline or not. It's always going to return minus1 because we pass negative bin. So this is our sine function, I hope now it's clear for you. And these are our arithmetic functions. Thanks for watching. See you in the next tutorial. 41. Mysql String Functions Part 1: Hello guys, Good to see you back. In this tutorial, we are going to learn MySQL string functions. Here you can see all the string functions. And in this tutorial, we're going to cover these green color functions. And in our upcoming videos, I'm going to complete the other functions. So let's start the practical and tried to understand how we can use this functions without slip command. So finally, I'm back to my MySQL workbench application. I already started my SEM 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, age, percentiles, and a city. So I'm going to apply our string functions in the stable. So our first function is upper. If I pass any stream to the upper function, is going to convert to this string into uppercase. Let me show you select id comma. And now I'm going to use upper function inside the parentheses. 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. So this function going to return a new column and we need to use allies name for that. So I'm gonna type as name comma, and our last column is student age. And then we need to provide the table name from student and semicolon to end the line. And if I execute this code, here, you can see it's created a new column name, which is uppercase name. And you can see all the names in capital letter at once, meet Sophia and EMEA. And we have another similar function, which is also do the same thing. Uk's, let me show you. So I'm going to type YOU CA is, if I execute this code, you can see the same result. All the names are uppercase. Our next function is lower function. It going to convert all the characters in lowercase. So here I'm going to type lower. If I execute this code. Here, you can see it converts to an names in lowercase. Similarly, we have another function which can do the same thing, LPs. Let me show you. If I execute this code, you can see the same result. Our next function is character late. We can go characters using this function. Suppose I want to count all the characters obtunded names for these. I'm going to use this function character, character Lynn. And inside the parentheses, we need to provide the column name, which is name. And now I don't want to see students age. Some do remove this column name. And also I'm going to change the name of this column which is proton. With that, I want to see Name column, some new type name, comma character lean. If I execute this command, arrogance is to rename and its column total character of this name at 15 characters meet five character, Sophia, six character for character, Mia, 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 fought from this function. If I pass a char Lynn 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 Len. The Len function not count the characters. It calculate the byte length. It's going to return how many bytes the name takes. Let me show you some. Remove this old function and I'm going to type Lynn. If I execute this code, hey, it's not work because I did some syllogistic. The lens spelling is wrong. G, th, if I execute this code, now you can see nearly 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 is going to join two string value, our StringBuilder with numeric value. Suppose I wanted to print student name with their age in a single stream. In that case, we can use concatenate. Let me show you. For now. I don't want to show student name, so I'm going to remove this column and I'm going to use concat function. And we can pursue multiple columns name inside the round braces with name, I want to concat student age. And also I'm going to change the name of this column, which is result. If I execute this code here you can see the result at 124 Smith continue to Sofia Twenty one, it prints rent name with their age and is create a single stream. And 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. Double-quotes and a space. And if I execute this code this time, you can see is a space between name NH. Not only that, we can use any character between them, suppose I want to use slash, and if I execute this time, here you can see a slash between name and age. There is no limitation with this cotton get function. We can use multiple columns to concat each other. Suppose up to age, I want to get student ID. So inside the double quotes, I'm going to pass dash, then comma, I'm going to pass ID, column ID. And if I execute this code this time you can see student name, student age, dash, student ID. Not only that, we can use strummed string with that. If I type student age. And if I execute this code here you can see it print ad one, student age can default dash ID 1. Similarly, we have another function which is concat, underscore WAS 70 use underscore W is, there is a minor difference between concat and conjugate. W is. In this function, we can pass third parameter, which is separator and it count or first parameter as separator. So I'm going to pass inside the, the whole course underscore. And in our second parameter, I'm going to pass, I'm going to pass name column, then each column, and then our ID. And if I execute this code here you can see the result, it print name, then their age, and then their ID. So we don't need to use this separator multiple time. In our previous method, it's looked very clumsy, but in this way it's look very organized. So this is the basic difference between concrete Ws and concat function. In concat WAS it stick our first parameter as a separator. Our next function is related to cream, related dream. We have total three function, L, dream, dream and dream. There. See what is the difference between these three function? So first, I'm going to type L3 and INSEAD their own brushes. I'm going to pass a string, which is student name. And before and after string name, I'm going to provide a lot of space. But if I execute this code here, you can see there is no space after and we post to the name. Let me prove you that theme. So I'm going to type another slip. Come on, select inside the quotation. I'm going to provide a lot of space. Then I'm going to type student name, student name up, just couldn't name. I'm also going to provide lots of space as name. If I execute this query only. Here you can see, here you can see it's preen student name, but it also print lot of space. But if I use interim function, let me show you. And we need to move the string inside the round braces and then execute this code hesitancy. In left side, there is no spaces. Similarly, if I use our trim and then execute this code. Now you can see in right side, there is no spaces, and you can see this space in lip side, L3. I'm going to remove all the space from the lip side and RPM going to remove all the space from the right side. And if I use simple dream and then execute this code, here, we can see it through all the space from both of the side. And Vince, come on next function, which is position. So 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 keyword i-in, in. And then you need to provide the stream. Then inside the double quotes, Your name is add one. And now I wanted to find this name or in the stream. And this function going to return exact position of this orb. So let's execute the command and see what happened if I execute this command. Here you can see is written seeks the name word, start from the sixth character of the string. Let me prove you are first vector, second, third, fourth, then a space, but it is also a character. So this is our fifth character and bills come out six character name, and that's why it's written six. But if I use the same word multiple time, then what happen? If I execute this code? It's always going to return the phosphor position. Similarly, we have another function and we called it istr. Somebody use this function. I in is TR. It's similarly worked like position function. Just a little difference. We don't need to type in keywords. And first we need to provide the string wherever you want to search. And then we need to provide the key word, what do you want to search? And this time, I want to search SQ work. And if I execute this code here you can see is written Ellie win, because is start from element index. So this is it. In our upcoming video. I'm going to go over all the remaining functions. Thanks for watching this video. Stay tuned for our next tutorial. 42. 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 this string function from SQL command. But in this tutorial, we're going to cover these green ones. So without wasting your time, Let's start the practical. So finally, I'm back to my MySQL workbench application, and I already start my SEM server and create the connection. Here you can see a table name student. As you can see, there are total five students and we have also total five columns, ID, name, age, percentiles, and ACT. I'm going to start this tutorial. We'll look at function. Let me show you the lookup function also work like position. But we can see in thought parameter to this function. So let me show you select located. At first, we need to provide the subquery and the awardees m, and then we need to provide them a second perimeter means our string. And a string is I M a student, unless you need to provide another parameter, which is completely optional. And then I'm going to take a allies name for this column. As result, if I execute this code, here you can see it's written three. It's return a index position, which is three. It's sort like position. And now I want to search only a character, not him. If I execute this code here you can see the same result. It's written three because the character start from the third index. But as you can see, there are another, a character in the string. So I want to serve, 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, its mean, how much character you want to skate. So I want to pass some of the bus three. So I want to search result up to three character, 1, 2, 3. So it's going to escape these three character then is going to serve the result means is going to search a character. If I execute this code. Here you can see, oops, sorry, we don't need to use double-quotes. And also I'm going to change the parameter forth. So I'm going to solve this result from the fourth index, 1, 2, 3, 4. If I execute this code hesitancy it's written six, because up to our fourth index, or a character start from sixth index, 1, 2, 3, 4, 5, 6. So this is the basic difference between local and position function. In look at function, we can use third parameter and we can define the position from where we want to search. If you want to search result after 10 character, yes, you can just, you need to provide the parameter here. So let's jump into the, our next function, which is substring. It's mean you can remove a part from a beak stream. So I'm going to use this function sum to remove this function locate and I'm going to type sub string. First, we need to provide the stream. In our case, I'm going to pass it, demonstrate, hey, the string is too big, so I'm going to remove this part. Lorem Ipsum is simply an experimenter who need to provide from her, We wanted to start when position. So we need to provide the index number. So I'm going to use fourth index means ie. So if I execute this code here, you can see it's written the stream without these three character. Because Henry mentioned that our string start from this character E M. That's why escape these three character and return the string from this position. If I pass seven and then execute this code. Here you can see it's written is spam is simply, Ipsum is simply. But wait, there is another parameter. Suppose I want to print only Ipsum is from the stream, so we need to pass third parameter to specify the exit physician. Let me show you coma 14. So this query going to return the 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 up to your starting index. This is your starting index, 1, 2, 3, 4, 5, 6, 7, 8. If I pass eight and execute this code. Now our spring Ipsum is from the starting index. We need to count serially like 1, 2, 3, 4. It's work that way. Not only that, we can use minus V2 in this parameter, let me show you. This is our minus1 position. Minus two, minus three, minus four, minus five and minus the x. So I'm going to type minus six. From minus six, I want to print six character. Its mean, this simple you work. So I'm going to type six here. If I execute this code. Here, you can see is print, simply work. Here our starting position is minus 6, mean this position. And then up to minus six, I want to print product six character means this warm. There is a shorthand version of this function which is serviced year. Let me show you just another type Sub STR. If I execute this code. We can see the same result. Similarly, we have another function name mean. Let me show you. If I execute this code is written same result, substring, sub STR and meet 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 sub string, underscore index. In this function simply, we just pass a tree. With that. We need to provide a delimiter. It's mean from where you want to break the string. And it's going to return a result before the breaking point. Suppose here I'm going to pass a URL, www.google.com and Dino or second parameter, we need to pass the denominator name. Its mean from where I want to break the stream. Suppose I want to break this string from this point. So inside the inverted quotes, I'm going to pass dot. And now we need to mention the exact dot hurricane see two dots. So we need to provide the index number of this character. And I'm going to pass one. Means our files dot if I use two in going to recognize these 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. And if I pass to index and then execute this code, is 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, hesitancy www dot z w is spent all the character before the second z. So this is our substring index function. Our next function is flipped. So I'm going to type leaped, basically is going to return all the string lips side of the index number. Here we can pass only two parameter. Suppose I want to print only www dot. So I need to pause fourth index. From fourth index, it's going to print all the string from the flipside. If I execute this code. Here, you can see it's printed www dot. Basically it's going to print four character from the left side. Similarly, we have opposite function, which is right. Let me show you. So this function going to return lust for character means.com. If I execute this code here, you can see it print.com. If we use Lyft function, then we can print our phosphor character. If a US right function, then we can print our lust for character. Our next function is Arpad, some new type. Our PAD. Arpad means First Aid going to count all the character from the string. And if you want to increase the character Lind 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 from 14 to 20. Some of the paths going T character in our second parameter is going to be adding our text from the right side. And 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 preen WW dot google.com, it fill the area with slash. Here you can see put all six slash because we have already 14 character in the 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 this code, It's fill the area with UI character. For now, I'm going to use slash. Similarly, L pet 4k. If I use l pad and then execute this code. Here we can see it Philly area in the lips site, which slash? And it's also written 20 characters. It's totally opposite of our pet function. Similarly, we have another function, namespace. Let me show you some retype space. We use this function rarely. Basically we do not use this function. Suppose I wonder use pip to space. So I'm gonna type 50. If I execute this code. Here, you can see it's provided 50 space. So 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. Some 100 type. Reverse basically is going to print a string in opposite direction. So here I'm going to type a string at one means, means. If I execute this code here, you can see there is it's print out string from the opposite direction. So this is the use case of reverse function. Our last and next function is repeat. So I'm going to type the function name, which is repeat. In this function, we need to pass two parameter. In our first parameter, we need to pause stream. And in our second parameter, we need to pass the number how many time we want to repeat this stream. So I want to repeat the string 4 three times. So I pass three. If I execute this code here, you can see it's spring at one means three time. If I give a space and then run the code, now it's clear for you at one means add one, add one range. So 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. 43. 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. So let's jump into the MySQL workbench application. So finally I'm back to my MySQL workbench application. Here you can see a function name, repeat, this is our previous function. So let's start with a new function name. Replace someone to type the function name, replace. Using this function, we can replace any character or a word with our new character or a word. Let me show you in our second parameter, we need to provide which character you want to find, I want to find me. And then in our third parameter, we need to provide the new string, which is going to replace the old stream. And I want to replace means with Smith. Smith. So first it going to find this keyword in our string, and then it going to replace this string with our new keyword Smith. If I execute this code, here, you can see it print at once meet. Not only that, if I use this mean GD multiple time at one means, means. Let me show you and then execute this code. Here you can see it replaced niche keyword multiple time. So this is our Replace function in that way it's work. Our next function is string compare. Let me show you. So I'm going to type string compare STR, CMP. Basically we use this function to compare strings. Let me show you, suppose Australian Navy's at one mange. And now I'm going to pass another parameter. And in this parameter I'm going to use some student name, but I'm going to use Smalley. And one means if the comparing successful, then it's written 0. Let me show you. If I execute this code here, you can see it's written 0. So both the string, our mass, and now I'm going to remove means from our same parameter. Here you can see our lips as string is greater than right-side string. If I execute this code is going to return one. Here you can see it's written one because our lives such string is greater than right side's sheep. Similarly, if I remove Minsky word from the lip side and type Minsky word in right side and then execute this code. Here you can see it sit and minus one. If our rights such string greater than Lipset string, in that case, it is going to return minus one. So this is the main use case of string compare function. Let's jump into our next function, which is field. So I'm going to type field. Basically here we provide multiple string list. And if we want to find a particular string from this least, we can do using ffill function. So here I'm going to pause multiple stream means Smith Ri comma inside the double quotes. Smith once again, from this list, I want to search message. So if I execute this code here, you can see it's written 0 because in our first parameter will need to provide the search query and our subquery is mean. And remaining values are our list items. So if I type means once again and then execute this code. Here you can see it's sweet unfold because it's written the index number of this order. This is our first index. Smith wrote ethos again, index, and Smith is our third index and means either fourth index. That's why it's written for 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 integers. We can search in teaser. We'll also similarly we have another function which is related to this function find in say, summertime, find in sit. Similarly, it's going to find the query in a list, but we need to convert this list into a sit. So I'm going to convert this list into a state. So I'm going to remove the double quotes and convert it into a sit. That's it, as you can see, now it's a complete stream. So inside this string, I want to search means cure. So if I execute this code here, you can see it's also written for. Similarly, if I search ri and then execute this code. Here you can see it's written to index. It's a sit, just unit to remember. Between coma, you don't need to provide any space because string function count space as a character. So this is our findings it function. Let's jump into the next function, which is form it. Let me show you some new type form. It will use this function, basically we numeric values. So here I'm going to take a numeric value. I'm going to type 1, 2, 3, 4, 5, 6 heads. You can see after decimal point we have total three value, but I want to return only Tuvalu after this decimal point. For this, we need to pass a second parameter and mentioned that how many you want to show up traditional point. So I'm going to show Tuvalu. And if I execute this code here, you can see it's written only 46 because it's written around it when it's always written rounded value. In our arithmetic function tutorials, I told you how round will works. So I'm going to explain it here. So this is the use case of format function. If I want to show one character and then execute this code here you can see incident 0.5 because 0.456 is greater than one for 500. That's why it's written the rounded with and it's print five. So let's talk about our last function, which is hex. So I'm going to type hex, basically is going to return hexadecimal form of any string. If I pass any string, suppose I want to pass and one, if I execute this code here you can see a HEXACO. It always written hexa from the string. Basically, we don't use this function. And if you want to use this function, you can use it in password column. So 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. 44. Mysql Date Functions Part 1: Hello friends, good to see you back. In this tutorial, we are going to learn MySQL date function. Hell, you can see at least update 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. So let's start the practical and tried to understand how we can use this function with our select command. Here you can see I'm back to my MySQL workbench application. I already start XAMPP server and I already create my connection. At first, I'm going to use current debt function, which is going to return current date means today. So 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 return. It is not our local time. It came from the server. It's returned Server date. For now we use local host server. That's why it's written our local system time and date. Similarly, we'll have the short form version of this function which is carded. Let me show you if I use current date and then execute this code. You can see it's written similar result. This function also return the current date. Our next function is synced it let me show you. It's basically written our local system date. There is no difference between current date and system did. 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 cease did, which is now, let me show you. If I execute this code. Here you can see is written similar result. It's written Server date and time. Our next function is date. Let me show you. If I pass date and time two, this function is going to return date. So first I'm going to take a allies name for this column as date, and then I'm going to pass date and time as parameter. So first I'm going to provide year 2021 dash month is and I'm going to pause June month. And then our debt is 21. With that, I'm going to pass a time. Ten colon 22 minute, 21 second. If I execute this code here, you can see it's written date on me, not the time. Similarly, our next function is MAD. Basically is going to return the month of this date. If I execute this code here, you can see it's written six, only. A wave pass, any date and time two, this function is going to return the month. Our next function is month name. Here you can see a numeric value, 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 if suddenly month name, Jew. Our next function is DAY function. If I use this function, let me show you and then execute this code. Here you can see is written the date get is 21. So this function basically eat and the date of this month. Similarly, we have another function related to this function, which is day op month. If I execute this code, here you can see the same result. Our next function is binning. Let me show you. It's going to return the week Dean him according to this date. So if I execute this code, hurricane see a student Monday, 21 June 2021 is was Monday. If I change the month and I type seven and then execute this code here you can see witnessed day. And 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 if students fourth day, as you know, desktop from Sunday, That's why we're at Nestle is the fourth day of the week. Similarly, we have another function which is related to this function. Day of year 7 group type, day of year. As you know, we have total 365 days in a year. So according to this date is going to return the day number. If I execute this code here, you can see it's written to a 100 and to our next function is a function. Let me show you if I die, Lust underscore day and I'm going to burst, February month wins two. If I execute this code, is going to return last date of this month, which is 28 February. If I use any different year, something 2020, and then run this code. Here, you can see the less debt of this month is 29 because we know 2020 is lithium ear. That's why it's 1029. For now, I compete all these functions. And now I'm going to use this functions with real database. And now I'm going to use all this query without table. Here you can see a table limb student. If I show you my table here you can see we have total five student in this table, Advanced Smith, Sophia, and EMEA. With that, you can see another column name DOP means date of birth. And I said their date of birth, year, month, and day form it. And now I'm going to use all the quality one by one. So first I'm going to type select id, comma, name. Comma DOB means date of birth from student. And now I want to extract day from this date of birth. For these, I'm going to use a function comma and our function name is DAY function. Then inside the parentheses, we need to provide the column name, which is DOB. And also I'm going to take ls name for this column as day. If I execute this code. Here you can see is Seton day from their date of birth at 1 tenth, the Smith, 23rd, Sophia, ninth, etc. And now I want to see their day name. So I'm gonna type in a name, the name, if I execute this code. Here, you can see at one born in Saturday, Smith morning, Tuesday, Sophia, Sunday, etc. So similarly, you can use all this function one by one. In the next tutorial, I'm going to cover this functions. So thanks for watching this video. Stay tuned for our next exercise. 45. Mysql Date Functions Part 2: Hello guys. Good to see you that this is the second video related MySQL date functions. Here you can see all the date functions related MySQL. 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 MySQL workbench application and I already start my Zoom server. So I'm going to start this tutorial with weak function. So I'm going to select week. Instead, the down resists. I'm going to pass the date 2021 dash. And our month is August and the debt is 21. Space also, I'm going to provide time. 09 colon, 35 minutes, colon 41 second. And also I'm going to take allies name to this column as weak. If I execute this code. Here you can see it's written, this is the 33 week in this year. As you know, we have total 52 weeks in a year. It basically return number according to this date. Similarly, if I change the month and I'm going to pause January month and then execute this code. Here you can see it's written three-week. Similarly, we have another function which is weekday. So I'm going to type DAY according to this date is going to return the number of this week. Let me show you if I execute this code here, you can see it's written three. So let's change the month eight. And once again, I'm going to run this code. Advocacy is written five, as you know, our week start from Sunday. So according to this result, this is thrust day. Our next function is Yaro week. So I'm going to type yr. Week is going to return to result year and weak according to this date. If I execute this code here, you can see first it written year 2021, and then it's written week number, which is 33. Or next function is we cough year, someone who tie week of year. This function also going to return week number of the given date. If I execute this code here, you can see similarly is going to return week number 33. Our next function is ear function. We already learned about it in our previous tutorial. So let me show you once again. If I remove this week off and then execute this code. Here you can see it's written the ear 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, etc, as we want. So I want to tie extract. Using this extract function, you can retrieve any value from the given date. Suppose we want to extract month to this date for this ONE to type month here, MAN, from this date. And also I'm going to change the Allies name week to result. If I execute this code, here you can see 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 as cetera. Suppose now I want to extract week from this date, summertime week. If I execute this code here, you can see it's sweet and 33. And if you want to extract our form this date, let me show you an execute this code if certain nine. In the similar way, you can extract minute, second millisecond, etc.. So in that way, you can use all the parameters. You can try it one by one yourself. So let's talk about our next function. Our next function is at date. First, we need to take a existing date. Then using interval will return a new date. So I'm going to type select add date. For now. I'm going to use this gate only, not the time. So I'm going to remove the time. And now I want to add 20 days to this date or this first I'm going to use a comma. Then I'm going to type it text, which is interval. After interval 20 day. Obviously I don't need this form and our key work, It's mean, I want to add 20 day with this existing date. If I execute this code, is going to return a new date. Let me show you. Eric assay is sit on a new date. 2021 or month is 09, AND gate is pin. We can add how much did we want? Let me show you. Suppose I want to add 321 day if I execute this code here we can see is written in New Year 2022, seven month. And did this eight, not only that, we can add months, let me show you. Suppose I want to add three month and we need to pause 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, etc. Here you can see the least what kind of parameter we can use this function. Similarly, we have another function which is do the same job, which is date underscore, add. If I execute this command, you can see the same result. Suppose I want to add five month. If I execute this command, arrogant see is jump into the new year 2022. So there is no difference between add debt function and debt underscore add function. So in this function, we can add value with existing date. But if we want to create a new date, then we have another function. So let me show you. So I'm going to use make date. Obviously we need to have senior command, select make debt instead the round rices who need to pass two parameters. First, we need to pass the new debt year, suppose 2023. And then we can pass a day. Suppose our days. Five. If I executing this code here Eric can see is sit on a date. Year is 2020, three, and month is 01, means January month. If we pass urine date by default, it's always written January month is going to return first month of this ear. This function can only create January month, date. We cannot post any month value in this function. So let's jump into the next function. And our last function, which is debt underscore self, it split the opposite roll-up, get underscored at function. Let me show you date underscore is you'd be served. If I execute this code is going to minus from this existing date. Let me show you if I execute this code. Here you can see is written. Here. You can see it's written much Month, 2020, 103, month and date is 21. So using this function, we can subtract month, hour, day, week, etcetera. Suppose I want to extract, can do one day from this existing date. So I need to pass the parameter. If I execute this code here you can see the result. So for now this is the end of this tutorial. In our upcoming tutorial, I'm going to cover the remaining ones. So update to underscore days, from underscore days, etc. So thanks for watching this video. Stay tuned for our next tutorial. 46. MySQL Date Functions Part 3: Hello guys, Good to see you back. In this tutorial, we are going to learn MySQL did functions. In our previous tutorials, I cover this 20 functions related date. And in this tutorial I'm going to cover with the green ones. So without wasting your time, Let's start the practical. So finally, I'm back to my MySQL workbench application. And as you can see, I already create a connection. And I'm going to start this tutorial, which subnet function gets up function and subnet function do the same thing. And we need to follow the similar process to run this function. Let me show you select. So update. Inside the parentheses. First we need to assign a date. So instead the double-quotes, I'm going to pass 2021. And our month is seven, and our days 21. And as you know, then you need to pause the interval, some time interval, interval. And I wanted to minus 100 day from this date, 100 day. And also I'm going to take a ls name for this column as date. So if I execute this code here, you can see it sit on a new date. It's subtract a 100 days from this date and return a new date. Similarly, we can use month parameter, year, perimeter, quarter parameter, etc. Let me show you. And now I want to subtract month, a 100 month. If I execute this code, it's written 20133, month 21 day. It minors nearly eight year from this date. Let's jump into the next function, which is dead deep, deep mean difference. Let me show you. So I'm going to type dead deeps. D t. D, t. If we want to see the difference between today it's in that case we use DDB function ofs. There is a spelling mistakes or less collector should be if now it's correct. In this function, we need to pass two parameters. Let me show you would need to purchase another date. And in this parameter, I'm going to pass another did 2013, February month. And our DDS 21, if I execute this code, is going to return result in dates. Let me show you. So the difference between two deities, 3,072 days related to this function, we have another function which is today's. Let me show you some to remove this function and window. And I'm going to type two days to underscore days. In this function, we need to pass only one parameter. So I'm going to remove one parameter from this place. 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 is going to shore is they'll between from one year to 2021 year. Let me show you if I execute this code here, you can see it's written seven leg tight, 8,357. We'll just use this function for education purpose. This is not a very important function. We have another function, we'll just play the opposite role of this function. And our function name is from this. 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 wanted to pause same number of days. So I want to pause the desk number here, 738357. If I execute this code here, you can see a seat on the same date. It's also provide the difference between from 0 days and put these dates and dysfunction or less written date. Our next function is period add. Let me show you some good time period. Underscore. Add. As you can see, we already possess a period. With that. I want to add five days comma five. So if I execute this code here, you can see with our old period, it add five and it's return a new period. Similarly, we have another function related period, which is period def. Let me show you. It's going to return difference between two periods. So I'm going to pass another period where instead the double-quotes, I'm going to pause 738, 1, 2, 3. 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 iss did for me it some new type date format here, date underscore, form it. In date format function. Basically we pass two parameters. In our first parameter, we need to pass a date, sand deposit date, 2020, 109 month and 20 day. So our data's 20 September 2021. But in Asian countries, first we use date. We use month and then we use year. We don't use this format. So for these, we need to use debt format function. Suppose I want to see the ear from this date. So in our second parameter, I'm going to pass percentages. Y, capital Y. If I execute this code, is going to return four digit year 2000 and 2001. And we call this perimeter did form. It will have lot of date formats in MySQL. So let me show you the list of these kit for mates, hair, you can see a jet forming. According to this date, 26 February 2000 and 2001. Most of Asian countries use this date format. And our debt for mates divided in four parts, day, month, year, and weak. And every birds come with different parameter. And recall, it did form it. Suppose you want to show dates, then we have four different formats. Let me show you. Suppose if you want before date between 0, 12, 31. In that case, you need to use percentages design. And if you don't want to show 0 before the date, in that case, you need to use percentages e sine and if you want to for daylight, first day, second day, third, fourth, in that case, you need to use Parson, this capital D sine. And if you want to show the day number between 1366, then you need to use percentages, small Jake character, and four 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 Parthenon is capital M. Similarly, if you want to show for programmed this month, in that case, you need to use parsing does B. And if you want to show the month in visit permit for this unit to use percentages, aim for January, it going to show 001, for February, it going to show 0 to something like that. And if you don't do unusual 0 before the month number, in that case, you need to use PARCC and 40 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 percentages. Why? And if you want to show lust to deceit, in that case, you need to use small OIE. And four week. We have three different formats. If you want short program 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 capital W. And if you want number of this week, in that case, you need to use small w. So similar to the date for mate, we have time format. Let me show you here. You can see at time five, our 32 meat contains second and zeros, 0 microsecond. And our time porn red it in five parts. Our minutes against men idiom and microseconds. For our, we have four different formats. If you want to shoot 12 hour clock. In that case, you need to use percentages, small edge. And if we wanted to show 24 hour time permit, in that case, you need to use percent is capital H. And if you don't want to show 0 before the 12-hour format. In that case, you need to use small g if you don't want to show 0 before the 24 hour format, in that case, you need to use capital G. And for many, we have one for me. If you want to show 0 to 60 minutes, then you need to use AI. And four seconds we have on format, which is S and Vince come medium. If you want four AM, PM. In that case, you need to use parsing this P. And Vince come microsecond. If you want to show microsecond, in that case, you need to use passing these AIF. So let's jump into the practical and tried to understand how we can use this formats. Now I want to, for this date in echelon form it. For this, we need to create a form it for date I'm going to use percent is small d parsing these small d slash, four month, I'm going to use percentages, small b slash, and four-year. I'm going to use percentages, small, wide. You don't need to remember all these parameters just unique to remember the process, how its work. So if I execute this code here, you can see is sit on a date 20 September 2021. If you'll notice, you can see is street and could visit year form it. And if you want to show four digit year form it, in that case, you need to use capital Y. If I execute this code. Now you can see the four digit year for me. And now I want to show the full month name of this date. For this, I'm going to use capital M if I execute this code. Now you can see the full month name, September. And now I wanted to show the week according to this date. For this, I'm going to use the underscore and I'm going to use a perimeter, and I'm going to type parenthese capital W. So if I execute this code here, you can see it's Monday. And if you want to show time with this date, you need to pass time here. So I'm going to pause time. So first I'm going to bust our, for our 30 minutes, 20 once against, and 30 microseconds. So we this date, I want us all the time. So I'm going to remove this week name and I'm going to use paracentesis. Small h for our colon. Percent is I4 minute. So if I execute this code here, you can see, here you can see we did it sit and time for our 30 minutes. So I hope now you understand the date formats and Vince come out last function, which is STR to date. So first I'm going to type the function name, which is STR underscore to underscore date. So in our first parameter, I'm going to pass a string format update. Let me show you. So first I'm going to type month name, which is March. And our debt is 23, and the year is 2000 and 2001. So basically a parse, a string permit of a date. But the problem is MySQL do not upset this kind of form it. So in our second parameter, we need to assign the term of this date. So here we need to pass the date format. So I'm going to type some retype percent capital M for month and for date. I'm going to use Parson D. And for four digit year, we need to pass percentage is capital Y. So after this string, we declare the gate, form it for a month. I use capital M, for date, I use a small d, And for a year I used capital Y. So if I execute this code here, you can see it's return a date. Here you can see it's return a date which MySQL support properly. We basically use this function when we get data from the user using a stable forms. And when we need to set this date in our database, then we convert this date in SQL readable format. So I hope all the debt related functions are clear for you. And you know, an extra tutorial, we are going to learn about time functions. So thanks for watching this video. Stay tuned for our next tutorial. 47. MySQL Time Functions Tutorial: Hello guys, Good to see you back in this tutorial, we are going to learn about MySQL time functions. In our previous tutorials, we'll learn about MySQL date function. But in this tutorial, we are going to work with time functions. Here you can see all the time functions related MySQL. And in this tutorial, I'm going to cover up two microsecond function. So without wasting your time, Let's start the practical. Here you can see I already started my exam server and they also stablished my connection. And I'm going to start this tutorial with currentTime function. So I'm going to type select current time, underscore time. Basically this function going to return server car in time. For now, we use local host server that. So I is going to return my local computer time. And also I'm going to take a last name for this column as time. So let's execute the code and see, and see what it written. So I'm going to click on the tundra icon. And here you can see the result. If certain current time, it's written ten a M, 43, meet 43 seconds. This is my system time. But whenever we upload our code into the server, then it's going to return the Sarawak time. Similarly related to this function, we have another function which is shorthand 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 contained underscore timestamp. We need to remove one or from this current. If I execute this code here, you can see it's 310 time with current date. And we have another function similar to this function, which is local time. Let me show you. So I'm going to type local time. This function also written local current date and time. If I execute this code here you can see the result. Our next function is local timestamp, supper time. I'm going to type stamp 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 at time from particular date. So we need to pass a date as a parameter. So inside the parentheses, I'm going to take a date with time. So first I'm going to type a date 20217 month. And the date is 21. With that, I'm going to take a time. 14 hour will be neat, 30-second. And 000 eight millisecond. So if I execute this code is going to return time only. Here you can see it's written only three format time. That's why, firstly, to return our than meat and in second. And if you want to see the color from this date and time, just unique to type our hair. If I execute this code, you can see the result if street and 14. Similarly, if you want to see the minute just type, we need function. If I execute this code, you can see the result. It returns peeled minute. And similarly, if you want to see the second, just you need to type second here. I execute this code. You can see the result. It returns 30-second. Similarly, you can extract microsecond from this time. So I'm going to jump into the next function, which is time def. Let me show you. So I'm gonna type time beef. In this function, we pass Putin as a parameter. And this function going to return difference between two time. So I'm going to pause to time as parameter. So our first Tammy's 15 hour, 54 minutes, and 32 seconds and hours. Again, time is 13 hour, 44 minute, 21 second. And also you need to move this time inside the double quote. So if I execute this code here, you can see the difference between two time is to our 10 and 11 second, we basically use this function to extract the time difference between two times. So this is it for the tutorial. In our accounting tutorial, I'm going to cover this remaining functions at time subtype, Make Time, Time formate, second to time, time to second, etc. So thanks for watching this video. Stay tuned for our next tutorial. 48. MySQL Time Functions Tutorial Part 2: Hello guys, Good to see you back. This is the second part related MySQL time function. And in this tutorial, I'm going to cover the remaining functions. I'm going to start our tutorial from a time function. So without wasting your time list at the practical. So finally, I'm back to my MySQL workbench application. And first I'm going to use at 10 function, plumbed remove time dip function, and I'm going to type at time. Basically using a time function, we can add times with existing time. With existing time, we need to pass a interval. Let me show you. So as parameter, I'm going to pass a date and a time. 2021, August month, and 23rd today. We did also I'm going to pass the time 13 our 32 feet per 2.1st microsecond. With this time, I want to add one hour, 000 minute and 00 seconds and, and 0.5 millisecond. If I execute this code. Here, you can see the result. It add 1 over without existing time. Now, our new time is 14 hour, 32 minute, 22.6th milliseconds. With that, if you want to add with this didn't time. Yes, you can just enter to pass to the next space. If I execute this code here, you can see our new debt is 25 August. So using at time function, you can add new date and time with our existing time. Similarly, we have another function which is pretty opposite rule of this function, which iss sub time. Let me show you. So I'm going to type septum here. Subtend means subtract time. If you wanted to minus some times for the existing time. In that case, you need to use sytem function. If I execute this code. Here you can see our new deities 21st August because it's subtract two days from our date. And also is subtract one over five microsecond from this time. That's why it's written 12 hour, 32 minute, 99, 99, 99 and six microseconds. Or next function is Make Time. Some type make time. Using mating function, we can create new time for these. We need to pass three parameter. So I'm going to pause this three parameter. And our first parameter is Our. So I want to pass 14 hour, can do one minute and 32 second. So if I execute this code is going to return a new time. As you can see, our time is 14 out. Don't do one minute and 32 second. And remember, you need to follow the sequence first unit 2 plus hour, then you need to post minute, and then you need to per seconds. Our next function is time format. Let me show you time underscore, form it. In this function basically we pass a format which can MySQL understand. Let me show you 14 hour, 32 minutes, and 33 seconds. Our MySQL understand this kind of time for me. And now you want to see the hours from this time. For this, you need to pass a second parameter. And inside the same parameter, we need to pass a form it. And for our, we need to proceed parsing these capital H. So if I execute this code here, you can see it's written 14. So using time format function, we can extract exact time in 1D. You can extract hours, you can extract minutes, you can extract seconds and milliseconds. Also, MySQL provide lot of time formats like our let me show you the list. Here. You can see at time five hours, 42 minutes, 28 seconds, and 00 millisecond, and it is an evening time. That's why I use Pm value. Basically, we divide our time in Fort form it, our meat seconds and microseconds medium is not a form it, but we can declare AM, PM using it. So let's start with ours. To show ours, we have total four different formats. Small h, capital H, small z, and capital G. If we use small h time for mates is going to return Gaylord time forming, but it going to add zeros before the single deceit. Suppose for a one hour is going to print 0, 1 for three hours is going to print 0, 3 hour. And if you want to show the 24 hour time for me, you need to use parsing TCE capital H. It's going to return time from 000 223. And also he's going to return 0 before the single disease. And if you use small g is going to return 12 hour time for it, but it doesn't go into print 0 before the signal is the time it is returned, 1234, something like that. Similarly, if you don't want to show 0 in 24 hour time for me it you can use Parson does capital Z. And four minute, we have only one time for me it parsing does AI is going to return result from 000 to 594 seconds. You need to use smallest is going to return result from 000 to 59 once again. And then scum microsecond for that, you need to use percentages, small leaf, It's going to return result between 000, 000, 000 from 99, 99, 99, 99. It's always written six days it will. So let's see how we can use this form it 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 210, 09 hour, but there is no 0 before the nine. And if you want to return result without 0, in that case, you need to use capital Z. If I execute this code. Now with our I wanted to show seconds, not the minutes. So I'm going to use percentages. Smallest, execute this code. You can see the result first it written nine hour, then you treat and 33 seconds. So using time format function, you can return any kind of time for me to result. If you want to show minute first, yes. You can just enter two bus pass 90s. I, if I execute this code, you can see the result phosphate written 32 minute, nine, our 33 seconds. And if you want to print any separator, yes, you can. Just enough to pass hyphen between these formats. If I execute this code, you can see the result. You can use dot, hair, slash, etc. And once again, I'm going to change our view and I'm going to pass 14 here. And now I want to show men idiom with this time. So I'm going to type pathophys P. If I execute this code, you can see the result as, you know, up to 12 hour, it's written PM. That's why for 14 hours It's written PM. So basically we use this function to improve our readability. Our next function is time to seek some real-time time to second harrowing need to pass the time. And he's going to convert this time into a second if I pass this time. And then underscore here you can see it calculate and return the seconds of this time. Similarly, we have opposite function related to this function which is set to time. Let me show you say to time as a parameter, we need to per second and it's going to return the time. So I'm going to pass 47,234. If I execute this code here, you can see it's written 13 hour, seven minutes, and 14 seconds. So using this function, we can convert seconds into a time, and also we can convert time into a second. So I hope now the time function concept is clear for you. So thanks for watching this video. Stay tuned for our next tutorial. 49. MySQL ALTER Tutorial: Hello guys, Good to see you back. In this tutorial, we are going to learn MySQL Alter command. Here you can see a table named student. And in this table we have total for student. And also we have to build three column in this table, name, age and city. And 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 is the gender column in this table. Otherwise, I want to change the datatype of each column. Now our H column data viz worker. But I want to change the data type where guerre to eat. So this is one kind of modification. Otherwise, you want to reorder your column name. You want to move city column first of the order, a name column, lust of the water, and name column thus stop the order, something like that. So the reordering is also one kind of modification. And also if you want to change any column name, otherwise, you want to change TV limb. For all type of modification, we have a command and we'll call this command alter command. Using Alter command, we can change anything in our table. So 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. And also you can change the data type of a column if you want to convert in datatype to varchar 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 neat. And I want to replace this name with student name. In that case, you can use this command. And 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 Atlas using this order command, you can change table name, but for all type of modification will have different syntax. And do 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 datatype. And also you can add some constraint with that. Four, add a new column in a table you need to use at keyword with Alter command. And if you wanted to modify the column, you need to type alter table, table name. Then you need to type modify keyword. Then similarly, column name and data type. And if you want to delete the column just unique to type, alter table, table name, 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 table, TV limb unit to use change cured at first, you'll need to pass your existing column name and then you need to pause your new column name. And also you need to pause the data type of this column. And if you want to rename your table name, in that case you need to type alter table, table name. And you need to use rename cure. And then you need to type your new table name. So this is the introduction video 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. 50. MySQL ALTER Tutorial part 2: Hello guys. Good to see you back. As you can see, I already open them control panel. And also I start Apache and MySQL. And as you can see, I already established my connection. You can see in your left side there is the database name students. And instead this database, we have a bootable CD and student. And as you can see inside the student table, we have put all five students at once, Smith, Sophia, and Mia. With that, we have total three columns, 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 a PER table. And then you need to provide the table name and our TV limits student. And then you need to use a keyword, which is add a double d when we need to create a new column in a table. In that case, we need to use at keyword. And now we need to provide the new column name and column name is Emil. And 40 mil. I'm going to take work at datatype. And I'm going to take total to a 100 character for email lists, 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 reload my table, as you can see, there is the new column name e-mail. So you can create new column using Alter command. Not only that, you can reordering these column, not only that, you can reorder this column also, suppose you want to show the column up and 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. So in that case, we need to type alter table TAB limb, heroin need to use modify keyword, modified. After modify, we need to provide the column name means which column we want to modify. In our case, I wanted to modify email. Also, I'm going to use same data type for this column. Then we need to use another keyword, which is adapter. So here I'm going to tie up after. Next, we need to provide the column name. Upon which column you want to put this email column. And as you can see, our column name is name, someone to type name here. So let's execute the code and see is it work properly or not? I execute this code and show you my table and reload my table. Here you can see the e-mail column after name column. Not only that, also, we can change the datatype of any column. Suppose I want to change the data type of email column. In that case, you can use Alter command also. If I show you the data type of this column, as you can see, the male column datatype is where care. So I'm going to change the data type of this column. And now I want to convert this varchar datatype into an int datatype. For that, we need to type, alter table, student, modify our column name, email. For now, don't need this lines. And heroin need to type the data type, which is int. And then we need to pause it without character. How much character we want. For our column. I'm going to Bus 20 character. So if I execute this code and show you my table structure, here you can see our email data viz int. So as you can see, we successfully convert our column datatype using Alter command. And now I want to add some constant in this column. I wanted 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 table our TV limb student. Then we need to use accurate a double D. And now we need to pass the constant name. I want to use evenly constants. Our type uniq, unique inside the parenthesis, will need to provide the column name and column name is e-mail. So let's execute the code and see is it work properly or not? As you can see, or query run successfully. And if I show you my table properties once again, let me show you, as you can see, there is a tick mark on cuny 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. Just you need to type primary key here. And now I want to change the column name. As you can see, our column names e-mail. And I want to rename this column and I want to make it e-mail 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 name, which is e-mail. And then we need to pass the our new column name, which is e-mail ID, email underscore ID. And also need to pass the datatype, which is vernier, some group type worker. And for the melody, I'm going to use 200 character. So if I execute this code and show you my table, as you can see, we successfully changed the column name and you can see our new column name is e-mail ID. Not only that, also we change the data type of this column. Let me show you, as you can see now the datatype, these worker and it stick to a 100 character. Not only that, using Alter command, we can delete any column. Let me show you how. For this, we need to type alter table, our TV limb, as you can see, our table linens student. And then we need to type drop column command, drop column. And then we need to pass the column name. And our Columbia Ms. e-mail ID, underscore ID. If I execute this code, it going to remove the column from this table. So let's execute the code. If I show you my table and reload my table, as you can see, there is no column name, e-mail ID. So this is it for this tutorial. In the next tutorial, we are going to learn how can we modify tables. Removed tables are chimps David name using Alter command. So thanks for watching this video. Stay tuned for our next tutorial. 51. MySQL DROP & TRUNCATE Table Tutorial: Hello guys, welcome back. Once again, I'm back with a new tutorial relative, MySQL. And in this tutorial, you are going to learn two new command, drop and trunk it. We use this to command for same job. They are mainly used to delete data from the table. Suppose we have a table name student. And also you can see we have multiple data in our table. And if you use drop command to delete data, in that case, it removed all the data with the table. I want to say it going to delete the table from the database. It removed all the data, all the columns, and the table also. But if I talk about truncate command, then it's not going to do complete table 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 datatype is still remain. So this is the basic difference between these two command, drop and truncate. Let's talk about the syntax. If you wanted to use drop command, then you need to type drop table and the table limb. And if you want to use truncate command, in that case, you need to type truncate table, table limb. 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 Zen control panel and I start Apache and MySQL. And also you can see I already established my connection. As you can see in your left side, we have a database names students. And in this database we have to, I'll put able city and student. And you can see in our city table, we have total four records. And in our student table we have total five level. So at first I'm going to apply truncate command in our city table. So I'm going to tie truncate, truncate table and our TV limeys city. So I will type city then semicolon to end the line. As you can see in our city table, we have total four record. So if I execute this code and show you the CT table, as you can see, there is no record. It's delete all the records 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. So this is the US casual truncate command. Now let's talk about our next command, which is drop. And I'm going to run this command in our student table. So hell, I'm going to type drop table. Our table limb. As you can see, our table limits students, some retype student. Then semicolon to end this line. This command going to remove everything from this table. Also the table limb from the database. Here you can see the table name in our database. But if I execute this code, as you can see it, we have our table from our database. So this is the US scheduled drop table command. It going to remove all the data and the table from our database. But if we use the truncate command, it removed only the data from the table, but it remained the table structure as it is. So this is it for this tutorial. Thanks for watching this video. Stay tuned for our next video.