SQL Full Course 2026 [Free] | SQL Tutorial for Beginners | SQL for Data Analytics | Simplilearn

Key Takeaways

Hey everyone, welcome to this course on data acquisition and manipulation using SQL by simply where we will learn how data is stored, accessed and manipulated using one of the most essential languages in data analytics. SQL which stands for structured query language. It's the backbone of almost every datadriven application. Whether you're working with analytics, business intelligence, data science or back-end system, SQL is the skill that allows you to retrieve meaningful data, transform it efficiently and answer real business questions. So in this video, first we will dive into databases and SQL foundations where we will cover the evolution of data storage core database concepts and the asset properties along with an overview of SQL commands and setup. Next, we'll explore SQL basics and crude operations including MySQL workbench, SQL syntax and best practices. Moving on, we will learn about filtering functions and query control focusing on logical operators, string functions and handling null values. Then we'll cover grouping conditions and time functions diving into advanced grouping conditions filtering and date time functions. Finally, we will wrap up with joins and advanced data manipulation where we will learn about SQL joins, table creation, and advanced quering techniques. Now, if you're looking to advance your career in data analytics and generative AI, then the professional certificate course in data analytics and generative AI from ENIC Academy IT Kpool is designed to equip you with the skills you need to succeed in today's techdriven world. With master classes delivered by IT Kpur faculty and a program certificate that boost your professional profile, this course offers hands-on learning through over 215 exercises and 12 plus real world projects. You will deep dive into data analysis, Python, SQL, generative AI and AI powered visualization tools like PowerBI and Tableau. With live interactive sessions, you will not only gain expertise in these tools, but also earn Microsoft Azure data fundamental certification along the way. So, what are you waiting for? Hurry up and enroll now and you can find the course link below. So, before we get started, here's a quick quiz to test your understanding. Which SQL clause is used to filter aggregated date? Your options are where having group buy or order by. Let me know your answers in the comment section below. Let's get started certification along the way. So what are you waiting for? Hurry up and enroll and I can find the course link below. So before we get started, here's a quick quiz to test your understanding. Which SQL clause is used to filter aggregated date? Your options are where, having, group by, or order by? Let me know your answers in the comment section below. Let's get started. Let's start with SQL. Now, uh let's get introduced to SQL or sometimes you also got a SQL there. Now, let's understand this here. So, for example, here, okay, um Fix is a global leader like Netflix, we have got Amazon and several other streaming u subscription based uh companies out there, right? And they are very uh they will give you recommendations. This isn't a new movie, but this is the recommended movie based on what you have seen so far. Right? It means uh it collects the data. It collects the data that when you are watching it I'm pretty sure most of you have done the uh okay the binge watching on Netflix or anywhere else and after a while okay this actually ask you that are you a are you still there right so how do they know that so they are actually collecting our data now just imagine millions of people out there they are looking at Netflix right now and whatever they are looking at and whatever media they are uploading there and however the user is interacting with that Netflix application or the website there they are collecting the database there. Now that collected content or that collected data all together here they are saving somewhere inside the database they are they are saving somewhere inside the database right so when they have such information here all right where they are going to save it so wherever they're going to save it okay that becomes a database now that database can be anything a database here can be anything these are databases they can be your servers. Okay, they can be your cloud or third party server or endpromise server or onpremise server. Ultimately they all are your databases. Ultimately they all are the databases. So first uh what we are going to start with today is we will first start here with database and its types right and first we'll talk about that first we will only talk about that only database and their applications. after database and the applications here I'm going to talk about the TOC after this here I will go ahead and I will talk about TOC it means that what we are going to cover in this SQL program all right after the TOC here we will go ahead and learn about SQL okay that how this language is used what are the clauses out there what are the commands out there that we should be looking out right so we'll go ahead and learn about that too Once you will learn about that then we'll go ahead and get introduced to lab practice lab that is available online right that runs on a virtual machine that runs on a virtual machine right so let's go ahead and get introduced to database here so let's go ahead and get introduced to database here but before I go ahead and start talking about database everyone let's have some few uh small things written out right what are small things I'll be talking about Okay, because not everyone is using Snowflake, right? A few percentage of the companies use that here. Now, why are we learning SQL here? Because uh SQL here is the base or foundation language to retrieve data from any database out there. Okay. So, since you are using a Snowflake, so you uh we think that that oh my god, everyone is using that here. But that is not the case. People are using Oracle also. People are connecting with AWS, those who are working with PowerBI, they're working with Azor there. And most of the companies, those who are the older companies right there, their data is still lies in Oracle and they are still utilizing that perfectly. So if we are about to learn any kind of language here or towards data engineering, just simply retrieving or connecting my PowerBI to any other system or connecting Tableau to any other system out there or it can be a cloud storage or database there. At least I should know what I'm doing. So as a data analyst or a business analyst here, we should be aware about this that okay, how am I connecting my data there? Even if you're a data scientist, you're far away from an analyst or and the data engineer there, you should know how the data is getting here. So if someone provides you a code somewhere or you are having little bugs out here, you will be able to at least understand what is going on. So that's how learning SQL language will help you. It will also help you to understand that how database is actually created. And once you understand that it will be far much easier to actually analyze that data set and understand from where this will be coming from and it it will actually help you to write the calculations uh or use the functions effectively. So that's how it works. Okay. Now let's understand here. uh now we say that we have social media and everything and the data is getting collected and we need to save data somewhere here but uh we didn't had the cloud technology before 2000s it is not available widely spread to all the businesses uh likewise there in later 2000s here we get to have our cloud technology and it was a big boom and everywhere after 2010 we had uh social media and too many and two different types of data set there but before that also data existed business existed clinical research were happening then back there too meaning everyone has the data with them. So where were the saving that when we didn't had any databases like that at the back end okay we were saving everything in the plat files we were saving everything in the plat piles so whatever data we had it was getting saved in the plat files here right now you must be thinking that why I'm taking you through here okay for the understanding about what is database here because nowadays we think about database or it's a cloud it's a database but what is actually happening in the packing let's understand that here I will be very quick don't worry about that. So here let's say we are saving the employee records here. All right. Now these are all going to be simple flat files as I'm typing it right now in the notepad. So for example if this is the uh employee data here maybe I'm going ahead and uh here we are saving the employee data. So I need my headers like ID, name, designation, employee status and a lot more inside that here. And now we will keep going here and type in the row by row details of the employees here. Row by row details of the employee here likewise and like this we have to go ahead uh I will go ahead and write all the thousand entries right here. Now of course people are leaving people are joining back again. Okay. So um this data will keep on getting edited. So if the five people are there and those who are responsible to update this data every time or have the access to this data here you'll keep on accessing this data and they keep on adding and deleting that but everything has to be very manual here and they have to be very very careful that what they are doing right here. So if this is a notepad file, simple notepad file here and we are collecting our data set right here. What do you think here? What can go wrong in this case? If it's a simple notepad file, it is a simple text file and we are manually entering the data here. What do you think can go wrong here? There is no data type here. There is absolutely no data type here. Okay. So for example, look here. It is using thousand separators. This is not using thousand separators. Okay. Here we are using upper case. Here we are using lower case. Again we are using upper case. So there is absolutely no data validation. There is absolutely no data validation. Meaning your data type has no meaning. Okay. We cannot enforce that this is the way you should be entering the data set to plus there will be issues with data integrity because we have no idea what someone is typing. Okay. There can be manual errors here. So here comes the question for data integrity. Very good. Now here if uh someone will ask us here that can you get me the list for all the managers out there it is going to be very difficult here because it is a simple text file. I cannot simply put a filter and get the data out. So here comes uh that retrieving the data becomes very painful. You have to go line by line by line and try to figure out that okay this is uh and uh copy paste manually to figure out that okay what's going on. So these are the few limitations that we were going to see there. So they have faced these limitations of course and then they came up with the database management system. Then they came up with database management system. Database management system they understood that as the technology has progressed okay they understood that flat files are a nogo. Okay that is very very difficult here. We have got tons of files saved in a computer drive there and that drive that is my server. I'm connecting by a lamp there. Those are sit there. they can only go ahead and access that file and still it can have multiple wrong entries there and of course it was very very uh difficult to update this here for example if someone leaves right now okay if let's say the pali is leaving right now so should we simply go ahead and delete this line that the pali has left the company should we simply go ahead and delete this line here why not she's not working with us anymore should we delete this since she since the pali has resigned So for example right now we have employee status here right? So we have to update the employee status and with that we have to give date of joining and date of resignation too or date of leaving we have to update that date too here. We cannot simply go ahead and delete the thing here. This is why this is required. Okay. So the now they will keep adding DOJ and date of leaving here. So let's say now this person has resigned. So they have to go ahead and update the status resigned. Now here they have to write DOJ. They have to write again one more status there and uh keep on writing the dates. If this person has resigned, this person is active, of course, they have to go ahead and type n anywhere. So, of course, there is going to be a lot of duplication again. So, that's where they came up with the database management system that okay, this is not working for us. So, let's go ahead and uh they created a database management system. What was the good thing here? Okay, they decided that there are no uh simple plat files here. Now we are going to go ahead and save everything in the table structure. Now they actually defined the table structure. They said that everything will be saved in the table structure which is going to contain rows and columns and the first row first row that is there. Okay, the the first row there it has to be a header. So they came up with the rules there. The first it should have rows and columns. First row there should be always be a header there and uh while you're reading that data set okay you should read from top to bottom plus each column header that we are giving or each column name that we are giving there each column header should have its own data type and data written inside it should be written according to that so if I'm saying it is data of joining it means inside that column only dates can be written. If I'm writing this is the employee name only the name of the employee can be written. So they came up with the the table structure here that this is the table structure that should be followed in DBMS. However in DBMS here okay they are simply going ahead and creating a standalone tables. So for example if I have department table here right. So a department table will be there. So for I have an entire database with but that entire database will have multiple tables but they will be simple tables there. So employee table is there, department is there, customer is there and vendor is there right connected to each other. Now if I'm collecting employee data I'm simply collecting the employee data as I'm doing there. If I'm collecting department wise data I'm collecting department data. If I have customer details there I have got customer details there. So they were huge just imagine your MSXs data or MS XL files that you have here. So that kind of tables you have. So for example here okay now we have employee table and department table in the rows and columns like this. If this is a row and column table here with the proper definition inside a proper MSXS or MSXL or that kind of software there it means we can define the data type data validation everything. So all the limitations of the file system there they became advantage in the database management system. The only drawback here was that if I will ask you for example this is department table right now this was uh our employee this is our employee table. This is our employee table. I'm simply going to get this employee table here. Let's just uh assume right now that both are in the table format. Both are in the table format. This is our employee table. And let's imagine here that both are in the same table format. Now if I will ask you right now that can you go ahead and get me the manager from marketing department. How we will how you will do that? See here we cannot do the join here. Why? Because tr is I have department name here. I have a department code here. I have a department code here too. But since these are two these two are not connected here. Right? What will I do here? I will find that okay this is my department code here go ahead and find the matching department code here and okay this is the marketing now once I know that this is the marketing department code here now I can go ahead and find okay which is one which is one which is one it means we can filter it out now we can filter it out so this is this is what we could have done but the only issue is okay we cannot go ahead and uh we we have standalone tables they were actually not connected to each other the way they were getting collected the way they they were getting saved. So with the DBMS here when they saw this is the problem here and plus the uh hu the when the data got bigger okay they keep kept on accumulating more data there it was becoming difficult for them to keep on saving multiple different files there and if they want to find anything there okay they cannot simply join it together like we do nowadays and move on with it why because they did not have those common columns actually created inside it if I'm selecting the vendor data there okay if I'm having the transaction slips in front of me. Only codes are written there. Okay. Or if there are no codes there, they are simply using their own names uh for the product there. So it was becoming very difficult because they there we have to manually go ahead and figure out okay find out that out of these 30 40 columns which one is the common to which table there. So they came up with relational database to figure out these limitations here. Okay. Now they came up with the relational databases that is your RDBMS now. Okay, people started converting their DBMS into the RDBMS here. So what was the objective and why the RDBMS was there? First of course everything was saved in the tables here. Okay, everything was in the table structure as before in the DBMS. However, now these table structures were getting connecting connected based on the common columns. Now from where those common columns are coming from. So now we were creating those relationship between the tables. We were creating those relationship between the tables here by con if there were no common columns here we were creating the common codes. Okay. So that we can relate the tables together. And this is the work of data engineer. This is the work of data engineer. They are the ones those who will interact with the data and make sure in your database they are extracting data from multiple sources there and then they uh then they are making your data ready to be used by the end user by simp for simply a simple extraction of data tables or reports out of the server there. So this is how the relational database came into view. Now whichever database you're working with no matter what it is it is a snowflake or something else out there right it is will have basic three entities or three components there what are the three components here three components here of uh any uh RDBMS any RDBMS here or three entities are going to be very simple first thing that you need here is of course data that you will be extracting from multiple different sources so in the MySQL here whichever database you're working with it is always based on three entities and three components. What are the three entities here? Three entities here is to create any database. Now we have the we have the definition of database, right? We have the definition of database here. Uh let's look at the proper definition of the database right now and then we will see here. So within the database first word is data and we have already discussed this word before. What is data here? data is just the facts, numbers or any kind of information whether it's an image, an audio file, a video file, anything out there, any form of information is that is a structured in a specific way and is stored for a particular purpose that is data. Plus, this data can be in several forms. It can be numbers, text, bits or bytes. It can be stored in several forms too. That is data. Remember that it's structured in a specific way. It is not saying tabular way. No, it is saying any specific way and then it is stored for a particular purpose. If I'm collecting the uh data from YouTube comments, it is for a particular purpose. If we are collecting data from my LinkedIn profile or for my GitHub profile there, that is for a particular purpose. Right? So that is the data there. Now what is database then? How do we define a database here? Database here is the structured collection of multiple data set. They are generally generally stored together. Okay. In one single entity so that we can access it, manage it and update it. So what is a database here? Database here has always three entities. What these three entities are? Look at the three entities here. A structured collection. A structured collection everyone. Right? Then it is saying that is stored on a computer. It means it has to be stored on a computer some hardware there and we need to access that hardware manage that and update that data there by using uh this hardware here. So we have always three entities. First is your data, right? Second is your hardware and the third component is the software through that you can access manage that data easily. So whenever we talk about three components here or three entities here, this is what we're talking about. We are saying that you have what whichever database you're using here, you always have three entities. First is your data. First is your data. Second is any kind of hardware, any kind of container that can contain data or any kind of hardware there to access that hardware. We will also have an application program through which we can contact and communicate with that. Right? And where are we saving this here? So we have got data, we have got a container in which we are saving our databases. And third is the application program here through which we are going ahead and communicating with that container so that we can retrieve our data as you want. So three components will be coming here right of course here what are the three components? First is of course data you are importing data maybe from uh some SAP maybe you are collecting from Google forms maybe some other forms or excel sheets there or maybe your data is actually somewhere in from an ERP system and you're trying to extract it from there or somewhere website or APIs you're trying to get your data here basically you're trying to collect data from multiple sources right here now to do all of those tasks here you need hardware you need hardware to make sure that you have somewhere to save that particular data and then you need a software application to access that. So you can call them entities or uh components here but this is what you need to create any kind of database there. Since we have got three different components here we have got three different types of users based on the aritecture of any database that is out there. Right? I'm going to take you through a very simple one here. Okay. every time no matter where you say for this uh it is a layered architecture why I'm calling it layered architecture here because it is based on data obstruction what is data obstruction here layer architecture means based on these three components here please understand here that not everyone will work on all the three components it is uh why because they are not going to give the access to each and everyone to view in out of everything. Okay, that's why it is always based on obstruction. So, uh if there are the three components here, one person will be using the one component. Okay, they will not have the access to do something else. So, they will be obstructed to view that particular data or view that particular side of the entire database. So on the basis of that obstruction here, okay, there are three types of users. The first user here is the end user. First user here is the end user. What does this end user want? This end user is only interested in accessing the data, creating the reports here. Okay? Uh use as minimal coding as possible. All this is called as external view or application over here. So this is your third component where we are in we are going ahead looking at the application program the end uh end view there and we are simply using that to create reports extracting data or accessing data that we want there that person is an end user. Second person here is your application programmer or a developer or you right now we also call them data engineers right you also call them data engineers nowadays what do they do here they go ahead they interact with the with the data here they are the ones who will decide uh the that the data is in the temporary structure it should have common columns it should it is having common columns there relationship is there and the entire database is secured data types are properly given. All right. And plus it is optimized to to access it. So they are the ones who will take care of the entire application program at the back end. Plus they are also responsible to get uh what kind of data should be stored, what kind of relationships should be there. They will go ahead and decide the schemas databases uh inside the logical structure of database that is called as schema. So they will go ahead create a data model and they get the entire structure of the multiple data tables that we're saving inside it. They are the ones responsible for this. This is why this is called as logical or conceptual. This is why this is called as logical or conceptual level. Now the third here is your best friend in your company. Third one here is your best friend in your company. if you're working on any kind of research project there. Okay. Now, third person is your database administrator. Third person is your database administrator. Now, what does this DBA do? DB is the one okay that is responsible for maintaining the operability here. Meaning this is the person who will go ahead and make sure that uh you are connected to the server properly. You have your uh you have your access properly given. Okay. You have the permission to data set whatever you want permission with and this person will be um responsible for any kind of recovery any kind of restriction has to be placed on you. Meaning you should be given access to only the data that you are working with. If you're a finance person there you will have the access only to finance data. If you're the person who are looking at the accounts, if you're the person looking in the HR, you will have only the accessory data which you're responsible for and nothing else. So this is decided by the DBA here. Usually in a proper uh setting of uh proper setting of the corporate here these three roles are properly defined separately. Okay. In some cases if you go to a startup okay they might ask you to do everything that is out there. So you're m everyone who is working out there. Okay. If you are going for a business analyst or a data analyst, what do you supposed to do there? You're supposed to access the data, get that data into some uh software and actually create the reports out of it, create the charts there, analyze what is happening in the business, know about that domain and then go ahead and give that here. Right? The two here looks very fascinating right now. But this is the pure coding here and this person should not know just one simple language. This person should know everything about the APIs. They should know how to work with any other system too. Okay. They should be wellvered with Java, JavaScript. They should be wellvered with C, C++ so that they can go ahead and use Python also if it meant to extract the data in a proper table format. So these people are the application programmer and the developer. You are not that right? They are not concerned with what is happening in the business now. They're only concerned with that data should be there. Are you that person whose work stops at okay my data is here now I'm done. No, our work starts from there. Our work work starts when we have the access to the data. After we have the data there, then we have to move forward and analyze the data and work forward with that. Okay. So, we are the end users. Do we understand that? Do we understand this part here that yes, we are the end users? No. Right. So, uh have you ever seen uh Google forms? When you go ahead and look into the Google forms here, okay, Google forms suddenly give you uh the uh some some pie charts here and there and it will give you uh the analysis as per itself, right? It gives you a little bit of ability right now to simply do some tweaks here and there and that's it. Okay. So you are as an end user you might have to work them right but it is not the end here. That's where you start your journey as a data analyst. So please understand here as a data analyst what you are supposed to do here. You are supposed to analyze it data set here. Okay. This I uh almost discuss in every SQL here. Even though it is not related to SQL here, I still need to tell you this and as a data analyst what you're supposed to do once you have access the data set, right? First thing you have to think, first thing you should know that what is your objective? Step number one is always here. Step number one is always here to know the objective for the research or the analysis. Know the objective for the research or analysis. For example, okay, you're uh you're a branch manager for a bank. All right? And you get the call there from your manager uh from the area manager that uh I want 5% increase in next quarter in the insurance sales. Go ahead and get it. Now you have to that how I'm going to get the 5% there. So you have to first know the objective. So for example they will ask you that sell 5% more insurance in the next quarter or maybe they want to figure out here that why the our assembly line is having uh too many defects in the uh end product figure out how to optimize the cost for a certain project right or maybe go ahead and optimize the logistics out there. So you have to know the objective that what you are supposed to do here. Once you that is clear here you go ahead and collect that what are the data tables here which data tables you need from where do you need that maybe you need customer data maybe you need employee data or ship data from someone you might need to ask access from DBA and they will provide you the data set with them or you may have to ask the data from some other department too it is all based on what is your objective once you have collected the entire data set here you go ahead and you create a blueprint that what do you want to analyze You'll figure out what is happening in this process, where it is happening, who is responsible, which particular process or part of the process is hampering it. Now you go and deep dive into it and figure out why is it happening. You see tendencies, trends and patterns out there. You create graphs and reports out of that here. That is your step number two. Okay. Now after end, what is your step number three here? After step number three here, whatever reports you have built, you have to go ahead and represent the solutions. So, whoever the uh decision makers are, right? All the uh business process managers those are there directors, CEOs, uh CEOs, bodies, whoever is the decision maker, you have to present your solution to them and then they will select a solution and then they will run a test run of your solution. Again, you will collect the data. Again, you will analyze it. You'll fix a solution. Then you will apply that solution to everywhere and and once you apply the solution to everywhere you again collect the data you will see that how much effective was your solution. So this is a a cyclical process. It will keep on going. Okay. So as a data analyst this is your entire work. So this is uh what we're supposed to do. So SQL where do we use it? here. So this is uh the part here. Now talking about the RDBMS here. Let's go ahead and figure out different types of databases everyone. Let's see the types of databases that we have here. So we understood the file system. Then we have understood about uh the DBMS. Uh now here we have got RDBMS. So with the RDBMS here it is in two parts here. So you see here we have got RDBMS, NoSQL database and graph database right these three databases here relational NoSQL and graph databases here it is based on what type of data we are saving. Okay, these three these three are based on a type of type of data we are saving type of data that we are trying to save and this distributed and centralized database here is that where are we saving where are we saving it basically location so let's uh understand them one by one here so relational database we are all aware about the relational database here. We are all aware that we have just seen it. Okay, there's a collection of data objects that are linked together by predefined relationships. Plus uh in the RDBMS here every data is connected to each other and it is connect creating a proper logical structure there. It means we have got we can call we here we'll be we will be calling columns attributes here and rows are called as records and each attribute is going to have we are going to have common attributes that is common columns here that will establish the relationship among the data points there. Few examples here are okay MSQL, MySQL, Postgress SQL and what so PL/SQL is also there. There are multiple examples for RDBMS here and most of the time we are using RDBMS right simple data set here, simple data set here that is RDBMS here. Now let's talk about NoSQL database. What is a NoSQL database here? No SQL database is responsible here for saving a not structured or unstructured database. What is unstructured database here? Meaning any type of geographical data with a lot of relationships or lot of hierarchy or maybe you have images that you want to save there. Okay, which in which they are in pixels or something. So whenever you have a data here which is a non-tabable database which cannot be saved inside a table only in that case no SQL is used used here. So whenever your data is huge it cannot be saved in a proper table there we go ahead and we saved that in the JSON documents and instead of relational tables here we'll have JSON documents here. What is JSON everyone? That is Java script object notation documents. Now these documents here they follow a certain notation. I will show you how does it look like. And uh there they are going to simply save the key value and graph databases inside it. And it is very uh no matter how huge your data is, the files is going to be very very small and it is simply going it is going to save your data in a very normal format. Now these JSON files they are really good when it comes to geographical data set here. What I meant by this? So for example, let's take an example here. Okay. Let's take an example here. Uh have ever seen a geographical data set? A geographic data set. Let's go ahead and create one. So let's imagine here we want to go ahead and list all the countries a geographical data set there. And in this geographical database here, right? What we need? But let's say we want all the countries there. So we need a column called as country. In front of country here what we need? What we will go ahead uh we need a states. What the states are inside it. And then here we will we will need cities that are inside it. Now if I will go ahead and write the country A. For example in front of the country A here I will type uh the state here right? I will go ahead and type the state for country is here. Let's say A1 done. Now let's say that country A has 50 states. It means that the country has to be repeated 50 times. Country name will be repeated 50 times because the I have got 50 states here. We have got 50 states here. Now just imagine here just imagine here if each state here has let's say 100 cities or let's say in a very nominal way here let's say it has got uh 30 cities or 13 cities set out uh there so that many times country will be repeated the state also will be repeated so simply to simply save a hierarchical structure here that inside this country these many states are there and this city is there we have to actually go ahead and save thousands and millions of rows there and that were a lot of redundant data a lot of redundant data out there. So because of this reason here we have got JSON documents and JSON documents are very very easy to save your data set here. So I will go ahead and open up a document here to show you out. But before I go ahead and show you a JSON document and how does it look like okay the major examples here as you can see MongoDB Amazon manage baser BSON is used. Great. But I think the objective remains same that we are the not Tableau databases. Than great thank you very much. So this is where we use it. So basically you are saving the databases in these kind of formats. Now comes the graph database here. Now what is a graph database? So for uh okay uh have you ever visit um you see you have seen LinkedIn and Facebook right? So they whenever you visit somebody's profile on the right hand side they always tell you that people have visited these people also whoever has visited this profile okay they have visited these profiles also have you ever seen that in LinkedIn Facebook or anywhere else especially I've seen that in LinkedIn a lot have you seen that before now how do they know that how do you think they are noticing that if you're going ahead and actually visiting a certain profile right if you you are the person here and you're and how you know that you're visiting a certain profile and how they do they know that someone else has also visited that certain profile and uh if they're visiting this profile here if you you are also visiting someone else is also visiting this profile here okay they should go ahead and uh visit other profiles too how do they know that do you think they can save that in a table structure do you think they can save this in a table structure format part the entire path that you're taking there or anyone is taking there whether your parts are get getting crossed at a certain point how do they know that that's where comes the data uh graph databasing basically to figure out the social networking and recommendation systems here so for example is this is you this is you okay and this is some other person one this is other person one and you have been visiting multiple LinkedIn profiles there this person and you have visited the same profile here. Same profile here afterwards. Afterwards, okay, you went ahead and you looked for some other profiles back here. You went ahead, you went in a separate direction. You were looking at some other profiles and this person is looking at other profiles here. Right? So now what will happen next? It what it is what they are going to do here? They are going to simply tell you that uh why don't you view these profiles? Why? because this person uh has also seen this profile but he went on to see these profiles and to person one here they will tell that why don't you see these profiles why because you came here and you went ahead in different direction and you were viewing the profile of these persons here so basically so they are simply tracking here okay and they are making sure that you understand what is happening there so instead of saving that in a table yes it is going to create huge data records and very complex ones too. So instead of managing a managing that here okay they will go ahead and use these graph structures or to store the data where they will they going to have notes edges attributes and parent child kind of relationships out there. So these are the graph databases here. If you're interested in that go ahead and read more about it. H it is quite interesting that how do they actually build these systems at the back end and how they are saving that kind of options with us. It is quite interesting. Okay, go ahead and read about that and this is your graph database there. So these are the three databases based on what kind of data we are saving inside it. Okay, now you have centralized database and distributed database here. So what is centralized database here? Centralized database says that that uh whatever database you have here, whatever hardware system you have where you are saving your data ultimately. Okay, it is maintained at a single location. And it is maintained at a single location such as mainframe computers. And physically also it is going to be stay at one single location there. And if anyone wants to visit uh if anyone wants to access the access that database from anywhere from anywhere of course via LAN or van connection there okay they have they will simply connect with the one centralized database here. So most of the servers that we see here usually are centralized like this. Now uh people uh centralized is used a lot. Major banks here uses the centralized system right for example whatever users they are getting fetching the data here they save the entire customer transactions in one central server. Even the airlines reservation systems the few are out there they save in one single uh they will go ahead and save that in one single server at one single location. Even whatever ERP system or SAP systems we have there right they will all for especially created for the enterprises that is also saving entire data in one single place. So most of the time okay we are using these centrally but yes these are the disadvantages right here. Now what is the distributed database here? What is the distributed database here? Distributed database is uh quite uh it is just opposite to centralized here right what what we will do here in distributed database in distributed database here okay uh it says that you're going to have databases at several different locations at several different locations here however okay it is going to have the unified collection of linked databases meaning even though databases are saved at multiple different locations there. They're still going to be linked to each other. They're still going to be linked to each other. So, it will form a distributed database. They will be logically interconnected but physically they will be distributed over several sites. Now, whoever wants to access this here. Now, whoever wants to access this here from the multiple uh locations here, okay, they can access wherever the data set is. So, maybe they are accessing the data set from here. Okay, they want to access data from here. And likewise now what are the advantages here? So uh first thing is there is no single failure point. Your data is properly distributed across uh the multiple locations. Sometimes it can be slower if you're getting data from everywhere. It can be slower there but yes it is quite uh expensive to maintain. Yes. Yes. Let's say the hardware will be distributed here. it they can be at multiple locations physically. So that location can be uh geographic locations there of course and they will be on entirely different hardware systems too but they will be interconnected there. They will be interconnected there. So these are the few basic differences between centralized and decentralized here and failure risk is very low in decentralized here. In centralized it is going to be higher. Okay. uh decentralized might be slower. Why? Because we do not have a local access to it. So data locality is in the centralized database there because locally it is available at one single access. However, when it is at the decentralized database, it is a little bit slower than the centralized database because you're getting data from multiple locations at once. Okay. So in these cases here decentralized will happen. Usually the Google cloud that we have here okay they use the distribute database and across the data centers they actually distribute the database there and there are MNC's also there okay they use the local databases for operation but they will they use local databases for example if I have a office in Bangalore their database will stay there another office at Mumbai the data will stay there so they once the uh the uh corporate wants to analyze that data set or get that data set they will simply they uh have different data centers they have to go ahead and get their database from the different data centers too but yes they will be logically interconnected so yes that happens and that is your distributed database geographically also they will be at several different places so these are the different systems everyone uh so let's try to answer this here which system is characterized by having its data structured logically interconnected and physically distributed over several sites within one computer network. Which one do you think it is? Yes, it is. It it is in the question itself. They see the distributed database system from here. Distributed database system is unique. Okay. It involves multiple linked databases which are physically dist dispersed across different location within a computer network. Right? It is going it is uh still remain interconnected and structured. It can enable efficient data management and retrieval across diverse geographical locations right now here what is SQL and uh what are we going to work on so when we are talking about yes very good thank you very much for the answers and the participation everyone thank you very much now let's go ahead and uh understand here uh we understand about databases how we are collecting everything and why we are doing that here But when we say that I want to access the data here, that's where we need SQL as a language. Now what is SQL here? SQL here is a structured query language, right? So let's divide it by everyone. Let's divide it by. So we have got a structured, right? And then we have got query lang query and the language. What is language? Medium of communication. Yes, very good. Uh it's a means of communication with whom we want to communicate. In this case, we want to communicate with the machine. We want to communicate with the machine because machine contains the data. So I want to ask to the machine there that can you give me my data. That's it. The system that we have developed the application that we have developed there. We want to talk with that particular database specifically the container that is holding all the data set. So what is uh so we have to use a language. So it's a language. Now what kind of language it is? It is a query language. What kind of language it is? It is a query language. What is meant by the term query here? What is meant by query here? That we're asking a question, right? We're asking a question there. We are querying something out. So this particular language is simply meant for asking the questions to the database. It is not a programming language. Programming language is the one where you create something. Okay? Uh from uh when something doesn't exist, you create out of nothing. So we are not creating anything there. We're not developing an application here. Absolutely not. We are simply communicating to the application. Let's see. I want you to create this. Can you give me this? Can you give me that? So basically, we are simply requesting yes data or information here. So we are simply going to request that data information here. We're simply going to ask questions. But since we want to ask questions and it's a language here, right? It has to be structured. It will have its own grammar. It will have its own rules and regulations and certain keywords and letters that it should be used using there. Okay, that is why it is called as a structured query language. So it's a language through which we ask questions to the system. But since it's a language, it will have its own rules there. Okay, and this particular system here, it allows for uh any specific data query that we want to extract. Right? Through this function here, we can go ahead and we can create, read, update or delete. What does that mean here? Meaning we can go ahead and we can create databases. We can create tables out there. And we can read or extract whatever we need. We can update whatever already exist. Maybe I want to update my data set. Maybe I want to update more columns inside it. Maybe rows inside it. Multiple different kind of updates can be done inside existing table here. Plus maybe we want to simply go ahead and delete whatever we want here. So these are the four type of operations that can be performed by using SQL. Now in SQL here we have learned about here our DBMS right. So what we will be using there we will be using MySQL there. So the lab that we have available right that lab uh is going to access the MySQL for us. Why are we using MySQL here? Because it is a very specific software product. it uh implements the functions of database server and it is the most fundamental and easiest tool to learn SQL. Okay. Whatever we are going to work here, whatever we are going to work within it, it will be you will find the commands the concept almost everywhere else. Right? So that is uh that is MySQL that we are going to use here. Now we are seeing that our uh this MySQL here is RDBMS meaning this is the relational database management system. This relational database management system here it is going it is the most popular one right now. Even though we have big data not everyone has the big data there is still using the RDBMS here. So what is RDBMS? In the RDBMS okay you will we will have here. So what will be the RDBMS here? Okay, in the RDBMS here you have multiple data tables. Okay, you will have multiple data tables here and all these data tables will be interconnected will be interconnected to each other based on common columns will be interconnected to each other based on common columns. Okay, so that's how it works. So that is RD DBMS relational database management system. Okay, so that's how it works. All right. Okay. Thank you. So if that is RDBMS everyone. Okay. Few examples are these. This is just the advanced version of uh there. And then now we have wait right now. What is the difference between DBMS and RDBMS here? DBMS here it stores data as a pile a single pile here. R DBMS we do have uh multiple data piles there. What are the differences here? It stores data which is not related to each other. These are the standalone tables. These are the uh tables which are getting which are connected there with the foreign keys or uh they will have their primary keys and foreign keys there. Meaning basically the common columns here in DBMS if you want data from multiple tables it is going to be slower and complex because the data volume is huge. In RDBMS your data tables are smaller and interconnected. It will be easier and faster than DBMS. Next here is okay uh RDBMS of course without normalization we are not going to have any kind of keys or common columns there. So RDBMS will have the normalization we'll see what normalization is and uh it has got multiple levels of security here. DBMS doesn't and it has got absolutely no security. So what are the examples of DBMS here? Okay. Any kind of file system we have in our particular drive which is a uh let's say a shared drive among the entire department there any kind of XML file or excess file there right so these are the files that people can access they can access it okay understand that yog they can actually access uh multiple people can access that file there but if they are making the changes simultaneously okay DBMS might not be able to isolate the transaction So for example there 10 new people have joined okay and two people uh uh there were two uh HR executives those who have been given the responsibility to update the data there they didn't communicated well and both of them updated the data inside so let's say the executive one executive one has updated the data at 11:00 a.m. at 11:00 a.m. and the executive two executive two has updated the data at 1300 hours. So this is how it works. This is how it works because there is absolutely nothing there it can do. So that is your DBMS and RDBMS everyone. This is the difference between them. Now we will talk about RDBMS and we'll we will only work with the MySQL here. Of course the MySQL here needs workbench. It also needs uh uh the server there. So we will always be working with two things there. One is going to be the server. What is going to be the workbench? Workbench will be the one where we will go ahead and type down what we are looking for and uh let's talk about what about the database or RDBMS right now that is very very important here and why we are working on that. So first thing is we need server and the workbench. Workbench is the interface end user interface or GUI there through which we can interact with the server at the back end. Now everything will be uh given to you on the lab. We will see what is that after the break. So what is a database here? Technically if we talk about is a it is a well organized digital storage system right that allows us to store manage and retrieve data efficiently. Now MySQL here uh we are going to use MySQL. All right. So MySQL here in MySQL the term schema and database are synonymous and they can be used interchangeably and this happens in MySQL only. Okay, otherwise the schema is the logical structure of your table. Okay, basically we call it an ER diagram or entity relationship diagram that is the logical structure at the back end and database is the physical entity or in which we will go ahead and save that schema. Okay. However, in MySQL, whether it use the term schema or database, they are going to be synonyms. Always remember that here. Second here is second here is it follows a certain quality. Okay, these are called as that any database that is a good database. How do we decide that? We decided with the asset properties here. Now, what are these asset properties here? And by uh this is atomosity, consistency, isolation and durability. Now let's understand them one by one. First here is atmosity. What is atmosity here? Okay, atmosity here is that whatever transaction we are doing, it has to be completed. Whatever transaction we are doing here, it has to be completed. Meaning if any part of the transaction fails right it should be considered the entire failure and we should roll back everything else meaning we should undo that change immediately and it should go back to the original manner only. So it is all or nothing. What is atmosity here? It is all or nothing. All or nothing simply means that that you can uh for example here let's take a very simple example to understand these terms around here. Let's say we are doing some banking transaction, right? And this happens to a lot of us whenever we are using any kind of UBI or transaction there that your transaction fails. What happens when your transaction fails? Do you get the message that your transaction has been failed? Does money leave your account or not? As soon as you get the message that your transaction has failed, did your money actually left your account? No. Why? Why did why that didn't happen? It didn't happen here because of the atmosity because whichever a a good database here will make sure that if any transaction fails right the entire transaction should be reversed. No database database here is left in the half done state. So if any part fails we simply go ahead and roll it back. Right? So that is atmosity. Now the second is consistency. What is consistency here? Consistency means that database should always be in a valid state before or after a transaction. Okay, what does that mean here? It simply means that for example, if you want to go ahead and trans uh you want to go ahead and transfer 10,000 to someone, it means that your account should have 10,000 in your account. So any amount that you want to transfer to someone all right any kind of amount you want to transfer to someone you have to make sure that amount is in your system right now in your account you cannot have zero balance and you cannot go ahead even having the zero balance you will not be allowed to simply go ahead and transfer 500 there right that is consistency consistency is it will it will maintain a transaction and it will follow a certain set of rules that see if I want to go ahead and save date. Date should be in a proper format. If date is in a proper format, then I will be able to extract the year- wise transactions out there. Yes. So that is consistency right. So it is simply maintaining a simple transaction right here. So it is solely concerned okay that when whatever happens during the transaction there whatever happens during the transaction it always follow a set of rules. So if I'm updating my date column for example, so that should be uh before also it should be a date column. After we go ahead it should still be a date column. All right. Basically if you know about the term constraints there your constraints are the rules that we place on the databases while creating them. Those constraints should match the business logic should match. Right? So that is consistency. After consistency here we have isolation. What is isolation here? Isolation means that your transactions should not clash. Your transactions should be isolated. I will give you a very simple example here. Okay. Uh you all have booked movie tickets online. You all have booked movie tickets online, airplane tickets online and you have selected your seats there. Now this happens rarely but it happens. Okay. Sometimes uh two people can book same seat. How many times do you think has happened that two people have booked the same seat on the same flight? That is isolation. It means that if there are multiple transactions going on together, right? Each transaction should be taken as an isolated incident and accordingly your database should be updated. If that is not done, if that is not done and the transactions are actually overlapping with each other here, it will be very difficult to actually get any kind of data set there. So to prevent conflicts, okay, isolation ensures that the one transaction finishes before the other affects the same date. So if multiple people are booking the seat at exactly that minute. Okay, you will be still uh there with uh they will make sure that you are actually booking one after another at the back end. They'll make sure that you're booking one after another only on what basis what rules they will apply that depends on them. But they will ensure that the transaction should not affect each other at all. Now the last one here is durability. Durability is simple. Okay, we want to guarantee there that whatever data we have here, it should not be lost. It should not be lost. So, data should always stay same. So, uh for example, uh let's say okay, they have decided that we want to go ahead and delete a data set. So, if I want to go ahead and delete an entire table from there and once I will successfully commit to that, okay, then that particular transaction should not be lost. Even if the system crashes and we get the system back, that transaction should still be safe. Okay. Now let's understand this here. Okay. Did that that ever happened to you that you have completed the entire shopping online? Okay. You have p you paid everything. You paid everything and then your system crashes and website crashes there and it shows you error. But when you go back to your email id or your SMS there your transaction has already passed through your order is already placed. So there was nothing uh nothing happened to your order. However the moment you pay it the entire system in the web page has crashed. But when you go back right just after making the payment but when you go back to your email ids they will tell you that no your order is already placed and you are good. Your payment has been received. Your order has been placed. You'll get your order in these many days. That is durability. That is durability. That web page crashed. You are not able to see your order right then and there. But you have committed the order. Right? It means that even if the system has crashed, it should it must remain saved. So key terms here are commit and write ahead login. Meaning we are permanently saving any transaction that we are doing there. So even it uh even if it crashes there data will remain safe forever. Right? So those are the asset properties of database everyone. Right? Now before we go ahead and learn about anything else okay we will go ahead and see that what are different type of commands that we have here and uh how and when we are going to use that but and I will go ahead and learn about different type of commands that we are going to work with. Now let's understand here about SQL. Now we will go ahead and look at the technical part of it. Let's see. Uh we have understood one thing about it that it performs the four operations that is C R U D right meaning we are either we are going ahead and we are creating something which means we are creating a database or a table or inserting the database uh creating that here. Yes, we can verify that but you should no commands first. to check whether that property actually works in that or not. Okay, we have to first check uh we should know the proper SQL commands first. So you know the commands and how do they work then you will be able to check that those who already know about it right for you it is easier but when I it is not easier for max for I think 60% of the class which are which is for them it is very very okay so let's see learn technicality everyone first thank you rather thank you there first let's go ahead and learn about the commands basic commands right and then when The time will come dur during the end of when we have seen all the commands, clauses, operators out there, right? Then we'll go ahead and we'll see that how do we verify the asset properties in a database. Okay. Uh here is let's go and see create uh read update and delete. Now remember that these are the actions we are talking about. What are these? These are the actions we are talking about. It is not talking about the types of commands but to carry on these sections. Okay. How about now? Am I audible right now? What about others? Am I properly audible? All right. Great. Thank you. Okay. Great. Thank you very much. So these are the actions everyone. These are the actions that we can perform. But to perform these actions here, we need commands, right? We need commands here. So here are the uh here are the four types of commands as you can see that is DDL, DML, DCL and TCL. What is DDL here? DDL here is data definition language. Data definition language here. What we are defining here? We are defining here how we are defining the table or a database. So we are here trying to create a database here or alter the table structure or drop the table or a column or we simply want to uh truncate it or rename a table or a rename a column. So this is here data definition language where we work on the table structure and the database structure here. After DDL here we have got DML. What is M here? M is manipulation. M is manipulation. When we are talking about man collation here we are going to talk about that while uh here we are inserting the data set right we are selecting the uh we are selecting data set by selecting I might want to filter it out I might want to add some calculated column in that okay or I might want to sort that table out while extracting that. So we are kind of manipulating the data set here. So we can select any data from there. We can insert the data. We can update existing rows of the data or we can go ahead and delete the rows of the data set. That is data manipulation language. Next here is DCL and TCL. What is DCL? Yes, data control language. So this is data that is language that is done. So it is data control language. And then what is TCL then? So here C is still controlled by CRC is also it stands for control here. But now it is controlling the transaction. Now it is controlling the transaction. So this is data control everyone. And this is transaction controlling. So what is data control? Data control here is we're simply controlling the permissions. What we are controlling here? We are controlling the permissions and the access. We are controlling the permissions and the access to certain database or data tables. So either we are granting the permission, we are revoking the permission. So we are creating the users. So in the DCL here we will be creating the users out there. We were granting them the permission giving access to certain databases and a lot more that who does this which user does this part then we have transactional control here. What is transactional control here? Transactional control here is that if I want to go ahead okay and insert a new data set. So uh this month let's say uh new customers have subscribed our application. So I want to go ahead and insert the new customer data of course in the existing uh data in the existing data table in the existing data table here. So while I'm inserting the new customer data here in the existing data table, I have to uh go ahead and abide by the rules and everything right I'm updating here. I we have got the entire team who is extracting and inserting the entire new customer data into our database and pushing that data there. So while I'm doing any kind of transaction, I'm altering the table structure. I'm inserting the data there. I'm deleting something out there or I'm simply updating something inside it or adding more columns inside it as per the need there. Making any changes there that is called as transaction because right now you're transacting with the database. You're giving some data there or you're taking some data set out meaning permanently making the change inside the database. So in that case here you get to either commit the change, roll back the change meaning undo the change or save up to that point and later on you you can decide whether you want to roll back or you want to close. Commit means permanently saving it. Roll back means undoing the change there. Save point means just save till here and uh don't change it permanently. Just save the changes till here. So this is transactional control. Basically we are transacting within the database with the data that we are controlling with. Who takes care of this part? Managing the data inside the database. Who does this part? Are we supposed to interact with database? These are the developers. These are the developers or the engineers or application programmers. Those who are supposed to bring data from multiple sources here and make the changes there. And in some cases DBA will also do this part. In some cases, in very few cases, right? Okay. In very few cases, admin people will be there. Okay. Though we'll be separately working as a data engineer and uh working with the database there. So what is our focus is going to be? Our focus is going to be these two. Our focus is going to be these two here. DDL and DML. Now to talk about DDL and DML here, right? So can you tell me which of these comes under the DML command? Data manipulation language. This is our DML everyone. This is our DML. Select, insert, update, delete. So what falls inside it? Okay, DML here our purpose is to manipulate and interact with the data that is already stored in the database. So what is data manipulation language here? The only purpose of that is to manipulate and interact with the data that is already stored in the database. So we there we are going to select it, insert it or delete it there. So which of the following commands are we using here? Select, insert, update, delete. What is available here? Select. So first I am going to talk about DML here. Let's talk about DML course first. Then we'll talk about the DDR. Now in DML here we say that uh it is about inserting, updating and deleting databases. Here it primary consist of retrieving, inserting and updating and deleting a data set. Right? So we will go ahead and understand about these options here. First we are going to start with of course selected statements. But before we see the select statements here, we will also have to understand about data because unless we don't have a table there or a database there, we cannot simply go ahead and create. We are going through the MySQL workbench here. And now first let's go through the interface here. And now we will be able to smoothly work within the SQL and learning process through. So right now as uh here you will have four quadrants everyone. We will all have the four quadrants here. So as you can see right now you will have query one written here you guys and you'll go one by one step by step. Step number one left and upper corner everyone we you will see two tabs are there right one is home tab one is local instance tab here we can switch between them. So if you click on the home tab you will go to the home tab. If you come back here you will see the local instance tab here. Just below this you will find your menu card system that is file edit and view. Why these are these are all menu dropdowns file edit menu here these are all menu dropdowns here so in the file dropdown edit view you will see all the options right here below this file menu dropdown here you will see quick access toolbar in this quick access toolbar here we have exactly the similar ones but these are the these are the ones that are used uh frequently okay these icons here or features or functions these are used frequently here. Now below this here, okay, this is our navigator window. This is our navigator window. Currently in this navigator window, you are able to see all these options here. Management, instance, performance, right? I would like all of you to go ahead and look here. Right now you are in administration. Right now you're at administration here. But there is one more option called as schemas. So within the navigator window here, we have got two options. One is administration, another is schemas. So go to the right side of the administration there and click on schemas please. So when we click on schemas here once we click on schemas here we are able to see here syss this is our simple database right it's a simple database here whatever system yes it's a system database here. So right now whatever databases we have here it will be written here. So this is the schemas window. Now we are here and now here you have query one. What is this query one here? Here we get to write our script. Then we go ahead and we get to write our script. And this is the help section as it is written here. Whatever we use here, the help started appearing here. Below this here, we have got output window. In this output window, whatever query we are going to execute there, okay, it will tell us what is the date and time there. What was the action we performed? Is there an error or it was executed properly? And how much duration did it took to fetch uh the answer and from here okay you can get any object if you're using or the session details here you'll have your session details here. So if you're not connected to the server it will be red in color and giving you a message your server is not connected. If you're connected properly you can see your port login user and all that details around here. Okay. Now here on the right and upper corner of course it is not a very huge space to type anything here. So on the right and upper corner you must be looking at three squares right now. You must be looking at three squares here on right and upper corner here. With the help of these three squares here you can switch on and off your help section. You can switch on and off your output section. You can switch on and off your schema section. Right? So this also depends on us. Now in the query here in this query one here do you see we again have some uh toolbars here we again have some toolbars here right so in this here first thing that we do is let's save this query as day one so here is the save button everyone control S also works so if you click on this save here this gets saved as day one and with simple control and uh what do we call this um this is scroll control bar right with simple control and is full here we can zoom in and zoom out to increase the font size here or decrease the font size here before we go ahead and start typing and practicing here there are few things about typing here that we should be aware about so let's go ahead and now here uh first thing I would like to remind you whether we use the term database here or schema whether we use the term database here or schema both are synonyms in MySQL both are synonyms in MySQL So it doesn't matter which term do we use here. Now why I'm typing this pound sign here? Okay, it means that this is a comment. What? Okay, this is a comment. What kind of comment it is? It is a used here to write a single line comments. Single line comment to write a single line comment. We can also use hashtags and the space here. We have seen that yesterday, right? This is also used for the single line comments here. What are comments everyone? Comments are the non-executable parts. It basically we will go ahead and we will write the descriptions about it. We will write the description of the codes or any message that you want to give there are the two lines here. What if I have a huge comment to write to go ahead and write this multi-line comment here. We go with the forward and aesthetic here. And we get to type the multi-line multi-line comment here. How do we end a multi-line comment right now? So multi-line comments are enclosed with asteric and the forward sign again. And when you have multi-line comments here, you see this minus and plus. Okay. So this is all our comments. Now uh we need to we will go ahead and we need to get our schema. We need to go ahead and get the schema here everyone. And in that schema here um we will do four applications here. Either we are going to create something either we are going to create something or we are going to read it or we are going to update it or delete it. So these are the four options that we are about to see. First let's go ahead and understand about create here. in the create here. First thing we should know how many data types SQL can handle if we understand that it will help us to uh it will help with a better understanding of the data uh base that we will extract right now here. So we uh we will see how many data types SQL can handle and how we should be reading that there to check out the data types in SQL. Uh how many types of data types we usually have in general? Types of data variable. No data types in a table. What are the basic data types in a table? So there are three major one. There are three major ones, right? What are the major ones here? The first one here is the first one here is character. You can also call this text. You can also call this string. Right. What is the second one here? Second one is your numbers. Numbers. What is the third one here? What is the third one here? There is third one also other than character numbers. Date. Date. Very good. Date. Boolean is also there. Boolean is there. But the major these are here. Date. So let's see these three first here. So let's talk about the characters in SQL. So now what are the characters here in SQL here? The first one here you will find is car. So what is a car here? It's a fixed length character. It's a fixed length character. What is fix pixel length character here? Fixed length character means that for example I have got any kind of ids here or any kind of u a student ID or codes or keys given to something. Right? So when you are giving any column a character here it means that it in every row in every row there okay you we are going to have exact same length of the characters there in case we use the car second one here is the where okay we are using uh here uh everyone we are going ahead and we are I'm simply giving you the description about whatever we generally use where as the name suggest it's variable character. Variable character means the length is variable. Variable length here. What is meant by variable length? It means that if we are going ahead and writing somebody somebody's name, person's name, employee name, country name, city name or product name, department name, whenever you're writing name of something or anything here, okay, in that case every record that we have inside that column, it is going to have multiple variable length. Right? Of course we can go ahead and we can define the maximum length there. So after vcar here okay we get to define the maximum length. So that what is the maximum character length that we have here. So we get to define that in character and see a car and wear car and work that is there. These are the simple ones here. Car and wear. Of course we do have more here. These are the most used ones there. Let's talk about the numbers here or you can call them numeric. You can call them numeric here. So these numeric variables here in here the first one here in numbers. First is integer. What's integer everyone? Integer here is a whole number. Whole number for example number of cars, number of employees, uh number of classes we have, number of branches, number of departments. So it is always going to be a whole number. Other than whole number here we have got float. Float is sometimes also called as double. Float is sometimes also called as double. With a float here we do have decimal. We do have decimal and yes both are different. So with float and double here what is float or double? Okay huge number with decimals. So a big number with decimal points or you can call this is that it's a fractional number. Okay. What is decimal? Decimal also contains the number with decimal number with decimal or fractions. Then what is the difference between the two? Now let's understand the difference between that here float and float and uh float or double here. Okay. Any the kind of numbers or the columns where we are using float there. Okay. We can go ahead and round that number off. we can go ahead and round that number up. We are allowed to do that in that float here. Meaning for example, I'm doing any kind of a statistical calculation. I have calculated average of something. I have multiplied two numbers and uh and then I have calculated the standard deviation or maybe the variance of that. Okay. So any or median calculation or mode calculation I'm doing. So if I am I'm doing any kind of statistical calculation there and I'm having a huge number and when I round it off there it will not make any difference there that is float. Okay. But what is decimal here? Decimal number here we cannot round it off because rounding it off will actually impact your data. It will actually impact your data. For example, if you're working in accounts, right? in accounts you're going to go ahead and calculate somebody's compensation there. So you cannot round off that compensation. If you're calculating some economic uh number there or economic uh KPI there you cannot simply round it off. You have to be uh with precision you have to write that these many decimal points are there and it means that so any kind of scientific calculation any kind of calculation related to your finance or economics you cannot round that thing off so that's where the decimal comes in so if you're talking about finance or salary there okay you don't round off your numbers there you use the precision you use that precision there we cannot compromise that so this is how it works so This is your integer float and double. Other than that, other than this here, we do have big a big integer, small integer, medium, tiny integer and a lot more. So like this here we do have more. We do have more but these are the few that are used most here. So we can go ahead and decide okay how much accuracy we are we are we require at uh any numeric number that we while creating your data. Remember that while creating that data table we decide we define the data type. Now let's come to date here this is not only date everyone this is date time because it consists of date and time both. So in this date time here first is of course date after date here we have got time. After date here we have time here. After time we have date and time together. Yes we can get date and time together. Then we have got time stamp. We have got time stamp there. After time stamp here we we also have year separately. We have year separately here. So let's talk about when we talk about date or time stamp here. What is the format in MySQL here? The date format is fixed everyone. Date format will go from by MD. Meaning first you should have year then month and then date. If we are not following up this format or your database is not following this format there, it is going to go ahead and not recognize that column as a date. So we have to go ahead and format it separately so that it can follow along with this part here. We can also go ahead and write it without uh the without the hyphens here. It will still follow the it can still go ahead and recognize your date format. Years are also recognized. can be twodigit or fourdigit format and both are recognized here. Now let's see here. Right now we don't have any databases in here because we have just started right now. So we let's see here uh the uh let's see here how we can get how we can get databases here. Right? How we can get databases here. So uh there are three ways okay three ways to get database to get database what are those three uh three ways here those three ways here first one is we can go ahead and create it by ourselves three ways here three ways here are first here we can go ahead and create it by ourselves so we can create a database and inside the database we can keep creating the tables and insert the values there right Or we can go ahead and uh import the data. We have already seen the import data from yesterday. Right? Very good. We can import the data also here. Or we can simply use the pre-existing database and we can open it here. We can use the pre-existing database here. Uh so how do we go ahead and utilize the pre-existing database here? A preexisting SQL file. create existing SQL file already written file there so we'll see we know how to create a database we created that yesterday but let's go ahead and create it again because uh now we have this in our systems right now so we can type database I'm going to give this name as I dB and here now remember that what is the semicolon here the semicolon here the semicolon here tells us about terminates a state um terminates the statement. It means this is the statement and this is only what we want to give the instructions here. So we terminate the statement here. So basically we're informing that this is where the statements ends and this is what I want to do. This is yes end of the line. So we are simply telling it that see this is where it ends and this is the first instruction I want you to follow along. So that's what we giving there. Now how do we execute the statement? To execute the statement here either you can go ahead and press control enter. Okay. Or you can use those lightning bolts that we have here. About these lightning bolts here. There are two types of lightning bolts everyone. There are two types here. Right. What are those two types here? First is the simple lightning bolt on the left side. One is the bolt with the cursor. Right? So here you have got bolt. Another you have got bolt with cursor. Bolt with cursor. Now let's see the difference between these. So first one here is this one here is it is going to execute the entire thing if there is no selection. It means this can go ahead this bold can go ahead and if you have multiple statement there it can go ahead and execute multiple statements at once. Multiple statements without selection. Multiple statements at once of whatever you have selected. whatever you have selected and the bolt here with the cursor it can uh even if you're selecting multiple statements it can go ahead and execute only single comment single command or statement so you don't have to select anything you have to simply keep the cursor there and control enter that is bold with the cursor there and it will work where is it written I'm not saying here go to query dropdown go to query dropdown down and see execute all control shift enter execute current statement control enter. Okay. So if you go to the query dropdown you can see it right here. All right. So let's go ahead and create the database everyone. I'm going to select this here. Execute this here. But I don't see database. I don't see the database here. But when as soon as we go ahead and refresh it, we get the database here. Once you see the database, everyone, you can simply go ahead and expand it and you can see an empty database. Right now, now once you have SQL saved properly in your system, please let me know. The file name is my sample database.sql. We are going to import that file here. to import that file here. Okay, we can either click on this folder here or we can go to the file tab and click on open here. So if you will go to file tab and click on open SQL script here, right? Open SQL script here. All right. Now follow along with me please everyone. Okay. Okay, whether you go to file dropdown or you click on open SQL script or you simply go ahead and click on this folder here, you will see this right now. Now from here you can go ahead and get your MySQL sample database wherever it is. So go ahead and get the MySQL sample database. You will easily find the SQL file. Please click in here and click open here. Everyone go ahead and click open there. Do you see my SQL sample database here? Multiple lines are here. So table uh script is already written here. As you see this table script here, do we see this table script here? As you can see the annotation right now. Everyone go ahead and click on the first lightning bolt. It is going to go ahead and execute the entire script. Once the uh execution is done in next 4 minutes, 4 5 seconds, click on refresh. Once you will click on refresh here you will be able to see classic models and when you expand the tables inside it you can see the list of tables here. Once you can see the list of tables here so first here is uh we are going to go ahead and start with the read query here. It means we will start with the simple select query everyone which comes inside the DQL. So from the CRUD here we'll start with read and uh select query DQL here. Right. So first thing here how do we go ahead and uh select anything here. So for example we want to view customers table here. So how do we go ahead and give the table reference that which table I want to view. Before we go ahead and understand the table reference here let's understand the how loc how the tables are located. So as you can see in the schemas right now first we have got database inside the database we have got tables. If I go ahead and expand the customers table here, you will find that inside the table here now we have got columns. And if I expand the columns here, you will can see the column names are all here listed out. So if I want to see the entire table here, we have to go ahead and give the entire location. Meaning we have to go ahead and say that go to this particular database or schema, go to the schema here or database name here and then we have to uh type a dot here and get the table name there. That's how we have to give the entire location here. Now to give the location here we use the select query. Right? How does the select query works here? In the select query here we go ahead and we write select first select. Now in uh in the select here we have two choices. Either we can go ahead and define the column names that these are the columns I need to see from a certain table. Or if you want to see all the columns there. Okay, we can go ahead and type the eststeric there. We can go ahead and type asteric sign there. Ester means I need all the columns. That's it. All the columns are are good. So we can go ahead and we can get all the columns there. If so, for all the columns here, we go ahead and we type a string. Right? So we need these things here. Now we have to go ahead and define from. So after select is written. After select is written now we will go ahead and type from here. In the from here we go ahead and we give the location here. What is the location here that inside a certain schema there is a certain table that I want you to go to and pick up these columns from there. So this is a certain syntax everyone. Right? So let's go ahead and write the syntax here. So right now we are going to fetch the whole table. We are going to go ahead and get the whole table here. To get the whole table here, this is how we get it. So, select everything from classic models. I'm selecting the database here. I'm putting down a dot here or period. And see what I'm getting the list of tables. Right now, I want to see customers here. I'm selecting customers. And then we terminate the statement here. After dominating the statement here, either we can click on this or we can press control enter. And here we see what is this here. This is result grid. What is the result grid here? This result grid here everyone it is your temporary output. And uh in this temporary output here you get to export your file from here. So we will move forward right now. Now uh uh and let me please tell you here how we are going to practice it. Right now what we have seen we have already selected the entire table. So we have the entire table. Here we have the horizontal bar. We have got the vertical bar here. If you look at the output right now, look at the output here. We have 122 rows. Meaning the customers table right now have 122 rows here. Great. Wonderful. What if I don't want to see all the columns here, right? I want to see select few columns here. So here I will go ahead. I'm going to copy paste the entire thing same as it is as it is. I'm copying pasting exactly same thing here. However, instead of asteric here, now I will go ahead and type the column name. But I don't want to type the column name here. Why? Because it can be case sensitive. I can make manual mistakes here. So instead of typing down the name of the column here, we'll simply go ahead and double click on that column name. Just double click everyone. So after select copy paste here, when the cursor is blinking here, I'm double click double click here and see I have got customer number. If I go ahead, okay, if I go ahead and type a single column, if I go ahead and type a single column here and execute this line right now, you can see I'm getting only one line here. Correct? In the similar manner here, we can go ahead and get multiple lines too. We can go ahead and get multiple columns too. How do we get multiple columns here? So since I already have the single column here, since I already have the single column here, I can go ahead type a comma here and double click again to whatever we want. Maybe I want customer name, customer number, country here. So just type a comma and get the another here. Type a comma and we have credit limit. Let's get that too. Simple, right? So like this we can go ahead and get the multiple columns here. If I now go ahead and execute this. How do we execute this? Either you select and control enter or you can simply keep your cursor inside and click on this bolt here. And see now I only have four columns here. Few things that you should know here. Okay. Command or that we are typing here or the keywords that we are typing here they are not case sensitive but the name of the columns are case sensitive. Okay. So the commands and queries that we type here, commands or keywords we type here, okay? Or the clauses that we are going to type here, those are not case sensitive. But if you are selecting anything else here, that is case sensitive. All right. So here uh let's move forward right now. Now we understand how to simply get a table there and how to go ahead and select few columns here. Now let's understand something about the select query everyone. Now in this select query here we get to go ahead and and select columns and attributes in we can call them columns or attributes here. Other than that here it is not a programming language and each statement is a separate statement that okay it is not a VBA code or it is not a VBA code that my script is start with the sub script or subprocess subrocedure. So entire thing is subrocedure. No each statement is a separate statement and while you are trying to extract something the script will start from select when you are trying to update something script will or the command will start with update. If you're creating something it starts with create right. So when we are retrieving something on that part you're absolutely correct that it will start from select always. So let's look at the writing sequence everyone and the writing sequence here. Okay, we get the select and we select with these columns here. Okay, select columns and attributes. Right now within the selected statement here you can we also get to go ahead and do the aggregate calculations. Do the aggregate calculations. Meaning by doing these aggregate calculations here we also get to add calculated columns. Calculated columns. These calculated columns can be added by simple mathematical calculations or logical calculations. After select we go ahead and we type from here. What is from will do? From will simply tell us about the location or the address where the columns are because I want to select columns in a table and from will give us the location that where the tables are. Generally where the tables are tables are always inside a table or inside a schema name. So uh instead of writing table here you have to first get give it a name of database. and go inside this database and now go ahead and get the table inside the table you'll find the columns after from here we have got where because every time I don't want each and everything out there right instead of that I might want only data from USA maybe we are looking for the persons who have credit limit of more than 75k largest spenders out there right so we can go ahead and we can give multiple filters or conditions on the rows by using the wear clause After the wear clause here we will have group by. What is a group by here? Group by here will help us to create aggregated reports. It helps us to create aggregated reports. So group by here group by here helps us to create pivot tables. Simple pivot tables. Right? And what is having here? Having here will work as a slicer on pivot tables. Work as a slicer on pivot tables. The having is uh here having. Okay, will give condition and filter only on the aggregated reports and nowhere else. So having here work as a slicer as slicer works on pivot tables only. Right? In the similar manner on aggregated reports in the similar manner having is also a filter but it works only with the group by. After this here we have got order by and limit. Order by is very simple. We are sorting our extracted table that we see in the result. And limit will simply limit the number of rows in the grid output. It will simply go ahead and limit the rows in the grid output. That's what we see here. Great. So this is the entire select uh from and where. This is the writing sequence everyone. Select from where group by and having. First we are going to go through select from and where in detail and then of course we'll look at the group by order by limit and we will also see group by and having. Now first let's go through select from and where so far whatever we are writing here whatever we are writing here every time we are uh we have to go ahead and get classic models customer right so for example here I'm going to copy paste to the exact same thing here but now we only want to see the US data we know that whenever we are extracting this here we're getting 120 rows right now how about we need uh the customer's data only from US only from US if I want to get the data only from USA here first let's check the spelling see the spelling here it is USA in caps right now correct now how do we go ahead and give a filter everyone to give a filter here after classic models here after from here we will go ahead and type where in the where we need to give a filter everyone so we can simply go ahead and say that in the where clause go to country column and check that if it is equal equals to USA. Now, USA is a string. It's a text. So, we have to go ahead and write it inside the the single quotes or double quotes. Both will work. Right? Now, as you can see here, USA written uh USA is written here in caps. But I'm I'm writing this here in the lower case here. Okay. Once I will execute this here, look at it. It doesn't matter. 36 rows are there. Yes. Wonderful. How do we know that? Okay. So see here I do understand that I do get the answer five or seven a lot. The reason is this is the result grid. Okay we are is fetching all the list here but if you look at the output everyone whatever is your last output here that will tell you that we have got 36 rows here. Right now whenever even we are when we are giving filter here every time we are writing classic models classic models customer right. So instead of doing this here, we can set this as default. By default, we will tell SQL here that see anyways I'm going to go ahead and work only on one database. So I don't want to write the database name again and again. I will go ahead and set the database as default. So inside the from here, if I'm going ahead and writing table name directly, you should be able to understand that I'm working with in that default database only. To make it a default database, you can simply write use and name of the database here. The name of database here right now is classic models. I'm going to select copy and paste here. And here we go. It is right. What are the other ways here? Other ways here whenever you want to go ahead and make anything default here, you can always right click and make uh here you can set as default schema here. This is manual process here. You can simply right click and set as default schema here. Or we can go ahead and simply doubleclick on the name of the schema and it will be set as default say default. And also observe that if I go ahead and execute use classic models right now here okay classic model turns highlights in bold. So whenever something is default okay it is going to highlight in bold here that it is working fine. Okay, if we are giving it as use classic models here, okay, how we can go ahead and write it again? So, right now I'm going to go ahead and write a simple query that select everything from. And this time, let's go ahead and get it from the products table. Right now, if I want to type any table name here, I'm not going to type it at all. I'm simply going to go ahead and double click on that table. I'll simply go ahead and double click on the table and control enter. We have the result crit. Easy. The caution here is which product has a standard price of less than 150. So if you look at the products table here, if you look at the products table here, all right, you will see right now we have MSRP here. Okay. Now we want to find out all the product here which have MSRP less than $150. Let's see how does that work. First thing is when I when you look at the product table here, how many total lines do we have? 110. Right? How many rows we have in here? Everyone 110 rows we have here. Now what are we looking for? We want to now go ahead and get the list here only for the MSRP less than $150 here. Correct? So we will go ahead and see we want to see entire columns here. So within the select here we want to see entire columns within the from here we want to get it from the products table here but now what we want to do we want to filter out here right MSRP less than 150. So we have to use the where clause everyone in the wear clause here okay we will ask it that go inside the column called as MSRP here go where the MSRP is less than 150 and we end the statement here. That's how we write it. That's how we write. Now the answer will be 98 rows. Answer will be 98 rows. So remember that the sequence everyone very very important here. Select will take ask you to select the columns from will location frame and where is the filter? Where is the filter? Read this here. Try to understand what is asking here. Which product? It means it needs all the columns. It is not specifying here. Standard price that is MSRP is less than 150. Of course, where is the product columns here in the product table and then it is asking us to filter it out. Very good. It doesn't understand currency. Raj, it has only three things. Numerical, character. It doesn't understand currency here. Now, let me show you something here. Let me show you all something here. is a button called as a button called as beautify or format. Right now as you can see here okay no matter whether I write in one single line or whether I write it in two different lines here everything is still works there is no proper way here that we are following up to write okay neither I'm asking to put a tab here tab there and take care of those things here no indentation nothing required however they do have a proper way to write it okay to figure that proper way out here I'm going to copy and paste entire thing here and I'm going to click on this paintbrush on the right hand side. So this is uh the command here. Okay, this is the paintbrush here. Beautify or reformat. The once I click on it, it is going to properly write it here. So both are exactly similar. This is also the same. This is also the same here. But the way to write is different. Now please tell me which option looks better. Tell me here which option looks better here. first one or the second one. See it from the perspective of not typing in. Okay. But from the perspective that once you come back after the 10 days, which one is easier to comprehend? Once you will come after the 10 days, which one of these is easy to comprehend? First one or the second one. As in when we reach to the more complex ones there, right? For writing, you might find the first one easier to write down here. But once you come back after 10 days and you see a complex statement there right to simply view and understand the second formatted one looks much better. So as in when we will move forward you will be able to see the difference between the both. Okay, you were able to see this difference between the both and it is of course personal uh here but when you are collaborating with someone okay they might not be able to understand this right away but this is pretty clear for a person if it is they are someone else and you're collaborating with someone okay it now let's go ahead and see few very common functions that we can use here now I'm going to talk about few uh functions out here okay to use in the select here because we can add calculated columns. We can also uh have aggregated calculations at the end what I'm talking about. Let's see. So here let uh we want to go ahead and we want to see here that how many product lines or product category we have in the product table. So if you look at the products table right now here if you look at the products table right here we have got product line. What is product line here? Product line here is the product categories that we are selling out. So now we want to go ahead and understand there uh understand that that let's figure out okay how many categories are we working with so here we go I'm going to go ahead and type here product selecting from products only what what do we want to see select product line from the products table here if I go ahead and ask it to get me the product line here is it the correct Okay. Is it the correct way right now to see how many product lines are we selling out? Does this look correct to you? This is not a good way here. We should have a unique list here. We should have a unique list here. Unique list here. Okay. We don't need any duplicates right now. We don't need any duplicates here. How do we get the duplicates right now? To get the unique list or duplicate here, we use the keyword. We use the keyword distinct. Now where do I write that keyword? Where do we write that keyword? We write that keyword with select because select will help us to type down the functions here. So I'm going to select this entire thing here. Again I'm writing from in the next line in the select here in the select here before the product line I'm going to use the keyword distinct. As you can see in the dropdown I can see distinct right now. So I'm typing distinct here. type distinct product line like this here I get the distinct line and I can see right now seven are there right I can also go ahead and write this distinct I can also go ahead write this distinct here like this it will still work if I go ahead and write distinct like this okay it is still going to work so whether you write it like this here or as a formula here in both the manner it is going to work it has it doesn't make any difference it depends on your preference how you want to type it. Now we know that there are seven counts here. Only when we look at the output, right? Only when we look at the output then not even know that. Yes, very good. Thank you. So how about that? I don't want to say look at the output here. Instead I want to count that how many are there. Count how many product line are there. So how do I count this here? To count here to count here I am going to get the exact same thing. And now here after select. Now here after select I'm going to simply type clear count you have a keyword also you have a library function also. There is absolutely no difference. So I'm going to go ahead and get the count here. I'm going to get the here. I'm going to open the parenthesis up for count distinct and I close it here after the product line. It will give me the count here. Right? But when I'm writing this like this here, look at the name of the column. Name of the column is not good. It is writing the entire formula right here. So uh we will go ahead and rename the calculated column. Rename the calculated column. Now this is a calculated column everyone. Why? Because right now this calculated column here, okay, it is not the part of your database. We're extracting something out here. We'll go ahead and rename the calculated column here. embed what we call this here as alias or the alternate name here. How do we write the alternate name here? How do we write the alias here? So there are multiple ways out here. I'm going to show you three ways here right now. First thing here is alias here is nothing but the alternate name everyone. Okay. And it will only appear in the result grid. First way here is I can go after the count here. I will go ahead and type as and after as here I can get write the name inside the single quotes. So for example here I want to type uh here total product line total product line right one way is that and when I execute this you can see total product line here as you can see right now while I'm writing this name here I am not giving any space here I'm not giving any space here but if I choose I can give a space here and the space will appear here in practice we don't give the space but uh give writing a as like this we Another way to type here is another way to type is I can uh definitely forgo the single quotes. Even if I don't give the single quotes here, we can still type the name of the column here. However, now we cannot afford to have a space here. Okay, our name has to be in the one single string. Now we cannot have the space because we are not writing inside the single quotes here. Still we get the output. Still we can get the output here. Another way to write the alias here we can simply go ahead and forgo as al together and directly write the name in continuous string and we still get the out. You can choose anything here but as a beginner here prefer this one. As a beginner here prefer this one because uh we will know that when we are giving the alternate name okay and as we move forward of course we'll go ahead and keep um you can go ahead and choose anything that you want. Okay. See the alias here give the alias see the different ways to type it. Great. Very good. Now let's go ahead and see functions everyone. Let's go ahead and see the functions here. Uh these functions here can be mathematical, they can be logical also, right? And through which what we can do? We can go ahead and add a simple calculated column. We can add a calculated column in the output. And then we to give the filters here we are going to see multiple operators with clause. We are going to see multiple operators with wear clause. Now in these operators here we have already seen the comparison operators. What are the comparison operators here? Comparison operators are simple equals to, less than, greater than, or any combination of them here. Right? Any combination of them here. Figure it out which one works for you for not equals to. Okay? We'll see what works here. So this is comparison operators here. Let's see a simple function right now. So in the products table here when we are working with the products table everyone okay what we have seen in the products table in the products table here we know that there is the MSRP right there where is the MSRP here MSRP is the price or MRP that we are placing for the customers there so let's say the company decided that that we want to go ahead and we create a new we want to create a new MSRP how are they going to create the new MSRP They want to go ahead and increase the MSRP here by 10%. They want to increase the MSRP here by 10%. And they want to create the new MSRP there. So to create the new MSRP there, okay, we will go ahead and get the 10% of the MSRP. Can I go ahead and write this new MSRP calculation like this? 10% as.1 multiplied by the original MSRP. Now since MSRP is N and MSRP is uh is common there we will take that out and we can multiply the entire 0.1 there ultimately we can write new MSRP like this here okay so this is how this is what we are going to type MSRP multiplied by 1.1 and we get the new MSRP here now how do I write this here first thing first I will write select from product here inside this here from product is going to remain same. So I will go ahead and type the from product first so that we are through with that. Now instead of selecting everything here I will go ahead and simply I will go back to the products table here and maybe from here I need product code and the product name and then we need MSRP. But this MSRP here okay I'm going to rename this as old MSRP. This is our old MSRP everyone correct this is old MSRP comma after comma here I'm going to the next line because here now we want to add a calculated column called as new MSRP to add that calculated column here I'm going to get MSRP we are going to multiply that MSRP with 1.1 and I'm going to name this as new MSRP this is our new MSRP This is our new SRP here. If I go ahead and calculate like this here, we get the output here. Let's see. But this output here contains three decimal points. This output here contains three decimal points. Right? Now, so we can go ahead and we can uh control these three decimal points here. How do we control that? By using another function here called as round. So I'm going to use the round function here. You can see that in the drop-down. So I'm writing round function opening the parenthesis here and closing the parenthesis after uh 1.1 type a comma. I want two decimal points. So I'm typing two here and closing the bracket. Same as Excel everyone. And here we go. Right. And I'm going to simply reformat it just to show you how does it look like. So if I reformat this here, if I go ahead and reformat this here everyone, this is how it is going to look like much easier to comprehend and what is happening here. In a similar manner, we can simply go ahead and use other aggregate functions here. For example, we can use minimum, maximum, count, average, round and distinct. For example, if I ask you right now to get the average price for all the product, can you get that average price? meaning average MSRP of all the product all product at once. So this is an aggregated calculation. How do we get this here? We again start with select here and we are selecting from the products table. First we will define that. Now we go here and we want to calculate average. So we go ahead and get the average function here. Inside the average function here we simply go ahead and type MSRP. This is our MSRP here. as usual. Okay, we have to give the alias here. So, we can give the alias here for average price. As you can see right now, I'm not writing any other categorical column in front of it. I'm simply writing the uh aggregated calculation here. I'm not writing anything in front of that in no category at all. When we do this here, we get the only average price here. Now, you know that okay, now we can go ahead and round it off because it is too huge a number here. after MSRP here we can ask it maybe I want looking for two decimal or three decimal points here and we can get it like this here so this is average price everyone yes sum of and average of so this is why this is how we can get the average when we don't write any category there okay we get the single one single number calculation out there we'll move forward and uh we will see about here logical operators we are going to go ahead and talk about logical operators what logical operators are I'm talking about here and or between right after logical operators here we will go ahead and uh we will talk about group we will move forward okay we will uh see more operators for example we are going to talk about more operators that we can use with bear It means filter operators. Okay, we will see about more operators right now and uh we will learn about the wild card. We will move on for group by having order by and limit. We will move on to that too. So we are going to uh understand the entire select sequence today. Of course other than logical operators we'll move on and we'll see more. Yes. First we are going to look at compar we have already seen the comparison operators. Okay. Uh equals to less than uh greater than that's there right and then we have got logical operator. We will see that today what logical operators are there. And we will also see more functions meaning we are going to see date function, text function and more different types of functions out there. And we will also discuss group by and having. But while writing this function or understanding group by and having here we will have to go ahead and discuss about a very important part that is this writing sequence and the order of execution of a select query. First thing what we do here is we go ahead and use classic models as default. We have to set that as default so that every time we are using any table name here it will always go ahead and uh find that table in classic models database only. Okay. Now let's talk about logical operators. Now I'm going to talk about logical operators here. What are logical operators here? Logical operators uh before logical operators we were looking about comparison operators. What are comparison operators everyone? Comparison operators are Comparison operators are equals to, less than, greater than or any combination of them. Okay, it can be here not equals to and this can also be not equals to. Now once you have these comparison operators when we were using these comparison operator like here or maybe yesterday here like this we were checking only one condition right this is I'm checking only one condition we are checking only one condition here meaning based on one condition only we are going ahead and and filtering out the entire table however now if I want to go ahead and want to filter out want to filter out by two or more conditions two or more conditions while I uh two or more conditions here then I need to go ahead filter out by two or more conditions here then I need logical operators what are the logical operators here the logical operators are so first let's talk about and here so what is the and logic here and logic says that it is going to return true only if both conditions are true what does that mean here and mean that either go ahead and set two conditions or more. Right? We can go ahead and set two conditions or more. Meaning I'm going to go ahead and check multiple conditions and each condition has to be true. Each condition has to match. Each condition has to match for the out for the output. Meaning that has to be all true. That has to be all true. If any one condition fails there then and doesn't work. So for example, I want to go ahead and find out all the vintage cars who has price less than $200. Meaning I need both the conditions satisfied that has the vehicle has to be vintage car and the price has to be less than $200. So whenever we are applying a condition where we want multiple conditions to be applied and each and every condition has to satisfy two in that case we apply and then what is or here or is just opposite to end here in or also I will go ahead and apply the multiple conditions. However any one condition will do. However any one condition can be true. Any one condition can be true. In which cases uh we go ahead and uh use or function here but is an or condition. So for example okay most of us go to office or any organization we attend to any university or something. So when you are going ahead and logging your attendance okay either it can be with biometric or you should have a card if you don't even have that you forot okay even if that is not there you will go ahead and sign in and register. So if you go to your organization there let's say you forgot your card so they will say it's okay use your biometric and if you don't have biometric they'll say it's okay sign in the register and go meaning you have multiple options by that you can choose any option and you will be let in in a similar case what happens in order here in or here I will say that okay I need a person okay who lives either in USA or in France or I need a manager that can work in marketing department or in sales department. So right now or will if any one condition is satisfied I'm good with that. I want that row in the output. That's all. What is between here? Between is pretty simple. Okay, between is always used with and and uh it will simply give us the range of values and it is always in numerical values of course. So it will simply go ahead and give us the range of values and usually the range of numerical values. For example, okay, I want the credit limit uh of I want all the customers whose credit limit is more than $75,000 or between uh $75,000 and $100,000. So like that I might need a range of numerical values to be filtered out. It means we have got more than one condition. So why uh logical operators? Because we need to go ahead and check more than one condition here. So let's see how does this help. So here we will go ahead and check two conditions here. So for example, I want to go ahead and check that go ahead and find all the product whose MSRP is greater than 100 but less than 180 whose MSRP is greater than 100 but less than 180. Now they belong to and because there are two condition here where MSRP has to be greater than 100 also and MSRP has to be less than 182. So first thing here is we go ahead and we select product. I will go ahead and type you use classic models again just in case one of uh just in case okay we'll forget that. So what we want to do here we want to select what we want to select everything from where from the product. This is also done. Now we have to use and here. How do we use and? So we will use the where clause here. In where clause I'm going to say that go ahead and pick uh go ahead and find out where MSRP. So first we'll get the MSRP columns here. Here it is. where MSRP is greater than 100 greater than 100 and and again MSRP is less than 180 here less than 180 here. If I go ahead and uh search for that here. Okay, let's see. Look at the MSRP column. And we do have everything greater than 100 but less than 180. Greater than greater than 100 but less than 180. How many rows I have here? 47 rows. Right? So here this is good. This is uh what we have used here. We have used and here this and here. Now you must be thinking that why have you not written greater than equals to or less than equals to here. All right because we have a choice. We have a choice here. Okay, I have a choice here. If I want greater than uh less than here. If I want to type equals to, I can. If I want to include both of them here, I can. All right. So with the and here I have the choice right go ahead and get how does and works and then we are going to talk about between yes I understand that it does more look like between we can solve it with that too and yes we can but first go ahead and see and come back then we'll talk about the between. We'll also talk about the difference between writing with and and between. Now here let's talk about between. So this is and here now after and here it looks more like between portion because it is a numerical range right here. So here I'm going to use between. So how does between works here? So we will use between and end together. I have told you before. So how are we going to write between and end together here? To write between and end together I'm going to go ahead and get this part exactly as it is. And here where MSRP is between what would do we write here that where MSRP is between is between 100 100 and 180 100 and 180. Very simple. Now when I go ahead and look for this here remember that between here by default includes both 100 and 180. So we don't have to go ahead we don't have to go ahead and uh include uh we don't have to worry about the equals to sign here as we have to understand in and right now. So in between here by default it is going to include both the intervals right now that is 100 and 180. So it is specifically looking for greater than equal between 100 and 180 including both the numbers right there. The uh output is going to be exactly similar. This is between everyone. Now I have a question here. Now here if I ask you right now that can you get me the list of the customer those who are either living in USA or in France. USA and France. So I want a list where people are either living in USA or in France. Meaning I want a list where people are living in USA in France both. Oh. So how why do I say that we are going to use over here and not end because basically what I want the be I want a list where customers are living in USA and France either in USA or in France. So we have to go to the customers here. If I'm checking here every time that which customer lives where so we I have to how we will check manually also we will go ahead and check is this customer lives in USA or France. France if they live in France here we'll be like right okay filter that now we will go ahead and see USA this matches the condition we will filter it we'll check again is this person lives in France or USA no then we will go ahead and cross it off so in this way here in this way here we will go ahead this is why we use or because no person is giving us the uh two addresses in one single row. So here we go ahead. This is list of customers living in US or France. Right? So we will go ahead and use or. How do we use or here? So select everything from is going to be same here. I'm copy pasting right now. From where? From customers. From customers then we have got where where in which particular column we are about to give the purchase country column here. So where country is equals to USA here or or country is equals to France. Remember that I have to use the country twice here because this is how we go ahead and uh see in and also okay so if you're having two separate here conditions you have to go ahead and rewrite the country column here. Okay. If I go ahead and use this bear right now and let's check the country here. Yes, we do have only US and France. That's how we go ahead and filter it out. That's how we can use or here. So for now, okay, let me show you right now. Here uh if I will go ahead and execute this here, I'm getting how many rows written? 48. And what do I have in the output? US and France. Right? that why we are writing country equals to again even if I don't write country equals to here okay then also I'm getting an output you are definitely getting an output how many rows are you getting here 36 check your country column do you have France here no why because it has no idea we have to find France it has stopped here and given you the answer right that is why we have to go ahead and include country equals to here because or or only understands that that after this something else will comes. That's it. All right. Now, if I will ask you here, if I will ask you here that okay, now go ahead and get uh go ahead and give me the list of customers living in either USA, Australia or France. It means that I'm going to ask you here to add one more country. How you will go ahead and uh how will you go ahead and add one more country? You have to write or again. You have to write or again. And then you have to write Australia again. Great. So here we go ahead. If I ask you here that go ahead and get the country equals to Australia here. It means that you have to go back again. You have to write the or again. You have to go ahead and write the or again type country equals to once more. Type country equals to once more and get the Australia again from here. You have to go ahead and get the Australia from here. Right? So here I have to go ahead and get the Australia again. It means that if I need the list of 10 countries, maybe I list the list of 10 product out there or product ids every time I have to go ahead and type equals to column name equals to column name or equals to column name or I don't want to write it like this multiple times. So what we do here is we go ahead and we use the operator there. We do have 53 rows. Now here we I'm saying what is the objective right now here is this objective here is that I don't want to write country equals to here because as you can see right now we are making mistakes minor mistakes here we are forgetting to write country equals to maybe forget to give the proper quotes around here or whatever. So instead of using whenever we have to go ahead and find multiple values inside one single column here in that case we can use another operator called as n. We going to use another operator here called as n. This n operator here is going to help us write a list. n operator helps us to filter out a list. And this n operator is going to whatever list I will how does it work? I will go ahead and type n inside this. I will give a list. For example, value one, value two, value three here. And this n is going to act like exactly like or so v_sub_1 or v_sub_2 or v_sub_3 it is going to act like that right. So this is why we this is how we will go ahead and use the n operator inside here we go ahead and filter out a list. So it helps us to define a entire list right in follow along. So first uh till here everything is fine. Select everything from the customers here. But instead of everything here, I'm going to go ahead this time and I'm going to select only customer number, customer name and country. Okay. So that we are able to see clearly the output. Now I'm going to type a country in where country in after in here we will give a list here. What is the list here? List here is going to be first is USA. So I'm copying and pasting USA right here. Comma. I'm copying and pasting France here. Then I'm copying and pasting here Australia. After that also if I need to go ahead and add anything in in the list here. Now it's quite easy. I don't have to write country equals to multiple times or remember to type or here. When I go ahead and execute this, I still get 53 rows returned and I have here three countries here. So this is the in operator here. In operator is used in place of or. n operator used in place of or whenever we have a list to print out. Let's go ahead and now we know how to use the n and or between and n operator. Remember that how are we going to proceed here? I'm going to show you one example for each operator and at the end we'll solve a small problem that is going to include everything that we have learned in one single problem. That's how we are going to proceed. Okay. So right now please go ahead and uh let's see the next one here. So till now whatever we have been filtering out everyone till now whatever we have been filtering out it has been always uh either one condition or multiple conditions and every time we are looking for the entire string. What if I will go ahead and ask you that can you get all the product line can you get the all the product line where any product line or any product category where in which car is written any product in which in which name car is written meaning this time we are not going ahead and asking you to find a proper vintage car or some car here no we are simply asking you here that go ahead and filter out wherever the car is written We don't care. Okay. So in in this product line inside the products table, how do we get that? This is where we use wild card and we can define the wild card with the help of like operator here. Like operator here. Now what are the wild cards available to us? The wild cards available to us here are percentage and the underscore sign. Percentage sign is widely used. We will see here why percentage defines percentage defines zero or more characters. Percentage defines zero or more characters. What does that what is meant by that here? So basically I can go ahead and ask here what does percentage defines here zero or more characters. What does that mean? If I ask it that go ahead and find percentage car. So basically I'm saying here that before car before car there can be any number of characters there can be any number of characters any number of characters can be there. If I will go ahead and type car percentage here it means that and it ends at car and it ends at car. If I will go ahead and type car percentage here, it means we are communicating here that it is going to start with car and uh at the end there can be any number of variables there. If I go ahead and type percentage car percentage basically we are saying here the car is somewhere in between car somewhere written in between the string and we have there can be any number of variables before car and after car and all of this is checked all of this is checked within a string within a string. So here inside a string here it is reaching word letter by letter there right. So this is the percentage here. Now what is the underscore sign? Now this underscore sign here this underscore sign here it is fixed. What is meant by fixed here? If I will go ahead and write like this. Okay 1 2 and three. If I go ahead and write like this here. So I'm defining right now that in total in total it is a threelet word. In total it is a threeletter word that ends at a that ends at letter A. Go and find that out. Maybe I will go ahead and write like this. It means that that this is a fourletter word in which O comes at the third place here. Go ahead and find this pattern out. Yes. So this is how if you are looking for a fixed pattern you will use the underscore sign here. If you're looking for a variable pattern you will go with the percentage here. Most of the time percentage works and that is used a lot. Now let's see an example everyone. So here what is underscored here? It is a fixed characters. Fixed characters here. Okay. Basically looking out for any pattern. Looking out for a pattern. What is the example here? Example is exactly this. Okay, we want any product with car in it. So we are looking for get all product lines with car written there. With car written there. Now uh since we are not that familiar with the data set, okay, I have to go ahead and check each and every line there and each and every number there. Instead of doing that, I will go ahead and say that okay, let's go ahead and get everything from the cars. Let's go ahead and get everything from the products there. So, here is the products table everyone. Great. So select everything from the product where now this is where and now we will go ahead and type like where like like what where product line is like where product line is like car now where the car should be there so I have no idea where the car is going to be or whether it is car or cars I don't know that so I'm going to simply go ahead with percentage car percentage that there should be car somewhere in there it it is at middle it is after that I don't care. And it is will go ahead and get the cars. Okay. Go ahead and get that please. Now in the similar manner here, in a similar manner here, please go ahead and get all product name. Go ahead and get all the product name with the word Ford in it. With the word Ford in it. I don't know where it is. So here everything is going to go and everything is going to be exactly similar to this here. And after this we will need product name. And after product name we'll go ahead and write like and it will be code and we'll have 15 rows there. Very good. Let's uh look at the wild card here. Let's look at the wild card here. So how do I use a wild card? If I want to go ahead How do we know that this time we use like? Because I'm asking you get all the product name with word for in it. I'm asking you to find not an entire string but I I'm trying I'm asking you to find one word inside each name. So this is how you will know that where to use like because I'm not asking you to go ahead and find a vintage cars. No, I'm asking you to find anywhere if car is written. Okay. So now to understand about the wild card here, to understand about the wild card here, find the customers whose first name starts with letter M and have exactly three characters and have exactly three characters. How will you define this? We have to go for with the customers there. How do you think we will write it here? So if I will write select everything from customers where where customer name customer first name here is like like what I want M is the first letter. It should have exactly three characters. So maybe one and two. Do we have anyone with name like this? Yes. Great. This uh says that anywhere in the name M if is there. No, I'm I'm saying it's starting word letter should be M. So if uh it's starting one word. Yes. Oh, there is one person here. There is one person here. Only one. So this is the uh wild card here everyone. Right. This is the wild card here. Now it can be used in multiple ways. If you are going to look for a pattern there, right? That's where we can use the underscore signs here. And these these are two underscore signs everyone. Why? Because you're looking for exactly three characters here. All right. So this is all about the filters and the operators right here. Okay. It's very very good. Very good. Right. So we know how uh we have to go ahead and find select. Okay. And in this select here what we need? We need to count here. And from which table are we working with? Are we are working with customers table here. So I can say count customer number from where? From customers table. Now we have to type where. Now we know that how to get the list here. So in the where we have to go ahead and get the list that is country in this particular list. Very good. It means we understand right now that uh where and these operators can be used with each other too. Very good. The count of the customer number here will be 53. Now we go ahead and uh discuss about the functions everyone. Yes, more functions. Now what is the next here? Right now we have seen multiple operators. how to give the uh how to go ahead and give multiple conditions here for filtering out. Now let's talk about the functions here. What type of functions can we see here right now? So here we will we are going to go ahead and talk about first a string or text functions then date functions. We'll also go ahead and learn about conditional statements and we will also discuss about the null values. How to find out the null values? How to deal with the null values too and we can do that by using the functions only. So we will go ahead and discuss about these functions right now. First thing that I'm going to discuss here is the text functions. That's why I want need your attention here. Right now what we are going to discuss about the fun in the text functions here. So let's see how many different types of functions can be here. So these functions are auto are going to be exactly similar to what you have seen in Excel or on any other platform. They're mostly similar on Tableau, SQL, Excel and other similar platforms. For example, there's a function called as upper. What does uh what does up what does upper do here? Upper will make sure that your smaller case letter gets converted into an uppercase letter. Similarly here, okay, we have lower function here. Lower function make sure that your uppercase letter changed into a lowerase letter. Then you have got here another function called as length. What is what does length do here? length will tell you about the length of a string or how far how long your address is or what is the length of your uh data point inside your rows inside one single cell there then we have got trim function there you also have left trim and right trim what does trim function will do here trim function will get rid of your the extra spaces where extra spaces well it can be a sentence it can be a name it simply gets rid of extra spaces and understands that there should be one space between each word that it understands. So there should be only one space between each word. If there are extra spaces, trim is going to trim them down and keep whatever is required. Similarly, here we have more examples here. For example, you have got here substring. You have got the reverse out there and a lot more. There is one very popular function in here. Concatenate. Have you heard about that? concatenate everyone. Okay. It joins the two or more strings together. So here it is uh join or two or more strings here. Yes, it is because it is the most commonly used text function here and one of the first text functions people usually learn in their uh on their work. Right? So concat here simply joins two or more strings here. Yes. All right. No worries. Those who didn't know about that we are going to learn something new then. Right. you'll see that white is used most of the times here. So let's go ahead and look at that. So for example here okay we want to go ahead uh look at the customers table everyone in customers table here we have a customer name if you remember the customers table okay do you remember that here our customers are the vendors right look at the vendor's name here land of toys mini wheels euro shopping channel model and uh this is the name of the companies or the vendors out there distributors basically so our spark details are here our spark details are But respond details are written in two separate columns first name and last name. So what so what we want to see here right now we want to go ahead and look into we want to go ahead and look into both the names in one single column. Both the names in one single column. So let's go ahead and get the full name of the contact name here of customer in one single column here. How do we get that? So first thing is we select everything from the customer. So I'm going to go ahead and not not select everything from the customers here. I'm only going to select customer number here and customer name. Now after customer name here I would like to see entire full name of our customer. So here we will use the function right now. So I will first let go of from right here from customers right here. Now here I will go ahead and type the function. What is the function here? Function is concat. So I can write concat here. And you can see that it's a function right here. I will select concat. And inside concat here, what is the syntax of concat? Just these strings. A string 1 2 3 4 5. So the concat here, first I will go ahead and select the first name. Right? Now after first name, how do I write a full name? I will go ahead and type a last name here. So here I need simply two strings tied together. So I'm going to select this and execute this right here. Look at the output. Everyone on the screen please. Look at the output, please. And please tell me, does this output look correct to you? Does this output look correct to you? I've got a space here at Karen. I've got a space for Janine and Jonas. So, I've got a space at Diego and Christina. I got a space here uh in the name here, Mary. So, in few names, I have the space, but in few names, I don't have the space. Any idea why few names have the space and few names doesn't? because here it already had the space here. So when someone was writing the first name and last name right here when they were entering the data here they might have added the extra spaces maybe after the last name or maybe before after the contact uh first name or uh before the contact last name. So they might have some extra spaces somewhere maybe at the start or the end. So what I have to do here first I have to go ahead and trim these spaces here. I have to go ahead and ask you to trim these spaces. Now the trim spaces here trim will work on individual column only. So I have to write the trim function here for individual column. It will not work outside the concat like itself. So I have to go ahead and write the trim function for individual name here. So when I'm writing trim function for first name and last name both here. And now when I execute this here now I'm getting absolutely no I'm getting an absolutely no space. When I'm getting absolutely no space here then we will go ahead and add few spaces right here. So here single code space single quotes comma again and now here we have a proper output right of course the name has to be given here. So I'm going to give the name here as contact me right here. Of course it is not going to make any changes in the existing database. Existing database will have the separate names. it only affects what we are trying to export or export or retrieve from here. Okay. So this is the full name here. I'm going to beautify this uh for you so that you can see how it is going to look proper here because it uh now the functions here are going to look a little bit longer. So here I'm going to beautify this right now or reformat that. I will show you how the reformatted version looks like. This is how easy to look at. We are moving forward right now. So we can see here uh contact name here right now and we have seen how to use trim and concat together. Now once we have seen the trim and concat together uh go ahead and get full contact name with the full address within the same query. Right? So the query that we have written here within this query itself please go ahead and get the customer name and the P contact address too. Go ahead and get that please. So basically you have to what you have to do is you have to copy and paste right copy and paste. After you have copy pasted here after copy paste here type a comma type a comma press enter press enter and type concat again. But inside this concat here, I would like you to go ahead and get the address. Get the full address. Get the full address. What does full address is going to consist of? Full address is going to consist of address line 1 2 3 till the country. Right? Please go ahead and type contact. I know concat here. I know you can do that. We do get the answers here. But there are a lot of null values right now. What this does null values tells us that kang we don't they don't have given us the address. Only the Australian collectors they have given us the address here. So let's go ahead and check. Do these people don't have the address? If I go to the customers table here and if I try to check that here I see that no everywhere I have the addresses. However, however, if I talk about this right now, uh yes, I do have null values in between. So, for example, address line two is not there. In some cases, state name is not there. So, I have null values in a few of the cases here. Few cases, I don't have any null values. This is quite this is quite a standard, right? So, whenever you guys go ahead and in um to any website portal, maybe you're shopping something, right? So on the website if you're shopping you have to enter your address in that they might give you address line 1 2 3 city name estate name neighboring area right so not every line is mandatory only address line one is mandatory and your city is mandatory other than that they will and pin code uh is mandatory or postal code other than that nothing is mandatory because not everybody's address is going to have address 9 1 2 3 4 5 not everyone is going to have that in that case the address line two you can see it has multiple null values right here and because of this null values our concat is breaking concat is not able to deal with null values so even if only one column has a null value in this list even if one column has a null value here concat breaks and gives null as the output concat breaks and gives null null as the output so here what we understand about concat we understand about concat here that concat cannot work with the null values. It cannot deal with that. In the uh in the ideal scenario, in the ideal scenario, the function should go ahead and look at the null value, escape that and move forward and go to another column and paste whatever value is there. Right? So what we do here, we will go ahead and find out how many null values are there. And how do we find the null values? We simply ask SQL is there null? Is there a null value? So for example here I will ask it that go uh that go ahead and select go ahead and select and count all the values count all the values from all the all the columns there from the customer's table where where address line one is null where address line one is null or address line two is null or city is null or state is null or postal code is null and likewise it means you can keep on checking like this here if I go ahead and check it like this it's tell me here that oh there are 121 counts here it means that there are too many null values already there now how do we deal with this null values here to deal with this null values here we have a function here called as coal. What coales do here? We'll go ahead and use the function called as coal. What coal will do here? Coalies will simply ignore the null values. So what coies can do? Co is very good at ignoring the null values or replacing the null value with any kind of symbol function or whatever we need. So it can replace the null value with any text, number or symbol. Okay. So it can uh symbol basically we are talking about separators right. So it can do anything here with the null value. It means co can deal with the null values. It understands that it has to read the null value. It has to identify if there is a null value and then move forward with it. How does the syntax of square looks like? So this is how the syntax looks like everyone. So in the syntax here inside the co ask here for a column name or a string. It will ask for a column name or a string and it will identify if this null or not. If this is not null, it is going to go ahead and give uh this exact value as the output. If this particular row inside this column if any row is null if the value is null. Now here you get to replace the value with any value there. You can also leave it blank, right? Or you can go ahead and give any kind of number separator na. You can write anything out here. You can go ahead and write anything out here. So you can go ahead and uh replace the null value here with any symbol text or anything else or maybe leave it blank. So that's how coies help us to concat it better. So we will use the concat and coilies together. So I'm going to show you how the concat and coilies can be written together. One more thing coies has to be used for individual column here. It cannot be used for all the columns at once. So uh what I'm going to do here is I'm going to copy I'm going to copy the entire same thing that I've seen before and here we will use concat and co together. So here I will type after full address one I'm going to type a comma again and now I will go ahead and type concat again. So this is concat here again inside the concat what is the first thing we need address line one but before I need address line one here I will go ahead and type queries and inside the co here I will get the address line one now I'm going to ask co that go and check address line one column and it is a row wise calculation it will go and check that row if you find that row is null if you find that row is null here okay go ahead and replace it with nothing meaning just keep it blank blank. How do I define blank here? Single quote, single quote. No space, nothing. Single quote, single quote. Continuously replace it with nothing. I can also go ahead and replace it with a comma. That is also fine. Now I'm going to uh type uh replace it with a comma here. Maybe if there is a null or just leave it blank. Just leave it blank. So you can write anything also. You can write na also. For now, I'm not doing that. So I have opened the coies here and I'm opening I'm closing the coies also here. Now I'm typing a comma here. After comma I'm going to give a simple separator like here that if this is address line one after that I'm going to get address line two. Between these two here I want a separator and that is comma. Now I'm going to type comma here again for the third character I'm going to type coil again. Inside this I'm getting sign and we are aware that end line two has nulls. So I'm going to go ahead and type here NA so that we can identify that that okay there was a null value because we are doing it for the first time here. So we should be able to identify that. Now in the similar manner I will go ahead and write it for the city and state two. In the similar manner here I will go ahead and write for the city and state two. So I'm typing comma again and we are getting boil here. This is here for city. If city is none, I would like it to simply leave it with a hyphen so that I can identify what's going on. And now here I will go ahead and type co again. This time I'm writing it for state. If the state is not there, just leave it blank because it is at the end here. And uh remember here always close the bracket for co here and at the end close the bracket for concat. I'm going to name this as full address two. And look at the output. Wherever the address line two is not there, I'm getting the na na na here. Right? I'm getting the na here. And after uh the uh address line one, okay, this na is properly written here. After this na here if I want it to be written properly I can give a space and comma and see it looks better go ahead and get this please let's see there how co is working try to understand that I will also go ahead and show you a beautified format reformatted here reason is it looks much better uh from your end once you will if we are trying to understand and read after 2 days also. Okay, it is going to look better if you reformat it. Look at this here. It looks much better. Now you can clearly see that here you have the first concat. This is your second concat. This is your third concat with coies. Where are you typing separator where you're not giving any separator? Everything is quite clear. Now we can see quies is helping us in getting the entire whatever is available in the address. Everything is here. Now next here is uh see this becomes very complex right this does has the flexibility by the way what is the flexibility here flexibility here is I can give multiple different types of separator maybe I don't want to give two commas here maybe I want to give one comma and one forward slash here so here I we have got the flexibility to give multiple separators we do have the flexibility to replace the null values with any value we are looking for with any kind of value we are looking for. So we do have this this flexibility here. However, there is one more function here which can help us in uh getting the proper full address but without that much effort without this much effort here and that is your concat WS concat WS so concat and coales here can we uh this is the advantage of concat and coales right here I'm going to write this here very important to know the difference. So concat and coilies advantage is this right multiple types of symbols and deep limiters. But we do have one more function here called as concat ws. WS is with separator. WS is here means with separator. This is the concat with separator. This is the concat with separator here. So what does concat with separator does? First thing that it does is it can ignore the null value completely. It doesn't replace it. It simply ignores it. It can it can read the null value and it can escape the null value. After skipping the null value here it is going to uh it is going to trim uh we have to apply trim if we want to and it can add only one type of symbol or delimiter meaning only one type of separator. We can add only one type of separator. We do not have the flexibility of adding multiple separators. Neither we can replace null with any value. Right? So concat ws does the work. it gives us the output but with the limitations of only one type of separator here plus it doesn't it doesn't replace the null value with anything it simply ignores them completely so how do we go ahead and write this concat ws so inside uh how does the concat ws looks like so let me show you that of if I want to use trim I have to use the trim trim as we have seen before okay I have to use the trim as we have seen before so this is how the concat ws looks Right. First we'll go ahead and define the separator. Right? Then we are going to type at this line 1 2 3 whatever we want here. And that's it. Quite a smaller than what we have typed here. Right? So let me show you how we are going to type this out. I'm going to copy paste from the top here. I'm going to get all the three columns right here. After the full address true, type a comma and press enter. Why? Because now we are adding a new calculated column after full address two. So now here I'm going to go ahead and use concat WS. This is concat WS with separator here. Inside with separator first thing that I'm going to define is the comma as a separator there. After this here I simply need address line 1 2 and three. And if I want to use the trim function there I can. So I'm going to use this right here. Right here. And I'm naming this as full address three. Naming this as full address three. Let's look at the output right now. So uh let's look at the output everyone. So this was with the full address one. We have got null values. Second one is cois and concat. This one is concat ws and concat ws does look quite good. It does have few extra spaces here and there but we can apply trim uh to these columns and we can get it right. This is concat ws everyone concat with the separator. Now let's uh continue here with substring everyone. Substring here. Let's understand about the substring there. Okay. So here we go. This is substring. Substring is a very simple function. Okay. We'll find it again at mostly most of the places there with different names. Uh so substring what does it do? It simply creates a subset out of the string. Right? It simply takes out some part or some words out of the string there. First uh what is the syntax here? Syntax here is that it should be column right? It should be start position and how much length. So for example here those who are looking at this for the first time here. So let's say this is uh the name here this is a string everyone out of this string here if I will go ahead and ask uh ask here that go ahead and use this string go ahead and use this string there use this string here and start extracting start extracting from position number three and get the length position number three and get the length here let's say five. So what will be the output? So here here we will start at the third position. This is first this is second. This is third. Right? So we start at R. Then we will get D. A space will also be counted. A space will also be counted here. So 1 2 3 4 and 5. So output will look like this. RD space a n. So we now do we understand how does uh substring works space will also be counted. Okay. So let's go ahead and see an example right here. So let's say let's extract a few letters from a string or a text. So we have a product code. Everyone product code here is uh if I will show you the product code it is quite long we have S10 1678 and a lot more here right so let's go ahead and extract uh the first few letters here so maybe we can go ahead and say that select product code okay from the product so select uh I'm going to talk about the products table here what are we trying to select from the products table here first I'm going to go ahead and get the product code so that we know what we are doing there. Now here I'm going to apply the sub string. This is sub string here. Inside the sub string I will ask it that read the product code. Okay. It start here with the first position and get me maybe four letters after afterwards. Right. If I go ahead and get this here, this is what we get. What the exercise question here is that generate a custom product code that combine the first three letters of the product line. First three letters of the product line and a formatted price formatted by price column. So there is a buy price column here. That buy price column has to be formatted. What what does do they mean by it should be formatted here? If you look at the buy price column here, you will see that there are two decimal points. We don't need any decimal points, right? formatted by price means we need zero decimal points. Zero zero decimal points here and a unique reverse identifier. Reverse identifier here means uh I want to go ahead and use the product code here. But I want I will go ahead and reverse it because I want it to appear uh uniquely. So it's a unique reverse product code. So I will simply reverse unique product code here. And I don't want uh now once this will be done. Okay, I don't want to see the entire uh data set here. Now I simply want to see a sample of five rows. I only want to see a sample of five rows. Do we understand the question here? So we are using the product table. After using the product table, we are creating a separate product code, a custom product code down here. So step number one, first thing is where are we working? We are working with the product. So I'm getting here select everything from the product. Now in the product here, I will get the product code of course, right? And I will also go ahead and get the product line because this is the name. These are the two things that we using right now. Now after this here now we start first we need to create a code here right now first three letters of the product line and then a buy price with the formatted by price without the zero with the zero decimals there and then a unique reverse with the product and all these three should be just concated at one here so I'm going to go ahead and write concat first now inside the concat here inside the concat here now we I we will go ahead and get them one by one. So what is the first thing here? What is the first thing here that we are typing? First three letters. How do we get the first three letters out of the product line? Any idea what we should use here? Right? So you all know how to use a substring now. So let's use another function. You can also use left. You can also use left in this case because it is quite clear that I just want the first three values. Right? So how do we use the left function here? Left function here I can write product 9 and I can simply say get me three things from the left. Easy. P is shorter than the substring. Right? So if it is extreme it is if it is from the extreme left instead of substring I will use length. If it is somewhere in between I will use substring. Okay. So I'm writing left concat left client here. Okay, that's great. Very good. So if I go ahead and type the concat here, I'm getting here the first three letters C L A but I'm creating a code here right now. Uh custom code here. So I will go ahead and write this in the upper case. I will go ahead and write this in the upper case. Remember to close the bracket for every every function that you write. Now this looks correct. Very good. So first is done. Now after this here I will give a separator. After this I will give here a separator. The separator I'm giving a simple hyphen. Okay. A simple hyphen as a separator. Now go ahead. Now let's get the second part here. The second part here is a formatted by price with zero decimal. We know that the round function can go ahead and make it zero decimal. But uh there is a uh but the round function is used on numbers and round function give output as a number too. Yes. Okay. So whether I use true number, two decimal, rounded any function I use here, it will give me output as a number. Here I don't need output as a number. I need a text out of that. Why? Because I'm creating a text right now. Here I need a custom product code. I need a text out here. I need number as a text without any decimal. In this case here we will go ahead and use the format function. Another function here format. This format function here gives output as a text. It can do whatever uh round function can do. Right? But this format can go beyond the round function. So for example in the format right now I will go ahead and get the buy by buy price here. I will type a comma and type zero. And that's it. If I'm writing it like this here, if I'm writing like this here, I'm uh making sure that I will go ahead and close the bracket here again and look at the output. So what is the difference between the format and the round function there? Right? What is the format and the round function there? The major differences between format and round function is format gives the output as uh the character and uh round will be used on the numbers the output will be a number too. Another major uh here about the format function format function can uh whenever there is a huge number right whenever there's a large number big number there in that case format can actually give you thousand separators in the output format can actually give you thousand separators in the output. So if you want to go ahead and read a number a big number properly, you will use format. So format will make it easier to read with the thousand separators there. Plus you can use the format here to give a percentage sign too. So if you want to go ahead and uh format as a percentage. Okay, format can be easily used for that too. However, the output is going to be output is going to be a text. But yes, it can add a percentage sign at the end. So simply to add the percentage sign at the end we can do that. We can use format. Okay. So right now our we are halfway here that's why we are using the format. Yes. Right. I am directly using the format just one function there. Once that is done I will add the hyphen again because that is our separator. Now after this hyphen here the third here is for unique reverse product code. So uh yes we are going to use the function called as reverse right now. So this is reverse what uh in the reverse here I will simply go ahead and ask it that reverse the product code right here. Once this is the reverse code here and uh I will close the bracket here for concat here. Now when I'm closing the bracket here for concat here let's look at the output everyone. Oh look at this. It is the exact mirror image. It is exactly mirror image of the product code right here. I'm going to name this as custom product code. Now let's look at the output right here. I'm going to beautify this two. This is how it goes. There is one thing that we forgot. Okay. So what is that? We need only the sample of five rows. But here I'm getting the entire table. I don't want the entire table here in the output. What we want only the five rows there. So if you want only the five rows there, we can go ahead and use limit. We can go ahead and use limit and limit the output to the five rows. So you'll get only five rows here. Right? So uh let's uh discuss few things here. So what we have seen so far, okay, we went through select. Now we understand few things about select. What do we understand here? that if we are talking about the select here everyone with the help of the uh select we can add the we can filter out the columns and attributes that we are looking for we can add the aggregations we can go ahead and add the calculated value calculated columns here with different types of functions the different type of functions there from is used for any kind of schema name table name addresses there where gives us conditional or filter on the rows after this here we have got group by what is group by everyone. We always go ahead and group by any kind of dimensions or the categorical values here. What group by will go ahead? Group by will go ahead and create the aggregate reports. What does it do? It creates the aggregate reports here. It will aggregate the numbers in front of each category. If you know Excel, okay, it basically works like a pivot table. It basically works like a pivot table. What is having here? Having here is a filter. What is a having here? Having clause here is a filter but this filter only works on aggregated value. It filters it filters only aggregated only aggregated reports. Aggregated reports generated by group by generated with group by. So basically if you know Excel it works like a slicer. It works like a slicer. Right. After this having here you have got order by. Order by simply sorts. We have recently seen limit. Limit will limit the output in the result. Right? So we will have to go ahead now and understand. Now here we will have to understand group by and hammock. We'll understand group by and hammock. Right? So this is the sequence everyone that we are going to talk about. We are going to go ahead and understand right now group by. Now what is a group by here? Group by creates the aggregate report. Now what is an aggregated report? So before I go ahead and uh show you an aggregated report, I will give you an example right now. So for example here if I ask you that what is the average buy price of the products. Okay, I'm going to ask you here that what is the average buy price by price of the product line of each product line there. Okay. Of product time. When I ask you this here, usually we go ahead with select from. Select from product. Select from product here where so select from product. But what we want to fetch here? We want to simply fetch here product line and average. Correct? So if I uh uh usually what we write we will go ahead and write the average price here. What do we type in the average right here inside the average? I want buy price. So I will go ahead and get the buy price. Buy price. Okay. The average is somewhere 54 and that's fine that 54 is the average there. Okay. However, if I am I will ask you here that go ahead and get me the average price for each product line. So if I will ask you this question here that get me the average buy price for each product line. In this case what you will do? In this case what you will do? What should the table how should our table structure look like? In this case if I want buy average buy price of each product line we should have two columns here. Right? First column should have product line names here. Product line names here. Second column should have the average buy price. Average buy price here. However, the first column here, first column here, should I extract the entire column? Should I extract the entire column that we have in the uh we have in the products table? Should I extract the entire product line column that we have here in our data set? We need unique list here, right? We will need unique list of the names here. Unique list of names here. Why do I need unique list of names at product line here? For a very simple reason, if this is uh the product line for example, this is the vintage cars here. This is the vintage cars here. This is the motorcycles here. They should be written here only once. So that in front of that we can go ahead and write the names right calculate the average for that. Now how do I get the unique list of names here? So group by actually tells the select function group by actually goes ahead and tells the select function that see I'm giving you this list of names here. Okay, go ahead and filter them out and then calculate the average. So it is just not the unique list of names here. Okay, group by will also inform select that see you have to group by group by with each of these names here. Okay, that's why unique and distinct will not work. Why not? Because group by is specifically informing select function that see group by vintage cars, group by motorcycles, group by this and that. So it will what is group by? Group by means uh select will go back to the entire data base. Inside the database here it is going to filter out vintage cars. Okay. Then it will go to the buy price. It will calculate the average. Then it will filter out the motorcycles. It will go to the uh available by filtered by price. It will calculate the average. And likewise so group by me group by will go ahead in form of that. So this unique list okay this is extracted by the group by that is why whenever we are writing this here to extract for each product line here inside the select now here I can get the product line and comma average buy price however now I have to go ahead and type group by because this is the writing sequence and in the writing sequence I will say that this is the product line this is a categorical variable right now go ahead and tell selected here that product line should be extracted out but it has to group it by here and then calculate the average price. So when the group buy will inform that then only it will get the product price here. Okay, that is why group by works not the unique and first we will go ahead and have we will see the group by in detail right when we are talking group about the group by here within the examples here we are going to use all the functions that we have learned so far and within that we will learn about date functions too after the date functions here or within the date function here we are going to also explore here order by and limit so we will have some practice exercises we will see some different scenarios there and we will have the entire practice exercise there and now afterwards we will go ahead and we talk about the logical statements. So uh we will see that when logical statements in this you will have if else and we will also talk about case and memory and after that again we will combine everything here and we have been working only on one single table right so we go ahead and we talk about join what is join how many types of join are there and more so this is the today's agenda as in as the time and the speed will be there we'll go ahead and try to cover this today it should contains only however it should contain only motorcycles, vintage cars, trucks, buses, and trains. We have done this before, but here's a little twist. Now, we want the uh we want the average MSRP of the product lines, but not all. Only for motorcycle, vintage cars, trucks, and buses and trains. And once you get that okay the average price that you will calculate the average price that you will calculate here average MSRP here it should be less than 100. It should be less than 100. And of course you will go ahead and uh order it properly. Go ahead and list in the alphabetical order of the product line. Go ahead and sort it in the alphabetical order of the product line. Okay. Okay. So I will give you a I will give you a simple guidance here. First tell me uh how are you going to solve it? Let me tell you the steps here. Okay. Let me tell you the steps here. First is which table are we working with? Which table are we working with first? Let's decide that I'm giving a corrective way or guide to solve it. Which table are we working with? Which table? From which table. Do we have to apply any filter here? Do we have to apply any filter here? Where are we applying the filter? In which column? Okay. So, we go inside the products table here and we are applying it in the product line column. Product line column. Right. H. And what which function are we going to use here to filter out product line column? Which function we should use here? Function or anything else? How do which operator do I use here to filter out the product line? Yes, we use the in operator here within the wear clause. We use the in operator here. Very good. Do we need to group by? Do we need to group by? By which column do we do we group by here? By which column do we need to group by? Product line not product name product line because that is what we are using here. That's what we are going to use here for filter out product line. Okay. And uh select is what we want in the output right. So what we want in the output here only two columns product line and the select here. First column is product line. What is the sec? This average MSRP for individual product line it is not present in the data here. Meaning if I have to filter this out here I have to use the having clause here. And having why we have to use the having clause here. Having is simply a filter. All the operators that you can use in where you can use in having also but having only works on a report that is generated with the help of group by. So under the having here we'll say that go ahead and get this calculated column average MSRP. Okay. And make sure that it is less than 100. Now if we type this here. Okay. We see everything will be less than 100 here. There are too many decimal points, right? So, I'm going to go ahead and write round function here. Inside the round function, let it be there with two decimal points. Right? Now, this looks much better. So, this is how we use group and having. Remember that having is used in the aggregated report only. Also if you will go ahead and beautify this it will look much much better and easier to understand once you understand this part here we have still go ahead and learn a lot lot more right now about group by now right now with the group now we know how group by works right so now we are simply going to move forward with the date functions but now whatever we will see here okay you have to determine whether the group by is going to be used or not in a certain scenario So we going to explore a few date functions here right and we will explore group by with this with within the date functions here. So let's uh see the first question here total number of orders placed each year in the orders table. So go to orders table right get the total number of orders placed each year from the orders table go and get from the orders table. So what do we want in the output? In the output here we are looking for uh so first let's look at the orders table. Okay so this is the orders table right here and these are the columns from the orders table. Now it is saying that you need to count the total number of orders. Total number of orders here we need to count it. So what do we count for total number of orders everyone? What do we count? So now here we know that we need to go ahead and count the order number right here. Great. But it is saying that place each year from the orders table. So from where do we get the year everyone? We can we are going to extract the year from the order date. Order date is the entire date here. We can go and extract the year. Meaning how does the output look like in the output here? We should have year as the first column and then here we should have total number of orders and here we should have total number of orders right that's what we looking for easy I want year wise total number of orders should we use group by year so let's go ahead and write this out so first thing that I'm going to uh get away with that is select everything from the orders table once that is out here Now I will begin. So from the orders table here we are looking for first year first year column right and from where I'm going to get the year column from the orders date here. So how do we extract year from the orders state? We simply use the function year. Simply use the function year and uh we will get the year order date. I'm going to call this as order year. I'm going to call this as order year. Now after this we want to count the order number. So we can simply go ahead and count and we can get the order number here. I'm going to call this as total order. I'm going to call this as total order here. Total orders here. From where are we go getting this from the orders but we have to go ahead and group by two. Now in the group by here we have just one category here. So we can type group by one and we are good. Use the beautify here on the right hand upper corner here. All right. This will help you to uh view your written query in a much better manner and you will be able to actually understand. Oh this is what we have counted. And uh now what we want right now we want to go ahead and find out which day of the week has most orders placed now we we are absolutely aggregating there right so first here is first here is I'm going to go uh ahead and fetch this right here because this is what we are doing here right now the only thing that we have to change here is now we don't need the year here what are we looking for we are looking for week day so How do we get the week day here? So to get the week day here I'm going to type day and see here I have a day name, day of the month, day of the week. So what do I get here? I will first get day of the week. Day of the week and inside the day of the week what we have order date right here. Now we'll go ahead and count by order number. Now here I will go ahead and get day of the week first and I will execute this right here. Once I execute this, what do I get here? order one order here and since I'm grouping it by one here grouping it by one here I'm uh getting here total order by individual date now I have no idea okay so I I'm also going to order it by I'm going to order it by two wherever I have got the highest order that's what I want to see most orders placed so I will order it by the total number of orders if I do that here I need to do that in descending order okay so 6 day 69 currently I have absolutely no idea okay whether one is Sunday or a Monday and how does I go how do I go ahead and name them right yes correct there so what we do here we will now go ahead and get the name what we do here we will go ahead and get the name here so now I'm writing here that okay I have day of the week but now go ahead and get the day name inside the day name here we will go ahead and get write only order date I'm going to name this order date here as I'm going to name this as order week number. This is order week number and I'm going to name this as order week name. This is all week name. Now since we are writing two categorical variables here we'll go ahead and indicate group by we have to write group by 1 comma 2 1 comma 2. Now if I go ahead and execute this now we know that okay it's bit mistake uh sixth is Friday now I have to change the order by order by should be three order by should be three. So now we get six is the Friday and this is the total number of orders here. Highest total number of orders is Friday. Yes, I would like to remind you we are using the column numbers here. So my first column is day of the week. Second column is uh name of that particular day. Third column is the total number of order. So we have uh ordered by total number of orders here in the descending order. Yes. Uh here we have learned about this year and month thing and this is uh one type of order here. Why I'm giving a hyphen here? Because I want a hyphen to be written here. The code is just percentage y and percentage m. Now what will happen? What will happen in the similar query here? In the similar query here, right? Now uh we have written y and m in caps. Correct? So when I'm writing y and m in caps here, what am I getting in the output? In the output, I'm getting uh full name of the month and four digits of the year. Look at this. This is what we are getting here, right? What if I go ahead and write lower case, lowerase y, and lowerase case m here? So, I'm going ahead. I'm going to type lowerase y and lowerase m. If I do that, I get 0301 0302. What am I getting here? Only digits. What we get here? Only digits. So here this y will give last two digits of the year and a lower case m will give us simply month in the numbers right now there is one more there is one more there is uh b is also there okay what b gives us gives us abbreviated month what is abbreviated month here let's look at that so I'm going to copy and paste it again here and instead of m here I'm going to go ahead and type b in small case b here and I get this. Okay, I can also go ahead and what if I will write it like this. Now it is January 2003 4 and 5. If I want to make it better, if I want to make it better here, we can go ahead and combine this here with B and Y here. And this looks much better. Yes. So let's see the next example here. Again, this is an exercise everyone. This is an exercise here. It is going to combine whatever we have learned so far. And of course, you'll see another function again. So the uh here we want to retrieve the average days average days between order date and shipped date for each month. So every month every month what is the average days we are taking to ship the order out of our out of our warehouse. Right? So I want to see how many average days are we taking to ship the order out of the warehouse. So we want to go ahead and see the difference between these two and we want to calculate average about that that two for each month. However, it says that show only the results where average is more than 5 days. Why somebody wants to uh know that here? So what we want here is I want to go ahead and figure out that in each month in each month how much average days we take to ship an order right and in here I'm saying that I simply want to see all the orders here wherever the average is greater than five now why would someone needs to know that because if we are taking more than 5 days just to ship the consignment it means that that person is going to receive may be lo once I have given the order okay that person consignment is not reaching them before 10 days if it is going to out of the warehouse after 5 days it means that the there is something not good in the processing we need to optimize the process of the entire process of receiving after receiving the order what goes on in the warehouse whatever is going on there we need to optimize that process and when it is happening and why it is happening. If I want to find that out, I need to know wherever we where we are taking in which particular month we are taking more than the average of 5 days. Right? So that's what we want to know about. So let's go ahead and see how to see the difference between the two. Where is the ship date and order date? Here. Ship date and order date is here. So uh what we need here I'm going to go ahead and type select and I will first write that we are taking it from the orders table right here. Very good. Now inside the select please understand here we want to go ahead and deduct order date with the ship date. Now how do I do that and that to by month. So let's go ahead and get the get the month out first. So this is the month here. I'm going to get the month out from the order date that's there. So I'm calling this as order month. This is order month right here. Now after this order month what we what we want difference between the two dates to uh find the difference between the two dates. The uh function is date diff. You will find this date diff in Excel. You'll find this date diff in Tableau. You'll find this date diff in PowerBI and almost every BI software out there. So date dip gives us the difference between the two dates. Here of course in this we'll simply give okay this is the last date recent date minus old date new date minus old date. That's what we do here. And since it is the row level calculation and we want to calculate average by month we'll go ahead and average the date diff here. We'll go ahead and average the date diff here. Fine. If I calculate the average daily pair, I'm going to call this as days to ship. Days to ship from the orders. Right? Of course, we have to go ahead and group by month here. That's what we are looking for. So, we uh we will go ahead and group by month. Month is one. That's fine. After group by here, it is asking us the average. So now we will get the average days to ship here. We have the average days to ship here. But what we want to see right now we want to go ahead and see only where the average is more than 5 days. Everything else is fine. Less than 5 days is good. So here we go ahead with having and to the having we will say that this what I have calculated days to ship right. So I have to go it without any space here. So I will say that that having days to ship here should be greater than five. If it is greater than five right oh only the 10th month here which one is the 10th month so I will go ahead and add here month name I'll go ahead and add the month name here from the order date as month name month name here let's look at the output right Oh, I don't have the correct output. Why? I am writing everything correctly. This is month order date. Now I need month name. Then I need average from the orders and group by. What is wrong here? Why I'm not getting the output? Yes, very good S. No, if I'm adding two categories, I need to mention this category in the group by also. So group by 1 comma 2. If I type 1 comma 2 here, I get the answer. Yes, very good. So now I get it is October month and ship is 5.5667. I don't want that much average here. We can round it off. So I'm using the round function here. We are rounding it off everyone by two decimal points. Now when you're typing this, you have to be very careful with the parenthesis that you're writing. We are using a nested function. round nested by average nesting the date function here I'm going to beautify this so that it looks properly written here give me a moment it is showing unexpected error occurred it's okay showing unexpected error here. So now let's understand about the rollup everyone. Okay. So what is rollup here? Adding subtotals and totals in the report. Now before I go ahead and use it right here, let me show you an example first so that we it is easier to grasp. So this is an aggregated report. Okay. In this report here we are getting year here. This is uh let's say a month right now. And here we have got the quantity. Okay, how much quantity we have sold out. So let's say here I'm talking about 2005 and of course now I have got multiple months from 2005 for example. Now here I have 2006 2006 2006 and likewise okay so this is what I have here in the years now in the similar manner in the month here what do I get so let's say I'm getting everything for January February and March so here also I'm getting for last quarter only January February and March. Okay. And here are the quantities ordered. Here are the quantities ordered. I'm just writing some random numbers here. Okay. Great. Now, what is the total everyone? What is the total here? What is the total quantity? What is the total quantity everyone? A simple grand total. This quantity column total that up. How much is that? 15. So total quantity here is 15. So I'm going to write 15 here. This is our total quantity. This is what what it is. It is the grand total here. This is the grand total here. All right. Now if this is the grand total here, can you tell me can you please tell me the subtotal of years? Can you please tell me the subtotal of 2005 subtotals here? Can you add the sub totals? Everyone, you understand right? If I will go ahead and calculate EIS total it will be called as subtotals. You understand that the subtotal it will be called subtotals here for 2005 and 2006 right? Yes. 9 and six. So here the subtotal here for 2005 is 9 and here subtotal will be six. This is what we want to do when we are doing group by. Yes. Very good. So this is our objective. This is our objective here. Now we want to go ahead and add the total and the subtotal here too. So those who use use pivot table they know this happens automatically in pivot table. So if we have two categorical variables, we get automatically subtotal and grand total right there in the pivoted report. Remember that we have switched on and off the sub totals in the tabler and the outline view in the Excel. Okay. So that's how we get it. Now let's go ahead and see how do we do that in SQL. Do that in SQL. Here now here let's go ahead and see. Okay. So what I want uh we let's go ahead and say how do we add the grand total and the subtotal here. So let's go ahead and get where we have year and month together. Do we have year and month together anywhere? Here we are. I'm going to go get this first one here. So we going to group it by year and month. So after year here let me get month also here. I'm getting the month right now by order date. Order date. I'm going to type this as this is order month. This is order month here. This time we'll remember to group by 1 comma 2. And let's count total order numbers. Okay. And here we go. So right now here order month is 1 2 3 4 5. So instead of 1 2 3 4 5 year I will also go ahead and get the month name just to be precise. Just to be precise. Okay. so that I know which month we are talking about and we are not confused between total orders and the order month here. This is order date. I am also getting here name of the month here and we remember here to type 1 2 3 and group by. Now we are good. So now what we want here is we want to now go ahead and add the order month and the month name right here. That's what we want here. So if what will happen if I don't write this part I want to I'm simply showing you everyone remember that you are not working with me. Okay I'm showing you here. Let's say I uh it uh looks like that we don't need order month at all. Right? We are having month name. Then why do I need this number here? So let's remove it. So I have removed it. See now I have year, month, name and count. Now if I go ahead and execute it right here. Let me remove three from here and this I get. Okay, good. So right now we can see 2003 January I have total five. In February we have five. Now what we want to do here? We want to add the subtotal and grand total here. So in front of group buy I will simply go ahead and write here with rollup with rollup. If I write with rollup here it will actually add that here. However now since we don't have month number here it is going to forget that the January should come first. It will now sort this in the in the alphabetical order. That is why month number is necessary. However right now we'll work like that to understand grand total here. So this is the 2003 total everyone right then it is we have here we have 2004 total right now subtotal here 2005 subtotal at the end we have grand total go ahead and look at this please go ahead and get the group by with roll up here it will add the grand total and the subtotal here before you ask let me go ahead and answer your question right here that uh we can here in my SQL to replace the null value. There are few preference settings that we have to do and as an admin uh that permission should be given to us to actually uh write those codes right now where we can replace this null value with some other values here. So all these are the reference settings everyone preferences settings sorry since that those are not done we don't have the permission right now yet and by default it is set like this. So right we are not able to replace the nulls if else case and when. Okay we are going to go ahead and talk about if else and case and when that are that is the logical statements right here. Now we'll go ahead and talk about the logical statements. Now why logical statements? We had comparison operators. We have and uh or between. So we have got logical operators and comparison operators. Then why do we need logical statements too? So when you're applying and or between or anything okay you're looking at two or more conditions. So here in the logical statements why do we need this here? We need logical statement when I okay if I check for example and condition in the output what do I get? Do I get any output out there? No. It simply helps us to filter things out. It only helps us to filter things out. logical statements here help us to add a calculated column. Add a calculated column. But this calculated column is a logically calculated column. It's a logically calculated column. It is not mathematical. For example, right for example, I want to go ahead and assign a performance category to employees based on their job title. Right? For example, I now I want to go ahead and assign a performance category to employees based on their job title. To understand this question here, let me show you the employees table. You can see employees table here, we have got employee number, um their personal details out there, uh their manager details here and the job title right here. So based on the job title, we will go ahead and set their performance category. Done. Or we can go ahead and we can go to customer table and we can uh categorize the customers based on their credit limit. So if their credit limit is good, we can categorize them as gold. If the category if their um credit limit is somewhere between average, we can go ahead and categorize them as average and accordingly we can target them to sell credit cards. Right? So that's how it happens. So let's go ahead and see how do we go ahead and assign a performance category to employees based on their job title. So every time we'll go ahead and check what is happening inside it and and then we will move forward with that step step by step we'll go. So let me show you an example first. So there are two examples everyone if and else and case and when. So let me show you how if works here. So if here works as a function not as a statement there but it works as a function right now. F this F is exactly similar to Excel. Okay. In this if here person will go ahead and assign the condition. Now that based on that condition here you can get two outputs value true value of false and that can go ahead and give us the two outputs right there. If you have multiple conditions to check so this can help with uh this can help us with one condition and two outputs. Right? So if we have multiple conditions and multiple outputs, multiple conditions to check and multiple outputs everyone we will go ahead and apply next step and this next step is going to be exactly similar to what we have seen in Excel. So let's go ahead and see an example here. So first we'll see a simple example and then we'll go ahead and look at more examples there. The first example is going to be customer table to assign categories based on the credit here. So what are we going to do here? We will go ahead and say that that uh go ahead and assign high credit. Go ahead and assign high credit. If the credit limit is above 50K, if credit limit is below 50K, 50K or below, write uh categorize them as low credit. Categorize them as low credit. Now, where we will be writing this if function, we want to add a calculated column. Right? So, for example, here this is the customers table. Here I'm going to get select. So I'm making this is select everything from customer. So I'm getting this at least out and then we will copy more of I don't want all the columns right now. What are we looking for? I am only looking for let's say customer number and their name and their credit number. That's what we are interested in. Right? If we have this output here, if we have this output here, what we want? We simply want to add one more calculated column here in which we are going to say let go ahead and read this 21K and uh tell me what is the category there right so let's go ahead and write it out so to add a calculated column here we will go ahead and type within select we'll go ahead and type within select so I will type a comma here I'm going to next time because it is going to be a larger function see this is the if function simple function here opening the bracket up what is the condition Here condition here is to check 50,000. So I'm going selecting credit limit here credit limit if credit limit is above 50k if it is above 50ka what should be the output high credit because it is greater than 50k. If this condition is false if this condition is false what should be the output? No credit. Easy. Same as Excel everyone. Same as Excel. And of course I have to name it something. So I'm going to name this as customer category. Right? So while you're writing this here, while you're using the set function here, let me show you here that see it has added the calculated column. That's what we are looking for. And that to perfectly figuring out the numbers right there. Yes. Now here if I will go ahead and reformat it. I will show you the reformat version everyone. This is the reformat version. This is I'm copying it right here. pasting it here and reformatting it. See much better. All right. So this is what we have done yesterday everyone. And of course we have seen conditional statements. We have seen uh comparison operators plus the logical conditions too. And with this we have been looking at multiple different date functions also. So we have went through multiple date functions uh string functions, logical functions plus logical conditional statements too. Today we are going to begin with the join everyone with the join here we are going to go ahead uh understand what is joins what are the different types of joins out there and once join is completed we are going to talk about normalization constraints and we will discuss about the DDL DDL statements too that is data definition language where we are about to create the tables too. So by learning joins only we are going to go ahead and create the table right away. So before we move forward, okay, we have to understand joi