[00:11] payment, place an order, log into an application, search for a product, or open a business report, data is being stored, retrieved, and updated behind how companies manage millions of those records without losing track of the [00:24] information? How does an e-commerce platform find your exact order? or how does a bank retrieve your transaction history and how does a data analyst extract only the information needed from a database containing millions of rows? [00:36] According to the US Bureau of Labor Statistics, thousands of openings for database administrators and architects are expected every year over the coming decade. And as businesses continue to build applications, analytics platforms, [00:50] cloud systems, and AI solutions, managing reliable and well structured data remains extremely important. And that is why SQL continues to be a valuable skill across roles such as data analyst, business analyst, software [01:02] developer, data engineer, database administrator and backend developer. Hey everyone and welcome to this SQL full course mastering databases, queries and real world data management by Simple Learn. In this course, we will start [01:14] with the fundamentals and gradually move towards advanced SQL concepts so you can understand not only how to write queries but also how databases actually work. Here is what we will be covering in this full course. Database and DBS [01:26] fundamentals. Understand what databases are, how database management systems work, and where SQL fits into the picture. Getting started with SQL, creating and managing data, retrieving data with select, filtering and sorting [01:40] data, aggregate functions and grouping, keys, constraints and relationships, SQL keys, constraints and relationships, SQL joins, advanced SQL concepts, MySQL and MySQL workbench. And throughout the course, we will use practical examples [01:52] concepts are actually used in the real world applications. By learning these concepts, you will be able to write cleaner queries, organize data information, and work with the database systems more confidently. Also, if [02:06] you're interested in building a career in data analytics, don't forget to check out our data analyst masters program by Simply Learn. This program helps you through hands-on learning and real world projects. You will also learn important [02:19] tools and technologies like Excel, SQL, PowerBI, Tableau, Python and data widely used by data professionals and expertled sessions. You will learn how to collect, clean, analyze and [02:33] visualize data to generate meaningful business insights. You will also gain experience working on industry relevant projects that can help you strengthen your portfolio and prepare you for data analyst tools. So if you're looking to [02:45] upgrade your existing skills, this program can help you gain the knowledge succeed in this field. Now let's go course. By the end of this course, you will not just know SQL commands. You [02:59] will also understand how databases are structured, how information is stored and retrieved, how tables communicate with one another, and how SQL is used to problems. Before we move on, here's a quick quiz question. What does SQL stand [03:13] for? Is it a simple query logic? B structured query language, C system query list? >> My name is Shinikam. I have been associated uh with Simply and I have been in this industry for now more than [03:28] 11 years. I have been in corporate training and consultation. So far I have taken uh I have been with more than 55 plus organizations majorly for corporate trainings. I take corporate trainings in MS Office 365 applications uh office [03:43] productivity skills basically and with that I have transitioned myself five years before into PowerBI Tableau and data science. This is a short introduction about me and we are here together uh for data acquisition [03:56] manipulation using SQL. First is first we'll get introduced to SQL here and then we will learn about what we have in the course right. So in the course here course we will go ahead and uh learn about what are data types we'll also see [04:12] what is data what is database we have to go ahead and understand here about database context here plus we will understand about few more concept of understand about few more concept of DBMS like normalization and other [04:25] efficiency skills however within this course we are also going to look into select uh queries that means what SQL can do so within using SQL. What is SQL? Why are we using it? And we will learn all everything from very beginning on [04:40] how to extract the data on how to create a database till the Windows functions. So we are going to cover the entire SQL in upcoming sessions. First before we [04:52] simply deep dive into what is SQL, there are few terminologies that we need to clear out so that all everyone is on the same page. So uh let's go through some again and we first we have to understand what is database. We have to also [05:06] understand why do we need it. Okay. What is data according to you information. All right. Raw facts collection of information set of information specific [05:18] format set of information. So basically we are saying that uh data is we are saying that uh data is information. Correct. So if I go ahead information. Correct. So if I go ahead and write some things here [05:41] numbers. There are some text. But does this make any sense? So what is data? In very simple words, in a very very simple words, data is structured information saved in a proper format. Saved in a proper format. Format can be anything. [05:58] That format can be uh CSV. That format can be txt but it has to be some format and so that the context is also clear. Okay. It can be Excel also. It can be in Okay. It can be Excel also. It can be in uh JSON also. Can be anything. So there [06:13] are many formats that we can go ahead and open. It can be saved in multiple different formats there. Now that's a simple uh structure or definition of simple uh structure or definition of data. Then what is data bas? Data refers [06:27] to collection of facts, figures or symbols. It can be processed and analyzed to extract useful information. So now here if this is data and we are seeing that we are saving that data in a proper structured format. It can be any [06:40] uh CSV text file or Excel file or it is saved in a table anything there in in under any object. So what is meant by database? What is a database of and why do we need it? Data is stored multiple data system where data has stored amount [06:57] data system where data has stored amount of data storing facility of data where data is organized and can be referred to when data where collection of structured data is stored. Okay. So we are saying here where all information has been [07:11] collected. So basically we are saying that that there is a proper base inside this we are going ahead and uh saving our data set right. So let's understand [07:23] our data set right. So let's understand it one by one. Data here is packed. It can be a part it can be numbers. It can be any other form of information an image text icon pictures anything. Right? They are generally structured in [07:36] a specific way or stored even for a particular purpose. You cannot simply go ahead and store any random information together is to be stored for a particular purpose too. Now this data here when it is in one single file let's [07:49] say there is one CSV here then we are good that that one CSV is saved in our system in some folder there. Even now if in your system you have multiple folders everyone you have got multiple files maybe those are word files powerpoints [08:03] maybe those are word files powerpoints PDFs excel files they are all saved for a particular purpose in a certain folder yes so basically what you do here in your own system is you go ahead you manage it organize it and accordingly [08:16] time to time you alter it and you change it update it correct and you keep on adding more data into it as required so in the similar sense Here this entire system or the folder that you have within your system you can call this [08:31] also a database right we can call that also a database why because database here is just a structured collection of data that is generally stored on a computer so that we can access it manage it and update it easily. So that becomes [08:47] a database. However, within the database, if we go ahead and refer our own systems as database here because it fits the definition here, is it easy to go ahead and find two connected files if they are in absolutely two different [09:01] drives? We know that logic that how these two files are connected to each other. For example, your salary slips and your tax returns. They are connected to each other, but definitely they will be in two different folders, two [09:14] investment details are in another folder. your salary step in another these two things are connected to each other logical. So that is not very [09:27] easily managed ex organized because all logic is in our minds right now. Okay, that is why we have multiple different type of database management systems. In database management systems here these are the systems specifically designed [09:43] are the systems specifically designed just to manage organize and uh collect the data there and to ensure that multiple users can easily access it without even touching the data there. So if we are modifi if someone else is [09:58] modifying the data multiple users can access it extract the data too. Plus while modifying and updating the data this particular data management system this particular data management system makes that data is consistent their data [10:10] has validated properly and plus the correct data is getting uploaded. So all of this can be done within that management system there. So database definition you all are absolutely correct. So what is DBMS? um management [10:24] system they have created in which they're saving that this data set and still and working on extraction modifying updating the data set there. within a database. Now we agree that there are multiple data set. Done. Now [10:41] the problem here is why do we need database system here? Whenever we create a database system, there are few things that are very very important here. So for example, right? Now you have files in your system, right? So let's say you [10:55] have got two files in your office laptop. One is for employees and one is for department. And these are two separate Excel Excel files, Excel workbooks. So if I will ask you that can you go ahead and get me the marketing [11:08] manager personal details how will you find it? So I understand the question again. Let's say you have got two Excel sheets okay on your system right now or let's say do Excel workbooks here one is have employee details and one has [11:24] department details and if I ask you here that can you go ahead and get me personal info personal info like personal info here of marketing manager how will you get it what we have to do because we have to go ahead and figure [11:39] out who the person is and employee details let's let's as well here. Okay, we have the personal employee details data separate only with the employee ID. So, we have to go ahead and open the two Excel sheets in front [11:52] of us and we have to first figure out any department details. We'll go ahead and filter out any manager or wherever the marketing manager is. We have to go ahead and filter that out. Once we filter that person out, we check for the [12:04] employee ID. Once we have the person's employee ID, we go back to employee details. We filter out that employee ID and then we get the details out of that. So either we do it manually or we apply VLOOKUP or anything we have to go ahead [12:17] and merge both the tables together or at least open them together since we cannot merge the data is huge. Okay. So you have to go ahead and we are talking nothing like that will happen. We are in the Excel workbooks right now in the [12:31] exam. Right? So this is how it doesn't happen. This is why they came up with that this file management system is not working. Okay. with it is uh here there are two very important things here first retrieving the data is uh the huge [12:46] limitation here is retrieving of the data set if I want to go ahead and retrieve the data it is very very difficult if we are saving the entire organization's data into excel files only in some shared drive only then we [13:00] had no idea okay how people who is getting who is updating the data set how many people are updating the data set right so in that case here we have too many limitations. The first point is [13:13] there is no security okay because it is in a shared drive all the Excel files are there for finance for marketing for digital marketing for HR. So first of all security is not there and if a new joining join if new person joins an [13:28] organization they go back and update its data set in their HR file someone is manually doing it. So when is manually going ahead and updating that data set and only not one person is responsible for that. So if there are multiple [13:43] are going to have multiple duplicate top data set. If I'm saving employee ID, employee name, employee last name, employee phone number, email id. So if I have employee personal details that will be there also. If I have employee uh [13:57] work details, I have to keep their contact details there also in case we have to go ahead and contact them. So like this there will be lot of duplicate data there and like this there will be more limitations here. That is why they [14:11] came up with DBMS that is database management system. It was an entirely different type of a system. Inside the system each table was saved as an object called as uh each data was saved as an object called as table and that table [14:27] was made with rows and columns. Now you must be thinking that why are you telling us about table? We know how a table looks like. I'm sure about that. Let's learn a little bit more about it. What we call as a table technically a [14:40] What we call as a table technically a data is called as a table structure that we are saving it in a table structure only when it has rows and columns both. Plus the first row will always be called as a header. First row has to be a [14:54] header. And each column header should have its own data type. If it is a number, it has to be a number. If it's a date, it has to be a date or from first row to the end. And when we are reading this data set or this table here, we [15:08] will read it from top to bottom always. Right? So that is DBMS. Right? So that is DBMS. Now DBMS has just one problem. Okay? It has eradicated data validation issues. It has eradicated data consistency [15:22] issues because now we can have security because we securing on the uh row level there. We are securing on the table level there. they're good. We can give access to the people we want to. That was good with DBMS. However, they had [15:36] one uh huge problem that these tables were a standalone tables. They were not connected to each other at all. So, if there is one table saved inside the object, I can go ahead and extract table from there. However, I uh those are not [15:52] connected to each other. So, employee table is not connected to department. Department is not connected to customer or the vendor table. There is no common or the vendor table. There is no common key between them at all. So, so when [16:04] with relational database. They came up with relational database. Now they understood that that see if we need to have a table. Okay, we need to go ahead and connect these tables to so only keeping in table structure is not [16:19] working for us. Since databases are scaling every day, we need to go ahead and connect multiple detailed tables with each other. But how do we connect they connect them based on common columns. So if we had a department [16:36] table, let's say if we had a department table like this, if we had a department table like this, we will make sure that we will go ahead and add employee ID. So this employee ID will act as a key or a common column. If [16:52] I added a employee ID here that this person's employee ID is uh let's say 101 maybe this person's employee ID here is 103 106 and likewise okay so we can go ahead and we have to go ahead and update the [17:08] employee ID right here after this here if now what do we connect it with now we can easily connect it with the employee table why because employee table is also going to have the employee ID now this becomes easier [17:21] Now I don't have to go find and put. So this is relational database. In relational database okay we will go ahead and connect the multiple tables with based on common columns. If common column is there good. If it is not there [17:35] we go ahead and we create the relationships by creating the keys. Right? So this becomes relational database. Now these databases nowadays [17:47] database. Now these databases nowadays are very very different types. One of the types here is MySQL. Okay, we are going to learn with MySQL. We'll see that right now. This is called as relational database where we have the [18:00] common columns and we connect them together. So let's see different type of databases that exist nowadays. Nowadays things have changed because a database here like relational database it can save only date, numbers and text. That's [18:14] it. If the data is within that a database can easily save it in a tabular Right? So one how many types of DBMS. Now we have relational database here. This is the first here. [18:30] Then we have NoSQL database here. Then here is graph database. Then we have centralized database and wondering why I'm writing first, second, third here. But I'm writing first and [18:46] second right here again because these are uh databases of different types are uh databases of different types based on different uh bases here. So relational, nosql and graph database here. These three databases are [19:01] differentiated based on type of data we are seeing inside it. These are based on type of data we see inside it. And these here are these are based on these distributed centralized this is [19:17] based on location this is based on location of data location of database this is based on location or you can say type of location. [19:30] So let's go ahead and know what is relational database what is no SQL and graph. So as I have just explained to you okay what is relational database everyone it's a collection of the objects data objects we are not calling [19:42] them tables here we are saying these are data objects which are structured into tables okay they are linked together with predefined relationship what is a predefined relationship here columns right and of course uh sometimes rows [19:56] are also called as records columns are called as attributes here and each attribute has one single type of data type and that is fine with us we have already seen that in table it's structured plus we will create the keys [20:08] and the common columns to establish the relationship this is the most common type of database here and these are the two examples I'm a SQL my SQL post SQL 2 two examples I'm a SQL my SQL post SQL 2 now next here is next here is noSQL [20:23] everyone um since the social media okay after um since the social media okay after 2010 it has uh scaled up uh to the data that they started Ed collecting. Okay, it has been scaled up to billions now at [20:38] that time only. So these social media websites they have data set like not only your email ID and passwords and the band details meaning passwords and the band details meaning not only number, alpha numeric, date and [20:51] text. They also have now your images. They have got podcast, some audio clips, They have got podcast, some audio clips, videos and comments, blogs, articles, entire web pages out there. So now we have non-tabable databases. The thing [21:07] that we can the databases that cannot be stored in a just a table there. So they came up with no SQL here and uh these now stored as JSON documents. Not just JSON documents. These are JavaScript object notation documents. They are [21:22] written differently. They are programmed differently. If you will look at them, okay, it looks like curly brackets and lot of and lot of commas and colons. But the heral data and a huge data can be saved in a very very small amount. So [21:37] JSON documents here okay they are classified into several categories like data models, document key values, wide columns and graph databases. columns and graph databases. NoSQL databases are scalable very easily [21:50] scalable. Now if there is any kind of social media application right JavaScript object notation I will write it down here it's like we have Excel it down here it's like we have Excel files right I will show you a file here [22:04] so this is JavaScript object notation file it is like excel CSV JSON okay goj JSON you might have heard of that too so these are kind of the extensions of the format it is written with a lot of semicolons and u very easy to define we [22:21] have inside it. I'll show you an example later. So this NoSQL database can save pictures in pixels. It can identify that too. Right? So NoSQL database examples here are MongoDB, Amazon, Dynamo, Apache, uh HBase. If you have heard of [22:37] Hadoop, it is also linked together with this only. So when you we have these kind of data and we want to save that, we don't save it in uh Oracle or MySQL. We go ahead to NoSQL databases. Then we have got graph database to [22:52] understand graph database here whenever you go to LinkedIn right u and you are looking at somebody's profile does that tell you that uh whoever has visited this profile have also visited these profiles or these are the mutual con [23:07] connection this is your second level connection this is your three plus level connection have you ever noticed that on LinkedIn how do you think they're saving the data set with them so let's say if this is you okay you have got let's say [23:21] thousand connections there. This is you. Now you have thousand connections here. Now all of these connections here how do they know which is uh falling on a second level, first level uh second level or first level. If anything new is [23:35] coming up, new one, new person is coming up. They also know that they are connected to each other. They know that whoever the next person, a new person is connections with you. Meaning this person's network is coinciding with you. [23:54] network again uh first level second level. Is it possible to save it in uh in an excel type of file? Okay. Where if you try to do that there will be lot of redundancy repetition every time because [24:10] if you are saving one person's network is it possible to save one person's network? How many people are there on LinkedIn and they're all connected to each other? So saving this in any kind of format is very difficult and of [24:23] course it is uh huge data set there. So what they do is they use cloud database. This cloud database okay they use uh graph structures okay which has nodes edges attributes parent and child relationships and likewise. So that's [24:37] how they go ahead and so that just graph database we are not going to work on we are going to work on uh DBMS. We are going to work on relational database only. Right, we are going to work on this and we are going to learn with [24:51] MySQL. Now let's talk about what is centralized and what is decentralized. So what is centralized database here? As it is said centralized database here? As it is said that it is a store, located changed, [25:03] maintained at a single location such as a mainframe computer. So there is a Those who are from the nontech background, it's a huge computer. Okay, it is kind of a server physically with all the hardware connected with it and [25:18] it is somewhere located at one single place. Now everyone who works in the exactly that location. Okay. With the help of internet here, they can go ahead and access the access whatever data they want from here. Okay. [25:34] database. Do you think it is going to have any difficulties or uh some limitations here? There are advantages, clear advantages that is going to be low cost, easy maintenance. [25:49] maintenance. But what are the limitations? Yes, we are storing the data here. Entire data is stored here at one place. Uh yes, if we have to scale the data, we have to make sure that our hardware can [26:04] handle that. Correct. Both may know if I have one computer, I will place it at one geographical location, right? It is not going to be costly. Yes, if it gets corrupted or slowed down, entire organizations uh will be like server is [26:21] work. Yes, it is easier to secure the data because it is just one single place. It is easier to manage the data too because it is just one single place. But if anything happens here or the server slows down, okay, we are going to [26:34] face that difficulty quite often if the data is scaling rapidly, right? If there are too many people trying to access the same thing here and the hardware is not uh designed for that much scale or for that many people at once it is going to [26:48] lag. It can be slower in fetching the data too and if it gets corrected our backup is still in this main frame here. So it will go ahead. So we uh we are at a risk of losing it but we always have a backup. Okay. So this is the centralized [27:03] database and most of the organization most of the organization will be having the centralized database only. All the organization that we usually see they work with centralized database only because it is easy to access and handle [27:17] and scale. Now after centralized database here we have the distributed database. What is distributed database here? Okay, it is the collection. It is the unified collection of linked database that are physically dispersed [27:30] database that are physically dispersed across multiple location. So here it is saying that they have distributed the database at the multiple physically multiple locations here but they still connected with the computer network and [27:43] they do have databases inside. They will have the databases saved inside it and since it is RDBMS everything will be connected to each other. Now what happens when uh the database is like that? Now when we have the users [27:57] here, when we have the users here, they can still go ahead and connect with the data set. Okay. By using the servers, but yes, of course, they will be distributed to as per their requirement as per the requirement access permission [28:10] and job roles they will can go ahead and access the data accordingly. Now what or limitations do you feel will be there? Network dependency. It was in [28:22] high cost maintenance. That's correct. It may be faster. Yes, data security is the issue. You have to maintain it at all the levels and you need now a bigger team to handle all the physical locations too. But if there is a huge [28:35] data, they cannot save it at one single place. They will disperse it across the place. They will disperse it across the locations. Google uses that. Google uses the distributed data based system orbit database style. Yes. And scaling is [28:49] easier. meaning for to scale up they don't have to add keep adding up too much hardware or systems it will be easier so now we here understand that databases here what we are going to work with we are going to work with RDBMS [29:04] with we are going to work with RDBMS here RDB RDBMS is the most sought why because most of the organizations data is uh that they want to analyze is date is uh that they want to analyze is date numbers and text so RDBMS is best right [29:18] and plus it is a simple mainframe computer we can connected with local internet and we can uh connect with that easily. So that is the most sought after but yes with the uh need of in the social networking systems and too much [29:32] different types of data set they might need to scale up to no SQL or cloud database. Very good. So the question here is what system is characterized by having its data structured logically interconnected physically distributed [29:45] over several sites within a computer network. as it says here distributed. Okay, this is the key that we need. So the answer will be C. Now let's go ahead and understand about SQL. What is SQL? We will deep dive into what is DBMS and [30:00] We will deep dive into what is DBMS and are DBMS then. So first here is SQL. So let's go ahead and part this SQL. First in SQL is the language. What is in SQL is the language. What is language? So the any language. So this [30:15] something. What do we understand that now? Now here is it is a language through which we ask something. Since this is a language, it has to has its rules and grammars. Right? This is why it is a structure. So it will have its [30:30] own rules and regulations that we follow while talking to each other. We do go talking that how would you make a sentence which are the vocabulary that you. Right? So structured query [30:44] language that has been created to ask the questions to the database and retrieve the data that you want or if go ahead and you simply ask the questions to the system there. Remember [30:57] that it is not a programming language. What is a programming language? We create something new there. We program something new there. So we're not asking or creating something new out there or the entire structure of something. No, a [31:10] structure is already there. We are simply going ahead and retrieving the data from it and that's it. Okay. So structured query language here means we are using a language which has which has its own rules, regulations and vocab and [31:24] using that language we are talking to the machine and asking the questions to it. That's it. That is SQL a structured query language. Yes absolutely correct. Now let's understand here what SQL can do. SQL can perform in our if we say [31:40] here it can go ahead and perform the operations like create, read, update and can perform with SQL. What we are creating here, we can create a database [31:52] inside the hardware structure that we have available. We can uh create a table. We can insert the values inside it. We can if you want to update it, if you want to alter it in some way, you want to delete an artist structure or uh [32:05] a row or maybe I want to update some data set inside it. Now when we are modifying and saving data inside it, once it is done, it is my part another data and analyze that data that person will be reading that data. So while [32:21] reading reading the data again we will have multiple steps to follow. So this is the CRUD here and this is how we can read the data here. So these are the four operations basic four operations here. Now MySQL is a language. It is not [32:35] only used at one SQL is not a language that we use only within one single database. Okay. It has multiple uses across multiple applications and platforms. And why we are learning via MySQL? Because MySQL is free to use. [32:49] Second, it is the easiest uh way to learn SQL. If you learn the fundamentals here, you will be able to adapt to any changes that you find in MSQL, Oracle, PL/SQL or Snowflake as you will move forward, right? So the fundamentals [33:03] remain same. It is an open-source uh system here. Okay? And it is easier to use a very very simple bracket user interface. That's why we use this here. Okay. So now let's understand the difference between DBMS and RDBMS. It [33:18] stores data as a file. uh we know that right now our DBMS here stores data in right now our DBMS here stores data in uh the form of the tables. Now uh RDBMS can have multiple users. We can connect the tables with common columns that are [33:30] the foreign keys and the primary keys there and it is easy research in RDBMS here. Data pitching is slower and common. So we have already been through that. Now a quick check again which system is considered most popular and [33:44] advanced version of DBMS with examples like MySQL, Oracle and MSQL. As we've just discussed it is our DVMS here right I hope you are on your LMS portal in your LMS portal on the right hand side when you click on self learning here [34:00] on the right hand side you'll find us uh find a vertical menu card. Okay in your vertical menu card you can see all the lessons that we are going to cover. You going to cover. Okay. So as you can see we are going to cover uh right now we [34:14] were having introduction then we start working with the database and tables through different type of commands you can see here and then we will see how to work with the operators constraints and everything else. There are multip uh [34:29] we are going to cover with the topics and subtopics. First here is first here is look at in instructor slides. These are all the powerpoints you will see me download using in in the class. Then here you have two types of practices. [34:44] What are these two type of practices? Here first here we have practice project and then we have here guided practices. You also have demos, right? What are these demos? These demos are the ones that are used inside the slides. So when [35:01] see that they are doing some small activities within the slides and there are few queries written there. So on what particular in lesson which write that query you can go ahead and find it from here. Now you have guided [35:16] practice folders here guided practice are lesson wise folders given to you and the practice projects that you see here. These are the these are the projects lesson end project. So once your entire lesson is completed for example you have [35:29] learned about all these small commands still here right so the lesson two almost everything from this lesson number two so these are the projects what does these folder uh have these folders have PDF files and uh two PDF [35:45] files are there first is the problem statement and another is the solution to that solution consists of the screenshots and the explanations for the query now whatever data set has been used in any of these projects set. It [35:58] folder. Inside data set folder, you're going to find three things, three folders inside it. One data set for demos, one data set for guided practice, this is your reference materials available. Now I would like you all to [36:12] click on live classes. When you click on the live classes here, when you click on the live classes here, when you click on the live classes here, I uh you have this uh box right now with today's date. Can you click on the box please? When [36:25] you click on the box here, you'll find here session materials are written and you will see a list of session materials here. You will see some PDFs. You're that you're looking at, you have to go to the epsilon or the three dots there [36:39] and copy it and paste it in the next browser. So session materials and things. The session materials are the live session materials. Whatever we are using here in the live session or whatever I solve within the live [36:52] session. Now in the session materials go ahead and find out there is a PDF file one for Mac OS one for Windows whenever we talk about data everyone okay so let's say we talk about my SRA now in the myra here when we talk about RDBMS [37:05] database here it is made up of three entities or you can say three components what are the three components okay one is going to be hardware one is going to be data that we save inside that hardware and uh then we need an [37:19] application to access whatever objects we have saved inside it. So if you talk we have saved inside it. So if you talk about like this right so we have software hardware and the data. Now these three components require a [37:33] container a physical container that is hardware application program a software application program which will help us to connect with the hardware and receive data inside. These three components have three types of users too based on data [37:48] obstruction mean uh understand data obstruction as a security. So who has how much access to database or individual data tables. First person here is the end user. Who is the end user in server? End user is the person [38:03] who has the access to data as per the job role. Their main job is to extract the data and create reports. They will be using the minimal coding process and their only job is for extracting the data and making the reports analyzing [38:16] the data and then moving on to other applications. These are end users. They work on the external rim or the as the end user on the application level. Then the second here are application programmer developers to those we are [38:29] call them nowadays data engineers right we call them data engineers. What would these people do? They are the persons who will interact with the data directly. These are the persons who will decide how to uh get data inside this uh [38:44] particular server. How which uh relationship has to be created? What data should be stored? What kind of model should be created? So what kind of schema should be there? This is all decided by the application programmer. [38:57] This application programmer has got nothing to do with data analysis. they are more concerned about how the data is managed and and secured inside the software. Now the person comes up third person comes in here that is database [39:13] administrator. This is the trend in in the entire structure here because this is the person who is responsible for the maintenance, operability, recovery of this entire connection. Meaning the network should [39:28] work properly, right? It should be operable. So the end users can go ahead and access that and extract the data set. Plus this is the person who will uh give restrictions access uh to the user. This person will control the entire [39:43] data access. So as per the job rule whenever there's a requirement you keep asking for data and they will provide you with the data set or they will give you the access to that part of the database. These people work on internal [39:56] level. These people work on internal level or physical level creating user ID passwords providing uh and replacing the security delay. So these are the three type of users here. So now what we what type of which course are we doing [40:11] everyone? It is concerned with analysis of the data set. This entire program is concerned with the analysis of the data set. You're supposed to analyze the data set. So either you're a data analyst or you're a business analyst or you will [40:24] move forward to data science. any one of these three whatever is your upcoming these three whatever is your upcoming path there yes so who are you which user are you one two or three they are the first users [40:37] I don't know why you are not sure maybe you are uh the experienced professional right there okay and you have worked within mixed job uh roles that's why where you are going to go and end up so if you are from the programming [40:52] with APIs and connecting multiple portals and extracting data inside the server then you will be working as a data engineer right however when you are will join as a data analyst you will not have the access to SQL at all unless [41:07] you're joining there with the 10 15 years of experience and on a manager level then you will have the access to all the three levels right there but uh those who are learning for the first time and you do not have any experience [41:19] you will be working only with the analysis and be extracting the data here okay Not the entire team will have the access to it or anyone who joins as a data analyst after this program. Neither they are going to have access to make [41:32] sure that data is getting there unless yes they join in a job role where they are supposed to modify access and make sure data is properly stored there. So now it depends on which job role you go but yes as a data analyst you should [41:47] know about two things there. First here is uh as a data analyst your first step always to collect the data you have to uh first figure out as per the objective okay which data tables are required and from where are you going to get those [42:02] data tables and the required data. Now it can come from one single server or multiple different sources. Once that is done your step number will be now you go ahead you analyze your data set. Maybe you import it into PowerBI, maybe you're [42:14] in Tableau, maybe you want that data in a CSV, you will go ahead and analyze with Excel. So you go ahead and analyze and create your reports and at the end course, you're not creating reports to save it with us. So we go ahead and we [42:27] share the reports with others. We go ahead and present them to BPMs like the person who take the decisions. Those are the business process managers. However, okay, uh if you are an absolute beginner with no job experience, okay, you might [42:40] go ahead into the uh into the level where you will be writing the SQL queries, okay, to extract the data right from here or maybe you will be asked to assist in updating and modifying the data if you have been given that [42:53] permission. Usually, it is not given. Let's talk about our uh here my SQL. Now, I'll say SQL operations. Let's talk about SQL operations, right? uh SQL operations here. What is the first thing that we will learn here? SQL operations [43:08] that we will learn here? SQL operations here can help us to do four things. What we have learned about those four four things there remember them. What were the four things that that SQL operations can do? What were the four operations? [43:20] So first thing that we see here is so let's talk about read here. Create we understand. Okay, we create a database. We create a table. Create is fine. Let's understand about read, right? Let's understand about read. What read is [43:34] going to do? Read is uh read means here that it will help us to fetch or retrieve the data from uh data tables or data set or subset of a data. Okay, from a table inside the database. While fetching this data here, while [43:48] retrieving the data here, we might want to go ahead and uh give the filter. to view the marketing data set, only want to view the last six months data set. It means we can go ahead and bring uh I need to filter out the data from [44:04] the rows right first thing is that second here is maybe I want I don't want all the columns there maybe I want to go ahead and uh only keep the columns that we need right now by uh now after retrieving it or while retrieving it we [44:18] last six months data it has to be sorted in a proper time series or in proper date column so we can go ahead and sort the data too sometimes while sorting the data okay let's Say I have the data here from the cashers. So what the kind of [44:33] data we have here? We have uh the customer ID, what product they have bought, what is the date and time, what amount they have paid, any kind of uh coupons were there or the cards were there that we had they have used there. [44:46] So what data I have is just the sales amount data. If you want to go ahead and I want to calculate what was revenue there. If we need more calculations there, we can go ahead and add that too. So if you want to add any kind of [45:00] you want to aggregate by anything, we can do that. Any kind of mathematical calculations can be done and not only mathematical calculations, we can also go ahead and work with conditional or logical statements too. So this is what [45:15] logical statements too. So this is what we can do with read and more. So we can go ahead and add calculate and aggregate data to maybe I want to simply see total sales by some country, total sales by product. We can go ahead and create an [45:27] aggregated reports too. Now when we talk about create here. All right. So within create the databases. We create data tables. We insert data records inside it. Okay. And since we have created those things, we have to manage that [45:41] too. Update uh the table or the data inside it. We write all the data. Now to do these all operations here to do to uh do all these operations here, we need some commands, right? Vocabulary. Remember I have told you there will be [45:55] keywords and commands and vocab there because it's in a structured language. So to do all of these commands here what are the types of statements that we will be working on. What are the types of statements? Command groups or types of [46:07] statements to write these operations and we call them DQL data query language. Data query language here. So the selected statements that you're saying here right that is that is meant query language there. Correct. Now this [46:22] is data query language. It means the uh keywords that are used to query the uh used to query. Second is data definition language DDL. What is the uh definition here? We are defining the database structure or a schema. What we doing [46:37] here? We defining the database structure or schema. Meaning how many number of uh columns we have, how what is the data type of each column? What kind of uh limitation we have to put into that? Is it date? What kind of format? Right? So [46:51] that is data definition language. Yes. Correct. Like create the table, alter the structure of a table, drop the entire uh column from the existence and lot more. Third year is DML. Yes, very good. Third year is DML. What is DML [47:06] here? Data manipulation language. What does manipulation language will do? It will help us to insert the data, update the rows, delete the rows, maybe merge the data together. This is manipulation. Third here is DCL. [47:21] Yes, very good. Thank you. DCL is data control language. What is the data control language here? This data control language is going to help us to give access to people, grant permissions, revoke permissions and likewise this is [47:37] usually used by the D. The person who is supposed to give permissions, get user ids for everyone else. This is DCL. Then we have one more here that is TCL. TCL is transactional control language. Why transactional control here? What is a [47:52] transaction? Okay, transaction here is we are trying to update the underlying data. You're trying to modify, delete a column or delete few rows here or you want to add few more rows there and it is not uh done by a single person done [48:07] by the entire team. So the entire team is working on that who is keeping a correctly? Is it correctly done? Whatever changes they have done it is correct or not. So while doing these kind of transactional queries okay they [48:20] use TCL with that and uh like there's a few examples here like save point roll back and commit and save up to a point you should be ready to control Z undo it that is roll back there or commit the change it means completely update [48:34] whatever data we have done right whatever changes they have done there that is TCL transaction control language of course who uses this your data responsible for modifying and maintaining the entire data center. Now, [48:49] so these are the types of commands. Our main focus going to be here. Our main focus is going to be here. Okay. Uh we I can show you few examples at the end. examples here. But our main focus is going to be here. Just letting you know, [49:03] right? So these are the command prompts here. You will already have Microsoft Windows selected. Yes. Once you have your Microsoft Windows selected here, go ahead and click on [49:15] selected here, go ahead and click on download. Select the first one here. Select the first one here. Once you select on the first one here, now in this page here, you're supposed to click on no thanks, then start my [49:29] to click on no thanks, then start my download. So the one with 2 MB here, we will go ahead and download that one. Once you click on no thanks and start my steps here. The post is custom. You get the server [49:43] good instructions here. You have to click on the plus sign. Then uh you will have the all the versions expanded. Then double click on this. Click on this. Get it on the right hand side here. Then find the [49:58] workbench. Follow the same instructions. So everything is properly written here. Once you when once you will go through each and everything here, you will have box. Few of you will see this, few of you will not see this dialog box. So, [50:12] and you're good with that. Okay. So, you have to click on the plus sign and get the workbench and the server on the right hand side. After the after this here, everyone, you have to simply click on the plus sign here as it you can see [50:25] it clearly. There's a plus sign here. Okay. Everything is properly given here. So, Mino and everyone else just follow the instructions here. It's a really good PDF. It will take you through entire [50:38] steps. If you face any difficulty, you let me know. So, plus plus on server. Then once you see the list of server, select the first one and click on the select the first one and click on the green arrow. Now you click on minus. [50:53] Then we click on plus on workbench again. Then the list of versions of the workbench will appear and you get the workbench here too. After getting these two here, we click on next. And after [51:06] that okay keep clicking on next. It will take some time to execute. In few of your versions if you're using Windows 10 it might ask you to get the visuals in here Microsoft visual there. Agree to the terms and condition and [51:20] install it. After installation just keep clicking on next until you reach to the password there until you go ahead and reach to the password. about the password here. Please let me clarify you. This password is uh not an [51:36] easy password to recover. It's an open-source application everyone. So the root password please make it uh easy. Okay, that should be in your memory. Write it down somewhere. Write it down somewhere. If you think that you can [51:50] remember it, okay, you can uh it is not possible to remember this. I would possible to remember this. I would suggest that go with the 1 2 3 4 ab cd or uh quarterty these kind of passwords that you can remember there is nothing [52:03] that you can remember there is nothing to save and secure here. Okay. So keep your root password easy as easy as possible. So that if you uh because it is not just clicking on forget password you get an OTP and it is reset. It is [52:16] forget the root password even uninstalling and installing will not help you. You have to go inside it and then change it and update. You cannot recover it. So make sure that when you reach to the root password, it is easy [52:31] and memorable. Okay. In this welcome screen, everyone, we can see local instance. Okay. So let's go ahead and click on local instance here. This local instance is uh when you click on the local instance here, it will take you to [52:43] the workbench. What is a workbench? Here is the graphic user interface tool. so server. When you click on the local instance here, this is the interface you will be looking into. So we have uh nearly four quadrants here everyone. In [52:58] first quadrant here you will find you have a schemas here. You might not be looking at administration management like this. Yes. So on the left hand side here you will find your navigator management here. And just above this [53:13] observe here you have your quick access toolbar. Above this you have got drop-own menu toolbar and here we have home tab and the local instance tab right there. What is this home tab and the local instance tab? This if you go [53:26] welcome screen and back to local instance. You can have many instances open up here separately. Now we are in navigator we see the management instance options here. Within here you will also find an option called as a schemas. [53:41] Click on the schemas please. In the schemas, do you see a cylinder right now? A cylinder kind of a structure. Uh menu, please click on this here. Now you click on local instance. Type in your password and it will take you here. So [53:56] let me now go through the interface here. Okay. Okay. So as now here we do we see this container everyone? I hope we do. This container is our database. What is this? This container is our database. But what is written here? It [54:10] says it is a schemas. Correct? So in MySQL here, let's talk about the MySQL right now. In MySQL, what is a database? In MySQL, data is database, the term database, okay? It is going to be equivalent or synonymous to schemas. So [54:26] now we understand what a database is, right? A storage system. But what I'm trying to say tell you here is that you need to this schema and database is synonyms and MySQL, right? So in database in MySQL is implemented as a [54:40] directory that contain all the files that correspond to the tables of database. we can uh if you want to create the database uh database inside the MySQL okay you require admin privileges admin access without admin [54:55] privileges admin access without admin access you are not allowed to create you're not allowed to create the database or the tables and the term schema and database are synonymous in MySQL here and uh in MySQL we can copy [55:08] and clone the database features we can keep a backup of the copy of the existing database plus we can also it als also includes table structure and lot many properties like indexes, constraints and values. As you move [55:23] terminologies too. Now within the database here, there is one thing that database here, there is one thing that we uh all should know about. What is that? While we are working in database, okay, there is something called as asset [55:36] something very very important here. These asset properties of database here transactions ensure us reliable handling and management of the database. Now listen to this very very carefully. This [55:49] is something that is asked a lot for in the beginners for the beginner's that you should understand that how things work. So uh there are four things here atosity consistency isolation and [56:02] understand it with an exam. Okay. Okay, transactions are executing in all or nothing manner. Right? So let me give you an example. Has this ever happened with you that you have paid for you have [56:16] with you that you have paid for you have uh you paid online for something and uh they say that that your transaction has failed or if you go ahead and pay there online. Okay, they transaction the amount is uh out of your account but [56:32] they say it is not reflected yet on their part. And if in next half an hour it doesn't reflect they will make sure that the transaction doesn't take place and you get your money back. Have we ever faced these situations? Yes or no? [56:44] Yes. That is atmosity meaning either the transaction will take place or either or it is it will not take place at all but it will not be left in the middle that okay we I have updated some rows okay and only half of the row got updated [56:58] other half was not there. It is not going to work like that. That is atmosity. These enable developers to focus on application logic rather than failures as well as recover of the complexity of the environment. Okay. Uh [57:12] complexity of the environment. Okay. Uh that will be easily done too. And all of this here right these are solely concerned with how a database recovers from any failure any kind of transaction or system failure that may occur during [57:25] the process or the update of the data set. And all the databases that we see here are asset compliant and ensure that only transactions that were completely successful that will be processed. If the failure occurs before we update [57:39] something if the failure occurs before uh a transaction is completed the data will not be modified. So for example, I want to go ahead and uh based on the want to go ahead and uh based on the credit limit of a person, I want we want [57:53] to go ahead and see which card credit card they're eligible for so that uh we can go ahead and call them and connect them with them via emails that you're connect to it. So what we want to do, we want to go ahead and modify that [58:07] want to go ahead and modify that database and accord and we want to add that credit card there. Yes. So we went ahead and we have kept our logic that up conditions are met this person should be eligible for these credit cards and we [58:23] want to go ahead and create a formula for that. But when we create the formula here either it will go ahead and calculate for each and every person or the customer ID present there or it will not even add that data there. That is [58:37] all or nothing. All or nothing means either the transaction will happen or it will not happen. Right. So the easiest example that Ali we have talked about is example that Ali we have talked about is whenever you pay online okay and your uh [58:49] amount is uh out from your account but they say that we have not received your amount yet and in next half an hour if you don't receive it we will uh the amount will be given back to you by the system. So that is all or nothing [59:01] because uh this is an example of atmosity. I'm not saying this is how database works. Database works like I want to add a column something happens or error comes in between. All right. It is not going to go ahead and update it [59:13] halfway through. It will not not going to update the entire column at all. Unless uh it can go ahead and update each and every row within that database. It is not going to add that column. So that is atmosity. What is consistency [59:25] here? Consistency is simple. Okay. It maintains a transaction. It maintains that a transaction follows a set of rules. Now what is consistency here? Consistency here says that it maintains a transaction and it follows a set of [59:39] rules. Meaning if we are about to go ahead and modify any database within the team itself, we will go ahead and set some rules that okay, how does this work and what are the set of rules that we all should go ahead and and follow [59:54] there. So that is that becomes very important within consistency how the consistency will be maintained. If you know that uh if we want to go uh the entire process is saved, right? If you want to modify that or you want to alter [01:00:06] the changes or if you want to update a database so everything is going to be consistent so data should always be valid. So if I am using the uh date here DDMY so data date will exactly be like that. That's consistency. So for example [01:00:22] once you transfer the money to someone right once you transfer the money to right once you transfer the money to someone what happens the bank balance will remain same. Whatever transactions you have done, the bank balance or total [01:00:34] bank balance will remain the same. So if you have given 1,000 rupees to someone, it will only deduct 1,000 rupees automatically. It is not going to simply change the data there unless you don't do the transaction. So it maintains the [01:00:46] consistency there. So that's consistency. So data must always be valid whatever the rules of database are. If the database rules and the ahead and follow those set of rules, [01:00:58] data validations and data types and other limitations and constraints too. isolation. Isolation is pretty interesting. It ensures that transactions do not affect each other. What does that mean? It means that each [01:01:13] transaction will happen independently without interfering each other. So for example, if I am going ahead and adding a column, then the column will be added. will be deleted separately. They are not going to affect each other. A real time [01:01:29] example, for example, there are two people. Okay, two separate people, they're transferring money at the same time. And do you think that will happen many multiple people they're transferring money within the same bank [01:01:43] at the same time. Is that going to correct each other's oper operations? Is it going to mix up the amounts or the bank details? No. Why? Because these are isolated transactions. They will occur independently for them. So that's [01:01:57] database also this has been taken care of and we work within that. So that's isolation. So if you're doing any kind of update, modification, adding of data, deleting of data or adding columns, deleting columns or any calculations [01:02:12] inside it or creating relationships or anything each and every transaction a statement will be separate and isolated. Now the next the fourth one is durability. What is durability here? Durability guarantees that written data [01:02:25] will not be lost. So durability says that okay once the transaction is committed once we have finalized that and we have committed that then it will remain even if the power failure or it crashes whatever happens but once the [01:02:41] added a column I have added the common values and after that uh even if uh the power fails or the server crashes the data will remain within that particular [01:02:53] server. So when you switch off your laptop okay the data will still remain there. So it is something like that. So that's durability. So for example once you are paying to someone and on your screen it displays like transfer is [01:03:06] successful on the screen. So when it says transfer is successful there and the th000 rupees goes out of your account that is permanent. Can you reverse that on your own? Okay. Once the record is uh created there it will be [01:03:19] created. We cannot undo that. So that is durability that once a transaction is done transaction will be finalized and whatever changes it has done those changes will be finalized too. So that is asset properties of databases it uh [01:03:33] and all the databases must go must follow these properties here. So in the complete asset system the database engine guarantees that the synchronous preferred to physical medium. So basically we are trying to say here is [01:03:47] in the entire uh asset system here right we the data engineers basically those maintaining data set they will follow the same and it is designed based on these four properties. Now we have talked about the database and schema [01:04:02] synonymous and my SQL right. So we will now see how we can go ahead and see some now see how we can go ahead and see some very small few keywords when the instructions are given number right for example I will give instruction to SQL [01:04:18] existing table it will go ahead and add a column right because I'm the one user but but as if I'm the one user like me there are multiple users in a team I'm updating the customer data set another one is updating the employees [01:04:34] data it. Okay. So if I'm giving instructions to add a column, add the same instructions to another table but within the same server, these instructions or transactions are not going to affect each other. They are not [01:04:49] going to get mixed up. I'm saying that in customer go ahead and create a column at the same time if that person is saying go to employees and update the rows there nothing is going to affect each other's transactions here at all. [01:05:02] Or a very simple example when you go to ATM two people are simultaneously taking the money out of the ATM does that mean if you type 5k another person write 10k suddenly the transaction get mixed up and you get 10k from that person's [01:05:16] account does that happen no that's isolation that even though two the more same system right they are not going to affect each other right so let's go ahead and understand about schema and how to write these syntaxes here and [01:05:31] what does the syntaxes and parameters mean. We'll see that too here. Now, uh when we say database here, right? So, there are three type of databases schemas. Now, what these schemas are basic or logical and physical schema [01:05:43] here. However, to talk about the schemas here, first we need to go ahead and understand my SQL graphic user interface and run some queries there. Then we'll come here and talk about the commands. Okay, we have already seen that these [01:05:57] are the core type of commands there. So, let's go ahead and see. Now here uh this here is a schema. So we have established that there this is where we write our queries. This particular portion also have a quick access toolbar. Everyone on [01:06:11] the right hand side this is the help section. Okay. What is this? This is the help section and this is where we see the action out. What kind of action output here? Whatever query we will type here. Okay. When we execute that then [01:06:26] the execution time what action or what query was executed what the result of that execution and how much duration does it took that action output will be here right so let's go ahead let's go ahead and uh see first learn here how do [01:06:41] we type something here so first here is do we have any database here no we don't have any database here so let's understand how to go ahead uh and first add a comment and then we'll see how to get data set here. So first thing we [01:06:55] will always remember database and a schema will be used synonymously here in MySQL and nowhere else. How do we add a single line commitment? So if we uh if we go ahead and type the pound sign here or the hashtag that you call it, we can [01:07:08] add a single line. If we want to add a single line comment, either we can go with this or we can go ahead and type two minus signs or hyphens and a space here. This also adds the single line comment. Now what is a comment here and [01:07:22] why is it important? Single line comment here is the non-executable part. What is it? It is a non-executable part. Basically it's the description of whatever codes and queries that you are writing here. But what are you doing? [01:07:36] you're writing here, you have to write the description of that and that is time we can write everything every explanation in one single line too. So what we have here is the forward slash and the aster sign and the aster sign [01:07:52] and the forward slash. So we start writing like this. All right. And if you want to a multi-line command, we want to write in multiple lines, right? So we write in multiple lines, right? So we have to enclose them enclosed within [01:08:07] have to enclose them enclosed within these here forward/ Now what we want to do? We want to uh insert we need to go ahead and get database, right? We need to get database here because right now what we have is a [01:08:19] system database. It is not going to have any tables or something. It has some some system details only. Right? See what tables does it have here? System configuration. It is not going to help us uh here. So we need to get the [01:08:32] database. How do we get the database here? So we have got either we should have SQL codes through which uh query and codes through which data is created. and codes through which data is created. Right? So I create a database here. [01:08:47] Or we can have a CSV file. We can have a CSV file that we can import here. Okay. So we can go ahead and import the tables too. So first that we will see [01:08:59] here is how to get the database by using the SQL course right here. Right. And how do we how we go ahead and create the database. So let's uh talk about create database. Right? Whatever I am writing here. Okay. almost in the real time. I [01:09:14] will update that too. Now, Excel is quite direct. Sorry, SQL is quite create a database, we have to simply go ahead and write C in the dropdown. You can see create is there, right? So, from the drop-down, you can press on tab and [01:09:28] the create will be there. You can also go ahead with the uh mouse there and uh double click on that. It will be written there. And uh if you're getting it in something, select the keywords from the drop down. That will be easier to type. [01:09:41] Now what we want to create here? We want to go ahead and create a database. So we can go ahead and write DA. We have the database right here. Now we are creating our first database here. Right? So I'm going to go ahead and name it this here. [01:09:55] I'm naming it first database here. You can name whatever you want. So this is create database. First database here. Now first focus on what I'm doing. See whenever we are learning queries here, follow along with me. meaning first uh [01:10:10] right I will execute this I will show you where to execute it how to do it and is the output and where is it is attention on the screen please you can see I have written create database and first database here and then I have [01:10:23] first database here and then I have given a semicolon here yes what why do we need this here reason is uh semicolon here terminates the statement it tells here terminates the statement it tells the uh the tells the SQL that see this [01:10:36] is where your command or the instruction uction stops. Correct? So it terminates uction stops. Correct? So it terminates that statement that does here this is the statement. This is the only command that you have to go ahead and [01:10:48] execute right now. Now how do I execute that command here to execute the command here where either you go ahead and press control enter right that is the shortcut key here or you can go to the query dropdown here and see first. If you go [01:11:02] to query dropdown there are two options here. First is control shift enter that is uh it will go ahead and execute whatever you select. Select means proper select it will should highlight it and get selected otherwise it will go ahead [01:11:15] and execute whatever codes or queries are there inside the script. We call this a script. Otherwise if you want to go ahead and execute the current statement that we have here right now where our cursor is blinking right that [01:11:28] is execute current statement that is control enter. We can also go ahead and use these here. Okay. So this first lightning bolt that you see here, it will go ahead and execute all or selected. This here executes current [01:11:42] state because it has a cursor there. So lightning bolt with a cursor will execute the current statement wherever you are keeping the cursor. Since we are doing this, we are executing this for the first time here, right? Let me show [01:11:54] statement, you select it like this and you execute with lightning bolt. If you want to use the second lightning bolt and we are only executing one single line, you can keep the cursor here and execute with the lightning bolt with the [01:12:07] cursor or press control enter. So when we click here, see what happens it uh in the action output it is saying uh green sign and we have create database here sign and we have create database here one row affected right but even when the [01:12:21] uh when it is affected here are we able to see anything right now here on the schema part? Now, so you have to go ahead and refresh the schema right here. So, first we kept our cursor inside. We have executed [01:12:37] this, right? Now, go ahead and refresh the schema. So, once we get ahead and refresh the schema here, we find our database right there. Of course, currently it's empty. Okay, it has got nothing inside it. Once the exe we [01:12:49] execute the statement, okay, we will see the output. We see the action output here. But if you want to go ahead and view the schema here, we need to refresh the schema. And there is a small refresh button here. Click that. You'll find [01:13:03] this here. So first uh this is first. This is second. And this is going to be the third step. Okay. This is administration. Just towards the right there is schemas. When there's a small refresh button here, [01:13:18] I have given too many highlights on this. Can you find this on your own please? See you know right and what were the three database here first we could have created the database by ourselves but [01:13:33] the table we have to insert the individual row two so now instead of doing this let's go ahead and import the data now how do we import the data here from the SQL file from the SQL file in which already the codes are written for [01:13:48] us this file is in the session material that is called as MySQL sample database folder. If you will go ahead and expand and extract that folder everyone [01:14:00] go to the file dropdown left and upper corner from the file dropdown we have corner from the file dropdown we have fourth option open SQL script. So we are here file drop-down left and upper corner it click click on open SQL script [01:14:16] those who are working in their systems just follow along and find where your file is every and the person who is working on the lab go to desktop in the desktop you will find a classic uh you will find the classic models folder [01:14:30] inside the classic models folder you might find the file so we are going to the file here click on opensql script find various script is find various script is wherever you have saved it [01:14:52] on open, do you see these blue colored lines? If I scroll down here, you will find that okay, they have created a database on the 26th line and then they have created the table and then they inserted not many rows there. So, it's [01:15:05] anything else. Just keep your cursor anywhere doesn't matter where you're keeping the cursor. Keep the cursor anywhere. Doesn't matter. Just uh go to the query drop down here and click on execute all. Once you have opened the [01:15:18] SQL script here, go to the query dropdown and click on execute all. So when you click on execute all everyone, okay, there will be action output. Some will be green colored check boxes, some will be warnings. For now, please ignore [01:15:31] all of that. So once you click on execute on okay wait for five uh seconds there and you will see a lot of check signs and the green warning signs there and that's perfectly fine. We don't see classic models right here. Okay so [01:15:44] please go ahead and refresh please again please refresh the schemas here. confirm. Are you able to see classic models right now? Yes. Now I would also like you to pay uh attention here. Do you see now we have [01:16:00] two tabs here. Do you see we have two tabs here. One is MySQL sample database and another is where we were writing the queries. Do we see that? Yes. So we have classic models right here. We have refreshed it and yes we have got [01:16:15] two tabs everyone. One tab will be for MySQL sample database and one for query one. Right? So please switch back to query one and close the MySQL sample database. We don't require it anymore. So please close this one. It will be [01:16:29] saved in your system. Please close this here and keep only query one and go ahead and save this too. Once you'll try to save this uh dialog box will open up. You can save the query by going to file dropdown [01:16:45] and save and you click on save here. Okay. [01:16:59] go ahead and save your file within any name that uh you're looking for. So make sure you are where you are here where we are saving where we are writing the codes. Now this is the classic models right now [01:17:14] we uh inside the classic models here I would like you to expand please expand the classic models you will see tables here when you expand the tables here you will see list of tables right here we have got multiple databases also here [01:17:28] correct so how do we go ahead and see what all the databases that is that are present here okay so we can simply ask it that why don't you go ahead and show databases The next step is to write show databases [01:17:45] because now we have multiple databases here, right? So we simply go ahead and ask it to show databases. Then it is going to give us the hidden databases too that we don't see here in these schemas. So we simply go ahead and write [01:17:57] show databases. We can see the list of databases here. Yes. Now what do we see here? What is this? We are looking at this particular quadrant for the first time here because output is there, right? What is this? This is called as [01:18:11] result grid. This is called as result grid. Result grid will show us a temporary output. This is a temporary output. So while when we write here show databases, what it it is doing here? It is showing us the list of a list of the [01:18:26] databases here. And this is going to be a temporary output that we can see or export. So we can see it here. Okay. And export it from here. So whenever you ask for a data whenever you go ahead and ask for data from any database administrator [01:18:41] file right most of the time they will give you CSV files. So you get to export give you CSV files. So you get to export it from here. This is the very this is your temporary op. We will go ahead keep my cursor here. My cursor is here and [01:18:56] cursor sign. So I will go ahead and execute this. And here we are. Or you can press C enter and we see a temporary output. This temporary output [01:19:08] We have talked about different databases, right? We have talked about DBMS. DBMS is simple file systems that we have in our uh own laptops. Second, RDBMS. What are RDBMS like Oracle, MSSQL, PL/SQL, PISQL, uh we have MySQL [01:19:26] here, right? All the snowflake is there. It is also a type of RDBMS relational databases where data is connected to each other. Correct? Then we have got each other. Correct? Then we have got object- oriented NoSQL. In NoSQL, the [01:19:39] saved as an object or JSON values there. Why? Because it contains unstructured data set like you have got videos, you have your have your videos, podcast, comments, blogs, post, [01:19:53] pictures. So uh geographical data set that is too huge to be saved in a table. the objects there. Then we have graph databases. Graph databases save the [01:20:05] networking within the hierarchy or a flowchart. It has got parent node, parent and child relationships, a start node, end node, relationship node. So that is graph databases right there. So as we have already discussed when we [01:20:17] about LinkedIn social networking website how they are tracking that whom you are connected with and how they are interconnected with it that's your LinkedIn you go to Facebook you see the recommendations right there right that [01:20:30] is the real scenarios there so this is where these databases are created whatever databases are there when we talk about RDBMS for example there is an office uh an organization there that entire organization is collecting data [01:20:44] from multiple sources. What are the multiple sources? An organization is multiple sources? An organization is creating data set. So here how do you think the data is getting into the servers? All of us go there. All of us [01:20:57] uh go to the office. Correct. And uh do you go ahead and swipe your card or maybe you use the biometric attendance? Do we all do that? Yes. You're creating data. You're creating data. Where does that data go? Where is it getting saved? [01:21:13] We all have a portal in which from you can go ahead regularize your attendance, check your attendance uh and apply for leaves. You can download your salary slip. You can check uh everything. We have a portal, right? That portal is [01:21:27] like a website. That portal is like a website. Correct? Now, if that portal is like a website, where are they collecting their data set? So, at the back end, that website is collecting their data set somewhere. That is their [01:21:40] database. But is that database your server? No. Is wherever their data set is getting uh saved at the back end is that your database or is that your Oracle server or MySQL server? No. So what happens here is like we have HRMS [01:21:55] software there. In the similar basis, we also have okay we also have database engineers that will go ahead and connect these application data set to our server [01:22:08] and they will make sure that whatever data is getting created from all the departments maybe the sales people are using Salesforce and data is getting updated there. So what are they going to do with that? They'll go ahead and they [01:22:21] get that data first. they'll go ahead and they get the data first from multiple different multiple different options. Now there can those can be multiple different websites or portals or APIs for where that data is getting [01:22:34] saved and once they will go ahead and get the data in here that then uh we then uh end users like us will get to go ahead and extract the data and actually work on that. So as an end user I am not concerned from where the data is coming [01:22:49] into the server. What I am concerned is if my job role is in finance, if my job role is in operations and I have been given an objective as a data analyst, we have been given an objective that uh see and as a data analyst or a business [01:23:03] analyst that uh you are working in an insurance sector, you have to increase the sales by 10%. You have to increase the sales by 10% in next quarter. How will you do it? So what will you do as a data analyst everyone? Yes, you have to [01:23:17] previous sale detail. You can get multiple product details, customer details. After gathering the data here, now you have to go ahead and anal and uh manage the data meaning ETF. You'll extract the data that you do you want [01:23:32] from certain department or do you want data from the outside? Correct. Now you will go ahead and extract that data from where do you extract any server or cloud? Right? Not every time you'll have a server might be your data is saved in [01:23:45] a cloud. You need to get your data from the cloud there. Once you have data in any kind of BI tool then you go ahead visualize it analyze it predict it forecast it now that went that goes there. So where SQL is getting utilized [01:23:59] everyone SQL is getting utilized where we are extracting the data from a server remember that as we have established first here SQL is a language that is [01:24:11] used almost in every software. If it is not SQL then SQL like language will be used. So it is a foundation here to understand and learn so that you can extract the data from whatever server you will face. Okay. As per the [01:24:26] discussion, right? So uh this is there is something called as DSA. There is something called as DSA. What is a DSA here? That is your data source administration. This is done by database engineers or the developers. Right? [01:24:40] These engineers or the developers they actually go ahead and uh they will um connect see here it is called as data source administration. Okay. First they [01:24:52] will connect to the raw data right it can be any kind of uh it can be any other database also it can be a website it can be a file it can be any cloud AWS Azor is no or your Tableau cloud out there or any kind of cloud or any portal [01:25:07] that you guys have right or in your office it will go ahead and connect with everything and then it performs the actual ETL here it can be your Tableau it can be your SQL server it can be Oracle or you can use Python for that [01:25:21] You first go ahead extract every raw data from there and you go ahead and perform the idea within your SQL. Then they pre build the data model. What who does that? All database engineers not us. These engineers will decide the data [01:25:35] model meaning which tables are there. What should be the common column there? What is how the tables should be joined or they should be normalized or together or not. They will build the data model. It will create the entire [01:25:48] structure of the database and then the data governance is applied meaning security is applied role level security column level security uh metadata means your information about the database and the primary keys limitation constraints [01:26:02] everything is put in place security as per the user will put in place different layers will put in place for security and after that data profiling will they will make sure everything is working profine and Then at the end they [01:26:17] will go ahead and they uh they will publish that data set for everyone. After publishing that there now they will be managing it, updating it and taking care of it. Once everything is done then we go ahead and uh get [01:26:31] everything here. You don't have to know about it. This is done at the back end. What we are concerned with that I have a database. Now in the database I have these many tables. I need to solve uh how my customers are doing. So how do I [01:26:45] write the query so that we can go ahead and extract uh the data. Okay. So this and extract uh the data. Okay. So this entire structure here is huge and it has [01:26:57] multiple learner multiple people working in that. So let's have a quick recap here. Let's uh recap our interface everyone. Right in this interface here these are menu dropdowns. These are menu dropdowns. This is our quick access [01:27:12] toolbar. Once we open it, we already have the previous day one opened up rightly. Correct? Now in this day one here and this is our schemas. This is the schemas here. How do we reach to the schemas? We have got two tabs here. One [01:27:25] is administration and one is schemas. We have to simply click on it to switch have to simply click on it to switch between them. Right? Schemas and database. This terminology here in MySQL is used synonymously. [01:27:38] is used synonymously. Okay. Now we have learned about the comment first. Why we have learned about the comment here? Let's see understand this here. For example, get database is a comment. What is a comment? Comment is [01:27:50] has asked that why we are using these symbols. These are not symbols. These are notations. Okay, we are communicating to SQL that see this is not a command. This is not a command. Because if I'm not typing the pound sign [01:28:05] or the hyphens here, it uh what uh does it think that this entire part is a command? This entire part is a command which is not right. This is why we have to go ahead and either we will go ahead and type two hyphens and a space to [01:28:21] inform SQL that see this is a comment. What is a comment? Comment is any kind of description we are about to write. So as in when we move forward you will understand that why comments are necessary and when we writing a direct [01:28:35] statement okay we don't have to give any symbol right here we can directly start writing the comment all right when we go ahead and write a statement here or a command here you can say meaning we are commanding my SQL to do something here [01:28:48] we write create database let's see how do we go ahead and get this classic models right here so we go to the file dropdown we click on open SQL script [01:29:01] MySQL sample database is downloaded where it wherever it is downloaded. Select your U.SQL file here. You might not have the extension written there but [01:29:14] you will find this SQL because that is the only file it will search for. Once you will find the file wherever it is, go ahead and click on open. Once you click on open, you will see another tab is opened up. Right? As soon as you see [01:29:26] another tab opened up here, go ahead and execute it. How do we execute it here? We keep the cursor inside. We go to the query dropdown and click on execute all. Okay, just keep your cursor here anywhere. Doesn't matter where. Go to [01:29:41] query dropdown and execute all. You can see the keyboard shortcut control shift enter. Once you execute all, wait for 5 to 7 seconds there and then click on refresh right here. Click on refresh. This is a refresh button here. Refresh [01:29:56] the schema and you will find classic models. Right here you can expand the tables. Here you will see the list. Just below the uh file tab here we have a plus SQL icon. Just below the file tab. So let's open up the date 2 file here. [01:30:12] So go ahead and click on plus SQL here. When you click on the plus SQL here you will find the file will open up in the next tab right there. First thing that we do here is we save it right from here. So there you will uh find a small [01:30:25] quick access toolbar just below your script file. This is again a small quick access toolbar. So here we have a save icon right there. So after uh after clicking on plus SQL go ahead and click on the save here. The [01:30:42] save will open up a very familiar dialog box here. [01:31:02] like here. Once you have added and saved, yes, file is saved. This is where we work. Very good. So, this is our action output. There is too much extra output. Now, see here, output is filled because we have [01:31:15] executed the classic models. If you want to clear your output here, you can go to to clear your output here, you can go to output, right click here and clear. So if you go to your action output window everyone, okay, if there are too many uh [01:31:29] output lines are here, you can right click and clear. So that now when we write any new query, we will be able to identify easily about the output. If say there is something in the output, right click and clear. [01:31:42] While you're doing it, let's go ahead and talk about today's agenda. So uh before we have already seen the how to create a database and we we can go we have imported thesql right we imported the data.sql we can also go ahead and [01:31:56] import CSV files everyone we can also go ahead and import CSV files. So uh what we are going to see today first first we will go ahead and see how to import CSV will go ahead and see how to import CSV right how to import CSV here. Next uh we [01:32:09] will move forward and uh look for we have seen create and show databases we are going to talk about first data type in SQL but we have to understand the data types in SQL. After understanding the data types here then [01:32:24] we will move forward with the select query. Now what is select query? Why uh what is the sequence of select query out there? What is the syntaxes of that? We will understand about the select query. So first is importing the CSV then [01:32:38] is our today's agenda. Do we have the first database here which is empty? It has got absolutely no tables. So now let's go ahead and import a CSV file. So under the assisted practice data set there is under the lesson file there is [01:32:53] an employee table. There is an employee table. So it is inside the data set folder. Inside data set folder you will have your the assisted practices folder. In there you have got lesson number pi. In that we have this one CSV file [01:33:07] employee table CSV. So let's go ahead and import this table inside our database here. How do we import it? We right click on the tables everyone. Right click and go to the fourth option table data import wizard. Go to the [01:33:21] fourth option table data import wizard. We go to tables first database and tables. All right. The blank database that we had that we had here, go to tables, right click and [01:33:35] table data import wizard. When you click on table data import wizard, what are you looking at? This dialog box here is asking us to browse. So let's click on asking us to browse. So let's click on browse here and uh find your database [01:33:48] browse here and uh find your database please. set folder. It is a system practices lesson five employ. [01:34:09] will be here. After this just keep clicking on next. Do nothing. Keep clicking on next until you reach to the finish button. So click on next here. No finish button. So click on next here. No changes. Click on next again. [01:34:24] No changes. Click on next again. We uh this is only for us to read. Absolutely. You don't need to go ahead and do anything here. Just click on next again. [01:34:37] Once it has executed properly, it will simply check it by itself. Then click on next again. It will tell you 20 records imported. And then click on finish. So keep clicking on next until you see [01:34:50] 20 records imported. Once you see it there, just click on finish again. Right? So right now we have imported CSV everyone. Now we want to view what kind of table it is. How do we go ahead and view the table right there to view the [01:35:03] table here? Hover the cursor over here and you will find three gray colored icons. Information settings uh here and a table with a board sign here again. So bold sign and click on it. When you click on it, observe what happens. When [01:35:20] click on it, observe what happens. When you click on it, observe what happens. here? A new tab is opened up and a query is written for you. Correct. Now what? [01:35:35] is selected here. Right? Properly in tables and columns, rows and columns here. This is employee ID, first name, last name, gender, role, department and everything else is right there. We can see it here, right? And what we will be [01:35:50] focusing on the query that is written for us. See what is written here. Select estrix from first database that is the database name and the table name right ending or terminating with the semicolon right [01:36:03] here. So what uh what is the syntax right now? So I will go back to day two. Remember to switch back to day two. Everyone remember to switch back here. How does uh uh the select query is looking like? What is aster here? [01:36:16] Meaning all the columns. Okay. What does this mean? That we need all the columns. it that go ahead and select all the columns from where? From first database [01:36:28] inside first database go to the table. So we have to give the entire address giving database address that go to this database. inside this database go and fetch the entire columns of this particular table. So we are uh talking [01:36:44] in a very simple language here. Okay. So if someone will ask you here we are having dinner if you ask some ask uh this ask someone that can you go ahead and get me uh the uh salt and sugar from [01:36:58] kitchen. It's that simple. So they will go ahead whatever they find they will bring it back to you. Either you can select everything or you can pick what you need. So here the entire address is right there. Let's go ahead and [01:37:11] understand here how does that work in real time. So we are going to work on classic models everyone right. So uh in the in this particular table here I would like you to observe here employee ID first name last name gender role [01:37:25] department what uh type of uh what type of data it is text these are all text whether it is alphabet alpha numeric it is going to be text and most of the time is going to be text and most of the time we are holding on to text right let's [01:37:40] we are holding on to text right let's look into the experience and salary employee rating this is all numbers So first is data types in SQL. Let me cut and paste this here. So first type of data here is always text or [01:37:54] character. Most of the time the dimension a dimensional detail that you will find is uh going that you're going to find in a text format or text data type. Text data type is sometimes also called as uh character or a string. In [01:38:09] SQL how these data types are defined in SQL. Here these data types are defined [01:38:21] that is car. What is car here? Car means a fixed length character. Car means a pixel length character that all in all the uh length will be similar. What is meant by fixed length here? In a fixed length name, any kind of ID or unique [01:38:35] code or unique ID that we provide. For example, employee ID uh employee ID is fixed. Everyone will have either a alpha numeric employee ID. It will be uh it might have six uh characters or it might have seven characters or 10 characters. [01:38:50] is given. Employee ID for the entire organization will be fixed. If there is a product ID, it will be standardized or any kind of error codes or defect codes if we have during the operation cycle that will also have a fixed structure [01:39:04] and the format. Okay. So that is character that kind of columns are defined by character. Next here is where. What is here? Very simple. WCAR has variable name. What is meant by variable name? Name of anything. Name of [01:39:18] anything. Name of city, product, uh person name, last name, country, person name, last name, country, whatever is out there. That is where means variable length. What is a variable length here? Whatever data we [01:39:32] are going to write in multiple rows, there is uh we cannot say that their length is going to be always same. Not everybody's name is same. Not not every city name is similar. There is no fixed format for that. So it will have a [01:39:44] variable length across the rows. That is why it is val. We do have more text string. But these are the most used ones. Then we have numeric. In the numeric here first the integer example for the integer. We know that [01:39:58] it's a whole number. What will be the example of uh integer? A whole number. What kind of data will be whole number without decimal? whole number can be number of projects someone assigned with. Whole number can [01:40:11] be number of cars parked on a certain day. Sometimes we are only looking for uh the uh whole number age. Quantity in inventory correct that's also that that [01:40:23] is also integer. How much quantity we sold out. How many total employees are sold out. How many total employees are hired this month? How many got retired? Defective number of product. Number of defects in a product marks can be in [01:40:37] decimals not always whole number. So these are the few examples of whole number okay in real time. Now we have float or double. So either we call it float or we call it double in here. Yes no worries we are all learning. So this [01:40:52] is float or double right? What is float or double? So here it is with decimals or fractions. So maybe we are uh give me examples for decimals and fractions everyone. What kind of data set can be in decimal or fractions? Right? Says [01:41:07] profit any kind of continuous numerical variable with the larger number out there. It will be float or double. But we do have one more type here called as decimal. What is decimal? It means again the number with decimal or fractions. [01:41:22] Again with decimal and fraction. Then what is the difference between them? floating double and decimal? If both the numbers are written with decimals or in fractions, right? It is again on based on where are we using it first in the [01:41:36] float here. Okay. uh in the float here we are using float or double data type we are using float or double data type whenever uh we are working with where wherever we can round of the decimal wherever we can round of the decimal [01:41:50] where we can use rounded of decimal values right so we can have four for values right so we can have four for example we are writing here 45 let's say uh multiple options right there if I can [01:42:03] round it off to 45.9 only I can go ahead and write it with 35.9 for example then I can go ahead and write it because I can round it up or I can simply get rid of the extra decimal points there and it is not going to impact my uh last [01:42:18] answer. What kind of values uh are there where we can work like this? Any kind of mathematical statistical value calculated like your average, mean, median, mode, standard deviation, variance, probability. Whenever you are [01:42:32] going ahead and calculating any kind of values like these, right? And if we have too many decimals at the end, we can round it off or cut it off that okay, I don't want too many decimal points. Let's cut it off to one or two. In that [01:42:46] case here we use float or double where we don't happen stick around here. We can round it. Then where do we use decimals? We use decimals here where my decimal numbers are fixed and no matter what happens we cannot go ahead and [01:43:00] what happens we cannot go ahead and simply round it off or um cut it off there. What can be the examples here? I will give you a very simple example. will give you a very simple example. Would you like anyone to round off your [01:43:13] compensation for the work you have done? No. So when people are actually writing uh the any kind of accounts and finance data, right? They don't round it off. They'll keep every decimal out there. Correct. Any kind of commission [01:43:29] calculation, salary calculation, bas any calculation based on any kind of accounts, they don't round it off. Right? So this is where the data type will come. Now you will also find there are many other data types too like big [01:43:41] integer uh small integer medium integer tiny integer based on the length of the integer out there and you will find more. So when we will actually encounter more. So when we will actually encounter that we will see multiple uh different [01:43:55] numerical values or numerical data types there. Then we have date and time. Now where do we find date and time? So in data time here we can of course go ahead and type date separately. We can go ahead and uh type time separately. Of [01:44:11] have the date and time stamp also with us. So, either we have date time stamp here or we have only time stamp here. This is a standard everywhere. Okay. Another standard here is that your year [01:44:25] should be separately written because sometimes if it's a time series data, we'll only have years. Now, years can be either in two-digit format or four-digit format. That is also true. That's how we also write it in general life. However, [01:44:38] also write it in general life. However, in MySQL, the date format is fixed. In MySQL, date format is fixed. So, if you're importing CSV or you are just uh uh inserting the f data inside it, right, your format should be year, month [01:44:52] and date. If your data is written like this within the uh double quotes or single quotes there, then only it will recognize this as date and time as date and time. Time is hour, minute and seconds. Similar to Excel. Uh if we will [01:45:06] go ahead and we will not type any spaces anything out there. Okay. Like this again it will be uh considered as a date and time. You want a time stamp only then it will be hour minute and seconds. It will look like this. And two digit or [01:45:20] four digit year format we all understand that 25 or 2025. You can write it anything like that. So this is your date and time here. There are few things that I would like to remind everyone. always and the command with semicolon, right? [01:45:34] And if you want to go ahead and execute a certain statement, right? Then go ahead and you can type, you can simply go ahead and execute with keeping a cursor there and pressing control or enter. Okay. So all in the script or [01:45:52] select or I will type or here for clarification or select current statement or keep your cursor there and exit to execute. You can also go [01:46:06] ahead and uh press control and enter or we can use these lightning bolts right here. Use the second lightning bolt for one statement. We use the first second [01:46:18] board here which is a cursor. Okay. Now we already know how to create a database. We have seen that we need to create a database and we can have a name there. We can go ahead and show the databases too. Right? There is one very [01:46:30] important thing that we should know. Keywords are not case sensitive. Only the keywords that we use like create database and more. These are not case sensitive. All right. So what how many databases do we have right now in uh in [01:46:44] the server to see that we will simply go ahead and type show when you type sh in the drop-own show is here we can go ahead and select show space you're looking for databases so we type databases here and we end the statement [01:46:59] with semicolon right here now see here when the cursor is blinking here I want to execute this statement either press control or enter or simply go ahead and click on this lightning bolt right here why I'm explaining this in a uh very [01:47:13] detailed manner right now because I have been asked at the start of the session that um show it again how to execute it that's why so um I don't remember your name there but now do you understand how to execute a statement write a statement [01:47:29] executed please now this temporary output that we have right now with us it can be exported to CSV this is how you get CSVs whenever you ask your organization ation give me the data. So this is from where you get [01:47:43] the data. So this is from where you get your CSV files. So my SQL exports to CSV, right? So if you want this to be exported, you go ahead and ask it that uh export this to a CSV your HTML, JSON, Excel, whatever you [01:47:58] your HTML, JSON, Excel, whatever you want, you can get it from. is your temporary out. Now next here is we we can cross it and close it. We have a cross sign here, an X here. Right? We can close it right there. Now let u now [01:48:15] let's move forward and let's learn about the select queries. Everyone let's learn talk about the select query right now we are talking about reading reading the data set or retrieving the data set. [01:48:29] Right? There is a writing sequence. There is a writing sequence. Do you remember the SQL operations? First retrieve the table. But I want to choose the columns and filter out the rows based on maybe country or credit limit. [01:48:44] Then I want to sort the data set. So first option that we have here is select. We write sequence here. First is select. Select will help us to choose columns. Right? If I want to choose a column here, then I can use get me [01:49:00] employee ID, employee name like that. But if I need all columns here, what do we do? If I need all the columns, what do we write? Yes. Very good. We go ahead and envy the restricts. After select here, we have to also tell my SQL from [01:49:14] where are we getting it. So the second keyword that we use here is from and from here we give the entire address about it. What is the address here? Address is first your schema names comes in. First you write your schema name or [01:49:26] in. First you write your schema name or database name. All right. Then we type a period there or a dot there and then we go ahead and give the table name. We go are giving the entire address from there. Okay. After from here, after from [01:49:42] here, if we want to go ahead and apply any filter, we have a wear clause. This where clause will help us to app uh help us to the filter rows from database from database directly. It will apply the filter on the rows from within the [01:49:59] database. There this is where clause. Now after where clause we have more options here more keywords here after where that is row by having order by and limit. Order by is sorting your out. Order by will sort your out [01:50:15] right. What limit will do here? Limit will uh limit the number of rows and will uh limit the number of rows and output. Number of rows and output. there right to export. If you want to see only five rows 10 rows it will [01:50:29] simply limit it. defines the source of data that we are looking to retrieve. Uh yes, you can say that. So we we are simply asking it that this container, this is the tip. So let's focus on these four clauses first, [01:50:43] right? And then we will move on and learn this. First let's start a small with select and from we have the classic models here, right? So what is the how does the syntax looks like for select here? Now we understand that very [01:50:56] easily. We have to write select. We have to define whether we want few columns or one column here and then we will ask it to select our defined columns from a schema name right there. This is how the syntax looks like. So let's uh go ahead [01:51:12] and write it here. We are going to write here select. I'm selecting from a drop-down select. Right now let's get all the columns because we are not aware about how these tables are. Select everything from see [01:51:27] here I am simply typing and selecting from the drop-down from where classic from the drop-down from where classic models. So we will type here C L A see classic models is there in the dropdown it says it's a schema there select it [01:51:39] with the tab or double click on this I have selected this now we want to define the table so we click on uh we top uh click the dot there type the dot there or the p sign you will see the list of tables inside it. So let's look at the [01:51:52] customers table right now. That is the first table here. Click on tab or double click on it and it's there. Now we put a semicolon here. After you put the semicolon here. After you put the semicolon, control enter or click here. [01:52:04] Once you click here, you will find the entire customer table is here. Also look at the output here. It says 122 rows are written. Meaning in this entire data set, we have got 122 rows. Select it from the drop-down. Do not attempt to [01:52:20] are humans we'll make these spelling mistakes very easily. Currently we are getting all columns. Okay. Currently we are getting all the columns. Uh now next we are going to see here how to select only one column. Select only [01:52:38] one column there from the data set. How do we go ahead and select only one select only one column we will type select. All right. And let's see from the customers table here. from the customers table here we simply want to [01:52:52] get only customer name and nothing else okay in that case here what do we do I will not go ahead and type that column name absolutely not okay let's use the graphic you uh our interface here when you expand the customers here you'll [01:53:07] realize you have columns right now when you expand the columns you will be able you expand the columns you will be able to view all the column names right here okay once you're able to view the all column names right here If I want to [01:53:20] type any column name here, we have to simply double click. We have to simply double click on that, it will automatically types that into the script. First everyone expand expand expand reach to [01:53:34] the customer column names here. Now after select here when the cursor is blinking, I'm going double clicking on customer name and see it appears here. Now from where do we want it? So we will type from again. We'll ask it to go to [01:53:46] classic models again and from classic models go to customers again and it will only retrieve one single column for us. In the similar manner if we want multiple columns in the similar manner if we want multiple columns to be [01:54:00] retrieved what do we do? We go ahead and type select again and this time we will simply double click on multiple columns. For example double click on customer name. Next maybe I'm looking for their [01:54:13] Next maybe I'm looking for their credit limit here. So keep typing comma here and we are good. After this again I can copy paste from here. [01:54:25] Why to write it again? So copy and paste it. We can paste it in another line also. Why? Because you're terminating it here. So it understands that unless the here. So it understands that unless the termination doesn't come this entire is [01:54:38] one single command. So if my if I keep my cursor right here after typing this in two lines and I execute right here you will find we have customer name country and credit limit right here. First try it out for one column then [01:54:52] give comma and try it out for multiple columns. We are using the hashtag sign here to tell SQL that this is a comment I'm writing. It is not a comment to be executed. It is not a command to be executed. Is it? It's a message that I'm [01:55:07] writing here for our own understanding. We use the hashtags or the hyphens. Question number why it is there? What is a comment? Why do we need it? Because we need to differentiate between a command and just a message that is written for [01:55:21] our own understanding. This message is written for our own understanding. SQL doesn't need it. All right. Uh so now we have one column and multiple columns. Whenever we are writing this here, this is how we are writing. Okay. But there [01:55:35] is a way to type it. It is not a functional language but it does have a format to be written since we are new and we don't know that here. So go ahead and uh select the and uh select these two lines here and go ahead and click on [01:55:51] this brush right now. This is the beautify brush. What is it? It is the beautify brush or uh the shortcut here is control V. So beautify or you can is control V. So beautify or you can call it reformat. It is beautify or [01:56:03] call it reformat. It is beautify or reformat here in a separate a different manner in a proper functional language. So uh see here how does it look like after beautifying it. [01:56:18] So I'm going to select this entire command and click on this paintbrush. Once I click on the paintbrush, this is how it looks like. Select properly how it looks like. Select properly spaced name from and properly spaced [01:56:31] address right there. Okay. Uh you do not have a database called as employee table. Sorry, you don't have a database called as first database. This is the way it should be written. Which one looks better? First or second? First one [01:56:45] is better or the second one. This is how we have typed it. This is how it has beautified and written it. Which one looks better? Right. Absolutely. The second one looks better here. Right? However, it is better to read. If I [01:56:58] visit revisit this particular script, then it will be easier for us to read and understand. However, it is not that easy to write. So, writing is easy here. But when we want to revisit it, this is the better way to do it. Just a way for [01:57:12] you. If you're writing a two long queries, always verify this. It will be easier to read. So, let me also write with you here. So, here we go. I will with you here. So, here we go. I will ask it to select here. Select and we [01:57:25] will simply double uh click product name comma double click on buy price here and now we know we can simply copy paste from classic models customers but we don't want customers do we know we are looking for product so we have to [01:57:40] write classic models again and get the products here we can choose to beautify this and execute it here and we get the answer there is one issue right now here since we are working on one single database. [01:57:55] All right. Every time whenever we are writing a query, we have to write classic models classic models multiple times. So how do we get rid of that? We go ahead and we set this set the database [01:58:08] as default as default. Let's see. Whenever I ask you here that go and get the products table, go and get the uh employee table, go and get the orders table, whatever table I will tell you, go ahead and go to the default database. [01:58:22] How do we set our default database? Here we will type here that use classic models from here on. Meaning from here on go ahead and use only classic models as our default database. When we go ahead and execute this here, our [01:58:37] database turns into default database. And how do we know it has turned into default database? See here I writing this here. I am executing it right here. The moment I execute this classic models will turn in [01:58:50] bold. Right? Now another way is to set this as a default. We can right click that whatever table name we write, it will go ahead and get that table for us. That is default schema. Here you can also doubleclick on it and turn into [01:59:06] default. So for example, if I double click on first database, it will turn bold. It is default. If I double click on classic models, it will turn bold meaning it is default. So there are multiple ways here to set the database [01:59:19] as D. It is a very simple thing that we are writing here use database it is setting this as default and observe that it will turn into both once uh we will just do that here if I go ahead and say that okay select [01:59:33] everything from and let's say we need from orders so now I will not type the table name see it very carefully now I we are not writing classic models name here I want to directly write the table name here [01:59:47] for example orders so if I want the orders table here I will simply go here and double click even on the name of the table see here select everything from I anymore because it is set as default so we go to the orders and we double click [02:00:04] automatically orders will be written here we can end it with semicolon here execute it and see the orders are here okay go ahead and get this please without writing the classic models I'll [02:00:19] wait for your responses. From now on, whatever we type in this script, right? We don't need to type the classic models. Again, if you're using any table that is present within this database, how do we type the table name? Now, [02:00:34] now we can simply double click here. We get the table name right there. Observe that here selected from is written in caps, here selected from is written in a smaller case. Both are working fine. Why? Because it is not case sensitive. [02:00:48] We will go ahead and now we will see how do we filter the rows here. To filter the rows here, let's go ahead and uh we have multiple product right. So right now we will go ahead and get the product name and buy price. We'll go ahead and [02:01:02] get the product name and buy price. However, this time we will get the buy price and the product name. Product name. Okay. Which are which are lower [02:01:14] name. Okay. Which are which are lower than 100. Okay. which are lower than 100. Meaning buy price is lower than 100. [02:01:27] models product. We already have this here. So let's write it again. So we are typing here select. We will go to products table. We will say that get the product name, now get the buy price from where? From [02:01:44] products directly. So we double click on products. Now we have the products here. Now if we go ahead and execute this, we can see the buy price is right here. Buy price is right here. Oh, I see everything is already below 100. So [02:01:58] everything is already below 100. So let's make it below 50. Okay. Wherever the buy price is lower than 50. So how do we introduce the filter here? After from here we will go ahead and type where clause where [02:02:12] where is the filter we want to put? We want to say that go and check the buy price. Go and check the buy price. So we can go ahead and double click on buy can go ahead and double click on buy price again [02:02:28] less than 50. So we can go ahead and use the less than sign here. So buy price is less than 50. 50 is a number. Okay. So when we go ahead and execute it now this when we go ahead and execute it now this here execute this here how many uh rows [02:02:43] we have right now. So originally we had 110 rows see here originally we had 110 rows once we filter it out we have only 48 rows. 48 rows. We have only 48 rows. See here. [02:03:04] Yes. If I have only 48 rows, uh can you tell me that among these 48 where do we have the highest buy price? Which product has the highest buy price? To find the highest buy price, we have to sort it. How we can sort it? I'm going [02:03:18] to copy paste the exact same thing here. After where we can ask it to order by order by which column? By price. Order by a certain column. So we filter by [02:03:30] by a certain column. So we filter by order by we are sorting also by order by sort by sort means arranging my data set. So in the order by if I ask it to order by by price here and we execute it right here. See this is the lowest price [02:03:44] we can see. So right now we are sort um by default it is will sort or arrange the table in ascending order in ascending order or you can say in [02:03:57] ascending order or you can say in increasing order right? If you want to sort it in descending order, [02:04:10] order, then we will simply go ahead and define that get the descending order. So after buy here, after buy here, now we want to set that [02:04:23] then descending order. So for descending order, we will simply type D here. If we type dc here and we execute it right here, we'll find the highest [02:04:37] value. If we don't define anything after order by and we execute it, we have the lower price. So what we are doing? We're applying the filter and we are sorting our output too. Go ahead and find it out. First filter it out. You already [02:04:52] have the filtered list. Now what you will do next? We'll move forward and will do next? We'll move forward and sort your value or sort your result out. see them one by one and this is the less than sign. It's a simple logical [02:05:05] operator everyone or you can call it a conditional operator. Go ahead execute conditional operator. Go ahead execute them one by one and let me know we still have less than 10 responses. Order by here right? What is order by? Meaning [02:05:18] sorting my data set arranging my output. So if I simply go ahead and ask it to So if I simply go ahead and ask it to sort it uh ask the result just like this right it will give me there is no sorting order is your table arranged [02:05:31] output arranged in any manner is it alphabetically arranged >> or is the buy price arranged in some manner not arranged so what we do here we will ask it that why don't you order it as per the buy price when we ask it [02:05:45] to order by the buy price by default it orders in the ascending order increasing orders in the ascending order increasing order of the buy Yes. But what if we don't want this? What if we want it descending order, decreasing order? So [02:05:57] we go ahead and we write the exact same thing. We ask it to order by by price. But in front of that we write descending. D. We define that right now when you're arranging the table by the with the column by price. Make sure you [02:06:11] sort it in the descending order. So it will sort this into the descending So it will sort this into the descending order. the again I have a question for you. Okay. Can you get me the customers [02:06:26] customers from country France from country France and uh sort uh the from country France and uh sort uh the name in ascending order. [02:06:45] for this please? What table you need? what columns you need. First tell me which table we should go for from which table we can get this uh information. So good. Which columns do we need in the output? [02:07:00] You are telling me from customers is done. Right now which columns do you need? Which columns do we need? Country. We do need country. What else do we need? Who are the customers? Right. Okay, we need [02:07:15] customer name too. Is this the output we are looking for? Very good. Mana, is this the only output we are looking for? No, we need not from all countries only from France. So now once we have selected everything here after from we [02:07:30] need to go ahead and apply where clause. Why? Because we are looking for country should be France. So when we when we are looking for that here we will go ahead and double click on country again and this time we use another operator that [02:07:43] is equals to sign and now we will go ahead and type France. When you're typing France here since France is a text it has to be encompassed inside either single quotes or double quotes. Right? So whenever you you're writing a [02:07:58] text or a date text or a date it has to that then only it will able to when you go ahead and ask country from France here in the output you will see [02:08:12] you have only 12 rows here. We have what only 12 rows here right now we want to sort it by here. So now the country name is similar. So how do we sort this by is similar. So how do we sort this by here? So we can order by we can order by [02:08:25] here with customer name. So we will go ahead and double click customer name right here. Even if I don't write ascending it will be in the ascending order that is A2 set. You can also go ahead and define asc that is ascending [02:08:38] that will also set it in the ascending order. So whatever we we are looking at order. So whatever we we are looking at here are okay all these are your all conditional operators. So we are depending conditions individual [02:08:51] conditions rather. So these are known as conditional operators or logical operators you can say. What are these? These operators are equals to, less than, greater than or any combinations of them. Right? This is not equals to. [02:09:05] You can also write not equals to like this. everyone. Uh this is how we use them by to define one single condition. Right. I [02:09:20] will go ahead and uh write this in the code share. You can get it from there and let me know when we can move forward because uh next I what I would like to know here is list of countries. What I want I want [02:09:35] list of countries. What I want I want list of countries customers belong. List of con uh countries customer belongs to. This is what we want to get. Okay. I want only one column. I want only one column that is country. So we want list [02:09:50] column that is country. So we want list of country. So let me know when we can move forward. I will waiting for responses. Okay. [02:10:08] operators. Okay. How we can get to the uh country column only? How we can get only country column here where we can have all the countries everyone. So this we want to select. We want to select country from where from customers here [02:10:24] right? If we want from customers here we execute this here. This is the country list. Is this correct? Do we have do we have here 122 countries? [02:10:36] No. There are duplicates. There are repeating items. Duplicates are there. So how do we get rid of duplicates here? We get the unique list. So what exactly we're looking for is to get the unique list. To get the how do we get the [02:10:49] unique list here? So basically we want to go ahead and remove the duplicates, right? We want to remove anything that is repeating that is duplicates. So we will go ahead and copy paste the exact same thing but we use now functions [02:11:03] right functions and keywords. A keyword here is distinct. The keyword here is distinct. Distinct means unique, right? So if I go ahead and write here [02:11:16] distinct. It's a keyword, right? If I use the word distinct here and we execute it right here. Now we see that we have 27 rows written and there are 27 [02:11:28] countries there. is distinct can be returned either as a keyword or sometimes you will also find distinct return or used there as a function. So [02:11:40] will be followed by the opening parenthesis and after the country it will be closed within the parenthesis and closed like that. So sometimes this. Both are absolutely correct. They will get you the same answer. Okay. So [02:11:56] this is how we get the distinct here. So list there. If I ask you how many countries are there, you have to look at countries are there, you have to look at the output, right? 27. Uh now what if I [02:12:10] don't want to see the list of all 27 countries, right? We don't want to see the list of all 27 countries. I simply want to know. I simply want to know that want to know. I simply want to know that how many countries how many number of [02:12:25] countries number of countries are there. How many number of countries are there where we are shipping our product. I simply want to know how many number of countries mean total number of countries mean total number of countries. To def [02:12:41] get the total number or answer for how many the function is count. The function many the function is count. The function is count right. So how do we use this count here? We want to count how many total number of countries are there. So [02:12:54] we already have the list of countries. We want to simply count this list of We want to simply count this list of countries here. So here I'm going to select this here. Select and copy and paste this here. And now we will ask it [02:13:08] to go ahead and count. We'll ask it to go ahead and count. Count what? Count is a function everyone. So you open the bracket here because count is a function [02:13:20] and inside this count here if you will go ahead and type count country directly you'll get the answer here as 122 is wondering when 122 is correct answer. No [02:13:33] we know the correct answer is 27. So we have to go ahead and say that no don't have to go ahead and say that no don't count column entirely. You go ahead and distinct country there and then you count. So see [02:13:48] distinct is written in blue color right? It is a keyword. It can also be used as a function but count is a function also. See that after count immediately it is followed by the opening parenthesis. Now once we execute this we have count [02:14:01] distinct country as 27. Right? So it can be written like this or we can simply go ahead and write it like this also. So we will have the count here, open bracket will have the count here, open bracket here and close bracket right here. If [02:14:15] you type it like this then then also we get 27 is the answer. However, is this column name looking good in the output? I will give you enough time. Answer me just one thing here. Is this column name looking good to you? No. So we need to [02:14:31] rename this column, right? We need to rename this column name here. to rename this column name here in the output we will go ahead and give it an alternate name and we call that name as alias right so als is nothing but an alternate [02:14:46] name we are giving to that particular column how do we rename the column here to rename the column here so this is our select query right I will keep from in the next line I will keep from in the next line here and once we will write [02:15:01] the entire function after this say we'll go ahead and type as s here and within the double quotes currently I'm writing what we have here total countries right [02:15:13] total uh number of countries here so I'm writing here total number of countries right there so if I go ahead and write like this total number of countries within these single or double quotes here and I execute it right here it will [02:15:29] here and I execute it right here it will rename it right there this is called as however Ever there are multiple ways to type the areas here. Right? What is the other ways here? So let me copy and paste this here. Another way is that I [02:15:44] quotes. So I can directly go and type the name here. But if I go ahead and directly type the name here. I uh I'm not allowed to give a spaces anymore. number or something. So I have to go ahead and write total countries or total [02:16:00] countries right here or count of countries. So even if I will not give the double quotes uh here we I am good. I can give the name of the column here. It will be renamed but we cannot give a spaces anymore. If it is in double [02:16:12] quotes we can give a spaces. We can type it started with number or anything else. Okay. It can be alpha numeric. What is the another way? Another way here is we every time typing as to define that we are giving an alias or alternate name. [02:16:27] Even if I don't write it here and I will directly write total countries as it is and uh let me go ahead and change the name here so that you can see the in the [02:16:40] output. So if I go ahead and execute this see it is renamed again. So even if I don't type as SQL understands that we are renaming a column. So go ahead count it and renaming the column in the output. That's alias. It is uh very [02:16:54] output. That's alias. It is uh very basic. You always go ahead and name it. This is another way to type it. So even if you're not writing as whether you give the double quotes or not, okay, you will still get the double quotes are [02:17:08] given when when we want to give a spaces in the name. However, in the standard practice, we don't give a spaces in the name. Even if we are giving the double quotes. Okay. Preferred way is this. [02:17:20] Preferred way is preferred way is this. There's a reason for that because uh as a beginner okay if you will see something written directly here you might try to find uh this as a column name here. So to avoid the confusion to [02:17:33] avoid the confusion okay we will always type as and we will always give the double quotes like this makes us makes it easier for us to recognize the names [02:17:45] here I have a question for you an exercise for others I'm waiting this is exercise for others I'm waiting this is just an exercise [02:17:58] category so product line is one corner. [02:18:11] This part will is going to remain similar here. So I'm going to copy paste and now we have to work on the products table everyone. So we will go to products table here. Inside this we want to count the distinct product line. We [02:18:25] double click on product line here. We will name this as yes you can write in any case there doesn't matter and uh I'm writing this here as writing this here as total product lines [02:18:44] product table and here we currently we are using functions like uh count okay deviation and a lot more maximum minimum Right? So we we can go ahead and use these kind of [02:18:59] we we can go ahead and use these kind of functions in the similar manner. that. Right? So uh not only functions, we can simply use mathematical operators too. We can not only use the logical or comparison operators and functions here. [02:19:15] We can use mathematical operators too or simple mathematical calculation. So with the help of these here we can actually add the entire calculated column in front of the out. So let's take an example here. So we already have [02:19:30] the product table with us right. We know how to extract product ID name and after buy price we have one MSRP column also there. That is the standard pricing that we are selling it for. Correct. We can go ahead and get these three columns [02:19:43] go ahead and get these three columns from there. However, now uh the company has decided that whatever uh is the standard price is there, we will standard price is there, we will increase the standard price by 10% of [02:19:55] every product. They will increase the standard price by 10% for every product. So they want to go ahead and add here a new calculated column called as new MSRP in the out and that new MSRP will be simply 10% more [02:20:11] than the old MSRP here. Right? So this MSRP now will become old MSRP. This MSRP MSRP now will become old MSRP. This MSRP will become old MSRP. [02:20:25] new MSRP okay we can write it like this that MSRP that MSRP old MSRP plus 10% of old MSRP this MSRP formula um sorry the column there can we write the equation like this MSRP plus [02:20:41] 10% of MSRP so that we get the increased price for new one can we reduce this since uh we know with the help data type that we have seen at the start of the session today that it doesn't recognize that percentage sign right so instead of [02:20:57] that percentage sign right so instead of typing the percentage sign and uh off here can we reduce this to.1 and multiplication we can do that here right because we want the uh want uh the calculation to be done so now this looks [02:21:11] like that get the MSRP uh value from MSRP column in the individual row MSRP column in the individual row multiply that with.1 one and add it to the older MSRP. Very straightforward. But if we go ahead and reduce this here, [02:21:26] we can get the common value out to reduce it to a smaller version. So once we get the MSRP out, we are multiplying it with 1 + 0.1. Meaning we can simply it with 1 + 0.1. Meaning we can simply write the new MSRP like this. MSRP [02:21:41] multiplied by 1.1. Correct? So we have this calculated column right there. So how do we write this calculated column here? So we go ahead and say select right from where [02:21:54] are we selecting? I'm writing from first here. Okay, I'm getting this out first from the products table. Okay, I'm getting this out first. Then I'm typing select so that I don't forget which table we are working on. So in the [02:22:08] select here, first we need product ID. So this is product code right there. And I'm going to rename this here as product ID. Easy. Right after that what we are looking for comma we want product name also there so [02:22:22] comma we want product name also there so this is name let's take this here as name only since it is product ID it has to be the product name too after product to be the product name too after product name what we want MSRP so this is MSRP [02:22:35] but this is old MSRP so let's name this here as old MSRP [02:22:47] is not a column that exist. It's a calculation. So we write the calculation here. How do we write the calculation? We get the MSRP here and we ask it to multiply it with 1.1. That's what we have seen there. Now this entire [02:23:01] calculation we encompass it inside the parenthesis here and we call it that parenthesis here and we call it that name as new MSRP. Name as new MSRP. Right? I will go ahead and notify this. I will go ahead and execute this first. [02:23:15] I will go ahead and execute this first. See here we have got product ID, name, old and right. I will also go ahead and beautify this here. I'll go ahead and beautify this [02:23:28] here. I'll go ahead and beautify this right. can choose any name that you want. You don't have to stick to these names only. [02:23:42] You can give any alternate name that you want. Also observe I'm not using double quotes. I am writing single quotes here. Okay. Uh remember that whenever see how everyone observe the rows you're getting 110 rows right? So we are extracting [02:23:58] each and every number here. Each and every row here and this is a row wise calculation. We are calculating it for each and every value in each and every column. And do you see any difference [02:24:12] between the old uh difference in the format of old and new MSRP? format of old and new MSRP? Yes, we want two decimal here also. Yes, we want two decimal here also. How do we get uh only two decimals? We [02:24:26] truncate the other decimal points here. Correct. We round it off. So the function here is round. Function here is round. And we will say then we go ahead. We type a comma there and type two. Now [02:24:42] once we execute this one here, we get two decimal points. Right? Yes, our functions are exactly similar to Excel. You'll not find it is very far from there. You need to get the average price for vintage cars only. [02:24:58] You'll find the vintage cars in the product line column of product table. product line column of product table. Product line column of products table. [02:25:16] and price means average MSRP. Get the average MSRP. Okay. But only for the average MSRP. Okay. But only for the vintage cars. Vintage cars is the name of a product which is present under the product line column. So if I show you [02:25:30] product line column. So if I show you the product table here, [02:25:42] vintage car. See okay and it is not case sensitive. You can write it anyways just the spelling should be similar. [02:25:57] That's the question everyone right? So first thing you always figure out from first thing that we always figure out is from right. So what we should type in front of from here product we're aware about [02:26:12] that. After from here what is the second thing you will look for? What do we want in the output? What do we want in the output? We want average. output? We want average. Average of what? Average of MSRP. [02:26:27] That is also fine. Other than that, do we have any other requirement? We have average MSRP from the products table. What is the third requirement here? Do we have any other requirement? Right? We want to filter only for vintage cars. [02:26:42] How do we give the filter? Where clause. But on which now where clause here on which column we want to put the filter? What is the name of the column? Where we want to put the filter? We have to give the column name, right? [02:27:00] Because product line is the one that holds vintage cars. down it will be easier for you to write. So here if we go ahead and type select [02:27:16] now. So here we move here. Select right in select here we want to calculate the average. So let's calculate the average. Average of what? Average of what? MSRP right from where? From the products [02:27:30] table right here. This is products table. We want to apply This is products table. We want to apply the filter. We will apply where there on the product line. Product line is equals to what? Vintage cars. So we [02:27:46] cars here. First check the spelling in real time and then type the vintage cars here. Yes, that's it. here. Yes, that's it. However, this is just average MSRP. This [02:28:00] is just average MSRP written here. You can type it properly the name there. And see there are too many decimal points. So let's round it off. So we will round it off the average. Let's round it off to two decimal points [02:28:15] there and give a proper name here. So this is average MSRP. the proper name also here and we have rounded off. Once we round it off give a [02:28:29] proper name. See how does it look like better. But this output doesn't tell us it is for vintage cars. So what we can do here is we can type a comma here. And [02:28:41] we can ask it to get the name of the product line too. And when we execute product line too. And when we execute this here we can see one single name is written because filter is there. Yes. Because we need only one single output [02:28:53] here. It is a one single number output. We have just one single number here. While typing round to clean the data, I had overritten the average function. Yes, that happens. Okay. If you are selecting [02:29:07] it from the drop-down, all right, then you will get uh you will replace the upcoming or already written values there. So you what you're doing is you're all rounding of MSRP, right? So you are going to have error and very [02:29:22] different output. You'll have multiple vendage cards and each and every row will be out because right now we are asking you to calculate an average. What calculation there. So if the average will not be there, it will simply go [02:29:36] ahead and extract each and every row. Now it is not aggregating any. Observe that in all the three conditions and every condition that we have seen yesterday, we are only going ahead and uh filtering it by one condition. We are [02:29:50] condition. What if if this is the orders table, right? In the orders table here, if I ask you to go ahead and only get the orders, okay, which are on hold. Which are on hold. What you will do then? So [02:30:06] we are now going to have two conditions. We are saying that I want the orders which have been placed after first month 200 five year and they should have and the status should be shipped. has already shipped the status here and the [02:30:21] have here we have got two conditions here. So how do we ask SQL to go ahead and uh give us two conditions or filter the data by two conditions. This is [02:30:33] where logical operators comes in that is and or and between. This is where your logical operators will come in that is and or and between. What and will do here? This will help us to look for two or more conditions. And have you have we [02:30:49] heard about and end or in between? I'm sure we have heard about it and or in between. Not in SQL but somewhere else. If you are from engineering or just uh you have learned Excel anytime we have heard about these conditions and or in [02:31:04] between and in general cases also we do use that in general like I want apple and mangoes from the market. Get apple or mango from the market. there's a difference. So in general also we use that logical gates and excel and at many [02:31:19] places in general also we use it and here first. So what will and to and means I need both the things here I need apples also I need mangoes also. So in one single row here it has to check that in this per one single data row the end [02:31:34] all the conditions should be matching meaning the order date should be less than 2005 and this status should be shipped to both the conditions should be [02:31:46] go ahead and see how we are going to type it here. So we will uh get the same thing here which orders have been placed. Let's get it right here. [02:32:06] orders have been placed after first March and the status is ship. How do we write it? So we know that this is select and let's figure out from first. So from and let's figure out from first. So from we have orders right what we need here [02:32:20] in the orders table. So let's get the order number comma. We have order date that's what we're looking for and the status is shipped. So here we are till here it is fine. Now we go ahead and we type where. [02:32:34] Now inside where first condition is that our order date 1st March 2005. Now we know how to type this. This needs no explanation. Here is [02:32:47] this. This needs no explanation. Here is one more option. Now one more here and so we type and here and we ask it that now your status should be equals to shift. This shift that we are typing it's [02:33:00] purely uh uh not case sensitive. You can type it as you want it. The spelling should be correct. Now if I go ahead and execute it right now see here we get both the conditions are matching. Both the conditions are matching. [02:33:23] here and is helping us to go ahead and get the go ahead check two conditions in one single order. So if I go ahead and verify this this is how it looks. You're getting me the orders which are placed after 1 March and the status is shipped. [02:33:39] after 1 March and the status is shipped. But now I want the orders that has been But now I want the orders that has been placed after 1 March and check for check for whether status should be equals to ship [02:33:53] ship and resolved. So whether the state uh so status should be shipped and resolved. So there if you will see here there are So there if you will see here there are uh status like resolved here. Okay. [02:34:13] So we want the both the type of rows here is shipped and resolved both. How do we get that? Why are we using or? Let's understand. Reason is in one [02:34:25] single row it cannot be both. So we have to go ahead and write or here. So that it goes to every row and check either it is shipped or resolved. So I will copy [02:34:37] and paste exactly this thing here. And what we type up we type on. what we type up we type on. So here the status equals to So here the status equals to status equals to result. [02:34:54] this order date greater than 1st March 2005? Is it holding on 2005? Is it holding on with the status shift or resolve? Are all the order dates after 1st March 2005? [02:35:17] Are we having all the order dates after 1st March 2005 everyone? Yes or no? 2003 is in the first row. You don't have anything after 2005. March 2005 [02:35:33] anything after 2005. March 2005 specifically. You have January 2005, November 2004, October 2003. This order date is not holding up anymore. Yes. So when we are writing the [02:35:48] or condition here, right, the and condition here, this order date is getting uh thrown out of the window. Why? because it is focus right now with the and when and or what is happening or is getting the privilege. So first it is [02:36:04] resolving the or and then it is resolving the and since and cannot be resolved anymore. It is uh not holding on to this filter because what we are writing here is everything is in succession. We are saying check for [02:36:17] order date and shift after that check for or. So what it has done here is it for or. So what it has done here is it has taken all these two as uh here and then it is checking or status equals to resolved. So this is a problem. So what [02:36:33] resolved. So this is a problem. So what we need is that order date should be doesn't matter if it is shipped or resolved right it has to be after March 2005. So we have to simply go ahead and encompass this inside the parenthesis. [02:36:47] Once we do that now it understand that oh okay you want this to be held true oh okay you want this to be held true always and I have to check whether this or this. So this is what we need. This is how we write. So once you keep it [02:37:03] inside the parenthesis here it will go ahead and check between these two. It will apply the or only between these two. It is not going to apply the or something to be held with true keep it [02:37:16] separate. When you want something to be held true separately, just keep it inside the parenthesis. Now, both conditions are matching perfectly. Okay? Just simply go ahead and keep the give the parenthesis around it and it will be [02:37:28] the parenthesis around it and it will be resolved. writing here is please look on the screen here. Okay. Find a product whose [02:37:41] MSRP is greater than 100 but less than 180. How many conditions are here? Two conditions. So if I have and both the conditions should match. It has to be greater than also and below 180 also. Both the conditions should match here. [02:37:56] Both the conditions should match here. So we can use and here. So first let's figure out from here. So from the product. So this is the from products. And uh we are looking now in the product here I don't need all the columns. So we [02:38:11] here I don't need all the columns. So we get few select columns here. Let's get the product name, product code, and here is the MSRP, right? This is what we are looking for. Now, we go ahead and type where in the [02:38:25] where this is MSRP right here. MSRP is greater than 100. So, we can go ahead and type greater than 100. And again MSRP because [02:38:37] there are two conditions. So, MSRP less than one. Less than one. This is going than one. Less than one. This is going to give us see here the MSRP between 100 and 180. So what we are looking for a numerical range. So whenever we have a [02:38:51] between. We can apply between here. Instead of and here we could have written between. How do we type between? So this much [02:39:03] will remain similar. This much will remain similar. After MSRP we'll simply remain similar. After MSRP we'll simply type here between and we'll say MSRP should be between 100 and 180. So between cannot be used without and the [02:39:16] type 100 and 180. Yes. Now in both the cases here the answer is going to be similar. Now answer me here. Are we including 180 when we are filtering with the and function here? Are we including 180 when we are typing and here? Right [02:39:31] now we are not including upper and lower limit here. If I want to do that, we can go ahead and write equals to here. If I want to include it, I have to type equals to here. Then I'm including upper and lower limit. Meaning I have the [02:39:46] choice. Meaning I have the choice here. It works as Excel, not as programming language. As you have seen before, it work as Excel. It has to go back go to each column every time to filter something out. So we have to type MSRP [02:40:00] equals to MSRP equals to when we talk with between everyone in between upper and lower limit are automatically included. Upper and lower limit in the range are included automatically. So when you're using between and here it [02:40:15] will include upper and lower limit both. So this is your between and and it will So this is your between and and it will be included automatically. We don't have exact a 100 or a 180 here. So we cannot see the difference but yes it works like [02:40:28] see the difference but yes it works like that. So this is and and between here this is the difference between and and solve this [02:40:51] click here and look at the spellings here first. See this correct? Your logic is absolutely correct. [02:41:04] we are talking about from customers here, right? So of course we don't want to select everything from customers but we uh are looking forward from customers we uh are looking forward from customers right here. [02:41:22] Let's select few columns here. So customers uh this is customer name here and let's get the country that's what we are looking for and from customers. So we are good with that but we don't want all the countries of course we don't [02:41:35] want all the countries we want select few. So we'll go ahead and use all function here because in every row we'll check for individual country not all. So now here we have to go ahead and say that where where country is equals to [02:41:49] that where where country is equals to let's say USA here or or country equals to again we have to go ahead and type it again. Why? Because this SQL here will treat this country as three separate columns. It is not going to treat this [02:42:02] as one single column with multiple filters. So every time it will go ahead and check it uh check it as it going ahead and checking for another column right there. So when you're typing this here, it is not case sensitive but you [02:42:15] have to make sure that the spelling is absolutely correct. So we have to go ahead and type or or and of course you get the correct answer here and that is going to be see USA, France and Australia right that is 53 rows. Okay, [02:42:28] that is 53 rows here. So if your spelling is correct you'll get 53 as the answer. Most of the time you'll be making mistake at the uh spelling of Australia. But just imagine what if you have 10 conditions to check for. What if [02:42:42] if you had 10 conditions to check for that in one single column then it will it is going to be too hard to type and we will make mistakes in typing the spellings right there because you have to check and write and copy and paste. [02:42:55] to check and write and copy and paste. So here in this in this time here we go we go ahead and choose an operator here. What is in operator here? In operator What is in operator here? In operator will help us to give the entire list at [02:43:09] one place. So in operator acts like or right. So if I go ahead and type in operator here inside this we can go ahead and write the entire list and what it does is it goes to the country column and it will check from the list if any [02:43:22] one of that is present. So it automatically work as odd but it is convenient to write. How do we type n operator here? So we will first go ahead and get the column here. In front of column we write N and inside in here we [02:43:35] will go ahead and type here value one comma value two and value three. So if it is text we will type inside uh the quotes. If it is not a text we will simply leave it as it is. So this is how in operator works in [02:43:51] is. So this is how in operator works in operator helps us uh uh helps us help us to type the filter here properly. So this will remain exactly similar here and after country we'll simply type in and inside the India we can give the [02:44:06] entire list. So this is our USA I'm going to copy and paste here comma this is France here and now near Australia. So we don't have to type or again. We don't have to type country equals to again. We'll type that [02:44:19] only once. And we don't have to type or anymore. N automatically takes part of that. And we still have the 53 roots right here. right here. So this n is actually working as [02:44:32] So this n is actually working as multiple or like an operator. Like an operator we also have any operator or operator any all in exist. We do have that too. For now, we'll focus on N. So, let's [02:44:46] look at the employees table right here. In the employees table, what we have? We have got employee number, email, office code, and the job title. In the job title column here, do we have managers? Only type of manager will not work. Why? [02:45:01] Because there is nothing that is exactly equals to manager here. So we are equals to manager here. So we are currently not trying to find out uh the exact phrase inside a data point. Correct? What we are trying to find out [02:45:13] Correct? What we are trying to find out is a part of the a part of the string. We are trying to filter out the rows from the part of the string. So we want wherever manager is written in the entire phrase in this entire string [02:45:28] wherever manager is written based on that you filter out that row. Correct? that you filter out that row. Correct? So in this case here no equals to comparison operators will work. This is where the comparison op this is where [02:45:41] the wild card comes in. This is where we have wild cards. Wild cards are activated with like operator. So whenever we want to use the [02:45:54] wild cards here and we want to get these a small part of the string out, we first activate the like operator. So in the like operator here there are two things we can work with. One is the percentage sign. [02:46:13] is the underscore sign. What is the percent percentage sign here is for variable here that you will have variable uh what do you should say here? You should have variable variable letters letters and symbols, right? And [02:46:29] this letter here simply means fixed number fixed number of spaces fixed number of letters or blocks. What does that even mean? [02:46:42] Let's understand it. So if I go ahead and I will type here percentage after percentage for example, I will go ahead and type manager. Type manager. What does that mean? It means here that if I am typing if I'm typing percentage [02:46:58] am typing if I'm typing percentage percentage before manager so we are uh communicating to SQL that see the phrase should the entire phrase or entire should the entire phrase or entire string should end by manager and before [02:47:12] manager before manager any number of variable can come there any number of be a letter, a symbol or a space, anything can be here but it should be [02:47:24] before manager. If we go ahead and type that okay this is manager and if I type percentage afterward so we are communicating to SQL that this particular data point should start with M andJR and end with anything end with [02:47:39] anything. There can be spaces and any number of variables anything will be there. If we go ahead and type percentage first and then manager and then percentage again, we are simply saying that in the middle [02:47:53] somewhere in the middle of the uh of the entire phrase this particular words should be there. It doesn't matter what it starts with what it ends with. Okay, [02:48:05] this is percentage. This is percentage. So if you want to go This is percentage. So if you want to go ahead and extract manager from here what we should be using then should we be writing percentage and manager [02:48:21] or we should write manager and percentage or should we write percentage manager percentage we are 2 to one right now this is the correct answer. Why is this the correct answer? Look at this manager is [02:48:35] somewhere in the middle. At the end also we have some variables. Yes, Samr. So this is our V operator. Let's go ahead and get the employees table right ahead and get the employees table right here. [02:49:00] Now if we select everything here from the employees table, let's go ahead and get employee number. and their job title here from employees and now we want to go ahead and give a filter here. So inside the web we'll say that see I want [02:49:14] to put a filter on job title job title should be like similar to percentage manager percentage the spelling of manager should match there manager [02:49:26] percentage it should be encompassed inside the single quotes once it is like inside the single quotes once it is like that we got a manager is right here [02:49:40] supposed to type these things here like you're not supposed to type the keywords or the name of the columns or name of the tables here. I would like to remind you you simply need to double click. Okay, please double click. Do not make a [02:49:54] spelling mistakes. Double click please. Yes, somewhere are three records. I would like to see now which employee is sales manager. What will I do if I will I'm only seeing these three here. I want to know who are [02:50:09] these people. That's fine. Otherwise, how does it make sense just to see three titles here? I need these people detail. I want all the managers. Maybe I want meeting that this meeting is only for the managers. We should be actually [02:50:23] getting first name and last name also and their email ID also because I need right? They will call these people and tell them to report to somewhere. Okay, Percentage wherever percentage is written, it will look for multiple [02:50:38] characters. What it will look for? It will look for multiple characters or variable characters. Any number of characters should be front there can be any number of characters. If we go ahead and type [02:50:53] manager percentage meanings it should start at M andgr and enter with any number of characters. We don't care. And when we are writing percentage first and then percentage here meaning start and end we can have any number of multiple [02:51:07] variable characters that's what percentage is. Now next here is underscore sign everyone next here is the underscore sign right there it is fixed number of letters or you can say fixed number of characters. What is [02:51:21] meant by fixed number of characters here? It means that if I will go ahead and type underscore once. If I go ahead and type underscore once here. So basically we're asking it to get to the one letter. So if I go ahead and write [02:51:35] that the there should be two letters at the then there should be a letter O. Then there will there should be one more letter here. So we are looking for a letter here. So we are looking for a letter maybe it is from. [02:51:50] So it can have only one character. So this is the underscore. This is the underscore. One letter here. This O is something that a pattern should we are looking for a pattern. So all the in the entire column all the um data points [02:52:06] following this pattern here with having third character as O will be extracted out or filtered out. So this is what underscore means. It means one means one character. One means one character. One means one character like that. Yes, you [02:52:21] can use combination also right? But most of the time this is something that we use. This is something that we use, right? So let's see an example here. Let's see an example here. For some reason, let's say, for some reason, [02:52:33] let's say we want to go ahead and find a customer whose first name is starts with letter M, whose first name is starts with letter M and should have exactly three characters and should have exactly three characters. So we want a customer [02:52:49] whose first name it starts with letter M and not only starts with letter M it should have exactly three characters. So how we will go ahead and write this here. To write this here we have to go ahead and write that start with M and it [02:53:02] should have exactly three characters here. So I'll go ahead and type two and type two underscore signs here one after the another. How is it going to like a single line but there are two underscore signs right here. So this is [02:53:17] M and exactly have three characters in total. So let's see uh if we have any names like this. So here I'm select this is our [02:53:29] is our from customers. Let's select customer first name right here. Okay. And let's [02:53:43] go ahead and see if there are any names like this there. Again contact first like this there. Again contact first name is like M and two underscore signs. Here we have just one person. If I go ahead I [02:53:58] put one more underscore sign here it will be total four characters. You will for four characters you will see more answers here. So this is how it works. This how it works. In general you will not find many [02:54:12] recognition. If you're looking for a strict pattern recognition then it will be useful. Otherwise most of the time manager will be hand that percentage thing will be handy. Why? Because if we simply want the first letter start with [02:54:26] M we could have written it like M and percentage and that would suffice. So after M any number of words can be there any number of characters can be there. any number of characters can be there. So if I go ahead and write here [02:54:40] M with percentage M with percentage here. Now the uh letters afterwards uh here. Now the uh letters afterwards uh doesn't matter. See, so that's the difference between putting the underscore signs and the percentage. [02:54:54] Now go ahead and get it. This is another way to put the filter in wild cards. Everyone we already done this once here. We wanted uh from the orders table. We started with that. So we wanted to get [02:55:09] uh order number, order date and the status where order date is 2004. And then we are getting and function here. So here right now we want order date from January 2004. So I'm changing this [02:55:24] from January 2004. So I'm changing this to January 2004. So here it's fine. But to January 2004. So here it's fine. But afterwards here it is asking that that r so we will type and here but after n these are either shipped in process or [02:55:37] So now here we go ahead and we use in operator because we have to use or here instead of writing or multiple times here after add we will say that go and here after add we will say that go and check the status and in here we write we [02:55:51] go ahead and we type m chev process or code and we type this here make sure we are in the single quotes right here. [02:56:05] Now we don't need to type or anymore. We can simply type our entire list. Remember that there are no extra spaces. Yes, we can write like that too, Michelle. So let's check. Do we have any answers here? Oh yes, we do. [02:56:23] We have got shift here. End process or end process and not everything is getting checked out. This is the one. This is the post. Now let's look for the second one. Second is retrieve customers whose name is start [02:56:39] retrieve customers whose name is start with A or B and they are from Germany. it out. I have no idea whether we have any rules like this or not. We have two [02:56:52] people like that. Wonderful. Uh here is the question. Uh okay. retrieve the the question. Uh okay. retrieve the customers whose names start with A or B and they are from Germany. So what see uh you all are applying the filter on [02:57:07] different columns right someone is uh few of you are applying this particular filter on customer name few of you have chosen customer contact first name in both the cases you'll get the answers you might get one row or the two rows so [02:57:23] you might get one row or the two rows so for example if I take this uh here the first one that I have got if I take this solution right here. So this is customer name and country from the customers. After country and [02:57:38] Germany we are typing and now we have to can pass it inside one another like a starting from a or starting with B. So right now we using customer name here a [02:57:50] direct customer name the first one and see now we have two people here. If instead of customer name I will go ahead and use customer first name that is [02:58:02] contact first name then the answer will differ. So for this I will take the second solution I have got and see here we have contact first name has been we have contact first name has been taken here. See so the logic is same. [02:58:17] So both the answers are correct. Okay, let's go ahead and look into multiple different types of functions here. Basic type of functions that we have here is text function, date functions, conditional statements like [02:58:31] functions, conditional statements like uh if else and the case and when. And here we will also look for the null values. First thing that we are going to learn about is string functions. They are also called as text functions or [02:58:43] simple characters. Right? What types of string functions that we have seen so far? If you know Excel, you have seen string functions. If you have known Tableau or some uh similar languages out there, you have seen text functions [02:58:55] there. For example, there is a text function called as upper. What does it do? It goes ahead and converts a smaller case letter into a uppercase letter. [02:59:07] case letter into a uppercase letter. Similarly, we have a uh we have called as lower. What does it do? It gets the upperase letter, converts this into a upperase letter, converts this into a lowerase letter. That's upper and lower. [02:59:19] Another here an example here is length function. What is length will do? Length will simply go ahead and give the length of the string. Whatever is written inside and it will give us length of the string. We have another function called [02:59:31] as trim. What trim will do? Trim will get rid of extra spaces. It will leave one space. It will get rid of any extra spaces if they are written before or after the string or the characters. So if I have an address written somewhere [02:59:45] and if someone has given too many spaces between the addresses line one and line two then it will get rid of that. So on the similar basis we have another reverse. Reverse will reverse the uh data point. Sub service string will [03:00:00] simply get few words out of the entire string that is service string there. Okay. These are few very common condition uh uh text functions right there. Now do you think can you think of any other function which is very common [03:00:14] in text functions? We do have left and right function. Left will go ahead and will go ahead and get few characters from right. Welcome is the data type in yes we do have proper in Excel. The most common one that you will ever see is [03:00:30] common one that you will ever see is concatenate or concat. Most common that you will see there. What concat does? podcast cat will go ahead and it will join yes it joins say the strings very good it can join two or more strings [03:00:44] together it combines that so let's see an example for example we will go to the customers table again so I'm getting from customers again so this is here from customers write again now in the customers here [03:00:59] see we have contact first name and last name we want to write it together so here I will go ahead with select again in select What we want to do first I will get the customer number of course comma get customer name that is the name [03:01:13] of the vendor the company and inside that company this is our stock name here so now we go ahead and we type concat so here is concat everyone you can see the function right there this is concat function inside this concat function I [03:01:29] will simply ask it that now you concat first name comma last name I'm going to first name comma last name I'm going to name this here as full Okay. [03:01:45] here. When I'm applying the concat function here, look at the column full function here, look at the column full name and tell me is this uh output correct? Can you simply go ahead and give this uh share this output outside? [03:01:58] Is this possible? There is a space. See here Karen has the space. Janine here here Karen has the space. Janine here this has the space Dieago has the space Jonas here has the space not in all why is it like that why do some names have a [03:02:15] space some name doesn't because if I add the space now somehow they are going to the space now somehow they are going to have extra spaces correct so few if I add the space now few will have extra spaces so tell me why do we have extra [03:02:29] spaces here first figure out why why Does this appears like this? Why is it happening? Because when someone was updating the data, right? They have written the first name or the last name and by mistake [03:02:44] they have added the extra spaces either before that or after that. So either first name or last name these have extra spaces before or after them. Because of that because of that here we are having extra spaces. So we need to go ahead and [03:02:58] trim the extra spaces first and then add the uh required space. So first thing we do is we use trim function here. Absolutely correct. So I'm going to go ahead and type trim. Now what is the problem with trim function here in uh [03:03:13] SQL? We have to go ahead and apply trim function to in the jewel column name here. We cannot simply go ahead and encompass trim uh for the entire uh [03:03:25] concat. We have to go ahead and trim the individual names here. So once I trim it here, you will find that now see we do not have any spaces. See here I have applied trim function here and you can see there are no spaces. [03:03:46] after this comma here. I will type one more comma. Now we are having one sp character space in between. So here we go. Single quotes a space single quotes right here. Single quotes. Type the space bar. Click on the space bar and [03:04:01] single quotes again. And press control enter. And see we have got full name right here with proper one space in between. So we already have this right. So just copy and paste this here and also get the full address column here [03:04:15] because we have address here. We have got a design this line 1 2 3 4 tilt entry right so we want to get all of this at one single place so let's move forward what we wanted to get here we wanted to [03:04:29] go ahead and get the full address so when we go ahead and try to get the full address here I'm sure you all have written that there right so let's go ahead and type it out for others too so after concat and getting the full name [03:04:42] here now we type concat again but in this concat We want to get the address. Uh here this is address line one. This is address line two, city, comm, state, comm, postal code. And then we have country. [03:05:04] to go ahead and write this as address one for now. And let's look at the output. Okay. When you look at the output here, what does it show us here? That these are null values. meaning these uh we don't have address for these [03:05:17] customers. That's what it tells us here. Right? So let's check it out. Don't we have the address for Kerine and Jen here? Let's figure it out. So if I go to customers right now, everyone, and I look at these people here, okay, do we [03:05:32] not have the address for the them here? Do we have the address or not? We have the address. Correct. However, when we are trying to concat it, when we are trying to concat it here, are we getting the address? [03:05:47] No, we are getting null values there. Why not? Yes, absolutely right. Why is it happening like that? Because what is happening with concat here is it is reading it here. It is reading it here. However, as soon as it reaches the null [03:06:02] value in the second column, it stops right here and give us the null as the answer. So whenever uh everything is present, whenever in every column there is a text present, it is giving us the answer. But as soon as it reaches or [03:06:17] answer. But as soon as it reaches or looks at any null value, it mo it will not move forward anymore. It stops here and give us null as the answer. It means that concat cannot deal with null values. Concat fails to deal with the [03:06:32] null values. So this uh concat will not be able to work at all for our because it should simply ignore the null value and move on. Right? It is not able to do that. It it fails at that point. So what we will [03:06:47] do here now we'll go ahead here and first thing that we should know is how to find null values. How to find null values? To find the null values here we are going to use very simple thing here. will ask SQL is there any null values? [03:07:03] We can simply ask. So we can go ahead and ask it that go ahead and select and count. What to count here? Count every column. Count every column whatever we list here from the customer's table. from the customer table where [03:07:21] where you have to count wherever address line you have to count wherever address line one is null or one is null or or address line two is null [03:07:38] address uh city is none. and likewise So it'll go ahead and ask it to go and check if these columns are null. So when [03:07:54] we ask it to do that, it will tell us that yes, there are 113 null values out there. It simply tell us that yes, there are 113 values out there which are null. So go ahead and see this here. Now we have to go ahead and find out that how [03:08:08] do we deal with null values here. So we have null values. Fine. I know we have null values. So how do we deal with it? So there is a very famous function called as poies. Poles is a function that is used a lot to deal with the null [03:08:21] that is used a lot to deal with the null values. Why? Because it can successfully ignore the null value. It can also replace the null value if required with any text number or symbol. So kies can be used as a nested function with [03:08:34] another function very easily. So go ahead and first get the concat properly and see how to go ahead and uh count with the null values and then come back here. You'll go move forward with coies right there. Okay, first check this out. [03:08:54] So how does the queries looks like it? The syntax is like this. So quies can be applied here uh to any column name or a string, right? And afterwards here we can go ahead and ask it to uh either replace it with blank or replace it with [03:09:09] any text number or symbol. So co whenever it will find uh the null value it will simply go ahead. What coies will do? Coies will go ahead and check for do? Coies will go ahead and check for non-null value. If the value is not null [03:09:21] it will give that as the output. If value is null then it will give you whatever you ask it to give as the replacement. So if you want it black, it will give you blank. Two single quotes without any space. That is blank. Okay. [03:09:35] So let's go ahead and find it out. How does that work? So I'm going to copy and paste it right here. I'm going to copy and paste it right here. and paste it right here. And comma enter. What we want here is we [03:09:48] want to go ahead and work with concat. But concat cannot deal with null values. So we will ask here we will ask coalis here to go ahead and check is address [03:10:00] line one is a non-null value. If it is a non-null value we will type a comma here and we'll say if you find a null value go ahead and leave it blank. If there's a null value leave that blank. Now we type [03:10:18] comma again and here we will go ahead and type co again for address line two. For address line two in address line two if this is blank in address line two if this is blank here go ahead and type na. I'm going to [03:10:33] type a bigger na here so that I can show you that it replaces the value. Okay I'll type comma again. Now we go ahead and check for check for the nominal value again that under queries if is if city is null go ahead and [03:10:52] give this as the replacement. Okay, I'm not typing anything else right now because I want to show you how co works and I'm going to type this as address and I'm going to type this as address two. Let us see how does it work. [03:11:11] Observe everyone. Observe everyone here. Do you see NA is here? Meaning what is empty? What is null? Address line two. So wherever you see NA here, address So wherever you see NA here, address line two is null. If I go here, say [03:11:26] here, everywhere NA is at address line one. one. We have the city name everywhere. We do not have any null city name till now. [03:11:46] quies here, right now it is showing na. Right? Right now it is showing na everywhere. If I ask it here, let's simply go ahead and type a comma. Simply go ahead and type a comma with some spaces before and after. Then see how [03:12:01] easy it looks. See here how clean the output looks. Yes. So this is how polies output looks. Yes. So this is how polies works and yes it is a lot more work to type concat with quies. It does take some time. So go ahead and type concat [03:12:17] with queries everyone. And I will reformat this to show you it actually looks very good after reformatting and it and it is easy to follow also. So if I reformat this here, I will go ahead and reformat it with this here. [03:12:38] follow. Now, now here when we uh now we have seen everyone that uh we can write concat and co but it is not looking that good also because we have to go ahead and trim also. So when we are writing here concat and pois here we have to go [03:12:55] ahead and trim them individually too. If we are not trimming them up the problem is same. See we have too many extra spaces everywhere. So we have to go ahead and apply trim here too. So yes it does get tedious to [03:13:10] here too. So yes it does get tedious to type. Okay it does get tedious to type. So what is the another way? So now what is the another way here which can help us to reduce the effort. write down here. So another way here is to use [03:13:24] here. So another way here is to use another function called as concat ws. Concat ws ws means concat with a separator. Concat with a separator. Now why is it called as concat with [03:13:36] separator? So let's see something here. This is here is uh uh city name right one. So somewhere we have address line one two and city name two. So now what [03:13:49] is happening here is we are not giving any separator here. If we want we can. So after every comma here we have the choice to give any separator that we want. We have a choice here. We can go ahead and give any separator that we [03:14:04] want. So if I go ahead and type separators after address line one and after address line two right here this see can be can appear like this here. Okay. So these are the separators that are appearing here. Meaning if we're [03:14:20] using concat and poles together, we have the option to give as many separators we want and we can choose different types of separator here. However, when we use concat ws, concat ws is going to give us exact similar [03:14:35] output. It is going to ignore the null values. It is going to concat whatever we ask it to do. However, it will only give us option to write one separator give us option to write one separator and no more. So, concat with coies can [03:14:50] replace null with a message or blank, right? With multiple type of symbols and delimters. However, concat ws is going to be just opposite of that and it will ignore null but it will give us only one type of separator and it uh will not [03:15:06] give us an option to replace null value with any values or message. So we cannot with any values or message. So we cannot replace null with concats. So this these are few differences here. The best part here is our work does get easier. Okay, [03:15:20] how it will get easier here. So this is how concats works. So this is concat ws how concats works. So this is concat ws here. Okay. In concats first here we give separator any separator that we can define. Separator defining here means we [03:15:35] can go ahead and type a comma maybe a semicolon in here or maybe just a forward. Okay. Whatever we want or just the hyphen like like this here. If I go ahead and type a comma now. Now you go ahead and you maybe want to type trim. [03:15:48] You get column number one here. Then you comma again. Then you type trim here. Column number two here. and so on. But the separator that we give here, it is going to be only one. Separator can be any d limit, any kind of symbol right [03:16:01] there. Okay. So let's go ahead and see how we are going to write it there. So here we go. I'm going to copy paste it here. I'm going to copy paste the same thing here. And we add one more column everyone. We add one more column right [03:16:14] here. Now in this column here after edges two, comma, enter. And now here we are getting concat. This is concat with separator. So inside [03:16:26] define the separator. So let's give uh separator with two forward slashes so that I can show you where separator is coming up. And after this here we simply coming up. And after this here we simply need our address line 1 2 and three. So [03:16:39] we are looking for address line one, address line two, comma we have got city, state, postal code and country. If you want to type trim, you can. Currently, I'm not going to do that. So, here we have got address three right [03:16:55] It looks easy to type. And let's look at the output then. Let's look at the output. Everyone see here. This is address two and this is here. This is address two and this is address three. It was easy. We able to [03:17:09] get the proper output. And if I want to change the separator here, I will go ahead and type a simple uh comma right now. And it looks much better. [03:17:24] easy. But in real time, what happens here is in real time everyone, we use concat and co together. But it because it gives us flexibility to type whatever we want, wherever we want. That flexibility we [03:17:37] get in queries because every time we are not looking for one single separator neither we are uh and some and sometimes we want to actually replace the null values with something uh some message out there or maybe we want to give a [03:17:51] default value if something is not available. In these type of realtime cases we will use concat and coies more as compared to concat and coies can be used with any other function whenever you want to deal with [03:18:04] the null value. Yes, it is much easier to type concern voice. Absolutely correct. I will also show you the reformat version [03:18:16] because reformat version is easy to follow. minus sign is you can actually go ahead and collapse them. [03:18:31] You can simply go ahead and collapse them. So you will be only focusing on what you want to work with. It is still there but they collapsed. So you can there but they collapsed. So you can expand it, collapse it. [03:18:44] So that's the reformatable. [03:18:57] brush remember. So in when you will reformat it uh observe that this is how the reformatted uh query looks like right. Select customer number concat [03:19:09] concat concat here all the four formulas are here right. So do you see this minus sign here? Do you see this minus sign here? This is collapse. So see here we here? This is collapse. So see here we can collapse the lines right there. [03:19:23] Saving on the uh saving on the screen part. So we we can simply collapse it if you want to focus on one single formula. All right. Yes, it is easier to follow [03:19:35] but not easy to write. See here I am writing concat here and it is ending up with the name. I'm typing concat only once. It is ending up with the name. In your case it is not like that. I don't know what you're typing and from where. [03:19:49] Can you copy paste from code share and compare first? So copy paste from the code share and compare what you are typing and what is written here. You will be able to understand what's going wrong. Okay. So here next function here [03:20:01] is substring function. What is substring function here? Uh this is how the uh this is how the substring syntax looks like. As the name suggest what does it do? It will go ahead and create a subset from the existing string. It creates a [03:20:17] subset from the existing string. Meaning it uh see here we will pass the column to it. We'll ask it the starting position and from that start position how many uh letters we want out or how many characters we want out. Yes, part [03:20:33] of the string is retrieved. So if I simplify it, we simply extract a few letters or characters out of the entire string. Okay. string. Okay. So for example, I'm going to type uh [03:20:46] just like this first and then I will show you the example. So for example show you the example. So for example here if I am writing here [03:20:59] position a start position means this P here is one this R here is two this is here is one this R here is two this is three this is four 5 6 7 okay so if in the sub string here if I'll go ahead and I will get ask to go ahead and read the [03:21:17] letter product delete The word product the start position is start position is three and the length here is four. So what it is going to type what is the [03:21:29] what it is going to type what is the start position here? Three. And how what is the length we need here? Four. 1 2 3 4. So it will simply extract that position from that position till whatever length [03:21:45] from that position till whatever length is there. 1 2 3 4. So that's how service works. So let's go ahead and see a simple example right now. So we will work on the products table. We have got product code everyone here. So let's [03:21:58] work with that. So here is our product table right here. So I'm typing from table right here. So I'm typing from first from the products and here we have select right down in select here. I'm simply taking the product code for now. [03:22:13] And after put our code here let's type here substring everyone this is sub str sub string everything is same they work exactly same absolutely no difference in read the product code and let me show you how the product code looks like okay [03:22:30] this is how the product code looks like everyone [03:22:44] this is how the product code looks Okay. So this has here is 10 1678 1949 26 and done. So what we will we are saying to the here is that go and read the product the here is that go and read the product code and start with the first position [03:22:59] and get me the four characters out of that. Start with the first position and that. Start with the first position and get the four characters out of that [03:23:12] position including that it is getting 1 2 3 4 is a simple sub string here and whenever you are learning any new function everyone you can always ask for help. So you can go ahead and ask here help. Help with what? Help with [03:23:26] help. Help with what? Help with substring. actually going to give you the entire description right here [03:23:39] with the exam. Okay, and it will tell you that substr is a synonym for substring. Go ahead and get this please. Simple subring you will see an application of that. We'll see the application on that. [03:23:53] Yes, it is like of Excel. In Excel also you'll find substring. too. Let's look at the comprehensive example. [03:24:08] Now so let's uh go ahead and generate a custom product everyone. Okay. Now to generate a custom product code here we going to combine few things together. First we'll go ahead and get the first three letters of the product line. So [03:24:24] this is the products table everyone. So let's look at the products table right there. In this product table here first we want to go ahead and get the first we want to go ahead and get the first three letters of the product line that [03:24:38] we will combine with that we are going to combine with a formatted by price to combine with a formatted by price with zero decimal. A formatted by price with zero decimal. So we do have a buy price at the end right? We want to go [03:24:52] price at the end right? We want to go ahead and format it by that format it by price without the decimal part. We will want zero decimal values there. Then we will combine it with a reverse product code. Then we combine it with the unique [03:25:06] reverse product. So this product code that we have with we will reverse it. Absolutely reverse the entire string. And we only want to view five first five rows. We just want to sample the five rows. You don't want to get the entire [03:25:30] break it down one by one. So first let me write here select everything from customers. So from customers is fine. From product is fine here. Now in select here we want custom product code. So let's uh begin [03:25:46] here with product name because we are creating the code for these product names right there. Now we want to combine them together. If we are about to combine them together, we have to go ahead and type concat everyone. Okay. [03:26:00] Now after concat here, what is the first thing we are looking for? First three letters of product line. First three letters of product line. To extract the first three letters of the product line here, what will be the function? Left. [03:26:14] So we will apply the left function here. We apply the left function here and we ask it to read the product line and get three words from there. Get three words from there. And see here we are getting the three [03:26:29] words out once we apply lift. That is first part done. Now let's go ahead and get the second part. We need now a formatted buy price without decimal number. So if we ask for the buy price directly here, if we ask for the buy [03:26:44] price directly here, it is going to look like this. We don't want decimal point. So what we will do here? We will format this. To format this by price here, the [03:26:56] function is also format. Function is also format. This format is equivalent also format. This format is equivalent to the text function in uh Excel. It is equivalent to uh the format function in PowerBI. Meaning this format function [03:27:10] PowerBI. Meaning this format function will convert any column or any past field inside it and format that into whatever we want and most of the time whatever is the whatever the answer is right that answer will be converted into [03:27:25] a text. So this format function you can pass it any number and after comma you can go ahead and type whatever uh text whatever format you want it will convert it into that but the data type will be also converted as a text that is format [03:27:41] function right there and see now we do not have any decimals you can also go ahead and give a separator right here you can also go ahead give a separator right here so now we will see it like this so this is let function This is [03:27:56] format function right here. Now afterwards here I will type comma and we go for the third option here. What is the third thing that here is we want to go ahead and reverse it. We want to go ahead and reverse it. So we'll [03:28:10] simply go ahead and say that yes go ahead and use reverse function right there. Inside reverse here we want to reverse the product code entirely. And remember to give the separator here. So we are giving the separator we reversing [03:28:23] we are giving the separator we reversing the product code right there. [03:28:37] reversing. Okay. Once you have reverse it here, what is the next we are looking for? So this becomes our custom ID. This become this becomes our custom ID. This become our custom ID. So I'm writing this as [03:28:52] custom custom goal. Okay. This is our custom code right there. This is the customized code. But do we want to re uh do we want to see uh here uh it like this? Everything should be in caps, right? It's a code. So let's go ahead [03:29:06] and convert this into caps. So numbers we don't need in caps. S uh in here is already in caps. So we'll go ahead and simply the left function that we have written there. We can encompass it within the upper here. [03:29:23] You can en encompass the entire into these brackets or the parenthesis here. Now this looks like a code right here. That's the custom code. But remember that after all this here, we don't want to see all the rows. We only want to see [03:29:36] five rows. So we will go ahead and use here the last keyword that we have seen that is limit. And we ask it to limit the output by five rows. And we'll be looking at five rows only. Go ahead and see there are multiple functions inside [03:29:50] see there are multiple functions inside it. Type them one by one. and Cyia I it. Type them one by one. and Cyia I will talk about format function format function reverse function because I will talk about the format function we [03:30:05] are using format function right now to display the zero decimal values left function here is simply taking the first uh three letters out from the left hand side of the uh sentence upper function is converting this into uppercase [03:30:21] reverse here is simply reversing whatever is written. So if it is written ABC, it will reverse that into CBA and then we are concating all together uh by then we are concating all together uh by typing the separators here. [03:30:42] format function in detail. When to use this format function? when to use this format function and what it is for this for first let's look at the syntax of the format function the syntax here looks like this inside the format we can [03:30:57] format any number to anything so if I we can go ahead and uh decide the decimal places with it right we can also define the local with it as per the zone so we can go ahead and format the numbers in any type of format we want for example [03:31:13] if you want to go ahead and we want a currency display. Currency display means ensuring two decimal points. So we can type a number comma 2. That's our currency display. We need two decimal pix two decimal points there. Second [03:31:26] here is we can go if we had a large number we can add thousand separators to it. Or if we want a localized number format. So maybe if someone is writing currency in their own format, not everyone is going ahead and applying a [03:31:40] thousand operators every time. So every country has a different nom to type the numbers and the currencies. So we can go ahead and define that too. Or if we simply want to multiply by 100 and add a percentage sign there. So in these [03:31:54] multiple cases we can use the format function right here. These are one of the many answers uh where we can go ahead and uh work with it. So I will show you few examples here. I will show you few examples here. So let's take a [03:32:07] very simple example first. So if I go ahead and type select. If I So if I go ahead and type select. If I go ahead and type select here go ahead and type select here and in select here I will use format. [03:32:20] And let's say I will go ahead and type a big number. Okay. And I'm typing here as zero. As zero I'm typing this as formatted. [03:32:33] Okay. See it has added the th00and separators for us right here. Now if I want to add a percentage sign here. If I want to add a percentage sign here what we do we again [03:32:47] go with select here with select here. And now I will type format. Okay I will And now I will type format. Okay I will go ahead and type format. [03:32:59] Format. Inside format let's go ahead and uh I'm writing let's say something like this. some decimal values right there and we multiply it with 100 because that's what we are looking right comma one I need one decimal here if I go [03:33:16] ahead and type it like this right we want to change it into percentage here so look at this what happens we see 76.8 eight. Now what I want I want to add a percentage sign in front of it. To add the percentage sign in front of it. Now [03:33:32] I will use concat here. I will use concat here. And after format we can go ahead and type the percentage sign right there. And it is going to concat and [03:33:44] type the entire column like this. So these are the few examples here. Okay. Go ahead and check out the format function. [03:33:59] about the products table, right? We have all seen the products table right here. And we know about the product line too. You know about the product line too. If I ask you right now, If I ask you right now to get the average to get the [03:34:13] now to get the average to get the average buy price to get the average buy price of each product line of each product line here, how does the how should the output look like? What should be the [03:34:28] table structure of the out? What should be the table structure of the output? Let's say you're creating that on pen or paper or in Excel. So how do you think the output in the result grid should look like? [03:34:41] How many columns we should have in the output? If we want average buyer price of each product line here then how many columns we should here then how many columns we should have here? Which two columns? [03:34:56] have here? Which two columns? Which two columns [03:35:19] an average here. Now in this product line here here what do you expect? In this product line here, what do we expect? Should we go ahead and extract the entire product line column from here to here? Should we go ahead and extract [03:35:33] the entire product line column here as it is? Should we simply copy paste it entirely same? No. What we will do here is we'll look for unique entries, right? We'll go [03:35:45] ahead and we look for the unique entries without any duplicates of course. So these unique entries here when we type unique entries only here meaning each uh [03:35:57] each product line should appear only once right. So that if it is classic uh once right. So that if it is classic uh cars here if it is classic cars here and then we have got motorbikes here and uh we have got vintage cars here. Whatever [03:36:10] we have in front of these unique entries we will only have one associated average number right here. Correct. So this kind of reporting so we all know this is how the reporting has to be done. So this kind of reporting is [03:36:26] called as aggregate reports. These are called as aggregate reports. Okay. So these are are called as aggregate reports everyone. Why? Because we're aggregating as per individual data point. We're aggregating as per [03:36:41] individual data point. Right? If you have learned Excel, it is exactly what pivot table does. If you have learned Excel, this is what exactly pivot table does. So how do we create these kind of reports in SQL? This is where group by [03:36:57] comes in. This is where group by comes in. We haven't touched it yet, right? Remember in the sequence I have shown you there is group by and having. So this is where group by comes in. What group by will do here is group by will [03:37:11] go ahead and simply fetch the unique list of any categorical variable. Group list of any categorical variable. Group by fetches the unique list unique list [03:37:23] of any categorical variable any categorical variable that what the group by will okay and we already know if I we want to do any kind of calculation that query that we are aware about. So that [03:37:38] is fine that is done by select but group by will be the one who is going to fetch the unique entries right here. So last example everyone let's go ahead and see how group by is applied here. So we'll go ahead and we apply group by right [03:37:51] now. So what we want to get we want to go ahead and get the average buy price of each product line. So first thing that we do here is we type select in that we do here is we type select in select here uh we are working with the [03:38:05] product table here. So let me go ahead and type product first and after select here let's expand it here and we will get the product line here. After product line here what we are looking for? We are looking for to [03:38:19] calculate average. Average average of what? Average of buy price. So we'll ask it to get the average of buy price. Here I'm calling this here as average. Okay. However, after from product here, we [03:38:35] have to use group by here. Why group by? Because group by has to inform select that do not fetch the entire column. So when the select is in the select, group by will tell it that see you're not supposed to fetch the entire column. I [03:38:50] will go ahead and pass you the unique list of product line. So remember that whatever categorical value you write here, it has to be written here too in front of select. Okay, because select is the one that decides the output table [03:39:05] structure. So if we are grouping it by product line, product line has to be part of the output. So group by will actually pass the unique list of product line to select and then select will go ahead and calculate the average right [03:39:20] here. So once I execute this here, you will find the average right here. Of course, it doesn't look good. Okay, so we can round it off or we can format it out. So it all depends on you what are you looking for. So currently I'm [03:39:35] rounding this off here. So this is how group by works. Now why I have taught you group by because I'm going to give you multiple practice questions with the where where the group by will be used. Okay. So let's begin [03:39:50] here. So what we are looking for here is let's go ahead and say for each country let's go ahead and say for each country for each country let's figure out that for each country what is the average credit limit what is the average credit [03:40:04] limit and while we are looking for it we want to see only the top five and we want to go and see the top five let's go and see how to solve it so first thing use classic models we have to make sure that the classic models is default [03:40:20] Right? Once you set it that as default here, now let's uh break it down here and breaking down here. First you want to go ahead and get uh two columns here. Right? First column should be country. Second is average credit limit. Then it [03:40:34] will get to the top five. First let's here. So first thing that we understand here is we have to work with the here is we have to work with the customers table right here. [03:41:00] select what so we need country we'll go ahead and get the country right there and comma we need average right here so we'll calculate average for now average for credit limit everyone now this much we understood and we can name this here [03:41:14] as average credit. Okay, till here everything is fine. However, we need aggregate report because we cannot simply fetch the entire country column. We need unique [03:41:27] list of country there. So, I'll go ahead and ask it to group by and country right here. Now, this much we understand from our previous example. So, this is it. Uh this doesn't look good. Why? Because it has got too many [03:41:42] round it off. We'll go ahead and we round it up here. We have we already So here we go. We will go ahead and ask it to round it by the two decimal it to round it by the two decimal points, right? [03:42:01] limit method. Now in this average current limit here we want to see top five. Now how do we get top five here? This is where thing uh this is where it is very very interesting. Remember our select query right here right in this [03:42:15] entire select path here this group by right now after group by here we have order by and limit order by means sorting. So after by after afterwards has the highest or the lowest credit limit which country has the highest or [03:42:30] lowest we can simply ask it to order it by. So if we go ahead and ask it to order by meaning sort this particular output this result grid output here by what? by average credit limit. So we can simply go ahead and write the name here. [03:42:45] Since average credit limit is already a name inside this aggregate report order by works when the select has on uh when the select has given us the output. So in the output average credit limit this name is taken as name of the column [03:42:59] there. So if I go ahead and ask it to order by every credit limit and we execute this right here it gives us everything in ascending order. Okay, it is giving us an ascending order by default. [03:43:14] So if I want to see who has the highest, how do we get the highest here? We go ahead and we get the descending order. So we go move forward here and we order. So we go move forward here and we define that that go ahead and give us a [03:43:28] descending order. Now here when we define descending order, we do see the top here. See, but we don't want all the 110 product rows here. We are not looking for all the rows out there, all the 27 rows [03:43:42] here, right? We don't want that. And so what we do, we go ahead and ask it that okay, I don't want to see all the rows here. Limit the rows by five. Now, what is limiting the rows here? Limiting the rows here means we will go ahead and use [03:43:57] another keyword that is limit. Limit is pretty simple. Okay, limit here goes ahead and simply limits the number of rows from output right there. So this is limit five. The moment I put limit five here [03:44:11] and execute this, we have top five right here. This is limit. So it is just in succession everyone. So right now we have each country average limit and we want a top pi to get the top p. We'll use the order by and limit [03:44:27] to. Okay, very simple. We have simply talking about the group by and we have joined about the group by and we have joined with order by and after the order by [03:44:39] remember the sequence we will put the limit where we want to go ahead and limit the rows in the output. It is always written in the end because this is the last thing we execute. [03:44:53] Let's go ahead and see it just uh quickly once more. Okay, just a quick review here. What is the order of execution here? Auto execution that have to go ahead and do anything about it. Since we know about group by here [03:45:06] and we will soon see having also here understand when these queries are actually executed in the back end. Whenever you're writing a query, even you're writing a query, a simple query like select everything from customers [03:45:19] table. It will first go ahead to your address. What is the address here? That which particular database it is and which particular table it is. it will first go here and then it will go to all the columns that you're asking for. If [03:45:32] the columns that you're asking for. If you're uh giving a database uh filter so address it will go ahead and then put the filter there. Maybe you want every from everything from USA. So it will go to the address it will filter out all [03:45:46] the rows from USA and then it will go ahead and select the columns for us in the output. In the similar manner it will go ahead and execute everything out here and then it goes to select. So once select will decide that okay this is the [03:45:58] and these are the columns you want there. Once this is done then the there. Once this is done then the remaining answer or the output is sorted and chosen that which how many rows we want to view. So this is the order of [03:46:13] execution. If we know this order of execution and we understand this here, it will help us to write the queries properly. At least help us to uh break down the query and understand that what goes where and which one of the queries [03:46:27] is helping us to actually apply certain conditions. So this is the order of execution. Here I will go ahead and uh share in the session materials but at the end of the session I will share a write up okay [03:46:39] which will talk about the order of execution in detail. But that is uh what we have to remember. So this is the limit everyone. Okay. And this is the group by. So we'll move forward and uh we'll talk about group by and having [03:46:52] here sales rep. We have customers here. Right. So let's go ahead and count the number of customers mapped to each sales rep representative. So what we want here is that for each country for each [03:47:05] country we have got sales representative and we want to go ahead and count the So we need first country column and then then we want to count the number of customers. So far we have only written [03:47:20] one category uh categorical variable here and we want it to group by just by one. Now we'll go ahead and add the sales representative too. Meaning we want the select to filter out by two columns here. one with the country, one [03:47:34] with the sales rep and then count the number of customers mapped to each of them from each country. How do we write that here? One thing is simple that we are working from customers. That is there we are working [03:47:49] in the customers table. Now within the select here we want to select country. This is also fine. After country here now we are looking for one more categorical value that is sales error employee. Here the name is [03:48:02] too big for me. So I'm going to go ahead and give it a smaller name here. Next here is we want to count the number of customers. Next we want to go ahead and count number of customers. What should we should be counting here [03:48:15] everyone? What we should count here? If you want to count number of customers, what we should count in the third column out of the customers table of course. Okay, we have two options right now. Anyone else? Right. So remember that we [03:48:31] uh uh we want to keep it easy. We can go ahead and ask it to count count customer number. However, we can go ahead and ask it to count anything. However, we can go ahead and we can ask it to count anything any uh column right here. Why? [03:48:44] anything any uh column right here. Why? Because we uh if it if it go ahead and count any of these columns here, it will still count the entire rows. We can ask will simply go ahead and count the entire rows in the entire table. Of [03:48:58] course, it has null values and we'll have a different answer. So, count customer number seems uh quite why not name because name can be similar. So, it might not count all the rows out there. So, instead of asking it to count [03:49:13] something else, it is uh it'll count customer number because it is always going to be unique. Yes, primary key concept. So account customer number and concept. So account customer number and I'm going to name it as total customers [03:49:26] uh customers mapped because these customers are mapped to each of the representatives to take care of. So here we will go ahead and type customer. [03:49:48] writing the group by everyone when we are writing the group by here we want to are writing the group by here we want to group by by country right so country we want sales representatives so we'll go ahead and get comma here and after this [03:50:03] here we will we can order it by okay so let's order it by country then let's ask it to order it by country now when we ask you to do this here look at this what we see right now that Australia has only one resale [03:50:18] representative Here Australia has only one sales representative here and there are five customers mapped to that person. Similarly when we move uh below here we see USA right now and within USA [03:50:30] we can uh clearly see that we have got multiple sales representatives here and multiple sales representatives here and they have been mapped with exactly six persons each. So likewise we can see Switzerland has uh these many customers [03:50:43] but few customers have not been mapped with any sales representative there. So we do have null values. All the null values will show up right here and telling us that there is no sales representative mapped with these 10 [03:50:55] representative mapped with these 10 customers in Germany like this. Now this is a simple group by in this group by here we can go ahead and uh write two categorical variables right there and we can still get the correct answers. While [03:51:09] increasing, what if there are 10 columns are there that one has got year, another has got employee number, another has got your employee name, then you need uh their sales target and whatn not. So if the number of categorical variable [03:51:24] increases, okay, how do we pro there is one more way to write one more way to write this. See this very very carefully. See this very very carefully here. Till here everything is going to be exactly [03:51:39] similar. Till here everything is going to be exactly similar. Now see right here remember that in group by and select whatever we type whatever we type in select if I'm typing two categorical variable in select exactly those two [03:51:54] categorical variable has to be mentioned in group by. For example, if you will mention if you will say that no it's okay I want the sales employee number here but I don't want it to group it by I just want list there. It cannot [03:52:07] categories here. Otherwise, it will try to grab the entire uh row of thousand customers maybe right here and we have just 27 countries. So, we have to go ahead and write the exact number of categorical variable right here and the [03:52:22] exact name too. So, to avoid the conflict and the errors here, there is one more way to write it. Before I write it there, do you understand that in this result grid country is the first column? Do you agree to that? In this result get [03:52:35] sales rep is the second column and customers map is the third column. It's the similar manner we type here. This is uh the first column we want. So in the select query we write country as a first. If we want sales employee number [03:52:48] to be second column we type it on the second space. We want count as the third column. We type count at the third place. That's how we write. Do we agree to that 1 2 3 number of columns here? Do we understand this part that yes this is [03:53:02] in the output is 1 2 and three. So what we do here is this. We go ahead and we say that okay do one thing group it by group it by the column one comma column group it by the column one comma column two and order it by order it by in this [03:53:18] example let's order it by customer mapped that is the column number three column number three right there we can go ahead and write it like this too. So if I go ahead and execute this right now we still get the same answer. If you [03:53:31] that in descending order too. Right there. See? Okay. So go ahead and see that there. This is one more way to type your word by queries. And yes, order by also understands the column number here because select by then has already given [03:53:46] it the output. So see how do we go can go ahead and get the group by done with two columns plus uh another way to type two columns plus uh another way to type it. Now here now here. All right. Let's [03:53:59] add one more condition to this. Now we want to see to uh customers mapped. Now we want to see customer mapped. We want to see the countries. We want to We want to see the countries. We want to see the country list. [03:54:14] see the country list. Country list where customer mapped to Country list where customer mapped to sales reps are more than five. Okay. Only the country list where customer mapped are more than five. So in this [03:54:28] case here of course I'm talking about uh what the output that we are looking at what the output that we are looking at here but please tell me one thing uh where do we apply the filter on which particular column we need to go ahead [03:54:41] and apply the filter we know that we have to apply the filter where customer mapped is more than five. All right. So on which column do we need to apply this simple mathematical uh filter comparison filter customer mapped are do we all [03:54:54] agree that yes customer mapped. Okay. Where is the customer mapped present? Is Where is the customer mapped present? Is it present anywhere in the uh database or customer map is present right here in the result output. [03:55:08] It's a calculated field. It is in the result bit output. We do not have any customer mapped named column here. Right? It's a calculated field here. So whenever we have an aggregate report created with the help of group by and we [03:55:22] created with the help of group by and we have some filter to use here inside the calculated group or aggregated uh option here okay where group by is already fetching the unique list in this case we always give the filter with having we [03:55:36] always go ahead and give filter with having why because we have to give a filter exactly on a calculated value aggregated value here. So now inside having here having clause here is used [03:55:48] to give the filters only on the aggregated reports created with the help of group by. So here we can say having customer map greater than should be equals to five. When we go ahead and write that here. Okay, this is it. See [03:56:01] we getting all the uh customer uh countries here with customer mapped greater than five. We can go ahead and ask it uh that can you go uh ask it to order by one here and uh see [03:56:16] and uh see we have got country right here. Okay. So this is how having is used. Remember that having is a filter clause meaning this filter clause here can utilize all the operators that we have seen so far [03:56:29] and learned with. This is having clause everyone. Remember that uh this having clause that we are looking at right uh this uh clause here is avoided if we can avoid it with the help of wear clause. It says that because having is uh [03:56:44] executed once the group buy and select and calculations all are done then only the having takes effect. That's why it is avoided. If we can if [03:56:57] we can apply it with we will apply with not with here because once you beautify this here it looks really good and legible you can [03:57:09] read it easily how many product line that that we have here. Okay, we have that that we have here. Okay, we have got classic cars, vintage cars and trucks, buses, ships, planes, motorcycles and more. Okay. However, [03:57:22] what we are asking here, it is asking here that they don't want to see classic cars or something else, ships or something else. They simply want these from the list. So, first we will figure out from. So, which table are we working [03:57:34] on? We have figured that out very easily. We are working on the products table there. Fine. Now, from the select here, which columns do we want in the output? What should be the first column? We need product line with average MSRP. [03:57:48] Meaning the first column has to be product line. We agree to that there. And after this here we want to calculate average MSRP right there. Now after select here we have to group it by we agree. Okay, we understand that we have [03:58:03] it by the product line right here. We want to group it by the product line right here. However, now uh things will be different. What's different here? Now it is asking here that it doesn't want all the things there. It simply want to [03:58:17] all the things there. It simply want to go ahead and filter out only these list. So tell me here how do we filter out the entire list everyone? What do we use to filter out from entire list? We use wear clause. Very good. Now with the wear [03:58:32] here. Why are we using where clause? Because which column do we need to go ahead and filter out? It want to filter out every row which contain these four names. Which column contains these four names? Everyone product line. So we need [03:58:47] to go ahead and bring the product line. Since product line is present in the database, we will be using the wear clause. Great. Now tell me which operator helps us to filter from the list because we have to go and ask here [03:59:01] that go and check the entire list and if anything is present from the entire list go ahead and give me the output. Right? Very good. We will use n operator here. We use the n operator here. Wonderful. So now we have covered almost [03:59:15] everything. Last is determining here average price less than 100. So whatever average MSRP we have seen here. All right. From whatever output we have there, we need to go ahead and filter that also. [03:59:30] So if you want to filter this here, what do we do? We will go ahead and use having. Why you are using having here? Because we are using this average that we have that we have calculated out. If we want to go ahead and calculate it out [03:59:44] average, we want to filter this out if the average MSRP is less than 100. Are are we leaving anything out? The first line everyone the alphabetical order of the product line. So we can go ahead and we can order by order by what [03:59:59] product line we can type one right. So this is the product line we want to go and order. That's how we type it. So first I'm here. We know we are looking for product line everyone and we want to uh type [04:00:14] line everyone and we want to uh type average there and rounding it off. I'm rounding it off here. Easy to write it together and this is here as I'm taking this as average and here [04:00:32] talking about from the products right there after from products here. Now we go ahead and write where clause because select from and where. So in the where here we'll say and go to product line here and find it out where any of [04:00:49] line here and find it out where any of these names are present. [04:01:04] Amber we have to go ahead and get the single quotes. After where is done now we go ahead and type group what we can type one and in [04:01:16] the having here we cannot say having find out the two greater than three we have to go ahead and write the entire name here where average MSRP has to be name here where average MSRP has to be less than 100 and uh let's figure it out [04:01:29] is it 100 yes we do have the option there now we need to order it by so we there now we need to order it by so we can simply ask it to order by here [04:01:42] write order by one here it will be order by one right there go ahead and check by one right there go ahead and check this out please and I would like to reformat this too after reformatting reformatting it does look better [04:01:55] reformatting it does look better allegible there so now we will go ahead and we learn about a little bit about the date functions too and we will talk about group by roll up and sub totals to [04:02:11] there. So we're going to talk about two things. So first thing is we will go ahead and see how do we add the sub totals and totals there while we also delve into some date functions. Okay. [04:02:26] So the first question here is we know we want to go ahead and figure out total number of orders placed each year in the orders table. So what we are getting orders table. So what we are getting here is total number of orders is fine. [04:02:40] Yes. Yes. We have to because see here it is asking us not only total number of orders it is asking placed each year in the orders table. So if we go ahead and we look into the orders table. If we go ahead and we look into the orders table [04:02:54] everyone we'll see that orders have been placed every year. Orders have been placed every year. Yes. at almost every day and every day there are multiple day and every day there are multiple orders right here [04:03:10] we have to extract the year and then we get to count the number of order each get to count the number of order each year so let's go ahead what do you think we extract the year with which function can we use [04:03:29] see so first thing that we figure out here is that we want to go ahead and get everything from the orders table. So this is the orders table right here. Now what uh what two columns we want here. So inside the [04:03:41] select per year first we want to extract year. So we'll go ahead and use the year function. See the year function right there. Yes. I'm going to go ahead and there. Yes. I'm going to go ahead and extract the year out of order date. [04:03:55] Okay. We are going to name this as order year. So we can name this as order year. Comma. And what we want next? We want total number of orders. So how do we get total number of orders everyone? We use count. Right? We go ahead and we ask it [04:04:10] to count there. So you go ahead and ask that see this is the count function. Go and count the order numbers right there. We don't want to add them up. We simply want to count them that how many are there. I'm going to name this as orders [04:04:22] there. I'm going to name this as orders count. here. Here [04:04:38] has to be aggregated. We have to extract the year we need list of the unique years. So we go ahead and ask you to group it by. So you have to group it by what? First column. We can directly write one here. We don't have to go [04:04:50] ahead and write everything again. So we can simply write group by one. when we write group by one here we get 2003 4 and 5. Now this is something very easy uh easy to do and understandable too. Now what we want here is we want to go [04:05:06] ahead and add the grand total right here. We want to go ahead and add the grand total of the orders count right here. So what happens here? We have to how do we add that up? If we want to go ahead and see grand total through and uh [04:05:20] see grand total at the end, we will simply go ahead and use the rollup. What is the roll up here? So we will go ahead and write that I want the group by done. But at the end I will write want it with rollup with rollup is the entire [04:05:35] keyword. Once we add it after group by here and we execute it, it gives us the grand total right here. And no we will not be able to go ahead and uh replace this null value with the grand total or some name there because it is not the [04:05:50] part part of the order right here. So this is group by one with roll up right here. So go ahead my uh get it this please date and go buy the caution that I'm pasting here is okay it simply means which day the Sunday Monday Tuesday [04:06:05] which day we are having the most number of order placed because if someone knows that accordingly they can roll out the offers discounts and everything read it first completely find out which day of the week has most orders what is meant [04:06:22] by most orders means most number of orders most number of orders are placed meaning number of orders are placed meaning we need to go ahead and count the orders true for each day then only we'll have the answer right that Sunday [04:06:37] Monday Tuesday when did we had the uh most days there so 50% there so how do we go move forward this one so we will go ahead and type select and we already are aware that we want count there right we want counts again so I will go ahead [04:06:52] and write the counts again and we know we are looking it from uh the orders here and we have to group by the days that we have there. So this much is fixed. This much is fixed. What we want in front of count [04:07:07] right now we want the count to be done by individual day. So let's figure out which function is going to help us. So if I go ahead and type day right now if I go ahead and type day right now look at the list and here we'll find day [04:07:20] of the week. So once we find the day of the week here, we'll go ahead and ask it that extract the day of the week from the order date right here. And I'm going the order date right here. And I'm going to call this as order day. [04:07:36] day. Now in this order day here from the orders group by one, this is what we get. So uh we want to find out most count of orders. So let's order by two. So I will go ahead and write order by right here. [04:07:50] Let's order it by the second column. That is the counter there. And that's the order by. Let's make it descending. And now we know this is the highest. The only problem is I have no idea what day number six is. Is it Monday, Tuesday, [04:08:05] Wednesday or what? Because uh I cannot say that this one is Monday or Sunday. No idea. So we have to find it out whether this sex is what whether one is [04:08:17] a Sunday or a Monday. when does the week starts here to figure that out here. Okay, we will go ahead and uh add one more thing here after day of the week here. Let's go ahead and see one more function called as day name. Day name [04:08:32] particular day. So here we go and ask that go ahead and get the day name here. that go ahead and get the day name here. I'm going to call this as order day name. Here we go. Now here since we are adding [04:08:48] one more categorical variable in group I will say group by 1 comma 2 and we want to find out who has done the most right which day has the most so now we say count is three here so I'll say order by three right here and here we have so six [04:09:03] three right here and here we have so six is the Friday the day starts from Sunday go ahead and get this please there are more date functions out there now here let's go ahead and see what if they we want here that count of orders placed in [04:09:17] each month of each year. So again we want count of orders here. So this is going to be similar. So this much is going to be similar. The only thing that is going to change here is the functions. [04:09:36] here. What we want here is that we want for each year and month. So order year here and then we want to get order month. Then we want to get order month. Okay. Now, first here is year. We are aware of the year function there. We can [04:09:50] go ahead and type the year function right here. And we are good with that. Now, let's go ahead and find out another function which can get us month. So now we have two options here month and month name. Now it is quite evident [04:10:04] numbers. Month name will give us the numbers. So let's go ahead and get the month name there. So there here it is. We have got month name here. See this is order month and of course we have got two things here [04:10:17] year and month both when we go ahead and execute this right here it gives us two columns right here order year and month and fortunately this other month [04:10:29] that you see here it is properly sorted to in proper January February and March right here okay we are got here so this is order and month right [04:10:43] However, however, what if instead of writing year and month in a separate, we want to go ahead and write it in a formatted manner uh in one single column like this. Meaning I don't want uh year as a separate month as a separate [04:10:59] we want to write it something like that. This is 2005 here and maybe January. Okay. So, we want to go ahead and format it in one single column. So how do we [04:11:11] format this in one single column? To format this in one single column again this part is going to be sum right. This part is going to be sum. the selected statement is the selected [04:11:26] statement count is fine. What we want is a formatted value here. So if we want to customize the date format we will go ahead and use the function called as date format right here. And inside the date format in the syntax right now [04:11:40] we'll go ahead and pass order date because we want to format out of the order date. What we want to format here we want to go ahead and get the year and month out. Yes. So we can go ahead and give formats like this. We can go ahead [04:11:53] give formats like this. We can go ahead and type percentage uppercase y a hyphen and type percentage uppercase y a hyphen there and then percentage uh uppercase m there and close the parenthesis right here. This will become [04:12:06] as our order year and month. So this is what we looking for. This is what we are looking for here. So this becomes our order year and month. So that is our first column right there. [04:12:21] Okay. Let's see how does the output looks like. Here we are. All right. first year and month separate here and then we are getting year and month together in one column formatted [04:12:35] properly. So it's a custom format every there are many ways to use this custom date format. Let me show you here what happens if we go ahead and instead of upper case what happens if I go ahead and uh write lower case because now we [04:12:50] understand here that if I'm writing M here as for in the upper case right now we have written M as the upper case here right what we are getting in the output entire name of the month correct and if we are typing here Y in capture we are [04:13:04] getting the entire four digits of the year so what will happen here if we go ahead and type all of this here in a smaller case in a smaller case right here. If you go ahead and type it like this here, observe right now what is [04:13:18] this here, observe right now what is happening. Both of them are in numbers, right? Both of them are in numbers right now. Meaning and that two when we typing last two digits. We are getting only last two digits here. And M is now [04:13:34] written as a number. So if you want to go ahead and let if you want to go ahead and properly write it, maybe we can uh change it a little bit. Even we can go ahead and write it like this that this should be y here right and uh this m [04:13:50] here can be a numbers either this is a combination or maybe we want to go ahead and get the abbreviation of the month so if you're looking at the abbreviated if you're looking at the abbreviated month name like jn m a r d c like that [04:14:04] then we will go ahead instead of lowerase m here we type lowerase b and when we type that we get it like this here and it does not require required that you always write percentage y /b there hyphen b there we can always go [04:14:19] ahead and mix it up okay when we say mix it up here maybe we want to type b post and buy later it will come up like that okay so go ahead and find it out [04:14:33] okay so go ahead and find it out go ahead and check it out please you in understanding things here or maybe you can ask AI to explain the [04:14:48] concept to you with some examples but when you're working in real time first part is sudanchu we will not be able to give the data base structure to it however it can help us write the base queries for us it is good for practice [04:15:03] measures but in real time uh if you want AI to work with you you have to give the entire detail to it so unless your organization is not giving you any organization is not giving you any internal AI application to you. Never [04:15:15] ever use AI for your organizational data and with AI it is not faster. Why? Because you have to check check it by yourself. So you have to know at first answer or not because no matter how powerful the AI is even perplex [04:15:30] perplexity that writes in the bottom that this is AI fact check it first and then only believe that it is correct. And uh I have seen that in my experience also AI not always gives you the correct code. You have always have to go back [04:15:44] and cross check it. For beginners it does look faster. Uh but it isn't because now there are the epics to use AI and of course it's a danger for [04:15:56] organizational data too. So let's go ahead and find this out. So next here is a very simple question everyone. We want to go ahead and find out how many average days are we taking uh between order date and ship date for [04:16:12] each month. Every month, how many average days are we taking to ship something? Meaning once we get the order, how many time how many days does order, how many time how many days does it take us on an average to ship it out [04:16:25] of the warehouse? Okay. So that it can then take that many days to reach that person. So what is the average days between order date and ship date for each month? But we want to focus on wherever we are taking more than 5 days [04:16:39] to process an order. Okay, this is where we want to focus on. Can you please go ahead and try that by yourself? Can you find the function that will actually dates? [04:16:52] here is we have to go ahead and first get the month here. So we can go ahead and type select here and we can ask it to get month. So we are taking month name out here. Month name out of what? Order date. Now till here this says we [04:17:07] Order date. Now till here this says we are going to get order month. Okay. After this order month here we want to go ahead and calculate uh difference between the dates here. Correct? No. [04:17:21] So to get a difference between the dates here I will type date here and see we have got date date date add date format date sub here and date diff date diff between the dates this is the common function here you will find that in [04:17:36] excel too so in the date diff here okay it is very straightforward it will ask you to the last date first so we will go ahead and find the difference between two dates so please understand what we are trying to say here is I want to see [04:17:49] are trying to say here is I want to see that how many days we are taking between this order date and the shipping date. See here if someone has given the order on 6th January and we are shipping it out on 10th January. How many what is [04:18:01] the difference between this? How many how much difference of date is there? 4 days. Right? Now similarly we want to go ahead and do this calculation for each here is we want to go ahead and we want to do this calculation for each and [04:18:16] every row. And then for for example January here right. So for January here calculation here. See this is the whole January right? Now once we have done the calculation for each and every row that okay what is the uh difference between [04:18:31] these here we want to go ahead and calculate this average difference that you have calculated for each and every day for each month right there. So we want to do two calculations here. One is row wise calculation. Second here is we [04:18:45] want an aggregate calculation within that. So let's go ahead and solve it step by step. The first thing that we are looking for here is in each day we want to see the difference between shipping date, order date. [04:19:00] shipping date, order date. Right? Uh see right there. Now here when find the difference between the two here. First thing that we do is we want here. First thing that we do is we want we don't want the to add a calculated [04:19:13] column in the extracted table. Now we are looking forward to calculate an average out of it. So we'll ask it that whatever calculation we will do here go ahead and calculate the average of date [04:19:26] average difference of days here. Average difference of days here from where? So let me type here from orders table here and of course we need to go ahead and group it by. So here I'm typing group by [04:19:42] one that is month. Let's look at the output for now. Here there are too many decimals again. So we need to go ahead and round the top. So we'll go ahead and round it up. Here we ask you to round the entire function. And here we go one [04:19:56] more time. Okay. So now we getting that every month here this is the number of days we took to ship something right there. Now what where that that is more than 5 days here. So this is the average difference [04:20:12] in days. We have to go ahead and apply having here. And having here we can say having average difference of days average difference of days is greater than five. We have got one that is two. [04:20:33] and give us the proper difference between the two dates. Here it sometimes between the two dates. Here it sometimes also get hold of the liier. If you go ahead and yes uh see yes you can ask it to go ahead and minus the shipping date [04:20:46] to go ahead and minus the shipping date with the order date. You can do that. okay there are few discrepancies might happen. That's why we have a function [04:20:58] here. So let me show you about this function right here. So if you ever want help right you can simply ask for help by writing help like this that help you with the data function there. Once you write this what you get you get the [04:21:12] description of it right. So in as you can see here this is the syntax everyone can see here this is the syntax everyone what we see in the syntax here. [04:21:24] it is it will give us the value in days from one date to other. Expression one and expression two are date or date and time expressions and only the date part calculation there and they're also giving us a simple example right there. [04:21:40] In this example here they giving us two date and timing there and we are getting the answer and we getting the answer in number of days right there. So it works with time too and a simple minus will not work on time. Okay. [04:21:55] So this is data difference between date here. This you will find in uh Tableau you will find that in Excel you will find this in PowerBI almost everywhere in Excel it's a hidden function not shown there in the sequence. [04:22:11] It is more fun to type in Excel because you cannot see it there and in Excel it will also give you an variation. Okay, in Excel you can actually see the differences in years or months or days and PowerBI also it is quite interactive [04:22:26] in Excel and PowerBI and Tableau also it gives us multiple options. Go ahead and find I need to have a column name for it to work. Yes, you do need the underscore there. You cannot have a spaces because [04:22:40] it doesn't if you give single quotes it thinks that uh it it is some text. It doesn't understand that as name. You got doesn't understand that as name. You got it right [04:23:02] simply show sub totals here so with the rollup here we have uh seen sub totals here so we have got year and month separate remember if we go ahead and add [04:23:14] with rollup with rollup here and we have two categy toal variables two categy toal variables we uh will go ahead and get subtotals in between. See okay we get sub totals in between. [04:23:33] So after every year you'll find a subtotal for 2003 for example here then you go ahead and get subtotal for 2004 then you go ahead and get subtotal for then you go ahead and get subtotal for 2005 year and then an grand total. So [04:23:46] write like this you will get the uh sub totals and grand totals here if you have two categorical variables. So sub totals are automatically added [04:24:05] here because by default okay when we are using the this engine here it doesn't allow us to replace these null values here. So there are few uh preferential settings and the admin access we need here to change that and then only we get [04:24:20] to replace the null with coies. We can easily replace the null with coies here but there are some settings that which will not allow us to do that but for This is the free version that you're working with use not complete code [04:24:36] created here uh a small aggregate value here where we are saying that this is the person one this is the person two and this is the person three right and uh these people have been hired on contract and they have been uh paid uh [04:24:52] here everything in thousands okay everything in thousands here so they everything in thousands here so they have been paid 2K, 3K and 5K here. Done. [04:25:04] But uh so here we have got more people here. we got to know about that they have been employed for different purposes there, [04:25:18] right? So they have been hired for different departments here. So now we will go ahead and uh add the department name here. So these people hide both department one here. These are for department two here and this for [04:25:32] department three. Done. Now if I ask you here to give me uh totals right now. So tell me how much total we have paid so far. So how will you get the grand total here? How much total we have paid so far? What [04:25:47] How much total we have paid so far? What will be the grand total here? We add that up everything. Right? So we have here so we have here 5 Right? So we have here so we have here 5 5 10 11 12 and 13 right very good so we [04:26:02] are adding a bit to 13 right that's grand total but if I ask you now here that okay tell me department wise how how much we have paid here then give me sub totals also so how do we get the sub totals here so we have to go ahead and [04:26:14] write some uh thing along here what is the subtotal for department one there we have to add a row here what is the subtotal for department two add a row here what is it subtle for Department three here. Add a row here. Correct? So, [04:26:27] what's the subtotal here? Five. What's the sub total here? Six. What's the subtotal here? Two. So, we have to add a row here and add the sub total right there. That's the total for department one total right there. Department two [04:26:39] total right there. And likewise. So, that's what rollup is doing for we don't have to do this. This is what rollup does for us. It will go ahead inside that. And if we have two columns there, it will give us the total and the [04:26:51] So we don't have to define or do calculations. It knows that it has to go calculations. It knows that it has to go ahead and add everything up. [04:27:04] now? Next here we are moving forward everyone for logical statements. Right there. What are logical statements? We have seen comparison operators. We have seen and or between where we can go ahead and uh find uh comparison operator [04:27:18] one single statement and then and or in between we can filter out with more than one condition. Right? Then why do we need logical statement like if else and case and when [04:27:32] or in this case here we have got if function there we have got if function there. Why do we need if and case and when? Remember that with the help of comparison operator and uh with the logical operators out there, we will we [04:27:46] were able to only filter out by those conditions. Here logical statements are used when we want to go ahead and add a logically calculated column or a logically calculated column within the data uh within the result grade. There [04:28:02] for example, this is our customer's database, right? In this customer database, right? In this customer database here, we want to go ahead and read their credit limit and based on their credit limit, based on their [04:28:14] their credit limit, based on their credit limit, we want to give them uh credit cards that if you belong to this particular limit here, we can offer this credit card. However, the calling team that we have there, the u basic sales [04:28:28] simply go ahead and share the credit details of some customers. We cannot do details of some customers. We cannot do that. So we have to go ahead and uh get that. So we have to go ahead and uh get customer name customer name. All right. [04:28:42] Their contact their contact their contact plus which their contact their contact plus which credit card they are eligible for which credit card they're eligible for. All right. We cannot simply share their [04:28:56] credit card score with someone or credit card eligibility with someone in a sales table there. You will simply tell them the credit card eligibility that these are the cards that you're eligible for. Right? Have you received these calls [04:29:08] their contact number. They even have your email ID. They know which credit cards you're you are eligible for. Okay? We all have received those calls. So, we can go ahead and do that. Why? Uh [04:29:20] let's see if your credit limit is more than 75K, we offering a gold card. If it is between 50K and 75K, then we are offering you a bronze here. And other than that we are offering you a server or something else. Right? So what we are [04:29:35] doing here is we're simply checking the number and writing an output. We are not doing any calculation. It is a simple logic we're applying here. So when we want the such kind of logics applied here we go ahead and we use if else. [04:29:48] Another option here is uh for example we want to go ahead and uh assign the you want to go ahead and assign [04:30:03] So if uh so uh right now the season is going on okay those who are working those know that appraisal season is going on. So maybe now they want to go ahead and based on the job title and the performance they want to give us a [04:30:16] performance category. They want to assign a category to us good bad and uh good bad and and uh excellent. Okay they can go ahead and do that based on whatever logic they have with them. So when we want to add any kind of [04:30:28] calculated column like this, this is where the if and casement comes in. if function there. Those who have worked in Excel, you already know about the if function. It is exactly the same here. If you will look into the if function [04:30:40] here, this is how the syntax looks like. This is how the syntax of the if function looks like. Inside if here if function it asks you that okay, what is the condition that you're looking for? And if the condition matches, if the [04:30:53] condition is true, what is the value you want to give? If the condition is false, what is the value I want to give here? This if function right there, this if function right here helps us to check one condition and give two outputs based [04:31:07] on that. If condition is true, what is the output? If condition is false, what is the output there? And if we want to go ahead and uh we want to check if we want to check multiple conditions and we want multiple outputs, then we have to [04:31:20] go ahead and use nested. we have to go ahead and use nested or we resort to case and when case and min is easier than nested. case and min is easier than nested. Okay, let's go ahead and solve it out. [04:31:36] So, first thing that we see is that uh we will see customers here. We assign we will see customers here. We assign them the categories based on the credit them the categories based on the credit limit right there. [04:31:53] So first let me explain. Okay. So let's go ahead and learn about the logical statements and the functions. First we're going to not talk about if here if to give a condition and the output value. If the condition matches output [04:32:09] value of the condition doesn't match here. So let's go ahead and apply individual customer. Okay. Which credit card do they qualify for? So we will simply give them two categories right we'll say that they are high credit they [04:32:23] are high credit if uh their credit limit is above 50k all right we'll call them low credit if their credit limit is less than 50k so if there's 50,000 or below here so let's see how do we go ahead and get that so here I'm writing select in [04:32:39] select here uh we are getting it from where we getting it from the customers where we getting it from the customers table here so once that is established [04:32:53] get what we want. So from the customers here let's get their customer number also let's get their name here and then we are looking for their credit limit. want to add one more column where we have these categories assigned. So now [04:33:09] we go ahead and type the if function here. See here is the if function. Now we open the parenthesis here without any space everyone. And what is the condition here? Condition is that go and check the credit limit. If the credit [04:33:22] limit is greater than 50k, if it is greater than 50k here, if it is greater than 50k here and the condition is matching, go ahead and type [04:33:34] high credit. Go ahead and type high credit. If it's not matching, so value credit. If it's not matching, so value if true, that is high credit right here. if true, that is high credit right here. And if a response here go ahead and give [04:33:49] And if a response here go ahead and give local as the output as the output right there. So we are asking if function here to do that. Of there. So we go ahead and give it a name here. So I'm going to give the name here [04:34:03] as customer categories. You can write the entire name too. the entire name too. And see we have a calculated column. There is no calculation there. Simple logic application right there. I will [04:34:15] beautify this so that it looks better. Right? See how easy it is to understand now. So go ahead and get this please. Simple logical function right there. If next question here is go ahead and try to solve it by yourself first. Okay. Uh [04:34:29] just solve it with me. We'll solve it step by step. It says that assign a performance category to employers based on their job title. What are the someone is president okay we will give them a category of top management so not [04:34:45] performance category let's give them management categories that's correct and if they are manager any manager right if there any kind of managers out there we'll call them management and if there is anyone else other than manager and [04:34:58] president we'll simply go ahead and call them staff okay so uh tell me here first tell me here which table are we talking about here which table we have to work about here which table we have to work employees table right very good [04:35:13] out is that we have to work on the employees table I'm getting this out here now we go ahead and type select now in select here which columns do are we in select here which columns do are we looking forward [04:35:30] number right and we want their name there are here and let's we of course title. We need these three columns, right? Yes. Now tell me how many [04:35:44] conditions are there that we need to check right now? Because after job title here I am we we need to go ahead and type if there okay but if can check only one condition and how many conditions do we have to check here right now? Three. [04:35:57] So how do we check these three conditions? Right? We write the nested if. So before I go ahead and write the nested appear maybe there are few newcomers out there. I will go ahead and explain it here and then we I will go [04:36:10] ahead and type it out right because when if I type it out directly it might look a little bit uh complicated. So we will first start with if everyone right inside the if pair we have to give a condition. What is the first condition [04:36:23] here? President right. So uh we can go ahead and say that if if the job title here the job title here is equals to pres president here if it is equals to president here and if this is true if [04:36:39] president is written there and if it is true here what we want in the output top true here what we want in the output top management everyone what if this is false then what do we do now what if job title is not [04:36:55] president what we do Then we ask SQL here to check for another condition. Right? We ask you to check another condition. So if we wanted to check another condition here, what is the next condition? In the job title, [04:37:07] somewhere manager has to be written. How do we find this kind of condition everyone? We will check one more condition here. So how do I go ahead and write this condition that somewhere manager should [04:37:20] be there in their job title? How do we write that? Wild cards. Absolutely right. So we can go ahead and say that job title might be like anywhere manager to be written. So here when we are asking it to go ahead and check for [04:37:36] another condition okay we have to go ahead and after this value if true when we it will ask us value if false we will have to go ahead and type if again since we are typing if again here and we asking go ahead and check if job title [04:37:51] condition we are giving here again we have two options either this is going to be true or this is going to be false if this is going to be true here what is the output management We want them to write management here. [04:38:07] If this is also false. So the first condition we have given that is false. That's why we are checking second condition. If second condition is also false, what happens? What do we do now? Next here star. We simply go ahead and [04:38:22] say that whatever is left now you go ahead and tell them it's a star. That's it. And now here we close our f that we have written the second time. we have also closed the if that we have written the first time. So we have to take care [04:38:35] of parenthesis here. Okay. So if this is the parenthesis we are closing for this and this parenthesis of if we are closing it for here. So this is nec. If more conditions there and keep on writing if every time we check a new [04:38:51] condition. So go ahead and see let's go ahead and see here how do we write the nested appear. So we follow the syntax here. So we are typing here if inside here we are asking you to go and check is this job title is equals to president [04:39:10] if it is president what we want in the output we wanted to write top management right there we ask it to go ahead and type top management till here everything is fine now comes the catch here because we type a comma again and we type if [04:39:23] again and we ask it here that now go and check another condition what's the another condition that go and check job title again but this time the job title should go ahead and find out manager now that manager can be written anywhere I [04:39:37] have no idea so that manager can be written anywhere so that manager can be written anywhere in inside the entire phrase if job title is like manager there we type a comma again and uh we want to type management [04:39:50] in the output now if it is not the top management not the management it is Simple stuff. You go ahead and type the stuff right now. We close the bracket here. First bracket and the second bracket right there. And [04:40:05] I'm going to call this as management category. [04:40:20] Okay. So, right now I'm going to beautify this so that I can show you properly. See if if and likewise you can keep on writing multiple conditions right here. Uh one thing that you have understood here is [04:40:33] while typing nested if we have to remember by ourselves what to write type where we are not getting any help for the syntax like we would get in Excel right we can when we write something Excel actually guides us with the syntax [04:40:47] SQL doesn't do that since SQL is not doing that for us here the best way to doing that for us here the best way to work with this multiple conditions is case and when and we will go ahead and see how do we use case and when [04:41:00] statement to apply are multiple conditions. Great. So if there are multiple conditions, writing nested step is going to take a lot of time and a lot of attention right there effort. Instead of that we use case in a statement. So [04:41:13] and when here this is how case and when looks like the syntax here everyone it simply says that here okay the case statement starts with case and let me [04:41:25] write when in so whatever we are working in inside that when condition one is in inside that when condition one is there uh if condition one matches then give the output one if condition two matches then the output two and else [04:41:39] here it's quite easy to follow true so So let's go ahead and get this from So let's go ahead and get this from here. So uh we will go ahead and use the same customers table. Again we assign the customer categories based on the [04:41:51] credit limit. But this time we'll go in detail. We give them the credit cards. Here we are giving them gold credit cards eligibility. If they're above 75K we're giving them silver category here. If they're between 75 and 50K and if [04:42:05] they are below 50K here, okay, we are giving them bronze. there if they are below 50k. So let's go here and see how does that [04:42:19] how do we write that here. So one thing is similar. Okay, we know that we are working from customers table. I will get this out of the way. And now we go ahead and we type select in select here we can ask you to get [04:42:32] customer number customer name [04:42:44] when. So here we are writing that case. See it's a keyword right there. And now we go ahead and type when. Now what is when it's uh when here we are we will condition that we're looking for the correct limit should be greater than [04:43:01] correct limit should be greater than 75k. Okay greater than 75k if it is greater than 75k then then we want to give them the gold credit card. They're eligible for that. After this here we go ahead and we type in when [04:43:17] ahead and we type in when again credit limit here. If credit limit is between here is between how do we go ahead and type between how do we go ahead and type between here. If credit limit is between [04:43:30] between here. If credit limit is between between 75k between 75k and 50k here and 50k here then we are giving them silver right here. Else meaning if none of [04:43:44] these two are true here we can go ahead and type else. Else if none of these are and type else. Else if none of these are here go ahead and give them bronze. If none of these uh conditions matches, go ahead and give them blotss. Here [04:43:58] everything is uh is done right. But since case and when we will go till infinity like this we have to go ahead and tell SQL that see this is an statement and this statement ends here. So we will go ahead and type end just to [04:44:13] tell SQL let's see this ends here and after ending it we have to go ahead and give this a name because this entire thing cannot get go inside the name thing cannot get go inside the name here. So here we are writing a title [04:44:30] right there from the customers table right so this is how we are writing case and rent I'm write I was writing that in one single line okay and this is how it one single line okay and this is how it looks like after reformat everyone okay [04:44:44] so uh simple practice practice exercise everyone we want to find out and check whether the customer has a sales representative them assigned or not. Remember uh we had uh multiple null values when we checked it right now with [04:44:57] the group by. So we want to find out assigned or not. First thing is simple. We are working in the customers table. Okay, that is fine. We are also aware that in the select here we simply want to see whether the [04:45:11] customer has been assigned a sales representative or not. So this is what I'm getting here. Of course, I'm going to write this as sales rep since it's to write this as sales rep since it's too long name here. [04:45:27] whether the person has been assigned or not. Let's see find this out here. We'll not. Let's see find this out here. We'll simply go ahead and ah use if function here. How do we decide that? First tell me how many conditions do we need to [04:45:40] check. Let's understand here we have just one condition. What is the one condition? One condition here is if customer has a sales representative. If customer has a sales representative here, there are two outputs where we can [04:45:54] say yes or no. So we have to check one condition and we get the two outputs condition and we get the two outputs there. So this is how we work here. Now here we will uh if I have one condition and two outputs, we use if function [04:46:06] right here. So inside if here we will uh what do we ask? How do we ask this? We simply ask it here. Then go ahead and check if this sales representative check if this sales representative is null. That's it. Because we are [04:46:21] automatically getting the customer number and name there and sales rep. We'll ask it if sales representative is null go ahead and type not assigned. [04:46:33] Otherwise type assigned because if there is a number there it is assigned. And uh is a number there it is assigned. And uh let me call this as a status. [04:46:47] Wherever there is a null value, it says not assigned. ways to type it too. I'm not saying this is the only Okay. Uh I have to observe [04:46:59] one thing here. Even when we are learning about group by, we are learning about the statements out there. We need to go ahead. We are still working every time on one single table. every time we are working on one single table right [04:47:14] what if we have to go ahead and find out for example we are working right down here okay we have got customers table and all the tables out here what if I want to find out that which sales rep has given us uh the maximum sales so [04:47:29] because in the customers table we don't have any sales data but we do have sales rep data here in employees we can find the name of the employees too in employees table we can find name of the employees here. But from where do we get [04:47:42] the sales data everyone? How do we get the sales data that how much sales has been made? Total amount the customer has paid that has been assigned to them. Which table do we need for that? Where we can find out which customer has paid [04:47:55] how much and when or how many number of orders that person has placed under that sales representatives. So we have got orders table here right after orders table we have got payments table here that how much that particular [04:48:09] customer has paid for that. Okay. So right now we have orders payment rate customer number. So if I want a very simple answer now this is a very simple it I want to see that how our sales representatives have performed and how [04:48:23] much sales uh their customers have done so far. It seems like a very good direct question here. But the problem is now we are not working on one single table. We want to go ahead and work with multiple tables together. Now how do we do that [04:48:37] here? Because in the form here we don't want to use one table. We want to go right there. So when you go ahead and want to work with multiple tables here, this is where joins will come in. This is where the concept of joins will come. [04:48:52] Now what is joins here? Join here is very simple. Okay, it goes ahead, it merges the two tables there and remember it merges two table only at once. It can merge only two tables together. Okay, so it merges the two tables there and in [04:49:06] the output it will give us one big single table. It will give us one big single table. It will give us one big single table that is joints that is joints. Okay, how many types of joints are there in MySQL? We have uh very [04:49:20] basic four joints here. Okay, very basic three joints here. first is left join, the three joints that are present in MySQL. There is one very specific join for MySQL and that is called as cross. So when I say that this present in MySQL [04:49:36] here. So now when we say that these are the joints present in MySQL, I mean that the keywords are present. I mean that that the keywords are present. Direct it will do the left joint. If I say right, it will do the right joint and so [04:49:49] on. However, in general when we talk about data models there out there and uh database out there, we have some other joints too. For example, full auto join. join here. But this full order join is not present in MySQL as a keyword there. [04:50:06] not present in MySQL as a keyword there. So, we have to uh go ahead and uh use a permutation combination of other keywords to uh make a full auto join. Right? So, full auto join is not present directly as a keyword in my SQL. is [04:50:19] present in MSQL. It does present in MSQL, Oracle and others. Other type of join here that we can perform within MySQL and uh and anywhere else also is [04:50:32] self join. And if you want to go ahead and do a full order join inside MySQL, there we go ahead and we use union for you. We'll understand about this union keyword too. First let's go ahead and understand [04:50:47] about joints there. Now how many of you are looking at joints for the post time? concept here first. So let's go ahead to reshare my screen here. You should be looking at an Excel sheet. Okay. [04:51:10] about the common when we talk about merge the merging the two tables here. Okay. We cannot merge unless we don't have a reference column. All right, we cannot merge two tables unless we don't have a reference column. So before we [04:51:22] move forward, let's understand the what is meant by merging of two tables here. Okay, let's talk about that. As you can see here, just focus on what I'm trying to show you here. Do you see we have got two tables everyone? We have got order [04:51:34] ID and customer ID right here. Yes, we have two tables here. Now in this order ID, right? Now we know that these are the orders here. These are the orders placed for these products and this much they have paid so far. Of [04:51:47] course, we want to go ahead and analyze it till uh here when we have observed that the customer name is right there, customer name is here and more customer address, phone number and all that more details of customers are here. So why to [04:52:02] figure out that who has placed the highest order here, I would like to know the name. So we want this customer number to be fetched right here. Okay. So, we want [04:52:14] the customer number from the table number two which is placed on the right hand side of the sheet here. You want to fetch the customer name right here. If I want to do this here, if I want to go ahead and do this here, can we simply go [04:52:26] ahead and copy and paste it right here like this? Is it correct? Can I simply go ahead and copy and paste it right here? Because I want to merge two tables here. Is it right? Why not? Why it's not right? What do we need to do then? [04:52:39] Customer IDs are wrong. Yes. Right. We cannot simply go ahead and copy and paste, right? We cannot just simply go ahead and copy and paste. We have to go ahead and match the individual customer ID too. Meaning we need to have a common [04:52:51] column. What is the which one is the common column we have here? Everyone, what is the common column we have here? Customer ID. Customer ID is a common column. So if this is a common column here, how do we [04:53:05] work with it? We'll go ahead and read if this is CR1. Yes. Now we will move from here to here and we'll check. Okay. Uh, do we have a CR1? Yes, we have. What's its name here? John. So, we will go ahead and copy and paste. Right. Copy [04:53:19] and paste it right here. Now, we check again. Okay. This is again CR1. Now, we will check again. This is John here. We'll go ahead and paste it again. If this is CR2, we'll go ahead and find CR2 here. Okay, this is Peter. This is CR3. [04:53:31] This is uh here. This is CR4 here. And we'll get it there. This is CR3. CR4 here. We are matching them right here. Now, do we have CR6 here? [04:53:43] Do we have a name of CR6 here? So, what do we do here if we don't have a CR6? [04:53:55] It's null. There's nothing there. Do we have a merge table right now? Do we understand what we did? We simply matched at everything and we kept it. Now, what about others? those who are looking at after a very long time or for [04:54:09] the first time. Do do we understand what we did just right now? We merged the two tables together based on a common column. Yes. Now observe one thing here. We are uh when we were fetching everything here did we even cared about [04:54:23] this CR5? Currently we have currently in the CR5? Currently we have currently in the merge table here. Do we have CR5 in the merge table? No. It means you compromise this data set [04:54:38] for this current analysis. For this current analysis, do we need the name of this person here? Why don't we need the name of this person right now? Because this person has not done any sales yet. No need of that because this person has [04:54:52] not done any sales here. So if there is no sales here, we don't need uh uh it's their name too. Exactly. Because it is not part of the sales table yet. This kind of merge right now. So what we have done here we have made sure that all the [04:55:06] rows from the left hand side table are intact. All the rows from the orders compromising any data here because this is our main table. Okay. So we are keeping all the data and rows intact here. And we are only fetching the [04:55:20] matching data from the table number two. This kind of match is called as left join. This kind of merge is called as left join where we'll make sure that we [04:55:32] are keeping all the rows from the left table and we have only matching values from the right side table there. This is called as left join. Do we understand left join right now? Yes. Now can you please tell me if this is left join what [04:55:47] will be the right join? The opposite is going to be in the opposite right join here. will keep all the rows from the right side table here. Okay. And we keep only matching values right here from the left [04:56:01] side table. Right? Now, right here in this particular scenario, right join is this particular scenario, right join is not a good choice. And uh 99 95% of the times you will be using the left join and there are very rare cases where you [04:56:13] will do the right joint. Very rare cases out there. Okay, that is right join here. Now, what is inner joint? If this is left and right here, what is inner join here? inner join is it will keep only matching values from both the [04:56:26] tables here. Meaning we will not fetch CR5 neither CR6. Why? Because CR5 doesn't have a name. CR6 doesn't have a name here. And this CR5 has not done any sales here. Has not given us any sales here. So in the inner join here we will [04:56:42] not be fetching anything which is not common. So we keep only common rows here. Okay. So this is inner join. So this is inner join everyone. Right? If this is inner join here, uh is it the right choice for now? Is this the right [04:56:58] right choice for now? Is this the right choice for in this scenario? Yes. Is inner join a better choice? Let me show you here. Okay. When we do go ahead and do the inner join here, what happens? We have not even compromised one. We have [04:57:12] compromised two details here. First is this person has given us the most sales. We didn't have any name of this person here. But this person has given us the most sales there. We have compromised our data set because of the inner joint. [04:57:25] our data set because of the inner joint. Okay, it is just like the inner join. So here in inner join here we are compromising a major detail. Most of the time in the real time we will stick with the left join here. Why? Because in the [04:57:38] inner join we most of the time we'll be compromising our data set just because of few values or few details are not available. So it doesn't matter if I have the name of the customer or not there. But if I have the customer ID, I [04:57:51] can figure it out later. Right? So that's what inner joint is. What is inner joint? And when this inner joint is like intersection in my diagram, yes, it will look like that intersection. Yes. So this is inner joint. Right? So [04:58:04] everything from the left side. We have measured everything from the right side. In the inner joint, we have the common table right there. But it compromises the values. Of course, we do not have null values, but it compromises the data [04:58:16] set. Right? What is the full outer zone? What is left? Which permutation combination is left? Right? So what happens here is full order join. What is left? We will keep each and every row from both the table. It will going to [04:58:29] give us too many null values and too many details which are not required. So most of the time full order join is also avoided at all cost. So the types of joins that we have seen so far. So in [04:58:42] MySQL we have understood that there are uh basic joints are there like left outer join, right, right outer join, inner join and pull outer join. Other than these four basic joints what we have seen [04:58:58] course self join is also written here. What other join we have heard about? We are also clear that inner join, left join and right join these are the three key words that are present in my SQL. Pull auto join is not a keyword that is [04:59:13] present. Neither the self join is a keyword that is present. These both are keyword that is present. These both are the concept which uh these both are the concept of merging the table together. However, pull join is achieved pull out [04:59:27] join is achieved with union operator and self join is achieved with inner joint. So we will go ahead and of course the second one here last one here is the [04:59:40] cross join. So yes cross join is a keyword that is present with us within these mysql here and this will deploy with each platform. So left right join and p order join that is present in msql and Oracle and other places but not in [04:59:54] here. So let's go through the concept here. I will go through the con concept and do the hands-on practice that will be much better than that. So what is inner join here? Inner join is the one where uh we will get only the matching [05:00:10] values. Only the matching values are will be given in the output that is the inner join. What is the left join here? Left join conclude uh will make sure that we have all the data from the table number one and we are getting only [05:00:24] matching data from table number two. What is the right join here? In the right join here, we getting all the data from the right hand side here and all the data that is matching from the table number one. [05:00:36] join doesn't care. It will simply go ahead and grab all the rows present in both the tables. So these are the four basic joins everyone. Now we'll go ahead and we'll see the hands-on workers. So we are at debug bench everyone and today [05:00:51] we are at debug bench everyone and today we will see in detail and remember that while we are learning about joins here right we'll go ahead and create our own small table so that we are able to understand how to type join better. [05:01:07] So with the joints here we will also learn about DDL that is data definition language. Okay. definition language here. We are going to create a database. Inside database we [05:01:23] are going to create tables and inside the tables we'll go ahead and use DML and we will insert rows of data inside it. Okay. So first we start here we start a small and then we go ahead and join them together. Let's first go ahead [05:01:38] and create database. Right. We create the schemas here and understand joins how to write them and the variations of it. Okay. So first here I would like all [05:01:50] I'm going to show something and how to create the table right now. So in here I'm going to say that I want to go ahead and create a database. I'm going to name the database as DB joints because we're going to learn about joins. So this is [05:02:03] database we're using for the purpose of joins here. I am not executing it right now. Inside this DB joints here I will make sure that from now on we will be using DB joins as default. After this here we will say that I want to create a [05:02:17] table here? We want to create a table in DB joins and dot. So when we talk about creating a table here basically we go ahead and we prepare the structure of a table. To create a table here we go ahead and we [05:02:32] create the structure of a table. What comes inside the structure of a table? When you type a create table here, first thing that we do is we go ahead and we give it a database name and then the table name that go ahead inside this [05:02:45] database and create a table with this particular name. And inside that table, inside that table, we want you to go ahead and create column number one which should have a data type like this number, a string and date. We have care [05:03:01] flo decimal double the small integer big integer date and time all those are data types. So we'll go ahead and assign the name to the each column here and we assign the data type too. And like this we will keep on going and giving the [05:03:15] column names and everything else here. So what we do here is we go ahead and we prepare the structure of the entire table just by writing this here. Remember that we are only creating the structure of a table. Yes, we only [05:03:30] creating the structure of a table. It is still empty. So we'll go ahead and uh writing create table. This is the database name. Let's uh create a simple table here for employee info which we can all relate with. So we're creating a [05:03:43] table called as employee info here. Uh of course the first column we can think about is employee ID. Employee ID here. Uh let's keep the character four here. characters. We are keeping it easier. After employee ID, we can have the [05:03:57] employee name there. Employee name can be of variable characters. Every employee name is going to be different. So we can say maybe employee name can go to 50. And then their skill set what they are working with. So we [05:04:09] will go ahead and keep that as car again. And this is 52 because these are the skills that we are talking about they are also going to vary. And then we have their experience. Experiences can be in um years. So we are keeping it [05:04:24] integer for this purpose. So this is a small table. structure that we are se have created a database we have created a table but I'm not going to execute any of it right now after creating the table here we are inserting the values. Now [05:04:39] how do we insert the values here? To insert the values inside a database here we again go with the same structure. We say that see I want you to go ahead and say that see I want you to go ahead and insert the database. Insert the values [05:04:53] into the employee info table where the employee info table is. Employee info table here is in certain database in a certain table. And what we want to enter values. After entering the values here, now we [05:05:08] will go ahead and actually write individual row there. So for example, there, we'll go ahead and type that column number one data for row one. Then we have uh again a column two row two data. So for example, here we have a [05:05:24] skill set. Third is the skill set. So we'll be typing the skills here. Fourth is the experience. We're typing the experience in years. We type a comma. This is just first row. Then we go ahead and we add a second row. So maybe we [05:05:36] this is the uh employee ID there and here we go. This is some name there and now we have got a skills there. We give the skills there and here we have [05:05:48] give the skills there and here we have some experience like this. We'll keep on moving and writing individual rows. We'll keep on moving and typing individual rows right there. This is how we insert the values of row by row. So [05:06:00] if we have four columns there, we will be typing four values. So here we go ahead and type insert and into where into db joins info right here into db joins info right here. Now inside this here we want to go ahead and [05:06:16] add up the values and how does the values look like. So here we go. We can values look like. So here we go. We can say here that this is a123 here. That's the employee ID we are giving with four characters. Since all of this is either [05:06:30] characters. Since all of this is either alpha numeric or it is right now your numerical here or alpha numeric or the alphabets here we have to type it within the quotes there. If these are in numbers the numbers will go right away. [05:06:44] will be written without the quotes because it's a number it can be read as a number. Now like this you'll go ahead and type each and every row here. whether a 1 to3 here maybe this a 1 to4 here and more names and escal so I'm not [05:06:59] going to type all the rows here it is going to take a lot of time to type each of that so I have pre-written rows of data here and we are here done till here so now what we have done we have went through we have created a [05:07:13] database we went ahead and we created a table right there and we inserted the values now how do we go ahead and and execute this one Now listen to me very very carefully everyone. I want everybody's attention here please. I'm [05:07:27] going to go ahead and select all the three statements together. These are three different statements. Right? See the semicolon in here. Right? All three select them all together. And we are going to go ahead and click on the first [05:07:42] lightning bolt right here. Why first lightning bolt here? Because this means lightning bolt here? Because this means execute all. This means execute all the selected rows. So we'll go ahead and click on execute all right there. So it [05:07:55] it will go ahead and execute them one by one. One by one and it will be executed right here. So I'm going to click on here. Look at the output everyone. Now once it is executed of course nothing will be shown in the scheme. So [05:08:10] we have to go ahead and and refresh it. Once we refresh it we see it. See and inside this DB joins here you'll find the tables and inside tables you'll have the columns [05:08:23] I'm going to go ahead and provide you this right now go ahead and execute all with the first lightning bolt here is the link remember select all the three great now let's go ahead and create another table okay another table will be [05:08:38] created exactly similar to this we'll go ahead and type create table because we need two tables to join everyone we'll go ahead and type uh type create table again and again we are going to create table of 50 joints here and let's name [05:08:50] it employee salary. So we'll have the employee salary details here and I'm as employee ID because that's what we need here. It is going to be exactly similar to here because data type has to be similar. It is the exact same column [05:09:07] exact same column name. Now here we need designation there. This is designation. designation there. Let's give the maximum 25 here. Meaning maximum 25 [05:09:19] characters can be inside this particular designation name. And we have salary details. So we'll go ahead and keep the salary details under float is decimal. Okay, remember that here. So that's here we have created a [05:09:33] table. Now inside this table, let's go ahead and insert the values here. So let's insert some values here. So we want to insert the values inside the DP joints. This is the table here and we're going [05:09:45] to go ahead and set some values here. Remember three columns, three values everyone. So first is employee ID. So let's say the employee ID is uh 1, two, three here and let's say their designation is 01 for example and then [05:10:00] what we have salary. So I'm writing some random numbers here and sal here. So random numbers here and sal here. So again let's go in A126 here and maybe they will build two and again another number here [05:10:17] for their salary part. Here we go. This is another employee number here. Let's say they also belong to L2 here. And again a random number here for the [05:10:30] salary part. Let's add one more row. uh right now. So there are too many user. Okay. So here we go. I'm adding one more part here [05:10:42] we go. I'm adding one more part here and this person belongs to L3. So these are different designations right now here. And let's have again a right now here. And let's have again a random number right here. [05:10:59] rows we are adding up. Okay. So this these are the four rows. And let's go ahead and create another table. How do we create another table everyone? We will go ahead. We select everything. We'll go ahead and we select everything [05:11:13] right here. Okay. And we execute all together. Once I execute all together only once, we refresh the table here. We get to see the employee salary right get to see the employee salary right here. Okay. So, how do we go ahead and [05:11:27] write join everyone? So, to write join here, we again have a syntax. the way to type it. How do we type joints here? We start with select as usual, right? But two tables right there. So first thing that we are going to do here is I'm [05:11:42] like. So this is the employee info table everyone. This employee info table has how many rows? Five rows right here. Right? So this is our five rows from the employee joints table. Let's look at the employee salary here. It has got four [05:11:57] It has got four rows right here. So this is our second employee salary table right there. Now what we want here we want to go ahead and see how to type a join. We will go ahead and we want to go ahead and merge both the tables [05:12:10] together. If we type we are want to move forward and see how to join the tables we start with select. We start with select and in front of select right now we'll say I want all the columns from the merge table. We have no choice for [05:12:25] now about the columns because you have no idea which columns are there are going to come when we merge it together. After here we go ahead and type from where do we type the joints here inside the from because two the from keyword [05:12:38] will inform that okay this is where we have to go and get the data set. Now in tables here. So you'll say that go and get go to the employee info there. Now where the employee info is employee info is in the DB joins. So you'll say go to [05:12:53] DB joints and go to the employee input and then join it with the second table. So what type of joint we are looking at? So let's say we are first looking at here the left joint. We are first looking at the left joint. So we type [05:13:06] the word uh keyword here left joint. Now which one is the second table? Second table here is again that go DB joins and employee salary. This is our second table right here. Is this enough information? Now we have to go ahead and [05:13:21] also define which one is the common column. So we go ahead and we type it here using. Using is a keyword which will inform which one is the common column. Which one is the common column between the two tables right now? [05:13:40] employee ID. Yes, thank you. So we go ahead and type employee ID here and then we close this statement right there. Now why are we writing employee ID here? Why we are able to write the employee ID here? Okay, for a very simple reason [05:13:54] that we are simply informing right now seeker that see I want you to merge the these two tables together by using the employee ID column and that employee ID column we are mentioning it only once. Why? Because the spelling of common [05:14:09] column is similar. It's exactly same. Common column is exactly same in both Common column is exactly same in both the tables. [05:14:21] real time, this doesn't happen. We do not have the exact same name. For now, we have it. So, we are going to go ahead and execute it right here with simple uh bolt or control enter. And look at the left join everyone. [05:14:35] Do you see the left join right now? How many rows do we have here in the left join? Five. Why five rows? Because employee info is my left table and this contain five rows. So here we will also have the values [05:14:49] and look at the right side table here we have null values only the common uh rows here only the common rows here are having the values written properly till the end. However you must be wondering that why we are [05:15:03] typing DB joins DB joints multiple times here. Okay, if you will go ahead and you make sure that you are making sure that DB joints is a default then we don't have to type that. We can then type directly. So if I want to go ahead and [05:15:18] and start with select here and we type a strict science and we'll say that from from where now we can go ahead and type directly that okay go to employee info and this time let me type right join here right join it with what we have [05:15:35] employee salary right here but now this time instead of typing using here we have one more option if the names are not exactly same. Okay. What if it is employee ID and maybe employee ID space is there. If there is any kind of [05:15:50] discrepancy or the change maybe it is writing employee number and another table it is writing employee ID right is still they both same but the names are different. In that case here we use another keyword font. In this on here we [05:16:05] another keyword font. In this on here we can now define that go to employee info. Inside employee info, you're going to find a column called as employee ID find a column called as employee ID which is exactly equals to the employee [05:16:17] salary table which has a column called as employee ID. So we use on here right? So prefer the prefer uh preparable method is always [05:16:29] on. Why? Because we have no idea that uh whether it is always going to be same or not. Okay. So preferred method of defining a common column is so if I go [05:16:41] absolutely correct. And when we execute this look at the right join the and uh the output is four rows because the right side table has four rows here and see we have got the entire employee uh from the uh different side here we have [05:16:56] got only two matching rows right here. All right. So this is how we can go ahead and type left and right join. You can see the difference. We have to simply go ahead instead of black join we are simply typing right here. Let me go [05:17:09] ahead and beautify both of them here just to show you how it is going to look like and right here. Okay. Go ahead and find out left and right join. This is how we type join. Take your time and see both [05:17:24] the joints right here one by one. First we we are typing the keyword using to define the common column and then we are typing on. Great. So see right now, okay, we have seen uh left join and the right joint. Easy way to type it out [05:17:38] here. Now here after this right join here, let's go ahead with the inner joint. Let's go ahead with the inner joint. Now there is one more way to type. So before I go ahead and show you here, let's see [05:17:52] again we go ahead with select. We say everything here and from in the from here we want to do the inner join here. So if I go ahead and get the employee info here and we want to do the inner join. So we type inner join here. We [05:18:07] join. So we type inner join here. We type inner join here with our employees. Now here I'm going to show you two two three possibilities here. If I go ahead and type using because we have the exact same column name here. Okay, this is the [05:18:23] inner join output that we see right here. We have employee ID, name, a skill set, experience. And from the second table, we are only getting designation table, we are only getting designation and salary right here. Okay. What is the [05:18:35] other way here? Other way here is this will remain exactly similar. And now we will remain exactly similar. And now we go ahead and we type on. Now in the on here the again one uh major problem is we have to go ahead and type the entire [05:18:47] thing, right? Employee info equals to employee ID, employee employee salary dot employee ID. So we are going ahead and typing this multiple times the table name. So what we can do when we don't want to type table name multiple times [05:19:02] here we go ahead and we give alias to table name. We go ahead and provide alias here to table names. We'll simply say that see this is employee info table right I'm going to call this as t1 from now on and [05:19:17] I'm going to call it employee salary as t2. So when I go ahead and type on here t2. So when I go ahead and type on here I can write T1 dot and from T1 that is we are saying employee info here get the employee ID which is going to be equals [05:19:30] to the table to employee ID. Okay. And now if I go ahead here and execute this right there I'll get this answer right there. So when I type on [05:19:43] here observe that I have the exact same output. There is only one difference. output. There is only one difference. Can anyone notice that difference here? [05:19:56] easier to define on and the common columns. It has got no job for the output. So I would like you to go ahead and see the inner joint right now. Go ahead and see the inner join here. Okay. And tell me the difference between the [05:20:09] And tell me the difference between the two outputs. Same inner joint output but go ahead and tell me the difference. At first we are typing using to define the common uh column and second we are defi using on here to define the common [05:20:23] column. Go ahead and get it. Go ahead solve this and tell me do you see any difference between the output of using and output of [05:20:36] these two? And look at the output when we are executing with the using here. Since using assume that we the both the column [05:20:48] both the tables here have one single column name here it will keep employee ID only once. It will keep employee ID only once. However in real time that's not the case here. So when we are going ahead and and executing here with on [05:21:04] right now okay we will go ahead and see that employee ID here is added twice one from the table number one from the table number two even though they have the exact same uh data set inside it okay but since we typing on here with the [05:21:20] pre-assemption that the both names are different these will be added twice that is the difference here the preferred is on because in uh uh real time practices that's what we use and it is easier to keep track. So this is how we type the [05:21:36] alias and this is how we use uh this is why the difference between using and on and yes this is how we type alias and we use alias right here. It is easier to go use alias right here. It is easier to go ahead and define the two separate uh it [05:21:50] is easier to define the common columns with the help of the alas right here. here. What is inner join? Everyone have you observed something here in the inner [05:22:03] join right now please pay attention here in the inner join here one very distinct option is here that the employee ID that are present in the first table that are going to be exactly equals to the employee ID present in the second table. [05:22:20] employee ID present in the second table. Correct. This is what uh inner join is. Out of these two here, this uh values in the employee ID one should be exactly equals to employee ID present in the second table. That's what inner join is. [05:22:33] Because of this uh unique quality here, we can actually type the inner join without the keyword inner join. And how do we do that? How we can write we can create a inner join merge table without the keyword inner join. Here we'll go [05:22:48] ahead and type the wear clause. We'll go ahead and type the wear clause. We use the wear clause there. So let's let me show you how to type it uh there. So this will exactly remain similar. Let's write everything from [05:23:02] employee info and after that we type comma here and we say employee salary. So we need uh the data from both of these table there. Now we go ahead and type the wear clause and we'll say that where the table one employee ID is [05:23:16] exactly equals to table two employee ID. So without even typing here without even typing the inner join okay just by typing the comma here we will still get the output of inner join. See exact same. So most of the time whenever you [05:23:32] will see inner join written there it will be written here not with the keyword uh here but they will be using the where clause here. Why? because this [05:23:44] wear clause will act as a simple filter and it will be quite more quicker in a larger table as opposed to inner join right here. So whenever uh we are typing inner join most of the time professionally they will be using the [05:23:58] where clause to define inner join and remember that this can be done only with the inner join and not with the left and right. For left and right we have to type the keywords. Write down what do you mean can we write from t1 comma t2 [05:24:13] these are aliases right so in this line we are actually defining that these are aliases so that we can use them afterwards here so this was very simple left right and error join everyone okay let's go ahead and [05:24:27] learn about full auto join here about full auto join as I've already told you there is no keyword for this all right so what we do here is we go ahead and use an operator which operator do we use here we use the operator union. [05:24:41] I use the operator union. What does this operator do? This operator will go ahead and copy paste the table one below the other. So [05:24:53] append action. What is append action here? It is simply going to go ahead and copy two tables one below the other. Let me give you an example here. Let's say uh every month you have been given the monthly sales data to you. Okay, for the [05:25:07] monthly sales data to you. Okay, for the analysis purposes. So, uh once you get got it for January, then you got it for February, then for March. Now, they're quarter, they're asking that okay, now can you give me the quarter wise [05:25:21] analysis? So, in this case, here you have got three separate tables. So, how will you go ahead and join all the three tables together? They have exact same table structure. You're getting that data every month consistently. what you [05:25:36] will do in that case. So if these are the three data set here and you're getting it one for January, one for February here, how do you go ahead and February here, how do you go ahead and keep them together in one single page? [05:25:59] example, if these are three Excel sheets you have one for January, February and March and now they are asking you that no go ahead and give them me analysis by combining all the three files here. How will you combine them as one? [05:26:16] Yes. How do we merge it? We merge it like this one below the other. So basically we're copying pasting them one below the other making sure that we have exact same structure of the table. Okay. So this here this here is called as [05:26:32] append action. Now this append action we can do here with the keyword union. Right? So as we have the keyword union here we have one more operator called as union on. So we have got two operators. [05:26:47] One is union one is union on. What is union here? Union here when it goes ahead and copy paste two tables together here it will make sure that there are uh no duplicates here. It will remove the duplicate rows. It will remove the [05:27:02] duplicate rows if there are any. After merge what will union all will do? Union all will make sure that all the rows are still there. Okay. So how do we create the full order join with the help of union here because we don't want the [05:27:16] is full order join? What is the what was the definition of full order join? We simply wanted to go ahead and get all the rows from both the tables right if you want all the rows from both the [05:27:29] tables here. Meaning we have to go ahead and combine the left join and right join. So whatever is the output from the left join will go ahead and we append it with the output of the right join. So let's see how we do it. How do we do [05:27:44] that? So this is our right join here right? So I'm keeping it right there. This is our right join and I will convert this into left join. So if it is left join this is how we see the output employee ID name and skill [05:28:00] set and see we have got lot of null values here. Now if we have the right values here. Now if we have the right join here look at this right join output this one. When we go with the right join output [05:28:14] here, we or the left hand side we have got the null values. So we'll simply keep them one below the other. To keep them one below the other, first thing we do is we get rid of the first semicolon here because now this entire thing has [05:28:26] to be one single statement and we go ahead and write here post union. I'm going to go ahead and type union post here. So if I go ahead and type union, this entire thing will become one single statement. And look at the output. If [05:28:41] you look at the output here, this is the output from the left joint. This is the output from the right joint. This output is from the right joint and the upper one is from the left joint. And it is simply written one below the other. And [05:28:54] now we have the proper entire full order join in front of us. That's why union is written. We have seven rows in the output. Okay. But now if I go ahead and duplicates here. We go ahead and type union all here means keep meaning keep [05:29:10] all the rows there. The output has nine rows here. Why this output has nine rows here? Reason is when we are doing right join and left join. We have two common rows. Remember those two common rows are repeating here. So see here 1 2 3 is a [05:29:26] common row. 1 2 6 is a common row. It is repeating here. It is repeating here actually taking care of this repetition. Union all is not able to take care of this repetition. That is why in full orderment we will take care of union and [05:29:41] if we are merging or appending data from multiple timelines or time periods we'll multiple timelines or time periods we'll use okay go ahead and observe the difference between the two and this is how we go ahead and write a order join. [05:29:53] So we want to go ahead and see the customer numbers and name of all want to go ahead and see customer numbers and name of all customers along [05:30:05] with their order ids along with all the orders they have placed so far in the orders they have placed so far in the year 2005. In the year 2005 a very simple question, right? A very simple question here that we want [05:30:21] to see the customer numbers and their details along with their order ids. meaning they should have placed the orders in the year 2005. So first let's break it down. Okay, let's break it down here. Please tell me [05:30:34] first let's figure out from here. So please tell me which tables do we use from classic models to get these details. So customers table can give us customer number [05:30:46] order details. The order numbers are here. That's orders here. Now which one here. That's orders here. Now which one is the common column between the two? the common column between the two? ID, order ID or customer ID? So what [05:31:02] the two? But both of them in between there is a customer number or customer ID right there. Now here which type of join we should do here? Which type of join we should do here? Left, right or inner. How do we decide that? We decide [05:31:17] that in line with the objective that we are right now looking for customer are right now looking for customer numbers or customer details of those customers who have actually given the order so far in a particular year. So [05:31:31] what which rows are we talking about? Are we talking about all the customers and only uh and pitch only their order uh only the matching customers order ids [05:31:43] or are we looking for the common rows out there that if the customer has placed the order then only we need the detail or we need all the customer has to be there and if the order is placed we'll get only the related orders. What [05:31:56] are we looking for? Because in a customer's table, we'd have all the customers that are currently registered with us. But have they given order in 2005? Is that a guarantee? So do we want all the customers to be present in the [05:32:09] output? So in the this scenario here, we can go ahead with inner join. Why inner have placed the order so far. Maybe some customers registered with us, but they haven't placed any order yet. And if I do the left join, they will still be [05:32:25] there. All right? So that is why we'll go ahead with inner join here because we want only the customers who have placed the order so far. So you go with the inner join till here we are good right. So uh let's go ahead and start typing [05:32:40] here. So I'm typing here select as usual first I'm typing uh everything for now. Let's figure out from first and we will go back to that later. Now in the from here I'm getting customers table. I'm going to name that as C. We are going to [05:32:56] join it. And we will inner join it with the orders table right here. And I'm going to we are going to name orders here as O. Even if I don't write as there and write directly CN O will still [05:33:10] get it. But uh I will still type it here just for the clarification. Now when we type on here and we can use the sales right there. We can say that go to the customers here. Right? That is we are typing SC [05:33:25] get the customer number here which is going to be exactly equals to what you are going to see in the orders table. Customer number right here. Now we don't want the entire table. Do we want the entire table connected like this here? [05:33:39] No. We just want a few select columns from here. Okay. Now I would like you to pay attention on few things here. First here is do you see we have a customer number here this is the merge table everyone right do you see this is the [05:33:54] customer details right there when I am scrolling towards right here right here towards right after credit limit after credit limit we have start with the order number this is the orders table now if I move more towards right [05:34:10] the last column is also customer number yes meaning in the output here we have got two columns with exact name that is customer number. Done. Do we understand this part? Yes or no? That yes, we have got two separate columns and they both [05:34:25] are named exactly similar customer number and they both are coming from two different tables. Now so this is why this is why when we ask you that see I don't want all the columns here you go to the customers table. You go to the [05:34:40] customers table there and from the customer table you get me the customer number. Now here we need customer name right but customer name is not ambiguous it is the exact one single name here we can type [05:34:53] it directly that okay get me customer name what we want next here they uh we have we need order id so order ID is also singular it appears only once so this is order number right there after this here they are asking us for the [05:35:08] year 2005 right so let's go ahead and get order date that's what they're looking for date right here so we need only four columns. Since it was the common column here, we have to define from which table do you want this? [05:35:22] Otherwise, it will give us the error. Now, once you have this here, all right, are all dates are from 2005? Are all dates are from 2005 here? [05:35:42] 2005? How do we apply the filter? Select from where? We apply the filter with the where clause everyone. So select from and where. Now inside where what is the filter that the year should be 2005. Do [05:35:59] we have a year column here? No, we don't have a year column separately. But we can go ahead and extract the year as we have done before from the order date. Yes, absolutely correct. And we can say whatever year you extract from the order [05:36:13] date here it should be equals to 2005. And now when we execute this right here you get this. Now dates are already from 2005. [05:36:28] Okay. I'm going to beautify this right now so that it looks cleaner and go remember that whatever problem you have with you have to simply go ahead and break it down. uh remember that we do have keywords right we we have keyword [05:36:43] called as cross join and join and natural join too so we have got these keywords there and then there is a self join this is only a concept first let's talk about the keyword cross join here what is a cross join everyone cross join [05:36:57] has no common column concentrations it is a simple cartesian product it is a simple cartisian product those who are from the mathematics background they might remember this all right those who are not they are like me don't worry [05:37:11] about it at all let's understand what cartisian product is so for example here okay here are few numbers okay this is our column number one and this here is the column number two so here we have got few more numbers right here so let [05:37:27] me write some different numbers here okay so these are the two columns that we have here this is first column here and this is second column now what we are looking for Here we are saying that we are trying to understand cartisian [05:37:42] join here. What is a cartisian join here? That in the output in the output here I want to merge these tables together. However, since there is no concept of common column here, what it does is this one will get merged with [05:37:57] four with five and six. So if I'm going ahead I'm typing one here in the output ahead I'm typing one here in the output in front of the one here all the three words will be typed and this one will be repeated [05:38:11] cartisian product then we have got two here right so this two here will get multiplied to all these three numbers right here again here and same happens with three here okay the same happens with three here or [05:38:27] three will also go ahead and get multiplied to all the J values right multiplied to all the J values right here. [05:38:39] Now it actually doesn't make sense when you talk about numbers it's fine. Okay when we talk about only numbers it's fine that okay 11 one is getting multiplied by these four numbers here and this is called as cartesian product. [05:38:52] But how does it make sense if we have got two separate data set and we are we are merging them together without any uh consideration of the common problem. So let me give you another example here. Let's say we work in FMCG sector. I hope [05:39:08] you all understand FMCG there. All right. And we have got uh different product mix out there. So this is uh my products and this here the flavors that we will provide. Okay. I don't know what to call them flavors or something [05:39:23] say this is a shampoo. This is we want to create a product mix for shampoo based on flavors. We have got hand washes there and soaps out there. And we want to have we have three flavors we have figured out. We have got one flavor [05:39:38] here. We have got one flavor here. We have got one flavor here. And generally figured out that these are the products we are going to offer and uh we have got these three flavors and based on these [05:39:54] three flavors here we are going to create the entire product line for these. Now the cartisian product or the cross join will come in action and it will go ahead and give us the output. What will be the output here? It will go [05:40:07] ahead and get the shampoo here and this shampoo should have all the three flavors right here. And this shampoo is going to get repeated of course. Now the hand wash here. Since we have merged it with this here, I will go ahead and [05:40:21] merge it with all the three values right there. Same happen with soap seal [05:40:33] output right now in this output here everyone this output now contains the entire product mix that we were looking for because of the cross join and there is absolutely no consideration of common column it was not required right so when [05:40:49] we are talking these these kind of mergers here okay this is where the cross join comes in and this is called as cross join so let's take a quick as cross join so let's take a quick example here and uh We have all been uh [05:41:03] we have all went to cafes. Yes. And when you go to cafes everyone they have combo you go to cafes everyone they have combo offers. Yes. So let's uh take an offers. Yes. So let's uh take an instance that uh right now our cafe has [05:41:17] come to us and they have opened newly. So they want us to go ahead and create a combination menu for us. Combo menu for them. So they have beverages they have got food. So they want to go ahead and create multiple combination of food and [05:41:32] beverages and they want us to find out what will be the prices and what are the possible combinations they can offer. So we'll go ahead and create that combo menu there and plus of course we will go ahead and figure out what is their total [05:41:45] price that they should be offering there. So a path will figure out the multiple combos they can offer and of course the total price they can charge it for. Okay. So this is what we're going to see. This is an example here. [05:41:58] So first here let's understand how to type a cross joint. Okay. So let's go ahead and first understand this. What is a cross joint? Simple cartesium joint first decent product there. Basically what we are looking for we are looking [05:42:12] for multiple possible combination between the two tables. And of course we between the two tables. And of course we will not go ahead and consider any common column right there. Now there are two ways to type it. Okay there are two [05:42:25] ways to type. First way here is to use the keyword. Okay, first way here is to use the keyword and since we do not have an any other table here, we will go ahead and make sure that we're using DB joints this time. Remember that we have [05:42:39] to go ahead and set the default back as DB joints. Please do that. Now here we will uh type select again. In the select here we are getting everything from all the columns from both the tables here that is employee info. I'm calling this [05:42:55] that is employee info. I'm calling this as T1 for now and we are going to cross join it because we have the keyword available with employee salary here. I'm calling it T2. There is no common column concentration. We are we don't have to [05:43:10] type anything else here because when we go ahead and press uh and execute this here look at this. It makes no sense for now. But we can see here that here this [05:43:22] 1 2 3 is getting repeated four times uh and this is also four times getting repeated here and this is true for each and every employee ID. What is the other way to type it? What is the second way here? In the second way here we write a [05:43:37] simple query. We will not type the keyword at all. So if I don't type the keyword at all here what what we do we type here we say select everything from [05:43:50] type here we say select everything from from where from employee info comma employee salary and we end it here we end it here and once we execute this end it here and once we execute this this is what we exact same output [05:44:04] this is what we exact same output okay so we can type it without value two Right. Go ahead and find out the cross join here. Very simple thing here. And then we will create a small two tables. First we'll come back. First we create [05:44:19] these small two tables there. Okay. Then we'll move forward and create a cross join and create combo first for the cafe there. So let's uh create the database here. I'm going to call this database as cross table because that what that's [05:44:32] what we are going to do here. We're going to use the cross join right now. And inside this we are going to create table. What is the first table we are here? First is the food table. Everyone please [05:44:45] pay attention here. We are first going to create food table here. Inside the food table we are going to keep item code, item price right here. We go ahead and keep item code, item name and item price right there. Now we insert the [05:45:01] values into it. So we can say insert into where? into where? Insert into [05:45:14] cross table dot food. Now we need at least three food items here. So what would you like to eat? We very close to the break right now everyone. So let me tell me what would you like to eat? So this is I typing item code is 1 2 3 [05:45:27] here. What would you like to eat? The first thing that comes into my mind is pasta. And I'm going to go ahead and give some random pricing there. Yes. What would you like to eat? What is the next thing that [05:45:39] let me go ahead and give another food item right here. And uh here we go. One item right here. And uh here we go. One more. One more easy to type food item. Easy to type, easy to eat food item. Everyone tell me one more here. Maggie. [05:45:55] Okay, I saw Maggie first. Let's keep the Maggie right here. And let me give it a random pricing again. These are the food items, right? This is our food table right there. I will select this entire thing and execute it right here. Refresh [05:46:09] it. And here we have the cross table right here. After food here, we need beverages table everyone. Right? So we I'm going to create beverage table also here. Now cross table dot beverages. [05:46:23] Now you will keep thinking what would you like to drink? We will keep three things here. Okay? And beverages also we'll keep the three columns here. Beverage code, beverage name and beverage prices. [05:46:35] Now inside this I will go ahead and insert the values again. So which values do we want to insert here in the cross table by bridges and we want values table by bridges and we want values right here. That's values. [05:46:49] Now first one here is the code. So let's see code is v1 to here. And yes what would you like to eat here? Sorry drink here. First thing that comes into my mind is tea. What would you like? You have got two options left everyone. What [05:47:04] would you like to drink? Second, I'm keeping let's say B13. keeping let's say B13. Connect this [05:47:19] have the coffee right here. Very good. What's next? What else would you like to drink? We need one more drink, everyone. So B4. I'm naming it B14 here. Ah, very good. Yes. So, we'll go ahead [05:47:37] with live soda right here. And this is a random pricing again. And here we go. We have two tables everyone. So, I will go ahead and select this all together. Execute it only once right here. And we refresh it here. And see, we have two [05:47:53] tables. One is the beverages table right here. And second is the food table right here. Okay. I will go ahead and share this with you right now and uh during the break simply uh get these codes there and execute them [05:48:09] select them together execute them together only once and refreshing them okay both the tables are there food and web okay we'll make sure that we are web okay we'll make sure that we are typing here use cross table so that I [05:48:21] will make sure it is set as default okay now let's go let's go ahead and merge them together we simply want the cartisian product the cross table here. to select. Of course we are looking to select all the columns there and from [05:48:37] where from both the tables here. So say get from the food here we are calling it get from the food here we are calling it T1 and we are going to cross join it with beverages here and we are calling it T right here. So what we see here [05:48:52] right now is just the list here but we have to create the combination here. We need a combo here. So the combo is in front of us everyone. All right, this is the combo that we are looking at. So one item here, okay, is combined with all [05:49:06] the three beverages right here. Okay, so again see here this is here. It is combined with the beverages right here. So this is also combined with here. This is what we are looking for. So we have got multiple combinations here. However, [05:49:19] we still have their individual prices. What we want to add? We need to add a price of the combination. If the combination is spot, so first let's go ahead and now we'll add two columns here. All right, we are calling it total [05:49:35] price and the combo price. We'll add two columns in front of it. So let's go ahead and see how we are going to type that here. I'm going to take the same thing here. Select everything from here and copy this and [05:49:49] everything from here and copy this and paste it here and type I'm going to make write from in the next line here and after all the columns we want to add two more columns. So after the asterisk here we'll go ahead and type a comma and we [05:50:02] simple. So we'll ask it that go table number one that is the food table there and from the food table get the item and from the food table get the item price and add that with the table to [05:50:14] beverage prices there. We can keep it under the parenthesis here and we can name this as total price. We can name this this as total price. We can name this here as total price. [05:50:34] usually give the discounts for the combo prices. So for the combo price here we have to get the entire thing here. So let's say if we are giving the 10% discount. Let's say we are giving the 10% discount for the combo price here. [05:50:48] Meaning we are charging them only 90% of the total price. So go ahead and we will the total price. So go ahead and we will type here 0.9 right here. So this is type here 0.9 right here. So this is just 90% of the total price. We cannot [05:51:01] simply use the total price here. Why? Because select query will be executed at the end and at the end these calculations will be done uh one after another. So it doesn't understand total price as a column right now. So we'll go [05:51:13] ahead and type 0.9 here. I'm going to call this as combo price right now. This call this as combo price right now. This is combo pricing. this has got too many decimal points. Okay, let's round it off here. Currently [05:51:29] I'm going to round it off to no decimal point [05:51:43] and here we have total price and combo price right here see okay I'm going to go ahead and verify this so that we can see both the options the name here so that we can utilize these names inside the calculation. What [05:51:59] is self join everyone? Self join is not a keyword. Self join is not a keyword. Okay. And uh let's understand it clearly. Self join is very different from any other join we have seen so far. [05:52:15] from any other join we have seen so far. Self join is done on one single table. It is not done on two tables merging together. We will go ahead and we'll do together. We will go ahead and we'll do the self join in one single table based [05:52:27] the self join in one single table based on columns within the same table based on columns within the same table. Why do we use the self join here? Whenever we want to go ahead and create a hierarchy within a table itself. So self join here [05:52:41] is a special type of join. It is commonly used to create hierarchies. It makes us easier to retrieve the interconnected data within the same table. And remember that there is no specific keyword called as de join. [05:52:58] Okay, let's understand it better. To understand it better here, let me show you here the employees table. Okay, so let me go ahead and show you the employees table we have in classic models. Right, [05:53:14] Okay, in this employees table here, do we have a column which has almost common data set inside it? [05:53:37] the employees correct and this is another column that says reports to. So basically what is it here is these are employees and these are the managers or employees and these are the managers or anyone whom they are reporting it. Okay. [05:53:52] So this is our management here. This is our management here. Okay. Where these employees are reporting it. Now let's understand how this table is uh created. To understand how this table is created here, see very carefully. This is [05:54:07] employee number one2. Right? Who is this employees employee here? Let's figure it employees employee here? Let's figure it out. So this employee 22 here, who is this person is this person Dian Murphy? Yes, that's the name here. This person [05:54:22] Dian Murphy has the employee ID of 02 and this person is president. That's why and this person is president. That's why uh Diane is not reporting to anyone. Let's talk about 1056 Mary Patterson here. Mary here is reporting to 12. [05:54:41] here. Mary here is reporting to 12. Who is 02? Dying. And what is the designation of 1056? Mary here. The designation of Mary is PB sales. But she Mary here is reporting to president. Quite confusing to understand especially [05:54:57] Quite confusing to understand especially the job title column. Yes. Because if we the job title column. Yes. Because if we see 1088 William Patterson here, William is one reporting to 1056 that is uh William is reporting to Mary [05:55:11] and is 1056 sales manager. No, no, no. William is sales manager. So here everything is written in one single line. However, will it be easier to read if we will uh go ahead and reconstruct this table something like [05:55:26] this? Look at very carefully here. Will it be easier if I go ahead and type manager IDs first? In front of manager ID, we will have manager name and then ID, we will have manager name and then we will have the manager job titles. [05:55:42] Manager job titles here. After this year, after this year, now we can go ahead and get the employee IDs and employee names employee names and their uh job details. [05:56:04] this be easier to comprehend and analyze? Right? So this is what self join does. If we go ahead and use the self join on the same table, right? This [05:56:16] particular self join will help us to restructure the table so that we can see restructure the table so that we can see the hierarchy properly. Okay? If I have product ID uh product uh brand details, product details like that this is also a [05:56:29] hierarchy. So product category, product subcategory, product brand, product colors, product sizes, this is all all is hierarchy. So if you want to see a proper hierarchical table, okay, this is not how the tables are actually uh [05:56:42] collected. The data is collected. So we restructure it by using self join. So this is what we are looking for. Okay, now how do we restructure it? So to type the self join here everyone this is how we go ahead with it. Now remember that [05:56:57] uh how do we type the join here? Let me show you uh here how do we type the show you uh here how do we type the join? Let's take this right here. [05:57:09] To type join here we will use inner join. We will go ahead and use the inner join. We will go ahead and use the inner join. Now we are about to do the inner join on one single table. Right? So how do we mention uh only one table inside [05:57:24] the select statement? So we'll go ahead with start with the select everything get all the columns you can choose the columns later and how do we define the uh join or the merge there we'll start with simple from and we'll say that go [05:57:38] to employee table and we name the employee table E1 and then we will define the inner joint inner joint and after this here we will go ahead and get write the employee table again but we [05:57:52] write the employee table again but we give the alias as an E2 so now it will treat the same table as two separate tables and of course we go ahead and we define the common column. Now the common column here is going to be our two [05:58:05] separate columns. One is employee ID and another is manager ID. You'll say that we this is the same table. Treat them as two and based on these common columns you go ahead and merge them together. That's how we uh that's how we go ahead [05:58:18] That's how we uh that's how we go ahead and get the self joining group. Okay. So stay here. Okay. I'm going to show you I'm going to write this here uh uh inside the classic models. Okay. So here is the classic models everyone. [05:58:34] Make sure it is report and then I'm going ahead uh with the select currently I'm typing select every select everything and then from from where from [05:58:46] the employees table right I will call this employees table E1 and we want to go ahead and inner join it inner join it with again the employees table but this time we give the the name here as E2 when we are [05:59:01] defining the common column here we'll say that go to E1 go to E1 and first you'll ask it that which uh particular column is that which uh particular column is similar here [05:59:19] employee number okay and this is equals to E2 dot reports to this is our name here these are the two which are the common columns here now let's look it very carefully to in the output let's look at very carefully in the output [05:59:33] Here remember that this is the same table, right? This is the exact same table. Let we will look at the output right now. Let me give you a better view. Here we go. [05:59:46] go. Let's look at the joint output. Uh here see here we have got employee number, last name, first name. Observe here that this first column here contains employee number of who? [06:00:09] everyone. Right? If you will see this Murphy Mary here, these both are the per these both were the managers. Even if you see the titles right now here, I would like you to extend uh your view. I would like you to extend your view till [06:00:24] the job titles. Look at the job titles. Okay. All these people are this is for Murphy who is president. Mary is VP sales. Everyone else is a sales manager. If we go here, no one is below than sales manager other than this one. All [06:00:41] are here meaning someone is reporting to them. So the first part that we see here, it is the manager part. And when we move towards the right here, we again have the employee number, first name and last name here. But this time when you [06:00:54] look here, all these people are reporting to someone. Mary is reporting to the president here. Uh Jeff is reporting on William is reporting to Mary back here. So we have all employees staff here. We have got [06:01:09] the staff right here. So Mary is reporting to the to the president. Yes. Jeff is reporting to the president. William is reporting to the VP sales right now. And who is the VP sales? Mary. So right now in this part here [06:01:26] everyone the first first part here are the manager's part. This is the manager ID, manager name, manager first name and manager's job title right here. In the second join table here, these are the people who are the employees, reporting [06:01:41] persons here and this is their job title right here. So now we can go ahead and pick what we want easily. So I'm going to copy and paste here and we are going to pick up the columns right here. Now which [06:01:56] columns are we looking forward to? First we will need this employee number. Remember that each of these names are repeated up. So we have to go ahead and say that okay go ahead to employee one here E1 here because it is on the left [06:02:10] hand side and get the employee number right here. get the employee number right here. And this is our manager ID. This is our And this is our manager ID. This is our manager. [06:02:26] right now. You can concat them together too. So, I'm saying that go to E1 and get the first name and this is going to be manager name here. This is our manager name right here. Comma. After this here, we need job title, right? So, [06:02:43] we'll go to E1 dot here. We get the job title two from E1 there. And this is the manager titles, right? These are the manager titles, right? Let me show you the output right now. How clean the output is going to look here. [06:02:58] So, let me beautify this easy here. Okay. And let me show you how the output is going to look like. See, right now what we have here, we See, right now what we have here, we have got manager here and their names [06:03:13] and their manager titles right. Now we can go ahead and choose again. We'll go ahead and we choose again. So now once we have this we will go ahead and choose from the second part of the employees table. Now here we can move [06:03:27] forward that okay now you go to E2 there. From the E2 also get the employee number but this time these are going to be as employee ids and from E2 again we get the first names there and these first names are going to [06:03:42] be employee names right here. I'm finding it right there from E2. Once we get the job title there, this job title is going to be as employees job title there. Employee titles [06:03:58] and once we see it right there say so this is not easy. Now we can see right this is not easy. Now we can see right there that uh here okay to the president two people are reporting and to VP there to VP sales [06:04:12] there are three people reporting to Anthony who is a sales manager and maybe not anywhere these many people are these many people are reporting out. So now this is our joined out table. We can go ahead and do more calculations right but [06:04:28] this is how the self join works. Go ahead and check it out please. First join everything together. Okay. After joining everything together, then go joining everything together, then go back and uh pick out the comments. Okay. [06:04:43] So let's do one thing here. The Chrome has got unresponsive for me. So I'm going to share it right there. Okay. So this is the self join right here. I'm this is the self join right here. I'm going to share it in the parts. [06:04:57] First we will get everything. Okay. Once we understand the output from there we'll go ahead and pick up individual column right there. And why inner join has such a effect here if you will see it closely. All right. It is simply [06:05:12] going ahead and getting all the matching values in front of one another. Uh next is this exercise right here. First here we simply want to go ahead and calculate [06:05:24] we simply want to go ahead and calculate the total sales amount for each year and quarter. How we can do that? How many tables we very very simple question here that calculate the total sales amount for [06:05:39] each year and quarter. Let's figure it out from here. columns do we need here in the output? What we are talking about? How many [06:05:53] What we are talking about? How many columns are we looking for? So if we are talking about select here right which columns do we want? We want year and then we are looking for quarter and [06:06:05] here it is saying give me total sales amount. Give me total sales amount. Okay. So please tell me from where we can get year and quarter for the uh orders here that when they have been placed inside classic models only. [06:06:29] Order details do not have date. Order details do have quantity ordered and details do have quantity ordered and price. Which table has date everyone? Orders table. Remember the date functions right? So we need orders table [06:06:42] and from where are we getting the total sales or not quantity with price each that in individual order here how much quantity [06:06:56] has been ordered and what was the price paid for each. So we need all the details table everyone. So please tell me is there a common column between them so that we can figure out each year how much has [06:07:09] been paid for the orders. Do we have a common column? common column? Common column we have here. Yes. Order number. Very good. What will be the calculation for total [06:07:21] sales here? Remember that this is how the order details table looks like. Look at the order details table. Right now order number is getting repeated. Why? Because inside this order these many different product has been ordered out. [06:07:36] This is the quantity of individual product and this is the price paid for product and this is the price paid for each item. Right? Very good. So we need to go ahead and multiply quantity order with the price. Once we [06:07:49] will multiply this whatever answer we get after multiplication of these two here we need to add it up for individual order and then for individual year. So [06:08:01] first what we do? We go ahead and we join them together. Okay? Right? So we need to go ahead and multiply quantity ordered with price each. And we need to aggregate it too. Okay? Because we want total sales, not row wise out. So we [06:08:16] don't want to add a calculated column. We want to add it up. So we go ahead and type sum in front of it. Okay? So this is how we get the sum. So from and select is defined. Okay. Let's go ahead and type it out. So how do we go ahead [06:08:31] and type this out here? First here always look at the entire table if we have the excess row. So we will say select everything from where? So from uh we have got orders table first. I'm going to name orders table. Oh and which [06:08:47] join we should go ahead with. Now here we are not sure if I say inner join uh we might compromise the data set. What if uh we haven't received anything yet? Maybe that order is canceled out the status is something else. So uh whenever [06:09:03] you are in doubt you're not sure about how the data set is or how the how the data model is created at the back end we are not sure about that about the table structure we can simply go ahead and type join just join and we can ask it to [06:09:19] go ahead and join it with the order details here and we type OD we type OD details here and we type OD we type OD right there okay so after we write this common column here so we can go ahead and say on Here from the orders table we [06:09:33] know the common column that we have here is order number which is of course going to be equals to order details table order number till here everything is fine. Let me uh show you the entire table. See here this is the order [06:09:47] number. This is the order date. Everyone in front of the order date here we have individual price and quantity right here. What we want here? We don't want everything out here. We are simply [06:10:01] looking for year quarter and total sales. So now we will need to go ahead and get the year out of this year quarter out of this year and then we aggregate the each pro quantity ordered and price each as per that year and [06:10:15] quarter right there. Yes, I'm answering that there. So uh see here how many uh total rows are in the orders everyone. Total rows in the orders table itself [06:10:29] Total rows in the orders table itself we have 326. Okay, in order details we have again 326 rows. So let's see here order details has So let's see here order details has 3,000 rows here. Okay. So when we are [06:10:43] simply typing the join here, when we are simply typing the join here, how many rows do we have now? 296. Okay, what does this signify here? What type of join are we doing right now? [06:10:57] when we go ahead and define join in my SQL okay it will get only matching rows from both the tables so it will automatically define the inner join so whether we type join or inner join it is going to be exactly similar in so both [06:11:12] of them give you the exact same part okay yes inner join so right now we are simply going ahead and typing join here so one is inner join another is natural join another keyword is there natural join will figure out what is the best [06:11:27] joint to go with and there you will get the left. ahead and simply type join here. It will act as the inner joint for now. And now we go ahead and uh see here with the ear. So this is year. What do we want in [06:11:44] ear. So this is year. What do we want in the year or a date and I'm going just ear. We want quarter there. So this is We want quarter there. So this is quarter. This is it or a date. And I'm [06:11:56] quarter. This is it or a date. And I'm going to call this as quarter. Now we need total price from where do we get the total price here. We can simply go ahead and say that go to uh order details here. Get the quantity order [06:12:09] multiply that with price each right here. And we can call this as total sales. However, we have to aggregate it right [06:12:21] as per each year and quarter. So we'll go ahead and sum it up and remember when go ahead and sum it up and remember when we are aggregating something here select group by will go ahead and get the [06:12:34] individual year unique year and unique quarters right there. So in group by here we'll type 1 comma 2 that's what we are looking for and here we are we have got year we have got quarter right there and the total sales [06:12:49] right there and the total sales for for each year and quarter and if you want to add the total and subtotal there right so you want to go ahead and add total and subtotal how do we do that everyone [06:13:02] within the same thing we want to add uh grand total and subtotal how do we do that in the similar one here that at the end we should know uh for all the five years total and for each [06:13:14] year subtotal should be there too. Yes, we will get the exact same thing here we will get the exact same thing here but we will use with roll up with rollup but we will use with roll up with rollup is the entire keyword right here. [06:13:32] and subtotals right here. And no, we cannot replace null in my SQL is not in cannot replace null in my SQL is not in here. we want to do right now? We want to retrieve the customer details right now. [06:13:47] Name, ID, country and we want to calculate average order amount, average calculate average order amount, average total order amount by each country. So basically we are talking about [06:14:02] so this is what we want to retrieve. Okay the purpose is to retrieve customer details and the average order amount by country. So we want to retrieve we want to write a query to retrieve average order amount for each customer in each [06:14:16] country. Each customer in each country. So what is this average order amount we are talking about? We talking about this only quantity order and price each. This is the total sales uh there. But we want to calculate average of it this time. We [06:14:30] of this this time. That's the difference. Okay. And how does uh what should the output contain here? Output should have first your customer names [06:14:42] should have first your customer names and ids, right? So customer name here, customer name here and country. This is country right here. And this is here we should have the average. This is where we should have the average [06:14:57] now. But we want to see this report here only for the customers only include the customer details who have who are the high ticket customers for us. So we [06:15:09] include the customers who have an average order greater than $3,000. Who have the average order price greater than $3,000. [06:15:21] than $3,000. Now let's figure out Now let's figure out that which tables do we need? [06:15:34] get customer name, country and average and average contains here quantity and average contains here quantity orders and price. with from where do we get see order details [06:15:48] is very evident here because we are getting the quantity details here. Order details is evident. What is the so this is our first here we figure out order details from where do we get the customer name and country everyone from [06:16:03] customer name and country everyone from where do we get that to be customers. Here we get customer name and country from there. And the details. Go ahead and check it out please. We need a common column to [06:16:19] please. We need a common column to connect them. Do we have customer number and order details? [06:16:40] confident in your answer. You can see that in right front of your eye. Okay. See here we have customer number, their address, credit limit, country. Do we have any of that here? Absolutely not. But we are sure here that we need to get [06:16:55] these three things out. It is a a very uh genuine request. So if we do not have tables there do we have any common table. So right now we have two tables [06:17:07] one is customers right and another is order details table and in both of the case here we do not have any common column between them. So if you do not have a common column here do we have uh here any common table? customer has [06:17:23] customer ID as uh the uh column here and this has the order id. So do we have a table which can connect both of these tables together? A third table. So we have a third table [06:17:39] that is orders table everyone. Why orders table here? Because orders table have both the uh both of them here. It has got customer ID also and order number also order ID and order ID too. So what we have to do here is okay we [06:17:56] So what we have to do here is okay we will simply go ahead and table with the help of customer ID and we can order details with the order ID. [06:18:08] But when we will select the columns we will select the columns only from these two tables here. But we have to join all the three tables here. So writing this to write this query here, we have to go ahead and actually find a lot about the [06:18:23] data set just by scrolling up and down. Yes. So let's go ahead and see how to Yes. So let's go ahead and see how to type it and what we can do to avoid it. type it here? So now we understand we [06:18:36] need a third table the entire table here that is orders. So we have got multiple we go ahead and type multiple joins everyone. So to type multiple joins [06:18:48] here. Okay for remember that we can join one table at one time here. So we'll go ahead and join customer table and the orders table with the help of customer number. Then we will go ahead and join the output of this with the order [06:19:04] details table with order number as the common column because once it is joined whatever big table is there that will contain the order number within it. Okay. I'm going to go ahead and type select everyone. [06:19:22] the select here I will come back to it. First let's figure out from here. So connecting with orders customers table here. This is customers table. I'm going to call this as C and I'm going to join it together. Okay. Let it be inner join [06:19:37] it together. Okay. Let it be inner join for now. And here we want to connect it with the orders table. This is our orders table and this is O here. We want to join it on what? So we want to join it on [06:19:51] customer number. So we'll ask it to go to customers table and get customer number. which is going to be equals to orders table customer number right there. Till here everything is fine. Now what do we [06:20:05] do next? In the next here we will simply go ahead and type join again. And here we will ask you to go ahead and get order details. We'll name the order details as OD that join the order details OD based on what? based on based [06:20:21] details OD based on what? based on based on get the from the orders table get the order number which is going to be equals to order details order number and this is our prompt. This is our prompt. Now all the three tables are joined [06:20:35] together. See customer table right there. This is our uh credit limit order number. This is order state here. And third here you will find is the price each quantity order details table. Now all the three [06:20:49] tables are joined together. Okay. And uh but we don't want everything of course. So we will go ahead and say that okay I'm looking for customer name here. So from the customers table we can say give me [06:21:04] customer name and we want country right there. Now we want the average. So we will go ahead and write the round function beforehand and the average. And we know we need to go ahead and simply get quantity order and price. [06:21:19] We close the bracket here. Let's keep the two decimal points there and close the two decimal points there and close the bracket. Right. Now let's call this as average. I'm calling this as average for [06:21:31] average. I'm calling this as average for now. Giving it a small name right there. So we have right now three things around here. When we execute this here, is it going to work? No. Why? Because we aggregating it everyone. What we are [06:21:44] doing, we're aggregating it out here. So, we have to group it by. So, we going to go ahead and group by it with 1, two. We have two things, customer name and country. And here we are. [06:21:57] Okay. We want to include the customers who have an average order amount greater than 3,000 only. So how do we go ahead and place a filter here? See we have [06:22:10] average amount here and yes we have some above 3,000 some below 3,000 here. We have multiple type of amounts here. So how do we go ahead and place a filter on this column right here where clause is used for giving the filter if the column [06:22:27] is present in the database. So tell me is average column is present in the database. Is it present in any of these tables here? Any of the columns of these tables here. [06:22:39] The average. No. Average is a aggregated column with the help of pro by correct. So if we aggregating some column here with the help of group by and we're calculating it right here. How do we go ahead and [06:22:54] give a filter here everyone? With the help of having. With the help of having here. Why? because uh uh having can actually give the filter with any aggregate column which is created with the help of profile and which is [06:23:09] not present already in the database. So in having here we can say that okay the average here okay should be greater than 3,000 3,000 and now we have only 3,000 rows. You can [06:23:23] keep on going and uh give multiple other filters also there that you want to go filters also there that you want to go ahead that and uh the country should be equals to USA. So go ahead and write that country should be equals to USA. So [06:23:36] you can go ahead and keep on giving you can go ahead and use any any filter operator that we have seen so far and where clause everything can be used inside having too. Okay. When we will talk about the optimization here we will [06:23:51] learn something everyone that uh we are not supposed to give a filter which can be easily given with the help of where clause. So this country equals to USA we could have given this with clause we will not give it with having that is the [06:24:06] optimization but both are correct. What if we can go ahead and keep why are we even doing joints here? Why can't we simply keep all the columns at one place like this here? Wouldn't it be easier? No joints. Whatever you want, whatever [06:24:20] calculation you want, you just simply uh go ahead and extract column out of this. If the entire table is here, all the order details, customer details, product order details, customer details, product details, order details and date status, [06:24:33] everything is here. Wouldn't this be easier? We don't care of the joints, common columns. Then why are we not doing this? Why companies are not doing this? Data will be huge. Yes. Uh degrading the [06:24:48] performance means it will lag a lot and it is going to have too many null values and too many duplicate values. So that it will uh it we cannot simply go ahead and ask it to go and count how many customers are there. Not easy thing to [06:25:03] do here because there are too many duplicates. Correct. It will have and it will scale so large that it will be too hard to handle it. You need uh to scale up your hardware constantly and uh you have to scale your software too because [06:25:17] you need to handle such a huge data and since it is just one single table all the updates are done in this everyone will be accessing this one single table for each and every purpose. every marketing department person and HR uh [06:25:31] simple order uh per an operations team who will go ahead and process the order and everyone everyone will be only talking about this table and uh transactioning with this table only and there will be a lot of lack because they [06:25:45] do this with the help of LAN or any internet connectivity there. So this is quite a huge issue if you have one single table. What are the issues? uh what if I go ahead and define issues here first is too many duplicates [06:25:59] because one person uh there are multiple customers giving us multiple orders. So every time the entire customer detail is getting repeated. Now one order one product is bought by many customers. So the product will be uh will be there too [06:26:16] and since we are maintaining only one single table. So if I go ahead and some customer dregister themselves. So can we simply go ahead and delete that person's data from here? If someone dregisters themselves, can we simply go ahead and [06:26:31] delete their data from here? No. We have to go ahead and add one more column here and tell them whether they're is still active or not. Similarly, if we launch a new product, Similarly, if we launch a new product, of course, no one has given [06:26:45] any kind of order of that because we have just launched it on a date. And I want to update it right here because it's a new product. I will go ahead and type that product detail only just this part. Everywhere else will be none. [06:27:01] Everywhere else it will be none because no one has ordered it yet. So here we will face multiple difficulties if we are trying to get the data here. What are those difficulties? False. These are called as anomalies. Here it is very [06:27:15] difficult to insert new data. It is difficult to delete anything out of here. And it is very difficult to update any new thing. If some customer has changed their number, their contact number or [06:27:28] they've added new addresses inside it. It is very difficult to update it. It will create too many null values right there and of course there are redundancy there and of course there are redundancy or repetition issues. Other are it will [06:27:41] have large database memory, one single database and it will cause a lot of lack. And because of this reason what uh the companies do they go ahead they break up this table into the small parts here which can be handled independently [06:27:56] and when required then we can join them together and this process of decomposing or breaking up the table into the smaller table this process is called as nonline. Okay >> and with that we have come to the end of [06:28:10] this SQL full course. We hope that this course helped you understand SQL in depth from database fundamentals and basic queries to join data analysis and more advanced database concepts. If you have any doubts or questions, drop them [06:28:23] team of experts will be happy to help you. Don't forget to like this video and subscribe to SimplyLearn for more such career focused tutorials and full courses. Thank you for watching and keep learning.