SQL Full Course 2026 | SQL Tutorial For Beginners | SQL Data Manipulation Tutorial | Simplilearn

Key Takeaways

[music] Ever wondered how to work with data in a database and manipulate it using SQL but felt overwhelmed by the complexity? Well, you're at the right place. This video will take you from a complete beginner to confidently using SQL for data acquisition and manipulation. Hey everyone, welcome to this course on data acquisition and manipulation with SQL. So in this video we will start with the basics and start working our way up to more advanced techniques. You will learn everything from writing simple queries to working with complex joins and conditional logic. Now what makes this video different? Most SQL courses out there are either too expensive or way too complicated. But in this video we will break it down in a very simple understandable way. And by the end, you will be able to write SQL queries, manipulate data, and even perform complex operations like joins, aggregations, and handing null values all for free. So let's look at the agenda for this video. First, we will start with the basics that is what is SQL, how does it work, and how do you set up your environment. We'll also cover simple SQL commands like select, from, order by, and fetch and organize your data. Second, we will dive into data types and filtering where you will learn how to use filter data using the wear clause, apply comparison operators, format your code for better readability. We'll look at functions like concat reverse to manipulate strings and handle null values in your data sets. Fourth, we will cover aggregation and conditional logic. You will master grouping data with group by filtering aggregated data with having and using if and case for conditional logic in your queries. And fifth, we will get into joins and advanced queries. We will show you how to combine data from multiple tables using inner join, left, right join, and even advanced joins like self and cross join. We also explore the union operator to merge results from multiple queries. So this video is packed with all the SQL skills you need to start working with real world data. So without any further ado, let's get started. Now before we move on, here's a quick information. If you're interested in becoming a professional in the world of data, then you should definitely check out this course, Data Analyst Masters Program by Simply Learn. Now, this course is specifically designed to help you focus on Tableau, which is one of the most powerful tools for creating visual stories and interactive dashboards. So, whether you're a beginner or looking to upgrade your skills, our syllabus covers everything you need to know. You will start with the basics of Excel, SQL, move on to Python, R programming, and master advanced data visualization. The biggest advantage of this program is that it's not just about theory. You will be working on renewable projects like crime analytics, sales tracking. Plus, when you finish, you won't just have new skills. You will earn industry recognized master certificate from Simply Learn and official certificate from Microsoft. This is your chance to join in a field that is adding millions of new jobs. So, what are you waiting for? Hurry up and enroll now. You can find the course link below. Now, before we get started, here's a quick quiz question for you. What does the SQL command select do? Deletes data from a table, retrieves data from a table, updates data or creates a new table? Leave your answers in the comment section below. Now let's get started. So my people I have been associated uh with simply and I have been in this industry for now more than 11 years. I have been in corporate training and consultation. So far I have taken uh I have been with more than 55 plus organizations majorly for corporate trainings. I take corporate trainings in MS Office 365 applications uh office productivity skills basically and with that I have transitioned myself five years before into PowerBI Tableau and data science. This is a short introduction about me and we are here together uh for data acquisition manipulation using SQL. First is first we'll get introduced to SQL here and then we will learn about what we have in the course right. So in the course here what we are going to learn in the entire course we will go ahead and uh learn about what are data types we'll also see what is data what is database we have to go ahead and understand here about database context here plus we will understand about few more concept of DBMS like normalization and other efficiency skills however within this course we are also going to look into select uh queries that means what SQL can do so within SQL. What is SQL? Why are we using it? And we will learn all everything from very beginning on how to extract a data on how to create a database till the Windows functions. So we are going to cover the entire SQL in upcoming sessions. First before we simply deep dive into what is SQL, there are few terminologies that we need to clear out so that all everyone is on the same page. So uh let's go through some terminologies and then we will come back again and we first we have to understand what is database we have to also understand why do we need it okay what is data according to information all right facts collection of information set of information specific format set of information so basically we are saying that uh data is information correct so if I go ahead and write some things here. Is this data? It is data. There are some numbers. There are some text. But does this make any sense? So what is data? In very simple words, in a very very simple words, data is structured information saved in a proper format. Saved in a proper format. format can be anything. That format can be uh CSV, that format can be txt but it has to be some format and so that the context is also clear. Okay, it can be Excel also. It can be in uh JSON also can be anything. So there are many formats that we can go ahead and open. It can be saved in multiple different formats there. Now that's a simple uh structure or definition of data. Then what is data basis? Data refers to collection of facts, figures or symbols. It can be processed and analyzed to extract useful information. So now here if this is data and we are seeing that we are saving that data in a proper structured format. It can be any uh CSV text file or Excel file or it is saved in a table anything there in in under any object. So what is meant by database? What is a database and why do we need it? Data is stored, multiple data, system where data is stored, huge amount of data, storing facility of data, where data is organized and can be referred to when needed, where collection of structured data is stored. Okay. So we are saying here where all information has been collected. So basically we are saying that that there is a proper base inside this. We are going ahead and uh saving our data set. Right? So let's understand it one by one. Data here is packed. It can be a fact. It can be numbers. It can be any other form of information. An image, text, icon, pictures, anything, right? They are generally structured in a specific way or stored even for a particular purpose. You cannot simply go ahead and store any random information together. It has to be stored for a particular purpose too. Now this data here when it is in one single pile let's say there is one CSV here then we are good that that one CSV is saved in our system in some folder there even now if in your system you have multiple folders everyone you have got multiple files maybe those are word files powerpoints PDFs excel files they are all saved for a particular purpose in a certain folder yes so basically what you do here in your own system is you go ahead you manage it organize it and according accordingly time to time you alter it and you change it update it correct and you keep on adding more data into it as required. So in the similar sense here this entire system or the folder that you have within your system you can call this also a database right we can call that also a database why because database here is just a structured collection of data that is generally stored on a computer so that we can access it manage it and update it easily. So that becomes a database. However, within the database, if we go ahead and refer our own systems as database here because it fits the definition here, is it easy to go ahead and find two connected files if they are in absolutely two different drives? We know that logic that how these two files are connected to each other. For example, your salary slips and your tax returns, they are connected to each other, but definitely they will be in two different folders, two separate folders, right? There your investment details are in another folder. your salary slip in another folder but you don't know that that these two things are connected to each other logical so that is not very easily managed ex organized because all logical is in our minds right now okay that is why we have multiple different type of database management system in database management systems here these are the systems specifically designed just to manage organize and collect the data there and to ensure that multiple users can easily access it without even touching the data there. So if we are modifi if someone else is modifying the data multiple users can access it extract the data too. Plus while modifying and updating the data this particular data management system makes that data is consistent their data has validated properly and plus the correct data is getting uploaded. So all of this can be done within that management system there. So database uh definition you all are absolutely correct. So what is DBMS? Um management system they have created in which they're saving that this data set and still and working on extraction modifying updating the data set there. So within the database now we agree that there are multiple data set done. Now the problem here is why do we need databases from here? Whenever we create a database system, there are few things that are very very important here. So for example, right now you have files in your system right. So let's say you have got two files in your office laptop. One is for employees and one is for department and there are two separate Excel she Excel files Excel workbooks. So if I will ask you that can you go ahead and get me the marketing manager personal details how will you find it? So I understand the question again. Let's say you have got two Excel sheets okay on your system right now or let's say two Excel workbooks here. One is have employee details and one has department details. And if I ask you here that can you go ahead and get me personal info personal info like personal info here of marketing manager. How will you get it? What we have to do because we have to go ahead and figure out who the person is. and employee details. Let's let's imagine here okay we have the personal employee details data separate only with the employee ID. So we have to go ahead and open the two Excel sheets in front of us and we have to first figure out any department details. We'll go ahead and filter out any manager or wherever the marketing manager is. We have to go ahead and filter that out. Once we filter that person out, we check for the employee ID. Once we have the person's employee ID, we go back to employee details. filter out that employee ID and then we get the details out of that. So either we do it manually or we apply VLOOKUP or anything we have to go ahead and merge both the tables together or at least open them together since we cannot merge the data is huge. Okay. So you have to go ahead and we are talking about Excel workbook right now. So nothing like that will happen. We are in the Excel workbooks right now in the exam. Right? So this is how it doesn't happen. This is why they came up with that this file management system is not working. Okay, with it is uh here there are two very important things here. First, retrieving the data is uh the huge limitation here is retrieving of the data set. If I want to go ahead and retrieve the data, it is very very difficult. If we are saving the entire organization's data into Excel files only in some shared drive only then we had no idea okay how people who is getting who is updating the data set how many people are updating the data set right so in that case here we have too many limitations the first point is there is no security okay because it is in a shared drive all the excel files are there for finance for marketing for digital marketing for HR so first First of all, security is not there and if a new joining join if new person joins an organization they go back and update its data set in their HR file someone is manually doing it. So when is manually going ahead and updating the data set and only not one person is responsible for that. So if there are multiple people there there are chances that we are going to have multiple duplicate top data set. If I'm saving employee ID, employee name, employee last name, employee phone number, email id. So if I have employee personal details that will be there also. If I have employee uh work details, I have to keep their contact details there also in case we have to go ahead and contact them. So like this there will be lot of duplicate data there and like this there will be more limitations here. That is why they came up with DBMS that is database management system. It was an entirely different type of a system. Inside the system, each table was saved as an object called as uh each data was saved as an object called as table. And that table was made with rows and columns. Now you must be thinking that why are you telling us about table? We know how a table looks like. I'm sure about that. Let's learn a little bit more about it. What we call as a table. Technically a data is called as a table structure that we are saving it in a table structure only when it has rows and columns both. Plus the first row will always be called as a header. First row has to be a header and each column header should have its own data type. If it is a number, it has to be a number. If it's a date, it has to be a date. All from first row to the end. And when we are reading this data set or this table here, we will read it from top to bottom always. Right? So that is DBMS. Now DBMS is just one problem. Okay? It has eradicated data validation issues. It has eradicated data consistency issues because now we can have security because we securing on the uh row level there. We are securing on the table level there. We are good. We can give access to the people we want to. That was good with DBMS. However, they had one uh huge problem that these tables were a standalone tables. They were not connected to each other at all. So if there is one table saved inside the object, I can go ahead and extract table from there. However, I those are not connected to each other. So employee table is not connected to department, department is not connected to customer or the vendor table. There is no common key between them at all. So that so when they faced this issue here they came up with relational database. They came up with relational database. Now they understood that that see if we need to have a table. Okay we need to go ahead and connect these tables too. So only keeping in table structure is not working for us. Since databases scaling every day we need to go ahead and connect multiple detailed tables with each other. But how do we connect them with each other? they go ahead and they connect them based on common columns. So if we had a department table, let's say if we had a department table like this, if we had a department table like this, we will make sure that we will go ahead and add employee ID. So this employee ID will act as a key or a common column. If I added a employee ID here that this person's employee ID is uh let's say 101 maybe this person's employee ID here is 103 106 and likewise okay so we can go ahead and we have to go ahead and update the employee ID right here after this here if now what do we connect it with now we can easily connect it with the employee table why because employee table is also going to have the employee ID now this becomes easier Now I don't have to go find and put. So this is relational database. In relational database okay we will go ahead and connect the multiple tables with based on common columns. If common column is there good. If it is not there we go ahead and we create the relationships by creating the keys. Right? So this becomes relational database. Now these databases nowadays are very very different types. One of the types here is MySQL. Okay, we are going to learn with MySQL. We'll see that right now. This is called as relational database where we have the common columns and we connect them together. So let's see different type of databases that exist nowadays. Nowadays things have changed because a database here like relational database it can save only date, numbers and text. That's it. If the data is within that a database can easily save it in a tabular structure. Right? So one how many types of databases we have here. First we have DBMS. Now we have relational database here. This is the first here. Then we have NoSQL database here. Then here is graph database. Then we have centralized database and distributed database. Now you must be wondering why I'm writing first, second, third year here but I'm writing first and second right here again because these are uh databases of different types based on different uh bases here. So relational, nosql and graph database here these three databases are differentiated based on type of data we are saving inside it. These are based on type of data we save inside it. And these here are these are based on these distributed centralized this is based on location this is based on location of data location of database this is based on location or you can say type of location. So let's go ahead and what is relational database what is NoSQL and graph. So as I have just explained to you okay what is relational database everyone is a collection of the objects data objects we are not calling them tables here we are saying these are data objects which are structured into tables okay they are linked together with predefined relationship what is a predefined relationship here common columns right and of course uh sometimes rows are also called as records columns are called as attributes here and each attribute has one single type of data type and that is fine with us we have already seen that in table it's structured plus we'll create the keys and the common columns to establish the relationship this is the most common type of database here and these are the few examples I'm SQL my SQL post SQL 2 now next year is next year is NoSQL database what is a nosql database everyone um since the social media okay after 2010 it has uh scaled up uh to the data that they started collecting. Okay, it has been scaled up to billions now at that time only. So these social media websites they have data set like not only your email ID and passwords and the bank details meaning not only number, alpha numeric, date and text. They also have now your images. We have got podcast, some audio clips, videos and comments, blogs, articles, entire web pages out there. So now we have non-tabable databases. The thing that we can the databases that cannot be stored in a just a table there. So they came up with no SQL here and uh these now stored as JSON documents. Not just JSON documents. These are JavaScript object notation documents. They are written differently. They are programmed differently. If you will look at them, okay, it looks like curly brackets and lot of and lot of commas and colons. But the heral data and a huge data can be saved in a very very small amount. So JSON documents here okay they are classified into several categories like data models, document key values, wide columns and graph databases. NoSQL databases are scalable easily scalable. Now if there is any kind of social media application right JavaScript object notation I will write it down here it's like we have Excel files right I will show you a file there so this is JavaScript object notation file it is like excel CSV JSON okay goj JSON you might have heard of that too so these are kind of the extensions of the data set it is a different kind of format it is written with a lot of semicolons and u very easy to define and we hierarchy inside it. I'll show you an example later. So this NoSQL database can save pictures in pixels. It can identify that too. Right? So NoSQL database examples here are MongoDB, Amazon, Dynamo, Apache, uh Hace. If you have heard of Hadoop, it is also linked together with this only. So when you we have these kind of data and we want to save that, we don't save it in uh Oracle or MySQL. We go ahead to NoSQL databases. Then we have got grab database to understand graph database here whenever you go to LinkedIn right u and you are looking at somebody's profile does that tell you that uh whoever has visited this profile have also visited these profiles or these are the mutual con connections you have this is your first connection this is your second level connection this is your three plus level connection have you ever noticed that on LinkedIn how do you think they're saving the data set with them so let's say if this is you okay you have got let's say thousand connections there. This is you. Now you have thousand connections here. Now all of these connections here how do they know which is uh falling on a second level, first level uh second level or first level. If anything new is coming up, new one, new person is coming up. They also know that they're connected to each other. They know that whoever the next person, a new person is coming up, this person has mutual connections with you. Meaning this person's network is coinciding with you. And this person of course has its own network again uh first level second level. Is it possible to save it in uh in an excel type of file? Okay. Where if you try to do that there will be lot of redundancy reputation every time because if you are saving one person's network is it possible to save one person's network? How many people are there on LinkedIn and they're all connected to each other? So saving this in any kind of format is very difficult and of course it is uh huge data set there. So what they do is they use cloud database. This crowd database okay they use uh graph structures okay which has nodes edges attributes parent and child relationships and likewise. So that's how they go ahead and save. So that just grab database we are not going to work on we are going to work on DDMS. We are going to work on relational database only. Right? We are going to work on this and we are going to learn with MySQL. Now let's talk about what is centralized and what is decentralized. So what is centralized database here? As it is said that it is a store located changed maintained at a single location such as a mainframe computer. So there is a mainframe computer a huge computer. Those who are from the nontech background it's a huge computer. Okay. It is kind of a server physically with all the hardware connected with it and it is somewhere located at one single place. Now everyone who works in the organization they don't have to be at exactly that location. Okay. With the help of internet here they can go ahead and access the access whatever data they want from here. Okay. So this is called a centralized database. Do you think it is going to have any difficulties or uh some limitations here? There are advantages, clear advantages that is going to be low cost, easy maintenance. But what are the limitations? Yes, we are storing the data here. Entire data is stored here at one place. Uh yes, if you have to scale the data, we have to make sure that our hardware can handle that. Correct. Both may know if I have one computer, I will place it at one geographical location, right? It is not going to be costly. Yes, if it gets corrupted or slowed down, entire organizations uh will be like server is down today. We are not able to do the work. Yes, it is easier to secure the data because it is just one single place. It is easier to manage the data too because it is just one single place. But if anything happens here or the server slows down, okay, we are going to face that difficulty quite often if the data is scaling rapidly, right? If there are too many people trying to access the same thing here and the hardware is not uh designed for that much scale or for that many people at once, it is going to lag. It can be slower in fetching the data too and if it gets corrected, our backup is still in this main frame here. So it will go ahead. So we uh we are at a risk of losing it but we always have a backup. Okay. So this is the centralized database and most of the organization most of the organization will be having the centralized database only all the organization that we usually see they work with centralized database only because it is easy to access and handle and scale. Now after centralized database here we have the distributed database. What is distributed database here? Okay, it is the collection. It is the unified collection of linked databases that are physically dispersed across multiple location. So here it is saying that they have distributed the database at the multiple physically multiple locations here but they are still connected with the computer network and they do have databases inside. They will have the databases saved inside it and since it is RDBMS everything will be connected to each other. Now what happens when the database is like that now when we have the users here when we have the users here they can still go ahead and connect with the data set okay by using the servers but yes of course they will be distributed to as per their requirement as per their requirement access permission and job roles they will can go ahead and access the data accordingly. Now what are the limit what are the advantages or limitations do you feel will be there network dependency it was in high cost maintenance that's correct it may be faster yes data security is the issue you have to maintain it at all the levels and you need now a bigger team to handle all the physical locations too but if there is a huge data they cannot save it at one single place they will disperse it across the locations Google uses that Google uses the distributed data base system of different databases right here. Yes. And scaling is easier meaning for to scale up they don't have to keep adding up too much hardware or systems. It will be easier. Great. So now we here understand that these are the different type of databases here. But we are going to work with we are going to work with RDBMS here. RDB RDBMS is the most sort of tech. Why? because most of the organization's data is uh that they want to analyze is uh date numbers and text. So RDBMS is best right and plus it is a simple mainframe computer we can connect it with local internet and we can uh connect with it easily. So that is the most sought out there but yes with the uh need of in the social networking systems and too much different types of data set they might need to scale up to no SQL or grab database. Very good. So the question here is the system is characterized by having its data structured logically interconnected physically distributed over several sites within a computer network. As it says here distributed okay this is the key that we need. So the answer will be C. Now let's go ahead and understand about SQL. What is SQL? We will deep dive into what is DBMS and R DBMS then. So first here is SQL. So let's go ahead and part this SQL. First in SQL is the language. What is language? So the any language. So this is a language through which we ask something. What do we understand that now? Now here is it is a language through which we ask something. Since this is a language, it has to has its rules and grammars, right? This is why it is a structure. So, it will have its own rules and regulations that we follow while talking to each other. We do go ahead and we follow those rules while talking that how would you make a sentence which are the vocabulary that you have to go ahead and keep it with you. Right? So, structured query language. So it is a query language, a language that has been created to ask the questions to the database and retrieve the data that you want or if you want to maintain the data set, you go ahead and you simply ask the questions to the system there. Remember that it is not a programming language. What is a programming language? We create something new there. You program something new there. So we're not asking or creating something new out there or the entire structure of something. No, a structure is already there. We are simply going ahead and retrieving the data from there and that's it. Okay. So structured query language here means we are using a language which has which has its own rules, regulations and vocab and using that language you're talking to the machine and asking the questions to it. That's it. That is SQL a structured query language. Yes absolutely correct. Now let's understand here what SQL can do. SQL can perform in a if we say here it can go ahead and perform the operations like create, read, update and delete. These are the operations that we can perform with SQL. What we are creating here, we can create a database inside the hardware structure that we have available. We can uh create a table. We can insert the values inside it. We can if you want to update it, if you want to alter it in some way, if you want to delete an structure or uh a row or maybe I want to update some data set inside it. Now when we are modifying and saving data inside it, once it is done, it is my part another person who want to simply extract the data and analyze the data that person will be reading that data. So while reading reading the data again we will have multiple steps to follow. So this is the CRUD here and this is how we can read the data here. So these are the four operations basic four operations here. Now MySQL is a language. It is not only used at one SQL is not a language that we use only within one single database. Okay. It has multiple uses across multiple applications and platforms. And why we are learning via MySQL? Because MySQL is free to use. Second, it is the easiest uh way to learn SQL. If you learn the fundamentals here, you will be able to adapt to any changes that you find in MSQL, Oracle, PL/SQL or Snowflake as you will move forward. Right? So the fundamentals remain same. It is an open-source uh system here. Okay? And it is easier to use a very very simple graphic user interface. That's why we'll use this here. Okay. So now let's understand the difference between DBMS and RDBMS. It stores data as a file. uh we know that right now our DBMS here stores data in uh the form of the tables. Now uh RDBMS can have multiple users. We can connect the tables with common columns that are the foreign keys and the primary keys there and it is easy research in RDBMS and all of that is not present in DBMS here. Data ping is lower and so we have already been through that. Now a quick check again which system is considered most popular and advanced version of DBMS with examples like MySQL, Oracle and MSQL as we just discussed it is our DBMS here right I hope you are on your LMS portal in your LMS portal on the right hand side when you click on self learning here on the right hand side you'll find us uh find a vertical menu card okay in your vertical menu card you can see all the lessons that we are going to cover you can see all the lessons that we are going to cover. >> Okay. So as you can see we are going to cover uh right now we were having introduction then we start working with the database and tables within that also what we are going to go through different type of commands you can see here and then we will see how to work with the operators constraints and everything else. So there are multip uh there are everything defined here what we are going to cover with the topics and subtopics. First here is first here is look at in instructor slides. These are all the powerpoints you will see me download using in in the class. Then here you have two types of practices. What are these two type of practices here? First here we have practice project and then we have here guided practices. You also have demos. Right? What are these demos? These demos are the ones that are used inside the slides. So when you go through these slides here you will see that they are doing some small activities within the slides and there are few queries written there. So on what particular in lesson which particular data set has been used to write that query you can go ahead and find it from here. Now you have guided practice folders here. Guided practice are lesson wise folders given to you and the practice projects that you see here. These are the uh these are the projects lesson end project. So once your entire lesson is completed, for example, you have learned about all these small commands still here, right? So the lesson two practice project is going to contain almost everything from this lesson number two. So these are the projects. What does these folder uh have? These folders have PDF files and uh two PDF files are there. First is the problem statement and other is the solution to that. Solution consists of the screenshots and the explanations for the query. Now whatever data set has been used in any of these project set it will be provided inside the data set folder. Inside data set folder you're going to find three things three folders inside it. One data set for demos, one data set for guided practice, one data set for practice project. So this is your reference materials. Now I would like you all to click on live classes. When you click on the live classes here, when you click on the live classes here I uh you have this uh box right now with today's date. Can you click on the box please? When you click on the box here, you'll find here session materials are written and you will see a list of session materials here. You will see some PDFs. You're going to see YouTube links. Those links that you're looking at, you have to go to the epsilon or the three dots there and copy it and paste it in the next browser. So session materials and reference materials are two separate things. The session materials are the live session materials. whatever we are using here in the live session of whatever I solve within the live session. Now in the session materials go ahead and find out there is a PDF file one for Mac OS one for Windows. Whenever we talk about data everyone okay so let's say we talk about my SQL now in the my SQL here when we talk about RDBMS database here it is made up of three entities or you can say three components what are the three components okay one is going to be hardware one is going to be data that we save inside that hardware and uh then we need the application to access whatever objects we have saved inside it so if you talk about like this right so we have software hardware and the data. Now these three components require a container a physical container that is hardware application program a software application program which will help us to connect with the hardware and the save data inside. These three components have three types of users too based on data obstruction mean uh understand data obstruction as a security layer. So who has how much access to database or individual data tables? First person here is the end user. Who is the end user in server? End user is the person who has the access to data as per the job role. Their main job is to extract the data and create reports. They will be using the minimal coding process and their only job is to extracting the data and making the reports, analyzing the data and then moving on to other applications. These are end users. they work on the external view or at the at the end user on the application level. Then the second here are application programmer developers. So those we are call them nowadays data engineers right? We call them data engineers. What do these people do? They are the persons who will interact with the data directly. These are the persons who will decide how to uh get data inside this uh particular server. How which uh relationship has to be created? What data should be stored? What kind of model should be created? So what kind of schema should be there? This is all decided by the application programmer. This application programmer has got nothing to do with data analysis. They are more concerned about how the data is managed and and secured inside the software. Now the person comes third person comes in here that is database administrator. This is the trend in in the entire structure here because this is the person who is responsible for the maintenance, operability, recovery of this entire connection. Meaning the network should work properly, right? It should be operable. So the end users can go ahead and access that and extract the data set. Plus this is the person who will uh give instructions access uh to the user. This person will control the entire data access. So as per the job rule whenever there's a requirement you keep asking for data and they will provide you with the data set or they will give you the access to that part of the database. These people work on internal level. These people work on internal level or physical level creating user ID passwords providing uh and replacing the security level of there. So these are the three type of users here. So now what we what type of which course are we doing everyone? It is concerned with analysis of the data set. This entire program is concerned with the analysis of the data set. You're supposed to analyze the data set. So either you're a data analyst or you're a business analyst or you will move forward to data science. Any one of these three whatever is your upcoming path there. Yes. So who are you? Which user are you? One, two or three. We are the first users. I don't know why you are not sure. Maybe you are uh the experienced professional right there. Okay. And you have worked within mixed job uh roles. That's why you're saying that you are not sure where you are going to go and end up. So if you are from the programming background, you have already been working with APIs and connecting multiple portals and extracting data inside the server, then you will be working as a data engineer, right? However, when you are will join as a data analyst, you will not have the access to SQL at all. Unless you're joining there with the 10 15 years of experience and on a manager level, then you will have the access to all the three levels, right? But uh those who are learning for the first time and you do not have any experience, you will be working only with the analysis and be extracting the data here. Okay. Not the entire team will have the access to it or anyone who joins as a data analyst after this program. Neither they are going to have access to make sure that data is getting there unless yes they join in a job role where they are supposed to modify access and make sure data is properly stored there. So now it depends on which job role you go but yes as a data analyst you should know about two things there. First here is uh as a data analyst your first step always to collect the data you have to uh first figure out as per the objective okay which data tables are required and from where are you going to get those data tables and the required data now it can come from one single server or multiple different sources once that is done your step number will be now you go ahead you analyze your data set maybe you import it into PowerBI maybe in Tableau maybe you want that data in a CSV you will go ahead and analyze with Excel here is you go ahead analyze and create your reports and at the end you need to share those reports. Of course you're not creating reports to save it with us. So we go ahead and we share the reports with others. We go ahead and present them to BPMs like the person who take the decisions. Those are the business process managers. However, okay, uh if you are an absolute beginner with no job experience, okay, you might go ahead into the uh into the level where you will be writing the SQL queries, okay, to extract the data right from here or maybe you will be asked to assist in updating and modifying the data if you have been given that permission. Usually, it is not given. Let's talk about our uh here my SQL. Now, I say SQL operations. Let's talk about SQL operations, right? uh SQL operations here. What is the first thing that we will learn here? SQL operations here can help us to do four things. What we have learned about those four four things there remember them. What were the four things that that SQL operations can do? What were the four operations? So first thing that we see here is so let's talk about read here. Create we understand. Okay, we create a database. We create a table. Create is fine. Let's understand about read, right? Let's understand about read. What read is going to do? Read is uh read means here that it will help us to fetch or retrieve the data from uh data tables or data set or subset of a data. Okay. From a table inside the database. While fetching this data here, while retrieing the data here, we might want to go ahead and uh give the filter. Maybe I want to go ahead and only want to view the marketing data set. Only want to view the last six months data set. It means we can go ahead and bring uh I need to filter out the data from the rows right first thing is that second here is maybe I want I don't want all the columns there maybe I want to go ahead and uh only keep the columns that we need right now by uh now after retrieving it or while retrieving it we might want to sort it properly if I want last six months data it has to be sorted in a proper time series or in proper date column so we can go ahead and sort the data too sometimes while sorting the data okay Let's say I have the data here from the cashers. So what the kind of data we have here? We have uh the customer ID, what product they have bought, what is the date and time, what amount they have paid, any kind of uh coupons were there or the cards were there that we had they have used there. So what data I have is just the sales amount data. If you want to go ahead and uh I want to calculate profit uh there, I want to calculate what was revenue there. If we need more calculations there, we can go ahead and add that too. So if you want to add any kind of calculated column, we can do that. If you want to aggregate by anything, we can do that. Any kind of mathematical calculations can be done and not only mathematical calculations, we can also go ahead and work with conditional or logical statements too. So this is what we can do with read and more. So we can go ahead and add calculate and aggregate data too. Maybe I want to simply see total sales by some country, total sales by product. We can go ahead and create an indicated reports too. Now when we talk about create here. All right. So within create what do we create here? We create the databases. We create data tables. We insert data records inside it. Okay. And since we have created those things we have to manage that too. Update uh the table all the data inside it. We might want all the data. Now to do these all operations here to do to uh do all these operations here we need some commands. Right? Vocabulary. I have told you there will be keywords and commands and vocab there because it's in a structured language. So to do all of these commands here, what are the types of statements that we will be working on? What are the types of statements? Command groups or types of statements to write these operations and we call them DQL data query language. Data query language here. So the selected statements that you're seeing here, right? That is that is meant query language there. Correct? Now this is data query language. It means the uh keywords that are used to query the uh used to query. Second is data definition language DDL. What is the uh definition here? We are defining the database structure or a schema. What we are doing here? We defining the database structure or schema. Meaning how many number of uh columns we have, how what is the data type of each column? What kind of uh limitation we have to put into that? Is it date? What kind of format? Right? So that is data definition language. Yes, correct. Like create the table, alter the structure of a table, drop the entire uh column from the existence and lot more. Third year is DML. Yes, very good. Third year is DML. What is DML here? Data manipulation language. What does manipulation language will do? It will help us to insert the data, update the rows, delete the rows, maybe merge the data together. This is manipulation. Third here is DCL. Yes, very good. Thank you. DCL is data control language. What is the data control language here? This data control language is going to help us to give access to people, grant permissions, revoke permissions and likewise. This is usually used by the Dian. The person who is supposed to give permissions, get user ids for everyone else. This is DCL. Then we have one more here that is TCL. TCL is transactional control language. Why transactional control here? What is a transaction? Okay, transaction here is you're trying to update the underlying data. You're trying to modify, delete a column or delete few rows here or you want to add few more rows there and it is not uh done by a single person is done by the entire team. So entire team is working on that who is keeping a check on them. Are they doing it correctly? Is it correctly done? Whatever changes they have done it is correct or not. So while doing these kind of transactional queries okay they use TCL with that and uh like these are few examples here like save point roll back and commit when you save up to a point you should be ready to control Z undo it that is roll back there or commit the change it means completely update whatever data we have done right whatever changes they have done there that is TCL transaction control language of course who uses this your data engineers they are the ones who are responsible for modifying and maintaining the entire data set there. Now, so these are the types of commands. Our main focus going to be here. Our main focus is going to be here. Okay. Uh we I can show you few examples at the end. Maybe I will provide you with some examples here. But our main focus is going to be here. Just letting you know, right? So these are the commands here. You will already have Microsoft Windows selected. Yes. Once you have your Microsoft Windows selected here, go ahead and click on download. Select the first one here. Select the first one here. Once you select on the first one here, it takes you to this page here. Right now in this page here, you're supposed to click on no thanks. Then start my download. So the one with 2 MB here, we will go ahead and download that one. Once you click on no thanks and start my download, then you can see all the next steps here. The post is custom. You get the server here. Post say they are giving a very good instructions here. You have to click on the plus sign. Then uh you will have the all the versions expanded. Then double click on this. Click on this. Get it on the right hand side here. Then find the workbench. Follow the same instructions. So everything is properly written here. Once you when once you will go through each and everything here you will have at the end you might see this dialog box. Few of you will see this, few of you will not see this dialog box. So, and you're good with that. Okay. So, you have to click on the plus sign and get the workbench and the server on the right hand side. After the after this here, everyone, you have to simply click on the plus sign here as it you can see it clearly. There's a plus sign here. Okay. Everything is properly given here. So, Mu and everyone else just follow the instructions here. It's a really good PDF. It will take you through entire steps. If you face any difficulty, you'll let me know. So, plus plus on server. Then once you see the list of server, select the first one and click on the green arrow. Now you click on minus. Then we click on plus on workbench again. Then the list of versions of the workbench will appear and you get the workbench here too. After getting these two here, we click on next. And after that, okay, keep clicking on next. It will take some time to execute. In few of your versions, if you're using Windows 10, it might ask you to get the visuals in here, Microsoft visual there. Agree to the terms and condition and install it. After installation, just keep clicking on next until you reach to the password there until you go ahead and reach to the password. About the password here, please let me clarify you. This password is uh not an easy password to recover. It's an open-source application everyone. So the root password please make it uh easy. Okay, that should be in your memory. Write it down somewhere. Write it down somewhere. If you think that you can remember it, okay, you can uh it is not possible to remember this. I would suggest that go with the 1 2 3 4 ab cd or uh quarterty these kind of passwords that you can remember there is nothing to save and secure here. Okay. So keep your root password easy as easy as possible. So that if you uh because it is not just clicking on forget password you get an OTP and it is reset. It is not going to be like that. Once you forget the root password even uninstalling and installing will not help you. You have to go inside it and then change it and update. You cannot recover it. So make sure that when you reach to the root password, it is easy and memorable. Okay. In this welcome screen, everyone, we can see local instance. Okay. So let's go ahead and click on local instance here. This local instance is uh when you click on the local instance here, it will take you to the workbench. What is a workbench? Here is the graphic user interface tool. so that we can go ahead and access the server. When you click on the local instance here, this is the interface you will be looking into. So we have uh nearly four quadrants here everyone. In first [clears throat] quadrant here you will find you have a schemas here. You might not be looking at a schemas. You might be looking at administration management like this. Yes. So on the left hand side here you will find your navigator management here. And just above this observe here you have your quick access toolbar. Above this you have got drop-own menu toolbar and here we have home tab and the local instance tab right there. What is this home tab and the local instance tab? This if you go to home tab it will take you to the welcome screen and back to local instance. You can have many instances open up here separately. Now we are in navigator we see the management instance options here. Within here you will also find an option called as a schemas. Click on the schemas please. In these schemas, do you see a cylinder right now? A cylinder kind of a structure. Uh menu, please click on this here. Now you click on local instance. Type in your password and it will take you here. So let me now go through the interface here. Okay. Okay. So as now here we do we see this container everyone? I hope we do. This container is our database. What is this? This container is our database. But what is written here? It says it is a schemas. Correct? So in MySQL here let's talk about the MySQL right now. In MySQL what is a database in MySQL? Data is database the term database okay it is going to be equivalent or synonymous with schemas. So now we understand what a database is right there storage system but what I'm trying to say tell you here is that you need to this schema and database is synonyms and MySQL right. So in database in MySQL is implemented as a directory that contain all the files that correspond to the tables of database. we can uh if you want to create the database uh database inside the MySQL okay you require admin privileges admin access without admin access you are not allowed to create you're not allowed to create the database or the tables and the term schema and databases are synonymous in MySQL here and uh in MySQL we can copy and clone the database features we can keep a backup of the copy of the existing database plus we can also it also includes table structure and lot many properties like indexes, constraints and values. As we move forward, we will learn about those terminologies too. Now within the database here, there is one thing that we uh all should know about. What is that? While we are working in database, okay, there is something called as asset properties of database. This is something very very important here. These asset properties of database here transactions ensure us reliable handling and management of the database. Now listen to this very very carefully. This is something that is asked a lot for in the beginners for the beginner's interview here. And this is something that you should understand that how things work. So uh there are four things here atosity [clears throat] consistency isolation and durability. What is atmosity here? Let's understand it with an exam. Okay. What it's written here is it ensures this transactions are executing in all or nothing manner. Right? So let me give you an example. Has this ever happened with you that you have paid for you have uh you paid online for something and uh they say that that your transaction has failed or if you go ahead and pay there online okay they transaction the amount is out of your account but they say it is not reflected yet on their part. And if in next half an hour it doesn't reflect they will make sure that the transaction doesn't take place and you get your money back. Have we ever faced these situations? Yes or no? Yes. That is atosity meaning either the transaction will take place or either or it is it will not take place at all but it will not be left in the middle that okay we I have updated some rows okay and only half of the row got updated other half was not there. It is not going to work like that. That is atmosity. These enable developers to focus on application logic rather than failures as well as recover of the complexity of the environment. Okay. Uh that will be easily done too. And all of this here right these are solely concerned with how a database recovers from any failure any kind of transaction or system failure that may occur during the process or the update of the data set. And all the databases that we see here are asset compliant. It ensure that only transactions that were completely successful that will be processed. If the failure occurs before we update something, if the failure occurs before uh a transaction is completed, the data will not be modified. So for example, I want to go ahead and uh based on the credit limit of a person, I want we want to go ahead and see which card credit card they're eligible for. so that uh we can go ahead and call them and uh connect them with them via emails that you're eligible for this credit card and please connect to it. So what we want to do we want to go ahead and modify that database and accord and we want to add that credit card there. Yes. So we went ahead and we have kept our logic that up to this particular level and if these conditions are met this person should be eligible for these credit cards and we want to go ahead and create a formula for that. But when we create the formula here either it will go ahead and calculate for each and every person or the customer ID present there or it will not even add that data there. That is all or nothing. All or nothing means either the transaction will happen or it will not happen. Right? So the easiest example that Ali we have talked about is whenever you pay online okay and your uh amount is uh out from your account but they say that we have not received your amount yet and in next half an hour if you don't receive it you will uh the amount will be given back to you by the system. So that is all or nothing because this is an example of atmosity. I'm not saying this is how database works. Database works like I want to add a column something happens or error comes in between. All right. It is not going to go ahead and update it halfway through. It will not not going to update the entire column at all. Unless uh it can go ahead and update each and every row within that database. It is not going to add that column. So that is atmosity. What is consistency here? Consistency is simple. Okay. It maintains a transaction. It maintains that a transaction follows a set of rules. Now what is consistency here? Consistency here says that it maintains a transaction and it follows a set of rules. Meaning if we are about to go ahead and modify any database within the team itself, we will go ahead and set some rules that okay how does this work and what are the set of rules that we all should go ahead and and follow there. So that is that becomes very important within consistency how the consistency will be maintained. If you know that uh if we want to go uh the entire process is saved right if you want to modify that or you want to alter the changes or you want to update a database so everything is going to be consistent so data should always be valid. So if I am using the uh date here DDMMY by so data date will exactly be like that that's consistency. So for example once you transfer the money to someone right once you transfer the money to someone what happens the bank balance will remain same whatever transactions you have done the bank balance or total bank balance will remain the same. So if you have given 1,000 rupees to someone it will only deduct 1,000 rupees automatically it is not going to simply change the data there unless you don't do the transaction. So it maintains the consistency there. So that's consistency. So data must always be valid whatever the rules of database are. If the database rules and the limitations are set then it will go ahead and follow those set of rules data validations and data types and other limitations and constraints too. That's consistency. Third year is isolation. Isolation is pretty interesting. It ensures that transactions do not affect each other. What does that mean? It means that each transaction will happen independently without interfering each other. So for example, if I am going ahead and adding a column, then the column will be added. If I'm deleting other column, the column will be deleted separately. They are not going to affect each other. A real time example, for example, there are two people. Okay, two separate people that transferring money at the same time. And do you think that will happen in the world? A lot of times there are many multiple people they're transferring money within the same bank at the same time. Is that going to correct each other's oper operations? Is it going to mix up the amounts or the bank details? No. Why? Because these are isolated transactions. They will occur independently for them. So that's isolation. So while we are updating the database also this has been taken care of and we work within that. So that's isolation. So if you're doing any kind of update, modification, adding of a data, deleting of data or adding columns, deleting columns or any calculations inside it or creating relationships or anything each and every transaction a statement will be separate and isolated. Now the next to the fourth one is durability. What is durability here? Durability guarantees that written data will not be lost. So durability says that okay once the transaction is committed once we have finalized that and we have committed that then it will remain even if the power failure or it crashes whatever happens but once the transaction is completed let's say I've added a column I have added the column values and after that uh even if uh the power fails or uh the server crashes the data will will remain within that particular server. So when you switch off your laptop okay the data will still remain there. So it is something like that. So that's durability. So for example once you are paying to someone and on your screen it displays like transfer is successful on the screen. So when it says transfer is successful there and the th00and rupees goes out of your account that is permanent. Can you reverse that on your own? Okay. Once the record is uh created there it will be created. We cannot undo that. So that is durability that once a transaction is done transaction will be finalized and whatever changes it has done those changes will be finalized too. So that is asset properties of databases it uh and all the databases must go must follow these properties here. So in the complete asset system the database engine guarantees that the synchronous write options are used. Hence it is preferred to physical media. So basically we are trying to say here is in the entire uh asset system here right we the data engineers basically those who are designing the entire system and maintaining data set they will follow the same and it is designed based on these four properties. Now we have talked about the database and schema synonymous in MySQL right. So we will now see how we can go ahead and see some very small few keywords when the instructions are given number right for example I will give instruction to SQL that go ahead and add a column in the existing table it will go ahead and add a column right because I'm the one user but but as if I'm the one user like me there are multiple users in a team everyone is doing their own work maybe I'm updating the customer data set another one is updating the employees data it. Okay. So if I'm giving instructions to add a column, add the data, another person is also giving the same instructions to another table but within the same server, these instructions or transactions are not going to affect each other. They are not going to get mixed up. I'm saying that in customer go ahead and create a column at the same time if that person is saying go to employees and update the rows there nothing is going to affect each other's transactions here at all. Or a very simple example when you go to ATM two people are simultaneously taking the money out of the ATM does that mean if you type 5k another person write 10k suddenly the transaction get mixed up and you get 10k from that person's account does that happen no that's isolation that even though two more multiple persons are working in that same system right they are not going to affect each other right so let's go ahead and understand about schema and how to write the syntaxes here and what does the syntaxes and parameters mean. We'll see that true here. Now, uh when we say database here, right? So, there are three type of database schemas. Now, what these schemas are basic or logical and physical schema here. However, to talk about the schemas here, first we need to go ahead and understand my SQL graphic user interface and run some queries there. Then, we'll come here and talk about the commands. Okay, we have already seen that these are the core type of commands there. So, let's go ahead and see. Now here uh this here is a schema. So we have established that there this is where we write our queries. This particular portion also have a quick access toolbar. Everyone on the right hand side this is the help section. Okay. What is this? This is the help section and this is where we see the action out. What kind of action output here? Whatever query we will type here. Okay. When we execute that then the execution time what action or what query was executed what is the result of that execution and how much duration does it took that action output will be here right so let's go ahead let's go ahead and uh see first learn here how do we type something here so first here is do we have any database here no we don't have any database here so let's understand how to go ahead uh and first add a comment and then we'll see how to get data set here. So first thing we will always remember database and a schema will be used synonymously here in MySQL and nowhere else. How do we add a single line comment? So if we uh if we go ahead and type the pound sign here or the hashtag that you call it, we can add a single line. If we want to add a single line comment, either we can go ahead with this or we can go ahead and type two minus signs or hyphens and a space here. This also adds the single line comment. Now what is a comment here and why is it important? Single line comment here is the non-executable part. What is it? It is a non-executable part. Basically it's the description of whatever codes and queries that you are writing here. But what are you doing? What are you working on? So whatever you're writing here, you have to write the description of that and that is going to be the the query. But not every time we can write everything, every explanation in one single line too. So what we have here is the forward slash and the aster sign and the aster sign and the forward slash. So we start writing like this. All right. And if you want to a multi-line commit we want to write in multiple lines. Right. So we have to enclose them enclosed within these here forward slash and now what we want to do we want to uh insert we need to go ahead and get database right we need to get database here because right now what we have is a system database it is not going to have any tables or something it has some some system details only right see what tables does it have here system configuration it is not going to help us uh here so we need to get the database. How do we get the database here? So, we have got either we should have SQL codes through which uh query quotes through which data is created, right? So, a created database here or we can have a CSV file. We can have a CSV file that we can import here. Okay. So, we can go ahead and import the tables too. So first that we will see here is how to get the database by using the SQL course right here right and how do we how we go ahead and create the database. So let's uh talk about create database right now. Whatever I am writing here okay almost in the real time I will update there now Excel is quite direct sorry SQL is quite direct. Okay, if we want to go ahead and create a database, we have to simply go ahead and write C. In the dropdown, you can see create is there, right? So from the drop-down, you can press on tab and the create will be there. You can also go ahead with the uh mouse there and uh double click on that. It will be written there. And uh if you're getting it in the drop-down while you're writing something, select the keywords from the drop-down. That will be easier to type. Now, what we want to create here? We want to go ahead and create a database. So, we can go ahead and write DA. We have the database right here. Now we are creating our first database here, right? So I'm going to go ahead and name it this here first database. I'm naming it first database here. You can name whatever you want. So this is create database post database here. Now first focus on what I'm doing. See whenever we are learning queries here. Follow along with me. Meaning first uh see what I'm showing you on the screen. Right? I will execute this. I will show you where to execute it, how to do it, and I will show you where the output is, what is the output and where is it. is attention on the screen please. You can see I have written create database and post database here and then I have given a semicolon here. Yes. What why do we need this here? Reason is uh semicolon here terminates the statement. It tells the uh the this tells the SQL that see this is where your command or the instruction stops. Correct? So it terminates that statement that here here this is the statement. This is the only command that you have to go ahead and execute right now. Now, how do I execute that command here to execute the command here? Either you go ahead and press control enter right that is the shortcut key here or you can go to the query dropdown here and see first. If you go to query dropdown, there are two options here. First is control shift enter that is uh it will go ahead and execute whatever you select. Select means proper select. it will should highlight it and get selected. Otherwise, it will go ahead and execute whatever codes or queries are there inside the script. We call this a script. Otherwise, if you want to go ahead and execute the current statement that we have here right now where our cursor is blinking, right? That is execute current statement that is control enter. We can also go ahead and use these here. Okay. So this first lightning bolt that you see here it will go ahead and execute all or selected this here executes current state because it has a cursor there. So lightning bolt with a cursor will execute the current statement wherever you are keeping the cursor. Since we are doing this we are executing this for the first time here right let me show you like this. So if this is the statement you select it like this and you execute with lightning bolt. If you want to use a second lightning bolt and we are only executing one single line, we can keep the cursor here and execute with the lightning bolt with the cursor or press control enter. So when we click here, see what happens. It uh in the action output it is saying uh green sign and we have create database here. One row affected right but even when the uh when it is affected here are we able to see anything right now here on the schema part? Now, so we have to go ahead and refresh the schema right here. So, first we kept our cursor inside. We have executed this, right? Now, go ahead and refresh the schema. So, once we go ahead and refresh the schema here, we find our database right there. Of course, currently it's empty. Okay, it has got nothing inside it. Once the exe we execute the statement, okay, we will see the output. We see the action output here. But if you want to go ahead and view the schema here, we need to refresh the schema. And there is a small refresh button here. Click that. You'll find this here. So first uh this is first. This is second. And this is going to be the third step. Okay. This is administration. This towards the right there is schemas. When there's a small refresh button here, I have given too many highlights on this. Can you find this on your own please? See right and what were the three way what were the ways to get to the database here first we could have created the database by ourselves but now we have to create the table inside the table we have to insert the individual row two so now instead of doing this let's go ahead and import the data now how do we import the data here from the SQL file from the SQL file in which already the codes are written for us this file is in the session material that is called as my SQL sample database folder. If you will go ahead and expand and extract that folder everyone go to the file dropdown left and upper corner from the file dropdown we have fourth option open SQL script. So we are here file drop-down left and upper corner it click on open SQL script. Those who are working in their systems just follow along and find where your file is. Every and the person who is working on the lab go to desktop. In the desktop you will find a classic uh you will find the classic models folder. Inside the classic models folder you might find the file. So we are going to the file here. Click on open SQL script. Find where your script is. Wherever you have saved it and click on open here. When you click on open, do you see these blue colored lines? If I scroll down here, you will find that okay, they have created a database on the 26th line and then they have created the table and then they inserted not many rows there. So, it's all written. Now we don't have to do anything else. Just keep your cursor anywhere. Doesn't matter where you're keeping the cursor. Keep the cursor anywhere. Doesn't matter. Just uh go to the query drop down here and click on execute all. Once you have opened the SQL script here, go to the query dropdown and click on execute all. So when you click on execute all everyone, okay, there will be action output. Some will be green colored check boxes, some will be warnings. For now, please ignore all of that. So once you click on execute on okay wait for five uh seconds there and you will see a lot of check signs and the green warning signs there and that's perfectly fine. We don't see classic models right here. Okay so please go ahead and refresh please again please refresh the schemas here. When you refresh the schemas here please confirm. Are you able to see classic models right now? Yes. Now I would also like you to pay uh attention here. Do you see now we have two tabs here. Do you see we have two tabs here. One is MySQL sample database and another is where we were writing the queries. Do we see that? Yes. So we have classic models right here. We have refreshed it and yes we have got two tabs everyone. One tab will be for MySQL sample database and one for query one. Right? So please switch back to query one and close the MySQL sample database. We don't require it anymore. So please close this one. It will be saved in your system. Please close this here and keep only query one and go ahead and save this too. Once you'll try to save this uh dialog box will open up. You can save the query by going to file dropdown and save. Then you click on save here. Okay. You will get the dialog box and you can go ahead and save your file within any name that uh you're looking for. So make sure you are where you are here where we are saving where we are writing the codes. Now this is the classic models right now. the uh inside the classic models here I would like you to expand please expand the classic models you will see tables here when you expand the tables here you will see list of tables right here we have got multiple databases also here correct so how do we go ahead and see what all the databases that is that are present here okay so we can simply ask it that why don't you go ahead and show why don't you go ahead and show databases the next step al is to write show databases because now we have multiple databases here, right? So we simply go ahead and ask it to show databases. Then it is going to give us the hidden databases too that we don't see here in these schemas. So we simply go ahead and write show databases. We can see the list of databases here. Yes. Now what do we see here? What is this? We are looking at this particular quadrant for the first time here because output is there right? What is this? This is called as result grid. This is called as result grid. Result grid will show us a temporary output. This is a temporary output. So while when we write here show databases, what it it is doing here? It is showing us the list of a list of the databases here. And this is going to be a temporary output that we can see or export. So we can see it here. Okay. And export it from here. So whenever you ask for a data, whenever you go ahead and ask for data from any database administrator, what do they do? They give you a CSV file, right? Most of the time they will give you CSV files. So you get to export it from here. This is the very this is your temporary op. We will go ahead keep my cursor here. My cursor is here. And this is the lightning bolt with the cursor sign. So I will go ahead and execute this. And here we are. Or you can press control enter. And we see a temporary output. This temporary output can be closed. We have talked about different databases, right? We have talked about DBMS. DBMS is simple file systems that we have in our uh own laptops. Second, RDBMS. What are RDBMS? Like Oracle, MSSQL, PL/SQL, PISQL, uh we have MySQL here, right? All the snowflake is there. It is also a type of RDBMS relational databases where data is connected to each other. Correct? Then we have got object- oriented NoSQL. In NoSQL the data is not saved as a table. It is saved as an object or JSON values there. Why? Because it contains unstructured data set like you have got videos, you have uh your videos, podcast, comments, blogs, post, pictures. So uh geographical data set that is too huge to be saved in a table. Those kind of things are saved within the objects there. Then we have graph databases. Grab databases save the networking within the hierarchy or a flowchart. It has got parent node, parent and child relationships, a start node, end node relationship node. So that is graph databases right there. So as we have already discussed when we talk about graph database, we are talking about LinkedIn social networking website how they are tracking that whom you are connected with and how they are interconnected with it. That's your LinkedIn. You go to Facebook, you see the recommendations right there. Right? That is the real scenarios there. So this is where these databases are created. Whatever databases are there when we talk about RDBMS for example there is an office uh an organization there that entire organization is collecting data from multiple sources. What are the multiple sources? An organization is creating data set. So here how do you think the data is getting into the servers? All of us go there. All of us uh go to the office. Correct. And uh do you go ahead and uh swipe your card or maybe you use the biometric attendance? Do we all do that? Yes. You are creating data. You're creating data. Where does that data go is going? Where is it getting saved? We all have a portal in which from you can go ahead regularize your attendance, check your attendance uh and apply for leaves. You can download your salary slip. I can check uh everything. We have a portal, right? That portal is like a website. That portal is like a website. Correct? Now, if that portal is like a website, where are they collecting their data set? So, at the back end, that website is collecting their data set somewhere. That is their database. But is that database your server? No. Is wherever their data set is getting uh saved at the back end, is that your database or is that your Oracle server or MySQL server? No. So what happens here is like we have HRM software there in the similar basis. We also have okay we also have database engineers that will go ahead and connect these application data set to our server and they will make sure that whatever data is getting created from all the departments maybe the sales people are using Salesforce and data is getting updated there. So what are they going to do with that? they will go ahead and they get that data first. They'll go ahead and they get the data first from multiple different multiple different options. Now there can those can be multiple different websites or portals or APIs where that data is getting saved and once they will go ahead and get the data in here that that then uh we then uh end users like us will get to go ahead and extract the data and actually work on that. So as an end user I am not concerned from where the data is coming into the server. What I am concerned is if my job role is in finance, if my job role is in operations and I have been given an objective as a data analyst, we have been given an objective that uh see and as a data analyst or a business analyst that uh you are working in an insurance sector, you have to increase the sales by 10%. You have to increase the sales by 10% in next quarter. How will you do it? So what will you do as a data analyst everyone? Yes, you have to gather the data. You can get the previous sale detail. You can get multiple product details, customer details. After gathering the data here, now you have to go ahead and anal and uh manage the data meaning ETF. You'll extract the data that do you want data from the server? Do you want data from certain department or do you want data from the outside? Correct. Now you will go ahead and extract that data. From where do we extract any server or cloud? Right? Not every time you'll have a server might be your data is saved in a cloud. You need to get your data from the cloud there. Once you have data in any kind of BI tool then you go ahead visualize it analyze it predict it forecast it now that goes there. So where SQL is getting utilized everyone SQL is getting utilized where we are extracting the data from a server. Now remember that as we have established first here SQL is a language that is used almost in every software. If it is not SQL then SQL like language will be used. So it is a foundation here to understand and learn so that you can extract the data from whatever server you will face. Okay as per the discussion. Right. So uh this is there is something called as DSA. There is something called as DSA. What is a DSA here? That is your data source administration. This is done by database engineers or the developers. Right? These engineers or the developers they actually go ahead and uh they will um connect. See here it is called as data source administration. Okay. First they will connect the raw data right. It can be any kind of uh it can be any other database also. It can be a website. It can be a file. It can be any cloud AWS, Azor, is no or your Tableau cloud out there or any kind of cloud or any portal that you guys have right or in your office. It will go ahead and connect with everything and then it performs the actual ETL here. It can be your Tableau, it can be your SQL server, it can be Oracle or you can use Python for that. You first go ahead extract every raw data from there and you go ahead and perform the idea within your SQL. Then they pre build the data model. What who does that? All database engineers not us. These engineers will decide the data model meaning which tables are there. What should be the common column there? What is how the tables should be joined or they should be normalized or denormalized? Whether they should blend together or not. They will build the data model. They will create the entire structure of the database. And then the data governance is applied meaning security is applied. Row level security column level security. uh metadata means uh your information about the database and the primary keys limitation constraints everything is put in place security as per the user will put in place different layers will put in place for security and after that data profiling will happen they will conduct EDN validation they will make sure everything is working profine and then at the end they will go ahead and they uh they will publish that data set for everyone after publishing that there now they will be managing it updating and taken care of. Once everything is done, then we go ahead and get everything here. You don't have to know about it. This is done at the back end. What we are concerned with that I have a database. Now in the database, I have these many tables. I need to solve uh how my customers are doing. So how do I write the query so that we can go ahead and extract uh the data. Okay. So this entire structure here is huge and it has multiple learner multiple people working in that. So let's have a quick recap here. Let's uh recap our interface everyone. Right in this interface here these are menu dropdowns. These are menu dropdowns. This is our quick access toolbar. Once we open it we already have the previous day one opened up right here. Correct. Now in this day one here and this is our schemas. This is the schemas here. How do we reach to these schemas? We have got two tabs here. One is administration and one is schemas. We have to simply click on it to switch between them. Right? Schemas and database. This terminology here in MySQL is used synonymously. Okay. Now we have learned about the comment first. Why we have learned about the comment here? Let's see understand this here. For example, get database is a comment. What is a comment? comment is a non-executable part because someone has asked that why we are using these symbols these are not symbols these are notations okay we are communicating to SQL that see this is not a command this is not a command because if I am not typing the pound sign or the hyphens here it what uh does it think that this entire part is a command this entire part is a command which is not right this is why we have to go ahead and either we will go ahead and type two hyphens and a space to inform SQL that see this is a comment. What is a comment? Comment is any kind of description we are about to write. So as in when we move forward you will understand that why comments are necessary. And when we writing a direct a statement okay we don't have to give any symbol right here. We can directly start writing the comment. All right. When we go ahead and write a statement here or a command here you can say meaning we are commanding MySQL to do something here. We write create database. Let's see how do we go ahead and get this classic models right here. So we go to the file dropdown. We click on open SQL script. Go ahead and find out uh where your MySQL sample database is downloaded. Where it wherever it is downloaded select your uh SQL file here. You might not have the extension written there, but you will find this SQL because that is the only file it will search for. Once you will find the file wherever it is, go ahead and click on open. Once you click on open, you will see another tab is opened up. Right? As soon as you see another tab opened up here, go ahead and execute it. How do we execute it here? We keep the cursor inside. We go to the query dropdown and click on execute all. Okay? Just keep your cursor here anywhere. It doesn't matter where. Go to query drop down and execute all. You can see the keyboard shortcut control shift enter. Once you execute all, wait for 5 to 7 seconds there and then click on refresh right here. Click on refresh. This is a refresh button here. Refresh the schema and you will find classic models right here. You can expand the tables here. You will see the list. Just below the uh file tab here, we have a plus SQL icon. Just below the file tab. So let's open up the date 2 file here. So go ahead and click on plus SQL here. When you click on the plus/SQL here, you will find the file will open up in the next tab right there. First thing that we do here is we save it right from here. So there you will find a small quick access toolbar just below your script file. This is again a small quick access toolbar. So here we have a save icon right there. So after uh after clicking on plus SQL go ahead and click on the save here. The save will open up a very familiar dialog box here. You can save it with any name you want to write here. Once you have added and saved yes file is saved. This is where we work. Very good. So this is our action output. There is too much extra output. Now see here output is felt because we have executed the classic models. If you want to clear your output here you can go to output right click here and clear. If you go to action output window everyone. Okay. If there are too many uh output lines are here, you can right click and clear. So that now when we write any new query, we will be able to identify easily about the output. If say there is something in the output, right click and clear. While you're doing it, let's go ahead and talk about today's agenda. So uh before we have already seen the how to create a database and we we can go we have imported thesql, right? We imported the data.sql. We can also go ahead and import CSV files everyone. We can also go ahead and import CSV files. So uh what we are going to see today first first we will go ahead and see how to import CSV right how to import CSV here. Next uh we will move forward and uh look for we have seen create and show databases right a very simple thing here but today we are going to talk about first data type in SQL but we have to understand the data types in SQL. After understanding the data types here then we will move forward with the select query. Now what is select query? Why uh what is the sequence of select query out there? What is the syntaxes of that? We will understand about the select query. So first is importing the CSV then learning about data types in SQL. This is our today's agenda. Do we have the first database here which is empty? It has got absolutely no tables. So now let's go ahead and import a CSV file. So under the assisted practice data set there is a under the lesson file there is an employee table there is an employee table. So it is inside the data set folder. Inside the data set folder you will have your the assisted practices folder. In there you have got lesson number file. In that we have this one CSV file employee table CSV. So let's go ahead and import this table inside our database here. How do we import it? We right click on the tables. Everyone right click and go to the fourth option table data import wizard. Go to the fourth option table data import wizard. You go to tables first database and tables. All right. The blank database that we had here go to tables right click and [clears throat] table data import wizard. When you click on table data import wizard what are you looking at? This dialog box here is asking us to browse. So let's click on browse here and uh find your database please. So inside the materials you have data set folder practices lesson five employ. When you click on open the file path will be here. After this, just keep clicking on next. Do nothing. Keep clicking on next until you uh reach to the finish button. So, click on next here. No changes. Click on next again. No changes. Click on next again. We uh this is only for us to read. Absolutely. You don't need to go ahead and do anything here. Just click on next again. Once it has executed properly, it will simply check it by itself. Then click on next again. It will tell you 20 records imported and then click on finish. So keep clicking on next until you see 20 records imported. Once you see it there, just click on finish again. Right? So right now we have imported CSV everyone. Now we want to view what kind of table it is. How do we go ahead and view the table right there? To view the table here, hover the cursor over here and you will find three gray colored icons information settings uh here and a table with a bold sign here again. So go to the third icon, a table with a bold sign and click on it. When you click on it, observe what happens. When you click on it, observe what happens. Table is displayed. How is it displayed here? A new tab is opened up and a query is written for you. Correct. Now what see here now the entire employee data is selected here right properly in tables and columns rows and columns here. This is employee ID, first name, last name, gender, role, department and everything else is right there. We can see it here right and what we will be focusing on the query that is written for us. See what is written here. Select estrix from P first database that is the database name and the table name right ending or terminating with the semicolon right here. So what uh what is the syntax right now? So I will go back to day2 remember to switch back to day two. Everyone remember to switch back here. How does uh uh this select query is looking like. What is axis here? Meaning all the columns. Okay. What does this mean? That we need all the columns. Now within the select here we are asking it that go ahead and select all the columns from where from first database inside first database go to the table. So we have to give the entire address here in the entire address. First we are giving database address that go to this database inside this database go and fetch the entire columns of this particular table. So we are uh talking in a very simple language here. Okay. So if someone will ask you here, you're having dinner. If you ask some ask uh this ask someone that can you go ahead and get me uh the uh salt and sugar from kitchen. It's that simple. So they will go ahead whatever they find they will bring it back to you. Either you can select everything or you can pick what you need. So here the entire address right there. Let's go ahead and understand here how does that work in real time. So we are going to work on classic models everyone. Right. So uh in the in this particular table here I would like you to observe here employee ID first name last name gender role department what uh type of uh what type of data it is text these are all text whether it is alphabet alpha numeric it is going to be text and most of the time we are holding on to text right let's look into the experience and salary employee rating this is for numbers. So first is data types in SQL. Let me cut and paste this here. So first type of data here is always text or character. Most of the time the dimension a dimensional detail that you will find is uh going that you're going to find in a text format or text data type. Text data type is sometimes also called as uh character or string. In SQL how these data types are defined in SQL. Here these data types are defined with ch that is car. What is car here? Car means a fixed length character. Car means a pixel length character that all in all the uh length will be similar. What is meant by fixed length here? In a fixed length mean any kind of ID or unique code or unique ID that we provide. For example, employee ID uh employee ID is fixed. Everyone will have either alpha numeric employee ID. It will be uh it might have six uh characters or it might have seven characters or 10 characters. Character means any kind of code or ID is given. Employee ID for the entire organization will be fixed. If there is a product ID, it will be standardized or any kind of error codes or defect codes if we have during the operation cycle that will also have a fixed structure in the format. Okay. So that is correct. That kind of columns are defined by character. Next sh is vcar. What is vcar here? Very simple. Wcar has variable. What is meant by variable? Name of anything. Name of anything. Name of city, product, uh first name, last name, country, whatever is out there. That is where means variable length. What is when variable length is? Whatever data we are going to write in multiple rows, there is uh we cannot say that their length is going to be always same. Not everybody's name is same. Not not every city name is similar. There is no fixed format for that. So it will have a variable length across the rows. That is why it is. We do have more textual string. But these are the most used ones. Then we have numeric. In the numeric here first the integer example for integer. We know that it's a whole number. What will be the example of uh integer? A whole number. What kind of data will be whole number without decimal? No cash. Whole number can be number of projects someone assigned with. Whole number can be number of cars parked on a certain day. Sometimes we are only looking for uh the uh whole number age. Quantity in inventory correct that's also that that is also integer. How much quantity we sold out. How many total employees are hired this month? How many got retired? Defective number of product. Number of defects in a product. Marks can be in decimals not always whole number. So these are the few examples of whole number. Okay. In real time. Now we have float or double. So either we call it float or we call it double in here. Yes, no worries. We are all learning. So this is float or double right? What is float or double? So here it is with decimals or fractions. So maybe we are uh give me examples for decimals and fractions everyone. What kind of data set can be in decimal or fractions? Right? Says profit any kind of continuous numerical variable with the larger number out there. It will be float or double. But we do have one more type here called as decimal. What is decimal? It means again the number with decimal or fractions. Again with decimal and fraction. Then what is the difference between them? Then what is the difference between floating double and decimal? If both the numbers are written with decimals or in fractions, right? It is again on based on where are we using it. First in the float here. Okay. Uh in the float here we are using float or double data type. Whenever uh we are working with where wherever we can round of the decimal wherever we can round of the decimal where we can use rounded of decimal values right. So we can have four for example we are writing here 45 let's say uh multiple options right there if I can round it off to 45.9 only I can go ahead and write it with 45.9 for example then I can go ahead and write it because I can round it off or I can simply get rid of the extra decimal points there and it is not going to impact my uh last answer. What kind of values uh are there where we can work like this? Any kind of mathematical statistical value calculated like your average, mean, median, mode, a standard deviation, variance, probability. Whenever you are going ahead and calculating any kind of values like these right and if we have too many decimals at the end, we can round it off or cut it off that okay, I don't want too many decimal points, let cut it off to one or two. In that case here we use float or double where we don't happen stick around here we can round it. Then where do we use decimals? We use decimals here where my decimal numbers are fixed and no matter what happens we cannot go ahead and simply round it off or um cut it off there. What can be the examples here? I will give you a very simple example. Would you like anyone to round off your compensation for the work you have done? No. So when people are actually writing uh the any kind of accounts and finance data, right? They don't round it off. They'll keep every decimal out there. Correct. Any kind of commission calculation, salary calculation, bas any calculation based on any kind of accounts, they don't round it off. Right? So this is where the data type will come. Now you will also find there are many other data types too like big integer uh small integer medium integer tiny integer based on the length of the integer out there and you will find more. So when we will actually encounter that we will see multiple uh different numerical values or numerical data types there. Then we have date and time. Now where do we find date and time? So in date and time here we can of course go ahead and type date separately. You can go ahead and uh type time separately. Of course, if we have date and time, we'll have the date and time stamp also with us. So, either we have date time stamp here or we have only time stamp here. This is a standard everywhere. Okay. Another standard here is that your year should be separately written because sometimes if it's a time series data, we'll only have years. Now, years can be either in two-digit format or four-digit format. That is also true. That's how we also write it in general life. However, in MySQL, the date format is fixed. In MySQL, date format is fixed. So, if you're importing CSV or you are just uh uh inserting the f data inside it, right? Your format should be year, month and date. If your data is written like this within the uh double quotes or single quotes there, then only it will recognize this as date and time. As date and time, time is hour, minute and seconds. Similar to exterm uh if we will go ahead and we will not type any spaces anything out there okay like this again it will be uh considered as a date and time you want a time stamp only then it will be hour minute and seconds it will look like this and two digit or fourdigit year format we all understand that 25 or 20 25 you can write it anything like that so this is your date and time here there are few things that I would like to remind everyone always end the command with semicolon Right? And if you want to go ahead and execute a certain statement, right? Then go ahead and you can type you can simply go ahead and execute with keeping a cursor there and pressing control or enter. Okay. So all in the script or select or I will type or here for clarification or select current statement or keep your cursor there and exit it. To execute you can also go ahead and uh press control and enter or we can use these lightning bolts right here. Use the second lightning bolt for one statement. We use the first second lightning bolt here which is a cursor name. Okay. Now we already know how to create a database. We have seen that we need to create a database and we can have a name there. We can go ahead and show the databases too. Right? There is one very important thing that we should know. Keywords are not case sensitive. Only the keywords that we use like create database and more. These are not case sensitive. All right. So what how many databases do we have right now in uh in the server to see that we will simply go ahead and type show when you type sh in the drop-down show is here we can go ahead and select show space we looking for databases so we type databases here and we end the statement with semicolon right here now see here when the cursor is blinking here I want to execute this statement either press control or enter or simply go ahead and click on this light go Why I'm explaining this in a uh very detailed manner right now because I have been asked at the start of the session that um show it again how to execute it that's why so um I don't remember your name there but now do you understand how to execute a statement write a statement execute it please now this temporary output that we have right now with us it can be exported to CSV this is how you get CSVs whenever you ask your organization ation give me the data. So this is from where you get your CSV files. So my SQL exports to CSV, right? So if you want this to be exported, you go ahead and ask it that uh export this to a CSV your HTML, JSON, Excel, whatever you want, you can get it from. Okay. So it can be exported to from this is your temporary. Now next here is we we can cross it and close it. We have a cross sign here, an X here, right? We can close it right there. Now let um now let's move forward and let's learn about the select queries everyone. Let's learn about the select query here. When we talk about the select query, right? Now we are talking about reading reading the data set or retrieving the data set. Right? There is a writing sequence. There is a writing sequence. Do you remember the SQL operations? First retrieve the table. But I want to choose the columns and filter out the rows based on maybe country or credit limit. Then I want to sort the data set. So first option that we have here is select. We write sequence here. First is select. Select will help us to choose columns. Right? If I want to choose a column here, then I can use get me employee ID, employee name like that. But if I need all columns here, what do we do? If I need all the columns, what do we write? Yes. Very good. We go ahead and then we type restricts. After select here we have to also tell my SQL from where are we getting it. So the second keyword that we use here is from and from here we give the entire address about it. What is the address here? Address is first your schema names comes in. First you write a schema name or database name. All right. Then we type a period there or a dot there and then we go ahead and give the table name. We go ahead and we type the table name. So we are giving the entire address from there. Okay. After from here, after from here, if we want to go ahead and apply any filter, we have a wear clause. This where clause will help us to uh help us to the filter rows from database from database directly. It will apply the filter on the rows from within the database. There this is where clause. Now after where clause we have more options here more keywords here after where that is group by having order by limit. Order by is sorting your output. Order by will sort your output. Right? What limit will do here? Limit will uh limit the number of rows and output. Number of rows and output. So the temporary output that we see there right to export. If you want to see only five rows 10 rows it will simply limit it. upon defies the source of data that we are looking to retrieve. Uh yes, you can say that. So we we are simply asking it that go to this particular container. Inside this container, this is the tip. So let's focus on these four clauses first, right? And then we will move on and learn this. First let's start a small with select and from we have the classic models here, right? So what is the how does the syntax looks like for select here. Now we understand that very easily. We have to write select. We have to define whether we want few columns or one column here and then we will ask it to select or defined columns from a schema name. Right there this is how the syntax looks like. So let's uh go ahead and write it here. We going to write here select. I'm selecting from a drop-down select. Right now let's get all the columns because we are not aware about how these tables are. Select everything from see here I am simply typing and selecting from the drop-down from where classic models. So we will type here C L A see classic models is there in the dropdown. It says it's a schema there. Select it with the tab or double click on this. I have selected this. Now we want to define the table. So we click on uh we top uh click the dot there. Type the dot there or the P sign. You will see the list of tables inside it. So let's look at the customers table right now. That is the first table here. Click on tab or double click on it and it's there. Now we put a semicolon here. After you put the semicolon, control enter or click here. Once you click here, you will find the entire customer table is here. Also look at the output here. It says 122 rows are written. Meaning in this entire data set, we have got 122 rows. Select it from the drop-down. Do not attempt to type the entire thing. We will make we are humans. we'll make these spelling mistakes very easily. Currently we are getting all columns. Okay, currently we are getting all the columns. Uh now next we are going to see here how to select only one column. Select only one column there from the data set. How do we go ahead and select only one column? If we want to go ahead and select only one column, we will type select. All right. And let's see from the customers table here. from the customers table here we simply want to get only customer name and nothing else okay in that case here what do we do I will not go ahead and type that column name absolutely not okay let's use the graphic you uh our interface here when you expand the customers here you'll realize you have columns right now when you expand the columns you will be able to view all the column names right here okay once you're able to view the all column names right here if If I want to type any column name here, we have to simply double click. We have to simply double click on that. Once you will double click on that, it will automatically types that into the script. First everyone expand expand expand reach to the customer column names here. Now after select here when the cursor is blinking, I'm going double clicking on customer name and see it appears here. Now from where do we want it? So we will type from again. We'll ask it to go to classic models again and from classic models go to customers again and it will only retrieve one single column for us. In the similar manner if we want multiple columns in the similar manner if we want multiple columns to be retrieved what do we do? We go ahead and type select again and this time we will simply double click on multiple columns. For example double click on customer name. Next maybe I'm looking for their country. Next maybe I'm looking for their credit limit here. So keep typing comma here and we are good. After this again I can copy paste from here. Why to write it again? So copy and paste it. We can paste it in another line also. Why? Because we're terminating it here. So it understands that unless the termination doesn't come this entire is one single command. So if my if I keep my cursor right here after typing this in two lines and I execute right here you will find we have customer name country and credit limit right here. First try it out for one column then give comma and try it out for multiple columns. We are using the hashtag sign here to tell SQL that this is a comment I'm writing. It is not a comment to be executed. It is not a command to be executed. Is it? It's a message that I'm writing here for our own understanding. We use the hashtags or the hyphens. Question why it is there? What is a comment? Why do we need it? Because we need to differentiate between a command and just a message that is written for our own understanding. This message is written for our own understanding. SQL doesn't need it. All right. Uh so now we have one column and multiple columns. Everyone let me show you something here. Whenever we are writing this here, this is how we are writing. Okay. But there is a way to type it. It is not a functional language but it does have a format to be written since we are new and we don't know that here. So go ahead and uh select the end uh select these two lines here and go ahead and click on this brush right now. This is the beautify brush. What is it? It is the beautify brush or uh the shortcut here is control V. So beautify or you can call it reformat. It is beautify or reformat here reformat. Okay, it will simply write it in a separate a different manner in a proper functional language. So see here how does it look like after beautifying it. So I'm going to select this entire command and click on this paintbrush. Once I click on the paintbrush, this is how it looks like. Select properly spaced name from and properly spaced address right there. Okay. Uh you do not have a database called as employee table. Sorry, you don't have a database called as first database. This is the way it should be written. Which one looks better? First or second? First one is better or the second one. This is how we have typed it. This is how it has beautified and written it. Which one looks better? Right. Absolutely. The second one looks better here. Right? However, it is better to read. If I visit revisit this particular script, then it will be easier for us to read and understand. However, it is not that easy to write. So, writing is easy here. But when we want to revisit it, this is the better way to do it. Just a way for you. If you're writing a two long queries, always verify this. It will be easier to read. So, let me also write with you here. So, here we go. I will ask it to select here. Select and we will simply double uh click product name comma double click on buy price here and now we know we can simply copy paste from classic models customers but we don't want customers do we know we are looking for product so we have to write classic models again and get the products here we can choose to verify this and execute it here and we get the answer there is one issue right now here since we working on one single database. All right. Every time whenever we are writing a query, we have to write classic models classic models multiple times. So how do we get rid of that? We go ahead and we set this set the database as default as default. Let's see. Whenever I ask you here that go and get the products table, go and get the uh employee table, go and get the orders table, whatever table I will tell you, go ahead and go to the default database. How do we set our default database? Here we will type here that use classic models from here on. Meaning from here on go ahead and use only classic models as our default database. When we go ahead and execute this here, our database turns into default database. And how do we know it has turned into default database? See here I writing this here. I am executing it right here. The moment I execute this classic models will turn in bold. Right? Now another way is to set this as a default. We can right click here and set as a default schema. So that whatever table name we write, it will go ahead and get that table for us. That is default schema. Here you can also doubleclick on it and turn into default. So for example, if I double click on first database, it will turn bold. It is default. If I double click on classic models, it will turn bold meaning it is default. So there are multiple ways here to set the database as D. It is a very simple thing that we are writing here use database it is setting this as default and observe that it will turn into gold once uh we will do that here if I go ahead and say that okay select everything from and let's say we need from orders so now I will not type the table name see it very carefully now I we are not writing classic models name here I want to directly write the table name here for example orders so if I want the orders table here I will simply go here and double click even on the name of the table see here select everything from I don't want to type classic models anymore because it is set as default so we go to the orders and we double click when you double click on the orders automatically orders will be written here we can end it with semicolon here execute it and see the orders are here okay go ahead and get this please without writing the classic models I'll wait for your responses. From now on, whatever we type in this script, right? We don't need to type the classic models. Again, if you're using any table that is present within this database, how do we type the table name? Now, instead of selecting from the drop-down, now we can simply double click here. We get the table name right there. Observe that here selected from is written in caps, here selected from is written in a smaller case. Both are working fine. Why? Because it is not case sensitive. We will go ahead and now we will see how do we filter the rows here. To filter the rows here, let's go ahead and uh we have multiple product right. So right now we will go ahead and get the product name and buy price. We'll go ahead and get the product name and buy price. However, this time we will get the buy price and the product name. Product name. Okay. Which are which are lower than 100. Okay. which are lower than 100. Meaning buy price is lower than 100. So we already have this here. We already have the product name and the classic models product. We already have this here. So let's write it again. So we are typing here select. We will go to products table. We will say that get the product name, now get the buy price from where? From products directly. So we double click on products. Now we have the products here. Now if we go ahead and execute this, we can see the buy price is right here. Buy price is right here. Oh, I see everything is already below 100. So let's make it below 50. Okay. Wherever the buy price is lower than 50. So how do we introduce the filter here? After from here we will go ahead and type where clause where where is the filter we want to put? We want to say that go and check the buy price. Go and check the buy price. So we can go ahead and double click on buy price again where buy price is lower than 50 meaning less than 50. So we can go ahead and use the less than sign here. So buy price is less than 50. 50 is a number. Okay. So when we go ahead and execute it now this here execute this here how many uh rows we have right now. So originally we had 110 rows. See here originally we had 110 rows. Once we filter it out we have only 48 rows. We have only 48 rows. See here. Yes. If I have only 48 rows, uh can you tell me that among these 48 where do we have the highest buy price? Which product has the highest buy price? To find the highest buy price, we have to sort it. How we can sort it? I'm going to copy paste the exact same thing here. After where we can ask it to order by order by which column? By price. Order by a certain column. So we filter by order by we are sorting also by order by sort by uh sort means arranging my data set. So in the order pile if I ask it to order by by price here and we execute it right here. See this is the lowest price we can see. So right now we are sort um by default it is will sort or arrange the table in ascending order in ascending order or you can say in increasing order right? If you want to sort it in descending order, if you want to sort it in descending order, then we will simply go ahead and define that get the descending order. So after buy price here, after buy price here, now we want to set that then descending order. So for descending order, we will simply type D here. If we type dc here and we execute it right here, we'll find the highest value. If we don't define anything after order by and we execute it, we have the lower price. So what we are doing, we're applying the filter and we are sorting our output too. Go ahead and find it out. First filter it out. You already have the filtered list. Now what you will do next? We'll move forward and sort your value or sort your result out. see them one by one and this is the less than sign. It's a simple logical operator everyone or you can call it a conditional operator. Go ahead execute them one by one and let me know we still have less than 10 responses. Order by here right what is order by meaning sorting my data set arranging my output. So if I simply go ahead and ask it to sort it uh ask the result grid just like this right it will give me there is no sorting order is your table arranged output arranged in any manner is it alphabetically arranged >> or is the buy price arranged in some manner not arranged so what we do here we will ask it that why don't you order it as per the buy price when we ask it to order by the buy price by default it orders in the ascending order increasing order of the buy Yes. But what if we don't want this? What if we want it descending order, decreasing order? So we go ahead and we write the exact same thing. We ask you to order by by price, but in front of that we write descending D. We define that right now when you're arranging the table by the with the column by price. Make sure you sort it in the descending order. So it will sort this into the descending order. That's order by order by means sorting the A again I have a question for you. Okay. Can you get me the customers customers from country France from country France and uh sort uh the name in ascending order. But can you go ahead and write the query for this please? What table you need? What columns you need? >> First tell me which table we should go for. From which table we can get this uh information. [clears throat] So we should go to customers table. Very good. Which columns do we need in the output? You are telling me from customers is done. Right now which columns do you need? Which columns do we need? Country. We do need country. What else do we need? Who are the customers? Right. We need customer name too. Is this the output we are looking for? Very good. Man, is this the only output we are looking for? No, we need not from all countries only from France. So now once we have selected everything here after from we need to go ahead and apply where clause. Why? Because we are looking for country should be France. So when we when we are looking for that here we will go ahead and double click on country again. And this time we use another operator that is equals to sign. And now we will go ahead and type France. When you're typing France here, since France is a text, it has to be encompassed inside either single quotes or double quotes. Right? So whenever you you're writing a text or a date text or a date, it has to be encompassed inside the double quotes that then only it will able to understand what we are looking for. So when you go ahead and ask country from France here in the output you will see you have only 12 rows here we have what only 12 rows here right now we want to sort it by here. So now the country name is similar. So how do we sort this by here? So we can order by we can order by here with customer name. So we will go ahead and double click customer name right here. Even if I don't write ascending it will be in the ascending order that is A2. You can also go ahead and define asc that is ascending that will also set it in the ascending model. So whatever we we are looking at here are okay all these are your conditional operators right these are all conditional operators. So we are defining conditions individual conditions rather. So these are known as conditional operators or logical operators you can say. What are these? These operators are equals to less than greater than or any combinations of them. Right? This is not equals to. You can also write not equals to like this. So these are conditional operators everyone. And this is how we use them by to define one single condition. Right? I will go ahead and uh write this in the code shell. You can get it from there and let me know when we can move forward because uh next I what I would like to know here is list of countries. What I want I want list of countries customers belong. List of con countries customer belongs to this is what we want to get. Okay. I want only one column. I want only one column that is country. So we want list of country. So let me know when we can move forwarding for responses. Okay. Yeah, they are also called as comparison operators. Okay. How we can get to the uh country column only? How we can get only country column here where we can have all the countries everyone. So this is simple, right? We want to select what we want to select. We want to select country from where? From customers here. Right? If we want from customers here, we execute this here. This is the country list. Is this correct? Do we have do we have here 122 countries? No, there are duplicates. There are repeating items. Duplicates are there. So, how do we get rid of duplicates here? We get the unique list. So, what exactly we're looking for is to get the unique list. to get the how do we get the unique list here. So basically we want to go ahead and remove the duplicates right we want to remove anything that is repeating that is duplicates. So we will go ahead and copy paste the exact same thing but we use now functions right functions and keywords. Now keyword here is distinct. The keyword here is distinct. Distinct means unique right? So if I go ahead and write here distinct it's a keyword right if I use the word distinct here and we execute it right here. Now we see that we have 27 rows returned and there are 27 countries there. This distinct can be written either as a keyword or sometimes you will also find distinct return or used there as a function. So when it is used as a function distinct will be followed by the par opening parenthesis and after the country it will be closed within the parenthesis and closed like that. So sometimes you'll find it like this sometimes like this. Both are absolutely correct. They will get you the same answer. Okay. So this is how we get the distinct here. So go ahead and get this distinct unique list here. If I ask you how many countries are there you have to look at the output right? 27. Uh now what if I don't want to see the list of all 27 countries right? We don't want to see the list of all 27 countries. I simply want to know I simply want to know that how many countries how many number of countries number of countries are there. How many number of countries are there where we are shipping our product. I simply want to know how many number of countries mean total number of countries mean total number of countries to def get the total number or answer for how many the function is count the function is count right. So how do we use this count here? We want to count how many total number of countries are there. So we already have the list of countries. We want to simply count this list of countries here. So here I'm going to select this here. Select and copy and paste this here. And now we will ask it to go ahead and count. We'll ask it to go ahead and count. Count what? Count is a function everyone. So you open the bracket here because count is a function and inside this count here if you will go ahead and type count country directly you'll get the answer here as 122 is 122 is correct answer. No we know the correct answer is 27. So we have to go ahead and say that no don't count column entirely. You go ahead and first you create first you get the distinct country there and then you count. So see distinct is written in blue color right? It is a keyword. It can also be used as a function but count is a function also. See that after count immediately it is followed by the opening parenthesis. Now once we execute this we have count distinct country as 27 right here. So it can be written like this or we can simply go ahead and write it like this also. So we will have the count here, open bracket here and close bracket right here. If you type it like this then then also we get 27 is the answer. However, is this column name looking good in the output? I will give you enough time. Answer me just one thing here. Is this column name looking good to you? No. So we need to rename this column, right? We need to rename this column name here. to rename this column name here in the output we will go ahead and give it an alternate name and we call that name as alias right so alias is nothing but an alternate name we are giving to that particular column how do we rename the column here to rename the column here so this is our select query right I will keep from in the next line I will keep from in the next line here and once we will write the entire function after this say we'll go ahead and type as s here and within the double quotes currently I'm writing what we have here total countries right total uh number of countries here so I'm writing here total number of countries right there so if I go ahead and write like this total number of countries within the single or double quotes here and I execute it right here it will rename it right this is called as however However, there are multiple ways to type the alias here. Right? What is the other ways here? So, let me copy and paste this here. Another way is that I will not go ahead and give the double quotes. So, I can directly go and type the name here. But if I go ahead and directly type the name here, I uh I'm not allowed to give a spaces anymore. I'm not allowed to start it with a number or something. So, I have to go ahead and write total countries or total countries right here or count of countries. So even if I will not give the double quotes uh here we I am good. I can give the name of the column here. It will be renamed but we cannot give a spaces anymore. If it is in double quotes we can give a spaces. We can type it started with number or anything else. Okay. It can be alpha numeric. What is the another way? Another way here is we every time typing as to define that we are giving an alias or alternate name. Even if I don't write it here and I will directly write total countries as it is and uh let me go ahead and change the name here so that you can see the in the output. So if I go ahead and execute this see it is renamed again. So even if I don't type as SQL understands that we are renaming a column. So go ahead count it and rename a column in the output. That's how it is uh very basic. you always go ahead and name it. This is another way to type it. So even if you're not writing as whether you give the double quotes or not, okay, you will still get there. Double quotes are given when when we want to give a spaces in the name. However, in the standard practice, we don't give a spaces in the name even if we are giving the double quotes. Okay, preferred way is this. Preferred way is preferred way is this. There's a reason for that because uh as a beginner okay if you will see something written directly here you might try to find uh this as a column name here. So to avoid the confusion to avoid the confusion okay we will always type as and we will always give the double quotes like this makes us makes it easier for us to recognize the names here. I have a question for you. An exercise for others. I'm waiting. This is just an exercise. Product line uh means uh product category. So product line is one corner. So this here will remain similar, right? This part will is going to remain similar here. So I'm going to copy paste and now we have to work on the products table everyone. So we will go to products table here. Inside this we want to count the distinct product line. We double click on product line here. We will name this as yes you can write in any case there doesn't matter. And uh I'm writing this here as total product lines from where so from product table from products here and here we currently we are using functions like uh count okay you can use sum average standard deviation and a lot more maximum minimum right so we we can go ahead and use these kind of functions in the similar manner So we can use multiple functions like that right. So uh normally functions we can simply use mathematical operators too. We can not only use the logical or comparison operators and functions here we can use mathematical operators too or simple mathematical calculation. So with the help of these here we can actually add the entire calculated column in front of the output. So let's take an example here. So we already have the product table with us right? We know how to extract product ID name and after buy price we have one MSRP column also there that is the standard pricing that we are selling it for. Correct. We can go ahead and get these three columns from there. However, now uh the company has decided that whatever uh is the standard price is there we will increase the standard price by 10% of every product. they will increase the standard price by 10% for every product. So they want to go ahead and add here a new calculated column called as new MSRP in the out and that new MSRP will be simply 10% more than the old MSRP here. Right? So this MSRP now will become old MSRP. This MSRP will become old MSRP. So if we want to write the formula for new MSRP okay we can write it like this that MSRP old MSRP plus 10% of old MSRP this MSRP formula um sorry the column there can we write the equation like this MSRP plus 10% of MSRP so that we get the increased price for new one can we reduce this since uh we know with the help of data type that we have seen at the start of the session today that it doesn't recognize that percentage sign right so instead of typing the percentage sign and uh of here can we reduce this to.1 and multiplication we can do that here right because we want the uh want the calculation to be done so now this looks like that get the MSRP uh value from MSRP column in the individual row multiply that with.1 and add it to the older MSRP. Very straightforward. But if we go ahead and reduce this here, we can get the common value out to reduce it to a smaller version. So once we get the MSRP out, we are multiplying it with 1 + 0.1. Meaning we can simply write the new MSRP like this. MSRP multiplied by 1.1. Correct? So we have this calculated column right there. So how do we write this calculated column here? So we go ahead and say select right from where are we selecting? I'm writing from first here. Okay, I'm getting this out first from the products table. Okay, I'm getting this out first. Then I'm typing select so that I don't forget which table we are working on. So in this select here, first we need product ID. So this is product code right there. And I'm going to rename this here as product ID. Easy right after that what we are looking for comma we want product name also there so this is name let's take this here as name only since it is product ID it has to be the product name too after product name what we want MSRP so this is MSRP but this is old MSRP so let's name this here as old MSRP now we need new MSRP but that New MSRP is not a column that exist. It's a calculation. So we write the calculation here. How do we write the calculation? We get the MSRP here and we ask it to multiply it with 1.1. That's what we have seen there. Now this entire calculation we encompass it inside the parenthesis here and we call it that name as new MSRP. Name as new MSRP. Right? I will go ahead and I will go ahead and execute this first. See here we have got product ID, name, old and right. I will also go ahead and beautify this here. I'll go ahead and beautify this. Right? This looks much better right to read. Go ahead and get this please. Of course you can choose any name that you want. You don't have to stick to these names only. You can give any alternate name that you want. Also observe I'm not using double quotes. I am writing single quotes here. Okay. Uh remember that whenever see how many rows are we getting in the output everyone observe the rows you're getting 110 rows. Right? So we are extracting each and every number here. Each and every row here and this is a row wise calculation. We are calculating it for each and every value in each and every row. That's why it is a calculated column. And do you see any difference between the O uh difference in the format of old and new MSRP? Yes, we want two decimal here also. How do we get uh only two decimals? We truncate the other decimal points here. We round it off. So the function here is round. Function here is round. And we will say then we go ahead. We type a comma there and type two. Now once we execute this one here, we get two decimal points, right? Yes, our functions are exactly similar to Excel. You'll not find it is very far from there. You need to get the average price for vintage cars only. Where you will find the vintage cars? You'll find the vintage cars in the product line column of product table. Product line column of products table. The uh formula is AVG simple and price means average MSRP. Get the average MSRP. Okay. But only for the vintage cars. Vintage cars is the name of a product which is present under the product line column. So if I show you the product table here, see under the product line you will find vintage car. See okay and it is not case sensitive. You can write it anyways just the spelling should be similar. Now how do you go ahead and solve it? That's the question everyone right. So first thing you always figure out from first thing that we always figure out is from right. So what we should type in front of from here product we're aware about that. After from here what is the second thing you will look for? What do we want in the output? What do we want in the output? We want average. Average of what? Average of MSRP. That is also fine. Other than that, do we have any other requirement? We have average MSRP from the products table. What is the third requirement here? Do we have any other requirement? Right? We want to filter only for vintage cars. How do we give the filter? Where clause but on which now where clause here on which column we want to put the filter? What is the name of the column? Where we want to put the filter? We have to give the column name, right? So the column name is product line. Why? Because product line is the one that holds vintage cars. This is how you when you will break it down it will be easier for you to write. So here if we go ahead and type select now. So here we move here. Select right in select here we want to calculate the average. So let's calculate the average. Average of what? MSRP from where? From the products table right here. This is products table. We want to apply the filter. We will apply where there on the product line. Product line is equals to what? Vintage cars. So we can go ahead and simply type vintage cars here. First check the spelling in real time and then type the vintage cars here. Yes, that's it. However, this is just average MSRP. This is just average MSRP written here. You can type it properly the name there. And see there are too many decimal points. So let's round it off. So we will round it off the average. Let's round it off to two decimal points there and give a proper name here. So this is average MSRP. This is average MSRP. So we have given the proper name also here and we have round it off. Once we round it off give a proper name. See how does it look like better. But this output doesn't tell us it is for vintage cars. So what we can do here is we can type a comma here and we can ask it to get the name of the product line too. And when we execute this here we can see one single name is written because filter is there. Yes. because we need only one single output here. It is a one single number output. We have just one single number here. While typing round to clean the data, I had overritten the average function. Yes, [laughter] that happens. Okay. If you are selecting it from the drop- down, all right, then you will get uh you will replace the upcoming or already written values there. So you what you're doing is you're all rounding off MSRP, right? So you are going to have error and very different output. You'll have multiple vendage cars and each and every row will be out because right now we are asking you to calculate an average. What is an average? Average is an aggregate calculation there. So if the average will not be there, it will simply go ahead and extract each and every row. No, it is not aggregating any. Observe that in all the three conditions and every condition that we have seen yesterday we are only going ahead and uh filtering it by one condition. We are only going ahead and filtering it by one condition. What if if this is the orders table right in the orders table here if I ask you to go ahead and only get the orders okay which are on hold which are on hold. What you will do then? So we are now going to have two conditions. We are saying that I want the orders which have been placed after PMA 20 five year and they should have and the status should be shipped. Let's say this shift the status here and the status should be shipped. So now what we have here we have got two conditions here. So how do we ask SQL to go ahead and uh give us two conditions filter the data by two conditions. This is where logical operators comes in that is and or and between. This is where your logical operators will come in that is and or and between. What and will do here? These will help us to look for two or more conditions. And have you have we heard about and or in between? I'm sure we have heard about it and or in between. Not in SQL but somewhere else. If you are from engineering or just uh you have learned Excel anytime we have heard about these conditions and or in between and in general cases also we do use that in general like I want apple and mangoes from the market get apple or mango from the market there's a difference so in general also we use that logical gates and excel and at many places in general life also we use it and here first so what will and to and means I need both the things here I I need apples also, I need mangoes also. So in one single row here it has to check that in this per one single data row the end all the conditions should be matching meaning the order date should be less than 2005 and this status should be shipped to both the conditions should be matching in each and every row. So let's go ahead and see how we are going to type it here. So we will uh get the same thing here which orders have been placed. Let's get it right here. Orders have been placed after 1st March and the status is shipped. How do we write it? So we know that this is select and let's figure out from first. So from we have orders, right? What we need here in the orders table. So let's get the order number comma we have order date that's what we're looking for and the status is shift here we are till here it is fine now we go ahead and we type where now inside where first condition is that our order date should be after 1st March 2005 now we know how to type this this needs no explanation here is one more option now one more here And so we type and here and we ask it that now your status should be equals to shipped. This ship that we are typing it's purely uh uh not case sensitive. You can type it as you want it. The spelling should be correct. Now if I go ahead and execute it right now see here we get both the conditions are matching. Both the conditions are matching. Yes. Okay. So this is and what ANT is doing here and is helping us to go ahead and get the go ahead check two conditions in one single order. So if I go ahead and verify this this is how it looks. You are getting me the orders which are placed after first March and the status is shipped. But now I want the orders that has been placed after 1st March and check for check for whether status should be equals to ship and resolved. So whether the state uh so status should be shipped and resolved. So there if you will see here there are uh status like resolved here. Okay. See, see there is a status like result. So we want the both the type of rows here is shift and resolve both. How do we get that? Why are we using or? Let's understand. Reason is in one single row it cannot be both. So we have to go ahead and write or here so that it goes to every row and check either it is shipped or resolved. So I will copy and paste exactly this thing here. And what we type up we type or. So here status equals to status equals to result. Uh this you can write it by yourself. Is this order date greater than 1st March 2005? Is it holding on with the status shipped or resolved? Are all the order dates after 1 March 2005? Are we having all the order dates after 1st March 2005 everyone? Yes or no? Does this look like 2005 till 2003 is in the first row? You don't have anything after 2005, March 2005 specifically. You have January 2005, November 2004, October 2003. This order date is not folding up anymore. Yes. So when we are writing the or condition here, right? The and condition here this order date is getting uh thrown out of the window. Why? Because it is focus right now with the and or what is happening or is getting the privilege. So first it is resolving the or and then it is resolving the and since and cannot be resolved anymore. It is uh not holding on to this filtering because what we are writing here is everything is in succession. We are saying check for order date and shift. After that check for or. So what it has done here is it has taken all these two as uh here and then it is taking checking or status equals to result. So this is a problem. So what we need is that order date should be doesn't matter if it is shipped or resolved right it has to be after March 2005. So we have to simply go ahead and encompass this inside the parenthesis. Once we do that now it understand that oh okay you want this to be held true always and I have to check whether this or this. So this is what we need. This is how we write. So once you keep it inside the parenthesis here it will go ahead and check between these two. It will apply the or only between these two. it is not going to apply the or with the order rate. So when you want something to be held true, keep it separate. When you want something to be held true separately, just keep it inside the parenthesis. Now two conditions are matching perfect. Okay? Just simply go ahead and give the give the parenthesis around it and it will be resolved. This is the question for you. We now I'm writing here is uh please look on the screen here. Okay. Find a product whose MSRP is greater than 100 but less than 180. How many conditions are here? Two conditions. So if I have and both the conditions should match. It has to be greater than also and below 180 also. Both the conditions should match here. So we can use and here. So first let's figure out from here. So from the product. So this is the from products. And uh we are looking now in the product here. I don't need all the columns. So we get the loop select columns here. Let's get the product name product code and here is the MSRP. Right? This is what we are looking for. Now we go ahead and type where in the where this is MSRP right here. MSRP is greater than 100. So we can go ahead and type greater than 100. And again MSRP because there are two conditions. So MSRP less than 180 less than 180. This is going to give us see here the MSRP between 100 and 180. So what we are looking for a numerical range. So whenever we have a numerical range we can easily apply between. We can apply between here. Instead of and here we could have write in between. How do we type between? So this much will remain similar. This much will remain similar. After MSRP, we'll simply type here between and we'll say MSRP should be between 100 and 180. So between cannot be used without and the type 100 and 180. Yes. Now in both the cases here the answer is going to be same. Now answer me here. Are we including 180 when we are filtering with the and function here? Are we including 180 when we are typing and here right now we are not including upper and lower limit here if I want to do that we can go ahead and write equals to here if I want to include it I have to type equals to here then I'm including upper and lower limit meaning I have the choice meaning I have the choice here it works as Excel not as programming language as you have seen before it work as Excel it has to go back go to each column every time to filter something out. So we have to type MSRP equals to MSRP equals to when we talk with between everyone in between upper and lower limit are automatically included. Upper and lower limit in the range are included automatically. So when you're using between and here it will include upper and lower limit both. So this is your between and it will be included automatically. We don't have exact a 100 or 180 here. So we cannot see the difference but yes it works like that. So this is and and between here this is the difference between and and solve this verify the u spellings first. So you can click here and look at the spellings here first. say this correct the logic is absolutely correct. So let's see list of customers living in USA customer or PR. So we now know we are talking about from customers here right. So of course we don't want to select everything from customers but we uh are looking forward from customers right here. What we want to select from customers? Let's select few columns here. So customers uh this is customer name here and let's get the country that's what we are looking for and from customers. So we are good with that but we don't want all the countries of course we don't want all the countries we want select few. So we'll go ahead and use all function here because in every row we'll check for individual country not all. So now here we have to go ahead and say that where where country is equals to let's say USA here or or country equals to again we have to go ahead and type it again. Why? Because this SQL here will treat this country as three separate columns. It is not going to treat this as one single column with multiple filters. So every time it will go ahead and check it uh check it as it's going ahead and checking for another column right there. So when you're writing this here, it is not case sensitive but you have to make sure that is spelling is absolutely correct. So we have to go ahead and type or or and of course you get the correct answer here and that is going to be see USA, France and Australia right that is 53 rows. Okay, that is 53 rows here. So if your spelling is correct you'll get 53 as the answer. Most of the time you'll be making mistake at the uh spelling of Australia. But just imagine what if you have 10 conditions to check for. What if if you have 10 conditions to check for that one single column then it will it is going to be too hard to type and we will make mistakes in typing the spellings right there because you have to check and write and copy and paste. So here in this in this line here we go ahead and we choose in operator here and we go ahead and choose an operator here. What is in operator here? In operator will help us to give the entire list at one place. So in operator acts like or right. So if I go ahead and type in operator here inside this we can go ahead and write the entire list. And what it does is it goes to the country column and it will check from the list if any one of that is present. So it automatically work as odd. But it is convenient to write. How do we type n operator here? So we will first go ahead and give the column here. In front of column we type n and inside in here we will go ahead and type here value one comma value two and value three. So if it is text we will type inside uh the quotes. If it is not a text we will simply leave it as it is. So this is how in operator works. An operator helps us uh uh helps us help us to type the filter here properly. So this will remain exactly similar here and after country we'll simply type in and inside the India we can give the entire list. So this is our USA I'm going to copy and paste here comma this is France here and now near Australia. So we don't have to type or again we don't have to type country equals to again we'll type that only once and we don't have to type or anymore. N automatically takes part of that and we still have the 53 roots right here. So this n is actually working as multiple or like n operator like an operator. We also have any operator or operator any all in exist. We do have that too. For now we'll focus on n. So let's look at the employees table right here. In the employees table what we have? We have got employee number, email, office code, and the job title. In the job title column here, do we have managers? Only typing manager will not work. Why? Because there is nothing that is exactly equals to manager here. So we are currently not trying to find out uh the exact phrase inside a data point. Correct? What we are trying to find out is a part of the a part of the string. We are trying to filter out the rows from the part of the string. So we want wherever manager is written in the entire phrase in this entire string wherever manager is written based on that you filter out that row correct. So in this case here no equals to comparison operators will work. This is where the comparison this is where the wild card comes in. This is where we have wild cards. Wild cards. What are wild cards here? Wild cards are activated with like operator. So whenever we want to use the wild cards here and we want to get the small part of the string out, we first activate the like operator. So in the like operator here there are two things we can work with. One is the percentage sign, one is the percentage sign and another is the underscore sign. What is the percent percentage sign here is for variable here that you will have variable uh what do you should say here you should have variable variable ledgers letters and symbols right and this letter here simply means fixed number fixed number of spaces fixed number of letters or blocks what does that even mean let's understand it so if I go ahead And I will type here percentage. After percentage for example, I will go ahead and type manager. Type manager. What does that mean? It means here that if I am typing if I'm typing percentage percentage before manager. So we are uh communicating to SQL that see the phrase should the entire phrase or entire string should end by manager and before manager before manager any number of variable can come there any number of variable can be written there. So it can be a letter a symbol or a space anything can be here but it should be before manager. If we go ahead and type that okay this is manager and if I type percentage afterward so we are communicating to SQL that this particular data point should start with M andJR and end with anything end with anything there can be spaces and any number of variables anything will be there. If we go ahead and type percentage first and then manager and then percentage again, we are simply saying that in the middle somewhere in the middle of the uh of the entire phrase this particular words should be there. It doesn't matter what it starts with what it ends with. Okay, this is percentage. This is percentage. So if you want to go ahead and extract manager from here what we should be using then should we be writing percentage and manager or we should write manager and percentage or should we write percentage manager percentage they are 2 to1 right now this is the correct answer. Why is this the correct answer? Look at this manager is somewhere in the middle. At the end also we have some variables. Yes, Samr. So this is our operator. Let's go ahead and get the employees table right here. Now if we select everything here from the employees table, let's go ahead and get employee number. and their job title here from employees and now we want to go ahead and give a filter here. So inside the web we'll say that see I want to put a filter on job title job title should be like similar to percentage manager percentage the spelling of manager should match there manager percentage it should be encompassed inside the single quotes once it is like that we got a manager is right here I would like to remind you you are not supposed to type these these things here like you're not supposed to type the keywords or the name of the columns or name of the tables here. I would like to remind you you simply need to double click. Okay, please double click. Do not make a spelling mistakes. Double click. Please apply three rows. There are three records. I would like to see now which employee is sales manager. What will I do if I will I'm only seeing these three here. I want to know who are these people. That's fine. Otherwise, how does it make sense just to see three titles here? I need these people detail. I want all the managers. Maybe I want all the managers to invite them in a meeting that this meeting is only for the managers. We should be actually getting first name and last name also and their email id also because I need to share this uh list with someone, right? They will call these people and tell them to report to somewhere. Okay, this is what the percentage will do. Percentage wherever percentage is written, it will look for multiple characters. What it will look it will look for multiple characters or variable characters any number of characters should be there. So percentage manager meaning in front there can be any number of characters. If we go ahead and type manager percentage meanings it should start at m andgr and end with any number of characters. We don't care. And when we are writing percentage first and then percentage here meaning it start and end we can have any number of multiple variable characters that's what percentage is. Now next here is underscore sign everyone next here is the underscore sign right there it is fixed number of letters or you can say fixed number of characters. What is meant by fixed number of characters here? It means that if I will go ahead and type underscore once. If I go ahead and type underscore one here. So basically we're asking it to get to the one letter. So if I go ahead and write that the there should be two letters at the then there should be a letter O. Then there will there should be one more letter here. So we are looking for a letter maybe it is from. So it can have only one character. So this is the underscore. This is the underscore. One letter here. This O is something that a pattern should we are looking for a pattern. So all the in the entire column all the um data points following this pattern here with having third character as O will be extracted out or filtered out. So this is what underscore means. It means one means one character. One means one character. One means one character like that. Yes, you can use combination also, right? But most of the time this is something that we use. This is something that we use, right? So, let's see an example here. Let's see an example here. For some reason, let's say, for some reason, let's say we want to go ahead and find a customer whose first name is starts with letter M, whose first name is starts with letter M and should have exactly three characters and should have exactly three characters. So we want a customer whose first name starts with letter M and not only starts with letter M, it should have exactly three characters. So how we will go ahead and write this here. To write this here, we have to go ahead and write that start with M. And it should have exactly three characters here. So I'll go ahead and type two underscore signs here. I will go ahead and type two underscore signs here one after the another. How is it going to look like when you type it? It looks like a single line, but there are two underscore signs right here. So this is M and exactly have three characters in total. So let's see uh if we have any names like this. So here I'm select this is our from customers. Let's select customer first name right here. Okay. And let's go ahead and see if there are any names like this. There again contact first person first name is like M and two underscore signs here we have just one person if I go ahead I put one more underscore sign here it will be total four characters you will have four people so if you're working for four characters you will see more answers here so this is how it works this how it works in general you will not find many use cases other than pattern recognition if you're looking for a strict pattern recognition then it will be useful otherwise most of the time manager will be hand that percentage thing will be handled. Why? Because if we simply want the first letter start with M we could have written it like M and percentage and that would suffice. So after M any number of words can be there any number of characters can be there. So if I go ahead and write here M with percentage M with percentage here. Now the uh letters afterwards uh doesn't matter. See so that's the difference between putting the underscore signs and the percentage. Now go ahead and get it. This is another way to put the filter in wild cards. Everyone we already done this once here. We wanted uh from the orders table. We started with that. So we wanted to get uh order number, order date and a status where order date is 2004. And then we are getting and function here. So here right now we want order date from January 2004. So I'm changing this to January 2004. So here it's fine. But afterwards here it is asking that that r. So we will type and here but after n these are either shipped in process or on hold. So now here we go ahead and we use n operator because we have to use or here instead of writing or multiple times here after add we will say that go and check the status and in here we write we go ahead and we type m sh process or code and we type this here. Make sure we are in the single quotes right here. Now we don't need to type or anymore. We can simply type our entire list. Remember that there are no extra spaces. Yes, we can write like that too, Michelle. So let's check. Do we have any answers here? Oh yes, we do. We have got shift here. End process or end process and not everything is getting checked out. This is the V one. This is the post consultant. Now let's look for the second one. Second is retrieve customers whose name is start with A or B and they are from Germany. Do we any people like that? Go and check it out. I have no idea whether we have any rows like this or not. We have two people like that. Wonderful. Here is the question. Uh okay. retrieve the customers whose names start with A or B and they're from Germany. So what see uh you all are applying the filter on different columns, right? Someone is uh few of you are applying this particular filter on customer name. Few of you have chosen customer contact person name. In both the cases you'll get the answers. You might get one row or the two rows. So for example if I take this uh here the first one that I bought if I take this solution right here. So this is customer name and country from the customers after country and Germany we are typing and now we have to and can pass it inside one another like a starting from a or starting with b. So right now we're using customer name here a direct customer name the first one and see now we have two people here if instead of customer name I will go ahead and use customer first name that is contact first name then the answer will differ. So for this I will take the second solution I have got and see here we have contact first name has been taken here. See? So the logic is same. So both the answers are correct. Okay. Let's go ahead and look into multiple different types of functions here. Basic type of functions that we have here is text function, date functions, conditional statements like uh if else and case and when. And here we will also look for the null values. First thing that we are going to learn about is string functions. They're also called as text functions or simple characters. Right? What types of string functions that we've seen so far? If you know Excel, you have seen string functions. If you have known Tableau or some uh similar languages out there, you have seen text functions there. For example, there is a text function called as upper. What does it do? It goes ahead and converts a smaller case letter into uppercase letter. Similarly, we have a uh we have all is lower. What does it do? It gets the upperase letter, converts this into a lower case letter. That's upper and lower. Another here an example here is length function. What is length will do? Length will simply go ahead and give the length of the string. Whatever is written inside and it will give us length of the string. We have another function called as trim. What trim will do? Trim will get rid of extra spaces. it will leave one space. It will get rid of any extra spaces if they are written before or after the string or the characters. So if I have an address written somewhere and someone has given too many spaces between the addresses line one and line two then it will get rid of that. So on the similar basis we have another options like service string we have got reverse. Reverse will reverse the uh data point. Service string will simply get few words out of the entire string that is service string there. Okay, these are few very common than these uh uh text functions right there. Now, do you think can you think of any other function which is very common in text functions? We do have left and right function. Left will go ahead and get few characters from left. Right will go ahead and get few characters from right. Where is the data type in? Yes, we do have proper in Excel. The most common one that you will ever see is concatenate or concat. most common that you will see there. What concat does? Concat will go ahead and it will join. Yes, it joins the strings. Very good. It can join two or more strings together. It combines them. So let's see an example. For example, we will go to the customers table again. So I'm getting from customers again. So this is here from customers. Right again. Now in the customers here, see we have contact first name and last name. We want to write it together. So here I will go ahead with select again in select what we want to do first I will get the customer number of course comma get customer name that is the name of the vendor the company and inside that company this is our spark name here. So now we go ahead and we type concat. So here is concat everyone you can see the function right there. This is concat function. Inside this concat function I will simply ask it that now you concat first name perma last name. I'm going to name this here as full name. So we are applying the concat right here. When I'm applying the concat function here look at the column full name and tell me is this uh output correct? Can you simply go ahead and give this uh share this output outside? Is this possible? There is a space. See here, Karen has the space. Janine here, this has the space. Diego has the space. Jonas here has the space. Not in all. Why is it like that? Why do some names have a space, some name doesn't? Because if I add the space now, somehow they are going to have extra spaces. Correct. So few if I add the space now few will have extra spaces. So tell me why do we have extra spaces here? First figure out why. Why does this appears like this? Why is it happening? Because when someone was updating the data right they have written the first name or the last name and by mistake they have added the extra spaces either before that or after that. So either first name or last name these have extra spaces before or after them. Because of that because of that here we are having extra spaces. So we need to go ahead and trim the extra spaces first and then add the uh required space. So first thing we do is we use trim function here. Absolutely correct. So I'm going to go ahead and type trim. Now what is the problem with trim function here in uh SQL? we have to go ahead and apply trim function to individual column name here. We cannot simply go ahead and encompass trim uh for the entire uh concat. We have to go ahead and trim the individual names here. So once I trim it here you will find that now see we do not have any spaces. See here I have applied trim function here and you can see there are no spaces. Now we can go ahead and add a space after this comma. Here I will type one more comma. Now we are having one spa character space in between. So here we go. Single quotes a space single quotes right here. Single quotes. Type the space bar. Click on the space bar and single quotes again and press control enter. And see we have got full name right here with proper one space in between. So we already have this right. So just copy and paste this here and also get the full address column here because we have address here we have got address line 1 2 3 4 till country right so we want to get all of this at one single place so let's move forward what we wanted to get here we wanted to go ahead and get the full address so when we go ahead and try to get the full address here I'm sure you all have written that there right so let's go ahead and type it out for others too So after concat and getting the full name here now we type concat again but in this concat here we want to get the address uh here this is address line one this is address line two city comma state postal code and then we have country now this here is address so I'm going to go ahead and write this as address one for now and let's look at the output Okay, when you look at the output here, what does it show us here? That these are null values, meaning these uh we don't have address for these customers. That's what it tells us here, right? So, let's check it out. Don't we have the address for Karen and Jen here? Let's figure it out. So, if I go to customers right now, everyone, and I look at these people here. Okay. Do we not have the address for the them here? Do we have the address or not? We have the address. Correct. However, when we are trying to concat it, when we are trying to concat it here, are we getting the address? No, we are getting null values there. Why not? Yes, Amanda, absolutely right. Why is it happening like that? Because what is happening with concat here is it is reading it here. It is reading it here. However, as soon as it reaches the null value in the second column, it stops right here and give us the null as the answer. So whenever uh everything is present, whenever in every column there is a text present, it is giving us the answer. But as soon as it reaches or looks at any null value, it will not move forward anymore. It stops here and give us null as the answer. It means that concat cannot deal with null values. Concat fails to deal with the null values. So this uh concat will not be able to work at all for our because it should simply ignore the null value and move on. Right? It is not able to do that. It it fails at that point. So what we will do here now we'll go ahead here and first thing that we should know is how to find null values. How to find null values? To find the null values here we are going to use very simple thing here. will ask SQL is there any null values? We can simply ask. So we can go ahead and ask it that go ahead and select and count. What to count here? Count every column. Count every column whatever we list here from the customer's table. from the customer's table where where you have to count wherever address line one is null or or address line two is null or address uh city is none and likewise and likewise. So it'll go ahead and ask it to go and check if these columns are null. So when we ask it to do that, it will tell us that yes, there are 113 null values out there. It simply tell us that yes, there are 113 values out there which are null. So go ahead and see this here. Now we have to go ahead and find out that how do we deal with null values here. So we have null values fine. I know we have null values. So how do we deal with it? So there is a very famous function called as poies. Poles is a function that is used a lot to deal with the null values. Why? Because it can successfully ignore the null value. It can also replace the null value if required with any text number or symbol. So kies can be used as a nested function with another function very easily. So go ahead and first get the concat properly and see how to go ahead and uh count the null values and then come back here. You'll go move forward with quies right there. Okay, first check this out. So we are using the function quies now. So how does the queries looks like? It the syntax is like this. So curies can be applied here uh to any column name or a string right and afterwards here we can go ahead and ask it to uh either replace it with blank or replace it with any text number or symbol. So co whenever it will find uh the null value it will simply go ahead what coies will do coies will go ahead and check for non-null value. If the value is not null it will give that as the output. If value is null then it will give you whatever you ask it to give as the replacement. So if you want it black it will give you blank. Two single quotes without any space that is blank. Okay. So let's go ahead and find it out how does that work. So I'm going to copy and paste it right here. I'm going to copy and paste it right here and comma enter. What we want here is we want to go ahead and work with concat but concat cannot deal with null values. So we will ask here we will ask co here to go ahead and check is address line one is a non-null value. If it is a non-null value we will type a comma here and we'll say if you find a null value go ahead and leave it blank. If there's a null value, leave it. Leave that blank. Now we type comma again. And here we will go ahead and type co again for address line two. For address line two. In address line two. If this is blank here, go ahead and type na. I'm going to type a bigger na here so that I can show you that it replaces the value. Okay. I'll type comma again. Now we go ahead and check for check for the null value again that under queries if is if city is null go ahead and give this as the replacement. Okay, I'm not typing anything else right now because I want to show you how co works and I'm going to type this as address two. Let us see how does it work. Let's see here. Observe everyone. Observe everyone here. Do you see NA is here? Meaning what is empty? What is null? Address line two. So wherever you see NA here, address line two is null. If I go here, say here, everywhere NA is at address line one. We have the city name everywhere. We do not have any null city name till now. Okay. So this is how co works. So within co here right now it is showing na. Right? Right now it is showing na everywhere. If I ask it here but simply go ahead and type a comma. Simply go ahead and type a comma with some spaces before and after. Then see how easy it looks. See here how clean the output looks. Yes. So this is how police works and yes it is a lot more work to type concat with quies. It does take some time. So go ahead and type concat with quies everyone. And I will reformat this to show you it actually looks very good after reformatting and it and it is easy to follow also. So if I repormat this here, I will go ahead and repat it with this here. Look at this how easy it looks to follow. Now, now here when we now we have seen everyone that uh we can write concat and co but it is not looking that good also because we have to go ahead and trim also. So when we are writing here concat and pois here we have to go ahead and trim them individually too. If we are not trimming them up the problem is same. See we have too many extra spaces everywhere. So we have to go ahead and apply trim here too. So yes it does get tedious to type. Okay it does get tedious to type. So what is the another way? So now what is the another way here which can help us to reduce the effort. write down here. So another way here is to use another function called as concat WS. Concat WS WS means concat with a separator. Concat with a separator. Now why is it called as concat with separator? So let's see something here. This is here is uh uh city name, right? And this part here is the address line one. So somewhere we have address line one two and city name two. So now what is happening here is we are not giving any separator here. If we want we can. So after every comma here we have the choice to give any separator that we want. We have a choice here. We can go ahead and give any separator that we want. So if I go ahead and type separators after address line one and after address line two right here. This seeter can be can appear like this here. Okay. So these are the separators that are appearing here. Meaning if we're using concat and poles together, we have the option to give as many separators we want and we can choose different types of separator here. However, when we use concat WS, concat WS is going to give us exact similar output. It is going to ignore the null values. It is going to concat whatever we ask it to do. However, it will only give us option to write one separator and no more. So, concat with coies can replace null with a message or blank, right? With multiple type of symbols and delimters. However, concat ws is going to be just opposite of that and it will ignore n but it will give us only one type of separator and it uh will not give us an option to replace null value with any values or message. So we cannot replace null with concats. So this these are few differences here. The best part here is our work does get easier. Okay, how it will get easier here. So this is how concat ws works. So this is concat ws here. Okay. In conWS first here we give separator any separator that we can define. Separator defining here means we can go ahead and type a comma maybe a semicolon in here or maybe just a forward. Okay, whatever we want or just the hyphens like this here. If I go ahead and type a comma now, now you go ahead and you maybe want to type trim, you get column number one here. Then you comma again. Then you type trim here, column number two here. and so on. But the separator that we give here, it is going to be only one. Separator can be any D limit, any kind of symbol right there. Okay. So let's go ahead and see how we are going to write it there. So here we go. I'm going to copy paste it here. I'm going to copy paste the same thing here. And we add one more column everyone. We add one more column right here. Now in this column here after address two, comma enter. And now here we are getting concave. This is concat with separator. So inside concat with separator first you have to define the separator. So let's give a separator with two forward slashes so that I can show you where separator is coming up. And after this here we simply need our address line 1 2 and three. So we are looking for address line one address line two comma we have got city, state, postal code and country. If you want to type trim you can. Currently I'm not going to do that. So here we have got address three right here. It looks easy to type and let's look at the output then let's look at the output everyone see here this is address two and this is address three it was easy we able to get the proper output and if I want to change the separator here I will go ahead and type a simple uh comma right now and it looks much better see Okay. But in real time what happens here is in real time everyone we use concat and co together but it because it gives us flexibility to type whatever we want wherever we want. That flexibility we get in cos because every time we are not looking for one single separator. Neither we are uh and some and sometimes we want to actually replace the null values with something uh some message out there or maybe we want to give a default value if something is not available. In these type of realtime cases, we will use concat and coies more as compared to concatus. And coies can be used with any other function whenever you want to deal with the null value. Yes, it is much easier to type concat. Absolutely correct. I will also show you the reformat version because reformat version is easy to follow. Do you see this minus sign here? This minus sign is you can actually go ahead and collapse them. You can simply go ahead and collapse them. So you will be only focusing on what you want to work with. It is still there but they are collapsed. So you can expand it, collapse it. So that's the reformatable. Uh when it be reformat it from here this brush remember. So in when you will reformat it uh observe that this is how the reformatted uh query looks like right. Select customer number concat concat concat. Here all the four formulas are here right. So do you see this minus sign here? Do you see this minus sign here? This is collapse. So see here we can collapse the lines right there. saving on the uh saving on the screen part. So we we can simply collapse it if you want to focus on one single formula. All right. Yes, it is easier to follow but not easy to write. See here I am writing concat here and it is ending up with the name. I'm typing concat only once. It is ending up with the name. In your case it is not like that. I don't know what you're typing and from where. Can you copy paste from code share and compare first? So copy paste the code share and compare what you are typing and what is written here. You will be able to understand what's going wrong. Okay. So here next function here is substring function. What is substring function here? Uh this is how the uh this is how the substring syntax looks like. As the name suggest what does it do? It will go ahead and create a subset from the existing string. It creates a subset from the existing string. Meaning it uh see here we will pass the column to it. We'll ask it the starting position and from the start position how many uh letters we want out or how many characters we want out. Yes, part of the string is retrieved. So if I simplify it, we simply extract a few letters or characters out of the entire string. Okay. So for example, I'm going to type uh just like this first and then I will show you the example. So for example, here if I am writing here product, right? So what is meant by start position? A start position means this P here is one. This R here is two. This is three. This is four. 5 6 7. Okay. So if in the sub string here if if I will go ahead and I will get ask it to go ahead and read the letter product read the word product the start position is start position is three and the length here is four. So what it is going to type what is the start position here three and how what is the length we need here? four 1 2 3 4 So it will simply extract that position from that position till whatever length is there 1 2 3 4. So that's how sub works. So let's go ahead and see a simple example right now. So we will work on the products table. We have got product code everyone here. So let's work with that. So here is our product table right here. So I'm typing from first from the products and here we have select right down in the select here I'm simply taking the product code for now and after product code here let's type here sub string everyone this is sub str sub string everything is same they work exactly same absolutely no difference in this substring here I will ask you to read the product code and let me show you how the product code looks like okay this is how the product code looks like everyone This is how the product code looks like. Okay. So this has here is 10 1678 1949 263 and done. So what we will we are saying to the here is that go and read the product code and start with the first position and get me the four characters out of that. Let's start with the first position and get the four characters out of that and see it is including the first position including that it is getting 1 2 3 4 it's a simple sub string here and whenever you are learning any new function everyone you can always ask for help so you can go ahead and ask here help with what help with sub string when you ask for help here. Okay, it is actually going to give you the entire description right here with the exam. Okay, and it will tell you that substr is a synonym for substring. Go ahead and get this please. Sample sub string. You will see an application of that. We'll see the application on that. Yes, it is like of Excel. In Excel also you'll find substring. There is a substring like formula in Excel too. Let's look at the comprehensive example now. So let's uh go ahead and generate a custom product everyone. Okay. Now to generate a custom product code here we want to combine few things together. First we'll go ahead and get the first three letters of the product line. So this is the products table everyone. So let's look at the products table right there. In this product table here, first we want to go ahead and get the first three letters of the product line that we will combine with that we are going to combine with a formatted by price with zero decimal. A formatted by price with zero decimal. So we do have a by price at the end, right? We want to go ahead and format it by that formatted by price without the decimal part. We will want zero decimal values there. Then we will combine it with a reverse product code. Then we combine it with the unique reverse product. So this product code that we have with we will reverse it. Absolutely reverse the entire string. And we only want to view five first five rows. We just want to sample the five rows. We don't want to get the entire 110 rows. Okay. So let's go ahead and break it down one by one. So first let me write here select everything from customers. So let from customers is fine. From product is fine here. Now in select here we want custom product code. So let's uh begin here with product name because we are creating the code for these product names right there. Now we want to combine them together. If we are about to combine them together, we have to go ahead and type concat everyone. Okay. Now after concat here, what is the first thing we are looking for? First three letters of product line. First three letters of product line. To extract the first three letters of the product line here, what will be the function? Left. So we will apply the left function here. We apply the left function here and we ask it to read the product line and get three words from there. Get three words from there and see here we are getting the three words out once we apply left. That is first part done. Now let's go ahead and get the second part. We need now a formatted buy price without decimal number. So if we ask for the buy price directly here, if we ask for the buy price directly here, it is going to look like this. We don't want decimal point. So what we will do here, we will format this. To format this by price here, the function is also format. Function is also format. This format is equivalent to the text function in uh Excel. It is equivalent to uh the format function in PowerBI. Meaning this format function will convert any column or any passed field inside it and format that into whatever we want and most of the times whatever is the whatever the answer is right that answer will be converted into a text. So this format function you can pass it any number and after comma you can go ahead and type whatever uh text whatever format you want it will convert it into that but the data type will be also converted as a text that is format function right there and see now we do not have any decimals we can also go ahead and give a separator right here you can also go ahead give a separator right here so now we will see it like this so this is let function This is format function right here. Now afterwards here I will type comma and we go for the third option here. What is the third thing that here is we want to go ahead and reverse it. We want to go ahead and reverse it. So we'll simply go ahead and say that yes go ahead and use reverse function right there. Inside reverse here we want to reverse the product code entirely. And remember to give the separator here. So we are giving the separator we reversing the product code right there. And see we have reversed it. Absolute reversing. Okay. Once you have reversed it here, what is the next we are looking for? So this becomes our custom ID. This become our custom ID. So I'm writing this as custom custom code. Okay. This is our custom code right there. This is the customized code. But do we want to re uh do we want to see uh here uh it like this? Everything should be in caps, right? It's a code. So let's go ahead and convert this into caps. So numbers we don't need in caps. S uh in here is already in caps. So we'll go ahead and simply the left function that we have written there. We can encompass it within the upper here. You can en encompass the entire into these brackets or the parenthesis here. Now this looks like a code right here. That's the custom code. But remember that after all this here, we don't want to see all the rows. We only want to see five rows. So we will go ahead and use here the last keyword that we have seen that is limit. And we ask it to limit the output by five rows. And we'll be looking at five rows only. Go ahead and see there are multiple functions inside it. Type them one by one. and Cyia I will talk about format function which function upper uh left function format function reverse function because I will talk about the format function we are using format function right now to display the zero decimal values left function here is simply taking the first uh three letters out from the left hand side of the uh sentence upper function is converting this into uppercase reverse here is simply reversing whatever is written. So if it is written ABC, it will reverse that into CBA and then we are concating all together uh while typing the separators here. So here we go. Let's talk about the format function in detail. When to use this format function? when to use this format function and what it is for this for first let's look at the syntax of the format function the syntax here looks like this inside the format we can format any number to anything so if I we can go ahead and uh decide the decimal places with it right we can also define the local with it as per the zone so we can go ahead and format the numbers in any type of format we want for example if we want to go ahead and we want a currency specific Currency display means ensuring two decimal points. So we can type a number comma 2. That's our currency display. Meaning two decimal fixed two decimal points there. Second here is we can if we had a large number we can add thousand separators to it. Or if you want a localized number format. So maybe if someone is writing currency in their own format not everyone is going ahead and applying thousand operators every time. So every country has a different norm to type the numbers and the currencies. So we can go ahead and define that too or if we simply want to multiply by 100 and add a percentage sign there. So in these multiple cases we can use the format function right here. These are one of the many answers uh where we can go ahead and uh work with it. So I will show you few examples here. I will show you few examples here. So let's take a very simple example first. So if I go ahead and type select. If I go ahead and type select here and in select here I will use format. This is format and let's say I will go ahead and type a big number. Okay. And I'm typing here as zero as zero. I'm typing this as formatted. Okay. See it has added the thousand subters for us right here. Now if I want to add a percentage sign here, if I want to add a percentage sign here, what we do? We again go with select here. With select here and now I will type format. Okay, I will go ahead and type format format. Inside format, let's go ahead and uh I'm writing let's say something like this, some decimal values right there. and we multiply it with 100 because that's what we are looking right one I need one decimal here if I go ahead and type it like this right we want to change it into percentage here so look at this what happens we see 76.8 8. Now what I want I want to add a percentage sign in front of it. To add the percentage sign in front of it. Now I will use concat here. I will use concat here. And after format we can go ahead and type the percentage sign right there. And it is going to concat and type the entire column like this. So these are the few examples here. Okay. [clears throat] Go ahead and check out the format function. Yes. All right. So, uh I have one question for you here. Okay. We all know about the products table, right? We have all seen the products table right here. And we know about the product line too. We know about the product line too. If I ask you right now, if I ask you right now to get the average to get the average buy price to get the average buy price of each product line of each product line here, how does the how should the output look like? What should be the table structure of the output? What should be the table structure of the output? Let's say you're creating that on pen or paper or in Excel. So how do you think the output in the result grid should look like? How many columns we should have in the output? If we want average buy price of each product line here, then how many columns we should have here? Which two columns? Which two columns? Product line product line and average here and average here. Now in this product line here here what do you expect in this product line here what do we expect? Should we go ahead and extract the entire product line column from here to here? Should we go ahead and extract the entire product line column here as it is? Should we simply copy paste it entirely same? No. What we will do here is we'll look for unique entries, right? We'll go ahead and we look for the unique entries without any duplicates of course. So these unique entries here when we type unique entries only here meaning each uh each product line should appear only once, right? Show that if it is classic uh cars here, if it is classic cars here and then we have got motorbikes here and uh we have got vintage cars here. Whatever we have in front of these unique entries, we will only have one associated average number right here. Correct? So this kind of reporting so we all know this is how the reporting has to be done. So this kind of reporting is called as aggregate reports. These are called as aggregate reports. Okay. So these are are called as aggregate reports everyone. Why? Because we're aggregating as per individual data point. We're aggregating as per individual data point. Right? If you have learned Excel, it is exactly what pivot table does. If you have learned Excel, this is what exactly pivot table does. So how do we create these kind of reports in uh SQL? This is where group by comes in. This is where group by comes in. We haven't touched it yet, right? Remember in the sequence I have shown you there is group by and having. So this is where group by comes in. What group by will do here is group by will go ahead and simply fetch the unique list of any categorical variable. Group by fetches the unique list unique list of any categorical variable any categorical variable that what the group by will do okay and we already know if I we want to do any kind of calculation that calculation is done within the select query that we are aware about so that is fine that is done by select but group by will be the one who is going to fetch the unique entries right here so last example everyone let's go ahead and see how group buy is applied here. So we'll go ahead and we apply group by right now. So what we want to get? We want to go ahead and get the average buy price of each product line. So first thing that we do here is we type select in select here. Uh we are working with the product table here. So let me go ahead and type product first. And after select here, let's expand it here. And we will get the product line here. after product line here what we are looking for we are looking for to calculate average average of both what average of buy price so I'll ask it to get the average of buy price here I'm calling this here as average okay however after from product here we have to use group by here why group by because group by has to inform select that do not fetch the entire column so when the select is in the select group by will tell it let's see you're not supposed to fetch the entire column I will go ahead and pass you the unique list of product line so remember that whatever categorical value you write here it has to be written here too in front of select okay because select is the one that decides the output table structure so if we are grouping it by product line product line has to be part of the output so group by will actually pass the unique list of product line to select and then select will go ahead and calculate the average right here. So once I execute this here you will find the average right here. Of course it doesn't look good. Okay. So we can round it up or we can format it out. So it all depends on you what are you looking for. So currently I'm rounding this off here. So this is how group by works. Now why I have taught you group by because I'm going to give you multiple practice questions with the where where the group by will be used. Okay. So let's begin here. So what we are looking for here is let's go ahead and say for each country for each country let's figure out uh for each country what is the average credit limit? What is the average credit limit? And while we are looking for it we want to see only the top five. We want to only see the profile. Let's go ahead and see how to solve it. So first thing we have to understand is we are going to use classic models. We have to make sure that the classic models is default. All right. Once you set it that as default here. Now let's uh break it down here and breaking down here. First you want to go ahead and get uh two columns here. Right. First column should be country. Second is average credit limit. Then it is asking for top five. We'll see how we will get to the top five. First let's sort country and the private limit right here. So first thing that we understand here is we have to work with the customers table right here. We can go ahead and uh right here select select what? So we need country. We'll go ahead and get the country right there. and comma we need average right here. So we'll calculate average for now average for credit limit everyone. Now this much we understood and we can name this here as average credit. Okay till here everything is fine. However we need aggregate report because we cannot simply fetch the entire country column. We need unique list of country there. So go ahead and ask it to group by and country right here. Now this must be understood from our previous example. So this is it. Uh this doesn't look good. Why? Because it has got too many decimal points right here. So let's round it off. We'll go ahead and we round it off. Here we have we already know about the round function everyone. So here we go. We will go ahead and ask it to round it by the two decimal points, right? And now here we have the average current limit method. Now in this average current limit here we want to see top five. Now how do we get top five here? This is where thing uh this is where it is very very interesting. Remember our select query right here right in this entire select path here this group by right now after group by here we have order by and limit. Order by means sorting. So after afterwards here if I want to go ahead and see who has the highest or the lowest credit limit which country has the highest or lowest we can simply ask it to order it by. So if we go ahead and ask it to order by meaning sort this particular output this result grid output here by what by average credit limit. So we can simply go ahead and write the name here. Since average credit limit is already a name inside this aggregate report order by works when the select has uh when the select has given us the output. So in the output average credit limit this name is taken as name of the column there. So if I go ahead and ask it to order by credit limit and we execute this right here it gives us everything in ascending order. Okay it is giving us an ascending order by default. So if I want to see who has the highest, how do we get the highest here? We go ahead and we get the descending order. So we go move forward here and we define that that go ahead and give us a descending order. Now here when we define descending order, we do see the top here. See, but we don't want all the 110 product rows here. We are not looking for all the rows out there, all the 27 rows here. Right? We don't want that. And so what we do, we go ahead and ask it that okay, I don't want to see all the rows here. Limit the rows by five. Now what is limiting the rows here? Limiting the rows here means we will go ahead and use another keyword that is limit. Limit is pretty simple. Okay, limit here goes ahead and simply limits the number of rows from the output. Number of rows from the output right there. So this is limit five. The moment I put limit five here and execute this, we have top pi right here. This is limit. So it is just in succession everyone. So right now we