Excel & SQL for Business Analytics — Full Breakdown & Transcript

[00:08] channel. In this course, AI powered business analytics with Excel. We'll be learning how to use Excel to clean data, organize information, apply formulas, and turn raw data into useful business insights. Because in business analytics,

[00:21] data is everywhere. But raw data is often messy. Dates may be incorrect, symbols may be unwanted, formats may not match, and important values may be hidden inside large tables. And that is where Excel becomes a powerful analytics

[00:34] tool. In this course, we'll start with the basics of Excel navigation, shortcuts, referencing. You'll be learning how to move faster using shortcuts and how relative, absolute, and mixed cell references work. Then

[00:46] we'll move into data preparation and cleaning where we will learn how to standardize dates, remove unwanted characters, clean messy values, and prepare data for analysis. And after that, we'll be exploring text extraction

[00:58] formulas, random indexes, ways to structure data more effectively. We'll also understand lookup functions like VLOOKUP and advanced lookup methods that large data sets. We'll also learn

[01:10] logical functions like if statements along with conditional formulas like count if, sum if, average if to calculate values based on specific course, you'll be understanding how to work with Excel more confidently, clean

[01:23] data faster, use formulas correctly, and apply Excel for real world task. So let's get started with AI powered business analytics in Excel and learn you're interested in building a career in business analysis, I highly recommend

[01:37] you checking out this AI powered business analyst program by Simple. Now, learn modern business analysis skills along with AI powered tools that are used in real companies today. You'll be learning important topics like planning,

[01:51] requirement gathering, stakeholder management, solutions evaluation, agile, product thinking, RPA, process mining, business automation. You'll also get hands-on experience with tools like Excel, SQL, PowerBI, Tableau, Copilot,

[02:03] Jira, Miro, and many more. The program includes live online training, real world projects, case based activities, mentor support, IBA, Babok, V3 align, CBA training. So, if you want to become job ready and grow as a modern business

[02:16] course link in the description and start started, here's a quick quiz question for you. Which Excel feature helps keep a cell fixed while copying a formula? Relative reference, absolute reference,

[02:30] spell check or page layout? Let me know your answers in the comment section your answers in the comment section below. So, database is nothing but it is below. So, database is nothing but it is a structure which can store the data and

[02:42] 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

[02:57] 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. Right? You can save your files in it. So here also we

[03:11] create the database in order to store the data. in order to store the data. 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

[03:25] 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

[03:39] first of all uh you're getting a better storage keep all my jackets over here I'm going to keep all my shirts over here and so to keep all my shirts over here and so on and so forth so I get to organize my

[03:52] 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. Yeah, it will be very difficult for me to find it, right? But if I'm

[04:09] 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 easy. It will be very easy for me to access

[04:25] 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

[04:39] help of wardrobe analogy right. So you have everything uh with the help of have everything uh with the help of wardrobe all your clothes are organized. wardrobe all your clothes are organized. Yeah. Here also you've got database.

[04:51] 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

[05:05] 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

[05:17] 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

[05:31] 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

[05:47] data that how fast it must be increasing 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

[06:02] 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

[06:20] 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

[06:34] 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.

[06:49] Okay. There's one more thing that I would like to show screen. You'll get to know that u how your co- participants are. For example, I actively work with SQL. So we have got only two who are actively working on SQL. No experience

[07:03] with SQL. We have almost 53. So we are going to learn from very very basic and are starting from what is data. So that's one of the very basic thing. don't use it regularly. So we have got

[07:18] 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

[07:32] 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 you're going to forget most of the things what have been taught in the

[07:45] 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

[07:58] 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

[08:10] 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

[08:26] example here you'll have all the science books here you'll have let's say all the 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

[08:39] arrangements. So why do we have such arrangements? What do you think? Why? What is the reason behind such arrangements?

[08:54] 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

[09:09] 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

[09:24] database stores the data. Library stores book. Now just like how the library is book. Now just like how the library is properly

[09:36] 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

[09:50] 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

[10:02] 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

[10:15] 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.

[10:31] 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 automacity, any idea what automacity could be? So I

[10:44] do not want you guys to read the slides. I want you guys to answer with whatever I want you guys to answer with whatever comes to your from basic minimum aheads. Okay [gasps] fine. So we'll talk about atomicity. So

[11:00] I'll give you an example and we'll understand it with the example 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

[11:14] 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

[11:28] got a lot more other things. So you might have heard about apart from database. You might have heard about data You might have heard about data warehouse.

[11:43] 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 we have got multiple things in order to store the data. So why what is the use

[11:56] 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

[12:09] 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

[12:25] 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

[12:38] 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

[12:52] transactions that I made in last 7 days and so on and so forth. So to save this and so on and so forth. So to save this data now see my current balance then everything related to me um my profile then my transactions whatever

[13:07] 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

[13:22] data Yeah, whenever there's a day-to-day transaction data, it goes into the databases.

[13:37] 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

[13:49] transaction? because because it provides a 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

[14:02] 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

[14:16] 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 from the database. Yeah this might be coming from the database. So the

[14:32] database might be storing all of these things. So for day-to-day transaction we 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

[14:46] talk about atomicity and you'll get to know that why and you'll get to know that why asset is so important.

[15:03] 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

[15:16] 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

[15:30] 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

[15:43] 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

[15:55] 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

[16:10] 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 Yeah. And hear me out. Yeah. The money got debited from your account but

[16:23] your friend did not get the credit. Might have happened, right? So what will Might have happened, right? So what will happen in your account? you see 40,000 but in your friend's account still it is still reflecting zero so this is a very

[16:39] 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 carees and you know breaking our head that we I

[16:53] transferred did not work whatever it is it's definitely not some not something 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

[17:05] money is debited from your account and there was some problem while you were making the transaction, it gots recredited. You get the message that you have got your money back.

[17:17] So behind the scene, what happened is because there's a database that is because there's a database that is working. Database says that either all working. Database says that either all or none. Yeah, it says that either all

[17:31] 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

[17:44] 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 error in between, there's some problem in between, then the whole

[17:58] 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 is a error while you making some transfer and if there's some

[18:11] error you you'll get 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 the property of the database that either the whole transaction is

[18:24] 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.

[18:38] 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,

[18:51] should be nothing half in between, right? So doity is very very important. consistency. So everybody knows the meaning of Yep. So here it is written but still I write it. So again I'll explain you with

[19:06] 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.

[19:19] 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

[19:31] 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

[19:47] 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? transaction of 10,000. So how much amount will be left?

[20:02] Minimum balance should be 10,000. So here consistency says rules 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

[20:16] 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, Google pay that there's a limit of 1 lak rupees for every day. So

[20:31] 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 one lakh rupees in from your Google pay it gives you error it says big no that you're not allowed.

[20:45] 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 I'll make sure that the rules are being followed the rules are not broken.

[21:00] 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 each other. So again I'm I'm going to explain it to you with the example.

[21:19] 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.

[21:39] Now what is done is let's say you we make a first transaction that is 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.

[21:51] So the new balance would be what? The new balance new balance will be again 5,000. it can be anything. It's not only about credit and debiting. So the second

[22:07] transaction says that you know it sees it wants to see that you know it sees it wants to see the data. Yeah. It want to see like how let's say that you are taking the money out from the ATM and at the same time

[22:23] your parents are checking the balance. Yeah. So they are checking the balance.

[22:36] So they end up they may end up seeing they may end up seeing maybe 5,000. So let's suppose they end up saying 5,000. Yeah. So your mom is checking and

[22:49] 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

[23:05] 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

[23:18] mom is seeing 5,000. So that's a problem you're getting. have 5,000 because your your your transaction failed. So it so everything will roll back and you you actually you have 10,000 rupees only. You don't have

[23:33] 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 is isolation says that we are going to we are going to do the

[23:49] we are going to we are going to do the things in isolation. Yeah. So it means 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

[24:03] 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

[24:15] 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 5,000 at least. So it either it will show 10 10,000 or it will wait for some

[24:27] 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

[24:40] 5,000 to your mom but what happened the transaction got failed but your mom sees 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

[24:56] 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

[25:09] 5,000 once the transaction T1 is successful. Yeah. Means transaction two will not see the uncommitted deduction. As simple as

[25:24] that. And so am I clear with isolation?

[25:36] Simple. Everything is something that we get to see in our day-to-day life. Yes, Rajpar. What you're saying is right.

[25:49] Now what is durability? This is a very simple one.

[26:01] Durable simply means that the data is permanent. transaction is successful, see 5,000 deducted from your account and credited

[26:15] 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

[26:27] if the system crashes the data will be still saved. So once so it simply means once committed it stays forever

[26:41] and this makes sure that there's no data loss. So I'll just quickly recap. Atomicity says no partial updates

[26:56] either should be full or should be none. Consist consistency says no rules Consist consistency says no rules breaking. Isolation says no transaction

[27:18] 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

[27:31] 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 not write the whole thing. Transactional processing.

[27:49] transaction. Amazon your banks. So these are what day-to-day transactions. So all the companies for their OLTP they use databases. So their OLTP they use databases. So databases are very important.

[28:11] 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

[28:25] 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

[28:38] give you the example of Google pay also right. 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

[28:53] 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.

[29:13] 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've got 10,000 rupees. Yep.

[29:27] 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, with the same example. At the same time, your spouse looks into the account

[29:41] your spouse looks into the account balance and sees 5,000 rupees. happened? Something happened and your transaction got failed and everything [clears throat] got rolled back. So basically you have got 10,000

[29:57] But your spouse saw 5,000. So your spouse was able to see 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

[30:10] 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

[30:24] 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 going to top the class so it's like too soon to say right so you have to take

[30:36] 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.

[30:52] 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

[31:07] five 5,000. So that's a bad data. So either you have to either you should either you have to either you should show 10,000 or you should show 5,000 after the successful transaction. Am I clear now?

[31:44] 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.

[31:59] 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.

[32:12] a place where you store your data. Yeah. Where you store your data. centralized distributed in our curriculum we have got relational database SQL works with relational database only. So our focus would be on

[32:27] 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.

[32:45] 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?

[32:57] Tables as columns. Yeah. It has columns. Let's say student ID, student name, student number, all of ID, student name, student number, all of these. Yep. And then it has got data.

[33:15] So this is what this is relational database. 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?

[33:29] 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

[33:42] 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.

[33:57] you. Just give me a second.

[34:15] are tables with rows with columns and rows. So it's a relational database. So 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

[34:32] and this is table three. So relational database says that there's a 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

[34:46] example, so this table is storing the data of the orders. Order date, order ID, customer ID, product ID, quantity.

[34:58] 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

[35:13] the information of the customer in another table. So for example 1 2 3 another table. So for example 1 2 3 ID is of anul and he is male.

[35:25] We can have more information number birthday 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 all the data and then we have the

[35:38] relationship between the table. So you can see that this table and this table 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

[35:51] that two things are related to each other. Let's say that you are related to other. Let's say that you are related to your uh sibling. So there's something you're related to each other because of the family. Yeah. In the same way, let's

[36:05] 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.

[36:20] 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 establish the relationship. So you guys tell me see I've got this table customer

[36:34] 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

[36:49] relationship happen there should be some something in common. Yes. Customer ID. Yeah. Tell me about this one and this one now.

[37:07] 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

[37:20] 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've already explained you and we are going anyways going to

[37:34] 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

[37:47] 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

[38:05] then you can say that you know all of the others and I'll tell you the reason 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

[38:18] 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

[38:33] 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 uh if that person knows that candidate knows my SQL and he's able to answer all the questions

[38:50] 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.

[39:05] different different databases. That is definitely not required. You learn one properly, you can work on any database for that matter.

[39:23] 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

[39:35] 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

[39:50] role number name and marks awarded. So let's say I've got a new student. Yeah. Now for this new student I want to store the data but I've got I've got more data the data but I've got I've got more data for this new new student. So I've got

[40:05] let's say his role number is four name is Niha marks is whatever. Yeah, but I've also got one more data for for him for her.

[40:18] 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

[40:30] 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

[40:45] three columns. You cannot give me four columns. So it's very strict. I'll give you one more example of strict. The second more example of strict. The second strictness it says that now it says that

[40:59] 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.

[41:17] So again it will say no. It will say what what are you doing? I can just take 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 have

[41:33] created this table with the three columns

[41:45] columns. So for this one we have got the data type as integer. This one let's say data type as integer. This one let's say as string. Yeah, ware and so on and so forth. So we have to stick to that. Yeah, we have to

[42:04] We'll talk about that. Aperta will we'll see to that. Yep. It's very easy to set these rules. So you got that relational database is very very strict. You get that? Am I clear with this point? How do we do

[42:17] 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

[42:31] 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.

[42:50] 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 have run my own school and I say that fine. Yeah, but let's say there's some

[43:05] student with some you know more things. Let's say that he has got some uh he or 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. Yeah. I simply do not

[43:17] that data also. Yeah. 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.

[43:30] 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

[43:46] NoSQL databases. So, NoSQL databases. Now we 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.

[44:02] 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. flexible. So do you know what JSON?

[44:20] So relational database stores the data in the form of in the form of uh tables in the form of in the form of uh tables and in case of no SQL it stores the data in the form of JSON and there are other ways also but I'll show you the JSON

[44:32] one. I'm just uh looking for something that is quite easy to understand.

[44:47] see this is what this is JSON. So for example you've got student. So we we say student. Yep. Or let's say employee. Yeah. Employee. So you're display name, middle name, birth name of the employee. Now this is let's say the

[45:03] 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.

[45:15] So it easily accommodates with that. It says fine. So I'm going to have one more 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

[45:28] 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

[45:42] 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

[45:54] easily accommodate it. So I'm not strict [snorts] like tables. So that's what [snorts] like tables. So that's what NoSQL says.

[46:07] 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

[46:22] 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.

[46:37] Definitely you don't have to deep dive into it. XML format. Yes. XML JSON key value pair. Exactly.

[46:58] I'll take a pause. Why don't with whatever you see on my slide I want you guys to guess the use case of graph database

[47:14] 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,

[47:27] friends of friends, mutual connections, suggested friends. So whenever we have got data like this yeah

[47:42] where the data is very much related yeah it's mutually related you can see over 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

[48:02] so you buy one product then it starts showing you all the other products so 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

[48:16] 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. Even I've

[48:31] 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.

[48:46] so that they can you can find the connection between the data. Now we'll talk about centralized database.

[49:00] database for that matter. It can mean SQL, NoSQL, graph. So centralized database is what you have got one database.

[49:12] 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

[49:26] City Bank has got one centralized database. So tell me what could be the problems of having centralized one single big database.

[49:46] What if there's a power cut? There's a failure. What will happen? failure. What will happen? Everything will go down.

[49:59] Now you're not able to access your bank account and so the users.

[50:12] where you have just got one single big database storing everything and a lot of companies still uses centralized database though it is not centralized database though it is not recommended

[50:28] 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

[50:46] Don't you think so? You'll have the copies of it, multiple copies. copies of it, multiple copies. Yep. So backups. Yes. So if this goes down, you have a backup up and running so that you do not uh because of the

[51:00] system crash your website will not go down. As simple as that. down. As simple as that. So having these backups definitely is pretty expensive because here you're maintaining only one database. Now let's

[51:13] 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

[51:29] places and that's it's 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

[51:42] down. Yeah. All the good big companies websites they're 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

[51:57] issues with the power. Now this area where I'm staying is the IT hub 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

[52:12] 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 also and uh we have got some data centers when I say data centers u as in

[52:26] 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

[52:41] problems. It can definitely occur. So that's the reason why the companies they always prefer having the distributed databases. database, they're going to have databases at multiple places to avoid

[52:58] uh any downtime. Am I clear?

[53:16] 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

[53:30] 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

[53:43] after the break we'll continue because we have 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.

[54:04] 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

[54:20] storing the books. Who manages the library? Somebody should also be manage it, right?

[54:40] 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

[54:53] definitely but we want a tool which can help us manage the database. managing the library but the librarian might be uh maybe maintaining some book

[55:11] 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

[55:24] 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.

[55:36] 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

[55:52] 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 database management system. So database management system is nothing but it

[56:07] 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

[56:22] 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

[56:34] and we call it as DBMS database management system. So it's a software management system. So it's a software that helps us manage the databases.

[56:46] 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

[57:00] CRUD operations. So do not get scared with these jarens, technical jargon. These are very simple thing. So when I say CRUD,

[57:12] 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

[57:27] databases only like Yep. R stands for what?

[57:39] Update. And D stands for delete. So if anybody 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

[57:51] You're creating the data. You're just like u you've got a wardrobe. wardrobe. How how many clothes you've got. Maybe you're updating your wardrobe you're removing something from from it and so on and so forth. So here also we

[58:08] do all of these things but we do it with the help of the software called DBFS

[58:23] and guys see um okay maybe I'll talk about that later not right away. what it could be. I just told you what DBM is. What RDB

[58:35] I just told you what DBM is. What RDB DMS could be?

[58:47] database? You know that there are so many databases, right? We learn about the relational database. Now to manage the relational database, what we have? the relational database, what we have? We have got relational DBMS. Simple.

[59:03] 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.

[59:15] 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

[59:27] 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 charge GPD. I I do don't understand plain

[59:41] GPD. 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

[59:55] have to use SQL and that's what we are going to learn. and that's what we are going to learn. We are going to learn SQL.

[1:00:07] 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

[1:00:23] you're getting just like let's say there's a librarian. Yeah, librarian is there's a librarian. Yeah, librarian is what? Librarian in our analogy is DBMS who manages the database. Yeah. With the U. So

[1:00:37] U. So then we have got library. So library is then we have got library. So library is what? Library is the database. books. So books are what? Books are data.

[1:00:54] 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

[1:01:06] 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 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

[1:01:22] 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

[1:01:36] speaking Spanish in front of that librarian, the librarian would be like what are you saying that 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

[1:01:50] DBMS in order to make DBMS work for us we use in order to make DBMS work for us we use SQL

[1:02:02] 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

[1:02:17] systems. You use RDBMS, you use SQL and RDBMS. you use SQL and RDBMS. So am I clear what is SQL?

[1:02:30] got data, data 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

[1:02:43] databases. Now in order to make the DBMS work for us, we talk to DBMS in SQL work for us, we talk to DBMS in SQL language.

[1:02:57] 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

[1:03:11] 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

[1:03:25] language. Okay that's a language of DBMS. Yes, everything is uh everything is done in DBMS Rajpad. So, we are going to use

[1:03:39] DBMS. It's a software in order to manage the databases. question. What is the difference between SQL and MySQL?

[1:03:52] SQL is a language. Hear me out. Yeah, SQL is a language SQL is a language and MySQL MySQL is the DBMS.

[1:04:04] So, we have got multiple relational DBMS. We have got multiple relational database management systems in the market. For example, MySQL.

[1:04:16] Then we have got SQL server. databases. Yeah. Shares the name. Same name with the databases. Oracle

[1:04:28] 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

[1:04:40] or you can say database also they share the same name in order to talk to the 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?

[1:04:57] 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 my SQL is the So SQL is a language and my SQL is the DVMS.

[1:05:13] write but yeah my SQL possess 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.

[1:05:28] 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. DBMS. It's a coding language. Exactly.

[1:05:46] 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

[1:06:01] 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 about the different type of databases that we have. Also we learn about asset

[1:06:16] 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.

[1:06:35] the real world object. So basically entity you can think of it as a table. 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

[1:06:51] and it is represented with the help of rectangle. Then we have got attributes. So what do you think attributes could be?

[1:07:03] 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

[1:07:18] characteristics details yep of an entity. So let me write it. So attribute about they are the property of an entity.

[1:07:30] you tell me what could be the attributes. What could be the attributes for the student table? Student entity

[1:07:43] ID, name, age, class, marks, etc., etc. Right? So age, class, marks, etc., etc. Right? So these are nothing but the attributes. Attributes are shown using ovals. Yeah. So whenever you see

[1:08:00] 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.

[1:08:12] 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

[1:08:26] 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 if I give you the example let's say that we have got two entities students is one

[1:08:40] of the entity and course. So course let's say students let's talk So student table has got all the information about you guys and courses

[1:08:52] the courses that are provided by simply learn. So if I talk about the relationship between the two so simply relationship between the two so simply students have enrolled

[1:09:04] or I would say student has enrolled for which course? Yeah, I'll simply say student in rows for goals.

[1:09:21] 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 a right away. At least I'll give you a little bit idea about it. So, we have a

[1:09:33] primary key. Now, what is a primary key? So primary key uniquely So primary key uniquely it uniquely identifies an entity.

[1:09:56] 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

[1:10:11] you're sitting. So the government is maintaining the database of all the citizens. Now you I'm Tulika Gupta and there might hundreds and thousand but at least thousands of tulika Gupta in India right

[1:10:26] 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 will coincide. So it has to make sure that we it uniquely identify my my data

[1:10:39] and it uniquely identify the other Tulika Gupta's data. So what do you Tulika Gupta's data. So what do you think what government would use?

[1:10:52] 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

[1:11:05] talk about okay in this table we don't have any primary key okay in this table have any primary key okay in this table can you see any primary key

[1:11:18] 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 and me. So this can be definitely repetitated. But how will we uniquely

[1:11:32] identify this anu or how will we differentiate this anul 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?

[1:11:47] So we have got the data of the products. So we want a key that will uniquely identify each product its product ID. So am I clear with

[1:11:59] its product ID. So am I clear with primary key?

[1:12:19] it connects two entities. Yeah, it connects two entity. So let's say you've got the entity one.

[1:12:31] So let's say you've got the entity one. It has got some primary key P1. It has got some primary key P1. Now we have got entity 2. So in order to connect with entity one, entity two is using this primary key. So

[1:12:46] 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

[1:12:59] 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

[1:13:13] question that I've asked you before. The two tables are related with which column? Quickly this table I'm talking about and Quickly this table I'm talking about and this table I'm talking about.

[1:13:39] We use oval only. These are the attributes. So we use oval only. 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.

[1:13:52] 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 foreigner.

[1:14:10] It becomes what? It becomes foreigner. Yes, it becomes foreign key. this is what this is a private this is a primary key. Yeah this is a primary key.

[1:14:23] primary key. Yeah this is a primary key. Now in order to connect this entity the entity the column that I'm using the common column that I'm using is customer ID. Now for this table customer ID is a

[1:14:38] primary key but for this table customer ID is what? It's a foreign key. It's not 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

[1:14:55] becomes a foreign key of the other entity. Am I clear?

[1:15:15] 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

[1:15:31] 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

[1:15:45] our coding. Am I clear now? Shaa, 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.

[1:16:04] 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. So it looks like this. You've got the entity

[1:16:19] entity this entity may have multiple attributes let's say name. Okay I'm so sorry I don't know what is happening id.

[1:16:31] happening id. Now I've got another table again 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

[1:16:44] other. So there's a relationship between the two. So this is what in rules. er diagram, you should be able to understand student and courses are

[1:16:59] nothing but the entities. The ovals that you see are nothing but the attributes you see are nothing but the attributes of the entities and the diamond that you see are nothing but the relationship of the entities.

[1:17:11] the entities. Am I clear?

[1:17:35] also we have got different different type of attributes. exercise for you all. I'll see to that. If we can do it today that's fine

[1:17:49] If we can do it today that's fine otherwise we'll do it tomorrow. dialog. So we'll see

[1:18:17] 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,

[1:18:30] multivalued attribute and composite attribute. So let's cover all of these.

[1:18:45] 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 student ID, role number, passport number.

[1:19:00] These are what? These are key attributes. So you can just have a look.

[1:19:42] have got. Now what are composite attributes? As 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

[1:20:05] for product table a? Okay, I'll come to that. the product table. Now, tell me for product table, I'm talking about the

[1:20:17] product table, the one that I have highlighted. What is the primary key?

[1:20:29] 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

[1:20:41] 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.

[1:21:03] 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.

[1:21:17] 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.

[1:21:30] So that's what composite means. Am I clear with composite?

[1:21:43] Okay. And my laptop is acting up. Just give me a second. is what are multivalued attributes? Now there can be some

[1:21:56] 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

[1:22:08] attribute order or let's say we have got the or attribute. So order let's say that you make a order. Yeah. You make an order. So

[1:22:23] in ca in case of order in case of order let's say I make an order of a product let's say I make an order of a product I'm not able to write okay there's some issue with the writing thing just give me a second

[1:22:46] yeah so let's say we have got the product yeah 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

[1:23:03] 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

[1:23:18] the database. You're going to generate or play with the data of the database. So you should know if somebody give you know what this ER diagram is

[1:23:30] thing. If you see, it's just like the walk in the park. It's so simple. walk in the park. It's so simple. Moving ahead.

[1:23:44] means that these values see attributes are what? They are like column. they're 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

[1:23:59] other attributes. For example, let's say that you've got uh experience. Now we that you've 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

[1:24:13] and today date. Yep. 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

[1:24:27] 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 runtime. Yeah. So because see if I save

[1:24:40] it let's say I save it. So today it's 2 years 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

[1:24:53] 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

[1:25:05] update my database. So 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 say I've got another attributes with the uh start date or the joining date.

[1:25:22] for the derived attributes. We are going to work on it. We usually create the 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

[1:25:36] 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?

[1:25:53] example price. So quantity into unit price is total price derive attribute. Perfect. Very good Nish. Okay. Relationship you already

[1:26:07] understand. Now uh there are few things that just give me a second. I would like to just give me a second guys. these are boring but yeah we'll learn few more term. So entity sets are what?

[1:26:24] 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

[1:26:37] employees. Let's say we have got three employees. Employee one. So let's say employee one is Niha. Employee 2 is John. Employee 3 is Mark. So entity set will tell us the

[1:26:51] 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

[1:27:06] 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

[1:27:18] 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

[1:27:32] 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 E E1 works in D2 and E2 works in D3.

[1:27:46] 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.

[1:28:01] 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 I'll talk about table only. So tables

[1:28:14] 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

[1:28:30] that are related to each other. Yeah. The number of entities that are related 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

[1:28:44] 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

[1:28:56] we learned that entities are represented by the rectangle. relationship. So this is a relationship. So basically

[1:29:09] what it is saying is now hear me out. It's very simple thing. So urinary uh unary relationship is a relationship where the entity is related to itself.

[1:29:21] where the entity is related to itself. So it's a self relationship itself. So if I give you this example of employee let's say that we have got the

[1:29:36] employee table. Now hear me out. It's very simple. and then we have got the name of the employee. So let's say 1 2 3 4 and name

[1:29:51] 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

[1:30:04] manager maybe manager ID. So let's say that A's manager is D. So I will say that A's manager is D. So I will say four. B's manager is again D. four. B's manager is again D. C's manager is let's say uh let's say B

[1:30:20] 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

[1:30:36] 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?

[1:30:51] right so this is what this is self relationship where the table is related relationship where the table is related to itself.

[1:31:03] 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?

[1:31:16] Should I move forward to the next slide? It's a very simple one. If you see

[1:31:29] 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

[1:31:44] 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.

[1:31:56] I hope I'm clear with this that binary rel relationship simply involves two entities. It's a very simple one. Let me know if It's a very simple one. Let me know if you want reexlanation for any of these.

[1:32:09] 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 basically where we have got three entities. Yeah, three entities involved.

[1:32:24] 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

[1:32:37] 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

[1:32:52] So am I clear with this one? It's a simple one. Am I clear?

[1:33:04] a very interesting topic. The types of relationship. So uh we have got four types of relationship. One to one, one to many. relationship. One to one, one to many. So basically see basically one to many

[1:33:18] and many to one is one of the same thing. It's one of the same thing. thing. It's one of the same thing. So u you would either write one to many thing. So actually I say that there are three type of relationship but you'll

[1:33:31] 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 the only thing. Anyways we're going to talk about it. So allow me a second. So

[1:33:47] let's talk about huh one to one relationship. So what is huh one to one relationship. So what is one toone relationship?

[1:34:01] we have got the employee table and we have got the employee ID table. So what one relationship says that every single instance of one entity is connected to a

[1:34:13] 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

[1:34:26] over here if I talk about this one this example that is being shown on PPT. So example that is being shown on PPT. So let's say I've got the employee table. >> [snorts] >> Yep, I've got an employee table and

[1:34:40] >> Yep, I've got an employee table and there are some employees Let's say ID 1 2 3 4. So here, let's say for every

[1:34:54] employee like here also let's um employee ID is not a very good example 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

[1:35:09] 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 that you're holding. So here you've got the bank account details.

[1:35:30] So again the ID of the employee and some bank. Yeah, let's say City Bank and some bank. Yeah, let's say City Bank or HDFC and so on and so forth. So here or HDFC and so on and so forth. So here the relation is one to one. Why? Because

[1:35:42] 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 another entity. So it means this is connected to a single instance only. It

[1:35:56] means one instance is related to one instance. only one instance of the other table. It's not that one instance is related to

[1:36:08] 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. [snorts] Am I clear what is one to one? If it is not 100% clear to be very

[1:36:22] honest once we see what others are like what one to many etc you'll able to what one to many etc you'll able to understand what does instance means so instance means a row sana it means a row one row

[1:36:37] one record yeah so it's it doesn't mean the column it means the row I hope I've answered your question [snorts] so guys am I here what is one to1 relationship

[1:36:53] [snorts] it's very simple one but if still you confusion will be clear once we see the other type of relationships that we have

[1:37:05] okay now we look into the other one that is one to many [snorts] so one to many is where a single instance of one entity is connected to several instance of the other entity. So

[1:37:22] what does that mean? What does that mean? Let's say over here you can see 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

[1:37:37] 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.

[1:37:52] me add only two. I think two would be suffice. Okay. And then we have got the suffice. Okay. And then we have got the customer name. So, let's say ID 1 2 3 4. customer name. So, let's say ID 1 2 3 4. Customer name A B C D. And here we have

[1:38:05] got the orders table. So, order ID and [snorts] the other things. Yeah. Let's say uh the product and etc. Whatever you can think of. So and etc. Whatever you can think of. So again we have got some orders 10 11 12

[1:38:19] again we have got some orders 10 11 12 13 14 and so on and so forth. So here in 13 14 and so on and so forth. So here in case of one to many one customer case of one to many one customer one customer can place multiple orders

[1:38:32] 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

[1:38:46] is connected to multiple instance of the other table. So one record is connected to the multiple records may or may not be connected to the multiple records of

[1:38:58] the other in of the other entity. So this is what one to many this is what one to me 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

[1:39:11] table is on the one side. Which table is on the one side? Customer. And which table is on the many side?

[1:39:28] Order. [snorts] 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

[1:39:43] order over here on this side and if I put customer over 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 it's just that if I put order over here then one

[1:39:58] 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.

[1:40:13] So this [snorts] is one to many and many to one. It's one of the same thing. It's 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.

[1:40:25] 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 PPD anyways. So yeah. So anyways, we'll talk about many to many now. So many to many. Okay,

[1:40:42] [snorts] 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

[1:40:57] 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

[1:41:13] per the requirement, you can keep any table on the many side and any table on the one side. I repeat, as for 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

[1:41:25] 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,

[1:41:40] patient appointment date. Very good. Then okay, I'm getting a lot of student subject patient appointment. Okay, student

[1:41:52] 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

[1:42:05] say I'm working with IBM so until unless I'm moonlighting definitely that is not allowed so one employee sorry uh one company being employee sorry uh one company being worked by u one employee working in what

[1:42:20] worked by u one employee working in what I'm saying so one company is uh can have I'm saying so one company is uh can have multiple employees

[1:42:33] Patient table. A medical test table. Very good. Bank customer, right? One nation, many states. Very good. One state, many cities.

[1:42:48] Very good. One state, many cities. One city, many colonies. Perfect. So now One city, many colonies. Perfect. So now we'll move to many to many. we'll move to many to many. One person many bank accounts

[1:43:07] 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

[1:43:19] projects. Let's say we have got the projects let's say uh okay I'll just name some of the projects that I have worked on Sunrust etc. So definitely u there are situations in the company where one employee works in the multiple

[1:43:34] project right so you may end up working most of the time being worked by multiple employees so here it is one to multiple employees so here it is one to many socrust

[1:43:53] want you to contribute some examples on the chat the chat for many to many. just covered. But when I say what is cardarity means

[1:44:11] cardinality means how many in a relationship how means how many in a relationship how many. So

[1:44:30] So basically cardality 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

[1:44:54] related to rows of another table. you know a table we can have tables and suppose there's a one row so one row can

[1:45:10] 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 table. So cardinality is like going one step deeper and telling telling about

[1:45:24] 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

[1:45:40] 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

[1:45:54] go to the next slide and then I'll explain you that would be better. So if you see over here the cardinality is represented over here. So it says 0

[1:46:06] which is the minimum cardality and n is a maximum cardality. and n is a maximum cardality. Here also zero is a minimum cardality and one is a maximum cardinality. So what does it says? So it says let's

[1:46:24] 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.

[1:46:37] So, project ID and the name of the project.

[1:46:49] C D. Here for the projects also we have got some ids 1 2 3 4 5 6 7 8 9 and maybe got some ids 1 2 3 4 5 6 7 8 9 and maybe some project P1 P2 and P3.

[1:47:03] 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

[1:47:18] two tables. Now a may not be working any in any of the project. So zero means in any of the project. So zero means that it is possible that any instance of that it is possible that any instance of this entity any row of this entity is

[1:47:31] 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

[1:47:44] 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 have some developers who are not who are not involved in any project. N means

[1:47:59] maximum. It [clears throat] means that a developer so B developer so B can work on n number of projects, multiple projects. So that's what it means. Zero means we

[1:48:13] 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 and one means again the same thing. Now let's see P3 is a new project

[1:48:27] 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

[1:48:40] 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

[1:48:54] 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

[1:49:10] which are very very small projects yeah very small project just need one very small project just need one developer project so it means a project developer project so it means a project can have zero developers or at max the

[1:49:23] 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. 4 5 6. But one project cannot be worked by

[1:49:39] But one project cannot be worked by uh multiple developers. So am I clear uh multiple developers. So am I clear what 0 1 0 n denotes? what 0 1 0 n denotes? Hm.

[1:49:53] fine. Anyways, we have understood it. That is more important. So, am I clear? What does cardality means? What does 0 n 01 means?

[1:50:06] You can just read through this slide, but please confirm if I'm clear or not. but please confirm if I'm clear or not. I've just got one. Yes, clear. Perfect. You can just read through it. It's a very simple thing.

[1:50:23] 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

[1:50:36] maths. But that's not the case. So I'll quickly repeat one more time. Over here the cardity is represented

[1:50:49] Over here the cardity is represented like this 0 n. So 0 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 table developer and we have got this

[1:51:03] stable project. So zero means that we can have some developer 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.

[1:51:16] 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. And means a developer can work on multiple projects. So B can work on 1 2

[1:51:33] 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 zero and n means. Now here it says 0 and one. So

[1:51:45] 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've got a lot of projects with no

[1:51:59] 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

[1:52:16] instance of the developer table. So this instance zero means it is possible to instance zero means it is possible to have instances not having relationship with the instances of the other entity. One means maximum. So maximum means it

[1:52:31] 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

[1:52:46] 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

[1:53:02] or at max one one. Okay. So we are done with definitely we 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

[1:53:18] 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

[1:53:32] 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 20 minutes let's practice or let's see the holistic view of u using MySQL.

[1:53:47] So I want everyone to launch their MySQL workbench. So I'm using Windows. So what MySQL workbench. You have to open it. Now you can launch the uh labs. So

[1:53:59] yesterday Rashta 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

[1:54:12] 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

[1:54:27] hands-on if not all the time. So, uh your labs should definitely be working. working you'll not able to learn with me. Of course you'll able to learn with

[1:54:40] me but then you have to practice after the 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

[1:54:52] 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 want to see if you done on the chat once you are done with opening the lab.

[1:55:07] 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 is it double one and uh exclamation mark or is it one? Okay. So

[1:55:22] see everybody's helping you with the password. You can use the same password. password. You can use the same password. Thank you everyone. Thank you so much.

[1:55:34] fine. Definitely fine. So workbench is what? It's a UI way of working with the databases. You can also work with the databases databases via commands command prompt right cmds.

[1:55:49] So that is definitely not how we work with the databases. We always use workbench because that's more friendly and easier to use.

[1:56:02] 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 if you're using the lab provided to you by simply learn uh so it is get uh it

[1:56:16] 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.

[1:56:30] 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?

[1:56:42] 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.

[1:56:55] 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

[1:57:10] not when you are downloading or installing the SQL yeah it's not only about next next next. There are some settings that you have to do. So please which I shared yesterday. Today also I've shared it multiple times. So please

[1:57:24] 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 do the hands-on. Please do not start installing it right now. You can uh

[1:57:36] 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

[1:57:50] you're not able to practice. Uh that's very much okay. Yeah, you can write down able to type it. You can always write it. Oh, I have no idea about this this thing. Shri,

[1:58:06] I have no idea about Yeah, this thing unable to launch the Yeah, this thing unable to launch the remote. So Shri, you can try it with V with Yeah. So you can try later. just after some time just retry it.

[1:58:26] 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 10 5 so that we can take up your your screen and we'll try to help you out. So let's continue with our learning

[1:58:41] 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

[1:58:57] will not be using lab. I'll be using my SQL on my personal laptop. do is we are going to create a database. So as I told you that today uh the for

[1:59:11] 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 everything. So in order to create the database in order to create the database

[1:59:32] is we're 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

[1:59:47] 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 the keywords and you have to make sure that you write the statements like this.

[2:00:03] 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

[2:00:16] using the u u what do we call this? You can use upper case and lower case both. Yeah using semicolon. Thank you so

[2:00:29] both. Yeah using semicolon. Thank you so much. if I've just got one SQL command it will still run. Now in order to run it we

[2:00:41] 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

[2:00:55] 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 execute the selected portion of the script or everything if there is no

[2:01:10] 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

[2:01:23] 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, if you're using it for the first time, it might be showing one. So I'm going to run it. Once I run it

[2:01:39] 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 database school one row affected. And you can see this in green. it means that

[2:01:54] 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

[2:02:08] 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

[2:02:24] 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

[2:02:37] 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

[2:02:51] 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

[2:03:04] 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.

[2:03:16] 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

[2:03:30] 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

[2:03:45] you're done with writing the command, you can run it from here.

[2:03:58] 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

[2:04:12] 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,

[2:04:28] 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

[2:04:41] appeared. So you get to see the output now. see the output I hope that you guys are able to see the database created.

[2:04:58] Now guys maybe this panel is also collapsed for you. So please make sure collapsed for you. So please make sure that you expand it.

[2:05:15] You have to it might be collapsed like this. So you literally have to expand.

[2:05:28] 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

[2:05:41] go to view. You can go to panel and you can say show output area. talking about this one. So I'm not sure for this one side. Okay. Yeah. So in

[2:05:56] view you can see just uh check go to panel and click on show sidebar. the queries on the chat. So guys if it's okay if you're not able

[2:06:12] 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.

[2:06:31] queries because I've told you that these we these could be the problems still share your screen. Okay. So at least you can write the code.

[2:06:44] 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

[2:06:56] 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

[2:07:10] command. So just delete the previous command. command. And you can run the command use.

[2:07:26] 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

[2:07:40] database shows in bold it means that this database is selected and whatever whatever queries that you're going to perform will be performed on this database. So just double click on the database or you can

[2:07:57] 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

[2:08:10] 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

[2:08:22] have got tables and views. So we are going to cover these two. So first we'll talk about the tables because we're going to cover everything small topic. It's a short topic that we are going to cover later on. So what we

[2:08:35] 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 executed successfully. Just double click on it. Sheila.

[2:08:58] So you'll get to know all of these things. Yeah. Hold your horses everyone. 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

[2:09:12] have even a single table in our database. guys as I told you please uh don't worry about not able to see the output panel

[2:09:25] 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

[2:09:39] 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 order to create the table We write the command create. So you can make it uppercase, lower case, everything is

[2:09:53] 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.

[2:10:18] 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

[2:10:33] 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

[2:10:45] 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

[2:10:59] means the type of data a column can hold. So ID can hold integer in means learn about all the different type of data types also later. So for now

[2:11:14] 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.

[2:11:35] and age has got the data type in yeah age is always a number. So vcare what does ware means? Sorry we also have to define the number of characters that define the number of characters that this column can take. So what does 50

[2:11:49] 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

[2:12:04] 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

[2:12:18] data type of the city. It's again let's say 50 your marare 50 and my table the code for the table creating the table is ready. ready. So just give me a second

[2:12:33] I'll just quickly look into the chat via what you're saying is right. Vanc 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

[2:12:46] to say is that this is what this is a number but if you give three in vcar it number 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 um make

[2:13:00] 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 vcar is same. Yeah it's it's a it's a technical language. So string

[2:13:16] means text. So if I say string or if I say text it's all about the data type. say text it's all about the data type. Yeah. Or if I say vcare. So in SQL we Yeah. Or if I say vcare. So in SQL we say vcare right? In python we say string

[2:13:31] 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 here. It can be anything. It can be this. It can be number. It can be any any anything like this. So anyways,

[2:13:45] 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

[2:14:00] 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

[2:14:16] 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

[2:14:34] 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 use the table as such just like DB databases

[2:14:52] Aira you can share the screenshot on the chat so and look into it. What is the problem? So in order to insert the data we we run insert into command. So insert the name of the table. The name of the table is students.

[2:15:08] 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 it's just a just a helicopter view of everything. A small

[2:15:20] 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

[2:15:43] SQL is not case sensitive and then I I can give the values.

[2:15:55] So in values let's say id is one. Now it's a vcare. It 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

[2:16:13] comma I can give multiple values. So I'm just copy pasting it. So let's say ID is two. The name is Deha.

[2:16:30] 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.

[2:16:46] 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

[2:16:59] are working. So right now if I execute it, you can see that it is giving me it, you can see that it is giving me error. So I want you guys to tell me error. So I want you guys to tell me what is the error? What is the problem?

[2:17:17] 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

[2:17:30] 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

[2:17:47] taking both both the commands as one command. So how I can do this separation by adding semicolon. As soon as I add the semicolon you can see that the error that I was getting. You can see the error that I was getting over here. It

[2:18:01] 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

[2:18:15] 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

[2:18:30] 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

[2:18:42] 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

[2:18:56] that you want to run. So here I'm selecting this code and I'm going to run selecting this code and I'm going to run it. Perfect. You can see that it got run it. Perfect. You can see that it got run and the data is inserted into the table.

[2:19:08] 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. You can just Google the shortcut keys. I'm very bad with remembering the keys.

[2:19:24] I'm very bad with remembering the keys. There was uh I think it's control question that is asked to me in every batch and I tend to forget the answer.

[2:19:43] 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.

[2:20:06] So space whenever you have a new word you're going to give this space sha. I'm just giving comma then full name then again comma and so on and so forth.

[2:20:25] command use school. If you're not able to see the If you're not able to see the tab this left panel just you run this

[2:20:37] 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.

[2:20:52] to use the quotes for the varita. So whenever you're writing the strings, strings are always represented in computer in in machines um whether you're writing SQL or whether you're writing you're working on Python,

[2:21:07] 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

[2:21:21] 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. I' I've got three rows of data.

[2:21:37] 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

[2:21:50] file. Select star. So star means all the columns. All the columns from the name table? The name of the table is students. Again, please highlight it and then run it. Do not just run it

[2:22:04] without highlighting it. Otherwise, if you have the previous code, this will also run and you may get error that the table already exists and so on and so forth. So, please highlight it and run it.

[2:22:16] 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 file. The reason is that I will be needing this code and I do not want to

[2:22:33] 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

[2:22:46] database for me. We have already seen it. But if I rerun it, then it will it. But if I rerun it, then it will throw error. You can see over here.

[2:22:59] 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

[2:23:15] 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

[2:23:30] Now if I rerun this code. Yeah. If I rerun this code, Yeah. You can see over here create database DB. It means the error is in

[2:23:42] this command. And what is the error? The error is that can't create DB 100 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

[2:24:00] exist. So what I can do is I can say create database 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

[2:24:14] 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

[2:24:30] command it will not throw error. And that makes sense also because now what we are seeing 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

[2:24:44] 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

[2:24:59] 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.

[2:25:15] 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

[2:25:29] this command in order to see the warning. in order to understand the working of it.

[2:25:42] 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

[2:25:56] all it's these are the tabs you can hop on and hop off

[2:26:16] me end this command with the help of semicolon. Now I'm going to drop the database. So drop database

[2:26:30] 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 simply highlighting it and I'm executing it. The database has been dropped. Now

[2:26:44] 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, already been dropped. So if I rerun it, what do you think? What will happen?

[2:27:09] 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.

[2:27:22] 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

[2:27:38] 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

[2:27:54] 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

[2:28:08] 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

[2:28:22] 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

[2:28:37] Right now it's like we do not have even a single database. So let's recreate it. at the end of the session. I'll ask some one of you to share the screen and we'll

[2:28:50] 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.

[2:29:03] Now I want to know that what are 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 let's let me do one thing. Let me create another database. So DB 100 I created

[2:29:18] 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.

[2:29:31] Fine. Now let's say that you want to know that what all databases you have know that what all databases you have got in your MySQL. So what you can do is you can run the command show databases.

[2:29:44] So this will show you all the databases that you have 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

[2:29:59] system. Anyways you can see over here. So these are system defined databases and maybe I'll tell you about these databases not today but yeah in some one of the class that what these databases. So today is definitely not the right day

[2:30:13] 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

[2:30:28] 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 can tell you the answer.

[2:30:47] let's say that uh Okay, I'm going to use the database. So, I'll say use DB 200. I mean using this database.

[2:30:59] 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 table. So basically I'm using this database and then I'm creating I'm

[2:31:12] 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

[2:31:27] 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 into the table. So I'll wait for 2 minutes.

[2:31:46] 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 please make sure that you do not come without practicing in the next class.

[2:31:59] 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

[2:32:13] 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 the chart and you can run it. So that you have got a table created.

[2:32:30] database commands. So now we going to learn about the data 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

[2:32:42] 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 data type is what the type of data the column can hold or will hold. So here we

[2:32:56] 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

[2:33:10] me that Tikica 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 all of these things are something that if you just try it by yourself you'll able to

[2:33:24] 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

[2:33:36] 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

[2:33:50] just quickly show you and then I'll give you time to just try it at your end. So 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.

[2:34:05] 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

[2:34:17] 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

[2:34:33] 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

[2:34:48] guys can just try saving the SQL script file. different type of data types that we have got in my SQL.

[2:35:06] please make sure that you listen to me. Very important. So the first data type that you can see is car. So care simply means characters.

[2:35:22] text, string, whatever you want to call it. 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

[2:35:37] the car data type. The column can take zero characters. So that's a minimum range or maximum range is 255 characters.

[2:35:49] 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 hear me out. So if I write let's say ID and if I say care and if I give two so

[2:36:02] and if I say care and if I give two so it means that ID when I insert the 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

[2:36:16] it has to be of two characters. Yeah it has to be of two characters. So 1 2 1 3 has to be of two characters. So 1 2 1 3 3 4. So you're getting again I'll give

[2:36:28] you one more example. So let's say that I create a column. I create a column. Let's say the column is okay. I'll Let's say the column is okay. I'll create the column [snorts] code.

[2:36:46] 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

[2:37:02] it has to be fixed length. Means that I have it has to be fixed length. So I Yeah, three characters only. Or let's say if I give anything else, let's say say if I give anything else, let's say uh something else. So I'm just thinking

[2:37:16] of something which will have the text as such. Okay. So I'll just say state. Yeah. Again I give or maybe let me give month.

[2:37:30] 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

[2:37:42] 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

[2:37:55] 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.

[2:38:09] Am I clear with care? I've given you multiple examples. Am I I've given you multiple examples. Am I clear?

[2:38:30] think V stands for? What? What does this stands for?

[2:38:43] Variable. Right? So here it is what variable it means that when I create a column of var data type and let's say this is a

[2:38:57] var data type and let's say this is a column and I say var 100 it means that it can take the characters up to 100. So let's say I create a column name

[2:39:10] let's say I create a column name the data type is var 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 tulle which is of six

[2:39:25] characters I can give Ravi which is of four characters and I can give a very 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

[2:39:38] 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.

[2:39:55] Yeah this is what var is. Just give me a second vcare a lot. Now some of the data types you're going to use 90% of the time. So

[2:40:10] vcare is one of the data type. I is one of the data type. Anyways let's talk of the data type. Anyways let's talk about text. So see ware says that I can about text. So see ware says that I can accommodate characters up to 255 only.

[2:40:22] 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

[2:40:36] 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

[2:40:51] can simply make it text so text can accommodate large text data. 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

[2:41:06] 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

[2:41:23] 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.

[2:41:41] So for that you need to create the column with a data type blob. I intend says integer and this is a limit of it. You don't need to remember the limit but it can

[2:41:54] take negative numbers and positive numbers as well. Positive 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

[2:42:09] 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

[2:42:25] 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

[2:42:40] 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 int small integer.

[2:42:55] big integer really big integer you're talking about the revenues of the 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

[2:43:10] because again it has got some upper limit so then you can go with big end about float So float is what? Float can store

[2:43:26] 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 decimal data, then you can go with float.

[2:43:44] 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

[2:43:56] whereas float is uh less precision. So what does that mean? nothing to do with my SQL. What does precision means?

[2:44:14] 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

[2:44:29] number of digits stored. I'll write it over here. Total number of digit stored.

[2:44:43] 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 got a number like this. So I'll just write a big number 5 6 7 8 9 10

[2:44:59] 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

[2:45:16] 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 something like that. Yeah then it will round it off. So it's all about u

[2:45:30] 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

[2:45:46] the number get you know rounded off like this. So that's what it means. Yeah, that's what it means. what it means. I hope I'm clear.

[2:46:05] digit stores and lower means few fewer digits uh stored. digits uh stored. >> [clears throat] So anybody who doesn't know about boolean, you can be very very honest

[2:46:20] here because I understand that a lot of you are coming from the nontechnical background. So this will give me the clarity how how So this will give me the clarity how how do I have to explain you boolean?

[2:46:33] Anybody who doesn't know okay I'll explain. So boolean boolean is okay I'll explain. So boolean boolean is a very important data type a very important data type which stores two values. Yes,

[2:46:48] 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. True means yes. So true.

[2:47:00] write true. True means yes. So true. And then we have got false means zero. So [snorts] it stores only two values that is either true or false and it is a that is either true or false and it is a very important data type. So for example

[2:47:15] 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 have seen check boxes. So if you check it so ideally because I come from the

[2:47:29] 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

[2:47:44] 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

[2:48:00] 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. Hmm. SS date time. The name

[2:48:13] 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.

[2:48:31] 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 means it means that if [clears throat] you give any column yeah if you write

[2:48:46] any column with the data type of time stamp what it will do So whatever your system uh time stamp is. Yeah. The date as well as the time.

[2:49:02] If I change it and if I make it something else then it will take that only. So take system based date and time.

[2:49:15] 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

[2:49:30] 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

[2:49:45] 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 numerical values. So signed simply means that for example if I talk about tiny

[2:49:59] 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 values. So if you remember from the previous slide,

[2:50:21] see we have got tiny int small integer this - 128 to 127. from minus 128 to 127. And if I create a column, let's say I

[2:50:37] create age column. And if I give tiny hint

[2:50:49] 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

[2:51:01] 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.

[2:51:16] 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 uh on the pad. Anyway, tiny int. And then if I

[2:51:31] the pad. Anyway, 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

[2:51:45] 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 to be 200 and 255 also. But yeah, that can happen if we are uh uh working on to

[2:51:58] age. I think tortoise 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

[2:52:13] 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.

[2:52:30] calculation. This will come around to be 255. So we are saying I do not want 255. So we are saying I do not want negative numbers. So do not waste my range in negative. Yeah. So I just want

[2:52:44] the positive numbers. So unsign will help me to increase the range. Am I clear with what is sign and unsign?

[2:52:57] with what is sign and unsign? Simple Now you have to allow me a minute. I forgot to open my notes because again we

[2:53:12] 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 tables. So right now I'll ask you also to copy

[2:53:28] 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

[2:53:42] 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

[2:53:55] active, when you're participating on the chat, when you're doing the hands-on, code and pasting it and saving it in your notepad file, what happen is you

[2:54:07] 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

[2:54:19] 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

[2:54:33] just quickly run this command. Just give me a second. Yeah. So the table has been created. Now you can see that this table has got

[2:54:49] my data types. So it has got int int is unsigned. What was unsigned? Let's quickly see what was unsigned. Only positive numbers. So it gives a really huge cushion. Yeah. If I just

[2:55:04] remove the negative numbers, you you can do the calculations. So it will be a something around four plus+. So I get extra cushion of positive numbers. VCAD you already know. Okay. Here again unsigned small int. Okay. We have got

[2:55:19] something called small ints also which is I think smaller than tiny int. Then or maybe let let me make it tiny int. Then decimal. So I'm giving the precision over here.

[2:55:34] 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 7 8 and 2 is what? After decimal how many digits? So I can have 1 2

[2:55:50] then I've got float. Then I've got boolean which can have Then I've got boolean which can have true or false.

[2:56:02] 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.

[2:56:17] want you guys to see my screen. So here we go. I'm inserting the data. So here we go. I'm inserting the data. This is int. This is vare. Yeah, this is This is int. This is vare. Yeah, this is int. This is vare. This is tiny int.

[2:56:29] This is again tiny int. So this one is unsigned. This one is signed by default. decimal this is float

[2:56:41] boolean I'm giving one I told you that either you can give true or you can give 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

[2:56:57] 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

[2:57:12] 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

[2:57:26] you know that I definitely I do not want you guys to copy paste you guys to copy paste so run this command

[2:57:38] Okay. Yeah, very good question Sanskar. 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

[2:57:51] column name. So, when can I skip the column name? I can skip the column name 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.

[2:58:04] Yeah, the sequence would will be the first value that I'm giving is for product ID. The second value is for

[2:58:20] 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 I'm inserting the data into all the columns. Also, I'm maintaining the

[2:58:34] 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

[2:58:50] columns. So, we are going to talk about this thing more. Yeah, that when you can skip and when you cannot skip. 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

[2:59:04] 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 you the code. Yeah. So, I've given you the code now.

[2:59:20] 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

[2:59:39] was a quantity. But what I've done is I' I'm giving a string. It's an in integer. Yeah, it's a tiny int. So it will throw error. Now if I run this, it will throw error. It has thrown an error.

[2:59:55] It has thrown an 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.

[3:00:15] the table. You just check the format of the table, the data. Yeah. And then query it. And we done with today's learning.

[3:00:30] 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.

[3:00:43] 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 a

[3:00:56] previous session. So we start with a keyword create. keyword create. Then we have got the keyword table and Then we have got the keyword table and then we give the table name H.

[3:01:11] 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

[3:01:25] constraints? 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

[3:01:40] 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

[3:01:52] and I'm going to select this database. So either you can select it by doubleclicking on it. Now these things we have already done. So I'm quickly telling you it's like I'm reiterating. So that's why I'm pretty quick over here

[3:02:05] 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

[3:02:20] 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

[3:02:34] 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.

[3:02:59] into this table. Now guys, if you have missed any of the class, previous class, same that I'm not able to repeat the concepts because we have got limited

[3:03:11] 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 that you go through the recording. So you'll find recording in the LMS portal.

[3:03:28] 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.

[3:03:43] 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

[3:03:57] 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.

[3:04:19] I'm okay with the other things. Yeah. So if I execute this 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

[3:04:35] data of a student with the 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

[3:04:50] listen to me. It's very simple. Now if I run it, it is throwing error. So you get run it, it is throwing error. So you get that 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

[3:05:04] data for all the columns of your table. So if you've got the full data then it 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

[3:05:19] 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

[3:05:34] 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

[3:05:52] 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

[3:06:07] wanted to explain you. I'm just sharing this code with you all. So, here's a code for creating the table.

[3:06:24] for inserting the data into the table. Yeah. 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

[3:06:38] 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 give my maximum knowledge to you all. uh share my max the maximum

[3:06:53] you all. uh share my max the maximum knowledge that I have with you all named kunal in this table I've just got four columns as you can see

[3:07:19] insert what subject but subject is not there Right. over here? Kunal, is that so? So, Kunal, we are going to look look into that also

[3:07:33] 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.

[3:07:53] 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? to the table. So what are constraints? So constraints are uh think of it they

[3:08:07] 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

[3:08:21] around these constraints. So the first or let's do one thing we'll one by one rather than going through the whole thing we'll go one by one. So the

[3:08:33] 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 I have a column ID column and I

[3:08:48] want to apply some rules. So we learn about auto atomicity right we learn about not auto atomicity sorry we learn about acid. So if you remember we learn learn about consistency. So means that you're you are basically applying the

[3:09:02] rules to your database. So here also we are doing the same thing. We have got 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

[3:09:19] 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.

[3:09:35] 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 means that you're making this column required. As simple as that. Yeah. If

[3:09:48] you enter any of the row, this column should have value. should have value. Okay. So we'll do one thing. We'll Okay. So we'll do one thing. We'll just give me a second. Yeah.

[3:10:01] just give me a second. Yeah. So we are going to create a table

[3:10:17] create a table. Now we are going to create a lot of tables.

[3:10:29] 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 multiple columns. So one of the column is ID and

[3:10:44] the second column is let's say name. Okay, I'm not going to make it a very Okay, I'm not going to make it a very big table. columns as not not null. How I can make that column as not null? So all I have

[3:11:00] 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

[3:11:16] 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

[3:11:32] created and I'll just quickly say insert into I want everyone to see my screen. If I give one, comma, any name for example, let it be. I am just giving any name for that matter. Doesn't uh matter what name

[3:11:47] I'm giving. Yeah. So if I insert the data, see it is getting inserted. Let me also show you the data. Select star from my students. So it's working absolutely my students. So it's working absolutely fine.

[3:12:03] 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

[3:12:16] maybe a. Yeah, I'm not giving the student name. Yeah, I'm skipping a or 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

[3:12:33] cannot be null and this will happen for the other column also for this column 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

[3:12:50] I have applied a constraint to this column both the columns that these 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

[3:13:02] doesn't allow null values. So I'll wait for a minute for everyone to quickly try 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.

[3:13:19] Would you understand by unique distinct values? Yes. So in very simple words if I have a column so I will not allow I will not allow

[3:13:31] 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

[3:13:43] 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

[3:13:57] so you want to make sure that every record that is being entered has a record that is being entered has a unique email or unique phone number. So here we have created a table user. It has just got one column. It's okay. We

[3:14:11] 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

[3:14:28] that the table has been created. Now if I insert this athegmail.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.

[3:14:41] 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

[3:14:55] constraint that you're going to see you're going to encounter it. So let me you're going to encounter it. 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

[3:15:08] 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

[3:15:22] 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

[3:15:35] 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

[3:15:49] 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

[3:16:03] it. I we have already discussed it. So I'll quickly redisuss it. So primary key I'll quickly redisuss it. So primary key is a uni unique identifier of each row. For example, if I talk about human beings, so I think our genetics, our

[3:16:16] 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

[3:16:32] 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

[3:16:47] uniquely identifying the row of data. So definitely 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 null as

[3:17:01] 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

[3:17:17] that names can be common then [clears throat] 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.

[3:17:31] 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

[3:17:45] that's what primary key says. Okay. There's one more thing that is in a table. This is very important. In a in a table. This is very important. In a table only one primary key is allowed.

[3:18:07] 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.

[3:18:24] 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

[3:18:40] have only one primary key so I'll just quickly take the code yeah

[3:18:56] 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

[3:19:08] 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 the first value this is the first value one and rai. So employee ID. This one is

[3:19:21] one and rai. 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 the name Reena. And if I give the same employee ID to it, Reena, it will

[3:19:35] 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,

[3:19:47] if I try to insert null in a primary key, again, it will give error. [clears throat] 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

[3:20:03] just make a small change over here. I'm just changing the name of the table and 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.

[3:20:18] 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

[3:20:35] 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

[3:20:49] 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

[3:21:03] and primary key? See first of all I can have multiple unique columns. 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

[3:21:16] 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

[3:21:30] you can make as many columns unique as you want. Also if you have a unique 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

[3:21:46] 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

[3:22:01] maybe I can give some uh maybe some value. second row also null then it will not be unique. Both both the rows are having unique. Both both the rows are having the same value null. So it can take one

[3:22:15] 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.

[3:22:35] when you have a composite primary key. So 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

[3:22:48] primary key. So we we are going to look into composite also not right now write down these notes the these points say I'm going to give you these notes

[3:23:00] 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. So you'll have everything handy. Now passport number is a primary key.

[3:23:16] Now I've got another table. This another table is storing the This another table is storing the passport number. numbers that we have. And for these different passport

[3:23:31] numbers, we also have maybe this is not a very good example. I'm so sorry. Let a very good example. I'm so sorry. Let me uh give you some other example.

[3:23:46] 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.

[3:23:58] In this we have got the details of the customer like ID, name, phone number etc. So ID is what?

[3:24:11] This is a primary key. This is a primary key for this table. It uniquely identify key for this table. It uniquely identify a customer. Okay. Moving ahead, we have a customer. Okay. Moving ahead, we have got the orders table.

[3:24:31] 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

[3:24:47] Yeah. Who made the order? So this customer ID So it means that over here I have got some data. Let's say I've got the data

[3:24:59] like I've got a customer 1 2 with name A and B. So this table now understand this and B. So this table now understand this thing. This table will have values um [clears throat] it have values from this ID column. So

[3:25:14] 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

[3:25:30] 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

[3:25:42] orders table. So what is the significance of the foreign key? Foreign key make sure 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

[3:25:57] 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

[3:26:10] 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

[3:26:22] 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

[3:26:39] primary key. This is what this is a 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

[3:26:54] 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

[3:27:06] any other value. Anyways, I will see the practical part of it and you'll 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.

[3:27:24] So you can see department and staff I've got these two tables. Department has got 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

[3:27:37] column quickly now I'm asking you such simple questions. answered. If you see here we are defining a

[3:27:50] 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 a syntax of it. So we are saying that we have got a foreign key. This is a foreign key.

[3:28:04] 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

[3:28:20] 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.

[3:28:34] 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 the 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

[3:28:49] name of the table and this is the name of the primary key. of the primary key. So it means it means that this foreign key is for this table and in this this column.

[3:29:06] 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

[3:29:21] 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

[3:29:34] name [clears throat] is ID 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

[3:29:47] 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 departments we have got staffing. So I've got staff ID 1 and this staff goes

[3:30:00] 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

[3:30:18] department table. This value exists in the department table in this column. But 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

[3:30:34] 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.

[3:30:50] will 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

[3:31:03] 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 student table. So I'll just quickly make a change to the name of this table and

[3:31:18] 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. can see that the table has been created. Now the I have given a check over here

[3:31:32] 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.

[3:31:47] Yeah, if it is less than 18, it will throw error. So if you're seeing the if if you're getting the error, this is how the behavior should be. Yeah. So you should definitely get the error. This is how we expecting it to work. So it

[3:32:02] 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

[3:32:14] 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.

[3:32:29] 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

[3:32:45] 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

[3:33:01] straightforward it is not difficult also just that you have to follow some steps. for everyone to quickly try this constraint. Check constraint.

[3:33:15] The next constraint that we have is default. So till now every column that we are we have created we are we were inserting the data in it. Now let's say that if we do not get any

[3:33:30] 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

[3:33:44] student table of some school which is in India. India. So here we have got a country column. So if we are not giving the value explicitly for the country it will

[3:33:59] automatically take India as simple as that. So basically we are giving a 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

[3:34:11] 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

[3:34:26] we write the code around it you'll able to understand. So let me take the code. 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

[3:34:40] 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

[3:34:54] 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

[3:35:09] 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

[3:35:25] 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

[3:35:38] because I executed it once it has taken status as pending. If you want you can 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

[3:35:54] first one it took pending because if you're not giving anything in very simple once you're not giving anything, it will take the default value. But if you give anything that it will take that value.

[3:36:06] 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

[3:36:21] ID. Usually what you do is you give your name, address, phone number, email id and you're done and dusted. So let's say Amazon wants to give you So let's say Amazon wants to give you some ID. Yeah, some autogenerated ID. So

[3:36:35] 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

[3:36:49] have got a column let's say the name of the column is ID. If I make it auto increment it means that automatically it will like the first record let's say will like the first record let's say take the value one. So the second record

[3:37:04] 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 second will take two. The third record will automatically take three. So the

[3:37:17] this column will automatically u generate the value for itself without user explicitly giving. Yep. And it will increment increment the value every single time. So again we'll understand

[3:37:32] it with the help of the example so that you have better idea. Now you can see 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

[3:37:45] 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

[3:37:58] 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

[3:38:10] 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.

[3:38:25] 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

[3:38:38] 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. So now if I insert more data let's say if I insert

[3:38:54] 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

[3:39:06] 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.

[3:39:21] 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

[3:39:35] 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

[3:39:51] 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 this is the syntax of it. You can give auto increment equals to five.

[3:40:04] 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 c we we can have uh a table auto increment column without having the primary key. So I got confused because

[3:40:17] 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

[3:40:31] 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 The first value it is taking five and then uh 6 7 this is how we are defining

[3:40:45] the first initial value and you can also change this uh 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

[3:40:59] 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 Yeah Rajpal if you delete the second row

[3:41:11] the next increment will be the next one. It will not be the previous one. Yeah It will not be the previous one. Yeah right it will always take the next one.

[3:41:25] 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

[3:41:39] 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

[3:41:52] 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.

[3:42:06] till some time back we had it in SQL server databases but not in MySQL. Now we'll learn learn about composite primary key. So what is

[3:42:18] 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 there might be something off with your with your syntax

[3:42:31] 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

[3:42:44] then we'll move to the composite. Now we'll learn about composite primary 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

[3:42:57] 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

[3:43:10] 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.

[3:43:28] let's say country. Yep. 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 about the country let's say the latitude longitude all of these things and then

[3:43:41] the code country code also I've got now I want to define a primary key I want to define a primary key well latitude longitude can also be a primary

[3:43:53] key because every country will have distinct latitude longitude let's talk about something else let's say um number of states. Yeah, something else or number of cities.

[3:44:06] 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

[3:44:22] 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

[3:44:34] 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

[3:44:49] that that's hypothetical I know. So what I can do is now I want to define a I can do is now I want to define a primary key.

[3:45:03] the combination of two column as a primary key. So I can say my primary key primary key. So I can say my primary key is name as well as country code. So combination of these two are my primary key composite primary key.

[3:45:17] 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.

[3:45:31] 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

[3:45:53] 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

[3:46:06] 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

[3:46:21] in a fix that from where should I get a column which is completely unique. Now I completely unique. Let's say order ID is also getting repeated. So um order ID is

[3:46:33] actually repeated sometimes. So let's say I make an order. I'm just giving you say I make an order. I'm just giving you one context of it. And in that order I am purchasing three products. You getting? So I'll have the order ID

[3:46:45] getting? So I'll have the order ID repeated. So 1 2 3 1 2 3 1 2 3 product 1 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

[3:46:59] 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.

[3:47:12] 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 yeah one. So the combination of these three should be unique. So if you see this is I'll just

[3:47:28] 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

[3:47:42] the order ID is 1 2 4 product is one one and the the quantity is different. So the combination of these

[3:47:58] 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.

[3:48:17] 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.

[3:48:29] 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 together will make the primary key. It will very well work. Now

[3:48:43] primary key. It will very well work. Now again see one is repeated but course ID is different. Yeah. So one one not two again it will work for me because the combination of two is unique. Always remember that the combination of is

[3:48:56] 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 insert and let's say the product or the student

[3:49:11] and let's say the product or the student ID is true 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

[3:49:25] 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

[3:49:41] 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

[3:49:54] last insert statement will not run. Yeah, it will throw error. it's not a new thing but we'll see that what all we can do with the select

[3:50:10] statement. So we start with the keyword select then we we were giving star from the

[3:50:22] name of the table right so star basically means that return all the columns. So star means all the columns

[3:50:34] 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

[3:50:48] that you want of the part of the output. So you can give the name of the column 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 a table

[3:51:01] 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

[3:51:15] 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

[3:51:31] 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.

[3:51:43] So we are going to recreate in case we so we have created students table. There's 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.

[3:51:57] 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.

[3:52:10] 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 data almost six rows of data in the student table.

[3:52:26] So I'll wait for a minute for everyone to have their student table ready. So I've got this data also in front of me. H the table the table structure the

[3:52:41] data that I have inserted into the table. Yeah. So as this is something that we have ran so many times. So I say select star from student. So this will

[3:52:55] 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

[3:53:11] let's say only return some selected columns. So what I can do is I can say let's say select name comma 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

[3:53:26] 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 columns. So I want everyone to try try this out.

[3:53:42] 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

[3:53:57] 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 to play around with a lot of code a lot of uh SQL queries after the class. Okay.

[3:54:11] 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 which all cities. So I can make use of distinct

[3:54:27] 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.

[3:54:42] 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 the data. You've got the table and you're simply reading the data.

[3:54:59] 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

[3:55:14] 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

[3:55:30] 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

[3:55:43] apply some maths. So what I can do is over here I can say maybe okay I'll just over here I can say maybe okay I'll just put like this and I'll say marks

[3:55:57] because let's say the marks are from not okay. Let's say the marks are 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

[3:56:11] 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

[3:56:26] 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. run this. So, see this column is there now over

[3:56:40] 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

[3:56:55] 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 am literally not liking this name. So you can give an alias to your column. So I'll just give

[3:57:10] 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 columns also. So I I can say as for example over here

[3:57:28] let's say as full name. So if I run this see this so the 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.

[3:57:41] 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.

[3:57:56] 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.

[3:58:14] definitely we are going to cover all those functions. You can see yep but it's not something more now till now what is happening is it is

[3:58:28] 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

[3:58:43] 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

[3:58:56] 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

[3:59:10] say let's say city also yeah every all I've got six students so it is showing 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

[3:59:26] 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.

[3:59:38] 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.

[3:59:52] 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 yes, so this will this will help us to filter.

[4:00:07] 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

[4:00:22] 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 u practice

[4:00:38] it. So let's say I want all the students who who have got distinction marks. So marks greater than equal to 75.

[4:00:56] 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.

[4:01:14] 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

[4:01:30] 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.

[4:01:42] 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

[4:01:57] comparison less than greater than greater than equal to less than equal to equals to so these are what these are compar comparison operator examples of the different different operators then you can give it a try

[4:02:14] operators then you can give it a try in the same way I can also have logical operators so logical operators let me maybe [clears throat] take take this query so I can say that I want all the students who belongs to city Pune 8.

[4:02:31] the students who belongs to city Pune 8. Yeah. And so here I'm adding a logical 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

[4:02:44] 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

[4:02:57] or and the last one is not. So here it is going to return us. It is going to return us the students going to return us the students who belongs to Pune as well as the marks

[4:03:12] who belongs to Pune as well as the marks are greater than 75. So this will return are greater than 75. So this will return us Ahmed only Amit

[4:03:26] 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

[4:03:41] 75%. So whether they belong to Pune or not doesn't matter. So either like I so a [clears throat] student who belongs to Pune also marks greater than 75%.

[4:03:54] 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.

[4:04:10] 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.

[4:04:26] 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 of all the three logical operators.

[4:04:44] would like to take two minutes of hold 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

[4:05:01] 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.

[4:05:17] minutes. You can just copy paste these queries. Yeah. Do not write the query. we'll look into the more operators that we have got.

[4:05:34] range and set operator very very important operator. important operator. So as a name says that let's say that u So as a name says that let's say that u you want to know or you you want to know

[4:05:47] 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

[4:06:02] enough space. Yeah. So I'll say select so and so from Yeah. So I'll say select so and so from student where? Let me remove this student where? Let me remove this marks

[4:06:19] between. So I I can use between yeah between let's say 60 and 80. between let's say 60 and 80. So this will return me all the students

[4:06:33] 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. and Anita. And these are the marks. So if I give 78.

[4:06:51] 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

[4:07:05] well as 78. So it will give you if you have any range you can use between operator. You can use between operator. can use between operator. Now Rajpal had a question. What if I I

[4:07:19] 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 be. Yeah I I let's say I got all the columns from student where

[4:07:41] 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 values. So let's say I'll give the value Pune,

[4:08:01] 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

[4:08:16] 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

[4:08:33] cities that ends with E something like that so I have got some pattern to match that so I have got some pattern to match so

[4:08:45] 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

[4:08:58] 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

[4:09:11] name starts from A. Yeah, name starts from A. So let's say I I'll just maybe 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

[4:09:26] 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

[4:09:39] 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

[4:09:53] 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.

[4:10:07] 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

[4:10:25] only one character. Yeah, only one. So here hyphen means character but only one character. Percentage means any number of character. Hear me out. Percentage means any number of character and any

[4:10:41] 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 PR why P is only one character before R and then any number of character after P R so this

[4:10:57] will return me this is returning me PR so this is what this is pattern matching so I'm doing the pattern matching using the modulus or you can say percentage the modulus or you can say percentage age and using underscore not hyphen

[4:11:12] age 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

[4:11:29] 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

[4:11:42] 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 s I t a

[4:11:56] rb 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 two in number not more than that percentage is like any number yeah one

[4:12:12] underscore means one character one character two underscore so this one and character two underscore so this one and this one so that's how it is working this one so that's how it is working so am I clear with the pattern matching

[4:12:32] simply matching the pattern using percentage and underscore Am I clear?

[4:12:50] you time. So we have got the last operator null operator. So let's say you operator null operator. So let's say you simply want to return the rows where maybe the column is not null. So the column is not null.

[4:13:07] column is not null. So I can simply say where name

[4:13:31] where name is not null. Or I can say where the name is none. 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.

[4:13:44] 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.

[4:13:56] 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

[4:14:11] two minutes. You can just copy paste and play around with it.

[4:14:25] So I'm assuming that you guys are going to practice after the class also. But to practice after the class also. But fine, I'll give you more time.

[4:14:41] 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

[4:14:57] simply filtering your data. That's what you're doing. If you've got composite primary key, how does that matter? Where doesn't care about primary key or want to filter the data and filter the data for you. So let's say you have got

[4:15:13] 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 something. So you're getting it has nothing to do with the primary key or

[4:15:27] nothing to do with the primary key or composite primary key. in order to filter the data. Now let's say and you're going to use it

[4:15:40] 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 thousand rows. You do not want to see only thousand all the thousand rows.

[4:15:55] 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 or let me put back because I maybe I'll show you some some some

[4:16:09] I maybe I'll show you some some some other thing as well. So I'll say limit 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.

[4:16:24] 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 MySQL.

[4:16:42] number of data. It will return the number of [clears throat] rows. where clause in it. It's not that you cannot add v clause. So I can also say

[4:16:57] let's say we marks greater than marks greater than let's say 75 and then I can limit my let's say 75 and then I can limit my data. So I can also add clause. So first

[4:17:10] data. So I can also add clause. So first it will filter the records will simply give us the top two records, the top two rows. Anyone

[4:17:24] 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 to use a lot in order to understand the data.

[4:17:40] 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

[4:18:00] ordering something. So it is to sort the result. Yeah. Now you're looking into the result with select query. You're basically quering the data that your basically quering the data that your table is holding. So while u maybe when

[4:18:15] you're seeing the result you want the result to be sorted okay you want the result to be sorted via some column. So sorting can be definitely ascending.

[4:18:30] 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 Now I want to see all the students but I want to sort my students as per their

[4:18:43] marks. So what I can do is I can say order by the order by marks. Let me execute both the queries. Yeah. 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

[4:18:59] 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

[4:19:15] 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 So you can see that Surish has got minimum marks. Priya has got uh like you

[4:19:28] 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

[4:19:40] 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.

[4:19:53] So it means that I'm going to sort the data in the descending order. Now Rahul data in the descending order. Now Rahul uh is being seen first because Rahul has got the maximum marks. So descending means

[4:20:09] 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

[4:20:23] 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

[4:20:40] the last class and mostly we are going to have an extra So instead of seven we are going to have eight class mostly and I'm going to cover the project in the last class. Done everyone.

[4:20:53] Done everyone. So you can also sort the data 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

[4:21:06] to sort the data on two columns. I also want to sort the data on marks. So marks 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 [snorts] city as well.

[4:21:20] [snorts] 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.

[4:21:32] 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.

[4:21:47] 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

[4:22:03] sorted as per the marks. So let me show you the result. Yep. 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.

[4:22:19] 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

[4:22:33] 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

[4:22:49] 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

[4:23:03] sorting two level sorting. The first sorting is on the city. By default it is given anything. You can always give something over here. something over here. Now once the data is sorted by city.

[4:23:18] 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

[4:23:32] the next level of sorting that is marks descending. So it will check for the maximum marks. Anita has got more than na. So that's why we are seeing Anita first and then Niha. So in this way you can do the sorting on multiple columns

[4:23:48] 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

[4:24:04] Pune Huga 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

[4:24:16] marks. Yeah. And then you're sorting by city. So that will not make sense.

[4:24:28] 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

[4:24:43] well as to SQL. So I want everyone to try this out. So I want everyone to try this out. Try the sorting by multiple columns.

[4:25:05] 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.

[4:25:18] It can have the combination of multiple clauses. create the database and then we are going to use that database for our

[4:25:31] 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

[4:25:45] 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

[4:26:01] 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

[4:26:18] 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.

[4:26:33] 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

[4:26:48] with it. Yeah, that was a different table. That table we created in order to learn about auto number constraint. But this is uh if you want you can drop your existing table and you can recreate

[4:27:03] your existing table and you can recreate this table to avoid any confusion here we do not have any auto number. If you see

[4:27:15] 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

[4:27:29] 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 query is you should know that what is the order of

[4:27:43] execution. So when I say order of execution what I mean to say is let's execution what I mean to say is let's say I write a query select star from table where let's say um city equals to Pune

[4:28:03] and then I am also having order by let's say order by sub column and then limit some number. So if I've got a query like

[4:28:15] some number. So if I've got a query like this, now this is very very important. You should definitely know the order of execution. So when I say order of execution. So when I say order of execution, I mean to say how this query

[4:28:27] execution, I mean to say how this query will be executed by the database DBMS. will be executed by the database DBMS. So see first from is uh executed. Yeah, from is executed. So basically it will check that from which table you are

[4:28:41] querying. So it is going to execute this from from this table. Then it will then what it will do? It will filter the data. Now your table might have 100

[4:28:53] data. Now your table might have 100 records, belongs to Pune. So it will first it will go to that

[4:29:06] table. So from which table? Then from this table it will it will execute the where part of the query. So it will simply simply fetch these 10 rows. Yep. Then it will

[4:29:19] uh go to the select part. So after that it will go to the select part. So in these 10 rows like you're fetching 10 rows. We get that what all columns you want to se select right? Select is all about what all columns. So right now in

[4:29:32] this query I'm selecting all the columns. So then it will select the columns. Now it will get some 10 rows and uh the selected columns and then order by after that once it has got the result it is going to sort it and then

[4:29:48] it is going to give you the result with whatever limit you have put in your query. So this is what this is the logical execution order. Now you should definitely remember this execution order. Again I'm repeating

[4:30:03] when you start writing complicated complex queries you should know that how things are happening in the background. So I repeat first from is executed and that makes sense also right that which table we are actually looking into then

[4:30:17] we filter the data and then we filter the columns once we have the filter row with the rows and columns. Yeah. So once we have the filter rows and columns then we have the filter rows and columns then on those we apply order by. So DBM is

[4:30:33] apply order by and once it is ordered by then it applies limit and that's what you're going to see that it will show you the first two records after order by. So this is a very important thing and you should

[4:30:47] definitely know. [clears throat] Okay. Now we're going to move to the next topic that is aggregate functions. So when I say aggregate aggregate the word aggregate what comes to your mind

[4:31:02] average all together very important right uh some mathematical com uh calculations calculation. Yes. So aggregate aggregate this is very important all together is a very important word. So it performs so

[4:31:18] aggregate functions. So they perform maybe I'll write it over here rather than writing it on the notepad. So it performs calculations. Please ignore my writing on multiple

[4:31:33] Please ignore my writing on multiple rows. So it will perform the calculation on multiple rows and then return

[4:31:46] multiple rows and then return a single value. a single value. If I give you one example, let's say I've got a table.

[4:31:58] Just give me a second. I've got a table and in that table let's say like okay there are some columns and then I have a column profit

[4:32:11] columns and then I have a column profit yeah profit now in this column I'm having values like 100 200 then let's say 300 yeah these are the three values so with the help of aggregate see it will perform calculation on multiple

[4:32:25] rows and return a single value so if I perform count what will happen it will give me the count so how many the number of rows that I have so it will give me three then sum so what it will do it is going

[4:32:40] then sum so what it will do it is going to sum all the numbers so let's say I'm applying this function on all the rows of my table so it will perform the calculation on multiple rows always remember so it will give me 600 so

[4:32:54] I'm not going to do that I think it's going to be around I think 200 only. Yes. Then minimum. So it will again check for the minimum value and maximum. It will check for the maximum value. So these are the aggregate functions. These

[4:33:09] are the five aggregate functions. Now you already understand what is sum, average, min, max. Yep, it's a very simple one. Now we're going to look into the code for the same. So you can see it is showing me that I've got six records

[4:33:22] in total. Yeah, I've got six records in total. Now instead of this now hear me out instead of this I can also give some column name. Let's say I give a column column name. Let's say I give a column name um name or marks.

[4:33:37] So I can do that also. So what it will do now hear me out. Yeah what it does because this is very important. So it will count all the non-null value in this particular column. So if I go over here right now if you see marks they

[4:33:54] this is null this is not null this is null this is null this is null and this is null so it is going to count all the non-null values so basically this will give me six yeah this will give me six now I'm saying again I'm repeating it

[4:34:09] will count all the non-null values so let's say I insert one more record yeah so you can see that I'm inserting one more record okay any any name for that matter and it's just that I'm going to give null

[4:34:23] it's just that I'm going to give null over here. You're getting I'm in case of student ID 7. Let's say I do not have the marks right now. So I'm just giving null value over here. So I'm going to execute this got successfully executed.

[4:34:40] Now if I count this thing now you tell me what it will give what result it will give me. This will give me what result? What number will it give? Give give me seven records, right? Seven records because

[4:34:55] I've got seven records. No. Why error? Yeah, it will give me the number of rows. But as soon as I give marks, what it will give me is it will give me six. So why it is giving me six? Because it will count only the not yeah not null

[4:35:13] it will count only the not yeah not null values. So it will count all of these. But this it will ignore because marks is null over here. But if I go ahead and do me do it for city.

[4:35:26] Now tell me for city what it will give me. Come on. It will again give me seven. Yeah because I don't have any null city. All the seven rows have value in the city column. So definitely everything will be

[4:35:41] counted. So in the same way I can use other aggregate function. For example, sum. Now, definitely I cannot do the sum of city. I have to provide some numeric of city. I have to provide some numeric column over here. So, I'll provide maybe

[4:35:56] marks. So, this will do the sum of all the marks. All the do the sum of all the marks. All the marks that I've got. Yeah, sum of all the marks. In the same way, I can run the other

[4:36:12] ones. For example, average. So this will give me the average marks secured by the students. Then I can do min the minimum marks. So I think the minimum marks is yeah 55. Surish and I think the maximum marks is of Rahul. So I can also run for

[4:36:28] maximum marks. So you can also quickly try this in the same query. You can make the changes and you can just have a look how it works. you can just have a look how it works. It's a very simple concept.

[4:36:47] so this data is already the part of your table. Yes, your table already has got six rows. Now, if you want to insert more rows, then you have to rerun the command with more data.

[4:37:00] So, you can't give values in continuation to it. But see, if you but you can try that. So, you will have duplicate data. So if you run this three times, so you're going to have this data three times, the same duplicated data

[4:37:14] three times. So whenever you have new data, you have to run the whole command like this. So now we're going to learn about group So now we're going to learn about group by it's a very very important

[4:37:27] by it's a very very important concept in SQL. So what do you mean by group? When I say group, yep, you we all understand what the group means. Now here definitely we are having group by so it means that we are grouping

[4:37:41] something and what will we will group in SQL that uh of course when it comes to SQL we are going to group the rows yeah we are going to group the rows right so let's say that you have got right now uh we know we have got

[4:37:57] students right and uh we have got the data of students so let's say I want to group the students let's say I've got a job to group the students. Now what I'm going to do is I'm going to group the students as per the city. So what I'm

[4:38:12] going to do is I'm going to create the groups as per the city. Pune, Mumbai, Delhi and Chennai. Yes. So I'm making

[4:38:24] the groups as per the city. So I'm simply taking all the distinct values in the city column and I'm making them as one group. So this is one group. This is second group. This is third and this is fourth group. Then once I know that

[4:38:39] these are my groups. Yeah, these are my groups. Then what I I can do is I can perform some aggregate functions on the groups. You getting? So first you're going to group your data as per some parameter.

[4:38:54] In my case, I've decided the parameter to be city. I'm grouping the data via city. So I'm picking up all the distinct cities that I have in my data set and I'm grouping I'm going to group my rows [clears throat]

[4:39:08] as per the city. The next thing that I'm going to do is once the grouping is done I can perform some aggregations on the group data using the aggregate group data using the aggregate functions. So for example I can say that

[4:39:23] maybe I want the count of the Pune students. So it will simply help me with count. Yeah. So two for Pune, two for Mumbai, one for Chennai and one for Delhi. So that's how it is. So I'm I can perform any of the aggregate function. I

[4:39:39] can also perform sum. So B what I can do is I can sum the PN students numbers. Yeah, I can sum their marks and I can literally write it over here. So I think that's going to be around 150. Yeah. So basically I'm going

[4:39:55] to sum. Yeah, my calculation might be wrong. So understand this thing with the help of group. So with the help of group by I can group the rows with the same value. I can group the rows with the same value in one or more column. So I

[4:40:11] can group the rows. That's what I did. Yeah, I was grouping the rows one or more column. So I group the rows with Pune or sorry with city, right? I can group it with more columns also. I'll show it to you when we write the code.

[4:40:24] and then perform. So I'll quickly show you the syntax and then we'll write the uh code around it. Now group is very very important for your interviews also. Please make sure that you understand and it's a very

[4:40:38] simple thing. It's a very simple function. You write two three queries and you're done and tested kind of. So anyways uh so the group by syntax is so first now uh hear me out this is very important. You you start with select

[4:40:54] then you have a table name that select from so and so table. Now see you're from so and so table. Now see you're going to group by some column right? So here you'll give group by and you'll give the column name that

[4:41:08] you're grouping by which column? Yes. So I can give city I can give multiple columns. Let's say I'm grouping by city as well as um maybe something else also. Let's say as as well as area. Yeah, I

[4:41:22] show show that to you when we write write the code. Okay, now in select in select this is very important. Yeah, in select we give the column name. Now this

[4:41:35] column names, this column name, let me clean this off. This column name should be same as that of this column. So you're getting if I'm grouping by So you're getting if I'm grouping by city then this can be city and this

[4:41:50] cannot be any other column. So I cannot make it marks I cannot make it name. So I'll show that to you. So hear me out right now. You may not understand 100%. But if you don't listen to me because you're not understanding 100% then

[4:42:04] write the code also. So please make sure that you listen to me. Once we write the code I'm going to reiterate and you'll understand. So in select now this is a rule. Yeah this is a rule. So in the select statement you can have

[4:42:21] So in the select statement you can have either the group column it means the column with which you have grouped your data or you can have the [clears throat] aggregated column. Yeah you're going let's say I want I'm

[4:42:36] Yeah you're going let's say I want I'm counting or I'm doing the sum of marks. So I cannot have any other column. I cannot have any other column. Not cannot have any other column. Not possible.

[4:42:51] columns. Okay. Apa your question will be answered in some time. I've already answered this question but yes your question will be answered again in some time. So uh now we going to write the queries around

[4:43:05] group by so that you understand the concept. >> So we have got the same data. I want everyone to see my screen. So here what

[4:43:17] everyone to see my screen. So here what I'm going to do is I'm going to I'm going to do is I'm going to let's say city comma and I'll say count

[4:43:33] from student and I'm going to group by. So group by as I told you this the same example I've already explained you that I'm grouping the rows by by a city and then I'm making the count on the top of it. So if I run it you can see that it

[4:43:47] is showing me all the distinct cities and the number of students. So if you want you can give an alias to your column. Yeah as number of students you can give some alias. Yeah, for the better you uh

[4:44:02] uh so that your column has a proper name. So anyways, you can see that this is done. Yeah, you can simply say so here I've given city I group by city and

[4:44:14] this is the aggregation. Now let's say that you also want the name of the student. So let me try that. Yeah, I'm giving name also. Now you can see I have not grouped by name. I'm haveve not done anything with the name.

[4:44:28] haveve not done anything with the name. Can you see I'm getting the error? that every column in the select must either be so in group by. So it means

[4:44:42] either be so in group by. So it means whatever column you're writing in select either that column should be in the group by or it should be the aggregated column. Anything else will not work because I've given name. I'm not

[4:44:56] grouping my data via name. So it is throwing error. I I can maybe use marks any any other column. It will not work because I have not grouped my data via marks. So this is a rule. This is a thumb rule of group by and you should

[4:45:11] definitely remember this very important for your interviews. So yeah. So uh I'll just do one thing. I'll wait for a minute. I want you guys I'll wait for a minute. I want you guys to try running this query

[4:45:28] the different colors on the group by what all we can do other than this.

[4:45:41] So if I don't give it the column this new column account sorry count will take its name like this. But I do not want that. Yeah, I want to

[4:45:53] that's what I've done. Having I'll come to that. Sedart to that. Sedart I'll come to that as well.

[4:46:06] without the aggregate function uh why would you do it like you can do it but there should be some reason behind it right why you're grouping your data you might want to do something that's why you're grouping sanscar

[4:46:26] that I'm getting everyone now let's say that I want only those uh values or only those cities where the number of students are greater than one.

[4:46:42] So I've got a I've got a filter condition. I do not want all the rows rather I want only those cities where the number of students are more than one. So if I use where over here see if I use

[4:46:55] So if I use where over here see if I use where and if I say where let's say count star greater than one it will not work you can see that it is showing error. So when you whenever you want to filter your group data this is what this is a

[4:47:10] group data thumb rule you have to use having. So with the help of having you can filter your group data. Very important. You cannot use where in order to filter your group data. In order to filter your

[4:47:26] group data you have got another keyword, another uh clause that is having.

[4:47:41] that of where. It's similar to where just that where we use for the regular data and having we use for the group data to filter the group data. Now I otherwise I'll get very less data already I've got very less data.

[4:48:03] allow me a minute. So uh I just finished finished the explanation. So I told you that you can also group by multiple columns. So let's say I'm grouping my columns. So let's say I'm grouping my data via city as well as via grades.

[4:48:18] So it will group via city. So first it will group via city and in the city it is going to pick the grades. It will pick the grades. Yeah, it will pick the grades. So Mumbai I've got like three and B everybody is B. So let me show you

[4:48:34] the result and I'll just do one thing. I'll also show you the grade over here so that you get to see how things are happening. See, [clears throat] so what it is doing is

[4:48:46] we are saying that group by city as well as grade. So how things are happening over here. So it will take let's say Pune. Okay. So it will take Pune. So these are for Pune. Yeah. These two. Now

[4:49:02] definitely both both uh for Pune we have got two distinct grades. So it will make this as a one group and another group as this. Okay. Now let's talk about Mumbai. Yeah. Mumbai. So Mumbai if you see

[4:49:18] Yeah. Mumbai. So Mumbai if you see Mumbai. So 1 2 3 but all have got same grades. So it will just take one group out of it. So you can group via multiple

[4:49:30] columns. So first it will group via city and then it will group via the next column. So this is how it will work. Yeah, Mumbai has got three. So it will three. Okay. Then anyways, Delhi and Chennai there only one records. So this

[4:49:46] is how it is going to work. So first it will pick Pune and then the distinct value. So Pune and the distinct value of the grades. So if you see over here,

[4:49:59] for Mumbai because all the Mumbai students have got B-grade. So there's only one row for Mumbai but Pune Pune students have got a grade as well as C grade. So that's why there are two two rows of data. So I want everyone to try

[4:50:17] this out. Let me quickly answer the questions on the chat till then. Okay. [clears throat] So AA has got the answer I think from Shellpa.

[4:50:42] See you're saying Sheila having count marks what you have to give let's say greater than zero or so and then why descending there's no order by why have you put descending descending you can put only when you're ordering

[4:50:57] you can put only when you're ordering your data and uh the reason why I'm showing you this example is so that you get ready

[4:51:11] for your interviews So uh let's say that okay what you have to do is I'll just give you one uh one use case. So here's a use case that you have to ignore the students with grade

[4:51:26] with grade D. ignore the students

[4:51:38] [clears throat] show cities show cities let's say with average marks greater than 70 now this is a requirement ignore the

[4:51:53] students with grade D so we are do not So basically we do not want grade D students to be included in our calculation. And then I want to show all the cities again all the cities or the four cities that I've got but

[4:52:09] [clears throat] u the cities with the average marks greater than 70. So I'll do one thing. I'll just wait for a minute. I may not give you a lot of time but I'll just wait for a minute and

[4:52:21] I want you guys to try it by yourself. I'll give you a hint here. you are going to use a combination of where as well as having. So I repeat, ignore the students with grade D. So you're going to use where as well as

[4:52:37] you're going to use having because you're filtering on the table level, you're filtering on the group data level. Here you're doing two level of level. Here you're doing two level of filtering. So just give it a try.

[4:52:56] So you have to go step by step. Now a query can be written in multiple Now a query can be written in multiple ways. So it's not that

[4:53:10] always works like that. So here let me say okay I'm removing

[4:53:31] this will give me all the cities with their average marks. But what I have to do is I have to ignore the students with grade D. So this would have also grade D. So this would have also included the students with grade D.

[4:53:44] Mumbai students with grade D. So I do not want that. So what I'm going to do is I'll say where

[4:54:03] so while quering see while quering I'm saying that from this now here order of saying that from this now here order of execution come

[4:54:17] where the grade is not D. Yeah. So we are getting only those students the grade is not D. And now we are group making the group of those students. We making the group of those students. We are calculating the average and we are

[4:54:30] are calculating the average and we are simply saying where the average marks

[4:54:48] are before grouping the data. Before grouping the data, we are getting only that that data that is required. In our case, we are getting only those students whose grades are not D. Yeah, we are getting all the students

[4:55:13] and then the group data. Now this is going to give me this group data right. going to give me this group data right. So I'm going to I'm simply filtering it that I want only those where the average marks is greater than 70. So this is how

[4:55:27] it's going to work. So I just share this with you. You can just have a look run with you. You can just have a look run it for a um minute and then we'll move group by. Group by is very important. And I've shown you all the possible

[4:55:39] colors of group by. But as I told you SQL or for that matter any coding language is like math. So more you practice the better you become at it. So I'll give you some playlist also from where you can practice.

[4:55:58] line. And if you're running the query, please make sure that you you highlight please make sure that you you highlight or you select from line number 35 to 38. Also, in having average marks, the greater than or smaller than sign. Yeah,

[4:56:12] greater than or smaller than sign. Yeah, the greater than sign is missing. On line number 38, the greater than sign is missing.

[4:56:28] functions. So see guys in order to perform anything yeah in order to perform any task. So those who are coming from the coding background they will understand it what functions are and those who are not. So

[4:56:43] I'll try to explain it to you in a very uh naive way. So let's say in a very uh naive way. So let's say in a very layman terms. So let's say that you want to perform some some some job. Yeah, there's some action that you want to

[4:56:57] perform. So in the coding world, one way is that you write the whole thing. Yeah, you write the whole thing, the whole code in order to perform this action. The other way is

[4:57:12] that there are some pre-built there are some pre-built it means built by some pre-built it means built by somebody else functions or methods. It's one of the same thing available. So you can use them and you

[4:57:27] can simply make your life easy. So [snorts] let me explain you uh it uh to you with the help of one real life you with the help of one real life analogy. Let's say that

[4:57:41] you want to cook pizza. Yeah, you want to cook pizza. Now, in order to cook pizza, one way is that you do everything from scratch by yourself. You make your own base. Yeah. You need your own dough. You make your

[4:57:55] own base. Then you make your own sauce. Yeah. And then you're doing everything by yourself. So this is like you're doing everything by yourself. The other doing everything by yourself. The other way is that you know that the bases are

[4:58:08] readily available in the market. You go and buy that base and use it directly. So definitely it makes your life easy right definitely you'll not able to customize much you know this is what it is and this is what you're getting and

[4:58:20] fine if you're okay with it you can use it and you can also buy sauce uh in from the market. So here also it's the same thing. Now this explanation I've given for the people who are not coming from the coding background. So what are SQL

[4:58:36] functions? Think of it that these built-in Yeah. got a built-in SQL functions. Think of it as a ready-made pizza base where somebody has already

[4:58:48] written the code for us and we just have to use it in order to get our work done. to use it in order to get our work done. So, uh in SQL we have got a lot of built-in functions. So, we have got a huge list of built-in functions. We are

[4:59:03] going to cover some of the functions in order to like some of the important functions. Now with the help of functions what you can do you can manipulate your data you can perform calculations you can format output so

[4:59:17] calculations you can format output so manipulate the data means let's say um manipulate the data means let's say um fine so let's say um fine so let's say um for example I've got this name tikka so

[4:59:30] I can manipulate it and I can make t in caps and I can make the rest of the letters in small I can perform calculations. So, have you used any of the SQL built-in functions in today's class?

[4:59:46] class? Can you So, any of the SQL functions just told on when I gave only aggregate I got the error.

[5:00:08] So now uh see I told you that you're missing that greater than equal to sign. So if your question is not answered you can park it maybe. Yeah I'll just take it at the end of the session. So you can rerun the same query and we'll see what

[5:00:20] is the problem. Now these are clauses t these are clauses any of the built-in functions that you can remember that has made our life easy in today's session. We just directly use them

[5:00:35] in today's session only. I've got answer also on the chat. Yes, count. Did you write any code for it? Yeah. No. So there's some code written behind and we

[5:00:48] are simply using it in in order to count the number of rows, right? In the same the number of rows, right? In the same way sum, min, max. So there's some code written, right? It's just that we are saying min it is not going to calculate

[5:01:03] might be some there there is some code written behind the scene. So we are simply using these functions directly and making our life easy. In the same way you can format the output with the help of the function. So when I say

[5:01:17] output it can be anything. Let's say you can format your date. Yeah, your date is in this format. Maybe you can format it in maybe some other format. So you can do all of these things. So basically SQL function it takes the

[5:01:33] input it process it and it gives the output. already told you. Yeah, you are using something that is already built.

[5:01:48] So this will definitely uh make your life easy. Yeah, you don't have to write the complex code, complex logic. Definitely your code will be cleaner. Yeah, because you're just writing one

[5:02:01] line of code rather than like writing 10 line of codes. It will definitely save your time and efforts. So anyways, this part I've already explained you.

[5:02:15] This is very simple. Now in SQL we have got two type of functions. One is single row function and the other one is aggregate functions. Now hear me out. So

[5:02:28] single row function says that I'm going to work on one row at a time. For example, yeah, let's say I've got a table. Yeah. And this table has got some data. Let's say it has got name. And I'll just quickly write some name. Let's

[5:02:44] say it has got name Mark John John Monica. going to work on one row at a time. So let's say uh what I want to do now there

[5:03:00] is one function maybe I'm going to talk about that function later but let's say uh what I want is I want this all these names to be in capital letter yeah and to be in upper case. So we have got a single row function like uppercase

[5:03:14] function which will work on each row. So it will make this as mark in upper case. This is again upper case and so on and so forth. So I repeat what

[5:03:26] does it mean? It means that it is going to work on each and every row and it will give you the results. Yeah, it will give you the results each and every row. It doesn't depend on the other row obviously. See if I make I'm making it

[5:03:40] in capital it doesn't matter what what the value the other row is holding. So in case of single row function, it works individually on each row of data and it

[5:03:53] is totally independent of the um other rows. Fine. Now the other type of functions that we have got are aggregate functions. Now this you have already seen, we have already worked on it. So

[5:04:08] aggregate function says again let's say I've got some data and let's say I've got some numbers. Yeah. So aggregate function says that it works on multiple function says that it works on multiple rows and it gives you single value.

[5:04:26] it will work on the multiple rows and it will give me a value. Six average it will give me a value count it will give me a value. So it works on multiple rows. So it is dependent on all the rows in order to give you the values. Whereas

[5:04:40] single row function works on each row and give you the value. It is independent of of the other rows. You can just quickly read this slide and again [clears throat] if the things

[5:04:52] are not clear to you once we do the practical it will be 100% clear. Still not clear just try going through the recording once. Not clear ask me again. I'll reexlain it to you at the end end of the class.

[5:05:09] But if you're listening to me 100% then I'm sure things will be definitely clear learning like once the whole topic is done. like once the whole topic is done. Now SQL functions are divided into

[5:05:24] single row functions and aggregate functions. So for single row functions we have got numeric functions, string functions and date functions. And we going to look into all of these functions one by one. For aggregate

[5:05:38] functions, you have already seen this part. Yeah, we have got regular aggregate functions. You have already seen this. And then we also have got window aggregate functions. So this we are going to look into I think if

[5:05:52] possible some of it today otherwise we'll look into over the next weekend. So today our agenda is to learn about single row functions. Yeah. all of these three. So we'll first learn about the numeric function. Now as the name says

[5:06:07] numeric functions, it means that these functions functions can work on numbers.

[5:06:19] literally a lot of maths with numeric functions. So we'll start with a numeric function. And now we need more data because we are playing with numbers. So for that let me

[5:06:35] take more uh yeah so in student table see I'm going to insert more data. So I'll just run this insert command so

[5:06:48] that I've got more data. Now I've got almost 12 rows of data. And also I'm going to insert one more table that is employee.

[5:07:00] employee. So I've got two tables now. I've got number of rows in the student table and then I've got the employee table.

[5:07:14] Fine. So I'll just give you all this code. You can run it and I'll just wait for a minute. And it's very easy. These functions are most of these functions are very easy. It's just that we have got some

[5:07:30] functions just like let's say you buy microwave or EC. So somebody has already done the engineering. Yeah. It's just that you should now your job is to learn that how to use it. So here also we are going to do the same thing. We've got a

[5:07:46] learn about few of the important functions and when you're in the projects you may encountered some requirement where you might have not seen or worked on that function in the past. So we always

[5:08:00] Google we always try to find the solution we see if you have anything and solution we see if you have anything and then we work on it. you should have the data ready in order to run these functions run this code.

[5:08:17] Now we'll go one by one. See I cannot write or type these queries. If I start typing these queries then I'll able to cover very less functions. So I'll go one by one and I'll I'll also give you this code. So let me give you this code.

[5:08:32] this code. So let me give you this code. Okay. I'll give you one by one. employee table. I've got the employee table and it has got a lot of columns. So let me show you the data over here. Just give me a second.

[5:08:49] So you can see that we have got the employee table, employee ID, employee name, department, the salary of the employee, the bonus that the employee has got, the city employee belongs to and the joining date. So these are the

[5:09:03] and the joining date. So these are the this is the data that I've got. Now what I'm going to do is I you can see the salary right now. Yeah, the salary right now. So I'm going to round off the salary. Yeah, I'm going to round

[5:09:17] off the salary. So for for rounding of the salary, I'm using the round function. So here I'll show you the original figure, the original column with its values. Also I'll show you the column

[5:09:29] with the rounded salary so that you understand that what value we are getting. You can see over here. So rounding what does rounding means? Yeah, basic maths that what we have learned in school. So this is a salary and we are

[5:09:43] rounding it. So it uh get rounded off to the nearest number. So right now it's 75 the nearest number. So right now it's 75 so it will round off to 33.40 so it will round off to 45. Yeah. Uh 0.9 it will round off to Yep.

[5:10:00] 0. So in this way rounding I hope that you understand what rounding is. It's a maths that we learned in the school time. So during our school so school time. So anyways, so rounding is done. Now let's say that we want to do the

[5:10:15] rounding but with some decimal. Yeah. So right now it is removing the decimal. But let's say I want to do the rounding but I want one decimal. So here in the round function I can pass salary. This is the column that I want to round off

[5:10:32] is the column that I want to round off and this is a decimal. So if I run this you can see so it is again rounding off to one decimal place. It's a very simple one guys. Very simple maths. Okay. Now I want you guys to

[5:10:48] guess. So I want the class to be a little bit interactive. So I want you little bit interactive. So I want you guys to guess what seal could be.

[5:11:00] seal English word. So what this function could do for us now this is not a comment. So ideally the comment is written like this and that's why it is throwing error in each and every line.

[5:11:15] and every line. So it should be like this. Yeah. Upper something related to upper value. Right. So see uh rounding off

[5:11:32] value. Right. So see uh rounding off what does it do? It round off the number to the nearest number. So it can be upper also it can be lower also. So whatever is the nearest number. Now let's say that you want to round off and

[5:11:46] you simply want the upper number not the lower number. Yeah you so you do not want the nearest lower number. You don't care about the nearest lower number rather you want just the upper number. So it will give you the upper number. So

[5:12:00] every single time it will give the upper number last if you see 4 4 5.40. So if I talk about the lowest number no lowest nearest number. So the lowest

[5:12:12] nearest uh near number for this one is what it is 45 right but because we have what it is 45 right but because we have run sealed it is over here giving 46. So I hope that you understand. So for example if I give 1.1 round would have

[5:12:27] example if I give 1.1 round would have given one. Yes seal will give two. If given one. Yes seal will give two. If I've got let's say 1 or 2.3 round would have given me the nearest number but seal would have given me the

[5:12:41] number but seal would have given me the closest uh upper number. So like this closest uh upper number. So like this h fine moving ahead. Now you can also

[5:12:53] give the absolute. So you know what absolute is. We learned in school what absolute is. So absolute basically removes a negative. So if I've got a negative number, if I give absolute to it, it will give me the positive number.

[5:13:09] So here also it's doing the same thing. We are simply subtracting bonus with 5,000. Now this may result in the positive as well as negative number. But because we have applied the absolute function, it will simply convert the

[5:13:23] negative number into the positive number. So very simple one. Now moving ahead, we have got mod. So Now moving ahead, we have got mod. So what is mod?

[5:13:40] What [clears throat] is mod guys? What is mod?

[5:13:52] function, we first say that okay, I want see what is mod is a remainder. So when you're dividing two numbers, there are two things. One is the number

[5:14:04] and the other thing is you're dividing it by what? So we call it as dividend or divisor something like that. I forgot that but yes. So let's say I want to that but yes. So let's say I want to divide 100 by two. So this is the number

[5:14:18] number that you want to divide this number with what? So here I'm dividing number with what? So here I'm dividing all the salaries by two. So definitely now basic maths if you

[5:14:32] divide any number by two either it will give you zero remainder or one remainder and that's what we are getting. So this is the basic maths.

[5:14:49] right? Modulus. Just give me a sec. Uh just give me a second. No. So, uh that's what I told uh Vya. This is how we can write the comments

[5:15:02] This is how we can write the comments in my SQL. You can use hash. nothing. It is giving you remainder. So

[5:15:16] we have got the nu uh numerator and then we have got the denominator. Something like that which I totally forgot. Fine. Moving ahead. Power.

[5:15:29] Everybody understand what power is? Yeah. Maths. A lot of maths. 2 to the^ Yeah. Maths. A lot of maths. 2 to the^ 3. Yeah. 3 to the^ 2. So again you have got some two numbers involved in order to calculate the power and that's what

[5:15:41] we are doing over here power. So we are what we are doing is salary to the power of two. So this is going to calculate the salary to the power of two. So if I the salary to the power of two. So if I run this

[5:15:58] because we are taking the salary and we are you know multiplying it by itself. So yeah, so these are the some of the important

[5:16:10] numerical functions. We have got a lot more numerical functions. So you can explore more whenever you have some time. So what you can do is but if you don't explore also that's perfectly okay. I would suggest you to rather

[5:16:23] focus on the interview questions and focus on I'm going to tell you we have questions. So from there you can learn. So don't get into all these functions So don't get into all these functions and all because see if you try to mug up

[5:16:37] to forget and nobody expects you to remember all those complex functions. There are some basic functions that anyways you'll able to remember if you practice. So you can just try my SQL functions.

[5:17:01] of MySQL and here you'll find mediate functions dunky number of functions. Yeah, you can see over here. functions. Yeah, you can see over here. So for example,

[5:17:20] just give me a second. Yeah, built-in functions. are you can see that we have got so many functions. But don't break your head into these functions otherwise um you'll get super

[5:17:34] demotivated. Nobody asks these functions. use mod. Not power much but mod sometimes we may

[5:17:47] use. For example, you're calculating there. There are some calculations that you're doing. So you may want to remove the negative numbers. I'll share this link with you all. But again guys, please do not get into all

[5:18:02] these functions because there are lot of functions and if you start looking into demotivated. Nobody asks these functions. You should know some [laughter] some important functions and that's more than enough.

[5:18:16] practicing in the class, I'll give you a few of the links. You can use lead code in order to practice and that's more than enough.

[5:18:36] Fine. So maybe I'll just uh give you all of these order to execute these queries. Maybe I'll just give you 2 minutes. You can always do it after the class. Now we'll learn about uh string

[5:18:51] Now we'll learn about uh string functions as an allow me a minute. Yeah. So as a name says

[5:19:09] yes uh she what you're saying is right. So as a name says you can use these functions on the string values on your string columns. Now string functions can string columns. Now string functions can be broadly categorized into manipulate.

[5:19:30] to manipulate the strings for us. For example I already gave you this example of uppercase lower case. So you can replace you can concatenate. Yeah. Let's say I've got Tika Gupta. So I can

[5:19:43] concatenate my first name and last name. So this is these are what this will come under manipulation. Then you can do calculation on the string function. Now the question is that what you can calculate how you can

[5:19:55] do the maths on string function. So you can find the length for example

[5:20:10] perform the calculation like you can find out the length of your strings.

[5:20:30] also extract the substrings. I can extract the strings substrings from the string. So it is broadly categorized into these three category. So few of the functions you can use to manipulate your string like you have got the string and

[5:20:46] you manipulate them and you get some other string out of it. Calculation as I told you you can find the length extraction you have got the string and you some extra you are extracting some part of that string. We were talking

[5:21:01] about string functions. So I was telling you that the string functions are broadly categorized into three categories. Manipulation. So basically you can manipulate your string. So it's all

[5:21:13] about what all you can do with the string. Let's say I've got hello. So if you're manipulating it, let's say I'm just making it in caps. So I've got a

[5:21:25] lot of functions to manipulate. Then I can also do some calculations on it. Now the question is that how you can calculate. So I can simply find the length of the string. So this will give me five. Yeah. How many

[5:21:40] characters are there? There are five characters. I can also do some extraction on the string. So I've got this uh string hello. And let's say I want to extract the first two letters. So it will give me the first two

[5:21:53] letters. I want to extract the last letter. So it will give me last letter. Okay. So all the string functions are broadly categorized into these three. So functions when you have to do some manipulations on the existing string or

[5:22:10] when you want to find the length of your strings or you want to perform some extractions. So now we're going to look into the examples of same.

[5:22:27] everyone. So please pay attention. It's a very simple code. So first of all you can see that we have got the employee table. So I'm simply printing or I'm simply fetching the employee name from the

[5:22:44] employee table. So we have got these many employees. Now what I'm going to do is as I told you I'm going to do some manipulation. So I'm going to convert the employee names to the upper case. So what I can do for that it's very simple

[5:22:59] as I told you it's like functions are like uh let's say microwave oven at your like uh let's say microwave oven at your home. So you just have to

[5:23:14] that's all you don't have to worry about how it is made what is uh what is the uh worry about all of these things. So here also it's the same thing. So we have got upper function. I'm just passing my employee name and magically this will

[5:23:31] give me all the employees with the with [clears throat] the upper up in the upper case. In the same way I've got lower. Now this is very simple. This will give you all

[5:23:45] your employees like this will basically return everything in the lower case. So this column will be returned in the lower case. Very simple.

[5:23:57] function. I told you that length is one of the calculation function. So this of the calculation function. So this will calculate the length of the will calculate the length of the column values. So if I execute this, you

[5:24:11] can see that it is it is giving me the length. Now in this length, the space will also be included. So if you just maybe count the u number of characters 4

[5:24:24] maybe count the u number of characters 4 5 6 7 8 9 10 11. So if you have space or any special characters that will also be included. It's not that it will only calculate or it will only count the characters. No, everything will be

[5:24:39] included. So whatever you have got in the string. So whatever you have got in the string. Fine. Moving ahead. So trim let's see what trim does. So if you have any extra spaces a lot of time what happen is that

[5:24:56] we have got some extra spaces over here and over here. Yep. So trim. and over here. Yep. So trim. Yes. So you can use trim to remove the

[5:25:08] extra spaces. And when I say extra spaces I'm going to see the extra spaces if you have any over here or over here. So at this starting of the string or at So at this starting of the string or at the ending of the string.

[5:25:26] simple functions. So I'm not maybe uh waiting a lot for you guys. Maybe I'm not uh giving a lot of time to these That's what I feel because these are very simple functions. But if you want

[5:25:41] me to maybe elaborate on any functions, please feel free to say that. Yeah, please be vocal about it. So you have paid for this session, make sure that you take 100% from it. So if you think that there's any function that

[5:25:56] you have not understood and you want me to elaborate more, I'll definitely I'll be happy to do it. But you have to tell me. Yeah, magically I'll not get to know that. [clears throat] Okay, moving ahead.

[5:26:08] So we have got replace function. Now you tell me what this function would be doing. Just read this. Now this function has got three parameters. One is a column name. Then it has got the second parameter and the third parameter. So I

[5:26:23] want you guys to guess what this function would be doing. Yes. So all of you are right. So we are saying that whenever we have space Yeah. Whenever we have space just replace space with underscore. So if I run this

[5:26:38] you can see that all the spaces have been replaced by underscore. I can do it for something else also. Let's say that whenever I've got a [clears throat] now that's going to be crazy but yes whenever I've got a just

[5:26:52] replace it with underscore. See it is replacing a with underscore. You can replacing a with underscore. You can see. So here you have to uh give capital a and small a. [snorts] Okay. What I can do is let's

[5:27:05] [snorts] Okay. What I can do is let's say space I want to replace it with say space I want to replace it with let's say something else I'll say apple.

[5:27:19] So you can basically replace anything with anything as simple as that. I repeat you can replace anything with anything.

[5:27:33] simple one. concatenate. So concatenate as a a word says will So concatenate as a a word says will concatenate means we'll combine the two columns. So here what we are doing is we are concatenating

[5:27:47] are concatenating the employee name with that of hyphen So there are three things that we are going concatenating. Yep. Not only two but three things. First we are concatenating the employee name with

[5:28:02] concatenating the employee name with hyphen and then employee name hyphen with departments. So concatenating as the name says it concatenates the columns. It concatenates the column and you can

[5:28:15] definitely do a concatenation like this. Now let's say I do not want hyphen. Yeah, I do not want hyphen. So I can remove hyphen also. So definitely this remove hyphen also. So definitely this is not going to give me any error.

[5:28:35] concatenated the first uh the name and the department. So without any anything in between but yes I can give something in between also because that makes more sense to me. That looks better.

[5:28:52] these queries and I'll wait for again not more than 2 minutes. Just give me a second. And I think this is not allowing me. Okay, I'll just provide you the me. Okay, I'll just provide you the queries in

[5:29:05] so in two parts. So this is the first part of the query and this is the second part because the chart is not allowing me. It says that some maximum so and so me. It says that some maximum so and so letters characters are only allowed.

[5:29:20] Again, you can just copy paste all these queries and keep it in the notepad. Please make sure that you practice before you comes for the next class. the string function. So we'll finish off

[5:29:34] the string function. All right. So we'll look into more functions. Now you I've already talked about this left function. So this will simply extract the first four characters from the employee name

[5:29:49] four characters from the employee name column. So if I run this, see this is extracting the first four characters. Very simple, right? You want to extract do is you have to use the write function.

[5:30:03] So this will extract the last few characters. You can give the number of characters that you want to extract. It's very simple. So am I clear with left and right or let me ask a question other way around.

[5:30:17] Is there anyone who has not understood what is the use of left and right function? Fine. So I'll take it understood from everyone.

[5:30:29] let's say I do not want to extract from left or right rather I want to extract from the center. Yeah from from the part of the string. So I can use substring.

[5:30:41] Yep. So again the column on which you want to perform the extraction. Okay the start position. So start position is let's say I want to perform position is let's say I want to perform the extraction from let's say a and then

[5:30:58] the length. So till I want to perform the extraction what what would be the length of it. So if I run this you can see over here. So start position is what? One. So that's why it is saying one. And then I want to extract four

[5:31:13] one. And then I want to extract four characters. So Amit Niha. So in case of here Rohi let me do it. Let me say let's say I want to do it from the third character. So from the third third charact from the third character and

[5:31:27] charact from the third character and then four characters after that. So if I then four characters after that. So if I run this you can see. So A M I I is on the third position and after that four characters. So I T space and S. So that

[5:31:42] becomes four. So this will help you to extract extract the part of your string. In case of left and right, it always starts with from the beginning or from

[5:31:56] the end. In case of substring, it can um it starts from between also from the uh from uh maybe from some part of the string.

[5:32:08] concatenate C we have already done it but uh this example I've included in but uh this example I've included in order to make you understand that you can add or you can work on multiple function you can use multiple functions

[5:32:21] in order to get your work done so before I before I explain you this code let me I before I explain you this code let me ask you how many functions are we using functions oh sorry I've already given you the answer

[5:32:42] still I can see very less of you are attractive on the chat so I'm not sure how many of you are hearing me please make sure that you take 100% from this class and you learn something out of it so please make sure

[5:32:55] that you're interactive yeah we have got three functions yes concatenate upper and substring so basically how it will work first it So I want everyone to see my screen. So just like how the maths work. Yeah. If

[5:33:11] you give any a lot of uh parentheses in your maths, it always starts with the innermost parenthesis. Right? Here also it works the same way. So first the substring will be extracted.

[5:33:27] extracted. Now on that extracted substring we are going to make it uppercase. Yeah. So let's say the abstracted let's assume hypothetically that the abstracted substring is ami

[5:33:40] then upper will run on it and it will make a mi in upper case and then concatenate will work on it so concatenate has got two two values one is amii and the second value is over here so what it will give me am i

[5:34:03] That's right. You have got multiple parenthesis. You always start with the innermost and then you go to the next outer uh outer one till you go uh to the

[5:34:15] outermost. So here also we are doing the same thing. So you can actually include a lot of functions in one query. It's not that one or you can use only one function. You can see that in order to

[5:34:30] in order to get this result we have used three functions. So this is we have used three functions. So this is very very very uh normal thing in SQL where we use multiple functions in order to get our work done.

[5:34:44] Okay. More now this you have already seen. Yeah. Employee whose name starts with E. Yesterday we saw all of these things. Yeah. So it has nothing nothing to do with the function actually. So yeah, nothing to do with the functions.

[5:34:57] So I don't know why this code I have included. Maybe I'll remove this code. So I'll just provide you with yeah all this code you can again copy paste it. You can have a look play around with it for 2

[5:35:10] minutes. Before that we learn about the numeric functions. So for single row functions if you see we are done with the numeric functions. We are done with the string functions and now we are going to learn

[5:35:22] functions and now we are going to learn about the date functions. we have got date and time function in order to work on the date and time as a

[5:35:34] name says. So you know that when we are working on the data we may have the columns the date columns the date time columns and maybe we want to manipulate and do something with those columns. So we have

[5:35:48] got a lot of functions handy and we can use these functions in order to work on use these functions in order to work on the date or date time column. So if you the date or date time column. So if you see

[5:36:03] date part. Now you know when we have got any date or time for that matter. So any date or time for that matter. So let's say 02 02 something like this. I don't know why I have written 2014 that's a long back but yeah let it be.

[5:36:17] So if you want to extract year from it or month from it, day from it, date from it. Yeah. Day Monday, Tuesday, Wednesday. So all of these things can be Wednesday. So all of these things can be easily done using some of the extract

[5:36:32] datetime functions. Here also you if you want to extract the time now you know when it comes to time you have got hour right then you have you have got hour right then you have got minutes then you have got seconds.

[5:36:46] So if you want to extract hour, you want to extract minute, you want to extract second, yeah, any part of your date or date time, you can use the we have got a lot of functions that that can help us to extract the data from the date time

[5:37:02] or date functions sorry date time or date columns. Then we have got uh functions like current date and now. So this will return the current date. So current date means whatever the current date you have

[5:37:17] got on your system. So it will automatically read that date and it will return that in the same way. This will return the date as well as time

[5:37:29] return the date as well as time both the things. Moving [clears throat] ahead definitely it's not it's not uh the only thing that we uh we would like to do that's [clears throat] like not it's not only

[5:37:43] the extractions or um getting the current date and time. We would also like to modify or play around with the date and the time column. So for that we have got a lot of functions. For example, we have got date difference. So

[5:37:58] as the name says, let's say I give you two dates 2026 two dates 2026 01 01 and 20 26 02 and uh 01 something like that. So if I ask you that what is the difference between

[5:38:12] you that what is the difference between the two? So it will simply give me 31 days. So I can simply find the date difference means two dates and finding uh the difference between the two. I can add

[5:38:25] two dates, I can subtract two dates and so on and so forth. Yeah, I can format I can change the formatting of the date. So I've got a lot of date functions. We are going to cover few of the important date functions in order to learn about

[5:38:39] the date functions. Now it's again a very easy topic. A lot of functions are handy and we going to use them. Fine. Now if I talk about the data set that we have got. So here you can see in the employee data set we have got the

[5:38:56] joining date. So definitely we are going to learn the date functions using this column joining date because that's the only date column that we have got in the only date column that we have got in our data set.

[5:39:13] So moving ahead, uh let me quickly take this. Okay, some of this and then the rest I'll take after some time the rest I'll take after some time maybe. Yeah, I've got a lot of examples

[5:39:26] maybe. Yeah, I've got a lot of examples to show you. So this is very easy. See to show you. So this is very easy. See uh select current date. Now I'm not applying this function on any of the column of the table. It's a very simple

[5:39:38] one. select and then current date. So this will give me the current date. You can see this is giving me the current date as per my system. Also if I run now, so I told you about this function current date and now I've

[5:39:53] already told you. So now will return me what? It will return me the date as well as time. You can see over here. So this is returning me the date as well as is returning me the date as well as time. Very simple one. Yeah. Now, uh you

[5:40:08] time. Very simple one. Yeah. Now, uh you may want to um use these functions. Uh may want to um use these functions. Uh for example, let's say I want to know that uh okay, I've got the joining date. Yeah, I've got the joining date. So, I

[5:40:22] might be using it, but let me tell you right away. Let's say I want to know that it's been how many months, years or days since the employer has joined the company. So what I can say do is I can take the joining date. Yeah. And I can

[5:40:37] subtract it with the current date. So this will give me the number of days since the com the employee is associated with the company.

[5:40:50] Moving [clears throat] ahead now this is a very simple one. You want to extract the different different parts of the date. So for example you have got the joining date. Let's say I want to extract year from the joining date. So

[5:41:04] for this this is it. Yeah, I can use year function. In this I can pass the joining date column. It will simply extract all the years from the date. Yeah, date column. Month will extract the month part from the date and day

[5:41:20] will extract the day part. So if I run this you can see it is extracting the year and month is extracting the month and day is extracting the day. Very simple. Yeah the extraction part. So this is a

[5:41:36] very common function year month and day and you should definitely remember these functions because these are so easy right? So few functions we expect that you remember the interviewer will expect that you remember would expect that you

[5:41:49] remember. Okay, moving ahead. Let's say that right now see joining date is giving me more what it is giving me numbers. Yeah,

[5:42:01] what it is giving me numbers. Yeah, these are what numbers 6 1 9 3. So let's say that I do not want the numbers rather I want the month name. You're getting I want the month name. I do not want the month number rather I

[5:42:16] want the month name. So for this we have got the function month month name. So got the function month month name. So you're getting month was returning six for me and month name is returning June for me.

[5:42:34] So you can use month name if you want to get the names of the month rather than the number of the months. Day name as I told you before also. So this will um when we were talking about when we were on this slide. So anyways I told you

[5:42:50] about this day. So day will return Monday, Tuesday, Wednesday, Thursday. So over here you can see that it is returning that what the day was on uh returning that what the day was on uh 15th of uh sorry yeah June 2021. So it

[5:43:06] was Tuesday. So this will help us return the day name. So yeah that's all for like we have got more but let me give you these you can just play around with it and then

[5:43:23] just play around with it and then we'll see more datetime function done let's look into the more functions so I've already talked about date difference so date difference as the name says is going to uh find the

[5:43:39] difference between the two dates So for example over here in the date diff function I'm passing the current date. So this I

[5:43:51] I've already explained you this example. So this will give me the current date and the joining date of the employee. So basically it will find the difference between the two dates and definitely that is going to the days in the company

[5:44:03] of the employee. Right? So if you want to find the days in the company of the employee, you are we are simply uh finding the difference between the current date and the joining date. So you can see it's very easy.

[5:44:18] In the same way you can add let's say you want to add some days, years, months to the date. So you can do that also. Now here you cannot do the pro uh you know you cannot write plus and all because it will not give you it may not

[5:44:33] give you the expected results. So you can use date add function. Now this date add function takes two parameters. One is the date the original date column on

[5:44:46] which you want to do the addition and then what addition you want to do. So if you see over here in this what we are doing is in the joining date we are doing is in the joining date we are adding 30 days. Now you may want to add

[5:44:59] months. So all you have to do is in the interval you have to replace day with month. So this is what this is a unit. Yeah this is what this is a unit. So you can give day you can give month you can give year.

[5:45:13] There might be more units that you can give. You can just Google about this function. You can see the syntax of this function and you can see that what all values it takes. So over here again I repeat this function is going to add 30

[5:45:29] Here we are adding 6 months and here using the same function we are adding a year to the joining date. So if I run this

[5:45:44] here, you'll find 30 days added, 6 months added, and one year added. You can just quickly have a look to the result.

[5:45:57] function, it is the best practice that you Google about that function. For example, let's say I'm working on this function. Yeah. Now I'm not sure about the syntax of this function. So I can simply Google this and my SQL

[5:46:12] Yep. So these days you don't need need even Google. You can ask RGPT this is a function I'm using. Can you just give me the syntax of it? But yeah over here this is a AI part. So basically you don't need to uh hop onto the

[5:46:26] documentation. And why would we do that? Now we have got AI. So definitely we are Now we have got AI. So definitely we are going to use AI. So here also it's a um Gemini. So we have got the AI and you can use this. See this is a syntax. It

[5:46:41] is also explaining you about each and everything about in the syntax. So the first is a date column interval value unit. So interval that's what we give interval value any number

[5:46:57] and then unit. So over here you can see it is telling us that what are are the different units that you can play around with. So I've shown you three units but yes you can have more units and then there are some examples. So please make

[5:47:10] sure that you you make make use of the AI. So before AI a lot of times so this has happened with me also because if you go to any of the documentations

[5:47:22] documentations are full of technical jargon. Yeah, it's full of jarens and understand. Yeah. What this technical doc is saying because it's full of jargon. So it's always a best practice to use AI. You

[5:47:38] can use Gemini, you can use Google AI of course you can see. So the o AI overview or you can use chart GPT, you can use copilot. So whatever AI you are most comfortable with. So I'm most comfortable with chart GPT. So I make

[5:47:53] comfortable with chart GPT. So I make use of chart GPT and yes before we continue maybe because we talking about charge GPT I would just like to show you that if you have the paid version of chart GPT now the paid

[5:48:06] version of charge GPT is free for one year. So I have not paid even a single penny to charge GPT. So uh still I've got the paid version of charge GPT because it's free for one year. So for that you have got SQL

[5:48:22] expert. So this SQL expert is export with SQL. So anyways I'm going to talk about that later.

[5:48:34] So going back to our date functions in the same same way we have got date sub. Now I'm not going to talk much about it. It is very same as that of date add. Date add adds the date to the dates.

[5:48:48] date sub subtracts the date from the date. So let me run this. I'll just quickly show you the result and we are good to go. So I'll just quickly give you this code. You can run it. Please make sure

[5:49:02] that you also keep I'm going to give you all the notes as I told you previously also. Maybe over the next weekend I'll give you notes. But I want you guys to make this efforts of saving the notes by yourself and writing the notes by

[5:49:14] yourself. So until this you do not make efforts you will not learn anything we have got a lot lot of examples as you can see and again it's a very important

[5:49:28] like date all the functions are very important so date format again very important so you can again Google about it so there are different different formats that you can pass so if you go to the documentation of this function

[5:49:43] you'll find all these formats So these are not something that is fabricated or curated by me. So this is already there. They have got some table of formats huge number of formats and you can take those formats in order to get your work done.

[5:49:56] So here you can see that joining date is in if you if I quickly show you it's in year month and day format. Now let's say I do not want it to be in this format rather I want it to be in this format.

[5:50:10] So just that I have to pass this format in the date format function. It's so simple. So it will format my date into my into the desired format. You can see over here now it is showing the date in this

[5:50:26] format. Day, month and year. So it's very easy as I told you. You just have to Google this function and you'll find all the different type of formats that you can use. Now these this thing is not created by me. This is not something

[5:50:40] that I have written. These formats, codes are already available on this function documentation and you can use them directly in your code. Moving ahead. Okay, this is just for your practice. So, um it's just that

[5:50:56] let's say that you want we are doing the filtration on the on the date. So, employees who joined after 2020. So, we are simply filtering it. Now experience in year just like in string I showed you that you can use multiple functions in

[5:51:12] order to get your work done. Here also we are using multiple functions. So you want to know the experience in years for the employee. Yeah. So what we are doing the employee. Yeah. So what we are doing is first of all we are finding the date

[5:51:26] difference. So we are finding the number of days the employee has joined. Let's say the employee has joined. Um it's been let's say uh suppose let's say

[5:51:39] okay I have to do the maths I think let's say 720 days yeah it's been 720 days or 7 I think 30 days since employee has joined so this will give me 730 days but I want experience in years so I'm doing the

[5:51:56] basic maths guys I'm dividing it by 365 and that's what we do right so it will give me some number. Yeah, it will give me some number two point maybe or two I think. So, so it will give me some

[5:52:08] number. I'm simply rounding it off to two decimal places. experience in numbers sorry in years. And with that we have

[5:52:23] or questions you can ask them in the comment section below. Our team of possible. Thank you and keep learning with simple