Gen AI Powered SQL For Business Analytics 2026 | Learn SQL With Gen AI | Simplilearn

Key Takeaways

What if you could not only master SQL but also leverage the power of AI to make smarter and faster business decisions? Well, this course is all about taking your SQL skills to the next level and it's powered by generative AI. Hey everyone and welcome to the Genai powered SQL for business analyst course by simply learn. So in today's world, businesses are sitting on massive amounts of data and it's only valuable if you can unlock the insights hidden within it. So SQL is one of the best tools for working with data. And when combined with the power of AI, it opens up a world full of possibilities. So this course is designed to teach you how to efficiently analyze business data using SQL with AI tools that can help you automate tasks, optimize queries, and generate insights in real time. And in this course, here's what we will cover. Firstly, we will start with the basics of SQL and understand how it fits into the data analysis for business. Next, we dive into how to connect to different data sources, clean your data, and prepare it for analysis. Then, let's explore how SQL functions work with numbers, strings, and data, and write more efficient queries. After that, we will show you how to leverage the power of AI to speed up data analysis, automate tasks, and get better insights from your data. Finally, we'll put everything together by working through real world examples, building complex queries, and running them against large data sets, all while making datadriven business decisions. And by the end of this course, you will have the SQL skills that you need to succeed as a business analyst with the added bonus of an AI powered tools that makes your workflow smarter and faster. So, if you're ready to unlock the potential of your data, let's get started. Also, here's a quick information. If you are interested in boosting your career in business analysis, do not forget to check out our AI powered business analyst course. This course is perfect for professionals looking to enhance their skills with the latest tools like PowerBI, Excel, and SQL all while gaining hands-on experience with real world projects. So, you will also learn how to leverage generative AI for smarter, faster decision-m. Our program is IIBA and Babok V3 align and it helps you prepare for certifications like CBA and CCBA. You will engage with 10 plus industry projects, 40 plus practical activities, and benefit from live online sessions led by experts. Plus, with Simply Learn's job assist, you will get the support that you need to land your next big role. So, before we dive into the world of SQL and AI, let's test your knowledge. Here is a simple question to get you thinking. What is the primary purpose of SQL in data analysis? Is it A to create websites or is it B to manage and query data in databases? C to design mobile apps or is it D to edit photos? Take a moment to think about it and when you're ready let's jump into the course. >> So database is nothing but it is a structure which can store the data and definitely store the data digitally or electronically because see when I talk about the wardrobes you have the wardrobes at your home. So uh you you get to see the physical wardrobe but here in the computer world everything is digital right. So database is think of it as a folder. So on your machines. So on your machines why do you create a folder? So that you can save your data in it. Yeah. You can save your files in it. So here also we create the database in order to store the data. In order to store the data now when you have a database why it is uh always better to have a database. So if I ask you again just tell me one thing. Let's say that you've got a big wardrobe with a lot of clothes in it. Yep. So, don't you think so that if you have got a clothes, if you have got a wardrobe, first of all, uh you're getting a better storage, organized storage. Maybe I'm going to keep all my jackets over here. I'm going to keep all my shirts over here and so on and so forth. So I get to organize my clothes because I'm having a wardrobe, right? Also, I can easily access it. See, if I'm just dumping my clo clothes on the floor, if tomorrow I want a particular dress, yep, it will be very difficult for me to find it, right? But if I'm using a wardrobe and definitely the wardrobe is organized, then I can easily find the clothes. Yeah. So, it is very easy. it will be very easy for me to access right uh also now uh that is not related to the clothes but yeah in case of database we have one more thing maybe I'll talk about that so talking about the database which is nothing but the storage of data it has organized storage so I just explain you with the help of wardrobe analogy right so you have everything with the help of wardrobe all your clothes are organized Yeah, here also you've got database. So with the help of database we are getting the organized storage. Then efficient access. So as I told you in case of wardrobe also if your clothes are properly organized it will be very easy for you to access your clothes. So in case of database also it will be very easy for you to access the data because right now see the data can be in uh you know in mega GBs like it can be really huge. It can be really huge. So it is very important that there should be some way to store it so that we can also have efficient access secure scal scalability. So what do you understand by scalability? Scalability is about increasing the data and data is definitely increasing day by day. Now if you talk about Amazon think about the data that how fast it must be increasing every day there must be a lot of transaction being made. I'm talking about the Amazon website and if I specifically talk about only India Amazon website or only US Amazon website every day you can imagine the kind of transaction that Amazon might be getting and the kind of data that might that Amazon might be producing. So we need a system which is scalable. Scalable means that it should be able to handle so database is a system which can handle the growing data. Yeah. And it's definitely secure. It provides us the security also. So you may not want all your data to be accessible to everyone. Right? So with the help of database, we can also secure our data. Just like in your wardrobes, you can always lock your wardrobes, right? You can lock and unlock your wardrobes. In the same way, database data can also be locked and unlocked from uh different different users that you have. Okay. There's one thing that I would like to show screen. You'll get to know that uh how your co-articipants are. For example, I actively work with SQL. So, we have got only two who are actively working on SQL. No experience with SQL. We have almost 53. So, we are going to learn from very very basic and you can see that we are doing that if we are starting from what is data. So, that's one of the very basic thing. Okay. I have got some knowledge but don't use it regularly. So, we have got 16 folks. So we have got a lot of beginners in this batch. So we'll make sure that we cover everything from scratch. But guys one thing if you're learning the language for the very first time then you also have to make sure that you practice after the class. So only class learning would not be sufficed because see if you don't practice after the class when you come for the next class you're going to forget most of the things what have been taught in the previous class. So again there's one analogy in order to understand what database is. So just like you've got a lot of books. Yeah. So we've got a lot of books. So the books are stored where? Now this is the picture of what books are stored where? Think of books as the data. And where do we store books? In the library. Yes. Yeah. So this is what library you must have seen that when you go to any of the library usually the books are arranged in a certain manner. For example here you'll have all the science books here you'll have let's say all the maths books all the fctions. So in this way and then there might be some alphab alphabetical arrangement. If you go to any good library you'll see such arrangements. So why do we have such arrangements? What do you think? Why? What is the reason behind such arrangements? The library stores the book books easy retrieval. Yes, easy accessibility. Yes. Imagine that they are not storing the books in this way and then you'll end up maybe spending your whole day or two in order to find one single book maybe more than that depends on the size of the library. So database also works on the same concept guys. It's just that instead of books we are now storing the data and instead of library we are having the database. The database stores the data. Library stores book. Now just like how the library is properly just give me a second. Yeah. So just like how the libraries are properly organized so that we can easily access the data and it's not only about access the data I mean I mean to say the books it's all also about let's say there's a new addition yeah I've got a new addition there's a new fiction book so I know that I just have to add it over here so it helps me to modify also properly right I I'll quickly able to modify make the modifications so it's not that I'm very random with it but Yeah, it's mainly for the quick access. Now we're going to learn about these properties of database. So this is very important interview question also. So we have got asset properties. We call it as asset. So we'll go one by one. So this slide may take little bit of time. So please be patient. So I'll try to make it easy. I'll try to make it interesting. Yeah. All of these properties. But you should definitely know about these properties. These are the heart of the database. So when I say automatomicity, any idea what atomicity could be? So I do not want you guys to read the slides. I want you guys to answer with whatever comes to your basic minimum terms. Okay fine. So we'll talk about atomicity. So I'll give you an example and we'll understand it with the example and it's very important. So before I talk about atomicity maybe let me tell you the use case of database. So just like database we have got we have got more entities in order to store the data. Now you have to hear me out right? In order to store the data we have got more entities. Database is not the only place where the data is stored. We have got a lot more other things. So you might have heard about apart from database you might have heard about data warehouse. You might have even heard about data lake. So we are not going to get into that. That's definitely not our area to get into. But what I mean to say is that we have got multiple things in order to store the data. So why what is the use case of database? The question is let's say I've got some data. So why would I choose database rather than data warehouse or data lake. So we are going to look into the use case of database. Yeah we are not going to look into the use case of data warehouse or data lake. We are going to sorry look into the use case of database. So any idea any use case of database you can think of. See everything is used for storing the data. So why database? So I'll tell you why database. So database or not why I'm just telling you use case of it and then you'll understand why. So we'll go step by step. So if I talk about use case of database. So let's say that I access any of the bank website and when I say website I'm talking about my account. So I just want to see that what is the balance current balance of my account. what are the transactions that I made in last seven days and so on and so forth. So to save this data now see my current balance then everything related to me my profile then my transactions whatever you see so everything goes into the databases so most of the companies they use databases for such things so databases are used for day-to-day transaction so whenever there is any day-to-day transactions data Yeah, whenever there's a day-to-day transaction data, it goes into the databases. And the reason is that it provides asset. Yeah, it provides asset guarantees. I'm going to talk about what asset is. But why day-to-day transaction data goes uh why day why do we go with day-to-day transaction? Because because it provides database provides with the asset guarantees. If I give you the other example, if I talk about let's say Amazon, so again day-to-day transaction, right? You're making a payment, you're ordering something, making the payment. So mostly the company might be using the database in order to handle all of these things. So if you go to the Amazon, you see a lot of products. So let me go to the Amazon and I'll just show that to you. So all of these phones etc. So these are this this is what these are data images the description the price all of these things are data so this might be coming from the database yeah this might be coming from the database so the database might be storing all of these things so for day-to-day transaction we use we use database why for day-to-day transaction because database provides us with the asset properties so we'll talk about atomicity and you'll get to know that Why as it is so important? So atomicity says it ensures that transactions are executed in all or nothing manner. So what does that mean? So it simply means that let's say let's say this is you and this is your friend. Yeah. Now what you're doing is let's say you have got hear me out. Yeah. You've got 50,000 as your account balance. I'm talking about INR anything anyways. Yeah. And now your friend needs 10,000 rupees. So you say fine I'm going to give you 10,000 rupees. So what you do is you go ahead and you make a transaction. Now this is something that we do in our day-to-day life making the transactions via bank. So you'll understand that how things works. So here I go ahead and I make 10,000 rupees transaction. So what will happen is the 10,000 will get deducted in your account from your account and it will get credited in your friend's account. So this is the expected behavior when everything goes well. But let's say that you made the transaction. This might have happened a lot of time with you all that you make the transaction the money got debited. Yeah. And hear me out. Yeah. The money got debited from your account, but your friend did not get the credit. Might have happened, right? So what will happen in your account? You'll see 40,000. But in your friend's account, still it is still reflecting zero. So this is a very bad thing, right? If I talk about banks, this is something that we definitely do not want because if things are happening like this, then we end up calling these people all the customer care and you know breaking our head that we I transferred did not work whatever it is it's definitely not some not something that we would appreciate right so what happened usually in in your day-to-day life you might have seen that if the money is debited from your account and there was some problem while you were making the transaction ction it gots recredited. You get the message that you have got your money back. So behind the scene what happen is because there's a database that is working. Database says that either all or none. Yeah, it says that either all or none or nothing. So what does that mean? It means that either the whole transaction should get processed successfully. So what I when I say whole transaction should get processed su successfully it means your account is getting debited and your friend's account is getting credited. This is a one full transaction but if there's a problem in between yeah if there's an error in between there's some problem in between then the whole transaction should be rolled back. So either it should be the full transaction or there should be nothing. And that's why when there's a error while you're making some transfer and if there's some error you you'll get uh you'll get your credit back you'll get your money back automatically and this is because of the database property that is atomicity. So this is a property of the database that either the whole transaction is successful but if there's a problem in between then it will make sure everything is rolled back. So your deducted amount the amount which was deducted is rolled back and it is again credited. So this is what atomicity is. Now this is so important property. If I talk about banks and if I talk about any other website also Amazon also you make a order. Yeah. Either the order should be made or shouldn't be made. There should be nothing half in between. Right? So atomic city is very very important. Now we're going to talk about consistency. So everybody knows the meaning of consistency. Yep. So here it is written but still I write it. So again I'll explain you with the help of an example. So let's say that you've got an account. Yeah, you've got an account with a bank and you've got 15,000 rupees. Now in your bank and you might have seen that you a lot of banks does have such rules. So let's say that your bank has a rule that you should maintain minimum balance of 10,000. So this is a rule, right? Minimum balance of 10,000. That's a rule. Now what you do is you make a transaction you make a transaction of 10,000 rupees in your friend's account. So how much how much amount is left in your account? How much amount is left? You've got 15,000 and you make a transaction of 10,000. So how much amount will be left? Minimum balance should be 10,000. So here consistency says rules are not broken. So in very simple words, the rule says that you have to maintain 10,000 rupees in your account. If you try to if you try to transfer 10,000 rupees because you've just got 15,000, it will give you error. it will not allow you to do it. You might have seen on on GP pay, Google pay that there's a limit of 1 lak rupees for every day. So if you try to transfer more than 1 lakh rupees though you might be having even one cr in your account but if you try to transfer more than 1 lak rupees in from your Google pay it gives you error. It says big no that you're not allowed. So that are what that that is what that is rule. So databases says oh you got some rules I'll make sure that the rules are being followed the rules are not broken. So am I clear? So isolation as the name says it ensures the transaction do not affect each other. So again I'm I'm going to explain it to you with the example. So let's say that you and your sibling or your spouse or your parents you both are using the same account. So it happens right we share the accounts with our loved ones dear ones. So let's say let's suppose that the account balance is 10,000. Now what is done is let's say you we make a first transaction that is we are taking 5,000 rupees out of the bank. So let's say we are taking 5,000 from the ATM. So the new balance would be what? The new balance will be again 5,000. Now let's say that there's a second transaction and when I say transaction it can mean anything. It's not only about credit and debiting. So the second transaction says that you know it sees it wants to see the data. Yeah. It want to see like how how much is the account balance. So let's say that you are taking the money out from the ATM and at the same time your parents are checking the balance. Yeah. So they are checking the balance. So they end up they may end up seeing they may end up seeing maybe 5,000 or maybe 10,000. So let's suppose they end up seeing 5,000. Yeah. So your mom is checking and she sees that there is 5,000 rupees. But what happened when you were so you initiated the transaction and ATM uh took its sweet time and then it failed. Yeah, there was a failure. So what will happen? What will happen? Or maybe you're not taking it from the ATM. You're just doing the online transaction. So you have initiate 5,000 rupees transaction but in between it got failed. So what will happen? It will roll back and you'll again have the 10,000 in your in your account. But your mom is seeing 5,000. So that's a problem you're getting. That's a problem. Actually you don't have 5,000 because your your your transaction feed. So it so everything will roll back and you you actually you have 10,000 rupees only you don't have 5,000 you're getting so you don't have 5,000 you have 10,000 but your mom is seeing 5,000 so that's that's a problem that's a problem so isolation says isolation says that we are going to we are going to do the things in isolation yeah so it means it means that t2 yeah in the transaction to either it will see the old data 10,000 or it will not see anything. Sometimes the screen takes its sweet time in in order to get refresh or in order to show you the numbers, right? So it will not show you the data half incomplete data. Yeah, half done data. It will not show 5,000 at least. So it either it will show 10 10,000 or it will wait for some time for uh for the transaction to get processed that T1 transaction to get processed successfully and then it will show 5,000 but nothing in between here it was showing in between see you were making the transaction and it is showing 5,000 to your mom but what happened the transaction got failed but your mom sees 5,000 so that's a wrong thing that we are showing right so this is not even before this is not even after this is something in between So isolations make sure that everything happens all the transaction they happens in the isolation. So they are not affecting each other. So it means that if you're looking into the balance either you're going to see 10,000 or it will take some time and will show you 5,000 once the transaction T1 is successful. So this is what isolation means. Yeah. Means transaction two will not see the uncommitted deduction. As simple as that. So am I clear with isolation? Simple. Everything is something that we get to see in our day-to-day life. Yes Rajar what you're saying is right. Now what is durability? This is a very simple one. So what is the meaning of durable? Durable simply means that the data is permanent. So it simply means that once the transaction is successful, see 5,000 deducted from your account and credited in your friend's account. Yeah. This yeah this should be durable or the data should be permanent as simple as that. So if there's a power failure even if the system crashes the data will be still saved. So once so it simply means once committed it stays forever and this makes sure that there's no data loss. So I'll just quickly recap. Atomicity says no partial updates. Either should be full or should be none. Consist consistency says no rules breaking. Isolation says no transaction confusion. and durability says no data loss. So this makes database very very special and that is a why we use it for day-to-day transaction. So there's a word word for day-to-day transaction that is OLTP. So you should definitely know about this thing. It's nothing but online transactional processing. So maybe I'll not write the whole thing. transactional processing. So this simply means day-to-day transaction Amazon your banks. So these are what day-to-day transactions. So all the companies for the OLTP they use databases. So databases are very important. Okay, whip club I think I've already summarized, right? So what you have to say is for atomicity. So they usually ask like what is atomicity? So you can explain the whole thing that either the transaction should be full transaction or it should be no transaction. So there there should be no partial transaction. Consistency you can give the example that we have got the rule. So we have to make sure that data follows the rule. I give you the example of Google pay also right. So Rajpal let's keep it for the end of the session. Yeah data warehouse we'll cover what is in our curriculum and I'll explain you. So data warehouse also I'll explain it's a very interesting concept but just keep it for the end of the end of the session. So I'll explain sidesh what you're saying is absolutely right. I'll quickly explain isolation once again. So isolation simply says think of it maybe I'll give you one maybe one more example. So let's say that you make a transaction you have got 10,000 rupees. Yep. uh you make the transaction of 5,000 at the same time. So I'm explaining you with the same example. At the same time, your spouse looks into the account balance and sees 5,000 rupees. Now you made the transaction but what happened? Something happened and your transaction got failed and everything got rolled back. So basically you have got 10,000 But your spouse saw 5,000. So your spouse was able to see incomplete data. So either your when you're checking the account, it should be either 10,000 before the transaction or should be 5,000 after the transaction. So it should not be in between. Yeah. When you're making the transaction, understand the transaction is still not completed. Just like let's say you're taking the exam and when you're taking the exam so you're preparing for the exam maybe. Yep. And you're telling your mom that oh I'm going to top the class. So it's like too soon to say right? So you have to take the exam first and then you can say that okay you have topped the class. But here also it's the same thing that the transaction is ongoing and when the transaction is ongoing this state shouldn't be shown to the user because the transaction is ongoing. So if the transaction fails then if you are showing 5,000 see if you're showing 5,000 then it's a bad data right you're showing the bad data actually the the amount is still 10,000 because the transactions failed but you're showing five 5,000 so that's a bad data. So either you have to either you should show 10,000 or you should show 5,000 after the successful transaction. Am I clear now? Yes, Raja. Now there are different different type of databases. So we'll I'll quickly give you the brief of the different type of databases. But we are going to focus only on the one type of database which is used in major projects. So we have got different different type of databases. The first one in our list is the relational database. So we are going to learn relational database. Database is what? It's nothing but it's a place where you store your data. Yeah. Where you store your data. So we have got no SQL graph database centralized distributed in our curriculum we have got relational database SQL works with relational database only. So our focus would be on this but I'll just quickly walk you guys through these also so that you understand these terms. We'll definitely not deep dive that is not required. So let's talk about relational database. Database is what? It's storage of data. So, relational database says that I am going to store the data. Yeah. In the form of the tables. Now, what tables as? Tables as columns. Yeah. It has columns. Let's say student ID, student name, student number, all of these. Yep. And then it has got data. So this is what this is relational database. Now we also call it as relational. It means the tables are connected to each other. Yeah, the tables will be connected to each other. So how they are connected to each other? I'll give you one example but we are going to deep dive into this. This is what we are going to learn. So it's very much okay if you do not understand it right away 100%. Yeah, that's very much okay. If you understand 50% of it, that's also fine. So, for example, let's say that I've got let me let me see if I've got something. Just give me a second. So, instead of writing, I'll better show you. Just give me a second. Yes. Yes. So you can see these are what these are tables with rows with columns and rows. So it's a relational database. So relational database says that this is table let's say this is table one, this is table two and this is table three. So relational database says that there's a relationship between the tables. Yeah, there's a relationship between the tables. Now, when I say that there's a relationship between the tables, what does that mean? So, if you see for example, so this table is storing the data of the orders, order date, order ID, customer ID, product ID, quantity. So here the customer ID is 1 2 3. So here we are not storing the data of the customer in this table. We are just saying that this transaction is made by the customer with the ID 1 2 3 but we are storing this data of the customer the information of the customer in another table. So for example 1 2 3 ID is of anul and he is male we can have more information phone number so many so much of it. So this is just to make you understand. So in case of relational databases everything is stored in the form of the table or the data and then we have the relationship between the table. So you can see that this table and this table is related to each other. Yeah, this table and this table is related to each other. So when I say that two things are related to each other. Let's say that you are related to your uh sibling. So there's something that is common between you. Let's say you're related to each other because of the family. Yeah. In the same way, let's say that you have got multiple friends and you say that okay, I am uh you know somehow I am now that doesn't make much of sense but yeah let's say that I am related to this friend in a way that we all belong to the same college. So that's a key between the relationship uh that's a key between you and your friends to establish the relationship. So you guys tell me see I've got this table customer table and I've got this orders table. So what do you think what is the key between this table and this table that is actually making the relationship happen. So in order to make the relationship happen there should be some something in common. Yes. Customer ID. Yeah. Tell me about this one and this one now. product ID. I hope this is clear, right? So this is how the relational databases are designed. We are going to deep dive into it. That's what we are going to learn for next eight classes. Now let's talk about the other databases that we have. Okay, here I've got okay I just totally forgot that I've got a PPD and here I'm showing that how the different tables are related to each other. So there's a key a common key but let it be yeah I have already explained you and we are going anyways going to deep dive into it. Okay a lot of uh speaking from my side again I'm so sorry because I can't help it. Yeah from tomorrow we'll do a lot of hands-on. Now if I give you the examples of the relational database, examples are MySQL. You're here to learn MySQL. So that's a relational database. In the same way we have got SQL server, Oracle, Postgress. Now if you learn even one single database if you learn MySQL then you can say that you know all of the others and I'll tell you the reason why why because 85 to 90% of all the database they are seen. It's just that there's little bit syntax here and there. So you don't need to learn all the different relational databases. If you learn one, you you will surely be able to work on the others also. So if I'm let's say I'm taking interview so if I know that the person knows my SQL very well but in my project we are using SQL server I will not hesitate hiring that person because if that person is good as in if that person knows that candidate knows my SQL and he's able to answer all the questions and he understands the length and the breadth of the technology then I'm sure that uh working on SQL server will will be definitely no big deal. So all these databases, relational databases, they're very similar. You don't have to worry about learning different different databases. That is definitely not required. You learn one properly, you can work on any database for that matter. Fine. Now uh there's one thing about the relational database and why I'm talking about it right now because after this we have got no SQL database. So see relational database is kind of a strict database. So when I say it's a strict database what does that mean? So it simply means that let's say I've got a student. Yeah, I've got you can see over here I'm storing the student details role number name and marks awarded. So let's say I've got a new student. Yep. Now for this new student I want to store the data but I've got I've got more data for this new new student. So I've got let's say his role number is four name is Nha marks is whatever. Yeah, but I've also got one more data for for him for her. So, let's say I've got city as Pune. So, relational databases say yeah the that I'm so sorry you have something extra I cannot accommodate. So, it's very strict you're getting. So, this table says that you've got extra column. I'm sorry. I can just accommodate three columns. So I am I cannot provide you this flexibility of storing anything in me. Yeah, I've got three columns. You have to stick to three columns. You cannot give me four columns. So it's very strict. I'll give you one more example of strict. The second strictness it says that now it says that this column is integer. It means that it can take only integer values. Yeah, it can take only integer values. So again I go ahead and give the details but let's say I've got some string value. Yeah I want to give let's say 10. So again it will say no. It will say what what are you doing? I can just take integer. What the hell are you providing me? You're providing me 10 like the string 10. Sorry I can't do that. So it's very strict. Yeah, it says that see I've created this table with the three columns with a certain data type for all the columns. So for this one we have got the data type as integer. This one let's say as string yeah wcare and so on and so forth. So we have to stick to that. Yeah, we have to stick to that. We'll talk about that apert we'll see to that. Yep, it's very easy to set these rules. So, you got that that relational database is very very strict. You get that? Am I clear with this point? How do we do that? Once we start writing the code, you'll get to know and it's very easy. It's like one word things. Yeah. You just have to add one keyword and then you're done. done and dusted. Am I clear that the relational database are super strict, very stringent? Fine. Now, let's say there's a requirement. There's a requirement. The requirement is that we want flexibility. Yeah, we want flexibility. So what I want is let's say that I am um I'm the business owner and I do not want this. Yeah. Because let's say I I I am I am my own school and I say that fine. Yeah. But let's say there's some student with some you know more things. Let's say that he has got some uh he or she has got some certificates or maybe some achievements. So I want to store that data also here. I simply do not want to discard it. Yep. So either you add one more column to your table. Yep. Or give me something else that is more flexible. I want to store everything. Whatever whoever is coming to my school. Yeah. Whoever is getting registered, whichever student is getting registered in my school, whatever information they are giving me, I want to store it. So I just simply do not want to discard any of the information. So here comes NoSQL databases. So no SQL databases. Now we are not going to get into into the architecture of it. As you can see these are non-tabable databases. Yeah. So it stores the data in a way. Now this also stores the data. Yeah. This also stores the data but it stores the data in a way that it is super flexible. So do you know what JSON? So relational database stores the data in the form of in the form of uh tables and in case of NoSQL it stores the data in the form of JSON and there are other ways also but I'll show you the JSON one. I'm just uh looking for something that is quite easy to understand. Yeah. So here you here you go. So if you see this is what this is JSON. So for example you've got student. So we we say student. Yeah. Or let's say employee. Yeah. Employee. So you're storing the first name, last name, display name, middle name, birth name of the employee. Now this is let's say the information of some XX employee. Now let's say that we have got another employee and that another employee has got some other information also. Let's say passport number. So it easily accommodates with that it says fine. So I'm going to have one more node. So this is one node for one data for one employee. So you've got 100 employees. So you'll get 100 nodes. So it says that I'm very much okay with it. You have got one more data. So I just have to add one more key value pair over here. Yeah. Key. And this is a value. So this is how JSON looks like. So it's very very much accommodating. It says that I'm very much okay with it. Yeah, I'm not strict. Right now I've got five details. Yeah, five columns. If you got 10 columns for the next employee, I can easily accommodate it. So I'm not strict like tables. So that's what NoSQL says. So it stores the data in different different formats. So it stores the data in key value, column, graph and document format. You don't have to deep dive into it. But yes, so why do we use NoSQL? This you should know. So if you want that flexibility, see tables are very strict. So you want that flexibility that tomorrow if your data comes in some other format, you should be able to store it. Yeah. It should not it should not say no. Your database shouldn't say no. then you can go for NoSQL database. So am I clear with this point? Definitely you don't have to deep dive into it. XML format. Yes. XML JSON key value pair. Exactly. Okay. The next one is graph database. So I'll take a pause. Why I don't with whatever you see on my slide I want you guys to guess the use case of graph database social media. Fine. So have you seen or you might you on Instagram or Facebook you might have seen that it keeps giving you recommendation of friends of friends mutual connections suggested friends. Have you seen that? So whenever we have got data like this, yeah, where the data is very much related, yeah, it's mutually related. You can see over here. Yeah. So we use graph database. So friends of friends, so this is again graph database. In the same way product recommendation, so you buy one product, then it starts showing you all the other products. So that can again come under graph database. We have ML also in that it depends but yeah this is what this is graph database. So uh it stores the data in the form of the graph not table. So basically this is you yeah you're browsing Facebook. It will start showing you the mutual connections. This this Yeah. So this is how it works. You would have never worked on the graph database. Usually uh it is used by the social media companies. Yeah. Social uh media companies. Then uh it is also used by the banks I think for the fraud detection etc. Yeah. But it saves the data as a graph so that they can you can find the connection between the data. Fine. Now we'll talk about centralized database. So centralized database it can be any database for that matter. It can mean SQL, NoSQL, graph. So centralized database is what you have got one database. Yeah, you've got one database and everybody's connected to this one database. So there's only one database. You have one centralized database and everybody is connected to this centralized database. Let's say that City Bank has got one centralized database. So tell me what could be the problems of having centralized one single big database. What if the database crashes? What if there's a power cut? There's a failure. What will happen? Everything will go down. Now you're not able to access your bank account and so the other users. Yes. So this is a centralized database where you have just got one single big database storing everything and a lot of companies still uses centralized database though it is not recommended now what do you think what should be the solution of centralized database now I'm not expecting the right word for it I'm just expecting that what should be the solution what you you would Don't you think so you have the copies of it multiple copies? Yeah. So backups. Yes. So if this goes down you have a backup up and running so that you do not uh because of the system crash your website will not go down. As simple as that. So having these backups definitely is pretty expensive because here you are maintaining only one database. Now let's say I have got two backups. So I'll end up maintaining three databases, right? So it's pretty expensive but yes we call it as distributed databases where the data is stored in multiple places and that's is again done by all the big companies and that is the reason why these days you'll not see that your site is going down. Yeah. All the good big companies websites they are always up and running. So in Pune this happened almost a year back I think it was it happened in June July. So I stay in Hindabari those who are from Pune. So uh there was some issues with the power. Now this area where I'm staying is the IT hub one of the IT hub. So there was some big issue with the power supply of and that was for the whole area and we were without the power for 3 days straight 3 days and it was not only for the households it was for all the IT parks also and uh we have got some data centers when I say data centers um as in uh you can say servers which are storing a lot of data. So we have got some data center we have got a data center also here in uh this part of Pune in Javari. So everything went down. So definitely the these are very common problems. It can definitely occur. So that's the reason why the companies they always prefer having the distributed databases. So instead of having one centralized database, they're going to have databases at multiple places to avoid uh any downtime. Am I clear? Fine. So let's do one thing. I think we have got a lot of theory and I've been speaking from last one and a half hours. So it's not uh my thing it's about you guys because if somebody speaks or if you have to listen somebody for more than 20 minutes we tend to uh do a we tend to daydream. So according to some theories I think the attention span of a human being is only 20 minutes not more than that. So we'll do one thing. We'll take a break and after the break we'll continue because we've got the next topic. So I just want to you know cover these topics in continuation or if you're okay maybe I'll take another 15 minutes for these topics not more than that. Oh wow. Everybody is saying go ahead. I'm so happy. Fine. Let's go back to our library image. I literally have to go back now. Tell me one thing. We've got the data. We've got the books. We have got the library storing the books. Who manages the library? Somebody should also be manage it, right? Who manages the library? Librarian. Okay. So, in we just learn about data. We learn about the database. Don't you think so? We want somebody who can manage the database. We want a tool which can manage which can help us. Anyways, we are going to manage definitely but we want a tool which can help us manage the database. So let's say if librarian is also managing the library but the librarian might be uh maybe maintaining some book uh some records yeah it might be the librarian might be using something in order to maintain the library. So I remember when I was in college so my because that time it was like long back. So my library teacher uh there was a there was a computer of course in the library in front of uh on on her desk. So she used to use that also she used to maintain one register one copy. Yeah. And she used to write who's taking which book and what's the last date of the submission what's the fine all of these things. So there's a system that needs uh that we need in order to maintain the things. So here also in order to maintain or not maintain in order to manage the database we have got DBMS here we have got DBMS. So you can see database management system. So database management system is nothing but it allows user to create update retrieve and manage the data in the structure format. So basically see you've got the data you have got the database with a lot of data. Now in order to talk to the database get the data out of it insert the data in it update the data delete the data whatever you want to do with the data for that we need a system. Yeah we need a system and we call it as DBMS database management system. So it it's a software that helps us manage the databases. So what you can do in this see you can create the database you can read. Yeah you have got the data right. So you can create you can read you can make the updates you can delete it. So you can do all of these things. So these are called CRUD operations. So do not get scared with these jarens technical jargon. These are very simple thing. So when I say CRUD, C stands for what? Everyone tell me C stands for what? I just talk about the operations. It means create. Yes. So CRUD operation you're going to listen uh to this term a lot. So this is not specific to the databases only like Yep. R stands for what? Read. Yes. Very good. U stands for what? Update. And D stands for delete. So if anybody says CRUD operations, do not get scared of it. What is this CR? It's nothing. You're creating the data. You're just like u you've got a wardrobe. So you are just looking at your wardrobe. How how many clothes you've got. Maybe you're updating your wardrobe like you're adding something to it. you're removing something from from it and so on and so forth. So here also we do all of these things but we do it with the help of the software called DBFS and guys see um okay maybe I'll talk about that later not right away. Now relational DBMS is what? Tell me what it could be. I just told you what DBM is, what RDB DBMS could be. What RDBMS to manage the relational database? You know that there are so many databases, right? We learned about the relational database. Now to manage the relational database, what we have? We have got relational DBMS. Simple. So this is a software which help us to manage the database. As simple as that. Now what is SQL? So see understand this thing. Now you've got a software. Now hear me out. Yeah, you've got DBMS software. So you're going to talk to the DBMS and you're going to make the DBM DBMS work for you. Yeah. You say DBMS can you please create a table for me? Of course. So DBMS will create the table for you. But DBMS says that I don't understand plain English. Yeah. I I I'm not charg I I do don't understand plain English. So DBMS says I can do a lot of things for you but then you have to speak to me in my language the language that I understand. So DBMS in order to talk to DBMS you have to use SQL and that's what we are going to learn. We are going to learn SQL. Why? Because with the help of SQL we can talk to the DBMS and get our work done. Sorry I think there's some issue with the pen. Yeah, get our work done. That is CRUD. any of the CRUD operations that we want to perform on the database. So you're getting just like let's say there's a librarian. Yeah, librarian is what? Librarian in our analogy is DBMS who manages the database. Yeah. With the U. So then we have got library. So library is what? Library is the database. And inside the library we have got books. So books are what? Books are data. These are data. Now let's say that you want to fetch some book. Yeah. You want to fetch some book. You want some some book. So you go to the librarian and you say that I want so and so book. Yeah. I want a book of let's say Dan Brown. So and so. So your librarian will go and may fetch it for you. So you're getting So you're going to talk to the librarian. So you may talk to the librarian in the language that your librarian understands. That makes sense also. Yeah. If your librarian understands only one language that it's English and if you start speaking Spanish in front of that librarian, the librarian would be like what are you saying? I'm like please bother. Yeah. So it will the library will not do your work as simple as that. So here also in order to talk to the DBMS in order to make DBMS work for us we use SQL as simple as that. Yes. So it understands SQL. So it's a language. It's a query language. So you can see over here that it's a structured query language and it is designed to manage and manipulate the data in relational database manage systems. You use RDBMS, you use SQL and RDBMS. So am I clear what is SQL? So you have got four entities. You've got data. Data is saved in the database. In order to manage the database we have got DBMS. So in DBMS only we store the data actually. Yep. In the form of the databases. Now in order to make the DBMS work for us we talk to DBMS in SQL language. So Fatima it's very simple. See you're going to use a tool just like if I use Excel. So in order to use Excel, Excel only understands English. So I can just work I can just use I I'm not sure if it has got more languages. I'm not aware about that. But till uh like now I've just used English on Excel. So in case of DBMS also in order to talk to the DBMS you need to use SQL language. Yeah, that's a language of DBMS. Yes, everything is uh everything is done in DBMS Rajpad. So, we are going to use DBMS. It's a software in order to manage the databases. Fine. So, uh Amin has got a very good question. What is the difference between SQL and MySQL? SQL is a language. Hear me out. Yeah, SQL is a language and MySQL MySQL is the DBMS. So, we have got multiple relational DBMS. We have got multiple relational database management systems in the market. For example, MySQL. Then we have got SQL Server. So, it shares the name with the databases. Yeah. Shares the name. Same name with the databases Oracle uh sorry for this writing guys and then Postgress I don't know what is happening but yeah so these are this is what this is a DBMS or you can say database also they share the same name in order to talk to the MySQL we use SQL language so SQL is a language and this is a DBMS have I answered your question amin So in order to talk to SQL server also you'll use SQL. In order to talk to Oracle also you're going to use SQL. Postgress also you're going to use SQL. So SQL is a language and MySQL is the DBMS. Okay there's some issue I'm not able to write but yeah my SQL posgress SQL server these are what? These are the softwares. Yeah, these are what? These are RDBMS, database management systems. In order to talk to the database management system, we need a language. The language is SQL. So, MySQL is a DBMS and SQL is a language to talk to the DBMS. It's a coding language. Exactly. So, I think we are done. So, this is a boring topic, but yes, it's in our curriculum. So we are definitely going to cover uh tables and entity relationship model. So let's keep it for uh let's keep it whatever we have covered so far. A quick recap. We learn about what is data. We learn about what is databases. What what is database? Then we learn about the different type of databases that we have. Also we learn about asset guarantees of database. Then yeah that's all. Yeah that's all mainly then we learn about what is SQL and DBMS. The first and the foremost compon component is entity. So entity is what? Entity is nothing but the real world object. So basically entity you can think of it as a table. It's a real world object. For example, I gave you the example of student or employee and it is represented with the help of rectangle. Then we have got attributes. So what do you think attributes could be? We just talked about the table. So what attributes could be? So attributes are nothing but the details of the entity. Yes. So all of you are right attributes are the characteristics details yep of an entity. So let me write it. So attribute about they are the property of an entity. So if I give you the example of student, you tell me what could be the attributes. What could be the attributes for the student table? Student entity ID, name, age, class, marks, etc., etc. Right? So these are nothing but the attributes. Attributes are shown using ovals. Yeah. So whenever you see over it means that it's an attribute. Then we have got relationship. Now you know that tables they can be related to each other. Yeah we'll talk about primary key and foreign key not right now but yes how entities are connected to each other. So I gave you the example of orders table. So you told me that the two tables are connected using customer ID right. So relationship is how the entities are connected to each other. So if I give you the example let's say that we have got two entities students is one of the entity and course. So course let's say students let's talk about simply learn. So student table has got all the information about you guys and courses table has got all the information about the courses that are provided by simply learn. So if I talk about the relationship between the two. So simply students have enrolled or I would say student has enrolled for which course? Yeah, I'll simply say student enrolls for goals. Now, I told you that in order to establish the relationship, you need to have the keys. Yeah, you need to have the keys. So, I'll talk about the keys right away. At least I'll give you the little bit idea about it. So, we have a primary key. Now, what is a primary key? So primary key uniquely it uniquely identifies an entity. So if I give you an example over here. So uh before I give you this example, let's talk about a day-to-day life example. So let's say that our government now whether you're sitting in US or India wherever your country is whichever your country is wherever you're sitting so the government is maintaining the database of all the citizens now you I'm Tulika Gupta and there might be hundreds and thousands of tul not hundreds and thousand but at least thousands of tulika Gupta in India right that very much possibility right so my data and the other tulika Gupta data will coincide. So it has to make sure that we it uniquely identify my my data and it uniquely identify the other tika Gupta's data. So what do you think what government would use? What is my unique identity in India? We have got Aadhaar card. Those who are joining from US, those who are not Indians, maybe passport number, bank card. Yes. So it uniquely identifies an entity. So over here now you guys tell me if I talk about okay in this table we don't have any primary key. Okay in this table can you see any primary key customer ID is a primary key and that makes sense also. See customer name can be repetitive. We can have a lot of customers with the same name as Ashul and me. So this can be definitely repetitated but how will we un uniquely identify this unshu or how will we differentiate this unshield with the other ano that we have got in our database using the customer ID. Fine. Can you tell me what is the primary key for this table? So we have got the data of the products. So we want a key that will uniquely identify each product. It's product ID. So, am I clear with primary key? >> Okay, we'll talk about foreign key now. So, foreign key is basically it connects two entities. Yeah, it connects two entity. So let's say you've got the entity one. It has got some primary key P1. Now we have got entity 2. So in order to connect with entity one, entity 2 is using this primary key. So for this entity, this is not the primary key rather it's a foreign key. Yeah, this is a foreign key. So I'll explain it to you with the help of the example over here. Now again let's talk about this table and this table and we're going to repeat these concepts of primary key and foreign key. So please make sure that you understand and you remembers the concept. So tell me one thing uh again the same question that I've asked you before the two tables are related with which column? quickly. This table I'm talking about and this table I'm talking about we use over only. These are the attributes. So we use oval only. Yeah. Customer key. Now customer key is what? It's a primary key in this table. Yes, it's a primary key in this table. Now, we are using the primary key of this table in this table. So, for this table, for let's say the name of this table is orders. For this table, orders ID becomes what? It becomes what? It becomes foreign. Yes, it becomes foreign key. So I repeat I repeat this is what this is a private this is a primary key yeah this is a primary key now in order to connect this entity the customer entity with that of the orders entity the column that I'm using the common column that I'm using is customer ID now for this table customer ID is the primary key but for this table customer ID is what it's a foreign key. It's not a primary key. So foreign key help us to connect the two entities. Yeah. So this foreign key is a primary key of the one entity and it becomes a foreign key of the other entity. Am I clear? We going to again talk about this. Yes. So see this is what this is a primary key. So when the primary key is referenced in other table in order to establish the relationship between the two entities I repeat when the primary key is referenced into the other table in order to create the relationship between the two tables in order to establish the relationship between the two tables. Yeah. So it becomes the foreign key on the other table. So this is what this is a foreign key. We going to revisit this concept when we start our coding. Am I clear now? Sha just remember this thing that whenever you refer primary key in the other table in order to create the relationship between the two entities it becomes a foreign key. Am I clear? So I'll just quickly show you how the diagrams looks like. I think here I'm not I have not created the diagram. Fine. I'll just quickly show you. So it looks like this. You've got the entity. So student entity. This entity may have multiple attributes. Let's say name. Okay. I'm so sorry. I don't know what is happening. ID. Now I've got another table. Again the name of the table is course. And these two tables are related to each other. So there's a relationship between the two. So this is what enrolls. So this is how we create it. So if somebody gives you any entity diagram, er diagram, you should be able to understand student and courses are nothing but the entities. The ovals that you see are nothing but the attributes of the entities and the diamond that you see are nothing but the relationship of the entities. Am I clear? Am I clear? Fine. Now in case of attributes also we have got different different type of attributes. No. So, uh there is one I think one exercise for you all. I'll see to that. If you can do it today, that's fine. Otherwise, we'll do it tomorrow. So, I'll ask you to create one year dialogue. So, we'll see. right? So there are different different type of attributes. Now this is little bit boring topic but that's fine. Attributes are nothing but the features of your entity. So we have got the key attribute. Then we have got the derived attribute, multivalued attribute and composite attribute. So let's cover all of these. So key attributes are nothing but it used to identify one entity from the group of entity. So I just explain you the three attribute. So employee ID, student ID, role number, passport number. These are what these are key attributes. So you can just have a look. Let's look into other attributes that we have got. Now, what are composite attributes? As you can see on my screen, it's very simple. Just give me a second. I think I've got something on the chart. Okay, please go back to the previous slide. Fine. Is this the one for product table a okay I'll come to that yes key attributes are primary keys okay for product table a is asking for the product table now tell me for product table I'm talking about the product table the one that I have highlighted what is the primary key the same question product ID and I'm using it in this table. So in this table a primary key of one entity is used in the other table. So for other table this becomes what? This becomes what? It becomes foreign key. You can also write FK. Yeah, you can just write answers in Y also. No, N. So, I'm very much okay with that. Yeah, you don't have to type in the whole thing. Fine. So, key attributes. I hope I'm clear. Composite is also very simple. So, a lot of time we have got the attributes which are composed of several attributes. So, for example, address. So address is composed of three attributes. So we have got country, state and zip code and all the values or values of these three makes address. So that's what composite means. Am I clear with composite? Okay. And my laptop is acting up. Just give me a second. Yeah. Anyways, we'll continue. So, what is what are multivalued attributes? Now, there can be some attributes. There can be some attributes which may have multiple values. Yeah. Which may have multiple values. For example, let's say that we have got the attribute order or let's say we have got the attribute. Yep. So we have got the attribute order. So order let's say that you make a order. Yeah. You make an order. So in ca in case of order in case of order let's say I make an order of a product I'm not able to write. Okay. Okay. There's some issue with the writing thing. Just give me a second. Let me try this. Yep. So let's say we have got the product. Yep. So I buy one product of some quantity, two quantity. I buy the other product of one quantity. So what I mean to say is that these attributes may can have multiple values. It can have more than one value. So we represent it by double ovals as you can see. So as I told you that you are not going to design the database. That's not your job. So that will be designed by somebody else but you're going to work on the database. You're going to query the database. You're going to generate or play with the data of the database. So you should know if somebody give you this AR diagram, you should be able to know what this ER diagram is representing. And it's a very simple thing. If you see it's just like the walk in the park. It's so simple. Moving ahead. Yes, all of you are right. So it means that these values see attributes are what? They are like column they are columns, right? So the values of these attributes, the values inside these attributes will be derived from the other attributes. For example, let's say that you have got uh experience. Now we may derive the value of experience uh with the help of let's say I've got the joining date of the employee and today date yeah so experience in our company let's say that it's simply learn employees for simply learn so experience in simply learn how many years in simply learn so see this value can be derived from the other attributes yeah so we do not have the value we we are not going to save the value in this column rather we are going to derive it during the run time. Yeah. So because see if I save it let's say I save it so today it's 2 years let's let's say yeah so let's say I've saved it 2 years but after 2 months it will be 2 years and 2 months so if I'm saving these value it it may not give me the right values it right now while I'm saving yeah at this point of time maybe it's 2 years for an employee but after a year it will be 3 years so again and again I have to update my database so I I can simply derive it if possible I can simply derive it. So how I can derive it? Let's say I've got another attributes with the uh start date or the joining date. So Rajpal we usually create the column for the derived attributes. We are going to work on it. We usually create the column for it. But u so it's like you may or may not create the column. You can create this column on the runtime. So anyways, you may or may not create the column. Once we start working on SQL, this will be clear. You'll understand. So am I clear? What are derived attributes everyone? Yes, DH has given a wonderful example price. So quantity into unit price is total price derive attribute. Perfect. Very good. explanation. Okay, relationship you already understand. Now, uh there are few things that just give me a second. I would like to just give me a second guys. We'll just learn few more terms. I know these are boring but yeah, we'll learn few more term. So, entity sets are what? What? Entity sets are nothing but the relationship. So for example, for example, we have got employee. Yeah, we have got the employee. We have got three employees. Let's say we have got three employees. Employee one. So let's say employee one is Niha. Employee two is Joan. Employee 3 is Mark. So entity set will tell us the relationship that Niha is let's say working on so and so project. So let's say this is a project. Yep. So let's say this is a project and this project belongs to so and so department. So with the help of relationship set you at least give the basic idea of how the relationship is. So for example I repeat niha is working on so and so project and this project belongs to so and so department. John is working on so and so project and that belongs to so and so department. So this is what this is nothing but the relationship set. So you can see that it depicts that E1 works in D2 and E2 works in D3. So E E1 works in D2 and E2 works in D3. So this is something that is not created as a part of the document also to be very honest that you'll see er model for sure that is part created as a part of the document for the database design. So it just give you the brief of some data some dummy data of the entities and how it is connected to the other entities data. So just give you the glimpse of it. So right now you know about the table. So I'll talk about table only. So tables they they can have relationship with each other. So relationship degree basically tells that the degree of relationship that is between the entities. So basically tells the number of entities that are related to each other. Yeah. The number of entities that are related to each other. So for example if I talk about this one so you can see that I've got only one okay can you tell me how many entities are there how many entities are there they no so those who are saying two it's not two it's one can you see that I yesterday we learned that entities are represented by the rectangle and what is this is a relationship. So this is a relationship. So basically what it is saying is now hear me out. It's very simple thing. So urary relationship is a relationship where the entity is related to itself. So it's a self relationship where an entity a table is related to itself. So if I give you this example of employee, let's say that we have got the employee table. Now hear me out. It's very simple. So we have got the ID of the employee and then we have got the name of the employee. So let's say 1 2 3 4 and name I'll say A B C D. Yep, I'm not writing the proper names. And here here let's say I've got one more attribute that is manager. So manager maybe manager ID. So let's say that A's manager is D. So I will say four. B's manager is again D. C's manager is let's say uh let's say B and D there no manager. Yeah. No manager or so over here you can see that the table is related to itself. You're getting so the table is related to itself. So here because if I talk about the manager ID you can see that if I tell you okay let me ask you the question. So can you tell me what is the name of the manager of A? What is the name of the manager of A? D. Right? So this is what this is self relationship where the table is related to itself. So here because the entity is related to itself we call it as unary relationship. Am I clear? Should I move forward to the next slide? It's a very simple one. If you see now what does binary means? So binary simply means more than uh it means two right it means what it means two. So here you can see that how many entities do we have? We have got two entities customer and account. So where customer is related to the account. A customer can have accounts right? So we have got the customer table and then we have got the account table. So this is what this is a binary relationship. So it involves how many entities? It involves two entities. I hope I'm clear with this that binary relationship simply involves two entities. It's a very simple one. Let me know if you want reexlanation for any of these. Okay. So how about turnary? So turnary is the relationship where you have got more than two entities. So basically where we have got three entities. Yeah, three entities involved. And think of entities for now as a table. Think of it that entities is nothing but the synonym of table. So over here you can see that we have got three entities and all the three entities are related to each other. So employees works in a department also employee works with the organization. So and also an organization can have multiple departments. So there is a relationship between the three entities. So am I clear with this one? It's a simple one. Am I clear? Perfect. Now we'll move to a very interesting topic. the types of relationship. So uh we have got four types of relationship one to one, one to many. So basically see basically one to many and many to one is one of the same thing. It's one of the same thing. So u you would either write one to many or many to one. It's one of the same thing. So actually I say that there are three type of relationship but you'll you'll see that in a lot of blogs they say that there are four type of relationship. So do not get confused. It's all about how you look at it from left to right or right to left. That's the only thing. Anyways, we're going to talk about it. So allow me a second. So let's talk about one to one relationship. So what is one toone relationship? So if I give you this example. So here we have got the employee table and we have got the employee ID table. So what one:1 relationship says that every single instance of one entity is connected to a single instance of another entity. So here when I say instance what does that mean? It's not it means nothing but the row or the record. So do not get confused what this instance is. It's nothing but the row or the record. So over here if I talk about this one this example that is being shown on PPT. So let's say I've got the employee table. Yep. I've got an employee table and there are some employees and yes I've got the employee ID also. Let's say ID 1 2 3 4. So here let's say for every employee like here also let's um employee ID is not a very good example rather I'll say bank account. Yeah. So employees and their respective bank accounts. So here I've got the bank account table. Now when it comes to salary it's the companies you usually ask you to give only one bank account details right? you don't end up giving all the three four that you're holding. So here you've got the bank account details. So again the ID of the employee and some bank yeah let's say city bank or HDFC and so on and so forth. So here the relation is one to one. Why? Because every single instance means every single row. So this is a single row is connected to a single instance of the another entity. So it means this is connected to a single instance only. It means one instance is related to one instance. I repeat one instance is related to only one instance of the other table. It's not that one instance is related to multiple instance on the other table. one instance is related to only one instance of the other table. So this is what this is one to one. Am I clear? What is one to one? If it is not 100% clear to be very honest once we see what others are like one to many etc. you'll able to understand what does instance means. So instance means a row sa it means a row one row one record. Yeah. So it's it doesn't mean the column, it means the row. I hope I've answered your question. So guys, am I clear? What is one to one relationship? It's very simple one. But if still you have some confusion, I'm sure your confusion will be clear once we see the other type of relationships that we have. Okay. Now we look into the other one that is one to many. So one to many is where a single instance of one entity is connected to several instance of the other entity. So what does that mean? What does that mean? Let's say over here you can see that we have got the customer table and then we have got the order table. So this is one table let's say. So I'll just better create my own diagram over here for the better explanation. So let's say I've got the customer table. Now I'm just adding two columns. Um maybe uh yeah let me add three columns. That's fine. Okay. So we have got the ID. Let me add only two. I think two would be suffice. Okay. And then we have got the customer name. So let's say ID 1 2 3 4. customer name A B C D and here we have got the orders table so order ID and the other things yeah let's say uh the product and etc whatever you can think of so again we have got some orders 10 11 12 13 14 and so on and so forth so here in case of one to many one customer one customer can place multiple orders just like if I talk about Amazon. So you are the one customer, one account, right? And you can place multiple orders, right? So here also one instance, this is what one instance. This is what one instance is connected to multiple instance of the other table. So one record is connected to the multiple records. It may or may not be connected to the multiple records of the other instant of the other entity. So this is what one to many. This is what one to many. So tell me this is just give me a second I'll just write this is a customer table let's say and this is the orders table. Now tell me which table is on the one side which table is on the one side customer and which table is on the many side order. Perfect. Now as I told you that one to many and many to one. So this is many to one actually you can see over here but whatever it is yeah one to many or many to one it's one of the same thing it's just that if I put order over here on this side and if I put customer over here so right now it's here it is one and here it is many right so just that if I put order over here then one customer can can order multiple can uh Yeah, order multiple orders. So here we have got order customer. So order is on the many side and customer is on the one side. So this is one to many or many to one. It's one of the same thing. It's just that you see it like this or this. I hope I'm clear with this point that one to many and many to one is nothing. It's just that right to left or left to right. Okay. So here also I think see they don't have the PPD. Oh no, they have the people. Anyways, so yeah. So anyways, we'll talk about many to many now. So many to many. Okay, before we maybe hop into many to many, let's make the class interactive. Why don't you give me some example of many to one or one to many? Any any any example. Now guys, one more thing. So when you create the relationship between the two entities, it's not that it's a rule. Like when I say it's a rule, I mean to say that it's not that the employee will be on the many side, the department will be on the one side. This is what we see. So but as per the requirement, you can keep any table on the many side and any table on the one side. I repeat, as per the business requirement, we can keep any table on the many side and any table on the one side. So it's not that it follows the rule that this has to be on the one side and this has to be on the many side. As simple as that, right? So now I want you guys to give me some examples of many to one or one to many. So I'll give you one example. Okay, I've started getting the examples. Yes, patient appointment date. Very good. Then okay, I'm getting a lot of student subject patient appointment. Okay. Student school. Yes, one school at least in India. Yeah, one school can have many students. No, that's always goes in India. a student never attends more than one school at least that's how it is for for companies also right if I'm if let's say I'm working with IBM so until I'm moonlighting definitely that is not allowed so one employee sorry uh one company being worked by u one employee working in what I'm saying so one company is uh can have multiple employees Patient table, a medical test table. Very good. B customer, right? Yes. Very good. One nation, many states. Very good. One state, many cities. One city, many colonies. Perfect. So now we'll move to many to many. One person many bank accounts H. So many to many is when you have got let's say again we have got the employee table and then we have got the project table. So one employee can work on multiple projects. Let's say we have got the projects let's say uh okay I'll just name some of the projects that I've worked on Sunrust etc. So definitely uh there are situations in the company where one employee works in the multiple project right so you may end up working on multiple project also one project is most of the time being worked by multiple employees so here this is one to many so a bc can work on amx but a can also work on sunrust so it's what many to many so I now I want you to contribute some examples on the chat for many to many. So this is very similar to what we have just covered. But when I say what is cardarity means cardity cardity means how many in a relationship how many. So cardinality tells just give me a second. So basically cardinality tells that how many how many rows of one table can be related to rows of another table. So how many rows of one table can be related to rows of another table. Now you know we just learned about that you know a table we can have tables and suppose there's a one row so one row can be related to only one row of another table or one row can have multiple can be related to multiple rows of another table. So cardinality is like going one step deeper and telling telling about that if this is a row this is this can have what all values or this this can be connected to what all rows of the another table. Yeah. So a relationship cardality is a number of occurrence of the entity that can be associated with another entity. So basically cardinality will tell suppose you have got a row maybe I'll give you the example in the next on the next slide but it will give you the minimum and the maximum cardinality. So let me go to the next slide and then I'll explain you that would be better. So if you see over here the cardality is represented over here. So it says zero which is the minimum cardality and n is a maximum cardinality. Here also zero is a minimum cardality and one is a maximum cardality. So what does it says? So it says let's say I've got some developers in my company. Yeah. So ID and name. In the same way, let's say I've got some projects. So, project ID and the name of the project. So, I'll just quickly write 1 2 3 4 A B C D. Here for the projects also we have got some ids 1 2 3 4 5 6 7 8 9 and maybe some project P1 P2 and P3. So what does what does this cardity signifies? Let's look into that. So let's say that A is the developer but E is not working on any of the project right now. So you're getting we have got two tables. Now a may not be working any in any of the project. So zero means that it is possible that any instance of this entity any row of this entity is not related to any row of this entity. So I repeat let's assume that A is not working. Yeah A is not working on any of of the project. So A is a new hire in the company. Until now the A is not being allocated to any project. So zero means minimum. So it means that we can have some developers who are not who are not involved in any project. N means maximum. It means that a developer so B can work on n number of projects multiple projects. So that's what it means. Zero means we can have developers with no projects. Also we can have developers who are working on multiple projects. Now here 0 and one means again the same thing. Now let's say P3 is a new project and right now there are no developers allocated to P3. So it means that we can have any any project we can have any instance in the project table which doesn't have relationship or which doesn't have any connection with any of the other instance in the developer table. So we can have a project with no developers. So that's what zero means a project with no developers in this example. One means well that's quite weird because that doesn't happen in real life but yeah so one means that a project yeah a project can have maximum one developer. So here let's say that the company is maintaining the projects which are very very small projects very small project just need one developer project. So it means a project can have zero developers or at max the pro uh the project can have one developer and that's why what is this? It is one. So B is working on 1 2 3 also B is working in 4 5 6 but one project cannot be worked by uh multiple developers. So am I clear what does 0 1 0 n denotes h yeah ideally should be n for both but fine anyways we have understood it that is more important so am I clear what does cardinality means what does 0 n 01 means you can just read through the slide but please confirm if I'm clear or I've just got one. Yes, clear. Perfect. So, still I'll just wait for a minute. You can just read through it. It's a very simple thing. So, if you see these numbers, do not get scared. It's very simple thing. Minimum and maximum. Sometimes when we see all of these things, we get scared. Oh, there might be some maths involved, some complicated maths. But that's not the case. So I'll quickly repeat one more time over here. The cardity is represented like this 0 n. So zero is what the minimum number and n is what the maximum number. So minimum is what does minimum number means? It means now we have got this stable developer and we have got this stable project. So zero means that we can have some developer with no projects related to it. So let's say that A is not working on any of the project. A is a new hire in the company until now A has not been given any project. So I can have a developer with no project not related to even a single project. So that is very much possible. N means a developer can work on multiple projects. So B can work on 1 2 3. B can be related to 1 2 3 4 5 6 and 7 8 9. So that is very much possible. Okay. So this this is what 0 and N means. Now here it says 0 and 1. So what does that means? It means again you can have a project. So let's say this is a new project. Till now no developer is working on this project. So think of it as Upwork. Yeah. on Upwork you see that you got a lot of projects with no developer assigned right so we mean we can have the project with no developer so zero means that a project it is possible to have a project with no developer so this instance this instance is not related to any of the instance of the developer table so this instance zero means it is possible to have instances not having relationship with the instances is of the other entity. One means maximum. So maximum means it says that one project can be worked by only one developer. So 456 is worked by B. So that's all you cannot have C also working on 456. It says that maximum one instance can have relationship with maximum one instance of the developer table. So one instance one row can have relationship with only one row of the developer table. So minimum is zero either no relationship or at max one one. Okay. So we are done with definitely we are done with the theory part. So that's a good thing. Now what we going to do is uh I'll just give you the brief the very very brief of the whole thing that how you can create the database how you can create a table how you can insert the data into the table and then later on we'll deep dive into each and everything for database also what all commands we have for tables what all commands we have so we are going to deep dive into everything but for now let's for 10 15 20 minutes let's practice or let's see the holistic view of u using MySQL. So I want everyone to launch their MySQL workbench. So I'm using Windows. So what you have to do is you have to search for MySQL workbench. You have to open it. Now you can launch the uh labs. So yesterday Raashta helped you with the launching of the lab. If you're still facing any issues, you can post it on the chat. We'll see if we can help you right away. Otherwise, at the end of the session, please make sure that you're vocal about it that you're not able to use the lab because if you're not able to use the lab, then you're not able to do the hands-on in the class. So, I'll try to give you at least some hands-on if not all the time like at least sometimes I'll try to give you the hands-on if not all the time. So, uh your labs should definitely be working. So it's not that if your labs are not working, you'll not able to learn with me. Of course, you'll able to learn with me, but then you have to practice after the s session. But yeah, you always have to practice after the session whether your labs are working or not. So uh please make sure if your labs are not working, you're vocal about it and if you have to share your screen and show us then we can allow you at the end of the session to share your screen and we we'll try to solve the issues with your lab. So I want to see if you done on the chat once you are done with opening the lab. So password is same for everyone. I think the password guys what's the password? I think it's p sd is it double one and uh exclamation mark or is it one? Okay. So see everybody's helping you with the password. You can use the same password. Thank you everyone. Thank you so much. You can just write D also. Workbench is fine. Definitely fine. So workbench is what? It's a UI way of working with the databases. You can also work with the databases via commands command prompt right cmds. So that is definitely not how we work with the databases. We always use workbench because that's more friendly and easier to use. No the day uh I don't need any preloaded data. We'll create the database and anyways if you uh if you preload the data also guys if you're using the lab provided to you by simply learn uh so it is get uh it gets refreshed in every 5 hours. So if you add anything to it, it will get deleted in every 5 hours. So every time we are going to add the data for every class. Now you can see that we have got one schema. Now this is a default schema. So schema is nothing but in MySQL it is like a database. Now what is a database? You already know database is the it's like the wardrobe. It's like the library which can store the digital which can store the data for you digitally. So we are going to create the database. Yeah, if you're using your personal laptops as I told you yesterday also it is better that you install my SQL rather than using the labs because u it will be easier for you to access it. So ana it's not when you are downloading and installing the SQL yeah it's not only about next next there are some settings that you have to do. So please make sure that you go through the video which I shared yesterday. Today also I've shared it multiple times. So please go through that video. Now if you do not have the software installed, if you do not have the software ready for you to do the hands-on, please do not start installing it right now. You can uh write down the notes. Maybe you're not able to do the hands-on with me. But you can always write down those notes. You can stay with me in the class and you can do the installation etc after the class. But please make sure that you concentrate in the class just because you're not able to practice. Uh that's very much okay. Yeah, you can write down the comments. So it's okay if you're not able to type it. You can always write it. Oh, I have no idea about this this thing. Shri, I have no idea about Yeah, this thing unable to launch the remote. So Shri, you can try it with Vidya. Yeah, so you can try later. just after some time just retry it. So guys let's get started again. If you're facing any issues with the lab you can keep it parked for now. So I'll try to finish everything by 10 today 10 105 so that we can take up your questions on the lab. So you can share your screen and we'll try to help you out. So let's continue with our learning for today. And as I told you if you're having issues with the lab that is that is provided to you all by simply learn if you're using your personal laptop please download and install my SQL. So I will not be using lab I'll be using my SQL on my personal laptop. So the first thing that we are going to do is we are going to create a database. So as I told you that today uh the for we I'm going to give you the holistic view of creating the database the tables inserting the data into the table and then querying the table but after that we are going to deep dive into each and everything. So in order to create the database in order to create the database we start with the command create. So this is we going to write the SQL in order to talk to the DBMS. Now I want to tell DBMS that I want to create the database. So I'm writing the SQL command in order to create the database. So I'll say create database and I can give any name to my database. But this has to be as create a database only. So you cannot have anything else. These are the keywords. Whatever you see in blue are the keywords and you have to make sure that you write the statements like this. So and then you can give any name to your database. So let's say that I give the name to my database as school. Yeah. And then see I can always end my comment using the u what do we call this? No it's they there are no limitation. You can use upper case and lower case both. Yeah using semicolon. Thank you so much. So I can edit and if I don't edit also if I've just got one SQL command it will still run. Now in order to run it we have got this button. Can you see this icon? A small icon. Let me increase the size. I think the size is already increased. Just give me a second. Yeah I think the size is already increased. So anyways so this is the button that you need to use in order to execute a command. So if I just hover on it, it says execute the execute the selected portion of the script or everything if there is no selection. So if I'm not selecting anything, yeah, I'm not selecting anything. If I execute it, it will execute everything. Each and every line of code, each and every word that is written on my SQL file. This is what this is a SQL file. You can say SQL file. So because I've got the previous SQL file in my case it shows six. In your case you're using it for the first time it might be showing one. So I'm going to run it. Once I run it over here you simply have to expand the lower panel and you'll see that create database school one row affected and you can see this in green. it means that the database has been successfully created. So if you see in red, it means there was there was some error when you were creating the database. Guys, please make sure that you are attentive in the class. Otherwise, what will happen is that you may get some errors because you're quite good in number. So if you're not attentive then you may get the errors that I can always resolve. But if I get a lot of queries from you guys just because you're not attentive then I will not able to cover everything whatever I want to cover as a part of this training. So I don't want to cover anything on the surface level. I want to deep dive into the topics. I want to make sure that you understand how things are working. So for that I want only I have only one request from all of you. Just be attentive because if you're attentive I'm 100% sure you'll able to learn it. SQL is very easy. So you can see that the database has been created. In my case I've got the other commands. So if you have noticed I was some dropping I was deleting the databases. So these are because of that. But yeah the database has been created. So I want you guys to create the database and let me know when done. So you can just write D also instead of writing the whole run. So few runs will give me help. So it's okay. I'll come to that that where you can see the database. Right now I just want you to be sure that the command that you have written is executing successfully or not. So just that you have to write this command Rajni. Yeah. So you have to create a new SQL file in case if your new SQL file is not open by default. So this is from where you have to open the new SQL file and this is a command that you have to run once you run. This is a command that you have to write. Once you're done with writing the command, you can run it from here. Okay. I'll come to that output. I know that you're not able to see the output. So, just hold your horses. We'll talk about that also. Now, I can see that most of you are done with running it. And I hope that over here you see a successful message. this green button, this green icon. Yep. So the database is not appearing over here. So if you see over here, schemas, think of it, schemas are nothing but the synonyms of databases. So in MySQL, so the database is not appearing over here. So what you have to do is you have to refresh it. So this is what you have to press you. This is what you have to click on in order to see the database. Now you can see that the database has appeared. So you get to see the output now. So those who are saying that unable to see the output, I hope that you guys are able to see the database created. Now guys, maybe this panel is also collapsed for you. So please make sure that you expand it. I'll wait for a few seconds. Output window. So you have to scroll up. You have to It might be collapsed like this. So you literally have to expand. Okay. One more thing. You can go to view, you can go to panels. And over here guys, please make sure that you're looking at my screen. So see output area, I can hide it. So maybe your output area is hidden. So what you can do is you can go to view. You can go to panel and you can say show output area. My schema tab is not there. You're talking about this one. So I'm not sure for this one side. Okay. Yeah. So in view you can see just uh check go to panel and click on show sidebar. I I hope I've answered the queries or the queries on the chat. So guys, if it's okay if you're not able to see the sidebar or if you're not able to see the output panel, you can still continue practicing with me and we'll see at the end of the session what is the problem. I'll ask you to share your screen and we'll look into it. At least you can start writing the queries because I've told you that these u we these could be the problems. Still you're not able to see then you have to share your screen. Okay. So at least you can write the code. Fine. Now see this database is created. So you have to use the database. Basically you have to select the database. So how would you select the database? How would you select the database? In order to select the database either, now hear me out. Either you can doubleclick on the database. See if I double click it got selected. Or what I can do is I can run the command. So just delete the previous command. And you can run the command use the name of the database. School. That's the name of the database. You can run it and then it will basically select the database. So how you'll get to know that this database is selected. So you can see that this is in bold. So as soon as any database shows in bold, it means that this database is selected and whatever whatever you're going to write or whatever queries that you're going to perform will be performed on this database. So just double click on the database or you can now we are going to create the table. Now before we go ahead and create the table I would like to show you let's see you can collapse and expand the database. Now database has got something called tables. So these are the entities. Yeah. So we were using the word entity entities. So tables, views, stored procedures, functions these are the entities. So in your curriculum you have got tables and views. So we are going to cover these two. So first we'll talk about the tables because we going to cover everything related to the tables. View is a very small topic. It's a short topic that we are going to cover later on. So what we going to do is just give me a second. School is not highlighted but command executed successfully. Just double click on it. Sheila So Rajpal, I'll come to that question. So you'll get to know all of these things. Yeah. Hold your horses everyone. This today is the second day of the class and we've just started coding. You'll learn everything fine. Now what we are going to do is right now you can see that I we do not have even a single table in our database. So we are going to create the table and guys as I told you please uh don't worry about not able to see the output panel not able to see the schema panel we'll see to that at the end of the session right now just focus on the learning so I have already helped you with it but if that those options are not working then I have to look into I have to look at your screen and then I have to help you okay so we going to create the table so how do we create the table so in In order to create the table, we write the command create. So you can make it upper case, lower case, everything is okay. So let me make it upper case. So create table and then I'm going to give the name of the table. Now I want everyone to see my screen. Then I'll give you time. Yeah. Now this is what this is the name of the table. Now you know a table can have multiple columns in it. Yeah. A table can have multiple columns in it. So how do we define the column? We give the column name. ID is a column name. And then we give the data type of the column. So yesterday when I was teaching you the different type of database that are available in the market, I told you that RDBMS is very strict. It means that you cannot play around with the data. Like if you've got four columns, you have to stick to the four columns. Then a column also has a data type. When I say data type, what does that mean? It means the type of data a column can hold. So u ID can hold integer in means integer data type. So we are going to learn about all the different type of data types also later. So for now this much of knowledge is more than enough. Okay. The next column is let's say full name and full name has a data type vcap. It has got the data type vcap. Then let's say age and age has got the data type in. Yeah, age is always a number. So vare what does vare means? Sorry, ware we also have to define the number of characters that this column can take. So what does 50 means that at max? At max your the full name can be of 50 characters. So if your full name is more than 50 characters the table will say no I can't cannot take it. So it can take characters and at max it can accommodate 50 characters. Moving ahead the next column is city and here is the data type of the city. It's again let's say 50 your bar 50 and my table the code for the table creating the table is ready so just give me a second I'll just quickly look into the chart video with you what you're saying is right van can accommodate numeric values as well as alpha numeric values but it is always considered as a string so what I mean to say is that this is what this is a But if you give three in vcar it will take it like this. So how it is going to hold it that's matter. So it will it will u make three. Yeah it will take three not as a number but as a string. Yeah this is what this is a string. So like this. So anyways the code string and ware is same. Yeah it's it's a it's a technical language. So string means text. So if I say string or if I say text, it's all about the data type. Yeah. Or if I say var. So in SQL we say var, right? In Python we say string. Text we don't say in any of the language computer language I'm talking about. So they can hold strings. Strings can be anything. Yeah, it can be anything. It can be this. It can be number. It can be any any anything like this. So anyways, I'm going to simply remove this. Now I'm going to run this command. You can see that it got created and I have to refresh in order to see the table created. Now if I just expand it, you can see the table has been created. So I want everyone to run this command. I'll wait for a minute for 2 minutes. Now guys, please make sure if it is showing error, you also read the error. So please delete the above commands that we have run before. Or what you can do is let me uh so maybe this time I'm not going to delete the command. So I'll show you how the does the error look like. So what I've done is I've created the table. The next thing what we will do what we will do the next thing. Come on think about it. We are done with creating the table. Now we are going to insert the data into the table. You don't need to use the table as such. just like DB databases. So we are going to insert the data. So a run you can share the screenshot on the chart. So I look into it. What is the problem? So in order to insert the data we we run insert into command. So insert into the name of the table. The name of the table is students. Now I can give the columns of the table. Now that is optional. We'll come to that later but right now as I told you just a helicopter view of everything a small snippet of uh SQL how we can create the table insert the data into the table and query the table. So anyways, I'm going to give the column names. SQL is not case sensitive and then I I can give the values. So in values let's say ID is 1. Now it's a var means it's a string. It's a text. So I'm going to give it in the in the single quotes. Amit age any age and city comma I can give multiple values. So I'm just copy pasting it. So let's say ID is two. The name is okay. anything and Mumbai in the same way I can insert more and more records. So I have to add comma then again I can insert more records. Yeah. So you can see that I have I'm done with writing the command for insert for inserting the data into the table. So this will insert three rows into the table. Now guys I'm intentionally going to throw the error. So please make sure that you look at my screen because you may also get the same errors again and again. So please understand how things are working. So right now if I execute it, you can see that it is giving me error. So I want you guys to tell me what is the error. What is the problem? So I'll tell you the problem. The first problem is that first of all student table is already created. So that's okay. I'll talk about the first problem. So there's a problem with the syntax. The syntax is hear me out. This is a separate command and this is a separate command. Yeah, this is a separate command. So see it's a machine. I have to tell that this command is separate and this command is separate. Right now it is making both the commands it is taking both both the commands as one command. So how I can do this separation by adding semicolon. As soon as I add this semicolon you can see that the error that I was getting. You can see the error that I was getting over here. It will get resolved because I've added the semicolon. So this was a first problem. Now let me rerun it again. It will throw error. So see now you'll able to understand what the error is. Table student already exists. So basically what is happening I'm when I'm running it when I'm running it it is executing this statement but the problem is that the student is already there in this database student table is already there. So it is saying me that student table is already there. So that is the problem. So anyways I don't want to run this because I already have the student table. So what I'm going to do is I'm going to run only this command. So see I have to select this command and I have to click on run. So I told you that when you just click on it, it will run each and everything that you have. So that's what it says. If you go over here and hover, that's what it says. Or what you can do is you can simply select the code that you want to run. So here I'm selecting this code and I'm going to run it. Perfect. You can see that it got run and the data is inserted into the table. H okay. So I want everyone to try this. Yeah, there are some shortcut keys also. You can just Google the shortcut keys. I'm very bad with reme remembering the keys. There was uh I think it's control I have to Google. Yeah. So this is a question that is asked to me in every batch and I tend to forget the answer. Yeah. So I I'll help you. Just give me a second. I'll also I can also Google the shortcut is control + enter to run the query. Control + enter. So space whenever you have a new word you're going to give this space sha. So here ID now I'm not giving any space I'm just giving comma then full name then again comma and so on and so forth. Yeah, up now you can just run the command use school. If you're not able to see the tab this left panel just you run this command useful and then create the table. So guys let me know when you're done with inserting the data also I'm sharing the code on the chat. Done. What about codes? So you're going to use the quotes for the varita. So whenever you're writing the strings, strings are always represented in computer in in machines whether you're writing SQL or whether you're writing you're working on Python, C, C++, strings are always denoted with quotes. It can be single quotes or double quotes but single uh the strings are always encapsulated in quotes. Always remember this. So, okay Shri, so you can try working. Okay, got it. Got it shri. So, control + shift + enter. Now, definitely you have entered so much of data here. I' I've got three rows of data. I would like to query the data. So, yes, I'm going to write the query. So, here we go guys. So, I'm writing in the same file. Select star. So star means all the columns. All the columns from the name of the table. What is the name of the table? The name of the table is students. Again, please highlight it and then run it. Do not just run it without highlighting it. Otherwise, if you have the previous code, this will also run and you may get error that the table already exist and so on and so forth. So, please highlight it and run it. So now we're going to learn about some of the database commands. So you have already learned most of it but yes. So what we I'm going to do is I'm just creating a new SQL file. The reason is that I will be needing this code and I do not want to delete it because I will be needing it as simple as that. So anyways I'm going to create a new database. So you already know about this command create database and then the name of the database. Now if I run it, it will create the database for me. We have already seen it. But if I rerun it, then it will throw error. You can see over here. Okay. So I'll I'll try to keep my pace low. So I just uh told this thing to you quickly because uh I thought that we have already worked on this command but I'll keep it low. So what I've done is I just added a new SQL file in order to write the code. Okay. So what I've done I am creating the database. So you can see that the database got created. It got created. Now if I rerun this code. Yeah. If I rerun this code, you can see that it is giving me error. Yeah. You can see over here create database DB. It means the error is in this command. And what is the error? The error is that can't create DB 100 because the database already exist. So this is the error. So we have got a keyword called if not exist. So what I can do is I can say create database if not exist. It means as a word says that create the database only if it doesn't exist. If it exists then you don't need to create the database. No need to create the database. So if I run this command now see you know that this database is already created. This database is already created. But if I just simply add if not exist to my SQL command it will not throw error. And that makes sense also because now what we are saying is that create the database only if it doesn't exist otherwise no need to create the database. So if I run this command my command is running without giving me any errors. Yeah. So it it is not giving me any errors. It's just say it is giving me a warning. Now over here you can see just a warning. So it's not the error but a warning that we cannot create it because the database exist. So this is the error. This is a successful operation and this is a warning. So here all I have done is I've used this keyword if not exist. So I want everyone to do this. So let me know when done. Few done will give me heads up. All you have to do is you have to run this command in order to create the database and you can rerun this command in order to see the warning. Make sure that you run it multiple times in order to understand the working of it. Uh sheet if you want to go to the previous stage if you have not clicked on the cross. So if you see over here there's a cross like you know there's a cross. If you cross it without saving it it means that uh you have lost all the changes. But yes or it's these are the tabs you can hop on and hop off. Okay, I hope I've answered your question. Fine. So, we have created the database. Let me end this command with the help of semicolon. Mhm. Now I'm going to drop the database. So drop database and again the same database DB00. So we are going to rerun the commands in order to understand the working of it. So I'm simply highlighting it and I'm executing it. The database has been dropped. Now if I again execute this thing, let's see what happens. So why don't you guess what will happen? See the database has already been dropped. So if I rerun it, what do you think? What will happen? Error. No error. See how about others? So guys, I'm not expecting the right answer. I'm asking you to uh take a wild guess that what will happen. Make a wild guess. Drop database DB 100. Okay, let's do it. You can see that it is giving error. Why it is giving error? Because it says, "Oh, you're asking me to delete something that doesn't exist." So machine doesn't understand like we are we are having that history that we have created it and dropped it and blah blah blah. But machine says, "Oh, you want me to drop a database, but I cannot find it. I cannot find this database." So that is why we are getting the error. So what you can do is you can add if not exist the same keywords. So if you add this then sorry if exist drop database if exist. If this database exists then drop it. If doesn't exist that's okay. Yeah. So now if I run it it will show me warning. If not done let me know. So whenever I'm asking you to do the hands-on, make sure that you do the hands-on. It's not that I'm going to ask you to do the hands-on every single time because we also have to make sure that we cover the curriculum and we cover the maximum concept. But I'll try to give you as much as hands-on as possible in the class. Now let's recreate the database. Right now it's like we do not have even a single database. So let's recreate it. So Shireen we'll look into this problem at the end of the session. I'll ask some one of you to share the screen and we'll look into this problem. So please do not get bothered of about the same. Fine. Now let's say I'm deleting this drop database if exist. Now I want to know that what a database I've got in my machine in my sorry not machine in my workbench. So let's let me do one thing. Let me create another database. So DB 100 I created before and now I've created DB200. So just uh just for the sake of creating I'm creating two databases. So you can also go ahead and quickly create the two databases. Fine. Now let's say that you want to know that what all databases you have got in your MySQL. So what you can do is you can run the command show databases. So this will show you all the databases that you've got in your DBMS. So see we have got these databases. Now you'll see some extra databases. For example, you'll see information schema, performance schema system. Anyways you can see over here. So these are systemdefined databases and maybe I'll tell you about these databases not today but yeah in some one of the class that what these databases store. So today is definitely not the right day to tell you about these things but yeah we'll definitely going to talk about these system based databases Oracle I've never worked on with yeah show databases. So I've never worked on Oracle and Postgress. I've just worked on MySQL and SQL server. So you can Google it if it works on Oracle or not or if you want I can Google and I I can tell you the answer. Okay, no problem. Fine. In the same way let's say that uh okay I'm going to use the database. So I'll say use DB 200. I'm using this database. Now once I'm done with using this database I would like to create this table and insert some data into the table. So basically I'm using this database and then I'm creating I'm creating the table and I'm inserting the data into the table into this database. So this we have performed previously also. You can see that the table has been created and the data has been inserted into it. So I'm just copy pasting this code on the chart. You can also go ahead and use any of the database and you can create the table into that database and insert the data into the d into the table. So I'll wait for 2 minutes. Go slow. No need to hurry. Like this is your first coding class as such but later on you'll get used to it. So please make sure that you do not come without practicing in the next class. Okay. So, Fati Fatima first of all see I asked you to create multiple databases. In my case, I've created two. So, first of all, I'm using this database. So, I ran this command and then I've created this table and inserted the data into the table. So, you can copy paste this command from the chart. I've shared all the chart and you can run it. So, that you have got a table created. So we are done with all the major database commands. So now we going to learn about the data types in SQL. So if you remember when I was telling you to create the table when I was teaching you how you can create a table. I told you that for every column you also have to tell the data type. The data type is what? the type of data the column can hold or will hold. So here we go. We are going to learn about the different different data types that we have in MySQL. Now before we get on to that uh I will just quickly tell you that how you can save your code. So one of you was asking me that Tulika this code we have written how we can save it. So how you can save it is can you see the save button over here? So all you have to do is now all of these things are something that if you just try it by yourself you'll able to do it because we all are using laptops. Yep. Since ages so the a lot of options are same for every application. So you can go over here you can go to file maybe here do we have the save option? Yeah we have got the save option over here also. This is a shortcut save script as. So the same options we have got. So what you can do is you can click on it and maybe you can save it. So I'll just quickly show you and then I'll give you time to just try it at your end. So let's say my SQL 100. This is what I'm saving this file. Now I'll just show quickly show you on the desktop. Okay. Where? Yeah, this is the file. MySQL 100. Now if I want to open it, how can I open it? Let me close it. How can I open it? Just like how you use any other software. Yeah. The first thing is if you want to open something, you go to the file. So here also I'll go to the file and you can see open SQL script and I literally have to uh find where I have sh saved it. Yeah, here we go. And I'm I'm I'll open it. So in this way you can save the SQL scripts that you're writing. So maybe I'll wait for 2 minutes. You guys can just try saving the SQL script file. Fine. Now we're going to look into the different type of data types that we have got in MySQL. So I want everyone to see my screen and please make sure that you listen to me. Very important. So the first data type that you can see is car. So car simply means characters. Yeah. When I say character, you can say text, string, whatever you want to call it. So car data type is a fixed length string. It's a fixed length string. It can take the values like you can have the car data type. The column can take zero characters. So that's a minimum range or maximum range is 255 characters. Yeah. Now when I say it's a fixed length string, what does that mean? It means hear me out. So if I write let's say ID and if I say care and if I give two so it means that ID when I insert the values when I insert the data into the table in the ID column in the ID column it has to be of two characters. Yeah it has to be of two characters. So 1 2 1 3 3 4. So you're getting again I'll give you one more example. So let's say that I create a column. Let's say the column is okay. I'll create the column code. Yes, state code. And I give the car as a data type. And I give three. It means that whenever I insert the values, insert the data into this column, it has to be fixed length. See, it says it has to be fixed length. Means that I have, it has to be fixed length. So I have to give three characters only. Yeah, three characters only. Or let's say if I give anything else, let's say uh something else. So I'm just thinking of something which will have the text as such. Okay. So I'll just say state only. Yeah. Again I give or maybe let me give month. Yeah. Months. Now I create a column months and I give the data type as car and then I give it the length of three. It means that whenever I insert the data it will have the values like this. So if I try to insert this it will give error or if I try to insert m it will give error. If I try to insert ma it will give error. So it means that we have to add to the length. It is fixed length. It means that it will not take more than this and it will not take less than this. Am I clear with care? I've given you multiple examples. Am I clear? Very simple. Now let's talk about var. So what do you think V stands for? What? What does this stands for? Variable. Right? So here it is what? variable characters. It means that when I create a column of vcar data type and let's say this is a column and I say vcar 100 it means that it can take the characters up to 100. So let's say I create a column name the data type is vare and if I give let's say 50 over here it means that when I insert the values into this column I can give anything I can give tika which is of six characters I can give Ravi which is of four characters and I can give a very big name which is of 50 characters but if I give a very big name with let's say which is of 60 characters then it will go error. So it is telling us the upper limit that the upper limit is 50. You cannot go beyond 50 but you can choose any number within 50. It can be 1 2 3 4 any number for that matter. So this is what var is. Yeah this is what var is. Just give me a second and you'll see that you end up using vcar a lot. Now some of the data types you're going to use 90% of the time. So vcar is one of the data type in is one of the data type. Anyways let's talk about text. So seeare says that I can accommodate characters up to 255 only. So that's my upper limit. So if you have a very big something let's say you have a description let's say you want to capture review reviews you know that on the products we have got the reviews of the customer so var is saying that no I can capture only 15 255 characters I can't go beyond it that's not in my nature so what you can do is for a column like review or description you can simply make it text so text can accommodate large text data. Large text data. The next one in our list is blob. So what does blob means? Blob is binary large object. So do not get u petrified with this term. Yep, this is a very common term that is used in the technical world. So blob simply means any any file for that matter. It can be any CSV, text file, Excel file, it can be image. Yeah, it can be etc. So any file uh is called as a blob file. So you can also have blob files inserted in your tables. So for that you need to create the column with a data type blob. I int says integer and this is a limit of it. You don't need to remember the limit but it can take negative numbers and positive numbers as well. Positively you have seen it can take negative numbers as well. Now if you want uh if you know that your column is going to store the integer and the integer is going to be very small in number right it's going to be very small in number. So usually let's say age yeah age you know that age cannot go beyond maybe 120 not more than this you know that yeah that 120 is also like a very high age that I'm talking about. So what you can do is you can go with tiny int instead of int. So let's say that you want to save salary. So for salary int is a uh you can go with int. But when it comes to age or the numbers that you're going you know that they're going to be very small. So you go you can go with tiny int small integer. Now let's say that you have got really big integer. Really big integer. You're talking about the revenues of the companies like Amazon or what Tesla we have got so many companies with really huge revenues. So it may fail because again it has got some upper limit. So then you can go with big end. Okay we can ignore this thing. Let's talk about float. So float is what? Float can store decimal. See integer cannot store something like this. No, if you want to store decimals, if you want to store decimal data, then you can go with float. Yeah, you can go with float. Double is again for the decimal. Yeah, you can double is also for the decimal numbers. But double is high precision whereas float is uh less precision. So what does that mean? What does precision means? It has nothing to do with my SQL. What does precision means? So precision simply means prec precision sorry simply means just give me a second. I'll write it. Yeah. So it means precision over here. So what does it mean? It means the total number of digits stored. I'll write it over here. Total number of digit stored. Yeah. Total number of digits stored. So see when I say float. Yeah. When I say float. So float says that suppose in case of float let's say that you have got a number like this. So I'll just write a big number five 6 7 8 9 10 something like this. Yep. So what float would do it will simply round it off. Yeah. It will round it off and it will store something like this maybe. Yep. Uh five and then seven. So it will round it off. Now double will also round it off but it will it will round it off at the higher precision. So it will maybe do till here and then it will round it off something like that. Yeah. Then it will round it off. So it's all about u how many digits both of these get stored. So float has less precision. It means that if you have got a big number it may round it off like this. and double has got higher precision where the number get you know rounded off like this. So that's what it means. Yeah, that's what it means. I hope I'm clear. So higher precision means more accurate digit stores and lower means few fewer digits uh stored. Fine. Moving ahead, we have got boolean. So anybody who doesn't know about boolean, you can be very very honest here because I understand that a lot of you are coming from the nontechnical background. So this will give me the clarity how how do I have to explain you boolean? Anybody who doesn't know okay I'll explain. So boolean boolean is a very important data type which stores two values yes which means one. Yeah either you can also denote it with one. So yes it's not yes actually it is true. So let me write true means yes. So true and then we have got false means zero. So it stores only two values that is either true or false and it is a very important data type. So for example if I just give you one uh lemon example you might have seen check boxes. Yeah when you're filling the form you might have seen check boxes. So if you check it so ideally because I come from the background where I've also developed a lot of application. So that's what I'm telling you that when you see the check boxes in the back end, these check boxes are of boolean data type. It means that if you check it, it will hold the true value and if you do not check it for this one, it will hold false value. So that's what boolean means. True or false only two values. Then we have got date. Date. Now you already know what date is. I do not have to elaborate on this part. So the date will take this format. Y mm dd format. Time you already know about time. Yeah it will take this format. hh mm ss date time. The name itself says it will store date as well as time. Yeah it can store both date. So let's say today's date plus today's time. So it can store both the things both the things. And then we have got time stamp. So time stamp maybe we are going to talk about this uh more later maybe I'll use it somewhere I'll show it to you. So time stamp is uh auto date and time system based it means that if you give any column yeah if you write any column with the data type of time stamp what it will do it will take your system time. So whatever your system uh time stamp is yeah the date as well as the time if I change it and if I make it something else then it will take that only. So take system based date and time. Yeah current date and time as per the system. Exactly. Very good. Fine. So we have covered most of the data types all the important data types. I would like to tell you one more thing. So we have got signed and we have got unsigned. So let's talk about this also. It's a very easy concept. So see you can have sign. So all the data types all the data types are signed by default. So what does sign means? All the data types. Now here I'm talking about the data types which can store numerical values. So here I'm talking about the data type which can store numerical values. So signed simply means that for example if I talk about tiny int. Yeah. So by default it is signed. By default it is signed. It means that it can store the negative and positive values. So if you remember from the previous slide see we have got tiny int small integer and this minus 128 to 127. So here tiny int can take the values from minus 128 to 127. And if I create a column, let's say I create age column. And if I give tiny hint, so by default it is signed. So maybe I'll not write it. It is signed. So it means that I can store minus 128 up to 127. This is upper limit and this is a lower limit. Five. What is unsigned means? Unsigned means that if I make it unsigned. So if I again say age tiny. Okay, here it is already written. So maybe okay, let me write it. The writing is so bad. Okay, tiny. So difficult to write on the note on the part. Anyways, tiny int. And then if I give unsigned what will happen? So this will give me extra cushion. How it will give me extra cushion? It means that I'm explicitly telling SQL, hey SQL, I want to use tiny int. But I do not want this negative numbers. I know that age is never going to be negative. Well, age is never going to be 200 and 255 also. But yeah, that can happen if we are uh u working on tortoise age. I think to can live till 300. So anyways, so let's say that I'm just telling that I do not want negative numbers rather can you just give me extra cushion. So just remove the negative all the negative numbers and can you just give me extra cushion for the positive numbers? So that's how we can use it. Yep. So here you can see the limit is 0 to 255. So whatever numbers we had over here, they have been added over here. Yeah, you just have to do the calculation. This will come around to be 255. So we are saying I do not want negative numbers. So do not waste my range in negative. Yeah. So I just want the positive numbers. So unsign will help me to increase the range. Am I clear with what is sign and unsign simple five now you have to allow me a minute I forgot to open my notes because again we are going to create a table and this time for the table because we going to just play around with the databases the sorry with the data types So yeah the table is going to be a big table. So right now I'll ask you also to copy paste it but after the class please make sure that you type all of these statements. So what you can do is you can um I I'll share all these notes also with you. I'll share these PPTs also with you. But I would suggest you to make your own notes. Right? So what you can do is whatever comments that I'm giving you on the chat, you can create your own Wordpad Notepad file and you can keep saving those comments. See, I want you guys to be very very active during the session because when you're active, when you're participating on the chat, when you're doing the hands-on, let's say you're just picking up the code and pasting it and saving it in your notepad file. What happen is you tend to be attentive. But if you're just lying down and listening to me, then I'm sure you're going to daydream a lot. So I do not want that to happen because each and every minute of the session is important. So anyways, here we go. This is a table. So we already have got the database, right? Sorry, I'm just deleting all this. So this is a database and here I'm going to create this table. So I'll just quickly run this command. Just give me a second. What happened? Yeah. So the table has been created. Now you can see that this table has got married data types. So it has got int is unsigned. What was unsigned? Let's quickly see what was unsigned. Only positive numbers. So it gives a really huge question. Yeah. If I just remove the negative numbers, you you can do the calculations. So it will be something around 4 ++. So I get extra cushion of positive numbers where you already know. Okay. Here again unsigned small int. Okay. We have got something called small int also which is I think smaller than tiny int. Then or maybe let let me make it tiny int. Yep. Then decimal. So I'm giving the precision over here. So 82. What does that mean? 82 means that I can have digits like 1 2 3 4 5 6 7 8 and 2 is what? After decimal how many digits? So I can have 1 2 then I've got float. Then I've got boolean which can have true or false. Then I've got date and date time. So I'm playing with a lot of data types in one table. And then I have to insert the data. So inserting the data code is definitely more cumbersome than creating the table with so many data types. So I want you guys to see my screen. So here we go. I'm inserting the data. This is int. This is vare. Yeah, this is int. This is vare. This is tiny int. This is again tiny int. So this one is unsigned. This one is signed by default. Yeah. So you can see that this is decimal this is float boolean I'm giving one I told you that either you can give true or you can give one false or zero then I I'm giving date what is the format y mm dd that's a format and here I'm giving date time yeah and in the same way I've got multiple records so I'll insert it records inserted and let me quickly query the table select star from products product please make sure that you give right name of the table and it is done so I'm going to give you this code at least the select code you can write by yourself you don't need to copy paste you know that I definitely I do not want you guys to copy paste so run this command Okay. Yeah. Very good question, Sanscar. So, uh, see guys, uh, in the last in the last insert command, we gave the column name. So, we can skip the column name over here. If you see, I have skipped the column name. So, when can I skip the column name? I can skip the column name when I know that I'm going to insert the values into each and every column and the sequence is going to remain same. Yeah, the sequence would will be the first value that I'm giving is for product ID. The second value is for product name and so on and so forth. I know that the sequence and the columns I'm giving values to all the columns. Yeah, I'm when I'm inserting the data, I'm inserting the data into all the columns. Also, I'm maintaining the sequence of the data or I'm matching the sequence of the inserted data with that of the columns that I've got over here. So, if I know that that's the case, I can skip the name of the columns. Yeah, I I can skip the name of the columns. So, we are going to talk about this thing more. Yeah, that when you can skip and when you cannot skip. I we are going to talk about this more. So, uh right now our agenda is to learn about the data types. Yeah, the agenda for writing this code is to understand the data types. So, yes, now I'll I hope that I've given you the code. Yeah. So, I've given you the code now. So if you change the sequence, see if you change the sequence then it will give you error. For example, if I try to insert laptop over here. Yeah. So let me do that. Yes. Let me I'll just give a string over here. Let's say I am giving a string over here now. But it was a quantity, right? It was a quantity. But what I've done is I'm I'm giving a string. It's an in integer. Yeah. It's a tiny in. So it will throw error. Now if I run this, it will throw error. It has thrown error. See, so when you're inserting the values, you have to make sure that these sequence, this sequence matches with this sequence. This is very important. So I want everyone to run this code. Create the table, insert some data into the table. You just check the format of the table, the data. Yeah. And then query it. And we done with today's learning. So you can take the screenshot, Sheila. But I'm going to provide you with all these slides also. But yes, you can take the screenshot. I'll uh hold for some time on this slide. I'll also hold for some time on this slide. So in order to create a table, this is the basic syntax of creating the table. So I want everyone to see my screen. Now you already have the idea of it because we have created multiple tables in our previous session. So we start with a keyword create. Then we have got the keyword table and then we give the table name H. And then after this we give different different columns and different different data types of the column. Now this you have already seen right. But apart from this you can also add different constraints. Now what are constraint? We are going to look to that today itself. So let's do one thing. Let's quickly create a table first. So what you can do is you can open a new file and the first thing that you can do is you can create a database for today. So let's say 28 I'll just give it a name fee. Yeah. So I'm going to create the database. You can see that the database has been created and I'm going to select this database. So either you can select it by double clicking on it. Now these things we have already done. So I'm quickly telling you it's like I'm reherating. So that's why I'm pretty quick over here because the things we have which we have already covered I would definitely be little bit quick over those things. Now the database is created and I'm going to use this database in order to create the tables. So I'll wait for a few seconds for everyone to have their database created. Now you already know how to create a table. So rather than you know writing the same code again and again typing the same code I would like to quickly copy paste some of the code. So uh you know that how we can create the table. So I'm just taking this code of creating the table. Yeah. So you can see that I've got this table. This table has got four columns. So these are the four columns. And then I would also insert the data into this table. Now guys, if you have missed any of the class previous class, I'm afraid and I'm really sorry for the same that I'm not able to repeat the concepts because we have got limited classes. These are not unlimited classes. So every class has an agenda associated to it. So please make sure that you go through the recording. So you'll find recording in the LMS portal. So here here it is. We have got the student table. I'll copy paste the code. Hold on guys. And then I'm inserting the data into this table. Now I want everyone to see my screen. See this will create the table for me. I'll just quickly check. Yes, table got created. Now I'm going to insert the data into this table. Data got inserted. Now there are few things that that I would like to show you that I would like to tell you. So here see I'm passing the names of the column. So if I do not pass the name of the column, let me delete the names of the column and I'll just do one thing. I'll just add one more uh row of data. Yeah, I'll just add one more row of data. So here maybe I'll just write anel and I'm okay with the other things. Yeah. So if I execute this see it got executed. So this is fine. But if let's say that I've got some data of some of the students. So I've got a data of a student with a name let's say mark. But I do not know the city of the of mark. So let's say this is the data I have. Yeah, this is the data I have. Now listen to me. It's very simple. Now if I run it, it is throwing error. So you get that if you have the full data, if you have the full data, when I say full data, I mean to say that you've got the data for all the columns of your table. So if you've got the full data then it works perfectly fine but if your data is not full right now I don't have full data right then I have to explicitly define the columns that I'm going to use. Yeah for example id, name, age. So now I have to explicitly tell my SQL that five should go in id name should go in mark should go in name and so on and so forth. Let me execute it. And now it is getting executed. So you got my point. It's a very simple point that when you have the full data then you can omit the column names from here. But if your data is not full, yeah, if you have partial data, incomplete data, then you need to mention the column names. That's how the syntax goes. Now, you don't need to insert so many records that I did that because I wanted to show you, I wanted to explain you. I'm just sharing this code with you all. So, here's a code for creating the table. And here's a code for inserting the data into the table. Yes. So you can copy paste this code. Avoid typing each and everything in the class because if you type each and everything, see I don't mind it. You need to understand my predicament also over here that if I give you time for typing each and everything in the class then I may not able to cover all the concepts and I want to uh cover maximum things. I want to uh g give my maximum knowledge to you all share my max the maximum knowledge that I have with you all subject I don't have any such column name kun in this table I've just got four columns as you can see insert What? Subject. But subject is not there, right? Okay. You want me to add one more column over here? Kunal, is that so? So, Kunal, we are going to look look into that also that how we can alter the tables. Let's not do it right now otherwise everybody will get confused. But we'll see that you have got some data uh your structure already created. So how you can alter, how you can play around with it. Done everyone. So I told you that in case of when you create the table, you can also add the constraints to the table. So what are constraints? So constraints are uh think of it, they are nothing but the rules that you apply on the column. Yeah, they are nothing but the rules that you apply on the column. So h we'll learn about few of the constraints and then we are going to write the code around these constraints. So the first or let's do one thing we'll go one by one we'll write the code for one by one rather than going through the whole thing we'll go one by one. So the first constraint is not null. So see as I told you think of it constraints are nothing but the rules that you apply to the column. Now if I say that let's say I have a column id column and I want to apply some rules. So we learn about autoomicity right we learn about not autoomicity sorry we learn about asset. So if you remember we learn learn about consistency. So means that you're you are basically applying the rules to your database. So here also we are doing the same thing. We have got multiple columns in our U table and we simply want to apply some rules to those column. So let's say I've got the ID column and I want that whenever you're inserting any of the students record the ID shouldn't be null. You're getting the ID cannot be null. As simple as that. So I can use not null constraint for that. So you can see what it does. It disallows null value. So it means that the value should be provided. Yes. It means that you're making this column required. As simple as that. Yeah. If you enter any of the row, this column should have value. Okay. So we'll do one thing. We'll just give me a second. Yeah. So we are going to create a table using not null. Yeah, not null column. So let's start with not null. So I'm going to create a table. Now we are going to create a lot of tables. And let me name this table as my students. I'm just naming it anything that's fine. Always remember that what we are trying to learn. Yeah. Now this table may have my multiple columns. So one of the column is ID and the second column is let's say name. Okay. I'm not going to make it a very big table. So what I can do is I can make the columns. it's not not null. How I can make that column as not null? So all I have to do is I have to write not null in the front of that column. So it means that in order to add the data into this table, I have to make sure that the ID is not null. If I want, I can do it for name also. Let me do it for name also. Doesn't matter, right? So let me do it for name also. So now it means that in order to insert the data both the column should have some value. Let me create this table. The table has been created and I'll just quickly say insert into I want everyone to see my screen. If I give one, any name for example, let it be. I am just giving any name for that matter. Doesn't uh matter what name I'm giving. Yeah. So if I insert the data, see it is getting inserted. Let me also show you the data. Select staff from my students. So, it's working absolutely fine. Yeah, you can see. But now what I'm going to do is just give me a second. I will insert the data. But let's say that I I am skipping maybe a. Yeah, I'm not giving the student name. Yeah, I'm skipping A or let me give null in A. So let's see if it works or not. You can see it is not working. So it says that column name cannot be null. And this will happen for the other column also. For this column also sorry. So this also cannot be null. As simple as that. And the reason is very simple that I have made I have applied a constraint to this column both the columns that these columns cannot have null value. So this is what this is a not null constraint where we make sure that the column doesn't allow null values. So I'll wait for a minute for everyone to quickly try this. Again I do not want you guys to write the whole code. Maybe you can take it from the chat and you can modify the existing code what you have otherwise it will take a lot of time. Would you understand why unique distinct values? Yes. So in very simple words if I have a column so I will not allow I will not allow duplicate values in those columns. So any guesses what those columns could be like you might have seen in your real life here and there ID very good yes ID those who belongs who are from India maybe uh p card or passport number everybody would understand other number how about email yeah email should be unique phone number should be unique so you want to make sure that every record that is being entered has a unique email or unique unique phone number. So here we have created a table user. It has just got one column. It's okay. We are we are learning the concepts. So we don't need to create big tables with a lot of columns. So I'll just quickly run this code. Sorry, just give me a second. Yeah, I'll run this code. You can see that the table has been created. Now if I insert this a at the rategmail.com it will get inserted. This is the first time I'm inserting. But if I again insert maybe I can you know re-execute the same statement. So if I again insert it is giving error. Why the error? Because it's duplicate entry. But if I change the value of it then definitely it will allow me to execute. Yeah. So unique is a very important constraint that you're going to see you're going to encountered. So let me quickly give you this code on the chart. You can copy paste the code. Again do not type the code right now but yes you should definitely do a lot of typing of the code after the class. Right now we are copy pasting so that we learn maximum concepts. But yes, after the class you have to make sure that you do a lot of practice by typing each and every line of code. I'll wait for a maybe a minute. Now we'll learn about the next constraint that we have that is primary key. So let me open the notepad because I would like to uh write something around it or maybe I can write over here itself. It's fine. So see you can do the commenting like this. So when you write anything in the comments the interpreter is not going to read this. These lines are for us. The these lines are not for interpreter. So anyways we're going to learn about primary key. Now you already know about it. I we have already discussed it. So I'll quickly redisuss it. So primary key is a unique identifier of each row. For example, if I talk about human beings, so I think our genetics, our DNA, these are the unique identifiers. Yeah, that will or our fingerprints. These are what the unique identifier. So for every row also if you want maybe some column to uniquely identify that row. So we can declare that column as primary key. So I'll also write it. So basically if you say that a column is a primary column primary key column it means see that's what it means that it is uniquely identifying it right uniquely identifying the row of data. So definitely um every row that that column will take like every piece of data that column will take will be unique also it cannot be null. So it doesn't take nulls as simple as that it's a primary key it doesn't take nulls. So for example, we are storing the data of humans. Yeah. Of humans. Let's assume, right? We are storing the data of human humans. Now you know that names can be common. Then gender can be common. Most of the things can be common. But let's say uh thumbrint. So thumbrint is not common. So this we can make this as a primary key. So this will be unique for each and every human. Yeah. And we we also do not want it to be null. So if you're inserting any data, it it cannot contain null value. So that's what primary key says. Okay. There's one more thing that is in a table. This is very important. In a table only one primary key is allowed. only one primary key per table. It means that if again I'm storing the data of human beings. If I've said that thumb print is the column which is a primary key, I cannot have let's say passport number as a primary key. Now so a table can have only one primary key. It can have the combination of primary key composite primary key that we'll talk about. But it can have only one primary key. So those who may ask that composite primary key we'll come to that later but it can have only one primary key for now you understand this part that it can have only one primary key so I'll just quickly take the code yeah so you can see over here that I've got this table employee it has got two columns Employee ID name. Now employee ID is a primary key. I'm declaring it as a primary key. Let me create the table. Now I am inserting the values in it. Yep. So you can see that this is a first value one and Ravi. So employee ID. This one is what? ID. Yeah. Employee ID. So anyways, so this will work. Let's say that I've got another employee with a name Reena. And if I give the same employee ID to it, Reena, it will throw error. Why? Because it's a primary key. And primary key make sure that each and every thing is unique. Also, if I try to insert null in a primary key, again, it will give error. So that's what I wrote that it has to be unique and it cannot take null values. It cannot take null values. Now let me do one thing. I'll just make a small change over here. I'm just changing the name of the table and I just want to show you that if we can have multiple primary keys in a table or not. So let me execute this. You can see that it is giving error. It says multiple primary key defined. Always read the error. I'm not sure if my uh screen is visible to you or not, but yes, it says multiple key defined. So, it simply means that your primary key can only occur once in a table. You cannot have more than one primary key in the table. So, let me again yeah uh make it to the previous code. Change it back back to the previous code. I think we are good. So I'll share this code with you before you run it. Maybe let me also tell you one thing. So what is the difference between unique and primary key? See first of all I can have multiple unique columns. So I can have multiple unique columns. This is very much possible. I can have 10 unique columns. I can have more than one unique columns in one table. Primary key says that oh I can only exist once. Yeah, I cannot you can once you have created one once one column is given the primary key constraint you cannot have another column with the same constraint. You saw that you have already seen it but unique you can make as many columns unique as you want. Also if you have a unique column it can like one row can take null. So it's unique right? So one row I can give null. I have to make sure the next row I cannot give null because null will not be unique anymore. You got that? Let's say I've got this employee table. So for the first row I can say null and maybe I can give some uh maybe some value. So one row can be null. If I make the second row also null then it will not be unique here. Both both the rows are having the same value null. So it can take one row at least null. Primary key says no if there's a null I can't take it. It's as simple as that. So anyways I'm sharing this code with you all. I want all of you to try this. Play around with it. Copy paste it. Yes. So that's what I said sesh composite key when you have a composite primary key the primary key is one only. It's a combination of the primary key but the primary key is one right. It's a combination of the column that makes the primary key. So we we are going to look into composite also not right now. Make sure that you also write down these notes the these points. See I'm going to give you these notes but I'll give you these notes at the end of the class not right now on on a last class or second last class. So you'll have everything handy. Now passport number is a primary key. Now I've got another table. This another table is storing the passport number. Yeah, the different different passport numbers that we have. And for these different passport numbers, we also have maybe this is not a very good example. I'm so sorry. Let me uh give you some other example. Okay, let it be no I'll give you some other example. So I'll give you the example of orders only. I think that is a best fit. Now let's say that we have got a table customer. In this we have got the details of the customer like ID, name, phone number etc. So ID is what? This is a primary key. This is a primary key for this table. It uniquely identify a customer. Okay. Moving ahead, we have got the orders table. Orders table has got let's say order ID. So it has also got multiple columns. It has got order ID and let's say the date of the order. Uh maybe something else the product and then it has got the customer ID. Yeah. Who made the order? So this customer ID basically takes the values from this ID. So it means that over here I have got some data. Let's say I've got the data like I've got a customer 1 2 with name A and B. So this table now understand this thing. This table will have values um it have values from this ID column. So it can have let's say I've got maybe one another value. So it can have 1 2 3 only. It cannot have any other customer ID. It cannot have four as a customer ID because that doesn't exist over here. So this is what this is a foreign key. What is a foreign key? Foreign key is a key which is a primary key of the another table. So ID customer ID is a primary key of this table and we are using this as a foreign key in the orders table. So what is the significance of the foreign key? Foreign key make sureures that whatever values that you're filling in over here you're getting whatever values you're filling in over here should come from this column. Yeah it should always come from this column. So if I try to fill four over here it will give error. It says oh four we don't have any customer with the ID four but if I give 1 2 three values over here again and again orders made by one orders made by uh customer ID 2 it will happily take it but as soon as I give let's say five it will give error no I five is not here it says five is not here so foreign key think of foreign key as a lo it's very loyal to the primary key it says that I will take only those values that exist in the primary key of this table. Yeah. Of this primary key. This is what this is the primary column of this table. I will not take any other value. As simple as that. So it's super super loyal to the primary key and that's why we call it as a um foreign key. So uh this is a significance of having the foreign key that it only takes the values which are present in the primary key of the another table. Yeah, it cannot take any other value. Anyways, I will see the practical part of it and you'll be able to understand it's very easy. So here, okay, here I've got two tables. So I'll just quickly create these two tables. So you can see department and staff I've got these two tables. Department has got two columns and staff has got three columns. Now if you see over here which is a primary key in the department column quickly. Now I'm asking you such simple questions. Yeah. So Somia your question will be answered. If you see here we are defining a foreign key. Yep. So, department ID and here we are saying the department ID and this is how we do it. This is the syntax of it. So, we are saying that we have got a foreign key. This is a foreign key. This is a foreign key and how it is related to this table. We are also telling that yeah this is a foreign key and it is related to so references name of the table and the primary key of the table. You got that? So basically we have declared a normal column and then we are telling that this column is a foreign key. Yeah. Which this is a foreign key column. Then it's a foreign key column. It means there should be some primary key in some table. So we have to give that detail also that which is a table and which is a primary key. So how do we do that? We use references. Then we give the name of the table. This is what this is the name of the table and this is the name of the primary key. So it means it means that this foreign key is for this table and in this this column. Now we are going to insert the data guys. I want everyone to be very very attentive over here so that you understand how things are happening. See now I'm going to insert the data. So in department let's say I'm inserting only one row. Yeah that's fine. So I've got department ID as 10. I've got department ID as 10. So I've just got one department and this department name is it and the ID of the department is 10. Fine. Now I'm going to insert the data into the staff table. So see what I'm doing is I'm saying I've got one staff. So staff you know like for different departments we have got staffing. So I've got staff ID 1 and this staff goes in the IT department. Yeah. Department ID 10. So this will very well work. Yeah. Because 10 exist over here. you get getting 10 exist in the department table. So this value exist in the department table. This value exist in the department table in this column. But if I give 20, this will not work. Why this will not work? Because 20 doesn't exist. Yeah, here 20 doesn't exist. If I simply query select star from department, it just has got one u row of data. You just saw that we have inserted only one row of data. You can see over here. So it has just got one row of data. We'll make sure that department ID only have those values. Sorry, foreign key. Make sure the department ID this is a foreign key. it can only contain those values which are the part of the primary key of the department table. So that's how it works. So you can see that again I've got the student table. I think it will throw error because I already got the student table. So I'll just quickly make a change to the name of this table and that makes sense also because it's already there. It will throw error that this table already exist. Fine. So you can see that the table has been created now. the I've given a check over here that the age should always be greater than equal to 18. So if I insert the data which is greater than 18, it will happily take it as you can see. But if I try to insert any data which is less than 18. Yeah, if it is less than 18, it will throw error. So if you're seeing the error, as you can see on my screen, guys, if if you're getting the error, this is how the behavior should be. here. So you should definitely get the error. This is how we expecting it to work. So it shouldn't allow anything lesser than 18. As simple as that. If I give 18, then it will allow. See, now it will allow. It will work. It will not throw any error. But as soon as I give any number which is lesser than 18, it will not work. So I want everyone to quickly run this code. It's a very simple one. U Pushka you have installed MySQL software not unable to execute anything. So Pushka did you um followed the steps that I um so there was one video that I shared with you the link of the YouTube video did you follow all the steps religiously? So when you are installing my SQL it's not you just install and you know you just click next next next and done. So there's some setup that you need to do. So the installation is not that straightforward. It is not difficult also just that you have to follow some steps. So anyways I'll wait for a few seconds for everyone to quickly try this constraint check constraint. The next constraint that we have is default. So till now every column that we are we have created we we were inserting the data in it. Now let's say that if we do not get any data in it. Let's say we are not inserting any data in it and we want that column to take any default value. For example, let's say that we have got a table student table. Yeah. Now student table. Now this is uh student table of some school which is in India. So here we have got a country column. So if we are not giving the value explicitly for the country, it will automatically take India. As simple as that. So basically we are giving a default value to country column. So default value means that if I'm explicitly providing the value while inserting the data, if I provide USA then it will take USA. I provide India, it will anyways take India. But if I do not provide anything then by default it will take India. Again if you have not understood that's okay rest assured once we write the code around it you'll able to understand. So let me take the code. So I want everyone to see my screen. You can see that I've created the orders table. It has got one column that is status. This is the data type of it. And what is the default value of this column guys? Now I'm asking you such simple questions so that you remain in the session. I do not want you guys to uh you know daydream during the session. See I've just got one answer. I repeat my question. What is the default value of this column? I'm using the default constraint and I'm giving a default value. Yes, it's spending. Right. So I'll just quickly create this table. Now here I'm inserting into orders and values I'm literally not giving anything. So if I run this see it got successfully run. Let me quickly query this table. You can see that it has got one one piece of sorry one row and this one row because I executed it once it has taken status as pending. If you want you can give some other value also. Let's say I give a value closed. So it will happily take new values also. It's not that it will not take. Yeah, this ran successfully. If I run this, you can see first one it took pending because if you're not giving anything in very simple words, you're not giving anything, it will take the default value. But if you give anything that it will take that value. Now you might have seen like for example let's say that you go to Amazon and you create your profile over there. Yeah, you create your profile over there. Now, when you create your profile as a customer, definitely you do not give any ID. Usually, what you do is you give your name, address, phone number, email id and you're done and dusted. So, let's see Amazon wants to give you some ID. Yes, some autogenerated ID. So, we have got this auto increment. This is a wonderful constraint in order to um satisfy or meet this requirement. So auto increment as a name says that if I have got a column let's say the name of the column is ID and if I make it auto increment it means that automatically it will like the first record let's say take the value one. So the second record will take the value. Let's say I say that it should increment by one. So that is what I've given as a rule. So the second will take two. The third record will automatically take three. So the this column will automatically um generate the value for itself without user explicitly giving. Yeah. And it will increment increment the value every single time. So again we'll understand it with the help of the example so that you have better idea. Now you can see over here um now this is a great example why because here we are creating the table and see one column you're making it primary key also and you're also auto auto incrementing it. So that is very much possible that one column you're giving two rules in it. Yeah it happens right? So this one column has got two rules. First of all, it's a primary key also it's auto increment. So I need what you're seeing is right. So auto increment is that it will automatically increase the value will automatically increase. So I'll show it to you. So first I'll create the table. Then I'm inserting two two products in it. Now if you see when I'm inserting I'm just inserting the value in the product name column. I'm not inserting any value in the product ID column. I'm not inserting no because this column is self-sufficient to autogenerate the value for itself. So I'll quickly run this and let me show you the result. Sorry. Yeah. So you can see automatically it is taking one and two. Yeah. Now if I insert more data let's say if I insert um maybe keyboard it will take automatically it will take three. Let me show you. You can see so automatically it it is in uh getting incremented. So by default it takes one. The initial value is one and the increment value is also one. No, not at all. Now, you can use it for any key you can. So, what you can do is so guys, I want everyone to see my screen. Yeah, then I'll give you time again. So, I'm just making the changes uh to the name of the table because this table already exist and if I recreate the table with the same name, it will throw error. So, definitely I can remove this. Now product ID is auto incremented column but it's not the primary key. Now let's say yeah I'll come to that also. Let's say you want to increment it with five. So what you can do is over here this is the syntax of it. You can give auto increment equals to five. Okay. So auto increment has to be primary key. I'm so sorry for that. So it has to be primary key. I think SQL server we we can have uh a table auto increment column without having the primary key. So I got confused because different databases different rules but yes it has to be primary key. Now you can see that I'm saying that auto increment by five. Let me in let me quickly insert these products. Just give me a second. Okay sorry I have to insert the product. So I'm also changing the name of the because I've given auto increment equals to five. The first value it is taking five and then uh 6 7. This is how we are defining the first initial value and you can also change this uh increment value also but we'll not cover that right now when we alter the table that time I'll tell you. So we have got something called set and then we have got at the rate auto increment. So there's uh there's a property that we can define when we alter the table. Yeah, Rajpal. If you delete the second row, the next increment will be the next one. It will not be the previous one. Yeah, it will always take the next one. So, Abijit Abin Abinit as I told you that uh we have a property. So, I would u maybe I would like to talk about that property when we learn about how we can alter the tables. Not right now. So, this was a very simple property. So I just told you right away as I told you uh right now it was asking for the primary key. I need to check because in SQL server you can have an auto increment column without having it to be the primary key. But here in my SQL when I was trying to create it with it was asking for the it to be a primary key. I'll just check it if we can auto increment a column without having it to be primary key. In fact in my SQL auto increment feature wasn't there. Till some time back we had it in SQL server databases but not in MySQL. Now we learn about composite primary key. So what is the meaning of composite? What is the meaning of composite? No, it will not throw error. You might have said that there might be something u off with your with your syntax. And maybe I'll just wait for another 1 minute and then we'll move to the composite primary key. I do not want your questions to be unanswered and you're still trying and you know uh hanging around this part. I do not want that. So we'll wait for a minute and then we'll move to the composite. Now we'll learn about composite primary keys. So what is a composite key? So here what happens is if I give you this example just give me a second let me see the example that we have got. So let's say you've got a table. Yeah you've got a table. Now in that table it is not possible to define one column as a primary key. You getting? It is not possible to define one column as a primary key. Let's say suppose that I've got a table. I've got a table and the name of the table is let's say country. Yeah. Now here I'm having the name of the country and then I've got more details about the country let's say the latitude longitude all of these things and then the code country code also I've got now I want to define a primary key I want to define I've got this table and I want to define a primary key well latitude longitude can also be a primary key because every country will have distinct latitude longitude let's talk about something else let's um number of states. Yeah, something else or number of cities. So some some details about the country. Now we have to decide on the primary key. Now I may want make see number of cities and number of state cannot be the primary key for sure. So what I can do is now country code can be the primary key of course but let's say that uh it's a hypothetical thing that countries they can have sometimes similar code also yeah they can have the same code also so what I can do is now name also I think there is one country I don't know sharing the name something like that now countries may share some sometimes names also they can have let's let's assume that that's hypothetical I know. So what I can do is now I want to define a primary key. So what I can do is I can make the combination of two column as a primary key. So I can say my primary key is name as well as country code. So combination of th these two are my primary key. Composite primary key. you're getting the combination of two columns or it can be more than two columns are what u is as is a primary key. So I'll give you one more example. So let's say let's say I've got um okay so let's say I've got orders table I've got order ID I've got let's say product ID and let's say I've got quantity and then I've got name customer name etc. I've got these columns. So I have to define a primary key. Now the order ID is also repeated. Product ID definitely will be repeated. It's a orders table. Quantity definitely will be repeated. So I am in in a soup. I am in a fix that from where should I get a column which is completely unique. Now I don't have any column which is completely unique. Let's say order ID is also getting repeated. So um order ID is actually repeated sometimes. So let's say I make an order. I'm just giving you one context of it. And in that order I am purchasing three products. You're getting so I'll have the order ID repeated. So 1 2 3 1 2 3 1 2 3 product 1 2 and 3. So that is again a problem. Now I have to define a primary key. What should I do? So what I can do is I can use a combination of the column. So let's say I make these three column combine as my primary key. So what does that mean? It means now hear me out. It means if my order ID is 1 2 3, product ID is one and let's say quantity is maybe 1 one one let it be. Yep. One. So the combination of these three should be unique. So if you see this is I'll just take the second one. I'll take the third one. Now if you see the combination of these three is unique. It is always giving me a unique value. Yeah. If I go with one more, let me give you one more example. Let's say the order ID is 1 2 4 product is one one and the the quantity is different. So the combination of these values should be unique. You see the combination is unique. So that's what composite primary key does. It's same as that of the primary key. No difference. It's just that the primary key is made up of more than one column. Yeah, it is made up of more than one column. So here you can see I've got the enrollment table. I've got a primary key and the primary key is a composite primary key. Why? Because here the primary key is made up of two columns. Now I want everyone to see my U screen. So I'm inserting the data in it. One 1.1. So one and 1.1 together will make the primary key. It will very well work. Now again see one is repeated but course ID is different. Yeah. So 1 1 2 again it will work for me because the combination of two is unique. Always remember that the combination of is unique. Here also the combination is unique. See if I run it, it will run. But let me do one thing. Let me again run insert and let's say the product or the student ID is two and the course ID is also 1.1 you're getting. So the combination is no more unique. It has already been used. It has already been inserted in the table. So if I run this, it will throw error that it's a duplicate key because this combination is not unique anymore. Yeah, it's not unique anymore. So composite key is exactly same as that of the that of the primary key. There's no difference. The only thing is composite key says that I am again a primary key but I'm a combination of multiple columns. In order to be a primary key, I'm a combination of multiple columns. So I want you guys to run this code. The last insert statement will not run. Yeah, it will throw error. You've been using select statement. So it's not a new thing. But we'll see that what all we can do with the select statement. So we start with the keyword select. Then we we were giving star from the name of the table right. So star basically means that return all the columns. So star means all the columns of the table. Now let's say that you do not want to return you do not want to see all the columns rather you want to see some limited columns. So what you can do is you can say select and then you can give the names of the column that you want to be part of the output. So you can give the name of the column again from and then table. Now what we are going to do is we are going to create uh a table and we are going to add some data into that table. So let's let's see uh I'm not sure if we have got the students table or not. So let me uh quickly drop the table. Drop table and then the name of the table. So the name of the table is student. Yeah, if it is there. Yeah. So I think this table is not there. As you can see it's giving error. So if you guys already have this table, please make sure that you drop it. So this is one of the way to drop it. Other way is that you can go over here and you can right click on the table and you can say drop table. So we are going to recreate in case we so we have created students table. There is s added to it. So now we are going to create the student table. So if you already have the student table please go ahead and drop it. And once it is done I want all of you to recreate the table. and insert this data into the table. So we have got the table. Just give me a second. I think it did not get executed. Yes. So I've got this student table and I've got this much of data almost six rows of data in the student table. So I'll wait for a minute for everyone to have their student table ready. Now I'm just opening another SQL file. So I've got this data also in front of me. H the table the table structure the data that I have inserted into the table. Yeah. So as this is something that we have run so many times. So I say select star from student. So this will return all the columns and all the rows that we have got in the table. So you can see all the five columns. We have got five columns and we have got six rows of data. So this is returning everything everything. Now if I want to let's say only return some selected columns. So what I can do is I can say let's say select name, u marks from student. So I can definitely do it. I can give the name of the columns that I want to be the part of my input output. So if I run this, you can see now it is returning only two columns. So I want everyone to try try this out. So I'm not going to provide you the code for the select statements. It's a small it's a short code. So you can quickly type it. So let me again query this. I'm removing this this one. Okay. Or let me do one thing. Let me also share the code with you now. Hoping that you have already typed it. You can just type one and two columns. And definitely I want you guys to play around with a lot of code, a lot of uh SQL queries after the class. Okay. So here you can see that I've got students from different different cities. So let's say I want to know that what are the distinct cities that I've got. So I just want to know that I've got the enrollments from virtual cities. So I can make use of distinct keyword and then I can say I want distinct city. Right? So this will give me all the distinct cities that I've got in my data. So I want everyone to try try this out. So see guys when you are doing the select statement you're simply reading the data. You're not manipulating the data. You're not making any changes to the data. You've got the table and you're simply reading the data and while reading the data, you are applying some rules. You're applying some maths. As simple as that. So, it's like you're filtering your data while reading the data. Fine. Now, as I told you that when you're reading the data, of course, you can read the data directly like this. Of course, you can do that. Yeah, we have done it also. But let's say while reading the data now I do not have any let me again okay I do not have any percentage column over here but let's say that fine I've got the marks but I also want to know the percentage of the students. Yep. So I am not going to make any changes to the original data rather while reading the data I'm going to apply some maths. So what I can do is over here I can say maybe okay I'll just put like this and I'll say marks into 100 well you know how it is because let's say the marks are from not okay let's say the marks are um okay let it be out of 100 so this will return me now I want everyone to see my screen this will return me three columns now This column is not the part of the table. Rather I'm creating this column in the select clause itself. In the select statement itself, I'm creating this column. So I'm not creating this column in the table. No, while reading the data, while reading the data itself, I want to see something. Yeah. So I'm just creating this column. So let me run this. So see this column is there now over here as the output as the output. So this column is there as a output but originally this column is not added to the table. So this column is not the part of the table. While I'm reading the data from the table I'm I'm doing some maths. So you can try try this out now if you want. See this is what this is the name of the new column. And I'm literally not liking this name. So you can give an alias to your column. So I'll just give the alias percentage. So how you can give the alias like this. You can give alias to the existing columns also. So I I can say as for example over here. Let's say as full name. So if I run this see this. So the original name of the column is name. But while reading the data from the table, I've just given an alias to this column. So I can do it. But whatever I'm doing over here in the select statement is only for the output. It is not making any changes to the original or the underlying table. Yes, we can definitely do it to do two decimal point Rajput. So we have got a lot of functions. We are going to learn about those functions. So not right now but yes we have got a round function. So you can uh give uh you can make it only for two decimal points. So we have got a lot of functions and definitely we are going to cover all those functions you can see. Yep. But it's not something that I would like to maybe talk about more. Now till now what is happening is it is returning all the rows. Yeah, it is returning all the six rows that we have. Now what I want is I want to filter the data. Right now we just learned about that how you can return all the columns. How you can return limited columns. How you can do little bit with the columns. How you can do little with maths with little bit functioning on the columns. This we are going to cover more. But now what I want to do is I want to filter the data that is returned as a part of the output. I want to filter the data. So how I can filter the data. So I'm just making the changes to the same. Right now it is showing me all the students. All the students. Let me also say let's say city also. Yeah. Every all I've got six students. So it is showing me all the students. But I do not want all the students. Rather let's say there's a requirement and the requirement is I want to see data of only those students that are from city Pune. Yes, students that belongs to Pune city. So I can use where clause let me see if I've got anything on the PPD for the wear clause. Syntax still here and then you use the where clause. So where and then you give condition over here. Okay, you can give a boolean condition over here. not necessary boolean condition. You can give any condition for that matter. So basically you can give any condition but for any row. So anyways we'll not talk about the condition right now but yes so this will this will help us to filter. So we have to give the condition which actually gives a boolean answer. But I'll I'll talk about that later. Right now let's first look into some of the example. So here I want the students who belongs to the city Pune. So see this will help me to filter the data that is returned by my query. So where clause is very important. Again I'll give you one more example then I'll give you time to uh practice it. So let's say I want all the students who who have got distinction marks. So marks greater than equal to 75. So this will return me all the students with greater than equals to 75. I want everyone to try this out both the both the queries. So in where clause you can add different different operators. For example, for example, let's say I can add the operator where marks plus 10. Let's say yeah that doesn't make sense but you can see over here I'm just adding arithmetic operator that is plus. So I can say that where marks + 10 is greater than 75%. So I can add the different different operators. I can add the arithmetic operator. I can add the comparison operator. Comparison operator I've already done it. Yeah. The previous query that we wrote. H this is what comparison operator where you're comparing you have marks greater than equal to 75. So you're comparing the two values right? That's what comparison less than greater than greater than equal to less than equal to equals to so these are what these are compar comparison operator in the same way I'll just give you some examples of the different different operators then you can give it a try in the same way I can also have logical operators so logical operators let me maybe take take this query so I can say that I want all the students who belongs to city Pune 8. Yeah. And so here I'm adding a logical operator and marks greater than 75. So here and is what? And is a logical operator. We have got three logical operators. I hope that you guys know about it. I think I've already asked you and everybody told me that you know about the logical operators. So we have got three logical operators. I'll go slow and or and the last one is not. So here it is going to return us it is going to return us the students who belongs to Pune as well as the marks are greater than 75. So this will return us Amit only Amit you can see. But if I put or it means that it will return me the students who belongs to Pune. So it will return me first of all it will return me Amit and Priya. Also it will return me all those students whose marks are greater than 75%. So whether they belong to Pune or not doesn't matter. So either like I so here students who belongs to Pune also marks greater than 75%. So if I run this you can see that I'm getting the students from Pune as well as students with marks greater than 75%. So this is what or in the same way we have got not also. Yeah. So let's say I'll give you the example for not as well. So let me add another query so that you guys have it like I'll just give it to you. So I'll I'll say that okay I want all those students who do not belong to Pune city. So I'll just say where not city. So I want the students who are not from Pune. So here are the logical operators all the three logical operators. Then we have got more operators but I would like to take two minutes of halt over here and I'm just sharing this queries with you all. So what you can say Rajpal see a student will belong to either Pune or Mumbai right? So you can say city equals to Pune or city equals to Mumbai. So here you can give another condition city equals to Mumbai. So I'll take a hold for two uh 2 minutes. You can just copy paste these queries yet do not write the query. Play around with the query and then we'll look into the more operators that we have got. Yes. So we have got range and set operator very very important operator. So as a name says that let's say that uh you want to know or you you want to know about the students whose marks were in the range of 60 and 90. Yeah between 60 and 90. So you can use the range operator. So I'll just delete this so that I give get enough space. Yeah. So I'll say select so and so from student where? Let me remove this marks between. So I I can use between yeah between let's say 60 and 80. So this will return me all the students with the range of mark 60 and 80. So 60 is included, 80 is not included. So what I mean to say is right now you can see that I've got three students Niha, Priya and Anita. And these are the marks. So if I give 78. So oh 78 is also included. Let me give 70 65 also. In Python actually the last range is not included. So that's always a case of confusion for me. So yeah you can see that both the ranges are included 65 as well as 78. So it will give you if you have any range you can use between operator. You can use between operator. Now Rajal had a question. What if I I want to return the students who belongs to Pune as well as Mumbai. So what I can do is over here let me put star let it be. Yeah I I let's say I got all the columns from student where city and I'm going to use in operator. Yeah, in operator and here I can give multiple values. So let's say I'll give the value Pune, I'll give the value Mumbai. If I want I can give more values. Let's say I want to give Delhi etc. So these are what these are the range operators or set operators. So this is a range operator and n is a set operator. It's a set operator. We have got match pattern matching operators also pattern matching is like see sometimes it's not that we have the exact value let's say I do not have like city equals to pune rather I say all the cities that starts with P or all the cities that ends with E something like that so I have got some pattern to match so I'll I'll quickly write. So I select staff from student. Okay. Let's match the pattern for name. Let's match the pattern from name. So let's say that I want all the students where the name like name like so anything that starts with a. So here percentage means hear me out. Percentage means any number of characters and any characters. So this will show me all the students where the name starts from A. Yeah, name starts from A. So let's say I I'll just maybe give it a twist. So I just want a letter or let's say I want just I just want T letter between their name. So I just want that the student with the letter T. So now if I run this. Okay, again we have just got okay let me say R. I'm not sure if we have got any students. Yeah. So I'm just saying that I'm okay with any characters zero or more than one character more than zero character on the left side. I'm okay with zero or more than zero character on the right side. I do not care about that. All I care about that the name should have R. So now it is returning me all the students having R in it. Let me try some more permutations and combinations. So guys you guys you also have to play like this. Okay. So I'll say that um Okay. So I'll say that I want only one character. Yeah. only one. So here hyphen means only one character. It can be any character but only one character. Percentage means any number of character. Hear me out. Percentage means any number of character and any character. But when I give hyphen it means it can have only one character before R. You're getting before R. It can have only one character. So P R why P is only one character before R. And then any number of character after P R. So this will return me this is returning me priya. So this is what this is pattern matching. So I'm doing the pattern matching using the modulus or you can say percentage and using underscore not hyphen sorry underscore. So I repeat percentage simply means any number of characters and any character. This means one character but any character. Yeah, I can also give something like this. Let's say okay, I'll give a. Okay. Anything before a and any two characters after a. Any two characters after a I'm not sure if it will. So I don't have any any such student. But if let's say I've got a student I'm just giving any random name. Let's say I've got the student sit a RB. Yeah. Suppose I've got a student like this. So this will return this query will return this. So I've got two underscore it means any two characters but it has to be two in number not more than that. Percentage is like any number. Yeah one underscore means one character one character two underscore. So this one and this one. So that's how it is working. So am I clear with the pattern matching? It's a very simple one. Yeah. You're simply matching the pattern using percentage and underscore. Am I clear? I've just got one. Yes. Fine. So, we'll look into more operators then I'll give you time. So, we have got the last operator, null operator. So let's say you simply want to u return the rows where maybe the column is not null. So the column is not null. So I can simply say where name Yep. So I can say where name is not null. So this will return me all the rows where name is not null or I can say where the name is null. Let's say I want to see that if I've got any column with a no name. So I don't have any such column. So that's why it is not returning anything. So it's a very simple one here where the name is not null or name is null. You can check it for anything. Let's say city is null. Again I've got the full data. So it will not return me anything. Not null. So all the all the rows where the city is not null. All the rows where the city is not null. Okay. So we are done with the operators in the v clause. I'll take a hold for 2 minutes. So you can just copy paste and play around with it. Okay. So you can take more times with that. So I'm assuming that you guys are going to practice after after the class also but fine I'll give you more time. Rajpal see where clause has nothing to do with the primary key let's say that your name is a primary key so it doesn't matter right you are just going to filter with name name equals to something so it doesn't matter like if it's a primary key or non- primary key you're simply filtering your data that's what you're doing if you have got composite primary key how does that matter where doesn't care about primary key or composite primary It says that you want to filter the data and filter the data for you. So let's say you have got name and marks as a composite primary key. Now let's say you want to filter with these two. So you can just say and marks equals to some type. So you're getting it has nothing to do with the primary key or composite primary key. You can use any column over here in order to filter the readout. Now let's say and you're going to use it a lot. Yeah. When you start working, let's say you just want to see the structure of your data. Yeah. So in let's say your table is having thousand rows. So you do not want all the thousand rows. You do not want to see only thous all the thousand rows. Rather you just want to see the top 20 rows in order to see the structure of the data. So I'll just delete this. So here what you can do is let me put back because I maybe I'll show you some some some other thing as well. So I'll say limit two. So if I run this query, this will just give me the two records. Yeah, it will give me the two records. So you can give any number over here. Row number. No, no, in MySQL we cannot do that. I need to check. I've never used it in my SQL. So it's very simple. It will return the number of data. It will return the number of rows. Just try it out. And you can also add clause in it. It's not that you cannot add clause. So I can also say let's say we have marks greater than let's say 75 and then I can limit my data. So I can also add clause. So first it will filter the records with marks greater than 75 and then it will simply give us the top two records the top two rows. Anyone who has not understood what limit is a very simple clause that you're going to use a lot in order to understand the data. Okay. Now we going to learn about the next clause that is order by. I think this is going to be the last clause for today. So as a name says just give me a second as a name says that order by so you're ordering something. So it is to sort the result. Yeah. Now you're looking into the result with select query. You're basically querying the data that your table is holding. So while um maybe when you're seeing the result you want the result to be sorted. Yeah, you want the result to be sorted via some column. So sorting can be definitely ascending. So here I'll do the u sorting of the data. So you can see over here this is returning me all the students. Now I want to see all the students but I want to sort my students as per their marks. So what I can do is I can say order by the order by marks. Let me execute both the queries. Yep. So you can see this is the result of first query and this is the result of second query. Let me highlight it. Result of first query and the result of second query. Fine. So over here if you see there's no sorting. Yeah you Amit got 75, Niha got 72, Rahul got 90. So the data is not sorted. But for the second query the data is sorted order by marks. So you can see that Surish has got minimum marks. Priya has got uh like you know from what do we call that from from the lower she's second minimum and yes when and so forth Rahul has got the maximum marks so it is sorting the data by marks but by default it is sorting in the ascending order and if you want to sort it in a descending order all you have to do is you have to explicitly give dc. So it means that I'm going to sort the data in the descending order. Now Rahul uh is being seen first because Rahul has got the maximum marks. So descending means highest to lowest. Yeah, it starts with the highest and it moves to the lowest. Ascending means from the lowest to highest. So you can also sort in the descending order. But then you have to explicitly give. I'll give you more examples and then I'll give you time. But let me do one thing. Let me give you time right right away. So you can just try this code. So that I'm going to cover a project in the last class and mostly we are going to have an extra class. So instead of seven we going to have eight class mostly and I'm going to cover the project in the last class. Done everyone. So you can also sort the data u on two columns. Right now the data is sorted only on marks. Right? So let's say I also want to sort the data on two columns. I also want to sort the data on marks. So marks I want to be the uh the first column. And let's say I want to sort the data on city as well. So see I'm sorting the data. Okay, I'll just do one thing. I'll make it city first because that will make more sense. So I'm not giving any order over here. So city will be sorted in ascending order. Yeah, alphabetically it will it will be sorted in ascending order. So first the data will be sorted in city and then in that city like you know in Pune, let's say I've got three students. So first the data will be sorted as per the city. Then it will move one step forward and inside the city the data will be sorted inside uh each city the data will be sorted as per the marks. So let me show you the result. Yeah. Now you can see I want everyone to see my screen and be attentive. It's a very simple thing. Now you can see that the data is sorted as per the city in ascending order. Chennai, Delhi, Mumbai, Mumbai, Pune. Yeah. Now the second thing, the second question comes that okay, we have got two students for Pune and two students for Mumbai. So let's talk about Mumbai. Yeah, we have got two students for Mumbai. So whom should I show first, Anita or Niha? Yep. So we have got a second level of sorting. So that is marks descending. It means in Mumbai the students with the maximum marks will be shown first. So Anita is shown first because Anita has secured more marks than Niha and then Niha is shown. So this is how it is going to display. So you got that here we are doing the sorting two level sorting. The first sorting is on the city. By default it is doing ascending because we have not given anything. You can always give something over here. Now once the data is sorted by a city. So if I talk about Mumbai, I'm repeating the same thing. If I talk about Mumbai, the question is that we have got two rows. So which will be the first row and which will be the second row. So it will go to the next level of sorting that is marks descending. So it will check for the maximum marks. Anita has got more than nha. So that's why we are seeing Anita first and then nha. So in this way you can do the sorting on multiple columns more than one column also. Am I clear? Now if you want let's say you can also make city descending. So that is also very much possible. So it will show you Pune first. Now yeah Pune Mumbai Delhi Chennai and then inside Pune Ha has secured more marks that will be shown first. So if you do it vice versa that will not make sense. So first it will sort via marks. Yeah. And then you're sorting by city. So that will not make sense. So first it is sorting via marks you're getting and then it is sorting via city. So it doesn't make sense. So it should be logical also if you're doing something maybe syntactically it is right. But that doesn't logically it doesn't make sense to us as well as to SQL. So I want everyone to try this out. Try the sorting by multiple columns. It's an easy one, right? Done. You can add more clauses over here. You can add limit also. Let's say after sorting I just want to see three records. So you can have multiple clauses. So it's not that one SQL query will have only one clause. It can have the combination of multiple clauses. So the first thing first we are going to create the database and then we are going to use that database for today's session. So you can just quickly create a database with the name of your choice. You can give any name to your database and please make sure that you also use this database. So I'm going to execute these two commands that you see on my screen. And now once this is executed, I'll give you time. So just hold on. So you have to create the student tables. Now this table is same as that of the yesterday table. So if you already have it, no need to create it. You can use the same one, the previous database. That's your choice how you want to go ahead with it. So I prefer to create the new databases and new tables like in every session because if you're using lab provided to you by simply learn then definitely you'll you have to create the new database because everything is reset in I think every 5 hours. So anyways I'm creating the table also I'm creating I'm inserting the data into the table. So I'm done with all the configuration. Maybe I'll wait for a minute for everyone to finish up the configuration. I'm sharing all the code on the chart. So you can just copy paste it. Yes, you can just you can use the same database with Yeah, I'm very much okay with it. Yeah, that was a different table. That table we created in order to learn about auto number constraint vidya. But this is uh if you want you can drop your existing table and you can recreate this table to avoid any confusion here we do not have any auto number. If you see done everyone now yesterday we learned about select like we started with the select statement just give me a second and then we learn about where we learn about uh various things in select we learn about order by clause then we learn about limit clause so uh though this slide might look not very important to you but this is very important because when you start writing complex queries is you should kn