[00:09] most of that data is stored inside databases. So if you want to access, manage, analyze or understand business data, SQL becomes a must-have skill. But complicated. In this course, we will break SQL down in simple beginner
[00:23] friendly way so that you can clearly understand how databases work and how SQL help us to communicate with them. First, we'll begin by understanding what data is and why business need databases and how databases are better than
[00:37] storing information in spreadsheets or text files. Then we'll understand what SQL is and how it works and why it is used across different database systems like MySQL, SQL Server, Oracle, Postgress SQL and many more. After that,
[00:50] we'll move step by step into MySQL and MySQL workbench where you are going to learn how to connect to a database server, use a lab environment, create databases, view existing databases, and work with database objects like table,
[01:04] views, indexes, procedures, functions, and triggers. Then we'll explore the core SQL commands. You'll understand DDL commands like create, alter, and drop. DML commands like insert, update, delete and data retrieval commands like select
[01:19] to fetch informations from table. Next we are going to learn how tables are created using columns and data types, how to insert records and also how to to explore how to modify table structures using alter commands. We will
[01:34] also understand important database concepts like metadata, constraints, primary keys, unique keys, not null rules, foreign keys, and how these rules help maintain clean and reliable data.
[01:48] Then we'll move into relational database design. You'll learn how data is stored across multiple connected tables, how ER diagrams work, and how relationships like one to one, one to many, many to one, and many to many are used in real
[02:02] database systems. And the best part that this is just not a traditional SQL course. You will also see how geni tools like chat GBT and GitHub copilot can help you learn SQL faster, debug errors, understand queries, generate practice
[02:16] examples, and even write advanced SQL queries with the help of AI. So by the end of this course, SQL will no longer feel confusing. You'll have a strong foundation in databases, MySQL, SQL commands, table creation, constraints,
[02:29] relationships, ERI diagrams, and AI powered SQL workflows. Before we move ahead in the session, just a quick info guys, if you are interested in building a strong career in business analysis, then AI powered business analyst course
[02:41] by simply learn is a great opportunity for you. This business analyst course is endorsed by IIBA and it helps you earn 35 PDS CDUs with live CBA training
[02:53] aligning to the IIBA guide version 3. You will learn 10 plus AI integrated tools in PowerBI, Excel, MySQL and Tableau while building expertise in product thinking, agile, RPA and process mining. You will also
[03:08] work along 10 plus industry project, four plus practical case activities and helping you build the hands-on skills needed to solve real business problems. So if you want to grow as an AI powered business analyst, then you should
[03:22] definitely check out this course. The link to the course is mentioned in the description box. Now before you begin the session, here is a short quest for command is used to retrieve data from
[03:34] the table and your options are create, insert, select or drop. Please mention your answers in the comment section below. So data is nothing but a raw unprocessed fact. So data can be anything. So let's say if I tell you
[03:50] about my profile, yeah, where I have worked, uh what I have done in last 15 years in my professional life about my personal things. So that is again data. So data is what? Data is raw and unprocessed fact that on their own may
[04:05] not have much of the meaning. So basically data is as I told you. So if I give you one file with a lot of student details for example this is what this is data. So this is unprocessed. This is raw. So it doesn't have much value. Yeah
[04:21] it doesn't have much value. So what we do is we process the data. Now this is usually done by the data engineers. So the data is processed and organized in a way that it becomes information.
[04:36] So data is in a very raw form with not much of value with not much of meaning much of value with not much of meaning and the data is converted the pro it is and the data is converted the pro it is processed to make it information.
[04:52] processed one. So moving ahead now see you've got a lot of data just like let's say if I talk about okay so I'll give you one example
[05:04] one analogy so let's say that you've got a lot of clothes yeah you've got a lot of clothes now ignore my drawing yeah you've got [clears throat] a lot of clothes whatever it is yeah you've got a lot of
[05:17] clothes so now you've got clothes that's fine But where are you going to store your clothes? Definitely we store our clothes somewhere, right? So we store the
[05:30] clothes in the cabinets in Almir. Whatever you say. Yeah. So we store the clothes in the cabinets in Almiras. So here also when you have a lot of data, here also when you have a lot of data, data needs to be stored somewhere
[05:45] especially the processed data. So that needs to be stored somewhere. So just like you are storing the clothes in Almiras in your wardrobes in the same in Almiras in your wardrobes in the same way the data is stored in the database.
[06:00] So database is nothing but it is a So database is nothing but it is a structure which can store the data and definitely store the data digitally or electronically because see when I
[06:15] talk about the wardrobes you have the wardrobes at your home. So uh you you get to see the physical wardrobe but here in the computer world everything is digital right. So database is think of it as a folder.
[06:29] So on your machines so on your machines why do you create a folder so that you can save your data in it you can save your files in it. So here also we create the database in order to store the data. in order to store the data. Now when you
[06:44] in order to store the data. Now when you have a database why it is uh always better to have a database. So if I ask you again just tell me one thing. Let's you again just tell me one thing. Let's say that you've got a big wardrobe
[06:57] say that you've got a big wardrobe with a lot of clothes in it. Yep. So don't you think so that if you have got a clothes if you have got a wardrobe first of all uh you're getting a better storage
[07:10] keep all my jackets over here I'm going to keep all my shirts over here and so to keep all my shirts over here and so on and so forth so I get to organize my clothes because I'm having a wardrobe right also I can easily access it see if
[07:26] I'm just dumping my clo clothes on the floor if tomorrow I want a particular dress. Yep. It will be very difficult for me to find it, right? But if I'm using a wardrobe and definitely the wardrobe is organized, then I can easily
[07:41] find the clothes. Yeah. So, it is very easy. It will be very easy for me to easy. It will be very easy for me to access
[07:53] 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 explain you with the help of wardrobe
[08:06] 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
[08:20] 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
[08:35] to access your clothes. So in case of database also it will be very easy for you to access the data because right now see the data can be in uh you know in
[08:49] 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
[09:03] secure scal scalability. So what do you understand by scalability? and data is definitely increasing day by day. Now if you talk about Amazon think
[09:16] about the data that how fast it must be increasing. every day there must be a lot of transaction being made. I'm talking about the Amazon website and if I specifically talk about only India
[09:29] Amazon website or only US Amazon website every day you can imagine the kind of transaction that Amazon might be getting and the kind of data that might that Amazon might be producing. So we need a system which is scalable. Scalable means
[09:45] system which is scalable. Scalable means that it should be able to handle. So database is a system which can handle the growing data. Yeah. And it's definitely secure. It provides us the security also. So you may not want all
[09:58] your data to be accessible to everyone, right? So with the help of database, we 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 logged and
[10:13] unlocked from uh different different users that you have screen you'll get to know that u how your co-articipants are for example I actively work with SQL so we have got only two who are actively working on SQL
[10:29] no experience with SQL we have almost 53 so we are going to learn from very very that we are starting from what is data so that's one of the very basic basic thing. Okay, I have got some knowledge but don't use it regularly. So, we have
[10:44] got 16 folks. Okay, here is about the uh experience. So, you can just quickly have a look. So, we'll make sure that we cover everything from scratch. But guys, one thing if you're learning the language
[10:59] for the very first time, then you also have to make sure that you practice after the class. So only class learning would not be sufficed because see if you don't practice after the class when you come for the next class you're going to
[11:12] forget most of the things what have been taught in the previous class. So please make sure that apart from being consistent in joining the classes you're consistent in joining the classes you're also consistent in
[11:25] going through the material and doing some self-study. some self-study. So that is very very important.
[11:37] different different domains. You can see. So, we have got a lot from finance see. So, we have got a lot from finance domain
[11:51] to the career growth, help in the career growth. So again there's one analogy in order to understand what database is. So just
[12:03] like you've got a lot of books. Yeah. So we've got a lot of books. So the books are stored where? Now this is the picture of what books are stored where? Think of books as the data. And where do we store
[12:19] books? In the library. Yes. Yeah. So this is what library you must have seen that when you go to any of the library usually the books are arranged in a certain manner. For
[12:35] example here you'll have all the science books here you'll have let's say all the maths books all the fractions. So in this way and then there might be some alphabet alphabetical arrangement. If you go to any good library you'll see
[12:50] such arrangements. So why do we have such arrangements? What do you think? Why? What is the reason behind such arrangements?
[13:03] books easy retrieval. Yes, easy accessibility. Yes, imagine that they are not storing the books in this way and then you'll end up maybe spending your whole day or two in order to find one single book
[13:18] maybe more than that depends on the size of the library. So database also works of the library. So database also works on the same concept guys. It's just that instead of books we are now storing the data and instead of library we are
[13:30] having the database. Database stores the data. Library stores book. Now just like data. Library stores book. Now just like how the library is properly
[13:45] 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
[14:00] 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 modify make the modifications so it's not that I'm very random with it
[14:15] but Yeah, it's mainly for the quick access. Now, we're going to learn about these properties of database. So, this is very important interview question also. So, we have got asset properties. We call it
[14:30] as asset. So, we'll go one by one. So, this slide may take little bit of time. So, please be patient. So, I'll try to make it easy. I'll try to make it interesting. Yeah. All of these properties. But you
[14:44] properties. These are the heart of the database. So when I say autom atomic city any idea what atomicity could be? So I do not want you guys to read the So I do not want you guys to read the slides. I want you guys to answer with
[14:59] slides. I want you guys to answer with whatever comes to your Okay fine. So we'll talk about atomicity. So I'll give you an example
[15:14] atomicity. So I'll give you an example and we'll understand it with the example and it's very important. So before I talk about atomicity maybe let me tell you the use case of database. So just like database we have got we have got
[15:29] 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
[15:43] might have heard about apart from database you might have heard about data warehouse.
[15:56] 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
[16:09] 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 Yeah we are not going to look into the
[16:22] 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 So any idea any use case of database you can think of
[16:40] see everything is used for storing the data. So why database? So I'll tell you why database. So database or not why I'm just telling you use case of it and then you'll understand why. So we'll go step by
[16:53] step. So if I talk about use case of database so let's say that I access any of the bank website and when I say website I'm talking about my account. So I just want to see that what is the balance current
[17:08] transactions that I made in last 7 days and so on and so forth. So to save this data now see my current balance then everything related to me um my profile then my transactions whatever
[17:24] you see so everything goes into the databases. So most of the companies they use databases for such things. So databases are used for day-to-day
[17:36] transaction. So whenever there's any day-to-day transactions data, yeah, whenever there's a day-to-day transaction data, it goes into the databases.
[17:54] asset. Yeah, it provides asset guarantees. I'm going to talk about what asset is. But why day-to-day transaction data goes uh uh why day why do we go with day-to-day transaction? because because it provides a database provides
[18:11] with the asset guarantees. If I give you the other example, if I talk about let's so [snorts] again day-to-day transaction right you're making a payment you're ordering something making the payment so mostly the company might be using the
[18:26] database in order to handle all of these things. So if you go to the Amazon you see a lot of products so let me go to the Amazon and I'll just show that to you. [snorts]
[18:49] these are this this is what these are data. Images the description the price all of these things are data. So this might be coming from the database. Yeah. This might be coming from the database. So the database might be storing all of
[19:02] these things. So for day-to-day transaction we use we use database. Why for day-to-day transaction? Because database provides us with the asset atomicity and you'll get to know that why
[19:20] and you'll get to know that why asset is so important. transactions are executed in all or nothing manner. So what does that mean?
[19:37] So it simply means that let's say let's say this is you and this is your friend. Yeah. Now what you're doing is let's say you have got hear me out.
[19:50] Yeah. You've got 50,000 as your account balance. I'm talking about INR anything anyways. Yeah. And now your friend needs 10,000 rupees. So you say fine, I'm going to give you
[20:03] 10,000 rupees. So what you do is you go ahead and you make a transaction. Now this is something that we do in our day-to-day life making the transactions via bank. So you'll understand that how things works. So here I go ahead and I
[20:18] make 10,000 rupees transaction. So what will happen is the 10,000 will get deducted in your account from your account and it will get credited in your friend's account. So this is the expected behavior when
[20:33] everything goes well. But let's say that you made the transaction. This might have happened a lot of time with you all that you make the transaction the money got debited. Yeah. And hear me out. Yeah. The money
[20:47] 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 have happened, right? So what will happen in your account? You see 40,000.
[21:02] But in your friend's account still it is still reflecting zero. So this is a very bad thing. Right? If I talk about banks, this is something that we definitely do not want because if things are happening like this, then we end up calling these
[21:16] people all the customer care and you know breaking our head that we I transferred did not work whatever it is it's definitely not some not something it's definitely not some not something that we would appreciate right so what
[21:29] happened usually in in your day-to-day life you might have seen that if the money is debited from your account and there was some problem while you were making the transaction it gots recredited.
[21:41] You get the message that you have got your money back. So behind the scene what happen is because there's a database that is because there's a database that is working. Database says that either all
[21:54] 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. So what does that mean? It means that either the whole transaction should get
[22:08] processed successfully. So what I when I say whole transaction should get processed su successfully it means your account is getting debited and your friend's account is getting credited. This is the one full transaction but if
[22:21] there's an error in between there's some problem in between then the whole transaction should be rolled back. So either it should be the full transaction or there should be nothing. And that's why when there's a error while you're
[22:37] making some transfer and if there's some error you you'll get uh you'll get your credit back you'll get your money back automatically and this is because of the database property that is atomicity. So this is a property of the database that
[22:51] successful but if there's a problem in between then it will make sure everything is rolled back. So your deducted amount the amount which was deducted is rolled back and it is again credited. So this is what autoomicity
[23:05] 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 an order. Yeah. Either the order should be made or shouldn't be made. There should be nothing half in
[23:19] between. Right? So autoomicity is very very important.
[23:38] So everybody knows the meaning of consistency. Yep. So here it is written but still I write it. So again I'll explain you with the help of an example. So let's say that you've got an account.
[23:55] 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 that you a lot of banks does have such rules. So let's say that your bank has a
[24:10] rule that you should maintain minimum balance of 10,000. So this is a rule, right? minimum balance of 10,000. That's a rule. Now, what you do is you make a transaction.
[24:25] what you do is you make a transaction. You make a transaction of 10,000 rupees in your friend's account. So, how much how much amount is left in your account? How much amount is left? You've got 15,000 and you make a
[24:40] transaction of 10,000. So, how much amount will be left? But what the rule says? The rule says
[24:54] that minimum value should be 10,000. So here consistency says rules are not broken. So in very simple words,
[25:06] the rule says that you have to maintain 10,000 rupees in your account. If you try to if you try to transfer 10,000 rupees because you've just got 15,000, allow you to do it. You might have seen on on GP, Google pay that there's a
[25:21] on on GP, Google pay that there's a limit of 1 lak rupees for every day. So if you try to transfer more than 1 lakh rupees though you might be having even 1 CR in your account but if you try to transfer more than 1 lak rupees in from
[25:34] your Google pay it gives you error. It says big no that you're not allowed. So that are what that that is what that is rule. So databases says oh you got some rules I'll make sure that the rules are being
[25:49] I'll make sure that the rules are being followed the rules are not broken. So am I clear with consistency with consistency so isolation as the name says
[26:03] 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.
[26:15] 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
[26:27] let's suppose that the account balance is 10,000. Now what is done is let's say you we make a first transaction that is
[26: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
[26:52] new balance will be again 5,000. it can be anything. It's not only about credit and debiting. So the second credit and debiting. So the second transaction says
[27:07] that you know it sees it wants to see the data. Yeah. It want to see like how how much is the account balance. So let's say that you are taking the money out from the ATM and at the same time your parents are checking the balance.
[27:22] your parents are checking the balance. Yeah. So they are checking the balance. So they end up they may end up seeing they may end up seeing maybe 5,000
[27:41] So let's suppose they end up seeing 5,000. Yeah. So your mom is checking and she sees that there is 5,000 rupees. But what happened when you were so you what happened when you were so you initiated the transaction and ATM uh
[27:54] took its sweet time and then it failed. Yeah, there was a failure. So what will happen? What will happen? Or maybe you're not taking it from the ATM. transaction. So you have initiated 5,000 rupees transaction but in between it got
[28:08] failed. So what will happen? It will roll back and you'll again have the 10,000 in your in your account. But your mom is seeing 5,000. So that's a problem. You're getting that's a problem. Actually you don't
[28:21] 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 5,000 you're getting so you don't have 5,000 you have 10,000 but your mom is
[28:34] 5,000 you have 10,000 but your mom is seeing 5,000 so that's that's a problem that's a problem so isolation says isolation says that we are going to we are going to do the things in isolation
[28:48] are going to do the things in isolation yeah so it means it means that t2 in the transaction to either it will see the old data 10,000 the old data 10,000 or it will not see anything. Sometimes
[29:03] the screen takes its sweet time in in order to get refresh or in order to show you the numbers, right? So it will not show you the data half incomplete data.
[29:16] Yeah, half done data. It will not show 5,000 at least. So it either it will show 10 10,000 or it will wait for some time for uh for the transaction to get processed that T1 transaction to get
[29:28] processed successfully and then it will show 5,000 but nothing in between here it was showing in between see you were making the transaction and it is showing 5,000 to your mom but what happened the transaction got failed but your mom sees
[29:41] 5,000 so that's a wrong thing that we are showing right so this is not even before this is not even after this is something in between So isolations make sure that everything happens all the transaction they happens
[29:55] affecting each other. So it means that if you're looking into the balance either you're going to see 10,000 or it will take some time and will show you 5,000 once the transaction E1 is successful.
[30:15] Yeah. Means transaction two will not see the uncommitted deduction. As simple as that. So am I clear with isolation?
[30:33] Simple. Everything is something that we get to see in our day-to-day life. Yes, Rajpar. What you're saying is right.
[30:46] Now what is durability? This is a very simple one.
[30:58] Durable simply means that the data is permanent. transaction is successful, see 5,000 deducted from your account and credited
[31:12] in your friend's account. Yeah. This yeah this should [snorts] be durable or the data should be permanent as simple as that. So if there's a power failure even if the system crashes the data will be still saved.
[31:28] So once so it simply means once committed it stays forever and this makes sure that there's no data loss.
[31:44] So I'll just quickly recap. Atomicity says no partial updates. Consist consistency says no rules breaking.
[32:03] Isolation says no transaction So this makes database very very special and that is a why we use it for
[32:19] day-to-day transaction. So there's a word word for day-to-day transaction that is OLTP. So you should definitely know about this So you should definitely know about this thing. It's nothing but online
[32:34] transactional processing. So maybe I'll not write the whole thing. transactional not write the whole thing. transactional processing.
[32:46] transaction Amazon your banks. So these are what day-to-day transactions. So all the companies for their OLTP they use databases. So their OLTP they use databases. So databases are very important.
[33:08] 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
[33:22] transaction. So there there should be no partial transaction. that we have got the rule. So we have to make sure that data follows the rule. I give you the example of Google pay also right.
[33:41] the session. Yeah data warehouse we'll cover what is in our curriculum and I'll explain you. So data warehouse also I'll explain it's a very interesting concept but just keep it for the end of the end of the session. So I'll explain
[33:56] sidesh what you're saying is absolutely right.
[34:10] I'll quickly explain isolation once again. So isolation simply says think of it maybe I'll give you one maybe one more example. So let's say that you make a transaction you have got 10,000 rupees. Yep.
[34:24] uh you make the transaction of 5,000 at the same time. So I'm explaining you with the same example. At the same time, your spouse looks into the account balance and sees 5,000 rupees.
[34:45] happened? Something happened and your transaction got failed and everything got rolled back. So basically you have got 10,000 got 10,000 But your spouse saw 5,000.
[34:57] So your spouse was able to see incomplete data. So either your when you're checking the account, it should be either 10,000 before the transaction or should be 5,000 after the transaction. So it should not be in
[35:12] transaction, understand the transaction is still not completed. Just like let's say you're taking the exam and when you're taking the exam so you're preparing for the exam maybe. Yep. And
[35:25] 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 also it's the same thing that the transaction is ongoing and when the
[35:41] transaction is ongoing this state shouldn't be shown to the user. because the transaction is ongoing. So if the transaction fails then if you are showing 5,000 see if you're showing 5,000 then it's a bad data right you're
[35:57] showing the bad data actually the the amount is still 10,000 because the transactions failed but you're showing five 5,000 so that's a bad data so either you have to either you should show 10,000 or you should show 5,000
[36:12] after the successful transaction. Am I clear now?
[36:40] 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 but we are going to focus only on the one type of database which is used in
[36:54] major projects. So we have got different different type is the relational database. So we are going to learn relational database. Database is what? It's nothing but it's
[37:06] a place where you store your data. Yeah. Where you store your data. curriculum we have got relational database SQL works with relational
[37:21] database only. So our focus would be on this but I'll just quickly walk you guys through these also so that you understand these terms. We'll definitely understand these terms. We'll definitely not deep dive that is not required.
[37:41] 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?
[37:54] Tables as columns. Yeah. It has columns. Let's say student ID, student name, student number, all of these. Yep. And then it has got data.
[38:11] 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?
[38:26] I'll give you one example but we are going to deep dive into this. it's very much okay if you do not understand it right away 100%. And yeah, that's very much okay. If you understand 50% of it, that's also fine. So, for
[38:41] example, let's say that I've got let me let me see if I've got something. Just give me a second. So, instead of writing, I'll better show you. Just give me a second.
[39:12] are tables with rows with columns and rows. So it's a relational database. So rows. So it's a relational database. So relational database says that this is relational database says that this is table let's say this is table one,
[39:26] this is table two and this is table three. So relational database says that there's a relationship between the tables. Yeah, there's a relationship between the tables. Now, when I say that there's a
[39:39] does that mean? So, if you see for example, so this table is storing the data of the orders, order date, order ID, customer
[39:51] ID, product ID, quantity. So here the customer ID is 1 2 3. So here we are not storing the data of the customer in this table. We are just saying that this transaction is made by the customer with the ID 1 2 3 but we
[40:08] are storing this data of the customer the information of the customer in 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
[40:21] we can have more information phone number birth date so many so much of it. So this is just to make you understand. So in case of relational databases everything is stored in the form of the table all the data and then we have the
[40:35] relationship between the table. So you can see that this table and this table is related to each other. Yeah. This table and this table is related to each other. So when I say that two things are related to each
[40:49] other. Let's say that you are related to your uh sibling. So there's something that is common between you. Let's say you're related to each other because of the family. Yeah. In the same way, let's say that you have got multiple friends
[41:03] say that you have got multiple friends and you say that okay, I am uh you know somehow I am now that doesn't make much of sense but yeah let's say that I am related to this friend in a way that we all belong to the same college.
[41:16] So that's a key between the relationship uh that's a key between you and your friends to establish the relationship. So you guys tell me see I've got this table customer table and I've got this orders table. So
[41:34] what do you think what is the key between this table and this table that is actually making the relationship happen. So in order to make the relationship happen, there should be some something in common.
[41:53] 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
[42:08] 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 that we have. Okay, here I've got okay I just totally forgot that I've got a PPD
[42:20] and here I'm showing that how the different tables are related to each other. So there's a key a common key but let it be yeah I've already explained you and we are going anyways going to deep dive into it.
[42:33] Okay, a lot of uh speaking from my set again I'm so sorry because I can't help it. From tomorrow we'll do a lot of hands-on
[42:53] the relational database, examples are MySQL. You're here to learn examples are MySQL. You're here to learn MySQL. So that's a relational database. In the same way we have got SQL server, Oracle, Postgress. Now if you learn even
[43:07] Oracle, Postgress. Now if you learn even one single database if you learn MySQL then you can say that you know all the others and I'll tell you the reason why others and I'll tell you the reason why why because 85 to 90% of all the
[43:19] 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 you learn one you you will surely be able to work on the others also. So if
[43:33] I'm let's say I'm taking interview so if I know that the person knows my SQL very well but in my project we are using SQL server I will not hesitate hiring that person because if that person is good as in uh if that person
[43:50] knows that candidate knows my SQL and he's able to answer all the questions and he understands the length and the breadth of the technology then I'm sure breadth of the technology then I'm sure that uh working on SQL server will will
[44:03] be definitely no big deal. So all these databases, relational databases, they're very similar. You don't have to worry about learning different different databases. That is definitely not required. You learn one
[44:16] properly, you can work on any database for that matter.
[44:29] relational database and why I'm talking about it right now because after this we have got no SQL database. So see relational database is kind of a strict database. So when I say it's a strict database
[44:45] 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 storing the student details role number name and marks awarded. So
[44:59] 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 for this new new student. So I've got for this new new student. So I've got let's say his role number is four name
[45:14] is Niha marks is whatever. Yeah. But I've also got one more data for for him for her. So let's say I've got city as Pune. So relational databases say yeah the that
[45:30] I'm so sorry you have something extra I cannot accommodate. So it's very strict you're getting so this table says that you've got extra column I'm sorry I can
[45:42] just accommodate three columns. So I am I cannot provide you this flexibility of storing anything in me. Yeah, I've got three columns. You have to stick to columns. So it's very strict. I'll give you one
[45:57] more example of strict. The second strictness it says that now it says that this column is integer. It means that it can take only integer values.
[46:11] Yeah, it can take only integer values. So again I go ahead and give the details but let's say I've got some string value. Yeah I want to give let's say 10.
[46:23] So again it will say no. It will say what what are you doing? I can just take integer. What the hell are you providing me? You're providing me 10 like the string 10. Sorry I can't do that. So it's very
[46:37] strict. Yeah, it says that see I've created this table with the three created this table with the three columns
[46:51] 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
[47:10] We'll talk about that. Apart we'll we'll see to that. Yep. It's very easy to set these rules. So you got that relational database is very very strict. You get that? Am I clear with this point? How do we do
[47:23] that? Once we start writing the code, you'll get to know and it's very easy. It's like one word things. Yeah. You just have to add one keyword and then you're done. Done. That's it. Am I clear that the relational database
[47:37] Am I clear that the relational database are super strict, very stringent? Fine. Now let's say there's a requirement. There's a requirement. The requirement is that we want flexibility.
[47:55] Yeah, we want flexibility. So what I want is let's say that I am um I'm the business owner and I do not want this. Yeah, because let's say I I I am I have run my own school and I say that fine. Yeah, but let's say there's some
[48:10] 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 discard it. Yep. So either you
[48:27] add one more column to your table. Yep. or give me something else that is more flexible. I want to store everything whatever whoever is coming to my school. whichever student is getting registered in my school, whatever information they
[48:41] are giving me, I want to store it. So I just simply do not want to discard any of the information. So here comes So here comes NoSQL databases.
[48:55] 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. Now this also stores the data. Yeah.
[49:09] This also stores the data but it stores the data in a way that it is super flexible. So do you know what JSON?
[49:32] sometimes. Or maybe I'll just show it to you. Some uh JSON structure I'm just showing you so that why is it taking me to Yahoo?
[50:00] Yeah. So I'll just quickly show you how does it look like. So relational database stores the data in the form of in the form of uh tables
[50:12] 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 one. I'm just uh looking for something that is quite easy to understand.
[50:34] 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
[50:51] information of some XX employee. Now let's say that we have got another employee and that another employee has got some other information also. let's say passport number. So it easily accommodates that it says
[51:06] fine. So I'm going to have one more node. So this is one node for one data for one employee. So you've got 100 employees. So you'll get 100 nodes. So it says that I'm very much okay with it. You have got one more data. So I just
[51:20] have to add one more key value pair over here. Yeah. Key and this is a value. So this is how JSON looks like. So it's very very much accommodating. It says that I'm very much okay with it. Yeah, I'm not strict. Right now I've got five
[51:37] details. Yeah, five columns. If you got 10 columns for the next employee, I can easily accommodate it. So I'm not strict like tables. So that's what NoSQL says.
[51:54] different formats. So it stores the data in key value, column, graph and document format. You don't have to deep dive into it. But yes, so why do we use NoSQL? This you should know. So if you want that flexibility, see tables are very
[52:09] strict. So you want that flexibility that tomorrow if your data comes in some other format, you should be able to store it. Yeah, it should not it should not say no. Your database shouldn't say no. Then you can go for NoSQL database.
[52:24] Definitely you don't have to deep dive into it. XML format. Yes. XML JSON key value pair. Exactly.
[52:46] 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
[53:06] 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,
[53:19] friends of friends, mutual connections, suggested friends. So whenever we have got data like this,
[53:34] yeah, where the data is very much related, yeah, it's mutually related. You can see over here. Yeah. So we use graph database. So friends of friends, graph database. So friends of friends, so this is again graph database.
[53:54] 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
[54:08] 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. If I have
[54:23] never worked on the graph database usually uh it is used by the social media companies. Yeah. Social uh media companies then uh it is also used by the banks I think for the fraud detection etc.
[54:38] so that they can you can find the connection between the data. Now we'll talk about centralized database.
[54:52] database for that matter. It can mean SQL, NoSQL, graph. So centralized database is what? You have got one database.
[55:04] 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
[55:18] City Bank has got one centralized database. So tell me what could be the problems of having centralized one single big database.
[55:38] What if there's a power cut? There's a failure. What will happen? failure. What will happen? Everything will go down.
[55:50] Yeah. you're not able to access your bank account and so the users.
[56:04] 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.
[56:19] the solution of centralized database? Now I'm not expecting the right word for it. I'm just expecting that what should be the solution? What you you would do?
[56:39] Don't you think so you have the copies of it? Multiple 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 system
[56:53] crash your website will not go down as simple as that. So having these backups here you are maintaining only one database. Now let's say I have got two
[57:05] backups. So I'll end up maintaining three databases, right? So it's pretty three databases, right? So it's pretty expensive but yes we call it as distributed databases
[57:21] places and that's is again done by all the big companies and that is the reason why these days you'll not see that your site is going down. Yeah. All the good big companies websites they are always up and running.
[57:38] So in Pune this happened almost a year back I think it was it happened in June July. So I stay in hindari those who are from Pune. So uh there was some issues with the power. Now this area where I'm staying is the IT hub
[57:55] staying is the IT hub one of the IT hub. So there was some big issue with the power supply of and that was for the whole area and we were without the power for 3 days straight 3 days and it was not only for the
[58:08] households it was for all the IT parks also and uh we have got some data also and uh we have got some data centers when I say data centers u as in uh you can say servers which are storing a lot of data. So we have got some data
[58:22] center we have got a data center also here in uh this part of Pune in Javari. So everything went down. So definitely the these are very common problems. It can definitely occur. So that's the reason why the companies they
[58:37] always prefer having the distributed databases. database, they're going to have databases at multiple places to avoid
[58:50] uh any downtime. Am I clear?
[59:04] already explained you the differences between the two. have got a lot of theory and I've been speaking from last one and a half hours.
[59:18] 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 than 20 minutes, we tend to uh do a we tend to daydream. So
[59:32] according to some theories I think the attention span of a human being is only 20 minutes not more than that. So we'll do one thing we'll take a break and after the break we'll continue because we have got the next topic. So I just
[59:46] 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.
[01:00:02] Oh wow, everybody is saying go ahead. I'm so happy. Fine. Let's go back to our library image. I literally have to go back. Now tell me one thing. We've got the data. We've got the books. We have got the library
[01:00:18] storing the books. Who manages the library? Somebody should also be manage it, right?
[01:00:38] 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
[01:00:51] 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
[01:01:09] 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
[01:01:22] 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.
[01:01:34] 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 [clears throat] we need in order to maintain the things. So here also in order to maintain or not maintain in
[01:01:50] 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
[01:02:05] 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
[01:02:20] 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
[01:02:35] 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
[01:02:49] 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.
[01:03:05] 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
[01:03:21] 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?
[01:03:37] Update. And D stands for delete. So if anybody And D stands for delete. So if anybody says CRUD operations, do not get scared of it. What is this CR? It's nothing. You're creating the data. You're just
[01:03:49] 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.
[01:04:02] 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 DVFS
[01:04:21] and guys see u okay maybe I'll talk about that later not right away it could be I just told you what DBMS what RDB DBMS
[01:04:34] I just told you what DBMS what RDB DBMS could database? You know that there are so many databases, right? We learned about
[01:04:49] the relational database. Now to manage the relational database, what we have? the relational database, what we have? We have got relational DBMS. Simple.
[01:05:01] 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.
[01:05:13] 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
[01:05:25] 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 not understand plain English. So DBMS
[01:05:41] 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
[01:05:53] 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.
[01:06:05] Why? Because with the help of SQL, we can talk to the DBMS and get our work done. Sorry, I think there's some issue with the pen. Yeah, get our work done. That is CRUD. Any of the CRUD operations that
[01:06:18] 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
[01:06:30] 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
[01:06:46] books. So books are what? Books are data. These are data. Now let's say that you want to fetch
[01:06:58] 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
[01:07:15] 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
[01:07:28] 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
[01:07:43] 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
[01:08:00] 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
[01:08:15] systems. You use RDBMS. You use SQL and RDBMS. You use SQL and RDBMS. So am I clear? What is SQL?
[01:08:28] 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
[01:08:41] 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.
[01:08:54] Am I clear guys? What is SQL quickly? So you can just read this slide. I'll wait you can just read this slide. I'll wait for a minute.
[01:09:15] 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
[01:09:28] 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
[01:09:42] language. Yeah, that's a language of DBMS. in DBMS Rajpad. So we are going to use DBMS. It's a software in order to manage
[01:09:59] DBMS. It's a software in order to manage the databases.
[01:10:11] I hope I have answered all the questions on the chat. also. Allow me a minute. Maybe I'll ask Raxita to do that. You're talking about
[01:10:23] Raxita to do that. You're talking about the practice lab, right? Pakita will help you with it. Maybe I'll also check it. I'll also help.
[01:10:44] question. What is the difference between SQL and MySQL? SQL is a language. Hear me out. Yeah. SQL is a language SQL is a language and MySQL MySQL is the DBMS.
[01:10:59] and MySQL MySQL is the DBMS. So we have got multiple relational DBMS. We have got multiple relational database management systems in the market. For example, MySQL. Then we have got
[01:11:15] SQL server. So it shares the name with the databases. Yeah. shares the name same name with the databases Oracle uh sorry for this writing guys and then Postgress
[01:11:30] I don't know what is happening but yeah so these are this is what this is a DBMS or you can say database also they share the same name in order to talk to the MySQL we use SQL language so SQL is a language and this is a DBMS
[01:11:46] language and this is a DBMS have I answered your question amin you'll use SQL. In order to talk to Oracle also you're going to use SQL. Postgress also you're going to use SQL. So SQL is a language and my SQL is the
[01:12:03] So SQL is a language and my SQL is the DBMS.
[01:12:17] okay there's some issue I'm not able to write but yeah my SQL posgress SQL server these are what these are the softwares yeah these are what these are softwares yeah these are what these are RDBMS database management systems
[01:12:30] in order to talk to the database management system we need a language the management system we need a language the language is SQL so my SQL is a DBMS and SQL is a language to talk to The DBMS it's a coding language. Exactly.
[01:12:55] 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 uh let's
[01:13:10] do it after the break. Yeah, we'll keep it for after the break. So I think we are good with whatever we have covered so far. A quick recap. We learn about what is data. We learn about what is databases.
[01:13:24] 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 guarantees of database. So okay here I don't have the slide for
[01:13:38] the same we are just having the slide for the attribute. So I'll quickly cover for the attribute. So I'll quickly cover about the main components of the ER model. So if I talk about the ER model
[01:13:51] So if I talk about the ER model components the first and the foremost compon component is entity.
[01:14:05] the real world object. So basically entity you can think of it as a table. entity you can think of it as a table. It's a real world object. For example, I gave you the example of student or employee
[01:14:20] and it is represented with the help of rectangle. attributes could be? So attributes are nothing but the
[01:14:36] details of the entity. Yes. So all of you are right. Attributes are the characteristics details. Yep. of an entity. So let me write it. So attribute
[01:14:48] entity. So let me write it. So attribute about they are the property of an entity. So if I give you the example of student you tell me what could be the attributes.
[01:15:01] What could be the attributes for the student table? student entity ID, name, age, class, marks, etc., etc. Right? So
[01:15:15] 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
[01:15:30] know that tables they can be related to each other. each other. So it shows the relationship between the So it shows the relationship between the two entities. Just give me a second.
[01:15:55] Yeah. We'll talk about primary key and foreign key. Not right now. But yes, how entities are connected? to each other. So I gave you the example of orders table. So you told me that the
[01:16:09] two tables are connected using customer ID, right? So relationship is how the entities are connected to each other. So if I give you the example, let's say that we have got two entities. Students is one of the entity and course.
[01:16:26] So course, let's say students, let's talk about simply learn. So student table has got all the information about you guys and courses table has got all the information about the courses that are provided by simply learn. So if I
[01:16:40] talk about the relationship between the two. So simply students have enrolled or I would say student has enrolled for which course?
[01:16:55] Yeah, I'll simply say student in rows for codes
[01:17:07] and relationship are denoted with the help of diamonds. establish the relationship you need to have the keys. Yeah, you need to have
[01:17:19] the keys. So I'll talk about the keys right away. I'll give you the little bit right away. 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
[01:17:34] key uniquely it uniquely identifies an entity.
[01:17:50] 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
[01:18:05] 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
[01:18:20] 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
[01:18:33] 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
[01:18:46] 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
[01:18:59] okay in this table we don't have any primary key okay in this table can you primary key okay in this table can you see any primary key
[01:19:13] makes sense also. See customer name can be repetitive. We can have a lot of customers with the same name as Ashul and me. So this can be definitely repetitated but how will we uniquely identify this anulu
[01:19:27] or how will we differentiate this unul with the other anu that we have got in with the other anu that we have got in our database using the customer ID. Fine. Can you tell me what is the primary key for this table?
[01:19:41] 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
[01:19:53] its product ID. So am I clear with primary key?
[01:20:13] it connects two entities. Yeah, it connects two entity. So let's say you've got the entity one.
[01:20:25] So let's say you've got the entity one. It has got some primary key T1. It has got some primary key T1. Now we have got entity 2. So in order to connect with entity one, entity two is using this primary key. So
[01:20:40] 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
[01:20:53] 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
[01:21:07] question that I've asked you before the two tables are related with which column? Quickly this table I'm talking about and Quickly this table I'm talking about and this table I'm talking about.
[01:21:33] 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.
[01:21:46] Now we are using the primary key of this table. In this table. So for this table, for let's say the name of this table is orders. For this table orders ID becomes what? It becomes what? it becomes foreigner.
[01:22:04] It becomes what? it becomes foreigner. Yes, it becomes foreign key. this is what this is a private this is a primary key. Yeah this is a primary key.
[01:22:17] primary key. Yeah this is a primary key. Now in order to connect this entity the entity the column that I'm using the common column that I'm using is customer ID. Now for this table customer ID is a
[01:22:32] primary key. But for this table customer ID is what? It's a foreign key. It's not ID is what? It's a foreign key. It's not a primary key. So foreign key help us to connect the two entities. Yeah. So this foreign key is a
[01:22:49] becomes a foreign key of the other entity. Am I clear?
[01:23:09] 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
[01:23:24] 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
[01:23:39] our coding. Am I clear now Shua? 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.
[01:23:59] 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
[01:24:13] entity this entity may have multiple attributes let's say name okay I'm so sorry I don't know what is okay I'm so sorry I don't know what is happening id
[01:24:26] happening id now I've got another table again now I've got another table again the name of the table is course and these two tables are related to each other so there's a relationship between
[01:24:38] other so there's a relationship between the two so this is what in rows. somebody gives you any entity diagram, er diagram, you should be able to
[01:24:52] understand student and courses are nothing but the entities. The ovals that you see are nothing but the attributes of the entities and the diamond that you see are nothing but the relationship of the entities.
[01:25:05] Am I clear? Am I clear? Fine. Now in case of attributes also we have got different different also we have got different different type of attributes.
[01:25:24] exercise for you all. I'll see to that. If you can do it today that's fine. If you can do it today that's fine. Otherwise we'll do it tomorrow. dialog. So we'll see
[01:25:48] 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,
[01:26:01] multivaried attribute and composite attribute. So let's cover all of these.
[01:26:16] 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.
[01:26:32] These are what these are key attributes. So you can just have a look. have got. Now, what are composite attributes? As
[01:26:47] simple. Just give me a second. I think I've got something on the chart. Okay, please go back to the previous slide. Fine. Is this the one
[01:27:06] for product table? A okay I'll come to that for product table A is asking for the product table now tell me for product table the one that I have highlighted what is the primary key
[01:27:30] 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
[01:27:43] 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.
[01:28:04] clear. Composite is also very simple. So a lot of time we have got the attributes which are composed of several which are composed of several attributes. So for example address.
[01:28:18] 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 values of these three makes address. So that's what composite means. Am I clear
[01:28:34] that's what composite means. Am I clear with composite? Okay. And my laptop is acting up. Just give me a second.
[01:28:50] is what are multivalued attributes? Now there can be some attributes? Now there can be some attributes. There can be some attributes which may have multiple values. Yeah, which may have multiple values. For
[01:29:03] example, let's say that we have got the attribute order or let's say we have got attribute order or let's say we have got the or attribute.
[01:29:15] So order let's say that you make a order. Yeah. You make an order. So in ca in case of order in case of order let's say I make an order of a product
[01:29:29] let's say I make an order of a product I'm not able to write okay there's some issue with the writing thing just give me a second
[01:29:45] yeah so let's say we have got the product yeah so I buy one product of some quantity, two quantity, I buy the other product of one quantity. So what I mean to say is that these attributes may can have multiple values. It can have
[01:30:02] 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
[01:30:17] 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 representing. And it's a very simple thing. If you see, it's just like the
[01:30:32] walk in the park. It's so simple. Moving ahead, why why don't you guess what derived attributes could be?
[01:30:51] When you're guessing, I'll just open this. are what? They are like column. They're columns, right? So the values of these
[01:31:07] attributes, the values inside these attributes will be derived from the other attributes. For example, let's say that you've got uh experience. Now, we
[01:31:20] 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 and today date rate date. Yeah. So, experience in our company
[01:31:34] employees for simply learn. So, experience in simply learn how many years in simply learn. So see this value can be derived from the other attributes. Yeah. So we do not have the value we we are not going to save the
[01:31:49] 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 let's say. Yeah. So let's say I've saved it 2 years but after 2 months it will be 2
[01:32:05] years and 2 months. So if I'm saving these value it may not give me the right values it right now while I'm saving yeah at this point of time maybe it's 2 years for an employee
[01:32:18] but after a year it will be 3 years. So again and again I have to update my database. So I can simply derive it if possible I can simply derive it. So how I can derive it? Let's say I've got another attributes with the uh start
[01:32:32] another attributes with the uh start date or the joining date.
[01:32:48] Yeah. So let's say I've got another attribute or joining date and that of today's date.
[01:33:07] experience of the employee. So am I clear what derived attributes are?
[01:33:21] the derived attributes. We are 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
[01:33:35] on the runtime. So anyways you may or may not create the column. Once we start working on SQL this will be clear. You'll understand. So am I clear? What are derived attributes everyone?
[01:33:59] example price. So quantity into unit price is total price derive attribute. Perfect. Very good Nish. Okay. relationship you already
[01:34:13] understand. Now, uh there are few things that just give me a second. I would like to just give me a second guys.
[01:34:38] these are boring but yeah, we'll learn few more term. So entity sets are what? What? Entity sets are nothing but the relationship. So for example, for example, we have got employee. Yeah, we have got
[01:34:53] the employee. We have got three employees. 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
[01:35:06] is Mark. So entity set will tell us the So entity set will tell us the relationship that Niha is let's say working on so and so project. So let's say this is a project. Yeah. So let's
[01:35:19] say this is a project and this project belongs to so and so department. So with the help of relationship set you at least give the basic idea of how the least give the basic idea of how the relationship is. So for example I repeat
[01:35:34] Niha is working on so and so project and this project belongs to so and so department. John is working on so and so project and that belongs to so and so department. So this is what this is nothing but the relationship set. So you
[01:35:48] can see that it depicts that E1 works in D2 and E2 works in D3. So E D2 and E2 works in D3. So E E1 works in D2 and E2 works in D3.
[01:36:05] 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.
[01:36:20] 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 you can see that high volume of monthly transaction. So that itself is enough to tell that
[01:36:36] what database are we going to use. So uh what database are we going to use. So uh we are going to use RDBMS. Why? Because see what we want is we want this customer information is currently stored
[01:36:49] in different departments and so and so and or the bank they want to consolidate it. So see they have the customer they have the credit card. So we want the relationship between the data to be there. So we'll go with RDBMS.
[01:37:06] there. So we'll go with RDBMS. So see with with the help of RDBMS as you can see it's not that RDBMS is the only way for it. So see I'll just read that is launching a new credit card product. So you're working for a bank.
[01:37:22] The bank aims to target existing customers who already have saving accounts, a good credit history and a high volume of monthly transaction. So basically it's a marketing thing. However, this uh customer information is
[01:37:35] department. So you're getting all the data scattered. Yeah. So some of the customers are in Excel, some of are in the legacy systems, some of they are in the local folders. So what they want is they want to consolidate the all the
[01:37:51] information. So usually whenever it comes to the online transactions we you we go for RDBMS.
[01:38:04] Why not uh see why not graph? Because here's no networking that is being done. So graph doesn't make sense. distributed you can say as I told you distributed is something that see distributed is something that can be used for RDM DMS
[01:38:21] that can be used for NoSQL yeah it's it's a way of storing the data uh or you can say way of replicating the data not storing the data but it's the way of replicating so uh it has nothing to do with RDBMS or NoSQL you can replicate
[01:38:37] your RDBMS data also so if you replicate it at multiple places then it becomes distributed data storage the database. So anyways so uh here we are not
[01:38:50] focusing on distributed or centralized we are just focusing on that are we going to use um which database are we going to use. So graph is definitely not the answer. NoSQL is also not the answer because
[01:39:05] here it is not saying explicitly that the data is coming in you know different different formats and we need the flexibility to in order to store the flexibility to in order to store the data and so RDBMS is the best option. So
[01:39:18] definitely you can use NoSQL also we it's not that NoSQL will say no I cannot store the data for this one of course you can use it but RDBMS will be the best choice as per the question.
[01:39:31] Yes, very good Raindu. So the data is also structured over here. Exactly. So it's not flexible. The data is structured. So guys, uh there's little more theory that we're going to cover tomorrow and
[01:39:45] it's not going to take much of time. But yes, I do not want to hurry up on covering this part. It's a very simple one. It's a very simple one. We'll understand with some examples. It will take another 30 minutes. So today maybe
[01:39:59] have any. That's the first thing. And the secondly uh let me let me Yep. uh let me let me Yep. Just give me a second.
[01:40:14] you've got any. And the second thing is that Rakita is going to help you or she's going to guide you with the lab access with the lab. Yeah. SQL lab.
[01:40:27] So composite attributes are the attributes which are created by other attributes. So basically if I say that I've got an attribute address. So this I've got an attribute address. So this address attribute is created or is
[01:40:42] composed of several attributes that is country, state and zip code. So all of these three things makes the uh makes the attribute uh address you understanding? So in
[01:40:56] order to say the address you need these other three attributes also. So this attribute is composed of these three other attributes.
[01:41:13] let's say for the birth date we have got year then we have got day and then we have got month. We have got three different attributes. So birth date will different attributes. So birth date will be composed of day year and month.
[01:41:27] No say that I would not suggest you to use any other DBMS and the reason is that sometimes the syntaxes are different. So when the syntaxes are different you will get error. So I do not want that confusion to
[01:41:41] happen. So I would suggest all of you to use MySQL. And as I told you before also
[01:41:53] give me a second. Yeah. Uh if you're using your personal laptop, it would be best that you download the MySQL, you install it and then you use MySQL workbench in the class that is installed in your machine. But in case if you're
[01:42:08] using your office laptop and you have got this limitation of uh like you cannot download and install then you can use Zlab.
[01:42:23] multialued is not just give me a second. Yeah. Is not composed of the different attributes. So this is what this is composed of different attributes. Multialue says that I can hold more than one value.
[01:42:44] So it says that I can hold multiple values.
[01:43:19] multialued says that I can have I I can accommodate multiple values. So one attribute can accommodate multiple values. values. Composite says that I am made up of
[01:43:31] multiple attributes. So there's a difference. It says that I've got three different attributes. So if you combine these three attributes then I'm composed. Yeah. So then I'm how uh this is how I've made up made up of. So you
[01:43:45] have to literally combine these three attributes in order to create me. attributes in order to create me. Multialued says that Multialued says that I can I can carry or I can hold two
[01:43:57] I can I can carry or I can hold two values at a time.
[01:44:13] example. Now tell me one thing. Phone number. So you might have seen that phone number can have multiple values. Yes or no? Multiple phone numbers.
[01:44:28] is not composed of multiple attributes. So that is multivalued. It means that one attribute can have multiple values in it.
[01:44:44] student one with some phone number. Okay, some phone number and one has got another phone number also.
[01:45:00] what multialued. It means that same student yeah same student has got two multivalued. It means one attribute can have multiple values in it. Am I now
[01:45:13] have multiple values in it. Am I now clear over here
[01:45:29] on the other hand says so it's a very different thing yeah do not mix up the different thing yeah do not mix up the things it says that see things it says that see it says for example if I explain you
[01:45:41] okay So for example we have got attribute full for example we have got attribute full name.
[01:45:54] So full name is a composite attribute because I can divide it into let's say first name, middle name and last name. So if we combine these through three it becomes full name. So that's what composite
[01:46:09] means. Yeah, that's what composite means. Multival means that one attribute can have multiple values. But composite means that one attribute can be divided into multiple other attributes.
[01:46:41] Please make sure that you guys download and install my SQL different. Yeah, derived is different. So, in case of derive, what we do is uh
[01:46:57] we create a formula. There's a formula behind the scenes. comp composite there's no formula in case of derived there's a formula so composite is saying that okay I can
[01:47:12] be divided into these three but let's say if I've got a formula let's say I've got a derived one let's say it says revenue so let's say this is a derived one and how are we calculating we are
[01:47:26] how are we calculating we are calculating with price and quantity so you're getting this is a formula that is applied in order to get the derived attribute. So this cannot be splitted into parts.
[01:47:41] You cannot split them into parts. That will not make any logic. Right? So if I give you 100 as a revenue, how can you split it into the parts? You can't split it into the parts. But if I g give you for example Miss
[01:47:54] Mary John this you can split this you can split into first name last name etc. But if I calculate revenue you cannot split if I calculate revenue you cannot split it. So this is not composite this is
[01:48:08] derived. How many entities are there? No. So those who are saying two it's not two it's one. Can you see that I yesterday we learned that entities are represented by the rectangle
[01:48:28] relationship. So this is a relationship. So basically what it is saying is now hear me out. It's very simple thing. So urary relationship is a relationship where the entity is related to itself. So it's a
[01:48:44] entity is related to itself. So it's a self relationship. itself. So if I give you this example of employee let's say that we have got the
[01:48:58] employee table. Now hear me out. It's very simple. and then we have got the name of the employee. So let's say 1 2 3 4 and name
[01:49:14] I'll say A B C D. Yep. I'm not writing the proper names. And here here let's say I've got one more attribute that is manager. So
[01:49:26] more attribute that is manager. So manager maybe manager ID. So let's say manager maybe manager ID. So let's say that A's manager is D. So I'll say four. that A's manager is D. So I'll say four. B's manager is again D.
[01:49:38] B's manager is again D. C's manager is let's say uh let's say B. And D there no manager. Yeah. No manager or so over here you can see that the table is related to itself you're getting. So the table is related to
[01:49:53] itself. So here because if I talk about the manager ID you can see that if I tell you okay let me ask you the question. So can you tell me what is the name of the manager of A? What is the name of the manager of A?
[01:50:13] Right? So this is what this is self relationship where the table is related relationship where the table is related to itself.
[01:50:25] So here because the entity is related to itself we call it as unary relationship. Am I clear? Should I move forward to the next slide?
[01:50:38] Should I move forward to the next slide? It's a very simple one. If you see
[01:50:52] 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
[01:51:06] can have accounts right? So we have got the customer table and then we have got the account table. So this is what this is a binary relationship. So it involves how many entities? It involves two entities.
[01:51:19] I hope I'm clear with this that binary relationship simply involves two entities. It's a very simple one. Let me know if It's a very simple one. Let me know if you want reexlanation for any of these.
[01:51:31] Okay. So how about turnary? So turnary is the relationship where you have got more than two entities. So basically where we have got three basically where we have got three entities. Yeah, three entities involved.
[01:51:46] And think of entities for now as a table. Think of it that entities is nothing but the synonym of table. So over here you can see that we have got three entities and all the three entities are related to each other. So
[01:51:59] employees works in a department also employee works with the organization. So and also an organization can have multiple departments. So there is a relationship between the three entities. So am I clear with this one? It's a
[01:52:15] So am I clear with this one? It's a simple one. Am I clear?
[01:52:27] a very interesting topic. The types of relationship. So uh we have got four types of relationship. One to one, one to many. So basically see basically one to many and many to one is one of the same
[01:52:42] and many to one is one of the same thing. It's one of the same thing. So u one. It's one of the same thing. So actually I say that there are three type of relationship but you'll you'll see that in a lot of blogs they say that
[01:52:56] there are four type of relationship. So do not get confused. It's all about how right to left. That's the only thing. Anyways, we're going to talk about it. Anyways, we're going to talk about it. So allow me a second. So let's talk
[01:53:10] [clears throat] one toone relationship. So what is one:1 one toone relationship. So what is one:1 relationship?
[01:53:23] we have got the employee table and we have got the employee ID table. So what one to1 relationship says that every single instance of one entity is
[01:53:35] connected to a single instance of another entity. So here when I say instance what does that mean? It's not it means nothing but the row or the record. So do not get confused what this instance is. It's nothing but the row or
[01:53:47] the record. So over here if I talk about this one this example that is being shown on PPT. So let's say I've got the employee table.
[01:54:02] Yeah [snorts] 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
[01:54:17] employee like here also let's um employee ID is not a very good example employee ID is not a very good example rather I'll say bank account. Yeah. So employees and their respective bank accounts. So here I've got the bank
[01:54:32] account table. Now when it comes to salary it's the companies you usually ask you to give only one bank account details right? you don't end up giving all the three four that you're holding. So here you've got
[01:54:45] that you're holding. So here you've got the bank account details. So again the ID of the employee and some bank yeah let's say city bank and some bank yeah let's say city bank or HDFC and so on and so forth. So here
[01:55:01] or HDFC and so on and so forth. So here the relation is one to one. Why? Because every single instance means every single row. So this is a single row is connected to a single instance of the another entity. So it means this is
[01:55:14] another entity. So it means this is connected to a single instance only. It means one instance is related to one instance.
[01:55:27] not that one instance is related to multiple instance on the other table. one instance is related to only one instance of the other table. So this is instance of the other table. So this is what this is one to one.
[01:55:40] [snorts] Am I clear what is one to one? If it is not 100% clear to be very honest once we see what others are like one to many etc you'll able to understand
[01:55:55] what does instance means. So instance means a row sa it means a row one row one record. Yeah. So it's it doesn't mean the column, it means the row. I mean the column, it means the row. I hope I've answered your question.
[01:56:12] So guys, am I clear? What is one to one relationship? confusion will be clear once we see the other type of relationships that we
[01:56:25] have. Okay. Now we look into the other one that is one to many. [snorts] one to many. [snorts] So one to many is where a single
[01:56:38] instance of one entity is connected to several instance of the other entity. So what does that mean? What does that mean? Let's say over here you can see that we have got the customer table and then we have got the order table. So
[01:56:52] this is one table let's say. So I'll just better create my own diagram over here for the better explanation. So let's say I've got the customer table. Now I'm just adding two columns. Um maybe uh yeah let me add three
[01:57:07] Um maybe uh yeah let me add three columns. That's fine. only two. I think two would be suffice. Okay. And then we have got the customer
[01:57:20] Okay. And then we have got the customer name. So let's say ID 1 2 3 4 customer name A B C D and here we have got the orders table. orders table. So order ID and other things. Yeah let's
[01:57:34] say uh the product and etc whatever you can think of. So again we have got some can think of. So again we have got some orders 10 11 12 13 14 and so on and so orders 10 11 12 13 14 and so on and so forth. So here in case of one to many
[01:57:49] one customer one customer can place multiple orders just like if I talk about Amazon. So you are the one customer one account right and you can place multiple orders right. So here also one instance this is what
[01:58:06] one instance this is what one instance is connected to multiple instance of the other table. So one record is connected to the multiple records may or may not
[01:58:18] be connected to the multiple records of 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 a customer table let's say and this is the orders table. Now tell me which
[01:58:33] table is on the one side. Which table is on the one side?
[01:58:45] Customer and which table is on the many side order. Perfect. Now as I told you that one to many and many to one. So this is here. But whatever it is. Yeah. one to
[01:59:00] many or many 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 it's just
[01:59:17] that if I put order over here then one customer can can order multiple can uh yeah order multiple orders. So here we have got order customer.
[01:59:30] So order is on the many side and customer is on the one side. [snorts] So this is one to many or many to one. It's one of the same thing. It's just that one of the same thing. It's just that you see it like this or this.
[01:59:44] one to many and many to one is nothing. It's just that right to left or left to right. Okay. So here also I think see they don't have the PPD. Oh no they have they don't have the PPD. Oh no they have the PPD anyways. So
[01:59:58] yeah. So anyways, we'll talk about many to many now. So many to many. Okay, to many, let's make the class interactive. Why don't you give me some
[02:00:10] interactive. Why don't you give me some example of many to one or one to many? Any any any example. Now guys, one more thing. So when you create the relationship between the two entities, it's not that it's a rule. Like when I
[02:00:24] not that 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
[02:00:39] side. I repeat, as for the business requirement, we can keep any table on side. So it's not that it follows the and this has to be on the many side. As simple as that, right? So now I want you
[02:00:54] guys to give me some examples of many to one or one to many. So I'll give you one example. Okay, I've started getting the examples. Yes, patient appointment date. Very good. Then okay, I'm getting a lot of
[02:01:09] student subject patient appointment. Okay. Student school. Yes, one school at least in India. Yeah, one school can have many India, a student never attends more than one school at least that's how it is for
[02:01:25] for companies also right if I'm if let's say I'm working with IBM so until I'm moonlighting definitely that is not allowed so one employee sorry uh one company being worked by u one employee working in what
[02:01:43] worked by u one employee working in what I'm saying so one company is uh can have I'm saying so one company is uh can have multiple employees This
[02:01:56] patient table, a medical test table. Very good. B customer, right? Yes. Very good. One nation, many states.
[02:02:08] Very good. One state, many cities. One city, many colonies. Perfect. So now we'll move to many to many. One person many bank accounts
[02:02:29] when you have got let's say again we have got the employee table and then we have got the project table. So one employee can work on multiple
[02:02:41] projects. Let's say we have got the projects let's say uh okay I'll just name some of the projects that I have worked on Sunrust etc. So definitely u
[02:02:53] there are situations in the company where one employee works in the multiple project right so you may end up working on multiple project also one project is most of the time being worked by multiple employees so here it is one to
[02:03:06] multiple employees so here it is one to many so ABC 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 to many so I now I want you to contribute some examples on the chat
[02:03:20] contribute some examples on the chat for many to many.
[02:03:32] can also get taught by multiple trainers. Yes, students and teachers. trainers. Yes, students and teachers. Very good.
[02:04:10] the chat. Perfect. Very good everyone. construct an ER diagram to understand the process
[02:04:23] of ER models. Now this is something that we have learned yesterday, right? So maybe I would not like to give you 10 15 minutes because that's too much of time. So maybe you can take 10 minutes to construct a simple ER diagram so that
[02:04:38] you'll able to remember that what is are the different shapes used for different different things that we have got in the ER diagram. So we'll maybe I'll give you 10 minutes you can construct if you're using paint
[02:04:51] you can also send the screenshot to us on the chart. You can also use pen and paper but not more than 10 minutes. If you're not able to finish it in the class, you can take it up after the class.
[02:05:18] got some extra time left, please open your MySQL workbench. You can open it on your machines if you have downloaded and installed and you can either or you can open the lab. You can use the
[02:05:33] or you can open the lab. You can use the lab.
[02:06:19] anything you can take maybe a training um a training company so they want you to construct some database so you can include trainers courses all of these things or uh you can choose anything from your domain also. So if you're from
[02:06:35] finance domain, you can choose anything from there.
[02:06:47] Okay. So you have taken it. Perfect. So this is for the hospital.
[02:06:59] Okay. That's from your project. Got it. Got it.
[02:07:25] Yes, we have got a lot of tools. So I have used Lucid chart have used Lucid chart and you can use MySQL workbench also.
[02:07:43] can use and for Microsoft also we have got Microsoft visa has shared one. Okay, great. Student enrolls in course. That was very small one but good. Good job Raindra.
[02:08:26] guided you on the same were you present in yesterday's class.
[02:08:57] No, diagrams are not that important because you're not anyways going to develop or design the database. You're going to use it. But you should going to use it. But you should definitely understand er diagrams.
[02:09:14] So guys, till you're doing it, uh allow me a minute. I'll just try >> [clears throat] >> Accessing the L.
[02:09:47] It seems like I do not have access to it yet. sidesh. We are on this slide.
[02:10:13] lab because I'm going to take some little do the practicals. So I want everyone to be online.
[02:10:35] rip love right we can't do it like that's their choice. So this was the assignment exercise that we had to perform in the class.
[02:10:52] and then we'll continue with our learning.
[02:11:23] everyone to answer. Let me
[02:12:10] slides that you have. Okay. So I've got these slides from simply learn guys and we are not going to learn from slides. It's a theory part. So we are using slides but we'll be hardly using the slides. So do not worry about all these
[02:12:23] slides and all. SQL is not about slides. So u but anyways I'll check with simply learn because the slides that I'm using are of simply learn only. So the answer are of simply learn only. So the answer is a guys what does it says each entity
[02:12:37] in entity set. So here each entity means each instance each record each instance each record can be associated with at most one with uh one entity in entity set B. So basically one instance in table A can be
[02:12:52] associated with one instance in table B and vice versa. So the answer is 1 one. [clears throat] Now we'll quickly cover cardality also. So what do you understand by cardinalities?
[02:13:28] just covered. But when I say what does cardarity means? Cardinality. Cardinality means how many in a relationship how many. So
[02:13:54] So basically cardality tells that how many how many rows of one table can be related to rows of another table. So how many rows of one table
[02:14:17] related to rows of another table. you know a table we can have tables and suppose there's a one row so one row can
[02:14:34] be related to only one row of another table or one row can have multiple can be related to multiple rows of another table. So cardinality is like going one table. So cardinality is like going one step deeper and telling telling about
[02:14:48] 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
[02:15:04] 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
[02:15:18] go to the next slide and then I'll explain you that would be better. So if you see over here the cardinality is represented over here. So it says zero
[02:15:30] which is the minimum cardality and n is a maximum cardality. and n is a maximum cardality. Here also zero is a minimum cardality and one is a maximum cardality. So what does it says? So it says let's
[02:15:48] 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.
[02:16:01] So, project ID and the name of the project.
[02:16:13] 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.
[02:16:27] 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
[02:16:42] 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
[02:16:55] 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
[02:17:08] the company. Until now the A is not being allocated to any project. So zero means minimum. So it means that we can have some developers who are not who are not involved in any project. N means maximum. It means that a developer so B
[02:17:28] maximum. It means that a developer so B can work on n number of projects, multiple projects. So that's what it means. Zero means we can have developers with no projects. Also we can have developers who are
[02:17:41] working on multiple projects. Now here 0 and one means again the same thing. Now and one means again the same thing. Now let's see P3 is a new project and right now there are no developers allocated to P3. So it means that we can
[02:17:57] have any any project we can have any instance in the project table which doesn't have relationship or which doesn't have any connection with any of
[02:18:09] the other instance in the developer table. So we can have a project with no developers. So that's what zero means a project with no developers in this example. One means well that's quite weird because that doesn't happen in
[02:18:23] real life but yeah so one means that a project yeah a project can have maximum one developer. So here let's say that the company is maintaining the projects which are very very small projects. Yeah very small project just need one
[02:18:39] 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 pro uh the project can have one developer and that's why what is this?
[02:18:51] developer and that's why what is this? It is one. So B is working on 1 2 3 also It is one. So B is working on 1 2 3 also B is working in 4 5 6 but one project cannot be worked by uh multiple developers. So am I clear
[02:19:07] uh multiple developers. So am I clear what does 0 1 0 n denotes what does 0 1 0 n denotes h [clears throat] fine anyways we have understood it that is more important
[02:19:26] what does 0 n 01 means you can just read through this slide but please confirm if I'm clear or
[02:19:40] So, still I'll just wait for a minute. You can just read through it. It's a very simple thing. So, if you see these numbers, do not get scared. It's very simple thing. Minimum and maximum.
[02:19:55] 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
[02:20:10] 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
[02:20:22] means? It means now we have got this stable developer and we have got the stable project. So zero means that we can have some developer can have some developer with no projects related to it. So let's
[02:20:36] say that A is not working on any of the project. A is a new hire in the company and till 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.
[02:20:51] N means a developer can work on multiple projects. So B can work on 1 2 3. B can projects. So B can work on 1 2 3. B can be related to 1 2 3 4 5 6 and 7 8 9. So that is very much possible. Okay. So this this is what 0 and N means. Now
[02:21:07] here it says 0 and 1. So what does that means? It means again you can have a project. So let's say this is a new project. Till now no developer is project. Till now no developer is working on this project. So think of it
[02:21:19] as Upwork. Yeah. on Upwork you see that you got a lot of projects with no developer assigned right so we mean we can have the project with no developer so zero means that a project it is possible to have a project with no
[02:21:33] possible to have a project with no developer so this instance this instance developer so this instance this instance is not related to any of the instance of the developer table so this instance zero means it is possible to have
[02:21:46] instances not having relationship with the instances is of the other entity. One means maximum. So maximum means it says that one project can be worked by says that one project can be worked by only one developer. So 456 is worked by
[02:22:02] B. So that's all you cannot have C also working on 456. It says that maximum one instance can have relationship with maximum one instance
[02:22:16] 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 minimum is zero either no relationship or at max one one uh row. Am I clear
[02:22:31] or at max one one uh row. Am I clear now?
[02:22:51] wait for 30 seconds you can quickly reread this uh slide so you can read what it says over here you can read what it says over here so if you have little bit confusion here and there if you just read the slide maybe it will get Yeah.
[02:23:36] relationship over here. Sai, it's about cardinality. Right now we are learning cardinality. Right now we are learning that how cardinality works. many not many to many.
[02:24:09] so zero one project can have zero developers or one developer not many what number do you see over here one right so either zero or one developer
[02:24:34] project with many developers, it would have been 0 comma n. on the chat. Guys, if I miss out any of the question, please make sure that you
[02:24:46] the question, please make sure that you repost your question. Okay. So we are done with definitely we are done with the theory part. So that's
[02:25:00] a good thing. Now what we going to do is uh I'll just give you the brief the very very brief of the whole thing that how you can create the database how you can create a table how you can insert the data into the table and then later on
[02:25:15] we'll deep dive into each and everything for database also what all commands we have for tables what all commands we have so we are going to deep dive into everything but for now let's for 10 15 20 minutes let's practice or let's see
[02:25:29] 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 MySQL workbench. You have to open it. Now you can launch the uh labs. So
[02:25:46] yesterday Raashta helped you with the launching of the lab. If you're still facing any issues, you can post it on the chat. We'll see if we can help you right away. Otherwise, at the end of the session, please make sure that you're
[02:25:59] use the lab because if you're not able to use the lab, then you're not able to do the hands-on in the class. So, I'll try to give you at least some hands-on if not all the time like at least sometimes I'll try to give you the
[02:26:14] hands-on if not all the time. So, uh your labs should definitely be working. are not working, you'll not able to learn with me. Of course, you'll able to
[02:26:26] learn with me, but then you have to practice after the session. But yeah, session whether your labs are working or not. So uh please make sure if your labs are not working, you're vocal about it and if you have to share your screen and
[02:26:40] of the session to share your screen and we we'll try to solve the issues with your lab. So I want to see if you done on the chat once you are done with opening the lab. So password is same for everyone. I
[02:26:57] think the password guys what's the password? I think it's p sd is it1 and uh discrimination work or is it one? Okay. So see everybody's helping you with the
[02:27:10] password. You can use the same password. Thank you everyone. Thank you so much.
[02:27:25] everyone to launch the lab. I want everyone to be on the same screen.
[02:27:40] fine. Definitely fine. So workbench is what? It's a UI way of working with the databases. You can also work with the databases via commands command prompt right cmds. So that is definitely not how we work
[02:27:57] with the databases. We always use workbench because that's more friendly workbench because that's more friendly and easier to use.
[02:28:44] 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
[02:28:58] 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 So every time we are going to add the data for every class.
[02:29:33] that we have got one schema. Now this is a default schema. So schema is nothing but in MySQL it is like a database. Now what is a database? You already know
[02:29:45] 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 you digitally. So we are going to create the database.
[02:30:02] Yeah, if you're using your personal laptops, as I told you yesterday also, it is better that you install my SQL rather than using the labs because u it will be easier for you to access it. So ana it's not when you are downloading or
[02:30:19] installing the SQL. Yeah, it's not only about next next. There are some settings that you have to do. So please make sure that you go through the video which I shared yesterday. Today also I've shared it multiple times. So please go through
[02:30:32] software installed if you do not have the software ready for you to do the hands-on. Please do not start installing it right now. You can uh write down the
[02:30:44] hands-on with me. But you can always write down those notes. You can stay with me in the class and you can do the installation etc after the class. But please make sure that you concentrate in the class just because you're not able
[02:30:58] to practice. Uh that's very much okay. Yeah, you can write down the comments. So it's okay if you're not able to type it. You can always write it. Okay.
[02:31:23] by everyone. I'm resharing the password for the labs.
[02:32:11] Oh, I have no idea about this this thing. Shri, I have no idea about Yeah, this thing. Unable to launch the remote. So, Shri, you can try it with V withya. Yeah. So you can try later just after some time
[02:32:26] you can try later just after some time just retry it. you're facing any issues with the lab you can keep it parked for now. So I'll
[02:32:39] 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 out. So let's continue with our learning for today.
[02:32:52] And as I told you, if you're having issues with the lab that is that is issues with the lab that is that is provided to you all by simply learn, if you're using your personal laptop, please download and install my SQL. So I
[02:33:05] will not be using lab. I'll be using my SQL on my personal laptop
[02:33:53] 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
[02:34:09] everything. So in order to create the database in order to create the database this is we going to write the SQL in order to talk to the DBMS. Now I want to
[02:34:25] tell DBMS that I want to create the database. So I'm writing the SQL command in order to create the database. So I'll say create database and I can give any say create database and I can give any name to my database. But this has to be
[02:34:40] 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
[02:34:55] the name to my database as school. Yeah. And then see I can always end my comment using the u
[02:35:08] u what do we call this? can use upper case and lower case both. Yeah using semicolon. Thank you so much.
[02:35:23] 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.
[02:35:37] Just give me a second. Yeah, I think the size is already increased. So anyways, so this is the button that you need to use in order to execute a command. So if I just hover on it, it says execute the execute the selected portion of the
[02:35:53] execute the selected portion of the script or everything if there is no anything, yeah, I'm not selecting anything. If I execute it, it will execute everything. Each and every line
[02:36:07] of code, each and every word that is written on my SQL file. This is what this is a SQL file. You can say SQL file. So because I've got the previous SQL file in my case it shows six. In your case if you're using it for the
[02:36:19] your case if you're using it for the first time it might be showing one. first time it might be showing one. So I'm going to run it. Once I run it So I'm going to run it. Once I run it over here you simply have to expand the
[02:36:32] lower panel and you'll see that create database school one row affected and you can see this in green. It means that the database has been successfully created.
[02:36:45] there was some error when you were creating the database. Guys, please make sure that you are attentive in the class. Otherwise, what will happen is that you may get some errors because you're quite good in number. So if
[02:37:01] you're not attentive then you may get the errors that I can always resolve. But if I get a lot of queries from you guys just because you're not attentive then I will not able to cover everything whatever I want to cover as a part of
[02:37:17] this training. So I don't want to cover anything on the surface level. I want to deep dive into the topics. I want to make sure that you understand how things are working. So for that I want only I have only one request from all of you.
[02:37:30] Just be attentive because if you're attentive I'm 100% sure you'll able to learn it. SQL is very easy. So you can see that the database has been created. In my case I've got the other commands. So if you have noticed I
[02:37:43] was some dropping I was deleting the databases. So these are because of that. But yeah the database has been created. So I want you guys to create the database and let me know when done. So you can just write D also instead of
[02:37:56] writing the whole run. So a few runs will give me help.
[02:38:20] where you can see the database. Right now I just want you to be sure that the command that you have written is executing successfully or not. So just that you have to write this command Rajni. Yeah. So you have to create a new
[02:38:34] 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
[02:38:48] you're done with writing the command you can run it from here.
[02:39:02] you're not able to see the output. So just hold your horses. We'll talk about that also. Now I can see that most of you are done with running it and I hope that over here you see a successful message. This green button this green
[02:39:18] 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 my SQL
[02:39:31] 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
[02:39:45] appeared. So you get to see the output now. see the output I hope that you guys are able to see the database created.
[02:40:01] 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.
[02:40:19] You have to It might be collapsed like this. So you literally have to expand.
[02:40:32] Okay. One more thing you can go to view you can go to panels and over here guys please make sure that you're looking at my screen so see output area I can hide it so maybe your output area is hidden so what you can do is you can go to view
[02:40:45] 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 view
[02:41:00] you can see just uh check go to panel you can see just uh check go to panel and click on show sidebar I I hope I've answered the queries or the queries on the chat
[02:41:34] to see the output panel, you can still continue practicing with me and we'll the problem. I'll ask you to share your screen and we'll look into it.
[02:41:53] because I've told you that these u we these could be the problem still you're not able to see then you have to share your screen okay so at least you can your screen okay so at least you can write the Good.
[02:42:28] Basically you have to select the database. So how would you select the database? How would you select the database in order to select the database? Either now hear me out. Either you can
[02:42:41] doubleclick on the database. See if I double click it got selected. Or what I can do is I can run the command. So just delete the previous command. And you can
[02:42:56] And you can run the command use the name of the database. You can run it and then it will
[02:43:10] basically select the database. So how you'll get to know that this database is selected. So you can see that this is in bold. So as soon as any database shows in bold it means that this database is selected and whatever
[02:43:24] whatever you're going to write or whatever queries that you're going to perform will be performed on this database. So just double click on the database. So just double click on the database or you can
[02:43:37] I would suggest you to write this command use and the name of the database
[02:44:01] 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 see you can collapse and expand the database. Now database has got something
[02:44:14] called tables. So these are the entities. Yeah. So we were using the word entity entities. So tables, views, stored procedures, functions, these are the entities. So in your curriculum you have got tables and views. So we are
[02:44:28] 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
[02:44:40] are going to cover later on. So what we going to do is just give me a second. School is not highlighted but command executed successfully. Just double click executed successfully. Just double click on it. Sheila.
[02:45:02] So you'll get to know all of these things. Yeah. Hold your horses everyone. class and we've just started coding. You'll learn everything. Fine. Now what we are going to do is
[02:45:14] right now you can see that I we do not have even a single table in our have even a single table in our database. guys as I told you please uh don't worry about not able to see the output panel
[02:45:29] not able to see the schema panel we'll see to that at the end of the session right now just focus on the learning so I have already helped you with it but then I have to look into I have to look at your screen and then I have to help
[02:45:45] you. Okay. So we going to create the table. So how do we create the table? So in order to create the table we write the command create. So you can make it uppercase lower case everything is okay. So let me make it upper case. So create
[02:46:02] table and then I'm going to give the name of the table. Now I want everyone to see my screen.
[02:46:22] 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.
[02:46:37] And then we give the data type of the column. So yesterday when I was teaching you the different type of database that are available in the market, I told you are available in the market, I told you that RDBMS is very strict. It means that
[02:46:49] 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
[02:47:03] means the type of data a column can hold. So u ID can hold integer. in IND means integer data type. So we are going to learn about all the different type of to learn about all the different type of data types also later. So for now
[02:47:18] this much of knowledge is more than enough. Okay. The next column is let's say full name and full name has a data type vcap.
[02:47:39] and age has got the data type in. Yeah, age is always a number. So vare what does vare 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
[02:47:57] 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
[02:48:12] 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 bar 50
[02:48:26] and my table the code for the table creating the table is ready. So just give me a second. I'll just quickly look into the chart. Vidya what you're saying is right.
[02:48:41] Valkcan can accommodate numeric values as well as alpha numeric values. But it is always considered as a string. So what I mean to say is that this is what this is a number. But if you give three in vcar it will take it like this.
[02:48:58] So how it is going to hold it that's matter. So it will it will u make three yeah it will take three not as a number but as a string. Yeah this is what this is a string. So like this. So anyways the code
[02:49:14] string and var 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.
[02:49:26] say text it's all about the data type. Yeah. Or if I say vcar. So in SQL we say Yeah. Or if I say vcar. So in SQL we say vare right in Python we say string text we don't say in any of the language computer language I'm talking about so
[02:49:39] 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
[02:49:54] 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. And I'll wait for a minute for 2 minutes.
[02:50:31] showing error, you also read the error. So please delete the above commands that 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
[02:50:43] show you how the does the error look like. So what I've done is I've created the table. The next thing what we will do what we will do the next thing
[02:50:57] creating the table. Now we are going to insert the data into the table. You don't need to use the table as such just like DB databases.
[02:51:16] chart. So I'll 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.
[02:51:32] Now that is optional. We'll come to that later. But right now as I told you just a helicopter view of everything, a small snippet of uh SQL how we can create the table
[02:51:49] insert the data into the table and query the table. So anyways, I'm going to give
[02:52:06] SQL is not case sensitive and then I I can give the values.
[02:52:19] So in values let's say id is one. Now it's a var 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 and
[02:52:32] the in the single quotes. Amit age and age and city comma I can give multiple values. So I'm just copy pasting it. So let's say ID is just copy pasting it. So let's say ID is two. The name is
[02:52:53] In the same way I can insert more and more records. So I have to add comma then again I can insert more records. Three.
[02:53:12] see that I have I'm done with writing the command for insert for inserting the data into the table. So this will insert three rows into the table. Now guys, I'm intentionally going to throw the error. So please make sure that you look at my
[02:53:26] screen because you may also get the same errors again and again. So please understand how things are working. So right now if I execute it, you can see right now if I execute it, you can see that it is giving me error. So
[02:53:40] I want you guys to tell me what is the error, what is the problem.
[02:53:56] 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
[02:54:09] 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
[02:54:26] taking both both the commands as one command. So how I can do this separation? By adding semicolon. As soon as I add the semicolon, you can see that see the error that I was getting over here. It will get resolved because I've
[02:54:42] added the semicolon. So this was a first problem. Now let me rerun it again. It will throw error. So see now you'll able to understand what the error is. Table student already exists. So basically what is happening I'm when I'm running
[02:54:57] it, when I'm running it, it is executing this statement. But the problem is that the student is already there in this database. Student table is already there. So it is saying me that student table is already there. So that is the
[02:55:11] problem. So anyways, I don't want to run this because I already have the student table. So what I'm going to do is I'm going to run only this command. So see I have to select this command and I have to click on run. So I told you that when
[02:55:25] 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 that you want to run. So here I'm selecting this code and I'm going to run
[02:55:39] 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.
[02:56:14] You can just Google the shortcut keys. I'm very bad with reme remembering the I'm very bad with reme remembering the keys.
[02:56:29] question that is asked to me in every batch and I tend to forget the answer.
[02:56:41] Yeah. So, I I'll help you. Just give me a second. I'll also I can also Google
[02:56:58] selected. So you can just double click on this database.
[02:57:17] The shortcut is control + enter to run the query. Control + enter.
[02:57:29] 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
[02:57:41] 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
[02:57:54] If you're not able to see the tab this left panel just you run this command use cool and then create the table. So guys let me know when you're done with inserting the data also I'm sharing the code on the chat.
[02:58:48] it is not working. I'll check the screenshot. So guys, we'll wait for screenshot. So guys, we'll wait for another 1 minute.
[02:59:04] just did chat GPD showed control + enter.
[02:59:28] to use the quotes for the varita. So whenever you're writing the strings, strings are always represented in computer in in machines. um whether you're writing SQL or whether you're writing you're working on Python, C,
[02:59:43] 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
[02:59:57] 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've got three rows of
[03:00:12] 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 the here we go guys. So I'm writing in the same file. Select star. So star means
[03:00:26] all the columns. All the columns from of the table? The name of the table is students. Again, please highlight it and then run it. Do not just run it
[03:00:40] without highlighting it. Otherwise, if you have the previous code, this will also run and you may get error that the table already exists and so on and so forth. So, please highlight it and run it.
[03:01:28] we'll see to that also. Okay, I'll just wait for another 1 minute.
[03:01:49] screenshot of the error that you're getting. So maybe you do not have the student table created or maybe I think u the database is not selected there could be some problem. So unless you do not send me the you also do not send me the
[03:02:06] screenshot of the error I may not able to help you. So right now what I can see is that you have written the right code. So there's no problem with your code So there's no problem with your code Fatima.
[03:02:24] to look into all these commands again. Right now it was just a glimpse of it commands. So that time I'll tell you that what are the other ways of that what are the other ways of inserting the data.
[03:02:40] database. If when you drop the database definitely your tables will also get dropped. Whatever you have got inside the database will also get dropped. So one way is that you right click on it and you drop it. So right now I do not
[03:02:53] want you guys to do it in this way because you're learning. So I want you guys to write the commands as much as possible. So yeah this is one of the way I was doing this. I got a lot of database. If you saw I was dropping all
[03:03:05] my databases to have a clear clean screen for all of you. So I'm going to drop the database. So whatever we have created we going to drop it. As simple as that. So how do we drop it? You have to run the drop command. Drop database.
[03:03:19] Now again it is giving error. Why? Because I have not separated the two commands using the semicolon. Okay. Drop database and then the name of the database. Yeah, I am going to select it
[03:03:35] and then I'm going to run it. So do not get bugged by it. I know that we tend to get bugged by it. I know that we tend to get bugged by it. So please do not u get get bugged by it. So please do not u get um annoyed by it. You can keep writing
[03:03:48] concepts. Fine. So we have created the database. Let me end this command with the help of semicolon.
[03:04:06] drop database and again the same database DB00. So we are going to rerun the commands in order to understand the working of it. So I'm simply highlighting it and I'm executing it. The database has been dropped. Now
[03:04:22] 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?
[03:04:45] how about others? So guys, I'm not expecting the right answer. I'm asking 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.
[03:05:00] 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
[03:05:16] 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
[03:05:32] 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
[03:05:46] that's okay. Yeah. So now if I run it it will show me warning. will show me warning. So I want everyone to try this command.
[03:06:09] If not done, let me know. So, whenever I'm asking you to do the hands-on, make sure that you do the hands-on. It's not that I'm going to ask you to do the hands-on every single time because we also have to make sure that we cover the
[03:06:21] curriculum and we cover the maximum concept, but I'll try to give you as much as hands-on as possible in the class. Now let's recreate the database. Right now it's like we do not have even a single database. So let's recreate it.
[03:06:41] at the end of the session. I'll ask some one of you to share the screen and we'll look into this problem. So please do not get bothered of about the same.
[03:06:53] Fine. Now let's say I'm deleting this drop database if exist. Now I want to know that what a database I've got in my machine in my sorry not
[03:07:06] machine in my workbench. So let's let me do one thing. Let me create another do one thing. Let me create another database. So DB 100 I created before and database. So DB 100 I created before and now I've created DB200. So just uh just
[03:07:19] for the sake of creating I'm creating two databases. So you can also go ahead two databases. So you can also go ahead and quickly create the two databases.
[03:07:38] know that what all databases you have got in your MySQL. So what you can do is you can run the command show databases. So this will show you all the databases that you've got
[03:07:54] in your DBMS. So see we have got these databases. Now you'll see some extra databases. For example, you'll see information schema, performance schema system. Anyways you can see over here. So these are system defined databases
[03:08:09] and maybe I'll tell you about these databases not today but yeah in some one of the class that what these databases store. So today is definitely not the right day to tell you about these things but yeah we're definitely going to talk
[03:08:22] but yeah we're definitely going to talk about these system based databases. Oracle I've never worked on with the show databases. So I've never worked on Oracle and Postgress. I've just worked on MySQL and SQL server.
[03:08:37] 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.
[03:08:52] let's say that uh okay, I'm going to use the database. So, I'll say use DB 200. I'm using this database.
[03:09:05] Now, once I'm done with using this database, I would like to create this table and insert some data into the table. So basically I'm using this table. So basically I'm using this database and then I'm creating I'm
[03:09:18] creating the table and I'm inserting the data into the table into this database. So this we have performed previously also. You can see that the table has been created and the data has been inserted into it. So I'm just copy
[03:09:32] pasting this code on the chart. You can also go ahead and use any of the database and you can create the table into that database and insert the data into the into the table. So I'll wait for 2 minutes.
[03:09:51] 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.
[03:10:05] Okay. So, Fati Fatima first of all see I asked you to create multiple databases. In my case, I've created two. So, first of all, I'm using this database. So, I ran this command and then I've created this table and inserted the data into
[03:10:20] the table. So, you can copy paste this command from the chart. I've shared all the chart and you can run it. So, that you have got a table created. Yeah, you've got a table created into this database. Fatima
[03:10:52] the tables that are the part of this database DB200 in my case. So what I can run is I can run this command show tables. So this will show me all the
[03:11:04] tables. So this will show me all the tables that exist in DB200. So right now tables that exist in DB200. So right now I've just got one table. But had I got more than one table, it would have shown me the name of all the tables.
[03:11:17] The table is not creating. So Mohammad, please make sure that you also run use the database, the name of your database. Make sure that you run this command Make sure that you run this command before creating the table.
[03:11:47] database commands. So now we going to learn about the data types in SQL. So if you remember when I was telling you to create the table when I was teaching you how you can create a table I told you that for every column
[03:12:02] table I told you that for every column you also have to tell the data type. The data type is what the type of data the column can hold or will hold. So here we go. We are going to learn about the different different data types that we
[03:12:17] have in MySQL. Now before we get on to that uh I will just quickly tell you that how you can save your code. So one of you was asking me that Tulika this code we have written how we can save it. So how you can save
[03:12:31] it is can you see the save button over here. So all you have to do is now all you just try it by yourself you'll able to do it because we all are using
[03:12:43] to do it because we all are using laptops. Yep. since ages. So the a lot of options are same for every application. So you can go over here, you can go to file maybe here. Do we have the save option? Yeah, we have got
[03:12:56] the save option over here also. This is a shortcut save script as. So the same options we have got. So what you can do is you can click on it and maybe you can save it. So I'll just quickly show you and then I'll give you time to just try
[03:13:11] it at your end. So let's say my SQL 100. This is what I'm saving this file. Now I'll just show quickly show you on the desktop. Okay. Where? Yeah, this is
[03:13:23] the desktop. Okay. Where? Yeah, this is the file. My SQL 100. Now if I want to open it, how can I open it? Let me close it. How can I open it? Just like how you use any other software. Yeah, the first
[03:13:36] 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 script and I literally have to
[03:13:50] uh find where I have sh saved it. Yeah, here we go. And I'm I'm I'll open it. So in this way you can save the SQL scripts that you're writing. So maybe I'll wait for 2 minutes. You guys can just try saving the SQL script
[03:14:05] guys can just try saving the SQL script file.
[03:14:28] different type of data types that we have got in MySQL. please make sure that you listen to me. Very important.
[03:14:42] So the first data type that you can see is care. So care simply means is care. So care simply means characters. text, string, whatever you want to call it.
[03:14:58] 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
[03:15:12] zero characters. So that's a minimum range or maximum range is 255 characters. Yeah. Now when I say it's a fixed length string, what does that mean? It means hear me out. So if I write let's say id
[03:15:29] hear me out. So if I write let's say id and if I say care and if I give two so and if I say care and if I give two so it means that ID when I insert the it means that ID when I insert the values when I insert the data into the
[03:15:41] 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
[03:15:59] 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 Let's say the column is okay. I'll create the column [snorts] code.
[03:16:17] And I give the car as a data type. And I give three. It means that whenever I insert the values, insert the data into this column, it has to be fixed length. See, it says it has to be fixed length. Means that I
[03:16:33] it has to be fixed length. Means that I have, it has to be fixed length. So, I have to give three characters only. Yeah, three characters only. Or let's say if I give anything else, let's say uh something else. So, I'm just thinking
[03:16:47] 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.
[03:17:01] 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
[03:17:14] 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
[03:17:26] 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.
[03:17:40] Am I clear with care? I've given you multiple examples. Am I I've given you multiple examples. Am I clear?
[03:18:01] think V stands for? What? What does this stands for?
[03:18:15] 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 var 100 it means that
[03:18:33] it can take the characters up to 100 so let's say I create a column name the data type is vare and if I give let's say 50 over here. It means that
[03:18:47] when I insert the values into this column I can give anything. I can give column I can give anything. I can give tulika which is of six characters. I can give Ravi which is of four characters and I can give a very
[03:19:01] 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
[03:19:17] 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 is. Yeah this is what vare is. Just give me a second
[03:19:36] vcare a lot. Now some of the data types you're going to use 90% of the time. So vcar is one of the data type. 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.
[03:19:53] 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 where care is saying
[03:20:07] that oh I can capture only 15 255 characters I can't go beyond it that's not in my nature. So what you can do is for a column like review or description for a column like review or description you can simply make it text.
[03:20:22] you 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
[03:20:38] 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
[03:20:54] 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.
[03:21:12] So for that you need to create the column with a data type blob. column with a data type blob. In 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
[03:21:26] take negative numbers and positive numbers as well. positive you have seen it can take negative numbers as well. Now if you want uh if you know that your column is going to store the integer and the
[03:21:40] integer is going to be very small in number right it's going to be very small in number so usually let's say age yeah age you know that age cannot go beyond maybe 120 not more than this you know that yeah that 120 is also like a very
[03:21:56] high age that I'm talking about so what you can do is you can go with tiny int instead of int so let's say that you want to save salary. So for salary, int is a uh you can go with int. But when it comes
[03:22:11] 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 int small integer.
[03:22:27] 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 int may
[03:22:41] fail because again it has got some upper limit. So then you can go with big. talk about float. So float is what? Float can store
[03:22:57] 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.
[03:23:15] Double is again for the decimal. Yeah, you can double is also for the decimal numbers. But double is high precision whereas float is uh less precision. So
[03:23:27] whereas float is uh less precision. So what does that mean?
[03:23:42] What do you think what high precision and low precision means?
[03:23:58] nothing to do with my SQL. What does nothing to do with my SQL. What does precision means? sorry simply means just give me a second I'll write it.
[03:24:15] I'll write it. Yeah. So it means precision over here. So what does it mean? It means the total number of digits stored. I'll write it number of digits stored. I'll write it over here. Total number of digit stored.
[03:24:38] So see when I say float. Yeah. When I say float. So float says that suppose in got a number like this. So I'll just write a big number 5 6 7 8 9 10
[03:24:53] something like this. Yeah. 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 store something like this maybe. Yep. Uh five and then seven. So it will round it
[03:25:08] off. Now double will also round it off but it will it will round it off at the higher precision. So it will maybe do till here and then it will round it off. Something like that. Yeah. Then it will round it off. So it's all about uh
[03:25:25] 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
[03:25:37] has got higher precision where the number get you know rounded off like this. So that's what it means. Yeah, that's what it means. I hope I'm clear.
[03:25:59] digit stores and lower means few fewer digits uh stored. digits uh stored. >> [clears throat]
[03:26:11] So anybody who doesn't know about boolean, you can be very very honest here because I understand that a lot of you are coming from the nontechnical background. So this will give me the clarity how how
[03:26:24] do I have to explain you boolean? Anybody who doesn't know Anybody who doesn't know okay I'll explain. So boolean boolean is a very important data type which stores two values. Yes,
[03:26:42] which means one. Yeah, either you can also denote it with one. So yes, it's not yes actually it is true. So let me write true. True means yes. So true.
[03:26:55] And then we have got false. Plus false means zero. So it stores only two values that is either true or false and it is a very either true or false and it is a very important data type. So for example if I
[03:27:10] just give you one uh lemon example you might have seen check boxes. Yeah when you're filling the form you might have seen check boxes. So if you check it so ideally because I come from the background where I've also developed a
[03:27:25] lot of application. So that's what I'm telling you that when you see the check boxes in the back end, these check boxes are of boolean data type. It means that if you check it, it will hold the true value and if you do not check it for
[03:27:41] 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
[03:27:55] 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
[03:28:07] itself says it will store date as well as time. 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
[03:28:20] it can store both the things. Both the things. 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
[03:28:35] auto date and time system days. It means that if you give any column yeah if you write any column with the data type of time stamp what it will do
[03:28:51] 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.
[03:29:10] system. Exactly. Very good. Fine. So we have covered most of the data types all the important data types. I would like to tell you one more thing. So we have got signed and we have got unsigned. So let's talk about this also.
[03:29:25] 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
[03:29:38] about the data types which can store numerical values. So here I'm talking about the data type which can store numerical values. So signed simply means
[03:29:50] 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. If you remember from the previous slide,
[03:30:15] see we have got tiny int small integer and this minus 128 to 127. from minus 128 to 127.
[03:30:30] And if I create a column, let's say I create age column. create age column. And if I give tiny hint,
[03:30:43] 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
[03:30:56] and this is a lower limit. Five. What is unsigned means? Unsigned means that if I make it unsigned. So if I again say age tiny okay here it is already written. So
[03:31:12] maybe okay let me write it. The writing is so bad. Okay tiny so difficult to write on the note uh on the pad. Anyways tiny int and then if I
[03:31:26] give unsigned what will happen? So this will give me extra cushion. How it will give me extra cushion? It means that I'm explicitly telling SQL hey SQL I want to use tiny int. But I do not want this negative
[03:31:42] numbers. I know that age is never going to be negative. Well age is never going to be 200 and 255 also. But yeah that can happen if we are uh u working on to age. I think toto can live till 300. So anyways, so let's say that I'm just
[03:31:59] telling that I do not want negative numbers rather can you just give me extra cushion. So just remove the negative all the negative numbers and can you just give me extra cushion for the positive numbers? So that's how we
[03:32:12] can use it. Yep. So here you can see the limit is 0 to 255. limit is 0 to 255. So whatever numbers we had over here, they have been added over here. Yeah, you just have to do the
[03:32:26] calculation. This will come around to be 255. So we are saying I do not want negative numbers. So do not waste my range in negative. Yeah. So I just want
[03:32:38] the positive numbers. So unsign will help me to increase the range. help me to increase the range. Am I clear Am I clear with what is sign and unsigned
[03:33:03] forgot to open my notes because again we are going to create a table and this time for the table because we going to just play around with the databases the sorry with the data types So
[03:33:17] yeah, the table is going to be a big table. So right now I'll ask you also to copy paste it. But after the class, please make sure that you type all of these statements. So what you can do is you can um I I'll share all these notes
[03:33:32] also with you. I'll share these PPTs also with you. But I would suggest you to make your own notes, right? So what you can do is whatever comments that I'm giving you on the chat, you can create your own wordpad, notepad file and you
[03:33:44] can keep saving those comments. See, I want you guys to be very very active during the session because when you're active, when you're participating on the chat, when you're doing the hands-on, let's say you're just picking up the
[03:33:57] code and pasting it and saving it in your notepad file, what happen is you tend to be attentive. But if you're just lying down and listening to me, then I'm sure you're going to daydream a lot. So I do not want that to happen because
[03:34:12] each and every minute of the session is important. So anyways, here we go. This So we already have got the database, right? I'm just deleting all this. So this is a database and here I'm going to
[03:34:25] create this table. So I'll just quickly run this command. Just give me a second. Yeah. So the table has been created. Now
[03:34:40] you can see that this table has got my data types. So it has got int. int is unsigned. What was unsigned? Let's quickly see what was unsigned. Only positive numbers. So it gives a
[03:34:56] really huge question. Yeah. If I just remove the negative numbers, you you can do the calculations. So it will be something around 4 ++ plus. So I get extra cushion of positive numbers var you already know. Okay. Here again
[03:35:13] something called small ints also which is I think smaller than tiny int. Then or maybe let let me make it tiny int. Yep. Then decimal. So I'm giving the precision over here.
[03:35:29] So 82. What does that mean? 82 means that I can have digits like 1 2 3 4 5 6 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
[03:35:44] many digits? So I can have 1 2 then I've got float. Then I've got boolean which can have true or false.
[03:35:56] 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 definitely more cumbersome than creating the table with so many data types. So I
[03:36:09] want you guys to see my screen. So here we go. I'm inserting the data. So here we go. I'm inserting the data. This is int. This is vare. Yeah, this is This is int. This is vare. Yeah, this is int. This is vare. This is tiny int.
[03:36:24] This is again tiny int. So this one is unsigned. This one is signed by default. decimal this is float boolean I'm giving one I told you that either you can give true or you can give
[03:36:39] 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
[03:36:52] 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
[03:37:07] right name of the table and it is done 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
[03:37:21] you know that I definitely I do not want you guys to copy paste you guys to copy paste so run this command
[03:37:33] So, uh, see guys, uh, in the last in the last insert command, we gave the column name. So, we can skip the column name. Over here, if you see, I have skipped the column name. So, when can I skip the
[03:37:49] column name? I can skip the column name when I know that I'm going to insert the values into each and every column and the sequence is going to remain same. Yeah, the sequence would will be the first value that I'm giving is for
[03:38:05] first value that I'm giving is for product ID. The second value is for know that the sequence and the columns I'm giving values to all the columns.
[03:38:20] Yeah, I'm when I'm inserting the data, I'm inserting the data into all the columns. Also, I'm maintaining the sequence of the data or I'm matching the sequence of the inserted data with that of the columns that I've got over here.
[03:38:37] So, if I know that that's the case, I can skip the name of the columns. Yeah, I I can skip the name of the columns. So, we are going to talk about this thing more. Yeah, that when you can skip and when you cannot skip. I we are
[03:38:51] going to talk about this more. So, uh right now our agenda is to learn about the data types. Yeah, the agenda for writing this code is to understand the data types. So, yes, now I'll I hope that I've given
[03:39:06] you the code. Yeah. So, I've given you the code now. you change the sequence then it will give you error. For example, if I try to
[03:39:19] insert laptop over here. Yeah. So let me do that. Yes. Let me I'll just give a string over here. Let's say I am giving a string over here
[03:39:34] was a quantity. But what I've done is I' I'm giving a string. It's an in integer. Yeah. It's a tiny int. So it will throw error. Now if I run this, it will throw error. It has thrown error.
[03:39:50] It has thrown error. See, so when you're inserting the values, you have to make sure that these sequence, this sequence matches with this sequence. This is very important.
[03:40:09] Create the table, insert some data into the table. You just check the format of the table, the data. Yeah. And then query it. And we done with today's query it. And we done with today's learning.
[03:40:27] 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.
[03:40:48] you already know about it. We'll learn about the constraints. Then we'll see how we can insert the data. So I you already know this. We have seen we have already seen worked on the insert into
[03:41:02] are there I'm going to tell you those things. Yeah then we are going to start with a wear clause. So we'll write a lot of queries. So please make sure whatever of queries. So please make sure whatever we have learned till now you have very
[03:41:15] much hold or grasp on the concepts otherwise you'll struggle and I'll also otherwise you'll struggle and I'll also struggle as a trainer. So make sure that struggle as a trainer. So make sure that you revise and come
[03:41:35] time, Siddhart. So, you can just try refreshing it once again because you're using the online lab. So, sometimes you may get the these error uh these may get the these error uh these problems.
[03:41:53] speak to me and if you have any questions you can also ask the questions. So first I would like to take up the questions that you have got from today's session
[03:42:05] and once we are done with that then I will take up the questions regarding the lab. We'll try to sol resolve the lab issues. So Sat you have to just wait and watch and retry.
[03:42:23] there's anything. Okay. I'll just share it right away. That's fine. H it's fine. So let me share this PPD right away on the chat but I also upload
[03:42:35] it on the elements portal of simply learn. and as I told you PPD I'm going to show you sometimes we are going to do a lot
[03:42:49] of hands-on. So SQL is not about PPD. SQL is coding SQL SQL is coding only like it's like maths. The more you practice, the better you become at it. Now you already know how to create a table. So rather than
[03:43:04] you know writing the same code again and again typing the same code, I would like to quickly copy paste some of the code. So uh you know that how we can create the table. So I'm just taking this code of creating the table.
[03:43:18] Yeah. So you can see that I've got this table. This table has got four columns.
[03:43:34] into this table. Now guys if you have missed any of the class previous class I'm afraid and I'm really sorry for the same that I'm not able to repeat the concepts because we have got limited classes. These are not unlimited
[03:43:47] classes. So every class has an agenda associated to it. So please make sure that you go through the recording. So you'll find recording in the LMS portal.
[03:44:03] 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.
[03:44:18] 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
[03:44:32] 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.
[03:44:53] 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. see it got executed. So this is fine. But if let's say that I've got some data
[03:45:08] of some of the students. So I've got a data of a student with the name let's 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
[03:45:24] listen to me. It's very simple. Now if I run it, it is throwing error. So you get run it, it is throwing error. So you get that that if you have the full data, if you have the full data, when I say full data, I mean to say that you've got the
[03:45:39] data for all the columns of your table. So if you've got the full data then it works perfectly fine but if your data is not full right now I don't have full data right then I have to explicitly define the columns that I'm going to
[03:45:56] define the columns that I'm going to use. Yeah for example id, name, age. use. Yeah for example id, name, age. So now I have to explicitly tell my SQL that five should go in id name should go in mark should go in name and so on and
[03:46:11] 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
[03:46:23] omit the column names from here. But if your data is not full, yeah, if you have partial data, incomplete data, then you need to mention the column names. That's how the syntax goes. Now, you don't need to insert so many records that I did
[03:46:40] that because I wanted to show you, I wanted to explain you. I'm just sharing this code with you all. So, here's a code for creating the So, here's a code for creating the table.
[03:46:59] for inserting the data into the table. Yes. So you can copy paste this code. Avoid typing each and everything in the class because if you type each and need to understand my predicament also over here that if I give you time for
[03:47:15] typing each and everything in the class then I may not able to cover all the concepts and I want to uh cover maximum things. I want to uh g give my maximum knowledge to you all share my max the maximum knowledge that I have with you
[03:47:29] maximum knowledge that I have with you all name kunal in this table I've just got four columns as you can see
[03:47:53] insert What subject but subject is not there right? over here? Kunal, is that so? So, Kunal, we are going to look into that also that
[03:48:08] 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?
[03:48:23] how you can play around with it? Done everyone? So I told you that in case of when you create the table you can also add the constraints to the table. So what are constraints? So constraints are
[03:48:39] uh think of it they are nothing but the rules that you apply on the column. Yeah they are nothing but the rules that you apply on the column. So h we'll learn apply on the column. So h we'll learn about few of the constraints
[03:48:53] and then we are going to write the code around these constraints. So the first or let's do one thing we'll go one by one we'll write the code for one by one rather than going through the whole thing we'll go one by one. So the
[03:49:07] 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 the column. Now if I say that let's say I have a I have a column id column and I
[03:49:21] want to apply some rules. So we learn about auto atomicity right we learn about not auto atomicity sorry we learn about asset. So if you remember we learn learn about consistency. So means that you are basically applying the rules to
[03:49:36] your database. So here also we are doing the same thing. We have got multiple the same thing. We have got multiple columns in our u table. And we simply want to apply some rules to those column. So let's say I've got the ID
[03:49:52] column. So let's say I've got the ID column and I want that whenever you're inserting any of the students record the ID shouldn't be null. You're getting the ID shouldn't be null. You're getting the ID cannot be null. As simple as that. So
[03:50:05] ID cannot be null. As simple as that. So I can use not null constraint for that. So you can see what it does. It disallows null value. So it means that the value should be provided. Yes, it means that you're making this column
[03:50:18] required. As simple as that. Yeah. If you enter any of the row, this column should have value. Okay. So we'll do one thing. We'll
[03:50:31] just give me a second. Yeah. So we are going to create a table
[03:50:50] everyone. So we we will use the same database. I think Nancy please can you uh can you please stop doing this?
[03:51:15] create a table. Now we are going to create a lot of tables
[03:51:27] my students. I'm just naming it anything that's fine. Always remember that what we are trying to learn. Yeah. Now this table may have multiple columns. So one of the column is ID and
[03:51:42] the second column is let's say name. Okay, I'm not going to make it a very Okay, I'm not going to make it a very big table. columns as not not null. How I can make that column as not null? So all I have
[03:51:58] that column as not null? So all I have to do is I have to write not null in the front of that column. So it means that in order to add the data into this table I have to make sure that the ID is not null. If I want I can
[03:52:14] do it for name also. Let me do it for name also. Doesn't matter right? So let me do it for name also. So now it means that in order to insert the data both the column should have some value. Let me create this table. The table has been
[03:52:30] created and I'll just quickly say insert into
[03:52:52] values. Okay. Now if you see I want everyone to see my screen. If I give one, comma any name for example, let it be. I am just giving any name for that matter. Doesn't uh matter what name I'm giving. Yeah. So
[03:53:06] if I u 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.
[03:53:21] 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
[03:53:33] data. But let's say that I I am skipping maybe a. Yeah, I'm not giving the student name. Yeah, I'm skipping a or let me give null in A. So let's see if it works or not. You can see it is not working. So it says that column name
[03:53:50] 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
[03:54:07] I have applied a constraint to this column both the columns that these columns cannot have null value. So this is what this is a not null constraint where we make sure that the column doesn't allow null values. So I'll wait
[03:54:23] for a minute for everyone to quickly try this. Again I do not want you guys to write the whole code. Maybe you can take it from the chat and you can modify the existing code what you have otherwise it will take a lot of time.
[03:54:52] Done. Now we'll learn about the next constraint that we have that is unique. Now you tell me what do you understand by unique?
[03:55:07] distinct values? Yes. So in very simple words if I have a column so I will not allow I will not allow duplicate values in those columns. So
[03:55:20] 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 those who belongs who are from India maybe uh p card or passport number
[03:55:35] everybody would understand. Other number how about email? Yeah email should be unique. Phone number should be unique. So you want to make sure that every record that is being entered has a
[03:55:48] record that is being entered has a unique email or unique phone number.
[03:56:02] for this we have got unique constraint as you can see. So here we have created a table user. It has just got one column. It's okay. we are we are learning the concepts so we don't need to create big tables with a
[03:56:15] lot of columns so I'll just quickly run this code sorry just give me a second yeah I'll run this code you can see that the table has been created now if I
[03:56:28] 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 statement So if I again insert it is giving error.
[03:56:44] Why the error? Because it's duplicate entry. But if I change the value of it then definitely it will allow me to execute. Yeah. So unique is a very important constraint that you're going to see
[03:56:58] 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 the code after the class.
[03:57:14] Right now we are copy pasting so that we learn maximum concepts but yes after the class you have to make sure that you do a lot of practice by typing each and every line of code. I'll wait for a maybe a minute.
[03:57:42] constraint that we have that is primary key. So let me open the notepad because I would like to uh write something around it or maybe I can write over here around it or maybe I can write over here itself. It's fine. So
[03:58:01] So when you write anything in the comments, the interpreter is not going to read this. These lines are for us. These lines are not for interpreter. So anyways, we're going to learn about primary key. Now you already know about
[03:58:14] it. I we have already discussed it. So I'll quickly redis it. So primary key is a unique identifier of each row. For example, if I talk about human beings,
[03:58:26] so I think our genetics, our DNA, these are the unique identifiers. Yeah. that will or our fingerprints these are what the unique identifier so for every row the unique identifier so for every row also if you want maybe some column to
[03:58:39] uniquely identify that row so we can declare that column as primary key so declare that column as primary key so I'll also write it so it's uniquely
[03:58:53] identifies each row combination of so it's combines
[03:59:24] a primary column primary key column it means see that's what it means that it is uniquely identifying it right uniquely identifying the row of data. So definitely every row that that column will take
[03:59:40] like every piece of data that column will take will be unique also it cannot be null. So it doesn't take null as simple as that. It's a primary key. It doesn't take nulls. So for example, we are
[03:59:53] storing the data of humans. Yeah. Of humans. Let's assume, right? We are humans. Let's assume, right? We are storing the data of human humans. Now storing the data of human humans. Now you know that names can be common then
[04:00:06] Most of the things can be common. But let's say uh thumbrint. So thumbrint is not common. So this we can make this as a primary key. So this will be unique for each and every human make. Yeah. And we we also
[04:00:22] 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. that's what primary key says. Okay. There's one more thing
[04:00:37] 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
[04:00:52] 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.
[04:01:09] It can have the combination of primary key, composite primary key that we'll talk about. But it can have only one primary key. So those who may ask that composite primary key, we'll come to that later.
[04:01:23] But it can have only one primary key. For now, you understand this part that it can have only one primary key. So I'll just quickly take the code. I'll just quickly take the code. Yeah.
[04:01:41] this table employee it has got two columns employee ID name. Now employee ID is a primary key. I'm declaring it as a primary key. Let me create the table. Now I am inserting the
[04:01:57] values in it. Yep. So you can see that this is a first value one and Ravi. So employee ID this one is what? ID. Yeah, employee ID. So anyways, so this will
[04:02:11] 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 key. And primary key makes sure that
[04:02:26] key. And primary key makes sure that each and every thing is unique. Also if I try to insert null in a primary key again it will give error. [clears throat] So that's what I wrote
[04:02:41] that it has to be unique and it cannot take null values. It cannot take null values. Now let me do one thing. I'll just make a small change over here. I'm just changing the name of the table and I just want to show you that if we can
[04:02:56] 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.
[04:03:09] Always read the error. I'm not sure if my uh screen is visible to you or not but yes it says multiple key defined. So it simply means that your primary key
[04:03:22] can only occur once in a table. You cannot have more than one primary key in the table. So let me again yeah uh make it to the previous code. Change it back back to the previous code. I think we are good. So I'll share
[04:03:38] this code with you. Before you run it, maybe let me also tell you one thing. So what is the difference between unique and primary key? See first of all I can have multiple unique columns. So I can have
[04:03:53] multiple unique columns. This is very much possible. I can have 10 unique columns. I can have more than one unique columns in one table. Primary key says that oh I can only exist once. Yeah I cannot you can once you have created one
[04:04:07] once one column is given the primary key constraint you cannot have another column with the same constraint. You saw that you have already seen it. But unique you can make as many columns unique as you want. Also if you have a
[04:04:20] unique as you want. Also if you have a unique column it can like one row can take null. So it's unique right? So one row I can give null. I have to make sure the next row I cannot give null because null will
[04:04:36] not be unique anymore. You got that? Let's say I've got this employee table. So for the first row I can say null and maybe I can give some uh maybe I can give some uh maybe some value.
[04:04:53] unique. Both both the rows are having the same value null. So it can take one row at least null. Primary key says no. If there's a null I can't take it. It's
[04:05:05] 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.
[04:05:19] composite key when you have a composite primary key, the primary key is one only. It's a combination of the primary key. But the primary key is one, right? It's a combination of the column that makes the primary key. So we we are
[04:05:33] going to look into composite also. Not right now. >> Make sure that you also write down these notes the these points. See, I'm going to give you these notes, but I'll give you these notes at the end of the class,
[04:05:49] not right now on on a last class or second last class. on on a last class or second last class. So, you'll have everything handy. So, you'll have everything handy. I do not want to give you right away.
[04:06:01] I do not want to give you right away. So, I'll wait for a minute.
[04:06:25] Fine. So now we'll learn about foreign key.
[04:06:38] foreign key what foreign key is. So just tell me what those who are able to recall what foreign key is. We've already discussed this point. Today we are going to look into the practical part of it.
[04:07:06] Yes, primary key from another table connect two entities. Very good. Right, all of you are right. So, a quick recap on what foreign key was.
[04:07:40] and this table has got maybe some columns. ID let's say passport number name
[04:07:55] name and anything else let's say uh gender and anything else let's say uh gender now passport number is a primary key now I've got another table this another table is storing the
[04:08:10] this another table is storing the passport number. numbers that we have. And for these different passport numbers, we also have maybe this is not a very good example. I'm so sorry. Let
[04:08:25] a very good example. I'm so sorry. Let me uh give you some other example. other example. So, I'll give you the example of orders only. I think that is
[04:08:40] a best fit. Now let's say that we have got a table customer. got a table customer. In this we have got the details of the In this we have got the details of the customer like ID, name,
[04:08:55] customer like ID, name, phone number etc. So ID is what? This is a primary key. This is a primary key for this table. It uniquely identify key for this table. It uniquely identify a customer. Okay. Moving ahead, we have
[04:09:10] a customer. Okay. Moving ahead, we have got the orders table. So it has also got multiple columns. It has got order ID and let's say the date
[04:09:24] has got order ID and let's say the date of the order. Uh maybe something else the product and then it has got the customer ID. Yeah. Who made the order. So this customer ID
[04:09:43] So it means that over here I have got some data. Let's say I've got the data some data. Let's say I've got the data like I've got a customer 1 2 with name A and B. So this table now understand this thing. This table will have values um
[04:09:59] [clears throat] it have values from this ID column. So it can have let's say I've got maybe one another value. So it can have 1 2 3 only. It cannot have any other customer ID. It cannot have four as a
[04:10:13] 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
[04:10:25] key of the another table. So ID customer ID is a primary key of this table and we are using this as a foreign key in the orders table. So what is the significance of the foreign key? Foreign key make sure that whatever values that
[04:10:40] getting whatever values you're filling in over here should come from this column. Yeah, it should always come from this column. So if I try to fill four over here, it will give error. It says oh
[04:10:54] four we don't have any customer with the ID four but if I give 1 2 three values over here again and again orders made by one orders made by uh customer ID 2 it
[04:11:06] will happily take it but as soon as I give let's say five it will give error no I five is not here it says five is not here so foreign key think of foreign key as a lo it's very loyal to the primary key it says that I will take
[04:11:21] only those values that exist in the primary key of this table. Yeah. Of this primary key. This is what this is the primary column of this table. I will not take any other value. As simple as that. So it's super super loyal to the primary
[04:11:36] key and that's why we call it as a um foreign key. So uh this is a significance of having the foreign key that it only takes the values
[04:11:48] 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 understand it's very easy.
[04:12:01] understand it's very easy. So here, okay, here I've got two tables. So I'll just quickly create these two tables.
[04:12:13] So you can see department and staff I've got these two tables. Department has got two columns and staff has got three columns. Now if you see over here which is a primary key in the department column quickly now I'm asking you such
[04:12:28] column quickly now I'm asking you such simple questions. Yeah. So Sia your question will be answered.
[04:12:43] Yeah. And if you see if you see here we are defining a foreign key. Yep. So department ID and here we are saying the department ID and this is how we do it. This is the syntax of it. So we are saying that we have got a foreign key.
[04:12:57] This is a foreign key. This is a foreign key. And how it is related to this table? We are also telling that yeah this is a foreign key and it is related to so references name of the table and the primary key of
[04:13:14] the table. You got that? So basically we have declared a normal column and then we are telling that this column is a foreign key. Yeah. Which this is a foreign key column.
[04:13:28] there should be some primary key in some table. So we have to give that detail also that which is a table and which is the primary key. So how do we do that? We use references. Then we give the name of the table. This is what this is the
[04:13:43] name of the table and this is the name of the primary key. of the primary key. So it means it means that this foreign key is for this table and in this this column. Now
[04:14:00] 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
[04:14:15] 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
[04:14:28] one department and this department name is [clears throat] 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.
[04:14:45] So staff you know like for different departments we have got staffing. So departments we have got staffing. So I've got staff ID 1 and this staff goes I've got staff ID 1 and this staff goes in the IT department. Yeah, department
[04:14:58] ID 10. So this will very well work. Yeah, because 10 exist over here. You get getting 10 exist in the department table. So this value exist in the
[04:15:12] department table. This value exists in the department table in this column. But the department table in this column. But if I give 20, this will not work. Why this will not work? Because 20 doesn't exist. Yeah, here 20 doesn't exist. If I
[04:15:28] 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.
[04:15:53] Yeah. So you got that that staff will make sure that department ID only have those values. Sorry foreign key make sure the department ID this is a foreign key. it
[04:16:08] can only contain those values which are the part of the primary key of the department table. So that's how it works. Okay.
[04:16:24] so Aperna is saying that in the department table if we give null that's what she's saying. So will it work or not? you the output. What do you think? Will it work or not? Department I'm giving
[04:16:40] it work or not? Department I'm giving null. This is what this is a primary key. What did I tell you? Primary key cannot be
[04:16:52] null. So this will not work. You have to give some value over here. Fine. So I'll just quickly give you the whole code.
[04:17:10] and let me know if you have any any issues, any questions.
[04:17:33] told you? I will give you these nodes at the Yeah. So in the end of the class, not right now otherwise you you're going to copy paste. So or I we'll do one thing I'll give you
[04:17:47] over the next weekend on Sunday. Is that okay?
[04:18:05] Sheila, I'm giving you all the syntax on the chat. just see error, I'm not able to help you.
[04:18:37] give you my PPT. In PPD also, I've got these syntaxes and all. But if you insist, I can give you the notes right away also. So that's your choice. Uh try not to use notes because if I give you the notes, I'm sure while practicing
[04:18:51] you'll just do the copy paste thing and I surely do not want that. If you assist, I do not mind giving you with the notes right away.
[04:19:08] time, you can just copy paste the code and try running play around with it. So, it I think you're getting some issues. Just give me a second.
[04:19:25] error with this one. Can you also share your code with me? What code you're your code with me? What code you're running?
[04:19:44] different line of code and this is a different line of code. Make sure that you're adding semicolon between your different different codes that you have got. You're segregating the different code of lines. Otherwise, what you do if
[04:19:57] you're running it like this, then it will definitely throw error.
[04:20:10] to clear off your whole screen and then run the queries that I'm giving you. with the that's exactly should be the behavior of
[04:20:23] that's exactly should be the behavior of the foreign key. foreign key is. So what you're getting is absolutely right.
[04:20:44] that it will give error. So that's that's a right behavior. is check. So check as a name says let me see if I've got it on my PPT. It will
[04:21:00] restrict the values. Yeah. So it will restrict the values. For example, let's restrict the values. For example, let's say here we have got the example also. So let's say that I make a check on my column where I say that the age should
[04:21:17] be greater than 18. So it means that whenever I'm inserting any data, I have to make sure that this column has a value maybe greater than equal to 18. So if I give anything lesser than 18, it will throw error.
[04:21:38] check constraint. a very simple constraint to work with. So [clears throat] let me quickly take the whole thing. let me quickly take the whole thing. Yeah,
[04:21:54] So you can see that again I've got the student table. I think it will throw table. So I'll just quickly make a change to the name of this table and already there. It will throw error that this table already exist. Fine. So you
[04:22:10] can see that the table has been created. Now the I've given a check over here that the age should always be greater than equal to 18. So if I insert the
[04:22:22] data which is greater than 18, it will happily take it as you can see. But if I try to insert any data which is less than 18. Yeah, if it is less than 18, it will throw error. So if you're seeing the
[04:22:36] if if you're getting the error this is how the behavior should be. Yeah. So you should definitely get the error. This is how we expecting it to work. So it shouldn't allow anything lesser than 18. As simple as that. If I give 18 then it
[04:22:52] will allow. See now it will allow. It will work. It will not throw any error. But as soon as I give any number which is lesser than 18 it will not work. So I want everyone to quickly run this code. It's a very simple one.
[04:23:14] 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?
[04:23:30] So when you are installing MySQL not 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. there's some setup that you need to do. [clears throat]
[04:23:45] also just that you have to follow some steps. for everyone to quickly try this constraint check constraint.
[04:24:07] So pushkar I would suggest you to uninstall my SQL and then reinstall it and make sure that you go through each and every step.
[04:24:32] is default. So till now every column that we are we have created we are we were inserting the data in it. Now let's say that if we do not get any data in it. Let's say we are not
[04:24:47] 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. a table student table. Yeah. Now student table. Now this is uh
[04:24:59] student table of some school which is in India. So here we have got a country column. So if we are not giving the value explicitly for the country it will automatically take
[04:25:15] India as simple as that. So basically we are giving a default value to country column. So default value means that if I'm explicitly providing the value while inserting the data if I provide USA then
[04:25:28] it will take USA. I provide India it will anyways take India. But if I do not provide anything then by default it will take India. Again, if you have not understood, that's okay. Rest assured, once we write the code around it, you'll
[04:25:42] able to understand. So, let me take the code.
[04:25:54] screen. You can see that I've created the orders table. It has got one column that is status. This is the data type of it. And what is the default value of this column guys? Now I'm asking you such simple questions so that you remain
[04:26:09] 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
[04:26:24] 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
[04:26:39] values I'm literally not giving anything. So if I run this see it got anything. So if I run this see it got successfully run. Let me quickly query successfully run. Let me quickly query this table.
[04:26:59] You can see that it has got one one piece of sorry one row and this one row because I executed it once it has taken status as pending. If you want you can give a value closed. So it will happily
[04:27:12] will not take. Yeah, this ran successfully. If I run this you can see first one it took pending because if you're not giving anything in very simple words you're not giving anything it will take the default value but if
[04:27:27] you give anything then it will take that value am I clear it's a very simple one right so again I'm copy pasting the code I'll wait for
[04:27:41] I'm copy pasting the code I'll wait for a few seconds.
[04:27:55] again sansa see it will again insert some value and you will see three rows now definitely because you're inserting the values
[04:28:10] pending is getting replaced. So default is that either you give some value and if you're not giving any value then default it will take pending
[04:28:30] value but if I do not give any value then it will take pending.
[04:28:44] be equal to the value then see ware size is all about that what will the size of the data for example if I give really big size let's see [clears throat] if we get any error
[04:28:58] you can see that I'm giving the error data is too long for this column because I've given 20 over here it has nothing to do with default constraint it has this simply means that this column can accommodate 20 characters at max.
[04:29:14] I hope I've answered your question Rajpal. So at max it can be 20. Yeah but you can give definitely lesser than that.
[04:29:30] So I'll just wait for some time. I think I've already given you the code. If not anyways I'll again give you the code and I'll wait for a minute.
[04:29:51] over here it will take that. If you do not give any value then only it will go with pending. Default can be anything. It can be integer also any data type.
[04:30:08] Sanscar Rajpal it will take any value. It's just that if you do not provide any value then it's like if I give you a funny example like I think most of you are from India so you'll able to understand. So if
[04:30:23] there's no vegetable we tend to cook potatoes right? So that's a default vegetable. If there's no nothing else, potatoes is our go-to meal. At least in that that's a funny example, but yeah, that's how it is.
[04:30:44] It's just that here you'll have ID and here you'll have int. That's all. And then default you'll give a number over here.
[04:31:03] constraint that we have auto increment. So when I say auto increment, what comes So when I say auto increment, what comes to your mind?
[04:31:16] 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
[04:31:29] ID. Usually what you do is you give your name, address, phone number, email id and you're done and dusted. So let's see Amazon wants to give you So let's see Amazon wants to give you some ID. Yeah, some autogenerated ID. So
[04:31:43] we have got this auto increment. This is a wonderful constraint in order to um satisfy or meet this requirement. So auto increment as a name says that if I
[04:31:57] have got a column let's say the name of the column is id. If I make it auto the column is id. If I make it auto increment it means that automatically it will like the first record let's say take the value one. So the second record
[04:32:12] will take the value. Let's say I say that it should increment by one. So that is what I've given as a rule. So the second will take two. The third record will automatically take three. So the this column will automatically
[04:32:28] u generate the value for itself without user explicitly giving. Yep. And it will increment increment the value every single time. So again we'll understand it with the help of the example so that
[04:32:42] it with the help of the example so that you have better idea.
[04:33:02] a great example why because here we are creating the table and see one column you're making it primary key also and you're also auto auto incrementing it. So that is very much possible that one column you're giving two rules in it.
[04:33:17] Yeah it happens right? So this one column has got two rules. First of all column has got two rules. First of all it's a primary key also it's auto it's a primary key also it's auto increment. So uh we need what we are
[04:33:29] seeing is right. So auto increment is that it will automatically increase the 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
[04:33:46] the value in the product name column. I'm not inserting any value in the product ID column. I'm not inserting. No, because this column is self-sufficient to autogenerate the value for itself. So I'll quickly run
[04:34:02] value for itself. So I'll quickly run this and let me show you the result. Sorry. Yeah. So you can see automatically it is taking one and two. Yeah. Now if I insert more data let's say if I insert
[04:34:17] insert more data let's say if I insert um maybe keyboard it will take automatically it will take three. Let me show you. You can see so
[04:34:29] 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.
[04:34:51] any key you can. So what you can do is screen. Yeah, then I'll give you time again. So I'm just making the changes uh
[04:35:03] to the name of the table because this table already exist and if I recreate the table with the same name, it will th error. So definitely I can remove this. error. So definitely I can remove this. Now product ID is auto incremented
[04:35:17] 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.
[04:35:33] auto increment equals to five. Let me run this.
[04:35:48] It must be defined as a key. Just give me a second. primary key. I'm so sorry for that. So, it has to be primary key. I think SQL
[04:36:03] server we c we we can have uh a table auto increment column without having the primary key. So I got confused because different databases different rules but yes it has to be primary key. Now you can see that I'm saying that auto
[04:36:17] increment by five. Let me in let me quickly insert these products. And now quickly insert these products. And now yeah
[04:36:30] to insert the product. So we also changing the name of the table.
[04:36:45] 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. initial value. So it is starting from five.
[04:37:09] >> 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
[04:37:22] increment. So there's uh there's a property that we can define when we property that we can define when we alter the table.
[04:37:38] row, the next increment will be the next one. It will not be the previous one. one. It will not be the previous one. Yeah, it will always take the next one.
[04:37:52] that uh we have a property. So I would u maybe I would like to talk about that property when we learn about how we can alter the tables not right now. So this was a very simple property. So I just told you right away.
[04:38:23] So this is a very good question. So u as I told you uh right now it was asking for the primary key. I need to check because in SQL server you can have check because in SQL server you can have an auto increment column without having
[04:38:35] it to be the primary key. But here in my SQL when I was trying to create it with it was asking for the it to be a primary key.
[04:38:50] I'll just check it if we can auto increment a column without having it to be primary key. In fact in MySQL auto increment feature wasn't there till some time back. We had it in SQL Server databases but
[04:39:04] We had it in SQL Server databases but not in MySQL.
[04:39:17] composite primary key. So what is the meaning of composite?
[04:39:31] have said that there might be something u off with your with your syntax.
[04:39:44] Okay, I'm sharing the whole thing with you all composite primary key. I do not want your questions to be unanswered and
[04:39:56] you're still trying and you know u hanging around this part. I do not want that. So we'll wait for a minute and then we'll move to the composite.
[04:40:23] 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.
[04:40:46] 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
[04:41:00] 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.
[04:41:13] Yeah. Now here I'm having the name of the country and then I've got more details about the country let's say the latitude longitude all of these things
[04:41:25] 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 latitude longitude can also be a primary key because every
[04:41:39] country will have distinct latitude longitude let's talk about something else let's um number of states. Yeah, something um number of states. Yeah, something else or number of cities.
[04:41:51] So some some details about the country. Now we have to decide on the primary key. Now I may want make see number of cities and number of state cannot be the primary key for sure. So what I can do
[04:42:06] is now country code can be the primary key of course but let's say that uh it's a hypothetical thing that countries they can have sometimes similar code also yeah they can have the same code also so
[04:42:21] yeah they can have the same code also so what I can do is now name also I think there is one country I don't know sharing the name something like that now countries may share some sometimes names also they can have let's let's assume
[04:42:33] that that's hypothetical I know. So what I can do is now I want to define a I can do is now I want to define a primary key.
[04:42:48] the combination of two column as a primary key. So I can say my primary key is name as well as country code. So combination of th these two are my primary key composite primary key.
[04:43:01] you're getting the combination of two columns or it can be more than two columns are what u is is a primary key.
[04:43:16] are what u is 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
[04:43:37] 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
[04:43:50] 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
[04:44:06] 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
[04:44:18] actually repeated sometimes. So let's say I make an order. I'm just giving you one context of it. And in that order I am purchasing three products. You getting? So I'll have the order ID repeated. So 1 2 3 1 2 3 1 2 3 product 1
[04:44:35] 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
[04:44:51] primary key. So what does that mean? It means now hear me out. 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.
[04:45:07] So the combination of these three should be unique. So if you see this is I'll just take the second one. I'll take the third one. Now if you see the combination of these three is unique. It is always giving me a unique
[04:45:22] value. Yeah. If I go with one more, let me give you one more example. Let's say the order ID is 1 2 4 product is one one and the the quantity is different. So the combination of these
[04:45:43] combination is unique. So that's what composite primary key does. It's same as that of the primary key. No difference. It's just that the primary key is made up of more than one column. Yeah, it is made up of more than one column.
[04:46:02] 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 Because here the primary key is made up of two columns.
[04:46:14] of two columns. Now I want everyone to see my U screen. So I'm inserting the data in it. One 1.1. So one and 1 together will make the primary key. It will very well work. Now
[04:46:28] again see one is repeated but course ID is different. Yeah. So one one not two again it will work for me because the combination of two is unique. Always remember that the combination of is unique. Here also the combination is
[04:46:43] unique. See if I run it, it will run. But let me do one thing. Let me again run insert and let's say the product or the student
[04:46:56] ID is true and the course ID is also 1.1 you're getting. So the combination is no more unique. It has already been used. It has already been inserted in the table. So if I run this, it will throw error that it's a duplicate key because
[04:47:12] this combination is not unique anymore. Yeah, it's not unique anymore. So composite key is exactly same as that of the that of the primary key. There's no difference. The only thing is composite key says that I am again a primary key
[04:47:29] 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.
[04:47:44] Let's see. Uh I'm not sure if you have got the students table or not. So let me uh quickly drop the table. Drop table and then the name of the table. So the name of the table is student. Yeah, if it is there. Yeah. So I think this table
[04:48:01] is not there. As you can see it's giving error. So if you guys already have this table please make sure that you drop it. So this is one of the way to drop it. Other way is that you can go over here and you can right click on the table and
[04:48:15] and you can right click on the table and you can say drop table. So we are going to recreate in case we so we have created students table. There's s added to it. So now we are going to create the student table. So if
[04:48:29] you already have the student table, please go ahead and drop it and once it is done, I want all of you to recreate the table
[04:48:41] and insert this data into the table. So we have got the table. Just give me a second. I think it did not get executed. Yes. So I've got this student table and I've got this much of data almost six rows of data in the
[04:48:56] data almost six rows of data in the student table. So I'll wait for a minute for everyone to have their student table ready.
[04:49:12] to see over here. I think you got it right over here. Fine. Now I'm just opening another SQL file.
[04:49:24] me. Hm. The table the table structure the data that I have inserted into the table. Yeah. So as this is something
[04:49:38] that we have run so many times. So I say select star from student. So this will return all the columns and all the rows that we have got in the table. So you can see all the five columns. We have got five columns and we have got six
[04:49:55] rows of data. So this is returning everything everything. Now if I want to let's say only return some selected columns. So what I can do is I can say columns. So what I can do is I can say let's say select name comma u marks from
[04:50:10] student. So I can definitely do it. I can give the name of the columns that I can give the name of the columns that I want to be the part of my input output. So if I run this you can see now it is returning only two
[04:50:23] columns. So I want everyone to try try this out. for the select statements. It's a small it's a short code so you can quickly
[04:50:36] 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
[04:50:50] thing. Let me also share the code with you. Now hoping that you have already typed it. You can just type one and two columns and definitely I want you guys to play around with a lot of code a lot of uh SQL queries after the class. Okay.
[04:51:05] So here you can see that I've got students from different different cities. So let's say I want to know that what are the distinct cities that I've got. So I just want to know that I've got the enrollments from which all
[04:51:18] got the enrollments from which all cities. So I can make use of distinct keyword and then I can say I want distinct city right. So this will give me all the distinct cities that I've got in my
[04:51:31] data. So I want everyone to try try this out So I want everyone to try try this out distinct.
[04:51:51] 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.
[04:52:06] applying some rules. You're applying some maths. As simple as that. So it's like you're filtering your data while reading the data. Fine. Now, as I told you that when you're reading the data, of course, you
[04:52:21] can read the data directly like this. Of course, you can do that. Yeah, we have done it also. But let's say while reading the data, now I do not have any let me again. Okay, I do not have any percentage column over here. But let's
[04:52:37] say that fine, I've got the marks, but I also want to know the percentage of the students. Yep. So I am not going to make any changes to the original data rather while reading the data I'm going to apply some maths. So what I can do is
[04:52:51] over here I can say maybe okay I'll just put like this and I'll say marks
[04:53:05] 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
[04:53:19] 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
[04:53:34] 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 so see this column is there now over
[04:53:48] 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
[04:54:02] maths. So you can try try this out.
[04:54:26] the name of the new column and I am literally not liking this name. So you can give an alias to your column. So I'll just give the alias percentage.
[04:54:56] 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
[04:55:09] let's say as full name. So if I run this see this so the So if I run this see this so the original name of the column is name but while reading the data from the table I've just given an alias to this column.
[04:55:22] So I can do it but whatever I'm doing over here in the select statement is only for the output. It is not making any changes to the original or the underlying table. Yes, we can definitely do it to do to two decimal point Rajput.
[04:55:37] are going to learn about those functions. So not right now but yes we have got a round function. So you can uh give uh you can make it only for two decimal points.
[04:55:55] definitely we are going to cover all those functions you can see. Yep. But it's not something that I would like to maybe talk about more. right now we'll go step by step but I
[04:56:09] right now we'll go step by step but I hope that you have got the answer. Yes. Yes. It's all about the formula that you're giving over here.
[04:56:26] all about the formula that you're giving over here.
[04:56:45] returning all the rows. Yeah, it is returning all the six rows that we have. Now what I want is I want to filter the data. Right now we just learned about that how you can return all the columns. How you can return
[04:57:00] limited columns. How you can do little bit with the columns. How you can do little with maths with little bit functioning on the columns. This we are going to cover more. But now what I want to do is I want to filter the data that
[04:57:13] is returned as a part of the output. I want to filter the data. So how I can filter the data? So I'm just making the changes to the same.
[04:57:30] repeat 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
[04:57:44] there's a requirement and the requirement is I want to see data of only those students that are from city Pune. Yes, students
[04:57:56] that are from city Pune. Yes, students that belongs to Pune city. So I can use wear clause. Let me see if I've got anything on the PPD for the wear clause. anything on the PPD for the wear clause. Yeah. So here
[04:58:09] Yeah. So here you've got syntax till here and then you use the where clause. severe and then you give condition over here.
[04:58:22] 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
[04:58:38] yes so this will this will help us to filter
[04:58:52] actually gives a boolean answer but I'll I'll talk about that later. Right now let's first look into some of the example. So here I want the students who belongs to the city
[04:59:07] Pune. So see this will help me to filter the data that is returned by my query. So where clause is very important. Again I'll give you one more example then I'll give you time to uh practice
[04:59:22] it. So let's say I want all the students who who have got distinction marks. So marks greater than equal to 75.
[04:59:41] So this will return me all the students with greater than equals to 75. I want everyone to try this out both the both the queries.
[05:00:02] different operators. For example, for example, let's say I can add the 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
[05:00:15] see over here I'm just adding arithmetic operator that is plus. So I can say that where marks + 10 is greater than 75%. So I can add the different different operator. I can add the comparison operator.
[05:00:30] it. Yeah. The previous query that we wrote. H this is what comparison operator where you're comparing you have marks greater than equal to 75. So you're comparing the two values
[05:00:44] right? That's what comparison less than greater than greater than equal to less than equal to equals to. So these are what these are compar comparison In the same way I'll just give you some examples of the different different
[05:00:58] examples of the different different operators then you can give it a try. 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
[05:01:14] 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
[05:01:26] 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
[05:01:40] 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
[05:01:57] 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 AIT
[05:02:14] 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
[05:02:30] 75%. So whether they belong to Pune or not doesn't matter. So either like I so here students who belongs to Pune also marks greater than 75%. So if I run this you can see that I'm
[05:02:44] getting the students from Pune as well as students with marks greater than 75%. So this is what or in the same way we have got not also. Yeah. So let's say I'll give you the
[05:03:00] example for north 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 I'll say that okay I want all those students who do not belong to Pune city.
[05:03:15] So I'll just say where not city. So I want the students who are not from Pune. So here are the logical operators of all the three logical operators of all the three logical operators.
[05:03:32] would like to take two minutes of hold over here and I'm just sharing this 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
[05:03:45] see a student will belong to either Pune or Mumbai right? So you can say city 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.
[05:04:06] minutes. You can just copy paste this queries yet do not write the query. Play look into the more operators that we have got.
[05:04:48] going to talk about that as well. You can use logical operators also. City equals to Pune or city equals to Mumbai. But and there's one more way we can use range operator. So I'm going to talk about that. That's the next
[05:05:04] minute. Everyone just give it a try. It's a very simple thing. It's just that It's a very simple thing. It's just that you need practice.
[05:05:27] 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
[05:05:39] 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
[05:05:55] 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
[05:06:11] 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
[05:06:25] 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
[05:06:44] 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
[05:06:57] 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
[05:07:11] 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
[05:07:23] be. Yeah, I I let's say I got all the columns from student where city and I'm going to use in operator. Yeah,
[05:07:37] 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,
[05:07:54] 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 in is a set operator. range operator and in is a set operator. It's a set operator.
[05:08:07] 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
[05:08:21] like city equals to Pune rather I say all the cities that starts with P or all the cities that ends with E something like that. So I have got some pattern to like that. So I have got some pattern to match. So
[05:08:37] from student okay let's match the pattern for name let's match the pattern from name. So let's say that I want all the students where the name like name
[05:08:50] the students where the name like name like so anything that starts with a. So here percentage means hear me out percentage means any number of characters and any characters. So this will show me all the students where the
[05:09:04] name starts from a yeah name starts from a. So let's say I I'll just um maybe 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 just I just want T letter
[05:09:18] 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
[05:09:31] 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
[05:09:45] 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.
[05:09:59] 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
[05:10:17] only one character. Yeah only one. So here hyphen means only one character. It can be any character but only one character. Percentage means any number of character. Hear me out. Percentage means any number of character
[05:10:33] 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 Priya why P is only one character before R and then any number of
[05:10:46] 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
[05:11:01] using underscore not hyphen sorry underscore. So I repeat percentage simply means any number of characters and any character this means one
[05:11:15] character but any character yeah I can also give something like this let's say okay I'll give a okay anything before a and any two
[05:11:29] characters after a any two characters after a I'm not sure if it will so I after a I'm not sure if it will so I don't have any any such student. But if let's say I've got a student, I'm just giving any random name. Let's say
[05:11:43] just giving any random name. Let's say I've got the student sit t a rb. Yeah. Suppose I've got a student like this. So this will return this query will return this. So I've got two underscore. It means any two characters.
[05:11:58] But it has to be two in number not more than that. Percentage is like any number. Yeah. One underscore means one character. One character, two underscore. So this one and this one. So that's how it is working.
[05:12:13] that's how it is working. So am I clear with the pattern matching?
[05:12:25] simply matching the pattern using percentage and underscore. Am I clear?
[05:12:42] you time. So we have got the last operator null operator. So let's say you simply want to return the rows where maybe the column is not null. So the column is not null.
[05:12:59] column is not null. So I can simply say where name
[05:13:23] 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 [clears throat] name. So I don't have any such column. So that's why it is not returning anything.
[05:13:36] 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.
[05:13:48] Not null. So all the all the rows where the city 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
[05:14:00] Okay. So we are done with the operators in the V clause. I'll take a hold for 2 minutes. You can just copy paste and play around with it.
[05:14:34] 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.
[05:15:15] do with the primary key. Let's say that your name is a primary key. So it doesn't matter right? You are just going to filter with name. Name just going to filter with name. Name equals to something.
[05:15:29] primary key or non- primary key. You're simply filtering your data. That's what you're doing. If you've got composite primary key, how does that matter? Where doesn't care about primary key or composite primary key? It says that you
[05:15:43] want to filter the data and filter the data for you. So let's say you have got name and marks as a composite primary key. Now let's say you want to filter with these two. So you can just say and marks equals to
[05:15:57] some type. So you're getting it has nothing to do with the primary key or nothing to do with the primary key or composite primary key.
[05:16:09] You can use any column over here in order to filter the readout.
[05:16:30] we learn about one more clause that is limit. limit. As the name says that this clause can be As the name says that this clause can be used
[05:16:47] number of rows returned by It's a very simple one. Now let's say and you're going to use it a lot. Yeah.
[05:17:01] When you start working, let's say you just want to see the structure of your data. Yeah. So in let's say your table is having thousand rows. So you do not want all the thousand rows. You do not want to see only thous all the thousand
[05:17:15] rows. Rather you just want to see the top 20 rows in order to see the structure of the data. So I'll just delete this. So here what you can do is or let me put back because I maybe I'll show you some some some
[05:17:29] 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.
[05:17:44] will give me the two records. So you can give any number over here. Row number. [clears throat] No, no, in MySQL we cannot do that. I need to check. I've never used it in MySQL.
[05:18:02] >> [clears throat] >> rows.
[05:18:26] where clause in it. It's not that you cannot add v clause. So I can also say let's say we marks greater than marks greater than let's say 75 and then I can limit my
[05:18:40] 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
[05:19:11] 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.
[05:19:28] 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
[05:19:48] as a name says that order by so you're ordering something. So it is to sort the result. Yeah. Now you're looking into the result with select query. You're basically querying the data that your table is holding. So while u maybe when
[05:20:03] you're seeing the result you want the result to be sorted. Okay, you want the result to be sorted by some column. So sorting can be definitely ascending
[05:20:31] fine. So here I'll do the uh sorting of the So here I'll do the uh 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
[05:20:44] Now I want to see all the students but I want to sort my students as per their marks. So what I can do is I can say order by marks.
[05:21:03] both the queries. Yeah. So you can see this is the result of first query and this is the result of second query. Let me highlight it. Result of first query me highlight it. Result of first query and the result of second query.
[05:21:17] Fine. So over here if you see there's no sorting. Yeah you Amit got 75, Niha got 72, Rahul got 90. So the
[05:21:29] data is not sorted. But for the second query the data is sorted order by marks. So you can see that Surish has got minimum marks. Priya has got uh like you
[05:21:42] know from what do we call that from from the low she's second minimum and yes when and so forth Rahul has got the maximum marks so it is sorting the data maximum marks so it is sorting the data by marks but by default it is sorting in
[05:21:58] 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 DESC. So it means that I'm going to sort the data in the descending order. Now Rahul
[05:22:14] data in the descending order. Now Rahul uh is being seen first because Rahul has got the maximum marks. So descending means highest to lowest. Yeah, it starts with the highest and it moves to the lowest.
[05:22:26] Ascending means from the lowest to highest. So you can also sort in the descending order but then you have to explicitly give. I'll give you more examples and then I'll give you time. But let me do one thing. Let me give you
[05:22:43] time right right away. So you can just try this code sorting. >> And with that we have come to the end of
[05:22:55] this GI powered SQL course. In this course, we understand what databases are, why SQL is important, how to work with MySQL and MySQL workbench, how to create databases and tables, how to insert and retrieve data, how to use SQL
[05:23:09] commands, and how constraints, keys, relationships, and AI diagrams help us understand better business decisions. We also saw how geni tools like chat GPT and GitHub copilot can also support SQL learning by helping us understand
[05:23:23] queries, fix errors, generate examples and write SQL queries more efficiently. please feel free to ask them in the comment section below. Thank you guys comment section below. Thank you guys for watching this video.