SQL Full Course 2026 [FREE] | Complete SQL Traning For Beginners | Advanced SQL Course | Simplilearn

Key Takeaways

What if you could look at a huge table full of business data and find exactly the answer you need to know in just a few seconds? Whether it is customer details, sales performance, product trends, or employee records. That is exactly what SQL helps you do. Hey everyone, welcome to this course on SQL training. Today, data is at the center of almost every business decision. Companies collect information from website, app, payments, customer interactions, operations and much more. But collecting data load is not enough. The real value comes from knowing how to retrieve it, filter it, organize it, and turn it into useful insights. And that is where SQL becomes one of the most important skills you can learn. This course is designed to help you understand SQL from the ground up in a very practical and simple way. You will learn how databases work, how tables store information, how to write queries, how to filter data, join multiple tables, apply calculations, and manage data properly. So, it's not just about learning commands like select or where. It is about learning how to ask better questions, work with structured data, and use SQL as a tool to solve real problems. Let's look at the agenda now. First, we'll understand the basics of databases, why they are needed, and how SQL helps work with large amounts of structured data. Next, we will learn how to retrieve and filter data using commands like select, where, and, or, and like. Then, we'll move into table creation and data manipulation where we will explore insert, delete, alter, and the role of data types. After that, we will understand joins and relationships, including inner join, left join, right join. So we can connect data from multiple tables. We'll also look at the aggregate functions, group by having to summarize and analyze data more effectively. And finally, we will explore important concepts like primary keys, foreign keys, constraints and transactions using commit and roll back. Also, if you are interested in building a strong career in data analytics, I highly recommend you checking out data analyst certification course by simplon. This course gives you an industry recognized master certificate from simply learn along with individual certificate from Microsoft that you can showcase to potential employers. A real boost for your resume. You will master key tools like Excel, SQL, Python, Tableau and PowerBI working on real projects to build practical skills and learn how to turn raw data into insights that drive better business decisions. Plus, job assist helps you prepare for interviews and get noticed by top hiding companies. So, what are you waiting for? Hurry up and enroll. Now, the course thing is mentioned below. Here's a quick quiz question before we get started. Which SQL clause is mainly used to filter rows based on a condition? Your options are order by, where, group by, or join? Let me know your answers in the comment section below. So, before so SQL is a programming language. Okay. Before we move to the programming part, we have to understand few uh terminologies. Okay, we are going to understand few terminologies and then we are going to move to the coding. Okay. Hello everyone. So without wasting any time, let me just share my screen. Now let's understand few of the terminologies. Okay. why your uh you know uh SQL is important and we although we have the Excel. So we are going to understand first those concepts. Okay. And then we are going to move to the coding part. Okay. I will also show you how to install though we have a lab. We will go for the installation as well. Okay. So also one more thing you have to remember. So I can see at now currently there are 94 participants leaving me and the Allison. Okay. So at any point of time you are not able to understand anything. You don't have to wait for the poll. you can directly ask in the chat. Okay. So every my eyes will be on the chat. Maybe I may I will be explaining something. I may take 5 minutes and I'll then take go through the chat. Okay. All right. With that thing in mind. Okay. Let's understand now. So we have the individual data, right? We individuals have some data. Suppose we have the bank data. We have our personal information. Personal information. Okay. Info. What can be the personal information? Maybe our name, our age or date of birth. You can say, write our qualification. Qualification. We have our address and so on and so forth. Okay. So these are the we have the bank deta uh data. We have that is a bank details. Okay. I'm talking about the bank details. Then we have yes we have the personal information. Then we also have uh maybe our phone data right we have our phone data, our internet data. So we are generating lots and lots of data right? Everything we are doing is generating some data. Right? Now storing this individual like on if I talk about storing this data right we can still store this data in the Excel files. Now please check here everyone focus on my screen. Now if I talk about the individual data right what are the individual information we have? We can store these things in an Excel file, right? We do store information in Excel file, right? In different sheets, we can store this data. Even though these are the individuals data, if I'm talking about the bank details or maybe the personal information, phone details or internet details, I will be able to store it in an Excel file. When it comes to the individual information of people, I'm able to store that because Excel also is capable of storing 10 lakh plus data in a single sheet. Right? It is able to store that. It is able to store that much that much amount of data. So 10 lakh plus data we can store in Excel files, right? But even if we try to store even if we try to store for individual information totally okay not a problem right we don't have 10 lakhs plus data right even we I individual people right we individuals even don't have one lakh data okay one lakh data that's completely okay for small data we are able to store it in excel files but what about the organizations what about the organization we can consider the simply uh simply learn for example where where we have so many courses running we have so many trainers too we have learners as well different courses are running so if I talk about this and apart from that we have different you know the financial statements and all those things is it possible to store all this information in an excel file Yes, if I if I want to store because see if an Excel is capable of storing this 10 lakh plus data in a single sheet right but if I talk about this organization simply learn where we have multiple information large data huge information is there okay is it possible to store it in an excel file even though we are able to store only 10 lakh data right if I talk about okay let's take again one more example that we have all of you are comfortable with we are doing online shopping right the e-commerce websites right we have the Amazon flipkart everyone is using maybe the blinket right uh or anything else right zeppto and all those things all those things every if I talk about this websites also they do have the information about the customers they have the information about the products they have information about the orders so on And there are so many information. It is not limited to any size, right? It is not limited to any size. Huge data. And when I talk about huge data and if I want to store that information in an Excel file, your file will crash. It is not possible to store more than 10 lakh data in an Excel file. And even if you feel like and you can notice one thing even though Excel has the capability to store 10 lakh data in a single sheet but even if you store five lakh data in an Excel file it will crash it will become slow so slow and it will finally crash only it will not open only right when we have huge data for huge data for big data like I'm talking about this organizations we will have need a database for that. We will need a database for that. Okay, a database is nothing but it is you can just think of it as a container. Okay, you can think of a database just as a container. Just give me a moment. Let me take a shape here. Uh-uh. Okay. Okay. A database is just like a container is a container which is used to store and organize the data. just a container. You can see here where you are having all the datas right all the data. So you are going to use a database you are going to use a database for storing huge volume of data right you for storing huge volume of data you need a database. Okay. Now the thing is once you have this database right this is just storing the it's basically it is storing and organizing the data here right all the datas are present here. Now the thing is people will be let me write here Excel file what we do Excel file we directly open and we share the file to fetch the information right we are able to directly open the file and fetch the information. But if I talk about the Excel uh databases, right? The data are stored in the container. The datas are stored in the container. So now if I want to fetch the information because the datas are present in the container here. If I want to fetch the information, okay, suppose here in my organization I have the user one. Okay, user one. Or maybe you can just take a person one. Let me write here person one. If you are confused about this, let's go for this person one. Then we have person two. Okay, the person two also requires some information from the database. Okay, person two also requires some information from the data base and also at simultaneously you can just think some application. Okay. Some websites, some websites also require some information. Okay. Require some information from the uh database. Okay. Now the persons okay or the applications the how they will communicate right the way they will communicate how they will communicate the like we are communicating in the class is uh with the English language right we are communicating with each other. We are doing this class with the medium is in English language. Similarly, if I talk about your database, right? These persons or this applications are going to communicate with this container are going to communicate with this um container. Okay. Using language SQL. Okay. they the language they are going to use the like we are doing we are communicating in English similarly these users or these persons who are going to require some information from the database they are going to use the SQL language okay so SQL I'm writing here okay instead of writing here let me go for this okay SQL this is a medium of communication okay it's a medium of communication SQL is nothing but your structured query language. Structured query language it is basically the language okay which is used to communicate to fetch the information from the database. Now check here as I mentioned this is simply a container. This is simply a container which is storing the data. It is storing the data. But here we have different users who is asking for the information. Right? It is asking for the information here. Now the thing is if this person one person two person three websites are asking the information who is managing the request who whose request should I uh prioritize first? Who should be given the access? Who should not be given the access? Will the database will be able to uh do that? Prioritize the access. It is simply a container. It is storing the information. It is just storing the information. It will not be able to do that. And these persons are communicating to the database with the medium that is your language that is a programming language SQL. The that is a programming language. Now to manage this database and the person request these persons are requesting right this applications or persons are requesting the database right now they need a middleman because it is just a container it will it is a storing holding the information able to prioritize it and they will not be able to give the permission to all of this person or application okay you need this information okay take it you need this information That decision is basically done by a software. Okay, that is done by a software known as your DBMS. Okay, this is known as DBMS. That is database management system. Let me write it here. Give me a moment. Okay. Database management system. So this is basically a software which manages the request. Now what happens is that give me a moment here. This right this directly does not query. Now this person will write an SQL query in the DBMS. Okay. And then this will pass on to the let me copy this this will be passing to the database. Okay. It will taking up the query and then this DBMS will be deciding I can change the arrow as well. Okay. I can go for the changing the arrow. Let me go for this here. I'll make it shorter here. So it is quering now the request right the persons is request websites are request the request are handled by your database management system okay now the database management system will basically manage the database that means it will prioritize the request okay show with the help of an arrow here so it is uh asking the question and then it will decide Right. Which give me a moment. Which query? Okay. Which query or request to process first? Which query or request to process what first? So it is basically you can think of it as the uh manager. It is like the manager who is managing the system because every at the same time many people are uh requesting for the information requesting for the information. this container it is just storing the information it will not able to do anything so this DBMS is basically serving as the manager okay that is a database management system give me a moment yes it's an software DBMS is a software okay DBMS is basically a software which manages the request which manages the request of the clients. Who are the clients? So these are the clients. It can be a person that means it can be a human people working in the same organization or it can be from some website. It can be any application maybe you can also connect from PowerBI or you know the visualization tool tab you can uh connect to the DBMS you can query from there and you are able to connect all those things rightuh like the thing is that permission who which request should I process first is by it's basically decided by the DBMS it's decided by the DBMS okay are you getting my Right. SQL and DBMS will okay SQL is just a language. Okay, SQL is just a language. The programming language you are going to query the DBMS. The way how are we conducting the class? How are we conducting the class? We are conducting in English. That is just the way of communicating with the database, right? You are asking the question, the way we communicate. Okay, I want this information. Similarly, the clients are going to communicate with the database. Okay, that is SQL is a uh language. It's a programming language that we use to communicate with the database. And database management system is a software which manages the request. It's a software which manages the request based upon the priority. Based upon the priority, it is basically the DBMS. Okay. Okay. On what basis it will prioritize it totally depends. Okay. Uh like the users level. So there are different users levels also. Okay. So based upon the users they are going to prioritize the request. Suppose if someone is already reading it the file so probably that is not possible to show it right now so read permission they can give it so something that is also done by the admin team okay so the administrator point of view so I'll just uh discuss little bit later about it okay first understand this part no need you don't have to note down on all the things you have to understand okay first we have to understand the environment and then we will be going to the programming language Okay. Now the thing is that please understand here we have the clients here. These are the clients. We have a database management system and here we have the database. Now if I talk about this database, give me a moment. If I have this database, let me just go for uh no fill. Yeah. Now, please check here. We have a database. We have a database management system, right? It should reside somewhere. It should reside somewhere, right? Physically, a software is a software is present here. A software is present here. Now, this place we need an infra, right? where this should stay this database management system I mean the database management system and your database should reside somewhere it need a physical system for that right it need a physical system for that for so physical system we have this server just give me a moment we have a server where your where your database and your DBM MS resides inside a server a physical system. A physical system are which should run 24/7. Okay? Which should run 24/7 here. Are you getting my point? This is just a software. This is just a software. Your data is stored here right now. So it needs a physical system to store this information. Right? So we need a server for that. We need a server for that. Okay. So currently I'll not discuss about SQL my SQL and SQL. Okay. Just give me some time. You will be understanding it in the process. Okay. I will be explaining you everything. No worries about that. Please focus on what I'm teaching right now. All right. Now let us take an example here. Let us take an example here and understand this concept better. Okay, imagine okay now think of it if I talk about if this three parts let me just remove this give me a moment let us take a real world example okay of a library okay let me go for this scroll down let us understand this database then we have uh your DBMS okay it is understand here library manage management. Okay, let's go for this library system or library management system. Let's go for this in real world. Okay, if I to go for a real system here. Okay, I hope everyone has gone to a library. In the library, we have the bookshelves, right? Okay. In a library, we have the bookshelves, right? What is this bookshelves doing? They store the actual books. Okay, let me just give you an example here. Let me write it here. A database, you can just think of it as the bookshelves where the actual books are stored. The actual books are organized organized and stored there. Yes. Used to organize books. Yes. Now this if I talk about if we have the bookshelves, we have a database here. We need a librarian as well. We need a librarian as well. What does the librarian does? What does a librarian does in the library? They organize the books. We can say, right? Then what they do? Uh helps you search helps to search the books. Search or you can also say borrow or issue the books. borrow issue issue books and also maintains the rules of the library or I can also say maintains the rules in the library right yes exactly keeps the records of the book great an manages the request documents information how of how is it taking the books etc. Yes. Ex absolutely correct. Okay. Now you can think of the librarian okay as the DBMS as the DBMS that is your database management system. Okay. Database management system because it manages the queries. manages the queries that is coming from the different users, right? Manages the queries. Then what it does? Applies the rules. We can apply various constraints. Okay? Apply rules there. It controls the rules. Controls the rules. Ensure safety. Ensure safety too. So these are all the roles of your DBMS database management system. Any confusion here? Any confusion here? Okay. Now lastly, okay, if we have a librarian, if we have a library, we will also need a infrastructure, right? Where this people will be present. Okay, if I talk about you need a library infrastructure, right? library infrastructure like the if I talk about the library building because if I talk about where will your librarian and the database will be present if we don't have a building if we don't have a building right so you can just think of it as the server here which provides the space okay to which provides the space to uh space for the database and the librarian right without the library building you cannot have your librarian and data uh library. Similarly if I talk about the server here okay if I talk about the server here what it does server runs the DBMS software. It runs the DBMS software also. You can also think it provides the CPU memory CPU memor. Okay, the server should be uh so in the server only we have to connect to run the DBMS. Now if I talk about the DBMS, right? If I talk about I'm just removing this part. Okay. Now if I talk about the DBMS, okay, so there are multiple DBMS softwares are there. Okay, there are multiple DBMS softwares are there in the market. Okay. But we are going to discuss about RDBMS. Okay. So these are the types of DBMS. So we are going to study in this course for your SQL certification that is your relational database management system. Okay. Relational database management system. Now what happens here? Your datas are stored in the form of tables. Okay. In the form of tables where you have okay multiple rows where you have multiple rows and columns okay multiple rows and columns because RDBMS stores the data in the form of tables here. Okay. Okay. Each row so it is stores the information in such a way that each row should depict or should give us some information. Each row should give us some okay and in over here right we are going to discuss about so there are multiple RDBMS software are also there okay but we are going to discuss about MySQL in this course we are going to discuss about the MySQL it is not new it is mostly used in the organization it is more than 50 years old it is more than 50 years old and it is still being used okay in most of the in we mostly go for the MySQL. It's highly valid in today's edge as well. Okay. And as I mentioned, okay, data are stored in the form of tables. Why is it relational? Okay. Because there is connection between the tables. There is connection between there is a relationship exist between the tables. So that is why it is known as relational database management system. Okay. MySQL is basically a DBMS database management system software. Okay. MySQL is a database management system software. SQL is a programming language. Okay. Am I clear? Give me a moment. Okay. It is a database management system software and SQL is just a programming language. I am sure that heard of like Python or then you have heard about Java. So similarly you have this data uh sorry my SQL. So SQL people call it SQL also, people call it SQL also. Okay, that is a programming language to communicate with the database to query the database RDBMS if I talk about the RDBMS datas are stored in the form of tables. So as I have taken the earlier information right of the customers and the orders. So what will happen here? Suppose the customer's table customer details will be stored in the customer table. So this is your customer all the information of the customer will be stored here. Okay. And suppose this is your orders table. Okay. Orders table. All the details of the orders will be stored here. Right? And there is relationship between if I want to bas basically extract the information the customers who have ordered something. Okay. In Excel what happens is that okay let me just give you an example here. Just give me a moment. Uh in Excel we store the data we store the data in a single sheet like this. Right? We store the data in a single sheet like this. All the information whatever the customer have ordered and all those things are stored in a single file are stored in a single file right in a single sheet. But in SQL we actually break that into different multiple different tables small small tables we store the information. Am I clear now? Okay. Yes, there are uh different types of DBMS as well. Okay. So like we have uh your um key key value pair as well. So here what happens? Okay. I'm not going for the detail because this uh course does not cover that. Okay. If you wish I will be also sharing any like the uh notes or something on the on your uh LMS. Okay. But other types if you want to know for it it's basically one is your key value pair. Okay. So what is your RDBMS? Then you have your key value pair. So what happens your information the data is stored as key value pair. Then you have the document database as well. Right? Then you have the where the in datas are stored in the form of documents. Then you have the colum columner as well. So lots of databases are there. Lots of databases are there. But we are going to focus on the RDBMS. Okay. Uh there is an alternate for MySQL. So you can go for MySQL server. Okay. You have the Oracle Postgra SQL. Okay. So not there for RDBMS as well. There are multiple softwares are available. You have Postray SQL. Okay. You have MySQL server. You have Oracle as well. And you uh so I missed out the server here. Okay. So these are the different database management softwares. But we are going to learn here this because it's absolutely free. Okay. But the syntax in most of the cases are same. The environment also you will almost the environment might be a little bit different but the query right the query you write the SQL queries are exactly the same. Okay. These are all the MySQL. Yes we do. Yes. Obviously this is very useful. So uh because when we go for organization as an analyst you have to query the database right you have to extract the information a lot. So for that you need the MySQL right it is really useful that's what I have mentioned even like it has been using for we have been using MySQL since last 50 years and it is the most beneficial way of storing the information right in the form of tables right what happens in the Excel what even if you go to organizations excels are mostly used but what happens in the Excel is that excels the datas are stored in a very complex way in a single table you are not able to store this those information in a um like you know you are not able to store more information there it's pretty complicated but when you have a data and we can only store small data limited data in your Excel but when you talk about the big data you will go for my SQL okay all right so now we are going it is not out outdated it is not outdated is still in I started with my MySQL journey okay more than 10 years back okay but still it is being in used so as I mentioned it's more than 50 years old okay let's go for the installation okay RDBMS is a type RDBMS is a type of database case management system. Okay. Where your tables are uh stored, right? Where data are datas are stored in the form of tables. Okay. And MySQL is a type of RDBMS. MySQL is a type of RDBMS. I hope I'm clear. Okay. Now, please check here. We do have the lab here. Okay. But before we proceed with the lab, we the lab has certain limitations. be able to work on the lab for only um 5 hours in a day. Okay. And since most of us will be working on the lab, it may also uh get hanged and it is bit slow as well. It is bit slow as well. So for now, okay, you can also go for installing it. You can also go for installing it. I have shared this links with you in your LMS as well if you are aware of that. Yes, Raki. Great. So, please let's go for the installation. I'll show you. I'll take up your might me one of your screen and I'll show you how to install it. Okay. After that, if you're not able to do it, not to worry. We'll just spend 10 minutes in the installation. After that, what we are going to do, I'm also going to show you with the lab. Okay? So, please click on the link. Please click on the link. Any Mac users are here? Any Mac users are here? Mac system. Okay. Only one. Only Malvika. Anyone using Mac system? Okay. Aneli is there. Okay, iPad. I'm not sure whether you will be able to install in the iPad or not. Okay. So, please check here for the Mac users. Okay. For the Mac users, I have uploaded one video in your LMS. I'll show it to you. Okay. So, my system I'm using is the Windows. Okay. So please for the this is for the Mac users. Okay. For Mac users you have to install two things. For the Mac users you have to install two things. First you have to download two things here. That is your MySQL community server and your MySQL workbench. You have to download both. This is for the Mac users. Right? Go for installing both. I mean downloading both. Okay. You have to download both MySQL community server and MySQL workbench. You have to first go for the community server and the workbench. Okay. After that what you need to do Windows please wait for some time. Okay. After that I have shared one link in your LMS. Okay. Please check the window u MySQL installation for so I'm a non-mac user. So I'm using for Windows only. So that I will be uh showing here you here. Okay, for the Windows user, okay, for the Windows user, you have to install, you have to download only one. You have to download only one. Okay, please everyone for Windows user please check MySQL installer for Windows. You have to download only one. Click here. Click here. Okay. And you have to download the So once you click on the link right you have to select or download this one the first one 2.1 not the other one not the second one okay you have to go for Microsoft Windows then Windows installer MSI installer you have to go for this 2.1M M Am I clear? Okay. Now if you click on the link, okay, if you click on this download link, you don't have to do anything. Just click on the download link, you will be getting this page, okay? You don't have to get a or Oracle account there. Just go for just go for no thanks start my download. You can go for no thanks just start my download. You have installed two. Second one NL is it already installed? Okay. If it is installed then not required. So please check here. I have already shared another link. Okay. So please go for this. I have shared the link. Give me a moment. Though for the Windows user, you have to go for window uh sorry, MySQL installer for Windows. MySQL installer for Windows. Okay. Click on that. Then scroll down. You have to select the first option. You have to select the first option that is your this one. Okay. 2.1M Windows installer. The first one you have to download it. Click on that. Click on that. You are getting to this page. My SQL community downloads. You don't have to get an Oracle account or anything. Just what you have to do. Just what you have to do. Give me a moment. You have to go for no thanks start my download. No thanks start my download. If you click on that automatically you will realize it has start downloading. Check here. Small file. Your one is a bigger file. Uh our one is a small one. Smaller one. Okay, it out. Double click on the file. Please give me a moment. Okay, I got stuck here. Please, once you have downloaded it, just give me a moment. Wherever you are having the downloads, right? Wherever you are having the downloads, go for doubleclicking it. You will be getting the installer, right? Uh Bhavia, are you working on the MacBook or you are working on your uh Windows? Kindly confirm me. Bhavia, are you working on the Windows or the MacBook? Then if you if you're working on Windows, you have to install only one thing. Listen to my instructions carefully. Please. Okay. Now please check here once you have downloaded and once you have downloaded you doubleclick on that screen. Double click on that uh exe file. Right? You will be getting a choosing a setup type. There you need to select custom. You have to select custom and click on next. Go for sharing your entire screen. Okay. Please go for sharing your entire screen. I'm also unmuting you. Okay. Can you go for choosing your setup type now? first option. Click on the first option please. Yeah, please everyone check here. You are get after you double click on that right you will be getting the custom option. Please click on next. Once you click on custom click on next please click on next. Okay. Now click on the MySQL server plus symbol again. Plus symbol in the MySQL server again plus plus. Okay. Select the first one. first one. Yes. Click on the green arrow. Yes. Okay. Now, similarly, click on the minus symbol and go to the applications. Over here only. Okay. Can you scroll down, please? Can you? Yeah. Okay. Go to the application. Applications plus plus. Please click on that again. MySQL workbench. MySQL workbench plus. Okay. Again. MySQL workbench plus. Okay. Again the first one. Select the first one. Okay. Yes. Click on the arrow please. Yes. Okay. You have to select two. Okay. Everyone did you notice what did I do? Please turn please wait. Okay. What did I do? I went to this first one. Okay. My SQL server selected the first product. Okay. Similarly, my applications then MySQL workbench first product. Okay. You have to just click on the green arrow and then we will be uh going for this products to be installed. Once this is done, we'll go for next. Please click on next. So, uh Chandana just I'm unsharing your screen. I'm sharing my slide again. Let them uh help uh let me help them and I'll take up your screen again. Okay, please be on the screen. Let it download. Okay, please be on next. I'll take up your screen. Just let me help them out. Okay, everyone. Okay, let me Yeah, my screen is shared. So, those who are unable to follow that, please check. First, you have to select the custom. Double click on once you have downloaded it, double click. You will be getting the choose uh choosing a setup type. You have to go for custom. You have to go for custom. Once you have gone for custom, you have to click on next. You have to go for next. Okay. Once you click on next, what you have to do? You have to go for selecting a product type. Selecting a product type. So here you have to select the server as well as the application. So you have to click on this plus symbol here, MySQL server. You will be getting the MySQL server here. Click on plus symbol again. Okay? Until you get the first product and then you will notice that this arrow will turn into your green color. Just click on that. Similarly, you will be get doing it for the application and the workbench as well. You will be getting two products here. My SQL server and MySQL workbench. Typical customer or complete to choose it. Am I clear that I am repeatedly mentioning custom? Are you able to understand that it is custom? Totally up to you. Those who are not doing what to choose custom I mentioned right you have to go for the custom here. Once you go for see this is the custom you have to choose the first option that is your custom. After that go to the next. Okay. You have to go for the server and the application that is the MySQL workbench. Yeah, that's okay. Uh you have to go for the lab. We'll go for the lab. Those who are not able to do it, we'll go for the lab. My SQL below is slightly. Okay. Once you please everyone focus on the screen. Please everyone focus on the screen. I have already taken one screen. Okay. So once you get to this point, once you have downloaded your two products, once you have downloaded the two products, you have to click on next. Okay. Go for execute. So this will take some time. This will take some time, especially the second one, the workbench one. >> Yes, ma'am. This one. >> What did you choose for this? Can you just cancel this, please? Cancel this. Cancel it out. Fully cancel. >> Which one you have downloaded it? >> Which one you have downloaded? Can you show me that 9.6? Let's go to that uh website, please. >> So, we'll not be taking too much time on the u installation part. Which one you have downloaded it? >> Yeah. I'll tell you. I'll tell you. You are Windows users, right? And you have went for the community download. I have asked you for Windows users, we have to go for 2.1 m. So, you have gone for the wrong file. Okay. Uh, which one? The second one or the first one? >> Go back. Go back to the earlier screen. Go back to the website downloads. >> Yes. >> The last option. You have the MySQL installer for Windows. Yes, you have to install only one. It's a 2.1 m. You can check the small file. >> Okay. I'll I'll try and get back to you, ma'am. Thank you. >> Please listen carefully. Okay. Okay, please unshare your screen. Please unshare your screen. Baba, please unshare your screen. Unshare your screen please. Chandana, are you there? Can you please share your screen? Chandana. Yogenda. Please wait. What is the issue you're facing? I'm unmuting you. Jana, please share your screen. Yeah. Okay. It will take some time. Okay. Okay. Yog again. What is the issue? Please speak to me. You can unmute yourself. >> Okay. Hello. Good evening, ma'am. >> Yeah. >> Actually, what instructions you have given? I have followed the same but uh still I'm not able to install the actual what I'm trying to install. >> I'm not getting the same. Are you not getting the same option like Chandana? >> No, I am getting the same option. Okay. Now uh what to do for that? >> No, if at which stage you are right now it's the same check requirements. Check requirements. Check requirements. >> Check requirements. Okay. Chandana please share your screen. Sorry I'm doing that repeatedly. Can you share you again you share your screen please? >> Okay >> Chand it will take some time for you. Okay because that workbench installation does take some time for everyone also. You can you can share your entire screen. Go for sharing your entire screen. Check requirements. Okay. Can you adding community installer? Okay. Can you go for the select setup type please? Can I just remove the stop sharing part somewhere else? You have to Yeah. Can you just hide that? Hide that. Hide. Hide. Just next to that hide is there, right? And yeah, not this one. Not this one. Your zooms zoom screen. Okay. Can you show me the installer again? Installer. Installer. Not this one. Not zoom. Yes, this one. Actually, I'm not able to see your that screen. You know, live class uh simplearn.com. Can you just remove that part? Not this one. Not this one. Not this one. What should I say? Uh can can you just move your live class? Right. You're not getting that part. That screen sharing part is there, right? Stop sharing, hide option is there, right? So, can you hide that? Can you? No. Hide. Hide. Yes. Now, you share that screen. Installer. Go for the installer. Now, installer. Installer. Okay. Again, you're hide that. Please hide that. I cannot see your complete screen. Not this one. Stop sharing. Hide. Yes. Now, go for installer. Installer. installer. Not this, not this one. The earlier screen, the installer you have, right? The blue color installer. Just click on that installer. No, no, no, no. You have on your taskbar. You have it. Go to the taskbar, please. Yes, this one. Okay. Now, it was correct. That was the screen, right? Why are you doing it for? Okay, go for custom. Select the custom. Next. MySQL server class. First one. First one. Yes. Okay. Go for the application workbench. Yes. Yes. Next. Okay. Execute. Execute. Yes. Okay. Okay. The following products have failing requirements. Installer will attempt to resolve them automatically. Requirement marks as manual cannot be resolved. Okay. Click on each item to Okay. Please can you can the first one first item? No. Not. Yes. Here. Click. Click on this. Okay. Click on the status. It's not happening. one one item go for okay it's doing it it's happening it's happening it's happening one person yeah let it happen it will take some time what happened is that you need the visual C++ so probably that is the case yeah it's happening it will take its own time okay similarly you have to do for the others as well are you getting my point you get okay you are on mute yeah can you please unmute yourself can you please go to the zoom screen and unmute yourself. Where is your zoom? Yes. Unmute yourself. Yeah. So, let it happen. Okay. Yogender. >> J. >> Okay. Let it happen. >> Okay. It will take some time for you. >> G. >> Okay. So, I'm unsharing your screen right now. Uh Chana, did it complete? Vikas, are you able to get it? Same thing. If you are getting the redistribute, you have to go for do doing it manually. Select one by one product. Automatically it will execute. Okay. Okay. No worries. So those who are not doing it. So all the steps are done. I'm I can't able to add the application option 86% great. So it will it may it might get stuck at 89%. Okay. For 89% it will take the longer time. After that you only need to set the password. So if anyone completes that it's okay. Okay. 86 sai are you done s uh is your that portion completed then I'll take up your screen. Okay most of you it's 86%. Okay one of you mentioned regarding jodika please wait no worries I'll be take have the lab also we'll work on the lab. It's done. Okay, Sindu, can you please share your screen, please? Sindu, can you please share your screen? Why I'm showing the installation right now? So, installation you can do it yourself or we have the lab as well. But why I'm suggesting because you can practice it. Yes. Okay, this is done. Okay, you have not downloaded it. Once this is done, go for next. I thought the earlier one was done. Okay, you have to go for execute. Okay. So, it will take some time the workbench. Okay. For me it was done. S can you go for sharing your screen? Please have some patience. I'll discuss about the lab. Why I'm showing this one? Because you have to use it. Okay. Once it is complete, right? Once it is, you are getting the status like this. Everything is complete. You have to click on next. Just go for next. Okay. Again go for ne next here. Go for next. Go for next. Just keep on clicking on next. Go for next. Here you have to set up a password. You have to remember this password because this is the Okay, you can give a simple password because if you forget this password then you have to reinstall it. You have to reinstall it. You can just give 1 2 3 4. Okay. S 1 2 3 4. Again, repeat also. Okay. Go for next. Next. No. No. No. No. Next. Go for next. Go for next. Next. Go for execute. Okay. Finish it, please. Finish. Yes. Next. Finish. No, no, no. Don't stop sharing. Don't stop sharing. Can you go for your uh the MySQL workbench? I think it has opened for you. Yeah. Workbench open for you. Just check. Are you getting it or not? Yes. So, please. So, you all will be getting it like this. You all will be getting it like this. Okay. So, this is your MySQL workbench. So now I'm going to show you the lab. So please focus on the lab. Okay. Once you log in, please follow the steps of the lab. Okay. Please follow the steps here. There you go. Wherever you have the SQL, right? Wherever you have your SQL course, go to the SQL course. Go to your SQL course. Okay. Okay. Confirm me once you are in the LMS. Uh just for for your uh information. Okay. So all of you you can see your LMS here, right? the live classes link you will be getting all the materials right attached here under the my class section okay you will be seeing the recordings will be available here and also your uh the materials which I will be sharing you will be getting it here only okay so for today I have shared the download link right the MySQL download link one data uh one data set and I I have also shared you the uh MySQL installation for MacBook Okay. So, every day you will be getting the recording also here and also the attachment before the class. Okay. Now coming to your lab. So you will able you will be able to see the practice lab option here. Yes. Are you able to view it? Practice labs. Please click on that practice lab. Please click on the practice lab. Once you click on the practice lab, you will be able to see this launch lab option. Kindly confirm me. Are you able to get it or not? Launch lab. Okay. So, click on that once again. I'll get twice launch lap. Launch lap two two times. Okay. You can you please have some patience. I will be discussing everything. Let the lab be launched. Okay. So please you have to understand that there are around 118 participants are there and I'm one right. So make sure if I'm able to I'll be answering all your queries but but please you have to have some patience. Yes, MySQL workbench because you are giving the permission to uh create a shortcut for the MySQL workbench. Okay. So everyone I would just request you one thing. So launching the lab also takes some time, right? And also here it is little bit different. So I would request uh you to kindly follow the screen. Okay, you can concentrate on the screen, focus on the screen to what I'm doing because again see if you missed out any steps I have to relaunch the uh lab again and at that time it will take more time for me. Okay. So like most of us are working in the lab. Those who have those who are already having downloaded the offline version like Sai, you can work on the offline version. You don't have to go for the lab. Okay. So, it'll take some time to set up the virtual environment here. so this is a Linux environment you are getting right now and this is your application this is your MySQL application here once your lab is launched right you will be able to get that here and Then you have to double click on that. You have to double click on that to open it. Go for double clicking it. And once you do that you are getting the MySQL workbench. You are getting the MySQL workbench. Kindly confirm me. Are you able to see it or not? Is the lab is launched for you? Whether the lab is launched for you? Okay. S you don't need it. Okay. S you have already installed. If you have already installed, you don't need it. Okay. Okay. All right. Then I can discuss. So for the lab, right? We have to just uh what we have to do, we have to play with the system settings a little bit. Okay. Otherwise, we will not be able to see the full screen here. So I have to resize this website screen. So that is the problem with the lab here. Just let me go for resizing it. Okay. So this is how the lab looks like. So here you have the let me go for this lab. I'll show you with the offline version also. Okay just give me a moment so that uh I don't have to you know minimize the slide. So this is your workbench. This is your workbench. So basically it is the graphical user interface okay of your DBMS. So you can see here it's the official graphical user interface. So here this is the basically where you are going to write the code. This is your this is your DBMS. This is your DBMS database management system. Okay. Where you are going to write the code. Here you will be able to notice this local instance. Okay. So if I click it's local instance is nothing but like your one drive. You store all your details here all the data here. Okay. Local instance is like a one drive or like a folder where you have all the information. So if you click on that local instance it will be you can see here first focus on my screen you can see connect to the they are asking you for the password here they're asking you for the password here you have to connect to the this is basically your GUI workbench is the GUI and if I want to connect to the my SQL server I have to share the password I have to share the password here. So you can think of it as the server is basically the engine where your my DBMS runs. It is basically the engine where your DBMS runs and workbench is the graphical user interface. Okay. No, let it install. Let it install offline version you can use the lab you can use a lot not an issue. So those who are working with the offline system once you set the password right like you have seen for Sai you have to share your password here like 1 2 3 4 for me also it's 1 2 3 4 only otherwise I might forget right. So once I go for this 1 2 3 4 I'll click on okay so this is a coding environment so you don't have to understand this. So for now this is my coding environment where I will write all my codes here. Okay. So I've shown you the interface here. Now let's go back to the lab. Okay. So this is for the lab users. You are also getting the same thing. The GUI you are able to see the graphical user interface. You are able to see it here. Okay. Now you also have the local instance here. See for those who are able to get it the lab. Yes. If you click on that are it is asking you for the password for the lab. Now this is not the password. You cannot use 1 2 3 4 here. Okay. This is the system password. So where will we get the password? Where will we get the password? So please check here. You can see the three dots here. You can see the three dots here. Can you see that? Can you see the three dots? Can you see the three dots? Please drag that. Yes, I think you have already got it. Vikas has got it. Great. So those who are not, this is a lab. Okay, here you have to basically drag it. Once you drag it, you will be able to get the password. Here you have to drag the three dots. Okay, you have to drag the three dots. You will be able to get the admin password here. Yes. Are you getting it? You can cancel that. We do get it for the first time. What you can do? You can click here. Click to paste. You can click here. Okay. Okay. If you once you click here, you will be able to notice that you are getting the password. You are getting the password here. Just click on okay. Just click on okay. See, because I'm also getting it. That's okay. Help. Okay, let me just drag it for further more. If you are not able to get it in the uh desktop, what you can do, you can go to the start button. Okay, and then go for it. Okay. Now please check here. Those who are done, please check here. We have the administration, right? You are getting the administration here for the lab users, right? Please, this is from the database administrator. Basically, what happens is that uh your database, right? My SQL can be used from the administrator point of view also where we share the privileges. We give who persons can what persons can do right if someone joins the organ organization what privileges we do give to that users and if someone leaves the organization we take away the permissions so these are the things that are done by the administrators right but we are going to learn it from the analyst point of view so those who are working on the lab you are getting this administration here we are going to learn from the data analyst point of view or data scientist point of view so I'm clicking on this arrow You will be getting the schemas. You will be getting the schemas here. Okay. Similarly, those who are working on the offline, those who are working on the offline, you might be only think that is a change in your GUI. So you is present here. You have administration here. Okay. Not you are getting you have to click on the schemas those who are working in the offline site and then I think mentioned puja I believe right who have installed it. So please click on the schemas here. So this is your code editor. So this is your code editors. This is your give me a moment code editor where we are going to write our codes or you can write your you can this is known as your SQL script. This is known as your SQL script. Okay. Okay. Great. Raki. So Raki, did you get the schemas? Okay. Great. Now see for you you must who have downloaded it right now or you are using the lab you will be able to see only a single database that is the system database. Okay, we cannot store the data, right? Uh we cannot store the data or we cannot have the uh we cannot store anything without the database. So this is a system database we are right now we are having here. Okay, this is a system database and we are not going to do any changes in the system database. Why? because if we do any changes in the system database, it may just completely mess out the working of other uh workbench. Okay, so we are not going to do it. So we are always going to create our own database. We are always going to create our own database. Okay, am I clear? Great Venet. So Venkeet please click on the schemas. Okay, you are currently on the administration. Click on the schema. Okay, so please those who are unable to get the administration of the schemas here. What is the issue? You have to reset your screen size. So for the lab, right, you have to reset your screen size a lot. See I'm also I'm also able to view it. Now if I go for minimizing the screen little bit reducing the screen size I'm able to give get that okay so if I want to go for writing any commands okay for single line comment I can use this symbol I can use this symbol. What is this hyphen hyphen then space this is basically used commenting comment for single line you don't have to write all those things I will be sharing okay and if you have multi-line command okay if you want to display multi-line command you have to use this symbol okay that is slash star and then also you are closing with this and this is your multi-line comment. This is your multi-line comment. Okay, I'll give you some time to do first understand it. And one more thing you have to understand my SQL is not case sensitive. What do I mean by case sensitive? That is there is no difference between so I have to use multi-line command here okay cuz it's coming in multiple lines between and small letter and capital letter. Okay. Yogenda, can you please work on the lab comment means if you want to display anything. If you want to display any line, so you are that is your comment. Single line comment. Okay. You you want to give a message. you want to give a message. Okay. All right. Now, please check here. Why did I mention that? Everyone focus on my screen. Before we want to do anything, right? We need a space for that. We need a database, right? To store the information. So, how do we store the how do we create a database? For that, we have to write a query. That means we have to write a code. Okay. To create a database. Okay. So the keyword the code I write is basically create database and you have to give a name to the database. So I'm giving the name as SQL. uh today's Feb right? So I have multiple batches so I'm giving SQL fab as your database name. First write this only write this code please create database SQL fab or you can give any name you can give any name I have given SQL fab because it should be a one word you cannot have two word okay you cannot have two word so I have since I have two word here I have joined it my using an underscore and we should always end the code with the semicolon Can you write the code please? Just write the code. You don't have to write the comment as well. So write it. I will be taking a doc file. I'll be sharing anything. Great. Great. How to comment until lab is how to comment on lab going on? You have this lab, right? You can go for this comment. This database it's slow right now because all of us are working together, right? Yeah. database. So, what is the code? Create database uh SQL fab, right? SQL PB. That's it. Yeah. Yeah. Please can I continue to use offline? I have already. If your lab is not opening, you have to launch the lab again. Relaunch the lab again. And those who have the online version, please go with the offline version. Okay. Now, please check here. Now, please check here everyone. Are you able to write the code? I'm sharing that in the chat for now. Check it out. So once we have written the code, you have to also execute the code. You also have to execute the code. Okay. So how do I execute the code? How do I execute the code? So please check it out. You will be able to notice this thunderlight icon with the i symbol. Yogenda, can you quickly message uh the LSM and try to maybe she will maybe she she may try to help you out if you are not able to do it for the lab. Yes, everyone please check. Once you have written the code, you have to execute it. Do you have to run it, right? How do I execute the code? Thunderlike icon with the I icon. Thunderlike icon with the I icon. Please execute it. Yeah, you can go for control + enter as well. Please click on that. Yes, there is a difference between that thunderlike icon and I'll discuss later. Okay, great. So you go for this you will be able to notice that a message is displayed. A message is displayed in the action output. Right? Yes. Okay. So V because you have already created it. So since you have sorry NICL you have already created it uh but since you have executed it twice you are getting an error it's already created for you. Okay check here though we have created it we are not able to see that fab SQL here or SQL fab here are you able to get it? Are you able to get it? No. Right. In the schemas schemas are nothing but databases only. Okay. schemas are nothing but in MySQL it's a database it's a container yes okay so what you have to do even though we have executed the query correctly you will not be getting it so you have to go for refreshing it you have to go for refreshing it please click on the refresh icon that is your refresh icon code is enough. You don't have to write the comment. Check. If you go for this, you will be able to view the Feb SQL right now. So, let me show you in the lab as well. So, for the lab, see this is the one. Let me go for removing this things. Okay. So go for this. Okay. I have not executed it. Let me execute done. Once I done I will be able to get this uh SQL FB here. Are you all getting it? Okay, now the thing is we have created the database. Let me drop all those date. Okay, let the database be. Okay, now the thing is we have created the database. Now we also have to now if you check the structure of the database. Okay. So as I mentioned this is our database. Let me go for the lab only. You will be able to view it properly. So if you expand, you can see this expand icon here, right? You can see that expand it icon here. If you expand it, you will be able to see under the database, you have the tables, you have the views, you have the store procedure and you have the functions. You have the functions, right? So that that is why you know this what is a schema? Schema is basically a logical container which is basically storing the groups of tables, views, stored procedures and functions. We are going to learn about all of this in the coming days. Okay. So tables are storing your actual data. tables are storing your actual data here. So all of you are done creating the database. All of you done creating the database. Okay. Now once we have created the database to use the data we have to select it. Okay. We have to select the database then only we will be able to work on it. How do I select? Two ways. Okay, two ways of selecting the database. You can double click on the database. Please check here to select. One of the shortcut is double click on the database. Double click on the database. You can see your database is highlighted. That means your database is selected. Or you can double click on the database. Or you can write the code use SQL FB. Okay, this is my name of the database SQL fab. I will go for executing it and again. So you can see my SQL fab is selected. My SQL fab is selected. No, no, there is no shortcut kit for the comment. If you wish to write, you have to go for uh two time hyphen then single line comment right under a space. Okay. Now please everyone see as a analyst we'll hardly go for uh creating a table right we are always we will be having the uh getting the data and we are going to work on that. Okay. So how do we we will be able to load the CSV file in a MySQL. we will be able to load the CSV file in a MySQL. So how do we do that? Okay. So only thing that will differ for the offline users is basically the path. Okay. Other thing that for the lab users and the u offline users it is exactly the same. Just give me a moment. So please check it out how do we do it. So everyone please focus on my screen. We are going to import the import the table here. For the lab users, first check for the lab users and then I will show you for the offline users. Okay, it's the same thing. Only the thing that will differ is the path. So offline users if you're able to do it, you can do it. The path will vary. Okay, so please everyone to import the table here what you need to do right click on the database. Right click on the database that you have created. SQL Feb. Right click on the database that you have created. Okay. I right click here. I will be able to get the fifth option as the table data import wizard. Table data import wizard. Are you getting it? Table data import wizard. Please focus on my screen. Are you able to get it? Okay, there you have to click on the browse. You have to click on the browse. Okay. Once you click on the browse, you have to go for the desktop. Go for the browse then the desktop and there in the desktop you will be getting the data sets. Let me know once you are able to get it here. Are you able to go to the browse desktop data sets? Okay. Anyways, I think I have to repeat only. Click on the data sets. Double click on the data sets. You will be getting here the first option as the assisted practice data sets. Assisted practice data sets. Double click on that and click on lesson five. You can check the path here. You can check the path here. Whatever I have selected desktop data sets, assisted practice data sets. Then lesson five, you have the EMP table CSV. You have the EMP table CSV. Yeah, for offline that is the same thing. You have to download that file. Just give me a moment. I'm sharing that file with you all. Even it is present in your LMS. Give me a moment. Let me share it here as well. Offline users also I will show you. Just wait for some time only the path will differ. By that time you can download this file. Okay, I'm sharing it chat box. Yes, everyone check here. Once you get this file, right? Once you get this file, what I'm going to do, I have to minimize my screen a little bit. Just give me a moment. Still minimize more. App should have taken up to a new screen only. Okay, I'll just increase it. Okay, once you have got that data file right, emp table, you have to click on open. You have to click on open. This is for the lab users. Okay, I will show you for the offline users as well. Once you select the table, emp table, you have to click on the open here. You have to click on the open here. Okay, you are getting the path here. Please confirm me. Are you able to get it or not? Are you able to get it or not? The lab users. Okay, then just you have to go for click on next next. Okay, nothing else. Just go for let me just minimize Okay, just you have to go for next, next, next, next, next. And finish. That's it. And refresh it. And refresh it. Okay. Lab users. Uh Naim, did you install it today? Did you install it today or are you working with the lab or are you working with the lab or uh this one? Lab, you have to relaunch it. If it is disconnected, please go for relaunching it. Okay, others those who are working with the offline please check here go for this. Go for wherever your database is present. Right? Your database is selected. Right click on that. Go for table data. Again the same thing table data import wizard. Here you have to browse to the file. So that is basically the file I have shared in the chat box. So wherever your file is present right browse to that location. For lab it was present. This was a virtual environment but for you it might be present in your own system. So you would be probably knowing it. Great. Right. So go for next. Sorry. Sorry. Sorry. Okay. Go for clicking on next. Nothing new. We have to do just click on next. Next. Next. Next. Finish. Okay. Both the lab users, offline users, are you able to load the file? Are you able to load the file? Yes. Once you are able to load the file, you also have to refresh it. Once you have loaded the file, please go for refreshing it. Go for refreshing it. Have you created that Gori? Have you created the SQL fab database? We have created the SQL fab by writing this create database SQL fab. Have you refreshed it? Have you refreshed it? After creating it, have you refresh it? Okay, great. Similarly, after you have imported the table, you have to refresh it. Then only you will be able to notice the table here. See emp similarly, I'm going to show it here as well. See, I have imported the table. I have to refresh it here as well. I have to refresh it here as well. See if I refresh it. See, I will be able to see the EMP table. Kindly confirm. Are you all done with this? till this part. Okay. Now check if you wish to display. Okay. Display anything. Okay. If you wish to display anything, you have to write the select statement. Okay, you have to write the select statement. Always remember, yeah, it will take some time. So those who are facing any issue with the lab, I would request directly ping the LSM for this. Okay. Any issue with the lab. Okay. So directly ping the LSM. So what you have to do Na? So either you can ping the LSM or you can go for refreshing it. So once your lab is disconnected, it will take time. So go for relaunching it again. Refresh it. Refresh your screen. So what happens is that I want to just because you as you can see we have now more than 100 participants here right. So if I just go for uh taking up everyone one by one it will just break the flow. Okay. So for the thing if anyone is facing issue with the lab I just want you people to connect with the lesson. Okay. I can help you with the technical part. Okay. Is it prepare import or import file? Sorry. Uh is it prepare import or import uh data file? So you have the import table data this one right table data import wizard. So you have to go for that table data import uh import wizard. So go for that. Yes, we can go to next. All right. Now please check here if I what I was saying if I wish to display anything we have to write the select statement. Okay. So if I write select asteric right asteric then from table name. So this is the syntax okay from table name. So that is in our case our table name is emp table. Okay. Now what does asteric refers to? All columns it refers to all columns here. Once you have imported the table, you have to refresh it as well. Once you have imported the table, you have to refresh it as well. Then only you will be able to see the EMP table over here. Right? So if you wish to display all columns, you have to go for select star that is asteric symbol from EMP table. That is the table name. If you do that and if you click on execute, you will be able to see a result grid opening up for you. A result grid opening up for you. You are also able to see how many rows are there. So there are 20 rows. Okay? And you can also expand the result grid here. See, you will be also able to see it. Are you able to see it? Great. Few more responses. Okay. Refresh it. Na refresh it here. This one you have to refresh it. Even in your lab you have that then only you will be able to get the table. Okay. This is done. Okay. Now please check this is asteric symbol is used for getting your uh you know all columns. It is used for getting all your columns. Yes Hindu will answer that how many rows we'll get there. Okay. How many rows are there? So you will be able to get it here. Give me a moment. If you just expand it. Okay. If you expand it, you will be able to see in the output grid this 20 rows. Are you getting it? Okay. Now, basically to display, give me a moment. Asteric is display basically to display all columns right we can also go for displaying specific column okay now if you wish specific column you have to write select select and whichever you want see I'm writing simply like this emp letter anything also you can write capital as well Last name suppose role only this many I want from EMP table whichever columns you want you can name that and from EMP table and execute see we will be getting it right out right out let me check out this as If you wish to display everything all columns right then you have to use the asteric symbol. If you wish to go for specific columns you have to mention. Now to display all columns we are going for asteric. To display specific columns we are going for comma. name of the comma sorry name of the column and then we are going for comma here right now suppose now suppose if you wish to get specific rows I want to get only five rows I want to display five rows instead of 20 rows right you check here we are getting here 20 rows all if you just expand it you will be able to see it here you will be able to see it here right so if you wish to display only suppose five rows. How will you do that? I will write the same query. Please check. I'm writing the same query to display five rows. Only thing I'll change is let me write it here. I will use limit five. So limit is basically the statement which is used for limiting the rows for limiting the number of rows. So now this time we will only have the five rows. See 1 2 3 4 5. You can also check the count of it in the output. See five we are having here. Okay. When you mention select star, select star, that means select everything. Okay, that means you are selecting everything. You cannot write like this. Okay, suppose if you wish to go for some other thing that is okay. But you don't go for like this because already already you will be having this already you are having EMP ID over here. So you don't have to go for this. You don't have to because you are going for selecting all columns. So please check here what will happen in this case. You are getting EMP ID twice. You are getting EMP ID twice. Okay. So that's what is happening. Don't go don't go for that. You are selecting it right now. So are you able to get the purpose of the limit? Are you able to get the purpose of the limit? So if I wish to go for select star from emp table limit 10. So you will be getting 10 rows. You will be getting 10 rows. Okay. to always end the SQL statement with a semicolon. Always end the SQL statement with a semicolon. Okay. Now suppose check if you write like this. Please focus on my screen. you will be getting some time to do. Okay. If I write like this suppose limit three comma comma 2 that is starting from the third row I want two rows. I don't want it from the first row. Okay. So check from the first row I'm getting as Roy. Okay. is E20 E260 right we have the Roy colins and that's what we are giving getting here but now if I go for limit 3 comma two so please check what happens here how many rows am I getting only two only two okay so that's what we have mentioned we are not getting the first three we are not getting the first three give me a moment Three means from which row you are want to get the number from. So you check here. Okay. So I will show you one thing. If I was to limit three rows. Okay. So here we have till we have till Krina here. Okay. But the thing is that I want to skip this. Okay. Okay, I want to skip the three rows and I want to from from the start starting three I'm skipping here and I want to get suppose four rows. So I want to get it from 4 5 6 7. Okay, so check it out. Yes, index. Okay, so here we are getting here we are getting four. That's the index here starting from one only index zero. Yeah. Yes. After three. You can also use. So I have used all columns right? I have used all columns. You can also go for specific as well. Let's go for the unique identifier that is your EMP ID. Okay, let's go from starting from zero, I want two rows. Let's go for this. What do I get? Starting from zero index, I want two. Okay, so it's the index. Index always starts from zero, right? So, I'm pasting it and check try it out. Try it out. All those things. Okay. So you have already the link of the file. Please try it out. So there are I I think more than uh like I can see 99 participants are there or 8 n people have only joined the doc file. Can you please explain? Okay. So this is basically the index number. This is basically the index number. 3x 2 3 is the index. So index always starts from zero. Index always starts from zero here. Right? So if I go for this suppose 3x4 here. Okay. Okay. Let me first explain you this first 10. Okay. So let me first explain you this first 10. Check out here. Z let me write here. 0 1 2 3. Right. This is Jennifer is three. You can check here. This is 0 1 2 3. Now if I execute if I execute this one select star from EMP table starting from three four right. You are getting third index four rows. No, not from Steve. It's from Jennifer. It's from Jennifer because Jennifer is the third index. Jennifer is the third index, not Steve. Because index starts from Z. Index starts from zero. Yes. Dhani you can write your doubt please got the purpose of limit so it's not only you are able to get it by the number of rows you can also get it by index as well you can get it by index as well last thing for today. Okay, one last thing for today. Give me a moment. Index as in the line number. So line number we do not consider as the index. Okay. So index is basically at what position. Okay. Start at positions. The position starts from zero position. Okay. So index is basically the position it's stored at. So it's stored at the zeroth position. That is your index. index number. Okay. Now check here. Now check here. Select. Select. Let's go for this only. I'm not going for limit. Okay. I'm not going for limit here. Only this. Okay. And here I'm going for order by Okay. order by salary. Okay, before we go for the order by salary, if you check the salary, suppose if I go for this, if I go for only this, I can see the salaries are not arranged in proper order, right? Not arranged in proper order, right? No ascending order or no descending order. we are having 7,000 6,500 3,000 2,800 again I'm getting 5,000 7,500 so it's a random order right but if you wish to sort it if you wish to sort the data you have to write here order by okay you have to write order by and then the column name so I want to sort it by the salary if I don't if I want to sort it in ascending order that is okay order by salary. So now check. I'm getting it in ascending order. I'm getting it in ascending order. Order by Order by is used for sorting. Order by is used for sorting. Uh Zeroth is the first row. N zeroth is the first row that is your roy I believe. Right. Yes. By default it is by ascending. By default it is by in ascending. Okay. Please focus on what I'm teaching right now. You can ask me your doubts later. Okay? Just because we are now discussing regarding the order by let's focus on the order by. You can ask me just uh like after 1 minute you can ask me regarding the confusion you have in your limit as well because that time you did not ask. Okay. So if I don't mention anything it will be sorted. Yes, it is basically sorting in ascending order. Okay, sorting in ascending order. If you want it by the highest salary, you have to mention DEFC. Okay, you have to mention here Let's go for this. This time you can see the data has been sorry the data has been sorted in descending order. For ascending you don't have to mention anything. For ascending you don't have to mention anything. So order by is basically used for sorting. Okay I'm sharing it here. and then I'll be taking up your queries. ASC is not required. Okay. So by default it will be in if you don't mention anything only the column name it will be in ascending order. It will be in ascending order. Okay. So the thing is that if you have confusion regarding okay that's all from my side. I don't have any uh question regarding like I'll not teach you anything new right now. Okay. So we'll also discuss about uh tomorrow's class. We are going to discuss about the operators and all those things uh in tomorrow's class. Today stay back. Uh if you have any confusion others I would request you all to share the feedback form. Your feedback links has been shared. If you have liked the session you can rate the session as five or anything less than five you can mention the comment as well. So please check your name. If I go for select star from emp table right so if I go for displaying everything what is the first name I'm having Roy Collins right so if I want this is the zerooth place zerooth index right the first name is stored at the zeroth index now please check here if I write here limit starting from the zerooth index I want only two rows Starting from the zero index, I want only two rows. If I execute it, we have discussed about. So I'll just go for the demos first. We now know how to create a database, right? We now know how to create a database. After that, how to use the database. how to use the database and then we will be we have also learned how to import import CSV file into a table right and we have looked into once we have done This we have also looked into few clauses like we have looked into the select statement right? We have looked into the select statement. We have looked into your limit to limit the number of rows. We have looked into your order by right. This was what we have covered in yesterday's session. Those who uh don't have it, those who don't have it in your installed in your machine, the offline version, please go for your lab and that is also expected. I did mention that uh you should get your lab ready 10 minutes before the session. Okay. Totally up to you. Okay. Totally up to you. Okay. We'll look into that. Okay. Aka. Okay. Now, shall we proceed? Have you all have your data imported? EMP table imported those who are working with the lab. Have you imported the EMP table? Have you imported the EMP table? Because those who are working with the lab, those who are working with the lab, you have to do it every day. After 5 hours, it gets reset. Great. Okay. So now let's go for doing an example solving. We'll do a revision now. Okay. Please check here. Retrieve the employee details. Retrieve the uh employee details. Okay. Having having the Okay. Retrieve retrieve five employee details. Okay. who are having or who are getting the highest salary. Can you try it out? Retrieve five employee details. What is the issue? Punam others please check. Retrieve five employees who are getting the uh highest salary. Can you try it out? Okay, let it happen. Let me show you here. Okay, for me it's executing. So you can see here retrieve five employee details were getting the highest salary. I did not mention the column name. I did not mention the column name but yes I did mention regarding the employee details. So what I can do I can simply go for select star that is all columns from EMP table. Right now I will be going because I have to get the employees with the highest salary. So for that I have to arrange the data. I have to arrange the data. Sort the data. I'll go for order by order by salary and I'll go for DC descending I'll go for DC descending order right after that I want to display only the five employee details only five so I'll go for limit five always uh remember that limit is used at the last limit Limit is used at the last. Okay. After order by we can go for the limit. Limit is specify how many rows you want. Right? If I go for this see these are the people. So first we have 16,500 14,500 11,000 10,500 and 10,000. Is that okay? Okay, you if when once you are done right once you are done is that your table is coming due to this uh display right you have mentioned the select command now if you just you can also expand this expand this it will be gone you are getting five rows here check now if you work on the other query it will be automatically done it will be automatically done okay you can compress it again like this okay let Let me check once. Give me a moment. Okay. So those who are working on the lab, please check. This is the way you all have to do it. The screen is hanging right now for me. So once you get this, this is your virtual screen. This is your workbench. This is your workbench. You can click on that and the password you will get here. The password you will get here in the three dots, right? You can also go for Okay, just let me show you here. This is your password. Once you go for the local instance, this is your password. Password dot one uh exclamation exclamation also. One more thing, there are three dots are here, right? These three dots are here. Click on that and go for VM native clipboard. Okay. Otherwise what happens is that it is not allowing you to paste it paste the code suppose. So if you just go for clicking that it will give you the permission to paste the code whichever I'm sharing in the doc file. Okay whichever I'm sharing in the doc file. So you can go for owning that. So the local instance password. So this is your password. I hope I'm clear. limit after exit. Okay. So, everyone focus here right now. Bhavia, what is your question? Limit 10 after what is that? What to do? I did not see last session. You don't have to do anything. This is the query you have written. That's it. And this is the output you are getting. This is the output you are getting. You don't have to do just I wanted to get the retrieve five employee details who are getting the highest salary. That's it. So we are first we have sorted the uh we have sorted the table and then we have limited it to five rows that's it just to practice it what are you getting then give me a screenshot please select the portion you want to execute and flash icon which is to I'm using the lab why I'm getting the error okay so Since you are using the lab, you have to import the table again and you have to create the database. Okay. So now if I launch this. Okay. Let's go for this. For lab you have to do it every day. You have to do it every day. So that's why I asked you. Okay. Give me a moment. Yeah, we got it. Okay. So please check here. Okay, I have to resize the screen. So this is my local instance, right? So if I open that, I have to go to the schemas again. See, I don't have that fabsql database. So I have to repeat the steps. That's it. You have to go for creating a database. You have to select it. Import the table again. Emp table again. Then only you will be able to do it. Okay. So that's why I asked you that because in lab what happens? It gets reset after it gets reset after uh the thing. Okay. Have you went for refresh? If you have gone through it, have you gone for refresh this one? If you have done it, is Jennifer or Janet? Just give me a moment. Janet, have you done that? Refresh. Have you refreshed after importing the table? Still you are not getting it. Baba, please check here. You have to be in the schemas. After that, you have to create this SQL. See, I'm in the database. My database is selected right now. SQL_FB. What you can do? There is a doc file which is shared with you all. Right? This is the doc file. This is the doc file. You have to go for uh executing this code that is the create a database and then use SQL FB. Import the uh import the table then only your query will work. Okay. Directly. If your table is not there, how will it work? Am I clear? So go to the doc file and execute the execute line number 12 and 14. Line number 12 and 14. Then import the CSV file. Then only you will be able to work with this. I hope I'm clear. All right everyone. So let's begin the class today. Right. So please check here. Yes. Please execute that that two queries. So please now we have we have already learned about the order pi and the limit. Right? Now suppose if we want few records. Okay. Rows or you rows are known as records in SQL. Okay. It can rows are known as records in SQL. Okay. If we want to see few records based on condition okay based on condition in that case we have to write the where clause. So if you want the syntax so this is how we write it. Okay. So we write suppose the select statement you can go for all columns or particular column right? You can go for that. Let me go for this. Okay. Columns from EMP table. Not okay. From table name. From table name and then we write the where clause. Where is basically your filtering condition. Okay. Filtering condition. You have to wear and then condition. Okay. Now, how do I implement it? I'm going for a condition right now. Okay. Let's go for this. Show the details of all employees working in the finance department. Okay. I don't want the all the records. I want the records. I want to display the records of only those employees working in the finance department. Okay. Now it's totally up to me. Suppose if I go for all details, select star from emp table where okay the condition is dpt equal to finance. Okay. Now this finance is basically a text. This finance is basically a text. So we are writing within quotes. We are writing within quotes. Why do we use semicolon? Semicolon marks the end of the query. It represents that your query ends here. If you don't give a query, if you don't give a semicolon, you will not be getting an error here. Okay? But the next query you write. Okay? So I'll show you one thing here. I'm even I can execute this without the semicolon. I will not get an error here. But the moment I try to write the second query, I'm getting an error. Okay, I'm getting an error. So make sure that you use the semicolon because that marks the end of the query, end of the statement. Okay. Now please check here select star from EMP table where department equal to finance. I'll go for you can also go for control enter anything works either you go for this the current execution or you can go for control + enter on your keyboard as well. Okay anything will work. So please check I'm going for this. Keep your cursor here. So how many records are we getting? I think it's three. Yes, we are getting three rows. We are getting three rows. So these are the people working in the finance department. These are the people working in the finance department. Ma'am in the syntax you have what is comma. Okay. Now in this case I have went for all the columns right. If you wish to go for specific column. Okay. If you wish to go for specific column maybe you want to go for first name role department. Okay. experience from EMP table. Okay. Where DP equal to finance. So instead of all the columns right we have we have used selective columns here. That is the purpose of your comma. Yes, you can go for order. No, for ascending, right? For ascending, you don't have to me mention ASC. How to add SQL FB in the schemas. First you write create database. Okay, create database. The command I have shown you here. Okay, line number 12 you have to write and then you have to go for executing it. Then you have to go for refreshing it. You will be having it then. Okay. Bhavia. Are you all able to understand? Any confusion here? Show me the details of employees earning salary more than 5,000. Now check here 5,000 is a numerical value right? It is not a text. So I'm going to write here. So for numeric right for any numeric number I don't have to give the quotation. The quotations are necessary for your text values. So I can simply mention select staff from EMP table where salary greater than 5,000 where salary greater than 5,000. Okay. How to import the tables? Yesterday also I've shown you multiple times. So today also I'm showing you right now. Right click on the database. Right click on the database. You have the fifth option as the table data import wizard. Table data import wizard. go for that. Here you have to browse to the location where your file is present. Where your file is present. Okay, browse to that location and just keep on going for next next. Okay, I have to I have already the table, right? So you have to go for next, next, next and then finish. That's it. I have already imported the table. So you have to go for browsing. Just you have to browse to the path and click on next next finish. That's it. 13 you will not get you will get 14 I believe whe check whether you have mentioned greater than equal to or only greater than atka. Let's check for that again. Give me a moment. So this one right? Let's go for this example. Yeah, it's 14 rows. Check whether you have used greater than, equal to or greater than. I have only mentioned here more than. I have only mentioned more than not more than equal to. Okay. So more than means I have using I have used a greater than symbol here. How to write the salary order? by descending when department okay when you have to write it okay salary order by descending if you wish to go for it always remember we write the order where comes first and then comes your order by you can go for this you can go for this yes everyone shall we proceed Okay, let's try out more one more question. Okay, please try out this question as well. detail of okay I'll write it here fully only employees who have experience experience five years or more. Try out this question. Show me the detail of employees who have experience 5 years or more. or more than five years you can also go for that. No no no no select star from EMP table is basically used for displaying all the information from the table. If you have imported the table I would suggest whatever we are continuing right now start from there itself. Great chana. Okay 15 rows. Okay. I'm not sure. I have to check it out. 14. 14. Someone's getting 14. Someone's getting 15. Okay. Yes, that is exactly correct. So, I gave you this question because of the, you know, operator. Select star from EMP table where exp greater than equal to just give me a moment 5 years. Okay, 5 years or more. So when I mention five years or more, it will include the equal to. It will include the equal to. Okay. So in this case right salary I have mentioned greater than 5,000. So 5,000 was not included here but in this case I have included five. So that's why greater than equal to so that is 15 rows we are getting 15 rows here. Okay let's continue. Okay. Show me. Show the employee details. Show the employee details. Working in the healthcare department. Healthcare department. Okay. I have to use a multi-line command. and having experience. Okay. More greater than equal to 5 years. We have two condition this time instead of single one. All this while we have solved uh two using one condition right. So we have two conditions this time four rows. Okay. So when we have two conditions so please understand this when we have two condition one is that your department should be healthcare and exper having experience greater than equal to 5 years. If there are two conditions and both needs to be satisfied, both needs to be absolutely satisfied. In that case, you have to use the and operator. You have to use the and operator. So please check select star from emp table. Okay. So this will give me the details of all employees. Now the condition what is the condition? Department equal to healthcare. Done. This is one condition. Another condition. For another condition to hold I have to write and here and experi. So we are connecting these two condition using this and using this and I'm just creating the database. I don't have the database right now SQL done. execute that. Okay, I have to refresh it. I'm getting this. Double click. It is selected. Now I have to go for importing it. Give me a moment. Done with uh nine rows. I'm getting zero rows. Jikica, please check it out. Okay. Show me the details of employees working as either senior data scientist or so in the question only they have mentioned or having salary greater than 10,000. It is an either or condition. It's an either or condition. Okay. So here the thing is that what is the criteria here and we go okay I'll just discuss about that later here in the question itself in the problem statement itself they have mentioned that the role must be senior data scientist or the people should be get or that particular people should be getting salary greater than 10,000 right any one of the condition needs to be true so what happens here if I go for this select star from EMP table, right? Where role equal to senior data scientist. Okay, here I'm writing or or salary greater than 10,000. So we go for or when the both the condition needs to be absolutely true. Absolutely true. Both the condition needs to be satisfied. But in the question itself they have mentioned that they want to go for the role should be senior data scientist or salary must be greater than 10,000. Nowhere they have mentioned as and nowhere they have mentioned as and in the question itself we are getting it right. So we will be going for this this one. Okay. Either or. Right. So we'll go for the or operator. We'll go for the or operator. And we are getting nine rows. We are getting nine rows. Yes, you can even use okay so some of you might have the confusion. So we can use the pipe operator as well for or this operator is used for or as well and if I talk about okay let me show you here don't let me not give the single line command here I'll go for the multi-line command okay so this represents your or in any other programming language it also works here and we have the amper as the and okay so these are the operators we go for but we can go for and as Let me check how much I have added there. Give me a moment. And is done. Yeah, I have to paste this one. Yes, I have given that. Okay, you can use this. You can use the operator or you can for and you can use the amp% or you can for or you can use the pi. Okay. So please let me know if you are able to do it till this part or some of you are struggling. Yes. Okay. Great. Yes. Clear. Everyone is able to follow. My pace is okay with you all. Okay. Great. Okay. So, shall we go for the next example? Okay. If you find any issues, can you explain once again? Okay. So in the question itself so here you have also two conditions here also you have two conditions okay that is senior data scientist or salary must be greater than 10,000 but in the condition okay in the condition they have mentioned that one of if one of the condition is true you will be able to get the output you should be able to fetch the output so here you can see here not all roles are senior data scientist if the person's role is senior data scientist that is they are extracted. Similarly, if someone is getting more than 10,000, those rows are also extracted. If you check the senior data scientist, right? No one is getting salary 10,000. Only I think one person is getting it, right? No, no one is getting uh 10,000, right? Only one condition was satisfied. And for the managers, for the managers, people are get they are not they are not senior data scientists, but they are getting the salary. So that is why all the rows have been fetched. If I talk about your and right both the condition needs to be satisfied. Both the condition needs to be satisfied. But if I talk about your or it is like if any one of the condition is satisfied it should be able to set it. Okay. This is I have used or here right? Instead of that you can also go for this pipe. You can go for this pipe as well as check. See it also works. This is the all operator only. This is the all operator only. This is the operator. Okay. We are getting the same output. This is just the operator. Okay. Or you can go for and you have to go for amp%. You have to go for amp%. You can either write or or you can write this. That's what I mentioned. Is it clear? Okay. Scientist spelling is mistake there. Okay. You have made a mistake in the scientist spelling spelling mistake is there. Okay. So if you do if you correct it to if you correct it then you will be getting nine rows. Okay. All right. Now suppose let's go for one more thing. Give me a moment. Okay. So please check out here in case in case you have multiple conditions. You have multiple conditions and all the conditions all the conditions are from the same column. Okay, same column. Okay, so what do I mean by that? Okay, so please check. I'm going for the question retrieve the details of employee working in the retail. retail then finance and healthcare department. So if you check here, if you check here all this uh if I talk about this one conditions right department equal to retain, department equal to finance all the are the options of the conditions of the same column. If this is the case right you can write it like this. If I want to solve this query, how do I do that? Select asteric from EMP table where department equal to retail or. So that when we go for the multiple conditions of the same column, we can write or here. Department equal to finance or department equal to healthcare. Okay, department equal to healthcare. Now if I execute that, so you will be getting the output as uh give me a moment 14 rows. you will be getting as 14 rows. So please check you will be only extracting the fe uh department retail finance and healthcare no other departments no other departments. Okay. So when this is the case, when this is the case when we are fetching the condition from a single column, so instead of writing or multiple times, instead of writing or multiple times, we can replace it with in operator that is known as the membership operator. How do I do that? Please check it out. This is also correct. You can see you have got the output. But instead of this what you can do in in okay after that give a bracket start and keep mentioning the options retail finance healthcare here. So if I do this, so this is much more better way. I'm still getting 14 rows. Exactly the same output. Try it out. Yes, because we are separating the values, right? Conditions. Try it out please. You can. Both ways are correct. Both ways are correct. Try it out. Great. Sharing the code in the file as well. Give me a moment. Yeah, this is correct only right. You have modified it to senior data scientist. So that is why you are getting as nine rows. Are you all able to follow? Please at any point of time you are not able to follow, let me know in the chat. Great. Okay, try it out. Try it out. So when do we use the in operator? When we are talking about the conditions of the same column. When we are talking about the conditions of the same column, we can use either or like this multiple times or we can go for the in operator. Please try it out multiple condition of the single column is it only applicable the thing what I have mentioned is wherever you can apply this or right you can go for this in instead of writing the uh query in this way Right? Multiple times or this looks much better. Okay? Then we can go for that. Yeah. See uh share me the uh you know share me the screenshot only if there is an issue. Okay. In oper. Yes. Condition of the same row. Condition of the sorry not single row. Single column. Single column. Okay. Multiple condition of the single column. We are going to use the or. So there you can go for in checking for the condition. Okay. Can we proceed? Okay. See, I'm trying to solve as many question as possible so that you are able to understand it better. Okay. So, please don't let's try it out. Show the detail of employees earning salary. Okay. From 5,000 to 10,000. How will you do this? It's talking about the range. All this while we have went for salary greater than 5,000 greater than 10,000. Now if I want to go for like the range right so we have to use the operator in this case select star from emp table where salary when we talk about the range we are going to use this between between 5,000 and 10,000 we have to use this range. We are going to use the between and and uh operator here. So if I do this you will be getting the salary within this range. So 14 rows. So where we have the salary range between 5,000 and 10,000. No larger value than 10,000 and no smaller value less than 5,000. Is it 12? Okay. I did not check it. Give me a moment. Okay. Probably yes. 12 rows. Okay. Try it out. Try it out. 15 not possible. Check it out. Why are you getting 15? Have you mentioned between and and if all of us are getting uh 12 only? Check the operator properly. Why are why are you getting it 15? Give me a screenshot of the code or make maybe you can paste your code. Maybe you can paste your code in the chat. Mangit give me a screenshot that also works up. All of you are you able to understand that's correct only where salary between can you give me a screenshot please? Give me a screenshot when check it properly as well. Okay. So, is this the output of the current one or not? You can go for this. It is 12 rows. Check why what are you getting? No no no no no. We can use the in operator. We can use the in operator when we are talking about uh when we are working with the multiple condition of the single column. So generally or okay don't worry I'll give that If you don't show me this one, how will I understand? Okay, please give me this one. The output screen. Select star from EMP table where salary between 5,000 and 10,000. So, give me the output screenshot please. I'm talking about this one. Others try out this. with experience from 5 to 12 years and working in the finance department. Okay. So, please share me the details. Share me the details with this. Try out this others. Please try out the question. SQL is not a case-sensitive language or it is it is not case sensitive but for lab it might be little bit case sensitive especially for the table name table name SQL is not case sensitive okay but lab is case sensitive especially for your table name you're getting 12 only not 15. Okay, wanket please check it out this one. You are having 12 rows. Okay, you are having 12 rows not 15. Okay, this is the answer. Am I clear? You are getting correct only. Okay. All right. The people are sharing me. Answer is one. I have to try it out and then I have to tell you. Okay. So, let's go for that. Select star from EMP table. Okay. where experience between 5 and 12 and again I have to use and department equal to finance if I go for that is it only one yes exactly one row Eric Right. The person name is Eric. So those who have done it exactly correct. It's the correct one. So are you able to understand the operators? Let me check if I have shared the code or not. Okay. Okay. Let me go for the another one. Okay, just try it out. Try it out. So you people are doing it really fast. So that's why I'm giving you the questions. If anyone you are not able to follow, please feel free to ask. No, no, no, no. In operators that is the usage of in. In operator is used to check the condition multiple condition in a single column. Okay. Multiple condition in a single column. That is how you use the in operator. You cannot go for multiple columns. You have the other operators for that based upon the question. So that is an exact same thing in your Excel as well and in your other programming language also when to go for and and or when you have in the problem statement that both the condition needs to be satisfied. Okay, here you check here. Okay, what is the condition in the question? They have mentioned show me the details of employees with experience from 5 to 12 years and so I am also interested in the people having experience between 5 to 12 years and they have mentioned the people should be working in the finance department not all department. Similarly, if you check the condition here as well okay here as well that the people should be working in the healthcare department and what is the other thing I want and both the condition needs to be absolutely true if one condition is satisfied not required I want to I want both the condition so it's like if I just go back to your logic gates okay So we have few concepts of logic gets okay I'm sure that you have you must have studied in school. So in that okay so you have the input and the output we have something known as the AND gate and the OR gate okay let me show you here so this is two inputs are there okay please understand here this is input this is input and this is the output I'm showing you for first the and okay I'm showing you for and operator this is the logic gate what is the output you have so First we'll go for 0 0 1 0 Sorry s sorry give me a moment okay it's okay still okay 0 1 1 1 If both the so and you can compare it with the multiply okay when both the conditions are false output will also false if one of the condition is false one of the condition is true and other is false then also false. If both one of here also same thing but when both the condition are true then only you will get one here that is the exact case you are doing in the end in the end as well when both the conditions are satisfied you are have to go for the end and if I talk about your or right it's like your if I talk about your orgate so it's like addition it's like addition okay only if both the conditions is false then it will return zero otherwise it will return one. Even if one condition is true you will be getting that output. Okay that means if even if one of the it was senior data scientist you are getting it. Okay it is not checking that the salary also should be in please check here. If I mention here, if I mention here senior data scientist and I give no and the salary should also be greater than absolutely I I want the role should be senior data scientist and they should be getting the salary greater than 10,000 in that case the output will be totally different. We don't have any person getting salary above 10,000 here for the senior data scientist. So we are not getting anything. So we'll go for or here because that is a mentioned in the question. Either the person should be working as a senior data scientist or he should be getting the salary or someone who is getting the salary above 10,000. Okay. So that is why we are going for the or here. We are going for the or here. Clear when to use and when to use or. Yes, it is correct. Okay, it is correct. Okay. So, you will get only one. You will get only one row. All right. Let me give you one more question. I hope that's okay. One question and then we'll go for a break. Okay. So please check out retrieve the details of employees working in country USA, India, China. Okay, just let me put it here. And E experience greater than 7 years. Try out this question please. check here you have this country column values all the conditions are from a single column and you have a different column here. Great. People have already done it. Yes. Exactly. Right. So it's pretty simple. Since this is a condition from a single column, you can go for the in operator here. So please check select star from emp table where country in bracket start USA. Okay and one more thing okay in SQL right even if you give a single quotes that is also works. Okay single code double quotes same thing. If and if I write like this that is also okay India and I mentioned regarding China and and what is the other condition and okay see and experience greater than seven. Let's check it out. How many rows are we getting? Five rows. We are getting five rows. Let's check it out. Is it correct? Is USA, India, USA. So we don't have anything from China. Okay. So that's why it's not counting that. So check here. I think there was a question from Anjeli during the break. Uh have gone through the chat. Just give me a moment. Distinct purpose I think show categories no duplicates. Right? So the thing is that if you there is another operator is there okay or function is there so we'll go for distinct okay now please check here if you go for this distinct and then you mention here suppose department okay distinct department from emp table right EMP table. So check what do we get? So you will be able to get the distinct departments. What are the departments are here? You have the retail, finance, automotive, uh, healthcare and all. So this is the purpose of distinct which is giving you the distinct values. Okay. All the unique values. Okay. Now we are going to discuss about the aggregate functions. We are going to discuss about the aggregate functions. So aggregate functions is basically works. Okay. It is used to group the rows. It is used aggregate function is basically used to group the rows. So if I talk about the aggregate functions or you can also say you can also say it as let me write here in the multi-line command. Okay. It is used for summarization. It is used for summarization. Okay. So what are the what are the aggregate functions are here? You have sum, you have average, you have um maximum, you have minimum, you have count. So these are the five aggregate functions and it only works with okay only. So this is a like you can see here it's a functions right? It only will work with numerical column. Okay numerical column you will not be able to work with the text column here. You will not be able to work with the text column here. Okay. So let's understand it better right now. So this is your aggregate functions. Five aggregation functions are there. Okay. Now all this were all this while we were just able to retrieve the data. we were able to retrieve the data. But if you want to summarize it, okay, get me the total salaries. Okay, get me the total salary of all the employees present in the organization. Okay, if I give you the question, retrieve the total salaries total salaries of employees working in the organization. Okay, if you wish to go for this. So here, please check how do I do that. Select. Okay, select. I'm going for sum and I have to go for the aggregation right of the salary column. I want the total salary from EMP table. I'm passing the column here. inside I'm passing the col salary column here as inside okay so if I execute that see that is a total salary it is basically adding all the salaries it is adding all the salaries I'm not counting it I'm just basically I'm going for the sum total I'm adding it I want the adding it right addition Now check here if I go for this. If I go for this function, right? Check the name of the column. Check the name of the column. It is a function name here. Doesn't look good, right? So what we can do? We can rename the column. We can rename the column. You can give an another name to this column. Okay. So how do I do that? Here I'm writing as a as known as all that means another name. Okay. As total cell. Okay. Now check what if I do like this you are getting to instead of that sum salary you are getting total underscore salary you are getting total underscore salary getting It now if I wish okay if I wish to suppose get the maximum salary. If I wish to get the maximum and minimum salary. Okay, I'm doing it in a same column. Maximum salary. Minimum salary. Okay. Minimum salary and average salary. Okay. of employees. Let's go for I have to use a multi-line comment here. Let's let me check it out. Average salaries of employees working in the organization. So I'm talking about the entire organization. Okay. Okay, in that case, how are we going to do it? Select max salary. Okay, as maximum cell, this is the name of the column. Give a comma here. Mean, salary as this is a rename. I'm just renaming the column. You can give any name. Okay, please check it out. And I want the average. So it is the AVJ salary as cell from EMP table. So I'm doing it in a single uh row. Okay, I'm doing it in a single query itself. I'm getting. So please check here if I do this the maximum salary of the entire organization is 16,500 minimum salary is 2800 what is the average salary it is 7,815 not done ma'am it's okay not done I just want to show If you wish to go for addition of sum of salary right of two person you can go for the wear clause. Okay. First understand this. I'll answer all your query. Sir, we are going for the entire organization right now. You will definitely have the wear clause, right? Try out this please. Try out this please. Then I'll show you one some more thing. I'll take up your query Anjeli. Don't worry. I'll just copy paste it in the word file. Let me give it to you. It's not pasting. Okay, I pasted that. So when we mention the sum of salary right it is doing the for the entire column all the rows right in a single column it is doing for that right if you wish okay Anjeli this is for you okay so please check this is my thing I have select staff from emp table okay let's go for this others you don't have to focus Say you focus on the other queries please. If you wish to do it for two people only. Okay, maybe for EMP ID 260 and EMP ID 245. Okay, let's go for this one. How do we do that? Check it out. Select sum salary from emp table where let's see it works out where EMP ID. Let's see if it works out. Get in. So if you wished for to get this is for Anjali. Okay. So what she wanted she wanted to get the you know sum of salary for two people. So that's what I have done it others you don't have to do it. Okay I'm getting error. Okay. What is the error you are getting? Others you don't have to do this. We have to use a wear clause. That's what I mentioned. Let me check it out. Give me a moment. Jotika. Comma before average. Oh yes. So what happens is that jotika you can see here you have missed out this comma. So that's why you are not getting it. Okay. That's why you are not getting it. Jotica I think your query is solved. You can you don't have to share the screenshot. You have missed out a comma. Okay. So after minimum salary you need a comma and then you have to go for the third column. Is it clear? Check out the code please. You are getting because you have not added a comma here. Kindly confirm me if you are done till this part and if you're uh okay great what about others okay now please check here this max right I'm doing it of a numerical column I will be able to get this as well select suppose max experience Okay, I will be able to do that max experience from not an issue. Okay, I will be able to get that max experience mean experience. I will be able to get it because this is a numerical column. That's what I mentioned here in the aggregate function. It works with a numerical column. Okay. So when we have numerical column, you will be able to get that. But if I wish to get select max okay of give me a moment first name first name it will it will okay so this is also I'm getting here okay here max is working so let's go for sum instead of max we go for sum okay let's go for sum of first same. Okay, let's see what we get. We are getting a zero here. We are getting a zero here. It is not valid. Okay, it is not valid. So, if I wish to go for check for this, I want to go for average. So, we do not use the aggregate function with the text column. We do not use the aggregate function with text column. So this is incorrect. This is incorrect. This is incorrect. Are you getting my point? No, no, no, no, no. It is not counting the characters. Let's go for this. Okay, let's go for roll. What it disappearing? Senior data scientist. We are getting senior data scientist. Okay, let's go for this select star. And how are we getting? So these are all incorrect actually. Let's go for this. So senior data scientist how many time it is appearing 1 2 3 4 five five time it's appearing here right and what about others 1 2 3 4 5 why it is appearing senior data scientist what do you think why it is appearing as max is it appearing as max because it is a first value it is a first not the character length. It is not taking the length into consideration. Okay. Okay. See this everything when we import the table, right? When we import data from the table, all these columns are text only. All these columns are text. But if you check here, if you check here, suppose the experience, it consist of number. If if you check for salary column, it consist of numbers. If you check for employee rating, so that is also numbers. So you will be able to apply your uh aggregate here. Aggregation here. Getting my point? So that's how you decide. Shall we proceed? You don't have to do this this part. Okay. But anything suppose if you wish to get the count then it will not create any problem. If you wish to go for count and this as well. Okay. It will not create any problem because it is just counting it. It is just counting it. So you will be getting 20 here. It is just counting all rows. But if you wish to go for select all this is all columns, right? If I wish to go for this, I'm getting an error. You I'm getting an error. I will not be able to do it because it's a comprises of numerical value as well as text values. Getting it? So, this is incorrect. Am I clear? we shouldn't use we shouldn't use the aggregate function on the uh text column. So when we are using this aggregate function on this okay suppose this role or maybe we are going for this uh so this is just giving some garbage values this is some giving some garbage values sum of the first name if we add the first name can we add the text values no right so it is simply giving us zero it is simply giving us zero what if we write max of first name so that is why it is we should never use it we should never use it let's go for this Maybe check it out. So we are getting William here. So is William is a name that is appearing maximum number of times. Is it so? How many characters? So it is not just this is just giving us some garbage value not based on anything. role uh role is a text column then why aggregate function got over applied there but data got retrieved for the role column okay okay so why we go for aggregate functions I'll let you know okay so I'm just removing shall I remove this one or shall I keep it the text should I keep it give me a moment Okay, let it be here. But I'm having incorrect here. Uh, let me remove this. Okay, let me remove this. But I hope you understood it. You don't have to try all these things. Now, please check it out here. That is the incorrect way. Okay. So, gar you are getting the garbage value. Why? Because you have not done it in the correct column. Right? These are the text columns here. So, you have as I mentioned, you have to use it on the numerical columns. then you will be uh able to get the correct value here. Okay. Never go for doing this. We do not go for adding the getting the total of the first name. We do not go for getting the average of the uh first name. Not possible. Neither we go for the max and the mean. Right? We don't don't go for that. So do not apply that. Do not apply that. Okay? So these are all incorrect usage. Incorrect. We only use it with the number val number column. Okay. Now if you check here, if you check here, we were going for retrieve the maximum salary, minimum salary, average salary of employees working in the entire organization. But suppose if you wish to go for if you wish to get okay retrieve the total salary total salary of employees in their department. If you want to total the salary based upon their department. Okay. If you wish to get it, how will you do it? Select sum of salary. Okay. Select sum of salary. You are grouping the data. You are grouping the data from EMP table. Okay. Then you have to group it. You want to group it according to the department. So you have to mention group by dpt group by dept okay so check here when I do the grouping I'm able to get the total salary but I'm not able to understand which department what a total salary I'm not able to get it so what I'm going to do I'm also mentioning the department right now okay select department so you are able to get the Department wise total salary or let's make it max salary. What is the department wise max salary? Let's do it. That will make more sense. Okay. So let's go for this. This is a department wise max salary. So you are able to clearly understand that for the retail department what is the max salary? 10,000. For the finance department uh you have the max salary as 10,500 automotive we have 11,000 healthcare 9,500 or 16,500. So this is the maximum salary. You have the maximum salary when you are using group by right it is just grouping into a single row. You it it is just grouping into a single row. But if I wish to get the employee details right I'm getting the department wise I'm able to get it in a single row. Right? But if I wish to do it like this select asteric then if I wish to go for this same thing I'm just copy pasting it from here it will throw me an error it will throw me an error okay for group when we go for group by right we will not be able to get the entire details It is just summarizing it into a single row. The all the rows right are being grouped into a single rows. So that is why if you check here the retail department the details are we getting as so 10,000 that the maximum value is 10,000 for finance all the finance you it is just comparing and we are getting 10,500. Please check it out if you're able to understand or not. Can you retrieve the role wise? Roll wise average salary. Try to go for this. Roise average salary. All is a department. All is a department. Okay, we are department all also. Please check it out. If you go for select star. So there is a department name all can retail retail see. Okay. Yes. Great shi. Great. Okay. If you wish to get the role wise average salary, you can go for role here. We'll go for average salary as I'm renaming the column group by is always written after the employee uh after the table name. Okay, group by uh role. So this is the average salary. So senior data scientist we have the average salary as 6840. Junior data scientist we have 2900. Associate so on we have this roles and then we are getting the uh you know the average salary as well. It will not group. You will be getting it for the entire organization. If you don't write group, okay, you will be getting it for the entire organization. So if you write like this, we have seen it here, right? for the entire organization. Uh data types you cannot use it here. Okay. Data types we will be doing it while going for creating the table. Okay. While going for creating the table we can go for that here. I know you are asking because uh you want it for maybe you have uh this one. Just give me a moment. Okay, I have not used group by here. Let's go for this. Okay, you are talking about this one, right? For that we have functions. We have functions like round functions. We do have I'll discuss later. First understand this part. Anyone having confusion here? If you do, if you just use the aggregate function, you you are doing it for the entire organization. That is all the data. You are able to get a single value for that. But if you are doing it group by, you are able to group by department or you are going so you are going to do it column wise. the values of the column. Can you retrieve? Can you try to retrieve continent wise, countrywise? Maybe uh average rating or max rating max rating there is a employee rating column is there right? Can you try to retrieve the country wise sorry continent wise country wise max rating can you go for that? So I'm going this instead of grouping by a single column I'm going for two. Give me a moment. I'm just checking your queries. Okay. Chandana give us example for creating a table mean I mean we are using. So see everything will be discussed to you whether creating a table and all those things. So today is the second class. So we are learning about uh how can we query the uh because as an analyst we have to know how to query the database right we will also in the upcoming sessions we are going to discuss about how can we go go for creating a table how what are the data types are there everything will be discussed to you okay then kana give me a moment select ro average salary from group by role select ro average salary as as What is the difference between the two? There is no difference. Only thing one name see there is no difference. Even if you write like this, that is also correct. I am just re So if you write it like this, okay, if I'm writing the query like this, you can check the name of the column. You can check the name of the column. It has been renamed as ag cell. If you do not give that, if you do not give that, you will be able to see the function name. You will be able to see the function name. Check. That's the difference. I'm renaming the column. So ultimately it is doing the same thing. Exact same thing. We are just renaming the column. Okay. For readability purpose. Great Bat Red Man has also done it. Great. Yes, exactly. So those who have done it, it's absolutely correct. You have done it correctly. So we wanted so you want to group it not only by single column but by two columns. So I can go for select continent. Let me go for first continent. Okay. Then we have the country. Okay. Then we are wish to get the max and we want the EMP rating. I'm going to rename the column. Okay. as highest rating. Okay. From table group by group by uh continent not only a single column. I want to group it by continent and country. So you have to separate it by a comma. You have to separate it by a comma. If I execute that, see for the continent Asia, country India, highest rating is four. For continent Asia, country China, highest rating is two. For South America, Colombia country, we have highest rating as five. So you are able to basically group it based upon the cont continent and the country. Are you getting it? So please check it out. I'm sharing the code as well. You will not be able to see it using the group by. Okay. How do I see the employee details of who got the highest rating now? Okay. So after this you will not. So using the group by you will never be able to get the details. Okay. For that we have the advanced functions like your windows function. Okay. So we'll discuss that in the later later sessions. Using group by it is it will get summarized into a single row. You will not be able to get the details. Okay? All of you are done till this part. Are you all done? Okay, shall we proceed? Okay, now please check it out. I'm going for the next query. Okay, why error is coming? Okay, you are grouping by country then continent. Okay, the thing is that which is the biggest component here? We have the continent and then country. You cannot go for country and then component. That is wrong. Right? Logically it's also wrong. So you have to go for continent then country and here also continent and country. Do not go for country because a country belongs to your continent. Right? Continent does not belong to your country. Ma'am only do continent. Okay, if you wish to do only continent here. Okay, if you wish to do only by continent, I have to remove the country here. If you wish to get the continent wise suppose max rating, okay, in that case I will not be able to use the country here. It's a single column. So I will be able to fetch only single information here. See this is the continent wise highest rating. Continent wise highest rating. Okay. Any confusion? Shall we proceed? Okay. Give me a moment. But ma'am, India for China too. India that is the highest rating. What is the highest rating they are getting? What is the highest rating that they are getting? If I talk about here if you this is basically based upon your continent and country. So from Asia we have two countries we have India and China where India is having that is based upon the table. Okay that is based upon the table. So employee is getting the highest rating four. For China the highest rating is getting they are getting two. What's the issue there NL total six asia but you are grouping it right? You are grouping the data here. You will not be get able to get it. Total six. You are not getting the question only. Then you are getting the highest rating. That is the maximum. Even if you have six here, please check it out here. If you don't get it, it's very difficult here. Okay. Uh select star from EMP ID. Sorry, MMP table where sorry continent equal to Asia. Let's go for this. You are saying that you have okay let's go for this okay there are four right in India we have in India we have three three it's coming three 1 2 3 what is the highest rating 314 what is the highest rating here for India what is the highest rating check here this is India This two is again India. So we have employee rating three and one and four. What is the highest rating for India? Four. For China we have only one person. For China we have only one person. And that rating is two. That rating is two. Am I clear now? Am I clear now? how it is getting. Okay, let's proceed now. Give me a moment. Let's go for writing one query here. retrieve how many members are working in each department. But their salary is more than 5,000. Retrieve how? Okay. Read the question properly. Okay. Retrieve how many members are working in each department? Okay. Okay. Let me write. But no, whose salary? Let me go for whose whose salary is more than 5,000. Whose salary is more than 5,000? How will you do that? First try to do this one. You can do it. You can do it this one. Try it out. How will you do it? What you have to get it? What do you have to do here? You have to go for count. You have to count. Go for count here. Okay, let me go for this. Check here. when we talk about this retrieve how many working how many members are working in each department okay so let's go for this select okay department count I can go for this this simply going to count right from EMP table okay if I I can simply go for group by department. So half of my question is done. Half of my question is done. So retail we have seven, finance is three, automotive four, healthcare four and all is two. All is two. Right? But if you check here the this question is not complete. retrieve how many work uh how many members are working in each department whose salary is more than 5,000. So there is a wear clause. Okay. So here where you have to go for the condition where is always written before group by. Where is always written before group by? Okay. where salary greater than 5,000. Okay, you are going for the criteria. We are only counting those people whose salary is greater than 5,000. Then we are grouping by the department. Okay, now check the output before applying the wear clause right after I apply the wear clause. What is the output? So for the rep retail department people getting salary above 5,000 is four automotive three healthcare three finance two and all is two. This is basically we are trying to combine we are trying to combine where with group by. Okay. Combining where with group by. Yes. Bit tricky. We are combining where with group by here because okay I'll discuss the thing is that uh the next thing I'll go for the having clause only when do we go for where when do we go for where clause when we are talking about the individual salary when we are talking about the individual salary when we are talking about the group data we go for having clause Okay, I'll give you an example right now. You'll be having uh you'll be understanding it better. Please check it out. Here I'm sharing this. Give me a moment. I I'll just remove this one. Okay, this is creates some confusion. Let's try out this please and then we'll go for next uh query here. Try out. Try out please. And then I'm doing the next query. Try out this one. Then I'll show you one more query. Kindly confirm if you're done. Okay. Okay now check I'm changing the question man you will be getting your uh answer from here as well please check if total salary if total salary of the department of the department is more than 30,000. Okay. Then only show then only show how many members or how many employees are working in it. Now check the question properly. If total salary of the department is more than 30,000 then only show how many me uh like employees are working in it. We are not talking about the individual people but we are looking at the indiv uh department wise total salary department wise total salary. Right? So if you wish to go for this question here you will not apply where here you are not going to apply where where is basically for individual rows here we are going for the grouped rows we are going to filter the grouped rows okay department salary is more than 30,000 how do I do that first select department let's go for count star Right. From EMP table group by department that's the first part of the question. If total salary of the department is more than 30,000 then only count for it. Okay. In this case when we are talking filtering about based upon the department right grouping by the department we are having right using the having clause I'm writing having sum of salary that is your total salary greater than 30,000 greater than 30,000 now please check this is the output this is the output once we add it right if I go for this let me just show you here if I go for this right so here we are adding here we are adding based upon this right we are checking whether departmental salary is total salary of the department is greater than 30,000 is not or not okay now if I go for this see we are getting this one having clause is always written after group by having clause is basically you have grouped the data and you are applying the filtering condition on the group data. You are applying the filtering condition on the group data. Okay. Give me a moment. Others try it out this one. Yeah. Yeah. I will explain one more time. I'm getting an error when I have a salary in column name. When you go for group, right? When you go for group you will not be able because ultimately you are going for the uh grouping right you are going for the grouping you will not be able to use the salary here uh only the column which by which you are trying to group you will be able to get it don't put the salary here don't put the salary here having is basically a filtering condition for the groups where is basic when you where when you where is also a filtering condition but it is it works on individual rows. It works on the individual rows. But if I come to the having right, what happens? You have already grouped the rows and you are trying to filter that. You are trying to filter that. Okay, let's try out few more examples. Shall we do that? Then it will become more better clear actually. Okay. So let's try it out in where clause what happens you first filter and then you go for that okay grouping and here having is different. So please check find departments where the average salary is greater than 8,000. Now read the question. Is it talking about the department w department wise average salary or the individual average salary? First understand the problem. First understand the problem. It is talking about the individual one or the department one that means is it talking about the group data? Yes, department right. So first you have to go for you have to go for your u finding the group data department wise average salary you have to go for it and then you have to apply the condition average salary greater than having average salary greater than 8,000 okay so let's go for this don't go for count it has already mentioned average salary it has already mentioned average salary no count okay so what they have asked Select D average salary. You can rename the column as well. So you are getting department wise average salary here. Right? If I do that I'm getting it. But what is the condition? The average salary is greater than 8,000. So here I will be using having I have already renamed the column right? I can use directly this one. So I'm getting only one. Yes. Okay. So now please check out one thing. What is the syntax? Now you might be curious about okay what is the syntax that we should follow. Okay. So please check here. I'm writing the syntax here. Give me a moment. You should always right for displaying anything you should go for suppose all columns right or you can go for mention the column name okay to display column names okay then you have to mention from table name from which table you are trying to extract it then you have the where condition where is basically filtering filter the individual rows, right? Then you have group by then column name. Okay, group by column name. That is you are going for summarization or you can also if you are aware with Excel, you can also say POT, right? Pwatt is also doing the same thing. Then you have the having clause. Okay. Having column condition. Let's go for this is means what? Filter for group data. You can you can never have having condition without group by. If there is group by don't then only there will be having clause having condition. Okay, otherwise we cannot have it. Then we have order by column, right? So that is basically sorting sorting the data. Then we have last limit number of rows specific rows. So this is the exact sequence you should follow. I hope it will be clear right now. What is the sequence? When should come what? Okay. So I'm just quickly sharing that and if you have any confusion you can feel free to ask. Just give me a moment. I'm just pasting this here. Okay. So, this is a sequence you must follow. Check it out if you have any confusion here as well. Okay. I have not discussed that, right? The like operator. Okay. I'll discuss the like operator. Can you please explain again when to use where and when to use having? Okay, have. So always remember where is basically filter the data for individual rows. Filtering for individual rows and when we have grouped the data when we have used group by and we want to go for filter the grouped data we will be going for the having clause. Okay. So suppose again I'm going for another condition. Let's go for another condition here. Okay. Let's go for some um other question right now. Okay. Show departments where average salary. Okay. Is greater than 70,000. No, 7,000. Let's go for 7,000 instead of 7,000. for employees having more than 3 years experience. How will you do this? How will you do this? Show departments where average salary is greater than Show departments where average salary is greater than 7,000 for employees having more than 3 years experience. How will you do that? Yes. So if you check the question properly, so they have asked us to go for group by departments, group by average salary, right? So we have to go for select D. Always break the problem. Always break the problem. Okay. Average salary. Sorry. Sorry. Like this. Average salary. Okay. Then we have to go for from emp table group by department. Okay, this is half question is done. This is half question is done. Show departments where average salary is greater than 7,000. Okay, so this is talking about greater than 7,000. So that means what? It is talking about the departmental average salary. Right? So let's go for this. So departmental average salary you have to just your it should click in your mind departmental means after group by we have to go for having having um average salary right. So I have not renamed the column. So I will be using this only I have not renamed it average salary greater than 7,000. Half question again done. So I'm getting the finance automotive and all but there is one catch again for employees having more than 3 years experience right half of my question is done but I have one more criteria here I don't have to go for group by entire department and the average salary so this is your where condition where experience Sorry, sorry, sorry. Experience greater than three. So we are getting this four departments are there. We are not getting healthcare here. So I'm combined where clause and I have combined the uh sorry I have combined the having clause. Are you little bit able to understand this? Great. Shall I give you one homework? small homework. Yes, definitely you need practice. Okay. Okay. So, please try out this question. Okay. I'll give you one one question only. Not to worry. So go for this. Um, greater than greater than or let me write uh equal to four. Retrieve the countries. Okay. having more than uh two uh having more than two emp rating greater than equal to. So this is basically talking about the uh count of the employees. Okay, count of the employees where EMP rating greater than equal to four. Okay, will you be able to do it? So this is a homework for tomorrow. You can I'll just check it after the in the class. Okay. Would you mind showing the question? Which question are you asking me about? This this question. Okay. Please check the question. Okay. So in this case why the and operator will not work? Why the and operator will not work? Check here. Here it is talking about the departmental wise average salary which should be greater than 7,000. And here it is talking about the in uh so we only should count only those people right who are having uh sal experience greater than 3 years. Experience greater than 3 years. So we will not go for and this is not the same condition. This is not the same condition. These are the two different condition. Yes. So I will be sharing the question in the doc file as well and in the chat also you can go for it. So tomorrow what I will do first I'll start with the like operator. Okay. So if like operator is basically used for pattern matching and after that I sorry I will be going for the joints. Okay, a very important topic we are going to cover to in tomorrow's class joints. So please make sure whatever we have done so far practice it very well. Okay, try to do all the questions again. Give me a moment. Since it is two different column fields that no no you have to understand you have to understand from the question you have to understand about the question it is not about the two different fields what is talking about here one is talking about the uh you know departmental data you want to check for only departments where the average salary is greater than 7,000 right but we only should have we should only consider those people who are having experience that is individual data it's talking about. So we have to go for the wear clause. This is not of the same condition. Had it been the same condition maybe the greater than 7,000 uh sorry greater or maybe you can go for between and and as well that is different that is operator is different. Here we are having the having clause. Having clause is basically you are trying to filter the group data. Right? Where clause you are going for individual data. You are going for individual data. First you have to check. Okay. We are only going to group those people having experience more than 3 years. Okay. First you find those and you are then grouping it. Then you are going for checking whether the average is greater than 7,000 or not. That's what give me a moment. Should we check for EMP rating? You try it out. Okay. This that's a question for you. I'm not going to help you in this question. Okay. Okay. Now, check me check it out. How to add data in table? If we want to add new data that I will be discussing when I go for the uh types of commands. Okay. After the joints we will be going for that don't worry about it everything will be taught to you Jotica ma'am countst star means give me a moment count star means uh for whole data okay if we go for count right yes count we can go for like this it's okay if I write it here I can count because it is simply counting right so this means all columns asteric means all columns okay I can count Or even if I go for a single column as well that is also works. Either you go for this 20 or you go for it will just do the counting, right? It will just do the counting. 20 same output. Okay, my screen is visible to you all now. Now check here for me. So I was taking class with the NMIT students. So that's why for my my schema Bhavia, please check here. My NMIT is selected right now. Okay. But I have to select your SQL fab. What I will do? What I will do? I'll click here and go to the SQL FAB. So I will double click here. Right? Double click here and then I can execute the query. Pavia got it. Now after you have you can see here it is highlighted. It is in bold. You can go for uh you can go for uh this one. Executing the query. How will you go for this? Okay. How will you go for this? This one thunderlight icon. So you will just right click on the database. Okay. Go for table data import wizard. I'll repeat once again. Right click on the SQL fab database. Fifth option you have the table data import wizard. Click on that. Browse to the location where your file is present. EMP table. Okay. So probably wherever you have this EMP table, browse to that location. Click on open and then just go for next next. Okay. I already have this table. So I'm getting this drop this table. Okay. just go for next next next and finish it. You will be getting it. I'm not doing it right now. If you if I wish to go for the retrieve the countries having more than two countries, okay, having more than two emp rating greater than equal to four. So you have to understand okay I have to get the I have to go for group by here. Okay. And I have to check for the count more than two. Right? So what I'm going to do here, please check it out. Select country. Country country and I'm going to go for count. Right? I can use for this for count asteric. Then I'm going for emp table. Okay. Emp table. If you don't wish to go for this, okay, please everyone check it out. Here I can go to this tables, right? I can double click on the table name. Then also the table name will appear here. For now, do this. Later I will show you how to go for renaming the table. Pavia, please check here. If you are finding the name complicated, check here. Okay, check on my screen what I'm doing right now. You don't have to write the name of the table. The name of the table is present here, right? Just go click on this EMP table and you will be able to get it. Okay. So group by country. So this is your half question. We are grouping it by country and we are trying to get the count of the people. Right? I'm able to get the count of the people. So this is the normal group by we have. This is the normal group by we have. But what they have asked us. So check here. This is a normal group by uh output is there. Okay. Now what they have want us in the question having countries more than two emp rating okay greater than equal to four okay I I'm this here we are talking about uh number of people okay number of people should be two number of people should be two having emp rating greater than equal to four. So here emp rating greater than equal to four that means we are talking about the individual records having basically it's emp rating is basically of the individual rows individual rows. Okay. So I will go here. Just give me a moment. Where where EMP rating where EMP rating greater than equal to four because I am considering four as well. So half of my question is done. Let me check for this. So here we have this countries. We have this countries right now. Okay. We have Colombia. two peoples are getting uh EMP rating as four. Germany we has two two people getting EMP rating as four. Canada 3, USA 2 and India 1. Okay, who are getting greater than equal to four. But I want countries having more than two EMP rating. Right? Countries having more than two EMP rating. So I'll go I'll go for having here. I'll go for having Count greater than two. Greater than two. If I execute it, I will get only Canada. Having is basically used having is basically used for your once you have done the group by, right? Once you have done the group by to filter that group data, you are going to use the having clause. Okay? And where is used? When you are go talking about the individual rows, when you are talking about the individual rows, okay, please remember we are going to if if we are talking about the individual rows. Okay, so if you wish to go for check here one thing always remember because few of you are having confusion. Select star from EMP table. Right, this is my table name. So when we are talking about this rows, if I want to filter this rows, individual rows, right? We are going to use the wear clause. You cannot use having here. Having can never be used without group by. If you have grouped any data, if you have grouped any data, then only your having will work. Okay? If you have grouped any data then only you have to use the having clause. Okay. Everyone clear? Everyone clear? I'm sharing this code because today now we are going to look with the work with the like operator. Okay, just give me a moment. So this is the I'm pasting it here. You can try it out please and let me know if you have any confusion here. You can never use please remember this thing. There can be no having clause without group by. Okay. So you can you you can use where clause without group by. Okay. Where is we have used that but we cannot use having because if you have group then only you will be able to filter that group. Try it out please. Shha are you able to import the table now see what happens even if I if I talk about this one right I have used count asteric right because this is just counting it so that's what yesterday also I have shown you even if you go for this okay give me a moment suppose I'm going for emp ID uh right if I do this I will get 20 and even if I write all columns from EMP table I will still get 20 here I will still get 20 understood so that is why anything you can use you can go for EMP country as well. You can go for the aesthetic as well. Here in this case when you go for count. Okay. So if you have if you go for the output grid you can just expand it. You will be getting the output grid. You can just expand it. You will be able to get it. Yes, count can go for all columns, right? Ultimately, it will show count there. It is just counting how many records are there. Total it's just giving you not an issue. Okay, where I was writing the like operator, please check it out here. Okay, now please everyone focus here. Right now, we are going to discuss about the like operator. Okay, focus from here. Focus from here. The like operator. You don't have to write the text. What I'm going to do, you can just try out the quotes. First, we have to understand what we are doing everyone. Okay, first we have to understand. See course you can run. This is not about a data entry or anything just typing the codes. You will be able to it will come with practice. First you try to understand the thing understanding is pretty much important. Okay. So please everyone focus here right now. So we are going to work with the like operator. Now we are going to work with the like operator. Now what is this like operator used for? It is used for pattern. Let me go for pattern matching in text columns. Okay, it is used for pattern matching in text columns. So we have so these are there are two like operators. Okay, these are also known as your wild card operators. Okay, these are also known as your wild card operators. Okay, one is there are two operators are there. One is percentage which represents which represents u any sequence of character any sequence of characters. Okay. And one is your underscore, right? Represents exactly one character. Okay, it represents exact one is your uh percentage symbol and another is your underscore symbol. Okay, so it is represent any sequence. So it can be null also, it can be uh five also, six also, anything and it will represent exactly one place. Okay, exactly one character here. Now focus here. Suppose I write the query like this. You don't have to write all those things. All those things I will be sharing in your doc file. You will be getting it. Okay? You don't have to write the text. Now how do I use it? For example, okay, I give you a question right now. Retrieve employees whose first name starts with letter D. Okay, if I write this in this case, if I just mention the first name should begin with letter D. Okay. Then you can go for select star that is select all columns all details from emp table. Then I'm going for the filtering condition using where where first name like okay give a uh give a inverted comma here. You can give single inverted comma or double inverted comma up to you. Okay, both are same. Both refers to the same. And what is the question here? The the name should begin with D, right? It should begin with start with D. After that we can have any sequence of character. After that it can have any sequence of character. So I have given a percentage here. Right? So semicolon if I execute see all the details of the employees where the first name is starting with D right it's appearing here okay it's appearing here so there are three names in this data set where the there are like uh the it starts with the alphabet D so you can see here D3 is a name where see how many characters are there are six characters after D Here in David we have four characters. In Diana we have again five characters. So it is any sequence of characters but we did not write start with D. No I have used the like right where first name like and I have mentioned the letter D and the person. So so automatically means that that the it should start with D. It should begin the name should begin with D and after that I can have any sequence of character. We don't have to write start. No, it is not. MySQL is not case sensitive. My SQL is not case sensitive. Let's go for this. Why do we need to write start? We will this like is going to do the pattern matching. We don't have to write like means start. No, we have to go for this. Okay. Anything we can go for. There is no command like start. So I'm pasting it. Give me a moment. Let me paste it so that others are also able to try it out. I've shared that in the code file as well. Now coming to here, coming to this case, retrieve employees whose first name has A and anywhere. So when I talk about A and anywhere, it can be in the middle, it can be the first name, it can be in the last letter. Okay? So check here. How do I do that? How do I do that? Select star from EMP table where now I'll give the pattern. So you can see here Neon, Diana and Janet. Sharing that as well. Try it out. Try it out. For text columns we have other functions as well. Okay. So I will be discussing as well. So this is used for pattern matching. We have for text functions other things like the upper function, lower functions that also we are having. We will be discussing no worries. Not today maybe. So those are the functions. Okay. While writing that a n should be that color what color bhavia multiple conditions like generally the operators you have the clauses are coming in Oh, yes. These are called operators. Lakshmi. Yeah, this is the first name. This is the column name. This is the table name. This is the pattern. You don't worry about the color right now. Just to execute and check. Great Gori. Gandhar. Uh, first name has an a and and last name. Yeah, you can go for using the and operator and check it out or or you can go for and check it out. Okay. So, please check out here. Find employees whose first name has exactly five characters. Okay. Exactly five characters. Okay. not any sequence. So you have to use the underscore here instead of where first name like 1 2 3 4 5 underscores. Okay. So here I am trying to extract the first name or details of the employee where the first name has exactly five characters. If I execute that, so I'm getting this Steve, David, Janet, Emily. So all of them are having exactly five alphabets or characters. Yes, it means five means I have this particular means right? It means exactly one character. Okay, it means exactly one character then I have used five. So if you want five you have to go for that. Okay. So just uh one second. So I just wanted to mention one thing I I'm sure that you might know how you might have some knowledge of the SQL okay but if it will be really in sync okay we will be able to coordinate well if whatever I'm teaching right I will be teaching you everything don't worry about that okay but but as you can see it's a mixed cohort okay few of them are absolutely beginners so if we are able to maintain the same pace right then it would be better Okay. How to get data with five characters and starting with D that also you are you will be able to do that. Okay. So please check here. Okay. Now uh you will be solving that question. Who asked me that question? Just give me a moment. Okay. Nikil, you can answer that question, but first answer this question. If you do that, if you do, if you're able to solve this, you are able to solve your answers as well. Okay, your question as well. Please check it out. Find employees from countries from countries where the second letter is n where the second letter is n. How will you do this? Any sequence of character percentage is there right Palvi? If you have long se if you have long names because someone's name may have 13 characters, someone name have uh you know three characters after. So you can just go for the starting alphabet and then you can go for it. If you're going for exactly, then you have to use the underscore. Try it out. Try out this question. Can we use or not? I have already given you a question. Please try it out. Okay. So, please check here. Find employees from countries where the second letter is N. Right. So first thing I'll go for select star that is mandatory where country like okay now I have mentioned second letter how do I know the first alphabet is underscore exactly one right then I have n then I have n so second alphabet is my n after that I can have any sequence of characters any sequence of characters right if I use this I'm only getting India see in India what happens the second letter is your N so can we use it together yes you can go for any length of character so if you wish to go for this how to get data with five character and starting with D okay so I'll go for select where first name like D. So first character is your D 1 2 3 4 five characters with exactly five characters and first letter is D. You don't need the percentage here. You don't need the percentage here. We have only David here. Are you getting it? You mentioned you need only five characters. So this is the first out of the five characters you have first character as D. Okay, let's check it out. Can we combine that or not? Okay, let's go for that as F lakmi has mentioned. Okay, suppose I'm going for let me go for the uh let me check if we can work it out. Can we use it in this way? If the two characters I'm going for like is not the valid. So we can go for only one here. Right. Okay. First name. Okay. Sorry, sorry, sorry, sorry, sorry, sorry, sorry, sorry. I did a mistake here. It is okay. Let's go for this now. Can I get it? Let us check it out. I can actually right first I'm getting okay I have for India as well I'm getting for India and I have David as well is it clear I have just make it little bit less constraint okay because and right both the condition needs to satisfy so I'm not going for and because and both the conditions need to be satisfied Okay, I can send all the codes whatever I have done so far in the doc file. This is your like operator. We can use and also but in this case uh yeah that is nicl that is not the correct way. Okay, that is not the correct way. You are mentioning see already you are so you whatever you have done this. Okay, if you write like this, please check it out. If you write like this, so what are you having here? Okay, you are having any length of character. So instead of mentioning here the five characters, you can just simply replace this, right? Same thing any length of the first the first letter should be D. After that I can have any length of character. So I will be getting the exactly the same output. That is the this one is the case right? Yes Jika you are correct because for India you have three rows and for David you have one who is belonging who who is coming from the country Colombia right? So you have total four we also got four only. If you wish to go for specific five characters the first letter so your this this one right percentage it just null and voids their condition right now it is not not five characters right now if you wish to go for five characters this is your starting character after that you will be having four so this is the correct answer you cannot go for this after that you cannot have more than this right if you wish to go for specific length of five this This is the case. No other way out because the moment you give the percentage that means we can have any length of character. Are you all clear with the like operator? Are you all clear with the like? Okay. Now we are moving to the next concept that is your joints. Okay. So as a analyst or as a data scientist you are going to work with joints a lot. Okay. So what happens when we first understand what is a joint then you will understand what is left. But that is not the correct way. Okay, see I I'm getting one people because here okay I am I have written the same but I'm getting three people. How many characters you have given? I have given only four. I have given only four. Okay. Because you mentioned the uh length is of four right? Length is sorry length is of five. The out of which you have d here. And if you give this one if you give this one I will get three only. That's what you are getting three. you have introduced a percentage nickel even if you give if you don't give this and if you give three only small letter D okay I'm getting one only why are you give that's what I'm saying you are giving the percentage if what is the meaning of percentage here why are you not understanding telling the same thing I'm giving getting the percentage means any length of character after you have given that one right so please check here see did I use the percentage no that is if I wish to get the percentage why because I can directly do this I will be getting you will be getting the exact same answer but if you wish of character length five you have to use this one underscore underscore And I did I'm using check it properly. Am I using a percentage? No. So that's what I was saying multiple times. Okay everyone. So before we go for the joints right we have to understand few concepts that what is join here. Okay, we have to understand here what is join? What is join? Okay, so as a analyst, as I mentioned happen like as a analyst, we are going to work on joins a lot, right? But what are joins? What happens? So in SQL, okay, we do not store the data in a single file like in Excel. All the informations are not stored in a single file. Rather we break the file and store it into multiple different tables. Okay. These are done by the database administrators. They create the design or the yeah database administrators where they go for the designing of the table. So in this case okay suppose here what is happening here we have the first table orders. You can see here the first table as the orders and we have the second table as the customer. So orders table will consist of all the informations related to the orders. Right? And the customer table will consist of all information related to the customers. Right? So the like what what are the information they will have it? Suppose in this case you can check the customer table have the customer ID, customer name, city and the age. And when it comes to your orders table they will be having the order ID, customer ID, product ID, sorry product and the amount. Now in this case you can see here when we want to go for joints right there have to be a common column between the two table. So in this case we have the customer ID as the common column. Okay. So customer ID in the customer table it will be appearing only once. Okay. That is a unique identifier. That is the unique identifier. But see one customer can place multiple orders. One customer can place multiple orders. So the customer ID will be appearing multiple times in your orders table. Do you agree? Do you agree? Okay. Now the thing is so the this is the unique just I'm writing here the customer ID in the customer's table is working as a unique identifier. Okay. So you can just think of it. I'm sure that when you joined this course, right? You were given a specific ID. Simply learn ID were given to you. Okay. Two people can have the same name. Two people can have the same name and the surname. But is it possible that two people are having the same PAN number or Aadhaar number? No right similarly if I talk about in this simply learn course also many of you might be it's possible that some of you having the same name and the same surname but it is not possible that learner ID you are having from simply learn it's the same it will be different right so that is the unique identifier that is the unique identifier like the customer ID so here the customer ID the the in the customer table it A customer ID is a unique identifier and it will not be repeated twice. Right? But in case of your orders table as I mentioned one customer can place multiple orders. Right? So for this case it is just acting as a reference. It is just acting as a reference to the customer table. So now if I want to check the information of like who is this person right C01 then what I can do I can just refer to the customer table and I can fetch the information. So this is just a reference here. Are you getting my point? Okay. So what is happening here? Please check. So the in the uh in SQL right in SQL the datas as I mentioned it's a relational database management system where datas are stored in the forms of tables right datas are stored in the forms of tables. So here we have the customer table. Here we have the orders table. Right? Now the thing is suppose you have to show me I mean going going for this query. Okay. Show me the details of employees. Details of employees who have placed an order. Okay. details of employee who have placed an order. Okay, how do you do that? How will you able to fetch that information? Okay, so for that I have to check. Okay, I since the details of the employee, the details of the employee are present in which table? The customers table. Give me a moment. Let me take this. Details of the table are pres of the customers are present here. And the orders placed are present here. The details of the orders. Now if I wish to fetch the information, right? If I wish to fetch the information, the uh details of the customer who have placed an order, I have to connect these two tables together. I have to connect these tables together and then only I will be able to fetch the information. Do you agree? Otherwise I will not be able to uh from a single table I will not be able to fetch the information. Can I do the can I do so? Okay. So for this purpose right for this purpose why employee it's a customer customer okay so please check here no employee here I'm just mentioning about the details of the table I will sh send you everything but understand it please understand it everything will be this file also I will be sharing with you all these things okay first you understand then we will be implementing it. Okay. Now, please check here. Now, please check here. If I wish to fetch the details of the customers who have placed an order, I have to connect it. Otherwise, this is this is the table of this is the table storing the information of all customers. No matter whether the customer has placed an order or not, right? But here we have the details of the customer who have placed an order. only when the customer has placed an order then only the customer ID is present here in the orders table and I have to refer to this table to fetch the information of the customer right so we have to go for joints right we have to go for joints now for joints the most common so there are basically four types of joints are there okay first is known as the inner joint okay and this is the most popular type inner joint. Then we have the left joint. Then we have the right joint and finally we have the pull joint. Okay. So when we go for joints, right? When we go for joints, we have to always make sure we can only give go for we can only go for joints if and only if there is a common column common column between the two tables. Okay, between the two tables. If there is a common column between the two tables then only we will be able to do join join otherwise we cannot do it. Okay we cannot do it. So now we are going to understand each of these join one by one. Okay we are going to the details. So as I mentioned first we are going to go for the inner join because that that is the most popularly used in industries. So we are going when to use uh what join we are going to understand that as well because without understanding that we will not be able to get the correct output. So it's pretty much important before we go for applying the joints we understand when to apply what join. Okay. So please check here as I mentioned the most popular join we have is basically your inner join. Okay. So check which type of join will you be you will be using depends totally upon the problem statement. Okay. So please check here. Suppose I'm doing a ven diagram here. Okay. I'm going for a ven diagram. You must have do done it in school as well. So you can check here. So this is the two tables are there. Okay. So this is we have seen that there is a common uh customer ID is there in both the table. Common column is there. Now suppose this is my orders table. This is my orders table and this is my customers table. Okay. This is my customers table. Now I want to fetch the information of the customer. Suppose here this is a question right? Show me the details of employees who have placed an order. Show me the detail. So not employees customers who have placed an order. Okay let me cross it here. Just give me a moment. Instead of this, let's go for customer. Okay, I'm canceling this. Don't go for this question. Check here. Okay, show me the details of customer who have placed an order. That means what? We are interested in the common portion of this. We are only interested in the intersection. We are only interested in this part. Right? So when the question is this where we are only interested in customers who have placed an order. Customer who have purchased something or placed an order we are going to go for inner join. We are going to go for we are going to do inner join there. Okay. Because in inner join what happens? Okay. Now let's check it out. What happens in inner join? Please check it out. I'm scrolling down. I'm removing this part. Okay, so this is my inner join. Okay, this is my inner join. So you can see here what were the columns? What were the columns in your uh orders table? You have the order ID, you have the customer ID, then you have the products and the amount, right? And in the customer table we have the customer id, customer name, city and age. Right? All the uh columns are present from both the tables right now. But here you can check here whichever customer ID right you can see here in this case C in the orders table C06 is there but C006 is not present in your customer table. Right? So you will not be able to get it in C06 right you are not able you will not be able to get it in your inner join and if I talk about your C05 here which is present in the customer table but that customer has not placed an order. So in the inner join that is not present that is not present. So what happens in inner join whichever rows are common in both the table only we are having the information of those rows. Are you getting my point? Whichever is common in both the table. Right? So that is why we are not getting it. So this is your inner join. This is your inner join. Yes, common rows, right? Common rows will appear here both the table. So that is the intersection. That is the intersection. That is your inner join. And this is the most common join type of join in SQL. This is the most common type of join in SQL. Okay. Now again understood inner join. Understood? Inner join. Check it again. Give me a moment. This is your customer table. This is your customer table. And this is your orders table. In customer ID, you are getting having C1, C2, C3, C4, right? C 0 1 0 0 2 3 and four. C 005 is there. Okay. But in the order table, you will not be able to find that C 005. Okay. And C 006 right that customer details is not present in the uh customers table. So when once you go for the inner join right you will not be able to get the details of that particular customer. Yes. Does it mean that it will display only the common values? Yes. The common values. Right. The for the common column how are you joining it? You are joining it using the customer ID. Right? So in the customer id whichever is in whichever rows are matching exactly in both the table for that only you are able you will be able to get it. Okay. Now suppose if I go for this query. Okay. Have you understood inner join? Have you understood inner join? Okay. What will be the benefit for inner join? You are getting the common columns intersection the customer who have placed an order. If you want to get the information of or the details of the customer who have in the table please. Okay. So these are the customer. Let me go for this. Check here. Check here. These are the details of the customers who have placed an order. So, Alice have placed an order. Bob has placed an order. Charlie and David has placed an order. So, now you are able to get the details of the customer who have placed an order, right? But you are not getting for Eva because the that this particular customer has not placed any order. But here not only you are able to get what is the like customer name but also the products they have ordered amount they have paid everything both rows and columns should be common right? No no no only common columns should be there. Give me a moment. VLOOKUP is something different. Okay, V lookup you are trying to fetch the information from a table. Okay, V lookup is different here. But this is just joining. You are able to join the table merge the table together. If you have heard the term merge, we are merging two tables together using a single column. Okay. So here the if I go for inner join it's matching the values values of that common column row values of that common column because we are merging that check the output please check the output uh please how are we getting this is the 01 right oh sorry 01001 and here we have the customer so that customer has placed two times two orders with order ID Okay. 1 104 and they he have placed a suppose for the product laptop and the headphones amount is 1200 and then 150 right so you are able to get the information twice here similarly this person right C002 he has placed two orders you are also able to get the information but C003 and C4 has placed one orders Yes, it is merging from two tables. You are able to get one table. Okay. Shall we proceed with the left join? Check it out. The output the inner join output. You can check the table as well. Okay. Now this was basically if we are interested in the information. Now we have we were interested in information where the customer has we want to get the customer details where the customer has placed an order. Placed an order. So we will be going for inner join in that case. Right? But we also have left join. Now what do I mean by left join? Here we have the first table as the orders. The second table we have the customers, right? So this is your first table which is your orders. Okay. And here we have this is the table customers. Right? Now suppose I go for a condition. Now this is my problem statement. Okay. Show me the Okay. Show me the details. Details of all orders. Okay. The details of all orders. Even if no customer details are available for it. Okay. Available for it. Check the problem statement please. Show me the details of all orders. Okay. So I'm giving priority to the orders here. I want the details of all orders even if no customers. Okay. Even if no customer details are available for it. That means I am interested in the customer details but also I want the orders where the where the uh probably the orders have never been ordered. Okay, pro probably the products are never been ordered. So in that case, okay, so please understand this. This portion gives us the common information, right? The products that were ordered and we have the customer details. But here in this question, what we have? Show me the details of all orders, right? Including those if including those that where no products were ordered. Okay? or maybe where no customer details were available. So what we are giving priority to this table, we are giving priority to this table. We want all the details plus we want the common details as well. We want the common details as well. Are you getting my point? Okay. Now if this is the case okay so what happens in the left join if this is the all the information all the rows of your left table will be present all the rows of your left table will be present but for the right table okay for the right table only the common values will be present okay so now please check it out I'm just removing this diagram Now please check it out. Okay. The output. Now C05 is present here. Okay. And C06 is present here. But it is not present in the uh right table. Right? That is your customer table. Scroll down. Scroll down to the left join. Now you can see here all the details of your all the details of your C uh orders table are present here. All the details of your um all the details of your order table are present here. So that's why we are getting C06. But C05 is not present because we are giving priority to the orders table. Right? So it is not present. But since we don't have any information of the customer of sorry customer details of the C06 in that case we are getting give me a moment we are getting null here for the customer name uh city and age because we don't know the c with customer information we don't have. Are you getting my point? When we go for left join, right? When we go for left join, we give priority to the left table. Everything from the left table that is the orders table and only the common from the right table. So that is why when we go for the left join here. Okay. So for all the information or all the rows of your left table will be present. But for your right table since this information was missing right since this information was missing of C06 for the right table you are getting the value as null. Okay you are getting as null. Am I clear now? Because the informations are not present. I want all the details of the left table. Right? And only the common of the right. So this is the case. Now to be honest, this is after inner joint. This is the second kind of joint we go for. This is the second type of joint we go for. Mostly we 100 more like 99.9% of time we go for the uh inner joint. Okay. After that we go for it's still we have something we'll go for the left joint. Okay. Hardly we go for your right joint. Why? because left join and right join are exactly the same thing just changes is basically the placement of the table okay it's the placement of the table now suppose okay let me show you here let me give you an example here in this case left join and right turn are exactly the same thing okay now suppose if I tell you that is the placement of the table if I'm writing the problem statement Here, show me the details of all customers who have placed an order. Okay. including those who have play including those who have never placed an order. Okay. So what I'm interested in what I'm interested in in the so that's is just the positioning of the table. Okay. So this is my orders table. Now this is my customer table. Right. The second one is the customer table. I'm writing here. So this is my customer second table and here give me a moment. This is my orders table. Right now I'm interested in obviously the customer who have placed an order and including those who have never placed an order. So this portion this portion I'm getting. Right? Now if I wish to check the details. Okay. So check here. I want all the information of the customer. So C005 is there right? C005 is there. So please check it out. If I wish to go for right join scroll down. So check the right join here. We will have C05 as well. We will have C05 as well. But we don't have the C06 right and for the right because we don't the C05 right this person has never placed an order. So for them for it the orders details are null. Are you getting my point? This is right join. This is right join. Yes. In left joint C05 will be missing. In right joint C06 will be missing. Are you able to getting? Are you able to get it? So depending upon the right table whichever table you are taking right your left join and right journ is the same thing. If you take the customer table as the first table okay then you will be going for left join. So both are the same thing. We are going to do it practically right now. So let's go for the same question. Find all customers who have placed an order. Okay. Who have placed an order. Now the thing is that before we do anything, right? We have do anything what we have to think what is the tables we will be going for. So we need a customer table and we'll need a order table. So we have it both. We have it both right customer table and the orders table. Now what we have to check the common column. So you can expand it and the columns you can see customer number is there. Similarly in the orders table also you can check what are the column um columns are there. Okay. So here too we have the customer number. So we have the common column. So we can go for join. Okay. We can go for join here. Okay. So here you see now you focus on the screen. Okay. Focus on the screen. Let me explain you the concepts. If you are not able to do it, I'll help you out. Okay. The customer number, the customer number is the common column. Okay. Now check what are the which is the type of join you will be going for. So that you have to identify from the problem statement. Okay. The customer's table will consist of the information of the customer's table. Give me a moment. So this is your customer's table. All the details of the customer will be stored over here. Okay? And all the details or order details will be stored over here. Right? So now if I wish to get the customer uh find all customer details who have placed an order that means who have placed an order. I want to get the intersection. So what kind of join I will be going for? What kind of join I will be going for? Yes, exactly. Inner join. Okay. So now please check the syntax. How do we go for that? I can simply go first. I'm showing you with all columns. Okay. Select star from Okay, you want the syntax, right? Let's go for the syntax first. This is the syntax of join. Okay. Syntax. Select star from table one. Okay. Join type you have to mention join. Then table two you always when we go for the uh joints right we have to mention on here and then you have to go for the common column. So this is the syntax we use. Okay. So this is the syntax we use for your joining. Now please check it out. I'm going to implement it. Select star from customers inner join orders or what is the common column? I have customer number equal to customer number. Now if I do this, it will throw me an error. Okay, it will throw me an error. Okay, what is the error? It is ambiguity. Customer number is on clause is ambiguous. So that means see what is ambiguous here. It is unable to find that this customer number belongs to which table because customer number is common column in both the table. Right? So it is unable to identify whether this customer number are you talking about the customer number of the customer table or the customer number of the orders table. Right? To break the ambiguity you can mention here suppose this customer table name and here I can go for orders dot customer number. Okay. If I execute this I will be able to get the rows after uh you know after your join. So all the columns will be present here. How to fix the position of customers and orders ma'am? I mean as per syntax how to choose what should be it is totally up to you. It is because even in the in the case of inner join right. Okay. In the case of inner join you can go for the orders table as well as the first or the customer tables totally up to you. Or you can go as per the problem statements as per the problem statement which table you should. So if you're talking about the customer is appearing first. So we are going for the customer table as the first table order table as the second table. Okay. Okay. Now please check here. You don't want to write the customer like customers dot customer number every time. Right? orders dot because it's a such a large name here a big name here. So what you can do you can write C here I'm renaming the customer table as C and I'm renaming the orders table as O right now instead of writing customer and orders every time I can simply type like this can I do this can I do this are you getting my Yes, we have used the alli for the table. I have done absolutely nothing. I have taken all the columns, right? I have taken select all columns from customers as C. This is basically I'm renaming this table. I don't want to write multiple time the customers customers customers. Okay. What I'm doing? I'm just renaming the table as C. Similarly, I want I'm going for inner join. So, I'm mentioning the join here. Then I'm going for orders and I'm mentioning it as O. And this is what we have to write on. Then the common column that this customer number belongs to your customer table and this customer number belongs to your orders table. Getting it? Okay. Now check. Now check please everyone. Okay. Here I have the customer number. Customer number of the customer table. Okay. Customer number. Customer name. Contact last name. Okay. Then contact first name. Phone address line one, address line two, city. Then we have the state, postal code, country, sales representative, uh employee number, credit limit. Then we have the order details. Check order number, order date, required date, ship date, states, comments, and then the customer number is repeating twice because we have went for select star. Select all the column here. Select all the column here. Right? We went for twice. So I can go for instead of select star I can go for specific column. Okay. I can go for specific column. How? So I'm doing it in the next query. Okay. I'm doing it in the next query. The same query I will write but instead of going for all column only particular column I will be going for. Okay. So check here. I am going for from the customer table from the customer table from the columns I'm going for customer number I'm going for customer name okay and from the orders table I'm going for I'll put a comma here I can select order number order date and let me go for ships status. Okay. Now the thing is I'm going for selecting particular column. I'm going for selecting particular column. But here as well I it will throw it will throw an error here. Why? Because of this customer number. Customer number is common in both the table. Do you agree that is a common column here? Right? Customer number is a common column. So it will be ambiguous the DBMS right it will not understand okay you are asking about the customer number of the orders table or you are asking the customer number of the uh customer table to break that ambiguity you have to mention C you have to mention C so basically I'm referring to that this customer number belongs to the customer table okay and if I do this see I will be getting the exact output but here I'm only fetching specific columns I am only fetching specific columns 326 rows only are you getting it I'm sharing it in the on your doc file please check it Sharing the doc file again even if you mention here okay even if you're confused why she has mentioned this C you can also mention here as well that customer name belongs to your this is also correct. Okay, you can use the dot operator and you can mention that see the customer name belongs to the customer table. Okay, order number belongs to the order table. Order date belongs to the this is also correct. If you are confused then you are specifying it that this belongs to the which table? The columns belongs to which table. You will be getting the same output. Do we need to type C dot for customers name as well as not compulsory but if you wish to do you can go for it only for customer number it is compulsory because it will not understand whether you are talking about the orders orders customer number or the uh you know customer tables customer number others it is just not compulsory okay not share this table also. Which table should I share? I could not join the classic models. Okay. Now did you get it? Now are you able to understand? If you wish to go for specific column, you can mention the table name and then you can mention here for the customer name it is not required. You can skip that as well. But for the common column, you have to specifically mention that this common column that common column belongs to which table. Okay? Otherwise, it will be ambiguous. Otherwise, it will be ambiguous. Order name and order number is not from the C. You can check the columns here present in the orders table. You can check the columns. What are the columns are present in the orders table from here. Okay, expand it and you will be able to check it. That is different. Anjeli the order Excel file is just for our understanding. Where did the code mention that it belongs to the order number? Order number belongs to O. I did mention the code does not mention here. So please check what we are doing here. It is mentioning that the customer number belongs to the customer table. Customer name is belonging to the customer table. Order number belongs to the order table. Order date belongs to the order table. Ship date belongs to the order table. And even if you don't mention it, it's totally okay. Totally okay. What is the error? What is the error? Those who have understood try out this question please. Till that I'll take some errors who are getting errors. Okay, let me change this question. Instead of product and product line, I'll change the question. Try out this one. is show customers who have made payment. First do it. This one ordered by largest payment. First you do it and then you go for it. Give me a moment. Let me make it a multi-line comment. Prior this you can use the payment table and the customer table. Okay. So this time we are going to not with the orders table but the payment table. Why should we use that is the syntax okay that you you are trying to join the table using the common column value. You are trying to join the table using the common column value. Others those who have understood please try it out. So if you go for the syntax here. So this is the syntax common column equal to common column. How are you trying to combine using the common column right? So that is why we are equating that. That is why we are equating that. And why are we renaming this C sc because it is making it shorter right instead of if you don't make it short if you don't want to rename it that is also absolutely fine. But in that case every time you have to write customers dot customer number orders dot order number you have to mention the full table name. Now everyone few so check here in this column okay first you have to think okay which table you are going for so I already mentioned so are you all ready please check it out here. So two of you have already given me the query and those are absolutely correct. So please check here. Show customers who have made payments above 5,000. Okay, made payments. So you have to go for again breaking the question here. Show customers who have made payments. Show customers who have made payments. Right? Now we have to think what are the table we are going to consider. So we have the payments table. Okay. Let's go for this payment table. Like let us check out the columns. Okay. Let's go for this. Select star from payments. Okay, let's go for this. Oh, we have customer number, right? We have a customer number here. So, we can go for the as a common column. So, common column. So, one table is your customer, one table is your payments. Okay, common column. customer number. Okay. Now we will be going for the customer table. Let's check what are the columns in your customer table. Okay. So let's go for this select. I'm going for the customer number. check number amount from customers in a joint payments. payments. Okay. Payments and on customers dot customer number equal to payments. Since I have not used the allias, I have to write the full name. I have to write the full name of the table sorry this is a customer number only right so this is half question done this is my half question not the uh so we are going for this let's go for executing it so how many rows we are getting here 273 okay 273 that we are going for join here Right now who have made payment above 5,000. So along with this this is not over here. Okay. Once we have joined it we have to go for the wear clause where amount greater than 5,000 where amount greater than 5,000. Done. Then they have also asked us in the query ordered by largest payment. Okay. So ordered by largest payment. By now you are pretty much aware order by amount in descending order. Let's go for this. See we are getting the largest amount right now. And what is the total number of rows? 253. So initially we were getting 273. Now we are getting 253. Check it out please if you have any confusion. You can go for select star as well. Okay, I have gone for selecting few columns specific columns because I wanted to teach you regarding this one the dot operator. The recordings of the class will be available within 24 hours. Okay. No, for inner joint it does not matter. For inner joint it does not matter because it will be showing you the intersection part only. It will be showing you the intersection part only. The common in both the tables. Okay. So you can take the payment table first or you can take the customer table first. Anything will work. I'm pasting this. Okay. Not pasted yet. check it out if you have any confusion here. You can go for select star as well. Okay, select star is quite easy. Select star is quite easy. But only thing is that you will be having the common column appear twice. Okay, you can go for this as well. Not an issue. It will take all the columns. It will take all the columns. So you can go for this and this is quite easy, right? But here the customer number will appear twice. The customer number column, right, will appear twice. That's the only difference. So you can go for anything. Check it out if you're able to understand or not. Otherwise, we'll go for the next example. Shall we proceed? Are you able to follow? It is not removing that right. It is not remove the column. It even it because we are going for this customer number is appearing in your uh payments table also and customer number is appearing in your uh customers sorry yeah customers table also. So it is adding that the common value. So that is why when we add the select star right if you check here let me show you here. Okay, it is give me a moment. It is 141, right? It is 141. So if you check here, here also you are getting 141. Only for the common rows, only for the values of the common rows you are getting it. When we go for the inner join, what is the error? Are you getting shreddha? You don't have to remember the column name. No one remembers the column name. Okay. What you can do? You can check it from here. You can check it from here. The columns. What are the columns are there? You should know how to apply the joints. You should know how to apply the joints. How to fetch the information? That is what we have to do as an analyst. Please remove the between the okay you have the customer's number right is a customer's table not the customer select customers s is there right? Yes. After this one, s remove the space between the dot. We never apply a space. Yes. Customer name, check number, amount. Never give a comm. So, similarly, check number also you have a comma there. Space there. Yes. Customers payments again. Payments and you have after full stop you have given space here. Yes. Let's now go for this execute. Classic models does not exist. Give me a moment. Why your classic models does not exist? Okay. Okay. That is not the case. Table the classic model does not exist. Why should you get this? Okay. Can you click on the tables please? Expand the tables. Please expand it. You have the customer. Can you go for clicking the column number from Okay. Wait wait wait wait. Customers dot customer number equal to payments dot customer number. Okay. Can you go for select star? Did the other queries work for you? Shha table classic models. Where did you use table classic models? Okay, there is a spelling mistake in the see common column name as well. Customer number equal to customer numbers. You have given line number 68. Line number 68. Customer number equal to payments dot customer number. Payments dot customer number. Check your column names please properly. Customer it is not both the columns are same column right? How it can be numbers? Customer numbers. Check your customer. Go to the customer table. Please go to the customer table. Where is your customer table under classic models? Yes, expand that. Expand that. What is your customer number here? Yeah, there is no S, right? So, in the payments table, you have given an S. Remove that. Here also, you have added an S. Remove the S from the customer number. Remove the s here also. Now you try it out. Let's check. What is customer number? Customer. Uh Sha, can you please copy paste it from the doc file please? It will save much time. Can you copy paste in the doc file? The doc file I have shared. Yeah. Yeah. Can you copy paste it from here? Entire code. Entire code. No, remove your code and entire code. You can go for right. Number two, not this one. 229 to 234. Yes. Copy it. Paste it. Do not remove it. Put it in the next. Okay. Here. Go for this here. Okay. Enter. Paste it. Paste it. Control V. Control V. Okay. Now, execute. You're getting it. So what is the issue is that with the code okay your code not the classic models check it out now verify what is the error you are having your customer number column is wrong so everyone I am also sharing the code in the code file right you should learn to troubleshoot it where I'm going wrong so it is customer number not numbers right you have to do No order by is basically sorting it right. How can you go for it will give the if when you are going for sorting for single column right it is just sorting according to that when we go for multiple columns you go for comma for that but it will be first giving the priority to the first column only sorting. Okay. So if you wish to go for the employees and the office location the first thing you have to go for thinking okay what is the common column between the employees and the office location right let's go for select star from employees employee number last name first name extension email office code reports to job title. Right now we are going for select staff from offices. Let's check it out. So we have 23 rows. Office code is a common column here. Right now they want employees and their office location. So simple query would be employees in join offices. Okay, go for on and then the common code that is employees dot office code. Do not make a spelling mistake here. Equal to offices dot office code right that is a common query inner join. So if I do that see of