SQL Full Course for Beginners (Complete Guide For Data Engineers)

You need to master SQL to become a data engineer. And if you have literally zero experience with SQL, no worries because this 6 hours long complete SQL boot camp will help you master SQL in a step-by-step approach. Because you will master SQL by doing SQL. And you know what? In the form of dedicated chapters. But why should you watch this video? Because of these three reasons. Reason number one, this is a complete guide to master all the relevant areas in SQL for data engineers because SQL is used by so many other professionals as well. Reason number two, you will learn how to convert those stakeholder requirements and business requirements into the form of SQL code so that you can just deliver the requirements because they will not ask you directly write the code. You need to know how to convert those requirements into the form of code. Reason number three, you will tackle the real-time scenarios because this is the bread and butter for data engineering interviews plus day-to-day activities as well. In short, if you complete this video, you will be able to just tackle and just approach any SQL problem and you will be able to just help those stakeholders with the help of your SQL skills. And why I have created this video after getting a great response on my previous video which was for SQL for big data. Um I got so many request even from experienced data engineers that they want to learn complete SQL from me because previous video actually required some prerequest but this video is like completely beginner friendly and obviously as they said this is like complete guide in the form of structured chapters and all the notes are available in my GitHub repo that you can just download and you can just practice along with this video. So in short, this is a complete guide for you to master SQL this year and just make that backbone of data engineering even more stronger. Let's get started. So what's up? What's up? What's up my fam? Happy Sunday. And I know this Sunday you are really really excited for this particular SQL boot camp, the ultimate guide. First of all, let me just make one thing very very clear. I know you will be like Anlam. So by the way by the way by the way before that why did I create this video after um receiving a great response on my previous video which was also on SQL that was for big data engineers. I talked about SQL for big data engineers who already know some SQL beforehand and how they can just talk and work with realtime scenarios. So I I I got a great response but a lot of you requested bro an Lamba we want to learn SQL from scratch from you from zero. We want to learn SQL from zero. Just imagine that we do not even know the full form of SQL. Just create a complete boot camp and then raise the bar with the course of time. Right? So I thought like let's do it. So this particular SQL boot camp the ultimate guide is like the complete SQL boot camp and you know what the best part no prerequis like required no prerequis nothing nothing nothing nothing nothing nothing just you and that's it this is the prerequest for this video that's it and I am really really excited to complete this virtual SQL boot camp because this is the most structured SQL boot camp for any data professional And especially for data engineers. Why? Because do you know what? SQL is used by almost everyone. Web developers, app developers, um software engineers, um software development engineers, data analysts, data engineers, data scientists, almost everyone who is in the IT domain. And do you know what? Everyone uses SQL differently. Everyone has their preferences. So you as a data engineer, you as a data engineering beginner, you who want to just let's say um start the career in the data engineering, they need to understand SQL from a different perspective. Yes, there are some fundamentals which are the same in all the domains but after that you need to focus more on some other areas plus plus plus wait. You are a data professional, you need to serve the stakeholders. You are not a software engineer. You're not a um you can say database administrator that who who just need to just create the databases and that's it. No, you your profession your your profession is different. You are a data professional. You need to serve the stakeholders. So what will happen? Stakeholders will come to you. Who are the stakeholders? Managers, business managers, whatever business analyst, they will come to you. We want to find the sales that happened in the month of October in the past 3 years and we want to find the moving average and that's it. You'll be like wait we watched something else in the tutorial and I was expecting that you will ask me just apply where clause just apply group by clause. No business managers do not talk like this. They will simply give you the scenario. Now you need to convert that scenario into your SQL syntax. So that is why this particular tutorial is different and that is why that this tutorial is filling that particular gap. So you will understand how to receive the requirements, how to break down those requirements and convert it into a SQL query because you cannot imagine that person will ask me can you apply a group by here? Can you apply a wear clause here? Can you apply this? Can you apply that? No, they do not know anything in SQL. You need to use SQL to solve the data. That's it. So this SQL boot camp going to be really really fun and really really good. So if you have zero experience with SQL, you can obviously kick start with this video. Okay. If you have some experience with SQL, still this video is for you. Why? Because this video will be covering so so so many things that you will be unaware of or that you would need to revise those concept. And let me just tell you what are those things. So this particular video, master class, whatever you want to say, will cover so many areas and by the end of this video, you will be able to master these pieces. SQL database, SQL workbench, installation, setup, DDL commands, DML commands, DQL command, joins, aggregations, window functions, ranking functions, lag and link functions, views, store procedures, custom functions, interview questions, realtime scenarios. So much of content, so much of knowledge is in this particular video. And you know the best part? The best part is all of this information and knowledge is actually um built in a structured way in the in the in the form of dedicated chapters. So we have chapter 1, chapter 2, chapter 3, chapter 4 and I think in total we have chapter 14 or 15. So everything is actually um displayed in a structured format. And do you know what like another best thing? All those chapter SQL scripts are available in what is this? So all those all those chapters are available in my GitHub repo that you can just download that you can just um go and refer and I will just show you my GitHub repo as well. Don't need to worry. So you will actually be able to just do everything that you want with those SQL scripts and you can even take notes from those SQL scripts for your future reference and you can actually treat those um chapters as a cheat sheet. Right? So everything is there for you plus the PDF that you want to we will be using basically the slides for the notes that you'll be using will also be provided in my GitHub repo. Make sense? Are you excited with this video? for this video and obviously with this video because you are with this video okay and I'm also very very excited but I want to just show you something very very exciting that will motivate you a lot what is that so success stories for my data family members who have recently cracked their job offers in the field of data engineering and I am so so so happy and what they did special they they didn't they they didn't do anything special they just followed the videos they just followed the channel and that's it boom so very very very congratulations to all of these people who have just cracked these offers and if I miss anyone so sorry for that. So these are the you can say recent success stories and if you also want to just be here just try to learn and just try to follow this channel right just do it just do it just do it make sense and these are my some other handles as well that you can just follow and that you can just follow along make sense so I am really really excited to kickstart this particular video and you are also excited I know that but you need to tell me that you are excited so just write down your comment in the comment section and let's get started with this particular video. So this is my GitHub repo. Okay. And you can also click here and then you can click here repositories. So this is our repo SQL boot camp for this particular YouTube video. And when you just click here, you will find all the resources as you can see. Source setup. This is like the scripts that we are using to set up our source because you'll be just using a customized source instead of just using the by default source that we get. Okay. so that you can just get the real world experience like how you query the data in the real world. Then you have a dedicated folder for SQL notes and here you will find all the scripts all the theory. Let's say you just want to go through the theory that we have just covered here. There is a PDF file that you can just download and you can just see it here and it will take some time because it will be rendering it. But yeah, you can just download it and you can just see like all the things are there. All the things are here. All the things with notes everything is there. So you can just enjoy. Okay. Then you can go to scripts. So these are scripts based on the dedicated chapters chapter 1, chapter 2, chapter 3, chapter 4. So you can just sort it out and you can actually download this whole thing or the best thing is you can just download this whole repo in a zip folder. How you can just do that? Simply go to code and just say download zip. So it will simply download the zip folder and it will be creating everything within your local system. Make sense? So I hope you are really really excited for this particular video for this particular SQL boot camp. And this is all your resources that you can just use throughout the video. And now let's start it with our let's get started with our next step in this particular video. Let's see. So now let's try to understand the very very very basics. What is a database or let's say you would have heard about this word database database database database database. What is actually a database? Okay. So the database is nothing but just a structured collection of data stored electronically. Okay, think of it as a digital version of an Excel file. You would have worked with Excel, MSXL in the past. It is just like that but it is towed with much more powers and with much more you can say efficiency and so many things that I will be just talking about but you can say it is kind of excel sheet just for now you can understand it is like an excel sheet but it can do much more than just storing the data in the form of tables and columns okay so basically let's say for example you have some data data. Okay. What kind of data? Let's say you have some information such as let's imagine you are in a school right now. Obviously, you would have completed your schooling right now. Okay. So, your role number, your name, your marks, these three columns. Simple thing. You would have stored this information in your notebook, not in your notebook. Maybe your class teacher would have stored this information in her register. Okay? Maybe or like in my days like obviously they used to store this information in the register. Let's say nowadays they are using MSXL. Okay. So let's say these this information is stored in the MSXL nowadays. Make sense? Okay. So now if you are in your school um it makes sense to store that data in the Excel sheet. Why? Because data would not be very much big. There will be hardly let's say 100 200 students in a class. Okay? And your class teacher does not need to just store so much of data. Makes sense? So in that particular scenario your class can use um MSXL but now let's say your class and along all the classes let's say um 1 to 12th okay all the students information need to be stored in one place now you will say bro we can do it using Excel I know I know still because data is very very small right hardly thousand students 2,000 students 5,000 students 5,000 students hypothetical situation bro still like data is not very much big let's now imagine you need to store the information of all the students in your city oh now it's a big thing because let's say like obviously it depends like how many students you have in your city so in my city let's say um I have 100k plus students let's imagine okay so can you store all of that information in excel We can but it will be a mess. Okay, because 100k is still very small number but you can imagine let's say 1 million 1 million plus students. Okay. Okay. Now the thing is we were only talking about storing the data. We didn't actually talk about retrieving the data. Listen to me again. We were only talking about storing the data. We were not talking about retrieving the data. Okay. What does it mean? So let's say you have stored the information of 1 million plus students that are there in your city. Maybe let's say in whole country. There'll be more but yeah just imagine. Okay. So now obviously if you are just storing the data you need to use it at some point of time in future. Yes. Can you retrieve the information using Excel? you will say um yeah I can press Ctrl+ F and I can just search the name of the student bro no way you cannot do this why because it's not just about finding the data for a specific you can say student you need to talk to the data now can you can you feel it now because now you have the data so much of data okay still we are not actually starting the database We are still thinking about MSXL. Okay. So now you have so much of data, 1 million plus students and you somehow manage to store everything in your MSXL. Now you have 1 million plus records. Now you need to talk to data. How you will talk to the data? You need a language. Right? Now see I'm talking to you. I am using English. Right? Um I'm talking to my friends. Okay? I'm using a language. I'm using to let's say I I'm just let's say talking to someone else. Okay. So I'm using a different language. Similarly, we need to now use a language which is the language to talk to the data. So what is that language? That language is called you guessed it right SQL. Now in order to just use SQL we need a kind of proper data base. Data base in which we stored the data which is similar to MSXL but now we can perform more and more operations while storing this data because we found a gap that we need to talk to this data. That is one gap. Second gap is let's say we want to um efficiently read the data and efficiently update the data as well. Let's say one student. Okay, one student is like one student got married. Okay, hypothetical. Let's say we are talking about master's degree. Okay, not in school. Okay, so let's say one student got married. So obviously that student's last name will be changed most probably. Okay. So now now you need to make that change in your data. Can you do that in Excel? It will be a mess. So you found these issues because now you have the data. So that comes the role of a database. So what we do? We simply create something called as database which looks very similar to MSXL. But now we get amazing powerful features such as indexing, such as primary key, such as nulls, constraints. What are these terms? You will also learn it. Don't worry. But you first need to understand the concept. So database is nothing but you can say a kind of MSXL okay but with more powers okay or you can also understand it like this database is the repository is the um area where you just put your data in the structured format that's it sorted very good now let's see what is actually SQL which is so so so popular nowadays and obviously being a data engineer SQL is the backbone okay data engineer data analyst um data scientist like I'm a data engineer so I'm just talking about that perspective but SQL is the backbone of all the roles and do you know what with the advancements in AI SQL has become even more important why because all those data that we need to persist we need to do it in SQL for better search results for better everything we know that we use vector databases what is vector database we are simply talking about database okay so sorry so sorry I will be just talking about about the these topics later on but yes database is like everywhere and in In order to talk to database, you need a language which is called SQL. Okay? So I'm talking to you. I'm using English. If I want to talk to my data, I will use SQL. So it is just a language basically. It is pretty much similar to English. Oh no, bro. I am serious. It is very much similar to English and you will see the syntax when I was just showing you showing it to you. So you will understand everything. Okay? What is SQL and every all the things. So basically what is SQL? just a language structured query language when whenever you just want to talk to the data which is stored in the structured format we use SQL okay it helps us to store retrieve and manipulate data effectively so let's say I want to update that data remember that um marriage incident the last name was changed so now you can just change that last name easily with SQL very easily okay let's say you want to just fetch the data for some records for any any specific you can say criteria you can do it very easily using SQL. It is very much performant. Very much performant. Make sense? Make sense? Makes sense. And yes, when you are learning SQL, SQL is as I just mentioned earlier as well, SQL is everywhere. It is similar to any programming language. SQL has endless you can say um you can say implementations. Okay, we you you can literally use SQL anywhere. But being a data engineer, being in the data engineering side, you need to learn SQL from the right perspective. Yes, we going to learn about fundamentals. Yes, we going to learn about all those things which are required. But you need to put less focus on some areas, more focus on some areas. So you need to know about those areas. Okay, very good. So SQL is also done. Now let's see what actually is like reason behind SQL. What is the actual reason like why do we need SQL? Why we cannot just use any any any any any other thing? So basically let's say I want to talk to you. Okay, you understand English, I understand English. Okay, so what will be the common you can say way to talk to each other. Okay, I know you all are listening to me. So what is that common way? The common way is that common language that we both can understand. So SQL is that language that can be understood by your databases and by you as well. You you will say I don't know SQL. So that is why you are watching this video. But after after like by the end of this video you will be master in SQL. Okay. So then you can also say I am bilingual. I know English and SQL. No don't don't talk like this because SQL is just like a database language. You cannot say I am a bilingual. I know English and I know SQL. So yes, you know SQL and you can say that you are a bilingual in the technical domain that you know Python, you know SQL, you know multiple things. Okay. So are you excited to actually get into the SQL and all things and just a quick tip this course going to be full of fun and full of realtime examples and full of you can say um breaking down the complex things into the simpler into simpler ways and you will love that okay because SQL I know for some people is very very hard thing and after this video it will be the most easiest thing they would have thought okay in the data domain make sense make sense Yes. Okay. So are you excited to just master SQL? Because now you have some fundamental knowledge, conceptual knowledge because see it is required. I could have directly taken you to the SQL code and let's say hey select statement. Hey be statement bro hold on. First of all understand the environment. Understand the vibe what we are trying to learn and why we are learning that. Learn SQL learn SQL learn SQL. Why? Why? You have a now you have a strong why right? You have a strong why. So now let's try to learn SQL and what is the first thing we need to set up our environment. So let's set up our environment and it will be a little tricky but you need to follow the exact steps that I'm following and you will be seeing no errors. Okay. The best thing is there are endless tutorials available um to just u find out how to set up our SQL server. So that is the best thing. Okay. Um you like I will also show you so that you do not need to go anywhere else. So perfect and there's one line that I've added. You do not need to be a genius. Just be curious and ask questions. I love this line. So I thought of just adding this line here and it will just I know motivate you to just master this SQL. So okay let's quickly set up our environment. Okay. So now it's time to set up our environment. So what basically there are so so so many um SQL servers available that we can just use but for this particular course I want to use open source which is MySQL which is the most popular SQL server. I know I know I know Microsoft SQL server is also very popular but I personally feel whenever you are learning something always go with MySQL. It's my personal opinion and trust me I also just um started my SQL journey with MySQL. easy to set up, amazing UI. Um you can say I I don't know like I feel very very relevant whenever you're just learning something from um MySQL plus it is open source plus in the world of AI and all my SQL is like booming because it is open source and you can just literally start MySQL server like this. So let's set up it one by one okay step by step because there are some things that you need to consider. So first of all just open your favorite browser and just search let's say download MySQL. Okay. So this is basically the page. Uh let's see what do we have in this page. Accept all. Okay. We have this this MySQL community downloads. Let's click on this one. Okay. Um okay. So now you can just click on this particular button. My SQL installer for Windows. Basically what is this MySQL installer? Basically this is like one place where we get everything. Everything means everything like it will just ask us hey you need to download um workbench hey you need to download server everything. So there are two options. Okay I will say go with the second one because it will be having all the features available that you need to download. Simply click on download and no thanks just start my download and it will simply download this MySQL installer community. Okay once it is done just open that. So I will simply click on this particular installer and let's actually install it. So click on yes and it will simply open a window. Let's wait. Okay. Click on yes again and then it set. Oracle upgrade available. You will continue receiving the upgrade now. Um do we want to apply this upgrade? Yes. Let's upgrade it if it is asking us to upgrade. Okay. Perfect. So now this is the thing. So this is saying hey this is the catalog. Okay. So if you click here you will see all the things but you need to first of all click on add because now you need to just add all the things that you need. Okay. All the things because it is a complete package. Whenever you want to run SQL you need basically two major things front end and back end. Back end is your SQL server. Front end is your SQL workbench. So server is there in the back end where you will be just communicating with it, communicating to database, but whatever you want to communicate, however you want to communicate, you will be using SQL workbench as a front end to access the UI to write down the queries to just look at the columns, table names, all the things in a user u friendly you can say interface. That is why it is called UIUX, user interface, user experience. So it is that particular thing. Make sense? Okay, perfect. So now how you can just do that? Simply click on this plus button, click on this Microsoft SQL server and click on this 8.0 and click here and just drag it to the right. So what it will do? It will simply install the server. Now if you just click on applications, it will say workbench which is I said like very important. So we need this as well. We do not need shell, we do not need router. Okay, so this is fine. We just need two things. Documentation sample I want. Okay, we want sample. So do not forget this thing as well because we need samples and examples just to like be familiar with the table. Obviously you'll be writing the queries, right? So you need samples. Okay, perfect. Then enable select features page to customize. No, that's fine. Click on next. Okay, so this is the basically the directory where it will be saving our all the resources, our workbench and our server. Make sense? Simply click on next and you still have warning. Yes. So okay this is also fine then I can say execute. So what it will do it will simply execute each and everything. Okay so it has just downloaded SQL server. Now it has downloaded workbench. Now example samples and examples. Make sense? Okay. So now let's wait. It is also done. And I can also say like it's fine. Okay then you can say next. Then next. Then do not touch it here anything. Okay. Simply say next and use strong password. Yes. We will be simply using recommended option. Next. And you can generate any password if you just want. Okay. And perfect. Let's click on check. So it will simply check the secure place. Please remember to store enter the password. Okay. Let's try a different one. What about this? So if I click on check, so it will simply check if it is eligible or not. Okay. What? Oh wow. Let's pick a different difficult one. Um now check what? Oh, it is saying current root password. Okay. So, current root password is I don't know. I don't know. Let's click on back. Click on back. What is current root password? So, let me just check next. Next. Because in your case, you should be receiving something some windows where you'll be just putting your password. Okay. So, let me just put my previous password if it allows me. Let me just click on check because sometime it can throw this error. Um like see I just put my previous password but in your case you should be receiving a window where you'll be just creating the password. Um I don't know whenever we just work with on-prem SQL databases they just store that information I don't know where and it is so difficult to remove it. So if it is your case as well maybe you would have downloaded my SQL in the past. So you you just need to put that password. Um if you are just downloading it for the first time then you will be just getting the um option to select or basically create a password. Make sense? So I just put my previous password. Um luckily click on next. Yeah. So it is saying start MySQL server at system startup. Um we can like start. See what is this? Basically it is saying you need do you need to start MySQL server at system startup. Whenever your system starts do you need to just start it automatically or what? So we can say yes sure why not just say next and then you can simply say execute. So it will simply execute all of these things. Okay. So let's wait updating the start menu link and all. Okay. Perfect. So the configuration for MySQL server is successful. You can simply say finish. Um ready to configure samples and examples. Okay. Let's configure this as well. Okay. So we need to just put our password. So I can just put my password and it will simply say check. So what it will do? It will simply um load some sample databases, sample data set that you can just also run, right? And it is very important to just get started with this. Click on next and then say execute and perfect. Simply say finish. Okay. Now you can say next. Start MySQL workbench after setup. Yeah, why not? Click on finish. So now what it will do? This is my SQL workbench that it has opened for me. Make sense? So this is our MySQL workbench which is open source and it has amazing UI. You will love it. First of all, what is this? Basically, this is the homepage. Okay, so do not need to worry very much here. And this is basically the connections that you have. Now within this, you can actually create more connections as well by clicking on this plus button where you can just put your host and all those things. Okay, because this is just a workbench. This is just a UI, just a front end. You can connect this front end to so many back ends. And where is our back end? Back end is our server. Now where is our server? It is already here when you click here. Okay. So it will ask you for the password. Before putting the password, let me just show you something. If you click on this um wrench. So you will see so many things. Host name this this. Okay. Then port 3306. It is like will be the same in almost all the cases. Okay. Then if I just click here, I just need to put my password. that I selected and I can simply say say password involved. This is basically our you can say front end connected to the back end and you will also see some schemas here which are also called as databases. So there's like uh you can say name that we can use for like schema. Schema is basically a database. So for now you can just ignore this thing. Okay, just ignore this thing for now and you can simply see so many databases in in your case you will just see I think SIS and Sakila because Sakila because this is a sample database. Okay, world XYZ practice are like my previous previous databases that I just created I think 3 to four years back but obviously it just picked this database from this laptop because it saved it somewhere. I don't know where but yeah it's fine. So here if I just click on Sakila, if I click on tables, I will see so many tables created automatically. Obviously we'll be just creating our own tables as well. No need to worry. But this is a sample database that you should see because when we'll be just writing complex SQL queries, you would need dedicated tables which are there for you. Make sense? Very very very good. So you should be very very good till here. Okay. And just to test something, I can literally create a SQL query just quickly. And you do not need to put anything like you do not need to worry about this thing for now. Okay. So what I can do I can simply run any query just to check the connection and you do not need to worry about what I'm writing it here. So just ignore this thing and this is my command. So just ignore and I can simply double click on this one. Okay. Now let me just run this. Okay. So this is working fine. So by the way what I did I just wanted to make sure like everything is running fine. Just a test query. So just ignore this whatever I have written here. Ignore. Ignore. Ignore bro. So how to just cancel this one? Um result grade. Okay, just go here. Okay, makes sense. Okay, so by the way, let's now talk about some of the UI things. Okay, which is very interesting and that's why I ran this query as well so that you can just see all the tabs. Basically, this is your canvas. Why I call it as canvas? Because here you design your queries. This is called canvas where you will write the queries. Okay, write the queries. Make sense? All the queries, all the queries, whatever you want to write, create table, select table, do this, that, do, everything will be here. Okay, this is your main area. So let's say um you wrote some queries. Now you want to run it. How you can just run that query? Okay, so for that particular thing, you have this button. So this is basically the execution button. When you will click here, it will execute that command. Make sense? Very good. Now this is the save button. Obviously you'll be writing so many queries and we need to save it for our future reference whenever you're just revising the concepts. So you can save those query in the form of chapters that we'll be doing here as well. So do not need to worry. Okay. We'll be creating dedicated chapters and all those queries will be available to you in my GitHub repo. You can actually see and you can simply refer it for your future reference. Make sense? As a kind of notes then this is the thing don't limit. This is let's say you are running some queries. Okay. and your database or basically tables has so many records and you do not want to populate all the records. So by default you can also pick let's say limit to 100 rows. So what will happen? All the results will be just having the 100 rows. That's it. And it's a good practice by the way. It's a really good practice. Make sense? And this is the area where you will see whatever you have done that I have I have just um ran this query. So that's why it is coming here. I can even remove it. Just see this button. If I double if I click on it like this, it will be gone. Make sense? And I can even click on this button, then this thing will be appearing here. And this is of no use. So simply remove it. But this is of use definitely. But I don't like it. I simply like my screen big enough to write my complex queries and just to see the results. That's it. Make sense? Very very good. This is like a quick overview and obviously as uh we are moving ahead with this course, you'll be learning so many options, so many things because you need to understand while doing it, not just like covering everything in the beginning, right? So you'll be just learning step by step. Make sense? This is your setup. I know this was like a little bit tricky but you have to follow these steps. So now you have setup ready now you have database ready now you have SQL server ready everything is ready so now now now after setting up the environment now we need to talk about the DDL commands now you would have heard about if not okay it's fine so what are DDL commands even if you haven't heard about this thing so basically whenever you just talk about SQL there are two major parts okay basically three but um in the day-to-day life we just uh mention it two what are the two the first one is dd DL commands. Okay, DDL commands. What is the second one? The second one is DML command. Okay, what is the third one? DQL command. Okay, basically um in day-to-day activities uh we just group together DML and DQL and we just consider it as like DML. It's I know it's not correct but sometime when you feel like hey um we are just talking about DDL and DML. Why we are not talking about DQL? So ideally whenever we say DML we somewhere just consider DQL as well in that particular thing. Okay. So just a insider tip. So for now let's focus on DDL command. What is DDL command? So basically DDL stands for data definition language. Now hold on. Data definition language. What did you understand from this this full form? Data definition that means we define something correct. That's it. So basically DDL this is the set of commands that we have which help us to define our data structure H okay these are SQL commands used to define modify or delete the structure this word is important structure not the data only the structure define modify and delete the structure of the database not the delete or modify the data only the structure of the database. Okay. And obviously database and database objects. What are the objects? Tables, indexes, schemas. Currently we are focusing on tables. So that is fine. So this is the definition of DDL. If you just want to understand it, you can just simply feel like hey DDL command is just like to define the blueprint. Let's use blue color to write blueprint. So it is used to define the blueprint of your table like whatever will be there you need to define it. What can be the blueprint? Um it can be literally almost everything. For example you want to create a table correct. You want to have 10 columns that is detail command. You want to have three string columns and five integer columns and two datetime columns again DDL command. So all of these things are actually the DDL commands. Make sense? We will obviously see it and learn it through the code as well. But this is the backbone of the concept whenever you just want to define the blueprint. Make sense? Very good. What are the common commands that we have in the DDL? Create, alter, drop, truncate. Again, create, alter, drop, truncate. Make sense? If you just want to create a table, create. If you just want to alter, alter drop, drop, truncate, truncate. DDL commands are not very much handy for data engineers. We rarely use DDL commands. We just use at most this command create. That's it. But yeah, you should be aware of this thing as well. Okay, awareness is important. Make sense? Now let's look at the code as well like how we can just start working with our DDL command. So first of all, everything will be starting from the um database. Okay, here we just call database as schemas. Make sense? So you can just think it like that. Nothing special because in every SQL server there's a different terminology. Some some some data servers will be saying uh it as like schema, some will be as database, some will be both. So it's fine. Make sense? So basically this is a database that we have. In my case I have more. You can just ignore for now because when we will be just covering the query part, we will be just creating a demo database from scratch. So do not need to worry. Yes, even if you have this Sakila database, it's fine because if not, we'll be just creating our own database. So do not feel like that. Okay, so first of all, let me just create a database. Let me say create database. Let's say what should we create? Let's say sales. Okay, so this is the syntax for it. So what you do? You simply say create. Okay, then you say database. Okay, then you say database name and semicolon to end the statement. Okay, I told you this is like pretty much similar to English. You're simply saying, hey, create my database called sales. Create database sales. Now, if you just want to run this thing, you can simply click on this execute button and this will execute your command. Okay, I can refresh the database because I know it will be executed. I can simply refresh it and I will simply see this database called sales. See it is here. Makes sense. And if I just click on tables, I do not have anything because I have not created any table within this. Okay, makes sense. Let me just remove this or let me just keep it there. Okay, I can even add a comment. Let's say if you just want to add a comment in SQL, you can simply say double hyphen. That's it. This will be a comment. Let's say create database. Okay, makes sense. So let's say I want to create a table within this database. So I can simply say create table. So now what will be the syntax? Syntax will be like this. Create table name. You will say hey it is very simple. I told you. So let's say I want to create a table called so sales is a DB. Let's say we have a table called stores. Okay. Then semicolon. Make sense? Now I will not run this whole file. Okay. So if I just want to run the whole file, I'll be clicking here. Make sense? So what will happen? It will break my code. Why? Because this command is already executed and this database is already there. It cannot create one more database. So how I can just run the selected part in the you can say script. You can simply select it like this. Okay. Now hit this button. So what it will do? It will only run the code that you would have selected. That's it. Okay, let me just run this. And this will also not work. Why? It will say, "Bro, you want to create a table?" I will do that. But under which database I want to create the table? You will say obviously sales. I know it is very obvious for you, but it is not obvious for my SQL database. It will say, "Bro, just let me know." So, there are two ways you can write it something like this. use sales like this. Okay. And then you can just run this. Let's say do it like this. So it will work. Let me just refresh. And do we have any errors? Do we have create table store? So table must have at least one. Oh yeah, that's fine. That's fine. That is fine. So this also works. Make sense? I just wanted to let you know how you can just select the database. I know this is a there's an error and I will let you I will let you know why there's an error. So this is the database. Okay, that is fine. The second way to just enable the database because you cannot use the use use use use again and again. It doesn't make any sense. So what you can do? You can simply double click on this. See when the moment you double click on this this will be changed to bold. See the other are normal font. This is now bold. That means now this database is selected. So this way you can also select your database for your queries. Make sense? So I can even remove it. Now why did we get the error here? Why? So the error is pretty much simple. You want to create a table, right? You want to create a table. Whenever you create a table on your notebook, whenever you create a table or let's say in Excel sheet, you define columns, right? Columns. Where are the columns here? Oh, okay. So we need to tell like what columns do we want? Okay. So how we can just do that? I will simply say braces. We need to open the braces. Okay. Then I will simply say my column name. So my first column name will be let's say um store ID. Okay. So now I also need to say this is my store ID, right? Okay. What will be the data type of this column? So if you would have created MS Excel sheets there is no um you can say um rule to put like what will be the column type you can put any value literally any value but in SQL databases we need to tell that this column will be holding this type of values why why because the reason is very simple it will simply say bro store id should hold let's say integer values. Let me just say int. Okay, let me just say int. So if my records, future records will be of text or string type, it will fail. Why? Because we have mentioned that it should be of integer type. So it handles those kind of validations as well internally. That is why it is really important. Then let's say I want to create another column. Another column will be let's say store name. Okay. And this will be of the string data type string or character or text. So in the world of SQL databases we just call it as vchar. Okay let's say 200. This is basically the size of the column that we want. There are basically two string columns. Char and vchar or car or var or car or vchair whatever you want to say. So what is the difference between both? Um car will fix and will just hold your memory. Let's say if you just say care 200 it will simply hold the memory for 200 you can characters but ver is pretty much flexible if you let's say store name is of only 10 to 20 alphabets okay so it will not lock the memory for all the 200 alphabets so that that is the power of store var and 200 is the maximum length that we can just pass after that it will throw the error okay you can even increase it you can say 2,000 let's say I just want to store these two columns okay so I will simply close this And let me just run this thing now. Make sense? So let me just refresh it. So now you will see tables. I have stores. See? So this way we can actually create the table. If you just click on this dropdown, you will see columns and these are the two columns, store ID and store name. Perfect. So this is the way that we can just create the tables within the SQL database. Make sense? Very. So here I will also show you one DML command. I know that we are not tapping the DML area right now but it is really really important. Okay. So let's say this is my table. Okay. So now let's say if I want to insert some data into this table because currently this table is empty as we know right. So in order to just insert some data into this table because let's say in Excel you directly write the values in your columns tuck tuck tuck tuck tuck tuck here you cannot do tuck tuck tuck tuck tuck tuck. there's a specific language there's a specific syntax for that so let's say I want to insert some records okay so I can simply say insert some records okay makes sense so what is the syntax it is very simple okay again English you simply say insert okay into in which table you want to insert I want to insert in the stores table make sense hit enter now what are the values you want to insert insert. Now you will simply say I want to insert one in the store id column. Then store name will be let's say um store_xyz. Okay, this is my first record. Then second record will be let's say second and store ABC. Let's say I just want to um insert these two records. That's it. I will add semicolon. Make sense? And add semicolon here as well. That's it. and comma. Okay, so these are the two records. So how it will know that one will go to store ID and store XYZ will go to store name because that is the order of the column. Oh, so we need to make sure of the order of the column. Obviously, bro, obviously. So what I will do, I will simply add the data here. Okay. And let's see the results. It is fine. Okay. You can just uh ignore these things because I was just testing some data. I was preparing some amazing data for you that you'll be just using very soon but it's fine. Okay. So it is done. This data is inserted as you can see here. So this data is inserted. Make sense? Okay. So now we learned how to insert the data. But why did we cover this area like why did we insert the data so soon? Because I wanted to make you aware of an amazing concept in the world of SQL databases and that is called constraints. So let's talk about that. So what are basically the constraints? Constraints are just the fancy name for rules for rules. So constraints in SQL are rules applied to table columns to enforce data integrity and accuracy. What does it mean? See, let's say you are creating a table, okay? And you know that you will not be the only one who will be inserting the data. Or let's say you are the one who is inserting the data. Sometimes you can also make some mistakes. Okay. Sometimes the other person can make some mistakes while inserting the data. You will see Angela you are already inserting some rules. Right? When you write int vcare those type those those types of things. See those are also constraints but not actually the constraints not actually the rules. We are simply deciding the data type that we want. Right? But constraints are much more than that. Okay. Constraints means let's say you have decided a column called store id and you want a rule that hey you should never repeat that ID again let's say store ID one that means there should be no record with same uni uh same you can say store id so that is a rule right perfect let's say um you want to define a rule on store name column that hey this store name should not be null there should always be data available for this column. So these kinds of rules are basically called as constraints that we apply on the column levels. What are the most popular constraints that we have? The most popular ones are first of all obviously not null. Okay, not null. Then we have let's say unique. Just take notes, bro. Unique. And then we have let's say um not null unique primary key. What is this primary key? We will just talk about this thing in the keys but you can just imagine like this is one of the constraints that we have. Okay, make sense because we're going to just talk about keys separately so that you can just understand. Then we have something called as check constraint. Okay. Then we have um check is done and yeah default these are basically the popular constraints that we apply. Okay, make sense. Not null. Not null means you do not want any value any column to be null. Unique means that particular record should be unique. Primary key is the combination of both. Just to give you a hint, we're going to talk about this thing. Don't worry. But this is the combination of both. Okay, perfect. Now, check constraint. Check constraint means when you just want to add a rule. For example, you have a column called age in your database in your table. Okay, age you want to say age should always be greater than 80. That is your check constraint. So these kind of rules can you you can like these are like customized rules. Okay. Then default when you just want to set a default value to your column. So these are basically the constraints that we apply to our tables. Let's see in the code how we can just deal with it. So you know that we created these table. Okay. So currently let's say I want to add some more data. So I ran this command. So let's say I add one more record. Let's say three. And I do not even pass this record. Let's say okay. What will happen if I just run this? What will happen? Let's see. It will say hey column count doesn't match the value count in row one. Perfect. That's fine. But that doesn't mean like we cannot achieve this. We can achieve this by def. See if we write this insert into stores. That means we are saying hey insert into all the columns of stores. If you want to say bro insert into stores but only insert the data in this particular column that means it will work. If I run this it should run fine. See one row affected that means it ran fine. Wow. That means we can selectively insert the data as well. Perfect. But you know what did what what what would happen? So we know that we have this table. Okay, we have um ID 1 and we have some value. We have two, we have some value. But for three, we do not have anything. We have null. And you know why it is allowed? Because I do not have specified any kind of constraints here. That is why it is allowed. But if I add the constraint, I cannot do this. I cannot do this. Let me just show you. So let's create a new table. Okay, let's create this table and let's call it as stores new. Okay, stores new. Make sense? And this time, do you know what I want to add here? Constraint. And the constraint is not null. Make sense? This is one of the constraint. And this here I want to add a constraint. Let's say unique. Do you know what what does it mean? That means this store ID will be unique every time. If I will be just trying to add the same record, let's say same ID for one more time, it will fail. But it will not fail here because there's no constraint here. I can literally insert another record of the same value. Same value I can. But in the real world, we should not do that, right? Because we do not want any kind of duplicates in our tables, right? So that is why we use something called as constraints. So let's try to run this table. Okay. Okay. And let's see what is the result. Okay, it's fine. Now let's try to insert some data. Now let's say I want to insert the data. And I will simply say insert some data. Do you know what? If I try to run this command here, it will fail. Do you know why? See, same thing. Okay. And it worked here. But it will not work here. Why? Because I have constraint here. If I try to run this, let me just show you. If I try to run this, it will fail. Why? It is saying field store name doesn't have a default value because it is saying bro come here. You said that it should not be null and right now you are inserting null records. Why? So that's how you cannot let anyone to insert null records, null values for any column using not null. So I have to use something like this. Let's say X1 and store XYZ. Perfect. See now this will work. Make sense? So this is like not null, unique, these kind of constraint, right? And one more thing that I can just tell you about the constraint. um it is called default. You can also set the default value. Let's say you want to set it set default value to store id zero or let's say store name is um store xyz. You can also set that as well. But those are the constraints that you can just explore and that you can just try. We have so many constraints but you just need a highlevel concept. Okay, highle overview of the concept. That's it. That's it. because these things will be done by the DBAs which are database administrators. Okay, you will not be dealing with these things but you should be aware of these things because in the data engineering side when you build the data warehouse you use these constraints but not very effectively but yes you use sometimes okay or you should be aware of these things because what what like what is the reason see you are a data engineer who is a consumer of a database okay you need to understand the terms and terminologies effectively why because when you will be pulling some data okay Let's say your pipeline failed and something is written hey duplicates are there blah blah blah some error code is mentioned if you do not even know the concepts on the database side how you can just troubleshoot those things so that is why we call as like SQL is the backbone of every data role data engineer data analyst data scientist make sense because you need to be aware of these things so these are the rules that we apply make sense so Now let's talk about another constraint which is very very very popular. Okay, what is that constraint? It is called drop table. Okay, drop table means drop. Now there are two constraints drop and truncate. Drop and truncate both are very very similar. Very very similar like you can be confused in both of the things. Let me just tell you what is the drop and truncate. Drop constraint means when you just want to drop the table, the whole table. Okay, truncate will not drop your table. It will just drop basically the data within that. Let me just see if there's any good diagram for that. So drop versus truncate. And I'm so sure like there'll be some um documentation which can just show you the diagram. Um geek for geeks. Let's try this. Is there any diagram? Is there diagram? Um, no, there's no diagram. Lots of ads. Okay. Okay. Let me just No. So, there's no diagram, but yeah. Uh, let me just search share images. No, this is nice. So, let me just show you basically what is the difference. The difference is very, very simple. So what it does basically when you just want to drop any table it will simply drop the drop basically everything drop the DDL as well. So let's say if I first of all refresh this thing I will see I have two tables right stores and stores new. If I write drop table stores new okay and if I run this the command you know what will happen it will eliminate the table from here. The whole table is gone. Literally gone. Okay. Okay. And let's see what what we have in the stores if I write truncate table instead of drop. What will happen? Let's see what happens. See table is still there. But you know what? We do not have any kind of data within that really. Yeah. See truncate table z rows affected and that's it. We do not have any data. So basically it removed the data from that table. But the blueprint the structure is still there. Column names are still there. You can still feed the data. But drop means now table doesn't even exist. So that is a difference between both. Um your drop command and truncate command. Make sense? Very good. Now let's talk about another very popular constraint. Not constraint basically it's like um you can say DDL command. Okay. And drop and truncate are also not constrained. If I use the word constraint um detail commands. So the next detail command is alter command. What is alter command? What what do you understand by looking at the word alter? Obviously some kind of changes modifications. Exactly. Alter command is used to make some changes not to your data to your table. Remember this is a simple formula. Whenever you talk about DDL commands we are making changes on the structure. When we talking about DML, we are talking about changing the data. Okay, DML means update command and all those things that you'll be just covering very very soon. But yeah, that is a simple difference and just a good way to remember. So we are talking about alter command. Alter command means we just want to change the structure of our objects. Let's say you have this table. Okay, obviously this is dropped now. So let's say you created this table. Let's run this and it should run fine. Yes, it ran fine. Perfect. So, we have this table. Okay. And you inserted this data. Let's say like this. You inserted this record. Then you realize, oh, I want to add one more column. I skipped that column. So, sorry. So, how you can just add the column here? You will simply say it is very simple. Let's say store location. You cannot write it like this. No, it is not allowed. Okay, you cannot. So you have to use the alter command for that. Okay, so you will simply say alter command. So you'll simply say alter table. Okay, alter table. And which table you want to alter? I want to alter let's say stores new. Make sense? Now let's say I want to add add what I want to add a column. Right? I want to add a column. So you will simply say hey bro I want to add a column and I can simply say let's say add column and column name will be let's say store location. Okay. And obviously the data will be bar chart. Let's say bar chart 100 or let's say 200 or let's say location city or store city. Okay store city. Let's say you want to just run this. Make sense? Let's try to run this. And what is the status code? Let me just drag it here. So see it ran fine. Perfect. Now if I just refresh this, I will see this table which is called stores. Where is the table? Stores new. I will see this third column as well. See store CD which was not there in the beginning but now we have. Wow. So this way you can just let's say add a column. You can also drop a column. You can change basically you can rename the column as well. Really? Yeah. Let's say you want to rename this column store city to let's say store location. Okay. Okay. So you can simply say rename column store city to let's say store location. Store location. Let's say you want to do this. Okay. Let's run this and let's refresh this. See now it is called store location. So you can literally change the structure of your table using alter command. Make sense? structure of your table, not your data. Remember, not your data, just the table or basically the objects because table is not the right word because we can literally change the structure of any object. Make sense? So, this is basically your alter command. Okay. So, these are all the things that you have within the DDL space. Only one thing is left that we're going to talk about now. It is called keys. Keys. Keys are very very important. you will be just uh learning primary key, foreign key, natural key, surrogate key, all those types of keys because those are very very important and it's the best time to talk about those things. Okay. Now let's talk about the keys. Once you are familiar with keys as well, then you're going to master DQL and DML which is the your which is your focused area. Being a data professional, you need to focus more on the DQL and DML side, not much on the DDL side because DDL side will be your source. Okay, will be your source. But you should be aware of these things because this is the backbone of everything. You should be aware of these things, right? So let's try to learn about keys. So my fam, now it's time to master all the keys. Okay? After this you should not be able to confuse yourself with the keys concept. Okay, I know it is like little bit challenging but you will master it. Okay, just be with me one by one. Just be with me one by one. Okay, just be with me for one to two more minutes. You will master the concept of keys in SQL. Make sense? One thing um you do not need to master all the keys. Okay, you do not need to master all the keys first of all but you should know about all the keys. You should okay let's master all the keys bro it is so simple just master it okay so let's start our concept with unique key because this is the simplest key and the easiest key that you can see unique key what is it what what is it called unique key what does it mean that means it is unique it is unique simple so basically let's say there's a table okay we'll just read these text as well but First try to understand it. The real knowledge is in my diagrams. Okay. Yeah, I'm I'm serious. I'm serious. So, oops. What is this? Oh, man. What is this? Okay, just ignore it. Perfect. So, yeah, I was saying that real knowledge is in my diagrams. So, first of all, let's say we have a table. Okay, we have a table. And do you know what? Why I do like creating live diagrams so that you can just understand because it is called photogenic memory I guess. So when you just see the pictures it is good but when you see the real live images you can just store it in a better way. Anala Dr. Anala I'm serious bro I'm serious. I'm serious. Okay. So let's say we have a table called products. Okay. We have ID. We have name. We have price. Okay. We have let's say category. Okay, category. Now we need to create something called as unique key. Okay, I will ask you what will be the unique key and you will know the answer. Let's see 1 2 3. Okay. Uh name can be let's say a a let's say bb and let's say cc and then let's say price. price is 10 10 20 and then let's say category is same x okay let me just say y okay so now now you have this table okay now you have this table makes sense now what is a unique key just tell me you will say anjul lamba um unique key I think is this one ID or name. That's the correct answer. Why? Why this column is a unique key? Because this column is actually having all the unique values only. Do not have any kind of duplicate values as we can see in these two columns. So in this table there are two unique keys ID and name. So short answer whenever you see the unique values only in your column that is your unique key. That's it. Make sense? Very good. So now let's read it. Ensures that all values in a column or combination of columns are unique. Make sense? Can have one null which is very important point. Which is very important point. Let's say we have one more record which is called null. Which is called null. And this is also null. Will this key now be considered as unique key? The answer is yes. Why bro? It is just having one null. Null is also unique, right? Null is also unique. That is why if you see the whole column, all the values are unique and it has null. That is also unique. So that is why it is called unique key. Make sense? Simple. You don't need to read these definitions when you understand the concept. That's it. That's it. Okay. We have this much of books on SQL. This much. Why? Why are you watching this video? Because you need to understand the concept instead of just remembering those definitions. Okay, now it's time to understand the concept. Next key is my personal favorite, everyone's favorite, primary key. Tada. What is primary key? Okay, if you get this question in interview, what is primary key? You should laugh, bro. You don't even know what is primary key? Let me just tell you. So, just behave like this. Really, no. Just answer the question questions confidently obviously, but do not behave like this. Hey, bro, let me just tell you. The person already knows. Okay, that is why he or she is asking the questions. So let's say primary key. Primary key is the pro version is the advanced version of unique key. What is this? Yeah, I'm serious. Basically primary key is that key which can uniquely identify a record which is a very popular definition. Which is a very popular definition. Make sense? Basically why I am saying that uni primary key is the pro version of unique key because it is similar to that but there's only one difference when you have let's say the same table let's say you have the same table let's say like this the same table that we created before let's say this this this okay and let's say this if we have values let's say ID name, price. Okay. And category ID 1 2 3 4. Then I have here name A BB C and null. And price let's say 10 10 20 10. and category let's say y why y why y why y okay so now what is my unique key you will simply say bro same ID and name perfect what is my primary key what is my primary key primary key is only this column ID it is not name why you will say bro why it is not a primary key because it can also uniquely identify a record. You are partially correct. Yes, it can uniquely identify a record because this record is unique. This record is unique. This record is unique. But we have null. In primary keys, we cannot accept a null. That is the major difference between unique key and primary key which will be our interview question as well. I'm so sure. I'm so sure. Just say let me tell you. Let me tell you. So this is the difference. Make sense? So in this particular case, the ID is the primary key. Okay. There's one more rule in primary key. There are basically so many rules. One more rule is there can be only one primary key. Only one primary key. That's it. Only one. No two, no three. Unique keys can be more. As we just saw in the previous example, unique keys can be more, but primary key will be only one. Only one. Make sense? Per table. Okay, makes sense. Did you understand this? Was it difficult? Obviously, no. So, just drop a lovely comment in the comment section, bro. What are you doing? Let's let's learn foreign key. Foreign key is actually very very important. Why? Because it lives in foreign. No, foreign key means Yeah, kind of. Um for like first of all, just listen to this word foreign. Foreign means somewhere outside. Exactly. So this is also a primary key. Do you know what? It is also a primary key. What? What? Yes, it is also a primary key. But not of your table, of some other table. Let me just explain. Let's say you have this table. You have this table. Okay, my drawing is really good. I know that. And I was a great drawer of my class building landscapes and all. I was like pro in that. So ID, name, let's say price and category, right? 1 2 3 make sense? And name is A BB CC. Okay. Price let's say 10 20 let's say and category is let's say Z Z. Okay. Good. So this is just one table. Okay. And I'm just drawing this table in front of you so that you can make notes. Bro, please please just draw these things on your own. It is so easy for me to just use those snippets, use your use those images. I'm just doing it for you. I'm just drawing it live, okay? Just for you. Just take notes. It will be really really helpful. Trust me, because it will be really helpful for you to revise those concepts, okay? I'm just doing everything for you, for your success, for your growth. Please, please, please. So, this is let's say one table and this table name is called products. Okay, products. Make sense? Make sense? Okay. Now, now what is the primary key of this table? Primary key is this one ID because it is a unique identifier. Make sense? And there can be just one primary key. Very good. Now, we have one more table. Okay, we have table like this. And this table is called I will tell you don't worry. This table is called category. Oh, okay. This table is called category. Okay, let me just use pink color. I like this light pink color. It's good for black screen because some of you will say, "Hey, blue color is too dark." So pink pink wait what did I write bro pink category okay pink so let's say this is our category table now this is my category name okay this can be let's say x y z then category maybe category name or let's say category name is this category let's let's call it as category ID Just for your understanding, let's say category ID. Okay, and this as well category ID. Okay, makes sense. Then we have category name and name. And then we have let's say category group whatever. Okay, category name is um let's say food, shoes, then clothes. Okay, clothes and category group is let's say merchandise and this is merchandise what let's say this is merchandise and this is grocery make sense very simple table so now what is the primary key here what is the primary key here you will say it is very simple it is category ID how because it can uniquely ally identify the record. Very good. So I'll simply say this is my primary key. Okay. Do you know what what is a foreign key? Whenever you just talk about foreign key, whenever someone asks you, hey, what is foreign key? Just ask a back question in which table's reference. That is a smart question. You have to ask it. Whether it's a company CEO is asking you this question. Maybe he's taking your interview. Just say in which table's context, bro. Okay, because you cannot tell foreign key directly. For example, I asked you, hey, what is foreign key? You will say, bro, in which table's context, I will say in products table context. So in this particular table, the foreign key is this key category ID. Why? Why foreign? Why? Because because of two things. First thing is this is same column is the primary key of the different table. See this is the rule number one. Rule number two is this particular key is the joining condition. See we have ZZ. So we can connect this table to this table on this particular value. See so I can simply get clothes clothes clothes clothes clothes clothes clothes clothes clothes clothes clothes clothes clothes here because we have category ID here. Two rules primary key of other table plus joining condition of both the tables. when you get these two conditions foreign key is there here simple but that question was important in which tables context because if you will ask me what is the foreign key in category table I don't know I don't know and I doubt is there any foreign key why because where is the table's context I just have three columns okay and neither of these columns is the primary key of the other table I do not even need to Go to the second rule. No, no. So this is the foreign key. Make sense? So just draw these tables, okay, by hand and you will remember it. So what it what it what what it is written here creates a link between two tables. Okay. Ensures that values in column A exist in the another table's primary key. The same thing that I have just mentioned and drew everything for you just for your learning. Okay, let's see the next thing. Candidate key. This is confusing. Not confusing from the understanding perspective. No, whenever let's say you hear this word, hey, what is candidate key? So, you need to tell yourself it is very easy. We already learned it and let me just tell you what is that. So, basically it is saying bro is a table. Okay, this is a table. The table looks like this. Okay, the table looks like this. And this table is called customers table. Make sense? Customers table. So we have data something like this. Let's say ID, customer ID. We have customer name. We have customer email. We have customer phone. We have customer gender. Make sense? Now what I do? I will simply say one. Okay. Then I will say name let's say an okay why an okay let's say Rahul okay let's say Rahul the email is let's say um you can just imagine any email okay because obviously email is unique in every um record obviously because you cannot have any other email with the same email id okay let's say it has some email id dot dot dot dot you can just imagine any email id okay then it has some phone number obviously dot dot and gender is let's say male okay then we have two then we have let's say take a girl any name let's say Priya why I take example of Priya because Priya is a very generic name right that's it and no links no relations no nothing bro and dot dot dot dot dot I literally got one comment and the girl the girl's name was PIS she was saying like why do you take my name in everyone's in every example I was like that is like very common name so it's fine I have to use some examples bro which name I will take okay and it she is female and then we have third record and let's say an let let me just take let let me take my um nickname you will say bro don't be over smart this is not your nickname bro just ask my friends this is my nickname trust me love So yeah, I'm serious. So this is my Okay. T and Perfect. So let's say this is my this is our customers table. Make sense? Makes sense. So now what is the candidate key? This is interesting. Or I would say what are the candidate keys? Basically let me just give you a hint before you answer. Candidate keys means whatever column whatever whatever columns are the candidate to become a primary key are called as candidate keys. That means let's say you have this column ID. Is it unique? Yes. Okay. Mark it. It is unique. Okay. Name. Is it unique? Yes. But it can be repetitive as well because let's say two people have the same name. Let's say two girls are Priya, two boys are Rahul and only one is love. Okay. Then email. Email is unique. That means Okay. Take. Phone number is unique. Yes. Phone number is unique. Gender. No. So how many columns are there which can uniquely identify a record? Three. So there are three candidate keys. These are the candidates to become a primary key. So currently let's say you are sitting interview. You are a candidate. You can be selected and I trust you that you will be selected. Don't worry. I am with you. Okay? I am with you. you will be selected. So then you will become a primary key. You you will not become a primary key but yeah you can just feel it like this. Okay. So these are basically the candidates to become a primary key. ID, email and phone. But we have to pick one primary key per table. So you can pick anyone. Let's say I pick ID. You pick email. Third person pick phone. So it is fine. No one is wrong. No one is right. You can pick any column. That's it. So you can also me remember this thing. I have specially written it for you. Primary key is chosen from candidate keys. So someone will say okay these three are candidate keys. Now what is primary key? You will say bro these are the candidates and you can pick any key. So whatever is the chosen one. I know I'm the chosen one. So so whatever is the chosen one will become the primary key. That's it. Simple. Very good. Now let's see what is a composite key. This is something called as that we deal with the real world challenges. Composite key. You understood primary key concept. Okay. Let me just take you one step ahead. What is this? Let's say you have a table. Okay. You have this table and this table is called let's say orders table. Okay. Orders table. Now this is called order ID. Okay. This is called product ID. Okay. Then this is called let's say amount. Okay. Now let's ignore these two columns. Let's fill it. Okay. Let's say um order size. Okay. Makes sense. Then let's say order delivery location. Okay, makes sense. Now, let's say order ID is 1. Okay, product ID is 101. Amount is let's say 1,000. Okay, and size let's say extra large. Location is let's say California. Okay, then order number ID is two or let's say one. Why one, bro? Haven't you ordered anything from online? In the one order, you can order multiple products, right? Makes sense. Let's say you ordered um iPhone and you ordered earbuds. I don't know why are you using iPods? iPods. iPhone and AirPods. Both. Why are you using I don't know your choice. Uh I love Android. So basically let's say you ordered iPhone and AirPods both both. So in that particular scenario order ID will be the same order number but product ID is different. Right? That's what we are doing. Let's say product ID is 201. Then amount is obviously expensive. Let's say $2,000, bro. Dollars. Then size is small because it is lightweight. And the location is let's say um Bangalore. Bangalore. Okay. So let's say second order ID two. And I ordered let's say same stuff. Not me like someone ordered same stuff that maybe someone also ordered um iPhone okay because a lot of people can afford iPhone not only you okay so let's say thousand and this is extra large okay and the person ordered from let's say um Tamil Nadu okay I know it's a state but yeah it's fine so now now what is the primary key just tell Give me this thing. You will say um let me check. You will say um this is no because these are duplicates. Um no no [Music] there's no primary key. You will say location. No location cannot be a primary key. Why it is unique? I know it is unique but in the further you let's say there is another order from Bangalore. Now make your primary key go. So you will say okay then there is no primary key there cannot be any table which doesn't have any primary key. So there should be a primary key. Let me just tell you what will be the primary key. Just just be focused. These two are primary keys. These two together these two together are primary keys. How? Okay just tell me the first record 1 and 101. If I just write down the combination here, let's say 1 comma 101. Okay. If I write second combination 1 comma 2011. Third combination 2 comma 101. Are these unique? This is unique. Yes. Because there's no combination with the same um combinations. This is unique. This is unique. Oh. So when we combine together more than one column to create a primary key, it is called composite key. Make sense? It is called composite key. Simple. It was so simple. So so simple because you drew this diagram. Okay, makes sense. So that was all about your keys. There's one more key which is very very important and you need to understand this surrogate key. Surrogate key. This can be like very very very new to you because this is used on the data engineering side especially what is a surrogate what what is a surrogate key basically let's say you have this table let's say you ordered something from Amazon okay you ordered something from Amazon and you have obviously order summary with order ID okay let's say you have table structure like Yes, don't worry. We'll be just learning everything in code as well. Don't worry at all. Don't worry, bro. Don't worry. So, let's say this is order ID. Okay. And here it is order name. Here it is price. Here we have let's say size here. Let's say we have cancellation flag. Okay. Okay. Order ID. Let's say here the order ID that we are using you already know like whenever you just order some stuff from Amazon it is like containing big order ID let's say like this okay obviously then other order ids third order ID okay then there are names let's say you ordered um iPhone you ordered Samsung you ordered headset it. Okay. And price you already know. Let's say 2,00 2100. Let's say 200. Okay. Size is let's say small. Small and extra small. And cancellation. No. No. Yes. Let's say. So this is our orders table from the Amazon you can say marketplace. But what is the primary key? You will say, "Hey, hey, this one." Now we are pro. You are right. This is a primary key. I know that. But do you know what it is? Basically a natural key. What is a natural key? Okay, natural key is not actually an officially considered as a key. But you should know about this. Natural key means that has some natural business meaning. So it has a natural key. This is a natural key which is like has a business meaning because it is a order ID. It is an order ID. Okay. So this is a natural key. Natural key is nothing but let's say which key has some meaning. Okay. Some business meaning. Natural key, business key. It is like a very fancy name which is not actually considered as one of the keys but it is very much used as a jargon. Okay. So let's say this is your natural key, order ID. Now this key is very very big. Very big. It is not very efficient to apply joins. It is not very much efficient to update to upsert all those things. So in that particular do you know what we do? We simply define something called a surrogate key. Surrogate key means it is an artificial key which doesn't have any meaning which doesn't have anything which is just there to replace the natural keys with small numbers. Let's say my surrogate key is this one. So this value will be holding values like this. Let's say 1 2 and 3. Usually these keys are automatically incrementing keys. So whenever we just get new data, it increments its value by one. That's it. That is what I have written here. An artificial key usually an auto increment number used when natural keys are inconvenient or too long. Simple. This is a natural key order ID because this is describing an order. But this is very inconvenient and very long and very difficult to use. We will simply define something called as surrogate key. Simple 1 2 3. Very easy to apply joints, very easy to apply updates and everything. Easy to manage. This is the concept of your keys in the SQL. And I hope now you know all these things. Take notes about these things because you know what? Whenever you are sitting in any interview and just forget about any interview bro you need to understand these concept if you want to become a better developer and if I just want to focus on interview yes you will get complex SQL queries that you will master in the upcoming sessions okay but you will definitely get these types of questions regarding keys in your interviews I'm so sure so just master these things okay let's now try to learn some coding because I know enough conceptual knowledge age DDL part is also done. Now let's actually focus on the query part. Basically DQL part data query language which is called select statement. It is not just one statement. No, it is like the backbone of SQL. So currently now in this particular section, okay, we'll be just mastering um SQL from the query perspective. how we can query the data if we have the database ready if we have everything ready how we can query it that is something that we going to master in this particular section of this video and obviously we're going to start from scratch and we going to just level up step by step make sense so let's do it so now as you know that we created some scripts okay and we just um explored these areas and now it's time to actually explore the query part which is the backbone of SQL for that we be just creating a new database. Okay, what will be the name of that database? Let me just do that for you. Let me just say new SQL script. Okay, and let I I will simply say create database. Okay, create database. And you can just name it name it anything. I would say you can say e-commerce. Ecom, ecom ecom is a good name. Just run it. It will simply create an ecom database and you can simply refresh it and see it. See ecom. Now we need to create tables within this. We need to insert so much of data real world data so that you can learn and practice and how we just apply joins and all those things in the real world. Obviously you need to just know all those things and I am not using this default database Sakila because maybe some of you will not be able to access it. So I do not want to make it a complicated thing for you. I just want to make it everything easy for you. So what I have done I have this repo. Lovely repo. I love this guy. So you can also click here okay and then you just go to repositories and then you can just go to SQL bootamp repo okay you can just search it if you will not be finding it here if you can find it here you can simply search it okay SQL boot camp so this is basically the repo in which I have this folder called source setup you just need to click on the SQL file and you just need to open this SQL and copy this code from here that's it and you need to paste that code here and there is so much of data Okay. Then you can simply click on this execute button. So what will happen? It will simply execute all the commands and it will simply insert so much of data. It will take some time because there are so many records. Okay. Let me just see. See everything is going right now. So it will take some time. Once it is done, you will see everything is stopped and that you need to do. And there is one thing that I just forgot. Let's wait for it. It's not a mistake. But in your case, it will not be a mistake. Let me just tell you again. Let me wait for it to finish because let me just tell you what did I miss. Um, we didn't actually select our e-commerce data warehouse basically database before running these commands. So everything will be going in the XYZ database which was selected by default. Okay, which was selected by default. So it's not a big deal. You can also make these mistakes. It's fine. Makes sense. Makes sense. Makes sense. Makes sense. Okay. Makes sense. So let's wait for it to complete. Okay, this will take I think um I don't know how long it will take. Not much but yeah we can just wait. Okay, because there are like so many rows literally so many records and it is just running all the things one by one. Why I'm just running all of these things? Because I want to show you like we just work with so many of records and when you just work with much bigger data set you will actually learn a lot of things, right? Make sense? Very good. So, let's wait for it to complete. And yeah, how many records do we have? Let me just see. Oh, we have many records, bro. Okay, so it is done, I think. Yep, now it has stopped. So, and let me just show you where it has created those tables. If I just explore XYZ, I will see XYZ. Let me just refresh. Yeah, all the tables are here but don't worry and you already know how to select the data base. One option is you can just write use then database name. The quicker way is double click on this ecom. This ecom will be bold. Okay, now you can just rerun this command one more time. So this will just populate all the tables. Okay, this will populate all the tables inside this and it will take hardly 1 to 2 minutes and that's it. So that way your source will be ready. And you know what? What is the best thing about this particular database? This database is that database that you use in a daily basis in a in a day-to-day activity in the real world. Okay. What's so special in this database? Anchla. Why why why are you saying this thing? The reason is this database is actually designed in the form of dimensional data model in the form of fact and dimension tables that you actually use in the real world to query the data right good so you should know how to deal with these things make sense okay so see I'm so so so possessive for you I have just taken care of all the things that are important for you that are important for your growth see So so so possessive for you. Very well done. Anything for you. Anything. Okay. So in this particular thing, okay, we going to start very slow. Don't need to worry at all because that will be jumping directly. Don't worry at all. And the best thing is you will be understanding everything because nothing is easy or nothing is difficult. It's just about how you learn those things. Who is your mentor? Who is your teacher? Who is your guide? Okay, it can be any book. It can be any course. It can be any video. It can be this video. It totally depends on that particular thing. And plus obviously your curiosity, your excitement to learn about those things. Make sense? It's like two what what is it called? It's a very popular phrase, right? Phrase. Sorry. Um love from two sides. What's what's that? I I don't know. I don't know. I don't know about those things. Okay. So, let me just refresh this particular database and you will see all the tables are built here. dim customer, dim date, dim product, dim store, fact sales. Basically, dim is then dim is an abbreviation for dimension, fact is the abbrevation for abbreviation for fact. That's it. Okay, as I just mentioned that in the real world, we query dimensional data model. That means in the real world we work with dimensions and fact table. Okay, don't need to worry about that thing because that is a data modeling thing and data warehousing thing. We are simply learning SQL. We are simply learning how to query the data, right? So I can simply save this file in this as let's say chapter 2. Okay. So I'm creating dedicated chapters. So chapter 2 is for basically creating the script just for your reference. Okay. Let me just close this. Close this as well. And let's create a new SQL file. And you will get all of these chapters in my GitHub repo here. So what I will do, I will simply create a dedicated folder here inside the SQL boot camp where you will get all these SQL files. Okay. And I will also upload a folder which will be holding the slides the PPT that we are learning from. Right? That slides that will also be available as a notes. Make sense? Everything will be available for free. For free. Still you are thinking twice. Should I just hit the subscribe button? Should I hit the like button? Should I just drop a comment just to say a simple thank you? Still you are thinking just look at you. Oh man. Okay. So first of all, we are just trying to just write our first query. Okay, what is that first query? It's very very good and you will actually feel really good if this is your first SQL query because I still remember like when I ran my first SQL query when I was in school. I was in 11th standard. I was like wow man it's so cool. But I was not using this UI because our school was top level school and we were just using command line interface. Okay, you can also use command line interface. There's just like you just need to so in order to use command line interface let me just tell you not relevant to this video but just for your knowledge so usually your SQL server is installed at this location let's say program files and then inside that will be my SQL something like that within that you'll be having SQL server okay within that you'll be having bin because inside the bin you will be havingexe file which is the engine for you. Okay, exe file. If you just hit cmd command line prompt in that particular location, then you can just run anything within that particular thing because this is the same thing. See this UI is the same thing which is actually connected to your this exe which is like server. Okay, this server is open to this port 3306 and this is listening to this particular database. That's it. again not relevant to this thing but yeah nostalgia so I have thought of just revealing it to you so our first statement I will simply say select okay for select not select for select so first of all let's say I have these tables I have these tables okay now I want to query this table which is called dim customer make sense I want to query this dim customer okay I want to query this dim customer makes sense. So how I can just query this table? Query means I want to see this data. I want to see this data. How this data look like? It is very simple. There's a command called select. Okay. Then we write ax. Then we write from like from which table I want to read the data. I want to say dim customer and then semicolon. This is a table name. This is the aix. Aix means when you just want to read all the columns. Wow. You can simply run this command and see here you have the result. Here you have what? Result. Let me just run it one more time. Here you have the result. I have customer ID. Okay. I have customer ID. I have customer key, customer ID, first name, last name, gender, email, phone number, country, state, city, join date. All the information is here. And do not try to dial these number. These numbers are pseudo numbers. Do not like hey let me just dial these numbers. Um uh Kelly will be just picking the call bro. Hold on. Hold on. Okay. So this is basically the data. You will say an Lamba um let's say I want to just read only few columns. Is it possible? Is it possible? I do not want to read all the columns. I know like if I just want to read all the columns I can just use ax that is fine but let's say I want to read only two columns such as customer ID and email how I can just do that so simple you will simply say select okay then you will write the column name that you want to pick I will say customer ID okay then you will say comma comma and then email okay second column name then space then you will write from then from which column you want to read I told you SQL is just English okay so I can run this command and I will just only run this command only so I will select this thing and run it see now I'm getting only two columns very good now let me just introduce to a concept called indentation okay this is not a concept this is the thing that makes you a better developer indentation when I was in my school I used to write the whole code in one line and I was like what's what's wrong with this I can write the whole code in one line so it's your responsibility to read the code just read it because my class was like an what are you doing writing the whole code in one line it's so it it makes me like so so so difficult to just read your code I was like so now you need to understand my code it's not my fault so I know that we can just write the whole code in one line it's fine but we do not do that it makes your code look messy if it is just like one line of code it is fine but imagine you are writing 50 lines of code 50 lines of code bro 50 lines of code is nothing in SQL and you'll be writing all the all all that code in this particular course okay so the indentation means you simply write like this the same thing let me just show it to you see now just tell me one Is it looking better? Obviously, it is now much organized. I can clearly read how many columns do I have. The from statement is distinct. Okay. This from table is like highlighted. So, this is a better way to do it. Okay. Select then enter then add a tab then column name then same thing. Then if you just want to use from then go back and like this. Initially it will be a little pain for you but with the course of time you will become a pro because I started my programming world with C++ and in C++ you can just write the whole code in one line okay because you use semicolon to um make a break but in Python you cannot do that. So when I was just switched to C from C++ to Python I was like what what's what's wrong? But then I realized like Python is so so so cool and you have to use indentation here because my class teacher was like an un an my computer my computer science lectures were like all about an were all were all all the classmates okay all the classmates used to wait an will say something and teacher will scold him and okay then we will laugh. I was that particular student of my CS computer science subject only. Okay. Because I took uh non-medical in 11th and 12th. So it was not the case in mathematics, physics, chemistry and English. But yeah, it was just the case with computer science only. Okay. Okay. Okay. Okay. Okay. But it was fun. It was fun. Okay. So the thing is this is your first SQL select statement that you have just done. Okay, just the select statement. Now we going to just level up slowly slowly and it's not a big deal. See it's just with you that how you treat SQL. It's not like very very very tough. It's very very simple. Okay. So now within this file we going to learn something called as wear clause and it is very very important. So so far what we are doing we are simply fetching all the data like this. If I run this I'm getting all the data with this command I am returning only few columns but in both the cases I am returning all the data. All the data means all the rows right and currently we have limit to 100 rows that means um we are simply getting only 100 rows. If we have more then obviously it will be truncated automatically. Okay. So currently we are just seeing 100 customers. If I say don't limit and if I just run this one more time, you will see much more than 100. We have total 500 customers. Okay. But applying a limit is a good practice. It saves your compute resources and it's a good practice. So perfect. So currently we are just returning all the records. Let's say we do not want to return all the records. What does it mean? Let's say I I I I do not like returning all the records. I just want to return let's say top 100 records the same thing that it is doing limit do you know what we can also do it the do the same thing using code let me show you let's say I do not even want to return 100 records I just want to return 10 records you will say anala do you know what you can just go there and you can just click here and if you just run this you will get 10 records do you know what I do not want to use this UI I will say don't limit and I can use code to do the same thing. Let me show you. There's a command called limit. So if I say limit 10. Okay. And if I just run this I will see the same result. If I say limit 20. Oops not 120. 20. Same thing. See 20 records only. Then let's say 15. So this is the you can say your code version of this thing. Okay. So you can simply say limit that's a better way to do it. And obviously that's a better way to just learn as well. Okay. Obviously UI is there but not every workbench will give you this particular feature. So you should also know about this feature in the SQL as well. Make sense? Limit. Make sense? Okay. So this is also done. Now we going to learn something called as where okay you need to just make notes quickly because we going to start from scratch and you already know like we have literally started from scratch now we going to level up okay okay but don't worry we'll be just covering all the things literally all the things so now it's time to cover where clause where clause is a very much in deep and we need like dedicated lecture basically dedicated section in this video that is why in this video we have dedicated section for where because where clause you need to first of all understand the basics then you'll be just integrating where clause with so many things in short where clause is for your let me just add it here limit and this is just for your notes so that you can just keep this particular SQL file with you it's a kind of cheat sheet that you can just use to revise your concepts okay good first select limit and then wear clause basically what is this wear clause where clause means condition where clause mean condition whenever you see where that means condition we need to apply a condition can be any condition any condition let's say for example let's say you are using excel sheet okay you just want to find the email id for Samuel right that is a condition let's say you want to find all the customers in the city called uh bang Bangalore that is a condition let's say you want to find the customers all the customers in let's say uh California that is a condition let's say you want to find all the customers who are female that is a condition so any condition any condition will be your wear clause do you know what you will master SQL with the course of time with more and more practice that's a fact but you know what you need to imagine SQL what what what what so basically wait you need to imagine SQL person and I'm not talking just about interviews. This is true for interviews and your day-to-day requirements as well from your business managers, business people. You will directly get the requirement. Hey, whatever your name is XYZ, I want to find all the customers in this particular region. They will not tell you, hey XYZ, um can you please tell me how many um customers do I have where clause equals to this? Now you need to break down the requirement. Okay, you need to break down the requirement and you need to understand what part of the requirement is my condition, what part of the requirement is my data, what part of the requirement is the grouping. You need to do that. That is the skill that no one is talking about. SQL is not just about writing SQL. SQL is understanding the SQL first, breaking down the requirements into subsql commands and aggregating together. That is SQL. That is SQL. When I was learning SQL, I used to think like okay, I know SQL. But if someone asks me that, hey, I want to do this. How I will know that I need to write this thing in my where clause, I need to write this thing in my group by clause, this thing in my select clause. And I am so sure that you are having these questions and do you know what all of these question will be gone all of these questions because I have amazing case studies that we going to just cover in this particular course and you will understand like what I'm trying to do. So each requirement basically each clause where clause group by all those you want to master from the you can say requirements perspective. So I will make an environment that I am the manager. I am asking you to do this and you need to think like that. Okay. I know that I would need to do some extra efforts but anything for you my lovely Dam anything for you. Anything for you. So okay let's try with our first environment basically first that requirements environment. Okay let's do that. So bro so bro so bro where were we? Where were we? We were at where wow so we were just discussing about wear condition and let me just tell you more about wear condition so we were talking about let's say we want to prune my data wow fancy word prune basically let's say pruning is very very technical term basically I know it's an English word I know it's like very commonly used but in the field of data you will hear this word very frequently okay so pruning means when you just want to filter out your data When you just want to apply the conditions on your data in simple language where conditions are like applying filters. Okay? Applying what? Filters. Okay. So what type of filters we are talking about? Any filter. Let's say first of all let's try to query this data for one more time. Okay. Why? Just to see the requirements, right? So let's say now you want to prune your data. Why I'm using this word again and again because you will be familiar with this word and you will not actually feel awkward while sitting in the interviews right the person will say hey how can you prune this data on based on on the basis of this filter we will say okay let me apply very condition so let's say person says I have this data okay and you want to prune this data and I want to see only those records which have have the gender female or male anything. Okay, but for now just for this example let's say female how you can just do that. So let me show you. So you will ask do you want all the columns or specific columns? The person will say all the columns. Okay, now you know you will simply write select a for all the columns and from which table from dimension and then whatever your col table name is. Table is dim customer. Okay, what is this? Okay, dim customer. Now, now comes your wear clause. You will simply write where. What do you want? What is your condition? My condition is I want all the gender equals female. Make sense? Make sense? Now you will find any column relevant to gender. So I will find it here. Make sense? And I will apply filter on this particular column. Okay. And this is the same way that you apply filter in your Excel sheet. You go to that data tab and then you just hit that filter button that you just apply the filter. This is the code behind it kind of. Okay. So you'll simply say where gender which is your column name equals female and female is a string column. So you will simply add double quotes or single quote like this. The moment you write this code, let me just run this. You will only get the records for female. See now I'm getting data only for gender equals to female. I'm not getting any male here. Wow. Make sense? Okay. This is like very basic example. Okay. And we going to just level up step by step. So do not feel like okay this is the only way. So we will be just adding more and more challenges. Okay. So let's say what do we get other than that? Let's say I have more customized condition. For example, I want gender equals to female for sure. But at the same time I want to get the records only for the country let's say Bhutan. Okay. Let's say any country. Any country of your choice. Okay. Any country. For now, we can simply pick let's say Bhutan. Okay? Bhutan or let's say any any country, bro. Any country. Qatar. Any country. Okay. Or let's say France. Okay. Let's say France. So how I can just apply multiple filter? You will say um Anama can we write this again where clause? No. No. You can only write one where clause in your whole SQL statement only one. Okay. So now how we can just do that? Just guess guess. Answer is you can add and clause within the wear clause. So that you can just add multiple wear clauses. So I will write something like this and country equals let's say France, right? France. So now let's run this code. Now let's see what happens. Perfect. I just got the data for France. That's how you apply condition. Now, here's a tip from Anlamba. What is a tip? Um, we added two conditions here, right? So, it is a good practice. I would say the best practice. I also learned from my um class teacher. Yeah, that same teacher that used to scald me a lot. But yeah, uh that was a learning that I got from her. And actually, I didn't get this knowledge from anyone else. And that knowledge actually helped me a lot to find so many bugs in my day-to-day activities. Exactly. Like one small change, one small you can say modification in your code actually save you from silent bugs, silent errors. And trust me, you will not find it in your small queries, but in the bigger queries, you will definitely see it and it will be next to impossible to resolve it. Trust me. So just make this practice an Lamba. What is the practice? So whenever you write conditions, whenever you write conditions, make sure you enclose those conditions in the braces. If you're using multiple conditions, makes sense. If you're using one, it's fine. But if you are using multiple conditions, always enclose them. Let's say like this. This is condition number one. This is condition number two. This way, it will never be throwing those silent errors. This is the best practice. I follow this in Python. I follow this in SQL as well. Okay, just make this habit. You will definitely see you are writing efficient coding and you are actually writing efficient SQL queries as well. Trust me. Okay, so let's say let's run this. Obviously, we should see the same result. Perfect. This is just the wear condition. Make sense? Now, now obviously you know you can add n number of conditions using and and and and and make sense. Very good. Now let's say let's say you want to apply a filter on date. What do I mean? So here you can see join date. Okay. Let's say you just want to add um basically get the data which have join date greater than 2022. 2022 you do not need to see any data before 2022. How you can just do that? So there you will be using operators. Let me just show you. I will simply say and join date greater than and my date is 2020 2 0 1 01. Make sense? So this is my condition. Let's try to query this. You will see that I will only get two records and 2021 record is gone. So that's how you use the greater than and less than signs with your date conditions. Make sense? You can do the same thing with your numbers as well. Let's say you just want to get customer key greater than something. So you can just do that as well. So there is a lot of flexibility and very understood by looking at the code greater than equals to less than and. So it is like SQL is almost like English. Okay. Plus SQL is English plus visualization. How you need to visualize whatever you are writing. If you cannot see your data in your mind that what what will be the output, you will feel very difficult to learn SQL. I'm not saying that you cannot. I'm saying that make a habit of this as well. Whenever you are applying a filter, okay, this data will be pruned like this. Okay, this filter will be doing this thing. So, you need to actually see the data being changed. Okay, perfect. So, this was our wear clause. Now, we need to just go one step ahead in the wear clause. Okay, so what I can do, I can simply say one. Okay, now what is one levels? Basically, let's say level two. What is level two? Let's add some difficulties. Okay, step by step. And actually this is just like wear clause. And now if you just want to see um anything more advanced, we can just simply add more and more things. Okay, so basically this is your and condition. Okay, so what I can do, I can simply change this code. So let me just do it for you. So let's say one is this one. Second should be this one. And this should be removed from here because first you should learn only where clause then you should learn with and okay and this is chapter two and I will simply say and and or let's learn or as well okay now you want to do one thing you want and here you will see the advantage of those brackets okay I want gender gender gender equals to female for sure for sure this is my have to have two condition then I need country either equals to France or join date greater than 21. Listen to me again. So I will get all the female records for sure. Now I want that country can be France that is one condition or not and or this joint date is greater than this one. So that means if one condition is correct I will get the record. So let me just show you while running it what will be the result. So you will see all the gender equals to female for sure. Then you will receive all the records which will be having country equals to France or data is greater than 222. Now obviously I will run this code then I will just show you that what I asked you to do. Okay. So let me just show you. See I have female female female female female. But why did I get male? Why I got male as well? But an Lamba this should be the condition, right? This was the first thing gender equals to female. Okay, then we want and and only or between this, right? Like this is fixed condition like I have to have to get gender equal to female. I cannot receive male that is for sure according to this condition. But we need or condition between this and do you know what is happening right now? This is called silent errors. Nothing is wrong in your code. Nothing is wrong in your syntax. No, but it is still failing. You are not still getting the desired output. Why? You know the reason why you need to add one more brace here like this. Like this. What will happen? Let me just show you. Let me just show you. See now you are only getting female. Why? Because you are saying hey SQL I have two conditions. First condition is this one. gender equals to female for sure. Okay. Then and second condition. Then within the second condition, I have a choice. Either of this can be true. This is non-negotiable. But this can be negotiable. Like this can be negotiated based on these two conditions. Make sense? But earlier when I didn't add this brace, so what it was doing, it was actually not following the rule. It was simply saying, hey, gender equals to female um or this thing or whatever thing. So it was not actually giving you the desired result. So you it's your responsibility being a developer that you need to add the braces. You need to categorize your conditions. Make sense? So simply add like this. And this is a perfect example of the error that I was just talking about. Make sense? And do you know what? If you do not know about these things and if you are just being asked, hey, write this SQL query in the interview. You will write the SQL syntax. Everything will be right. But you will not get the desired output. You will be like what's going on? What's going on? What's going on? You are already nervous. You are already tensed. And on top of it, you are not seeing the desired result. What will happen? You know what will happen, right? So, I don't want that to happen. So that you can learn everything beforehand. Just be prepared. Makes sense. So, this is also done. Makes sense. Then what do we have next? Next, we going to learn about Next to like operator. This is one of the most popular operators that we have and which is like mostly used. We use this operator a lot in day-to-day activities. So what this operator does? Let me just show you. Let's say let's say your manager is coming to you. Hey Mr. Mrs. Miss XYZ. Okay. Person XYZ. Forget about the Mr. Miss Ms. Okay. Person XYZ. Okay. Um, I want all the customers whose first name is starting from let's say S. Let's say S. Okay, make sense? How you can just do that? You will say bro. Um, we cannot use this. We cannot use let's say I I cannot write like this, right? I cannot write like this. Let's say select ax. Select ax from table name. Table name is um dim customer where first name equals because I do not have equals to s. I want all the name which are starting with s which are starting with s. Okay. Now do you know what here comes the like operator here you do not need to use equals to sign here you will use something called as like you will say like so whenever you use like you need to give a pattern pattern looks like this I want t as the first element I will write t okay then what will be the next element or what do I need next anything because the condition is I just want first alphabet to be t after that it can be anything so I will simply say percentage that means anything. Let me just close this pattern and let me just run this. It will give me all the names which are starting from T. Right now you will say I want all the names. Okay, I want all the names. Let me just add one. Okay. Now let's say you want all those names which are starting with T and ending with Y. Can we do that? Yes. What? Can we do that? Yes, you can do that. How? So, I will simply copy the same code. Okay. And here you will simply write what? Just make a guess. What should be the you can say code? Just make a guess. Can I try this? Let's say like this like will it work first of all? Let me just add a semicolon. If I write like this, why it worked? See, just just try to read it, bro. It's so simple. Just try to read it. It is saying the pattern says first alphabet is t then percentage means anything and then y at the end. Simple simple simple simple right? Wo that was good. Um let's say I want to say that I want all the names which are starting with T and then um after two alphabets I want F. Okay. Like this is very complex one. Okay, let's try this. First letter is T. The last letter is Y. And after the first alphabet, I want two alphabet can be anything. Okay. So the fourth alphabet will become F. Fourth alphabet will become F. So it will be looking like this. Let's say T 2 can be anything. Then I want F. Then I want Y. Let's try to run this. Let's see what happens. See, I got Tiffany. Why? So first alphabet is T for sure. Last alphabet is Y for sure. And after two alphabets I and F, I want fourth alphabet as F because in Tiffany there are two F. So I got F. That's how you can just solve any questions related to patterns and I think that hope that makes sense. Make sense? Very good. Good. So these are two underscores 1 two. So T first alphabet then two alphabets any any two alphabets but just two not more than that then F then anything and then Y at the end. See that's how you just solve related to patterns. May make sense. See it was so simple. So simple. So now like operator is also done. Now what's what's next? What's next? Like what can we do? Like we learned where clause with multiple columns. We learned where as well. We learn everything. Everything. Everything. Bro, SQL is never ending subject. Okay. Never ending. You will explore new new things every single day. Okay. SQL is like that particular subject that is never ending. That is the positive thing. That means you cannot feel like you do not know SQL because no one knows SQL completely. No one. I do not consider myself as a master of SQL. But I consider myself who loves working with SQL. You need to have this attitude because SQL is the backbone. Okay, make sense? Sorted. Okay, good. Now we need to look at some data. Okay, and now we're going to learn about sorting because this is like again very common request. Okay, sorting. How you can just sort the data? Let's first of all query the data. Select aster from where is indentation. Bro, this is just a pseudo code oneliner code. Please, please customer okay so let's try to run this code and we got this data now do you know what I want to sort this data sort this data on what I think this is not a good example let's take an example of dim product okay because that makes sense when we just want to sort our data based on some numeric columns right so let's say this is my dim product and now I want to sort my data sort my data Okay, I want to sort my data based on the unit price so that I want to see uh the most expensive product in my catalog at the top and the most cheapest one at the bottom. How I can just do that? It is very simple. I will simply say select a bricks from okay any table this time it is dim product where where no nowhere nowhere we just need to write order by okay order by and you need to add space between order by order by which column you will simply say column name unit price make sense? Now if I run this code, do you know what's the issue with this? There's no issue by the way. This is a default behavior. So this will simply sort my data in the ascending order. See the cheapest product, it is coming at the top. Okay, 11.82 price. But if I want the reverse of it, you can simply write D. That's it. That that means descending order. So the default behavior is ascending order and we have to change it to the descending order. So now the most expensive one is this one which is of 999.04. Make sense? And you can even extensively add a c to just call it as ascending order. That's a good practice by the way. It makes code more readable. Okay. Make sense? Good. So this is your sorting. Sorting. Sorting. Sorting. Make sense? This is your sorting. Okay. Makes sense. Makes sense. Makes sense. Now you have a very good understanding. And now we going to talk about one scenario based question which is like very good to know but we will just learn the efficient way to do that as well. This is like um applying a patch applying a workar around for that particular question but you will just learn about this question using window function at the end of this video. But for now let's say you got a question in the interview that hey I want to find top three most expensive products in your catalog. So what is the way for that? You can simply write something like this. Let's say select a from this order by this and then you can simply say obviously D S that means in the descending order then you can simply say limit three. So what will happen? You will simply get three records in the descending order and these are the top three products. Okay, this is one way of doing it. Not the best way. We should ideally do do this using window functions because this is this works well when you just want to find top three, top five, top 10 or bottom three, bottom five, bottom 10. But let's say interviewer asks you hey I want to find the product which is at the fifth position in terms of price fifth position not top because top is very easy to find okay fifth so for that we use window functions okay so don't worry about that bro that is advanced concepts and you are still learning the basics it's fine that's how you grow step by step okay so this is sorting okay that is done and we also covered one question okay that is also good because you'll be just covering scenarios as well so you need to make sure you are just taking notes Now what we need to do? We learned all these things, right? Yes. Yes. Yes. Yes. Now you also need to learn one more command and after that your basics will be covered. Really? Yes. Your basic session will be covered. Then we'll be just covering about the manipulating the columns, adding functions, adding conditions in the columns, adding you can say all those things. Okay. All those things. And before that I want to add one more command. So two commands are left and after that you will be done with the basics. Make sense? Only this is the thing in the basics. Obviously bro SQL is not like you need to spend let's say 30 hours, 300 hours, 3,000 hours. No, you even need to spend I would say 300 days. That is still less. But what is the right way to do that? You simply need to get started with it and start using it. more SQL you will use more challenges you will face more challenges you will solve more proficient you will become okay so for you this is the basic after obviously two commands then we will just covering the intermediate steps then advanced steps then ultra advanced steps ultra advanced steps bro I will try my best to cover all the things okay all the things which are necessary for you to get started and to make you um you can say capable enough to solve or to tackle any situation in SQL. Any question in SQL in or you can say any query in SQL make sense? Okay. So the next thing is alias. What is alias? So currently whatever we are quering we are simply using the default column name. Yeah. So what do you mean? We can change the column name obviously. So let's say I query like this select. Let's say I just want to get product key, product ID and product name and category from this table. Okay. So let me just write product key. Boom. Boom. Product key, product ID, product name and category. Okay. From this table product makes sense. If I just run this code, I will simply get this thing. Now let's say I do not like this name product name product name. What is this? I want to use proper naming. How you can just do that? Let me just show you. You can use alias. And how do you use alias? You can simply say product name. Whatever the column name is. Then as how you want to call this? I will simply say I want to call it as like this. Product and name. Let's say like this. Okay. Only this column. Then simply run this. And this is now renamed as product name. See there's no underscore now. Now let's say you want to add a space between this. Let's say like this. So you cannot add it like this. You have to use single quotes. Okay? Because it will be very confusing for SQL like why did you add space and all. So now if you just run this you will see product and then space and then name. Okay. That's how you can just write any column name anything product key product ID anything. That's how whenever you are just building some data for business you do not use a default column names because obviously it is very ugly. You just provide the business names to your columns. Make sense? Good. So this was about your alias. Now what is the thing that we are going to cover? It is it will require a little bit of focus. Okay. So just listen to me now. Now we are just covering grouping. Okay. First of all, let me just show you the data. Select axis from dim product. Dim product. dim product. Let me just add semicolon dim product. Now this is important, okay? Because once you understand this thing, you can actually master so many things. So this is the last concept of basics. So just put a little bit more focus and then you can just celebrate your one milestone of covering SQL basics. Simple. Very good. So now this is our products table. Okay. This is the unit price of everything. Yes. This is the category. Yes. Do not look at the data for now. Just look into my eyes. Okay. Carefully by do not look into my eyes. So, so I'm just looking here. So, now I will ask you one question. The question is I want to find the average price per category. Is it a genuine question? Is this a question that you can just expect from any business um person? Like let's say your company has 10 number 10 10 different categories and there's obviously different different pricing. So what is the average price for let's say category clothing? What is the average price of clothing? This is a very valid question right? How you can just find that? You will say bro I have to just do my mathematics in 1112 standard. I have to use integration differentiation. No you do not need to apply all those things. No nothing. You do not even need to apply normalization and all those things. normal distribution do your statistics no nothing it's again English really but here you need to visualize the data otherwise you cannot understand this okay first of all what do you want I want each category very good visualize this let's try to draw it okay okay so let me just draw you first so let's say you are visualizing this okay and I hope you are visualizing is you're not visualizing anything else. Let's say this is you. Okay, make sense? Okay, very good. So, let's say you have this data. Okay, so what do you want? You want category? Okay, makes sense. And here you want let's say average price. Okay. Of each category of each category. Make sense? So let's say category is clothing. Then you have sports and so on. Right? And now here you want to find the average price. Let's say this average price is 100. This average price is let's say 150. Make sense? So how you will reach to this particular step? How? Let's think now step by step. Obviously you need this column category to display the numbers. Oh sorry to display the categories. Perfect. We need to select this column. Then you need unit price as well because that is the column that will be used to find the average. Makes sense? But now if you closely observe, let's say you have this data only. Let's suppose you have only this data. Okay, you have three rows for clothing. 1 2 3. Do you want three rows for for clothing? No, only one. Okay, that's good. So, you just want one row for clothing because you just need to combine them. But, okay, so now you have condensed those three rows. Make sense? Now, there's only one row. So, you have just squeezed these three three rows to one. Okay, can you imagine these three rows to one? Okay, you squeezed it. Okay, like a sandwich. See? Can you see my hands? Let's say there are three rows. 1 2 3 you squeezed it to one. Now obviously if you squeeze these three rows you also need to squeeze these three rows. Make sense? Common sense bro. Because that is the part of that row. So now while squeezing the unit price column while squeezing the unit price column you just need to apply one function called average because you are simply telling I want category column. Okay. And I want to apply the squeezing on category column. Make sense? And automatically other columns will be squeezed. But now this time you will tell the C like squeezing strategy. You will say bro squeeze it to one row and just find the average of it. Can we just change the strategy? Yes. Let's say you just want to find the total. So you can simply squeeze it and just find the total. Let's say you just find the minimum value. you will simply squeeze it and find the um maximum value or minimum value anything make sense hope I hope I'm making some sense okay so let's try to write it down then you will understand better so first of all I want select category okay for sure then I want unit price okay unit price from dim product makes sense now I will use squeezing I want to apply squeezing grouping on column what? So I will use group I group by on category. Make sense? So this will be squeezed. So this column is safe because this is the origin of squeezing. Okay. Now what about this column? This is saying bro you said that you want to be squeezed. What will happen to me? You will say bro nothing. Let me just tell you you will be using this function average. Let's say like this. That's it. That is your code. Let me just run this. See, now you are getting clothing average, home and kitchen average, average average, average average, average average, average average. So each record is squeezed on this value and we simply found the average of it. Make sense? Very good. Now obviously this column name doesn't look nice. You can simply add alias. Let's say I want to say average price. Okay, let's say. So I'll simply run this. Very good. Very good. Got it. That's how you do it. That's how you do it. Now, if let's say you want to add one more column. Let's say you want to also find the total of it at the same time. Again, you will simply apply here sum. There's a sum function on unit price. Okay. And I will simply say as total price. Make sense? Total price. Let's try to run it. See, now you are getting the total price as well. See 47684 40 4373 41048 36978 40495 41314. Make sense? So you got the total. Okay, makes sense. It was so easy, man. It was so easy. Yes, it is easy. I told you it is easy. Now let's say you want to make it more complicated. Okay, let's call it as like let's say one. Okay, let's add two. What is two and why do we like do it? Like what's so special? I will just tell you. Now the requirement is becoming more and more complex. Now the requirement is you want to fetch only those records which are greater than 4,000 total price or let's say average price. Who average price? I just want to get only those categories whose average price is greater than let's say uh 500. Make sense? Like this can be required. Hey Mr. Mrs. Miss XYZ um I want to find the average price of my categories each category and actually do one thing um just filter out the records which are greater than 500 because I just want to see the categories which are above 500. That can be a requirement in that particular scenario. will say bro I can write this code this code to achieve this result now in order to filter out the records I have learned that I can use wear clause perfect you are right you are absolutely right you are absolutely right can I use where clause here no bro why so let me just tell you hold Hold on. We use wear clause only with the columns which are already available in the table. What did I say? We use where clause with the columns which are already available in the table. And to all those who are pro-SQL developers, we are not talking about subquery. So just sh okay. So we use where clause with the columns which are only available in the table. Okay. Is average price available in the table? Yes. Is average price available in the table? No. You calculated that column. It is not available. Huh? Make sense? So whenever you are calculating something using grouping using group by you need to use a pro version of wear clause which is having which is having. So now you will simply write having okay having average price. Okay average price greater than let's say 500. Let me just run this and let me show you what will happen. So let's run this. This will just show you the desired result. How? Because here it knows how to apply the condition. Because here you are using the calculated column. Okay? That's why you're using having. You're not using where. You cannot use where because where doesn't even know that this column exist average price or total price. No. No. Make sense? I know this was a little bit tricky. What you need to do? Revisit this part of the video again and again. Practice this at least 10 times and that's it. Simple, simple, simple. You cannot expect everything to be like understood in just one go. No, no. You are not a superhuman. Okay? You are not a machine. Okay? You are not an LLM that I will just train you one time and you will just answer correctly. Okay? Even LLM cannot do this. Okay? So now, did you understand what I say? What did I say? Okay. So, make sense? Did you understand that thing? Let me add enter. Okay. Make sense? Now, now I can say that your basics are completed and we going to end the basics of SQL with one concept which is called flow of execution of SQL query. What did I say? Flow of execution in the SQL query. Like how flow of execution works. What is a flow? Whenever you write the query, what is the flow? Let me just show you. Oh, what is execution flow? Very very simple. I don't know what very very complex plus very very important. This will be the question in your interview for sure. Forget about interview. Okay, if you're becoming a good developer, when you understand the flow, you can write better queries. Really, trust me, just understand the flow and you'll be able to debug the queries better. Trust me and this is the outlier really not everyone focuses on this part whereas this is the backbone of everything because what is execution flow this tells you how SQL engine is actually processing your queries when you understand that thing that means it is a backbone right you should know how this is processing your data so let's say let's try to write one simple demo query and you will actually understand it so let's say let's say oh Let's say let me just write one query. Okay. So let's say I want to write this query. Let's say select. Okay. I want to select um product ID or let's say a category. Okay. Category and average of unit price. Okay. Make sense? As average price. Perfect. from from dim product make sense very good I will add one where clause as well let's say country do we have country in product um I doubt but let's imagine we have country as well for like from which country we are importing this product you can just imagine this okay because I just want to explain you that into an example let's say we have country as well then I will simply write group by and group by will be on let's say category and having um let's say average price greater than 500. Make sense? Let's say this is the query. Now, Mr. Mrs. Miss XYZ, what is the order of execution? I'm Lamba. Oh, come on. This is so simple. Let me just tell you. So, first of all, select statement will be executed. Then from then where then group by then having what if you say this in your interview will just say one thing bro you can just cut the call and that's bro this is wrong this is wrong because that is not the execution then what is execution do you know what is execution let me just ask you this question you have a table perfect let's let's say you have an excel sheet I know you are a big fan of Excel sheet. I am not. Let's say you are an Excel sheet fan lover. Fan lover. What? What but what are you saying, bro? Let's say you are a pro Excel sheet developer. Excel sheet developer. Yeah, kind of. Okay. Let's say you are that person. I will say can you just tell me that how many records do I have for country equals to France or just tell me the France records. What is your first step? You will say I will open the Excel sheet. Perfect. Stop there. Stop. Stop. Stop. Stop there. That's what I just said. Your first step is what? Open the Excel sheet. Your first step is not selecting the columns. Right. So, same here. You first need to select the table. Basically, read the table. Right? This is your step number one. Oh, okay. Because obviously, if you just want to fetch any data from the table, you first need to go to the table. That's what SQL engine does. Step number one. Then second step will be pruning the data. Let's say you want to apply some filters so that your data size will be small. So you will simply first of all filter the records. Why? Why you are so much in rush that you want to apply the filter. Why? Because it will save the computation. It will make your query runs faster. Why? Because I first of all go to the table and I first of all remove the unnecessary rows. Now data size is small. computation will be less. Common sense. Common sense. Okay. Then then it will go to group I then it will simply apply the groupings. Make sense? This is your step number three. Okay. Grouping like this because group by is on column. Perfect. Three. Make sense? Once it has applied group by then it will go to having. If we have any kind of conditions on the calculated columns, right? Yes, this is your four. Then at the end it will go to select statement at the end when you just need to fetch the columns. That's it. At the end. Okay. Let's say this one. Oh, okay. Makes sense. Makes sense. Okay. And if you have let's say order by in your basically sorting then the sixth step will be order by okay order by and seventh step will be limit if you have used limit as well make sense now if you have this question in your mind that means you are really really understanding the SQL and you are on the right track. If you have this question, let me still give you 3 seconds to think about this question. Okay, time up. So let me just talk about that question and let's ask yourself did you get this type of question? Anything similar to that? So the thing is just see here I am using number four here and I am saying so first of all I'm going to my table perfect. I pruned my data. Perfect. I applied squeezing on category. Perfect. Then I'm saying having average price greater than 500. What is average price? What is average price? Average price is not in the table. It is coming from here. But this step is not even executed right now. This is number five. How this is working then? This is the power of MySQL. So this will not be the case with all the other SQL servers. What did I say? This is only possible with MySQL. This will not be possible with MSSQL. Why? Because every SQL server server behaves differently during runtime. So here, yes, this fifth step will be executed afterwards. But it can just look at this column like how it is being created and it can just provide that data here. But if you're using any other SQL server, you have to write this logic. this one. This one instead of just pointing the column name, you will simply write average of unit price greater than 500 because it doesn't have executed this particular command before. This can be a tricky question and this can just hook you in the interview. But now you know the answer. Now you just need to wait for this question. Make sense? Very good. So that is why if you use the same code, if you write the same code in MySQL, it will work. If you write the same code in MSSQL server, I doubt it will work because it will not execute this thing automatically. No, that SQL engine cannot handle this. Okay, so that is why every SQL server is different. Maybe it will not work in postgrade. Maybe it will work. I'm not sure. But I'm just telling you the reason if it will not work. Make sense? This is the power when you understand the execution flow. Make sense? Very good. Now you are done with your SQL basics. Okay. Now we're going to jump to the to the what to the intermediate step and the first step that you need to master is joins because everyone is afraid of joints but let me just promise you you will love joins after this particular section of the video. Okay, you want to master all the joins that we have and then we'll be just covering all the functions, date functions, conditionals, unions, all those things. Make sense? Very good. Let's do it one by one. Let's first of all focus on the joints. Okay, let's see. So bro, now it's time to master joints. Okay, are you ready? And I know you'll be worrying about hey what are joins? Is it difficult? Is it what? It is very easy. Let me just tell you what's that. Basically joins are very very simple and let's go to the next slide. First of all, let's try to understand what are joins. What are joins? Okay, let's try to understand this. So joins are basically when we just want to apply join between two tables. That means we need to join two tables. Simple. By definition, it is okay. So let's take an example. Let's say this is your um orders table. Okay. Let's say this is your orders table. Make sense? And this is your customers table. H okay. And now these are two separate tables. Now you want to just create one table. Okay. How you can just create that table? Let's say you just want to create a resulting table which will be the combination of both. You need some columns from this table. You want some column from this table. So that is called joins. When you just want to join two tables. One quick question. Can we apply joins between multiple tables? Yes. Okay. But you need at least two tables to apply the join. Make sense? Simple sorted. How you can just apply joins? We'll just talk about that. Don't worry. But this is a concept that are about joins. Got it? Very good. Now, now we need to talk about the technical terms in the joints like what are the types. So, let's try to understand it by doing it. Okay. So, first of all, let's talk about inner join. Now, what is inner join? Inner join means if you are coming from mathematics background, it is equivalent to intersection. Anlama I know you have done bachelors of science and mathematics we have not done that so we do not know intersection bro intersection was taught in 11th standard 11th 11th standard I think in chapter number one if I can remember okay so there's no link with bachelor of science in mathematics okay still let's suppose you didn't take mathematics okay so let's try to understand without mathematics perspective basically let's say this is your table number Let's say this is your table number one. Pink side is your or um orders table. Blue side is your customers table. Make sense? You have some data here. You have some data here. Inner join means when you have data in both the tables that means you have some records here. Okay, obviously some records in this table and some records in this table as well. But there are some records which are in both the tables which is the inner part like this part where it is where it is overlapping this overlapping part why I'm drawing it in live mode so that you can understand it feel it better okay just for you so this overlapping part is your inner joint this is your inner joint which is common in both which is common in both Very Make sense? Understood the concept? Just tell me this thing. Understood? Now let's go to the basically the table concept. Okay. From the table point of view, let's say you have these two tables again. Same tables. We're going to use same example so that you can just understand. This is basically you can say um orders table. Okay. This is the customers table. Make sense? Make sense? Okay. We have order ID, we have customer ID, we have price, we have customer ID, we have name and email. Make sense? Let me just add a heading for you. So let's say this is orders and this is let's say customers. Make sense? Now first of all in order to apply join we need a joining key. Now what is that joining key? Just user common sense. The answer is customer ID. Customer ID from orders table and ID from customers table. Make sense? This is the joining key between both the tables because you need a joining key joining column which will combine it in MSXL. In MSXL as well you would have applied XLOOKUP VLOOKUP hookup you apply on a basis of a key right or a column. Same thing here as well. We need a column. Okay same thing. So let's say this is our joining column. As per the definition, as per the definition, I want to apply join between two tables. So what should be the result? Result should be look like this. I will just tell you the result and you can just agree with me. Okay, result will be like having records 101 and 201. This record, this record, this record and this record. Make sense? Agree? Okay. And we can just add the other columns as it is. Okay. We have order ID, we have price, we have name, we have email. Okay. And we'll be just getting the records for that. So let's say this is the thing. And order ID will be one. Okay. Then price will be let's say 1,000. Then name will be love and email will be aa. Same thing with let's say 2. Then 1100 then an cc. Uh I think bb. Yeah. Perfect. This is your resultant um table. Agree. Why? Because 101 is both is in the both the tables. 201 is in the both the tables. Very good. You understood the concept of inner join. This will not be the part of the result. Why? Because 501 is not here. 301 will not be the part of here. Because 301 is not in the other table. So record should be available in the both the tables. Very good. This is the concept of inner join. Make sense? Now the second type of join. Second type of join is called left join. You will be using left join most of the time. Most of the time. Okay. So just try to understand it carefully. Again same example you have two tables orders and customers. This time do you know what will be your result. Obviously you have some records here in this table. You have some records here. Some records are common in both. Correct. But left join means left join means only and only this circle. This one. Let me just draw it. Only and only this circle. This one. That means all the records from the left table no matter if it is available in the second table or not. If it is available, we will count those records because this is also the part of left join. If it is not available in the next in the other table, we will still consider that part as well. So all this area is your left join. What did I say? All the records from the left table. Okay. No matter if no matter let's say whether the records are in the other table or not make sense only these records. Okay, makes sense. But what will happen to those records which doesn't have any data? Strong question. I also had this question. Let me just show you what will happen. So let's see it with the help of with the help of an example. So the thing is what will happen? Um on your left you have orders table again. Let's try to build it like write it orders. You have customers table. Okay, let's use green. Orders table, customers table. Right. Very good. Now this is my left table. Okay, this is my left table. You can just keep any table as left table. It's your choice. Whatever you will write in the from clause will become your left table. Don't worry, we will be just looking at the code as well one by one. First, you need to grab the concept. Make sense? Very good. Let's say you have this table orders as your left table. Again, the joining key column is same. Now the result will look like this. First of all, let's create the order ID. Okay, order ID, then customer ID, then price, then name, and then email. Okay, let's say customer ID. Okay, then price, then name, then email. Okay, makes sense. I will first of all write all the data from my left table. I do not need to worry if it is there in the right table or not. I will write all the data. Simple rule. I will simply say 1 2 3 customer I know I have 101 2011 301. Okay. price I know I have 1,000 1100 1200 simple I will simply write all the data from the left table perfect now do I have 101 in the other table yes good just note down the name love email is aa perfect do I have 2011 good what is the name an what is the email b do I have 301 no worries is null. Do I have 301? Obviously null. So null here as well. This will my result. I mentioned all the data for the matching records and I simply put null in the columns where I do not have the matching value. Simple. Simple. 301 doesn't find any data. 301. Sorry, not 301. 501. So so sorry. So sorry. So sorry. So sorry. 501. Okay. Because on the left table we have 501. Perfect. 501 didn't find anything in this table. We do not need to worry. Simply write null. That means we didn't find any match. Simple. Simple. Make sense? Very good. Very very very good. Now next join type is right join. Right join is simply the opposite of left join. And trust me you will not use right join. Why? It's not worth it. Because right join is just the reverse of left join. If you want to use right join, you can simply create your right table as your left table. Make sense? Make sense? So that is why it is a kind of meme as well that we do not use right join much. We simply use left join. Make sense? So I can just explain you with the help of an example what will happen. So if we just want to apply a right join, what will happen? If we just take the same directions left and right everything will be coming from here 101 2011 301 and 501 will not be coming and 301 will be having null values. Simple simple but you will not be using this thing because we always use left join. It is not a hard and set rule but it is a standard. Trust me it is a standard. We hardly use right join and trust me you can just find any code on internet on anywhere any code repository you will hardly find the keyword right join you will always find keyword left join because that's what we use but you should know about it and now you know perfect now the last join is full join full join means what do I mean by full join full join means I want I want all the data from both the tables. If it is matching, good. If it is not matching, good. If it is not matching, good. I want all the data. Full join. Full join. Full join. Okay, make sense. So let's see this with the help of an example because that's how you understand. So let's say I want to apply a full join between these two tables. What will be the result? The result will be very very simple. So first of all let me just add order ID, customer ID, price and then ID then name and then email. Okay. So let's say, order ID, customer ID, price ID, name and email. Perfect. First of all, write all the records from left table. Okay. Um, first of all, you should start from here because that is a joining key. 101 2011 501. Okay. Now start writing ids from your um right table. Okay. IDs are 101. Oops. Here 101 2011. Do you have 501? No. Fine. Simply write 301. Perfect. Perfect. Very good. Now order ID will be 1 2 and 3. Simple. Order ID for 301. Nothing. I don't have anything. Null. Simply write null. Customer ID. Nothing. Null. Okay. Price. What is the price? Price is 1,000. 1100 1200 for 301 I don't have anything null null simply null okay ID for this null okay by the way it will not be null because we will not be selecting both the columns when I just showing you the code you will understand it but just for the understanding point of view okay name love an for ID equals to 51 I don't have anything null Then lamba email same a bb null cc. I hope this makes sense and I will just ask you to read this result for just 1 minute. You will understand it like what is happening. We captured all the records from this table. Okay, we captured all the records from this table. Whatever was matching, we wrote the result from the other table. What was not matching? simply null. Make sense? Very good. Very very very good. Now it's time to just look at this code. Okay, basically look at this from the code code point of view. And just to make your understanding more better, I will use the same examples. So don't worry, I will just create two demo tables for you. One is orders table. We have order ID, customer ID, price. And I will create customers table ID, name and email. Okay, let's do it. We are here. Okay, let's create a new you can say script and I will simply say joints. Joints. Perfect. So now let's start working with joints. And if you also want to zoom in, zoom out, you can simply hit control and just scroll up and scroll down using your mouse. Okay, that's how you can just zoom in zoom out. Okay, so first of all, let's try to prepare the data. I will simply say create table. Okay, within the ecom I will simply say orders I want to create let's say order ID of integer type customer ID of integer type and price of integer type. Okay, makes sense. Let's do it. Okay, create table order. What's wrong with this? You have an error. Check the manual that. Okay. If I write orders. Yeah, it's fine because order is a reserved word. So, we cannot use it. So, let's use orders. Okay. Now, it is good. Now, insert some values. Insert into orders. Values. Let's say one 101. And then let's say 1,000. 2 2011 then let's say 1100 then let's say 3 501 1200 makes sense make sense let's run this okay perfect now I want to create table for order u for customers create table customers I want to create ID of integer and name of vcar or vchar whatever you want. Okay. Then let's say email of vcar 100. Perfect. Let's try to create this. Perfect. Perfect. Very good. Now, insert data. H. Insert into customers. Okay. Values 101. name love single quotes then aa okay 201 b then 301 then lamba then cc perfect same data Now you can just understand it better. Perfect. Okay. So currently I'm just removing this code because we just need to write the code just for joins. That was for you so that you can just live um code it live. Okay. So now we need to talk about first of all inner join. Okay inner join. If I refresh my database I will see two tables orders and customers. Okay. So what will be the code for inner join? It is very simple. I will simply say select okay a str from from what? From my table number one. Okay. I will simply say orders. Okay. And I will provide this table as an alias. So I will simply say O. O will be my alias. Why I need to provide this? it will be better to use it as an alias instead of reusing this name. Okay, because you need to apply the join condition. So when you need to apply the join condition, you can either write the full table name or any alias. Whenever you just provide the alias, you can just quickly complete your code. You will just see it right now. Orders I want to apply inner join. I will simply say inner join. What is a table with which I want to apply the join? Customers and that area will be C. Okay. Now I will simply say on like on which condition I want to apply the joint. I will simply say O dot customer ID. Okay. Equals to C do ID. So far so good. Okay. Let's try to run this and see the results. Perfect. What did what did I get? What did I get? I got 101 2011 only. Why? Because as for the inner joint definition we should only get those records which are common in both and only 101 and 21 2011 are common in both both. Okay, perfect. Make sense? Very good. One more tip. If let's say you do not want to display all the columns, you just want to display columns of only one table. Let's say from the orders table, you can simply say O.x. Right? If I just run this, you will only see the columns from the O table. Basically, orders table. If let's say you want to add C dot ID only, you can simply say C dot ID. That is another use of using alias. Otherwise, I have to use this customers again and again and that makes code not very much readable. This is better. See, now I'm simply getting ID as well. Okay, but for our example, we'll be using a perfect. So this was our inner join. Let's try to see left join. Okay. Now this is interesting. So I will simply write the whole code again because it is exactly the same. The only difference is here instead of you writing inner you just simply need to write left. Now in llamba what is my left table and what is my right table? How would I know this? Very good question. So whatever you write under from will become your left table automatically. Okay? And whatever you will write after left it will become your right table. Do not be confused. So whatever table you write first that will become your left table. Whatever you will write after that will become your right table. Okay? So it follows this particular pattern. Make sense? So let's say I will write another left join condition to just apply joins in multiple tables. Then this table will become my left table which is like coming first and the next table will become my right table. Make sense? Okay. So let's try to run this thing and let's try to see the results. So you will see the exact thing that I just mentioned in the slides. See we got everything from the left table 101 2011 501 and for the other tables we got null because there's no joining key for that. So that is why null. Make sense? Very good. If I just need to write join. I will simply copy the code. I will simply say write join. Okay. So I'll simply change it. So what it will become? It will just do the reverse of it. It will simply get the data from the other table which is customers and will throw null from the orders. But we hardly use right join. We simply use left join. See because the thing is let's say let's say come here come here come here. Let's say you want to treat your customers table as your left table. Right? That is the whole point of using right join. You can write this table first and then you can just use uh you can just write this table after that. So that is why no one uses right join. Okay, make sense? So this is your right join. Let's try to apply full join. So what do you will do? You will simply write full. Do you know what? Why did I explain all the things first? Why? Because now it is so easy for you to understand the results. So easy. Because now you are seeing all the things. Because now you know what will happen. So let me just run this. And this will throw an error. Why? Why? Because I am using here full. But full join is a valid join. Yes. Yes. But if I just remove this, it will work. But it will simply throw me just join. This is this is like inner join. So this is a quick tip for you. When you do not mention any kind of join, okay? And you simply write join. By default, it performs inner join. Make sense? By default, it performs inner join. But I want to perform full join. Why it is giving me error like this. So you have to try something like this. Full outer join. Will it work? Let's see. Uh how to close this? Okay. From here it will not work. It will not work. Why it is not working? Because the keyword to apply full outer join basically full join is different. And why did I write outer? Sometimes in some SQL servers you have to write this word outer left outer join full outer join right outer join. Okay, you do not need to understand what's that. It's fine. Okay, you simply need to understand the thing that is a keyword. Okay, so why full join is not working? Let me just tell you. So there's a fun fact. My SQL doesn't support full join. What? Yes, but yes, in most of the SQL servers, full joins are supported. Full joins are supported. Okay, so let me just add this comment. Not supported. Make sense? Not supported. So on lamba we have to use join. We don't know we we have to use full join. We have to achieve that result that you showed in the slides. There is a way. There is a way to do that. For that you need to understand one concept called union. Now what is union? Let me just tell you let's try to understand this concept called unions. Okay. What is union? Union is nothing when you just want to combine the results. For example, I write my first query. What query? Let's say I write my left join query. Okay. And right join query. Let's say like this. Let's say I paste this query here. And let me just zoom out so that you can just see. Okay. So let's say this is my first query left one. If I run this, what I will get? I will get the this I will get this result. Perfect. If I run this query, I will get this result. Perfect. And full join is what? Combination of both. When you just combine both things. So what is the way when we just want to combine both the results? How can we combine that? Because this is a different query. This is a different query. I agree with you. I totally agree with you. But that's where the concept of union comes into the picture. So union says I can apply I can basically combine your both the queries. So if you just run this query now and obviously you need to remove the semicolon because this is a part of one single query. So now this query is just acting as only one query. This query is now acting as only one query. See this whole query. So what is this query? Query number one. Okay. Query one, query two. So both having different results. Both are independent of each other. No relation, nothing. But we are simply applying union. That means whatever the results are, we can simply combine it. And this way you can apply union between any number of queries literally any number of queries. So let me just run this. Okay. And you will see this result. And that's exactly you should see for full join. Right? This is your full join. Make sense? Make sense? So both the results are here now. Wow. This is exactly the result that we saw in the slides. You can just go back and check. Okay. Make sense? So that is basically unions and joins whenever we just want to play with the data. Let me just save this file as well. Chapter 4. Perfect. So this is a guide for you. Okay. And this is obviously not supported. So do not feel like hey why my code is not working because this is not supported. And there is a workaround for that using union. How was it? Was it difficult? No, not at all. Not at all because now you understand all the things. That is why. Make sense? Very very good. Now what you need to do because you have learned so many things. You have learned all the basic basic commands. You have learned joins. You have learned union. Now what? Now do you know what? Now you need to focus more on how you can customize the columns. But before that you need to learn some DML commands as well. DML commands. Yes. And then you will just jump directly onto the focusing on the column transformations. Column level transformations. But before that you need to learn first of all DML commands. And I would say being a developer you just need to focus on one command which is sure short sure like very much used update command that's it trust me trust this boy just learn update command and that's it and sometimes delete command as well that is also good but update command and delete command are fine okay you do not need to master every command no you will learn eventually you need to understand the concept and then you will eventually learn and master it trust me make sense very good so now let's try to see what are update and delete commands okay let's so now it's time to just learn about DML commands and DML commands are like very very easy that's why I didn't cover this thing in the beginning because some things were really really important because DML before DML okay so now we can just quickly cover DML commands and let me just tell you you have already used DML command what which command remember you used insert command Yes, that is a DML DML command. Okay, so it is not very very important but the main DML commands that you need to know insert that you already know delete and update that's it. I told you that sometimes people consider select statement as well under DML command but ideally it is a DQL data query language but it can be considered under DML command as well. Make sense? It can be. Okay. So let's quickly master DML commands as well. So let's see. Let's create a new file and let me just write DML commands. So first of all you know about the insert command. Okay, insert command already done. Okay, so I'm just preparing the detail notes for you like this and obviously the PDF for those slides will also be available. So just enjoy. Okay. So, insert command is done. Now, we have update update command. What is this command? Let me just first of all show you select from let's say customers. Okay, let's run this. So, I have this data right now. As the command suggest whenever you want to update your data, you can run this command update. So let's say I want to change the name from love to let's say um let's say kunal any name right any name let's say Sam okay Sam so let's say I just want to update this name update what name for this ID ID equals to 101 right make sense okay I want to do this so I can literally say update table name table name is customers. Okay. Then set. Set means what I want to set. I want to set name equals Sam. If I just do this, what do you know what it will do? It will simply set name equals equals to sam for all the values. We do not want this. We want name equals to sam where where ID equals to 101. That means it will only change the name sam where we have id equals to 101. Make sense? So this way you can write your update command using where condition as well just to make it more specific. Make sense? So let's try to do it. So what is the result? It is oops what is it saying? Um update customers at name equals to s ID. You are using safe update mode and you try to update a table without a where. What do you mean? What do you mean? Do we have ID there? Let me just read the where without a wear that uses a key column to disable safe mode toggle. Oh, I see what does it mean. So it is saying hey bro you just need to turn off that particular safe mode. So it is like there in the MySQL workbin let me just turn it off. Let me show you how you can just also do it. By the way it is mentioned here. It is written that hey to disable safe mode toggle the option in preferences and then go to SQL editor and reconnect. Make sense? You simply need to go to the options in preferences and then you can just reconnect it. Make sense? And let me just add non-key column. Let me just say email equals to aa. Okay. So it will also fail because again you are using safe mode. So it will not allow us to update. Let me just do that. So I basically just change the preferences. And if you do not know how to change it so you can simply go to edit and then you can simply go to preferences and then you simply go to SQL editor and then scroll down. You will see this. This will be checked. You can just uncheck it and then simply say okay. This should be like this. Okay. Okay. And then you can simply save your file first of all. Let's say and then close the connection and then just reconnect. Make sense? And then if you just try it, it will work. See, it worked. Perfect. Perfect. Perfect. Perfect. So this way you can just actually update the data within your SQL tables. Make sense? So now if I just try to query it, I will simply see Sam instead of love. See now I have Sam. So this way you can actually update any data using update keyword. Make sense? Very good. Now this is also done. Now let's say that I want to use delete command. Why do we use delete command? Delete command is used to delete specific records. Let's say I want to delete this record where I have email equals to aa. So how I can just do that? Same delete from table again where clause where email equals to aa. That's it. So what it will do? It will only delete this record. Okay. If I want to delete all the records, you can simply write this command. Delete from customers. But do not write it because it will delete or delete all the records. Okay. So we simply need to oops we simply need to delete only this record. Okay. This worked fine. Let's try to see it. Select a from customers. Perfect. Makes sense. That record is deleted. That's how you just update your data. Basically manipulate your data. That is why it is called DML, data manipulation language. When you manipulate your data, make sense? When you manipulate your data. Okay, make sense? So your DML, DML commands are also covered. Very good. Now do you know what we going to cover? Some column level transformations which is you can say you need to master. You cannot compromise in this area because this is the area where you can flex. This is the area where you can just actually grow. Okay. So this will be a new notebook basically new script and I will just call it as let's say chapter 6. Perfect. So chapter six will be our transformations when we want to transform our data. Make sense? When we want to transform our data within the transformations we have different different transformations available. So I want to just categorize it. How? I will simply put together um all the numeric transformations first of all. Then I will put all the um date transformation because we have so many date functions. Then I will put all the string transformations together because we have so many string transformations. And there's a key note you cannot cover all the transformations cannot you will eventually learn more and more transformations more and more functions and you will add it into your knowledge. Okay. Okay. So you need to make one promise that you will be exploring more and more functions by solving more and more questions. You can just go on lead code. You can just go on not now. Okay. You need to first learn all these transformations. Once you learn these transformations, you can just try hacker rank questions because lead call would be like one step ahead for you. You can just try hacker rank easy questions. Just try to solve those questions and just try to look at the solutions as well. Okay. And there you will explore more and more functions, more and more transformations. And just make a promise that you will keep on exploring more and more functions. You will add it into your knowledge base. Promise. Pinky promise. Paka paka. So now now okay. So now let's get started with our first type of transformations which is numeric transformations. Numeric transformations are very very simple. Numeric transformations we apply when we just want to use mathematical operations. Let's say I want to find multiplication of two columns. I want to multiply a column by 100. Something like that. Anything mathematical. Let me just show you. First of all, let's say you want to and which table will be good. I would say dim product will be very very good column. Yeah, I think so. Dim product. Let's say let's say this is the column not column basically the table result. Perfect. So now we know that we have one column unit price. Okay. So this is the unit price column and we need to make sure that we are covering most of the functions but as you just made a pinky promise that you will be just learning more and more functions because you cannot cover all the functions. There are infinite number of functions. Make sense? But you need a strong knowledge, strong base so that you can just understand those functions. Okay? And you will understand all the functions after this. So first of all, let me just show you what what things you can just do. Let's say you want to apply this thing unit price into let let's say uh your manager says hey we want to run a sale. Okay, we want to run what? We want to run a sale. Okay. And in this sale, we want to give 10% discount. Okay. So, all the unit prices should be now available at the 10% discount. Okay? And if you have some basic mathematics knowledge, um what will be the formula for that? Bro, come on. So, let's say I am just running a sale, okay? and I just want to provide 10% off. So in that particular scenario, you need to use this formula into 0.90, right? Because 10% is gone. Bro, do not say that you didn't understand this simple mathematics. Simple. This is not I didn't get this knowledge from my bachelor's degree. Okay? From bachelor's of mathematics. No. So this is my unit price. So I can apply this transformation. It is allowed in SQL. Let's say I want to alias it at let's say discounted price. Make sense? Make sense? What else I can do? Let's say I want to add tax. I want to add what? Tax in my unit price. How I can just do that? Let's say um I will simply say unit price plus there's a flat tax of $10. Let's say I will simply say plus 10 flat flat. We do not worry about the price. Simple flat $10. I will simply say taxed price. Make sense? Now let's say you want to just um divide this number with let's say um 10. Let's say okay as let's say fractioned price you can do that okay and let me just add a comma perfect now there's a very good function called round okay so currently currently we have two digits after round after point after decimal right two digits let's say I want to round it off to one digit only so I will simply say Round. Okay. Round round what? Round unit price to only one digit. Make sense? I will say as rounded price. So these are some of the day-to-day functions that we use with the numeric columns. Make sense? So I can simply say dim product. Make sense? Make sense? Very good. So if I run this I will see this data. Perfect. And you can see I am getting all the results desired result that I wanted. Perfect. Very good. You can just have a look. Make sense? Makes sense. Makes sense. Makes sense. So this is these are basically the set of numeric transformations that we apply. Very basic I know and you can just explore more and more. In short, I can say if you have the formula, okay, you can apply it here as a transformation. In short, now that formula can be anything. You can divide it, you can multiply, you can subtract, you can do whatever you want. You can even write two columns as well. Let's say I want to write unit price. I do not have like any other column name. So just for the demo I'm just using unit price into unit price. Okay. And you can just imagine here we have a other column name as well. So you can literally multiply two columns. Let's say as multiply price. Okay. So you can literally do any mathematical thing here in the SQL. That is a flexibility that we have that is called numeric transformations. Make sense? Perfect. Now next thing we need to learn about date transformations. This is very very important because being a data engineer okay being a data engineer you need to play a lot with date transformations and date transformations I can say is the backbone. Okay is the backbone for your analysis for your data engineering for everything. Okay so let's try to see some of the data transformations. Okay let's see. So what's up? What's up? What's up? Another day, another SQL session. Let's let's let's open the um scripts. So, where were we? Where were we? My my my lovely da fam. Where were we? Okay. So, we were just discussing about date transformations. Nice. So, do you know what? Um I actually prepared a list of all the date functions, all the date transformations. And do you know what? And do you know why? Why? So that you do not need to just refer to any other resource. I have just covered almost all the date functions that you would be just using in a day-to-day activity. Okay. So I have here a list long list and I would love to just show you. Okay. So let's get started. So first of all let's talk about some date functions and don't worry again I'm reminding you you do not need to worry you will get this script as well so that you can just refer and obviously um take notes. Okay make sense? Okay good. So first of all let me just query some data. Okay. And let's try to see what will be the good column. Actually the best table is like dim date to just talk about date transformations. Okay. And once you learn about these concepts you can just apply these things anywhere. And let me just check if my voice is being recorded. Yes. Because it happened with me in the past when I just recorded hours of content and my voice was not recorded. I was like wow anchumba slow clapse okay this time it's fine so first of all let me just show you the data select tests from by the way are you loving this course okay because I am loving this course while teaching SQL because SQL has been my one of the favorite subjects okay favorite subjects and it it should be your favorite subject as well so currently as you can see that we have this particular table where we have almost everything almost everything right but you know But we going to just create our own columns. Okay, we going to just create our own things out of this date column. Make sense? Or we can actually use something let's say um fact sales or let's say dim customer as well because I think we have dim date there as well. But it's fine. Okay. So what we going to do? We going to use this column date. Make sense? Okay. So let's say let's start with so I will just writing all the code here. Okay. one by one. So let's create it in the form of module. So number one, let's talk about some of the functions that we use to fetch the current date. Okay, current date. Let's say you just want to query the data and in the query you just need to fetch let's say current date. Make sense? Like current date, current time, whatever you just need to use that. So how you can just do that? So there are like multiple ways. Let me just show you. So first of all, what you can do? You can simply write select. Okay. And you want date column from this. And you can use something called as now now function. Let's see if it works. Okay. So I'll simply say dim date. Let's let's let's run this. And yes, perfect. As you can see, I got the current time stamp which is 16th of October. Okay. 16th of October. And this is a time stamp. It is not just a date complete date plus time right I can obviously even rename it as let's say current time stamp make sense as current time stamp why there is an error why you have an error SQL syntax current time stamp okay yeah makes sense because it's a reserved keyword so I can just add a single quote just to tell him hey this is my personal column name as Yes, perfect. It worked. See, so this is my current time stamp. Make sense? There are multiple ways as well. Let's say I can also use something called as UTC now or basically UTC date. If if I just want to find the date and let me just use it in the next column so that you can just also um gather it UTC date. I can also use let's say UTC time and I can also use UTC time stamp as well. I have a lot of options. Okay, let's explore it. Perfect. See, so what did I get in UTC date? I just got today's date. In UTC time, I just got time. UTC time stamp, I got the same thing that I'm getting in the now as well. Right? See, exact same. Uh obviously, this is like the older one, but this is like the UTC time stamp. Why it is like difference of 3 hours? Because my current time zone is 3 hours behind the UTC. Okay, make sense? Makes sense, bro. UTC is like um your country will will also be like maybe 3 hours, 2 hours, 1 hour behind or maybe um it can be it can be okay. So it's up to your uh zone where are you currently at. Okay. So if you use something called as now, it will simply show you the time stamp according to your time zone. But if you say UTC time stamp, it will just show you the generic time that we have no matter from where you are running your queries. And in the real world we prefer UTC time stamp because it is a kind of generic time stamp which will be very very you can say um standardized across the queries. Make sense? So you do not need to worry if let's say um you you are running your queries from let's say uh India someone else is running the queries from let's say um US and you will be like what's going on? So usually we use UTC time stamp. Make sense? Very very good. Very good. So this is like make basically the functions that we have to fetch the uh you can say time stamp. Okay. Then let me just tell you some of the cool features of the date functions. Basically date um data type functions. So what we do let's say I have this date function date uh column right? So let me just say module 2. Okay. So let's say I have this select date. Okay. And let me just add semicolon. Perfect from let's say dim date. Okay. So what I will get? Obviously I will get the date column obviously. Okay. So now what I need to do I need to fetch only the year. Can I do that? Can I do that? I just need to fetch only the year out of this date. Yes, I can do that. So, I can simply say a function called year. Okay, year is a function. Then you can just pass this column. I will simply say date and I can simply say let's say date year or I do not need to add alias. It's fine. Okay. Similarly, you can do it for month as well. Okay. Similarly, you can do it for day as well. See, so these are the functions that you can just use to fetch let's say year, month, day, hour, minute, second, anything, anything. You can even just get the week day as well if you just want to see weekday like which day of the week it is. Okay, date. You can even I think say day name like which what's the name of this day? Everything everything. Okay, let's try to run this and let's see. See I got everything. Okay, make sense. Date, year, month, day, week day. Week day means fifth day because today is uh I think Thursday or Friday. Friday. Yeah, because it is just run uh running first day as Sunday. That is why. Okay, then Saturday day name Saturday. Oh, because it is just calculating this date. Okay, okay, makes sense. I was like, okay, today is Thursday. So, that is fine. So, but are you getting my point? You will say, hey, Saturday should be six, right? No, see Sunday is one here in North America region. Okay, Sunday is one and obviously in these coding languages as well, Sunday is one the start of the week. Monday is not the start of the week. Make sense? So why you go to office on Monday not on Sunday? I don't know. Ask them. Ask them who just made these rules. Okay. So this is Sunday. C6. Monday is zero. zero kind of like see zero or one same thing same thing right same thing okay makes sense so that's how you can just treat it and this will be vary according to the technology you use okay so do not feel like you will get that so you have to test it like what week it is using to initiate the numbers make sense because in our calendar we just treat Sunday as first but in maybe Indian region you will see Monday as one in UK region you will see Monday as on maybe zero. So every region has a different you can say preferences. Okay, make sense? Very good. So now this is all about that we can just use these kind of extraction. These are called basically extraction. Make sense? Extraction. Then you will say an Lamba um do we have any function or anything? Let's say I want to add or subtract the date. Do you know what what do I mean? So let's say you just want to add something. You want to add something. Make sense? No, let me just tell you. So let's say you have this date column. Okay, this date column. And let me just show you one more thing here in the UTC time stamp. You can use one more function here. Let me just add it here because we are just using extraction. You can actually extract only the date part as well. Let's say you have a time stamp. So you can also say date. And this way you will only extract the date. Let me just show you. See? So that's how you can also extract only the date from the time stamp. Make sense? Very good. So now you will say an Lamba just tell us let's say I want to add something. Let's say I want to add two days. Two days into this date. Can I do that? Yes. How? So let me show you how you can just do that. So first of all let's say this is your date column. Okay. So what I will say I will use a function called date add. Okay. Date add. And I want to add two days into this. Okay. Let's try to see what will happen. Let's try to see if I want to do it. Okay. Date add. Basically there are two functions add date or basically date add. And we need to see like which function will work because sometimes um because there are so many SQL servers and every SQL server has like different different functions for this particular task. So I also need to test okay because I mostly use MSSQL nowadays. So no need to worry bro no need to worry. You see it is not working. So I think it is add date. Yeah. Who's this idiots? Add date. Date. And let's say I want to add two. Two means two days. It worked. Perfect. See, now I am getting this thing 14th and then I added two that means 16. See, so I added 2 days into this date. So that's how you can just add date and subtract date. add date and I think sub date is also a function that I can just check. Not a big deal. Let's say I want to just subtract two days. Okay, let's see what happens. Yep, it worked. Make sense? So now it is 12. So that's how you can just add and subtract date. Make sense? Now you will say an L everything is fine. But why do we use this function? Like why like this function exists? Why? Let me just tell you, let me just give you the scenario again because I told you like I will be just giving you the scenario so that you can just relate better. It's not like you just need to learn UDC time stamp. You need to understand its application as well. So let's say there's a manager okay he or she comes to you and they say hey I want to know um let's say all the products which are let's say or let's say all the orders which are 30 days older. Okay, that means any order which was placed before 30 days, I want to fetch all those orders. How you can just dynamically do it? You will say, "Oh, how we can just do that?" So, basically in that particular scenario, you can create a column which will just give you the number of days for a for any order. Make sense? So for example, for example, let's say this is the date column and I want to know how many let let's suppose this is the order date. Make sense? Let's say this is the order date. It is not but just you can just imagine this is an order date. You want to find out number of days that are passed and or you can say number of days between this order date and now. Okay. So how you can just do that? You just need to use your creativity. So you will simply say um date difference because there's a function called date difference. Okay. And you need to find the difference between this date and you need to find the difference between date column. Right? And this will be like let's let's add an alias. I will call it as let's say as um total days. Okay. Total days make sense. If I run this, you will see the number of days between those. See uh 733 days. And that's true. That's true. So between 14th of October 2023 till now there are 733 days. Almost almost more than 2 years kind of. Yeah. Yeah. Almost almost 2 years. Yeah. Almost almost 2 days less because today 16th of October. Yeah. So almost almost 2 years. So that's how you can just find out the number of days between any two dates. And that is why this function is very handy. Plus this function acts as an application for this particular function. Like both function complement each other. Make sense? I hope it makes sense. Okay. So this was like all of your mostly used date functions. And again you need to explore more. you need to let's say um do a lot of exploration while just writing the queries because date function is the area where you cannot say I have the knowledge or not no because dates are that area which you cannot master cannot because every requirement is so so so unique so do not feel that you do not know about date functions no if you know about these things you know about date functions after is it's all about debugging and exploration. Okay, I can guarantee bring any SQL pro developer. Okay, and just ask him or her, hey, I just want to solve this. The reason is like why it will be like very difficult for that particular person because every SQL server behaves differently for date functions. Every SQL server handles date functions differently. So it's quite next to impossible to learn all the data functions of all the SQL server. It is not generic. It is not standardized. Some function are standardized like this. That is why I have mentioned all of these functions. Make sense? Makes sense. Makes sense. Makes sense. Now I also want to talk about one more important function because this is like very very important and you will be handling this particular issue in your code as well. Make sense? Make sense? Makes sense. So what is that issue? Sometimes when you just work with the data you do not get data in the time or basically date format but you need to use it in the date format. What does it mean? What does it mean? So let's say you want to let's say there's a value like this. Let's say this um 20 25 0 1 01. Okay. This is the value. This is a string value, right? This is not a date value because string because this is a single quote. So you want to convert it into date time. How you can just do that? So there's a function called cast. Okay. Cast is basically a very detailed topic in itself. Not very detailed. Not very detailed. Yes. Because with the help of cast you can literally cast any data type from and to like you can cast any data frame. data frame data type from any data type to any data type. Let's say you want to change um string to integer, you can do it. You want to change integer to string, you can just do it. Okay. So, I'm just covering casting part here. Okay. After that, we'll be just looking at some cast functions. Integer to string, string to integer after this. Okay. Make sense? Good. Okay. Makes sense. So, let's say I want to cast it. So, I will simply write cast and I want to cast it in let's say date time. Make sense? And let's see what happens. Perfect. So now that text column is now treated as a datetime column. Why? Because see it has automatically added this time stamp which was not even there. This was just a date but it added this time stamp as well because I casted it. It is called type casting. What is it called? Type casting. Makes sense. Fancy name very fancy name but simple stuff makes sense so this was like almost all of your you can say functions I just want to talk about one more function it's called date format and for date format you have to see like which format pattern it follows so let's say I want to format my date into any other format for example let me add three let me add three Uh okay so let me let me add three and I will simply say select okay I have date column right so I will simply say date as it is and then let's say I want to convert it so I will use a function called date format okay this is a function so I need to say I want to change the format of this column but What will be the format pattern? I also need to give the pattern. So there are so many patterns. Okay. And it depends upon the requirement. So I can simply show you any particular pattern. What is this man? Okay. So MySQL date format patterns. Okay. Makes sense. Um yeah, my SQL official documentation always the best thing. So let's see. Yeah. See um there are some patterns as you can see y mmd which is a default one. Okay then there are so many patterns that you can just use. So as you can see here let me just find it. Date and type date and time types. Um date format. Date format. Date format. Okay let me just see if we have anything good. Let's explore this one. So this is date format. See. So this is the kind of pattern that we are using percentage y that means we just need to get the year out of it and there are so many patterns literally so many patterns that you can just use and this is like pure exploration you do not need to remind you do not need to remember let's say you want to use this format percentage mdy let's let's try to use it okay or let's try to use this pattern let's see what happens so if you just use this pattern and by the way I love their particular you can say website documentation whatever you want to say amazing stuff okay very handy when you just need to find small small things very fundamental things detailed things as well. So I highly recommend okay you can just go to this website amazing stuff amazing stuff like they literally are doing a great work um literally like documenting all almost everything that you need being a developer okay amazing stuff so yeah there are good people on this planet who are helping others okay so let's say I just want to put this particular format and let's see what happens from dim date okay make sense So it will convert the data type for my date pattern. See now it is looking so so so good. Let me just add an alias. I will simply say converted date. Okay. Perfect. So see this was my original column and now it looks so good. Saturday October 14, 2023. Sunday October 15. So that's how you can just provide a pattern. You do not again you do not need to remember any pattern. You will simply go to the documentation. You will simply go to let's say any browser. You can just fetch the pattern. Okay? Because every SQL server behaves differently. Plus every requirement is different. So you cannot remember all the patterns. You you are not a machine, right? You are not a machine. You are living in an AI era. You need to use these things. You do not need to remember these things because that was a time like in the early 2000 when people didn't actually have the access to these things like they had the access internet was there I know that but it was not very very easy to just crawl between the web pages and just get the right result like this okay so that's like at that time people used to remember all these things plus there was not such or much complexity around that particular area at that time. Okay. People used to carry books, people used to just carry their own notes while developing the stuff. Now you cannot do it, right? Because you have all those things in the in the in a digital manner, right? So you just need to use it wisely. Perfect. So the these are our all the you can say um date transformations. Make sense? Very good. So now it's time to just focus on the you can say string transformations. String transformations. And before that I will simply say casting. Okay, type cast. Type casting. And you have already seen that. And let's take one to two more example for type cast. So I'll simply say type casting. Okay, makes sense. Type casting. So let's say let me just query a table. Select axis from dim customer. Okay, perfect. So let's say I have this particular customer key and I know that this is of integer format. This is of what? Integer format. Make sense? But what if I want to convert it into string? Answer just stop. First of all, just tell us why you want to convert into string. The one of the most common scenarios is whenever you want to apply a join between table one and let's say table two. The first and the foremost condition is your data types should be exactly matching. Even let's say customer table has customer key one and your orders table has also customer key column one. But orders table has a data type of string and here you has a data type of in integer. It will not work. Data type should be exactly same. So you can just type cast it in those scenarios and you will type cast these columns very very frequently during joints because that is like one of the most um go-to area for type casting. Okay. So how you can just do that? Very simple. I will simply say select. Okay. So this is the original column customer key and I can simply say cast customer key as in which type I want to uh do the type cast I just want to do it in the var whatever you want to say okay let's say var or let's say 1,000 okay var make sense okay so this is the thing then I'll simply say from dim date oh so dim customer makes sense make sense why it is wrong um let's see uh right index to use near this this okay what's wrong bro what's wrong um where char let's use char yeah char worked so you can just use R perfect so here you can see that now my database is converted into string okay that's how you can just do it make sense and you can just do the reverse of it let's say you just want to convert a string column into integer column I can even do this as well see let me just add a cast function on top of this function okay just a good learning right okay and I will simply say cast this as let's say int. Okay. Uh let's see if it works. Cast of cast. Okay. Cast of cast as integer. So braces are good. Okay. And this brace is getting closed here. Let's see if it works. I love experimenting new new things. Okay. So it is saying, "Bro, what are you doing? What are you doing, bro? Bro, bro, bro, what's the big deal? We can actually achieve a achieve it using subqueries, but currently we are not discussing about subqueries. So, let's say you just want to test something else. Um, let's use which is of string type and we can just convert it into integer. Okay, let's see. Let's let's convert it this into integer. We can just do it. Let's say into integer as integer. Oh okay. I think I got it as I think error is here. So that um nested casting was working fine. Okay. So why it is giving me error here. Type cast customer key as integer. Maybe it is also in like already in integer maybe. Let me just check the data type for it. Dim customer. Let's click on here columns and customer key. And it is of integer. Yeah it is already an integer. So makes sense. Makes sense. Makes sense. Makes sense. But yeah, that's how you can just do it. I can just show you because I want to show you every every everything. Very good. So now what we need to do, we need to just discuss about string functions. And let me just tell you, bro, there are endless string functions. Endless literally like endless. But we'll be just talking about the major functions. And I think this is like one of the superpowers of SQL that you can just apply so many string functions, literally so many. And yes we will be just exploring learning more and more string function and let's see what do we have in string functions. So now let's see like what do we have inside string function. So very common very common and let me just first of all show you by querying a table and mic check mic check it's working fine very good. So select ax from um dim customer because obviously if we just want to launch string function we need a table which has a lot of string columns. Wow. IQ IQ IQ top level. So basically let's say this is our full of string columns. Wow I love it. So let's say very common you can say scenario person comes to you and the person says hey bro can you please give me some data with some transformations. So for example in our data we have some columns called first name last name and the person wants only one column instead of two columns. So you need to concat or basically concatenate these two columns. Make sense? In Excel you will be saying hey it is so easy so easy. We can just simply use equals concat and then just selecting the cell and then just next one. You can do it here as well in much more easy manner. So for example I want to um apply concat function. Okay so the function says concat. I want to apply on column first name last name and you can use n number of columns. Do not need to worry. Okay you can just keep on going and going and going make sense. So it will simply give me the concat version of my two columns. Make sense? Very good. So I will simply say from dem customer. makes sense. Let me just run this. Perfect. See, I got it. I got it. But it is not actually very very good. So I will just show you what is a better way of doing it. So obviously whenever you're just using concat function, it will simply combine the values. Boom. Like this. But in the real world scenario, you need to use some separators. It can be space, it can be hyphen, it can be underscore, it can be anything. Let's say I want to use space. So I will simply say concat this column then concat this column and between this just add space like this. So basically concat this then this then this make sense and I will be just renaming it as let's say full name. Make sense? Let's see. Perfect. Now it is like absolutely perfect. Better than your Excel. See everything is sorted. So that's how you use your concatenation function, concat functions. Okay, make sense? So I'm not here writing down 1 2 3 4 because there are so many functions. So I will just keep on writing all the functions in a flow. Make sense? Very good. So now this is good. Now let's say we want to find something called as length. Now what is length? What what is length? Very good. Very good question. Very good question. Keep it up. Keep it up. Keep it up. Basically size. What is the size of your basically column? H interesting. Can you just do that here? Obviously, easily. How? Let me just show you. So, first of all, let me just query it here. Select a from customer. Okay. So, these are the columns. So, let's say I want to find the length of um city or basically country. Let's say I want to just get the size of the country. Basically size as in spelling. Okay, how many alphabets do I have? So I can use a function called length. Okay, and I'll simply say country and I will name it as country size. Okay, country size. And if I just want to run this, I will simply select this query and run it. Perfect. So I am automatically getting the size. See 7 8. It is not the size of this one. It is not the size of this one. Size of the country. Make sense? Size of the country. Okay. So that's how you can just find the length if you just want to get the length. Make sense? Makes sense. Makes sense. And I can do one more thing. I can simply add a stick as well here. So that you will see like these are the additional columns. Okay. If I run this, you will see all the columns. Additionally, you will see these columns on the right hand side. This is better. Okay, perfect. Now, this is also good. Now, let's say you want to convert your data in the lower and upper case. What does it mean? Because whenever you just work with the real-time scenarios, um we sometimes we we sometime get the data in the lower case. We sometime get the data in the upper case, sometime in the proper case. So, we need to maintain a standard. Let's say I want to keep everything in the lower case. Let's say. So how you can just do that? Let's say for example um for uh city I want everything in the lower case. Make sense? Everything in the lower case. So I'll simply say lower and then um city. Similarly you can just use upper similarly. Okay. And you can use I think proper as well. Let's see um proper function in MySQL. Proper function in MySQL. My SQL my because every again every SQL server is different. Okay. So you need to just fetch you need to just explore more and more things. So do we have proper function? Uh no. See in MySQL you do not have proper function. I doubt if we have an MSSQL as well. So yeah. So we can just use some customize function but that is fine. Okay. So you can just anytime just explore more and more things. Make sense? So it is saying you can just use a combination of um lower and upper and then just use substring. But it is fine. I wanted to show you this because everything is not available in SQL. No you need to just tweak a lot of things and you need to explore a lot of things. That's why I said like it is string functions like something that you will see something new whenever you're just writing some SQL. I'm so sure. Really? Yeah. Yeah. Okay. So, let's say you want to convert into lower case. So, let me just run this. And for now, I'm just Yeah. Let's let's run this and you will see. Perfect. Now, it is converted into lower case. See all the cities are converted into lower case. Perfect. Perfect. Perfect. Perfect. Make sense? Okay. Now, you will say, hey, what if we want to slice some data? I know you do a lot of slice and dice in Excel when you just want to let's say fetch first four alphabets. Let's say you just want to fetch last four alphabets all those things right. So there's a function called substring. Okay. So what this function does this function will take obviously the function name substring. Substring means we are just extracting a substring from the string. You will simply pass the column. Let's say I just want to fetch um the first four alphabets of the email. Okay. So I'll simply write email column email and I will start my position from one. Okay. And I want four alphabets. So I will just run this and I will get only the four alphabets as you can see here. Rich, Denny, Joe's, Jan. If let's say I want um six alphabets, I will simply write six and I will get six alphabets as you can see. Perfect. Richard, Daniel, Joseph, Jan, Robber, Samuel and blah blah blah. Make sense? So that's how you can just um fetch some alphabets and do your slicing and dicing. Make sense? Now my personal you can say favorite function. This function is called replace and you will do basically you you will use this function a lot whenever you are just doing some data cleaning. Sometimes we just get a lot of undesired characters within our text. Let's say I'm getting some special characters which I do not really want but I can get those functions right not function like those characters right so in those particular scenarios I can use a function called replace okay so what this function will do let's say in the email um column I want to replace all the at the rate with something else let's say I want to replace at the rate obviously you should not but I'm just showing you you want to replace at the rate with let's say percentage okay so I can just do this so if I Just run this. It will work fine. Perfect. See, now all the email addresses are actually replaced with like all the at the at the rate are replaced with percentage. That's how you can just replace the uh you can say characters and you can just literally replace anything. Okay, literally anything. Okay, so this these are basically some of the functions that we use on a daily basis. One more function that I want one to two more functions that I want to show you is left and right. You just saw a function called substring when we just gave hey I want the starting point and the number of characters. Make sense? But sometimes we also need to just fetch the data from the left only and right only. So in those particular scenario we have two functions called left and right in which you simply say I want n number of characters from left. I want n number of characters from right. Simple. For example, I want let's say n number of characters from um country. Okay. So I want country and I want just three characters from the left. That's it. Okay. And similarly you can just do use write as well. Write country. And let's say I just want three characters from the right. Okay. So that's how you can just fetch those data. As you can see, I got it here. Perfect. Make sense? So, this is like I'm slicing and dicing from a particular direction. Okay, make sense? Now, let's say you just want to repeat or basically not repeat. Um, I can say you just want to reverse not repeat. Let's say you just want to reverse anything, reverse any string. Can we do that? Obviously let's say you just want to reverse whatever it is written whatever it is written okay so in that particular scenario you can use a function called repeat or sorry why I'm using repeat again and again by the way there's a repeat function as well I will just show you so reverse let's say I just want to reverse the country okay so this will simply reverse the country column and now you will see the name is in the reverse verse order. Okay. And let me just show you the repeat as well. I'm not I'm I don't know like why I'm just using this particular name. Repeat, repeat, repeat. So let's say I want to repeat something n times. Let's say I want to repeat um first name two times. Okay. So I will simply say first name and repeat it let's say two times. So what will happen? It will create a column and it will write my name two times. See Todd Todd Matthew Matthew Gregory Gregory Nathan Nathan see Jimmy. So it will just keep on repeating the values end time. You can just rewrite 1 2 3 4 5 6 7 8 9 10. So these are the functions that we use in combination with other functions but the these are your bread and butter that you have to have to know. Make sense? Make sense? Makes sense. Makes sense. Very good. Very very very good. By the way, there's one more function called concat ws instead of concat. You will say what is the difference between those. So let's say let's say you have total five columns. Okay. And you need to combine those five columns. Okay. And you need to add a space between those five columns while combining it. Okay. What is the problem? Did I say is there any problem? No, but you will be just doing a lot of manual work. For example, you will say first name and then you will add a space then last name then space then third column then space then fourth column then space then fifth column then space. SQL said bro I found this problem. So I created a new function called concat ws. So what it will do it will simply say bro whatever you want to add between those five columns if this is the same value just tell me once I will say yes the value is space that's it now it is saying just mention all the columns you want I will say first name last name and that's it you you can add more columns let's say country as well just for now okay just to show you okay just to show you so you will Perfect. Where is that? Okay. Here. On the second. Uh, not on the second. Here. Perfect. See. See here. So, it is now adding spaces automatically. I'm not adding any space. It is a power of concat WS. Make sense? So. So, these are like frequently used string functions that you should be aware of. Make sense? Very good. Now, now, now, now we going to talk about something called as conditionals. Okay, let's create a new file. Okay, and by the way, I have saved it. Yes, saved it. Very good. Very good. Very good. So, let's talk about conditionals. And this is similar to your if condition in Excel. Let me just prepare you. I know you are a pro Excel developer. I know. I know, bro. I know. So SQL file and let's save it as let's say chapter 7. Perfect. So now let's say let me just say conditionals. Conditionals okay perfect. Now, now um let me just take a very good example in order to just illustrate this because I want you to understand this completely. Um let's say we have um select axis from dim store. Let's see what we have in that particular table. Dim store. Uh okay, it is good by the way. Hm. Okay. H country, city, north, west, east. I think um let me just try one more thing. Maybe fact sales will have something that we can just use. Okay, this is good. H okay. Facts is also good. Okay. And if I say dim product, dim product, I think. Perfect. That's what I wanted. Perfect. I wanted some contextual data and the uh numerical data as well. Okay, perfect. Let me just put my thing on charging because because it's just 15% and then you will be shouting Anjal Lamba we cannot see you what happened. So this is my data okay this is my table that I just want to apply. So conditionals basically I gave you a hint that it is similar to if conditions when you just want to apply conditions. What kind of condition? Let's try to understand it within a with the help of an example. So let's say you have something called as unit price. Okay. Now whenever you just go to buy anything, I know you are very very rich. I know that. Okay. So obviously you'll be just buying the expensive stuff. But I'm just talking about some normal people. So let's say there are some normal people who are just going to a normal shop. Okay. And obviously the person will first of all see the price tag. Okay. Because not everyone is rich like you. Okay. So the person will be seeing a price tag. So how expensive is that? So let's say you are owning you are owning H nice. Now you're rich too. Okay good. So let's say you you uh or basically your manager said hey bro let's suppose you're working in a retail organization. Okay. So your manager said hey bros listen um let's try to help the customers. And obviously every product is having a different price tag. We know that. But let's actually categorize it. Let's say if it is like less than $100, then we will simply say it is um affordable or cheap. Cheap is a bad word, affordable. Um and if it is like between 100 to 200, then it is normal. Okay. Then if it is like more than 200 then it is expensive. Okay. Let's create these three categories. Make sense? I know I know I know that that you will be just going to the expensive category. I know that. But I'm still talking about everyone. So we need to create the categories. How we can just approach this thing? It is very easy. Let me just show you. For this particular thing, we use something called as case when statement. What did I say? Case when statement. So I want to fetch first of all the columns. Okay. And along with that I want to create a new column. And this new column will look like this. Case go to next line. When when that means now we need to just pass a condition. Okay. case is just the starting point of the case and then you just need to pass the when statement I will say when unit price is less than 100 oh 100 okay less than equals to 100 let's say then call it as affordable make sense simple any doubt till here no simple English right this is condition number one Okay. Then I will create another when. Another when means another condition. When Okay. When unit price less than equals to 200 then normal. Okay. This is condition number two. Condition number two. If it is more than that, that means I just want to call it as expensive. Make sense? So I'll simply say else. Else means if this condition is not satisfied. If this condition also is also not satisfied that means it is greater than 200. Make sense? Common mathematics. Basic mathematics. So I will simply say else expensive um but not for you because I know you are really really rich. Okay. So but not for you. Okay. So let's say this is the price tag that we have just applied. Okay. Makes sense. By the way this is a great marketing, right? You're simply writing expensive but not for you. So you are actually targeting everyone like the ones who have the money obviously they will go too expensive ones who do not have and they will be just reading it not for you they will feel like oh we are rich right that's a good marketing strategy lamba you are a great marketer right right I know that so basically uh else expensive but not for you okay let's add an exclamation mark as well perfect so this is the tag that I want to use now we always end this case statement always. Okay. So I'll simply say and that's it. Okay. And that's it. This is my column. Then I will simply say from dim product. Make sense? And I know that this name will be looking very very weird, very very bad. So let me just add a good name as um let's say price category. Makes sense. So let's run this. Perfect. So what did I get? I got see price is $8.97. Wow. Expensive but not for you. 97 that means affordable less than 100 and 170 that means normal. So see that's how you can just create price categories like this. These are basically the conditionals. H that makes sense. That makes sense. Make sense. Now I want to add one challenge but this will actually um make you a pro developer. Okay, this is a really good one. So what's that? So let's say you just want to add these price tags. Okay, only for clothing only for clothing brand nothing for any like not not for any other category. See we have here category. So we need to just apply these tags for only clothing. If we have any other category then we just want to print the same category here in the price category. How can we do that? H let me show you. So it will be challenging but it will be good for you. Good for your learning and that's why I'm just creating this video for you. Okay perfect. So I will simply do copy. Okay. And this is my one. Okay. And let's call it as two. Okay. Perfect. Perfect. So what we need to change here? So what you need to change here is you will simply say when unit price is less than equals to 100 and you will apply an and and category equals clothing. Make sense? Perfect. Same thing here you will add and here as well. Perfect. And then you will simply add this one more when here as well. Why? Because this time we need to say this thing in the returning value. Perfect. Else else if it is not any clothing category that means it is something else then we simply need to return category column that's it because we do not want to do anything okay you can do one more thing because you have now learned concat so you can also say concat okay category or you can simply add a text you will simply say not for not for this not for that's it not for and then category name. Make sense? Makes sense. And that's price category. Let's try to run this. Let's see what's the output. Perfect. So now, as we can see, amazing result. So what is there? So this is clothing. Okay. Expensive. Expensive. Expensive. When we have home and kitchen, it is saying not for home and kitchen. Sorry. Then home and kitchen. Not for home and kitchen. Okay. Then not for sports. So that's why we can just use this thing. See because we are using here concat function makes sense that we can just return these values as well. H makes sense. It is cool. Yeah, it is cool. I know. And I hope you learned something good, right? So that's how you can just create conditionals. Make sense? Very good. Very very very good. Okay, these are conditionals. One homework, small homework for my lovely deam. Create at least three conditionals on your own. Do not create conditionals for the queries that you can write. No, you do not even need to think about query like what will be the query behind it. You need to first of all behave like a business manager. And you need to create three conditional criterias like I want to see this, I want to see that, I want to see this. Let's suppose you are a manager and you are just asking a developer I want this. then immediately switch a role and now you need to behave as a developer and you need to you need to now write those SQL queries. Make sense? Make sense? Make sense? I can easily provide you those three queries. Okay? But what I want I want you to think from that perspective as well. Make sense? I want you to think from that perspective. That's why I'm such saying just create your own queries because you will challenge yourself. you will solve those challenges as well. Make sense? Make sense? Good. Okay. Perfect. Perfect. Perfect. So now what do we need to do? Conditionals are also done. Okay. Now finally we need to learn about the most hyped topic, the most you can say fearful topic and the most important topic from the interview point of view plus from the development point of view. Window functions. Window functions. Window functions. Window functions. Let's talk about it. So bro, now we're going to talk about window functions. Okay. And now you just need to be little bit more serious. I'm not saying like do not have fun. Have fun. But now you just need to be serious as well while having fun. What is this combination? That's my combination. Okay. So now window functions. Window functions. Window functions. I know this was the most scariest topic till now for all the u beginners for all not even the beginners. See not on not only the beginners even the advanced people as well like advanced level people developers feel scary when they whenever they are just asked to perform any window functions or window operations okay in this particular section you want to master window functions just trust this guy and just watch the next I don't know 10 15 20 minutes because I do not know because I have no clue like in what depth I will be just going into this topic because I want to just show you everything with the code with the co with the you and say concepts everything okay so let's talk about window functions like what are window functions first of all tell us that basically window functions in SQL are special functions okay that perform calculations across a set of rows what does it mean what does it mean obviously we apply the transformations or functions calculations across rows no you do not really yeah you do not because you collapse You squeeze the rows whenever we are asked to apply any transformation of basically calculation. Remember whenever we just we were just finding sum we were squeezing the rows. Whenever we were just finding the average we were squeezing the rows whenever we were just finding max min values we we were squeezing the rows. We were just collapsing the rows. But with window function we do not need to squeeze or collapse the rows. Oh, so we'll still get the same results but row by row. Exactly. We will get the same result without squeezing the row. That is the power of window function. So that's what it is written here but without collapsing them into a single output row unlike aggregation functions. That's why window functions are different from aggregation functions. Okay. Now we got some hint and obviously we have so many doubts because we haven't seen how to code it and how to see it. I know I know and I'm here for that. Okay then they allow you to do things like ranking running totals moving averages. Do you know all those by the way window functions are bread and butter for all the financial organizations because they do these kind of things a lot running totals moving averages and do you know what not just the financial organizations these are the backbone for your KPIs as well so you need to do all of these things and do you know what if you can just do all of these things in SQL you are actually becoming a very very good developer because window functions are backbone make sense? So they allow you to do things like ranking, running totals, moving averages and comparisons between rows while still keeping all the rows in the result. And do you know what? I just made some notes for you that I am going to discuss some critical questions as well, critical scenarios because that will make you a pro developer. Okay, are you ready for that? Are you ready for this this this section window functions? Just tell me in the comment section right now. tell me in the comment section right now right now right now and I know whoever loves me whoever um supports me will write the comment and other are like few outliers are always there so just ignore those people let's create a new chapter okay new SQL file and let's save it and I will call it as let's say chapter 8 perfect and I'm going to give a heading Okay, window functions. So, first of all, we going to start with some um you can say functions which are aggregation functions. And basically, let me show you what do I mean? Okay, then you will say, "Hey bro, what are you saying?" So, let's say um if I query this table, select a from select from dim product. Perfect. Perfect. So this is my table. Woohoo. Now I want to find I want to find average of unit price. Remember the same thing that we did in the past. You will simply say bro do you know what? I am a pro. Now let me just write a query for you. Just you you just sit back and relax. Let me just write a query for you. Okay. Okay. Write it. Write it. Write it. I know what you will write. You will write this. We'll simply say average and unit price from from dam product. I know you will write this. Let me just run this. You will get this value. See 495. Very good. Clapping. Clap. Clap for yourself. Very good. See because you learned this right now. Do you know what the requirement has changed? Okay. Now the requirement is um I want to see the same result, same table. I want a new column here which will display average price in front of each row. In front of each row. What an Lamba you said whenever we just apply aggregation functions we squeeze the rows. So if we just squeeze the rows if we just squeeze the rows obviously um it will be just one row. How you can just expect the average in front of every row? That's the power of window function. Oh yes, that's the power of window function. So do you know what what I will do? What? Let me just show you what I will do. So basically I will write select a tricks. Okay. Now I will say sum. Okay. Okay. Sum. Make sense? Okay. Make sense? Sum of what? unit price that I do. Okay. Now I will use something called as over. Okay. Over. Over. And then I just need to pass a command called order by. Okay. Or order by. Order by that means in which order you want to follow. This command is not very much relevant here. But this command will be very much relevant in the future scenario. So you need to just know about this thing that this command is used to sort your data. So I want to sort my data based on unit price. Okay, that's it. Not a big deal here, but it will be a big deal in the future scenarios. And you know what what it will do? Let me just run this. So now you are getting basically basically let me just zoom it a little bit. You are getting let's not sum basically yeah let's let's say we just want to find a total okay because you will be learning this thing that I just want to tell here. If you see unit price, let me just zoom it a little bit. What are you seeing here? Um, we are seeing unit price here and here we are seeing sum. This is not your sum. Just just just look at it for one more time. You you will see the answer. I want you to just figure it out on your on on your own. Just just have a look. Just try to see a pattern. Got it? Let me just tell you to those who are like what bro see this is unit price 11.82 this is 11.82 82. The next value is 12.15. This is 23.97. What does it mean? That means it has just applied. It has just applied addition on these two records. 23.97. Then it added this value it become 36. Then this added this value then it became 49. Then it added this value then 63. So it is called running sum. It is called running sum or running total. Okay. Running sum or running total where you are just actually seeing everything step by step. Top top. Now I want to change the order by with launch date because we want to see the values based on the date right because today we earned this then tomorrow we earned this then on third day we earned this much. So we need a running total based on the date and you will see it here. So you will see that on first day we earned 382 then on the second day we earn total 571. It's not like we just earned 571 only on this date. No 571 means this plus this. Now 772 means this plus this plus this. This is called running total. Running total. Perfect. Running sum running total. Okay. Make sense? Very good. That's how you can just also find running average. That is called moving average. Okay? You can simply write average here and you will see the moving average. See? Okay. I'm not a like pro in these particular moving average 50-day moving 30 moving average. No, we just need to talk about some very basic statistics terms. Okay. So, this is it. That's how you can just do. Okay. Make sense? Let me just say sum because sum is like much more relevant and easy to interpret just for you. Okay. So this is running some you will say an lamba. Don't be very very smart because demand was something else bro. I know demand was something else. You said that we want to put here average or basically total value here. You are becoming smart. We do not want running total. Okay that is good to know. I know like which is very good to know but demand was something else. Okay. See I'm not becoming very very smart because I'm already very smart. So the thing is I'm just kidding. So the thing is this is called something called as this is a concept um which is a little bit tricky but I want you to learn okay how you can just find the total. This is called something called as frame clauses. Okay. So let me just write here one. Okay. And the second will be frame clauses basically frames. Do you know what there is a code here which is not visible to everyone. Let me just write one code and you will not ask any question. You will not ask any question. Okay. And you will just observe me. Okay. This cute guy. You will just observe me. Cute guy. An come on man bro what? So let let me just write a code. I simply say select a tricks and then sum of unit price over order by launch date. Okay. No just just just just observe. Okay. The rows between um unbounded preceding and and current row I guess. Yeah, perfect. So I just I just wrote this code here. Ah Tim product. Let me just run this. Let me just run actually both. You will see no difference. See where is result two. See this is result one. This is result two. Same. See 38205 38205 57127 57127. So both are same results. Okay. Now what actually happened? Let me just open this one which is the latest result. What actually happened? What what did you write an and why it return same thing? What is this? What is rows between? What is preceding? What is current row? What is happening? So basically this is called prolevel concepts that you were you are just learning. Okay. So basically this is called manipulating the frames of your window functions. I told you that window functions are something that are applied on a row basically rowle operations. Okay. So when you apply something called as window function without defining any frames. So do you know what is the frame is um you can say picked as a default frame. That means it will simply use something called as rows because it is just applying operation on the rows between unbounded preceding. That means all the rows from the past all the previous rows. So that means when it is here, how many rows it has as a preceding rows? Zero. So it will take this value. Perfect. When it reach here, how many rows it has? One. So it will take that value. preceding unbounded preceding and current row it will stop here. It will stop here. Then it will go to next row unbounded preceding all the three rows and it will stop here. Why? Because this is the value current row because we are saying calculate all the data between all the unbounded preceding and current row. Current row stop here. Stop at the current row. Oh, okay. Makes sense. So that is a default behavior. Now if I want to say do not stop at the current row just keep on going unbounded following unbounded preceding and unbounded following. So what it will do it will simply find the sum of unbounded following as well. So let me just write it for you. Let me just write a new query. Okay. So if I just change the frame clause here. If I write and unbounded following okay unbounded following that means I don't want to stop at current row just keep on going so do you know what it will do it will simply find the sum total sum just see it here see now I'm getting the total sum 2 47 8953 perfect this is a total sum and it is it it will like keep on going obviously like till end row that is why you are seeing the same value because it is like the same the total sum this is the power of frames that you now know make sense how was it how was it and do you know what do you know what I know I know I know that you need some practice with it I I know that that is for sure but do you know what if you have understood this concept this is actually a prolevel concept actually Trust me, this is a prolevel concept when you just need to um manipulate the frames. Frames manipulation is not a beginner thing. No, but now you are able to understand why what because of you. Because you are learning because you have the dedication to learn. Do not say like hey you are teaching us that's why you are just making us understand um very very easily. It's your dedication. I am no one, bro. I am no one. Who Who is Anlam? I am no one. It's all about your dedication, your passion, your you can say um hunger to learn and grow. That's it. That's it. I am no one, bro. It's all about you. Okay, well done. So now this is like how you can just use window function. Now we want to talk about some very popular window functions. Okay, it's called ranking functions. Okay, what does it mean? So I will simply say ranking. Basically there are three ranking functions very popular popular functions. Okay, one is rank, second is denth rank and third is row number. Let me just tell you the difference between three. And now you understand the frame. So now you can just easily understand these things. These are like piece of cake for you. Let me just show you the difference between three first of all. Okay, let's see. So now now let's say we want to understand row number rank and dense rank and do you know what you will understand each and everything with the with the help of this simple example and after this we will do the coding and after that we will be just covering some realtime scenarios such as removing duplicates such as finding the fifth highest unit price, sixth highest unit price any nth highest unit price anything because those are the bread and butter for the interviewers they will definitely ask you those questions. Okay, plus you should know about those things. Okay, first of all let's say this is our column. Okay, and as we just know that row number, ranked and rank all of these three functions are related to ranking. Whenever you just want to rank our data, perfect. Let's say this is our column that you want to consider unit price. I want to rank my data and if I am just using row number, what will be the behavior? Let me just show you. And do you know what will be the ranking? What will be the number or basically it will be doing or it will be giving to this particular records. It will write it here one. Okay. It will write it here two. It will write it here three. You will see number. What? What just happened? This is 90. This is 90. So these are same, right? Yes. So why it is one and two? It is not rank. Yes. That is the property of row number. It doesn't care about your or you can say values. Even if it has the same values, let's say 90 90, it will say 1 and two and then three. Oh, okay. Make sense? Make sense? So that means even if you have data with different values or same value, it will keep on going. It will not assign any same value to two different or same records. Simple and sorted. Okay, makes sense. Now rank this will say one. Makes sense. This will say one. Makes sense because both are same. What will be the value here? The value will be three. What? What just happened? Why three? So basically one and one makes sense three because how many records there are um present um in this particular set two right so it will start the counting from three why because one and two and now it has three so if there would be like three 90 rows okay then it would start from four make sense So yes it will give the same value to the same you can say records but it will start the counting it will start the counting after calculating all the records. So if it has like say two records so it will say 1 2 and counting will start from three no matter if you have two after one. Okay this is my favorite 10th rank obviously like all of these three are my favorite. It depends like what um the requirements are. Okay, this will say 1 one and this will say two simple because 1 one and this is the second position right second. So this is the only difference between row number rank and then rank in short row number will just assign different values no matter if they have same values or not. Rank will assign the same values to same records but it will start the counting not from the very next number. No, it will count how many same records it it assigned and then it will simply um say 1 2 3 4 5 6 and then it will start the counting from the next number. Make sense? Whereas dense rank it is like very very understood one one first and then second simple because we just have like two positions right this is position number one same level and then second position that's it. So this is the difference between row number rank and dense rank. Do you want to see it by code as well? Let me just show you. So, we are here in our window functions, right? Ranking function. So, first of all, let's say I want to find the ranking for this unit price. Okay. Let me just write the code for you and let's create three more columns after unit price. Okay. So, I will simply say select unit price. Make sense? Then I want to create let's say row number. So I will simply write like this row number okay like this and then over over okay on which column I want to apply I will simply say order by order by what unit price make sense so what it will do it will simply sort the data based on unit price okay in the ascending order and it will assign the rank ranking 1 2 3 4 5 6 perfect so this is my row number okay let me just add the alias like this row number then let's say I want to um apply rank function okay now what is rank function we just understood let's try to apply it rank over order by unit price what it will do sort the data And it will simply apply the and let me just add single quotes because this is a reserved keyword. I will simply say rank. Okay, perfect. Then I will simply say dense rank. Make sense? D rank over. Okay. Then I will simply say order by unit price as um 10th rank. Makes sense. Makes sense. Makes sense. So dim product makes sense. Let's see what happens. Okay. So let's see. So if I just look at the data, let me just zoom a little bit. And I think they should also provide the zoom button here as well. I don't know why they don't provide. Okay. Yeah. And what is this? This is search button. Yeah. Okay. It's fine. I can just use my built-in zoom. So if I see it here. So this is my 11.82. Make sense? So you will see here we have 1 and 2 and 3 and four and five. Like these are all the different values. That's why we are seeing the same results. Let me just try to scroll down when we have same values. Maybe. Uh, do we have the same values? We should have. Um, okay. 222 224. Okay. So far all are diff. No, we got it. We got some same values. Okay. Where is that? I found one. Okay. Okay. Okay. It's here. It's somewhere here. Okay. 451, 453, 45. Yeah, perfect. I got it. So, just have some focus here. So, let's say we have this data. Okay. What is the price? 8.91.86. So, rank is 454. 454. 454. What is the price? 8.92.75. What is the rank? 455. Obviously, because it doesn't care, right? 455 45 makes sense because this is a a bigger value. Then same value 892.75. The row number is 456 because it doesn't care about your same value. It will keep on increasing. Perfect. Then rank function stopped at 455 because both has same values. 10th rank also stopped because both has same value. Okay. Now we have the bigger value 89 3.80. This is like normal 457. But here the record is starting from 457 not from 456. Why? Because 455, 456 and 457 make sense. It will not start from the very next number. No, it will first of all go through each iteration and then it will start the counting. Whereas 10th rank will not start the counting based on the iterations. it will keep on starting the uh counting just from the very next number. This is a live example for that. Oh, that was good. That was good. And you know what? I have one more feature in my ranking function. What's that feature? It's called partition Y. So currently if I see it has just keep on giving me the rankings, okay, based on the values. What if I want to reset my counting based on any kind of partition? So we know that in our data we have multiple categories if you know. Okay. And in those categories I want to reset my accounting for each category. H that's interesting. So you can just do that as well. Not a big deal. How? Let me just show you. So what I will do it's very simple. You will simply say uh here. So before order by you need to write one thing partition by let's say category I want to reset my accounting after category make sense let's say category and category perfect make sense and what is wrong here nothing is wrong we just simply forgot this thing and perfect so see now this well let me just select the uh where is the partition by? Oh, this one. Okay, let me just add a category as well so that you can just see um category. Yeah, perfect, perfect, perfect, perfect. See now you will see that first of all it gave me the ranking for books. Okay, from one and after books products are completed after 87 it reset my counting for clothing. See it reset my counting for clothing. Wow. Literally wow. Literally wow. Yes. Literally wow. So how it is possible? How it is possible? Because we applied partition by on books basically not on books on category. So whenever it gets a new value for category it resets the counting. Oh and it again starts from one. Oh so that's how you can just apply partitioning. Make sense? That's how you can just apply partitioning. Okay. This is the concept of ranking function. Rank dense rank and row number. And remember I was just talking about I was just discussing about the real-time scenarios. Yes. But after covering some more complex concepts such as subqueries and CTE because that will be required for those concepts. Once you master CTE and subqueries then you can easily easily easily go with those scenarios that I'm just talking about. Make sense? Okay. So we'll be just covering those scenarios in that particular section. Very good. Very soon it's just like um now ranking functions are done, window functions are done. Now the next topic is subquery. So let's try to master subquery and then CTE and then almost done. Then we going to just tackle some realtime scenarios and then at the end you will also learn something called as procedural programming. Um whenever we when you will be building procedures in SQL and functions okay that's it. That's it. That's all your SQL. Okay. So now let's try to learn about subqueries. What are subqueries? What are subqueries? Let's see. So now let's talk about the subqueries in SQL. First of all, subquery I know is a very popular topic. I know one of the most those hyped topic but concept is very very easy as the name suggests subquery that means query within query right. So subquery means subqueries are queries written inside another query basically a query within query. Okay, they are used when you need to get a result from one query and use it inside another simple I I don't know like how to explain this um in much more simpler steps. It is so easy. For example, still I have some use cases so that you can just use to understand. Let's say you want to write a query that needs a query within that. For example, a very good example, let's say um you have a table. Okay, you have a table of let's say let's let's take the same table products. You need to filter out only those records which are which are greater than the average price. You will say yeah this is so easy. Okay, just just just do it. Just do it. You will say an see let me just tell you. Yeah. Yeah. Tell me, tell me. You will say, see, I will write select average of unit price. Okay. From table, let's say from product, I will get one response. Okay. I will note it down and I will say I will write my second query. Select aix from table. Let's say product. and I will say where unit price greater than then noted value let's say x that's it yes your approach is absolutely correct but I don't want to use two queries I want to achieve this using only one query oh how we can just do that just just think about it you have the answer you are almost there so now the answer is You just need to replace this hardcoded value which is X with this query. Oh, okay. You just need to replace this hard-coded value which was the output of this. You just need to put this query here. Okay. So, what will happen? First of all, inner query which is this one will be executed. Okay. It will return some value. Let's say the value is 150. Okay. Then this outer query will be running select SD from product where unit price greater than X or basically 150. This is the whole concept of subquery. You got the application because see now you need to use a query within query right. Similarly whenever you want to use a query within query you can do that. Whenever you need to uh remember it you need to uh think like hey um I need to write a query. Okay, there's a requirement first of all and in order to fulfill that requirement, I would need to write multiple queries. So instead of writing those multiple queries, I can feed queries within queries. H that makes sense. That makes a lot of sense. Let me just show you with the help of code then it will make sense, sense and sense. Just sense okay let's see. So let's create a new file and let's call it as let's say chapter 9 obviously and the heading will be subqueries. Okay, subquery. So let's let's first of all query the product table. Select ax from dim product. Make sense? Perfect. What is the average price of unit price? So we know that we can simply say average unit price. Make sense? Select average of unit price from dim product. Okay, make sense? So if I just run this what I will get? I will get this value 495.7960. I can note down this value. Let's say I note down this value here. 495.79. Okay, perfect. Now this is my first query. Okay. Now I will write my second query. Select a strings from dim product where where let's say where unit price is greater than 495 because that was the output of the previous value previous query. Right? Let's try to run this. It worked. It worked. But now I'm saying I don't want to run these two queries differently separately. Then I will simply say I will copy this query and I will paste this query here like this. So this has become now my subquery. See just see this. Let me just run this. You will see the result. See same result. Now what is happening? What is happening? First of all the order of execution will be like this. First of all, the inner query will be running. Okay, it will generate the output. The output will be a number and then it will let's say the number is 500. Okay, 495. Let's say 495. So this will return this number. Make sense? Then this query will be just comparing this value unit price greater than 495. Simple, simple, simple. Make sense? Very good. So this is the whole concept of your subqueries. Okay, sorted. H. Okay, makes sense. Makes sense. Now, you can use this subquery literally anywhere. Literally anywhere. Let's say you want to use this subquery as a table. What do I mean? Let's say this is a result, right? Let me just copy this thing. Or let's say let's let's try to just copy this. Okay, let's say you want to treat the output like this output as a table. So you can even use the subqueries with the from clause really. So currently what we are writing or what we used to write in the previous sections we simply write something called as this table name directly right directly table name. Yes. So here now instead of writing this table name I can literally write any query. So do you know what will happen? Whatever the result of that query will become or basically will be treated as a table. For example, for example, this whole query, this whole query is returning this result. Yes. Yes. Now let's say you want to further query this result. this result, not this dim product, no just this result. Then you can literally put this whole query in the from clause and you can literally query anything on top of it. For example, I want to use this result as my table as my I want to treat it as a table. Okay. And then I want to query let's say I want to fetch this product name which is called figure method. Okay. Now let's try to do it. Okay. you will see the results. So I will simply say select ax from not from table name. No no no no no no from this query this whole query. Okay. And you can even provide the alias if you just want you can say better naming. So I can simply say as um subquery. Okay. So this whole result this thing is now a subquery. Okay, we all know that and this whole thing is now being treated as a table which is called subquery. So you can treat it like you have created an in-memory table which is not available here but it is it is a kind of in-memory table. Oh, and table name is subquery. Whatever you want to give. Okay, let's say subquery table. Okay, whatever. Makes sense. Now I can literally put So now it is simple from clause. So now just forget about this code. Forget about this code. This code. Now you can just imagine you are simply quering this table. That's it. I will simply say where um I know that I have this column. Let's say product name. I'll simply say product name equals figure method. Make sense? Let's try to query this. Let's see if it works. Perfect. It worked. So what actually happened? Let me repeat it. So first of all, we prepared this query. Okay. So now you can see imagine if I would have shown you this query earlier you would be like what is happening here three three select statements what now you know what is happening and now you have written this complex threelevel select statement it is not an easy task but now you know how to write it why because of your passion okay not because of me because of your passion so now what is happening here first of all we prepared this query, this one. Okay, this query. Okay, what is this query? Simple. We wanted to find all those products which were greater than the average price. And now in order to find this average price, we already wrote this subquery. So this is the final result end table that we got. Okay, we were happy with this. Now I added one more complex challenge. I said like, hey, you want to treat this as a table. So we can literally put the whole result into the from clause. So what we can do we can literally put anything in the from clause that we want to use as a table and obviously it should be a table. Okay. The the returning result should be a table because the result the returning result of this query is a table. It's not just a static value. Make sense? So you created this particular query to use it as a subquery. Make sense? And now you can simply see the result and just imagine you have written down this kind of query. Feeling happy? Good. Very good. This is all about your subquery. Why do we use subquery when you just want to perform multi-step transformations? Because you cannot transform everything in one go. You have to go multiple transformation. You have to just go through multiple iterations. Make sense? You have to go through multiple iterations. That's where you use this kind of you can say multi-step subqueries. But trust me and just be honest if I would have shown you this query directly or basically let's say um I started this chapter hey subquery and I would have shown you this query you would be like very very mad right you would be like what is happening right but now you know what is happening so very very good because the thing is being a data engineer you need to write those multi-step queries I call it as multi-step queries when you just perform one step then you go ahead then you go ahead then you go ahead it makes your code looks more readable plus obviously you need to perform multi-step uh transformations make sense this is all about subqueries now you will learn the brother of subquery it is called CTE common table expressions one good thing you can either pick one thing subqueries and CTE or CTE basically um when I started I actually loved subqueries more but eventually you have to learn both because it is good for your growth but yes you can use one thing okay and it's fine you can either use subqueries or CTEs which are better it depends upon the requirements sometime we need to prioritize performance sometime we need to prioritize code readability sometime we need to prefer multiple iterations so multiple things are there but yes subqueries are alternative to CTE or vice versa CTE are alternative to subqueries simple um if you will ask me I will say um just pick on your own because everyone has a different learning taste, right? If you prefer subqueries easier or if you just find subqueries easier, go with it. If you find CTE is easier, go with it. Solve more and more more more and more more and more questions, more and more challenges. Then eventually learn the second thing as well. Let's say subquery or CT. Okay, but yeah, in the beginning you can pick one. It's your choice. So that's a good thing. That's a good thing. That's a good thing. Okay, when I was learning, I picked subqueries. So I digged more in subqueries. Then eventually I learned CTEs as well. Okay. Yeah, it's your choice. Make sense? Both are easy. Both are easy. Let me just tell you about CDs as well. Let's see what are CTs. CTS are called common table expressions. So now it's time to focus on CTS. That means common table expressions. What are CTS? Ana you said that these are a kind of alternative to subqueries. Yes. Yes. Yes. Yes. So I will just show you the code okay and I will just show you the implementation as well and we going to just you can say approach this particular topic with the same example that we used for the subquery so that you can just um you can say understand better okay so you will also learn how you can just create multiple cities same way when you just created multiple subqueries right so it's really really good and you will I will also show you one uh option when there is a blend of both using subqueries and cds because it's not like you can either use CTE or use subqueries. You can use both in within one query. You can use both. It's your choice. But usually um there's a personal you can say choice when just going with either way. Uh what I do what I personally do I always start with subqueries like always start with subqueries when I see that okay there are already two to three subqueries. Now if I just want to let's say use those hierarchical subquery then I will just go with the CTE. So after two to three subqueries I will just start using CTE. So that's my choice and you can just also switch accordingly. Okay and both are very very simple. In a nutshell CTE in a nutshell is nothing but just a temporary table similar to subquery. Make sense? So as you know like with the subquery we can just use subquery anywhere anywhere but CTE are little bit specific you can only treat CTE as tables for example you cannot u put your CTE in the where clause you cannot put your CTE uh let's say in any condition no you have to use CTE in the from clause that means you have to use TTE whenever you just want to create a temporary table or basically table in memory. So subquery is quite flexible. You can just treat it as a table, treat it as a condition, anything. But CTE are just you can say specific version of subqueries. Okay? When you can just treat your result as a temporary table. Make sense? Makes sense. Makes sense. Makes sense. Very good. So now let's try to see the code then only you will understand better. Okay? Let's see. So now let's try to look at this code. So this is subqueries. Now let's create a new SQL file and let's call it as chapter 10 and we going to create CT or you can say common table expressions. Perfect. Comment table expressions. So basically let's take let's take a simple example before jumping on to that complex example. Let's say I have a query like this. Um let's say so let let's take that example because that was not a complex example because you will you can compare parallelly right. So let's say I have this query this one only this one I'm not using the below one okay just a basic one. Let's say I have this query. Let me just run this code. So what I will get I will get this result. Okay. And here as you know I'm just using the subquery in the wear clause and I have to use it. I cannot use CTE within this condition. Remember we only use CTE when we just want to treat the result as a table. That's it. You cannot use it to compare your values. Apply greater than less than no. Okay. So let's say um now we are just comparing the table version of subquery and table version of CTE. Remember in the previous example this is the query we created when we wanted to treat our result as a table right so this is the comparison this can be replaced with the CTSE okay so for example this is the result I want to treat this result as a table okay and I do not want to use CT so what I will do I will simply say uh so this is my query I will remove the you can say semicolon so what I will do I will simply write with then name of the table that I want to give temporary table or you can say name of the CT common table expression so I want to name it as let's say CTE table okay then I will say as okay then I will encapsulate the whole query in the braces perfect that's it that's it yeah that's it now this whole query this whole query is stored in this particular table. Wow. Yes. Now you can literally write your normal queries as you write. Let's say select a from CTE table. That's it. Let me just run this. If I run this thing, I will get the same result. Why? Because I'm just selecting a let's add a condition as well. Let's say I want to filter only um product name equals to figure method. Same way that we were just doing here, right? Let me just add this wear clause. So let's add this right. Let's run this. Same results that I'm getting here. And if I run this, I am getting the same results here as well. See? Perfect. So this is the way. This is the way. Now you will say this was very easy. I know. I know. I know. I'm not saying that CTS are difficult. It's just like the personal choice. I know some of you will say CTS are simple. Some of you will say subqueries are simple. Some of you will say both are simple because Anela you have just talked about both the things. So now both are simple. I know that's the best thing if you just um feel like both are simple. Very good. Okay. So let's say this is my table. Now a quick question or you can say querious question can be asked by some of you. Hey Anlam, this is done. This is done. Let's say this table is created. Now let's say I want to treat this result. Let me just change the result. Um, let's say I want to Oh, by the way, let me just show you one more command which is very very important. So, we just saw equals to sign. We saw greater than sign. We saw let's say less than equals to sign all those signs. But there is one more sign basically not sign operator. It is called in. Inoperator. Inoperator means when you want to pass a list of values, basically pass multiple values. For example, if I just remove this statement from here and if I run this, I will see so many things, right? So let's say I want to filter the values equals figure method and huge change. Okay, just these two or let's say one more film finally. Okay. So one way is I can just literally write um multiple equals to equals to equals to that is a mess. So what we do usually we simply pass a list and for that we have to use this particular you can syntax product name in we use in operator and we will simply pass a list. So I'll simply say figure method. Okay. Then second value uh it is huge change. Then third value which is finally film finally. Perfect. So these are the values that I want to pass. So what does it mean? It means that I am saying that either this product name can be equals to this this and this. Make sense? Okay. Let's try to run this. Hm. error. Why it is saying SQL version? Yeah, makes sense. So whenever we just want to pass a list, we simply use braces instead of square brackets. Okay, simple. That's just a change. Perfect. So now it is working fine. So what just happened? So now you can see only three records. Why? Why? Because now only these values are selected. Now just let's go back to our original question. So original question was let's say we want to use this result as our next table. Let's say we want to inherit this CT table as well. How we can just do that? So for that you do not need to write the same syntax as you write in the subquery. So you will simply say enclose this in a um you can say round braces or whatever braces that you want to say and this will behave as you can say hierarchical query. No, if you just want to inherit any CTE table, what you will do and how you will do? You will simply say, see, you will simply remove first of all the select statement because you are not using the select statement. Okay, make sense? Or let's say you are even using the select statement. Okay, let's say you are just originally using this whole result as your you can say result, right? That's what you want to do. Make sense? Make sense? I know that. So what you will do? You will simply say let me remove the semicolon. You will say now comma okay comma you will do not say with because we do not use multiple width. You will simply say comma let's say CTE table parent or let's say CT table 2. Okay. And I'll simply say as make sense and I can write my query. Let's say now I want to just filter out the record select ax from CTE table where product name equals figure method. So what I'm trying to do here if you see what I'm trying to do. So now I am just trying to first of all this was my result. Okay. And this was my result from this query. Just forget about the new query. Now this was my result and I'm happy with this. But this is just like one level of CTE. Now I want to use this particular result subset subset subset as my let's say next query. I want to use this table right. I want to use this table. How I can just do that? So for that particular purpose you will create another CTE. What another CTE? Okay. So how you can just do that? Now you will wrap only this query not the entire query. Reason I have already mentioned let me just repeat the reason is now you are just concerned with this table. The easy way to remember is forget about this query. Forget about this query Q this query because now you are treating this as a table like a normal table. See here you are using we are dim product. We know that this is a product that is saved in this particular catalog. But you can also imagine that this is also a table which is saved in this particular catalog. Simple make sense. So now you need to imagine that this is also a table here. This is the easy way to remember. So you will simply say wrap this particular query in another CT. The same way you wrap this pin product. I know it is a little bit confusing but yeah it's fine. So oh by the way why did I add comma here. So it will not be here. So it will be like this. Let's say let me just cancel close it. Comma and then this. Yeah perfect. So now what will happen? First of all this is the query. Okay. This is the query that we have. Makes sense. So what was the query? It was saying that hey this is my query. Okay. Makes sense. Now let me if if I run this query what will be the output like whole table right which has price greater than average. Makes sense. Then I use this query. Okay this one makes sense. I obviously I cannot run this because um it is inside this particular CT. So then I just created this query. Now you said I want to use the result of this query. Okay. So what I did I simply created another CTE. You will say why didn't you write with command? Because we do not write with command multiple times. We just write with command only one time. Okay. Then I simply wrote here second CT as this command. That's it. Then I can simply say cd table 2 where product name equals wait where product name equals because we didn't copy the command. So it will be like this because this was the result right. Perfect. Now we can simply try to query this select from CTE table 2 because this is our new CT name the latest one where product name equals figure method. Make sense? I know this is confusing. I know why. See it was so easy for me just to tell you the basics. just one level of CT. Why I'm just telling you the second level of CT? Because I know it is challenging but you need to work on it bro. I am not the one who will just tell you the basics. I know basics are important but after the basics you need to master the things and you need to understand the core concept. I know it is difficult. I know but with the practice with more and more practice it will be easy. You can just rewatch this part two to three times 10 times. What's the harm? It's for your benefit. Simple logic. Okay. So now let's try to run this query. I should see only one record. That's it. Make sense? That's it. And obviously you will get this particular script as well that you can just copy and paste if you are just facing any kind of syntactical error. It's a win-win situation for you. I don't know like why are you feeling like oh what is going on bro? It's just for your benefit. I know I know I know it is like a little bit confusing but you need to understand this make sense. So this is like CTE and that's why I said that usually people prefer subqueries in the beginning then they eventually you start using CTE and then they will be like familiar with CT. So um it's like your choice as well. If you just want to stick with subqueries in the beginning, it's fine. It's fine. Just have the basic understanding of CTS just one level of CT it's fine. Then eventually practice and adopt. That's it. Adopt adapt adopt make sense I hope because currently like I don't know like how to just tell you this thing. Okay very good. So so this is also done. CTS are also done and you have seen that how to just work with comment table expressions and all those things make sense. Now I just want to talk about some realtime scenarios that I was just talking okay in the previous sections that hey just we want to just talk about some realtime scenarios which can be asked in your interviews and trust me I am just not covering just the interview questions and all those things I just want you to become eligible enough to solve any query to solve any problem to to solve any SQL problem. Make sense? So let's try to cover two to three scenarios because those two to three scenarios are your building blocks. Make sense? Building blocks literally building blocks. Okay, makes sense. So now let's try to cover those scenarios. And once we are done with those scenarios, then we will be just shifting our focus to stored procedures and functions and obviously the views. As you can see there are three more items here. Views, stored procedures and functions that you want to master after tables. Make sense? Very good. So what are those things and how we can just work with those things like realtime scenario and you know what I will also tell you about two new functions lag and lead. Why I'm not covering those functions as a dedicated topic because I want you to learn about those topics through realtime scenarios. So you will also learn about lag and lead and again those are also window functions. Okay, lag and lead and you will see so many situations based on these two functions lag and lead. So many so many lag and lead. Okay. So let's try to focus on that part. Let's create a new SQL notebook and let's call it as realtime scenarios. Okay. Scenario one. Scenario one is very very simple. What's that? We want to we want to let's say scenario one. Let's cover u finding the nth value. Okay, finding the nth value. Finding the nth value. Okay, let's start with this. So just to showcase the example, let me just first of all describe the scenario. So let's say you have this data dim product. Okay, make sense? Now I want you to fetch. Let's say a manager comes to you and he or she says I want to get um fifth most expensive product we have in our company. Fifth most that means fifth top fifth. Okay. How you can just find that? How? Do not say hey we can sort the data and we will simply apply limit five. Remember you just showed us that way. That is like very very noob level thing. Let's say that person will say just help me finding the um 99th okay 99th top 99th. So in in those scenarios you have to just use that logic using window functions. Okay. So before approaching um the solution you need to understand the architecture like how we can just approach this. You have the right knowledge by the way. So you can also think okay you can also think just pause the video and think like what can be the way just build the highle you can say approach on your notebook right now because that is the you can say the core concept of this video that you are able to tackle these scenarios you have the right knowledge so I hope you have done that so let's actually see how we can just approach this basically we want to find in this example top fifth Top what? Top fifth most expensive product. Make sense? Okay. So, in order to do that, we simply need to sort our data based on the ranking functions which are window functions. H but we have three different window functions for ranking. Row number, rank and dense rank. What do you think? What should be the best option to pick it here? The answer is dense rank. Why? Because it will not skip the rankings. Okay. So you will simply give the rankings and once you have all the rankings then you will filter out the product based on number fifth or number any any n any nth n can hold any value. Make sense? Very good. So now let's try to approach that uh question. So first of all I will simply say select a strix and then comma then I will say 10 rank. Okay. over order by unit price. Okay, unit price as let's say ranking. That's the column name because I will be just filtering it out. Okay, ranking from dim product. Make sense? Simple. Now let's see what it will return. If I run this, I will get all the ranking. 1 2 3 4 5. 1 2 3 4 5. Yes. Good. Very good. Now what I will do? I just need to filter out the fifth the fifth most expensive product. What I can do? You have two options. You can simply use having here. Okay. But I'm not a good fan of doing this through having. I am a big fan of doing this through a subquery. So what I will do? I will simply wrap this whole this whole result in a subquery and I will then apply a simple filter function on top of it. Why? Because this way you will understand how to integrate all the pieces of SQL. Make sense? So what I will do? I will first of all wrap this query in like basically wrap this result. Okay. Then what I will do? I will simply say select ax from from what? from this query from this result set where uh unit price not unit price basically ranking ranking equals five perfect let's run this so I will get only oh I I got the error why why why let's see um okay from where ranking equals to five so what is the error every drive table must have its own alias. Yeah, makes sense because we didn't provide any alias here. So, let's say um subquery not a big deal. And we can say subquery. Remember, we always use the alias name whenever you just pass it to table. So, that's what that worked fine. Okay, perfect. So, this is my result. This is my result. See, subqueries are always better to use whenever you have like two to three level of hierarchy. Make sense? So that's how you can just fetch only the fifth top fifth like most fifth most highest um you can say brand or product. Let's make it a little bit more challenging but it will be very easy for you because now you know the concepts. I want to find top fifth um product of each category not like in the whole list but in the each category. So what is the concept? We will simply use partitioning right? So we'll simply say partition by partition by category. Make sense? If I run this what I will get all the categories are here with fifth most expensive product. So item woman um is the product name in the box. Clothing weather today electronic student code and all those things make sense? And now n value uh n value uh can be anything. and value can be let's say top like top third or anything make sense. So that's how you can just do it. H if you if you just um observe the result we are actually not finding the most expensive we are just finding the most affordable. So we have to use one more thing here DCE that means descending order that's how you will just get the most expensive one. But that's again the requirement. You can you can be asked let's say the most affordable top fifth most affordable. So yeah I have shown you both the ways. So now you should be good. So this is like scenario number one. Okay. This is scenario number one. Now what we need to do. Now we need to look at the scenario number two. And this is very very important because this is not just important for interviews. This is important for your day-to-day data engineering activities. And we use it on a daily basis. Okay. Scenario two, removing duplicates. Removing what? Duplicates. Okay. So for that, let's quickly create a table. Okay. Let's create a table. Create table. Or we already have a table. Let's insert some duplicate values. Let's say I have a customers table. Select from customers. Let me first of all look at the data. So we have ID, name and email. Let me just add this ID again. 301 Lamba CC. Okay, let me just do that. insert into customers or customers values and I will simply pass let's say 301 lamba which is the same value duplicate and then cc okay and I will add one more record which is not a duplicate just a new record let's say 101 okay and I will say of and I will say aa perfect let's run this thing and perfect now let's query this data so now if you just look at the data we have some duplicates but you will say an llamba duplicates are not actually allowed how we just got the duplicates it's not the scenario in the world of big data we can receive duplicates so it's our responsibility being a data engineer to remove the duplicates. So how you can just remove the duplicates. So first of all let me just explain you the concept and we need to do it using row number because that is the most preferred way to do that and in the interviews interview can literally ask you hey just do it using row number simple. So what we need to do we simply need to use row number here but you will say lamba you told us that row number doesn't even care about duplicates. It will simply keep on generating the values 1 2 3 4. So if I just use row number let's say like this. It will give me 1 2 3 and four. Make sense? You are absolutely right bro. So how we will just be able to find the duplicates. So I will do something special. What I will apply a partitioning O on the key column on the primary key. So I know that I have the primary key equals to ID. Yes. So in the partitioning we know that counting resets. Okay. So if I apply partitioning on the key column, what will be the output of the row number? Um for 201 it will very good start. Then for 30 um it's a new ID and you have applied partition by on ID. So obviously it will start from one again. Very good. Now 301 um it's not a new value and row number doesn't worry about any kind of duplicate. So it will say two. Very good. For 101 it's a new value. So it will simply reset the counting. So it will return one. Very good. That's what I wanted to tell you. Now just tell me one thing. What do you need to do? Your 90% of work is done. Really? Yeah. So now you simply need to filter out the records with row number equals to 1 only. Oh, so let's say you have 10 values for 301. What will be the values here? 1 2 3 4 5 6 7 9 10. But you simply need to fetch one. H. And here, how many values do you have? Only one. That means no duplicates. Yeah, it's fine. We want this value. How many values do we have here? One. That's what we want. So we simply use row number to provide the ranking based on the key column and then we simply fetch the values equals to 1. That's it. How was it? And do you know what? This is one of the most challenging part when someone asks you hey you need to ddup basically dduplicate your data. Ddoop is a very common word. You can just take notes for this that how you can just apply ddup or dduplication. Make sense? So you need to just understand how you can apply dduplication. Make sense? Good. So now let's try to write this code and it will be fun. So first of all we will simply apply select a bricks and then row number. Okay. row number over partition by ID. Make sense? And then order by order by order by can be literally anything because we are not actually provide the ranking based on any unit price. No. So simply say order by on ID. Simple. Okay. And simply remove comma here. Perfect. So I will simply say as ddup this is my ddup column. Okay from customers make sense. So what it will do you will see the output. So it will give you 1 1 2 1 same thing that we just discussed. See 11 one and then two. Why here two? Because now this is the same value. So it will not reset the counting. So it will just keep on adding the number. That is why row number is very very handy whenever you just want to apply dduplication. Make sense? So this is my query. So what I will do now I will simply say encapsulate this whole query okay in a subquery and simply remove it. Let's say select subquery dotrix from this query and this query name is subquery where ddoop equals to 1. Simple perfect perfect. So that's how you can just find the right data. So now we don't have any duplicates in our data. Perfect. That's how you can just remove the duplicates. Make sense? Very good. Very very very good. Very good. So now let's see our first of let me just save this chapter 11. Perfect. Now we need to just see um not chapter scenario three. Scenario three and this scenario is lag and lead lag and lead. These are two window functions that go hand in hand. Basically what a lag and lead. Any guesses by just looking at the names lag and lead. Lag and lead functions are used to get the previous value or the uh you can say subsequent value. Let's say preceding value and following value. Got it? No. Let me just show you. So let's say you have some data. Let's let's take a very good example. Um let's take an example of Yeah. So do we have a good table? So let's say fact sales. Nah. Dim customer, dim date, dim product, dim store. Let's say dim store. Let's try this one. Dim stored. Actually let's try to create our own table because it will be very handy for you as well to understand this because I I want you to understand and grasp this concept not just like writing the queries. So let's try to create a table. Okay let's take try to create a table called weather. Okay weather and this will simply say um ID weather ID where ID is just a representation of day. Okay make sense? Okay. So, ID of integer type and temperature. Temperature is of float type. Float is just like decimals. Okay. Makes sense. So, I want to create this table. Makes sense. Makes sense. So, now let's insert some data. Insert into weather values. Let's say on the first day the weather is 10° centigrade. And on the second day the weather is let's say 12 let's say on the third day the weather is let's say 9. Okay. And uh on the fourth day we have weather equals to 15. And on the fifth day the weather is 20. Wow it's getting hot. So on the sixth day the weather weather is down again 15. And on the seventh day, the weather is really really beautiful and the weather is let's say 12°. Perfect. So let me just insert this data. Okay. What's the issue? Um you have SQL syntax. Okay. To use 520. Oh, we forgot a comma. Okay. Let's run this again. And it worked fine. Perfect. So let me just remove this code because this is not required now. Perfect. So now what we need to do, let me first of all show you the data. Select axis from weather. Perfect. Perfect. Okay. So this is weather data and you can um say like this is day one and temperature is 12 and so on. Perfect. Perfect. Now what you need to do you need to create a column okay for each record. Okay. Where you need to display the temperature of previous day. This can be the requirement, right? Where you need to see the temperature of previous day. Makes sense. It can be. Yeah. Because in dashboards we just do like you you can just do the this these things right. So how you can just do that the um temperature of previous day. So here you need to use a window function where you will apply rowle transformations. So what you will do you will go to this row and you will see the previous temperature. There's no previous temperature. So the value will be null. Common sense. Then it will go to this row. It will see the temperature of previous row which is 10. So it will simply write 10. Then it will go to this row 9. It will simply display the temperature of previous which is 12. Make sense? So that's how it will return our data. Make sense? Let's try to do this. And you need to also display the reverse of it. Let's say you want to display the temperature of the next day. You will say how we can just display the temperature of next day. Maybe it's kind of prediction. You have the data. You just need to display it. Oh, makes sense. Yeah. So, let's try to create two columns. One for previous day and one for the next day. Okay. Let's do it. Select a str. Okay. And I will use function called lag. And now you simply need to write the column temperature and what is the value of lag? How many days you want to go back? Just one. Okay. I will simply say as previous day temperature then lead that means I want to go one day ahead I will simply say as next day temperature make sense from weather. Okay make sense and what is the problem here as is not valid at this position. What do you mean? Um, is there a syntax error? Let let's try to run this. Uh, let's see. You have an error. MicrosQ syntax as previous because they keep on like just changing the syntaxes in every SQL server. I don't know why, but yeah, it is what it is. Okay, so it is saying uh previous day temperature. Okay, let's try to see the syntax for lag and lead. Let's see lag in MySQL because I hardly use like MySQL these days. So, it's fine. It's fine bro. Oh man. Oh man. See silly mistake. What? What? What mistake? We are writing literally writing a window function. How we can just forget about over command. We cannot we cannot we cannot write. So we have to write this particular thing over. Oh man. See I was like what what's wrong? What's wrong? Nothing is wrong. Just a silly mistake. So basically what we need to do we simply need to say this is our function lag. Okay. Now over obviously with runo function we always use over function right. So here we do not need to apply any partitioning. We will simply say order by order by. Now order by command is very important here. Why? You need to find the temperature of previous day. So for that you need to first of all sort your data. Right? So on which column you will sort your data? Obviously on the day column which is ID. Okay. on ID and by default it will sort the data in the ascending order and that's what you want but you can extensively write it as well like this make sense and similarly here as well perfect so now let's try to run this and it should run fine perfect now let's try to see the results and here are our result so what it is doing so let me just zoom it here perfect so what it is doing so you will see that this is previous day temperature on the Second day the previous day temperature was 10. Makes sense. But why it is null here bro? I just mentioned that there is no temperature on the zero day. No that is why null. So 10 12 9 15 20 15 and here we have null. Why? Because we do not have temperature for the next day. So is there any way we can just remove these nulls? Yes, definitely you can just choose a default value as well. Let's say um not available or something like that. Let's say you want to say um zero zero yeah kind of like you can just pick any value right any value so let's try to run this and what will be there zero instead of null that's how you can just save it okay anamba a quick question can we change these values like which value let's say I want to find the temperature of 2 days prior instead of just one day obviously let me show you you can do that as You you can simply change the value from 1 to two. Perfect. And you can simply say previous 2 days temperature. Okay. Let me just run this and you will see here the data. Perfect. Perfect. Here you will see on the first day previous two days zero. On the second day previous two days zero obviously because it is asking for the 2 days prior. On the third day what is the value of 2 days prior data? 2 days prior data it is so simple it is just like 10 right 10 make sense understood oh man see it is the 10 so it will come here right 2 days prior see on the third day let's say today is Wednesday's day 3 is Wednesday's day and on Monday it was 10 so you want to see 10 here right so that's how it works so I hope it makes sense and I hope you understood the lag and lead function as well because lag and lead functions are very very important literally very important important and it will help you to just solve so many queries, so many questions, so many scenarios and so much of things. Makes sense. Makes sense. Makes sense. Makes sense. Now, finally, let's talk about views. Let's talk about views. Let's let's do it. Let me just create a new SQL file. And let me just save it called chapter 12. How many chapters you'll be creating? Um I think 14 or 15, I guess, because yeah, one for f, one for views, then one for store procedures, then one for functions. Perfect. So what are basically views? Views are just views. So what are views? So basically whatever we are doing right now, whatever query we are writing, let me just take any example. Let's say um let's say this one. Okay, let's say this one. Let's say this is a query. Okay, this is a query that I have written. Make sense? This is my dduplicated data because I knew that I will be having duplicated data with my normal source but I want to dduplicate it. Perfect. But this is just a query. So every time every time if I just want to ddup my data I have to run this query. Yes. And then only I'll be seeing seeing this result. Yes. Is there any way that I can store this query somewhere and I can literally write basically I can literally run that query in just one line so that I do not need to run this whole set of query. Why it is a big deal? Because for now it is just like 13 lines of code. In real world let's say you have literally written 100 lines of code. So you have to write that query again and again plus you have to keep that query secured because you'll be running that query. Is there any way that you can just store this query in a variable in in an object and you can simply query that object and you will get the same result. Is there any way? Yes. The way is creating views. So what we do let me just show you um visually as well. So what we do let's say this is our table. Okay this is our table and this is basically the result. Okay, resulting query resulting query result whatever you can say. Okay. So we know that we run a query. Let's say some query some text talk talk. We return this query. Right? So we know that we run this query and then this table appears. Makes sense. Now we need to store this query somewhere basically in an object so that we can directly run that object instead of running this query and we will simply get the data. Hm. So here comes the concept of views. So I'll simply create a view. A view on top of this particular query. Perfect. So what I will do let's say this is you. So what you will do next time you will simply run this view which will automatically run this query and you will get this result easily. That is the concept of view. View is just a stored query. You have stored that query in an object which is called view which doesn't have any data. No, do not feel like we are storing this data in a view. No, view doesn't hold any data. View cannot hold any data. It is just a query. You have simply stored this query inside this view. Make sense? And you are simply using or basically querying this view. Make sense? Basically whenever you just run this object it simply runs the query within that that's it whatever query is there make sense so let's say I want to store this query in a view I will simply say create view as basically view name view name is let's say ddup view okay and this is my query if I create if if I if I run this what will happen do you know let me just refresh it you will see a view here see view name is ddup Now this view is there. This view is there. Let me just open a new you can say SQL query. Do you know what? Let's say on the next day on next month let's say I just want to run the same query. I can literally write like this. Select a stick from ddup view like this. And do you know what will happen? I will get the same result. Why? Because now this ddup view will run the query which is stored here. Okay. Not in the file. Basically it stores the query. Okay. here. So it has the query. It will automatically run the query and it will give you the results. This is the concept of view. Make sense? This is the concept of view. So you have created the view on top of your query. You can literally write any query any query hierarchical simple multi-step CTE subqueries literally anything and you can just save it in a view. Make sense? That is a concept of a view. Okay, makes sense. Makes sense. So now, now you understood the concept of view. Now you can easily understand the concept of basically stored procedures and functions. Okay. So now let's start with the stored procedure. What are stored procedures? So now let's try to see what are these stored procedures. Basic basically stored procedures are very very popular. Okay. And nowadays um it's not that much popular but yeah still you should understand store procedures because now in the world of big data we prefer um creating store procedures with our new versions which are JSONs but you still need to understand this because it's not like not like all all the solutions are like not using store procedures. Yes. So slowly slowly people are moving away from store procedures but do you know what um with the latest advancements in the SQL with the data engineering people are coming back to store procedures. So you should understand in short you should understand store procedure that's it that's it make sense okay very good in short you need to understand it okay by the way it is very very easy so basically what is a store procedure in a nut nutshell okay basically stored procedure in my SQL or basically in any SQL server and before continuing wait before continuing um stored procedure is different in every SQL server not concept but the implementation. Okay. So do not feel like this particular code um is working here and not working there because every SQL server is different. Okay. But concept is same. Make sense? So it's fine if you understand the concept using one SQL server. You can easily do the same steps or same implementation to other SQL server as well because you are smart enough to just change the syntax. It's not a big deal. Make sense? So now let's get started. So basically it says a store procedure in MySQL is a predefined set of SQL statements that means we define some SQL statements okay that are stored in the database and can be executed whenever needed. You will say an Lamba that's the same thing that we are doing using a view. So what is the difference here? See view is actually just holding your query just the select query that's it. View cannot just run your DDL command, DML command, it cannot take parameters but stored procedure can do almost all those things. Oh okay. So basically if you're coming from a programming background let's say you have done any programming using Python, C++, Java, JavaScript whatever. So you can think of it like a function in programming name. So you will say an we have functions in SQL as well and you are saying that stored procedures are functions. Listen to me bro listen to me. Functions in programming language are equivalent to stored procedures in SQL. Okay. Then what are functions in SQL? Have I started functions in SQL? No. So just wait for that. For now you need to understand that stored procedures in SQL is equivalent to function in a programming language. And do you know what I'm just trying to explain this thing so that you can just relate ultra pro legendary people are saying what is a function in programming language? We do not have any background in programming language. So bro love you. So if you are so it's it's fine. It's fine. It's fine jokes part it's fine if you are not familiar with a programming language first of all you should be because you are trying to become a data engineer or you are just trying to um be someone who will be just working as a data professional you need to okay if you are not coming from a programming background basically function is like when you just store something okay within you can say an object and you can just call it whenever you want okay and again I would say just go and just cover your fundamentals, basics in um programming language. You will say Anala, so what programming language should we do? Um my personal favorite, my pick is Python. And in today's era, Python is everything. Okay, so just do and pract and what is the resource? If you want the best resource for Python, who is this? Oh. Oh. So, so what what I was saying? Okay. Yeah. I was about to tell you the best Python resource. So, basically, if you want to understand Python from scratch and if you do not have any experience, literally you do not even know what is Python, you can watch my YouTube video. Just go on YouTube and just type Python an Lamba. Okay. So actually you will get two videos. One is for like Python for data engineers in six hours that is from scratch. The other one is AI for Python basically Python for AI that is like a kind of second part of it that is like advanced Python. But before learning and learning advanced Python you need to cover the basics. So just watch that 6 hours long video that will cover all the fundamentals plus not just fundamentals. Same same like with same like this video. This video started from scratch and now you know like the level that we have reached right same with that Python video as well. Just go cover that video and just become a master of Python because it is required. Okay, make sense. Yay. Woohoo. So let's try to just write the code for store procedures. Okay, let's see. So now we are here in our MySQL workbench and my check my check. Okay, perfect. So now let's create our first store store procedure and let's create the store procedure. Perfect. So third procedure by the way let me just show you the Python resource as well because some of you were saying that let me show you. So Python anal lamba just write python lamba and that's it. And just a quick tip if you do not know about this thing. So whatever topic whatever thing you want to learn whatever you want to whatever thing you want to master simply type the topic name and just search on llamba it would be there. Trust me. So this is a video that I was just talking about 6 hours long course and it is like started from scratch. So you can actually master this particular you can say Python video. Okay. Like Python in this video. Okay. Nice nice nice. AWS Lambda. I know Lamba is like very similar to Lambda but hey it's an Lamba. Okay. Don't make fun of my name. M okay. Getting some co-pilot recommendations as well. See see M nice nice nice h good stuff well done. Okay so now you have the resource so now you can just master Python as well. Okay so now let's try to write our store procedure. First of all write the correct spelling. Okay. So in order to just write your store procedure which I have already mentioned that is very much similar to a function. So how we can just write it? There is obviously a difference in syntax when you just write it in programming language and here first of all you need to define a delimter. Why? I will tell you why. And this delimter can be of your choice. Let's say I want to define at the rate at the rate. Let's say I want to define back slash or slash or let's say I just want to define back slback backlash. Usually we define / sl/ you will say in slab we already have a delimter which is this semicolon that we are using so far. See semicolon is a delimter. This is a delimter. Delimter is basically the you can say character which tells us hey this is the end of the code delimter. Okay. So why we are creating a new delimter? Because if you remember when we were just talking about the concepts stored procedure actually stores the queries. So it can store multiple queries and obviously each query will be delimated by the original delimter of the SQL which is a semicolon. So we cannot differentiate between if it is an internal delimter or external delimter. That means if it is a delimter of the query which is stored or the delimter of the stored procedure itself. So what we do first of all we create a delimter for this toward procedure. Make sense? So delimter is double slash make sense that is a beginning. So now I will simply write create procedure and procedure name. Procedure let's say first procedure and you can just provide any name if you just want fourth procedure. Okay makes sense. And then open braces. Why? because we always just pass the parameters within that within that it is not compulsory but usually you will pass the parameters I'm so sure okay because that is the purpose of creating the store procedure right so let's say you just want to pass a parameters let's say I just want to pass it can be literally anything let's say I just want to pass um parameter which is p id okay and this is of integer type make sense I want to pass key name. Okay. Or let's do one thing. If I just open this table which is called customers, we have three columns, right? ID, name, email. Let's do this. P ID P name and name will be let's say character of let's say 100 and then email. Email is also of character type. Okay. Perfect. Perfect. Okay. So now these are my parameters. But do you know what in SQL basically in MySQL we also define the type of the parameter. Is it input parameter or output parameter? Now what is what what are input and output parameters? Input parameters are those which we are inputting from the user. Inputting. Yeah. Just just get the vibe inputting because I want you to understand and focus on input word. You are literally asking the customer to provide the input right and output parameter means when you are just simply returning something. Usually you will rarely use output basically out parameters. We 99% of the time use input parameters but again it depends upon the requirement. So there's a keyword called in. Okay that you need to add here. Make sense? Okay. Now you do not need to remember this code basically this syntax because um whenever you will be writing the store procedure um you will get the syntax basically the skeleton of the store procedure and if not you can simply get it from online as well and I can just show you after completing this that how you can just do that so there's no need to remember this code first of all because there's a reason behind it because syntax for each SQL server for store procedures are different okay so you do not need to remember any syntax no you just need to grab you can say understanding basically concept basically implementation that's it okay so let's say this is our store procedure that we have created okay so now we can simply say begin I will simply write begin what is begin like why are we writing begin basically begin means that we want to just now um start our coding the main coding okay and what is this bargain begin perfect now begin means now Write your stored query that you want to store. Make sense? Now write the query that you want to store. Let's say I want to insert some data. Insert into customers. Okay. And values are let's say values are we need ID but I will pass P ID. Okay. Then I will pass P name. Okay. So currently you can see I'm not hard coding the values. I'm simply providing the parameters right good then I will simply say P email perfect I want to run this thing so I now want to just end this SQL command because that is it that is the delimter which is internal make sense now our query is completed I will simply say end that's it I want to just store this query within this you can say store procedure then I will simply say delimeter why delmter like this that this is the end. That's it. Now this is actually behaving as a semicolon but external to store procedure. Make sense? And after you have done this you can create or basically reset your delimeter and you can say hey just change my delimter to semicolon because obviously you'll be writing some different code uh under this because you change the delimter to this now you're changing it back because you just want to create the delimter only for this code. Make sense? Very good. So this is done. Let's try to execute it. And I think it is done. Okay. If I refresh this, I should see this store procedure. Hopefully if everything is fine. So this is my store procedure. See function. And you can also see this thunderbolt. If I click here, you will see these parameters box. I can literally provide these parameters box and bo box. I can literally provide the parameters in this box the in these boxes and I can click on execute what it will do it will execute this function not function function in the programming world stored procedure in the SQL make sense so this is one way of doing it let's say you will say bro I do not want to do this like do this way I want to write it using the code so there is also a way so first of all let me just show you this way let's say I want to insert ID number 10 name is blah blah and email is okay. So let's say execute. So this will write the code automatically for me. See this will write the code automatically for me. Call. Call is the keyword that we use to call basically execute the you can say store procedure. Then ecom ecom is the obviously database name dot first procedure procedure name and then all the parameters. Okay, this is the code behind that load that boxes. If you do not want to use boxes, you can directly call this function. Okay, like this. Let me just copy and paste it here. So, hey. Okay, makes sense. I thought like it just removed my code. My code is here. Okay, good, good, good. So, I will just paste it here like this. And I can simply say chapter 13. Perfect. Make sense? Good. So, now this is my command. Let me just run it and what will happen? Unknown P email in the field list. Why? Oh, because we didn't use email. P email. We simply use email. Okay, no worries. We can simply do this. And now, oh, okay. So, what we can do? I can simply first of all drop the procedure because it is already there. Okay. And it is using this thing. So, I'll simply say drop procedure. Okay. And procedure name which is first procedure. Let's quickly drop it. Perfect. Now we can just recreate it. I can add P here which is like a better way of it instead of removing it from here. Okay, let's try to run both the commands. Perfect. Now it ran it successfully. So what it did it simply created this fun store procedure obviously and then now we can let's say I want to insert any data. So whenever I want to insert any data I do not need to write this statement. No, I will simply call this stored procedure and my task will be done. I simply need to pass the parameters. That's how stored procedure works in MySQL. And I hope it makes sense. I know it is very very handy and I personally love store procedures. It is very handy when you just want to perform DML commands. And truly speaking, 99% of the time you will be using stored procedures to apply DML commands to run DML commands basically data manipulation language commands. you will not be creating store procedures to um apply select statement or whatever. Okay, store procedures are 99% of time used to apply DML commands sorted. Now let's talk about the functions in SQL. Okay, let's talk about it. Now let's talk about the functions. So basically let me just give you a hint before just starting with the explanation. Functions we already understood in the programming and we know that store procedures are equivalent to functions in programming language. But what are then functions in functions in SQL like what what are functions in SQL? Basically if you want to understand it functions are basically the transformations if you will understand like this you can get the better you can say hint of it like what kind of transformations for example if you write let's if if you let's say you want to convert your category column to upper case what do you write you simply write select and then upper and then category column right okay what If you want to do something which is not available in SQL, okay, which is not available in SQL but you know the custom logic for it. Okay, so how you can just do that? You can create a function like this. Instead of upper, you can use your own function. That is the core concept of function. That is why it is not a function that we use in programming. Now it is just a transformation function. I think this should be renamed to let's say transformation function or custom transformation like this because it is it is confusing right people can think hey functions are stored procedures stored procedures are functions what are functions so you can think functions like customized transformations okay because see this is also a function but this is a transformation function so you can just rename it as transformation functions okay make sense so for example you want to square your number. We do not have any kind of square function. Maybe there will be but let's imagine we do not have square function available in this particular SQL. So what you will do? You will simply create a function called C square. Okay. And then you will use it like this. Square and then category. That's the whole concept of functions. That's it. Now let's try to look at the definition. Functions in MySQL are predefined or userdefined. That means predefined are those functions upper lower um you can say all those functions that we studied before right concat those are predefined userdefined are your functions that you are defining customized ones okay that perform operations on data and return a single value it it cannot run your DML commands and all no no just returning a value okay they are very useful for manipulation manipulating data performing calculations and simplifying query Make sense? So now let's actually see it see it with the help of code then only you will understand better. Okay let's so let's try to create our new SQL file and we going to call it as let's say um chapter 14. Oh we covered 14 chapters. That's really really nice right? That's really really nice. Okay so now we're going to talk about functions. So let's say I want to create a function called square or basically get square okay or let's say square it square it is a good name okay so how I can just do that so I will simply say create function and function name is square it okay now this square it obviously this will take one um you can say um parameter okay parameter but you know what the the good thing about function we do not need to say in or out? No. Simple. The variable. Variable let's say is x. Okay. And it is of integer type. That's it. That is it. You just need to specify the variable name and its integer type. Then you also need to specify the return type. That what type of data it is returning. Right? It returns data of integer type because we are simply doing the square. So square of integer is square. Right? Make sense? Make sense? Now you will simply say begin again. Same thing. Yeah, again same thing. Simple. Okay. Begin. Now in the begin you will simply say your code. Let's say begin. And I want to return basically um let's say x into x. Okay. You can also create a function called cube it. Square it. Can simply say one more x. Okay. And this is end. Make sense? Make sense? So now one thing that is missing just guess what? You need to add a delimter here as well. See here. And let's close it here as well. Perfect. Perfect. Perfect. So what what what is the error? Why it is throwing the error here. Any guesses? Any guesses? Any guesses? Just just think about it like what can be the issue? Just think about it. So basically you are saying return x into x. Okay, that is fine. So, what is the issue? I can run it. It will throw the error. See? Why? You you know the issue. Where's the telemter? Oh, you forgot it, right? You need to do this the same thing that you did in the past, right? And then you can need to simply change the delimter back to the semicolon. Simple. Make sense? Let's run this now. Okay. Now what's the issue? Now issue is something different. Now we can just see or it is fine. Oh, it is actually fine or okay. This function has none of deterministic no SQL reads SQL data. Okay, this function has none of deterministic. So what is this deterministic thing? What is this thing? So that is obviously like intentional error. Let me just show you. So if I just search about you can say MySQL custom function you will see there are basically three things deterministic read SQL data and one more my SQL custom functions um yeah I think that should be enough to just get the right web page or um this co-pilot can give us the answer um I think this is better yeah so what is this thing deterministic nondeterministic and all those things so you have to pick one option between three. There are total three things deterministic, nondeterministic and one more I think um read SQL data something like that. So I think deterministic deterministic deterministic and okay so I'm just trying to find out a very good you can say short explanation for this deterministic let me just write deterministic deterministic or basically what are what are my SQL custom function deterministic So hm see deterministic and nondeterministic. Let's open this document. Let's try to read something. So it is saying well this this document is the best because you will not find anything useful. Um what does deterministic mean? So I just I I wanted to just open um you can say a good documentation but what we can do they have like amazing documentation here. So uh maybe it is here. Yeah perfect. So these are the options available. These are the options available. Deterministic non-deterministic contain SQL NoSQL read SQL data modify SQL data. So you have to tell like what kind of function are you creating. This is basically the characteristic. Okay, this is basically the characteristic that you have to have to mention. Make sense? In some SQL servers, it is like by default set. Okay, you can modify it. But here it is not by default set. So you will be writing here deterministic. Make sense? Let's try to run this now. Now it is fine. Perfect. Now I want to use this. So I'll simply say select uh unit price. Okay. And now I will use my function that I have personally created. It is called square it. Why it is very very handy? Let's say in your organization they want to just create some custom functions that they want to reuse it. Okay. So you can just write it like this. Square it and just pass the column unit price. Okay. And now you can simply say from uh dem product. Okay, let's try to see the result. Perfect. So see I have got the result which is very very good. So it has simply applied the square and even I don't have this particular function available in my SQL. I can still use my own custom one square it. And this way you can literally write any logic here. Here we have simply written the very simple logic X into X. You can literally write any logic. Let's say you want to just apply a formula, a customized formula that your organization uses to find specific KPIs. You can just do this like this. Okay, these are basically you can say your matrix. In the modern world, we just call it as matrix. You can create your own matrix. Make sense? Make sense? Makes sense. What is this? What is this? Ah, what is this? Let me just Oh, okay. Make sense? So, this is your fun. This is your um script for functions. So, now let me just talk to you. How was this SQL master class? Did you learn so much of things? Obviously, yes. So, now I have a small task for you and don't worry, I will be just telling you what you need to learn next. So, just stay here for one to two more minutes because you will get something very very important. First of all, you need to hit the like button, subscribe button and the comment section should be full of lovely lovely comments. That is your duty. You have to do it. Second thing which is very very important. Now you have mastered SQL and now you need to just obviously um learn more and more things in the world of data engineering. So you will be like what should I learn next? Because some of you will be saying hey I have the resource I will learn Python. But let's say you already know Python. Now what is your next step? Okay. So now your next step is you need to learn something called as data warehousing. Okay. Data or let's say even before that you need to first of all learn data modeling. Now you will say data modeling. Hm. This this name is is like very famili familiar. Do not be confused with data modeling that database analyst or database administrators do. You are a data engineer. you need to perform data modeling for data engineering. Are both different? Obviously, yes, both are different because in the data engineering domain, we use something called as dimensional data modeling. In the world of DBAs, they use normalization forms. Okay? So, we do not use normalization forms. We use dimensional data modeling. So, you need to learn dimensional data modeling. Now, you will say an Lamba did you create any video? Can you please guide us a little bit? Obviously, bro, that's why I'm here. So you can simply go on Google simply search data modeling anal simple you will get an amazing video and it is called data modeling the ultimate guide. This is the video that is designed from scratch to tell you how you can just perform data modeling. Okay. And how you can just actually learn all the concept of data modeling. Amazing video, amazing source of learning. Just go and watch this video. Okay. And if you want to learn something something something unique and different, you can just click on the video coming on your screen and I will see you there.