SQL Full Course 2026 | SQL Tutorial For Beginners | SQL Basics To Advanced Full Course | Simplilearn

Key Takeaways

[music] Hey there, welcome to SQL full course by simply learn. Imagine you have huge pile of data. Now imagine trying to organize it all using just an Excel sheets. Sounds like a nightmare, right? And that's where SQL comes in. It's the tool that helps you manage and organize massive amounts of data easily without even crashing your system. In this course, you will go from the basics to advanced SQL techniques, all while learning how to interact with databases like a pro. Whether you're looking to organize customer data, analyze sales figure, or work with complex data sets, SQL will help you do it all. Here's a quick look at what we'll cover. First, we'll start with the basics. What databases are, how to set up your environment, and how to write your first queries. You'll be up and running with SQL right from the start. Next, we'll dive into how to retrieve data using select and sort it using ordered by. We'll also teach you how to filter results and perform calculations with aggregate functions like sum and average. As we move forward, we'll introduce joins. This is where the things get really interesting. You'll learn how to combine data from different tables using inner and right join. We'll also cover how to create your own tables, define data types, and ensure your data stays accurate using primary keys and constraints. Finally, we'll wrap it up with how to control transactions, which is savings and undoing changes, and even how to modify your data with commands like insert and update. By the end of this course, you'll be able to manage and manipulate data, build powerful queries, and work with databases like a true expert. Let's get started to unlock the power of SQL. Before we dive in, let's see how much you've already known. What does SQL stands for? Is it structured query language, simple question language, sequential query logic or is it standard query locator? Drop your answers in the comment section below and let's get started. 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 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, right? Our qualification, qualification, we have our address and so on and so forth. Okay. So, these are the we have the bank data 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 data like on if I talk about storing this data right we can still store this data in the excel files 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 individual's 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 uh 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 lakh 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 flip card 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 okay 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-oh. 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 this 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. Okay. 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. 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 will be passing to the database okay it will taking up the query and then this DBMS will be deciding now 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 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 it's 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 right like the thing is that permission who which request should I process first is by is basically decided by the DBMS it's decided by the DBMS okay are you getting my point so there is no access privileges needed to connect to the okay that access privilege that comes later Okay, that is said set by the admin team. We'll discuss about later. Okay, but 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. 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 DBMS 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 uh okay let me go for this scroll down let us understand this database then we have uh your DBMS okay understand here library 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 the 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 Anand manages a request documents information uh how of how is it taking the books etc. Yes. 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 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 A CPU memory. CPU me storage access. Storage access. Okay. So this is your server. Okay. The server should be uh so in the server only we have to connect to run the DBMS. Yes. Any confusion here? Are you able to understand now? Okay, is there a confusion or anyone having confusion? Okay, great. Okay, 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 out 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. 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 customers 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 b 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? Yes, there are 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. 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 Postgray 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 little bit different but the query right the query you write the SQL queries are exactly the same. Okay. These are all the MySQL. 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 are being 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 hap 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 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 MySQL. Okay. All right. So now we are going it is not outdated. It is not outdated. Is still in use. 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 management system okay where your tables are uh stored right where data 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, Gandhar. 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. So, we 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're 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. 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, uh 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 my SQL installation for Mac. Please go through the video. Please go through the video. Am I clear? Anjali Malvika please watch the video I have shared it's a hardly 3 minutes video okay yes 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 okay now if you click on the link okay if you click 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 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. Nicl. 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. The for the Windows user you have to go for window uh sorry my SQL 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. Are you done downloading? Are you all down done downloading? Just a small file. Anjeli and Malvika, please watch that video. Okay? Please follow the steps uh provided in the video. Okay? Or if you are not able to follow for today's uh class, you can go with the lab as well. That also works out. Okay? it it is in the LMS. Uh Punam I mentioned I don't think we can do it in the uh iPad. Okay. You don't have to go for an opening and please listen to my instructions properly. I didn't ask you to open an Oracle account. You have to go for no thanks. Start my download. You don't have to go get an Oracle account here. Am I clear? Pavia, did you get it? No thanks. Just start my download. No, there is no way of downloading the lectures. Okay. Recordings that is not possible. I believe that is not possible. The you can also you can only watch the recordings. I can't find the video ma'am. Uh Mahalika, are you aware of the LMS? Are you aware of the LMS? Aunam simply learn for what? Okay, great. Okay, those who have downloaded it, those who have downloaded it, uh my screen might not be so much visible. 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 Bhavya, 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 [clears throat] 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. Anyone willing to volunteer? I can help with the uh if anyone can just go for the screen share. Can anyone Okay, Chandana is there. So, Chandana, please wait. I'll be taking up your screen right now. Yeah, Chandana, can you please share your screen? 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? Tend can yeah okay go to the application applications plus plus please click on that again mysql workbench MySQL workbench plus okay again myql 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. Okay. Now go for you are getting this. Please go for execute. Uh that is not possible. Okay. Dana Lakshmi and Puja. What I'll do I'll take I will show you from the slide. I'll show you from the slide because uh she is already downloading it right now. It's not possible for her as well. So uh Chand 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. My SQL 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. Am I clear that I am repeatedly mentioning custom? Are you able to understand that it is custom? Puja and uh Dhalaki. Is it clear? Puja Dhan Lashmi is it clear both of you? Bhavya is it clear? Okay, 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 will go for the lab. Malvika, I believe you are going for uh Mac, right? Uh Mac will not be doing it because I am not using it. So it would be better that if you follow the steps in the mentioned in the video MySQL server and MySQL workbench are you there? Okay, please share your screen. My SQL below is sliding. 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. Bhavy I'm muting you. Unmuting you. Okay. So let me know what is the issue you are having. I'm not getting it. Bhavy what is the issue? You can unmute yourself. Uh yes ma'am uh I'm unable to install this uh this options are not coming. Uh can you share my screen? >> Okay you can unshare your screen. Yeah for now let it install. Turn you can unshare your screen. Let me take yeah you can share your screen now. >> Yes ma'am. This one did you choose for this? Can you just cancel this please? Cancel this. Cancel it out. Fully cancelled. 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. >> One second. >> So, we'll not be taking too much time on the u installation part. Which one you have downloaded it? >> Yeah. One second. I'm going to 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 this 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 It's a 2.1 amps. 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. Cha, are you there? Can you please share your screen? Jana Yogandra 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. Yogend. What is the issue? Please speak to me. You can unmute yourself. Okay. Hello. Ma'am. >> Yeah. >> Actually, what instruction you have given? I have followed the same but uh still I'm not able to install the actual what I'm trying to >> I'm not getting the same are you not getting the same option like Chandana I have 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 Jana please unare your screen. Sorry, I'm not doing that repeatedly. Can you share you can you share your screen please? >> Okay, it will take some time for you. Okay, because uh that workbench installation does take some time for everyone also. You can you can share your entire screen. Go for sharing your entire screen. 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. I'm Yeah. Not this one. Not this one. Your zoom 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 lifeclass 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, I 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 task bar, please. Yes, this one. Okay, now it was correct. That was the screen, right? Why are you doing it? It was correct. 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. 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 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? Okay. 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 sai 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 Jodica 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 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. Reset. 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. 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. Okay. Thanks. S. So, why did I show you here? Those who are not getting it, not to worry. You can watch the recording. How to do it. Okay. How to do it? You can watch the recording. So now I'm going to show you the lab. So please focus on the lab. Those who are not getting also not to worry. Okay, you can just maybe do it tomorrow. Let me go for give me a moment. Let me share my screen again. So we don't want to uh you know waste lots of time in the installation because we have to go for the hands-on coding as well. All right. So those who are not able to install it, not to worry. You can take your time and do it tomorrow also. Why I'm asking you? Because it's always uh better to uh go with the offline version. Okay. So everyone have your uh LMS right details the user ID and the password. You have to log in there. 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. Pavia, you can watch the recording later. Okay. Or if possible, I will also share this uh PPT with you all. Okay. Now please check just for the uh 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 lab. Launch lab. Two two times. Okay. Not to Not an issue. Okay, man. See, it's okay because I'm launching the right lab right now. So, I will not be able to show you. Please launch the lab twice. You have to click on launch. It's okay. Uh it will take some time. It will take some time. By that time launch the lab and we'll work on the lab. We'll start working on the lab. Yeah. For first lab launch. No, every time for launching the lab it's take it is it will take some time. Okay. So go for the offline version. Not to worry from tomorrow onwards people will be ready. I know that or if you're working with the lab make sure that you before the class starts right you launch the lab 10 minutes before 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 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 will 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 a 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, 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. 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 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. Yes. Are you able to get it? Okay. Gori, please resize your screen sometime. Depends. Okay. 15 to 20 minutes. GI, please resize your screen. Your browser. Your browser screen. that that might be the reason that you are not able to see it. Okay, I have resized my screen. So I have resized to 75%. See you can do this one also but I will not be able to show you here. So let me go for 75% again. All of you are you able to get the lab at least? Are you able to get the lab? Okay. Uh if 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. You 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 s 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 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 Wet. So, wanket please click on the schemas. Okay, you are currently on the administration. Click on the schema. Sha, what is the issue? What is the issue? Unable to locate administrator. Are you working on the lab or are you working on the u are you working on the lab or are you working on the offline system lab? 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 not also able to view it. Now if I go for minimizing the screen little bit reducing the screen size I'm able to get that okay offline system Shha okay Shreddha have you have you installed it properly have you I'm quickly unmuting you as well give me a moment Sha, have you installed it properly? Can you please unmute yourself? >> Yes. Yes, I can install it. >> Okay. >> Can I share my screen to you? >> Give me a moment. First, you check in the start button. The workbench, please. Right here. Are you able to see the workbench? In the start button, are you able to get the workbench? Wait. Yes. Yes. Okay. Can you open it from there? >> Yes. Okay. Is it working? Can I share my screen? Can you check from your side for once? Share your screen, please. Please wait. It is showing like this. >> Yeah, that's okay. So, you have the three screen. Okay, that is okay. You are getting it right now, right? It's okay. Completely okay. >> But it is showing the SQL file too. I'm not I'm not clicking anywhere. But >> that's okay. That's because of the three lines you have there. Right. On your right, you have on your right screen above. You can check. Yes, that is because of that you are getting it. It's okay. You can click on this. You're getting it. See? >> Okay. >> Can you script please? Yes. Pick a script. >> So, I'm also on mute. >> Where is script? >> I'll show you. You can I'll share your screen. So, Shha, please look here. You have the script here. SQL this one. So please click on that you will be getting the script. Okay. Now everyone shall we proceed? At least we can create the database. Okay. Few things. Can we proceed? Yes. All right. So please understand one thing. If I want to give me a moment let me check it out. Okay. So if I want to go for writing any comments 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 /ash 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 because 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 are 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 feeb 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. First write it. I will be taking a doc file. I'll be sharing anything 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. It is 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. 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 thunderike 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 sorry, Nikl, 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 fab here. 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. 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 doubleclick 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 fab. 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. 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 fee. 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. 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 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 I should have taken up to a new screen only okay I'll just increase 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, next, 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. 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 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 fair by writing this create database SQL fab. Have you refreshed it? Have you refreshed it? After creating it, have you refreshed 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 table. 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. >> [snorts] >> Kindly confirm. Are you all done with this till this part? Shall we proceed? Because two three people have mentioned. Yes. What about others? Are you all able to follow? Great. 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 now? So either you can bring 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 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 fac facing issue with the lab I just want you people to connect with the LS. 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. Okay. So, you have to display anything. Just give me a moment. Meaning previous I was able to see the lessons. Now I don't see anything. Give me a moment. N can you please unmute yourself? Others please wait. What is the issue? N can you please unmute yourself regarding the lab or anything if you are facing the issue please directly ping. Yes. >> Yes. So sorry [clears throat] u so I relaunched it and then I can see the database that I have created >> right. So now uh when I tried doing the right click on the database and when I try to see the table that you have uh you know previously shown uh we've seen the unlock >> have you sorry we've seen the >> have you imported it >> have you imported the table share your screen please I think yeah sure just give me one moment I'll share the others please wait. Okay, just give me some time. Please let me know once you're able to see the screen. >> Yeah, I can see your screen or the correct one. Go to your lab, please. >> Workbench. >> Okay, work. Yeah, here is the workbench. >> Okay. Okay. You are in the administration right now. You're in the administration. Go to the schemas. >> Yeah. Yeah. So now >> Okay. You have not imported the table. So just go for right click. Right click and then go. No, no, no, no. Right click. >> Click on the SQL f. Yes. >> Okay. Transport. >> Yes. Uh I would request you just I'll show you on my screen. Okay. Because others flow will get hampered. I hope you >> Yeah. Yeah. Please do it. >> Okay. Yeah. >> I I'll just stop it. Yeah. I'll stop the screen. >> Yes. So I hope you people please understand that we have lots of people. Okay. So do not to worry. So just uh today's class went slow. I do understand for that. Okay. No issues. We are going to learn. So u for others. Okay. So please we have some patience as you know most of them are beginners. So it will take some time. Okay. Now those who are unable to load the table, so please I'm going to show you once more. Right click here wherever you are. Okay. You have to go for table data import wizard. Okay. There you have to browse to the file where your data set is present. If you are working on the lab, you have to go right. You have to go to just give me a moment. You have to go to the path that is let me paste it somewhere. I will not be able to though because it's a virtual one. I don't think I will be able to paste it. Able to paste it. So basically that file is present under your desktop. Okay. So go to that browse option. Okay. Browse option. You will be getting the desktop. There you have the data sets. Go to the data sets. Go to the assisted practice data sets. lesson five. Then you have the EMP table. You can take a screenshot of this. You can take a screenshot of this. Okay. And just go for clicking. Once you get this, you have to go for open and click on next, next, next, next, next. Finish. Okay. I hope you understood. Just take a screenshot of this desktop data set assisted practice data sets lesson five. You have the MP table. the same table I have shared you in your chat box or in your LMS as well. Okay. Please uh share me the screenshots only when you have any error there. Okay? Otherwise it is too confusing for me if I go for uh checking each of the screenshots. So everyone please notice here focus on my screen. 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. N 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 u 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? You have to give uh Raki. Okay. Semicolon. Please do not forget the semicolon. So why are you getting an error? Give me a screenshot please. Raki see please mention that select is a statement. Okay. So always after select you need a space. Then you have to give an asteric space from space. Emp table always end it with a semicolon. Always end it with a semicolon. Click on next, next, next, next, next and finish. Nine. Once you get it, you have to go for you. Once you click on the open, right, you have to go for next, next, next next and finish it. Uh yeah, you can give me a screenshot, please. What error are you getting? Or you can I'm just pasting the code. We have though we haven't written any code yet right now. Just give me a moment. Sharing the code in the code file. Check it out. Please write it from the code file. What is the error you are getting? No, it is it is not case sensitive. So it will not it will not hamper anything. Okay. So even if you write capital EMP not to worry. 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 well. You have to just click on next. You don't have to do anything. Just go for next. Cancel this and go for next. Doika. all of you. Okay. 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 What if are you all done till this part? Role here is basically the designation. Role here is a designation here. Okay. So you can see here the role as a senior data scientist, associate data scientist etc. Check. Shall we proceed? No. No. After comma, we have role. After that, we don't have any. We have space here, right? Because these are the columns, right? These are the column names. Then go for drop go for drop. Click on that. If it exists then drop it. 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 we 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 okay get 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. Give me a moment. I tried with Give me a moment. Yeah, Jotica please share your screen please. Please share your screen. Others please wait. Can you please share your screen? Can you please refresh it? it. No, first refresh it. How do you refresh it? On top you have the Yes, refresh it. You already have the table. So that's why you are not getting it. Click somewhere else. Click outside. Remove this. Yeah, you already have the tables. Click on the tables, please. See, you are already having this table. Okay, the name of the table is different. So if you are not able to select the name of the table, I'll show you how to do. So I'm just I'm sharing your screen right now. Let me share my screen. So if you're not able to uh share your if you're not able to go for the table name, right, you can also doubleclick the name of the table here as well or you can copy it from here as well. That also works, right? You can just click here and you'll be able to select that. Yes, double click on that. Okay. Shall we proceed? Are you all done? Shall we proceed? Okay. Select star. No, no, no. The code is wrong. 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. You don't go for like this because already already you will be having this already you are having emp because you are going for selecting all columns. So please check here what will happen in this case you are getting empice. 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 Royal Collins 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 want to limit three rows. Okay. So here we have till we have till Katrina here. Okay. But the thing is that I want to skip this. 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 four 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 after Hey 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 like I can see 99 participants are there or 8 n people have already 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 3 4 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. Yeah. Yes, Dani 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. It start at positions. The position starts from zeroth 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? Low 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 Zero is the first row. N zero is the first row. That is your Roy I believe. Right. 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 this it is basically sorting in ascending order. Okay sorting in ascending order. If you want it by the highest salary you have to mention DC. 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 zeroth place, zeroth index, right? The first name is stored at the zero index. Now please check here if I write here limit starting from the zerooth index I want only two rows. Starting from the zerooth index I want only two rows. If I execute it check. Got it. None can leave but please share your feedbacks. No no no no we have to save it. If you are working in the offline you it will be there. It will be there. Okay. But if you are working in the lab your lab will be reset after 5 hours. So it will not be there. Even your database will be uh not present. Okay. Yes, the class recordings will be present to you like it will be available within 24 hours. Can you share the give me give back form will go. I think the feedback link form I think the LMS will LSM will share with you. Just give me a moment. Please share the B note. Okay. So I will be sharing uploading that in your LMS. Okay. The file no for lab you cannot save it. Lab will be reset after 5 hours. Okay. So you cannot do anything. Ma'am uh once this session uploads on the LMS would it be possible to catch up with the installation etc. Once this session up Yeah. Yeah, you can go for the you can watch the recording there and you can follow the installation there. That's why I have shar I have taken up your screen and shown you the installation right it's pretty simple there and also I will be uh and I I will be also sharing the PPT which I have I'll be sharing there what is the password for the SQL lab SQL server so columns sorry the for the columns you have the option for the comma right so if you want specific columns if you want specific column columns. You can go for that. That's it. Execute. You have only two columns. Server like Ashish, you are asking me regarding the lab or anything. Yes, if you are working on the lab, please make sure tomorrow if you are working on the lab, uh you have launched your lab 10 minutes before the class. Okay? And if you are working on the lab, the password for the lab, just give me a moment. You will be getting it in the three dots. Okay. So you have the three dots Ashish. You can check it here. Here. Okay. You can just drag it. You will be able to see the password here. Okay. You will be able to see the password here. Check. Password dot.1 then two exclamations. Another way of selecting the columns. You can also select the columns like this. But you have to use a comma here. Okay. Select then write. Go to the table. Expand it. Expand the columns. Emp ID. Double click. Comma. First name. Double click. Last name. This way also you can do it. Till not install the lab. What is offline lab? Offline lab is a workbench we have installed. I have shared the link. Right. So that is the one. So you can watch the recording and install the software that is the workbench software. Okay, you have to install it or if you are not able to do it, we can work with the lab. But uh in the lab, you will not be able to save it. Okay, whatever we have done so far right in this file, I will be sharing that in your LMS. Okay, I will be compressing the file and I'll be sharing it in your LMS. So if someone wish to sh uh save this you can save it from here right you can save it from here. So you can just click on the save button you will be able to get it. So let me write web SQL. Okay you are able to save the file but in the lab you won't be able to do it. Q 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 wear 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? Cuz 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 that 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 columns. 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 dpt 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. Bya are you all able to understand any confusion here? Give me a moment Paul. Okay. Give me a moment. Hello ma'am. I have a problem while connecting with the uh lab SQL ser uh lab SQL server while entering the password 1 2 3. It's access did not show me the warning issue if you say. So I will share the okay. Muhammad Asan what is the issue is that for lab it is not 1 2 3 4 the lab you have the password whatever you have the password just give me a moment for the lab the password is this one password dot one then you have exclamation exclamation 1 2 3 is basically the password what we have set for our offline system you can set some others as Where can I find the word file? Okay, I I'll share the link. It is also present in your LMS. Uh NL can you give me a screenshot please? What is the error you are getting? Okay, let me copy paste it in your doc file as well. Let let me copy paste it in your doc file as well. Just give me a moment. Whenever you are having any issue, you are getting an error. First thing you have to do, you have to share me in the screenshot. Yeah, I have shared that. Just give me a moment. Let me make it. It's now in bold. Yeah, check it out. What is the error you are getting? Okay, we do. So, let me check it out. Select star from error code table says does not exist. Okay. Have you imported the table? Have you imported the table here? Emp table. Okay. You have two tables here. Emp table and this one. Okay. Your database is not selected. Pu please check here you have to select the database so your SQL_FB right is not selected please double click on that your SIS is selected for you your SIS database is selected make sure you select this database the current database you are working with is it clear double click on that double click on that pu clear are you able to understand your CIS is selected you have to select the SQL self. Okay, give me a moment. Okay, Nichls. Okay, spelling mistake is there. Your It is this department you have mentioned depth. Okay, it is DP. It is D P. You have mentioned DEBT. Okay. Nik double click. Double click on it. Any database you want to select, double click on that. You will be able to select it. Okay. Just go for double clicking on that. So I'm working on the SQL fab. I will be selecting that. Double click. Or you can also write use. You can also write use. Then the database name works and you have to execute it. Those who are done, please try out this one. Show me the details of employees. Okay. Employee earning earning salary more than 5,000. Those who have understood this, you can able you will be able to do this as well. Great read ma'am in that word file have to write 11 commands. No, no, only two commands, Bavia. Only two commands. You have to create the database. You have to use the database. Then go for importing the table. Go for importing the table for now. Have you imported the table? Great. Chana, have you imported the table? CSV file. CSV file. I'm sharing it here again. Empt CSV. Just give me a moment. Okay, Imm has also shared. Thanks, Imam. Okay. Okay, I imam. Okay, your table name is one. Bab, I've shared the CSV file. Please check it out. Okay. Yes, those who have done it absolutely correct. So, please check 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 go for that we will be getting around 14 rows. We will be able to get 14 rows table import is really simple. So it's a request for everyone. Okay. So those who have missed the session, you have attended the half session, please go and watch the recording. 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 two 14 I believe whe check you 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 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 you shouldn't get check it properly please it will be 14 check it properly is how many of you are able to do till this part. Give me a moment. I have just now show you Ashish. Just now I have went for that table data import wizard. Check. You have to just browse to the location for you. The location might be your uh downloads if you have downloaded it. Now the EMP table and just go for next next. That's it. Okay, great. 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. You are getting it in descending order location shows me your system lab. Others please wait. Ashish please unmute yourself. What is the issue? You are working on the offline system right Ashish? Uh >> yes ma'am >> you are working on the uh offline system right workbench you have downloaded right >> no I'm using system lab >> system lab means >> can I >> simply learn lab >> no I'm using yeah learn lab yeah >> okay >> screen share my screen >> no it's not required I'll show you so please here also you have under the so one thing have you created the database >> yes ma'am I have already created but I try to import the table >> but uh I can >> table. So fetch the location is basically your see browse I you told me that I thought you were working on the workbench uh desktop data sets desktop data sets >> data set >> double click assisted practice data sets >> okay >> lesson five emp this is the one and you have to go for open you have to go for open and then next next that's it >> okay yeah thank you Yeah. So here comes first. Where comes first? Yes. After then order by Yeah. And last is the limit. Yeah. I will discuss the entire structure. Don't worry about it. 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 like to carefully 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. 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 Chandana. 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, five 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, you went per order by that's also correct. Great. Those who have imported the table are lagging behind but you have imported the table. You don't have to go for the other query. Wherever we are right now, please focus on that. Okay. Okay. Now let me try out few more things. Are you all done till this part? Okay. Great. So let's continue then. 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 end. Using this end about the commands we should use to ask. Kindly please explain about the commands we should use to ask and reply the question related to the table. That is the thing we are doing right. We are asking the query right? This is the select clause. Select if you wish to display anything regard from the table you have to use the select display. Yes. What is the issue? like you are not getting it. We are trying to fetch the query from the database only. Right? So this is the one if I win to if I wish to get the employee details working in the healthcare department and having uh experience greater than equal to 5 years. This is the command for that. But commands are different. Seven star. Sorry. Commands are different. Seven star. I didn't get you. Okay. This is asteric. So this was discussed in the previous session. If you wish to go for display all okay Danisha I'm just going for unmuting you so that I'm able to understand. >> Yeah. What is the issue Danisha that you are having right now? >> Uh sorry I'm just uh um watching uh you are typing the questions and asking for the answers. when you do the command you do the select star and every time is change but there I can see there there is a backslash and then star uh and then >> that is a comment this is the comment are you talking about this one so this is this is called the multi-line command so if I don't write this okay if I don't include this see I'll get an error here because that's the statement see so this is a code editor this is a code editor so you can only go for comment lines Right? This is a comment multi-line comment. >> So it means in the session we will also be taught like on which question we should use which command. >> Yeah. That is what I'm going for. Yeah. Yeah. This is I have already discussed that in yesterday's session. So what happens is that when we go for multi-line commands right like this. >> So we are displaying the statements right? This is a question. So we have to go for this symbol. Okay. So you can see here it is starting here it is ending here. But when we have single line so you can go for this. So now what we are trying to do we are just going to display the information. If we wish to go for display any information we have to write this select statement. Now I'm not fetching uh any column. I'm going for all the details. Right? So I'm going for asteric. Asteric means all columns. Okay. So this were discussed in yesterday's session itself. And also one more question uh when you are typing all the questions uh like here on 27 28 uh continually you write and 29th you left blank >> and then >> uh so we it's started like after every single question or >> okay so this is because see this is an uh this is a question this is a question this is the code for that this is the code for that I can type and enter so My query is ending here. The code is ending here. Right? So what I can do? I can just type an enter here and just I can start the next question. So this is a comment I'm giving. I mean giving a comment. Just a comment what I'm doing what I wanted. >> Okay. Okay. >> Okay. Got it. Thank you. Thanks. >> Give me a moment. I'm Can you help me? Ashish, why are you facing the table import? Wankit give me what is the issue you are facing in the table import. Okay, great. You have shown me the output wet if I go for the lab. Others please wait. Okay. So, okay. Let just I'll help you out. Ash, don't worry. Okay. Others, are you all done with this part? Are you all done with this part? Let me share that. So what is happening is that few of you are moving ahead and you are able to follow up the pace. Those who have missed yesterday right. So what is the happening here? I have to again switch back. So there is a kind of break in the flow. So I'm now scared that whether you are able to understand or not. Okay. So those who have understood it, those who don't have any confusion, a question is for them. A question is for them. Okay? Try out this one. Try out this question. Okay. Uh let's go for this. Show me the details of employees. Okay. Employees working as either senior data scientist or having salary greater than 10,000. So I'm sharing this question. Let me help out Ashish. I'll be sharing my screen. Just give me a moment. So others try out this question. Others please try out this question. Ashish, please check here. What you need to do? I don't know why are you not getting. First you have to import the database. 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. Jotica, please check it out. Okay. Show me the details of employees working as e 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 N. Nowhere they have mentioned as N. 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 amperson as the and. Okay. So these are the operators we go for. But we can go for and as well. 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 percent 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 roles 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 or 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 fetch 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 or operator only. This is the or 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. Shall we proceed? No, not now. No break now. Okay. We generally have the break after 1.5 hours. Oh, sorry. Yeah. 1 and a half hours. So that is 9:30 IST. Give me a moment. Okay, Abhilash, you are getting four rows. What is the issue here? Uh-uh. 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 so I if you correct it then you will be getting nine rows. Okay Vash. 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 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 a 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 end 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 you 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 you maybe you can paste your code. Maybe you can paste your code in the chat. Wangit give me a screenshot that also works out. 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. Wet check it properly as well. Okay. 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. 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. T table name SQL is not case sensitive. Okay. But lab is case sensitive especially for your table name. You are getting 12 only not 15. Okay Wenet 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 to 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 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. 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 end. Okay. I'm showing you for and operator. This is the logic get what is the output you have. So first we'll go for 0 0 1 0. Sorry. Give me a moment. Okay. It's okay. Still okay. 0 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 condition is false then only 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 mention 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 quote that is also works. Okay. Single code double quote same thing. If and if I write like this then is also okay. India and I mentioned regarding China 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. 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. Just 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 2,800. What is the average salary? It is 7,815. Not done, ma'am. It's okay. Not done. I just want to show you if you wish to go for uh 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 s. 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 There 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 empid Okay. Let's see if it works out. Get in. So if you wished for to get this is for Anali. 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. 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 Jotica 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 name. 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 Dear data scientist, what do you think? Why it is appearing as max? Why is it appearing as max? Because it is a first value. It is a first value, not the character length. It is not taking the length into consideration. Okay. Okay. See these 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? 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 dep 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 all 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 Yes. Max salary for each department. Max salary for each department. sharing the query. You can try it out. Please don't go for annotation. Don't annotate. Let me go for stopping the annotation. Okay, I have to go for on sharing. Group by used only one row just for summary. Yes, you are able to group the data into a single uh row. You are able to group the data into a single row. Can you retrieve the role wise? Roll wise average salary. Try to go for this role wise 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 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. Yeah, that will work. Okay, man. Yes, I'll do. I'll do. Don't worry about it. I'll give more queries. Okay. 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 uh retrieve the country wise sorry continentwise 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 whe the 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 kita give me a moment. Select ro average salary from group by role. Select ro average salary as from 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 Red Bat great manny 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 it's coming three 1 2 3 what is the highest rating 3 1 4 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 there whose salary let me go for whose how many working? Okay. Department can simply go for half of all is two. Check here more than five condition where department what is we when do we individual salary we will be having please check it out here then I'll show you one more query answer of the department how many employees like employees are working salary Here you are not going to apply filter the group rows. First select from EM 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 account for it. Okay, in this case when we are talking filtering about based than if I go for here we are is greater than clause is always written after group by on the group data you are apply this one go for you will not salary here is basically when you where when the individual roles but if I come to okay in salary 8,000 average salary That means right. So first it mentioned average salary the average 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 write 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 p right p 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 EM 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. 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 fourth. 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 please check here if you're 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 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 output is there. Okay. Now what they have wanted us in the question having countries more than two emp rating okay greater than equal to four. Okay I am 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 have 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 so few of you have already shared me the answer exactly so this are the homework I have given to you right otherwise what happens it will only give you the it will only the code you have written it's correct but you will not be able to get the count right how many people have uh are getting employee rating above uh two sorry above four we are not able to get it for you you are only getting Canada I believe are you getting my point Nikl 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. 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 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 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 asteric 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 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. Why should be emp rating? Is it be EMP rating? Because we can go for if I talk about the EMP rating can't we take the EMP ID because what is the question that people are working there right that people are working there right man? What is the issue if you go for EMP rating and EMP country Bhavia we have all the things we have discussed in yesterday's session itself it is just kind of we are going for the revision I have given a homework and then we are discussing it. We have not even started with the today's session. Okay. The topics we haven't started. These are all the aggregate functions. These are all the aggregate functions. 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 codes 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 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 the it starts with the alphabet D so you can see here D 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. Are you able to understand what is the use of the like operator 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. My SQL 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. Pavia, did you go for entering a semicolon in the previous statement? In the previous statement, uh, have you given a semicolon when you are entering it? Give me a screenshot. Give me a screenshot. Yes. Great. Many Yeah, change it please. I request everyone. Okay, so those who are beginners, absolutely beginners starting with the SQL. Okay, so the thing is that okay, I come from a technical background. Okay, so what happens is that I found find SQL very easy. When I started my SQL journey, it was really fun for me because I started my complete coding and all those things with the C programming, C++, Java and all those things. So I found it tough but when it comes to your SQL it was truly simple for me because it's exactly similar to English language the way we speak also you can check the syntax right so it was simp simple for me so I was coming from the coding background so it became easier for me but if you are coming from non-coding background ultimately it's a coding language so it will be difficult for you I do understand so what you have to do you have to doubly work hard okay If I give 1 hour to this coding language, you have to give 2 hours. But after the session as well, you have to work. You have to watch the session again. Okay? Go for repeating on repeatation mode and just go for uh practicing as much as you can. Do it repeatedly and practice on your own. So I missed out this. Okay? Then only you will be able to learn it. Okay, Anand in that case if you write the query like this. Okay, if you write the query like this. Okay, so that means that means that the last letter should be D. Okay, where the last letter is D. So you can see debit and charge we have the uh last letter as D. Okay, here the starting alphabet. Here is the last letter is the D. 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. 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? multiple conditions like generally the operators you have the clauses are coming in blue. Yes, these are called operators. Sri 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 you 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. Take the command. I have also shared this. Give me a moment. Don't go for equal to. Don't go for equal to here. Done. Done. Okay, I answered that. Many I haven't discussed about the length, right? 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 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 that 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 so 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. Nikil, did you get your answer? Atka, did you get your answer? Nikil, I think you can able to do the query as well. You will be able to solve the query also. Yes. Uh uh uh. What is that query? Give me a moment. No, this is not correct. Nikil, your one is not correct because as per your question, this was the thing, right? So, you have given underscore sorry percentage that means 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 SD okay let's check it out can we combine that or not okay let's go for that as lakshmi 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 needs 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 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 a percentage no that is not 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 concept 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 now let me 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 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 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 this 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 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 C006 is there but C006 is not present in your customer table. Right? So you will not be able to get it in C006. 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 does 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 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? 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. 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? O sorry 01 01 and here we have the customer. So that customer has placed two times two orders with order ID one4 and they he have placed a suppose for the product laptop and the headphones amount is 1,200 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're 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 u all the details of your order table are present here. So that's why we are getting C06 but C005 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 are 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 joint 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 join are exactly the same thing. Okay, now suppose if I tell you that is a 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 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 C05 is there right? C05 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 C006. Right? And for the right because we don't the C005, right? This person has never placed an order. So for them, for it the order 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. Okay. Full join I will be explaining you little bit later. Okay. Full join I'm going to explain you little bit later. Okay. So let's proceed with the implementation. Shall we? Shall we proceed with the implementation? Okay. So I'm sharing the sheet. If anyone wants to go through the sheet, you can go for it. I'm not going to save right now. Just I'm going to share it with you all. So if you want to spend 2 minutes of time in the sheet, you can go through it that. Okay. Anyways, I'm going to share that sheet with you all in your LMS as well. So if you feel like going through this, just give me a moment. Let me search that file. Yeah, we have. So I'm sharing the joints file with you all. The Excel sheet which I was using. If you have any confusion in the sheet, you can ask me and then we will be going for joints. Ma'am, when customer has never placed an order, okay, how can we even the data be present? So that is why including those who are mentioned, right? So we are if they if they have never placed an uh um order, right? Then we will be getting null there, right? Okay, give me a moment. Gandhar, I didn't get you. There is uh here is there only one CSV file and multiple tables or multiple CSV files. I didn't get you Gandhar. These are looking like a A union B and B. Yes, exactly the same thing. S this is exactly the same thing. You can also have multiple CSV files. Okay. In a single database, you can have multiple CSV files as well. Okay. Shall we proceed with the implementation or Okay. Okay. Now we will be going for joints, right? And for joints there we need multiple table. YC06 is showing when there is no matching because you are giving preference to the right join. Oh sorry you are giving preference to the left join left table. So in the left table it is present not in the inner joint. In the left join you are getting C06. Okay Nikl because we want to get the uh details right we want the all details of the all orders even if it was never placed. So that is why see the percent information we don't have it but still we are getting that C06. All right. So now please check here because we are going for left joint. So that is why it is missing here from the problem statement you have to understand. So that's what we are going to practice it. Okay. When to go for right join? When to go for left join? Exactly the same thing. It's just the positioning of the table. Which table you consider it. Okay, let's start the execution and then we will be again understanding it better. Yes, Asan, you can write your query. Now, please understand one thing. For joints, we will be requiring multiple tables. Okay. But we have only one table, EMP table. So, for this, we are going to use the classic models database. Okay. So, please everyone check here. I'm going to share one link with you right now. I'm going to share with a link with you all. Just give me a moment. Copy link. So please check what I have to you have to do here. You have to go to this link. Pavia. Now you focus on what I'm doing right now. Okay? Leave it. Whatever you have missed, leave it. Focus on what I'm doing right now. Please go to this classic model sheet. So whenever you miss anything due to your internet issue, you can use the AI companion for the summary. Okay. So please check here. You have this right now. Classic models. What you have to do? Click go to this link. Go for control A. Select everything. Ctrl A. Control C. Copy it. Right. Control A. Control C. If you are using the Windows machine, if you are using Mac, you know better. That is command. Right. After that, go to your SQL. Take a new script. Take a new script. This one. Take a new script and paste it. Paste it. Then go for the first thunderike icon. Execute this. this first thunderlike icon. The difference between the thunderlike icon with the I and this thunderlike icon, you will be able to get it. If you go for the thunderlike icon, the first one, it will execute all the query at once. It will execute all the query at once. Not the current execution. Just go for that. There is an issue in lab. There is no option for the grid view for the database table. Kindly you sort it out. Okay, we'll check it out. Wait, Assan, please wait. Okay, once you are done with this, once you have executed it, yes, you have created a database that is your classic models. Okay, once you have executed it, go for refreshing the schema. Go for refresh. You should be able to get the classic models like this. Go for refresh. If you have done it correctly, you will be getting the classic models. Great. Double click on the classic models. So, select the classic models because now we are going to work on the classic models. Okay. Please double click on that. Take a new script ata. Take a new script. Paste it. paste your entire doc file code that I've shared right go for control V and go for executing the lab will app uh allow you just give me a moment sorry not this one just give me a moment in the doc in the chat box only I have shared okay after that you have to go for this thunderlike icon thunder like icon. Okay. And then you refresh it. After pasting, you have to execute it. After pasting, you have to execute it. Still can't paste. Right. Let me check it out once. Okay. Sorry, not this one. Uh Raki, you go for this one. Okay. The first one. Thunderlike icon. Control V. Rakit. control + V if you're using the offline. So I'm pinging the LSM words and uh you can connect with her. Okay. Wait, can you please help? This is not the SQL fab. Moit, you are working with the classic model. You have to refresh it, you are no more working with the SQL fab. Okay, Janet. Okay, I have pinged the LSM. Janet, I have ping the LSN. You can also go for that. Everyone, please check it out. You have to go for this. After you go for the classic models, you copy it. Then you go for this one. New take a new SQL script. Paste the SQL script here. Once you have pasted, go for clicking this thunderlike icon. Then you have to go for refreshing it. You will be able to see this classic models. Okay. No shortcut. This is a shortcut only. You can go for this one. Right. What shortcut you need? You have already this one. Yes, you will be able to get the tables. Okay, Janet, please ping LSM once. Otherwise, I will be creating two tables and then uh we can work on that one. Okay. Okay, everyone. So, we'll go for a break. I cannot zoom it. This SQL screen, I cannot zoom it. Right. So this is the classic models you can check here. Okay, you have the order details, orders, payments, product lines and the products. So these are the tables that are present under the classic models. So please to use the classic models, doubleclick on this. If you are getting it bold, that means you are uh you are you will be working on it. Okay, so it is uh you are going to use it right now. Okay Janet, I'm really sorry. I think the uh learner success manager has not connected with you. So currently you watch it, okay, so that we can see few examples, okay, on the classic model. So once you download it, you will be able to paste it. So that is the problem with the lab we have. Sometime it gets pasted, sometime it doesn't. Okay, really sorry about that. So everyone shall we start? Can't see anything. Is my screen visible to you all? Okay, give me a moment. Moit, my screen is visible. Please check it out. Have you gone for refresh? Jotika, please go for refresh it. Here is your refresh. So make sure that you show uh make sure that you double click on the classic models. Now let us go for solving the problem. Okay. So let's go for the same question. Find all customers who have 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 in 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. Please check it. Uh Jikica, please repeat the process and check it out. Drop the database and repeat the process. 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 customers 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 staff from customers inner join orders on 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 all 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 are 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. So, try out this one. Just give me a moment and let me tell uh help out Jotica once. Okay. I'll take Jotica's screen right now. Uh you are This is not over. Okay. This is not over. I will also show you more. Give me a moment. Jotika, are you there? Is your query uh is your problem solved? Jotika? Jik, are you there? Okay. Is your qu problem solved? Are you able to get the tables? Okay. Others please do it from the thing from the doc file. Jikica, please share your screen please and also unmute yourself. Yeah, please share your screen quickly. Quickly share your screen please. Maximize it. Okay. Refresh it. Okay. Double click on the classic models. Double click on the classic models. Table exist. Okay. Okay. Can you go for uh can you go for deleting it and can you go for pasting it again the code? Delete the classic models. Right. Right click on the Okay. Go for Right click on the classic models. Not here. Not here. Not here. Right click on the classic models. Drop now. Yes. Click on this. Click on this. Jotika. Yes. Drop now. Drop now. Okay. Now go for the execute. The first icon. No. No. No. No. No. No. Don't drop this. Don't drop this. Cancel it out. The first icon after the save. Yes. This