AI Summary
This video is a comprehensive introductory tutorial on SQL and databases, aimed at business analysts. It covers database fundamentals, ACID properties, relational vs NoSQL, and hands-on SQL commands using MySQL Workbench.
Chapters
Introduces the course structure and the combination of SQL with generative AI for business analysis.
Databases provide organized storage, efficient access, scalability, and security, akin to a wardrobe or library.
Atomicity ensures transactions are all-or-nothing; Consistency maintains rules; Isolation prevents transaction interference; Durability ensures data persists.
OLTP (Online Transaction Processing) is used for day-to-day transactions, leveraging ACID properties.
Relational databases store data in tables with strict schemas; NoSQL offers flexibility for unstructured data.
SQL is a language to communicate with DBMS; MySQL, Oracle, PostgreSQL are examples of RDBMS.
Entities, attributes, and relationships form the basis of ER diagrams; primary and foreign keys establish connections.
SQL commands: CREATE DATABASE, CREATE TABLE, INSERT INTO, SELECT, WHERE, grouping, and constraints.
Introduction to data types: INT, VARCHAR, TEXT, BLOB, FLOAT, DATE, and constraints like NOT NULL, UNIQUE, PRIMARY KEY.
Advanced queries: WHERE, DISTINCT, ORDER BY, LIMIT, and logical order of execution.
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY and HAVING for grouped data filtering.
Single-row functions for numeric, string, and date manipulation; introduces window functions for advanced analytics.
Using AI tools like ChatGPT to generate practice code and answer questions, enhancing learning.
Mentioned in this Video
Tutorial Checklist
💡 Key Takeaways
SQL is a language, MySQL is a DBMS
Clarifies a common confusion, setting the foundation for understanding database tools.
01:06:12Order of execution is crucial
Understanding how queries execute helps optimize complex queries and troubleshoot efficiently.
04:28:56Hands-on SQL practice with AI assistance
Shows practical way to learn by combining practice with AI tools, accelerating skill acquisition.
02:12:53Relational databases are strict, NoSQL flexible
Guides technology choice based on data nature and requirements, a key decision for analysts.
01:07:12Group by with having for filtered aggregates
Essential for grouped analytics, often tested in interviews and real-world reporting.
04:38:18Full Transcript
[00:02] course, we will explore how business analysts can combine SQL skills with generative AI to analyze data, solve business problems and improve their productivity. You will begin by learning essential SQL concepts including
[00:15] databases, tables, queries, filtering, sorting and aggregating functions. We will then learn explore advanced SQL techniques such as joins, sub queries, functions. Along the way, you will understand how to use generative AI
[00:29] tools to create SQL queries, explain complex queries, identify errors and data cleaning techniques, business data hands-on demonstrations to help you apply SQL skills in the real world
[00:43] you will have knowledge to work with databases, leverage AI powered assistance and create meaningful business insights with confidence. Before we begin, here's a quick question for you. Which SQL clause is used to
[00:56] filter grouped results after applying aggregate functions? Is it A where, B order by, C having or is it D distinct? Before we continue, here's a quick update. Do you want to build advanced skills in SQL and AI powered data
[01:09] Simply Learn's professional certificate program in AI powered data analytics featuring live online classes, SQL and Python training along with AI powered dashboards, Power BI, cloud AI tools, intelligent agents and hands-on industry
[01:24] projects. This program helps you develop the skills needed to transform raw data into meaningful insights and support smarter business decisions using modern analytics technologies. >> So database is nothing but
[01:37] it is a structure which can store the data and definitely store the data digitally or electronically because see when I talk about the wardrobes, you have the wardrobes at your home.
[01:51] uh you you get to see the physical wardrobe, but here in the computer world everything is digital, right? So database is think it as a folder. So on your machines. So on your machines, why do you create a folder?
[02:06] 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 have a database, why it is
[02:20] uh always better to have a database. So if I ask you again, just tell me one thing, let's say that you've got a big wardrobe with a lot of clothes in it. Yeah.
[02:34] 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, uh keep all my jackets over here. I'm going to keep all my shirts over here and so
[02:48] on and so forth. So I get to organize my clothes because I'm having a wardrobe, right? Also I can easily access it. See, if I'm just dumping my clothes on the floor, if tomorrow I want a
[03:02] yep, it will be very difficult for me to find it, right? But if I'm using a wardrobe and definitely the wardrobe is organized, then I can easily find the cloth. Yeah,
[03:15] so it is very easy, it will be very easy for me to access. Uh also now that is not related to the clothes, but yeah,
[03:27] thing. Maybe I'll talk about that. So talking about the database, which is nothing but the storage of data, it has organized storage. So I just explain you with the help of wardrobe analogy, right? So you have
[03:41] everything uh with the help of wardrobe. All your clothes are organized. Yeah, here also you got database. So with the help of database, we are getting the organized storage.
[03:56] Then efficient access. So as I told you in case of wardrobe also, if your clothes are properly organized, it will be very easy for you to access
[04:09] your clothes. So in case of database also, access the data because right now see the data can be in
[04:21] you know in mega GB's 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.
[04:34] scalability. So what do you understand by scalability? Scalability is about increasing the data and data is definitely increasing day by Now if you talk about Amazon, think about the data
[04:49] that how fast it must be increasing. Every day there must be a lot of transaction being made. I'm talking about the Amazon website and if I specifically talk about only India Amazon website or only US Amazon
[05:01] 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
[05:13] system which is scalable. Scalable means that it should be able to handle. Yeah and it's definitely secure. It provides us the security also. So you
[05:28] may not want all your data to be accessible to everyone, right? So with the help of database we can also secure our data just like in your wardrobes you can always lock your wardrobes, right? You can lock and unlock your wardrobes.
[05:40] In the same way database data can also be locked and unlocked from different different users that you have. Okay there's one thing that I would like screen. You'll get to know that how your co-participants are. For
[05:56] example, I actively work with SQL. So we have got only two who are actively No experience with SQL. We have almost 53. So we are going to learn from very doing that. If we are starting from what is data. So that's one of the
[06:12] very basic thing. Okay, I have got some knowledge but don't use it regularly. So we have got 16 folks. So we have got a lot of beginners in this batch.
[06:24] So we'll make sure that we cover everything from scratch. But guys one thing if you're learning the language for the very first time then you also have to make sure that you practice after the class. So only class
[06:36] learning would not be sufficed because see if you don't practice after the you're going to forget most of the things what have been taught in the previous class. So again there's one analogy in order to
[06:50] understand what database is. So just like you've got a lot of books. Yep, so you we've got a lot of books. So the look books are stored where? Now this is the picture of what? Books are stored where?
[07:06] Think of books as the data. And where do we store books? Yes? Yeah, so this is what library. You must have seen that
[07:19] when you go to any of the library usually the books are example here you'll have all the science books, books, all the fictions.
[07:33] So in this way and then there might be some any good library you'll see such arrangements. So why do we have such arrangements? What do you think why
[07:46] what is the reason behind such arrangements? books easy retrieval yes easy accessibility.
[07:58] Yes, imagine that they are not storing the books in this way and then you'll end up maybe spending your whole day or two in order to find one single book. Maybe more than that depends on the size
[08:10] of the library. So database also works on the same concept. Guys, it's just that instead of books we are now storing the data and instead of library we are having the database. The database stores the data, library stores
[08:24] Now just like how the library is properly
[08:37] 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 say there is new edition.
[08:49] I've got a new edition. There is a new fiction book. So I know that I just have So it helps me to modify also properly, right? I I'll quickly able to modify, make the modifications.
[09:02] So it's not that I'm very random with it. But yeah, it's mainly for the quick access. properties of database. So this is very important interview
[09:16] question also. So we have got acid properties. We call it as acid. So we'll go one by one. So this slide may take little bit of time. So please be patient.
[09:30] make it interesting. Yeah, all of these properties, but you properties. These are the heart of the database. So when I say atomic atomicity, any idea what atomicity could be? So I do not
[09:44] want you guys to read the slides. I want you guys to answer the slides. I want you guys to answer with whatever comes to your
[09:56] Fine. So we'll talk about atomicity. So I'll give you an example and we'll understand it with the example.
[10:08] So before I talk about yeah atomicity, maybe let me tell you the use case of database. So just like database, we have we have got more entities in order to store the data. Now you have to hear me
[10:20] In order to store the data, we have got more entities. Database is not the only place where the data is stored. We have got a lot more other things. So you might have heard about apart from database,
[10:35] you might have heard about data warehouse. that. That's definitely
[10:47] But what I mean to say is that we have got multiple things in order to store the data. So why what is a use case of database? data. So why would I choose database rather
[11:02] than data warehouse or data lake? So we are going to look into the use to look into the use case of data warehouse or data lake. We are going to sorry, look into the use case of database.
[11:15] So any idea? Any use case of database you can think See everything is used for storing the data. So why database? So I'll tell you why database.
[11:31] you'll understand why. So we'll go step by step. So we'll go step by step. So if I talk about use case of database, so let's say that I access any of the bank website. And when I say website,
[11:44] I'm talking about my account. So I just want to see that my account? What are the transactions that I made in last seven days and so on and so forth. So to save this data, now see my current
[11:59] balance, then everything related to me, my profile, then my transactions, whatever you see. then my transactions, whatever you see. So everything goes into the databases.
[12:11] So most of the companies, they use databases for such things. So databases are used for day-to-day transaction. data yeah whenever there is a day-to-day
[12:27] transaction data it goes into the databases. it provides asset yeah it provides asset guarantees. I'm going to talk about what
[12:40] asset is but why day-to-day transaction data goes uh why they they why do we go with day-to-day transaction because because
[12:52] it provides a database provides with the asset guarantees. If I give you the other example if I talk about let's say Amazon. right you're making a payment you're ordering something making the payment.
[13:06] So mostly the company might be using the database in order to handle all of these things. So if you go to the Amazon you see a lot of products. So let me go to the Amazon and I'll just show that to you.
[13:19] So all of these phones etc. So these are this this is what these are data images things are data. So this might be coming from the database. Yeah this might be coming from the database. So the database might be storing all of these
[13:34] things. So for day-to-day transaction we use we use database. Why for day-to-day transaction because database provide us with the asset properties. So we'll talk
[13:46] about atomicity and you'll get to know that why and you'll get to know that why asset is so important.
[14:02] are executed in all or nothing manner. So what does that mean? So it simply means that let's say let's say this is you and this is your
[14:16] Yeah. Now what you're doing is let's say you have got hear me out. Yeah, you've got 50,000 as your account balance. I'm talking about INR. Anything anyways.
[14:30] Yeah. And now your friend needs 10,000 rupees. So you say fine, I'm going to give you 10,000 rupees. So what you do is you go ahead and you make a transaction. Now this is something that we do in our day-to-day life making the
[14:42] transactions via bank. So you'll understand that how things works. So here I go ahead and I make 10,000 rupees transaction. So what will happen is the 10,000 will
[14:55] account and it will get credited in your friend's account. So this is the expected behavior when everything goes well.
[15:08] transaction. This might have happened a lot of time with you all that you make The money got debited. Yeah. And hear me out. Yeah, the money got debited from your account. But
[15:23] your friend did not get the credit. Might have happened, right? what will happen in your account you see 40,000. But in your friend's account still it is still reflecting zero.
[15:38] So this is a very bad thing, right? If I talk about banks, this is something if things are happening like this, then we end up calling these people all the customer cares and you know
[15:51] that we I transferred did not work. Whatever it is, it's definitely not some not something that we would appreciate, right? So what happened usually in in your day-to-day life you might have seen
[16:04] that if the money is debited from your account and there was some problem while you were making the transaction, it got recredited. You get the message that you have got your money back.
[16:17] what happened because there's a database that is working. Database says that either all or none. either all
[16:31] or none, nothing. that either the whole transaction should get processed successfully. should get processed successfully, it means
[16:46] your account is getting debited and your friend's account is getting transaction. But if there's a problem in between, yeah, if there's a error in between, there's some problem in between, then
[16:58] back. transaction or there should be nothing. And that's why when there's a error while you're making some transfer and if there's some error, you you'll get
[17:13] you'll get your credit back. You'll get your money back automatically. property that is atomicity. So this is that either the whole transaction is successful, but if there's a problem in
[17:26] between, then it will make sure everything is rolled back. So your deducted amount, the amount which was deducted is rolled back and it is again credited. So this is what atomicity is. Now this is so
[17:38] important property. If I talk about banks and if I talk about any other website also, Amazon also, you make a order, shouldn't be made. There should be nothing half in between, right?
[17:52] So atomicity is very very important. Now we're going to talk about So everybody knows the meaning of consistency. Yep. So here it is written, but still I write it.
[18:05] So again, I'll explain you with the help of an example. of an example. So let's say that you've got an account. bank and you've got 15,000 rupees.
[18:19] Now in your bank and you might have seen that you a lot of banks does have such rules. So let's say that your bank has a rule that you should maintain minimum balance of
[18:33] 10,000. So this is a rule right minimum balance Now what you do is you make a transaction you make a transaction of 10,000 rupees
[18:48] 10,000 rupees in your friend's account. your account how much amount is left? You've got 15,000 and you make a transaction of 10,000 so how much amount will be left?
[19:04] Minimum balance should be 10,000. So here consistency says rules are not broken. So in very simple words the rule says that you have to maintain 10,000 rupees in your account if you try
[19:16] to if you try to transfer 10,000 rupees because you've just got 15,000 it will give you error it will not allow you to do it. You might have seen on on GPay Google Pay that there's a limit of 1 lakh rupees
[19:30] 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 lakh rupees in from your Google Pay it gives
[19:42] you error it says being no that you're not allowed. So that are what that that is what that is rule. So I'll make sure that the rules are being followed the rules are not broken.
[20:06] it ensures the transaction do not affect each other so again I'm I'm going to each other so again I'm I'm going to explain it to you with the example. So let's say that you and your sibling or your spouse
[20:20] or your parents you both are using the same account. So it happens, right? We share the accounts with our loved ones, dear ones. let's see, let's suppose that the account balance is 10,000.
[20:38] what is done is, let's say you we make a first transaction that is we are taking ₹5,000 out of the bank. So let's say we are taking ₹5,000 from the ATM.
[20:51] So the new balance would be what? The new balance new balance will be again 5,000. when I say transaction, it can be anything. It's not only about credit and
[21:05] debiting. So the second transaction says it wants to see the data. Yeah, it want to see like how how much
[21:17] is the account balance. So let say that you are taking the money out from the ATM and at the same time your parents are checking the balance. Yeah, parents are checking the balance. Yeah, so they are checking the balance.
[21:34] they may end up seeing they may end up seeing maybe 5,000
[21:46] Yeah, so your mom is checking and she sees that there is ₹5,000. But what happened when you were so you initiated the transaction and ATM took its sweet time and then it failed. Yeah, there was a failure.
[22:02] What will happen? Or maybe you're not taking it from the ATM, you're just doing the online transaction. So you have initiated ₹5,000 transaction, but in between it got failed. So what will happen? It will roll back
[22:14] in your account. But your mom is seeing 5,000. So that's a problem. You getting that's a problem. Actually you don't have 5,000 because your your transaction failed. So it.
[22:28] So everything will roll back and you you actually you have 10,000 rupees only. You don't have 1000. You're getting. So you don't have 5000, you have 10,000 but your mom is seeing 5000. So that's
[22:41] that's a problem. That's a problem. So isolation says isolation says that we we are going to do the things in isolation. Yep. So it means it means that T2
[22:57] yeah in the transaction to either it will see the old data or it will not see anything. Sometimes the screen takes its sweet time in in order to get refreshed or in order to show you
[23:12] So it will not show you half incomplete data yeah half done data. It will not show 5000 at least. So it either it will show 10 10,000 or
[23:26] it will wait for some time for a for the transaction to get processed that T1 transaction to get processed successfully and then it will show 5000. But nothing in between. Here it was showing in between see.
[23:38] You are making the transaction and it is showing 5000 to your mom. What what But your mom sees 5000. So that's a wrong thing that we are showing, right? even after. This is something in between.
[23:51] So isolations make sure that everything happens, all the transaction they happens in the isolation. So they are not affecting each other. So it means that if you are looking into the
[24:04] balance, either you're going to see 10,000 or it will take some time and will show you 5000 once the transaction T1 is T1 is successful.
[24:19] Means transaction two will not see the uncommitted deduction as simple as that. uncommitted deduction as simple as that. And so am I clear with isolation?
[24:36] Simple, everything is something that we get to see in our day-to-day life.
[24:49] Now what is durability? This is a very simple one.
[25:01] Durable simply means that the data is permanent. transaction is successful, see 5,000 deducted from your account and credited
[25:15] Yeah. This This yeah, this should be durable or the data should be permanent, as simple as that. So if there is a power failure, even if
[25:27] the system crashes, the data will be still saved. So once, so it simply means once committed, it stays forever.
[25:40] And this makes sure that there is no data loss. So I'll just quickly recap. Atomicity says no partial updates.
[25:55] or should be none. Consisting consistency says consistency says no rules breaking.
[26:17] So this makes database very very special and that is a why we use it for day-to-day transaction. So there's a word word for day-to-day transaction that is OLTP.
[26:30] So So should definitely know about this thing. It's nothing but online not write the whole thing Transactional Transactional Processing.
[26:49] transaction, Amazon Your banks. So these are what day-to-day transactions. So all the companies for their OLTP they use databases. their OLTP they use databases. So databases are very important.
[27:12] 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
[27:24] it should be no transaction. So there there should be no partial transaction. Consistency you can give the example make sure that data follows the rule. I
[27:36] give you the example of Google Pay also. Right? the session. Yeah data warehouse will cover what is in our curriculum and I'll
[27:48] explain you. So data warehouse also I'll explain. It's a very interesting concept but just keep it for the end of this end of the session. So I'll explain.
[28:01] Siddesh what you're saying is absolutely right.
[28:13] once again. So isolation simply says Think of it maybe I'll give you one maybe one more example. So let's say You make a transaction you have got 10,000 rupees. Yeah
[28:27] Now 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
[28:42] looks into the account balance and sees ₹5000. happened? Something happened and your transaction got failed and everything got rolled back. So basically you have got 10,000.
[28:57] But your spouse saw 5,000. So your spouse was able to see incomplete data. So either your when you are checking the account, it should be either 10,000 before the transaction or should be
[29:12] 5,000 after the transaction. So it should not be in between. Yeah, when you're making the transaction, understand the transaction is still not taking the exam and when you're taking the exam,
[29:26] so you're preparing for the exam maybe, yep. And you're telling your mom that oh, I'm going to top the class. So it's like too soon to say, right? So you have to take the exam first and then you can say that okay, you have
[29:38] topped the class. But here also it's the same thing that the transaction is And when the transaction is ongoing, this state shouldn't be shown to the this state shouldn't be shown to the user.
[29:52] if the transaction fails, then if you are showing 5,000, see, if you're showing 5,000, then it's a bad data, right? You're amount is still 10,000 because the transaction failed.
[30:06] But you're showing 5,000. So that's a bad data. So either you have to either you should show 10,000 or you should show or you should show 5,000 after the successful transaction.
[30:18] 5,000 after the successful transaction. Am I clear now?
[30:43] there are different different type of databases. brief of the different type of databases. we are going to focus only on the one type of database which is used
[30:56] in major projects. So, we have got different different type of databases. The first one in our list is the relational database. So, we are going to learn the relational database. Database is what? It's nothing but it's
[31:09] a place where you store your data. Yeah, where you store your data. centralized, distributed. In our curriculum, we have got relational
[31:23] 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 We'll definitely not deep dive. That is not required.
[31:42] database. Database is what? It's storage of data. So, relational database says that I am going to store the data, yeah, in the form of the tables. Now, what tables as?
[31:57] Tables as columns. Yeah, it has columns. Let's say student ID, student name, student number, all of these, yeah. And student number, all of these, yeah. And then it has got data.
[32:15] So, this is what? This is relational database. means the tables are connected to each other. Yeah, the tables may be connected to each other.
[32:28] So, how they are connected to each other? I'll give you one example. But we are going to deep dive into this. This is what we are going to learn. So, it's very much okay if you do not understand it right away 100% yeah
[32:40] that's very much okay. If you understand 50 percent of it that's also fine. So for example let's say that I've got let me let me see if I've got something just give me a second. So instead of writing I'll better show
[32:55] you. Just give me a second.
[33:14] So you can see these are what these are tables with rows with columns and rows. So it's a relational database. So relational database says that this is
[33:26] table let's say this is table one. This is table two So relational database says that there's a relationship between the
[33:38] between the tables. Now when I say that there's a relationship between the tables what does that mean? So if you see for example so this table is storing the data of the
[33:51] orders. Order date, order ID, customer ID, quantity. So here the customer ID is one two three. So here we are not storing the data of
[34:03] the customer. In this table we are just saying that this transaction is made by the customer with the ID one two three. But we are storing this data of the customer the information of the customer in another table.
[34:17] So for example one two three ID is of Anshul and he's male. We can have more information phone number birthday so many so much of it.
[34:29] So this is just to make you understand. So in case of relational databases everything is stored in the form of the table all the data between the table. So you can see that this table and this
[34:43] So you can see that this table and this table is related to each other. related to each other. So when I say that two things are related to each other, let's say that you are related to your
[34:56] So there's something that is common between you. Let's say you're related to each other because of the family. Yeah. got multiple friends and you say that okay, I am
[35:09] you know, somehow I am that doesn't make much of sense. But yeah, let's say that I am related to this friend in a way that we all belong to the same college. So that's a key
[35:25] 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 orders table. So what do you think? What is the key
[35:39] between this table and this table that is actually making the relationship So in order to make the relationship happen, there should be some happen, there should be some something in common.
[35:56] Yes, customer ID. Yeah. Tell me about this one and this one now. I hope this is clear, right? So this is how the relational databases are
[36:09] designed. We are going to deep dive into it. That's what we are going to learn for the next eight classes. Now let's talk about the other databases that we have. Okay, here I've got okay, I just totally forgot
[36:22] that I've got a PPT 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 and we are going anyways going to deep dive into it.
[36:36] Okay, a lot of speaking from my side. Again, I'm so sorry because I can't help From tomorrow, we'll do a lot of hands-on. Now if I give you the examples of the relational database,
[36:50] 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, PostgreSQL. Now if you learn even one single
[37:02] database, if you learn MySQL, then you can say that you know all the others. And I'll tell you the reason why because 85 to 90% of all the database they are same. It's just that there's
[37:16] little bit syntax here and there. So you don't need to learn all the different relational databases. If you learn one, you you will surely be able to work on the others also. So if I'm let say I'm
[37:28] taking interview, so if I know that the person knows MySQL very well, but in my project we are using SQL Server, because if that person is good as in
[37:43] that person knows, that candidate knows MySQL and he's able to answer all the questions and he understands the length and the breadth of the technology, then I'm sure that working on SQL Server will will be
[37:57] definitely no big deal. So all these databases, relational databases, they're You don't have to worry about learning different different databases. That is definitely not required. You learn one properly, you can work on
[38:12] You learn one properly, you can work on any database for that matter. Now there's one thing about the relational database
[38:25] and why I'm talking about it right now because after this we have got NoSQL database. So see relational database is kind of a strict database. So when I say it's a strict database,
[38:39] what does that mean? So it simply means that let's say over here, I'm storing the student details. Roll number, name and marks awarded. So let's say I've got a new student.
[38:55] Now for this new student, I want to store the data, but I've got I've got more data for this new student. So I've got let's see name is Neha,
[39:12] marks 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
[39:24] that I'm so sorry, you have something extra, I cannot accommodate. So it's very strict. You're getting? So this table says that you've got extra column. I'm sorry. I can just accommodate three columns.
[39:39] So I am I cannot provide you this flexibility of storing anything in me. I've got three columns. You have to stick to three columns. You cannot give me four columns. So it's very strict. I'll give you one
[39:52] more example of strict. The second strictness it says now it says that this column is integer. It means that it can take only integer values.
[40:05] Yeah, it can take only integer values. So again, I go ahead and give new string value. Yeah, I want to give let's string value. Yeah, I want to give let's say 10.
[40:19] It will say what? What are you doing? I can just take integer. What the hell are you providing me? You're providing me 10 like the string 10. Sorry, I can't do that. So it's very strict. Yeah, it says that
[40:33] see I've created this table with the three columns for all the columns. So for this one, we have got the data
[40:48] type as integer. This one, let's say as string, yeah, varchar, and so on and so forth. So we have to stick to that. stick to that. Yeah, we have to
[41:04] We'll talk about that. We'll we'll see to that. Yep, it's very easy to set these rules. So you got that that relational database is very very strict. You get that? Am I clear with this point? How do we do
[41:17] that? Once we start writing the code, you'll get to know and it's very easy. It's like one word thing. Yeah, you just have to add one keyword and then you're have to add one keyword and then you're done, done and dusted.
[41:31] Am I clear that the relational database are super strict, very stringent? Fine. Now let's see there's a requirement. There's a requirement. The requirement is that we want flexibility.
[41:50] So what I want is let's say that I am I'm the business owner and I do not want this. Yeah, because let's say I I I am I have my own school and I say that fine. [snorts] Yeah, but
[42:03] let's say there's some student with some, you know, more things. Let's say that he has got some he or she has got some certificates or maybe some achievements. So I want to store that data also. Yeah, I simply do
[42:17] Yep. So either you add one more column to your table, yep, or give me something else that is more flexible. I want to store everything whatever whoever is coming to my school, yeah, whoever is
[42:31] getting registered, whichever student is getting registered in my school, I want to store it. So I just simply do not want to discard any of the information. So
[42:44] here comes NoSQL databases. So NoSQL databases Now we are not going to get into into the architecture of it. As you can see, these are non tabular databases.
[42:59] So it stores the data in a way. Now this also stores the data. Yeah, this also stores the data. But it stores the data in a way that it But it stores the data in a way that it is super flexible.
[43:20] in the form of in the form of tables. in the form of tables. And in case of no sequel, it stores the other ways also. But I'll show you the JSON one. I'm just
[43:34] looking for something that is quite easy to understand.
[43:46] Yeah. So here you here is here you go. So if you see this is what this is JSON. So for example, you've got student. So we we say student. Yeah. Or let's say employee. Yeah, employee. So you're storing the first name, last
[43:58] name, display name, middle name, birth name of the employee. Now this is let's say the information of some X X employee. Now let's say that we have got another employee and that another employee has
[44:11] got some other information also. Let's say passport number. So it easily accommodates that. It says fine. So I'm going to have one more note. So this is one note for one data, for one employee. So
[44:25] 100 notes. So it says that I'm very much okay with it. You have got one more data. So I just have to add one more key value pair over here. Yeah, key and this is a value.
[44:38] 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. I'm not strict. Right now I've got five
[44:50] 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.
[45:07] different formats. So it stores the data in key value, column, graph, and dive into it. But yes, so why do we use NoSQL? This So if you want that flexibility, see tables are very strict. So you want that
[45:23] flexibility that tomorrow if your data comes in some other format, you should be able to store it. Yeah, it should not it should not say no. Your database shouldn't say no. Then you can go for NoSQL database. So
[45:37] am I clear with this point? Definitely you don't have to deep dive into it. XML format, yes, XML, JSON, key value pair, exactly.
[45:58] So I'll take a pause. Why don't with whatever you see on my I want you guys to guess the use case of graph database.
[46:14] So have you seen on you might you on seen that it keeps giving you recommendation of friends of friends, mutual connections,
[46:27] friends of friends, mutual connections, suggested friends. whenever we have got data like this, yeah, where
[46:43] the data is very much related, yeah, it's mutually related, you can see over here. Yeah, so we use graph database. So friends of friends. So this is again graph database.
[47:02] showing you all the other products. So that can again come under the graph depends. But yeah, this is what? This is graph But yeah, this is what? This is graph database. So
[47:18] graph, not table. So basically if this is you, start showing you the mutual connections. There's, there's, there's. Yeah, so this is how it works. Even I have never worked on the graph
[47:32] database. Usually it is used by the social media companies, yeah, social media companies. Then it is also used by the banks, I think for the fraud detection etc.
[47:46] so that they can you can find the connection between the data. Now we'll talk about centralized database.
[48:00] database for that matter. It can mean SQL, NoSQL, graph. So centralized database is what? You have got one database.
[48:12] everybody is 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
[48:26] database. So tell me what could be the problems of having centralized one single big database?
[48:46] What if there's a power cut? There's a failure. What will happen? Everything will go down.
[48:59] Yeah, you're not able to access your Account and so do the users. a centralized database where you have just got one single big database storing
[49:15] everything. And a lot of companies still use this centralized database, though it is not recommended.
[49:28] solution of centralized database? Now I'm not expecting the right word for I'm just expecting that what should be the solution? What you you would do?
[49:46] Don't you think so you'll have the copies of it, multiple copies? So backups, yes. So if this goes down, you have a backup So that you do not uh
[50:00] because of the system crash, your website will not go down. So having these backups definitely is maintaining only one database. Now let's say I have got two backups. So I'll end
[50:15] up maintaining three databases, right? So it's pretty expensive, but yes, we call it as distributed distributed databases
[50:29] 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 Yeah, all the good big companies websites, they are always up and
[50:44] running. So in Pune, this happened almost uh a year back. I think it was it happened in June, July. from Pune. So uh there were some issues with the
[50:58] power. Now this area where I'm staying is the IT hub. One of the IT hub. So there was some big issue with the power supply of and that was for the whole area.
[51:11] And we were without the power for three days, straight three days and it was not only for the households, it was for all the IT parks also. And we have got some data centers. When I say data centers,
[51:25] you can say servers which are storing a lot of data. So we have got some data here in this part of Pune in Jawadi. this part of Pune in Jawadi. So everything went down.
[51:41] problems they can definitely occur. So that's the reason why the companies they always prefer having the distributed databases. database, they're going to have databases at multiple places to avoid
[51:58] any downtime. Am I clear?
[52:14] got a lot of theory and I've been speaking from last one and a half hours. So it's not my thing, it's about you guys because if somebody speaks or if you have to listen somebody for more than 20 minutes, we
[52:28] somebody for more than 20 minutes, we tend to 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
[52:43] continue. Because we have got the next topic, so I just want to you know cover these topics in continuation. Or if you're okay, maybe I'll take another 15 minutes for these topics,
[52:56] another 15 minutes for these topics, not more than that. I'm so happy. Fine.
[53:15] one thing. We've got the data, we've got the books. books. Who manages the library? Somebody should also be manage it,
[53:27] Somebody should also be manage it, right?
[53:39] 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
[53:53] definitely, but we want a tool which can help us manage the database. managing the library, the librarian might be
[54:08] maybe maintaining some book, some records. 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
[54:24] there was a there was a computer, of course, in the library in front of on on her desk. So she used to use that. Also she used to maintain one register, who's taking which book and what's the last date of the submission, what's the
[54:40] fine, all of these things. So there's a system that needs things. So here also in order to maintain not maintain, in order to manage the database, we have got DBMS.
[54:57] Here we have got DBMS. So you can see database management system. So database management system is nothing, but it allows user to create, update,
[55:09] retrieve, and manage the data in the structure format. So basically, you have got the database with a lot of data. Now, in order to of it, insert the data in it, update the data,
[55:26] with the data, for that we need a system. Yeah, we need a system. And we call it as DBMS, database management system. So it it's a software
[55:41] So it it's a software that helps us manage the databases. create the database, you can read yeah, you have got the data, right? So you can create, you can read, you can make the updates, you can delete it.
[55:58] So you can do all of these things. So these are called CRUD operations. So do not get scared with these jargons, technical jargons. These are very simple thing. So when I say CRUD,
[56:15] stands for what? I just talk about the operations. It means create. Yes, so CRUD operation you're going to listen to this term a lot. So this is not specific to the databases only like
[56:29] not specific to the databases only like yeah, R stands for what? Yes, very good. U stands for what? Update. And D stands for delete. So if anybody
[56:43] says CRUD operations, do not get scared of it. What is this CRUD? It's nothing. You're creating the data. Yeah, just like uh you've got a wardrobe.
[56:55] So you are just looking at your wardrobe, how how many clothes you have Maybe you're updating your wardrobe, like you're adding something to it, and so on and so forth. So here also we do all of these things,
[57:09] but we do it with the help of the software called DBMS.
[57:22] okay maybe I'll talk about that later not right away. it could be? I just told you what DBMS what RDBMS
[57:36] I just told you what DBMS what RDBMS could be? database you know that there are so many databases right?
[57:51] We learn about the relational database now to manage the relational database what we have we have got relational DBMS simple.
[58:03] manage the database as simple as that. Now what is SQL? So see So see understand this thing.
[58:16] Now you've got a software now hear me out yeah you've got DBMS software. you're going to make the DBMS work for you yeah you say DBMS can you
[58:29] 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 chat GPT I I do not understand plain English. So DBMS says see I can do a lot of
[58:44] things for you but then you have to speak to me in my language the language that I understand. So DBMS in order to talk to DBMS you So DBMS in order to talk to DBMS you have to use SQL.
[58:58] are going to learn SQL. Why? Because with the help of SQL we can talk to the DBMS and
[59:11] talk to the DBMS and get our work done. the pen. Yeah, get our work done. That is CRUD, any of the CRUD operations that we want to perform on the database. So you're getting?
[59:24] Just like let's say there's a librarian. Yeah, librarian is what? Librarian in our analogy is DBMS who manages the database here with the uh so then we have got
[59:38] library. So library is what? So library is what? Library is a database and inside the library we have got books.
[59:54] So books are what? Books are data. These are data. you want to fetch some book. Yeah, you want to fetch some book. You want some some book. So you go to the librarian and you say
[1:00:09] that I want so and so book. Yeah, I want book so and so. So your librarian will go and may fetch So you're getting? So you're going to talk to the librarian.
[1:00:24] So you may talk to the librarian in the language that your librarian That makes sense also. Yeah, if your librarian understands only one language that it's English and if you start speaking Spanish in front of that
[1:00:37] librarian, librarian would be like what are you saying? I'm like please bother here. So the librarian will not do your work as simple as that. So here also
[1:00:49] simple as that. So here also in order to talk to the DBMS yeah, in order to make DBMS work for us, we use SQL.
[1:01:01] So it understands SQL. So it's a language. It's a query So you can see over here that it's a structured query language and it is is to manage and manipulate the data in relational database manage
[1:01:17] systems. You use RDBMS. You use SQL RDBMS. You use SQL RDBMS. So am I clear what is SQL?
[1:01:30] Data is saved to the database. In order to manage the database, we have got DBMS. So in DBMS only we store the data actually in the form of the databases. Now in order to make the DBMS work for
[1:01:44] Now in order to make the DBMS work for us, we talk to DBMS in SQL us, we talk to DBMS in SQL language.
[1:01:57] going to use a tool. Just like if I use Excel, so in order to use Excel, Excel only understands English. So I can just work. I can just use I am not sure if it has got more languages. I'm not aware about
[1:02:11] But till like now I've just used English on So in case of DBMS also, in order to talk in case of DBMS also, in order to talk to the DBMS, you need to use
[1:02:24] SQL language. Yeah, that's the language of DBMS.
[1:02:36] DBMS, Rajpal. So we are going to use DBMS as a software in order to manage the data. >> Integration, continuous delivery, monitoring and also reliable [snorts] deployed practices.
[1:02:48] In this DevOps, what is the difference between SQL and MySQL? >> SQL is a language. Hear me out. Yeah, SQL is a language. MySQL is a DBMS.
[1:03:03] is a DBMS. >> Microsoft Excel full course intro. >> DBMS. We have got multiple relational database management systems in the For example, MySQL. >> Excel full course intro.
[1:03:17] >> have got SQL server >> the name with the databases. Yeah, shares the name same name with the databases. Oracle Sorry for this writing guys and then
[1:03:31] Sorry for this writing guys and then Postgres YouTube channel. >> What happens in developers
[1:03:45] we use SQL language. So SQL is a language and this is a DBMS. language and this is a DBMS. Have I answered your question, Avinash?
[1:03:57] you'll use SQL. In order to talk to Oracle also you're going to use SQL. Postgres also you're going to use SQL. So SQL is a language So SQL is a language and MySQL is the DBMS.
[1:04:13] write but yeah, MySQL, Postgres SQL server these are what? These are the softwares. Yeah, these are what? These are RDBMS, database management systems. In order to talk to the database management system we need a language.
[1:04:28] The language is SQL. So MySQL is a DBMS and SQL is a language to talk to the DBMS. It's a coding language, exactly.
[1:04:44] a boring topic but yes, it's in our curriculum. So we are definitely going to cover tables and entity relationship model. So let's keep it for uh let's keep it
[1:04:59] whatever we have covered so far. A quick recap, we learn about databases. What what is database that we learn about the different type of databases that we have.
[1:05:13] Also we learn about acid guarantees of database. Then yeah, that's all. Yeah, that's all mainly. Then we learn about what is SQL mainly. Then we learn about what is SQL in DBMS.
[1:05:28] component is entity. the real world object. So basically entity you can think of it as a table.
[1:05:41] entity you can think of it as a table. That's a real world object. I give you the example of student or employee and it is represented with the help of rectangle.
[1:05:59] Then we have got attributes. So what do you think attributes could be? We just talked about the table. So what attributes could be? are nothing but the details of the entity. Yes. So all of you write
[1:06:14] details yep, of an entity. let me write it. So attributes are what? They are the property of an entity.
[1:06:30] you tell me what could be the attributes. for the student table? Student entity
[1:06:43] name, age, class, marks, etc etc, right? So these are nothing but the attributes.
[1:06:57] using ovals. Yeah. So whenever you see oval, it means that it's an attribute. Then we have got relationship. Now you know that tables they can be related to each other.
[1:07:12] foreign key not right now, but yes, how entities are connected So I gave you the example of orders tables. So you told me that the two
[1:07:27] 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 if I give you the example, let's say that we have got two entities, students
[1:07:40] is one of the entity and course. So course, let's say students, let's talk about simply learn. So student table has got all the information about you guys and courses table has got all the
[1:07:53] information about the courses that are provided by simply learn. So if I talk about the relationship between the two, so simply students have enrolled or I would say student
[1:08:07] enrolled for which course. Yeah, I'll simply say student enrolls for course.
[1:08:19] I told you that in order to establish the relationship, you need to have the keys. You need to have the keys. So I'll talk about the keys right away. idea about it. So we have a primary key. Now what is a
[1:08:35] primary key? So primary key uniquely So primary key uniquely it uniquely identifies an entity.
[1:08:56] uh before I give you this example, let's talk about our day-to-day life example. So let's say that our government, now whether you're sitting in US or wherever your country is, whichever your country is, wherever you're sitting.
[1:09:12] So the government is maintaining the database of all the citizens. Now you, I'm Tulika Gupta and there might be hundreds and thousands of at least thousands of Tulika Gupta in India, Right that very much possibility
[1:09:27] right? So my data and the other Tulika Gupta data will coincide. So it has to make sure that we it uniquely identify my my data and it uniquely identify the other
[1:09:41] Tulika Gupta's data. So what do you think what government would use? In India we have got Aadhar card those who are joining from US, those who are
[1:09:55] not Indians maybe passport number, PAN card. Yes? So it uniquely identifies an entity. So over here now you guys tell okay in this table we don't have any primary key. Okay in this table can you
[1:10:11] primary key. Okay in this table can you see any primary key? makes sense also see customer name can be repetitive. We can
[1:10:23] have a lot of customers with the same name as Anshul and male. So this can be definitely repeated but how will we uniquely identify this Anshul or how will we differentiate this Anshul with the other Anshul that we
[1:10:37] have got in our database using the customer ID? Can you tell me what is the primary key for this table?
[1:10:50] So we want a key that will uniquely identify each product. It's product ID. So am I clear with primary key?
[1:11:16] basically it connects it connects two entities. Yeah it connects two entity. So let's say you've the entity one.
[1:11:31] It has got some primary key. P one. with entity one, entity two is using this primary key.
[1:11:46] So for this entity, this is not the primary key, rather it's a foreign key. Yeah, this is a foreign key. So I'll explain it to you with the help of the example over here. Now again, let's talk about this table
[1:11:59] and this table and we're going to repeat these concepts of a primary key and foreign key. So please make sure that you understand it and you remembers the concept. So tell me one thing
[1:12:12] again, the same question that I've asked you before. The two tables are related with which column? Quickly. This table I'm talking about and this table I'm talking about.
[1:12:39] We use over only. These are the attributes, so we use over only. Now customer key is what? It's a primary key in this It's a primary key in this table. Now we are using the primary key of this table
[1:12:55] in this table. So for this table, for let's say the name of this table is orders. For this table, orders ID becomes what? It becomes what? It becomes foreign key.
[1:13:10] It becomes foreign key. Yes, it becomes foreign key. I repeat. This is what? This is a private, this is Yeah, this is a primary key. Now in order to connect this entity, the
[1:13:26] customer entity with that of the orders entity, the column that I'm using, the common column that I'm using is customer ID. Now for this table, customer ID is a primary key.
[1:13:39] But for this table, customer ID is what? It's a foreign key. It's not a primary key. So the two entities. Yeah. So this foreign key is a primary
[1:13:55] key of the one entity and it becomes a foreign key of the other entity. Am I foreign key of the other entity. Am I clear?
[1:14:14] 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
[1:14:28] the other table in order to create the relationship between the two tables, between the two tables, yeah. So it becomes the foreign key on the other table. So this is what?
[1:14:41] This is a foreign key. We are going to revisit this concept when we start our coding. Am I clear now, Shweta? Just remember this thing that whenever in order to create the relationship between the two
[1:14:55] create the relationship between the two entities, it becomes a foreign key. diagrams looks like. I think here I'm not I have not created the
[1:15:09] you. So it looks like this. So it looks like this. You've got the entity. This entity may have multiple attributes. Let's say name.
[1:15:26] Okay, I'm so sorry. I don't know what is happening. ID. Now I've got another table. Again, the name of the table is course.
[1:15:43] the two. So this is what in rules. ER diagram, you should be able to understand student and courses are
[1:15:59] 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 diamond that you see are nothing but the relationship of the entities.
[1:16:34] got different different type of attributes. exercise for you all. I'll see to that. If we can do it today,
[1:16:49] that's fine. Otherwise, we'll do it tomorrow. So I'll ask you to create one ER diagram.
[1:17:18] attributes. Now this is little bit boring topic, but that's fine. of your entity. So we have got the key attribute. Then we have got the derived attribute, multi-valued attribute and composite
[1:17:32] multi-valued attribute and composite attribute. So let's cover all these.
[1:17:44] but it used to identify one entity from the group of entity. So I just explain you the key attribute employee ID, student ID, roll number, employee ID, student ID, roll number, passport number.
[1:18:00] These are what? These are key attributes. So you can just have a look.
[1:18:42] have got. Now what are composite attributes? As simple. Just give me a second. I think I've got something on the chart. slide. Fine.
[1:18:56] Fine. Is this the one? For product table, Akshay, okay, I'll come to that.
[1:19:12] asking for the product table. Now tell me for product table, one that I've highlighted. What is the primary key?
[1:19:28] The same question, product ID. And I'm using it in this table. So in this table, a primary key of one entity is used in the other table. So for other table, this
[1:19:40] So for other table, this becomes what? This becomes what? You can also write FK. Yeah, you can just write answers in Y also no and
[1:19:52] you don't have to type in the whole thing. So key attributes I hope I'm clear. Composite is also very simple.
[1:20:06] So a lot of time we have got the attributes which are composed of several attributes. So for example, So address is composed of three attributes.
[1:20:21] 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. So that's what composite means. Am I clear with composite?
[1:20:43] Okay, my laptop is acting up. Just give me a second. So what is what are multi-valued attributes?
[1:20:55] there can be some attributes, there can be some attributes which may have multiple values. Yeah, which may have multiple values. For example, let's say that we have got the attribute order or let say we have got
[1:21:09] attribute order or let say we have got the attribute order. Yeah, you make an order.
[1:21:21] So in in case of order, in case of order in in case of order, in case of order let's say I make an order of a product. let's say I make an order of a product. I'm not able to write.
[1:21:35] There's some issue with the writing thing. Just give me a second.
[1:21:47] Yep. So I buy one product of some quantity, one quantity. So what I mean to say is that
[1:21:59] these attributes may can have multiple values. It can have more than one value. So we represent it by double ovals as you can see. So as I told you that you are not going to design the database. That's not your
[1:22:14] So that will be designed by somebody else, but you're going to work on the database. You're going to generate or play with the data of the database. So you should know if
[1:22:26] should be able to know what this ER diagram is representing. And it's a very simple thing if you see. It's just like the walk in the park. It's so simple. the walk in the park. It's so simple. Moving ahead.
[1:22:44] means that these values see attributes are what? They are like column they are columns, right? So the values of these attributes, the values inside these attributes will be derived from the other
[1:22:56] attributes. For example, let's see that you have got experience. Now we may derive the value of experience with the help of let's say I've got the
[1:23:09] of the employee and today date. Yep. So experience in our company, let's say that it's simply learn, employees for simply learn.
[1:23:21] years in simply learn? So see this value can be derived from the other attributes. Yep. So we do not have the value. this column. Rather we are going to derive it during the runtime.
[1:23:37] Yep. So because see if I save it, let say I save it. So today it's two years. Let's let's say. Yep. So let's say I've saved it two years. But after two months, it will be two years and two months.
[1:23:50] So, if I'm saving these value, it it may not give me the right values. It this point of time, maybe it's two years for an employee.
[1:24:02] years. So, again and again I have to update my database. So, I can simply derived it. If possible, I can simply derive it. So, another attributes with the uh
[1:24:15] uh start date or the joining date. for the derived attributes. We are going to work on it. We usually create the column for it. But uh
[1:24:30] so it's like you may or may not create the column. You can create this column on the runtime. So, anyways, you may or may not on SQL, this will be clear. You'll understand.
[1:24:47] So, am I clear what are derived attributes, everyone? example, price. So, quantity into unit price is total price. Derived attribute.
[1:25:02] Derived attribute. Perfect. Very good, Ganesh. Okay, relationship you already understand. there are few things that just give me a second. I would like to just give me a
[1:25:15] second, guys. We will just learn few more terms. I know these are boring, but yeah, we'll learn few more term. So, entity sets are what? Entity sets are nothing but the relationship.
[1:25:28] are nothing but the relationship. So, for example, for example, we have got employee. Yeah, we have got the employee. We have got three employees. Let's say we have got three employees.
[1:25:42] So, let's say employee one is Neha. Employee two is Joan, employee three is Mark. So, entity set will tell us the relationship that Neha is let say working on so and so project.
[1:25:57] So, let's say this is a project. Yep. So, let's say this is a project and this project belongs to so and so department. So, with the help of M relationship set
[1:26:09] you at least give the basic idea of how the relationship is. So, for example, I repeat Neha 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
[1:26:22] that belongs to so and so department. So, this is what? This is nothing but the relationship set. So, you can see that it depicts that E1 So, you can see that it depicts that E1 works in D2 and E2 works in D3. So, E
[1:26:36] E1 works in D2 and E2 works in D3. to be very honest. That you see ER model for sure that is
[1:26:52] created as a part of the document for the database design. data, some dummy data of the entities and how it is connected to the other
[1:27:05] entities data. So, just give you the glimpse of it. so I'll talk about table only. So, tables they they can have relationship with each other. So, relationship degree basically tells
[1:27:20] that the degree of relationship that is between the entities. So, basically tells the number of entities that are related to each other. Yeah, the number of entities that are related
[1:27:34] to each other. So, for example, So, you can see that I've got only one. Okay, can you tell me how many entities are there? How many entities are there?
[1:27:49] So those who are saying two, it's not two. It's one. Can you see that I yesterday we learn that entities are represented by the rectangle.
[1:28:05] relationship. So this is a relationship. So basically what it is saying is now hear me out. It's very simple thing. So unary relationship is a relationship where the entity is related to itself.
[1:28:21] where the entity is related to itself. So it's a self relationship. So if I give you this example of employee, let's say that we have got the
[1:28:36] employee table. Now hear me out. It's very simple. employee. So let's say 1 2 3 4 and name I'll say a
[1:28:52] b c d yep. I'm not writing the proper names. more attribute that is manager. So manager maybe manager ID.
[1:29:06] So manager maybe manager ID. So let's say that is manager is D. So I will say four. B's manager is again D. B's manager is again D. C's manager is let's say uh let's say B.
[1:29:20] And D they no manager yeah no manager. So over here you can see that the table is related to itself. You're getting? So the table is related to itself. So here because if I talk about the manager
[1:29:34] 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? of A? What is the name of the manager of A?
[1:29:51] So this is what? This is self relationship, where the table is related relationship, where the table is related to itself.
[1:30:03] itself, we call it as unary relationship.
[1:30:29] simply means more than uh it means two, right? It means what? It means two. entities do we have? We have got two entities, customer and account.
[1:30:41] So where customer is related to the account. A customer can have accounts, right? So we have got the customer table and then we have got the account table. So this is what? This is a binary relationship. So it involves how many
[1:30:53] entities? It involves two entities. I hope I'm clear with this that binary relationship simply involves two entities.
[1:31:05] you want re-explanation for any of these. Okay. So how about ternary? So ternary is the relationship where you more than two entities. So basically where we have
[1:31:19] got three entities. Yeah, three entities involved. And think of entities for now as a table. Think of it that entities is nothing but the synonym of table. So over here you can see that we have
[1:31:32] got three entities and all the three entities are related to each other. So employees works in the department, also employee works with the organization. So and also an organization can have multiple departments. So there is a
[1:31:47] relationship between the three entities. So am I clear with this one? So am I clear with this one? It's a simple one. Am I clear?
[1:32:02] Now we'll move to a very interesting topic, the types of relationship. So uh we have got four types of relationship, one to one, one to many.
[1:32:14] So basically see basically one to many and many to of the same thing. So you either write one to many or many to one. It's one of the same thing. So
[1:32:28] actually I say that there are three type of relationship, but you'll you'll see in a lot of blogs they say that there are four type of relationship. So do not get confused. It's all about how you look at it from left to right or
[1:32:40] right to left. That's the only thing. Anyways, we're going to talk about it. allow me a second. So let's talk about
[1:32:53] uh one to one relationship. So what is one to one relationship? and we have got the employee ID table.
[1:33:06] what one to one relationship says that every single instance of one entity is connected to a single instance of another entity. So here when I say instance, what does that mean? It's it's means nothing but the row or the record.
[1:33:22] instance is. It's nothing but the row or the record. So over here if I talk about this one, this example that is being shown on PPT. So let's say I've got the employee table.
[1:33:39] table and there are some employees. let's say ID. 1 2 3 4
[1:33:51] So here let's say for every employee like here also let's uh employee ID is not a very good example. Rather I'll say bank account, yeah.
[1:34:06] accounts. So here I've got the bank account table. only one bank account details, right? You don't end up giving all the three
[1:34:20] 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
[1:34:34] and some bank, yeah. Let's say City Bank or HDFC and so on and so forth. So here the relation is one to one. Why? Because every single instance means every single row. So this is a single row
[1:34:47] is connected to a single instance of the another entity. So it means this is a single instance only. It means one instance is related to one It means one instance is related to one instance.
[1:35:04] only one instance of the other table. It's not that one instance is related to multiple instance on the other table. One instance is related to only one instance of the other table. So this is what? This is one to one.
[1:35:19] If it 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.
[1:35:33] means a row. Saranya, it means a row. One row, one record, yeah. So it's it doesn't mean the column, it means the row. I hope I've answered your question.
[1:35:50] So guys, am I clear what is one to one relationship? But if still you have some confusion, I'm sure your confusion will be cleared once we see the other type of relationships that we have.
[1:36:10] [snorts] So one to many is where a single instance of one entity is connected to several instance of the other entity. So what does that mean? What does that
[1:36:24] mean? Let's say over here you can see that we have got the customer table and this is one table let's say. So I'll just better create my own diagram over here for the better explanation.
[1:36:37] So let's say I've got the customer table. Now I'm just adding two columns. Um maybe uh yeah, let me add three columns. That's fine.
[1:36:52] be suffice. Okay. And then we have got the customer name. So let's say ID 1 2 3 4 customer name a b c d. And here we have got the orders table.
[1:37:10] let's say uh the product and etcetera whatever you can think of. So again we have got some orders 10 11 12 13 14 So here in case of one to many
[1:37:26] 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
[1:37:39] right and you can place multiple orders right? So here also one instance this is instance is connected to multiple instance of the other table. So one record is connected to the multiple
[1:37:54] may or may not be connected to the multiple records of the other of the other entity. So this is what one to many. This is what one to many. So tell me, this is just give me a second. I'll just write this is a customer
[1:38:08] table. Now tell me which table is on the one side? one side? Which table is on the one side?
[1:38:22] Customer. And which table is on the many side? Now as I told you that one to many and many to one. So this is many to one actually. You can see over here, but whatever it is, yeah,
[1:38:37] 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.
[1:38:50] So right now it's here it is one and here it is many, right? So just that if I put order over here, then one customer can can order multiple can
[1:39:03] yeah, order multiple orders. So here we have got order, customer. So order is on the many side and customer is on the one side. one to many or many to one. It's one of the same thing. It's just that you see
[1:39:18] the same thing. It's just that you see it like this or this. one to many and many to one is nothing. It's just that right to left or left to Okay. So here also I think see they don't have the PPT. Oh no, they have the
[1:39:32] So yeah. So anyways, we'll talk about many to many now. So many to many. Okay, before [snorts] we maybe hop on to
[1:39:44] many to many, let's make the class interactive. Why don't you give me some interactive. Why don't you give me some example of many to one or one to many? relationship between the two entities, it's not that it's a rule. Like when I
[1:40:01] say it's a rule, I mean to say that it's not that the employee will be on the many side, the department will be on the one side. This is what we see. So but, the
[1:40:25] that this has to be on the one side and this has to be on the many side. As simple as that, right? So now I want you guys to give me some example of of many to one or one to many. So I'll give you one example. Okay, I've started getting
[1:40:38] the examples. Yes, patient appointment date, very good. Then okay, I'm getting a lot of student subject, patient appointment, okay.
[1:40:51] Student school, yes, one school at least in India, yeah. One school can have many students. Now that's always goes in India. A student never attends more than one school. At least that's how it is for for companies also, right? If I'm if
[1:41:05] let's say I'm working with IBM, so unless I'm moonlighting, employee, sorry, sorry, uh one company being worked by uh
[1:41:18] one employee working in what I'm saying? So one company is can have multiple employees.
[1:41:33] Patient table, a medical test table, very good. Bank customer, right? One nation, many states. Very good. One state, many cities.
[1:41:48] One city, many colonies. Perfect. Perfect. So now we'll move to many to many.
[1:42:02] So many to many is when you have got, let's say again we have got employee table.
[1:42:16] So one employee can work on multiple projects. Let's say we have got the projects let's say okay I'll just name some of the projects that I have worked on Sun Trust etc. So definitely
[1:42:30] there are situations in the company where one employee works in the multiple project. Right? So you may end up working on multiple project also one project is most of the time being worked by multiple employees. So here this one
[1:42:44] to many. So A B C can work on Amex. But A can also work on Sun Trust. So it's what many to many. So I now I want you to contribute some examples on the chat please.
[1:42:58] chat please. For many to many. But when I say what is cardinality means?
[1:43:14] in a relationship how many? So
[1:43:29] that how many how many rows of one table can be related to rows of another table. So how many
[1:43:43] rows of one table to rows of another table.
[1:44:04] Now you know we just learned about that you know a table we can have tables and can be related to only one row of another table or one row can have multiple can be related to multiple rows of another table. So cardinality is like
[1:44:20] going one step deeper and telling telling about that if this is a row this is this can have what all values or this this can be connected to
[1:44:33] what all rows of the another table. Yep. So a relationship cardinality is a number of occurrence of the entity that can be associated with another entity. So basically cardinality will tell suppose
[1:44:46] you have got a row maybe I'll give you the example in the next on the next slide but it will give you the minimum and the maximum cardinality. So let me go to the next slide and then I'll explain you
[1:44:58] that would be better. So if you see over here the cardinality is represented over here. So it says zero which is the minimum cardinality and n is a maximum cardinality.
[1:45:13] and n is a maximum cardinality. Here also zero is a minimum cardinality and one is a maximum cardinality. So what does it says? So it says let's say I've got some developers in my company yeah.
[1:45:28] some developers in my company yeah. So ID and name. In the same way let's say I've got some projects. So project ID and the name of the project.
[1:45:46] four a b c d. some IDs. One two three four five six seven eight nine and maybe some project p one p two and p
[1:46:02] three. So what does what does this cardinality signifies? Let's look into that. So let's say that a is the developer but e right now. So,
[1:46:17] you're getting have got two tables. Now E may not be working any in any of the project. So, zero means that it is possible that any instance of this possible that any instance of this entity, any row of this entity is not
[1:46:32] repeat let's assume that E is not working. Yeah, E is not working on any of of the project. So, E is a new hire in the company until now the A is not being allocated to any
[1:46:47] So, zero means minimum. developers who are not who are not involved in any project. N means maximum. It means that a developer, so B
[1:47:04] can work on n number of projects, multiple projects. So, that's what it means. Zero means we can have developers with no projects. Also, we can have developers who are working on multiple projects.
[1:47:19] Now here zero and one means again the same thing. Now let's see P3 is a new and right now there are no developers allocated to P3.
[1:47:32] So, it means that we can have any any project. We can have any instance in the project. We can have any instance in the project table doesn't have any connection with any of the other instance in the developer
[1:47:47] 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 that's quite weird because that doesn't happen in real life, but
[1:48:00] 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, a small project
[1:48:14] Project. So it means a project can have zero So it means a project can have zero developers or at max the the project can have one developer and that's why what is this? It is one. So B
[1:48:29] is working on 1 2 3 also B is working in 2 5 6. But one project cannot be worked uh multiple developers.
[1:48:42] So am I clear what does 0 1 0 and denotes? denotes? Hm. but fine. Anyways we have understood it that is more important.
[1:49:01] So am I clear what does cardinality means? What is 0 n 0 1 means? You can just read through this slide but please confirm if I'm clear or not.
[1:49:17] You can just read through it. So very simple thing. scared. It's very simple thing minimum and maximum.
[1:49:31] things we get scared oh there might be some maths involved some complicated maths but that's not the case.
[1:49:49] like this 0 n. So zero is what the minimum number and n is what the maximum number. So minimum is what does minimum number means? It means now we have got this table developer
[1:50:02] and we have got this table project. So zero means that we can have some with no projects related to it. So let's say that A is not working on any of the project. A is a new hire in
[1:50:16] the company until now A has not been given any project. So I can have a developer with no project not related to even a single project. So that is very N means a developer can work on multiple
[1:50:30] projects. So B can work on 1 2 3 B can be related to 1 2 3 4 5 6 and 7 9 So that is very much possible. Okay. So this this is what zero and N means.
[1:50:43] Now here it says zero and one. So what does that means? It means again you can have a project. So let's say this is a new project. Till now no developer is working on this project. So think of it as Upwork. Yeah on Upwork
[1:50:56] you see that you got a lot of projects with no developer assigned. Right? So we we can have up the project with no developer. So zero means that a project it is possible to have a project with no
[1:51:09] developer. So this instance this instance is not related to any of the instance of the developer table. So this instance zero means it is possible to have instances not having relationship with
[1:51:25] the instances of the other entity. One means maximum. So maximum means it says that one project can be worked by only one developer. So 4 5 6 is worked by B. So that's all. You
[1:51:40] cannot have C also working on 4 5 6. It says that maximum one instance can have a relationship with maximum one instance of the developer table. So one instance
[1:51:54] one row can have relationship with only one row So minimum is zero either no relationship or at max one one. Okay. So we are done with
[1:52:09] part. So that's do is I'll just give you the brief the very very brief of the whole thing that how you can create the database, how you can
[1:52:22] create a table, how you can insert the data into the table. And then later on will deep dive into each and everything. For database also what all commands we have, for tables what all commands we
[1:52:35] have. So we are going to deep dive into everything, but for now let's for 10 15 20 minutes let's practice or let's see the holistic view of using MySQL. So I want everyone to launch their MySQL
[1:52:49] So I'm using Windows. So what you have to do is you have to search for MySQL workbench. You have to open it. Now you can launch the labs. So yesterday Rakshita helped you with the launching of the lab.
[1:53:03] If you're still facing any issues, you can post it on the chat. We'll see if we can help you right away. Otherwise at the end of the session, please make sure that you're vocal about it that you're not able to use the lab because if
[1:53:15] you're not able to use the lab, then you're not able to do the hands-on in the class. So I'll try to give you at least some hands-on if not all the time, like at least sometimes I'll try to give you the
[1:53:27] hands-on if not all the time. So your labs should definitely be So your labs should definitely be working. learn with me. Of course, you'll able to learn with me, but then you have to
[1:53:40] practice after the session. But yeah, you always have to practice after the session whether your labs are working or not. So please make sure if your labs are not working, you're vocal about it. And if you have to share your screen and
[1:53:53] end of the session to share your screen and we'll we'll try to solve the issues So I want to see a few done on the chat once you are done with once you are done with opening the lab.
[1:54:10] think the password guys, what's the password? I think it's p s s w o r d. Is it 11 and exclamation mark or is it one? Okay. So see everybody is helping you with
[1:54:23] password. You can use the same password. Thank you everyone. Thank you so much. fine, definitely fine. So workbench is what? It's a
[1:54:39] UI way of working with the databases. You can also work with the databases via commands, command prompt, right? CMDs. So that is definitely not how we work with the databases. We always use
[1:54:53] workbench because that's more friendly and easier to use. data. We'll create the database and anyways,
[1:55:06] if you if you preload the data also guys, if you're using the guys, if you're using the lab provided to you by Simply Learn, so it is get it gets refreshed in every five hours.
[1:55:19] So if you add anything to it, it will get deleted in every five hours. data for every class. Now you can see that we have got one schema. Now this is a default schema.
[1:55:35] schema. Now this is a default schema. So schema is nothing but in MySQL, it is like a database. Now what is a database? You already know. Database is the it's like the wardrobe, it's like the library which can store the digital
[1:55:49] digitally. So we are going to create the database. Yeah, if you're using your personal laptops, as I told you yesterday also, that you install MySQL rather than using the labs because
[1:56:05] it will be easier for you to access it. So Anibrata, it's not when you are downloading or installing the SQL, yeah, it's not only about next, next, have to do. So please make sure that you go through
[1:56:20] Today also I've shared it multiple times. So please go through that video. Now if you do not have the software installed, for you to do the hands-on, please do not start installing it right now. You
[1:56:35] write down the notes. Maybe you're not able to do the hands-on with me, but you can always write down those notes. You can stay with me in the class and you can do the installation etc. after the class. But please make sure that you
[1:56:48] concentrate in the class just because you're not able to practice, 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.
[1:57:01] Well, I have no idea about this this thing, Shri. yeah, this thing unable to launch the remote. So Shri, you can try it with
[1:57:14] Vidya. Yeah. So you can try later. Just after sometime just retry it.
[1:57:26] you're facing any issues with the lab, you can keep it parked for now. So I'll try to finish everything by 10 today, 10 10 5 so that we can take up So you can share your screen and we'll try to help you out.
[1:57:39] So let's continue with our learning for today. And as I told you, if you're having issues with the lab that is that is provided to you all by simply learn, if you're using your personal laptop,
[1:57:53] if you're using your personal laptop, please download and install MySQL. So I will not be using lab. I'll be using MySQL on my personal laptop.
[1:58:05] do is we are going to create a database. So as I told you that today are the we view of creating the database, the tables, inserting the data into the
[1:58:17] But after that, we are going to deep dive into each and everything. So in order to create the database, in order to create create the database, in order to create database,
[1:58:32] is we're going to write the SQL in order to talk to the DBMS. Now, I want to tell DBMS that I want to create the database. So, I'm writing the SQL command in order to create the database. So, I'll say create database and I can give any name
[1:58:47] create database and I can give any name to my database, but this has to be as created 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.
[1:59:03] So, and then you can give any name to your database. So, let's say that I give the name to my database as school, yeah. And then see I can always end my comment
[1:59:16] using the uh uh what do we call this? You can use uppercase and lowercase both. Yeah, using semicolon. Thank you
[1:59:29] both. Yeah, using semicolon. Thank you so much. And if I don't end it also, if I've just got one SQL command, it will still run. Now, in order to run it, we have got this button. Can you see this icon, a
[1:59:43] this button. Can you see this icon, a small icon? Let me increase the size. I think the size is already increased. Just give me a second. increased. So, anyways, so this is the button that
[1:59:57] you need to use in order to execute a command. So, if I just hover on it, it says execute the execute the selected portion of the script or everything if there is no selection. So, if I'm not
[2:00:11] selecting anything, yeah, I'm not selecting anything. If I execute it, it will execute everything, each and every line of code, each and every word that is written on my SQL file. This is what? This is a SQL
[2:00:24] file. You can say SQL file. So, because I've got the previous SQL file, in In case it shows six. In your case, you're using it for the first time, it might be showing one. So I'm going to run it.
[2:00:37] Once I run it, over here, over here, you just simply have to expand the lower and you'll see that create database school one row affected
[2:00:50] and you can see this in green. It means that created. So if you see in red, it means there was there was some error when you were creating the database. Guys, please make
[2:01:03] sure that you are attentive in the class. Otherwise, what will happen class. Otherwise, what will happen is that you may get some errors because you're quite good in number. So if you're not attentive, then you may get
[2:01:15] 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
[2:01:28] cover as a part of this training. So I don't want to cover anything on the surface level. I want to deep dive into the topics. I want to make sure that you understand how things are working. So for that, I want only I have only one
[2:01:41] 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
[2:01:55] if you have noticed I was some dropping, I was deleting the databases. So these are because of that. But yeah, the database has been created. So I want me know when done. So you can just write D also instead of
[2:02:08] writing the whole done. So a few runs will give me help. where you can see the database. Right now, I just want you to be sure that the
[2:02:21] command that you have written is executing successfully or not. So just that you have to write this command Rajni. Yeah, so you have to create a new SQL file in case if your new SQL file is not
[2:02:33] So, this is from where you have to open the And this is a command that you have to run. have to write. Once you're done with writing the command, you can run it from
[2:02:48] writing the command, you can run it from here.
[2:03:00] 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,
[2:03:14] Yep. So the database is not appearing over here. So if you see over here schemas, think of it schemas are nothing but the synonyms of databases so in MySQL.
[2:03:28] here. So what you have to do is you have to refresh it. So this is what you have to press. This is what you have to click on in see that the database has appeared.
[2:03:43] that the database has appeared. So you get to see the output now. I hope that you guys are able to see the database created.
[2:03:58] Now guys, maybe this panel is also collapsed for you. So please make sure collapsed for you. So please make sure that you expand it.
[2:04:15] this. So you literally have to expand.
[2:04:28] view, you can go to panels and over here guys, please make sure that you're looking at my screen. So see output area, I can hide it. So maybe your output area is hidden. So what you can do is, you can go to
[2:04:41] view, you can go to panel and you can see show output area. talking about this one. So I'm not sure for this one side. Okay,
[2:04:55] So I'm not sure for this one side. Okay, yeah. So in view you can see just check, go to panel and click on show sidebar.
[2:05:07] I I hope I've answered the queries or the queries on the chat. able to see the sidebar or if you're not able to see the output panel, you can still continue practicing with me and we'll see at the end of the session
[2:05:20] what is the problem. I'll ask you to show your screen and we'll look into it. At least you can start writing the queries because I've told you that these
[2:05:35] you're not able to see, then you have to share your screen. Okay, so at least you can write the code. Now see this database is created. So you have to use the database.
[2:05:51] database. So how would you select the database? How would you select the database? In order to select the database, either you can double click on the database. See, if I double click, it got
[2:06:05] selected. Or what I can do is I can run the command. So just delete the previous command you can run the command use
[2:06:25] School, that's the name of the database. You can run it and then it will basically select the database. So how you'll get to know that this database is selected? So you can see
[2:06:37] that this is in bold. So as soon as any database shows in bold, it means that this database is selected and whatever or whatever queries that you're going to perform will be performed on this
[2:06:50] database. So just double click on the database or So just double click on the database or you can Now before we go ahead and create the table, I would like to show you let's
[2:07:03] see you can collapse and expand the database. Now database has got something called tables. So these are Yeah, so we were using the word entity entities. So tables, views, stored
[2:07:17] procedures, functions, these are the entities. tables and views. So we are going to cover these two. because we're going to cover everything related to the tables. View is a very
[2:07:31] small topic. It's a short topic that we are going to cover later on. So what we're going to do is just give me a second. highlighted, but command executed successfully. Just double click
[2:07:45] on it. Sheila.
[2:07:58] So you'll get to know all of these things. Yeah, hold your horses everyone. class and we have just started coding. You learn everything.
[2:08:10] 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 don't worry about not able to see
[2:08:24] the output panel, not able to see the schema panel. session. Right now just focus on the learning. if that those options are not working, then I have to look into I have to look
[2:08:39] Okay. So we're are 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, lowercase,
[2:08:52] everything is okay. So let me make it uppercase. So create the table. Now I want everyone to see my screen.
[2:09:17] 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
[2:09:33] 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
[2:09:45] that you cannot play around with the data. Like if you have got four columns, you have to stick to the four columns. Then a column also has a data type. When I say data type, what does that mean? It means the type of data a column can
[2:09:59] So ID can hold integer INT. INT means integer data type. So we are going to learn about all the different type of data types also later. So for now
[2:10:15] enough. Okay. The next column is let's say full name. And full name has a data type varchar.
[2:10:35] and age has got the data type INT. Yeah, age is always a number. So varchar, what does varchar means? Sorry, varchar, we also have to define the number of characters that this
[2:10:47] that at max at max of the full name can be of 50 characters. So, if your full name is more than 50 characters the the table will say no I can't cannot take it.
[2:11:01] So, it can take characters and at max it can accommodate 50 characters. Moving ahead the next column is city
[2:11:16] data type of the city it's again let's say 50. Your varchar 50 and my table the code for the table creating the table is ready.
[2:11:29] table is ready. So, just give me a second. Vidya what you're saying is right varchar can accommodate numeric values as well as alpha numeric values but it is always considered as a string. So,
[2:11:43] what I mean to say is that this is what this is a number this is a number but if you give three in varchar it will but if you give three in varchar it will take it like this.
[2:11:56] matter. So, it will it will make three yeah it will take three not as a number but as a string. Yeah this is what this is a string. So, anyways the code string and varchar is same yeah it's
[2:12:12] it's a it's a technical language. So, string means text. So, if I say string or if I say text it's all about the data type yeah or if I say varchar so in SQL we say varchar right in Python
[2:12:28] so in SQL we say varchar right in Python we say string computer language I'm talking about. So, they can hold strings. Strings can be It can be anything it can be this it can be number it can be any any anything
[2:12:43] like this. So, anyways I'm going to simply remove Now I'm going to run this command. You can see that it got created and I have to refresh in order to see the table created.
[2:12:56] Now if I just expand it, you can see the table has been created. So I want everyone to run this command. I'll wait for a minute, for two minutes.
[2:13:08] showing error, you also read the error. So please delete the above commands that we have run before. Or what you can do is let me also maybe this time I'm not going to delete the command. So I'll
[2:13:20] like. So what I have done is I've created the table. The next thing what we will do what we will do the next thing.
[2:13:34] creating the table. Now we are going to insert the data into the table. You don't need to use the table as such You don't need to use the table as such just like DB databases.
[2:13:52] Aviral, you can share the screenshot on the chat so I look into it what's the problem. So in order to insert the data we we So in order to insert the data we we run insert into command. So insert into
[2:14:04] the name of the table. The name of the table is students. Now I can give the columns of the table. Now that is optional. We'll come to that later. But right now as I told you it's just a
[2:14:16] helicopter view of everything everything a small snippet of the data into the table and query the table. So anyways, I'm going to give
[2:14:43] SQL is not case sensitive. And then I I can give the values.
[2:14:59] Now it's a varchar, it means it's a string, it's a text. So I'm going to give it in the in the single quotes. Amit age any age and city.
[2:15:16] just copy pasting it. So let's say ID is two, the name is So let's say ID is two, the name is Neha.
[2:15:30] more records. So I have to add comma, then again I can Yeah. So you can see that I have I'm done with writing a command for insert for inserting the data into the table.
[2:15:46] So this will insert three rows into the table. Now guys, I'm intentionally going So please make sure that you look at my screen because you may also get the same errors again and again. So please understand how things are working.
[2:16:01] So right now, if I execute it, you can see that it is giving me error. So I want you guys to tell me what is the I want you guys to tell me what is the error? What is the problem?
[2:16:17] that first of all, student table is already So that's okay, I'll talk about the first problem. So there is a problem with the syntax. The syntax is hear me out. This is a separate command and this
[2:16:31] 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
[2:16:47] it is taking both both the commands as one command. So how I do this separation by adding semicolon. As soon as I add this semicolon, you can see that the error that I was getting, you can see the error that I was getting
[2:17:00] it will get resolved because I've added the semicolon. So this was the 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
[2:17:13] exists. So basically what is happening, I'm when I'm running it, when I'm running it, it is executing this statement, but the problem is that the student is already there in this database. Student table is
[2:17:27] already there. So it is saying me that student table is already there. So that is the problem. So anyways, I don't want to run this because I already have the student table. So what I'm going to do is I'm going to run only
[2:17:39] this command. So see, I have to select this command and I have to click on run. So I told you that when you just click on it, it will run each and everything So that's what it says. If you go over here and hover, that's what it says. Or
[2:17:54] what you can do is you can simply select the code that you want to run. So here run it. Perfect. You can see that it got run and Perfect. You can see that it got run and the data is inserted into the table.
[2:18:16] You can just google the shortcut keys. I'm very bad with remembering the keys. There was I think it's control
[2:18:31] So this is a question that is asked to me in every batch and I tend to forget me in every batch and I tend to forget the answer.
[2:18:43] me a second. I'll also I can also google. The shortcut is control plus enter to run the query, control plus enter.
[2:19:06] you're going to give the space with Shweta. Now I am not giving any space. I'm just giving comma. Then full name. Then again comma and so on and so forth.
[2:19:25] command use school. If you're not able to see the this left panel, just you run this command use school and then create the table.
[2:19:41] let me know when you're done with inserting the data. Also, I'm sharing inserting the data. Also, I'm sharing the code on the chat. What about codes? So you're going to use the codes for the varchar Shweta.
[2:19:55] So whenever you're writing the strings, strings are always represented in computer, in in machines, uh whether you're writing SQL or whether you're writing you're working on Python,
[2:20:07] you're writing you're working on Python, C, C++, strings are always denoted with quotes. It can be single quotes or double quotes, but single the strings are always encapsulated in quotes. Always remember
[2:20:21] this. So okay, Shri, so you can try working. Okay, got it, got it, Shri. So control plus shift plus enter.
[2:20:34] I've got three rows of data. I would like to query the data. So yes, I'm going to write the query. So here we go, guys. So I'm writing in the same file. Select
[2:20:48] So star means all the columns, all the columns the name of the table. What is the name of table? The name of the table is students. Again, please highlight it
[2:21:02] and then run it. Do not just run it without highlighting it. Otherwise, if you have the previous code, this will also run and you may get error that the forth. So please highlight it and run it.
[2:21:17] So now if you're going to learn about some of the database commands. So you have already learned most of it, So what we are going to do is I'm just creating a new SQL file. The reason is
[2:21:29] that I will be needing this code and I do not want to delete it because I will anyways, I'm going to create a new database. So you already know about this
[2:21:41] command create database and then the name of the database. database for me. We have already seen it. then it will throw error. You can see over here.
[2:21:59] Okay, so I'll I'll try to keep my pace low. because I thought that we have already worked on this command, but I'll keep it low. So what I've done is I just added a new
[2:22:12] SQL file in order to write the code. Okay. So what I've done, I am creating the So you can see that the database got created. It got created.
[2:22:27] Now if I re-run this code, yeah, if I re-run this code, Yeah, you can see over here. Create database DB, it means the error
[2:22:42] error? The error is that can't create DB 100 because the database already exists. So this is the error.
[2:22:58] a keyword called if not exist. So what I can do is I can say create database if not exist. It means as a word says that create the database only if it doesn't exist. If it exists, then you
[2:23:14] don't need to create the database. No need to create the database. So if I run this command, now see, you know that this database is already created. This database is already created.
[2:23:26] But if I just simply add if not exist to my SQL command, it will not throw error and that makes sense also because now what we are saying is that create the database only if it doesn't
[2:23:39] exist. Otherwise, no need to create the database. So if I run this command, my errors. Yeah. So it is not giving me any errors. It's just say it is giving me a warning. Now over here you can see just a
[2:23:54] warning. So it's not the error but a warning that we cannot create it because because the database exists. So this is the error. This is a successful operation and this
[2:24:06] is a warning. So here all I've done is I've used this keyword if not exist. So I want if you want to do this, so let me know when done.
[2:24:20] have to do is you have to run this command in order to create the database and you can rerun this command in order to see can rerun this command in order to see the warning.
[2:24:35] in order to understand the working of it. previous page, if you have not clicked on the cross. So if you see over
[2:24:47] there's a cross. If you cross it without saving it, it means that you have lost all the changes. But yes, only it's these are the tabs you can hop on and hop off.
[2:25:06] >> Okay Sheetal, I hope I've answered your question. Let me end this command with the help of semicolon.
[2:25:25] I'm going to drop the database. So drop database and again the same database DB 100. So we are going to rerun the commands in order to understand the working of it. So I'm simply highlighting it
[2:25:39] and I'm executing it. The database has been dropped. Now if I again execute this thing, let's see what happens. So why don't you guess So the database has already been dropped.
[2:25:53] So if I rerun it, what do you think what will happen?
[2:26:08] So guys, I'm not expecting the right answer. I'm asking you to answer. I'm asking you to take a wild guess that what will happen. Drop database DB 100. Okay, let's do it.
[2:26:24] 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.
[2:26:36] 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
[2:26:52] exist the same keywords. So if you add this, then sorry, if exist. Drop database if exist. If this database
[2:27:05] exists, then drop it. If it doesn't exist, that's okay. Yeah. So now if I run it, it will show me warning. I'm asking you to do the hands on, make sure that you do the hands on.
[2:27:20] the hands on every single time because we also have to make sure that we cover the curriculum and we cover the maximum concept. But I'll try to give you as class. Now let's recreate the database. Right
[2:27:35] now it's like we do not have even a single database. So let's recreate it. At the end of the session, I'll ask some one of you to share the screen and we'll
[2:27:50] look into this problem. So please do not get bothered of about So please do not get bothered of about the same. Fine. Now let's say I'm deleting this drop database if exist.
[2:28:06] databases I've got in my machine, in my sorry, not machine, in my So let's let me do one thing. Let me create another database. So DB 100 I and now I have created DB 200. So just
[2:28:23] just for the sake of creating, I'm creating two databases. So you can also go ahead and quickly create the two databases. Now let's say that you want to know that what all databases you have got in your
[2:28:37] So what you can do is you can run the command show databases. So this will show you all the databases that you have got in your DBMS. So see, we have got these
[2:28:51] databases. Now you'll see some extra databases. For a information schema, performance schema, system anyways you can see over here. So these are system defined databases
[2:29:04] and maybe I'll tell you about these databases not today, but yeah, in some databases store. So today is definitely not the right day yeah, we'll definitely going to talk about these system based databases.
[2:29:21] Oracle I have never worked on with yeah, show databases. So I've never worked on Oracle and Postgres. I've just worked on MySQL and SQL Server. So you can Google it if it works on
[2:29:34] Oracle or not. Or if you want, I can Google and I I can Or if you want, I can Google and I I can tell you the answer. Okay, no problem. Fine. In the same way, let's say that
[2:29:49] okay, I'm going to use the database. So I'll say use I'll say use DB 200. I mean using this database. Now once I'm done with using this database, I would like to create this
[2:30:04] table. So basically I'm using this database and then I'm creating I'm creating the table and I'm inserting the data into the table into this database. So this we
[2:30:18] have performed previously also. You can see that the table has been created and the data has been inserted into it. So I'm just copy pasting this code on the chat. You can also go ahead and use
[2:30:31] any of the database and you can create the table into the database and insert into the table. So I'll wait for two minutes.
[2:30:46] Go slow, no need to hurry. Like this is your first coding class as such. But later on you'll get used to it. So please make sure that you do not come without practicing in the next class.
[2:31:00] So Fatima, first of all, see I asked you to create created two. So first of all, I'm using this database. So I ran this command. And then I've created this table and
[2:31:14] inserted the data into the table. So you can copy paste this command from the I've shared on the chart and you can run it so that you have got a table created.
[2:31:30] database commands. So now we are going to learn about the data types in SQL. So if you remember when I was was teaching you how you can create a table,
[2:31:43] 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 So here we go. We are going to learn about the
[2:31:58] different different data types that we have in MySQL. Now before we get on to that, I will just quickly tell you that how you can save your code. So one of you was asking me that Tulika,
[2:32:11] save it? So how you can save it is, can you see the save button over here? So all you have to do is, now all of these things are something that if you just try it by yourself, you'll able to
[2:32:24] do it. Because we all are using laptops, yep, since ages. So the a lot of options are same for So you can go over here, you can go to file.
[2:32:37] Yeah, we have got the save option over here also. This is a shortcut. Save script as. So the same options we So what you can do is, you can click on it and maybe you can save it.
[2:32:50] So I'll just quickly show you and then I'll give you time to just try it at your end. So let's say MySQL 100. This is what I'm saving this file. Now I'll just show quickly show you on the desktop.
[2:33:05] Okay, where we yeah, this is the file. MySQL 100. Now if I want to open it, how can I open it? Let me close it. How can I open it? Just like how you use any other software. Yeah, the first
[2:33:20] thing is if you want to open something, you go to the file. So here also I'll go and you can see open SQL script. I literally have to find where I have saved it. Yeah, here
[2:33:36] 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 two minutes. You guys can just try saving the SQL script file.
[2:33:55] different type of data types that we have got in MySQL. please make sure that you listen to me. Very important.
[2:34:11] is char. So char simply means characters. text, string, whatever you want to call it.
[2:34:27] data type is a fixed length string. It's a fixed length string. It can take the values like you can have the char data type, the column can take zero characters.
[2:34:42] 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.
[2:34:55] So if I write let's say ID and if I say char so it means that ID that ID when I insert the values, when I insert
[2:35:09] the data into the table in the ID column, in the ID column, it has to be of two characters. Yeah, it has to be of two characters. So 1 2
[2:35:23] 1 3 3 4 So you're getting again. I'll give you one more example. So let's say that I create a column. Let's say
[2:35:37] Let's say a column is okay, I'll create the column [snorts] code. And I give a char as a data type and I give three.
[2:35:51] It means that whenever I insert the values, insert the data into this column, it has to be fixed length. See, it says it has to be fixed length means that I have it has to be
[2:36:04] So I have to give three characters only, yeah, three characters only or let's say if I give anything else, let's say something else. So I'm just thinking of something which
[2:36:18] I'm just thinking of something which will have the text as such. Again, I give or maybe let me give Yeah, months. Now I create a column months
[2:36:34] and I give the data type as char. And then I give it the length of three. It means that whenever I insert the data, whenever I insert the data, it will have the values like this.
[2:36:52] this, it will give error. Or if I try to insert M, it will give error. If I try to insert MA, it will give error. So it means that we have to enter to the length. It is fixed length. It means that it will not take more than this and
[2:37:06] it will not take less than this. Am I clear with char? I've given you multiple examples. Am I clear?
[2:37:30] you think var stands for what? What does this stands for? So here it is what variable
[2:37:53] varchar data type and let's say this is a column and I say varchar 100, it means that it can take the characters up to that it can take the characters up to 100. So let's say I create a column name
[2:38:13] let's say 50 over here it means that when I insert the values into this column I can give anything. I can give Tolika which is of six characters. I can give Ravi which is of
[2:38:27] four characters and I can give a very big name which is of 50 characters. But if I give a very big name with let's will go error. So it is telling us the upper limit that
[2:38:41] the upper limit is 50. You cannot go beyond 50 but you can choose any number within 50. It can be 1 2 3 4 any number for that matter.
[2:38:53] So this is what varchar is. Yeah, this is what varchar is. Just give Yeah, this is what varchar is. Just give me a second.
[2:39:05] varchar a lot. Now some of the data types you're going to use 90% of the times. So varchar is one of the data type. INT is one of the data type. Anyways, let's talk about text. So see varchar says that I can
[2:39:18] accommodate characters up to 255 only. So that's my upper limit. So if you have a very big something, let's say you have a description, let's say you want to capture review reviews. You know that on
[2:39:31] the customer. So varchar is saying that no, I can capture only 15 255 characters. I can't go beyond it. That's not in my nature. So what you can do is for a column like review or description, you can simply
[2:39:48] make it text. So text can accommodate large text data, So text can accommodate large text data, large text data.
[2:40:01] is blob. So what does blob means? Blob is binary So what does blob means? Blob is binary large object. So do not get uh petrified with this term. Yep.
[2:40:14] This is a very common term that is used in the technical world. So blob simply means any any file for that matter. It can be any CSV, text that matter. It can be any CSV, text file, Excel file. It can be image.
[2:40:29] Yeah, it can be etc. So any file uh is called as a blob file. So you can also have blob files inserted So for that you need to create the column with the data type blob.
[2:40:45] column with the data type blob. INT you already know that INT says And this is a limit of it. You don't need to remember the limit, but it can take negative numbers and positive numbers as well. Positive you
[2:40:58] as well. Now if you want if you know that your column is going to store the integer and the number, right? It's going to be very small in
[2:41:13] number. So usually let's say age. Yeah, age you know that age cannot go beyond maybe 120, not more than this. You know that. yeah that 120 is also like a very
[2:41:25] high age that I'm talking about. So what you can do is you can go with tiny int So let's say that you want to save salary. So for salary int is a
[2:41:39] But when it comes to age or the numbers that you're going you know that they're going to be very small. So you go you can go with tiny int. So you go you can go with tiny int. Small integer.
[2:41:55] big integer really big integer. You're talking about the revenues of the companies like Amazon or what Tesla we have got so many companies with really huge revenues. So int may
[2:42:10] fail because again it has got some upper limit. So then you can go with big int. Let's talk about float. So float is what?
[2:42:25] Float can store decimal. See integer cannot store something like this. If you want to store decimals if you want to store decimal data then you can want to store decimal data then you can go with float.
[2:42:41] Yeah you can go with float. Double is again for the decimal. Yeah you can double is also for the decimal numbers. But double is high float is less precision. So what does that mean?
[2:43:04] nothing to do with my sequel. What does precision means? So precision simply means precision sorry simply means
[2:43:17] precision sorry simply means just give me a second I'll write it. So it means precision over here. So what does it mean? It means the total number of digits stored. I'll write it over here. Total
[2:43:31] number of digit of digit stored.
[2:43:43] So, see, when I say float, yeah, when I say float, so float says that suppose in case of float, let's say that you have got a number like this. So, I'll just write a big number.
[2:43:56] I'll just write a big number. 5 6 7 8 9 10 something like this. Yep. So, what float would do? It will simply round it off. Yeah, it will round it off and it will store something like this
[2:44:08] Yep. Uh 5 and then 7. So, it will round it off. Now, double will also round it off, but it will it will round it off at the higher precision. So, it will
[2:44:20] 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 how many digits both of these get
[2:44:34] stored. So, float has less precision. It means that if you have got a big number, it may round it off like this and double has got higher precision where
[2:44:46] the number get, you know, rounded off like this. So, that's what it means. Yeah, that's what it means. what it means. I hope I'm clear.
[2:45:05] digits stored and lower means fewer digits digits stored. Moving ahead, we have got boolean. So, anybody who doesn't know about boolean,
[2:45:19] you can be very very honest here because I understand that a lot of you are coming from the non-technical background. So, So will give me the clarity how how how do I have to explain you boolean.
[2:45:36] So boolean boolean is a very important data type which stores two values yes
[2:45:48] which means one yeah either you can also denote it with one so yes it's on yes actually it is true so let me write true true means yes. true means yes. So true
[2:46:03] means zero. So it stores only two values that is or false and it is a very important data type. So for example if I just give you one layman example you might have seen
[2:46:19] checkboxes yeah when you're filling the form you might have seen checkboxes so so ideally because I come from the background where I've also developed a lot of application so that's what I'm
[2:46:33] telling you that when you see the checkboxes in the backend type. It means that if you check it it will hold the true value and if you
[2:46:45] 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
[2:46:58] do not have to elaborate on this part so the date will take this format yyyy mm dd format. Time you already know about time yeah it ss. Date time the name itself says it will
[2:47:14] store date as well as time yeah it can store both date so let's say today's date plus today's time so it can store both the things both the things.
[2:47:31] about this more later. Maybe I'll use it somewhere and I'll show it to you. So timestamp is auto date and time system means. It means that if you give any column, if
[2:47:45] you write any column with the data type of timestamp, with the data type of timestamp, what it will do?
[2:47:57] So whatever your system timestamp 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 it takes system based date and time.
[2:48:15] Yeah, current date and time as per the system. Exactly. Very good. data types, all the important data types. I would like to tell you one more So we have got signed and we have got
[2:48:28] unsigned. So let's talk about this also. It's a very easy concept. So see, you can have signed. So all the data types, all the data types are signed by default.
[2:48:41] So what does signed means? All the data types. Now here I'm talking about the data types which can store numerical values. So here I am talking about the data type which can store numerical values.
[2:48:53] So signed simply means that for example, if I talk about Yeah. So by default it is signed. By default it is signed. It means that it can store the negative and positive values. So if
[2:49:08] the negative and positive values. So if you remember from the previous slide, we have got tiny int, small integer
[2:49:21] small integer this minus 128 to 127. tiny int can take the values from minus 128 to
[2:49:33] 127. And if I create a column, let's say I create age column. And if I give tiny hint.
[2:49:49] it is signed. So maybe I not write it. It is signed. So it means that I can store minus 128 up to lower limit.
[2:50:04] Fine. What is unsigned means? Unsigned means that if I make it unsigned. So if I again say age So maybe okay, let me write it.
[2:50:21] The writing is so bad. Okay, tiny. pad. Anyways, tiny int and then if I give unsigned
[2:50:37] extra cushion. How it will give me extra cushion? It means that I'm explicitly telling SQL, hey SQL, I want to use tiny int but I do not want this negative numbers. I I know that age is never going to be negative. Well, age is never
[2:50:51] going to be 255 also, but yeah, that can happen if we are working on tortoise age. I think tortoise can live till 300. So anyways, tortoise can live till 300. So anyways, so let's say that I'm just telling that
[2:51:06] 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 numbers? So that's how we can use it. Yep. So
[2:51:18] here you can see the limit is 0 to 255. So whatever numbers we had over here So whatever numbers we had over here they have been added over here.
[2:51:31] calculation. This will come around to be 255. So we are saying I do not want negative numbers. So do not waste my range. In negative. Yeah. So I just want the positive numbers. So
[2:51:45] unsigned will help me to increase the range.
[2:52:09] because again we are going to create a table and this time for the table because we are going to just play around with the databases with the data types.
[2:52:21] yeah, the table is going to be a big table. So right now I'll ask you also to copy paste it, but after the class please make sure that you type all of these statements. So what you can do is you
[2:52:35] can I I'll share all these notes also with you. I'll share these PPTs also 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
[2:52:48] Wordpad Notepad file and you can keep saving those comments. See, I want you guys to be very very active during the session because when you're active, when you're participating on the chat, when you're doing the hands on, let's
[2:53:01] say you're just picking up the code and pasting it and saving it in your Notepad What happens is you tend to be attentive. But if you're just lying down and listening to me, then I'm sure you're
[2:53:14] want that to happen because each and important. So anyways, here we go. This is a table. right? So I am just deleting all this.
[2:53:28] So this is a database and here I'm going to create this table. So I'll just quickly run this command. Just give me a second.
[2:53:42] So the table has been created. Now you can see that this table has got myriad data types. So it has got int. Int is unsigned. What was unsigned?
[2:53:56] Let's quickly see what was unsigned. Only positive numbers. Only positive numbers. So it gives a really huge question here. If I just remove the negative numbers, you you can do the calculation. So it
[2:54:08] something around four plus plus plus. numbers. Varchar you already know. Okay. We have got something called small int also which is I think smaller than
[2:54:22] tiny int. Then or maybe let let me make it tiny Yeah. Then decimal. So I'm giving the precision over here.
[2:54:35] So eight two. What does that mean? Eight two means that I can have digits like one two three four five six seven eight. And two is what? After decimal how many digits? So I can have one two.
[2:54:55] Then I've got boolean which can have true or false. table. And then I have to insert the data. So inserting the data code is definitely more cumbersome than
[2:55:10] creating the table with so many data types. So I want you guys to see my screen. So here we go. I'm inserting the data. This is varchar. Yeah. This is int. This is varchar.
[2:55:27] This is tiny int. This is again tiny int. So this one is This is again tiny int. So this one is unsigned. This one is signed by default. decimal.
[2:55:39] This is float. Boolean. I'm giving one. I you that one. False or zero. Then I I'm giving date. What is the format?
[2:55:52] YYYY MM DD, that's the format. And here I'm giving date time. Yeah, and in the same way I've got multiple records. So I'll insert it. Records inserted and let me quickly query the table.
[2:56:06] Select star from products. Product. Please make sure that you give right name of the table.
[2:56:18] And it is done. So I'm going to give you this code. paste. You know, that I definitely I do not want you guys to copy paste. So run this command.
[2:56:38] Okay, yeah, very good question, Sanskar. So see guys, in the last in the last insert command, we gave the column name. here. If you see, I have skipped the column
[2:56:52] So when can I skip the column name? I can skip the column name when I know that I'm going to insert the values into each and every column and the sequence is going to remain same. Yeah, the
[2:57:05] sequence what will be the first value that I'm giving is for product ID. The second value is for
[2:57:20] know that the sequence and the columns, I'm giving values to all the columns. Yeah, I'm when I'm inserting the data, columns.
[2:57:32] Also, I'm maintaining the sequence of the data inserted data with that of the columns that I've got over here. So if I that that's the case, I can skip
[2:57:46] the name of the columns. Yeah, I I can skip the name of the columns. So we are going to talk about this thing more, yeah, that when you can skip and when you cannot skip. I we are going to talk about this more.
[2:57:58] right now our agenda is to learn about the data types, yeah. The agenda for writing this code is to understand the data types. So yes, now I'll I hope that I've given you the code. Yeah, so I've given you
[2:58:13] you the code. Yeah, so I've given you the code now. then it will give you error. For example, if I try to insert laptop over
[2:58:26] here. Yeah, so let me do that. Yes, let me I'll just give a string over here. Let's say I am giving a string over here.
[2:58:40] I'm giving a string. It's an int integer. So it will throw error. Now if I run this, it will throw error. It has thrown error.
[2:58:55] It has thrown error. See. have to make sure that these sequence this sequence matches with this sequence. This is very important.
[2:59:15] the table. You just check the format of the table, the data, yeah, and then query it. And we are done with today's learning.
[2:59:30] Sheila, but I'm going to provide you with the all these slides also, but yes, I'll hold for some time on this slide. slide. So
[2:59:45] in In to create a table, this is a basic syntax of creating the table. So I want everyone to see my screen. Now you already have the idea of it because we have created multiple tables in our previous session. So we
[2:59:58] tables in our previous session. So we start with a keyword create. start with a keyword create. Then we have got the keyword table. Then we have got the keyword table. And then we give the table name.
[3:00:13] we give different different columns and different different data types of the Now this you have already seen, right? But apart from this, you can also add Now what are constraints? We are going to look to that today itself. So let's
[3:00:30] table first. So what you can do is you can open a new file. And the first thing that you can do is you can create a database for today. So let's take 28th.
[3:00:44] I'll just give it a name Feb. Yeah. can see that the database has been created. So either you can select it by double clicking on it. Now
[3:00:58] these things we have already done. So I'm quickly telling you. It's like I'm reiterating. So that's why I'm pretty quick over here because the things we have which we have already covered, I would definitely be little bit quick
[3:01:11] over those things. Now the database is created and I'm going to use this database in order to create the tables. So I'll wait for a few seconds for everyone to have their database created.
[3:01:28] So rather than you know writing the same code again and again, typing the same some of the code. So you know that how we can create the table. So I'm just taking this code of creating the table.
[3:01:44] Yeah. So you can see that I've got this table. This table has four columns.
[3:01:57] I would also insert the data into this table. Now guys, if you have missed any of the class previous class, I'm afraid and I'm really sorry for the same that I'm not able to repeat the concepts because we
[3:02:10] have got limited classes. These are not unlimited classes. So every class has an agenda associated to it. So please make sure that you go through the recording. So you'll find recording in the LMS portal.
[3:02:28] student table. I'll copy paste the code. Hold on guys. 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
[3:02:41] created. Now I'm going to insert the data into this table. Data got inserted. would like to show you, that I would like to tell you.
[3:02:55] the column. So if I do not pass the name of the column, let me delete the names of the column and I'll just do one thing. I'll column and I'll just do one thing. I'll just add one more row of data.
[3:03:08] Yeah, I'll just add one more row of data. and I'm okay with the other things. Yeah.
[3:03:22] Yeah. So if I execute this, see it got executed. So this is fine. But if let's say that I've got some data of some of the students. I've got a data
[3:03:36] of some of the students. I've got a data of a student with a name, let's say Mark. But I do not know the city of the of Mark. So let's say this is the data I have.
[3:03:48] Yeah, this is the data I have. Now listen to It's very simple. Now, if I run it, it is throwing error. So, you get that that if you have the full data,
[3:04:00] if you have the full data, when I say full data, I mean to say that you have table. So, if you have got the full data, then it works perfectly fine. But if your data is not full, right now
[3:04:15] I don't have full data, right? Then I have to explicitly define the columns that I'm going to use. Yeah? For example, ID comma name comma age.
[3:04:30] my SQL that five should go in ID, name should go in mark should go in name, and so when and so forth. Let me execute it and now it is getting executed. So, you got my point. It's a very simple
[3:04:44] point that when you have the full data, then you can omit the column names from here. But if your data is not full, yeah, if you have partial data, incomplete data, then you need to mention the column
[3:04:59] names. That's how the syntax goes. Now, you don't need to insert so many records. The I did that because I wanted to show you, I wanted to explain you. I'm just sharing this code with you all. So, here's a code for creating the
[3:05:14] So, here's a code for creating the table. for inserting the data into the table. Yeah? So, you can copy paste this code,
[3:05:29] avoid typing each and everything in the class because if you type each and everything, see, I don't mind it. You need to understand my predicament also that if I give you time for typing each and everything in the class, then I may
[3:05:43] not able to cover all the concepts and I want to cover maximum things. I want to give my maximum knowledge to you all, share my maximum the maximum knowledge share my maximum the maximum knowledge that I have with you all.
[3:06:03] name Kunal. In this table I've just got four columns In this table I've just got four columns as you can see.
[3:06:28] over here Kunal is that so? So Kunal we are going to look look into that also that how we can alter the tables. Let's not do it right now otherwise everybody will get confused but we'll see that you have got some data your
[3:06:42] structure already created. So how you can alter how you can play around with can alter how you can play around with it. So I told you that
[3:06:55] when you create the table you can also add the constraints to the table. So what are constraints? So constraints are think of it they are nothing but the rules that you apply on the column.
[3:07:11] Yeah, they are nothing but the rules that you apply on the column. So we learn about few of the constraints around these constraints. So the first or let's do one thing we'll
[3:07:27] one by one rather than going through the whole thing we'll go one by one. So the first constraint is not null. So see as I told you think of it
[3:07:39] constraints are nothing but the rules that you apply to the column. Now if I say that let's say I have a have a column ID column and I want to apply some rules so we learn about atomicity right? We learn
[3:07:52] acid. So if you remember we learn learn about consistency. So means that you are you are basically applying the rules to your database. So here also we are doing the same
[3:08:06] thing. We have got multiple columns in our table and we simply want to apply some rules to those column. So let's see I've got the ID column
[3:08:20] and I want that whenever you're inserting any of the students record the ID shouldn't be null. You're getting the ID cannot be null. You're getting the ID cannot be null as simple as that. So I can use not
[3:08:33] null constraint for that. So you can see what does it does it disallows null value. So it means that the value should be provided. Yes, it means that you're making this column required as simple as that.
[3:08:47] Yeah, if you enter any of the row this column should have value. column should have value. Okay. So we'll do one thing we'll just give me a second. Yeah.
[3:09:01] Yeah. So we are going to create a table
[3:09:17] So I'm going to create a table. Now we are going to create a lot of tables
[3:09:29] my students. I'm just naming it anything that's fine. Always remember that what we are trying to learn. Now this table may have my multiple columns. So one of the column is
[3:09:43] ID and the second column is let say name. Okay, I'm not going to make it a name. Okay, I'm not going to make it a very big table. columns that not not null. How I can make that column as not null?
[3:09:58] all I have to do is I have to write not null So, it means that in order to add the data into this table, I have to
[3:10:13] make sure that the ID is not null. If I want, I can do it for name also. Let me right? So, let me do it for name also. it means that in order to insert the data, both the
[3:10:29] create this table. The table has been created and I'll just quickly say insert into I want everyone to see my screen. If I give one comma any name, for example a, let it be. I am just giving any name for
[3:10:44] that matter. Doesn't matter what name I'm giving. Yeah. So, insert the data, see it is getting inserted. Let me also show you the data. Select staff from my students.
[3:10:57] my students. It's working absolutely fine. Yeah, you can see. But now what I'm going to do is
[3:11:10] I will insert the data, but let's say that I I'm skipping a. Yeah, I'm not giving the student name. Yeah, I'm not giving the student name. Yeah, I'm skipping or let me give null
[3:11:24] in a. So, let's see if it works or not. You can see it is not working. So, it says that column name cannot be null and this will happen for the other column also, for
[3:11:37] Sorry. So, this also cannot be null. As simple as that. And the reason is very simple that I have made
[3:11:50] 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
[3:12:03] allow null values. So I'll wait for a minute for everyone want you guys to write the whole code. Maybe you can take it from the chat what you have. Otherwise, it will take a lot of time.
[3:12:19] What do you understand by unique? Distinct values, yes. So in very simple words, if I have a column, so I will not allow I will not allow duplicate values in those columns.
[3:12:34] So any guesses what those columns could be? Like you might have seen in your real life here and there. ID, very good. Yes, ID. Those who belongs who are from India, maybe PAN card or passport number, everybody
[3:12:49] would understand Aadhaar number. How about email? Yeah, email should be unique, phone number should be unique. record that is being entered has a unique email
[3:13:03] that is being entered has a unique email or unique phone number. has just got one column. It's okay. We are we are learning the concepts, so we lot of columns. So I'll just quickly run this code.
[3:13:18] So I'll just quickly run this code. Sorry, just give me a second. I'll run this code. You can see that the table has been created. this [email protected],
[3:13:32] it will get inserted. This is the first time I'm inserting. But if I again insert, maybe I can, you know, re-execute the same statement. So if I again insert, it is giving error. Why the error? Because it's
[3:13:45] 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 are going to see,
[3:13:58] you're going to encounter. So let me quickly give you this code on the chat. You can copy paste the code. Again, do not should definitely do a lot of typing of the code after the class.
[3:14:13] Right now we are copy pasting so that we learn maximum concepts. But yes, after the class you have to make sure that you do a lot of practice by typing each and every line of code.
[3:14:25] line of code. I'll wait for a maybe a minute. Now we'll learn about the next constraint that we have that is primary key. So let me open the notepad because I would like to
[3:14:38] write something around it or maybe I can write over here itself. It's fine. write over here itself. It's fine. So
[3:14:50] 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 primary key. Now you already know about it. I have we
[3:15:03] have already discussed it. So I'll quickly re discuss it. So primary key is a unique identifier of each row. For example, if I talk about human beings, so I think our genetics, our DNAs, these
[3:15:17] are the unique identifiers, yeah, that will or our fingerprints, these are what the unique identifier. So for every row also, if you want maybe some column to uniquely identify that row,
[3:15:30] so we can declare that column as primary key. So I'll also write it. is a primary column, primary key column, it means see that's what it means that
[3:15:44] it is uniquely identifying it, right? Uniquely identifying the row of data. uh every row that that column will take like every piece of data that column will take will be unique. Also it cannot
[3:15:59] be null. So it doesn't take nulls. As simple as that. It's a primary key. It doesn't take nulls. So So example, we are uh storing the data of humans, yeah, of humans.
[3:16:12] Let's assume, right? We are storing the data of human humans. Now, you know that names can be common, then most of the things can be common. But let's say thumbprint. So thumbprint is
[3:16:26] not common. So this we can make this as a primary key. every human being. Yeah, and we we also do not want it to be null. So if you are inserting any data,
[3:16:42] it it cannot contain null value. So that's what primary key says. So that's what primary key says. Okay, there is one more thing
[3:16:54] table only one primary key is allowed.
[3:17:07] It means that if again I'm storing the data of human beings, if I've said that thumb print is the column which is a primary key, I cannot have, let's say, passport number as a primary key now. So a table can have only one primary key.
[3:17:24] key, composite primary key that we'll talk about. But it can have only one primary key. So those who may ask that composite primary key, we'll come to that later. [sighs]
[3:17:38] For now, you understand this part that it can have only one primary key. it can have only one primary key. So I'll just quickly take the code. So I'll just quickly take the code. Yeah.
[3:17:56] this table employee. It has got two columns, employee ID, name. Now employee ID is a primary key. I'm declaring it as a primary key. Let me create the table.
[3:18:10] Now, I am inserting the values in it. Yep. So, you can see that this is a first value one and Ravi. So, employee ID this one is what ID yeah employee ID.
[3:18:24] ID yeah employee ID. So, anyways so this will work. Let's see 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.
[3:18:37] Why? Because it's a primary key and primary key makes sure that each and thing is unique. Also
[3:18:50] again it will give error. So, that's what I wrote that it has to be unique and it cannot take null values. It cannot take null Now, let me do one thing. I'll just make a small change over here. I'm just
[3:19:05] changing the name of the table and I just want to show you that if we can have multiple primary keys in a table or not. multiple primary keys in a table or not. So, let me execute this.
[3:19:21] says multiple primary key defined. Always read the error. I'm not sure if screen is visible to you or not but yes it says multiple key defined. So, it simply means that your primary key
[3:19:36] can only occur once in a table. You cannot have more than one primary key in the table. So, let me again yeah make it to the previous code. Change it
[3:19:48] we are good. So, I'll share this code with you before you run it maybe let me also tell you one thing. So, what is the difference between unique and primary key? See first of all I can have multiple
[3:20:04] See first of all I can have multiple unique columns. So, I can have 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
[3:20:17] exist once. Yeah, I cannot you can once you have created one once one column is given the primary key constraint, you cannot have another that. You have already seen it. But unique, you can make as many columns
[3:20:31] unique as you want. Also, if you have a unique column, it can like one row
[3:20:43] So it's unique, right? So one row I can give null. cannot give null because null will not be unique anymore. You got table. So for the first row, I can say null
[3:20:58] So for the first row, I can say null and maybe I can give some it will not be unique. Yeah, both both the rows are having the same value,
[3:21:12] null. So it can take one row at least null. Primary key says, no, if there's a null, I can't take it. So anyways, I'm sharing this code with you all. I want all of you to try this,
[3:21:25] you all. I want all of you to try this, play around with it, copy paste it. composite key when you have a composite primary key.
[3:21:37] So primary key is one only. It's a combination of the primary key. It's a combination of the column that makes the primary key. So we we are going to look into composite also, not right now.
[3:21:56] write down these notes, these points. See, I'm going to give you these notes, but I'll give you these notes at the end of the class, not right now on the on the last class or second last class. So you'll have everything handy.
[3:22:19] This another table is storing the passport number. numbers that we have. And for these different passport
[3:22:31] numbers, we also have maybe this is not a very good example. I'm so sorry. Let me give you some other example.
[3:22:45] no, I'll give you some other example. So I'll give you the example of orders I think that is a best fit. Now let's say that we have got a table Now let's say that we have got a table customer.
[3:23:01] the details of the customer like ID, name, phone number, etc. So ID is what? This is a primary key. This is a primary key
[3:23:15] for this table. It uniquely identify a customer. Okay. Moving ahead, we have got the orders table.
[3:23:31] So it has also got multiple columns. It has got order ID and let's say the date of the order maybe something else, the product. And maybe something else, the product. And then it has got the customer ID.
[3:23:44] Yeah, who made the order. So this customer ID So it means that over here I have got some data. Let's say I've got the data
[3:23:59] like I've got a customer one two with name A and B. So this table, now understand this thing, this table thing, this table will have values
[3:24:14] ID column. So it can have let's say I've got maybe one another value. So it can have 1 2 3 only. It cannot have any other customer ID. It cannot have four as a customer ID because that doesn't exist
[3:24:27] So this is what? This is a foreign key. What is a foreign key? Foreign key What is a foreign key? Foreign key is a key which is a primary key of the another table. So ID, customer ID is a primary key of this table
[3:24:42] in the orders table. So what is the significance of the foreign key? Foreign key makes sure that whatever values that you're filling in over here. You're in over here should come from this column.
[3:24:59] Yeah, it should always come from this column. So if I try to fill four over four, we don't have any customer with the ID four. But if I give one two three values over here again and again orders made by one, orders made by
[3:25:15] customer ID two, it will happily take it. But as soon as I give let say five, it will give error. No no no, I five is not here. It says five is not here. So foreign key think of foreign key as a loyal, it's very loyal to the primary
[3:25:31] 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
[3:25:45] loyal to the primary key. And that's why we call it as a um So this is the significance of having the foreign key that it only takes the values
[3:26:00] which are present in the primary key of the another table. Yeah, it cannot take practical part of it and you'll be able to understand. It's very easy.
[3:26:13] to understand. It's very easy. So here, okay, here I've got two tables. So I'll just quickly create these two tables. I've got these two tables. Department has got
[3:26:28] has got two columns and staff has got two columns and staff has got three columns. Now if you see over here, which is a primary key in the department column quickly? Now I'm asking you such
[3:26:40] column quickly? Now I'm asking you such simple questions. answered. If you see here, we are defining a foreign key. Yep. So department ID and here we are
[3:26:55] saying the department ID and this is how we do it. This is the syntax of it. So we are saying that we have got a foreign key. This is a foreign key. This is a foreign key.
[3:27:07] And how it is related to this table, we are also telling that yeah, this is a foreign key and it is related to so references name of the table and the primary key of the table.
[3:27:22] You got that? So basically we have declared a normal column and then we are telling that this column is a foreign key. Yeah, which this is a foreign key column. Then it's a foreign key column, it means
[3:27:36] there should be some primary key in some table. So we have to give that detail also that which is a table and which is the primary key. So how do we do that? We use references. Then we give the name of the table. This
[3:27:48] is what? This is the name of the table and this is the name of the primary key. So it means it means that this foreign key is for
[3:28:02] it means that this foreign key is for this table and in this this column. Now we are going to insert the data. Guys, I want everyone to be very very attentive over here so that you understand how things are
[3:28:14] happening. See, now I'm going to insert the data. inserting only one row. Yeah, that's fine. So I've got department ID as 10. I've got department ID as 10.
[3:28:29] So I've just got one department and this department [clears throat] name is IT and the ID of the department is 10. Now I'm going to insert the data into the staff table.
[3:28:45] So see what I'm doing is I'm saying I've got one staff. departments we have got the staff So I've got
[3:28:57] staff ID one and this staff goes in the IT department yet department ID 10. So this will very well work. this will very well work. Yeah, because 10 exists over here.
[3:29:11] You get getting 10 exist in the department table. So this value exists in the department table. This value exists in the department table in this column. But if I give 20
[3:29:26] this will not work. Why this will not work? Because 20 doesn't exist here. 20 doesn't exist. If I simply query select staff from department it just has got one
[3:29:41] You just saw that we have inserted only one row of data. You can see over here. one row of data. You can see over here. So it has just got one row of data.
[3:29:54] have those values sorry foreign key make sure the department ID this is a foreign key. It can only contain those values which are the part of the primary key of the department table.
[3:30:07] So that's how it works. So you can see that again I've got the student table. I think it will throw error because I already got the student table. So I'll just quickly make a change to the name of this table and
[3:30:19] already there will throw error that this table already exist. So you can see that the table has been created. Now, the I've given a check over here that
[3:30:32] the age should always be greater than equal to 18. if I insert the data which is greater than 18, it will happily take it as you can see. But if I try to insert any data which is
[3:30:45] less than 18, yeah, if it is less than 18, it will So, if you're seeing the error as you can see on my screen guys, if if you're getting the error, this is how the behavior should be, yeah.
[3:30:57] So, you should definitely get the error. This is how we're expecting it to work. So, it shouldn't allow anything lesser than 18, as simple as that. If I give 18, then it will allow. See, now it will allow, it will work.
[3:31:11] 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 So, I want everyone to quickly run this code. It's a very simple one.
[3:31:29] software not unable to execute anything. So, Pushkar, did you uh followed the steps that I uh so there was a one video that I shared Did you follow the all the steps religiously?
[3:31:47] not that you just install and you know, you just click next, next, next and So, there is some setup that you need to do.
[3:32:01] also, just that you have to follow some steps. for everyone to quickly try this constraint, check constraint.
[3:32:15] The next constraint that we have is default. So, till now, every column that we are we have created, we are we were inserting the data in it. Now, let's say that if we do
[3:32:29] not get any data in it, let's say we are not inserting any data in it default value. For example, let's say that we have got a table, student table.
[3:32:41] Now student table, now this is a student table of some school which is in India. So here we have got a country
[3:32:53] column. So if we are not giving the value explicitly for the country, it will automatically take India. So basically we are giving a default value to country column.
[3:33:07] So default value means that if I am explicitly providing the value while inserting the data, if I provide USA, then it will take USA. I provide India, it will anyways take India. But if I do not provide anything,
[3:33:19] then by default it will take India. Again, if you have not understood, that's okay. Rest assured, once we write the code around it, you'll able to So let me take the code. So I want everyone to see my screen.
[3:33:34] You can see that I've created the orders table. It has got one column that is status. This is a data type of it. And what is the default value of this column, guys? Now I'm asking you such
[3:33:48] the session. I do not want you guys to uh you know daydream during the session. So I've just got one answer. I repeat my question.
[3:34:03] column? I'm using the default constraint it's pending. Right? So I'll just quickly create this table. Now here I'm inserting into orders
[3:34:19] anything. So if I run this, see, it got Let me quickly query this table.
[3:34:31] 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,
[3:34:43] Let's say I give a value closed, so it will happily take new values also. It's it will not take. Yeah, this ran successfully. If I run this, First one it took pending because if you're not giving anything, in very
[3:34:58] anything, it will take the default value. But if you give anything, then it will take that value. example, let's say that you go to Amazon and you create your profile over there.
[3:35:14] there. Now when you create your profile as a definitely you do not give any ID. Usually what you do is you give your name, address, phone number, email ID, and you're done and dusted.
[3:35:28] let's see Amazon wants to give you some ID, yeah, some auto-generated ID. So we have got this auto increment. This is a wonderful constraint in order to
[3:35:41] is a wonderful constraint in order to um So auto increment as the name says that if I have got a column, let's say the name of the column is ID. If I make it auto increment, it means
[3:35:57] that automatically it will like the first record, let's say take So the second record will take the value. Let's say I say that it should increment by one. So that is what I've given as a rule.
[3:36:10] So the second will take two. The third record will automatically take So the this column will automatically um generate the value for itself without
[3:36:24] user explicitly giving. Yeah, and it will increment increment the value every single time. So again, we'll understand it with the better idea. Now you can see over here.
[3:36:38] Um now this is a great example why because here we are creating the table and see one column you're making it primary key also and you're also auto auto incrementing it. So that is very much possible that one
[3:36:53] column you're giving two rules in it. Yeah it happens right? So this one column has got two rules. First of all it's a primary key it's a primary key also it's auto increment. So uh we need
[3:37:05] what you are seeing is right. So auto increment is that it will automatically increase. The value will automatically increase. So I'll show it to you. So first I'll create the table. Then I'm inserting two two products in
[3:37:18] it. Now if you see when I'm inserting I'm just inserting the value in the product name column. I'm not inserting any value in the product ID column. I'm not inserting no because
[3:37:32] this column is self-sufficient to auto generate the value for itself. So I'll quickly run this and let me show you the result.
[3:37:44] 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 maybe keyboard
[3:38:02] show you. You can see. So automatically it it is getting incremented. So by default it takes one. The initial value is one and the
[3:38:14] The initial value is one and the increment value is also one. You can so what you can do is
[3:38:27] screen. Yeah then I'll give you time again. So I'm just making the changes to the name of the table because this table already exists and if I recreate the table with the same name,
[3:38:39] it will throw error. So definitely I can remove this. Now product ID is auto incremented column but it's not the primary key.
[3:38:51] Now let's say yeah I'll come to that also. Let's say you want to increment it with five. So what you can do is over here this is the syntax of it. You can give auto increment equals to five.
[3:39:06] key. I'm so sorry for that. So it has to be primary key. I think SQL server we we a table auto increment column without having the primary key. So I got confused because different databases different rules but
[3:39:20] yes it has to be primary key. Now you can see that I'm saying that auto increment by five. Let me in let me quickly insert these products. Just give me a second. Okay sorry. I have to insert the product.
[3:39:34] I'm also changing the name of the because I've given auto increment equals to five. The first value it is taking five and six seven. This is how we are defining the first initial value.
[3:39:48] And you can also change this 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.
[3:40:01] And then we have got at the rate auto increment. So there's there's a property that we can define when we alter the table. Yeah Rajpal if you delete the second row, the next increment will be
[3:40:13] the next one. It will not be the previous one. previous one. Yeah it will always take the next one. Abhinav as I told you that we have a property so I would maybe I would like
[3:40:28] to talk about that property when we learn about how we can alter the tables learn about how we can alter the tables not right now. just you right away. As I told you, right now it was asking
[3:40:43] for the primary key. I need to check because in SQL server you can have an auto increment column without having it to be the primary key. But here in MySQL, when I was trying to create it with there, it was asking for
[3:40:56] the it to be a primary key. I'll just check it if we can auto increment a column without having it to be primary key. In fact, in MySQL auto increment feature wasn't there till some time back.
[3:41:09] We had it in SQL server databases, but not in MySQL. Now we'll learn about composite primary key. So what is the meaning of composite? What is the meaning of composite?
[3:41:22] have said that you there might be something off with your with your syntax. minute and then we'll move to the composite primary key. I do not want
[3:41:36] your questions to be unanswered and you're still trying and you know hanging around this part. I do not want that. So we'll wait for a minute and then we'll move to the composite Now we'll learn about composite primary
[3:41:48] keys. So what is a composite key? So here what happens is if I give you this example, just give me a second. Let me see the example that we have got. So let's say you've got a
[3:42:01] table, yeah, you've got a table. Now in that table as a primary key. You're getting it? It is not possible
[3:42:14] to define one column as a primary key. Let's say suppose that I've got a table, I've got a table that I've got a table, I've got a table and the name of the table is let's say
[3:42:26] Yep. Now here I'm having the name of the and then I've got more details about the country. Let's say the latitude, longitude, all of these things and then the code, country code also
[3:42:42] I've got. Now I want to define a primary key. I want to define a primary key. Well, latitude, longitude can also be a primary key because every country will have distinct latitude,
[3:42:57] else. Let's say um number of states, yeah, something or number of cities. So some some details about the country.
[3:43:09] key. Now I may make see number of cities and number of state cannot be the primary key for sure. So what I can do is
[3:43:24] 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.
[3:43:37] So what I can do is now name also uh I think there is one country, I don't that. Now countries may share some sometimes names also. They can have hypothetical, I know. So what I can do is now I want to define
[3:43:53] So what I can do is now I want to define a primary key. the combination of two column as a primary key. So I can say my primary key
[3:44:07] is name as well as country code. So combination of these two are my primary key, composite primary key. You're getting the combination of two columns
[3:44:25] uh is is a primary key.
[3:44:41] okay. So let's say I've got orders table. I've got order ID.
[3:44:54] I've got let's say product ID. And then I've got name, customer name
[3:45:06] etc. I've got these columns. So I have to define a primary key. Now the order ID is also repeated. Product ID definitely will be repeated. It's a orders table. Quantity definitely will be repeated.
[3:45:19] will be repeated. So I am in in a soup. I am in a fix that from where should I get a column which is completely unique. Now I don't have Let's say order ID is also getting repeated.
[3:45:31] So order ID is actually repeated sometimes. So let's say I make an order. I'm just giving you one context of it. And in that order I You getting? So I'll have the order ID repeated. So 1
[3:45:47] 2 3 1 2 3 1 2 3 product 1 2 and 3. So that is again a problem. Now I have to define a primary key. What should I So what I can do is I can use a combination of the
[3:46:01] column. So let's say I make these three column combine as my primary key. So what does that mean? It means like here me It means that my order ID is
[3:46:15] 1 2 3 product ID is 1 and let's say quantity is maybe 1 1 1 let it be yeah. 1 So the combination of these three should be unique. So if you see this is
[3:46:28] So if you see this is I'll just take the second one. Now if you see the combination of these three is unique. It is always giving me a unique value. Yeah, if I go with one more, let me give
[3:46:41] 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
[3:46:58] combination is unique. So, that's what composite primary key does. It's same as that of the primary key. No difference. It's just that the primary key is made up of more than one column. Yeah, it is made up of more than one column.
[3:47:17] enrollment table. I've got a primary key and the primary key is a composite primary key. Why? Because here the primary key is made up Because here the primary key is made up of two columns.
[3:47:32] screen. So, I'm inserting the data in it one 101. So, one and 101 together will make the primary key. It will very well work. Now, again see one is repeated
[3:47:46] but course ID is different. Yeah, so 1102 again it will work for me because the combination of two is unique. Always unique. Here also the combination is unique. See
[3:47:59] if I run it, it will run. But let me do one thing. Let me again run insert and let's see the product of the student ID is two and the course ID is also 101.
[3:48:15] more unique. It has already been used. It has already been inserted in the table. So, if I run this it will throw error that it's a duplicate key because this combination
[3:48:29] 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
[3:48:42] that I am again a primary key but I'm a combination of multiple columns. In order to be a primary key. I'm a combination of multiple columns. So I want you guys to run this code. The
[3:48:54] last insert statement will not run. Yeah, it will throw error.
[3:49:06] but we'll see that what all we can do with the select statement. So we start with the keyword select. Then we we were giving star
[3:49:21] the name of the table, right? So star basically means that return all the columns. So star means all the columns
[3:49:34] of the table. Now let's see that you do not want to return. You do not want to see all the columns, rather you want to see some limited columns. So what you can do is you can say select
[3:49:46] column that you want to be the part of the output. So you can give the name of the column again from and then table. going to create a a table
[3:50:01] that table. So the students table or not. So let me
[3:50:15] quickly drop the table. Drop table and then the name of the table. So the name of the table is student. If it is there. there. As you can see, it's giving error.
[3:50:29] So if you guys already have this table, please make sure that you drop it. So this is one of the way to drop it. Other way is that you can go over here and you can right-click on the table and you can say drop table.
[3:50:43] you can say drop table. So we are going to recreate in case so we have created students table. There's S added to it. student table. So if you already have the student table, please go ahead and
[3:50:55] drop it. And once it is done, And once it is done, I want all of you to recreate the table
[3:51:07] So we have got the table. Just give me a second. I think it did 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
[3:51:19] got this much of data, almost six rows of data in the student table. So I'll wait for a minute for everyone to have their student table ready.
[3:51:33] SQL file. So I've got this data also in front of me. The table, the table structure, the data that I have inserted into the table.
[3:51:45] So as this is something that we have done 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.
[3:52:00] So you can see all the five columns. We have got five columns and we have got six rows of data. So this is returning everything, everything. Now if I want to, let's say
[3:52:12] only return some selected columns, so what I can do is I can say, let say select name comma marks from student. So I can definitely do it. I can give
[3:52:24] the name of the columns that I want to be the part of my input output. So if I run this, you can see. Now it is returning only two columns. So I want everyone to try try this out.
[3:52:42] for the select statements. It's a small it's a short code. it's a short code. So you can quickly type it. So let me again query this. I'm removing this this one.
[3:52:54] Okay? Or let me do one thing. Let me also share the code with you now. Hoping that you have already typed it, you can just paste one and two columns. And definitely I want you guys to play around with a lot of code, a lot of SQL
[3:53:07] queries after the class. Okay. 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
[3:53:22] cities? So I can make use of distinct keyword. right? So this will give me all the distinct
[3:53:35] So this will give me all the distinct cities that I've got in my data. So I want everyone to try try this out. So see guys, when you're doing the the data. You're not manipulating the data. You're
[3:53:49] not making any changes to the data. You've got the table and you're simply You've got the table and you're simply reading the data. applying some rules, you are applying some maths. As simple as that.
[3:54:07] Fine. Now as I told you that when you're reading the data, of course you can read the data directly like this. Of course Yeah, we have done it also. But let's say while reading the data,
[3:54:21] now I do not have any Let me again. Okay, I do not have any percentage column over here. But let's say that fine, I've got the marks, but I also
[3:54:33] students. Yep. So I am not going to make any changes to the original data. Rather while reading the data, I'm going to apply some maths. So what I can do is over here I can say
[3:54:45] maybe okay, I'll just put like this and I'll say and I'll say marks 100. Well, you know how it is because let's say the marks are from not
[3:55:00] let's say the marks are from not okay, let's say the marks are this will return me now I want everyone to see my screen. This will return me three columns. Now this column is not the part of the table. Rather I'm
[3:55:16] in the select clause itself in the select statement itself I'm creating this column. So I'm not creating this column in the table. No, while reading the data, while reading the data itself I want to see something. Yeah, so I'm
[3:55:31] just creating this column. So let me run this. over here as the output as the output. So this column is there as the output
[3:55:46] 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 data from the table I'm I'm doing some maths.
[3:56:00] see this is what this is the name of the new column and I'm literally not liking this name. So you can give an alias to your column. So I'll just give the alias percentage. So how you can give the alias like this?
[3:56:16] You can give alias to the existing columns also. So I I can say as columns also. So I I can say as for example over here
[3:56:30] this so the original name of the column is name but while reading the data from the table I've just given an alias to this column. So I can do it. But whatever I'm doing over here in the
[3:56:44] select statement is only for the output. It is not making any changes to the original or the underlined table. Yes, we can definitely do it to do two decimal point Rajpal. So we have got a lot of functions. We are going to learn
[3:56:58] about those functions. So, not right now, but yes, we have got So, not right now, but yes, we have got a round function. So you can give you can make it only for two decimal points.
[3:57:14] definitely we are going to cover all those functions. yes, but it's not something that I would like to maybe talk about more.
[3:57:26] Now till now, what is happening is it is returning all the rows. Yeah, it is returning all the six rows that we have. Now what I want is I want to filter the data. Right now we just learn about that how you can return all the columns, how
[3:57:40] you can return limited columns, how you can do little bit with the columns, how you can do little with the columns. This we are going to cover more.
[3:57:52] But now what I want to do is I want to filter the data that is returned as a part of the output. I want to filter the data. So how I can filter the data? So data. So how I can filter the data? So I'm just making the changes to the same
[3:58:06] right now. It is showing me all the students, all the students. Let me also say let's say city also. Yeah, every all I have got six students. So it is showing me all the students. But I do not want all the students.
[3:58:18] Rather, let's see there's a requirement and the requirement is I want to see and the requirement is I want to see data of only those students that are from city Pune. Yes, students that belongs to Pune city.
[3:58:32] So I can use where clause. Let me see if I've got anything on the PPT for the where clause. Syntax till here and then you use the where clause. So where and then you give condition over here.
[3:58:45] over here. Not necessary Boolean condition, you can give any condition condition, you can give any condition for that matter. but for any row. So anyways, we'll not talk about the condition right now.
[3:58:59] But yes, so this will this will help us to filter. actually gives a boolean answer but I'll I'll talk about that later. Right now
[3:59:11] example. So here the students who belongs to the city Pune.
[3:59:27] 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 then I'll give you time to practice it.
[3:59:48] who who have got this distinction marks. So marks greater than equal to 75. So this will return me all the students with greater than equals to 75.
[4:00:07] I want everyone to try this out both the both the queries. different operators. For example, for example, marks plus 10, let's say yeah that doesn't make
[4:00:23] sense but you can see over here I'm just adding arithmetic operator that is plus. So I can say that where marks plus 10 is greater than 75 percent. So I can add the different different
[4:00:37] operator. I can add the comparison operator. Comparison operator I've already done it yeah the previous query that we wrote.
[4:00:50] you're comparing you have marks greater than equal to 75 so you're comparing the two values right that's what comparison less than greater than greater than equal to less than equal to equals to. So these are what?
[4:01:04] comparison operator. In the same way, I'll just give you some operators, then you can give it a try. In the same way, I can also have
[4:01:19] logical operators. So logical operators, let me maybe take take this query. So I can say that I want all the students who belongs to city Pune, yeah.
[4:01:32] so here I'm adding a logical operator and marks greater than 75. So here and is what? And is a logical operator. We have got three logical
[4:01:44] operators. I hope that you guys know about it. I think I've already asked you and everybody told me that you know about the logical operators. So we have got three logical operators. I'll go slow
[4:01:56] and or and the last one is not. So here it is going to return us, it is So here it is going to return us, it is going to return us the students
[4:02:09] who belongs to Pune as well as the marks are greater than 75. are greater than 75. So this will return us Amit, only Amit.
[4:02:23] But if I put or, it means that it will return me the students who belongs to Pune. So it will return me Priya. Also it will return me all those
[4:02:37] students whose marks are greater than 75 percent. So whether they belong to Pune or not doesn't matter. So either I so here [clears throat] a students who belongs
[4:02:50] to Pune also marks greater than 75 percent. So if I run this, you can see that I'm getting the students from Pune as well as students
[4:03:02] with marks greater than 75 percent. So this is what? Or. this is what? Or. In the same way, we have got not also. I'll give you the example for not as well.
[4:03:14] So let me add another query so that you guys have it like I'll just give it to you. So I'll I'll say that okay, I want all those students who do not belong to Pune city. So I'll just say where not city.
[4:03:30] So I want the students who are not from Pune. So here are the logical operators, all the three logical operators.
[4:03:44] would like to take two minutes of halt over here and I'm just sharing this over here and I'm just sharing this queries with you all. see a student will belong to either Pune or Mumbai, right?
[4:03:59] So you can say city equals to Pune or city equals to Mumbai. So here you can give another condition city equals to Mumbai.
[4:04:18] You can just copy paste this queries do not write the query, play around with the query and then we'll look into the more operators that we have got.
[4:04:32] So we have got range and set operator, very very important operator. So as the name says that let's say you want to know or your you want
[4:04:47] or your you want to know about the students 90 yeah between 60 and 90. So you can use the range operator. So I'll just delete this
[4:05:01] I'll just delete this so that I give get enough space. Yeah. So I'll say select so and so from student where let me remove this.
[4:05:17] let's say 60 and and 80.
[4:05:33] is included, 80 is not included. So what I mean to say is right now you can see that I've got three students Neha, Priya and Anita and these are the marks. So if I give 78
[4:05:51] so oh 78 is also included. Let me give 70 65 also. included so that's always a case of confusion for me. So yeah you can see that both the ranges are included 65 as well as 78.
[4:06:07] So it will give you if you have any range you can use between operator. You can use between operator. Now Rajpal had a question what if I I want to
[4:06:20] 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 let say I got all the columns from student where
[4:06:43] in operator yeah in operator and here I can give multiple values. So let's say can give multiple values. So let's say I'll give the value Pune I'll give the value Mumbai.
[4:07:01] let's say I want to give Delhi etc. So these are what these are the range operators or set operators. So this is a range operator and in is a set operator. range operator and in is a set operator. It's a set operator.
[4:07:16] We have got match pattern matching operators also. see sometimes it's not that we have the exact value. Let's say I do not have like city equals to Pune. Rather I say all the cities
[4:07:31] that starts with P or all the cities that ends with E, pattern to match. So
[4:07:45] star from student. Okay, let's match the pattern for name. let's say that I want all the students where the name lie, name like so anything that starts with A.
[4:08:01] So here percentage means hear me out. Percentage means characters. So this will show me all the students where the name starts from A. Their name starts from A.
[4:08:16] So let's say I I'll just maybe give it a twist. So I just want A letter or let say I want just I just want T letter between their name. So I just want that the student with the letter T.
[4:08:36] just got okay let me say R. I'm not sure if we have got any students. Yeah. So I'm just saying that I'm okay with any characters, zero or more than one the left side. I'm okay with zero or more than zero
[4:08:52] care about that. All I care about that the name should have R. So now it is returning me
[4:09:05] R in it. Let me try some more combinations and combinations. So guys you guys you also have to play this. Okay, so I'll say that
[4:09:20] um, okay. So I'll say that I want only one character. They're only one. So here hyphen means character, but only one character. Percentage means
[4:09:35] any number of character. Hear me out. Percentage means any number of character and any character. But when I give hyphen, it means it can have only one character before R. You're getting before R it can have only
[4:09:48] one character. So Priya, why P is only one character before R PR. So this will return me this is returning me Priya. So this is what? This is pattern matching. So I'm doing the
[4:10:04] pattern matching using the modulus or you can say percentage and using underscore, not hyphen, sorry, underscore. percentage simply means any number of characters
[4:10:19] and any character. This means one character but any character. Yeah, I can also give something like this. something like this. Let's say okay, I'll give A
[4:10:32] okay, anything before A and any two characters after A. Any two characters after A. I'm not sure if it will so I don't have any any such student.
[4:10:45] But if let's say I've got a student, I'm just giving any random name. Let's say I've got the student S I T A
[4:10:57] Yeah, suppose I've got a student like this. So this will return this query So I've got two underscore. It means any two characters, but it has to be two in number, not more than that.
[4:11:09] 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.
[4:11:32] percentage and underscore. Am I clear?
[4:11:48] So we'll look into more operators, then I'll give you time. So we have got the last operator, null operator. So let's say you simply want to return the rows where maybe the column is not
[4:12:03] So the column is not null. So I can simply say where name
[4:12:16] So I can say where name is
[4:12:31] where name is not null. Or I can say where the name is null. any column with a [clears throat] no name. So I don't have any such column, so that why it is not returning anything. So
[4:12:44] where the name is not null or name is null. You can check it for anything. Let's say city is null. Again, I've got the full data, so it will not return me anything. Not null.
[4:13:00] is not null. All the rows where the city is not null. where clause. I'll take a halt for two minutes. You can just copy paste and play around with it.
[4:13:26] to practice after the class also but fine I'll give you more time.
[4:13:41] do with the primary key. Let's say that your name is a primary So it doesn't matter right you are just going to filter with name name equals to going to filter with name name equals to something.
[4:13:57] simply filtering your data. That's what you're doing if you have got a composite primary key how does that matter? Where doesn't care about primary key or want to filter the data and filter the data for you.
[4:14:12] So let's say you have got name and marks as a composite primary key. these two so you can just say and marks equals to So you're getting it has nothing to do with the primary key or composite
[4:14:27] with the primary key or composite primary key. in order to filter the data. Now let's say and you're going to use it
[4:14:40] when you start working let's say you just want to see the structure of your data. Yeah so instead let's say your table is having thousand rows so you do not want all the
[4:14:52] only all the thousand rows rather you just want to see the top 20 rows in order to see the structure of the data.
[4:15:04] So here what you can do is or let me put back because I maybe I'll show you some some some other thing as well. So I'll say two. So
[4:15:17] if I run this query this will just give me the two records. Yeah it will give me the two records. So you can give any number over here.
[4:15:30] No, no. In MySQL we cannot do that. I need to check. I need to check. I've never used it in MySQL.
[4:15:42] number of data. It will return the number of rows. [clears throat] It's not that you cannot add where clause. So I can also say let's say
[4:15:57] marks greater than let's say 75 and then I can limit my data. So I can also add where clause. So first it will filter the records
[4:16:17] will simply give us the top two records, the top two rows. Anyone who has not understood what limit is?
[4:16:31] that you're going to use a lot in order to understand the data. Now we're going to learn about the next clause that is order by. I think this is going to be the last clause for today.
[4:16:51] So as the name says, just give me a second. that order by so you're ordering something. So it is to sort
[4:17:03] the result. Yeah, now you're looking into the result querying the data that your table is holding. So while maybe when you're seeing the result, you
[4:17:16] want the result to be sorted. Yeah, you want the result to be sorted via some column. So sorting can be definitely ascending.
[4:17:28] 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 want to sort my students as per their marks.
[4:17:44] So what I can do is I can say order by the order by marks. Let me execute both the queries. Yep. So you can see this is the result of second query. Let me highlight it.
[4:17:59] Result of first query and the result of second query. So over here if you see there's no sorting. Yeah. You Amit got 75, Neha got 72,
[4:18:14] Rahul got 90. So the data is not sorted. But for the second query the data is sorted order by marks. So you can see that Suresh has got minimum marks. Priya has got uh
[4:18:28] like you know from what do we call that from from the lower she's second And yes when and so forth Rahul has got the maximum marks. So it is sorting the data by marks.
[4:18:42] But by default it is sorting in the ascending order. And if you want to sort it in a descending order all you have to do is you have to explicitly give DSC. So it means that I'm going to sort the
[4:18:56] So it means that I'm going to sort the data in the descending order. Now Rahul uh is being seen first because Rahul has got the maximum marks. So descending means
[4:19:09] highest to lowest. Yeah, it starts with the highest and it moves to the lowest. Ascending means from the lowest to highest. order. But then you have to explicitly give.
[4:19:23] I'll give you more examples and then I'll give you time. So let me do one thing. Let me give you time right right away.
[4:19:39] So so that I'm going to cover a project in the last class. class. Instead of seven, we are going to have And I'm going to cover the project in the last class.
[4:19:52] Done everyone? So you can also sort the data uh on two columns. marks. Right? So let's say I also want to sort
[4:20:05] the data on two columns. I also want to sort the data on marks. sort the data on marks. So marks I want to be the the first column. And let's say I want to sort the data
[4:20:17] on city as well. So see I'm sorting the data. Okay, I'll just do one thing. I'll make it city first because that will make it city first because that will make more sense.
[4:20:33] So city will be sorted in ascending order. Yeah, alphabetically it will be it will be sorted in ascending order. So first the data will be sorted in city. And then in that city like you know in
[4:20:45] Pune let's say I've got three students. So first So first the data will be sorted as per the city. Then it will move one step forward.
[4:20:57] sorted inside the marks. So let me show you the result. Now you can see I want everyone to see my screen and be attentive. It's a very
[4:21:12] Now you can see that the data is sorted as per the city in ascending order. Chennai, Delhi, Mumbai, Mumbai, Pune. Now the second thing the second question comes that okay we have got two students
[4:21:28] for Pune and two students for Mumbai. So let's talk about Mumbai. Yeah, we have got two students for Mumbai. So whom should I show first? Anita or Yep. So we have got a second level of sorting.
[4:21:42] So that is marks descending. It means in Mumbai the students with the maximum marks will be shown first. So Anita is shown first because Anita has secured more marks than Neha and then
[4:21:56] Neha is shown. So this is how it is going to display. So you got that. Here we are doing the sorting, two level city. By default it is doing ascending because
[4:22:09] we have not given anything. You can always give something over here. always give something over here. Now once the data is sorted by a city the same thing. If I talk about Mumbai
[4:22:26] So which will be the first one and which will be the second row? So it will go to the next level of sorting. That is marks descending. So it will check for the maximum marks. Anita has got
[4:22:40] more than Neha. So that's why we are seeing Anita first and then Neha. So in this way you can do the sorting on multiple columns, more than one column also. Am I clear? So if you want
[4:22:54] descending. So that is also very much possible. So it will show you Pune first now. Yeah, Pune, Mumbai, Delhi, Chennai and marks that will be shown first.
[4:23:10] So if you do it vice versa, that will not make sense. So first it will sort via marks. Yeah, and then you're sorting via a city. So that will not make sense.
[4:23:27] via a city. So it doesn't make sense. doing something, maybe syntactically it is right, but that doesn't logically it doesn't make sense to us as
[4:23:43] well as to SQL. So I want everyone to try this out.
[4:24:05] here. You can add limit also. Let's say after sorting, I just want to see three records. So you can have multiple clauses. So it's not that one SQL query will have only one clause.
[4:24:19] It can have the combination of multiple clauses. to create the database and then we are going to use that database for our
[4:24:31] So you can just quickly create a database with the name of your choice. You can give any name to your database and please make sure that you also use So I'm going to execute these two commands that you see on my screen.
[4:24:50] once this is executed, I'll give you time. So just hold on. So you have to create the student tables. Now this table is same as that already have it, no need to create it. You can use the same one, the previous
[4:25:03] database. That's your choice how you want to go ahead with it. So I prefer to create the new databases and new tables like in every session because if you're using lab provided to you by simply learn,
[4:25:17] create the new database because everything is reset in I think every five hours. So anyways, I'm creating the table. Also I'm creating I'm inserting the data into the table.
[4:25:31] So I'm done with all the configuration. Maybe I'll wait for a minute for to finish of the configuration. I'm sharing all the code on the chat. So you can all the code on the chat. So you can just copy paste it.
[4:25:45] database. Vidya, I'm very much okay with it. table we created in order to learn about auto number constraint
[4:25:57] But this is if you want you can drop your existing table and you can recreate this table to avoid any confusion.
[4:26:11] Here we do not have any auto number if you see. Done everyone? Now yesterday we learned about select like we started with the select statement. Just give me a second.
[4:26:24] about various things in select. We learn about order by clause, then we learn about limit clause. So though this slide might look not very important to you, but this is very
[4:26:37] because when you start writing complex queries, you should know that what is the order of execution. So when I say order of execution, what I mean to say is let's say I write a query select staff from
[4:26:55] where let's say let's say city equals to Pune
[4:27:07] let's say order by some column and then limit some number. So if I've got a query like this, now this is very very important. [clears throat]
[4:27:20] execution. So when I say order of execution, I mean to say how this query will be executed by the database, DBMS. So see first from is executed. Yeah, from is executed. So
[4:27:37] basically it will check that from which table you querying. So it is going to execute this from from this table. Then it will then what it will do? It
[4:27:49] filter the data. Now your table might have 100 records. Maybe only 10 rows belongs to Pune.
[4:28:03] So it will first it will go to that table. So from which table, then from this table it will it will execute the where table it will it will execute the where part of the query. So it will simply
[4:28:15] part of the query. So it will simply fetch these 10 rows. Yep. Then it will go to the select part. So after that it will go to the select part. So in these 10 rows, like you're fetching 10 rows, we get that. What all
[4:28:27] columns you want to select, right? Select is all about what all columns. So right now in this query I'm selecting all the columns. So then it will select the columns. Now it will get some 10 rows and
[4:28:40] the selected columns and then order by. After that, once it has got the result, sort it and then it is going to give you the result with whatever limit you have put in your query.
[4:28:56] So this is what? This is the logical execution order. Now you should definitely remember this execution order. Again, I'm repeating when you start writing complicated, complex queries, you should know that how things
[4:29:08] are happening in the background. So I repeat, first from is executed and that makes sense also, right? That which table we are actually looking into. Then we filter the data and then we filter the columns. Once we have the filter day
[4:29:23] row with the rows and columns, yeah? So once we have the filter rows and columns, then on those we apply order by. So DBM is apply order by and once it is ordered by, then it applies limit.
[4:29:39] And that's what you're going to see that it will show you the first two records after order by. So this is a very important thing and So this is a very important thing and you should definitely know.
[4:29:51] Now we're going to move to the next topic that is aggregate functions. So when I say aggregate, aggregate, the word aggregate, what comes to your mind?
[4:30:04] right? Uh some mathematical calculations, calculation, yes. So aggregate, aggregate, this is very important, all together is a very important word. So it performs, so
[4:30:18] aggregate functions, so they perform uh maybe I'll write it over here rather than writing it on the notepad. So it performs calculations. Please ignore my writing
[4:30:32] Please ignore my writing on multiple rows. and then return
[4:30:52] So it will perform calculation on multiple rows and return a single value. I've got a table. Just give me a second. I've got a table I've got a table and in that table
[4:31:07] columns and then I have a column profit. Yeah, profit. Now in this column I'm having values like 100, 200 then let's say 300. Yeah, these are the
[4:31:20] So with the help of aggregate, see it will perform calculation on multiple rows and return a single value. So if I perform count, what will happen? It will give me the count. So how many the number of rows that I
[4:31:35] have. So it will give me three. Then sum. So what it will do? It is going to sum all the numbers. So let's say I'm applying this function on all the rows of my table. So it will
[4:31:49] rows, always remember. So it will give me 600. average. I'm not going to do that. I think it's going to be around I think Then minimum. So it will again check for the minimum value and maximum, it will
[4:32:05] So these are the aggregate functions. These are the five aggregate functions. Now you already understand what is sum, average, min, max. Yep. It's a very simple one. Now we are going to look into the code for the same.
[4:32:20] So you can see it is showing me that I've got six records in total. Yeah, I've got six records in total. Now instead of this, now hear me out. Instead of this, I can also give some column name. Let's say I give a column
[4:32:32] um name or marks. So I can do that also. So what it will do now hear me out, yeah, what it does because this is very important. So it will count all the non-null value
[4:32:48] in this particular column. So if I go over here right now, if you see marks, they this is non-null, this is not null, this is non-null, this is non-null, this is non-null, and this is non-null. So it is going to count all
[4:33:01] the non-null values. So basically this will give me six. Yeah, this will Now I'm saying again, I'm repeating, it will count all the non-null null values. So let's say I insert one more record. Yeah, so you can see that I'm inserting
[4:33:16] one more record. Okay, any any name for that matter. And it's just that I'm going to give null over here. I'm going to give null over here. You're getting? I'm in case of
[4:33:28] student ID seven, let's say I do not have the marks right now. So I'm just giving null value over here. So I'm going to execute this. Got successfully executed. Now if I count
[4:33:42] this thing, now you tell me what it will give what result it will give me. This will give me what result? What number will it give me? Seven records, right? Seven records because I've got seven records. No, why
[4:33:56] rows. But as soon as I give marks, what it will give me is it will give me six. So why it is giving me six? Because it will count only the not yeah, not null
[4:34:13] will count only the not yeah, not null values. So it will count all of these, but this it will ignore because marks is null over here. But if I go ahead and do it for some other column, let's say let me do it for city.
[4:34:26] me? Come on. because I don't have any null city. All the seven rows have value in the
[4:34:38] So definitely everything will be counted. So in the same way I can use other aggregate function. For example, sum. city. I have to provide some numeric column
[4:34:53] I have to provide some numeric column over here. So I'll provide maybe marks. So this will do the sum of all the marks, all the marks that I've got. Yeah, sum of all the
[4:35:07] marks. In the same way average. So this will give me the average marks secured by the students. Then I can do min, the minimum marks. So I think the minimum marks is yeah, 55
[4:35:23] And I think the maximum marks is of Rahul. So I can also run for maximum marks. So you can also quickly try this. In the same query you can make the changes and you can just have a look how
[4:35:37] changes and you can just have a look how it works. It's a very simple concept. so this data is already the part of your table. Yes, your table already has got
[4:35:51] six rows. Now, if you want to insert more rows, then you have to rerun the command with more data. So you can't give values in continuation to it. See, if you rerun this, I'm not
[4:36:05] going to do that, but you can try that. So you will have duplicate data. So if you run this three times, you're going to have this data three times, the same duplicated data three times. So whenever you have new data, you have
[4:36:18] to run the whole command like this. So now we're going to learn about group by. It's a very, very important concept in SQL. So what do you mean by group when I say
[4:36:32] group? Yep, you we all understand what the group means. Now here definitely we are having group by. So it means that we are grouping something. And what will we will group in SQL that of course, when it comes to SQL, we are
[4:36:47] going to group the rows. Yeah, we are going to group the rows. So let's say that you have got right now we know we have got students, right? And we have got the data of students.
[4:37:02] So let's say I want to group the students. Let's say I've got a job to Now what I'm going to do is I'm going to group the students as per the city. So create the groups as per the city, Pune,
[4:37:17] the groups as per the city, Pune, Mumbai, and Chennai. Yes, so I'm making the groups as per the city. So I'm simply taking all the distinct values in the city column
[4:37:31] this is one group, this is second group, this is third and this is fourth group. once I know that these are my groups, yeah, these are my groups, then what I I can do is I can perform some aggregate functions on the groups.
[4:37:48] So first you're going to group your data as per some parameter. In my case I've decided the parameter to be city. I'm grouping the data via city. So I'm picking up all the distinct cities that I have in my data set
[4:38:04] and I'm grouping I'm going to group my rows as per the city. The next thing that I'm going to do is once the grouping is done I can perform some aggregations on the group data
[4:38:18] using the aggregate functions. So for example I can see that maybe I want the count of the Pune students. So it will simply help me with count yeah. So two for Pune, two for Mumbai, one for Chennai
[4:38:34] So that's how it is. So I'm I can perform any of the aggregate function. I can also perform sum. So it be what I can do is I can sum the Pune students numbers yeah. I can sum their marks and I can literally
[4:38:50] write it over here. So I think that's going to be around 150. Yeah so basically I'm going to sum yeah my calculation might be wrong. of group so with the help of group by
[4:39:04] I can group the rows with the same value. I can group the rows with the same value in one or more column. So I can group the rows. That's what I did. Yeah I was grouping the rows one or more column.
[4:39:16] So I group the rows with Pune oh sorry with city right. I can group it with when we write the code and then perform. So I'll quickly show you the syntax and then we'll write the
[4:39:31] code around it. Now group by is very very important for your interviews also. Please make sure that you understand and it's a very simple thing. It's a very simple function. You write two three queries and you're done and dusted kind
[4:39:43] So anyways so the group by syntax is so first now hear me out. This is very important. You you start with select. Then you have a table name that select from so and so table.
[4:39:58] see, you're going to group by some column, right? So here you'll give group by and you'll give the column name that you're grouping by which column. Yes, so I can give city. I can give
[4:40:12] multiple columns. Let's say I'm grouping by city as well as maybe something else also. Let's say as as well as area. and I'll show show that you when we write write the code.
[4:40:27] Okay. Now in select in select, this is very important. Yeah, in select we give the column name. Now this column names, this column name, let me clean this off. This column name
[4:40:42] should be same as that of this column. So you're getting if I'm grouping by city then this can be city. This cannot be any other column. So I cannot make it marks. I cannot give make it name.
[4:40:55] So I'll show that to you. So hear me out right now. You may not but if you don't listen to me because you're not understanding 100% then write the code also. So please make sure that you listen to me. Once we write the
[4:41:09] understand. So in select rule. So in the select statement, you can have
[4:41:21] either the group column. It means the column with which you have grouped your data or you can have
[4:41:34] going let's say I I'm counting or I'm doing the sum of marks. So I cannot have any other column. I cannot have any other column. Not cannot have any other column. Not possible.
[4:41:51] I can just accommodate these two columns. answered this question, but yes, your question will be answered again in sometime. So, now we're going to write the queries
[4:42:05] around group by so that you understand the concept. So we have got the same data. I hope everyone can see my screen. So here what I'm going to do is I'm going to
[4:42:23] comma and I'll say count So group by as I told you this the same
[4:42:37] example I have already explained you that I'm grouping the rows by a city and it. So if I run it, you can see that it is showing me all the distinct cities and the number of students. So
[4:42:52] if you want, you can give an alias to your column, yeah, as students. You can give some aliases, yeah, for the better
[4:43:04] your column has a proper name. So anyways, you can see that this is done, yeah. You can simply say so here I've given city. I've group by city and this is the aggregation.
[4:43:17] name of the student. So let me try that. Yeah, I'm giving name also. Now you can see I have not grouped by name. I'm I've not done anything with the name. Can you see I'm getting the error?
[4:43:35] just re-read this that every column in the select must either be so in group by. So it means whatever column you're writing in select either that column should be in the group by or it should be the aggregated
[4:43:50] column. Anything else will not work because I've given name. I'm not grouping my data via name. So it is throwing error. I I can maybe use marks any, any other column.
[4:44:03] It will not work. Because I have not group my data via So this is a rule, this is a thumb rule of group by and you should definitely remember this very important for your interviews.
[4:44:18] So I just do one thing I'll wait for a minute. I want you guys to try running minute. I want you guys to try running this query. the different colors on the group by, what all we can do other than this.
[4:44:39] column Ekta. So if I don't give it the column, this new column account sorry count will take its name like sorry count will take its name like this.
[4:44:53] I've done. Having I'll come to that Siddharth. Having I'll come to that Siddharth. I'll come to that as well.
[4:45:06] without the aggregate function why would you do it? Like you can do it there should be some reason behind it, right? Why you're grouping your data. why you're grouping Sanskar.
[4:45:25] Now let's see this is the result that I'm getting everyone. that I want only those values or only those cities where the number of students are greater than one.
[4:45:42] So I've got a, I've got a filter condition. I do not want all the rows I want only those cities where the number of students are more where the number of students are more than one.
[4:45:55] use where and if I say where let's say count greater than one it will not work. You can see that it is So, when you, whenever you want to filter your group data, this is what?
[4:46:09] This is a group data thumb rule, you have to use having. So, with the help of having, you can filter your group data. Very important.
[4:46:21] You cannot use where in order to filter your group data. In order to filter your group data, you have got another keyword, another another keyword, another uh clause that is having.
[4:46:39] So, it's very same as that of where. It's similar to where, just that where we use for the regular data and having we use for the group data to filter the group data. Now, I told you, let me remove having,
[4:46:53] yeah otherwise I'll get very less data. Already I've got very less data. error, just allow me a minute. So, I just finish finish the
[4:47:06] explanation. So, I told you that you can also group by multiple columns. So, let's say I'm grouping my data via city as well as via grades.
[4:47:21] will group via city and in the city it is going to pick the grades. It will pick the grades. Yeah, it will pick the grades. So, Mumbai I have got grades. So, Mumbai I have got like three and B, everybody is B. So,
[4:47:33] do one thing, I'll also show you the grade over here so that you get to see how things are happening. [clears throat] So, what it is doing is we are saying
[4:47:46] that group by city as well as grade. So, how things are happening over here? let's say Pune. Okay, so it will take Pune. So, these
[4:47:59] Yeah, these two. Now, definitely both both for Pune have got two distinct grades. So it will make this as a one group and another group as this. Okay. Now let's talk about Mumbai.
[4:48:15] Yeah, Mumbai. So Mumbai, if you see Mumbai, so one, two, three. But all have got same grades. So it will just take one group out of it.
[4:48:28] So you can group via multiple columns. So first it will group via city and then this is how it will work. Yeah, Mumbai has got three, so it will three. Okay. Then anyways Delhi and Chennai, they are they
[4:48:43] are only one records. So this is how it is going to work. So first it will pick Pune and then the distinct value. So Pune and the distinct value of the grades. So if you see over here,
[4:49:00] for Mumbai because all the Mumbai students have got B grade. So there's only one row for Mumbai. But Pune, Pune students have got A grade as well as C grade. So that's why there are two, two rows of data.
[4:49:16] me quickly answer the questions on the chat till then. So Aparna has got the answer I think from Shilpa.
[4:49:40] then what? See, you're saying Sheila, having count marks what? You have to give let's say greater than zero or so and then why descending? There's no order by.
[4:49:55] you can put only when you're ordering your data. the reason why I'm showing you this example is so that you get ready for
[4:50:11] your interviews. So let's say that okay what you have to do is I'll just give you one So here's a use case that you have to ignore the students
[4:50:24] with grade D. Ignore the students
[4:50:40] cities let's say with average marks Now this is a requirement. Ignore the students with grade D. So we are do not
[4:50:56] so basically we do not want grade D students to be included in our And then I want to show all the cities again all the cities all the four cities that I've got but
[4:51:10] the cities with the average marks greater than 70. a minute. I may not give you a lot of time but I'll just wait for a minute and I want you guys to try it by yourself. I'll give you a hint. Here you are going
[4:51:25] to use a combination of where as well as having. So I repeat ignore the students with grade D. you're going to use where as well as you're going to use having because
[4:51:39] you're filtering on the table level. You're filtering on the group data level. Here you're doing two level of filtering. filtering. So just give it a try.
[4:51:56] So let me show you how we have to do it. So you have to go step by step. Now a query can be written in multiple ways. So it's not that
[4:52:10] So it always works like that. So [clears throat] here let me say okay, So [clears throat] here let me say okay, I'm removing
[4:52:31] this will give me all the cities with their average marks. But what I have to do is I have to ignore the students with grade D. So this would have also included the students with grade D
[4:52:44] the Mumbai students with grade D. So I do not want that. So what I'm going to do not want that. So what I'm going to do is I'll say
[4:53:03] So while querying, see, while querying I'm saying that from this now here order I'm saying that from this now here order of execution come
[4:53:18] Yeah. So we are getting only those students the grade is not D. And now we are group making the group of those students. We are calculating the average and we are simply saying where the average
[4:53:32] we are simply saying where the average marks
[4:53:48] We are before grouping the data, before grouping the data, we are getting only the that data that is required. In our case we are getting only those students whose grades are not D. Yeah, we are getting all the students
[4:54:11] calculating the average marks and then the group data. Now this is going to give me this group data, right? So I'm going to I'm simply filtering it
[4:54:24] 70. So this is how it's going to work. just have a look, run it for a um minute and then we'll move to the by. Group by is very important. I've shown you all the possible colors of
[4:54:40] But as I told you, SQL or for that matter, any coding language is like maths. The more you practice, the better you become at it. So I'll give you some playlist also from where you can practice.
[4:54:58] line. An Ekta, if you're running the query, please make sure that you you highlight or you select from line number 35 to 38. Also in having average marks, the greater than or smaller than sign, here
[4:55:12] greater than or smaller than sign, here the greater than sign is missing. On line number 38, the greater than sign is missing.
[4:55:28] functions. So see guys, in order to perform anything, yeah, in order to perform any task, background, they will understand it what functions are. And those who are not, so
[4:55:43] I'll try to explain it to you in a very uh naive way. So let's say in a very layman terms. So let's say that you uh want to perform some some some job. Yeah, there's some action that you want
[4:55:57] to perform. So in the coding world, one way is that you write the whole thing, yeah, you write the whole thing, the whole code in order to perform this action. The other way is
[4:56:15] there are some pre-built. It means built by somebody else. Functions or methods. It's one of the same thing.
[4:56:27] can simply make your life easy. [clears throat] let me explain you it to you with the help of one real life analogy. Let's say that
[4:56:41] you want to cook pizza. Yeah, you want to cook pizza. Now in order to cook pizza, one way is that you do everything from scratch by yourself. You make your own base. Yeah, you need your own dough. You make
[4:56:55] your own base. Then you make your own sauce. Yeah, and then you're doing So this is like you're doing everything by yourself. The other way is that you know that the bases are readily available in the market.
[4:57:10] You go and buy that base and use it directly. So definitely it makes your life easy, right? Definitely you'll not able to customize much. You know, this getting and fine. If you're okay with it, you can use it.
[4:57:22] And you can also buy sauce in from the market. So here also it's the same thing. Now this explanation I have given for the people who are not coming from the coding background.
[4:57:34] So what are SQL functions? Think of it that yeah, what are built-in SQL functions? Think of it as a ready-made pizza base
[4:57:46] somebody has already written the code for us and we just have to use it in order to get our work done. So in SQL we have got a lot of built-in
[4:57:59] functions. So we have got a huge list of built-in functions. functions in order to like some of the important functions. Now with the help of functions, what you can do? You can manipulate your data.
[4:58:13] You can perform calculations. You can format output. format output. So manipulate the data means let's say So manipulate the data means let's say fine. So let's say
[4:58:30] So I can manipulate it and I can make T in caps and I can make the rest of the letters in small. I can perform calculations. I can perform calculations. So have you used any of the SQL built-in
[4:58:43] So have you used any of the SQL built-in functions in today's class? Can you so any of the SQL functions? Just scroll down Aparna Tulika when I gave only aggregate I got the error.
[4:59:08] So Aparna see I told you that you're missing that greater than equal to sign. So if your question is not answered you can park it maybe yeah I'll just take it at the end of the session so you can rerun the same query and we'll see what
[4:59:20] Now these are clauses. Dinesh these are clauses. Any of the built-in functions that you can remember that has made our life easy in today's session we just directly use them
[4:59:35] in today's session only. I've got answer also on the chat. Yes count. Did you write any code for it? Yeah no. So there's some code written behind and
[4:59:48] we are simply using in in order to count the number of rows. Right in the same way sum min max. So there's some code written right it's
[5:00:00] just that we are saying min it is not going to calculate the minimum magically for us. There might be some there there is some code written behind the scene. So we are simply using these functions directly and making our life easy.
[5:00:13] In the same way you can format the output with the help of the function. Let's say you can format your date. Yeah, your date is in this format. Maybe you can format it in
[5:00:26] maybe some other format. So you can do all of these things. So basically SQL function, it takes the input, it processes it, and it gives the output.
[5:00:44] already told you. Yeah, you are using something that is already built. So this will definitely uh make your life easy. Yeah, you don't have to write the complex code, complex logic.
[5:00:57] Definitely your code will be cleaner. Yeah, because you're just writing one line of code rather than writing 10 line of codes. It will definitely save your So anyways, this part I've already explained you.
[5:01:15] This is very simple. Now in SQL we have got two type of functions. One is single row function and the other one is aggregate functions.
[5:01:27] So single row function says that I'm going to work on one row at a time. For example, yeah, let's say I've got a table, and this table has got some data. Let's say it has got name.
[5:01:42] And I just quickly write some name. Let's say it has got name Mark, John, Monica.
[5:01:54] going to work on one row at a time. So let's say uh what I want to do now there's one function. Maybe I'm going to talk about that function later, but let's say what I want is I want this all these
[5:02:08] names to be in capital letter, yeah, into be in uppercase. like uppercase function which will work on each row. So it will make this as Mark in uppercase.
[5:02:22] forth. So, I repeat, what does it mean? It means that it is going to work on each and every row. it will give you the results, each and every row.
[5:02:35] obviously. See, if I make I'm making it in capital, it doesn't matter what what the value the other row is holding. So, in case of single row function, it works individually on each row of data
[5:02:52] works individually on each row of data and it is totally independent of the uh Fine. Now, the other type of functions that we have got are aggregate functions. Now, this
[5:03:04] you have already seen. We have already worked on it. So, aggregate function says again, let's say I've got some data yeah. So, aggregate function says that it
[5:03:16] works on multiple rows and it gives you a single value. definitely it will work on the multiple rows and it will give me a value.
[5:03:29] Six. Average, it will give me a value. Count, it will give me a value. So, it works on multiple rows. So, it is dependent on all the rows in order to give you the values. Whereas single row function works on
[5:03:42] each row and give you the value. It is independent of of the other rows. independent of of the other rows. You can just quickly read this slide. to you, once we do the practical, it will be 100% clear. Still not clear,
[5:03:57] just try going through the recording once. Not clear, ask me again, I'll re-explain it to you at the end end of the class.
[5:04:09] I'm sure things will be definitely clear by the end of everything, whatever we are learning. Like once the whole topic is done. Now, SQL functions are divided into
[5:04:24] functions. So for single row functions, we have got numeric functions, string functions and date functions and we are going to look into all of these functions one by one.
[5:04:38] you have already seen this part. Yeah, we have got regular aggregate functions. You have already seen this and then we also have got window aggregate functions. So this we are going to look into, I think if
[5:04:52] possible some of it today, otherwise we'll look into over the next weekend. our agenda is to learn about single row functions. Yeah, all of these three. So we'll first learn about the numeric function. Now as the name says numeric
[5:05:08] function. Now as the name says numeric functions, it means that these functions can work on numbers.
[5:05:20] literally a lot of maths with numeric functions. functions. So now we need more data because we are playing with numbers.
[5:05:33] So for that, let me take more So in student table, see I'm going to insert more data.
[5:05:48] that I've got more data. Now I've got almost 12 rows of data. And also I'm going to insert one more table that is employee.
[5:06:01] So I've got two tables now. I've got the number of rows in the student table and then I've got the employee table.
[5:06:15] So I'll just give you all this code. You can run it and I'll just wait for a minute. And it's very easy. These functions are most of these functions are very It's just that
[5:06:29] we have got some functions just like uh let's say you buy microwave or AC. somebody has already done the engineering, yeah. It's just that you should now your job is to learn that how to use it.
[5:06:43] thing. We've got a lot of functions and we are important functions and when you're in the projects you may encounter some requirement where you might have not seen or worked on that
[5:06:57] function in the past. So we always Google, we always try to find the solution we see if we have anything and then we work on if we have anything and then we work on it.
[5:07:11] you should have the data ready in order to run these functions run this code. Now we'll go one by one. See I cannot write or type these queries. If I start typing these queries then I'll able to
[5:07:24] So I'll go one by one and I'll I'll also give you this code. So let me give you this code. Okay, I'll So let me give you this code. Okay, I'll give you one by one.
[5:07:38] table and it has got a lot of columns. So let me show you the data over here. Just give me a second.
[5:07:51] employee table, employee ID, employee name, department, the salary of the employee, the bonus that the employee has got, the date. So these are the this is the data that I have got.
[5:08:09] I you can see the salary right now here the salary right now. So I'm going to round off the salary. Yeah, I'm going to round off the salary. So for for rounding of the salary I'm using the round function.
[5:08:23] So here I'll show you the original figure, the original column with its Also I'll show you the column with the rounded salary. we are getting. You can see over here.
[5:08:36] Yeah basic maths that what we have learned in school. it. So it get rounded off to the nearest number. So right now it's 0.75 so it will round off to 33.
[5:08:52] off to 33. 0.40 so it will round off to 45. Yeah. 0.90 it will round off to yep 00. So in this way rounding I hope that you understand what rounding is. It's a
[5:09:05] time. So during our school. So school time. So anyways so rounding is done. Now let's say that we want to do the rounding but with some decimal yeah. So right now it is removing the
[5:09:19] decimal. But let's say I want to do the rounding. But I want one decimal. So here in the round function I can pass salary. off. And this is a decimal.
[5:09:34] And this is a decimal. So if I run this. So it is again rounding off to one decimal place. maths. Okay. Now I want you guys to guess so I
[5:09:48] want the class to be a little bit interactive. So I want you guys to guess interactive. So I want you guys to guess what seal could be.
[5:10:00] Now the seal English word. So what this function could do for us? Now this is not a comment. So ideally the comment is written like this and that's why it is throwing error in each and every line.
[5:10:25] Yeah upper something related to upper value right. So see. Rounding of what does it do? It round of Rounding of what does it do? It round of the number to the nearest number. So it
[5:10:39] So whatever it is the nearest number. Now let's say that you want to round off and you simply want the upper number, not the lower number. Yeah, you so you
[5:10:51] the nearest lower number. You don't care about the nearest lower number, rather So it will give you the upper numbers there. Every single time it will give the upper number. Last if you see four four five point four zero.
[5:11:06] So if I talk about the lowest number, no lowest nearest number. So the lowest nearest near number for this one is what? It is 45, right? But because we have run seal, it is over here giving 46.
[5:11:22] So I hope that you understand. So for example, if I give 1.1 round would have given one. Yes, seal will give two. Yes, seal will give two. If I've got let's say 1. or 2.3
[5:11:36] round would have given me the nearest number, but seal would have closest uh upper number. uh upper number. So like this.
[5:11:50] Fine. Moving ahead. Now you can also give the absolute. So you know what absolute is. We learned in school what absolute is. So absolute basically removes the negative. So if I've got a negative
[5:12:03] if I give absolute to it, it will give me the positive number. So here also it's doing the same thing. We are simply subtracting bonus with 5,000. Now this may result in the positive as
[5:12:17] well as negative number, but because we have applied the absolute function, it will simply convert the negative number into the positive number. So very simple one. Now moving ahead, we have got mod.
[5:12:34] Now moving ahead, we have got mod. So is mod? What is mod? Guys, what is mod?
[5:12:51] So here in the mod function we first say that okay, I want see what is mod? Mod is a remainder. So when you're dividing two numbers, there when you're dividing two numbers, there are two things. One is the number
[5:13:05] it by what? So we call it as dividend divisor, something like that. I forgot that. But yes, so let's say I want to divide 100 by two. is the number that you want to divide and this is the number that you want to
[5:13:21] divide this number with what? So here I'm dividing all the salaries by two. divide any number by two, either it will give you zero remainder
[5:13:36] getting. So this is a basic maths.
[5:13:49] Modulus just give me a C just give me a second. how we can write the comments in my SQL.
[5:14:04] in my SQL. You can use hash. nothing. It is giving you remainder. So
[5:14:16] numerator and then we have got the denominator. forgot. Fine. Moving ahead, power. Everybody
[5:14:29] understand what power is. They have maths, a lot of maths. 2 raised to the power three. Yeah, three raised to the power two. So again, you have got some two numbers involved in order to calculate the power
[5:14:41] power. So we are what we are doing is salary to the power of two. So this is going to calculate the salary to the power of two. So if I run this,
[5:14:58] because we are taking the salary and we are you know multiplying it by itself. So yeah. So these are the some of the important numerical functions. We have got a lot
[5:15:12] more numerical functions. So you can explore more whenever you have some time. So what you can do is but if you don't explore also that's perfectly okay. I would suggest you to rather focus on the interview questions
[5:15:25] and focus on I'm going to tell you we have got something called lead code questions. So from there you can learn. So don't get into all these functions and all because see if you try to mug up all these functions anyways you're going
[5:15:38] to forget and nobody expects you to remember all those complex functions. There are some basic functions that anyways you'll able to remember if you practice. So you can just try MySQL functions.
[5:15:59] So you can use the developer guide of MySQL and here you'll find mediate functions, donkey number of Yeah, you can see over here. So for example
[5:16:25] have. You can see that we have got so many functions. functions otherwise you'll get super demotivated. Nobody you'll get super demotivated. Nobody asks these functions.
[5:16:41] Yes, we may use mod. Not power much, but mod sometimes we may use. For example, you're calculating there there are some calculations that So you may want to remove the negative numbers.
[5:16:57] But again, guys, please do not get into all these functions because there are a lot of functions and if you start looking into demotivated. Nobody asks these functions.
[5:17:10] [laughter] some important functions and that's more than enough. You can use lead code in order to practice and that's more than enough.
[5:17:33] So maybe I'll just give you all of these. order to execute these queries. Maybe I'll just give you two minutes.
[5:17:46] I'll just give you two minutes. You can always do it after the class. Now we'll learn about string functions as in
[5:17:59] So just allow me a minute. Yep. So as the name says Shri, what you're saying is right. So as the name says you can use these
[5:18:13] on the string values, on your string columns. Now string functions can be broadly categorized into manipulate can
[5:18:30] to manipulate the strings for us. For example I already gave you this example of uppercase, lowercase. So you can replace, you can concatenate. Yeah, let's say I've got Tolika Gupta.
[5:18:43] last name. So these is, these are what? This will come under manipulation. Then you can do calculation on the string function. Now the question is that what you can calculate, how you can
[5:18:55] do the maths on string function. So you can find the length. So you can find the length. For example,
[5:19:09] So you can perform the calculation like you can find out the length of your you can find out the length of your strings.
[5:19:30] So I can also extract the substrings. I can extract the strings substrings from the string. So it is broadly categorized into these three category. So few of the functions you can use to
[5:19:43] manipulate your string. Like you have got the string and you manipulate them and you get some other string out of it. the length. Extraction you have got the string and
[5:19:56] you some extra you are extracting some part of that string. We were talking about string functions. So I was telling you that the string functions are broadly categorized into three categories.
[5:20:09] So basically you can manipulate your string. So it's all about what all you have got hello. So if you're manipulating it, let's say I'm just making it in caps.
[5:20:24] manipulate. Then I can also do some calculations on it. Now the question is that how you can calculate. So I can simply find the length of the string. So this will give me five. Yeah? How
[5:20:39] many characters are there? There are five characters. I can also do some extraction on the string. So I've got this string hello and let's say I'm I want to extract the first two letters.
[5:20:52] So it will give me the first two letters. I want to extract the last letters. I want to extract the last letter. So it will give me last letter. you're going to use these string functions when you have to do some
[5:21:07] manipulations on the existing string or when you want to find the length of your strings or you want to perform some extractions. So now we are going to look into the examples of the same.
[5:21:24] So again, we have got a lot of code everyone. So please pay attention. It's a very simple code. So first of all, you can see that we have got the employee table. So I'm
[5:21:38] simply printing or I'm simply fetching the employee name from the employee table. So we have got these Now what I'm going to do is as I told
[5:21:50] you, I'm going to do some manipulation. So I'm going to convert the employee names to the uppercase. So what I can do for that? It's very simple. As I told you, it's like functions are like
[5:22:03] let's say microwave oven at your home. So you just have to all. You don't have to worry about how it is made. What is this what is the
[5:22:20] about all of these things. So here also it's the same thing. So we have got upper function. I'm just passing my employee name and magically this will give me all the employees with the
[5:22:41] In the same way, I have got lower. Now this is very simple. This will give you all your employees. Like this will basically return everything in the So, this column will be returned in the lowercase. Very simple.
[5:22:57] function. I told you that length is one of the calculation functions. So, this will calculate the length of the column values. So, if I execute this, you can see that
[5:23:12] it is it is giving me the length. Now, in this length, the space will also be included. So, if you just maybe count the count the number of characters 4 5 6 7 8 9 10 11.
[5:23:28] So, if you have space or any special characters, that will also be included. It's not that it will only calculate or it will only count the characters. No, everything will be included. So, whatever you have got in the string.
[5:23:47] So, trim, let's see what trim does. So, if you have any extra spaces, a lot of time what happens is that we have got some extra spaces over here and over Yep. So,
[5:24:02] trim yes. So, you can use trim to remove the extra spaces. And when I say extra spaces, I mean to say the extra extra spaces if you have any over here or over here.
[5:24:16] So, at this starting of the string or at the ending of the string. very simple functions. So, I'm not maybe waiting a lot for you guys.
[5:24:32] giving a lot of time to these functions I feel because these are very simple functions. But if you want me to maybe elaborate on any functions, please feel free to say that.
[5:24:46] free to say that. Yeah, please be vocal about it. So, you have paid for this session, make sure that you take 100% from it. So, if you think that there's any function that you have not understood and you want me
[5:24:58] to elaborate more, I'll definitely I'll be happy to do it. But you have to tell me. Yeah, magically I'll not get to know that. you tell me what this function would be doing.
[5:25:13] Just read this. Now this function has got three parameters. One is a column name, then it has got the second parameter and the third parameter. So, I want you guys to guess what this function would be doing.
[5:25:27] So, all of you are right. So, we are seeing that whenever we have space, yeah, whenever we have space, just replace space with underscore. you can see that all the spaces have been replaced by underscore. I can do it
[5:25:42] for something else also. Let's say that whenever I've got A, that's going to be crazy, but yes, whenever I've got A, just replace it with underscore. See, it is replacing A with underscore.
[5:25:57] You can see. So, here you have to uh give capital A and small A. space, I want to replace it with let's say something else. I'll say apple.
[5:26:19] So, you can basically replace anything with anything. As simple as that. I repeat, you can replace anything with anything.
[5:26:31] Moving ahead. Now this is a very simple one, concatenate. So, concatenate as a a word says will concatenate means will combine the two columns. So, here what we are doing is we are
[5:26:45] concatenating the employee name the employee name with that of hyphen and department. So, there are three things that we are going concatenating. Yeah, not only two
[5:26:58] First we are concatenating the employee name and then employee name hyphen with departments. So concatenating as the name says it concatenates
[5:27:13] and you can definitely do a concatenation like this. Now let's say I do not want hyphen. Yeah, I do not want hyphen so I can remove hyphen also. So definitely this is not going to give me any error.
[5:27:33] So you can see that it is concatenating concatenated the first the name and the department. So without any anything in between but yes I can give something in between also because that makes more sense to me.
[5:27:47] because that makes more sense to me. That looks better. these queries and I'll wait for again not more than two minutes. Just give me me. Okay, I'll just provide you the queries
[5:28:02] Okay, I'll just provide you the queries in So this is the first part of the query and this is the second part. Because the chart is not allowing me. It says that some maximum so and so letters
[5:28:17] characters are only allowed. And I'll wait [snorts] for two minutes. Again you can just copy paste all these queries and keep it in the notepad. Please make sure that you practice before you comes for the next class.
[5:28:30] And we have got a lot more queries for the string function. So we'll finish off All right, so we'll look into more functions.
[5:28:43] function. So this will simply extract the first four characters from the employee name column. So if I run this, see this is extracting the first four characters. Very simple,
[5:28:57] characters. All you have to do is you have to use the right function. So this will extract the last few characters. You can give the number of characters that you want to extract. It's very simple. So am I clear with
[5:29:12] left and right? Or let me ask a question other way around. what is the use of left and right function? Fine. So I'll take it function? Fine. So I'll take it understood from everyone.
[5:29:29] let's say I do not want to extract from left or right. Rather I want to extract from the center, yeah, from from the part of the string. So I can use substring. Yep. So again the column
[5:29:44] extraction. Okay. The start position. So start position is let's say I want to perform the extraction from let's say A.
[5:29:56] And then the length. So till when I want to be the length of it? So if I run this, you can see over here. So start position is what? One. So that's why it is saying
[5:30:10] one. And then I want to extract four characters. So Amit, Neha. So in case of here Rohi, let me do it. Let me say let say I want to do it from the third
[5:30:22] character. So from the third third from the third character and then four characters after that. So A M I. I is on the third position.
[5:30:37] And after that four characters. So I T space and S. So So this will help you to extract this will help you to extract the part of your string.
[5:30:54] starts with from the beginning or from the end. In case of it can um it starts from between also, from the uh from uh maybe from some part of the string.
[5:31:10] but uh this example I have included in order to make you understand that you can add or you can work on multiple function. You can use multiple functions in order to get your work done. So before I me before I explain you this
[5:31:26] code, let me ask you how many functions are we using? functions. Oh sorry, I have already given you the given you the answer.
[5:31:41] numbers. Still I can see very less of you are interactive on the chat. So I'm not sure how many of you are hearing me. Please make sure that you take 100% from this class and you learn
[5:31:54] that you're interactive. Yeah, we have got three functions. Yes, concatenate, upper and substring. So basically how it will work? First it will so I want everyone to see my screen.
[5:32:09] So just like how the maths work, yeah? If you give any a lot of uh parenthesis in your maths, it always starts with the innermost parenthesis, right? Here also it works the same way. So first the substring will be
[5:32:25] extracted. Now on that extracted substring we are going to make it uppercase. Yeah? So let's say the extracted let's assume hypothetically that the extracted substring is AMI.
[5:32:41] Then upper will not run on it and it will make AMI in uppercase. And then concatenate will work on it. So concatenate has got two two values. One here. So what it will give me? AMI
[5:32:57] So what it will give me? AMI _ EMP. parenthesis. You always start with the innermost and then you go
[5:33:09] to the next outermost outer one till you go to the outermost. So here also we are doing the same thing. So you can actually include a lot of
[5:33:22] functions in one query. It's not that one or you can use only one function. You can see that in order to in order [clears throat] to get this result we have used three functions. So this is
[5:33:36] very very very normal thing in SQL where we use multiple functions in order to get our work done. Okay, more now this you have already seen yeah employee whose name starts
[5:33:49] with a yesterday we saw all of these things yeah so it has nothing nothing to So yeah nothing to do with the functions. So I don't know why this code I have included maybe I'll remove this code. So
[5:34:01] yeah all this code. have a look play around with it for two minutes. functions. So for single row functions if you see
[5:34:17] we are done with the numeric functions, we are done with the string functions and now we are going to learn about the date functions. in order to work on the dates we have got date and time function in order to
[5:34:32] work on the date and time as the name says. So you know that when we are working on the data we may have the columns, the date columns, the date time columns and maybe we want to
[5:34:45] manipulate and do something with those columns. So we have got a lot of functions handy and we can use these functions in order to functions in order to work on the date or date time column.
[5:34:57] work on the date or date time column. So if you see to extract the date part. Now you know when we have got any date or time for that matter. So let's say 0202 something like this.
[5:35:13] 2014. That's a long back but yeah let it be. So if you want to extract year from be. So if you want to extract year from it or month from it, day from it, date from it, yeah day Monday, Tuesday, Wednesday.
[5:35:27] So all of these things can be easily done using some of the extract date time functions. Here also you if you want to extract the time. Now you know when it comes to time you have got hour,
[5:35:41] you have got hour, right? Then you have got minutes, then you have got seconds. So if you want to extract hour, you want to second. Yeah any part of your date or date time
[5:35:54] you can use the we have got a lot of functions that that can help us to extract the data from the date time or date functions. Sorry date date time or date columns.
[5:36:07] we have got uh functions like current date and now. So this will return the current date. So current date means whatever the system. So it will automatically read that date
[5:36:21] and it will return that. In the same way this will return the date this will return the date as well as time.
[5:36:36] it's not it's not uh the only thing that we uh we would like to do that's like not it's not only that extractions or um getting the current date and time. We would also like to modify or play around
[5:36:50] with the date and the time column. So for that we have got a lot of functions. For example we have got date difference. So as the name says let say I give you two dates 2026 0101 and 2026
[5:37:05] 02 and uh 01 something like that. So if I ask you that what is the difference between the two. So it will simply give me 31 days. So I can simply find the date difference
[5:37:20] means two dates and finding the difference between the two. I can add two dates, I can subtract two dates and so on and so forth. Yeah, I can format, I can change the formatting of the date. So I've got a lot of date functions. We
[5:37:34] are going to cover few of the important date functions in order to learn about the date functions. Now it's again a very easy topic. A lot to use them. Fine.
[5:37:48] Now if I talk about the data set that we have got. So here you can see in the employee data set we have got the joining date. So definitely we are going to learn the date functions using
[5:38:02] this column joining date because that's the only date column that we have got in the only date column that we have got in a data set. moving ahead let me quickly
[5:38:17] take this okay some of this and then the rest I'll take after some time maybe. Yeah, I've got a lot of examples to show you. So this is very easy. See
[5:38:30] So this is very easy. See select current date. Now I'm not applying this function on any of the column of the table. It's a very simple one select and then current date. So this will give me the current date.
[5:38:42] You can see this is giving the current date as per my system. if I run now. So I told you about this function current date and now. I've So now will return me what? It will return me the date as well as time. You
[5:38:58] can see over here. So this is returning me the date as well as time. Very simple one. Yeah. Now to use these functions.
[5:39:12] use these functions. For example, let's say I want to know okay, I've got the joining date. Yeah, I've got the joining date. So I might be using it, but let me tell you right away, let's say I want to know that it's
[5:39:27] been how many months, years or days since the employer has joined the So what I can say do is I can take the joining date yeah, and I can subtract it with the current date.
[5:39:40] So this will give me the number of days since the the employee is associated since the the employee is associated with the company. You want to extract the different different parts of the
[5:39:56] So for example, you have got the joining date. Let's say I want to extract year from the joining date. So for this, this is it. Yeah, I can use year function. In this I can pass the joining date column. It
[5:40:11] will simply extract all the years from the date Month will extract the month part from the date and day will extract the day part. So if I run this
[5:40:23] you can see it is extracting the year and month is extracting the month and day is extracting the day. Very simple yeah, the extraction part. So this is a very common function year,
[5:40:38] remember these functions because these are so easy, right? So few functions we expect that you remember, the interviewer will expect that you remember, would expect that you remember.
[5:40:51] Okay, moving ahead let's say that right now see joining date is giving me more what? It is giving me numbers. It is giving me numbers. Yeah, these are what? Numbers 6193.
[5:41:05] let's see that I do not want the numbers, rather I want the month name. You getting? I want the month name. I do not want the month number, rather I want the month name. So for this, we have got the function month month name.
[5:41:21] Month was returning six was returning six for me
[5:41:34] So you can use month name if you want to get the names of the month rather than the number of the months. Day name, as I told you before also. So this will where we were talking about
[5:41:48] where we were on this slide. So anyways, I told you about this day. So day will return Monday, Tuesday, Wednesday, Thursday. returning that what the day was on
[5:42:01] the day was on 15th of sorry, yeah, June 2021. So it was Tuesday. So this will help us return the So this will help us return the day name.
[5:42:16] more, but let me give you these. You can just play around with it and then we'll see more date time function.
[5:42:28] functions. So I've already talked about So date difference as the name says is going to dates. So for example, over here in the date
[5:42:44] So for example, over here in the date diff function, the current date. So this I I already explained you this example. So this will give me the current date and the joining date of the employee. So
[5:42:58] between the two dates and definitely that is going to the days in the company of the employee. Right? So if you want to find the days we are simply finding the difference between the
[5:43:13] current date and the joining date. So you can see it's very easy. In the same way you can add. Let's say you want to add some days, years, months to the date. So you can do that also. Now here you
[5:43:28] you know you cannot write plus and all because it will not give you it may not give you the expected results. So you can use date add function. Now this date add function takes two parameters. One is
[5:43:43] the original date column on which you want to do the addition and then what addition you want to do. So if you see over here in this what we are doing is in the joining date we are adding 30 days.
[5:43:57] Now you may want to add months. So all you have to do is in the interval you have to replace day with month. So this is what this is a unit. can give day you can give month, you can give year.
[5:44:13] There might be more units that you can give. You can just Google about this function. You can see the syntax of this function takes. So over here again I repeat this
[5:44:26] function is going to add 30 days. Here we are adding six months and here using the same function we are adding a year to the joining date. So if I run this
[5:44:42] here you'll find 30 days added, six months added and one year added. You can just quickly have a look to the result.
[5:44:59] it is the best practice that you Google about that function. For example here. Now I'm not sure about the syntax of this function. So I can simply Google this and MySQL.
[5:45:12] Yeah. So these days you don't need need even Google. You can ask ChatGPT this is give me the syntax of it? But yeah, over here, this is a AI part. So basically, you don't need to hop on to the documentation.
[5:45:27] got AI, so definitely we are going to use AI. So here also it's a Gemini. and you can use this. See, this is a syntax. It is also
[5:45:41] explaining you about each and everything about in the syntax. So the first is a date column interval value unit. So interval, that's what we gave.
[5:45:54] So interval, that's what we gave. Interval, value, any number it is telling us that what are are the different units that you can play around So I've shown you three units, but yes, you can have more units.
[5:46:08] please make sure that you be you make make use of So before AI, a lot of times, so this has happened with me also because if you go to any of
[5:46:21] the documentations, documentations are full of technical jargons. Yeah, it's full of jargons and sometimes it's so difficult to understand yeah, what this technical doc is saying
[5:46:33] because it's full of jargon. So it's always a best practice to use AI. You can use Gemini, you can use Google AI, of course you can see. So the AI overview or you can use ChatGPT, you
[5:46:46] can use Copilot. So whatever AI you are most comfortable with. So I am most comfortable with ChatGPT, so I make use of ChatGPT.
[5:46:59] because we're talking about ChatGPT, I would just like to show you that if you have the paid version of ChatGPT, now the paid version of ChatGPT is free for one year. So I have not paid even a single penny
[5:47:11] So still I've got the paid version of ChatGPT because it's free for one year. So, for that you have got SQL expert.
[5:47:24] So, this SQL expert is export with SQL. So, anyways I'm going to talk about that later. So, going back to our date functions in the same same way we have got date sub.
[5:47:39] Now I'm not going to talk much about it. It is very same as that of date add. Date add adds the date to the dates. Date sub subtracts the date from dates. Date sub subtracts the date from the date.
[5:47:55] and we are good to go. So, I'll just quickly give you this You can run it. Please make sure that you also keep I'm going to give you all the notes as I told you previously also maybe over the next week and I'll give
[5:48:08] you notes. But I want you guys to make this efforts of saving the notes by yourself. So, until unless you do not make efforts So, until unless you do not make efforts you will not learn anything.
[5:48:24] you can see. And again it's a very important like important. So, date format again very important. So, you can again Google about it. So, there are different different
[5:48:38] formats that you can pass. So, if you go to the documentation of this function you'll find all these formats. So, these are not something that is fabricated or curated by me. So, this is already there. They have got
[5:48:51] some table of formats, huge number of formats and you can take those formats in order to get your work done. So, here you can see that joining date is in if you if I quickly show you it's an year,
[5:49:03] month and day format. Now let's say I do not want it to be in this format. So, just that I have to pass this format in the date format function. It's so simple.
[5:49:18] So, it will format my date into my into the desired format. You can see over here. Now it is showing the date in this format, day, month, and year. So it's very easy. As I told you, you just have to Google this function
[5:49:32] and you'll find all the different type of formats that you can use. Now these this thing is not created by me. This is not something that I have written. These formats codes are already available on this function documentation and you can
[5:49:46] this function documentation and you can use them directly in your code. Moving ahead. Okay, this is just for your practice. So it's just that let's say that you want we are doing the filtration on the
[5:50:00] on the date. So employees who joined after 2020. So we are simply filtering it. Now experience in year just like in string I showed you that you can use multiple functions in order to get your
[5:50:13] multiple functions. So you want to know the experience in Yeah. So what we are doing is first of all we the date difference. So we are finding the number of days the
[5:50:30] employee has joined. Let's say the employee has joined it's been let's say suppose let's say okay, I have to do the maths I think. Let's say 720 days.
[5:50:44] Yeah, it's been 720 days or seven I think 30 days since employee has joined. So this will give me 730 days. But I want experience in years. So I'm doing the basic maths guys. I'm
[5:50:57] dividing it by 365 and that's what we do, right? So it will give me some number. Yeah, it will give me some number two point maybe So it will give me some number. I'm simply rounding it off to two decimal
[5:51:11] simply rounding it off to two decimal places. So this will give me the employee experience in numbers. the employee experience in numbers. Sorry, in years.
[5:51:29] syntaxes? That's a very good question, Kunal. So see, always try to understand. formatting. So you're not able to remember the as so
[5:51:43] [clears throat] a lot of time it happens when I'm taking interview, sometimes if there's something which is quite syntactically complicated, the smart candidates they say that I don't remember the
[5:51:56] the syntax and then I'll write the query for you. So you can always see the same thing. So you don't need to remember the syntax, but trust me, if you practice
[5:52:12] queries to some extent, you will remember some of the syntax, yeah, some of the basic syntax. For example, date format. So this will take joining date and this will take a format like this. Now you don't you don't need to remember
[5:52:25] If you try to remember also, you're going to forget on the 10th day. That's not possible. So just remember the concepts, practice. you can remember, well and good, but you don't need to remember the syntax.
[5:52:39] During your interview, you can always say, see, I don't remember the syntax. usually they allow, yeah, you can Google the syntax. So Google the syntax of of the function and then you can use it.
[5:52:52] And as I told you, usually when they ask the questions, the questions are not very much dedicated towards the function sites of SQL.
[5:53:06] Yeah, that will be more on the logical side. So not on the function side to be very honest. But you should definitely know all these functions. Now that's very important. If you say that you know SQL
[5:53:18] functions, that will not work. But the interviewer, usually when I also interview, like on when I'm the both the sides. It's more of the logic side. They check your logic. They don't check your you
[5:53:34] and all. So and that's more than enough. You don't need to remember the syntax.
[5:53:47] Can you please explain with example? So which one? Kavita, I think I'm taking examples for everything but yeah which one?
[5:54:04] are learning about functions. As we move forward, I'll tell you few of the right now because it's function there is less of logic and more of functions and its syntaxes. But as we move forward, I'm going to tell you
[5:54:18] example, I gave you the when I was teaching you group by. questions around group by and we solved one of the question also that we want students which are not of grade D
[5:54:32] grade D yeah and more than 70%. So this these are the questions that you may get in in your interviews. Yeah, so function related questions will be very less. It will be more of the logic
[5:54:44] related questions. Okay Kavita? So I've given you the example when we were learning group by. So as we proceed with window functions so window functions again we get a lot of
[5:54:57] questions around window function but yeah those are very easy functions to remember when it comes to syntaxes. Then when we learn about CT sub queries there there are a lot of questions around these topics but very less around
[5:55:11] these this topic what we are covering right now. whatever we are covering in the class that plus I'm going to give you some
[5:55:26] code well and good if you don't know anyways, going to tell Plus, I'm going to provide you with someone YouTube list, interview question list on SQL. You do all of these three things and
[5:55:40] you're good to go. You don't need to mug up these functions. Not needed. And in case any question which needs a lot of syntax,
[5:55:54] please be free to say that, okay, I don't remember the syntax. I would like don't remember the syntax. I would like to Google the syntax. okay, this is nothing. You can also order by the date. That is again
[5:56:10] possible. So we have been ordering our data. We have been sorting our data alphabetically or numerically, but you can also sort your data date wise. can also sort your data date wise. So I'll just provide you with
[5:56:26] and time functions. Again, I repeat, please do not burden yourself in learning or remembering the syntax of the function. If you practice it, you'll remember the basic syntax automatically.
[5:56:39] you encounter in your interviews, because see, when you're in projects anyways, you have ChatGPT, you have Google, you can write complex queries thinking much. Yeah, you have got ChatGPT after all AI these days. So
[5:56:53] writing queries is definitely not a big task anymore. thing. But yeah, cracking the interviews is something that is a challenge. So if you have anything which has got a lot of
[5:57:07] syntax, please feel free to ask your interviewers. Now, I'm not getting into this data set, what this data set is all about. I'm just quickly telling you that you can search for the data set of your
[5:57:19] choice, of your industry, something that some maybe some industry that work and then you can just play around with that data set. So what you can do is once you have the data, see I've got
[5:57:33] this data. So I can simply import this data into my database. So how I can import it? You can write down these steps and you can practice or you can try it out later on whenever you have time. It's very
[5:57:46] See guys, I always tell my student whenever it comes to exploring on or playing around with the options that we have got on anything for that matter, yeah, MySQL Workbench or anything for that matter.
[5:58:02] So just because you're learning MySQL or you're working on MySQL Workbench, we get scared. Oh, this might be something which is very difficult. But if I give you a new website just like Instagram, you'll automatically explore all the new
[5:58:14] So when it comes to the UI part, please be comfortable, please be more confident and try to explore everything. Yeah, for example, if you see you can drop the schema, you can alter the schema, you
[5:58:28] can create the schema. So a lot of options that you see over here are self-explanatory. So do not get scared by just reading this. Yeah, just explore it. The more you explore it, the more you'll learn
[5:58:42] and the more you'll able to remember the things. So I was talking about that we have got just give me a second Ravindra.
[5:58:56] you can import? You can right click on your database You can right click on your database table data import wizard. Then you have to browse. So I've got the data over here.
[5:59:11] Yeah, so I have to browse it. Click on next. So if you have some existing table, you can use any of the existing table in order to insert the data. But I don't have any any such existing table with
[5:59:23] the same schema. So I would like to create a new table. I'll say next. Yeah, so it is telling me about all the columns that this CSV file has
[5:59:36] everything all the columns and also if I do not want to include this column I can simply uncheck it. Yeah, I can click on next and I can click on next. So the data the whole data will be imported in my
[5:59:51] in my MySQL database and yeah I can use it just like any other you it got imported.
[6:00:08] Yeah, sorry. So I did it on on this one. So you can see the table has been created. Now I can use this table and the data which this table is the data that the table is having I can
[6:00:21] use it and I can just run the SQL queries. these steps. It's very easy. Right click on it and then click on table data import wizard. All the steps are
[6:00:34] self-explanatory. If you do it you'll able to do it without even looking into able to do it without even looking into your notes. data. I have downloaded the data from Kaggle.
[6:00:47] You can download the data from anywhere any source of your choice. So you might be having some data from your organizations. You can use any data Now you have to import that data into your database. You have to right click
[6:01:01] on it. Right click on sorry right click on the database table data import wizard. Let me do it over here. Yeah, table data import wizard. You have to browse your data.
[6:01:15] So you because I downloaded it over here so I'm just I'm going to browse it. I'm I'll say next. Now I may want to create a new table or I may want to use the existing table but I don't have any
[6:01:29] existing table with the same schema right the columns etc. So definitely I I I like database to create a table for this dataset. I'll click on next. Now,
[6:01:44] a lot of columns. Now, let's say I do not want these columns, so I can always not want these columns, so I can always remove these columns. from the CSV file, so I can always remove them. Click on next.
[6:01:58] Next and you can see that it will import and once the import is done, you're okay you can just click on finish. So it's that simple. Ravindra, to answer your question,
[6:02:13] see which one is more correct? Both are correct, but the the first one is a better one. Now there's a reason why the first one something called indexing and we are going to learn about it. So the first
[6:02:27] one will give you better performance. Now this is because of the indexing So we'll start with window function, very important with for you for your interviews. I think it works with CSV Kavitha. I
[6:02:41] I think it works with CSV Kavitha. I just need to check just need to check that what all formats it it takes. uh you can use CSV and JSON data files. Only these two formats it takes.
[6:02:58] So we are going to learn about the window functions now. So very very important for your interviews and window functions once you understand it, syntactically it's not that difficult.
[6:03:11] If you practice it enough, then you'll able to remember all the syntax of the window function. So what are window functions? What are window functions? So window functions, window functions, they perform
[6:03:25] calculation. Now let me write it across. So till now what we have learned is we have learned about the single row function. Single row functions works on Yeah. And it doesn't care about any other row.
[6:03:40] row. So it is independent of any other row data. Then we learn about aggregate functions. have got the whole data. I'm going to give you the aggregate of
[6:03:53] Now we're going to look into window function. So this is a quite a this is a big topic. So I may not able to finish it today. I'd give you the glimpse of it and we are going to continue it over the
[6:04:06] time. I've got 10-15 minutes. I'm going to use it in order to teach you and in order to give you brief about the window function over the next weekend. So window function they perform
[6:04:20] calculations. So definitely So definitely we are going to perform the calculations we are going to perform the calculations across a set of rows.
[6:04:39] set of rows. See, in case of window function as the name says window. So let's say you have got you've got some 10 rows of data. You've got data of 10
[6:04:54] 10 rows of data. You've got data of 10 rows. sum. You want to do the sum of marks. Now I'm giving you the same example of group by and I'll tell you that how window
[6:05:06] functions are different from group by. I'm going to speak about that also. So right now you may think that group by also does the same thing. But group by is very limited and there are lot of limitations with group by.
[6:05:19] With window functions there are lot of things that you can do. So anyways, so just hear me out. So let's say that you have to find the sum of marks of the students of different different
[6:05:33] of the students of different different city. this, you can do it with the window function. You you do it with the window So, window function it help us to perform
[6:05:46] the calculation across the set of rows. So basically what we do is we create the See here in this example we are creating the window of city. So Pune is one window.
[6:06:00] Yeah Mumbai is one window. Then Delhi is one window and Chennai is one window. So we are creating the windows, we are creating the windows. So we we can create the windows in very very different ways. So we have got
[6:06:14] very different ways. So we have got different ways of creating the windows. we are going to first see that what are the different window functions and once we start writing the code it will definitely be
[6:06:27] are. So we have got the aggregate window same like the aggregate functions that we have in the aggregate functions that we have in group by.
[6:06:42] So we have got the aggregate functions. Then we have got the ranking functions. Then we have got the ranking functions. So for example you have got some data. Yeah you want to give the ranking to your data. For example you have got
[6:06:56] students data. Now you want to rank your student based Yeah the student with the maximum marks will get the first rank. and so on and so forth. So you can easily perform all of these
[6:07:13] things like giving the ranking to the to your data etc. to your data etc. using the window ranking functions. now. I'm going to talk about value functions
[6:07:29] later on when we learn about it. It's again very simple. So all of these all of these functions are very simple.
[6:07:41] function. So just remember this syntax. I may not So just remember this syntax. I may not just give me a second. Yeah, I may not talk about the syntax again over the next weekend. I may I will start with
[6:07:57] uh like continuation of the window. I will not speak about the window syntax Window function syntax again. So see, it's very simple. So window function syntax is see, you have got a lot of function. I
[6:08:12] told you in the previous slide that we have got a lot of window functions. And I told you that window functions for window functions you have to give a window. You have to define a window, the window of your data
[6:08:27] on which you want to apply the function. Hear me out once again. fine. We are going to learn about these functions well and good. functions well and good. But we can apply these functions on
[6:08:40] the data and we can make the window of that data. I gave you one example, let's say city. So I'm making the window of city. Pune is one window. city. Pune is one window. All the students who belongs to Mumbai
[6:08:53] is one window and so on and so forth. So here if you see the syntax, we have got all of these things. So first is a function name. So in the previous slide we saw a lot of functions, window function. So let's say
[6:09:07] Yeah. Now we have to define the window. We have to define the window. So we define the window using over. So as soon as we have got over over keyword
[6:09:21] SQL knows that this is this is window function.
[6:09:36] function it is not a normal aggregate function. It gets to know function. It gets to know because of this keyword over.
[6:09:48] actually give the window, we define the window. the window. So for example I again repeat let's say
[6:10:01] that you have got data. You have got some red balls, you have got some blue balls, sorry. Okay you have got some blue balls, yellow balls. So now you want to
[6:10:16] balls, you want to do something with the blue the yellow balls. So with this code yeah with this part of the syntax you're
[6:10:28] going to create the window. So this is one window, this is another window and this is third window. Now it may be sounding complicated right now because I'm not covering the practical part of it.
[6:10:40] Yeah and to be very honest I'm sure it is sounding complicated. Do not worry once we see the practical part of it you'll find it very easy. It's like a walk in the park, it's so simple. You have got the data,
[6:10:53] you create the window of the data. It can be anything, it can be all the blue balls one window and it's very simple to create the windows. And then once your window is created you apply some functions on it.
[6:11:08] apply some functions on it. As simple as that. maybe write the code, start writing the code for it for now. We are going to keep it for the next weekend. So there's one thing, okay. So I will
[6:11:24] quickly revise what we have covered so far. I told you that this is very important. It may sound that it is not important,
[6:11:40] but when you start writing complicated queries you should know that what is the queries you should know that what is the execution order of your query. sense also. The table.
[6:11:53] Yeah, so it's first picks up the table. It filters the data from the table. uh takes those columns, yeah, the the table. Then you're sorting the data and then
[6:12:08] Then you're sorting the data and then you're limiting it. functions, count, sum, average, min and max. Then we learn about group by. So we learn about that with the help of
[6:12:23] group by you can group your data. You can group your data. functions on that data and that's what we did. We grouped our data using city.
[6:12:35] We grouped our data using the grades. And then we applied some aggregate functions on the same. Then we learn about SQL functions. Then we learn about SQL functions. We have got a lot of SQL functions.
[6:12:54] So we learn about few of the numeric functions, round, mod, power, etc. We learn about a lot of string functions. We tried a lot of string functions. And we tried a lot of date functions.
[6:13:08] functions that we tried today. this will give you the square root of a number.
[6:13:21] So these are very simple maths things. Then we learn about the string functions. We learn about concat, concat, upper, lower, replace, trim, concat, upper, lower, replace, trim, length, left, right, all of these.
[6:13:35] Then we learn about date and time functions. So we explored a lot of date and time functions in order to extract the date play around with the dates, yeah, modify them,
[6:13:49] them, get the current date and time, etc. about windows function over the next weekend. This is going to be a little big topic. We are going to practice a lot because it's very important topic
[6:14:04] for your interview. So I'm not going to rush So we are actually done for today because I can't start writing the code. because I can't start writing the code. I do not have that much of time left
[6:14:17] for today's class. So I'll just maybe give you the brief of ChatGPT also, how you can use ChatGPT in order to practice whatever we have order to practice whatever we have learned so far.
[6:14:44] are going to see that how you can use AI for for a lot of things. quickly give you the brief of it. So see, you can use ChatGPT, normal ChatGPT in order to ask questions related to your SQL
[6:15:01] or you can use SQL expert. Now, what is the difference between the two? The difference between the two is same as the difference between a normal doctor,
[6:15:16] and a specialist. So if you go to a physician, [sighs] a normal doctor, right? That doctor, he or she will able to help you with your issue, definitely.
[6:15:30] But that doctor is not specialist. Let's say that you're having some issues with okay. So let me say I don't want you guys to feel bad when it comes to the doctor. Let's
[6:15:42] Yeah, so let's say you're having cold. So one way is that you go to a normal doctor. So normal doctor is also going to help you. But what do you think? Who's better?
[6:15:56] You have some issues with your nose. So who's better? A normal a physician or So who's better? A normal a physician or ENT ENT right because ENT is specialized in that area and ENT will able to give you the best advice on your problem.
[6:16:13] So here also in ChatGPT so ChatGPT has SQL export. So it is specialized in SQL. So if you chat also it will give you all the answers. It will give you all the
[6:16:29] But if you go over here you'll get you may get better answers and you may get more advanced answers as simple as that. So you can use any of these. So you can use any of these. Now for your practice
[6:16:46] your five days. I want you guys to practice. Now let's say that you're practice. Now let's say that you're learning you can just write a prompt over here
[6:17:00] Now it will help you to create the table. So I give you the create table code, insert data code. So you can ask ChatGPT to you know give you the insert table code and insert data code. So help
[6:17:15] help me not curate. I'll say help me not curate. I'll say help me with the table code insertion code and some data insertion code.
[6:17:35] that I want it for the employees. So okay I'll just say can you create So okay I'll just say can you create the employee table?
[6:17:47] So you don't have to rely on my code. See it is giving you the code, the data. So you just have to copy this and paste it the way you have been copying and pasting from the chat.
[6:18:01] Then uh let's say you've got the data. Yes. So um maybe you want to learn you're learning you're done with learning. learning you're done with learning. So just tell say I
[6:18:17] it. Yeah I'm done with the learning. You can also ask it to explain and to help you learn. let's say you're done with it so you see here are the practice questions.
[6:18:31] Full name. So here are the practice questions. Now it it it it has not given questions. Now it it it it has not given you the solution so you can also say as well. So for all the practice questions it has
[6:18:46] it will give you the solutions as well. So in this way whatever we have covered so far you can pick each topic you