SQL & Database Fundamentals — Step-by-Step Guide & 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:35] 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 we'll move into data preparation and

[00:48] 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 formulas, random indexes, ways to

[01:00] structure data more effectively. We'll also understand lookup functions like VLOOKUP and advanced lookup methods that help us find the right information from large data sets. We'll also learn logical functions like if statements

[01:12] along with conditional formulas like count if, sum if, average if to conditions. And by the end of this course, you'll be understanding how to work with Excel more confidently, clean data faster, use formulas correctly and

[01:25] apply Excel for real world task. So let's get started with AI powered business analytics in Excel and learn how to make data work smarter. Also, if you're interested in building a career in business analysis, I highly recommend

[01:37] business analyst program by Simply Learn. Now, this course is designed to help you learn modern business analysis skills along with AI powered tools that be learning important topics like planning, requirement gathering,

[01:52] stakeholder management, solutions valuation, agile, product thinking, RPA, process mining, business automation. You'll also get hands-on experience with tools like Excel, SQL, PowerBI, Tableau, Copilot, Jira, Miro, and many more. The

[02:05] program includes live online training, real world projects, case based activities, mentor support, IAB, Babok, V3 align, CBA training. So, if you want to become job ready, grow as a modern business analyst with AI skills, check

[02:17] out the course link in the description, and start learning with Simply. And quiz question for you. Which Excel feature helps keep a cell fixed while copying a formula? Relative reference, absolute reference, spell check or page

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

[02:44] data digitally or electronically because see when I talk about the wardrobes, you have the wardrobes at your home. So uh you you get to see the physical wardrobe but here in the computer world everything is digital right. So database

[02:59] is think of it as a folder. So on your machines so on your machines 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. Now when you have a database why it is uh always better to have a database. So if

[03:23] I ask you again just tell me one thing. Let's say that you've got a big wardrobe 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 on and so forth. So, I get to organize

[03:52] my clothes because I'm having a wardrobe, right? Also, I can easily access it. See, if I'm just dumping my clo clothes on the floor, if tomorrow I want a particular dress, yep, it will be very difficult for me to find it, right?

[04:08] But if I'm using a wardrobe and definitely if the wardrobe is organized then I can easily find the clothes. Yeah. So it is very easy. It will be Yeah. So it is very 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 is organized storage. So I just

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

[04:53] getting the organized storage. Then efficient access. So as I told you in case of wardrobe also if your clothes are properly organized it will be very easy for you

[05:08] to access your clothes. So in case of database also it will be very easy for you to access the data because right now you to access the data because right now see the data can be in uh you know in

[05:22] mega GBs like it can be really huge. It can be really huge. So it is very important that there should be some way to store it so that we can also have efficient access secure scal scalability. So what do you

[05:37] understand by scalability? Scalability is about increasing the data and data is definitely increasing day by day. Now if you talk about Amazon think about the data that how fast it must be increasing

[05:52] transaction being made. I'm talking specifically talk about only India Amazon website or only US Amazon website every day you can imagine the kind of transaction that Amazon might be getting

[06:07] 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. Okay. I have got some knowledge but 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:33] 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:46] previous class. So again there's one analogy in order to understand what analogy in order to understand what database is. So just like you've got a lot of books. Yeah. So we've got a lot of books. So the books are stored where?

[08:01] Now this is the picture of what books are stored where? Think of books as the data. And where do we store books? books? In the library. Yes. Yeah. So this is

[08:16] what library you must have seen that when you go to any of the library usually the books are arranged in a certain manner. For example here you'll have all the science books here you'll have let's say all the

[08:30] 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 arrangements. So why do we have such arrangements?

[08:44] What do you think? Why? What is the reason behind such arrangements? books easy retrieval. Yes, easy accessibility. Yes, imagine that they

[08:59] are not storing the books in this way and then you'll end up maybe spending your whole day or two in order to find one single book maybe more than that depends on the size of the library. So database also works

[09:13] on the same concept guys. It's just that instead of books we are now storing the data and instead of library we are having the database. The database stores the data. Library stores book. Now just like how the library is properly

[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 new addition yeah I've got a new

[09:50] addition there's a new fiction book so I know that I just have to add it over 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

[10:31] easy. I'll try to make it interesting. Yeah. All of these properties. But you properties. These are the heart of the database. So when I say autom atomicity any idea what atomicity could be? So I do not want you guys to read the slides.

[10:47] 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 and it's very important. So before I talk about atomicity maybe let me tell you the use case of database. So just like database we have got we have got

[11:17] more entities in order to store the data. Now you have to hear me out right? In order to store the data we have got more entities. Database is not the only place where the data is stored. We have got a lot more other things. So you

[11:31] might have heard about apart from database 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 seven days and so on and so forth. So to save days and so on and so forth. So to save this data now see my current balance then everything related to me my profile then my transactions whatever you see so

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

[13:21] transactions data Yeah, whenever there's a day-to-day transaction data, it goes into the 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 asset is. But why day-to-day transaction data goes uh why day why do we go with data goes uh why day why do we go with day-to-day transaction? because because

[13:52] it provides a database provides with the asset guarantees. If I give you the other example, if I talk about let's say Amazon [snorts] so again day-to-day transaction right you're making a payment you're ordering something making

[14:05] 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 that to you. >> [snorts]

[14:19] >> So all of these phones etc. So these are this this is what these are data images things are data. So this might be coming from the database. Yeah this might be coming from the database. So the database might be storing all of these

[14:34] 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:17] 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 as your account balance. I'm talking about INR anything anyways. Yeah. And

[15:31] you say fine I'm going to give you 10,000 rupees. So what you do is you go ahead and you make a transaction. Now this is something that we do in our day-to-day life making the transactions via bank. So you'll understand that how

[15:46] via bank. So you'll understand that how things works. So here I go ahead and I make 10,000 rupees transaction. So what will happen is the 10,000 will get deducted in your account from your account and it will get credited in your

[16:01] friend's account. So this is the expected behavior when everything goes well. But let's say that you made the transaction. This might have happened a lot of time with you all that you make

[16:14] 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 your friend did not get the credit. Might have happened, right? So what will

[16:29] 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 bad thing right if I talk about banks this is something that we definitely do

[16:44] not want because if things are happening like this then we end up calling these people all the customer cares and you know breaking our head that we I transferred did not work whatever it is it's definitely not some not something

[16:57] it's definitely not some not something that we would appreciate right so What happened? Usually in in your day-to-day life, you might have seen that if the money is debited from your account and there was some problem while you were

[17:09] making the transaction, it gots recredited. You get the message that you have got your money back. So behind the scene, what happened is because there's a database that is

[17:21] 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 or none. Yeah, it says that either all or none or nothing.

[17:35] So what does that mean? It means that either the whole transaction should get processed successfully. So what I when I say whole transaction should get processed su successfully. It means your account is getting debited and your

[17:49] friend's account is getting credited. This is the one full transaction. But if there's an error in between, there's some problem in between, then the whole transaction should be rolled back. So either it should be the full transaction

[18:04] or there should be nothing. And that's why when there's error while you making some transfer and if there's some error you you'll get uh you'll get your credit back, you'll get your money back automatically and this is because of the

[18:18] database property that is atomicity. So this is the property of the database that either the whole transaction is successful but if there's a problem in between then it will make sure everything is rolled back. So your

[18:30] deducted amount the amount which was deducted is rolled back and it is again credited. So this is what atomicity is. Now this is so important property. If I talk about banks and if I talk about any other website also Amazon also you make

[18:45] an order. Yeah. Either the order should be made or shouldn't be made. There should be nothing half in between, right? So doity is very very important. consistency. So everybody knows the meaning of

[19:00] Yep. So here it is written but still I write it. So again I'll explain you with the help of an example. So let's say that you've got an account.

[19:13] Yeah, you've got an account with a bank and you've got 15,000 rupees. Now in your bank and you might have seen that you a lot of banks does have such

[19:25] rules. So let's say that your bank has a rule that you should maintain minimum balance of 10,000. So this is a rule, right? Minimum balance of 10,000. That's a rule. Now

[19:40] what you do is you make a transaction. You make a transaction of 10,000 rupees in your friend's account. So how much how much amount is left in

[19:53] your account? How much amount is left? You've got 15,000 and you make a transaction of 10,000. So how much amount will be left? Minimum balance should be 10,000. So here consistency says rules

[20:09] are not broken. So in very simple words the rule says that you have to maintain 10,000 rupees in your account. If you try to if you try to transfer 10,000 will give you error. It will not allow you to do it. You might have seen on on

[20:25] GP Google pay that there's a limit of 1 lak rupees for every day. So if you try though you might be having even one CR in your account but if you try to

[20:37] transfer more than one lakh rupees in from your Google pay it gives you error it says big no that you're not allowed. So that are what that that is what that So that are what that that is what that is rule. So

[20:51] 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. So am I clear? So isolation as the name says

[21:06] 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:40] make a first transaction that is we are taking 5,000 rupees out of the bank. So let's say we are taking 5,000 from the ATM. So the new balance would be what? The new balance

[21:55] 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 seeing 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 initiated 5,000 rupees transactions 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 isolation says that we

[23:47] 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 two 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. 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.

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

[27:42] not write the whole thing. transactional processing. transaction Amazon your banks. So these are what day-to-day

[27:55] 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:14] 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 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:11] 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 soon to say right? So you have to take the exam first and then you can say that

[30:38] also it's the same thing that the transaction is ongoing and when the transaction is ongoing this state transaction is ongoing this state shouldn't be shown to the user.

[30:50] because the transaction is ongoing. So if the transaction fails then if you are showing 5,000 see if you're showing 5,000 then it's a bad data right you're showing the bad data actually the the amount is still 10,000 because the

[31:05] transactions failed but you're showing five 5,000 so that's a bad data so either you have to either you should show 10,000 or you should show 5,000 after the successful transaction. Am I clear now?

[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 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%. And 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:58] you. Just give me a second.

[34:15] Yes. So you can see these are what these are tables with rows with columns and rows. So it's a relational database. So relational database says that this is table let's say this is table one, this is table two

[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:14] 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 we can have more information phone number birthday so many so much of it.

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

[35:43] can see that this table and this table is related to each other. Yeah. This table and this table is related to each other. So when I say that two things are related to each other. Let's say that you are related to

[35:55] 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 say that you have got multiple friends and you say that okay, I am uh you know

[36:10] somehow I am now that doesn't make much of sense but yeah let's say that I am related to this friend in a way that we all belong to the same college. So that's a key between the relationship uh that's a key

[36:25] 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 table and I've got this orders table. So what do you think what is the key

[36:39] between this table and this table that is actually making the relationship happen. So in order to make the relationship happen, there should be relationship happen, there should be some something in common.

[36:57] Yes. Customer ID. Yeah. Tell me about this one and this one now. So this is how the relational databases are designed. We are going to deep dive

[37:11] into it. That's what we are going to learn for next eight classes. Now let's talk about the other databases that we have. Okay, here I've got okay I just totally forgot that I've got a PPD and here I'm showing that how the

[37:26] different tables are related to each other. So there's a key a common key but you and we are going anyways going to deep dive into it. Okay, a lot of uh speaking from my set again I'm so sorry because I can't help

[37:41] From tomorrow we'll do a lot of hands-on. Now if I give you the examples of the relational database, examples are MySQL. You're here to learn MySQL. So that's a relational database.

[37:56] In the same way we have got SQL server, Oracle, Postgress. Now if you learn even Oracle, Postgress. Now if you learn even one single database if you learn MySQL then you can say that you know all of the others and I'll tell you the reason

[38:09] the others and I'll tell you the reason why why because 85 to 90% of all the database they are same. It's just that there's little bit syntax here and there. So you don't need to learn all the different relational databases. If

[38:23] you learn one you you will surely be able to work on the others also. So if I'm let's say I'm taking interview. So if I know that the person knows my SQL very well but in my project we are using SQL server I will not hesitate

[38:38] 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 and he understands the length and the

[38:52] breadth of the technology then I'm sure that uh working on SQL server will will 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 Nha 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:31] you're getting so this table says that you've got extra column I'm sorry I can 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

[41:11] but let's say I've got some string value. Yeah I want to give let's say 10. So again it will say no. It will say what what are you doing? I can just take What the hell are you providing me? You're providing me 10 like the string

[41:28] 10. Sorry I can't do that. So it's very strict. Yeah, it says that see I have 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 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 just have to add one keyword and then you're done. Done and dusted.

[42:30] Am I clear that the relational database are super strict, very stringent? Fine. Now let's see there's a requirement. is [clears throat] 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 this. Yeah, because let's say I I I am I am my own school and I say that fine.

[43:03] Yeah, but let's say there's some student with some you know more things. Let's say that he has got some uh he or she has got some certificates or maybe some achievements. So I want to store that data also. Yeah. I simply do not want to

[43:17] 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. whoever is coming to my school. Yeah, whoever is getting registered, whichever

[43:32] 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 are not going to get into into the architecture of it. As you can see these are non-tabable databases. Yeah. So it stores the data in a way.

[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. Yeah. 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 that it says fine. So I'm going to have one more 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:43] that I'm very much okay with it. Yeah, I'm not strict. Right now I've got five details. Yeah, five columns. If you got 10 columns for the next employee, I can easily accommodate it. So I'm not strict [snorts] like tables. So that's what

[46:00] [snorts] like tables. So that's what NoSQL says. different formats. So it stores the data in key value, column, graph and document

[46:12] format. You don't have to deep dive into it. But yes, so why do we use NoSQL? This you should know. So if you want that flexibility, see tables are very strict. So you want that flexibility that tomorrow if your data comes in some

[46:27] 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. Definitely you don't have to deep dive into it.

[46:42] XML format. Yes. XML JSON key value pair. Exactly.

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

[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 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:17] 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 this yeah so this is how it works even worked

[48:32] on the graph database. Usually uh it is used by the social media companies. 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] So centralized database it can be any database for that matter. It can mean SQL, NoSQL, graph. So centralized database is what you have got one database. Yeah, you've got one database and

[49:14] 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:47] 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 as the other 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:47] 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. So having these backups definitely is pretty expensive because here you're maintaining only one database. Now let's say I have got two backups. So I'll end up maintaining

[51:16] three databases, right? So it's pretty expensive but yes we call it as distributed databases

[51:29] places and that's is again done by all the big companies 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 are always up and running. So in Pune this happened almost a year back I think it was it happened in June July. So I stay in Hindabari those who are from Pune. So uh there was some

[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 um 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've got the next topic. So I just want to you know cover these topics in continuation or if you're okay maybe I'll take another 15 minutes for these topics not more than that.

[54:04] Oh wow. Everybody is saying go ahead. I'm so happy. Fine. 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] We learn about the database. Don't you think so? We want somebody who can manage the database. We want a tool which can manage which can help us. Anyways, we are going to manage definitely but we want a tool which can

[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 and we call it as DBMS database

[56:37] 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. So what you can do in this see you can create the database you can read. Yeah

[56:51] you have got the data right. So you can create you can read you can make the updates you can delete it. So you can do all of these things. So these are called CRUD operations. So do not get scared with these jarens, technical jargon.

[57:07] These are very simple thing. So when I say CRUD C stands for what? Everyone tell me C stands for what? I just talk about the operations. It means create. Yes. So CRUD operation

[57:23] you're going to listen uh to this term a lot. So this is not specific to the lot. So this is not specific to the databases only like Yep. R stands for 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 CR 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 like you're adding something to it.

[58:04] you're removing something from from it and so on and so forth. So here also we do all of these things but we do it with the help of the software called DBFS

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

[58:36] I just told you what DBMS what RDB DBMS could database? You know that there are so many databases, right? We learned about

[58:51] 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 charg I do don't understand plain English. So

[59:43] DBMS says hey 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. 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 pend. Yeah, get our work done. That is CRUD. Any of the CRUD operations that

[1:00:20] we want to perform on the database. So, you're getting just like let's say there's a librarian. Yeah, librarian is what? librarian in our analogy is DBMS

[1:00:32] who manages the database. Yeah. With the U. So U. So then we have got library. So library is then we have got library. So library is what? Library is the database

[1:00:48] books. So books are what? Books are data. These are data. Now let's say that you want to fetch

[1:01:00] some book. Yeah. You want to fetch some book. You want some some book. So you go 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 your

[1:01:17] librarian will go and may fetch it for you. So you're getting so you're going to talk to the librarian. So you may talk to the librarian in the language that your librarian understands. That makes

[1:01:30] sense also. Yeah. If your librarian understands only one language that it's English and if you start speaking Spanish in front of that librarian, the librarian would be like what are you saying? I'm like please bother. Yeah. So

[1:01:45] it the librarian will not do your work. As simple as that. So here also in order to talk to the DBMS now in order to make DBMS work for us we now 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 is saved in the database. In order to manage the database we have got DBMS. So in DBMS only we store the data actually. Yep. In the form of the databases. Now in order to make the DBMS

[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:58] 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. Yeah 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 that's a software in order to manage the databases. question. What is the difference between SQL and MySQL? SQL is a language. Hear

[1:03:54] me out. Yeah. SQL is a language and MySQL MySQL is the DBMS. and MySQL MySQL is the DBMS. So we have got multiple relational DBMS.

[1:04:08] We have got multiple relational database management systems in the market. For example, MySQL. Then we have got SQL server. So it shares the name with the

[1:04:23] databases. Yeah. Shares the name same name with the databases. Oracle. [snorts] 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 Aminina

[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 MySQL is the So SQL is a language and MySQL is the DBMS.

[1:05:13] write but yeah my SQL posgress SQL server these are what? These are the softwares. Yeah. These are what these are RDBMS database management systems. In order to talk to the database management system we need a language.

[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 not 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. So 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. you think attributes could be? We just talked about the table. So what

[1:07:05] attributes could be? So attributes are nothing but the details of the entity. Yes. So all of you are right. Attributes are the you are right. Attributes are the characteristics details. Yep. of an

[1:07:19] characteristics details. Yep. of an entity. So let me write it. So attribute about they are the property of an entity. you tell me what could be the attributes?

[1:07:36] What could be the attributes for the student table? Student entity ID, name, age, class, marks, etc., etc., right? So

[1:07:50] age, class, marks, etc., etc., right? So these are nothing but the attributes. Attributes are shown using ovals. Yeah. So whenever you see oval, it means that it's an attribute. Then we have got relationship. Now you

[1:08:05] know that tables they can be related to each other. Yeah, we'll talk about primary key and foreign key not right now. But yes, how

[1:08:17] entities are connected to each other. So I gave you the example of orders table. So you told me that the two tables are connected using customer ID, right? So relationship is how the

[1:08:32] entities are connected to each other. So if I give you the example, let's say if I give you the example, let's say that we have got two entities students is one of the entity and course. So course let's say students let's talk

[1:08:46] about simply learn. So student table has got all the information about you guys and courses table has got all the information about the courses that are provided by simply learn. So if I talk about the relationship between the two.

[1:08:59] about the relationship between the two. So simply students have enrolled Or I would say student has enrolled for which course?

[1:09:12] Yeah, I'll simply say student enrolls for course. establish the relationship you need to have the keys. Yeah, you need to have

[1:09:25] the keys. So I'll talk about the keys right away. At least I'll give you the right away. At least I'll give you the little bit idea about it. So we have a primary key. Now what is a primary key? So primary key uniquely

[1:09:40] 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 think 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 talk about

[1:11:07] okay in this table we don't have any primary key. Okay. In this table can you see any primary key? Customer

[1:11:19] also. See customer name can be repetitive. We can have a lot of customers with the same name as Ashul and male. So this can be definitely and male. So this can be definitely repetitated. But how will we uniquely

[1:11:32] identify this ano or how will we differentiate this anul with the other anulu 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 this table I'm talking mode.

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

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

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

[1:14:38] primary key. But for this table, customer ID is what? It's a foreign key. customer ID is what? It's a foreign key. It's not a primary key. So foreign key help us to connect the two entities. Yeah. So this foreign key is a

[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

[1:15:30] 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? Sha just remember this thing that whenever you refer primary key in the other table in order to create a relationship between the two entities it becomes a foreign key.

[1:16:05] you how the diagrams looks like. I think here I'm not I have not created the diagram. Fine. I'll just quickly show you. So, it looks like this. You've got the entity. So, student

[1:16:19] 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:36] 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 other. So there's a relationship between the two. So this is what enrolls.

[1:16:54] somebody gives you any entity diagram, er diagram, you should be able to understand student and courses are nothing but the entities. The ovals that you see are nothing but the attributes of the entities and the diamond that you

[1:17:07] see are nothing but the relationship of the entities. the entities. Am I clear?

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

[1:17:50] If you can do it today, that's fine. Otherwise, we'll do it tomorrow. dialogue. 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:01] These are what these are key attributes. So you can just have a Okay.

[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.

[1:19:56] Fine. Is this the one for product table A? Okay, I'll come to that.

[1:20:12] the product table. Now tell me for product table, I'm talking about the product table, the one that I have highlighted. What is the primary key?

[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, attributes. So we have got country, state and zip code and all the values or state and zip code and all the values or values of these three makes address. So

[1:21:30] 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. So what 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:09] 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] order. Yeah. You make an order. So in ca in case of order in case of order let's say I make an order of a product I'm not able to write. Okay. Okay. There's some issue with the writing thing. Just give me a second.

[1:22:46] Yep. So let's say we have got the product. Yep. So I buy one product of some quantity, two quantity. I buy the other product of one quantity. So what I mean to say is that these attributes may can have multiple values. It can have

[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] on the database. You're going to query the database. You're going to generate or play with the data of the database. So you should know if somebody give you this AR diagram, you should be able to know what this ER diagram is

[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 are columns right. So the values of these attributes the values inside these attributes will be derived from the other attributes. For example let's say

[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 yeah so experience in our company let's say that it's simply learn employees for simply learn so experience in simply learn how many years in simply learn so see this value can be derived from the

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

[1:24:42] 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 So if I'm saving these value it it may not give me the right values. it right

[1:24:57] now while I'm saving yeah at this point of time maybe it's 2 years for an employee but after a year it will be 3 years so again and again I have to update my database so I can simply derived it if possible I can simply

[1:25:10] 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 going to work on it we usually create the column work on it we usually create the column for it but uh so it's like you may or may not create the column you can create this column on

[1:25:37] not create the column once we start working on SQL this will be clear you'll understand so am I clear what are derived so am I clear what are derived attributes everyone

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

[1:26:09] Now uh there are few things that just give me a second. I would like to 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 employees.

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

[1:26:52] relationship that Niha is let's say working on so and so project. So let's 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

[1:27:30] 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 right now you know about the table. 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 tell

[1:28:44] me how many entities are there how many entities are there they no so those who entities are there they no so those who are saying two it's not two it's one can you see that I yesterday we learned that entities are represented by the

[1:29:00] entities are represented by the rectangle So this is a relationship. So basically what it is saying is now hear me out.

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

[1:29:31] itself. So if I give you this example of employee, let's say that we have got the employee table. Now hear me out. It's very simple.

[1:29:45] and then we have got the name of the employee. So let's say 1 2 3 4 and name I'll say A B C D. Yep. I'm not writing the proper names.

[1:29:57] And here here let's say I've got one more attribute that is manager. So manager maybe manager ID. So let's say that A's manager is D. So I will say

[1:30:11] 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 and D there no manager. Yeah. No manager or so over here you can see that the

[1:30:25] table is related to itself. You're getting so the table is related to itself. So here because if I talk about the manager ID you can see that if I tell you okay let me ask you the question. So can you tell me what is the

[1:30:41] name of the manager of A? What is the name of the manager of A? Right? So this is what this is self relationship where the table is related

[1:30:56] relationship where the table is related to itself. So here because the entity is related to itself we call it as unary relationship.

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

[1:31:30] 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 entities. I hope I'm clear with this that binary

[1:31:59] relationship simply involves two entities. It's a very simple one. Let me know if you want reexlanation for any of these. Okay. So how about turnary? So turnary is the relationship where you

[1:32:15] have got more than two entities. So basically where we have got three basically where we have got three entities. Yeah, three entities involved. And think of entities for now as a table. Think of it that entities is

[1:32:28] nothing but the synonym of table. So over here you can see that we have got three entities and all the three entities are related to each other. So employees works in a department also employee works with the organization. So

[1:32:43] 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 So am I clear with this one? It's a simple one. Am I clear?

[1:33:04] a very [clears throat] 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. So u many to one. It's one of the same thing. type of relationship but you'll you'll

[1:33:32] blogs they say that there are four type of relationship. So do not get confused. It's all about how you look at it from left to right or right to left. That's the only thing. Anyways, we're going to talk about it. So allow me a second. So

[1:33:47] talk about it. So allow me a second. So let's talk about onetoone 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:1 relationship says that every single instance of one entity is

[1:34:13] connected to a single instance of another entity. So here when I say it means nothing but the row or the record. So do not get confused what this instance is. It's nothing but the row or the record. So over here if I talk about

[1:34:28] this one this example that is being shown on PPT. So let's say I've got the shown on PPT. So let's say I've got the employee table.

[1:34:40] [snorts] Yeah 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 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. 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 one to many etc. you'll able to one to many etc. you'll able to understand what does instance means? So instance means a row sa it means a row one row

[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 clear? What is onetoone relationship?

[1:36:57] have some confusion, I'm sure your confusion will be clear once we see the other type of relationships that we have. Okay. [clears throat] Now we look into the other one that is

[1:37:10] 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:53] don't need 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] one customer can place multiple orders just like if I talk about Amazon. So you are the one customer one account right and you can place multiple orders right. So here also one instance this is what one instance this is what one instance

[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 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:26] order. [snorts] Perfect. Now as I told you that one to 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

[1:39:38] to one it's one of the same thing it's just that if I put [clears throat] order over here on this side and if I put customer over here so right now it's here it is one and here it is many right so just that

[1:39:54] and here it is many right so just that if I put order over here then one 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.

[1:40:08] So order is on the many side and customer is on the one side. So this [snorts] is one to many or 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.

[1:40:22] one to many and many to one is nothing. It's just that right to left or left to right. Okay. So here also I think see they don't have the P PD. Oh no, they have the people. Anyways, so yeah. So anyways, we'll talk about many

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

[1:40:52] Any any any example. Now guys, one more thing. So when you create the relationship between the two entities, it's not that it's a rule. Like when I say it's a rule, I mean to say that it's not that

[1:41:05] the employee will be on the many side, the department will be on the one side. the department will be on the one side. This is what we see. So but as per the requirement, you can keep any table on the many side and any table on the one

[1:41:17] side. I repeat, as for the business requirement, we can keep any table on side. So it's not that it follows a rule that this has to be on the one side and this has to be on the many side as simple as that. Right? So now I want you

[1:41:32] guys to give me some examples of many to one or one to many. So I'll give you one example. Okay. I've started getting the examples. Yes. Patient appointment date. examples. Yes. Patient appointment date. Very good. Then okay I'm getting a lot

[1:41:45] of student subject. Patient appointment. Okay. Student school. Yes. one school at least in India. Yeah, one school can have many students. Now that's always goes in

[1:41:58] India. a student never attends more than one school at least that's how it is for for companies also right if I'm if let's say I'm working with IBM so until unless I'm moonlighting definitely that is not allowed so one

[1:42:12] employee sorry uh one company being worked by u one employee working in what worked by u one employee working in what I'm saying so one company is uh can have

[1:42:25] I'm saying so one company is uh can have multiple employees Patient table, a medical test table. Very good. Ban customer, right?

[1:42:42] One nation, many states. Very good. One state, many cities. 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.

[1:42:56] we'll move to many to many. One person many bank accounts when you have got let's say again we have got the employee table

[1:43:13] and then we have got the project table. So one employee can work on multiple projects. Let's say we have got the projects let's say uh okay I'll just name some of the projects that I have worked on Sunrust etc. So definitely uh

[1:43:30] there are situations in the company where one employee works in the multiple where one employee works in the multiple project right so you may end up working on multiple project also one project is most of the time being worked by

[1:43:42] multiple employees so here it is one to many so a bc can work on amx but a can also work on sunrust so it's what many to many so I now I want you to

[1:43:54] to many so I now I want you to contribute some examples on the chat contribute some examples on the chat for many to many. just covered. But when I say what is cardinarity 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 cardinality tells that how many how many rows of one table can be related to rows of another table. So how many rows of one table

[1:44:54] related to rows of another table. you know a table we can have tables and

[1:45:08] suppose there's a one row so one row can be related to only one row of another table or one row can have multiple can be related to multiple rows of another table. So cardinality is like going one step deeper and telling telling about

[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 cardality 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 Here also zero is a minimum cardality and one is a maximum cardinality.

[1:46:21] So what does it says? So it says let's say I've got some developers in my 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

[1:47:56] not involved in any project. N means maximum. It means that a developer so B maximum. It means that a developer so B can work on n number of projects,

[1:48:08] multiple projects. So that's what it means. Zero means we can have developers with no projects. Also we can have developers who are working on multiple projects. Now here 0 and one means again the same thing. Now

[1:48:23] and one means again the same thing. Now let's say P3 is a new project and right now there are no developers allocated to P3. So it means that we can have any any project we can have any instance in the project table

[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 does 0 1 0 n denotes?

[1:49:53] fine. Anyways, we have understood it. That is more important. So, am I clear what does cardinality 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. I've just [snorts] got one. Yes. Clear. Perfect. So, still I'll just wait for a

[1:50:18] minute. You can just read through it. It's a very simple thing. So if you see these numbers, do not get scared. It's very simple thing. Minimum and maximum. Sometimes when we see all of these

[1:50:32] things, we get scared. Oh, there might be some maths involved, some complicated maths. But that's not the case. But that's not the case. So I'll quickly repeat one more time

[1:50:46] over here. The cardality 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

[1:50:58] means? It means now we have got this stable developer and we have got this stable project. So zero means that we can have some developer can have some developer with no projects related to it. So let's

[1:51:12] say that a is not working on any of the project. A is a new hire in the company. Until now A has not been given any project. So I can have a developer with no project not related to even a single project. So that is very much possible.

[1:51:27] N means a developer can work on multiple projects. So B can work on 1 2 3 B can be related to 1 2 3 4 5 6 and 7 8 9. So that is very much possible. Okay. So

[1:51:40] this this is what 0 and N means. Now here it says 0 and one. So what does that means? It means again you can have a project. So let's say this is a new project. Till now no developer is working on this project. So think of it

[1:51:55] as Upwork. Yeah. On Upwork you see that you've got a lot of projects with no you've got a lot of projects with no developer assigned. Right. So we may we can have the project with no developer. So zero means that a project it is

[1:52:08] possible to have a project with no developer. So this instance this developer. So this instance this instance is not related to any of the instance of the developer table. So this instance zero means it is possible to

[1:52:21] 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 says that one project can be worked by only one developer. So 456 is worked by

[1:52:39] B. So that's all you cannot have C also working on 4 5 6. It says that maximum one instance can have relationship with maximum one instance

[1:52:52] of the developer table. So one instance one row can have relationship with only one row of the developer table. So minimum is zero either no relationship or at max one one. Okay. So we are done with definitely we

[1:53:09] Okay. So we are done with definitely we are done with the theory part. So that's a good thing. Now what we going to do is uh I'll just give you the brief the very very brief of the whole thing that how you can create the database how you can

[1:53:23] create a table how you can insert the data into the table and then later on we'll deep dive into each and everything for database also what all commands we have for tables what all commands we have so we are going to deep dive into

[1:53:37] 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. So I want everyone to launch their MySQL workbench. So I'm using Windows. So what

[1:53:53] MySQL workbench. You have to open it. Now you can launch the uh labs. So yesterday Raashta helped you with the launching of the lab. If you're still facing any issues, you can post it on the chat. We'll see if we can help you

[1:54:08] right away. Otherwise, at the end of the session, please make sure that you're 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

[1:54:23] if not all the time like at least sometimes I'll try to give you the hands-on if not all the time. So, uh your labs should definitely be working.

[1:54:35] working, you'll not able to learn with me. Of course, you'll able to learn with me, but then you have to practice after the session. But yeah, you always have your labs are working or not. So uh

[1:54:48] please make sure if your labs are not working, you're vocal about it and if us then we can allow you at the end of the session to share your screen and we we'll try to solve the issues with your lab. So I want to see if you done on the

[1:55:01] chat once you are done with opening the lab. So password is same for everyone. I think the password guys what's the password? I think it's p sd is it double one and uh exclamation mark

[1:55:19] is it double one and uh exclamation mark or is it one? Okay. So see everybody's 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] like a database. Now what is a database? You already know database is the it's like the wardrobe. It's like the library which can store the digital which can store the data for you digitally. So we are going to create the database.

[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 um it will be easier for you to access it. So Anibraa it's not when you are

[1:57:11] downloading or installing the SQL yeah it's not only about next next there are some settings that you have to do. So please make sure that you go through the video which I shared yesterday. Today also I've shared it multiple times. So

[1:57:24] please go through that video. Now if you do not have the software installed if you do not have the software ready for you to do the hands-on. Please do not you to do the hands-on. Please do not start installing it right now. You can

[1:57:36] not able to do the hands-on with me. But you can always write down those notes. You can stay with me in the class and you can do the installation etc after the class. But please make sure that you concentrate in the class just because

[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 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 questions on the lab. So you can share your screen and we'll try to help you

[1:58:39] out. So let's continue with our learning for today. And as I told you, if you're having issues with the lab that is that is provided to you all by simply learn. If you're using your personal laptop,

[1:58:53] you're using your personal laptop, please download and install my SQL. So I will not be using lab. I'll be using my SQL on my personal laptop.

[1:59:05] do is we are going to create a database. So as I told you that today uh the for we I'm going to give you the holistic view of creating the database the tables inserting the data into the table and then querying the table but after that

[1:59:21] we are going to deep dive into each and everything. So in order to create the everything. So in order to create the database in order to create the database is we going to write the SQL in order to talk to the DBMS. Now I want to tell

[1:59:38] 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 create database and I can give any name to my database. But this has to be

[1:59:52] 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. So and then you can give any name to your database. So let's say that I give

[2:00:07] the name to my database as school. Yeah. And then see I can always end my comment using the u

[2:00:20] what do we call this? No it's there they there are no limitation. You can use upper case and lower case both. Yeah using semicolon. lower case both. Yeah using semicolon. Thank you so much.

[2:00:35] if I've just got one SQL command it will still run. Now in order to run it we have got this button. Can you see this icon? A small icon. Let me increase the size. I think the size is already increased.

[2:00:49] Just give me a second. Yeah I think the size is already increased. So anyways so this is the button that you need to use in order to execute a command. So if I

[2:01:01] 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 selection. So if I'm not selecting anything, yeah, I'm not selecting

[2:01:14] anything. If I execute it, it will execute everything. Each and every line of code, each and every word that is written on my SQL file. This is what this is a SQL file. You can say SQL file. So because I've got the previous

[2:01:27] SQL file in my case it shows six. In your case you're using it for the first time it might be showing one. So I'm going to run it. Once I run it

[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 you can see this in green. it means that the

[2:01:54] database has been successfully created. So if you see in red, it means there was there was some error when you were creating the database. Guys, please make sure that you are attentive in the class otherwise what will happen is that you

[2:02:08] otherwise what will happen is that you may get some errors because you're quite good in number. So if you're not attentive then you may get the errors attentive then you may get the errors that I can always resolve. But if I get

[2:02:20] a lot of queries from you guys just because you're not attentive then I will not able to cover everything whatever I want to cover as a part of this training. So I don't want to cover anything on the surface level. I want to

[2:02:33] deep dive into the topics. I want to make sure that you understand how things are working. So for that I want only I have only one request from all of you. Just be attentive because if you're attentive I'm 100% sure you'll able to

[2:02:46] learn it. SQL is very easy. So you can see that the database has been created. In my case I've got the other commands. So if you have noticed I was some dropping I was deleting the databases. So these are because of that.

[2:03:00] But yeah the database has been created. So I want you guys to create the database and let me know when done. So you can just write D also instead of writing the whole run. So few runs will give me help.

[2:03:17] where you can see the database. So 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] I'll come to that output. I know that you're not able to see the output. So, just hold your horses. We'll talk about that also. Now, I can see that most of you are done with running it. And I hope that over here you see a successful

[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] so the database is not appearing over here. So what you have to do is you have to refresh it. So this is what you have to press you. This is what you have to click on in order to see the database. Now you can see that the database has

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

[2:05:40] do is you can 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 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. Basically you have to select the database. So how would you select the database? In order to select the database

[2:07:00] either, now hear me out. Either you can doubleclick on the database. See if I double click it got selected. Or what I can do is I can run the command. So just delete the previous command.

[2:07:14] 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 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 have got tables and views. So we are

[2:08:24] going to cover these two. So first we'll talk about the tables related to the tables. View is a very small topic. It's a short topic that we are going to cover later on. So what we going to do is just give me a second.

[2:08:39] [clears throat] School is not highlighted but command 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. right now you can see that I we do not have even a single table in our

[2:09:15] database. So we are going to create the table and guys as I told you please uh don't worry about not able to see the output panel not able to see the schema panel we'll see to that at the end of the session

[2:09:30] right now just focus on the learning so I have already helped you with it but then I have to look into I have to look at your screen and then I have to help you okay so we going to create the table so how do we create the table so in In

[2:09:46] order to create the table, we write the command create. So you can make it uppercase, lower case, everything is okay. So let me make it upper case. So create table.

[2:10:03] 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] then we give the data type of the column. So yesterday when I was teaching you the different type of database that are available in the market, I told you 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 u 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 var

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

[2:11:53] name can be of 50 characters. So if your full name is more than 50 characters the table will say no I can't cannot take it. So it can take characters and at max it. So it can take characters and at max it can accommodate 50 characters. Moving

[2:12:08] ahead, the next column is city and here is the data type of the city. It's again let's data type of the city. It's again let's say 50 your care 50 and my table the

[2:12:25] code for the table creating the table is ready. ready. So just give me a second. I'll just quickly look into the chart. Vidya what you're seeing is right. Vanc

[2:12:37] 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 mean to say is that this is what this is a number. But if you give three in vcar

[2:12:50] a 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 u make three yeah it will take three not as a number but as a string. Yeah this is what this

[2:13:05] 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 means text. So if I say string or if I say text it's all about the data type.

[2:13:22] say text it's all about the data type. Yeah. Or if I say vcat. So in SQL we say Yeah. Or if I say vcat. So in SQL we say vcare right in python we say string text we don't say in any of the language computer language I'm talking about so

[2:13:35] 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 I'm going to simply remove this now I'm going to run this command you can see

[2:13:50] that it got created and I have to refresh in order to see the table created. Now if I just expand it, you can see the table has been created. So I want everyone to run this command. I'll wait for a minute for 2 minutes.

[2:14:06] Now guys, please make sure if it is showing error, you also read the error. So please delete the above commands that we have run before or what you can do is let me uh so maybe this time I'm not going to delete the command. So I'll

[2:14:20] show you how the does the error look like. So what I have done is I've created the table. The next thing what we will do what we will do the next 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 to use the table as such just like DB databases.

[2:14:53] chart. So I look into it. What is the problem? So in order to insert the data 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 vcar. It means it's a string. It's a text. So I'm going to give it in It's a text. So I'm going to give it in the in the single quotes. Amit age any

[2:16:09] the in the single quotes. Amit age any age and city comma I can give multiple values. So I'm just copy pasting it. So let's say ID is two. The name is 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 error. So I want you guys to tell me what is the 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 this semicolon you can see that the error that I was getting. You can see the error that I was getting over here.

[2:18:01] It will get resolved because I've added the semicolon. So this was a first problem. Now let me rerun it again. It will throw error. So see now you'll able to understand what the error is. Table student already exist. So basically what

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

[2:18:29] table is already there. So that is the problem. So anyways, I don't want to run this because I already have the student table. So what I'm going to do is I'm going to run only this command. So see I have to select this command and I have

[2:18:43] 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:09] and the data is inserted into the table. H okay. So I want everyone to try this. Yeah, there are some shortcut keys also. You can just Google the shortcut keys. I'm very bad with reme remembering the keys.

[2:19:24] 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. So here ID now I'm not giving any space I'm just giving comma then full name

[2:20:19] I'm just giving comma then full name then again comma and so on and so forth. command use school. If you're not able to see the

[2:20:32] tab this left panel just you run this command useful panel just you run this command useful and then create the table. So guys let 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 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 here. I' I've got three rows of

[2:21:37] data. I would like to query the data. So, yes, I'm going to write the query. So, yes, I'm going to write the query. So, here we go guys. So, I'm writing in So, here we go guys. So, I'm writing in the same file. Select star. So star

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

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

[2:23:27] got created. Now if I rerun this code. Yeah. If I 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 me a warning. Now over here you can see just a warning. So it's not the error

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

[2:25:10] all I've done is I've used this keyword if not exist. So I want everyone to do this. So let me know when done. Few done will give me heads up. All you have to do is you have to run this command in order to

[2:25:24] create the database. And you can rerun this command in order to see the 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] 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? So 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 DB 200. 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've got in your DBMS. So see we have got these databases. Now you'll see some extra databases. For example, you'll see information schema, performance schema

[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 store. 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 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

[2:30:26] Oracle and Postgress. I've just worked on MySQL and SQL server. So you can Google it if it works on Oracle or not. Or if you want I can Google and I I can tell you the answer.

[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 database and then I'm creating I'm creating the table and I'm inserting the

[2:31:15] data into the table into this database. So this we have performed previously also. You can see that the table has been created and the data has been inserted into it. So I'm just copy pasting this code on the chart. You can

[2:31:29] also go ahead and use any of the database and you can create the table into that database and insert the data into the table into the table. So I'll into the table 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

[2:32:13] this table and inserted the data into the table. So, you can copy paste this command from the chart. I've shared all the chart and you can run it. So, that 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:57] different different data types that we have in MySQL. Now before we get on to that uh I will just quickly tell you that how you can save your code. So one of you was asking me that Tikica this code we have written

[2:33:12] how we can save it. So how you can save it is can you see the save button over So all you have to do is now all of these things are something that if you just try it by yourself you'll able to do it because we all are using laptops.

[2:33:27] 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 Yeah we have got the save option over here also. This is a shortcut save

[2:33:43] script as. So the same options we have got. So what you can do is you can click on it and maybe you can save it. So I'll just quickly show you and then I'll give you time to just try it at your end. So let's say my SQL 100. This is what I'm

[2:33:59] saving this file. Now I'll just show quickly show you on the desktop. Okay. Where? Yeah, this is the file. MySQL 100. Now if I want to open it, how can I open it? Let me close it.

[2:34:15] How can I open it? Just like how you use any other software. Yeah. The first thing is if you want to open something, you go to the file. So here also I'll go you go to the file. So here also I'll go to the file and you can see open SQL

[2:34:28] script and I literally have to uh find where I have sh saved it. Yeah, here we go. And I'm I'm I'll open it. So in this way you can save the SQL scripts that you're writing.

[2:34:44] So maybe I'll wait for 2 minutes. You guys can just try saving the SQL script guys can just try saving the SQL script file.

[2:34:57] different type of data types that we have got in MySQL. please make sure that you listen to me. Very important.

[2:35:11] So the first data type that you can see is car. So car simply means characters. text, string, whatever you want to call it.

[2:35:27] 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 can take the values like you can have the car data type. The column can take

[2:35:41] zero characters. So that's a minimum range or maximum range is 255 characters. Yeah. Now when I say it's a fixed length string, what does that fixed length string, what does that mean? It means hear me out. So if I

[2:35:56] mean? It means hear me out. So if I write let's say id and if I say care and write let's say id and if I say care and if I give two so it means that ID when I insert the values when I insert the data into the table

[2:36:11] into the table in the ID column in the ID column it has to be of two characters. Yeah it has to be of two characters. has to be of two characters. So 1 2 1 3 3 4. So you're getting again

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

[2:36:44] Yes, state code. And I give the car as a data type. And I give three. It means that whenever I insert the values, insert the data into this column, it has to be fixed length. See, it says

[2:37:00] 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 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:29] [snorts] Now let's talk about var. So what do you think V stands for? What? what do you think V stands for? What? What does this stands for?

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

[2:39:02] it can take the characters up to 100. So let's say I create a column name let's say I create a column name the data type is var and if I give let's

[2:39:14] say 50 over here it means that when I insert the values into this column I can give anything I can give tikka which is of six characters I can give Ravi which is of four characters and I can give a very

[2:39:30] big name which is of 50 characters but if I give a very big name with let's say which is of 60 characters then it will go error. So it is telling us the upper limit that the upper limit is 50. You cannot go beyond 50 but you can choose

[2:39:45] any number within 50. It can be 1 2 3 4 any number for that matter. So this is what vare is. Yeah this is what vare is. Just give

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

[2:40:10] vcar is one of the data type. I is one of the data type. Anyways let's talk about text. So seeare 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:37] 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 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:07] 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. int you already know that in says integer and this is a limit of it you don't need to remember the limit but it can take

[2:41:55] negative numbers and positive numbers as well positive you have seen it can take 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 Yeah, age. You know that age cannot go beyond maybe 120, not more than this.

[2:42:22] You know that. Yeah, that 120 is also like a very high age that I'm talking about. So what you can do is you can go with tiny int. So let's say that you want to save salary. So for salary, int is a uh you

[2:42:37] can go with int. But when it comes to age or the numbers that you're going, you know that they're going to be very small. So you go you can go with tiny int small integer.

[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

[2:43:10] fail because again it has got some upper limit. So then you can go with big end. talk 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:41] Yeah, you can go with float. Double is again for the decimal. Yeah, you can double is also for the decimal numbers. But double is high precision 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:15] 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

[2:44:27] number of digits stored. I'll write it over here. Total 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] round it off. So it's all about u how many digits both of these get stored. So float has less precision. It means that if you have got a big number it may rounded off like this. and double has got higher precision where the

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

[2:46:17] boolean, you can be very very honest here because I understand that a lot of you are coming from the non-technical background. 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 means yes. So true

[2:47:00] true means yes. So true and then we have got false means zero. So it stores only two values that is either true or false and it is a very

[2:47:12] important data type. So for example if I just give you one uh lemon example you might have seen check boxes. Yeah when you're filling the form you might have you're filling the form you might have seen check boxes. So if you check it so

[2:47:27] ideally because I come from the background where I've also developed a 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 auto date and time system means it means that if you give any column yeah if you

[2:48:45] that if you give any column yeah if you write any column with the data type of write any column with the data type of time stamp what it will do

[2:48:57] So whatever your system uh time stamp is yeah the date as well as the time if I change it and if I make it something else then it will take that only. So else then it will take that only. So take system based date and time.

[2:49:15] Yeah current date and time as per the system. Exactly. Very good. Fine. So we have covered most of the data types all the important data types. I would like to tell you one more thing. So we have got signed and we have got

[2:49:28] unsigned. So let's talk about this also. It's a very easy concept. 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

[2:49:43] the data types. Now here I'm talking about the data types which can store numerical values. So here I'm talking about the data type which can store about the data type which can store numerical values. So signed simply means

[2:49:55] numerical values. So signed simply means that for example if I talk about tiny int. Yeah. So by default it is signed. By default it is signed. It means that it can store the negative and positive values. So if you remember from the

[2:50:09] values. So if you remember from the previous slide

[2:50:21] see we have got tiny int small integer this minus 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:02] and this is a lower limit. Five. What is unsigned means? Unsigned means that if I unsigned means? Unsigned means that if I make it unsigned. So if I again say age

[2:51:14] tiny. Okay. Here it is already written. So maybe okay let me write it. The writing is so bad. Okay. Tiny.

[2:51:27] So difficult to write on the note uh on the part. Anyways tiny int. And then if the part. Anyways tiny int. And then if I give unsigned what will happen? So this will give me extra cushion. How it will give me extra

[2:51:39] cushion? It means that I'm explicitly telling SQL, hey SQL, I want to use tiny int. But I do not want this negative numbers. I know that age is never going to be negative. Well, age is never going to be 200 and 255 also. But yeah, that

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

[2:52:09] extra cushion. So just remove the negative all the negative numbers and can you just give me extra cushion for the positive numbers? So that's how we can use it. Yep. So here you can see the limit is 0 to 255.

[2:52:23] So whatever numbers we had over here, they have been added over here. Yeah, you just have to do the calculation. This will come around to be 255. So we are saying I do not want negative numbers. So do not waste my

[2:52:40] negative numbers. So do not waste my range in negative. Yeah. So I just want the positive numbers. So unsigned will help me to increase the range. Am I clear with what is sign and unsign

[2:53:08] now you have to allow me a minute I forgot to open my notes because again we 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

[2:53:21] yeah the table is going to be a big table. So right now I'll ask you also to copy paste it but after the class please make sure that you type all of these

[2:53:33] statements. So what you can do is you can um I I'll share all these notes also with you. I'll share these PPTs also with you. But I would suggest you to make your own notes. Right? So what you can do is whatever comments that I'm

[2:53:46] 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 active, when you're participating on the chat, when you're doing the hands-on,

[2:54:01] code and pasting it and saving it in your notepad file. What happen is you 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

[2:54:15] I do not want that to happen because each and every minute of the session is important. So anyways, here we go. This is a table. So we already have got the database, right? So I am just deleting all this.

[2:54:28] So this is a database and here I'm going to create this table. So I'll just quickly run this command. Just give me a second.

[2:54:42] Yeah. So the table has been created. Now you can see that this table has got married data types. So it has got int is

[2:54:54] unsigned. What was unsigned? Let's quickly see what was unsigned. Let's quickly see what was unsigned. Only positive numbers. So it gives a really huge question. Yeah. If I just remove the negative numbers, you you can

[2:55:07] do the calculations. So it will be something around 4 ++. So I get extra cushion of positive numbers where you already know. Okay. Here again something called small int also which is I think smaller than tiny int. Then or

[2:55:25] I think smaller than tiny int. Then or maybe let let me make it tiny int. Yep. Then decimal. So I'm giving the precision over here. So 82. What does that mean? 82 means that I can have digits like 1 2 3 4 5 6

[2:55:42] 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 then I've got float. Then I've got boolean which can have

[2:55:55] Then I've got boolean which can have true or false. 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

[2:56:11] the table with so many data types. So I want you guys to see my screen. 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

[2:56:25] This is int. This is vare. Yeah, this is int. This is vare. This is tiny int. This is again tiny int. So this one is unsigned. This one is signed by default. 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:39] So, uh, see guys, uh, in the last in the last insert command, we gave the column So, we can skip the column name. Over here, if you see, I have skipped the

[2:57:52] 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:05] Yeah, the sequence would will be the first value that I'm giving is for 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. I we are going to talk about this more. So, uh right now our agenda is to learn about the data types. Yeah, the agenda for

[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

[2:59:32] I'll just give a string over here. Let's say I am giving a string over here was a quantity. But what I've done is I'm I'm giving a string. It's an in

[2:59:44] integer. Yeah. It's a tiny in. So it will throw error. Now if I run this, it will throw error. It has thrown error. It has thrown error. See,

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

[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] constraint? We are going to look to that today itself. So let's do one thing. Let's quickly create a table first. So what you can do is you can open a new file and the first thing that you can do is you can create a database

[3:01:40] 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 double clicking on it. Now these things we have already done. So I'm quickly telling you it's like I'm rehydrating. So that's why I'm pretty quick over here because the

[3:02:06] 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:35] 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] So here maybe I'll just write anel and I'm okay with the other things. Yeah. So if I execute this see it got executed. So this is fine. But if let's say that I've got some data of some of the

[3:04:34] students. So I've got a 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 that if you have the full data, if you have the full data, when I say full data, I mean to say that you've got the data for

[3:05:05] all the columns of your table. So if you've got the full data then it works perfectly fine but if your data is not full right now I don't have full data

[3:05:17] right then I have to explicitly define the columns that I'm going to use. Yeah 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

[3:06:36] need to understand my predicament also over here that if I give you time for typing each and everything in the class then I may not able to cover all the concepts and I want to uh cover maximum things. I want to uh give my maximum

[3:06:51] the maximum knowledge that I have with you all

[3:07:03] name col in this table I've just got four columns as you can see.

[3:07:19] 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? So constraints are uh think of it, they are nothing but the

[3:08:08] rules that you apply on the column. Yeah, they are nothing but the rules Yeah, they are nothing but the rules that you apply on the column. So we'll learn about few of the constraints

[3:08:20] and then we are going to write the code around these constraints. So the first or let's do one thing we'll go one by one. We'll write the code for one by one. Rather than going through the whole thing we'll go one by one. So

[3:08:33] the whole thing we'll go one by one. So the first constraint is not null. So see as I told you think of it constraints are nothing but the rules that you apply to the column. Now if I say that let's say I have a column id column and I want

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

[3:09:03] database. So here also we are doing the same thing. We have got multiple columns in our U table and we simply want to apply some

[3:09:15] rules to those column. So let's say I've got the ID column and I want that whenever you're inserting any of the students record the ID shouldn't be

[3:09:27] null. You're getting the ID cannot be null. As simple as that. So I can use not null constraint for that. So you can see what it does. It disallows null value. So it means that the value should be provided. Yes. It

[3:09:43] means that you're making this column required. As simple as that. Yeah. If you enter any of the row, this column should have value. should have value. Okay. So we'll do one thing. We'll

[3:09:57] Okay. So we'll do one thing. We'll just give me a second. Yeah. just give me a second. Yeah. So we are going to create a table

[3:10:17] going to 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 my multiple columns. So one of the column is ID and the second column is let's say

[3:10:45] name. Okay. I'm not going to make it a very big table. columns. it's not not null. How I can make that column as not null? So all I

[3:11:00] make that column as not null? So all I have to do is I have to write not null 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

[3:11:15] is not null. If I want, I can do it for name also. Let me do it for name also. Doesn't matter, right? So let me do it for name also. So now it means that in for name also. So now it means that in order to insert the data both the column

[3:11:29] should have some value. Let me create this table. The table has been created and I'll just quickly say insert into I want everyone to see my screen. If I give one, any name for example let it be. I am just giving any name for that

[3:11:44] matter. Doesn't uh matter what name I'm giving. Yeah. So if I uh 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 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 this. Again I do not want you guys to write the whole code. Maybe you can take it from the chat and you can modify the existing code what you have otherwise it

[3:13:16] will take a lot of time. Would you understand by unique distinct values? Yes. So in very simple words if I have a column

[3:13:28] so I will not allow I will not allow duplicate values in those columns. So any guesses what those columns could be like you might have seen in your real like you might have seen in your real life here and there ID very good yes ID

[3:13:44] 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 a at the rategmail.com it will get inserted. This is the first time I'm inserting. But if I again insert maybe I can you know re-execute the same statement.

[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 quickly give you this code on the chart. You can copy paste the code. Again do not type the code right now but yes you should definitely do a lot of typing of

[3:15:10] the code after the class. Right now we are copy pasting so that we learn maximum concepts. But yes, after the class you have to make sure that you do a lot of practice by typing each and every line of code.

[3:15:25] every line of code. I'll wait for a maybe a minute. Now we'll learn about the next constraint that we have that is primary key. So let me open the notepad because I would like to uh write something around it or maybe

[3:15:41] I can write over here itself. It's fine. So So when you write anything in the comments the interpreter is not going to

[3:15:54] read this. These lines are for us. The these lines are not for interpreter. So anyways we're going to learn about primary key. Now you already know about it. I we have already discussed it. So I'll quickly redisuss it. So primary key

[3:16:08] I'll quickly redisuss it. So primary key is a unique identifier of each row. For example, if I talk about human beings, so I think our genetics, our DNA, these are the unique identifiers. Yeah, that will or our fingerprints, these are what

[3:16:21] the unique identifier. So for every row also, if you want maybe some column to uniquely identify that row. So we can declare that column as primary key. So

[3:16:33] I'll also write it. So basically if you say that a column is a primary column primary key column it means see that's what it means that it is uniquely identifying it right uniquely identifying the row of data. So

[3:16:50] definitely um every row that that column will take like every piece of data that column will take will be unique also it cannot be null. So it doesn't take nulls as simple as that it's a primary key it

[3:17:03] doesn't take nulls. So for example, we are storing the data of humans. Yeah. Of humans. Let's assume, right? We are storing the data of human humans. Now you know that names can be common. Then

[3:17:20] gender can be common. Most of the things can be common. But let's say uh thumbrint. So thumbrint is not common. So this we can make this as a primary key. So this will be unique for each and

[3:17:33] every human make. Yeah. And we we also do not want it to be null. So if you're inserting any data, it it cannot contain null value. So that's what primary key says. Okay.

[3:17:48] 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] now you understand this part that it can 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 a first value one and rai. So this is a first value one and rai. So employee ID. This one is what? ID. Yeah.

[3:19:23] 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 throw error. Why? Because it's a primary

[3:19:39] key. And primary key make sure that each and every thing is unique. Also, if I try to insert null in a primary key, again, it will give error.

[3:19:53] key, again, it will give error. So that's what I wrote that it has to be unique and it cannot take null values. It cannot take null values. Now let me change over here. I'm just changing the name of the table and I just want to

[3:20:08] name of the table and I just want to show you that if we can have multiple primary keys in a table or not. So let me execute this. You can see that it is giving error. It says multiple primary key defined.

[3:20:23] 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:33] composite key 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. notes the these points. See I'm going to give you these notes but I'll give you

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

[3:25:11] it have values from this ID column. So it can have let's say I've got maybe one 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

[3:25:25] customer ID because that doesn't exist over here. So this is what this is a foreign key. What is a foreign key? Foreign key is a key which is a primary key of the another table. So ID customer ID is a primary key of this table and we

[3:25:42] are using this as a foreign key in the orders table. So what is the significance of the foreign key? Foreign key make sureures that whatever values that you're filling in over here you're getting whatever values you are filling

[3:25:55] in over here should come from this column. Yeah it should always come from this column. So if I try to fill four over here it will give error. It says oh four we don't have any customer with the ID

[3:26:08] four but if I give 1 2 three values over here again and again orders made by one orders made by uh customer ID 2 it will happily take it but as soon as I give happily take it but as soon as I give let's say five it will give error no I

[3:26:23] five is not here it says five is not here so foreign key think of foreign key as a lo it's very loyal to the primary key it says that I will take only those values that exist in the primary key of this table. Yeah, of this primary key.

[3:26:39] This is what this is the primary column of this table. I will not take any other value as simple as that. So, it's super super loyal to the primary key and that's why we call it as a um foreign key. So uh this is a significance of

[3:26:55] having the foreign key that it only takes the values which are present in the primary key of the another table. Yeah, it cannot take any other value. Anyways, I will see the practical part of it and you'll able to

[3:27:10] 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:25] got these two tables. Department has got two columns and staff has got three columns. Now if you see over here which is a primary key in the department column quickly. Now I'm asking you such

[3:27:40] column quickly. Now I'm asking you such simple questions. answered. If you see here we are defining a If you see here we are defining a foreign key. Yep. So, department ID and

[3:27:54] here we are saying the department ID and this is how we do it. This is the syntax of it. So, we are saying that we have got a foreign key. This is a foreign got a foreign key. This is a foreign key. This is a foreign key and how it is

[3:28:08] 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 name of the table and the primary key of the table.

[3:28:22] You got that? So basically we have declared a normal column and then we are telling that this column is a foreign key. Yeah. Which this is a foreign key column. Then it's a foreign key column. It means

[3:28:36] there should be some primary key in some table. So we have to give that detail also that which is a table and which is a primary key. So how do we do that? We use references. Then we give the name of the table. This is what this is the name

[3:28:49] the table. This is what this is the name of the table and this is the name of the primary key. So it means it means that this foreign key is for this table and in this this column. Now

[3:29:06] 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 it and the ID of the department is 10. Fine. Now I'm going to insert the data into the staff table. So see what I'm doing is I'm saying I've got one staff.

[3:29:51] So staff you know like for different departments we have got staffing. So departments we have got staffing. So I've got staff ID one and this staff goes in the IT department. Yeah. Department ID 10. So this will very well

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

[3:30:18] the department table. This value exists in the department table in this column. in the department table in this column. But if I give 20, this will not work. Why this will not work? Because 20 doesn't exist. Yeah, here 20 doesn't

[3:30:34] exist. If I simply query select star from department, it just has got one u row of data. You just saw that we have inserted only one row of data. You can see over here. So it has just got one row of data.

[3:30:50] row of data. We'll make sure that department ID only have those values. Sorry, foreign key. Make sure the department ID, this is a foreign key. it can only contain those values which are

[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 throw error because I already got the student table. So I'll just quickly make

[3:31:17] that makes sense also because it's already there. It will throw error that this table already exist. Fine. So you can see that the table has been created now. the I've given a check over here that

[3:31:32] 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 guys, if if you're getting the error, this is how the behavior should be. here. So you should definitely get the error. This is how we are expecting it

[3:32:01] error. This is how we are 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

[3:32:14] 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 node it's 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:32:57] there's some setup that you need to do. [clears throat] also just that you have to follow some steps.

[3:33:11] for everyone to quickly try this constraint check constraint. The next constraint that we have is default. So till now every column that we are we have created we we were inserting the data in it. Now

[3:33:27] let's say that if we do not get any data in it. Let's say we are not inserting any data in it and we want that column to take any default value. For example let's say that we have got a table student table.

[3:33:41] Yeah. Now student table. Now this is uh student table of some school which is in India. So here we have got a country column. So

[3:33:55] if we are not giving the value explicitly for the country, it will automatically take India. As simple as that. So basically we are giving a that. So basically we are giving a default value to country column.

[3:34:07] So default value means that if I'm explicitly providing the value while inserting the data, if I provide USA, then it will take USA. I provide India, it will anyways take India. But if I do not provide anything then by default it

[3:34:20] will take India. Again if you have not understood that's okay rest assured once we write the code around it you'll able to understand. So let me take the code. to understand. So let me take the code. So I want everyone to see my screen. You

[3:34:35] can see that I've created the orders table. It has got one column that is 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

[3:34:48] questions so that you remain in the session. I do not want you guys to uh you know daydream during the session. See I've just got one answer. I repeat my question. What is the

[3:35:04] default value of this column? I'm using the default constraint and I'm giving a default value. Yes, it's spending. Right. So I'll just quickly create this table. Now here I'm inserting into orders and values I'm

[3:35:20] literally not giving anything. So if I run this see it got successfully run. run this see it got successfully run. Let me quickly query this table. You can see that it has got one one piece of sorry one row and this one row

[3:35:38] because I executed it once it has taken status as pending. If you want you can give some other value also. Let's say I give a value closed. So it will happily 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 then 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 some ID. Yeah, some autogenerated ID. So, we have got this auto increment.

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

[3:37:01] take the value one. So the second record will take the value. Let's say I say that it should increment by one. So that is what I've given as a rule. So the second will take two. The third record will automatically take three. So the

[3:37:17] this column will automatically u generate the value for itself without user explicitly giving. Yeah. And it will increment increment the value every single time. So again we'll understand

[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 uh we 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 I'll quickly run this and let me show you the result.

[3:38:44] Sorry. Yeah. So you can see automatically it is taking one and two. Yeah. Now if I insert more data let's say if I insert insert more data let's say if I insert um maybe keyboard

[3:39:02] it will take automatically it will take three let me show you you can see so automatically it it is in uh getting incremented so by default it takes one the initial value is one and the increment value is also one

[3:39:20] Now you can use it for any key you can so what you can do is so guys I want everyone to see my screen yeah then I'll give you time again so I'm just making the changes uh to the name of the table because this table

[3:39:36] already exist and if I recreate the table with the same name it will th table with the same name it will th error so definitely I can remove this now product ID is auto incremented

[3:39:48] column but it's not the primary key. Now let's say yeah I'll come to that also. Let's say you want to increment it with five. So what you can do is over here this is the syntax of it. You can give auto increment equals to five.

[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 we can have uh a table auto primary key. So I got confused because

[3:40:18] different databases different rules but yes it has to be primary key. Now you increment by five. Let me in let me quickly insert these products. Just give me a second. Okay sorry I have to insert the product. So I'm also

[3:40:34] changing the name of the because I've given auto increment equals to five. The given auto increment equals to five. The first value it is taking five and then uh 6 7. This is how we are defining the first initial value

[3:40:48] and you can also change this uh increment value also but we'll not cover that right now when we alter the table that time I'll tell you. So we have got something called set and then we have got at the rate auto

[3:41:03] increment. So there's uh there's a property that we can define when we alter the table. Yeah, Rajpal, if you delete the second row, the next increment will be the next one. It will not be the previous one.

[3:41:17] one. It will not be the previous one. Yeah, it will always take the next one. that uh we have a property. So, I would maybe I would like to talk about that

[3:41:29] 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 I just told you right away as I told you uh right now it was asking

[3:41:43] for the primary key. I need to check because in SQL server you can have an auto increment column without having it to be the primary key. But here in my SQL when I was trying to create it Vya it was asking for the it to be a primary

[3:41:57] key. I'll just check it if we can auto increment a column without having it to be primary key. In fact in my SQL auto increment feature wasn't there. Till some time back we had it in SQL server databases but

[3:42:11] we had it in SQL server databases but not in MySQL. Now we'll learn about composite primary key. So what is the meaning of composite? What is the meaning of composite? No, it will not throw error. You might have said that

[3:42:24] there might be something off with your with your syntax minute and then we'll move to the composite primary key. I do not want

[3:42:36] your questions to be unanswered and you're still trying and you know uh hanging around this part. I do not want that. So we'll wait for a minute and then we'll move to the composite. Now we'll learn about composite primary

[3:42:48] Now we'll learn about composite primary keys. So what is a composite key? So here what happens is if I give you this example just give me a second let me see the example that we have got. So let's say you've got a

[3:43:01] table. Yeah you've got a table. Now in that table as a primary key. You getting? It is not possible to define one column as a

[3:43:15] 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. Yeah. Now here I'm having the name of the country and then I've got more details about the country let's say the details about the country let's say the latitude longitude all of these things

[3:43:40] and then the code country code also I've got now I want to define a primary key I want to define I've got this table and I want to define a primary key well latitude longitude can also be a primary

[3:43:53] key because every country will have distinct latitude else let's um number of states. Yeah, something else or number of cities. So some some details about the country.

[3:44:09] 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 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 sharing the name something like that now countries may share some sometimes names

[3:44:47] also they can have let's let's assume that that's hypothetical I know. So what I can do is now I want to define a primary key.

[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 th 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:22] 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 actually repeated sometimes. So let's say I make an order. I'm just giving you

[3:46:37] 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 getting? So I'll have the order ID repeated. So 1 2 3 1 2 3 1 2 3 product 1

[3:46:51] 2 and 3. So that is again a problem. Now I have to define a primary key. What should I do? So what I can do is I can use a combination of the column. So let's say I make these three column combine as my

[3:47:06] I make these three column combine as my primary key. So what does that mean? It means now hear me out. It means if my order ID is 1 2 3, product ID is one and let's say quantity is maybe 1 one one let it be yeah one.

[3:47:22] So the combination of these three should be unique. So if you see this is I'll just take the second one. I'll take the third one. Now if you see

[3:47:34] the combination of these three is unique. It is always giving me a unique value. Yeah. If I go with one more, let me give you one more example. Let's say the order ID is 1 2 4 product is one

[3:47:49] one and the the quantity is different. So the combination of these combination is unique. So that's what composite primary key does. It's same as

[3:48:03] 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. Now I want everyone to see my U screen.

[3:48:34] 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 primary key. It will very well work. Now again see one is repeated but course ID

[3:48:47] 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 unique. Here also the combination is unique. See if I run it, it will run.

[3:49:01] But let me do one thing. Let me again run insert run insert and let's say the product or the student and let's say the product or the student ID is true and the course ID is also 1.1

[3:49:15] you're getting. So the combination is no more unique. It has already been used. It has already been inserted in the table. So if I run this, it will throw error that it's a duplicate key because this combination is not unique anymore.

[3:49:31] 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 difference. The only thing is composite key says that I am again a primary key

[3:49:45] but I'm a combination of multiple columns. In order to be a primary key, I'm a combination of multiple columns. So I want you guys to run this code. The last insert statement will not run. Yeah, it will throw error.

[3:50:06] it's not a new thing. But we'll see that what all we can do with the select statement. So we start with the keyword select. Then we we were giving star from the

[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 again from and then table. Now what we are going to do is we are going to a table and we going to add some data into that

[3:51:03] and we going to add some data into that table. So 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 is s added to it. So now we are going to create the student table. So if you already have the student table please go ahead and drop it.

[3:51:57] And once it is done I want all of you to recreate the table. we have got the table. Just give me a second. I think it did

[3:52:11] 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:42] data that I have inserted into the table. Yeah. So as this is something that we have run so many times. So I say select star from student. So this will

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

[3:55:09] Fine. Now, as I told you that when you're reading the data, of course, you can read the data directly like this. Of course, you can do that. Yep, we have done it also. But let's say while reading the data now I do not have any

[3:55:23] reading the data now I do not have any let me again okay I do not have any percentage column over here but let's say that fine I've got the marks but I also want to know the percentage of the students. Yep. So I am not going to make

[3:55:37] any changes to the original data rather while reading the data I'm going to apply some maths. So what I can do is over here I can say maybe okay I'll just over here I can say maybe okay I'll just put like this and I'll say marks

[3:55:58] let's say the marks are from not okay let's say the marks are 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 the

[3:56:27] data while reading the data itself I want to see something yeah so I'm just want to see something yeah so I'm just creating this column so let me run this creating this column so let me 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'm literally not liking this name. So you can give an alias to your column. So I'll just give

[3:57:11] 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 original name of the column is name but while reading the data from the table I've just given an alias to this column. So I can do it but whatever I'm doing

[3:57:43] over here in the select statement is only for the output. It is not making any changes to the original or the underlying table. Yes, we can definitely do it to do two decimal point Rajput. So we have got a lot of functions. We are

[3:57:58] going to learn about those functions. So not right now but yes we have got a 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 those functions you can see. Yep. But it's not something that I would like to maybe talk about more.

[3:58:26] Now till now what is happening is it is returning all the rows. Yeah, it is returning all the six rows that we have. Now what I want is I want to filter the data. Right now we just learned about that how you can return all the columns.

[3:58:40] How you can 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

[3:58:53] to do is I want to filter the data that is returned as a part of the output. I want to filter the data. So how I can filter the data. So I'm just making the filter the data. So I'm just making the changes to the same.

[3:59:06] Right now it is showing me all the students. All the students. Let me also say let's say city also. Yeah. Every all I've got six students. So it is showing me all the students. But I do not want all the students. Rather let's say

[3:59:19] there's a requirement and the requirement is I want to see data of only those students that are from city Pune. Yes, students that are from city Pune. Yes, students that belongs to Pune city. So I can use

[3:59:33] where clause let me see if I've got anything on the PPD for the wear clause. Syntax still here and then you use the where clause. So where and then you give condition over here. Okay, you can give a boolean condition over here. not

[3:59:47] necessary boolean condition. You can give any condition for that matter. So basically you can give any condition but for any row. So anyways we'll not talk about the condition right now but yes so this will this will help us to

[4:00:03] yes so this will this will help us to filter. 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

[4:00:17] I want the students who belongs to the city Pune. So see this will help me to filter the data that is returned by my query. So where clause is very important.

[4:00:33] Again I'll give you one more example then I'll give you time to uh practice it. So let's say I want all the students

[4:00:45] who who have got distinction marks. So marks greater than equal to 75. So this will return me all the students with greater than equals to 75.

[4:01:07] I want everyone to try this out both the both the queries. different operators. For example, for example, let's say I can add the

[4:01:19] example, let's say I can add the operator where marks plus 10. Let's say yeah that doesn't make sense. But you can see over here I'm just adding arithmetic operator that is plus. So I can say that where marks + 10 is greater

[4:01:32] than 75%. So I can add the different different operator. I can add the comparison operator. it. Yeah, the previous query that we wrote.

[4:01:48] H this is what comparison operator where you're comparing you have marks greater than equal to 75. So you're comparing the two values right that's what comparison less than greater than greater than equal to less than

[4:02:01] 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 take take this query. So I

[4:02:26] can say that I want all the students who belongs to city Pune. Yep. And so here belongs to city Pune. Yep. And so here I'm adding a logical operator and marks

[4:02:38] greater than 75. So here and is what? And is a logical operators. I hope that you guys know about it. I think I've already asked you and everybody told me that you know about the logical operators. So we have

[4:02:52] got three logical operators. I'll go slow and or and the last one is not. So here it is going to return us. It is going to return us the students

[4:03:09] 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 Amit 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:42] 75%. So whether they belong to Pune or not doesn't matter. So either like I so [clears throat] a 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 all the three logical operators.

[4:04:44] would like to take two minutes of halt over here and I'm just sharing this queries with you all. So what you can say Rajpal see a student will belong to either Pune or Mumbai right? So you can say city

[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 this queries yet do not write the query. Play look into the more operators that we have got.

[4:05:35] range and set operator very very important operator. important operator. So as a name says that let's say that uh So as a name says that let's say that uh 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:03] 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:52] 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. range operator and n is a set operator. It's a set operator.

[4:08:15] We have got match pattern matching operators also pattern matching is like see sometimes it's not that we have the exact value let's say I do not have like city equals to pune rather I say all the

[4:08:30] cities that starts with P or all the cities that ends with E something like 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 let's say that I want all the students where the name like name like so

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

[4:09:11] students where the name starts from A. Yeah, name starts from A. So let's say I I'll just um maybe give it a twist. So I just want a letter or let's say I want

[4:09:23] just I just want T letter between their name. So I just want that between their name. So I just want that the student with the letter T. So now if I run this okay again we have just got okay let me say R. I'm not sure

[4:09:39] 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 only one character. It can be any character but only one character. Percentage means any number of character. Hear me out. Percentage means

[4:10:39] any number of character and any character. But when I give hyphen it means it can have only one character before R. You're getting before R it can have only one character. So P R why P is only one character before R. And then

[4:10:54] any number of character after P R. So this will return me this is returning me Priya. So this is what this is pattern matching. So I'm doing the pattern matching using the modulus or you can say percentage and using underscore not

[4:11:11] say percentage and using underscore not hyphen sorry underscore. So I repeat percentage simply means any number of characters and any character. This means one character but any character. Yeah, I can also give something like this. Let's

[4:11:28] can also give something like this. Let's say okay, I'll give a. say okay, I'll give a. Okay, anything before a and any two characters after a any two characters after a I'm not sure if it will so I

[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 just giving any random name. Let's say I've got the student si T A

[4:11:56] RB. Yeah. Suppose I've got a student like this. So this will return this query will return this. So I've got two underscore. It means any two characters. But it has to be two in number not more than that. Percentage is like any

[4:12:10] number. Yeah. One underscore means one character. One character. Two underscore. So this one and this one. So that's how it is working. So, am I clear with the pattern matching?

[4:12:33] simply matching the pattern using percentage and underscore. Am I clear?

[4:12:50] I'll give you time. So we have got the last operator null operator. So let's last operator null operator. So let's say you simply want to u return the rows where maybe the column is not null. So the column is not null.

[4:13:07] the 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 null. Let's say I want to see that if I've got any column with a no name. So I don't have any such column. So that's why it is not returning anything.

[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 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

[4:14:08] Okay. So we are done with the operators in the v clause. I'll take a hold for 2 minutes. So you can just copy paste and play around with it.

[4:14:22] that. So I'm assuming that you guys are going to practice after after the class also. But fine I'll give you more time.

[4:14:41] do with a 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 have 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 some. So you're getting it has nothing to do with the primary key or composite

[4:15:27] 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 instead, let's say your table is having the thousand rows. You do not want to see only thous 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 [clears throat] of rows. where clause in it. It's not that you cannot add clause. So I can also say

[4:16:57] let's say we have marks greater than let's say 75 and then I can limit my data. So I can also add clause. So first

[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.

[4:17:23] 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 querying 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 want to sort my students as per their marks. So what I can do is I can say

[4:18:45] marks. So what I can do is I can say order by the order by marks. Let me execute both the queries. Yep. So you can see this is the result of first query and this is the result of second query. Let me highlight it. Result of

[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 um uh like

[4:19:28] you know from what do we call that from from the low she's second minimum and yes so when and then so forth Rahul has got the maximum marks so it is sorting the data by marks but by default it is sorting in

[4:19:44] the ascending order and if you want to sort it in a descending order all you have to do is you have to explicitly give dc. So it means that I'm going to sort the data in the descending order. Now Rahul

[4:20:01] uh is being seen first because Rahul has got the maximum marks. So descending means highest to lowest. Yeah, it starts with the highest and it moves to the lowest. Ascending means from the lowest to

[4:20:15] 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 explicitly give. I'll give you more examples and then I'll give you time.

[4:20:27] But let me do one thing. Let me give you time right right away. time right right away. So you can just try this code. So that I'm going to cover a project in the last class

[4:20:42] and mostly we are going to have an extra class. So instead of seven we going to have eight class mostly and I'm going to cover the project in the last class. Done everyone.

[4:20:54] 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 want to sort the data on marks. So marks

[4:21:10] 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. So see I'm sorting the data. Okay, I'll

[4:21:24] just do one thing. I'll make it city first because that will make more sense. So city will be sorted in ascending order. Yeah, alphabetically it will it

[4:21:37] 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. Pune, let's say I've got three students. So first the data will be sorted as per

[4:21:51] So first the data will be sorted as per the city then it will move one step forward and inside the city the data will be sorted inside uh each city the data will be sorted as per the marks. So let me show you the result. Yep. Now you

[4:22:08] can see I want everyone to see my screen and be attentive. It's a very simple and be attentive. It's a very simple thing. Now you can see that the data is sorted as per the city in ascending order. Chennai, Delhi, Mumbai, Mumbai,

[4:22:21] order. Chennai, Delhi, Mumbai, Mumbai, Pune. Yeah. Now the second thing, the got two students for Pune and two students for Mumbai. So let's talk about Mumbai. Yeah, we have got two students for Mumbai. So whom should I show first,

[4:22:36] Anita or Niha? Yep. So we have got a second level of 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 nha. So that's why we are seeing Anita first and then nha. So in this way you can do the sorting on multiple columns

[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 Ha has secured more marks that will be shown first. So if you do it vice versa that will not make sense. So first it will sort via

[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 today's

[4:25:31] session. So you can just quickly create a database with the name of your choice. You can give any name to your database and please make sure that you also use this database. So I'm going to execute these two commands that you see on my

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

[4:26:13] because if you're using lab provided to you by simply learn then definitely you'll you have to create the new database because everything is reset in I think every 5 hours. So anyways I'm creating the table also I'm creating I'm

[4:26:29] inserting the data into the table. So I'm done with all the configuration. Maybe I'll wait for a minute for everyone to finish up the configuration. I'm sharing all the code on the chart. So you can just copy paste it.

[4:26:43] Yes, you can just you can use the same database with Yeah, I'm very much okay with it. Yeah, that was a different table. That table we created in order to learn about auto number constraint.

[4:26:58] But this is uh if you want you can drop your existing table and you can recreate your existing table and you can recreate this table to avoid any confusion

[4:27:11] here we do not have any auto number. If you see done everyone now yesterday we learned about select like we started with the select statement just give me a second and then

[4:27:24] we learn about where we learn about uh various things in select we learn about order by clause then we learn about limit clause so uh though this slide might look not very important to you but this is very important because when you

[4:27:39] start writing complex query is you should know that what is the order of 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

[4:27:53] table where let's say um city equals to Pune and then I am also having order by let's say order by sub column and then

[4:28:09] limit some number. So if I've got a query like 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

[4:28:23] execution. So when I say order of execution, I mean to say how this query 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,

[4:28:36] from is executed. So basically it will check that from which table you are querying. So it is going to execute this from from this table. Then it will then

[4:28:48] what it will do? It will filter the data. Now your table might have 100 data. Now your table might have 100 records,

[4:29:01] belongs to Pune. So it will first it will go to that table. So from which table? Then from this table it will it will execute the where part of the query. So it will simply

[4:29:15] simply fetch these 10 rows. Yep. Then it will 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

[4:29:28] want to se select right? Select is all about what all columns. So right now in this query I'm selecting all the columns. So then it will select the columns. Now it will get some 10 rows and u the selected columns and then

[4:29:42] order by after that once it has got the result it is going to sort it and then it is going to give you the result with whatever limit you have

[4:29:54] 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 when you start writing complicated complex queries you should know that how

[4:30:08] 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 we filter the data and then we filter the columns once we have the filter row

[4:30:23] 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 apply order by and once it is ordered by then it applies limit

[4:30:39] 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 definitely know. [clears throat] Okay. Now we're going to

[4:30:51] move to the next topic that is aggregate functions. So when I say aggregate aggregate the word aggregate what comes to your mind? Average all together very important right

[4:31:06] 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 an example, let's say I've got a table just

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

[4:32:25] multiple 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

[4:32:40] going to sum all the numbers. So let's say I'm applying this function on all 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:23] 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 some 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 null. This is null. This is null. This is null and this is null. So it is going to count all the null values. So basically this will give me six. Yeah, this will give me six. Now I'm saying again I'm

[4:34:09] repeating it 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 get give me? Seven

[4:34:53] records right? Seven records because 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

[4:35:08] six. So why it is giving me six? Because it will count only the not yeah not null 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

[4:35:22] it for some other column let's say let me do it for city. 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

[4:35:36] the seven rows have value in the city column. So definitely everything will be 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

[4:35:52] of city. I have to provide some numeric column over here. So I'll provide maybe column over here. So I'll provide maybe marks. So this will do the sum of all the marks. All the marks that I've got. Yeah. Sum of all the marks.

[4:36:09] In the same way I can run the other 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

[4:36:25] marks is of Rahul. So I can also run for maximum months. 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. 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. So you can't give values in continuation

[4:37:02] to it. But see if you rerun this. I'm not going to do that 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 three times.

[4:37:16] So whenever you have new data you have to run the whole command like this. So now we going to learn about group by. It's a very very important It's a very very important concept in SQL. So what do you mean by

[4:37:31] 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 something. And what will we will group in SQL that uh of course when it comes

[4:37:46] 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 students, right? And uh we have got the data of students. So, let's say I want

[4:38:03] 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, create the groups as per the city. Pune,

[4:38:17] Mumbai, Delhi and Chennai. Yes. So I'm making the groups as per the city. So I'm simply taking all the distinct values in

[4:38:29] 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 these are my groups. Yeah, these are my groups. Then what I I can do is I can

[4:38:45] perform some aggregate functions on the groups. You're getting so first you're going to group your data as per some parameter. In my case I've decided the parameter to be city. I'm grouping the data via city.

[4:38:59] So I'm picking up all the distinct cities that I have in my data set and cities that I have in my data set and I'm grouping I'm going to group my rows [clears throat] as per the city. The next thing that I'm going to do is once

[4:39:12] the grouping is done, I can perform some aggregations on the group data using the aggregations on the group data using the aggregate functions. So for example, I aggregate functions. So for example, I can say that maybe I want the count of

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

[4:39:38] aggregate function. I can also perform sum. So b what I can do is I can sum the pony students numbers. Yeah I can sum their marks and I can literally

[4:39:50] write it over here. So I think that's going to be around 150. Yeah. So basically I'm going 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

[4:40:04] can group the rows with the same value. I can group the rows with the same value in one or more column. So I 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

[4:40:19] city, right? I can group it with more columns also. I'll show it to you when columns also. I'll show it to you when we write the code and then perform. So I'll quickly show you the syntax and then we'll write the uh code around it.

[4:40:32] Now GPA is very very important for your interviews also. Please make sure that you understand and it's a very 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

[4:40:46] 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 then you have a table name that select from so and so table. Now see you're going to group by some

[4:41:01] column right? So here you'll give group by and you'll give the column name that 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

[4:41:16] as well as um maybe something else also. Let's say as as well as area. Yeah, I show show that to you when we write write the code. Okay. Now in select in

[4:41:29] select this is very important. Yeah. In select we give the column name. Now this column names this column name let me clean this off. This column name

[4:41:42] should be same as that of this column. So you're getting if I'm grouping by city then this can be city and this 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

[4:41:58] right now. You may not understand 100%. But if you don't listen to me because you'll not able to understand it once we that you listen to me. Once we write the code I'm going to reiterate and you'll

[4:42:11] 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 So in the select statement you can have either the group column.

[4:42:25] It means the column with which you have grouped your data or you can have [clears throat] the aggregated column. Yeah, you're going let's say I want I'm Yeah, you're going let's say I want I'm counting or I'm doing the sum of marks.

[4:42:40] So I cannot have any other column. I cannot have any other column. Not cannot have any other column. Not possible. columns. Okay. Your question will be answered in

[4:42:56] some time. I've already answered this question but yes your question will be answered again in some time. So uh now we are going to write the queries around group by so that you understand the concept.

[4:43:12] So we have got the same data. I want everyone to see my screen. So here what 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've grouped by here I've given city. I've grouped by city and this is the aggregation.

[4:44:17] 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. Can you see I'm getting the error.

[4:44:37] that every column in the select must 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

[4:44:53] because I've given name I'm not 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

[4:45:08] rule this is a thumb rule of group and you should definitely remember this very you should 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

[4:45:22] I'll wait for a minute. I want you guys to try running this query 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 to

[4:45:53] give proper name and that's why that's what I've done having I'll come to that sedat 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:27] 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

[4:47:08] your group data this is what this is a group data thumb rule you have to use having. So with the help of having you can So with the help of having you can filter your group data. Very important.

[4:47:21] You cannot use where in order to filter your group data. In order to filter your 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 SB. 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 by 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 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

[4:50:14] rows of data. So I want everyone to try this out. Let me quickly answer the this out. Let me quickly answer the questions on the chat till then.

[4:50:26] 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 all the four cities that I've got but

[4:52:09] [clears throat] 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:38] 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 ways. So it's not that

[4:53:11] So here [clears throat] 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:05] getting all the students [clears throat] and then the group data. Now this is going to give me this group data right.

[4:55:17] 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 it's going to work. So I just share this with you. You can just have a look run

[4:55:31] 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 colors of group by. But as I told you SQL or for that matter any coding

[4:55:44] 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. Anda 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. or you select from line number 35 to 38. Also in having average marks the greater

[4:56:11] than or smaller than sign. Yeah the greater than sign is missing on line number 38. The greater than sign is missing.

[4:56:29] 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 let [clears throat] me explain you uh it uh to you with the help of one real life uh to 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] You need your own dough. You make your 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 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 readym 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. So, uh in SQL we have got a lot of built-in functions. So, we have got a

[4:59:00] huge list of built-in functions. We are 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

[4:59:13] manipulate your data you can perform calculations you can format output so 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

[4:59:26] fine so let's say um for example I've got this name tika so 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

[4:59:41] the SQL built-in functions in today's class? class? Can you So, any of the SQL functions

[4:59:55] just hold on up to when I gave only aggregate I got the error.

[5:00:09] 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 is the problem. Now these are clauses t

[5:00:23] 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 in today's session only. I've got answer

[5:00:38] 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 are simply using in order to count the number of rows, right? In the same way

[5:00:54] 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 might be some there there is some code written behind the scene. So we are

[5:01:08] 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 output it can be anything. Let's say you can format your date. Yeah, your date is

[5:01:22] 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 input it process it and it gives the output.

[5:01:44] already told you. Yeah, you are using something that is already built. So this will definitely uh make your life easy. Yeah, you don't have to write the complex code, complex logic.

[5:01:57] Definitely your code will be cleaner. Yeah, because you're just writing one 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. Yep. 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 single

[5:04:41] 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 are not clear to you once we do the practical it will be 100% clear. Still

[5:04:57] 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:10] I'm sure things will be definitely clear by the end of everything whatever we are 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 seen this. And then we also have got window aggregate functions. So this we

[5:05:51] are going to look into I think if 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

[5:06:05] numeric function. Now as the name says numeric functions, it means that these functions can work on numbers.

[5:06:18] Yeah, on numbers. So you can do 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

[5:06:31] now we need more data because we are playing with numbers. So for that let me 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 we have anything and solution we see if we have anything and then we work on it. you should have the data ready in order to run these functions or 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:33] Okay. I'll give you one by one. So over here you can see I've got the 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 with the rounded salary so that you

[5:09:32] 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 rounding it. So it uh get rounded off to

[5:09:45] 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.90 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.

[5:11:58] So it will give you the upper number. So every single time it will give the upper every single time it will give the upper number as 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:42] number but seal would have given me the closest uh upper number. So like this h [clears throat] fine moving ahead. Now you can also give the absolute. So you know what absolute is. We learned in school what absolute

[5:12:59] 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. So here also it's doing the same thing. We are simply subtracting bonus with

[5:13:13] 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 negative number into the positive number.

[5:13:28] 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] [clears throat] What 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 u when you're dividing two numbers there are two things. One is the number

[5:14:05] 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 divide 100 by two. So this is the number

[5:14:18] number that you want to divide this number with what? So here [clears throat] I'm dividing all the salaries by two. 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 a basic maths.

[5:14:49] Right? Modulus. Just give me a sec. Uh just give me a second. is how we can write the comments in MySQL. You can use hash.

[5:15:12] Fine guys. Am I clear with mod? It's nothing. It is giving you remainder. So we have got the nu uh numerator and then we have got the denominator.

[5:15:25] something like that which I totally forgot. Fine. Moving ahead. Power. Everybody understand what power is? Yeah. Maths. A lot of maths. 2 raised ^ Yeah. Maths. A lot of maths. 2 raised ^ 3. Yeah. 3 raised to ^ 2. So again you

[5:15:37] have got some two numbers involved in order to calculate the power and that's what 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

[5:15:51] calculate the salary to the power of two. So if I run this, because we are taking the salary and we are you know

[5:16:04] multiplying it by itself. So yeah, so these are the some of the important 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

[5:16:19] don't explore also that's perfectly okay. I would suggest you to rather focus on the interview questions and focus on I'm going to tell you we have got something called lead code questions. So from there you can learn.

[5:16:32] So don't get into all these functions and all because see if you try to mug up all these functions anyways you're going to forget and nobody expects you to There are some basic functions that anyways you'll able to remember if you

[5:16:46] anyways you'll able to remember if you practice. So you can just try my SQL practice. So you can just try my SQL functions.

[5:17:01] of MySQL and here you'll find mediate functions. Dunkey 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 built-in functions. are you can see that we have got so many functions. But don't break your head into these functions otherwise 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 all the functions you'll be super demotivated nobody asked these functions some important functions and that's more than enough.

[5:18:16] class I'll give you a few of the links you can use lead code in order to 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 so just allow me a minute. Yeah. So as the name says

[5:19:09] yes uh Shri 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 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 Then you can do calculation on the that what you can calculate how you can do the maths on string function. So you

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

[5:21:14] what all you can do with the string let's say you've got hello so what all you can do with this string so if you manipulate ating it. Let's say I'm just making it in caps. So I've got a lot of functions to manipulate.

[5:21:27] 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 characters are there? There are five

[5:21:42] characters. I can also do some extraction on the string. So I've got 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:54] letters. I want to extract the last letter. So it will give me last letter. So all the string functions are broadly categorized into these three. So either 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

[5:22:42] fetching the employee name from the 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 uppercase. So

[5:22:56] what I can do for that it's very simple 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] it. That's all. You don't have to worry about how it is made, what is this uh what is the uh science behind it. You don't have to worry about all of these things. So here also it's the same thing. So we have got upper function.

[5:23:27] I'm just passing my employee name and magically this will give me all the employees with the with [clears throat] a upper up up in the upper case.

[5:23:41] In the same way I've got lower. Now this is very simple. This will give you all 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 functions. So this of the calculation functions. 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

[5:24:54] is that we have got some extra spaces over here and over here. Yep. So, trim. over here 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 mean to say the extra spaces if you have any over here or over here. So at the starting of the string or at So at the 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 defin 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:09] 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 want you guys to guess what this

[5:26:25] 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 you can see that all the spaces have

[5:26:40] been replaced by underscore. I can do it for something else also. Let's say that whenever I've got a now [clears throat] that's going to be crazy but yes whenever I've got a just replace it with underscore. See it is

[5:26:55] replacing a with underscore. You can see so here you have to uh give capital a and small a okay what I can do is let's say space I

[5:27:08] want to replace it with let's say something else I'll say apple you can basically replace anything with anything

[5:27:24] as simple as that I repeat you can Replace anything with anything. simple one. Concatenate. So concatenate as a a word says will

[5:27:39] 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 are concatenating the employee name with that of hyphen

[5:27:51] and department. 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 hyphen and then employee name hyphen

[5:28:06] with departments. So concatenating as the name says it concatenates the columns. It concatenates the column and you can definitely do a concatenation like this. Now let's say I do not want hyphen. I do

[5:28:20] not want hyphen. So I can remove hyphen also. So definitely this is not going to also. So definitely this is not going to give me any error.

[5:28:32] So you can see that it has concatenating 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.

[5:28:47] because that makes more sense to me. That looks better. these queries and I'll wait for again not more than 2 minutes. Just give me a second. I think this is not allowing me. Okay, I'll just provide you the queries

[5:29:02] in 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

[5:29:14] me. It says that some maximum so and so letters characters are only allowed 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

[5:29:30] and we have got a lot more queries for the string function. So we'll finish off the string function. All right. So we'll look into more functions. Now you I've already talked about this left function.

[5:29:44] So this will simply extract the first 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 the last few characters. All you have to

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

[5:30:14] other way around. Is there anyone who has not understood what is the use of left and right function? Fine. So I'll take it 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 AIT 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 say I want to do it from the third character. So from the third third car

[5:31:26] from the third character and then four characters after that. So if I run this 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 becomes

[5:31:43] So I t space and s. So that becomes four. So this will help you to extract four. So this will help you to extract the part of your string. In case of left and right it always starts with from the beginning or from

[5:31:57] 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. Concaten see we have already done it but uh this example I have included in order

[5:32:13] uh this example I have included in order to make you understand that you can add or you can work on multiple function. You can use multiple functions in order You can use multiple functions in order to get your work done. So before I we

[5:32:25] before I explain you this code, let me ask you how many functions are we using. So we have talked about all these three functions. Oh sorry I've already given functions. Oh sorry I've already given you the answer.

[5:32:41] numbers still I can see very less of you are interactive 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:56] that you're interactive. Yeah, we have got three functions. Yes, concatenate, upper and substring. So basically how it will work. First it will 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

[5:33:25] 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:41] then upper will run on it and it will make amii 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 ami

[5:33:58] here. So what it will give me ami emp. right you have got multiple parenthesis you always start with the innermost and you always start with the innermost and then you go to the next outer mo uh

[5:34:12] outer one till you go uh to the 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.

[5:34:27] You can see that in order to in order to get this result we have used three functions. So this is very very very uh normal thing in SQL

[5:34:40] where we use multiple functions in order to get our work done. 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

[5:34:53] to do with the function actually. So yeah, nothing to do with the functions. So I don't know why this code I have included. Maybe I'll remove this code. included. Maybe I'll remove this code. So I'll just provide you with yeah all

[5:35:05] you can again copy paste it. You can have a look play around with it for 2 minutes. Before that we learn about the numeric functions. So for single row functions if you see we are done with the numeric

[5:35:18] functions. We are done with the string functions and now we are going to learn functions and now we are going to learn about the date functions.

[5:35:30] we have got date and time function in order to work on the date and time as a 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

[5:35:44] maybe we want to manipulate and do something with those columns. So we have got a lot of functions handy and we can use these functions in order to work on the date or date time column. So if you see

[5:36:00] we have got the functions to extract the date part. Now you know when we have got any date or time for that matter. So let's say 0 to 02 something like this. I

[5:36:13] don't know why I have written 2014 that's a long back but yeah let it be. 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

[5:36:28] Wednesday. So all of these things can be easily done using some of the extract 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

[5:36:42] you have got hour right then you have got minutes then you have got seconds. 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

[5:36:57] lot of functions that that can help us to extract the data from the date time or date functions sorry date time or date columns. Then we have got uh functions like current date and now. So this will

[5:37:13] return the current date. So current date means whatever the current date you have 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:30] 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 like not it's not only the extractions or um

[5:37:45] 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 as the name says, let's say I give you

[5:38:00] 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 maybe yeah I've got a lot of examples to show you so this is very easy see uh select

[5:39:31] current date now I'm not applying this function on any of the column of the table it's a very simple 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

[5:39:45] system also. If I run now, so I told you about this function current date and now I've 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

[5:40:00] is returning me the date as well as time. Very simple one. Yeah. Now, uh you time. Very simple one. Yeah. Now, uh you may want to um use these functions. Uh

[5:40:12] 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 might be using it. But let me tell you right away. Let's say I want to know

[5:40:26] 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 subtract it with the current date. So this will give me the number of days

[5:40:43] since the com the employee is associated with the company. H moving ahead. Now this is a very simple one. You want to extract the different different parts of the date. So for example, we have got the joining

[5:40:58] date. Let's say I want to extract year from the joining date. So for this this from the joining date. So 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

[5:41:13] It will simply extract all the years from the date. Yeah, date column. Month will extract the month part from the date and day will extract the day part. So if I run this you can see it is extracting the year

[5:41:28] and month is extracting the month and day is extracting the day. Very simple. Yeah the extraction part. So this is a very common function year month and day functions because these are so easy right? So few functions we expect that

[5:41:45] you remember the interviewer will expect that you remember would expect that you 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 93. 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

[5:42:46] when we were talking about when we were on this slide. So anyways I told you 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

[5:43:01] returning that what the day was on uh 15th of uh sorry yeah June 2021. So it was Tuesday. So this will help us return the the day name.

[5:43:16] more but let me give you these you can just play around with it and then just play around with it and then we'll see more date time function done let's look into the more functions so I've already talked about

[5:43:33] date difference so date difference as the name says is going to uh find the difference between the two dates so for example over Here in the date diff

[5:43:45] example over Here in the date diff function 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

[5:43:59] between the two dates and definitely that is going to the days in the company 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

[5:44:12] between the current date and the joining date. So you can see it's very easy. In the same way you can add let's say you want to add some days, years, months

[5:44:24] 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 give you the expected results. So you can use date add function. Now this date

[5:44:39] add function takes two parameters. One is the date the original date column on 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

[5:44:55] doing is in the joining date we are adding 30 days. Now you may want to add 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

[5:45:09] can give day you can give month you can give year. 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

[5:45:22] values it takes. So over here again I repeat this function is going to add 30 days. 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

[5:45:37] year to the joining date. So if I run this, 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:58] function, it is a best practice that you Google about that function. For example, let's say I'm working on this function here. 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're 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 charge GPT and yes before we continue maybe because we talking about chart 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 So uh still I've got the paid version of charg because it's free for one year. So

[5:48:20] charg because it's free for one year. So for that you have got SQL expert. for that you have got SQL expert. So this SQL export 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. 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 give you notes. But I want you guys to make this efforts of saving the notes by yourself and writing the notes by yourself. So until this you do not make

[5:49:17] 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:29] 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

[5:49:56] done. So here you can see that joining date is in if you if I quickly show you 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

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

[5:50:24] here now it is showing the date in this 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

[5:50:38] created by me. This is not something 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

[5:50:53] your practice. So, um it's just that 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

[5:51:08] in year just like in string I showed you that you can use multiple functions in 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

[5:51:22] the employee. Yeah. So what we are doing is first of all we are finding the date difference. So we are finding the number of days the employee has joined. Let's of days the employee has joined. Let's say the employee has joined. Um it's

[5:51:35] say the employee has joined. Um it's been let's say uh suppose let's say 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

[5:51:50] will give me 730 days but I want experience in years so I'm doing the experience in years so I'm doing the 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

[5:52:04] me some number two point maybe or two I think. So, so it will give me some number. I'm simply rounding it off to two decimal places.

[5:52:18] experience in numbers sorry in years. And with that we have or questions you can ask them in the comment section below. Our team of possible. Thank you and keep learning with simple